Un problème qui revient souvent dans Excel est le comptage ou la sommation en fonction de plusieurs conditions OR. Par exemple, vous avez peut-être besoin d'analyser les données et de compter les commandes à Seattle ou à Denver, pour les articles rouges, bleus ou verts? Cela peut être étonnamment délicat, donc naturellement, c'est un bon défi!
Le défi
Les données ci-dessous représentent les commandes, une commande par ligne. Il y a trois défis distincts.
Quelles formules dans F9, G9 et H9 compteront correctement les commandes avec les conditions suivantes:
- F9 - Tshirt ou Sweat à capuche
- G9 - (Tshirt ou Sweat à capuche) et (Rouge, Bleu ou Vert)
- H9 - (Tshirt ou Sweat à capuche) et (Rouge, Bleu ou Vert) et (Denver ou Seattle)
L'ombrage vert est appliqué avec une mise en forme conditionnelle et indique les valeurs correspondantes pour chaque ensemble de critères OR dans chaque colonne.
Pour votre commodité, les plages nommées suivantes sont disponibles:
item = B3: B16
couleur = C3: C16
ville = D3: D16
La feuille de travail est jointe. Laissez vos réponses ci-dessous en commentaires!
Réponse (cliquez pour développer)Ma solution utilise SUMPRODUCT avec ISNUMBER et MATCH comme ceci:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Qui comptera les commandes où…
- L'article est (Tshirt ou Hoodie) et
- La couleur est (rouge, bleu ou vert) et
- La ville est (Denver ou Seattle)
Plusieurs personnes ont également suggéré la même approche. J'aime cette structure car elle s'adapte facilement pour gérer plus de critères et fonctionne également avec des références de cellule (au lieu de valeurs codées en dur). Avec les références de cellule, la formule dans H9 est:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
La clé de cette formule est la construction ISNUMBER + MATCH. MATCH est configuré «à l'envers» - les valeurs de recherche proviennent des données et des critères sont utilisés pour le tableau. Le résultat est un tableau à colonne unique chaque fois que MATCH est utilisé. Ce tableau contient des erreurs # N / A (pas de correspondance) ou des nombres (correspondance), donc ISNUMBER est utilisé pour convertir les valeurs booléennes TRUE et FALSE. L'opération de multiplication des tableaux ensemble contraint les valeurs TRUE FALSE à 1 et 0, et le tableau final à l'intérieur de SUMPRODUCT contient 1 où les lignes répondent aux critères. SUMPRODUCT additionne ensuite le tableau et renvoie le résultat.
