Kopsavilkums
Dažos gadījumos skaitliskās vērtības iegūšanai varat izmantot SUMIFS, piemēram, uzmeklēšanas formulu. Parādītajā piemērā formula G6 ir:
=SUMIFS(sales,region,G4,quarter,G5)
kur reģioni (B5: B20), ceturksnis (C5: C20) un pārdošanas apjomi (D5: D20) ir nosaukti diapazoni.
Rezultāts ir Centrālā reģiona 3. ceturkšņa pārdošanas apjoms, 127 250.
Paskaidrojums
Ja SUMIFS funkcija jums ir jauna, šeit varat atrast pamata pārskatu ar daudziem piemēriem.
Funkcija SUMIFS ir paredzēta skaitlisko vērtību apkopošanai, pamatojoties uz vienu vai vairākiem kritērijiem. Īpašos gadījumos jūs, iespējams, varēsit izmantot SUMIFS, lai "meklētu" skaitlisko vērtību, kas atbilst nepieciešamajiem kritērijiem. Galvenie iemesli to darīt ir vienkāršība un ātrums.
Parādītajā piemērā mums ir ceturkšņa pārdošanas dati par četriem reģioniem. Mēs sākam, norādot summas diapazonu SUMIFS un pirmo nosacījumu, kas pārbauda reģiona vērtību G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Summas diapazons ir pārdošana (D5: D20)
- 1. kritērija diapazons ir reģions (B5: B20)
- 1. kritērijs ir G4 ("Centrālais")
Pēc tam mēs pievienojam otro diapazonu / kritēriju pāri, kas pārbauda ceturksni:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- 2. kritērija diapazons ir ceturksnis (C5: C20)
- 2. kritērijs ir G5 ("Q3")
Saskaņā ar šiem kritērijiem SUMIFS atgriež 127 250, Centrālā Q3 pārdošanas numuru.
SUMIFS rīcība ir visu atbilstošo vērtību summēšana. Tomēr, tā kā ir tikai viena atbilstoša vērtība, rezultāts ir tāds pats kā pati vērtība.
Zemāk mēs aplūkojam vairākas uzmeklēšanas formulas iespējas.
Uzmeklēšanas formulas opcijas
Šajā sadaļā īsi apskatītas citas formulu opcijas, kas dod tādu pašu rezultātu. Izņemot SUMPRODUCT (apakšā), šīs ir tradicionālākas uzmeklēšanas formulas, kas atrod mērķa vērtības pozīciju un atgriež vērtību šajā vietā.
Ar VLOOKUP
Diemžēl VLOOKUP nav labs risinājums šai problēmai. Izmantojot palīgkolonnu, ir iespējams izveidot formulu VLOOKUP, kas atbilst vairākiem kritērijiem (piemērs šeit), taču tas ir neērts process, kas prasa izdomāt avota datus.
Ar INDEX un MATCH
INDEX un MATCH ir ļoti elastīga uzmeklēšanas kombinācija, ko var izmantot visu veidu uzmeklēšanas problēmām, un šis piemērs nav izņēmums. Izmantojot INDEX un MATCH, mēs varam meklēt pārdošanu pēc reģiona un ceturkšņa ar šādu masīva formulu:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter.
Šīs pieejas triks ir Būla loģikas izmantošana ar masīva operācijām MATCH funkcijā, lai izveidotu masīvu 1s un 0s kā uzmeklēšanas masīvu. Tad mēs varam lūgt funkciju MATCH atrast skaitli 1. Kad uzmeklēšanas masīvs ir izveidots, formula izšķiras šādi:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Paliekot tikai 1 uzmeklēšanas masīvā, MATCH funkcijai INDEX atgriež pozīciju 11, un INDEX šajā pozīcijā atgriež pārdošanas numuru 127 250.
Lai iegūtu sīkāku informāciju, skatiet: INDEX un MATCH ar vairākiem kritērijiem
Ar XLOOKUP
XLOOKUP ir jauna elastīga programma Excel, kas natīvi var apstrādāt masīvus. Izmantojot XLOOKUP, mēs varam izmantot tieši tādu pašu pieeju kā ar INDEX un MATCH, izmantojot būla loģiku un masīva darbības, lai izveidotu uzmeklēšanas masīvu:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Kad masīva darbības ir izpildītas, formula tiek atrisināta šādi:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Un XLOOKUP atgriež to pašu rezultātu kā iepriekš, 127 250.
Vairāk: XLOOKUP ar vairākiem kritērijiem
Ar MEKLĒŠANU
Funkcija LOOKUP ir vecāka Excel programma, par kuru daudzi cilvēki pat nezina. Viena no galvenajām LOOKUP priekšrocībām ir tā, ka tā var dabiski apstrādāt masīvus. Tomēr LOOKUP ir dažas atšķirīgas vājās vietas:
- Nevar bloķēt precīzās atbilstības režīmā
- Vienmēr pieņem, ka uzmeklēšanas dati ir sakārtoti, AZ
- Vienmēr atgriež aptuveno atbilstību (ja precīzu atbilstību nevar atrast)
Neskatoties uz to, LOOKUP var izmantot, lai labi atrisinātu šo problēmu šādi:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
kas vienkāršo:
=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
Ja situācija neatbilst abām prasībām, SUMIFS nav laba izvēle.
