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.