Sommaire
Dans certains cas, vous pouvez utiliser SUMIFS comme une formule de recherche pour récupérer une valeur numérique. Dans l'exemple illustré, la formule en G6 est:
=SUMIFS(sales,region,G4,quarter,G5)
où la région (B5: B20), le trimestre (C5: C20) et les ventes (D5: D20) sont des plages nommées.
Le résultat est un chiffre d'affaires de T3 pour la région Centre, 127 250.
Explication
Si vous êtes nouveau dans la fonction SUMIFS, vous trouverez ici un aperçu de base avec de nombreux exemples.
La fonction SUMIFS est conçue pour additionner des valeurs numériques basées sur un ou plusieurs critères. Dans des cas spécifiques cependant, vous pourrez peut-être utiliser SUMIFS pour «rechercher» une valeur numérique qui répond aux critères requis. Les principales raisons de le faire sont la simplicité et la rapidité.
Dans l'exemple illustré, nous avons des données de ventes trimestrielles pour quatre régions. Nous commençons par donner à SUMIFS une plage de somme, et la première condition, qui teste la région pour la valeur dans G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- La plage de somme correspond aux ventes (D5: D20)
- La plage de critères 1 est la région (B5: B20)
- Le critère 1 est G4 ("Central")
Nous ajoutons ensuite la deuxième paire plage / critères, qui vérifie le trimestre:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- La plage de critères 2 est le quart (C5: C20)
- Le critère 2 est G5 ("Q3")
Avec ces critères, SUMIFS renvoie 127 250, le numéro de vente Central Q3.
Le comportement de SUMIFS consiste à additionner toutes les valeurs correspondantes. Cependant, comme il n'y a qu'une seule valeur correspondante, le résultat est le même que la valeur elle-même.
Ci-dessous, nous examinons plusieurs options de formule de recherche.
Options de formule de recherche
Cette section passe brièvement en revue d'autres options de formule qui donnent le même résultat. À l'exception de SUMPRODUCT (en bas), il s'agit de formules de recherche plus traditionnelles qui localisent la position de la valeur cible et renvoient la valeur à cet emplacement.
Avec RECHERCHEV
Malheureusement, RECHERCHEV n'est pas une bonne solution à ce problème. Avec une colonne d'aide, il est possible de créer une formule RECHERCHEV pour correspondre à plusieurs critères (exemple ici), mais c'est un processus délicat qui vous oblige à bricoler les données source.
Avec INDEX et MATCH
INDEX et MATCH est une combinaison de recherche très flexible qui peut être utilisée pour toutes sortes de problèmes de recherche, et cet exemple ne fait pas exception. Avec INDEX et MATCH, nous pouvons rechercher les ventes par région et par trimestre avec une formule matricielle comme celle-ci:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.
L'astuce avec cette approche consiste à utiliser la logique booléenne avec des opérations de tableau dans la fonction MATCH pour créer un tableau de 1 et de 0 comme tableau de recherche. Ensuite, nous pouvons demander à la fonction MATCH de trouver le numéro 1. Une fois le tableau de recherche créé, la formule se résout en:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Avec seulement 1 restant dans le tableau de recherche, MATCH renvoie une position de 11 à la fonction INDEX et INDEX renvoie le numéro de vente à cette position, 127 250.
Pour plus de détails, voir: INDEX et MATCH avec plusieurs critères
Avec XLOOKUP
XLOOKUP est une nouvelle fonction flexible d'Excel qui peut gérer les tableaux de manière native. Avec XLOOKUP, nous pouvons utiliser exactement la même approche qu'avec INDEX et MATCH, en utilisant la logique booléenne et les opérations de tableau pour créer un tableau de recherche:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Une fois les opérations de tableau exécutées, la formule se résout en:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Et XLOOKUP renvoie le même résultat que ci-dessus, 127 250.
Plus: XLOOKUP avec plusieurs critères
Avec LOOKUP
La fonction LOOKUP est une fonction plus ancienne d'Excel que beaucoup de gens ne connaissent même pas. L'une des principales forces de LOOKUP est qu'il peut gérer les tableaux de manière native. Cependant, LOOKUP présente quelques faiblesses distinctes:
- Ne peut pas être verrouillé en "mode de correspondance exacte"
- Suppose toujours que les données de recherche sont triées, AZ
- Renvoie toujours une correspondance approximative (si la correspondance exacte ne peut être trouvée)
Néanmoins, LOOKUP peut être utilisé pour résoudre ce problème de la manière suivante:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
ce qui se simplifie en:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
Si la situation ne répond pas aux deux exigences, SUMIFS n'est pas un bon choix.
