Applies ToExcel dla Microsoft 365 Excel dla sieci web Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

Typowe zastosowanie funkcji WYSZUKAJ.PIONOWO

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.

Wyszukiwanie wartości przy użyciu funkcji INDEKS i PODAJ.WARTOŚĆ

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

Podręczna karta informacyjna: funkcja wyszukiwania i odwołań funkcji odświeżania funkcji WYSZUKAJ.PIONOWO

(informacje)

Używanie argumentu table_array w funkcji WYSZUKAJ.PIONOWO

Potrzebujesz dalszej pomocy?

Chcesz uzyskać więcej opcji?

Poznaj korzyści z subskrypcji, przeglądaj kursy szkoleniowe, dowiedz się, jak zabezpieczyć urządzenie i nie tylko.

Społeczności pomagają zadawać i odpowiadać na pytania, przekazywać opinie i słuchać ekspertów z bogatą wiedzą.