rezumat
În anumite cazuri, puteți utiliza SUMIFS ca o formulă de căutare pentru a extrage o valoare numerică. În exemplul prezentat, formula din G6 este:
=SUMIFS(sales,region,G4,quarter,G5)
unde regiunea (B5: B20), trimestrul (C5: C20) și vânzările (D5: D20) sunt denumite intervale.
Rezultatul este vânzările din T3 pentru regiunea Centrală, 127.250.
Explicaţie
Dacă sunteți nou la funcția SUMIFS, puteți găsi o prezentare generală de bază cu multe exemple aici.
Funcția SUMIFS este concepută pentru a suma valorilor numerice pe baza unuia sau mai multor criterii. Cu toate acestea, în cazuri specifice, puteți utiliza SUMIFS pentru a „căuta” o valoare numerică care îndeplinește criteriile cerute. Principalele motive pentru a face acest lucru sunt simplitatea și rapiditatea.
În exemplul prezentat, avem date trimestriale privind vânzările pentru patru regiuni. Începem prin a da SUMIFS un interval de sumă și prima condiție, care testează regiunea pentru valoarea din G4, „Central”:
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Gama sumă este vânzări (D5: D20)
- Intervalul de criterii 1 este regiunea (B5: B20)
- Criteriul 1 este G4 („Central”)
Apoi adăugăm a doua pereche interval / criterii, care verifică trimestrul:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Intervalul de criterii 2 este sfert (C5: C20)
- Criteriul 2 este G5 („Q3”)
Cu aceste criterii, SUMIFS returnează 127.250, numărul de vânzări central Q3.
Comportamentul SUMIFS este de a însuma toate valorile potrivite. Cu toate acestea, deoarece există o singură valoare potrivită, rezultatul este același cu valoarea în sine.
Mai jos, ne uităm la mai multe opțiuni de formulă de căutare.
Căutați opțiuni de formulă
Această secțiune trece în revistă pe scurt alte opțiuni de formulă care dau același rezultat. Cu excepția SUMPRODUCT (în partea de jos), acestea sunt formule de căutare mai tradiționale care localizează poziția valorii țintă și returnează valoarea în acea locație.
Cu VLOOKUP
Din păcate, VLOOKUP nu este o soluție bună la această problemă. Cu o coloană de ajutor, este posibil să creați o formulă VLOOKUP pentru a se potrivi cu mai multe criterii (exemplu aici), dar este un proces incomod care necesită să vă amestecați cu datele sursă.
Cu INDEX și MATCH
INDEX și MATCH este o combinație de căutare foarte flexibilă care poate fi utilizată pentru tot felul de probleme de căutare, iar acest exemplu nu face excepție. Cu INDEX și MATCH, putem căuta vânzările în funcție de regiune și trimestru cu o formulă matriceală ca aceasta:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.
Trucul cu această abordare este de a utiliza logica booleană cu operații de matrice în interiorul funcției MATCH pentru a construi o matrice de 1 și 0 ca matrice de căutare. Apoi putem cere funcției MATCH să găsească numărul 1. Odată creată matricea de căutare, formula se rezolvă la:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Cu doar 1 rămas în matricea de căutare, MATCH returnează o poziție de 11 la funcția INDEX, iar INDEX returnează numărul de vânzări la acea poziție, 127.250.
Pentru mai multe detalii, consultați: INDEX și MATCH cu mai multe criterii
Cu XLOOKUP
XLOOKUP este o nouă funcție flexibilă în Excel care poate gestiona matrici în mod nativ. Cu XLOOKUP, putem folosi exact aceeași abordare ca la INDEX și MATCH, folosind operații logice booleene și matrice pentru a crea o matrice de căutare:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Odată ce operațiile matrice s-au executat, formula se rezolvă la:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Și XLOOKUP returnează același rezultat ca mai sus, 127.250.
Mai multe: XLOOKUP cu mai multe criterii
Cu CĂUTARE
Funcția CĂUTARE este o funcție mai veche din Excel pe care mulți oameni nici măcar nu o cunosc. Unul dintre punctele forte ale LOOKUP este că poate gestiona matrice în mod nativ. Cu toate acestea, LOOKUP are câteva puncte slabe distincte:
- Nu poate fi blocat în „modul de potrivire exactă”
- Presupune întotdeauna că datele de căutare sunt sortate, AZ
- Returnează întotdeauna o potrivire aproximativă (dacă nu se găsește potrivirea exactă)
Cu toate acestea, LOOKUP poate fi folosit pentru a rezolva această problemă frumos astfel:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
ceea ce simplifică:
=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
Dacă situația nu îndeplinește ambele cerințe, SUMIFS nu este o alegere bună.
