Podsumowanie
W niektórych przypadkach można użyć funkcji SUMA.WARUNKÓW jak formuły wyszukiwania, aby pobrać wartość liczbową. W przedstawionym przykładzie formuła w G6 to:
=SUMIFS(sales,region,G4,quarter,G5)
gdzie region (B5: B20), kwartał (C5: C20) i sprzedaż (D5: D20) to nazwane zakresy.
Rezultatem jest sprzedaż w III kwartale w regionie centralnym, 127 250.
Wyjaśnienie
Jeśli nie znasz jeszcze funkcji SUMA.WARUNKÓW, możesz znaleźć tutaj podstawowy przegląd z wieloma przykładami.
Funkcja SUMA.WARUNKÓW służy do sumowania wartości liczbowych na podstawie co najmniej jednego kryterium. Jednak w określonych przypadkach można użyć funkcji SUMA.WARUNKÓW do „wyszukania” wartości liczbowej spełniającej wymagane kryteria. Głównymi powodami, dla których warto to zrobić, są prostota i szybkość.
W przedstawionym przykładzie mamy kwartalne dane sprzedaży dla czterech regionów. Zaczynamy od podania SUMIFS zakresu sumy i pierwszego warunku, który testuje region pod kątem wartości w G4, „Central”:
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Zakres sumy to sprzedaż (D5: D20)
- Zakres kryteriów 1 to region (B5: B20)
- Kryterium 1 to G4 („Centralne”)
Następnie dodajemy drugą parę zakres / kryteria, która sprawdza kwartał:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Zakres kryteriów 2 to ćwiartka (C5: C20)
- Kryterium 2 to G5 („Q3”)
Przy tych kryteriach funkcja SUMA.WARUNKÓW zwraca 127 250, czyli centralny numer sprzedaży w trzecim kwartale.
Zachowanie funkcji SUMA.WARUNKÓW polega na zsumowaniu wszystkich pasujących wartości. Jednak ponieważ istnieje tylko jedna pasująca wartość, wynik jest taki sam, jak sama wartość.
Poniżej przyjrzymy się kilku opcjom formuły wyszukiwania.
Wyszukaj opcje formuły
W tej sekcji omówiono pokrótce inne opcje formuły, które dają ten sam wynik. Z wyjątkiem SUMPRODUCT (na dole), są to bardziej tradycyjne formuły wyszukiwania, które lokalizują pozycję wartości docelowej i zwracają wartość w tej lokalizacji.
Z WYSZUKAJ.PIONOWO
Niestety funkcja WYSZUKAJ.PIONOWO nie jest dobrym rozwiązaniem tego problemu. Dzięki kolumnie pomocniczej można zbudować formułę WYSZUKAJ.PIONOWO w celu dopasowania do wielu kryteriów (przykład tutaj), ale jest to niezręczny proces, który wymaga majstrowania przy danych źródłowych.
Z INDEX i MATCH
INDEKS i PODAJ.POZYCJĘ to bardzo elastyczna kombinacja wyszukiwania, której można używać do wszelkiego rodzaju problemów z wyszukiwaniem, a ten przykład nie jest wyjątkiem. Dzięki INDEX i MATCH możemy wyszukiwać sprzedaż według regionu i kwartału za pomocą formuły tablicowej takiej jak ta:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Sztuczka w tym podejściu polega na użyciu logiki boolowskiej z operacjami tablicowymi wewnątrz funkcji MATCH w celu zbudowania tablicy jedynek i zer jako tablicy odnośników. Następnie możemy poprosić funkcję PODAJ.POZYCJĘ o znalezienie liczby 1. Po utworzeniu tablicy przeglądowej wzór wygląda następująco:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Gdy w tablicy wyszukiwania pozostała tylko 1, funkcja PODAJ.POZYCJĘ zwraca pozycję 11 do funkcji INDEKS, a funkcja INDEKS zwraca liczbę sprzedaży na tej pozycji, czyli 127 250.
Aby uzyskać więcej informacji, zobacz: INDEKS i PODAJANIE z wieloma kryteriami
Dzięki XLOOKUP
XLOOKUP to nowa elastyczna funkcja programu Excel, która natywnie obsługuje tablice. W XLOOKUP możemy użyć dokładnie tego samego podejścia, co w przypadku INDEX i MATCH, używając logiki boolowskiej i operacji tablicowych do utworzenia tablicy odnośników:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Po wykonaniu operacji tablicowych formuła przyjmuje postać:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
XLOOKUP zwraca ten sam wynik, co powyżej, 127,250.
Więcej: XLOOKUP z wieloma kryteriami
Dzięki funkcji LOOKUP
Funkcja WYSZUKAJ to starsza funkcja programu Excel, o której wiele osób nawet nie wie. Jedną z głównych zalet LOOKUP jest to, że może natywnie obsługiwać tablice. Jednak LOOKUP ma kilka wyraźnych słabości:
- Nie można zablokować w „trybie dopasowania ścisłego”
- Zawsze zakłada, że dane wyszukiwania są posortowane, AZ
- Zawsze zwraca przybliżone dopasowanie (jeśli nie można znaleźć dokładnego dopasowania)
Niemniej jednak LOOKUP może być użyty do ładnego rozwiązania tego problemu w następujący sposób:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
co upraszcza:
=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
Jeśli sytuacja nie spełnia obu wymagań, SUMIFS nie jest dobrym wyborem.
