Pobierz przykładowy skoroszyt
WYSZUKAJ.PIONOWO z uwzględnieniem wielkości liter - Excel
W tym samouczku zademonstrujemy, jak wykonać funkcję WYSZUKAJ.PIONOWO z uwzględnieniem wielkości liter w programie Excel przy użyciu dwóch różnych metod.
Metoda 1 - VLOOKUP z rozróżnianiem wielkości liter z kolumną pomocnika
=WYSZUKAJ.PIONOWO(MAKS(DOKŁADNIE(E2,$B$2:$B$7)*(WIERSZ($B$2:$B$7))),$C$2:$D$7,2,0)
Funkcja WYSZUKAJ.PIONOWO
Funkcja WYSZUKAJ.PIONOWO służy do wyszukiwania przybliżonego lub dokładnego dopasowania wartości w skrajnej lewej kolumnie zakresu i zwraca odpowiednią wartość z innej kolumny. Domyślnie funkcja WYSZUKAJ.PIONOWO działa tylko dla wartości, które nie są rozróżniane wielkością liter, takich jak:
VLOOKUP z uwzględnieniem wielkości liter
Łącząc WYSZUKAJ.PIONOWO, DOKŁADNIE, MAX i WIERSZ, możemy utworzyć formułę WYSZUKAJ.PIONOWO z uwzględnieniem wielkości liter, która zwraca odpowiednią wartość dla naszej funkcji WYSZUKAJ.PIONOWO z uwzględnieniem wielkości liter. Przejdźmy przez przykład.
Mamy listę produktów i odpowiadających im cen (zwróć uwagę, że w identyfikatorze produktu rozróżniana jest wielkość liter):
Załóżmy, że zostaniemy poproszeni o podanie ceny za przedmiot przy użyciu jego identyfikatora przedmiotu w następujący sposób:
Aby to osiągnąć, najpierw musimy utworzyć kolumnę pomocniczą za pomocą ROW:
=ROW() kliknij i przeciągnij (lub kliknij dwukrotnie), aby wstępnie wypełnić wszystkie wiersze w zakresie
Następnie połącz VLOOKUP, MAX, EXACT i ROW w następujący sposób:
=WYSZUKAJ.PIONOWO(MAKS(DOKŁADNIE(,)*(WIERSZ())), ,,0)
=WYSZUKAJ.PIONOWO(MAKS(DOKŁADNIE(E2,$B$2:$B$7)*(WIERSZ($B$2:$B$7))),$C$2:$D$7,2,0)
Jak działa formuła?
- Funkcja DOKŁADNE porównuje identyfikator elementu w E2 (wartość wyszukiwania) z wartościami w B2:B7 (zakres wyszukiwania) i zwraca tablicę PRAWDA, w której występuje dokładne dopasowanie lub FLAS w tablicy BŁĄD, PRAWDA}.
- Ta tablica jest następnie mnożona przez tablicę ROW {2, 3, 4, 5, 6, 7} (zauważ, że pasuje to do naszej kolumny pomocniczej).
- Funkcja MAX zwraca maksymalną wartość z wynikowej tablicy {0,0,0,0,0,0,7}, która w naszym przykładzie wynosi 7.
- Następnie używamy wyniku jako naszej wartości wyszukiwania w funkcji WYSZUKAJ.PIONOWO i wybieramy naszą kolumnę pomocniczą jako zakres wyszukiwania. W naszym przykładzie formuła zwraca pasującą wartość 16,00 USD.
Metoda 2 - VLOOKUP z uwzględnieniem wielkości liter z „wirtualną” kolumną pomocniczą
=WYSZUKAJ.PIONOWO(MAX(DOKŁADNIE(D2,$B$2:$B$7)*(WIERSZ($B$2:$B$7))),WYBIERZ({1,2};WIERSZ($B$2:$B$7) , $C$2:$C$7,2,0)
Ta metoda wykorzystuje tę samą logikę, co pierwsza metoda, ale eliminuje potrzebę tworzenia kolumny pomocniczej i zamiast tego używa opcji WYBIERZ i ROW do utworzenia „wirtualnej” kolumny pomocniczej, jak na przykład:
=WYSZUKAJ.PIONOWO(MAKS(DOKŁADNIE(,)*(WIERSZ())), WYBIERZ({1,2};WIERSZ(), ), ,0)
=WYSZUKAJ.PIONOWO(MAX(DOKŁADNIE(D2,$B$2:$B$7)*(WIERSZ($B$2:$B$7))),WYBIERZ({1,2};WIERSZ($B$2:$B$7) ,$C$2:$7$C$,2,0)
Jak działa formuła?
- Pierwsza część formuły działa tak samo jak pierwsza metoda.
- Połączenie CHOOSE i ROW zwraca tablicę z dwiema kolumnami, jedną dla numeru wiersza, a drugą dla ceny. Tablica jest oddzielona średnikiem reprezentującym następny wiersz i przecinkiem dla następnej kolumny: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
- Następnie możemy użyć wyniku z pierwszej części formuły w funkcji WYSZUKAJ.PIONOWO, aby znaleźć odpowiednią wartość z naszej tablicy CHOOSE i ROW.
VLOOKUP z uwzględnieniem wielkości liter w Arkuszach Google
Wszystkie powyższe przykłady działają dokładnie tak samo w Arkuszach Google, jak w Excelu.
