WYSZUKAJ.PIONOWO dla dwóch kryteriów? Niemożliwe!

Ale "jeśli coś jest niemożliwe do zrobienia, to trzeba znaleźć kogoś kto o tym nie wie i to zrobi", tak kiedyś powiedział Albert Einstein (albo Bob Marley, nie pamiętam).

Formuła WYSZUKAJ.PIONOWO jest bardzo przydatna, jeśli mamy dwa, podobne zestawy danych (np. w dwóch plikach lub w dwóch arkuszach) i chcemy z jednego skopiować wartości do drugiego, ale tak żeby odpowiadały tym samym wartościom w innej kolumnie, a wiersze wcale nie muszą być po kolei. Poniżej przykład:

Tutaj pierwszy zakres danych - znane już excelobiorcom zestawienie dochodów pewnej ambitnej grupki przestępczej. Zwróćcie uwagę na dodatkową kolumnę "Dzielnia" określającą miejsce dokonania przestępczego występku.

A tutaj lista dzielnicowych, na których trzeba uważać albo których trzeba będzie wziąć na garnuszek, ale to ostanie zostało szczegółowo opisane w strategii długoterminowej naszego gangu, kiedy ten będzie zgarniał większy hajs (nazwiska celowo zmienione).















Teraz dopasujemy dzielnicowego do odpowiadającego jego rejonowi wyczynu naszej ekipy.
Dodajemy nową kolumnę z dzielnicowymi i do pierwszego wiersza wpisujemy formułę:

=WYSZUKAJ.PIONOWO(E2;Psy!$A$1:$B$19;2;FAŁSZ)

"E2" to część wspólna pomiędzy naszymi zakresami.
"Psy" to nazwa arkusza, w którym znajdują się dane do dopasowania.
"$A$1:$B$19" to zakres, w którym będziemy szukać wartości z E2. $ zostały dodane, żeby przy przeciąganiu formuły w dół excel nie zmienił nam zakresu. Musimy tak wybrać zakres, żeby wartości szukane znalazły się w pierwszej kolumnie po lewej.
"2" to kolejny numer kolumny z zakresu, w której znajduję się wartość, którą chcemy znaleźć.
FAŁSZ mówi Excelowi, że interesuje nas konkretna wartość, a nie np. wspólne 3 litery.

Na razie wszystko idzie nam jak po maśle. Schody zaczynają się w momencie gdy kryteriów do wyszukaj pionowo są dwa. Wtedy musimy pokombinować trochę używając kombinacji formuł INDEX i MATCH (po polsku znanych również jako INDEKS i PODAJ.POZYCJĘ).

Potrzebujemy dwóch kryteriów do wyszukiwania, więc oprócz dzielnicy będziemy też szukać konkretnej daty. Tak się składa, że nasz zaradny gang wszedł w posiadanie listy wszystkich pieszych patroli, które wychodziły w teren w tym samym czasie, w którym działał nasz gang. Teraz wystarczy tylko dopasować datę i dzielnicę żeby wyciągnąć policjanta, który akurat wtedy był na służbie (nie pytajcie po co, bo kto mniej wie ten krócej siedzi).


Tutaj widzimy ową listę. Jak zapewne od razu rzuciło się wam w oczy wyszliśmy ze strefy komfortu, kiedy cały zakres dało się objąć wzrokiem bez używania scrolla. Teraz dopiero macie pole do popisu dla waszych nowych umiejętności (nazwiska celowo zmienione).

Żeby otrzymać, to co chcemy musimy wprowadzić specjalną formułę:

=INDEKS(Psy_2!A:C;PODAJ.POZYCJĘ(1;(Ekipa_2!A2=Psy_2!A:A)*(Ekipa_2!E2=Psy_2!B:B);0);3)

Ale zaraz, zaraz! Co tu się dzieje w tej formule? Już wyjaśniam. Formuła PODAJ.POZYCJĘ będzie szukała wartości "1" w tablicy, którą sobie stworzy, a wartość "1" (czyli PRAWDA) zostanie zwrócona tylko wtedy jeśli w kolumnie Psy_2!A:A (Data) zostanie znaleziona wartość Ekipa_2!A2 (24.03.2015) i w kolumnie Psy_2!B:B (Dzielnia) zostanie znaleziona wartość Ekipa_2!E2 (Ochota). Wynikiem tego będzie podanie numeru wiersza, gdzie sprawdził się ten warunek. Natomiast formuła INDEKS poda nam wartość komórki z wiersza zwróconego przez PODAJ.POZYCJĘ i kolumny 3 z zakresu Psy_2!A:C (Patrol). Ostatecznie powinniśmy otrzymać wartość "Jan Kuśka".

Nie bangla? Nic dziwnego, bo zapomnieliśmy, że to formuła tablicowa (dokonujemy obliczeń na wydzielonym zakresie, którego poszczególne kroki są przechowywane w pamięci programu). Jeśli zatwierdzimy formułę kombinacją klawiszy [ctrl] + [shift] + [enter] wtedy wszystko zatrybi i otrzymamy to o co nam szło.

Ten sprytny sposób możemy wykorzystać również do szukania wg 3 kryteriów przez dodanie w formule PODAJ.POZYCJĘ kolejnych nawiasów tj.

=INDEKS(Psy_2!A:C;PODAJ.POZYCJĘ(1;(Ekipa_2!A2=Psy_2!A:A)*(Ekipa_2!E2=Psy_2!B:B)*(XY=Z:Z);0);3)

Być może da się to również zastosować do 4 kryteriów, ale nigdy nie odważyłem się tego spróbować w obawie, że mi się coś w komputerze spali.

Ale kto stosuje tak wyrafinowane rozwiązania podczas pracy nad kilkunastoma wierszami? Nikt, bo może to zrobić ręcznie nawet dobrze wytresowana małpa. Ale gdy wierszy jest np 10 000 wtedy nawet stado małp nie pomoże. Trzeba jedynie pamiętać, że formuła tablicowa jest bardziej zasobożerna niż zwykła formuła i jeśli uruchomimy ją na 50 000 wierszy szukając wg 3 kryteriów, to mamy jakieś pół godziny z głowy. Polecam wyjście na spacer, bo przy takim obciążeniu procesorów nawet w pasjansa nie da się grać. Do zobaczenia w kolejnym odcinku!