Wyszukiwanie wartości przy użyciu funkcji WYSZUKAJ.PIONOWO, INDEKS i PODAJ.POZYCJĘ

Porada: Spróbuj użyć nowych funkcji XLOOKUP i XMATCH , ulepszone wersje funkcji opisanych w tym artykule. Te nowe funkcje działają w dowolnym kierunku i domyślnie zwracają dokładne dopasowania, dzięki czemu są one łatwiejsze i wygodniejsze niż ich poprzedniki.

Załóżmy, że istnieje lista numerów lokalizacji pakietu Office i trzeba wiedzieć, którzy pracownicy znajdują się w poszczególnych biurach. Arkusz kalkulacyjny jest duży, więc możesz uważać, że jest to trudne zadanie. Wykonywanie funkcji wyszukiwania jest bardzo proste.

Funkcje Wyszukaj . pionowo i Wyszukaj . tekst wraz z funkcją indeks i Uwzględnijsą najbardziej przydatnymi funkcjami programu 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— elementu danych, które funkcja musi działać. W przypadku funkcji Wyszukaj. pionowo ten pierwszy argument jest wartością, którą chcesz znaleźć. Ten argument może być adresem komórki lub stałą wartością, taką jak "Kowalski" lub 21 000. Drugi argument jest zakresem komórek, C2-: E7, w którym należy szukać wartości, którą chcesz znaleźć. Trzecim argumentem jest kolumna w tym zakresie komórek zawierająca szukaną wartość.

Czwarty argument jest opcjonalny. Wprowadź wartość PRAWDA lub FAŁSZ. Jeśli wprowadzisz wartość PRAWDA lub argument nie zostanie pusty, funkcja zwróci przybliżoną wartość podaną w pierwszym argumencie. Jeśli wprowadzisz wartość FAŁSZ, funkcja będzie odpowiadała 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. Gdy wprowadzisz wartość w komórce B2 (pierwszy argument), funkcja Wyszukaj. pionowo wyszukuje komórki w zakresie C2: E7 (drugi argument) i zwraca najbliższy przybliżony odpowiednik z trzeciej kolumny zakresu, kolumna E (trzeci argument).

Typowe zastosowanie funkcji WYSZUKAJ.PIONOWO

Czwarty argument jest pusty, więc funkcja zwraca przybliżony odpowiednik. 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.

Gdy będziesz wygodnie korzystać z funkcji Wyszukaj. pionowo, funkcja Wyszukaj. pionowo jest równie łatwa w obsłudze. Wprowadzasz te same argumenty, ale wyszukujesz w wierszach zamiast w kolumnach.

Używanie funkcji indeks i podaj. poziomo zamiast funkcji Wyszukaj. pionowo

Istnieją pewne ograniczenia dotyczące korzystania z funkcji Wyszukaj. pionowo — funkcja Wyszukaj. pionowo umożliwia tylko wyszukiwanie wartości od lewej do prawej. Oznacza to, że kolumna zawierająca przeszukiwaną wartość powinna znajdować się zawsze na lewo od kolumny zawierającej wartość zwracaną. 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. Jeżeli.

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 poznać więcej przykładów użycia funkcji indeks i UWZGLĘDNIj zamiast funkcji Wyszukaj. pionowo, zobacz artykuł https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ według Bill Jelena, Microsoft MVP.

Spróbuj użyć tej funkcji

Jeśli chcesz eksperymentować z funkcjami odnośników przed wypróbią ich z własnych danych, Oto kilka przykładowych danych.

Przykład użycia funkcji Wyszukaj. pionowo

Skopiuj poniższe dane do pustego arkusza kalkulacyjnego.

Porada: Przed wklejeniem danych do programu Excel Ustaw szerokość kolumn od A do C na 250 pikseli, a następnie kliknij pozycję Zawijaj tekst (kartaNarzę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ść kolumn od A do C na 250 pikseli, a następnie kliknij pozycję Zawijaj tekst (kartaNarzę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 indeksu i dopasowania

W tym ostatnim przykładzie zastosowano funkcję indeks i podaj. Jeżeli, aby zwrócić najwcześniejszy numer faktury wraz z odpowiednią datą dla każdego z pięciu miast. Ponieważ data jest zwracana jako liczba, używamy funkcji tekst w celu sformatowania 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ść kolumn od A do D na 250 pikseli, a następnie kliknij pozycję Zawijaj tekst (kartaNarzę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

Zobacz też

Podręczna karta informacyjna:

odnośnik do odnośnika i funkcje ODnośników funkcji Wyszukaj. pionowo (odwołanie)

Użyj argumentu table_array w funkcji Wyszukaj

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×