Összegzés
Bizonyos esetekben a SUMIFS-t, mint egy keresési képletet, használhatja numerikus érték lekéréséhez. A bemutatott példában a G6 képlete a következő:
=SUMIFS(sales,region,G4,quarter,G5)
ahol a régió (B5: B20), a negyedév (C5: C20) és az értékesítés (D5: D20) tartományokat nevez meg.
Ennek eredményeként a központi régió harmadik negyedéves értékesítése 127 250.
Magyarázat
Ha még nem ismeri a SUMIFS funkciót, itt megtalálhatja az alapvető áttekintést számos példával.
A SUMIFS függvény úgy van kialakítva, hogy egy vagy több kritérium alapján összegezze a numerikus értékeket. Bizonyos esetekben előfordulhat, hogy a SUMIFS segítségével megkeresheti az előírt feltételeknek megfelelő numerikus értéket. Ennek fő okai az egyszerűség és a gyorsaság.
A bemutatott példában négy régióra vonatkozó negyedéves értékesítési adatokkal rendelkezünk. Kezdjük azzal, hogy megadunk egy összegtartományt a SUMIFS-nak, és az első feltételt, amely a régiót teszteli a G4-ben, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Az összegtartomány az értékesítés (D5: D20)
- Az 1. kritériumtartomány a régió (B5: B20)
- Az 1. kritérium G4 ("Központi")
Ezután hozzáadjuk a második tartomány / kritérium párost, amely ellenőrzi a negyedévet:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- A 2. kritériumtartomány negyed (C5: C20)
- A 2. kritérium G5 ("Q3")
Ezekkel a feltételekkel a SUMIFS 127.250-et ad vissza, a Central Q3 értékesítési számot.
A SUMIFS viselkedése az összes egyező érték összegzése. Mivel azonban csak egyező érték van, az eredmény megegyezik magával az értékkel.
Az alábbiakban több keresési képlet-lehetőséget vizsgálunk meg.
Képletopciók keresése
Ez a szakasz röviden áttekinti azokat az egyéb képletopciókat, amelyek ugyanazt az eredményt adják. A SUMPRODUCT kivételével (alul) ezek inkább hagyományos keresési képletek, amelyek megkeresik a célérték helyzetét, és az adott helyen adják vissza az értéket.
A VLOOKUP segítségével
Sajnos a VLOOKUP nem jó megoldás erre a problémára. Segítő oszlop segítségével lehetséges egy VLOOKUP képlet felépítése, amely megfelel több feltételnek (itt példa), de ez egy kellemetlen folyamat, amely megköveteli, hogy ügyeljen a forrás adatokra.
INDEX-sel és MATCH-szal
Az INDEX és a MATCH egy nagyon rugalmas keresési kombináció, amely mindenféle keresési problémára használható, és ez a példa sem kivétel. Az INDEX és a MATCH segítségével régiónként és negyedévenként kereshetünk eladásokat egy ilyen tömbképlettel:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Megjegyzés: ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
Ennek a megközelítésnek az a trükkje, hogy a logikai logikát tömbműveletekkel kell használni a MATCH függvényen belül, hogy 1 és 0 tömböt építsen fel keresési tömbként. Ezután megkérdezhetjük a MATCH függvényt, hogy keresse meg az 1-es számot. A keresési tömb létrehozása után a képlet a következőre oldódik fel:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Ha csak 1 van a keresési tömbben, a MATCH 11-es pozíciót ad vissza az INDEX függvénynek, az INDEX pedig visszaadja az adott pozícióban lévő értékesítési számot, 127 250.
További részletek: INDEX és MATCH több kritériummal
XLOOKUP-tal
Az XLOOKUP egy rugalmas új funkció az Excelben, amely natívan képes kezelni a tömböket. Az XLOOKUP használatával pontosan ugyanazt a megközelítést alkalmazhatjuk, mint az INDEX és a MATCH esetében, logikai és tömbműveletekkel létrehozva egy keresési tömböt:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
A tömb műveletek futtatása után a képlet feloldódik:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Az XLOOKUP pedig ugyanazt az eredményt adja vissza, mint fent, 127 250.
Több: XLOOKUP több kritériummal
A KERESÉSEL
A LOOKUP funkció az Excel egy régebbi funkciója, amelyről sokan nem is tudnak. A LOOKUP egyik legfontosabb erőssége, hogy natívan képes kezelni a tömböket. A LOOKUP-nak azonban van néhány különös gyengesége:
- Nem zárható "pontos egyezési módban"
- Mindig feltételezi, hogy a keresési adatok rendezve vannak, AZ
- Mindig egy hozzávetőleges találatot ad vissza (ha a pontos egyezés nem található)
Mindazonáltal a LOOKUP használható a probléma ilyen szép megoldására:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
ami leegyszerűsíti:
=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
Ha a helyzet nem felel meg mindkét követelménynek, a SUMIFS nem jó választás.
