20 alternatyw dla WYSZUKAJ.PIONOWO

20 alternatyw dla WYSZUKAJ.PIONOWO

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 SUMOPRODUCT 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.

Pobierz 20 alternatyw dla WYSZUKAJ.PIONOWO

 

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *