Archiwa tagu: WYSZUKAJ.PIONOWO

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

 

XLOOKUP vs VLOOKUP

XLOOKUP vs VLOOKUP

Zapomnij o funkcji VLOOKUP (WYSZUKAJ.PIONOWO), nadeszła nowa funkcja XLOOKUP (X.WYSZUKAJ), którą bezpowrotnie zastąpi swoją pełną niedoskonałości poprzedniczkę. Jeśli poznasz możliwości funkcji XLOOKUP, nigdy więcej już nie użyjesz VLOOKUP. Nowa funkcja, która została niedawno wprowadzona ma więcej możliwości, jest bardziej przyjezdna oraz znacznie efektywniejsza.

XLOOKUP jest to rozbudowana wersja funkcji VLOOKUP lub HLOOKUP
Posiada 6 argumentów:

1. Co szukamy
2. W jakim zakresie szukamy
3. W jakim zakresie pobieramy rezultaty
4. Co jeśli nie znajdzie
5. Jak dokładnie:

0  – dokładne (domyślnie)
-1 – najbliższe mniejsze
1 – najbliższe większe
2 – znaki specjalne

6. Który wynik pobieramy:

1 – wyszukaj pierwszy (domyślnie)
-1 – wyszukaj ostatni
2 – wyszukiwanie binarne (dane szukane musza być rosnąco posortowane)
-2 – wyszukiwanie binarne (dane szukane musza być malejąco posortowane)

Zalety XLOOKUP w stosunku do VLOOKUP:

  • szukany rezultat w kolumnie może być po lewej stronie od kolumny z szukaną wartością
  • zmiana struktury tabeli z danymi poprzez wstawianie bądź usuniecie kolumn nie wpłynie na rezultat
  • zaznaczamy tylko istotne zakresy a niekoniecznie całą tabelę,
  • zwraca również zakres a nie tylko wartość
  • zwraca coś zadeklarowane, jeśli nie znajdzie szukanej wartości
  • domyślnie ustawione dokładne szukanie (0) w 4 argumencie, czyli najczęściej używana opcja
  • zwraca najbliższa większą wartość, jeśli zadeklarujemy (1) w 4 argumencie
  • jeśli szukamy najbliższa wartości w 4 argumencie nie musimy mieć danych posortowanych
  • znaki specjalne (Wildcard) obsługuje, tylko jeśli zadeklarujesz (2) w 4 argumencie
  • może szukać ostatnia wartość (-1) w 5 argumencie,
  • wyszykuje nie tylko pionowo, ale również poziomo, jeśli odpowiednio zaznaczymy zakresy
  • szybsza funkcjonalność

Podobne zagadnienia i sztuczki w Excel-u można nauczyć się u mnie na kompleksowym Szkoleniu w Londynie.

English Version