Jak obliczyć odchylenie standardowe w Excelu: Samouczek krok po kroku (2026)

Zrozumienie składni VLOOKUP

Przed napisaniem formuły warto zrozumieć, co robi każda część. Składnia vlookup ma następującą strukturę:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

W pełnej formie: lookup_value table_array col_index_num range_lookup to cztery argumenty, których używa każda formuła VLOOKUP. Oto, co każdy z nich oznacza:

• lookup_value: Wartość, której chcesz szukać. Może to być nazwa, identyfikator pracownika, identyfikator produktu lub dowolny inny identyfikator.
• table_array: Zakres danych lub zakres tabeli, który zawiera twoje dane. VLOOKUP zawsze przeszukuje pierwszą kolumnę tego zakresu.
• col_index_num: Numer indeksu kolumny, który mówi Excelowi, z której kolumny zwrócić wartość. Jeśli pierwsza kolumna zawiera identyfikator, a druga kolumna zawiera nazwę, wprowadzenie 2 zwraca nazwę. Trzecia kolumna byłaby 3, i tak dalej.
• range_lookup: Ostatni argument, który kontroluje sposób dopasowania. Wprowadź FALSE dla dokładnego dopasowania lub TRUE dla przybliżonego dopasowania.

VLOOKUP wymaga, aby kolumna do wyszukiwania była pierwszą kolumną w zakresie tabeli i może pobierać dane tylko z kolumn znajdujących się na prawo od tej kolumny. Nie może patrzeć na kolumny na lewo od klucza wyszukiwania.

Funkcja vlookup służy do wyszukiwania informacji w określonej tabeli i zwraca wartość w komórce nakładającej się w oparciu o podane indeksy wierszy i kolumn.

Metoda 1: Pisanie podstawowej formuły VLOOKUP (Dokładne Dopasowanie)

Najczęstszym sposobem użycia vlookup jest tryb dokładnego dopasowania. To jest najbezpieczniejsza opcja, gdy masz unikalny klucz, taki jak identyfikator pracownika lub identyfikator produktu, i potrzebujesz precyzyjnego wyniku.

Krok 1: Zorganizuj swoje dane tak, aby kolumna do wyszukiwania była pierwszą kolumną w zakresie tabeli. VLOOKUP działa z danymi pionowymi i wymaga tablicy zorganizowanej pionowo, co oznacza, że każdy wiersz reprezentuje jeden rekord.

Krok 2: Kliknij komórkę, w której chcesz, aby pojawił się wynik.

Krok 3: Wpisz formułę. Używając tabeli pracowników jako przykładu:

=VLOOKUP(G2,A2:D9,2,FALSE())

Tutaj G2 to wartość do wyszukania (poszukiwany identyfikator pracownika), A2:D9 to zakres tabeli (zakres komórek zawierający wszystkie twoje dane), 2 to col_index_num (zwracający nazwę z drugiej kolumny), a FALSE określa dokładne dopasowanie.

Krok 4: Naciśnij Enter. Formuła zwraca dokładną wartość dopasowaną do klucza wyszukiwania w tym samym wierszu.

Aby uniknąć zwracania niepoprawnych danych, zawsze używaj FALSE dla dokładnych dopasowań w VLOOKUP. VLOOKUP domyślnie przechodzi do trybu przybliżonego dopasowania, jeśli argument range_lookup jest pominięty, co może prowadzić do nieoczekiwanych i niepoprawnych wyników, jeśli potrzebne jest dokładne dopasowanie.

W większości przypadków zaleca się używanie vlookup w trybie dokładnego dopasowania (FALSE), gdy masz unikalny klucz, aby zapewnić dokładne wyniki.

Metoda 2: Pobieranie danych z różnych kolumn

Kiedy zrozumiesz, jak działa numer indeksu kolumny, możesz pobierać dane z dowolnej kolumny w zakresie tabeli, zmieniając tę jedną liczbę.

Używając tej samej tabeli pracowników:

• =VLOOKUP(G2, A2:D9, 2, FALSE) zwraca imię (druga kolumna w zakresie)
• =VLOOKUP(G2, A2:D9, 3, FALSE) zwraca dział (trzecia kolumna)
• =VLOOKUP(G2, A2:D9, 4, FALSE) zwraca wynagrodzenie (czwarta kolumna)

Numer kolumny zawsze jest liczony od lewego brzegu zakresu tabeli, a nie od kolumny A arkusza. Więc jeśli twój zakres tabeli zaczyna się od kolumny C, pierwsza liczona kolumna to sama kolumna C.

VLOOKUP może pobierać dane tylko z kolumn znajdujących się po prawej stronie kolumny wyszukiwania, co ogranicza jego elastyczność w pobieraniu danych.

Metoda 3: Używanie przybliżonego dopasowania dla zakresów

Przybliżone i dokładne dopasowanie służą różnym celom. Tryb przybliżonego dopasowania (range_lookup ustawione na TRUE) jest przeznaczony do sytuacji, w których szukasz wartości w zakresie, a nie konkretnego rekordu. Tabela progów podatkowych lub system poziomów prowizji to typowe przykłady.

Gdy range_lookup jest TRUE, VLOOKUP wykonuje przybliżone dopasowanie, co oznacza, że dopasowuje zakres wartości zamiast jednej dokładnej wartości. VLOOKUP ma dwa tryby dopasowania: dokładne i przybliżone, oba kontrolowane przez ostatni argument o nazwie range_lookup.

Ważne: W trybie przybliżonego dopasowania tabela danych musi być posortowana w porządku rosnącym według pierwszej kolumny, aby uniknąć niepoprawnych wyników. Jeśli tabela dostarczona do VLOOKUP nie jest posortowana w porządku rosnącym, użycie trybu przybliżonego dopasowania może prowadzić do niepoprawnych wyników. W trybie przybliżonego dopasowania tabela danych musi być posortowana w porządku rosnącym według pierwszej kolumny, aby uniknąć niepoprawnych wyników. Vlookup obsługuje przybliżone dopasowanie tylko wtedy, gdy spełniony jest ten wymóg sortowania.

Przykładowa formuła:

=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

To znajdzie przedział, do którego należy wartość w komórce B2, i zwróci stawkę z drugiej kolumny tego zakresu.

Metoda 4: Używanie VLOOKUP z odwołaniami bezwzględnymi przy kopiowaniu formuł

Gdy kopiujesz formułę vlookup na inne komórki, odniesienie do zakresu tabeli przesuwa się, chyba że je zablokujesz. Przy kopiowaniu formuły VLOOKUP, zablokuj zakres tabeli za pomocą znaków dolara, aby utworzyć odwołanie bezwzględne. To zapewnia, że zakres tabeli nie przesuwa się, gdy formuła jest kopiowana. To utrzymuje całe odniesienie do tabeli na stałe, podczas gdy komórka z wartością do wyszukania dostosowuje się automatycznie.

=VLOOKUP(A2, $C$2:$F$50, 2, FALSE)

Znaki dolara przed C2 i F50 tworzą odwołania bezwzględne i zapewniają, że zakres tabeli nie przesuwa się, gdy formuła jest kopiowana w dół.

Metoda 5: Łączenie VLOOKUP z funkcją MATCH

Funkcja match może być zagnieżdżona w VLOOKUP, aby stworzyć dynamiczne wyszukiwanie w kolumnach. Zamiast wpisywać stały numer kolumny, MATCH znajduje pozycję nagłówka kolumny automatycznie.

Użycie funkcji match w formule VLOOKUP pozwala na dynamiczny indeks kolumny, umożliwiając pobieranie danych z kolumny, która może zmieniać pozycję w tabeli.

=VLOOKUP(H2,A2:E6,MATCH(H3,A1:E1,0),FALSE())

Tutaj MATCH(H3, A1:E1, 0) wyszukuje nazwę kolumny przechowywaną w H3 w wierszu nagłówka i zwraca jej numer pozycji. VLOOKUP następnie używa tego numeru jako col_index_num.

Gdy VLOOKUP jest łączony z MATCH, zwiększa elastyczność, pozwalając użytkownikowi określać kolumnę do przeszukiwania w oparciu o dynamiczne odniesienie, a nie o stały numer. Integracja MATCH z VLOOKUP pomaga zapobiegać błędom, które występują, gdy zmienia się struktura danych, na przykład przy dodawaniu lub usuwaniu kolumn.

Pełen zestaw argumentów vlookup lookup_value table_array col_index_num nadal obowiązuje tutaj; tylko trzeci argument staje się dynamiczny.

Używanie VLOOKUP z tabelą Excel

Jeśli twoje dane są sformatowane jako tabela Excel (Wstaw > Tabela), VLOOKUP może używać odwołań strukturalnych zamiast zwykłych adresów komórek. Odwołania strukturalne sprawiają, że formuły są łatwiejsze do odczytania i automatycznie się rozszerzają, gdy do tabeli dodawane są nowe wiersze.

=VLOOKUP([@EmployeeID], EmployeeTable, 3, FALSE)

Odwołania strukturalne również zmniejszają konieczność ręcznego aktualizowania odwołań bezwzględnych i dobrze działają w dużych zestawach danych, gdzie liczba wierszy zmienia się regularnie. Tabela przestawna może uzupełniać to podejście, podsumowując dane wynikowe vlookup po ich pobraniu.

Typowe problemy i rozwiązywanie problemów

=IFNA(VLOOKUP(G2, A2:D9, 2, FALSE), "Nie znaleziono")

Szybki przewodnik: Tryby VLOOKUP i przypadki użycia

Argumenty Vlookup lookup_value table_array są zawsze wymagane. Ostatni argument jest technicznie opcjonalny, ale pominięcie go może w praktyce prowadzić do niepoprawnych wyników.

Ograniczenia VLOOKUP, które należy mieć na uwadze

VLOOKUP to niezawodna funkcja Excel do większości codziennych zadań, ale ma kilka wbudowanych ograniczeń, które warto znać, zanim się na niej polegasz w modelach produkcyjnych:

• Może przeszukiwać tylko pierwszą kolumnę zakresu tabeli, co oznacza, że twoje wyszukiwanie jest ograniczone do jednej kolumny na wyszukiwanie
• Zwraca tylko pierwsze dopasowanie, co czyni ją nieodpowiednią, gdy duże zestawy danych zawierają zduplikowane klucze
• col_index_num to stała liczba, co oznacza, że wstawienie nowej kolumny do tabeli może przesunąć licznik i złamać zwroty formuł
• Wartości tekstowe i liczby muszą zgadzać się typem, inaczej wyszukiwanie się nie powiedzie

Dla lepszej funkcjonalności rozważ użycie XLOOKUP zamiast VLOOKUP w nowszych wersjach Excel. XLOOKUP to nowa funkcja dostępna w Microsoft 365 i Excel 2021, która rozwiązuje ograniczenia lewokolumnowe i pierwsze dopasowanie. To powiedziawszy, VLOOKUP pozostaje szeroko wspierana we wszystkich wersjach Excel i nadal działa poprawnie dla zdecydowanej większości codziennych wyszukiwań.

Dla Deweloperów: Odczytywanie i Wyszukiwanie Danych Excel z IronXL

Jeśli jesteś deweloperem .NET lub C# i potrzebujesz replikować wyszukiwania w stylu VLOOKUP programowo, IronXL zapewnia czyste API do odczytywania arkuszy kalkulacyjnych Excel, iteracji wierszy i pobierania wartości komórek bez potrzeb Microsoft Office lub Interop.

Zainstaluj IronXL przez NuGet:

Install-Package IronXL.Excel

Poniższy przykład ładuje skoroszyt, odczytuje tabelę par identyfikatorów pracowników i nazw z tabeli Excel i wykonuje wyszukiwanie równoważne =VLOOKUP(lookupId, A2:D9, 2, FALSE):

using IronXL;

// Load the workbook
WorkBook workBook = WorkBook.Load("vlookup_demo.xlsx");
WorkSheet sheet = workBook.WorkSheets[0];

// Define the data range (equivalent to table_array in VLOOKUP)
string lookupId = "E004";
string lookupColumn   = "A"; // first column (Employee ID)
string returnColumn   = "B"; // second column (Name)
int dataStartRow = 2;
int dataEndRow   = 9;

string result = "Not Found";

for (int row = dataStartRow; row <= dataEndRow; row++)
{
    // Read the cell value from the lookup column
    string cellValue = sheet[$"{lookupColumn}{row}"].StringValue;

    if (cellValue == lookupId)
    {
        // Return the corresponding value from the return column
        result = sheet[$"{returnColumn}{row}"].StringValue;
        break; // Return the first match, same as VLOOKUP
    }
}

Console.WriteLine($"Employee Name: {result}");
// Output: Employee Name: David Lee

using IronXL;

// Load the workbook
WorkBook workBook = WorkBook.Load("vlookup_demo.xlsx");
WorkSheet sheet = workBook.WorkSheets[0];

// Define the data range (equivalent to table_array in VLOOKUP)
string lookupId = "E004";
string lookupColumn   = "A"; // first column (Employee ID)
string returnColumn   = "B"; // second column (Name)
int dataStartRow = 2;
int dataEndRow   = 9;

string result = "Not Found";

for (int row = dataStartRow; row <= dataEndRow; row++)
{
    // Read the cell value from the lookup column
    string cellValue = sheet[$"{lookupColumn}{row}"].StringValue;

    if (cellValue == lookupId)
    {
        // Return the corresponding value from the return column
        result = sheet[$"{returnColumn}{row}"].StringValue;
        break; // Return the first match, same as VLOOKUP
    }
}

Console.WriteLine($"Employee Name: {result}");
// Output: Employee Name: David Lee

Imports IronXL

' Load the workbook
Dim workBook As WorkBook = WorkBook.Load("vlookup_demo.xlsx")
Dim sheet As WorkSheet = workBook.WorkSheets(0)

' Define the data range (equivalent to table_array in VLOOKUP)
Dim lookupId As String = "E004"
Dim lookupColumn As String = "A" ' first column (Employee ID)
Dim returnColumn As String = "B" ' second column (Name)
Dim dataStartRow As Integer = 2
Dim dataEndRow As Integer = 9

Dim result As String = "Not Found"

For row As Integer = dataStartRow To dataEndRow
    ' Read the cell value from the lookup column
    Dim cellValue As String = sheet($"{lookupColumn}{row}").StringValue

    If cellValue = lookupId Then
        ' Return the corresponding value from the return column
        result = sheet($"{returnColumn}{row}").StringValue
        Exit For ' Return the first match, same as VLOOKUP
    End If
Next

Console.WriteLine($"Employee Name: {result}")
' Output: Employee Name: David Lee

To podejście odzwierciedla sposób, w jaki działa vlookup w Excel: iteruje pierwszą kolumnę zakresu danych i zwraca odpowiadającą wartość z określonej kolumny w tym samym wierszu. Dla dużych zestawów danych możesz rozszerzyć to na wyszukiwanie oparte na słownikach dla szybszej wydajności.

IronXL wspiera również odczytywanie kolumny e i dowolnej innej kolumny przy użyciu notacji adresu komórki, iterację przez odwołania strukturalne w nazwanych tabelach i eksportowanie wyników z powrotem do XLSX bez jakiejkolwiek zależności od Office.

Rozpocznij od darmowego testu, aby przetestować IronXL w swoim własnym projekcie. Pełna dokumentacja jest dostępna na ironsoftware.com/csharp/excel/.

Dalsza lektura:

• Odczytywanie plików Excel w C# z IronXL
• Pobierz dane ze skoroszytu Excel w C#
• Samouczek C#: Pobieranie wartości komórek Excel

Podsumowanie

Znajomość użycia vlookup w excelu otwiera szeroki zakres praktycznych przepływów pracy: pobieranie imion z listy ID, dopasowywanie cen do identyfikatorów produktów, pobieranie danych działu z katalogu pracowników i wiele więcej. Dla codziennych wyszukiwań z unikalnym kluczem, dokładne dopasowanie (FALSE) jest prawie zawsze właściwym punktem wyjściowym. Gdy potrzebujesz wyniku opartego na zakresie, przybliżone dopasowanie (TRUE) działa dobrze, o ile dane są posortowane w porządku rosnącym. Dla większej elastyczności połączenie index match lub zagnieżdżenie funkcji match wewnątrz vlookup Excel usuwa całkowicie ograniczenie lewokolumnowe.

Jeśli budujesz aplikacje .NET pracujące z danymi Excel na dużą skalę, IronXL umożliwia odczytywanie, wyszukiwanie i pobieranie danych z arkuszy kalkulacyjnych Excel bez ani jednej linii kodu Interop Office. Weź darmowy test i zobacz, jak szybko możesz dodać inteligencję arkusza kalkulacyjnego do swojej aplikacji.