Jednym z problemów, który często pojawia się w programie Excel, jest liczenie lub sumowanie na podstawie wielu warunków LUB. Na przykład, być może musisz przeanalizować dane i policzyć zamówienia w Seattle lub Denver dla towarów, które są czerwone, niebieskie lub zielone? Może to być zaskakująco trudne, więc naturalnie jest to niezłe wyzwanie!
Wyzwanie
Poniższe dane przedstawiają zamówienia, jedno zamówienie na wiersz. Istnieją trzy oddzielne wyzwania.
Jakie formuły w F9, G9 i H9 będą poprawnie liczyć zamówienia z następującymi warunkami:
- F9 - koszulka lub bluza z kapturem
- G9 - (koszulka lub bluza z kapturem) i (czerwony, niebieski lub zielony)
- H9 - (koszulka lub bluza z kapturem) i (czerwony, niebieski lub zielony) i (Denver lub Seattle)
Zielone cieniowanie jest stosowane z formatowaniem warunkowym i wskazuje pasujące wartości dla każdego zestawu kryteriów LUB w każdej kolumnie.
Dla Twojej wygody dostępne są następujące nazwane zakresy:
pozycja = B3: B16
kolor = C3: C16
miasto = D3: D16
Arkusz roboczy jest dołączony. Zostaw swoje odpowiedzi poniżej jako komentarze!
Odpowiedź (kliknij, aby rozwinąć)Moje rozwiązanie używa SUMPRODUCT z ISNUMBER i MATCH w ten sposób:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Która będzie liczyć zamówienia, w których…
- Przedmiot to (koszulka lub bluza z kapturem) i
- Kolor to (czerwony, niebieski lub zielony) i
- Miasto to (Denver lub Seattle)
Kilka osób również zasugerowało to samo podejście. Podoba mi się ta struktura, ponieważ łatwo się skaluje, aby obsługiwać więcej kryteriów, a także działa z odwołaniami do komórek (zamiast wartości zakodowanych na stałe). W przypadku odwołań do komórek formuła w H9 to:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Kluczem do tej formuły jest konstrukcja ISNUMBER + MATCH. PODAJANIE jest ustawiane „wstecz” - wartości wyszukiwania pochodzą z danych, a dla tablicy używane są kryteria. Wynikiem jest tablica z pojedynczą kolumną za każdym razem, gdy używana jest funkcja MATCH. Ta tablica zawiera błędy # N / A (brak dopasowania) lub liczby (dopasowanie), więc ISNUMBER jest używany do konwersji na wartości logiczne PRAWDA i FAŁSZ. Operacja mnożenia tablic razem przekształca wartości TRUE FALSE na 1 i 0, a końcowa tablica wewnątrz SUMPRODUCT zawiera jedynki, gdzie wiersze spełniają kryteria. SUMPRODUCT następnie sumuje tablicę i zwraca wynik.
