Kokkuvõte
Teatud juhtudel saate arvväärtuse hankimiseks kasutada summe SUMIFS nagu otsimisvalemit. Näidatud näites on valem G6-s järgmine:
=SUMIFS(sales,region,G4,quarter,G5)
kus piirkonda (B5: B20), kvartalit (C5: C20) ja müüki (D5: D20) nimetatakse vahemikeks.
Tulemuseks on Kesk-piirkonna III kvartali müük 127 250.
Selgitus
Kui funktsioon SUMIFS on teie jaoks uus, leiate siit paljude ülevaadetega põhiülevaate.
Funktsioon SUMIFS on loodud arvväärtuste summeerimiseks ühe või mitme kriteeriumi alusel. Erijuhtudel võite siiski kasutada summat SUMIFS, et otsida nõutavatele kriteeriumidele vastavat arvväärtust. Peamised põhjused selleks on lihtsus ja kiirus.
Näidatud näites on meil kvartali müügiandmed nelja piirkonna kohta. Alustame SUMIFS-i summavahemiku ja esimese tingimusega, mis testib piirkonna väärtust G4-s, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Summa vahemik on müük (D5: D20)
- Kriteeriumivahemik 1 on piirkond (B5: B20)
- Kriteerium 1 on G4 ("keskne")
Seejärel lisame teise vahemiku / kriteeriumide paari, mis kontrollib kvartalit:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Kriteeriumivahemik 2 on kvartal (C5: C20)
- Kriteerium 2 on G5 ("Q3")
Nende kriteeriumidega tagastab SUMIFS 127 250, keskse Q3 müüginumbri.
SUMIFSi käitumine on kõigi sobitatavate väärtuste liitmine. Kuna aga on ainult üks sobituv väärtus, on tulemus sama mis väärtus ise.
Allpool vaatame mitut otsinguvalemi valikut.
Valemivalikute otsimine
Selles jaotises vaadeldakse lühidalt teisi valemivalikuid, mis annavad sama tulemuse. Välja arvatud SUMPRODUCT (allosas), on need traditsioonilisemad otsinguvalemid, mis määravad sihtväärtuse positsiooni ja tagastavad väärtuse selles kohas.
VLOOKUPiga
Kahjuks pole VLOOKUP sellele probleemile hea lahendus. Abistaja veeru abil on võimalik ehitada VLOOKUP-i valem, mis sobib mitme kriteeriumiga (näide siin), kuid see on ebamugav protsess, mis nõuab algandmetega nokitsemist.
Indeksiga ja MATCH
INDEX ja MATCH on väga paindlik otsingukombinatsioon, mida saab kasutada igasuguste otsinguprobleemide korral ja see näide pole erand. INDEXi ja MATCHi abil saame otsida müüki piirkonna ja kvartali kaupa järgmise massiivivalemiga:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
Selle lähenemisviisi nipp on Boolean loogika kasutamine massiivioperatsioonidega funktsiooni MATCH sees, et ehitada massiivi 1s ja 0s otsingumassiivina. Seejärel võime paluda funktsioonil MATCH leida numbri 1. Kui otsimassiiv on loodud, lahendab valem järgmise:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Kui otsingumassiivi jääb alles 1, tagastab MATCH funktsiooni INDEX positsiooni 11 ja INDEX tagastab sellel positsioonil oleva müüginumbri 127 250.
Lisateavet vt: INDEX ja MATCH mitme kriteeriumiga
XLOOKUPiga
XLOOKUP on paindlik uus funktsioon Excelis, mis saab massiive naturaalselt käsitleda. Rakendusega XLOOKUP saame otsingu massiivi loomiseks kasutada täpselt sama lähenemisviisi nagu INDEXi ja MATCHi puhul, kasutades boolean loogikat ja massiivioperatsioone:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Kui massiivioperatsioonid on käivitatud, lahendatakse valem järgmiselt:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Ja XLOOKUP tagastab sama tulemuse nagu ülalpool, 127 250.
Veel: XLOOKUP mitme kriteeriumiga
LOOKUPiga
Funktsioon LOOKUP on Exceli vanem funktsioon, millest paljud inimesed isegi ei tea. LOOKUPi üks põhitugevusi on see, et see suudab massiive looduslikult käsitleda. LOOKUPil on siiski mõned erinevad nõrkused:
- Ei saa lukustada täpse vaste režiimis
- Eeldab alati, et otsinguandmed on sorteeritud, AZ
- Tagastab alati ligikaudse vaste (kui täpset vastet ei leita)
Sellest hoolimata saab selle probleemi kenasti lahendamiseks kasutada LOOKUP-i:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
mis lihtsustab:
=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
Kui olukord ei vasta mõlemale nõudele, pole SUMIFS hea valik.
