Comment créer un tableau de comparaison des offres ?

Dans cet exercice je vais vous montrer comment établir un tableau de comparaison des offres, en se basant sur les réponses des fournisseurs à un appel d'offres lancé par un hôtel qui désire renouveler les chaises de ses 150 chambres.

Ce tableau de comparaison des offres va permettre à notre hôtel de sélectionner le fournisseur qui propose le prix le moins cher. Ce fournisseur doit aussi respecter le critère de livrer la commande dans un délai qui ne dépasse pas les 30 jours.

Tout d’abord, j’ai préparé ce fichier (que vous pouvez télécharger ici) dans lequel vous pouvez voir, en plus de mon tableau de comparaison des offres, une petite partie intitulée Besoins et critères concernant ce que demande l’hôtel.

Et dans le tableau qui se trouve en dessous et qui porte le nom de Réponses des fournisseurs, j'ai saisi les données collectées des réponses des fournisseurs reçues par l’hôtel. Ces données vont me servir à remplir mon tableau de comparaison des offres et d’en faire une étude comparative afin de sélectionner le fournisseur qui présente la meilleure offre pour mon hôtel.

Entamons le remplissage de notre tableau de comparaison des offres :

Pour le prix unitaire, vous pouvez soit copier les prix à partir du tableau Réponses des fournisseurs soit créer une petite formule qui va introduire ces valeurs d’une façon automatique.

Sélectionnez donc les trois cellules Prix unitaire puis tapez =G9, validez ensuite par Ctrl+Entrée.

Calculer le montant total HT :

Le montant total = le prix unitaire x le nombre de chaises

De la même manière et pour insérer cette formule dans les trois cellules du Montant Total HT, sélectionnez ces dernières puis saisissez =B6*$G$3, validez enfin par Crtl+Entrée.

Remarque : N’oubliez pas de figer la cellule G3 qui contient le nombre de chaises.

Insérer le taux de remise

Vous remarquez sans doute que dans le tableau des réponses des fournisseurs, le fournisseur F2 ne fournit aucune remise, alors que les deux autres proposent leurs remises mais suivant quelques conditions :

• Pour le fournisseur F1 :

La remise de 6%  est applicable à partir de 100 chaises commandées, ce que nous allons traduire sous forme d’une formule utilisant la fonction SI :

- Sélectionnez donc la cellule B8 puis tapez cette formule : =SI(G3>=G11;G10;"")

- G3 contient le nombre de chaises commandées et qui est supérieur à 100 déjà saisi dans G11.

- Dans ce cas, Excel va afficher alors 6% se trouvant dans la cellule G10.

• Pour le fournisseur F2 :

Il propose une remise qui varie en fonction du nombre de chaises commandées :

5% à partir de 80 chaises et 10% si la quantité passe à 100 chaises ou plus.

Voici la formule à insérer dans la cellule D8 :

=SI(G3>=I13;I12;SI(G3>=I11;I10;""))

J’explique :

Si le nombre de chaises contenu dans G3 est supérieur ou égal à 100 se trouvant dans I13, le taux 10% sera affiché, sinon, on demande à Excel de vérifier si cette quantité commandée est supérieure ou égale à 80 que contient I11 et d’afficher donc 5% se trouvant dans I10.

Si aucune condition n’est respectée, alors Excel n’affichera rien.

Le nombre de chaises dépasse 100, alors le taux 10% est affiché.

Calculer le montant de remise :

Le montant de remise = le montant total x le taux de remise

Sélectionnez les trois cellules B9, C9 et D9 puis tapez  =B7*B8 et validez par Ctrl+Entrée.

Note : appliquez le format monétaire ou comptabilité pour mettre en forme les montants calculés.

Calculer le Net commercial

C’est le montant total HT – le montant de remise

Sélectionnez la plage de cellules B10:D10 puis tapez ceci =B7-B9 et validez ensuite par Ctrl+Entrée.

Insérer le taux d’escompte

Copiez tout simplement les valeurs à partir du tableau des réponses des fournisseurs ou sélectionnez les trois cellules de B11 à D11 puis tapez =G14 et validez par Ctrl+Entrée.

Calculer le montant d’escompte

Il égale à : Net commercial x taux d’escompte

Pour faire rapidement comme d’habitudes, sélectionnez la plage de cellules B12:D12 puis saisissez la formule suivante : =B10*B11, validez ensuite par Ctrl+Entrée

Calculer le Net financier

le Net financier= Net commercial - montant d’escompte

Sélectionnez les cellules du Net financier puis insérez cette formule =B10-B12, validez ensuite par Ctrl+Entrée.

Saisir le frais de transport :

Le fournisseur F2 propose de livrer la marchandise gratuitement contrairement aux deux autres.

Copiez donc le frais correspondant au fournisseur F1, quant au fournisseur F3 vous avez besoin de calculer ce frais en fonction du pourcentage qu’il précise (1% du Net commercial).

Pour cela, sélectionnez la cellule D14 et saisissez la formule suivante : =D10*1%

Calculer le Net HT

le Net HT = le Net financier + frais de livraison

Sélectionnez donc les trois cellules qui vont afficher le Net HT puis saisissez la formule =SOMME(B13:B14), validez ensuite par Ctrl+Entrée.

Insérer les données du délai de livraison et de la garantie

Sélectionnez la plage de cellules B16:D17 et insérez cette petite formule =G16 puis validez par Ctrl+Entrée.

Si vous souhaitez afficher les nombres des jours suivis du mot "jours", procédez comme suit :

• Sélectionnez les cellules contenant ces nombres puis cliquez sur la flèche située dans le coin inférieur droit du groupe Nombre.
• Dans la fenêtre qui apparaît cliquez sur Personnalisée
• Dans la zone Type tapez 0" jours" et cliquez enfin sur le bouton OK.

Suivez la même démarche si vous souhaitez également afficher le mot « mois » devant le nombre de mois de la garantie.

Choisir le fournisseur approprié

Pour ce faire, nous allons analyser ces deux données essentielles : Net HT et Délai de livraison.

Pour notre hôtel, ce qui est favorable pour lui c’est :

• Le Net HT le moins élevé,
• Un délai de livraison le plus bref qui ne dépasse pas les 30 jours prédéfinis.

Comme vous pouvez le remarquez, bien entendu, et d’après les résultats obtenus, le fournisseur F1 la ramène parce que son offre est la moins chère (30 629,70 Euros) , en plus, il est le seul qui propose de livrer la commande dans un délai plus rapide  (15 jours ) par rapport aux autres fournisseurs.

Pour traduire ce travail sous Excel, vous pouvez procéder ainsi :

• Afficher le nom du fournisseur proposant le prix le plus petit :

Sélectionnez la cellule B19, puis tapez la formule suivante :

=SI(B15=MIN($B$15:$D$15);B5;"")

Premièrement Excel va vérifier si le montant Net HT du Fournisseur F1 égale au montant le plus petit de ceux obtenus, si c’est le cas ; il affiche le nom de ce fournisseur contenu dans la cellule B5, sinon Excel n’affichera rien.

Puis on copie la formule dans les deux cellules C19 et D19.

• Afficher le nom du fournisseur qui va livrer la commande rapidement :

Sélectionnez la cellule B20, et tapez la formule suivante :

=SI(ET(B16<=$G$4;B16=MIN($B$16:$D$16));B5;"")

Dans cette formule, nous avons deux conditions à vérifier :

Le délai de livraison ne doit pas dépasser les 30 jours (B16<=$G$4) et s'il est le plus petit parmi les trois proposés par nos fournisseurs (B16=MIN($B$16:$D$16)).

J’ai utilisé dans ma formule la fonction ET pour tester ces deux conditions.

Si les deux conditions sont respectées, Excel affichera le nom du fournisseur F1 qui se trouve dans la cellule B5, sinon il n’affichera rien.

Copiez la formule dans les deux autres cellules C20 et D20.

D’après les résultats obtenus, j’arrive à décider que le fournisseur F1 est celui que l’hôtel va choisir pour lui passer la commande.

Voilà donc, vous avez suivi comment réaliser un tableau de comparaisons des offres sous Excel, en vous basant sur un exemple d’un hôtel qui a voulu commander 150 chaises pour ses 150 chambres selon des critères précis, et vous avez vu comment faire pour arriver à choisir parmi trois fournisseurs celui le plus approprié. 

Bien évidemment, il existe d'autres exemples de choix de fournisseurs tel que le choix effectué à l'aide de critères pondérés.

Lire la suite »

Comment utiliser la fonction RECHERCHEV ? 5 exemples et solutions de problèmes

Dans l’article d’aujourd’hui de la formation Excel, nous allons voir comment utiliser la fonction RECHERCHEV et comment dépasser les difficultés qu’elle peut présenter pour certains utilisateurs.

Contenu du cours

• Syntaxe de la fonction RECHERCHEV
• Les arguments de la fonction RECHERCHEV
• 5 exemples d’utilisation de la fonction RECHERCHEV
• Exemple 1 : Rechercher le prix d’un smartphone
• Erreur rencontrée de la fonction RECHERCHEV bien codée !!
• Exemple 2 : utiliser une valeur de recherche de type date dans la fonction RECHERCHEV
• Exemple 3 : Recherchez des valeurs appartenant à d’autres feuilles
• Exemple 4 : Ce que vous devez faire lorsque vous copiez votre fonction RECHERCHEV
• Exemple 5 : la valeur proche VRAI
• Condition à prendre en compte pour avoir le bon résultat

Vous pouvez débuter avec cette courte vidéo pour avoir une idée brève sur comment utiliser la fonction RECHERCHEV :

Maintenant, voyons ce petit exemple dans lequel nous allons utiliser la fonction RECHERCHEV. Pour cela nous allons utiliser le tableau suivant qui représente une liste des smartphones avec leurs prix et couleurs, pour répondre à cette question toute simple :

« Quel est le prix du smartphone Galaxy J5 ? »

- Sélectionnez alors, la cellule C12 pour entrer la fonction RECHERCHEV qui affichera par la suite le résultat voulu.

- La syntaxe de la fonction RECHERCHEV est :

RECHERCHEV(valeur_cherchée ; table_matrice ;no_index_col ;valeur_proche)

- valeur_cherchée  est le nom du smartphone désiré, c'est à dire Galaxy J5

- table_matrice  est le tableau ci-dessus, vous devez entrer la référence de la plage de cellules contenant les données du tableau.

- no_index_col est le numéro de la colonne qui contient le prix du smartphone recherché.
- valeur_proche: Choisissez enfin la valeur convenable pour cette recherche VRAI ou FAUX.

Votre formule ressemblera donc à l'une des expressions suivantes:

a) =RECHERCHEV(Galaxy J5;A1:D9;3;FAUX)               : [renvoie l’erreur #NOM ?]
b) =RECHERCHEV(Galaxy J5;A1:D9;3;VRAI)               : [renvoie l’erreur #NOM ?]

c) =RECHERCHEV("Galaxy J5";A1:D9;3;FAUX)           : [renvoie l’erreur #N/A]

d) =RECHERCHEV("Galaxy J5";A2:D9;3;FAUX)           : [renvoie l’erreur #N/A]
e) =RECHERCHEV("Galaxy J5";A2:D9;3;VRAI)            : [renvoie l’erreur #N/A]
f) =RECHERCHEV("Galaxy J5";B2:D9;2;FAUX)            : [renvoie le bon résultat 170]
g) =RECHERCHEV("Galaxy J5";B2:D9;2;0)                   : [renvoie le bon résultat 170]

A partir de ces formules, vous pouvez remarquer que le nom de la fonction est bien écrit, cependant, ce sont les deux dernières formules qui renvoient le bon résultat, les autres renvoient des erreurs. Le problème survient donc d’une utilisation incorrecte de l’un ou de tous les arguments de la fonction RECHERCHEV.

Les arguments de la fonction RECHERCHEV

Voyons à présent ce que nous disent les lignes suivantes sur l’utilisation des arguments de la fonction RECHERCHEV :

Syntaxe de la fonction RECHERCHEV

RECHERCHEV(valeur_cherchée ; table_matrice ;no_index_col ;valeur_proche)

Vous remarquez que la fonction RECHERCHEV comporte 4 arguments nécessaires à son bon fonctionnement :

valeur_cherchée : appelé également le critère de recherche, est la valeur que va rechercher la fonction RECHERCHEV dans un tableau de données (table_matrice ) pour renvoyer une autre valeur de retour qui correspond à la valeur recherchée et qui se trouvera dans la colonne définie par l’argument no_index_col.

La valeur_cherchée par la fonction RECHERCHEV pourra être exacte, c’est-à-dire elle se trouve dans la première colonne de la table_matrice, dans ce cas on choisit FAUX pour la valeur_proche . Ou bien la fonction RECHERCHEV cherchera une valeur proche de celle recherchée si cette dernière ne se trouve pas dans la table matrice, on tape alors VRAI pour l’argument valeur_proche.

Et pour bien éclaircir les choses, je vous présente 5 exemples qui vont vous permettre de bien savoir comment utiliser correctement ces arguments :

5 exemples d’utilisation de la fonction RECHERCHEV

Exemple 1 : Rechercher le prix d’un smartphone

Reprenons l’exemple précédent dans lequel nous voulions chercher le prix du smartphone "Galaxy J5" :

- Sélectionnez donc la cellule C12 et tapez la formule suivante :

=RECHERCHEV("Galaxy J5";B2:D9;2;FAUX)

- Le premier argument contient donc la valeur recherchée qui est Galaxy J5, et remarquez qu’elle est écrite entre deux guillemets puisqu’il s’agissait d’un texte. (Cette règle n’a pas été respectée dans les deux formules « a » et « b »)

- La table matrice est le tableau de données ou la plage de cellules dans laquelle la recherche sera effectuée. Et comme vous le voyez, nous avons sélectionné la plage de cellules B2:D9. Les titres des colonnes ne sont pas inclus.

(On a pas choisi la bonne plage de cellules dans les formules a,b,c,d et e.)

- Veuillez remarquer aussi que la table_matrice ne contient pas la colonne A car Excel impose que la valeur recherchée doit se trouver dans la première colonne de la table_matrice. La valeur Galaxy J5 se trouve donc dans la colonne B.

- Le no_index_col : est 2, c’est-à-dire le numéro d’ordre de la colonne dans la table_matrice sélectionnée qui contient le prix du smartphone, et non 3 comme s’est écrit dans les formules a,b,c,d et e.

- Valeur proche : nous avons écrit FAUX, car nous voulions rechercher ce nom exact : Galaxy J5.

Après avoir validé cette formule de recherche, Excel va chercher dans la deuxième colonne de la plage B2:D9 le prix qui se trouve dans la même ligne où se situe la valeur recherchée Galaxy J5 (ligne 2). L’intersection de la colonne 2 et la ligne 2 est la cellule C3 qui contient la valeur 170 Euros.

Cette valeur est nommée valeur de retour et c’est elle qui correspond à la valeur recherchée comme c'est mentionné dans la définition des arguments en haut.

Note : Pour que la fonction RECHERCHEV fonctionne bien aussi, les données doivent être organisées en colonnes verticalement comme dans le tableau précédent.

Une autre façon d’écrire la formule :

Au lieu de taper Galaxy J5 dans la formule =RECHERCHEV("Galaxy J5";B2:D9;2;FAUX), vous pouvez l’écrire dans une autre cellule et entrer seulement sa référence dans le premier argument :

Par exemple, tapez Galaxy J5 dans B12 et la formule s’écrira de la façon suivante :

=RECHERCHEV(B12;B2:D9;2;FAUX)

Remarquez que B12 n’est pas mise entre deux guillemets.

Lorsque vous effectuez une autre recherche sur un autre smartphone, tapez seulement son nom dans la cellule B12 et le résultat sera mis à jour.

Erreur rencontrée de la fonction RECHERCHEV bien codée !!

Ça arrive parfois que vous obteniez une erreur N/A même si les arguments de votre fonction RECHERCHEV sont bien saisis et que la valeur de recherche existait dans votre tableau. Dans ce cas vérifiez si vous n’avez pas tapé d’espaces avant ou après la valeur recherchée.

Exemple 2 : utiliser une valeur de recherche de type date dans la fonction RECHERCHEV

L’intérêt de cet exemple est de vous faire montrer que l’argument valeur_recherchée n’est pas limité à un seul type de données qui est texte, mais peut être aussi de type date.

- Voici un tableau de données qui va vous aider à accomplir cette tâche :

- Sélectionnez donc la cellule F4 et entrez la formule suivante :

=RECHERCHEV(F2;A2:C22;2;FAUX)

- Dans la cellule F2, saisissez une date de naissance comme valeur de recherche : 23/09/1997

- La fonction RECHERCHEV renvoie alors la valeur correspondante à cette date qui est le prénom du client: Maria.

Note : la valeur recherchée que contient la fonction RECHERCHEV peut être de différents types de données : numéro, texte, date, fonctions…

Exemple 3 : Recherchez des valeurs appartenant à d’autres feuilles

Par exemple, vous pouvez définir une feuille comme base de données qui contiendra la table_matrice, et une autre feuille de calcul pour afficher vos résultats de recherche.

Dans l’exemple suivant, vous avez deux feuilles:

• Une, appelée "Base de données" qui contient un tableau de données des vendeurs : nom, date de naissance, ville et montant réalisé, et dans lequel vous allez effectuer votre recherche,

• Et une appelée "Mt réalisé" qui affichera le montant réalisé du vendeur recherché.

- Sélectionnez la cellule C4 de la feuille de calcul « Mt réalisé » et tapez la formule suivante :

=RECHERCHEV(C2;'Base de données'!A2:D22;4;FAUX)

- C2 : contient la valeur recherchée : Nom du vendeur.

- 'Base de données'!A2:D22 : fait référence à la table_matrice qui se trouve dans la feuille de calcul "Base de données". Remarquez que nous avons commencé par taper le nom de la feuille suivi d’un point d’exclamation puis de la référence de la plage de cellules contenant les données des vendeurs.

- 4 : est le numéro de la colonne qui affiche les montants réalisés.

- Faux : permet de rechercher le nom exact du vendeur.

- Entrez donc un nom de vendeur tel qu’il est saisi dans le tableau de données et validez par Entrée.

- Voyez ce que vous pouvez voir comme résultat :

Exemple 4 : Ce que vous devez faire lorsque vous copiez votre fonction RECHERCHEV

Nous allons reprendre le dernier exemple, et nous allons ajouter une autre donnée à afficher en dessous du montant réalisé ; par exemple le nom de la ville du vendeur.

Dans cet exemple, c’est la cellule C5 qui affichera ce nom. Donc au lieu de retaper la formule RECHERCHEV, nous allons tout simplement copier la formule utilisée précédemment pour trouver le montant réalisé, et nous allons faire une petite modification en tapant 3 à la place de 4 pour indiquer le numéro de la colonne Ville.

- La formule est la suivante : =RECHERCHEV(C3;'Base de données'!A3:D23;3;FAUX)

- Et comme le remarquez une erreur est renvoyée.

- En comparant les deux formules, vous allez constater que les références des cellules sont incrémentées :

• La référence de la cellule utilisée dans l’argument valeur_cherchée a changé, elle est devenue C3 au lieu de C2
• La référence de la table_matrice a changé également, vous avez 'Base de données'!A3:D23 à la place de 'Base de données'!A2:D22.

- Alors pour dépasser ce problème vous devez figer ces références :

• Retournez à la première formule (dans C4) et définissez des références absolues comme suit :

=RECHERCHEV($C$2;'Base de données'!$A$2:$D$22;4;FAUX)

• Faites copier-coller dans la cellule C5 et modifiez 4 par 3 puis validez.

=RECHERCHEV($C$2;'Base de données'!$A$2:$D$22;3;FAUX)

Vous pouvez aussi recourir à une autre solution, c’est de donner un nom à la plage de cellule table_matrice 'Base de données'!A2:D22.

Par exemple :

• Sélectionnez la plage A2:D22 puis tapez un nom : « matrice » (par exemple) dans la zone nom de cellule ou cliquez sur Définir un nom sous l’onglet Formules dans le groupe Noms définis puis tapez le nom voulu.

• Retournez maintenant à votre formule de recherche et remplacez 'Base de données'!A2:D22 par le nouveau nom : matrice puis validez.

• Faites copier-coller dans C5 et n’oubliez pas de modifier le chiffre 4 par 3.

Exemple 5 : la valeur proche VRAI

L’argument valeur_proche de la fonction RECHERCHEV peut être soit VRAI ou FAUX.

Dans notre exemple précédent des smartphones, vous avez vu comment nous avons fait pour trouver le prix correspondant au nom du smartphone recherché, et nous avons réalisé un exemple d’utilisation de la fonction RECHERCHEV pour trouver le prix du Galaxy J5.

Voici la formule écrite : =RECHERCHEV("Galaxy J5";B2:D9;2;FAUX)

Et comme vous le remarquez, la valeur proche est définie sur FAUX, c’est-à-dire qu’on demande à Excel de rechercher dans la première colonne de la plage de cellules B2:D9, le nom exact Galaxy J5 et puis de retourner son prix correspondant qui se trouve dans la même ligne.

Mais si Excel ne trouve pas ce nom exact Galaxy J5, que va-t-il se passer ? dans ce cas la fonction RECHERCHEV renvoie l’erreur N/A.

Et si la valeur_proche est VRAI ?

Prenons l’exemple suivant : un magasin offre à ses clients des taux de remise sur leurs achats selon les conditions illustrées dans le tableau suivant :

Nous voulons par exemple utiliser la fonction RECHERCHEV pour trouver le taux de remise pour un client qui a atteint un montant d’achat de 789 Euros.

Ce chiffre n’existe pas alors dans le tableau des remises, et si nous saisissons la valeur_proche FAUX dans la fonction RECHERCHEV, nous obtiendrons une erreur N/A.

Dans ce cas, la solution est d’entrer la valeur VRAI.

Notre formule s’écrira alors de la façon suivante :

=RECHERCHEV(B8;A2:B5;2;VRAI)

Excel va rechercher donc la valeur approximative de 789 dans la première colonne de la plage A2:B5 et qui doit être inférieure à 789.

Il y en a donc deux valeurs : 0 et 500, Excel va alors sélectionner 500 car c’est le nombre le plus grand des deux et le plus proche de 789, et ensuite il renvoie le taux de remise correspondant à 500 qui est 10%.

Nous concluons donc que la valeur_proche VRAI indique à Excel de rechercher la valeur inférieure la plus proche de la valeur recherchée lorsqu’il ne trouve pas la valeur exacte.

Important :

Il reste une condition à prendre en compte pour avoir le bon résultat, c’est que nous devons trier en ordre croissant les données de la première colonne de la table_matrice. Si non Excel affiche une erreur N/A.

Note :

• Si on omet la valeur_proche, la valeur par défaut sera toujours VRAI.
• On peut écrire VRAI ou 1 pour indiquer une valeur approximative, aussi on peut écrire FAUX ou 0 pour indiquer une valeur exacte.

enfin, voici un lien d'un article dans lequel j'ai expliqué comment utiliser RECHERCHEV pour calculer un total d'une façon dynamique. Cliquez alors sur ce lien et regardez la vidéo présente dans cet article:

Comment utiliser RECHERCHEV pour calculer un total d'une façon dynamique ?

Lire la suite »

9 cas qui expliquent comment utiliser la fonction NB.SI.ENS

L’article présent de la formation Excel vous fait découvrir comment utiliser la fonction NB.SI.ENS pour appliquer plusieurs critères aux plages de cellules. Les critères qui seront traités dans les 9 exemples d’utilisation de NB.SI.ENS qui suivent, sont de types texte, numérique, date … Cet article va vous montrer aussi comment compter le nombre de cellules non vides correctement, et comment utiliser NB.SI.ENS avec OU logique.

La fonction NB.SI.ENS vous permet de compter le nombre de cellules répondant à plusieurs critères de types différents : Nombre, Texte, Date, valeur logique….

La syntaxe de la fonction NB.SI.ENS est :

NB.SI.ENS(plage_critères1; critères1; [plage_critères2; critères2]…)

Les deux arguments plage_critères1 et critères1 sont obligatoires au fonctionnement de la fonction NB.SI.ENS, quant aux autres paires plages_critères/critères, elles sont facultatives.

Combien de paires plages-critères/critères pouvez-vous introduire dans la fonction NB.SI.ENS ?

• La fonction NB.SI.ENS peut contenir jusqu’à 127 paires plages-critères/critères.

Note : la plage critères doit être insérée toujours avant le critère associé.

Pour découvrir rapidement comment utiliser la fonction NB.SI.ENS, regardez cette vidéo :

Pour bien comprendre comment utiliser la fonction NB.SI.ENS, veuillez suivre ces 9 exemples :

• Partons de ce petit tableau et entamons le premier exemple :

Exemple 1 : Combien de vendeurs ont réalisé un montant de vente plus que 3000 Euros à Paris ?

• Sélectionnez une cellule pour y insérer la formule suivante :

=NB.SI.ENS(C2:C14;"Paris";D2:D14;">3000")

Cette formule permet de sélectionner en premier les cellules contenant « Paris » dans la plage de cellules C2:C14, puis de trouver les montants supérieurs à 3000 Euros dans la plage de cellules D2:D14 et qui correspondent à ces cellules.

Note : pour le premier critère "Paris", il est écrit entre guillemets puisque c’est un texte. Et pour le deuxième critère ">3000", il est écrit aussi entre guillemets parce que le nombre 3000 est précédé d’un opérateur de comparaison ">".

Normalement lorsqu’un critère est de type numérique, on l’écrit sans guillemets, mais si vous lui faites accompagner <,>, = ou <> vous devez le mettre entre guillemets.

Exemple 2 : Utilisation des références de cellules dans les arguments de la fonction NB.SI.ENS

Dans cet exemple, nous avons écrit les critères Nom de ville dans la cellule F4, et le montant de vente dans F6.

Au lieu de modifier à chaque fois notre formule exemple de NB.SI.ENS, nous allons tout simplement changer les données dans ces deux cellules F4 et F6 :

• Voici donc notre formule dynamique que nous allons utiliser :

=NB.SI.ENS(C2:C14;F4;D2:D14;">"&F6)

• Remarque n°1 : le critère nom de la ville est remplacé par la référence de cellule F4 sans guillemets.
• Remarque n°2 : pour le critère >3000, nous avons laissé l’opérateur > entre guillemets et avons ajouté une esperluette suivie de la référence F6 sans guillemets.

Essayons maintenant de remplacer Paris par Lisbonne et remarquez que le résultat est mis à jour automatiquement :

Note : ne mettez pas une référence de cellules entre guillemets lorsque vous l’utilisez comme critère de la fonction NB.SI.ENS.

Exemple 3 : Combiner des astérisques avec des références de cellules

Par exemple, si vous voulez compter le nombre de cellules contenant le nom Antoine qui travaille à Lisbonne, tapez la formule suivante :

=NB.SI.ENS(B2:B14;"*"&F6&"*";C2:C14;F4)

Vous voyez les deux esperluettes qui se sont placées avant et après la référence de cellule F6.

Exemple 4 : Compter le nombre de cellules contenant des montants entre 2000 et 4000 Euros

Dans cet exemple, vous allez traiter deux critères de type numériques et qui se trouvent dans la même colonne ou plage de cellules.

• Commencez d’abord par donner à chaque colonne de votre tableau un nom significatif que vous allez utiliser comme référence dans les arguments de votre fonction NB.SI.ENS. ça va vous faciliter le travail !

Par exemple renommer la plage :

• A2:A14 par Date_Vente
• B2:B14 par Vendeurs
• C2:C14 par Villes
• D2:D14 par Montants.

• Sélectionnez une cellule vide et tapez =NB.SI.ENS( puis tapez  mon  pour faire apparaître le nom de la colonne Montants.
• Sélectionnez-le donc et continuez la saisie de votre formule pour obtenir la formule suivante :

=NB.SI.ENS(Montants;">2000";Montants;"<4000")

Attention !

Il faut que les plages de critères utilisées dans la fonction NB.SI.ENS aient le même nombre de lignes, si non Excel retournera une erreur de type #VALEUR !.

Utilisation des critères de type date dans la fonction NB.SI.ENS

Exemple 5 : Compter le nombre de ventes réalisées entre le 05/04/2017 et le 08/04/2017

• Sélectionnez une cellule vide et entrez la formule suivante :

=NB.SI.ENS(A2:A14;">=05/04/2017";A2:A14;"<=08/04/2017")

• Faites attention aux guillemets !

Exemple 6: Compter le nombre de ventes réalisées à Londres depuis le 05/04/2017 jusqu’aujourd’hui et ayant un montant supérieur à 2000 Euros

Notre formule va contenir donc trois plages de critères : Date de vente, Ville et Montant de vente et la fonction AUJOURDHUI() :

• Sélectionnez une cellule vide et entrez la formule suivante :

=NB.SI.ENS(Date_Vente;">=05/04/2017";Date_Vente;"<="&AUJOURDHUI();Ville;C14;Montants;">2000")

• L’objectif de cet exemple est de vous présenter une fonction NB.SI.ENS contenant quatre critères de 3 types de données différents.

NB.SI.ENS différent de

Exemple 7 : Compter le nombre de cellules contenant toutes les noms de villes sauf  "Paris" et dont la date de vente est postérieure ou égale à 05/04/2017

• Entrez la formule suivante en introduisant l’opérateur <> :

=NB.SI.ENS(Ville;"<>paris";Date_Vente;">=05/04/2017")

Exemple 8 : Compter le nombre des cellules non vides

Vous avez vu dans l’article: "NB, NBVAL et NBVIDE comptent le nombre de cellules différemment" comment compter le nombre de cellules non vides en utilisant la fonction NBVAL. Et pour le faire avec la fonction NB.SI.ENS, tapez la formule suivante qui va vous permettre de compter le nombre de montants de ventes réalisées à Londres en ignorant les montants non encore enregistrés :

• Par exemple la cellule D14 est vide.

=NB.SI.ENS(Ville;"Londres";Montants;"<>"&"")

• Remarquez donc que le critère vide est exprimé par les deux guillemets ""
• Remarquez aussi que nous avons mis une esperluette & entre l’opérateur <> et le critère vide pour que la fonction renvoie le bon résultat.

NB.SI.ENS avec OU

Vous avez sans doute constaté que tous les critères utilisés dans la fonction NB.SI.ENS sont évalués dans les exemples précédents, alors que parfois vous souhaitez que NB.SI.ENS soit effectuée en répondant à un seul caractère au moins,  tout comme l’utilisation d’un OU logique.

Pour utiliser donc la fonction NB.SI.ENS avec OU, la solution consiste à faire l’addition des fonctions NB.SI.ENS associée chacune à son critère.

Exemple 9 : Compter le nombre de cellules contenant Paris et Londres dont les montants réalisés sont supérieurs à 2000 Euros.

• Sélectionnez une cellule vide et entrez la formule suivante :

=NB.SI.ENS(Ville;"Paris";Montants;">2000")+NB.SI.ENS(Ville;"Londres";Montants;">2000")

Dans cet exemple nous avons utilisé une fonction NB.SI.ENS avec le critère Paris et une autre avec le critère Londres, et les deux contiennent aussi le même critère >2000

Pour vous assurer bien de la fonctionnalité de cette formule, faites un filtre des données comme s’est illustré dans l’image animée suivante :

La fonction NB.SI.ENS et le VBA

Terminons cet article par une solution à un petit problème qui gêne beaucoup les utilisateurs de VBA Excel (surtout les novices !) lorsqu’ils veulent utiliser la fonction NB.SI.ENS dans leur code VBA.

L’erreur qu’ils commettent c’est qu’ils utilisent la nomination en français de la fonction NB.SI.ENS dans leurs codes et bien sûr ces codes ne fonctionneront pas bien.

C’est tout à fait normal car dans VBA, ils doivent taper la fonction NB.SI.ENS en anglais comme ça : COUNTIFS.

Lire la suite »