Compter sur de multiples conditions additionnées

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Compter sur de multiples critères

Excel offre des fonctions de dénombrement conditionnel. Il s'agit notamment des fonctions Nb.Si et Nb.Si.Ens. Mais lorsque ces conditions sont multiples et que seule l'une d'entre elles suffit pour honorer l'incrémentation du décompte, elles sont inopérantes.



Dans l'exemple proposé par la capture, nous travaillons sur un petit parc automobile. En deuxième colonne, trois critères de recherche sont émis. Mais ils pourraient être plus nombreux d'où la présence des cases du dessous sanctionnées par la mention Vide. En troisième colonne, nous devons fournir le nombre de véhicules correspondant avec ces multiples recherches. Vous l'avez compris, une automobile ne peut pas représenter deux véhicules à la fois. Il ne s'agit donc pas de recouper les conditions de recherche mais bien de les additionner.

Source et présentation
Pour appuyer la démonstration de cette nouvelle astuce Excel, nous proposons de travailler à partir de ce tableau illustré.

• Télécharger le classeur correspondances-sur-criteres-croises.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier résultant pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Trois véhicules à chercher et à dénombrer sont déjà inscrits en colonne E. Et à ce titre, vous remarquez qu'une mise en forme conditionnelle les repère déjà en colonne D. Il convient de transcrire ce résultat visuel en chiffres en colonne F. Nous attendons le même décompte strict en cellules F4 et F7. Mais pour bien comprendre le mécanisme, nous allons exploiter deux méthodes. La première, bien que plus conventionnelle, va vite montrer ses limites quant au nombre de critères potentiellement extensibles à considérer. C'est pourquoi, nous la ferons évoluer avec une seconde méthode de calcul parfaitement dynamique. En cellule F10 enfin, nous verrons comment nous pouvons réaliser des dénombrements globaux. Seule la marque du véhicule peut être inscrite. Dans ce cas, aucune correspondance exacte ne peut être trouvée. Nous fournirons donc un décompte des résultats approchants.

Compter sur des critères additionnés
Comme vous le savez, dans sa version classique, la fonction Excel SommeProd permet d'additionner les résultats des multiplications opérées entre les lignes respectives de plusieurs matrices. Mais dans sa version dérivée, elle peut accueillir des matrices conditionnelles. En d'autres termes, elle vérifie si les conditions posées sur les plages sont vraies ou fausses. Lorsqu'elles sont vraies et additionnées à d'autres matrices conditionnelles, il en résulte des repérages sanctionnés par le chiffre 1. Tous ces chiffres sommés naturellement par la fonction à l'issue, fournissent le décompte des résultats concordants sur ces critères empilés.

Si vous consultez la zone Nom en haut à gauche de la feuille Excel, vous constatez que les deux premières colonnes du tableau sont respectivement reconnues sous les intitulés Autos et Criteres. Ces noms vont s'avérer précieux pour la construction des formules matricielles.

• Sélectionner le premier résultat strict à trouver en cliquant sur sa cellule F4,
• Taper le symbole égal (=) pour débuter le calcul,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : =SommeProd(,
• A gauche de la barre de formule, cliquer sur le petit bouton Insérer une fonction,

Pour la bonne compréhension du raisonnement et des résultats, nous allons effectivement nous faire aider par l'assistant fonction.

• Dans la zone matrice1, ouvrir une parenthèse pour accueillir la première matrice,
• Désigner la plage des véhicules par son nom, soit : Autos,
• Taper le symbole égal (=) pour annoncer la condition à honorer sur cette première matrice,
• Puis, cliquer sur le premier véhicule mentionné en critère, soit la cellule E4,
• Fermer alors la parenthèse de cette première matrice conditionnelle,

Dans ce raisonnement matriciel, nous cherchons premièrement à repérer toutes les positions des véhicules qui concordent avec le premier critère. Et à ce titre, vous notez que des résultats apparaissent sur la droite de la zone Matrice1. Dans ce contexte particulier, c'est tout l'intérêt de l'assistant fonction. Chaque valeur booléenne Vrai repère la position d'un résultat trouvé. Ici, il n'y en a qu'un pour l'instant. Nous devons faire de même pour repérer toutes les positions des concordances avec les autres conditions. Et comme nous l'avons déjà dit, ces conditions ne peuvent pas être recoupées. Elles doivent être combinées donc additionnées.

• Inscrire le symbole plus (+) pour annoncer la condition à combiner,
• Ouvrir une nouvelle parenthèse pour accueillir la deuxième matrice conditionnelle,
• Désigner de nouveau la plage des véhicules par son nom, soit Autos,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Désigner le deuxième véhicule stipulé en critère en cliquant sur sa cellule E5,
• Puis, fermer la parenthèse de cette deuxième matrice conditionnelle,

Les indicateurs réapparaissent. Mais du fait de l'opération d'addition enclenchée, ils sont transformés en chiffres. Chaque chiffre 1 repère un résultat concordant. Deux automobiles sont donc d'ores et déjà recensées. Ce sont ces chiffres que la fonction SommeProd additionnera au final pour livrer ce dénombrement multicritère.

• Taper de nouveau le symbole plus (+) pour annoncer la dernière condition à empiler,
• Ouvrir une dernière parenthèse pour accueillir la troisième matrice conditionnelle,
• Désigner de nouveau la plage des véhicules par son nom, soit : Autos,
• Taper le symbole égal (=) pour annoncer le dernier critère à vérifier,
• Cliquer sur le troisième véhicule mentionné en critère, soit sur sa cellule E6,
• Fermer la parenthèse de cette dernière matrice conditionnelle,

Les indicateurs de repérage refont surface. Ils indiquent que trois véhicules sont jugés comme concordants par rapport aux conditions additionnées.

• Cliquer sur le bouton Ok de l'assistant fonction pour valider la formule,

De retour sur la feuille, nous obtenons bien un décompte de trois automobiles trouvées.

• En cellule D7, remplacer le véhicule Cirtoën C4 par : Peugeot 208,

A validation et comme vous pouvez le voir, le décompte s'actualise parfaitement. Le nouveau véhicule est considéré, repéré et intégré dynamiquement. La syntaxe complète de la formule matricielle que nous avons construite est la suivante : =SOMMEPROD((Autos=E4) + (Autos=E5) + (Autos=E6)).



Mais nous l'avons dit, cette méthode a des limites. En E7, si vous ajoutez un nouveau véhicule à trouver, bien qu'il soit effectivement présent dans la liste, il n'est pas considéré dans le décompte. Dans la formule, le dernier critère posé s'arrête en cellule E6. Pour les considérer tous avec cette méthode, nous devrions les énumérer un à un. Il en résulterait une construction fastidieuse et une syntaxe très nettement alourdie.

Plage matricielle de critères
Nous le savons, la fonction Excel Nb.Si est capable de compter le nombre de fois qu'un critère est trouvé dans une plage de cellules. Mais dans son exploitation classique, elle n'est pas capable de raisonner de façon matricielle. En d'autres termes, elle n'est pas capable de considérer une plage de critères à dénombrer sur une autre plage de cellules. Pourtant, imbriquée dans la fonction SommeProd, la magie opère. Et c'est l'astuce que nous proposons de découvrir.

• Sélectionner le second résultat strict à trouver en cliquant sur sa cellule F7,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Inscrire la fonction de dénombrement conditionnel suivie d'une parenthèse, soit : Nb.Si(,
• Désigner la plage de recherche par son nom, soit : Autos,
• Taper un point-virgule (;) pour passer dans l'argument du critère de la fonction Nb.Si,
• Désigner la plage des critères par son nom, soit : Criteres,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Enfin, valider le calcul à l'aide de la touche Entrée du clavier,

Le décompte obtenu diffère du précédent et à juste titre. Cette fois, la condition supplémentaire émise précédemment est bien intégrée. Et si vous ajoutez en dessous un nouveau véhicule à rechercher, comme Peugeot 108, il est instantanément dénombré tandis que le premier décompte ne bouge plus.



Avec une syntaxe pourtant plus simple, nous obtenons une formule beaucoup plus puissante. Elle est capable de considérer des conditions non encore émises. La formule que nous avons construite est la suivante : =SOMMEPROD(NB.SI(Autos; Criteres)).

Critères approchants
Pour finir, nous souhaitons livrer le résultat du décompte sur des recherches partielles. L'utilisateur peut ne mentionner que la marque à la place du nom complet d'un véhicule. L'objectif est d'avoir une idée sur la proportion des voitures appartenant à une certaine marque. Nous l'avons déjà appris, ce sont les WildCards qui permettent d'observer les caractères dans leur généralité. Et précisément, le symbole de l'astérisque permet de considérer n'importe quel caractère et quel qu'en soit le nombre. Placé en préfixe et en suffixe d'un critère, il autorise de retenir toutes les occurrences contenant l'expression cherchée.

• Copier (CTRL + C) la syntaxe de la précédente formule,
• Sélectionner alors la cellule F10 et cliquer dans sa barre de formule pour l'activer,
• Coller (CTRL + V) la syntaxe précédemment copiée,
• Puis, l'adapter comme suit : =SOMMEPROD(NB.SI(Autos; "*" & Criteres & "*")),

Grâce au Et Commercial (&), nous concaténons chaque astérisque avec la matrice des critères.

• Valider le calcul avec la touche Entrée du clavier,

Dans la mesure où aucun critère vague n'est encore formulé, le résultat retourné est pour l'instant identique au précédent. Six automobiles concordent.

• En cellule E5, remplacer le véhicule Renault Clio par la marque Renault seule,

A validation et comme vous pouvez le voir, les deux compteurs stricts sont décrémentés. Fort logiquement, une concordance stricte disparaît. Mais dans le même temps, le compteur des résultats proches monte sensiblement. Toutes les conditions précises sont bien considérées. Et en plus de cela, l'ensemble des véhicules de la marque Renault viennent s'additionner.