Yhteenveto
Tietyissä tapauksissa voit käyttää SUMIFS-arvoa kuten hakukaavaa hakeaksesi numeerisen arvon. Esitetyssä esimerkissä G6: n kaava on:
=SUMIFS(sales,region,G4,quarter,G5)
missä alue (B5: B20), vuosineljännes (C5: C20) ja myynti (D5: D20) on nimetty alueiksi.
Tuloksena on kolmannen vuosineljänneksen myynti Keski-alueella, 127 250.
Selitys
Jos SUMIFS-toiminto on sinulle uusi, löydät täältä yleiskatsauksen ja monia esimerkkejä.
SUMIFS-toiminto on suunniteltu yhteenlaskemaan numeeriset arvot yhden tai useamman ehdon perusteella. Erityistapauksissa saatat kuitenkin pystyä käyttämään SUMIFS-funktiota "etsimään" vaaditut ehdot täyttävän numeerisen arvon. Tärkeimmät syyt tähän ovat yksinkertaisuus ja nopeus.
Esitetyssä esimerkissä meillä on neljän vuosineljänneksen myyntitiedot neljälle alueelle. Aloitetaan antamalla SUMIFS: lle summa-alue ja ensimmäinen ehto, joka testaa alueen arvon G4: ssä, "Keski":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Summa-alue on myynti (D5: D20)
- Kriteerialue 1 on alue (B5: B20)
- Kriteerit 1 on G4 ("Keski")
Sitten lisätään toinen alue / kriteeripari, joka tarkistaa vuosineljänneksen:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Kriteerialue 2 on neljännes (C5: C20)
- Kriteeri 2 on G5 ("Q3")
Näillä kriteereillä SUMIFS palauttaa 127 250, Central Q3 -myyntinumeron.
SUMIFS-toiminto on laskea yhteen kaikki vastaavat arvot. Koska vastaavia arvoja on kuitenkin vain yksi, tulos on sama kuin itse arvo.
Alla tarkastellaan useita hakukaavan vaihtoehtoja.
Hakukaavan asetukset
Tässä osassa tarkastellaan lyhyesti muita kaavan vaihtoehtoja, jotka tuottavat saman tuloksen. SUMPRODUCTia (alareunassa) lukuun ottamatta nämä ovat perinteisempiä hakukaavoja, jotka etsivät kohdearvon sijainnin ja palauttavat arvon kyseiseen sijaintiin.
Kanssa VLOOKUP
Valitettavasti VLOOKUP ei ole hyvä ratkaisu tähän ongelmaan. Apusarakkeella on mahdollista rakentaa VLOOKUP-kaava vastaamaan useita ehtoja (esimerkki tästä), mutta se on hankala prosessi, joka vaatii sinua korjaamaan lähdetietoja.
Indeksillä ja MATCH: lla
INDEX ja MATCH on erittäin joustava hakuyhdistelmä, jota voidaan käyttää kaikenlaisiin hakuongelmiin, eikä tämä esimerkki ole poikkeus. Indeksin ja MATCHin avulla voimme etsiä myyntiä alueittain ja vuosineljänneksittäin seuraavan taulukon kaavan avulla:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Huomaa: tämä on matriisikaava, ja se on syötettävä painamalla ohjainta + vaihto + enter.
Tämän lähestymistavan temppu on käyttää loogista logiikkaa matriisitoimintojen kanssa MATCH-toiminnon sisällä rakentamaan 1: n ja 0: n taulukko hakutaulukoksi. Sitten voimme pyytää MATCH-funktiota etsimään numeron 1. Kun hakutaulukko on luotu, kaava ratkaisee:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Kun vain yksi jäljellä on hakutaulukossa, MATCH palauttaa 11-sijainnin INDEX-funktiolle ja INDEX palauttaa myyntinumeron tässä paikassa 127 250.
Lisätietoja: Indeksi ja MATCH useilla ehdoilla
XLOOKUP: n kanssa
XLOOKUP on joustava uusi toiminto Excelissä, joka pystyy käsittelemään matriiseja natiivisti. XLOOKUP: n avulla voimme käyttää täsmälleen samaa lähestymistapaa kuin INDEX: n ja MATCH: n kanssa, käyttämällä loogista logiikkaa ja matriisitoimintoja luodaksesi hakutaulukon:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Kun matriisitoiminnot on suoritettu, kaava ratkaisee:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Ja XLOOKUP palauttaa saman tuloksen kuin yllä, 127 250.
Lisää: XLOOKUP useilla ehdoilla
LOOKUPin kanssa
LOOKUP-toiminto on Excelin vanhempi toiminto, josta monet ihmiset eivät edes tiedä. Yksi LOOKUPin tärkeimmistä vahvuuksista on, että se pystyy käsittelemään matriiseja natiivisti. LOOKUPilla on kuitenkin muutama selvä heikkous:
- Ei voida lukita tarkan haun tilassa
- Oletetaan aina, että hakutiedot on lajiteltu, AZ
- Palauttaa aina likimääräisen vastaavuuden (jos tarkkaa osumaa ei löydy)
Silti LOOKUPia voidaan käyttää ongelman ratkaisemiseen näin:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
mikä yksinkertaistaa:
=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
Jos tilanne ei täytä molempia vaatimuksia, SUMIFS ei ole hyvä valinta.
