Czy kiedykolwiek miałeś duży arkusz kalkulacyjny z danymi w programie Excel^(Excel) i potrzebujesz łatwego sposobu na filtrowanie i wyodrębnianie z niego określonych informacji? Jeśli nauczysz się korzystać z funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) w programie Excel , możesz wykonać to wyszukiwanie za pomocą jednej, potężnej funkcji programu Excel^(Excel) .

Funkcja WYSZUKAJ.PIONOWO^(VLOOKUP) w Excelu^(Excel) przeraża wiele osób, ponieważ ma wiele parametrów i istnieje wiele sposobów jej użycia. W tym artykule poznasz wszystkie sposoby korzystania z funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) w programie Excel i dlaczego ta funkcja jest tak potężna.

Parametry WYSZUKAJ.PIONOWO w programie Excel^{(VLOOKUP Parameters In Excel)}

Gdy zaczniesz pisać =VLOOKUP( w dowolnej komórce w programie Excel , zobaczysz wyskakujące okienko pokazujące wszystkie dostępne parametry funkcji.

Zbadajmy każdy z tych parametrów i ich znaczenie.

• lookup_value : wartość, której szukasz z arkusza kalkulacyjnego
• table_array : zakres komórek w arkuszu, który chcesz przeszukać
• col_index_num : kolumna, z której chcesz pobrać wynik
• [range_lookup] : tryb dopasowania ( TRUE = przybliżony, FALSE = dokładny)

Te cztery parametry umożliwiają przeprowadzanie wielu różnych, przydatnych wyszukiwań danych w bardzo dużych zbiorach danych.

Prosty przykład VLOOKUP Excel^{(A Simple VLOOKUP Excel Example)}

WYSZUKAJ.PIONOWO nie jest jedną z podstawowych funkcji programu Excel,^{(the basic Excel functions)} których możesz się nauczyć, więc spójrzmy na prosty przykład, aby rozpocząć.

W poniższym przykładzie użyjemy dużego arkusza kalkulacyjnego z wynikami SAT dla szkół w Stanach ^(United) Zjednoczonych^(States) . Ten arkusz kalkulacyjny zawiera ponad 450 szkół wraz z indywidualnymi wynikami SAT z czytania, matematyki i pisania. Zapraszam^(Feel) do pobrania, aby śledzić dalej. Istnieje połączenie zewnętrzne, które pobiera dane, więc podczas otwierania pliku otrzymasz ostrzeżenie, ale jest to bezpieczne.

Przeszukiwanie tak dużego zbioru danych w celu znalezienia szkoły, którą jesteś zainteresowany, byłoby bardzo czasochłonne.

Zamiast tego możesz utworzyć prosty formularz w pustych komórkach z boku tabeli. Aby przeprowadzić to wyszukiwanie, po prostu utwórz jedno pole dla Szkoły^(School) i trzy dodatkowe pola dla wyników z czytania, matematyki i pisania.

Następnie musisz użyć funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) w programie Excel , aby te trzy pola działały. W polu Czytanie^(Reading) utwórz funkcję WYSZUKAJ.PIONOWO^(VLOOKUP) w następujący sposób:

1. Wpisz =VLOOKUP(
2. Wybierz pole Szkoła^(School) , które w tym przykładzie to I2 . Wpisz przecinek.
3. Zaznacz cały zakres komórek zawierających dane, które chcesz wyszukać. Wpisz przecinek.

Po wybraniu zakresu możesz zacząć od kolumny, której używasz do wyszukiwania (w tym przypadku kolumny nazwy szkoły), a następnie zaznaczyć wszystkie inne kolumny i wiersze zawierające dane.

Uwaga^(Note) : Funkcja WYSZUKAJ.PIONOWO^(VLOOKUP) w programie Excel może przeszukiwać tylko komórki po prawej stronie kolumny wyszukiwania. W tym przykładzie kolumna nazwy szkoły musi znajdować się po lewej stronie szukanych danych.

1. Następnie, aby pobrać wynik Reading , musisz wybrać trzecią kolumnę z ostatniej wybranej kolumny. Więc wpisz 3 , a następnie wpisz kolejny przecinek.
2. Na koniec wpisz FALSE dla dokładnego dopasowania i zamknij funkcję za pomocą ) .

Twoja ostatnia funkcja WYSZUKAJ.PIONOWO^(VLOOKUP) powinna wyglądać mniej więcej tak:

=VLOOKUP(I2,B2:G461,3,FALSE)

Gdy po raz pierwszy naciśniesz Enter i zakończysz funkcję, zauważysz, że pole Czytanie^(Reading) będzie zawierać #N/A .

Dzieje się tak, ponieważ pole Szkoła jest puste i funkcja ^(School)WYSZUKAJ.PIONOWO^(VLOOKUP) nie ma nic do znalezienia. Jeśli jednak wpiszesz nazwę jakiejkolwiek szkoły średniej, którą chcesz wyszukać, zobaczysz prawidłowe wyniki z tego wiersza dla wyniku w czytaniu^(Reading) .

Jak radzić sobie z funkcją WYSZUKAJ.PIONOWO, uwzględniającą wielkość liter^{(How To Deal With VLOOKUP Being Case- Sensitive)}

Możesz zauważyć, że jeśli nie wpiszesz nazwy szkoły w tym samym przypadku, w którym jest ona wymieniona w zbiorze danych, nie zobaczysz żadnych wyników.

Dzieje się tak, ponieważ funkcja WYSZUKAJ.PIONOWO^(VLOOKUP) rozróżnia wielkość liter. Może to być denerwujące, szczególnie w przypadku bardzo dużego zbioru danych, w którym kolumna, którą przeszukujesz, jest niezgodna z kapitalizacją.

Aby obejść ten problem, możesz zmusić to, czego szukasz, do zmiany na małe litery przed wyszukaniem wyników. Aby to zrobić, utwórz nową kolumnę obok kolumny, której szukasz. Wpisz funkcję:

=TRIM(LOWER(B2))

Spowoduje to zmniejszenie nazwy szkoły i usunięcie wszelkich zbędnych znaków (spacji), które mogą znajdować się po lewej lub prawej stronie nazwy.

Przytrzymaj klawisz Shift i umieść kursor myszy w prawym dolnym rogu pierwszej komórki, aż zmieni się w dwie poziome linie. Kliknij dwukrotnie^(Double) myszą, aby automatycznie wypełnić całą kolumnę.

Wreszcie, ponieważ WYSZUKAJ.PIONOWO^(VLOOKUP) spróbuje użyć formuły zamiast tekstu w tych komórkach, musisz przekonwertować je wszystkie tylko na wartości. Aby to zrobić, skopiuj całą kolumnę, kliknij prawym przyciskiem myszy pierwszą komórkę i wklej tylko wartości.

Teraz, gdy wszystkie dane są wyczyszczone w tej nowej kolumnie, nieznacznie zmodyfikuj funkcję WYSZUKAJ.PIONOWO w programie ^(VLOOKUP)Excel , aby używać tej nowej kolumny zamiast poprzedniej, rozpoczynając zakres wyszukiwania od^{(the lookup range)} C2 zamiast B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Teraz zauważysz, że jeśli zawsze będziesz pisać małymi literami, zawsze uzyskasz dobry wynik wyszukiwania.

Jest to przydatna wskazówka programu Excel^{(handy Excel tip)} , która pozwala przezwyciężyć fakt, że funkcja WYSZUKAJ.PIONOWO^(VLOOKUP) rozróżnia wielkość liter.

WYSZUKAJ.PIONOWO dopasowanie przybliżone

Chociaż przykład dopasowania dokładnego WYSZUKAJ^(LOOKUP) opisany w pierwszej części tego artykułu jest dość prosty, dopasowanie przybliżone jest nieco bardziej złożone.

Dopasowanie przybliżone najlepiej nadaje się do przeszukiwania zakresów liczb. Aby to zrobić poprawnie, zakres wyszukiwania musi być odpowiednio posortowany. Najlepszym tego przykładem jest funkcja WYSZUKAJ.PIONOWO służąca^(VLOOKUP) do wyszukiwania klasy literowej odpowiadającej klasie liczbowej.

Jeśli nauczyciel ma długą listę ocen z pracy domowej uczniów z całego roku z końcową uśrednioną kolumną, byłoby miło, gdyby ocena literowa odpowiadająca tej końcowej ocenie pojawiła się automatycznie.

Jest to możliwe dzięki funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) . Wszystko, co jest wymagane, to tabela przeglądowa po prawej stronie, która zawiera odpowiednią ocenę literową dla każdego zakresu liczbowego.

Teraz, korzystając z funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) i przybliżonego dopasowania, możesz znaleźć odpowiednią ocenę liter odpowiadającą prawidłowemu zakresowi liczbowemu.

W tej funkcji WYSZUKAJ.PIONOWO:

• lookup_value : F2, końcowa uśredniona ocena
• table_array : I2:J8, zakres wyszukiwania klasy liter
• index_column : 2, druga kolumna w tabeli odnośników
• [range_lookup] : TRUE, przybliżone dopasowanie

Po zakończeniu funkcji WYSZUKAJ.PIONOWO^(VLOOKUP) w G2 i naciśnięciu klawisza Enter^(Enter) , możesz wypełnić pozostałe komórki, stosując to samo podejście opisane w ostatniej sekcji. Zobaczysz, że wszystkie oceny literowe są poprawnie wypełnione.

Zwróć uwagę, że funkcja WYSZUKAJ.PIONOWO^(VLOOKUP) w programie Excel wyszukuje od dolnego końca zakresu ocen z przypisanym wynikiem literowym do górnej części zakresu następnej litery.

Tak więc „C” musi być literą przypisaną do dolnego zakresu (75), a B jest przypisane do dolnego (minimum) własnego zakresu liter. WYSZUKAJ.PIONOWO^(VLOOKUP) „znajdzie” wynik dla 60 (D) jako najbliższą przybliżoną wartość dla wartości od 60 do 75.

WYSZUKAJ.PIONOWO^(VLOOKUP) w Excelu^(Excel) to bardzo potężna funkcja, która jest dostępna od dawna. Przydaje się również do znajdowania pasujących wartości w dowolnym miejscu skoroszytu programu Excel^{(finding matching values anywhere in an Excel workbook)} .

Pamiętaj jednak, że użytkownicy Microsoft , którzy mają miesięczną subskrypcję Office 365 , mają teraz dostęp do nowszej funkcji XLOOKUP. Ta funkcja ma więcej parametrów i dodatkową elastyczność. Użytkownicy z półroczną subskrypcją będą musieli poczekać na wprowadzenie aktualizacji w lipcu 2020 r^{(July 2020)} .

How to Use VLOOKUP in Excel

Have you evеr had a large spreadsheet with data in Excel and need an easy way to filter and extract specifiс information frоm it? If you learn how to use VLOOKUP in Excel, you can do this lookup with just a single, powerful Excel function.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• Dodaj linię trendu regresji liniowej do wykresu punktowego programu Excel

• Jak zrobić histogram w Excelu

• Jak bezpiecznie zabezpieczyć hasłem plik Excel

• Jak stworzyć schemat blokowy w Wordzie i Excelu

• Jak tworzyć etykiety w programie Word z arkusza kalkulacyjnego Excel

• Jak otworzyć wiele wystąpień programu Excel

• Jak tworzyć wykresy Gantta w programie Microsoft Excel

• Jak utworzyć listę dystrybucyjną w Outlooku

• Jak znaleźć i obliczyć zakres w Excelu

• Jak stworzyć kartkę z życzeniami w MS Word

• Jak zrobić listę kontrolną w programie Excel

• Użyj nowej funkcji Excel Mobile „Wstaw dane z obrazu”

• 40 najlepszych skrótów klawiaturowych Microsoft Excel

• Jak utworzyć listę rozwijaną w programie Excel?

• Formatuj komórki za pomocą formatowania warunkowego w programie Excel

• Użyj Excela jako narzędzia do kopiowania danych z sieci

• Jak oddzielić imię i nazwisko w programie Excel

• Użyj funkcji podsumowujących do podsumowania danych w programie Excel

• Jak używać Sparklines w Excelu

• Autodopasowanie szerokości kolumn i wysokości wierszy w programie Excel