WYSZUKAJ.PIONOWO to jedna z najczęściej używanych funkcji w programie Excel. Szuka wartości w zakresie i zwraca odpowiednią wartość w kolumnie o określonym numerze.
Teraz natknąłem się na problem, w którym musiałem wyszukać cały wiersz i zwrócić wartości we wszystkich kolumnach z tego wiersza (zamiast zwracać pojedynczą wartość).
Oto, co musiałem zrobić. W poniższym zestawie danych mam nazwiska przedstawicieli handlowych i sprzedaż, którą zrealizowali w 4 kwartałach 2012 r. Miałem listę rozwijaną z ich nazwiskami i chciałem wyodrębnić maksymalną sprzedaż dla tego przedstawiciela handlowego w tych czterech kwartałach.
Mogę wymyślić 2 różne sposoby, aby to zrobić - za pomocą INDEKSU lub WYSZUKAJ.PIONOWO.
Wyszukaj cały wiersz/kolumnę za pomocą formuły INDEKS
Oto formuła, którą stworzyłem, aby to zrobić za pomocą Index
=DUŻY(INDEKS($B$4:$F$13;DOPASUJ(H3;$B$4:$B$13,0);0);1)
Jak to działa:
Najpierw spójrzmy na funkcję INDEX, która jest opakowana w funkcję LARGE.
=INDEKS($C$4:$F$13;DOPASUJ(H3;$B$4:$B$13,0);0)
Przeanalizujmy dokładnie argumenty funkcji INDEX:
- Tablica - $B$4:$F$1
- Numer wiersza - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Numer kolumny - 0
Zauważ, że użyłem numeru kolumny jako 0.
Sztuczka polega na tym, że gdy używasz numeru kolumny jako 0, zwraca wszystkie wartości we wszystkich kolumnach. Jeśli więc z listy rozwijanej wybiorę Jan, formuła indeksu zwróci wszystkie 4 wartości sprzedaży dla Jana {91064,71690,67574,25427}.
Teraz mogę użyć funkcji Large, aby wyodrębnić największą wartość
Porada dla profesjonalistów - Użyj numeru kolumny/wiersza jako 0 w formule indeksu, aby zwrócić wszystkie wartości w kolumnach/wierszach.
Wyszukaj cały wiersz/kolumnę za pomocą formuły WYSZUKAJ.PIONOWO
Podczas gdy formuła Index jest schludna, czysta i solidna, sposób VLOOKUP jest nieco skomplikowany. Powoduje to również, że funkcja jest niestabilna. Jest jednak niesamowita sztuczka, którą chciałbym podzielić się w tej sekcji. Oto wzór:
=DUŻA(WYSZUKAJ.PIONOWO(H3;B4:F13;WIERSZ(ADR.POŚR("2:"&LICZBA($B$4:$F$4)))), FAŁSZ),1)
Jak to działa
- ROW(INDIRECT(„2:”&COUNTA($B$4:$F$4))) - Ta formuła zwraca tablicę {2;3;4;5}. Zauważ, że ponieważ używa ADR.POŚR, to czyni tę formułę lotną.
- WYSZUKAJ.PIONOWO(H3,B4:F13,ROW(INDIRECT(„2:”&LICZBA($B$4:$F$4))),FALSE) - Oto najlepsza część. Gdy je połączysz, otrzymamy opcję WYSZUKAJ.PIONOWO(H3,B4:F13,{2;3;4;5},FAŁSZ). Teraz zauważ, że zamiast pojedynczego numeru kolumny nadałem mu tablicę numerów kolumn. A WYSZUKAJ.PIONOWO posłusznie wyszukuje wartości we wszystkich tych kolumnach i zwraca tablicę.
- Teraz wystarczy użyć funkcji LARGE, aby wyodrębnić największą wartość.
Pamiętaj, aby użyć tej formuły, aby użyć Control + Shift + Enter.
Porada dla profesjonalistów - w funkcji WYSZUKAJ.PIONOWO zamiast używania pojedynczego numeru kolumny, jeśli użyjesz tablicy numerów kolumn, zwróci tablicę wartości wyszukiwania.
