WYSZUKAJ.PIONOWO w dwóch lub więcej kolumnach kryteriów – Uniwersytet Excel
Jeśli kiedykolwiek próbowałeś użyć funkcji WYSZUKAJ.PIONOWO z co najmniej dwoma kolumnami kryteriów, szybko odkryłeś, że po prostu nie został zbudowany w tym celu. Na szczęście istnieje inna funkcja, która może działać jako alternatywa dla WYSZUKAJ.PIONOWO w zależności od tego, co chcesz zwrócić.
Cel wyszukiwania w wielu kolumnach
Najpierw potwierdźmy nasz cel, patrząc na przykładowy skoroszyt. Wyeksportowaliśmy niektóre informacje z naszego systemu księgowego, które w zasadzie podsumowują sumy transakcji z miesiąca według klasy i konta. Przykład eksportu pokazano poniżej:
Na podstawie wyeksportowanych danych chcielibyśmy pobrać wybrane kwoty na podstawie kolumny klasy i konta. Chcemy pobrać kwoty i umieścić je w naszym małym raporcie przedstawionym poniżej:
Jeśli znasz funkcję WYSZUKAJ.PIONOWO funkcja, wydaje się naturalne, aby spróbować zbudować raport za pomocą tej funkcji, ponieważ jest to w końcu zadanie wyszukiwania. A zadania wyszukiwania najlepiej rozwiązywać za pomocą tradycyjnych funkcji wyszukiwania… prawda? Cóż, to zależy. To zależy od tego, co próbujesz odzyskać.
Sumowanie warunkowe dla wyszukiwań
Jeśli próbujesz pobrać wartość liczbową, taką jak kwota, wówczas tradycyjna funkcja wyszukiwania może nie bądź najlepszym rozwiązaniem. Dlatego. Począwszy od programu Excel 2007 firma Microsoft wprowadziła funkcję sumowania warunkowego SUMA.WARUNKÓW. Ta funkcja sumowania wielu warunków została zaprojektowana w celu dodania kolumny liczb i uwzględnienia tylko wierszy, które spełniają jeden lub więcej warunków. Czy kropki zaczynają się już łączyć?
Jeśli zastosujemy ten pomysł do naszego zadania, szybko zorientujemy się, że moglibyśmy użyć tej funkcji sumowania warunkowego do pobrania wartości naszego raportu.
Pierwszym argumentem funkcji SUMA.WARUNKÓW jest zakres sumy, czyli kolumna liczb do dodania. W naszym przypadku kolumna zawierająca wartość, którą chcemy zwrócić. Pozostałe argumenty występują w parach: zakres kryteriów i wartość kryterium.
Warto pomyśleć o funkcji w następujący sposób: dodaj tę kolumnę (argument 1), uwzględnij tylko te wiersze, w których ta kolumna (argument 2) jest równy tej wartości (argument 3) i gdzie ta kolumna (argument 4) jest równa tej wartości (argument 5) i gdzie… i tak dalej, aż do 127 par.
W związku z tym, aby wypełnić nasz raport, pobierzemy wartości kwot z eksportu i dopasujemy kolumny klasy i konta, jak pokazano poniżej.
Jeśli zdarzy się, że jest wiele wierszy z tą samą klasą i kontami, funkcja SUMA.WARUNKÓW zwróci sumę wszystkich pasujących elementów.
Jak widać, jeśli wartość które próbują zwrócić jest liczbą, wówczas funkcja SUMA.WARUNKÓW upraszcza wyszukiwanie w wielu kolumnach. Ale co, jeśli wartość, którą próbujesz zwrócić, nie jest liczbą? W takim razie musisz użyć tradycyjnej funkcji wyszukiwania, jak omówiono poniżej.
Korzystanie z funkcji WYSZUKAJ.PIONOWO z metodą SUMA.WARUNKÓW
Jedną z metod jest użycie WYSZUKAJ.PIONOWO i SUMA.WARUNKÓW w jednej formule. Zasadniczo używasz SUMA.WARUNKÓW jako pierwszego argumentu funkcji WYSZUKAJ.PIONOWO. Ta metoda jest szczegółowo opisana w tym poście na Uniwersytecie w programie Excel:
Korzystanie z funkcji WYSZUKAJ.PIONOWO z metodą ZŁĄCZ.TEKSTU
Jeśli próbujesz zwrócić ciąg tekstowy zamiast liczby lub używasz wersji Excel, który nie ma funkcji SUMIFS, prawdopodobnie utkniesz przy użyciu tradycyjnej funkcji wyszukiwania, takiej jak WYSZUKAJ.PIONOWO wraz z funkcją ZŁĄCZ.TEKST, aby wygenerować jedną unikalną kolumnę wyszukiwania. To podejście jest dość dobrze udokumentowane, ale podstawowa idea wygląda następująco: najpierw utwórz pojedynczą kolumnę wyszukiwania, a następnie użyj funkcji WYSZUKAJ.PIONOWO.
Nasz przykład będzie listą pracowników, jak pokazano poniżej:
Musimy pobrać stan z listy pracowników do naszego małego raportu pokazanego poniżej:
Ponieważ wartość, którą próbujemy zwrócić, stan jest ciągiem tekstowym, a nie liczbą, nie możemy używać funkcji SUMA.WARUNKÓW. Dlatego będziemy musieli przejść do starej szkoły z WYSZUKAJ.PIONOWO i ZŁĄCZ.ZŁĄCZ.
Zaczynamy od zbudowania kolumny pomocniczej, która zasadniczo tworzy połączone wartości wyszukiwania. Można to łatwo osiągnąć za pomocą funkcji CONCATENATE lub operatora konkatenacji (&). Ta nowa kolumna wyszukiwania jest przedstawiona w kolumnie B poniżej:
Teraz mamy jedną kolumnę wyszukiwania, której można użyć z tradycyjna funkcja wyszukiwania, taka jak WYSZUKAJ.PIONOWO. Raport można wypełnić, wyszukując połączone nazwy w nowym zakresie wyszukiwania, jak pokazano poniżej:
To samo podejście można użyć, gdy trzeba wziąć pod uwagę dwie, trzy lub więcej kolumn odnośników.
Wniosek
Oprócz możliwości wykonywania wyszukiwań w wielu kolumnach, gdy zwracana wartość jest liczbowa, funkcja SUMA.WARUNKÓW ma dodatkowe zalety w porównaniu z tradycyjnymi funkcjami wyszukiwania. Na przykład zwraca zero, gdy nie zostanie znaleziona pasująca wartość, zwraca sumę wszystkich dopasowań, obsługuje operatory porównania i nie zepsuje się, gdy nowa kolumna zostanie wstawiona między kolumną wyszukiwania i zwracania.
Tak więc, gdy masz zamiar wyłączyć funkcję WYSZUKAJ.PIONOWO, aby wykonać zadanie wyszukiwania, rozważ zamiast tego użycie funkcji SUMA.WARUNKÓW. Wierz lub nie, ale funkcja SUMA.WARUNKÓW jest wspaniałą funkcją wyszukiwania.
Jeśli masz inne preferowane podejście do wyszukiwania wielokolumnowego, chcielibyśmy usłyszeć więcej… zamieść komentarz poniżej.
Przykładowy plik
Jeśli chcesz pobawić się skoroszytem użytym do wygenerowania powyższych zrzutów ekranu, możesz bezpłatnie pobrać przykładowy plik:
MultColumnLookup