Porada: Spróbuj użyć nowych funkcji XLOOKUP i XMATCH , ulepszonych wersji funkcji opisanych w tym artykule. Te nowe funkcje działają w dowolnym kierunku i domyślnie zwracają dokładne dopasowania, dzięki czemu są łatwiejsze i wygodniejsze w użyciu niż ich poprzedniki.
Załóżmy, że masz listę numerów lokalizacji biura i musisz wiedzieć, którzy pracownicy są w każdym biurze. Arkusz kalkulacyjny jest ogromny, więc możesz uznać, że jest to trudne zadanie. W rzeczywistości jest to dość łatwe do czynienia z funkcją odnośnika.
Funkcje WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO wraz z funkcjami INDEKS i PODAJ.POZYCJĘ to jedne z najbardziej przydatnych funkcji w programie Excel.
Uwaga: Funkcja Kreator odnośników nie jest już dostępna w programie Excel.
Oto przykład użycia funkcji WYSZUKAJ.PIONOWO.
=WYSZUKAJ.PIONOWO(B2;C2:E7;3;PRAWDA)
W tym przykładzie B2 jest pierwszym argumentem — elementem danych, który musi działać w funkcji. W przypadku funkcji WYSZUKAJ.PIONOWO pierwszy argument jest wartością, którą chcesz znaleźć. Ten argument może być odwołaniem do komórki lub stałą wartością, taką jak "kowalski" lub 21 000. Drugi argument to zakres komórek C2-:E7, w którym ma zostać wyszukana wartość, którą chcesz znaleźć. Trzeci argument to kolumna w tym zakresie komórek zawierająca szukana wartość.
Czwarty argument jest opcjonalny. Wprowadź wartość PRAWDA lub FAŁSZ. W przypadku wprowadzenia wartości PRAWDA lub pozostawienia argumentu pustego funkcja zwraca przybliżone dopasowanie wartości określonej w pierwszym arguście. Jeśli wprowadzisz wartość FAŁSZ, funkcja będzie odpowiadać wartości podanej przez pierwszy argument. Innymi słowy, pozostawienie czwartego argumentu pustego — lub wprowadzenie wartości PRAWDA — zapewnia większą elastyczność.
W tym przykładzie przedstawiliśmy działanie tej funkcji. Po wprowadzeniu wartości w komórce B2 (pierwszy argument) funkcja WYSZUKAJ.PIONOWO przeszukuje komórki w zakresie C2:E7 (drugi argument) i zwraca najbliższe przybliżone dopasowanie z trzeciej kolumny w zakresie, kolumny E (trzeci argument).
Czwarty argument jest pusty, więc funkcja zwraca przybliżone dopasowanie. Wpisanie w tym argumencie wartości FAŁSZ wymaga podania jednej z wartości z kolumn C lub D, aby funkcja w ogóle zwróciła wyniki.
Jeśli wiesz, jak korzystać z funkcji WYSZUKAJ.PIONOWO, funkcja WYSZUKAJ.POZIOMO jest równie łatwa w użyciu. Wprowadzasz te same argumenty, ale są przeszukiwane w wierszach, a nie w kolumnach.
Używanie funkcji INDEKS i PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO
Korzystanie z funkcji WYSZUKAJ.PIONOWO ma pewne ograniczenia — funkcja WYSZUKAJ.PIONOWO może jedynie wyszukać wartość od lewej do prawej. Oznacza to, że kolumna zawierająca szukana wartość powinna zawsze znajdować się po lewej stronie kolumny zawierającej zwróconą wartość. Jeśli arkusz kalkulacyjny nie został utworzony w ten sposób, nie używaj funkcji WYSZUKAJ.PIONOWO. Zamiast tego użyj kombinacji funkcji INDEKS i PODAJ.POZYCJĘ.
W tym przykładzie przedstawiono krótką listę, na której szukana wartość (Szczecin) nie znajduje się w pierwszej kolumnie od lewej strony. Dlatego nie można użyć funkcji WYSZUKAJ.PIONOWO. Zamiast niej wyszukamy wartość Szczecin w zakresie B1:B11 przy użyciu funkcji PODAJ.POZYCJĘ. Wartość zostanie znaleziona w wierszu 4. Następnie funkcja INDEKS użyje tej wartości jako argumentu wyszukiwania i znajdzie populację Szczecina w czwartej kolumnie (kolumnie D). Użyta formuła jest wyświetlana w komórce A14.
Aby uzyskać więcej przykładów użycia funkcji INDEKS i PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO, zobacz artykuł https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ autorstwa Billa Jelena, specjalisty MVP firmy Microsoft.
Spróbuj użyć tej funkcji
Jeśli chcesz poeksperymentować z funkcjami odnośników przed ich wypróbowaniem z własnymi danymi, oto przykładowe dane.
Przykład funkcji WYSZUKAJ.PIONOWO w pracy
Skopiuj następujące dane do pustego arkusza kalkulacyjnego.
Porada: Przed wklejeniem danych do programu Excel ustaw szerokości kolumn od A do C na 250 pikseli i kliknij pozycję Zawijaj tekst (karta Narzędzia główne, grupa Wyrównanie ).
|
Gęstość |
Lepkość |
Temperatura |
|
0,457 |
3,55 |
500 |
|
0,525 |
3,25 |
400 |
|
0,606 |
2,93 |
300 |
|
0,675 |
2,75 |
250 |
|
0,746 |
2,57 |
200 |
|
0,835 |
2,38 |
150 |
|
0,946 |
2,17 |
100 |
|
1,09 |
1,95 |
50 |
|
1,29 |
1,71 |
0 |
|
Formuła |
Opis |
Wynik |
|
=WYSZUKAJ.PIONOWO(1;A2:C10;2) |
Wyszukuje wartość 1 w kolumnie A za pomocą dopasowania przybliżonego: znaleziona największa wartość mniejsza niż lub równa 1 w kolumnie A (czyli 0,946) jest używana do określenia wiersza kolumny B, z którego jest zwracana wartość. |
2,17 |
|
=WYSZUKAJ.PIONOWO(1;A2:C10;3;PRAWDA) |
Wyszukuje wartość 1 w kolumnie A za pomocą dopasowania przybliżonego: znaleziona największa wartość mniejsza niż lub równa 1 w kolumnie A (czyli 0,946) jest używana do określenia wiersza kolumny C, z którego jest zwracana wartość. |
100 |
|
=WYSZUKAJ.PIONOWO(0,7;A2:C10;3;FAŁSZ) |
Wyszukuje wartość 0,7 w kolumnie A za pomocą dopasowania dokładnego: nie istnieje dokładne dopasowanie w kolumnie A, dlatego funkcja zwraca błąd. |
#N/D! |
|
=WYSZUKAJ.PIONOWO(0,1;A2:C10;2;PRAWDA) |
Wyszukuje wartość 0,1 w kolumnie A za pomocą dopasowania przybliżonego: wartość 0,1 jest mniejsza niż najmniejsza wartość w kolumnie A, dlatego funkcja zwraca błąd. |
#N/D! |
|
=WYSZUKAJ.PIONOWO(2;A2:C10;2;PRAWDA) |
Wyszukuje wartość 2 w kolumnie A za pomocą dopasowania przybliżonego: znaleziona w kolumnie A największa wartość mniejsza niż lub równa 2 (czyli 1,29) jest używana do określenia wiersza kolumny B, z którego jest zwracana wartość. |
1,71 |
Przykład funkcji WYSZUKAJ.POZIOMO
Skopiuj wszystkie komórki w tej tabeli i wklej je w komórce A1 w pustym arkuszu programu Excel.
Porada: Przed wklejeniem danych do programu Excel ustaw szerokości kolumn od A do C na 250 pikseli i kliknij pozycję Zawijaj tekst (karta Narzędzia główne, grupa Wyrównanie ).
|
Osie |
Łożyska |
Śruby |
|
4 |
4 |
9 |
|
5 |
7 |
10 |
|
6 |
8 |
11 |
|
Formuła |
Opis |
Wynik |
|
=WYSZUKAJ.POZIOMO("Osie";A1:C4;2;PRAWDA) |
Wyszukuje słowo „Osie” w pierwszym wierszu i zwraca wartość z drugiego wiersza, który znajduje się w tej samej kolumnie (kolumnie A). |
4 |
|
=WYSZUKAJ.POZIOMO("Łożyska";A1:C4;3;FAŁSZ) |
Wyszukuje słowo „Łożyska” w pierwszym wierszu i zwraca wartość z trzeciego wiersza, która znajduje się w tej samej kolumnie (kolumnie B). |
7 |
|
=WYSZUKAJ.POZIOMO("Ł";A1:C4;3;PRAWDA) |
Wyszukuje „Ł” w pierwszym wierszu i zwraca wartość z trzeciego wiersza, która znajduje się w tej samej kolumnie. Ponieważ nie znaleziono dokładnego dopasowania „Ł”, użyta jest największa wartość w wierszu 1, która jest mniejsza niż „Ł”: „Osie” w kolumnie A. |
5 |
|
=WYSZUKAJ.POZIOMO("Sworznie";A1:C4;4) |
Wyszukuje słowo „Sworznie” w pierwszym wierszu i zwraca wartość z czwartego wiersza, która znajduje się w tej samej kolumnie (kolumnie C). |
11 |
|
=WYSZUKAJ.POZIOMO(3;{1;2;3\"a";"b";"c"\"d";"e";"f"};2;PRAWDA) |
Wyszukuje liczbę 3 w trzywierszowej stałej tablicowej i zwraca wartość z drugiego wiersza w tej samej (w tym przypadku trzeciej) kolumnie. W stałej tablicowej są trzy wiersze wartości oddzielone od siebie ukośnikiem odwrotnym (\). Zwracana jest wartość „c”, ponieważ znajduje się w drugim wierszu i w tej samej kolumnie co liczba 3. |
c |
Przykłady funkcji INDEKS i PODAJ.POZYCJĘ
W tym ostatnim przykładzie użyto razem funkcji INDEKS i PODAJ.POZYCJĘ w celu zwrócenia najwcześniejszego numeru faktury i odpowiadającej mu daty dla każdego z pięciu miast. Data jest zwracana jako liczba, dlatego funkcja TEKST służy do formatowania jej jako daty. Funkcja INDEKS w rzeczywistości używa jako argumentu wyniku funkcji PODAJ.POZYCJĘ. Połączenie funkcji INDEKS i PODAJ.POZYCJĘ jest stosowane dwukrotnie w każdej formule — najpierw w celu zwrócenia numeru faktury, a następnie w celu zwrócenia daty.
Skopiuj wszystkie komórki w tej tabeli i wklej je w komórce A1 w pustym arkuszu programu Excel.
Porada: Przed wklejeniem danych do programu Excel ustaw szerokości kolumn od A do D na 250 pikseli, a następnie kliknij pozycję Zawijaj tekst (karta Narzędzia główne, grupa Wyrównanie ).
|
Faktura |
Miasto |
Data faktury |
Najwcześniejsza faktura wg. miasta, z datą |
|
3115 |
Warszawa |
07.04.12 |
="Warszawa = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Warszawa";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Warszawa";$B$2:$B$33;0);3);"d/m/yy") |
|
3137 |
Warszawa |
09.04.12 |
="Poznań = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Poznań";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Poznań";$B$2:$B$33;0);3);"d/m/yy") |
|
3154 |
Warszawa |
11.04.12 |
="Gdańsk = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Gdańsk";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Gdańsk";$B$2:$B$33;0);3);"d/m/yy") |
|
3191 |
Warszawa |
21.04.12 |
="Nowy Targ = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Nowy Targ";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Nowy Targ";$B$2:$B$33;0);3);"d/m/yy") |
|
3293 |
Warszawa |
25.04.12 |
="Rzeszów = "&INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Rzeszów";$B$2:$B$33;0);1)& "; Data faktury: " & TEKST(INDEKS($A$2:$C$33;PODAJ.POZYCJĘ("Rzeszów";$B$2:$B$33;0);3);"d/m/yy") |
|
3331 |
Warszawa |
27.04.12 |
|
|
3350 |
Warszawa |
28.04.12 |
|
|
3390 |
Warszawa |
01.05.12 |
|
|
3441 |
Warszawa |
02.05.12 |
|
|
3517 |
Warszawa |
08.05.12 |
|
|
3124 |
Poznań |
09.04.12 |
|
|
3155 |
Poznań |
11.04.12 |
|
|
3177 |
Poznań |
19.04.12 |
|
|
3357 |
Poznań |
28.04.12 |
|
|
3492 |
Poznań |
06.05.12 |
|
|
3316 |
Gdańsk |
25.04.12 |
|
|
3346 |
Gdańsk |
28.04.12 |
|
|
3372 |
Gdańsk |
01.05.12 |
|
|
3414 |
Gdańsk |
01.05.12 |
|
|
3451 |
Gdańsk |
02.05.12 |
|
|
3467 |
Gdańsk |
02.05.12 |
|
|
3474 |
Gdańsk |
04.05.12 |
|
|
3490 |
Gdańsk |
05.05.12 |
|
|
3503 |
Gdańsk |
08.05.12 |
|
|
3151 |
Nowy Targ |
09.04.12 |
|
|
3438 |
Nowy Targ |
02.05.12 |
|
|
3471 |
Nowy Targ |
04.05.12 |
|
|
3160 |
Rzeszów |
18.04.12 |
|
|
3328 |
Rzeszów |
26.04.12 |
|
|
3368 |
Rzeszów |
29.04.12 |
|
|
3420 |
Rzeszów |
01.05.12 |
|
|
3501 |
Rzeszów |
06.05.12 |
