20 alternatyw dla WYSZUKAJ.PIONOWO

Ja używam angielskiej wersji pakietu Microsoft, w związku z tym używam angielskich nazw funkcji, niemniej podaję również odpowiednią nazwę polską. Ponadto budowa funkcji w wersji angielski rożni się od polskiej tym, że poszczególne argumenty oddzielone są przecinkami, w odróżnieniu do średników używanych w wersji np. polskiej. Wynika to z faktu, że w wersji angielskiej część całkowitą liczby oddzielamy od części dziesiętnej kropką, a w wersji polskiej – przecinkiem.

WYSZUKAJ.PIONOWO – funkcja, która znajduje i zwraca powiązaną wartość ze wskazanego zakresu danych. W zadaniu będę używał zakresów w formacie Tabel.

Pewnie każdy z was używa jednej z najbardziej popularnych funkcji Excela, jaką bez wątpienia jest WYSZUKAJ.PIONOWO. Czy wiesz jednak o tym, że można ją zastąpić innymi funkcjami czy też metodami, aby osiągnąć ten sam rezultat? Postanowiłem podzielić się 20 alternatywami, przy okazji pokazując możliwości innych formuł oraz metod stosowanych w Excelu.

W załączeniu dostępny jest plik z opisywanymi przykładami zastosowania 20 alternatyw dla WYSZUKAJ.PIONOWO. Ponadto można by jeszcze dodać kolejne rozwiązania z kombinacją nowej funkcji X.DOPASUJ XMATCH (365) zamiast DOPASUJ MATCH. Różnica między tymi funkcjami jest jednak stosunkowo nieznaczna. Najistotniejsza to ta, ze domyślnie ustawione jest dopasowanie dokładne.

[@Waluta] – wyszukiwana wartość w kolumnie Waluta
KursyWalut – tabela źródłowa
KursyWalut[Waluta] – kolumna z walutami
KursyWalut[Kurs] – kolumna z kursami

Tabela główna nazywa się Tabela_01. Natomiast tabeli źródłowej, zawierającej wybrane kursy walut w stosunku do GBP przypisałem nazwę KursyWalut Obydwie są w formacie Tabel. Jest to istotne podczas tworzenia formuł, gdyż odwołania funkcji będą odnosić się do nazw strukturalnych tych tabel.

• KursyWalut zostały posortowane po wyszukiwanej nazwie ze względu na funkcję LOOKUP, która w przeciwnym wypadku nie działa poprawnie
• Zastosowałem w tym przykładzie dopasowanie dokładne.
• Gdyby zadanie polegało na dopasowaniu w przybliżeniu, przeszukiwane wartości musiałaby być liczbami a nie tekstem.
• Funkcje SUMPRODUCT SUMIF(S), MAXIF(S), MINIF(S), AVERAGEIF(S) oraz FILTRUJ funkcjonują jedynie w dopasowaniu dokładnym.
• Tabela źródłowa zawiera tylko unikatowe rekordy. W przeciwnym wypadku rozwiązanie byłoby inne dla funkcji SUMIF(S), MAXIF(S), MINIF(S), AVERAGEIF(S)

#0. WYSZUKAJ.PIONOWO

=WYSZUKAJ.PIONOWO([@Waluta];KursyWalut;2;0)

#1. WYSZUKAJ LOOKUP Najstarsza funkcja z grupy funkcji wyszukiwania. Wymaga, aby tabela poboczna była posortowana rosnąco po wyszukiwanej wartości.

=WYSZUKAJ([@Waluta];KursyWalut)

=WYSZUKAJ([@Waluta];KursyWalut[Waluta];KursyWalut[Kurs])

#2. X.WYSZUKAJ XLOOKUP (365) – Najnowsza, najbardziej elastyczna, i wszechmogąca funkcja.

=X.WYSZUKAJ([@Waluta].KursyWalut[Waluta].KursyWalut[Kurs])

#3. INDEKS  + PODAJ.POZYCJĘ  INDEX + (X)MATCH

=INDEKS(KursyWalut[Kurs];PODAJ.POZYCJĘ([@Waluta];KursyWalut[Waluta];0))

=INDEKS(KursyWalut[Kurs];X.PODAJ.POZYCJĘ([@Waluta];KursyWalut[Waluta]))

#4. JEŻELI IF

=JEŻELI([@Waluta]=$V$5;$W$5;JEŻELI([@Waluta]=$V$4;$W$4;JEŻELI([@Waluta]=$V$3;$W$3;$W$6)))

#5. WARUNKI IFS (2016+)

=WARUNKI([@Waluta]=$V$6;$W$6;[@Waluta]=$V$5;$W$5;[@Waluta]=$V$4;$W$4;[@Waluta]=$V$3;$W$3)

#6. PRZEŁĄCZ SWITCH – (2016+)

=PRZEŁĄCZ(TRUE;[@Waluta]=$V$6;$W$6;[@Waluta]=$V$5;$W$5;[@Waluta]=$V$4;$W$4;[@Waluta]=$V$3;$W$3)

#7. WYBIERZ  + PODAJ.POZYCJĘ CHOSSE +(X)MATCH

=WYBIERZ(PODAJ.POZYCJĘ([@Waluta];$V$3:$V$6;0);$W$3;$W$4;$W$5;$W$6)

#8. ADR.POŚR + PODAJ.POZYCJĘ INDIRECT+MATCH

=@ADR.POŚR("$W$"&(PODAJ.POZYCJĘ([@Waluta];KursyWalut[Waluta];0)+2);TRUE)

#9. PRZESUNIĘCIE + PODAJ.POZYCJĘ OFFSET + MATCH

=OFFSET(KursyWalut[[#Headers];[Kurs]];PODAJ.POZYCJĘ ([@Waluta];KursyWalut[Waluta];0);0)

#10. POWT REPT

=POWT($W$3;[@Waluta]=$V$3)&REPT($W$4;[@Waluta]=$V$4)&REPT($W$5;[@Waluta]=$V$5)

#11. Algebra logiczna

=([@Waluta]=$V$4)*$W$4+([@Waluta]=$V$3)*$W$3+([@Waluta]=$V$5)*$W$5

#12. FILTRUJ (365) – Rozwiązanie występuje jedynie dla dokładnego dopasowania

=FILTRUJ(KursyWalut[Kurs];KursyWalut[Waluta]=[@Waluta])

#13. SUMA.ILOCZYNÓW SUMPRODUCT – Wynikiem jest rezultat działań arytmetycznego a wartościami wyszywanymi musi być tekst. Szukana wartość musi być liczbą, a przeszukiwany zakres musi zawierać wartości tekstowe. W przeciwnym wypadku występuje błąd, zastosowałem więc inną tabelę z kursami walut. Rozwiązanie występuje jedynie dla dokładnego dopasowania. W szczególnym wypadku w połączeniu z EXACT dopasowanie jest z uwzględnieniem wielkości liter

=SUMA.ILOCZYNÓW(--([@Waluta]=KursyWalut[Waluta]);(KursyWalut[Kurs]))

#14. SUMA.JEŻELI/SUMA.WARUNKÓW SUMIF(S), MAXIF(S), MINIF(S), AVERAGEIF(S)

=SUMA.JEŻELI(KursyWalut[Waluta];[@Waluta];KursyWalut[Kurs])

#15. BD.POLE (DGET) Jest to funkcja tzw. Database, która ze względu na swoja budowę tworzy jednostkowe rozwiązanie i nie można jej użyć w innych komórkach. Niemniej w takich okolicznościach może zastąpić VLOOKUP, choć jedynie dokładną wartość

=DGET(KursyWalut[#All];KursyWalut[[#Headers];[Kurs]];Criteria)

#16. Funkcja własna użytkownika MyLookup.
Charakteryzuje się tym, że jest stworzona w kodzie VBA, wymaga więc zapisania w pliku z rozszerzeniem .xlsm i włączenia makr.

=MyLookup([@CURRENCY];CurrencyTbl[CURRENCY];CurrencyTbl[VALUE])

Rozwiązanie inne niż za pomocą funkcji/ formuły to:
#17 Filtr zaawansowany

#18 POWER QUERY
#19 PIVOT TABLES
#20 POWER PIVOT

Bezpłatny plik z przykładami 20 alternatyw dla WYSZUKAJ.PIONOWO, dostępny jest do pobrania, klikając poniżej.