Łącząc funkcję WYSZUKAJ. PIONOWO programu Excel z funkcją NR. KOLUMNY można utworzyć formułę wyszukiwania, która zwraca wiele wartości z jednego wiersza bazy danych lub tabeli danych. Dowiedz się, jak utworzyć formułę wyszukiwania, która zwraca wiele wartości z jednego rekordu danych.
Instrukcje zawarte w tym artykule dotyczą programu Excel 2019, 2016, 2013, 2010; i Excel dla Microsoft 365.
Dolna linia
Formuła wyszukiwania wymaga, aby funkcja NR. KOLUMNY była zagnieżdżona w funkcji WYSZUKAJ. PIONOWO. Zagnieżdżanie funkcji polega na wprowadzeniu drugiej funkcji jako jednego z argumentów pierwszej funkcji.
Wprowadź dane samouczka
W tym samouczku funkcja NR. KOLUMNY jest wprowadzana jako argument numeru indeksu kolumny dla funkcji WYSZUKAJ. PIONOWO. Ostatni krok w samouczku polega na skopiowaniu formuły wyszukiwania do dodatkowych kolumn w celu pobrania dodatkowych wartości dla wybranej części.
Pierwszym krokiem w tym samouczku jest wprowadzenie danych do arkusza programu Excel. Aby wykonać kroki opisane w tym samouczku, wprowadź dane pokazane na poniższym obrazku do następujących komórek:
- Wprowadź górny zakres danych do komórek od D1 do G1.
- Wprowadź drugi zakres do komórek od D4 do G10.
Kryteria wyszukiwania i formuła wyszukiwania utworzone w tym samouczku są wprowadzane w wierszu 2 arkusza roboczego.
Ten samouczek nie obejmuje podstawowego formatowania programu Excel pokazanego na obrazku, ale nie ma to wpływu na działanie formuły wyszukiwania.
Utwórz nazwany zakres dla tabeli danych
Nazwany zakres to łatwy sposób na odwoływanie się do zakresu danych w formule. Zamiast wpisywać odwołania do komórek dla danych, wpisz nazwę zakresu.
Drugą zaletą korzystania z nazwanego zakresu jest to, że odwołania do komórek dla tego zakresu nigdy się nie zmieniają, nawet jeśli formuła jest kopiowana do innych komórek w arkuszu. Nazwy zakresów są alternatywą dla bezwzględnych odwołań do komórek, aby zapobiec błędom podczas kopiowania formuł.
Nazwa zakresu nie zawiera nagłówków ani nazw pól danych (jak pokazano w wierszu 4), tylko dane.
-
Podświetl komórki D5 do G10 w arkuszu.
Image -
Umieść kursor w polu nazwy znajdującym się nad kolumną A, wpisz Tabela, a następnie naciśnij Enter. Komórki od D5 do G10 mają nazwę zakresu Tabela.
Image - Nazwa zakresu dla argumentu tablicy tabeli WYSZUKAJ. PIONOWO jest używana w dalszej części tego samouczka.
Otwórz okno dialogowe WYSZUKAJ. PIONOWO
Chociaż możliwe jest wpisanie formuły wyszukiwania bezpośrednio do komórki w arkuszu, wiele osób ma trudności z zachowaniem prostej składni - szczególnie w przypadku złożonej formuły, takiej jak ta użyta w tym samouczku.
Alternatywnie użyj okna dialogowego Argumenty funkcji WYSZUKAJ. PIONOWO. Prawie wszystkie funkcje programu Excel mają okno dialogowe, w którym każdy z argumentów funkcji jest wprowadzany w osobnym wierszu.
-
Wybierz komórkę E2 arkusza. Jest to lokalizacja, w której będą wyświetlane wyniki dwuwymiarowej formuły wyszukiwania.
Image -
Na wstążce przejdź do zakładki Formuły i wybierz Wyszukiwanie i referencje.
Image -
Wybierz WYSZUKAJ. PIONOWO, aby otworzyć okno dialogowe Argumenty funkcji.
Image - W oknie dialogowym Argumenty funkcji wprowadzane są parametry funkcji WYSZUKAJ. PIONOWO.
Wprowadź argument wartości wyszukiwania
Zwykle wartość wyszukiwania pasuje do pola danych w pierwszej kolumnie tabeli danych. W tym przykładzie wartość wyszukiwania odnosi się do nazwy części, w której chcesz znaleźć informacje. Dozwolone typy danych dla wartości wyszukiwania to dane tekstowe, wartości logiczne, liczby i odwołania do komórek.
Bezwzględne odwołania do komórek
Gdy formuły są kopiowane w programie Excel, odwołania do komórek zmieniają się, odzwierciedlając nową lokalizację. Jeśli tak się stanie, D2 odwołanie do komórki dla wartości wyszukiwania zmienia się i tworzy błędy w komórkach F2 i G2.
Bezwzględne odwołania do komórek nie zmieniają się podczas kopiowania formuł.
Aby zapobiec błędom, przekonwertuj odwołanie do komórki D2 na bezwzględne odwołanie do komórki. Aby utworzyć bezwzględne odwołanie do komórki, naciśnij klawisz F4. Spowoduje to dodanie znaków dolara wokół odwołania do komórki, takich jak $D$2.
-
W oknie dialogowym Argumenty funkcji umieść kursor w polu tekstowym lookup_value. Następnie w arkuszu wybierz cell D2, aby dodać to odwołanie do komórki do lookup_value. W komórce D2 zostanie wprowadzona nazwa części.
Image -
Bez przesuwania punktu wstawiania, naciśnij klawisz F4, aby przekonwertować D2 na bezwzględne odwołanie do komórki $D$2.
Image - Pozostaw otwarte okno dialogowe funkcji WYSZUKAJ. PIONOWO, aby przejść do następnego kroku w samouczku.
Wprowadź argument tablicy tabeli
Tablica tabeli to tabela danych, którą formuła wyszukiwania przeszukuje w celu znalezienia żądanych informacji. Tablica tabeli musi zawierać co najmniej dwie kolumny danych.
Pierwsza kolumna zawiera argument wartości wyszukiwania (ustawiony w poprzedniej sekcji), podczas gdy druga kolumna jest przeszukiwana przez formułę wyszukiwania w celu znalezienia określonych informacji.
Argument tablicy tabeli musi być wprowadzony jako zakres zawierający odwołania do komórek tabeli danych lub jako nazwa zakresu.
Aby dodać tabelę danych do funkcji WYSZUKAJ. PIONOWO, umieść kursor w polu tekstowym table_array w oknie dialogowym i wpisz Tabelaaby wprowadzić nazwę zakresu dla tego argumentu.
Zagnieżdżenie funkcji KOLUMNY
Normalnie funkcja WYSZUKAJ. PIONOWO zwraca tylko dane z jednej kolumny tabeli danych. Ta kolumna jest ustawiana przez argument numeru indeksu kolumny. W tym przykładzie występują jednak trzy kolumny, a numer indeksu kolumny należy zmienić bez edytowania formuły wyszukiwania. Aby to osiągnąć, zagnieżdż funkcję NR. KOLUMNY w funkcji WYSZUKAJ. PIONOWO jako argument Numer_indeksu.
Podczas zagnieżdżania funkcji program Excel nie otwiera okna dialogowego drugiej funkcji w celu wprowadzenia jej argumentów. Funkcję KOLUMNY należy wprowadzić ręcznie. Funkcja NR. KOLUMNY ma tylko jeden argument - argument Odwołanie, który jest odwołaniem do komórki.
Funkcja KOLUMNA zwraca numer kolumny podanej jako argument Odwołanie. Konwertuje literę kolumny na liczbę.
Aby znaleźć cenę przedmiotu, użyj danych w kolumnie 2 tabeli danych. W tym przykładzie użyto kolumny B jako referencji, aby wstawić 2 do argumentu Col_index_num.
-
W oknie dialogowym Function Arguments umieść kursor w polu tekstowym Col_index_num i wpisz COLUMN(. (Pamiętaj, aby dołączyć otwarty okrągły nawias.)
Image -
W arkuszu wybierz komórka B1, aby wprowadzić odwołanie do komórki jako argument Odwołanie.
Image - Wpisz zamykający nawias okrągły, aby zakończyć funkcję KOLUMNA.
Wprowadź argument wyszukiwania zakresu WYSZUKAJ. PIONOWO
Argument WYSZUKAJ. PIONOWO jest wartością logiczną (PRAWDA lub FAŁSZ), która wskazuje, czy funkcja WYSZUKAJ. PIONOWO powinna znaleźć dokładne lub przybliżone dopasowanie do wartości Lookup_value.
- PRAWDA lub pominięte: WYSZUKAJ. PIONOWO zwraca bliskie dopasowanie do wartości Lookup_value. Jeśli dokładne dopasowanie nie zostanie znalezione, funkcja WYSZUKAJ. PIONOWO zwraca następną największą wartość. Dane w pierwszej kolumnie tabeli Table_array muszą być posortowane w kolejności rosnącej.
- FALSE: WYSZUKAJ. PIONOWO używa dokładnego dopasowania do wartości Lookup_value. Jeśli w pierwszej kolumnie tablicy Table_array znajdują się dwie lub więcej wartości, które pasują do wartości wyszukiwania, używana jest pierwsza znaleziona wartość. Jeśli dokładne dopasowanie nie zostanie znalezione, zwracany jest błąd N/D.
W tym samouczku zostaną wyszukane określone informacje o konkretnym elemencie sprzętu, więc Range_lookup jest ustawiony na FALSE.
W oknie dialogowym Argumenty funkcji umieść kursor w polu tekstowym Range_lookup i wpisz False, aby nakazać funkcji WYSZUKAJ. PIONOWO zwracanie dokładnego dopasowania danych.
Wybierz OK, aby zakończyć formułę wyszukiwania i zamknąć okno dialogowe. Komórka E2 będzie zawierać błąd N/D, ponieważ kryteria wyszukiwania nie zostały wprowadzone do komórki D2. Ten błąd jest tymczasowy. Zostanie to poprawione po dodaniu kryteriów wyszukiwania w ostatnim kroku tego samouczka.
Skopiuj wzór wyszukiwania i wprowadź kryteria
Formuła wyszukiwania jednocześnie pobiera dane z wielu kolumn tabeli danych. Aby to zrobić, formuła wyszukiwania musi znajdować się we wszystkich polach, z których chcesz uzyskać informacje.
Aby pobrać dane z kolumn 2, 3 i 4 tabeli danych (cena, numer części i nazwa dostawcy), wprowadź częściową nazwę jako Lookup_value.
Ponieważ dane są ułożone według regularnego wzorca w arkuszu, skopiuj formułę wyszukiwania do cell E2 do cells F2 i G2 Podczas kopiowania formuły program Excel aktualizuje względne odwołanie do komórki w funkcji NR. KOLUMNY (komórka B1), aby odzwierciedlić nową lokalizację formuły. Program Excel nie zmienia bezwzględnego odwołania do komórki (takiego jak $D$2) i nazwanego zakresu (Tabela) podczas kopiowania formuły.
Istnieje więcej niż jeden sposób kopiowania danych w programie Excel, ale najprostszym sposobem jest użycie uchwytu wypełniania.
-
Wybierz komórkę E2, gdzie znajduje się formuła wyszukiwania, aby uczynić ją aktywną komórką.
Image -
Przeciągnij uchwyt wypełniania do komórki G2. Komórki F2 i G2 wyświetlają błąd N/D występujący w komórce E2.
Image -
Aby użyć formuł wyszukiwania do pobierania informacji z tabeli danych, w arkuszu wybierz cell D2, wpisz Widget i naciśnij Enter.
Image Poniższe informacje są wyświetlane w komórkach od E2 do G2.
- E2: 14,76 USD – cena widżetu
- F2: PN-98769 - numer części widżetu
- G2: Widgets Inc. - nazwa dostawcy widżetów
-
Aby przetestować formułę tablicową WYSZUKAJ. PIONOWO, wpisz nazwę innych części w komórce D2 i obserwuj wyniki w komórkach od E2 do G2.
Image - Każda komórka zawierająca formułę wyszukiwania zawiera inne dane dotyczące szukanego elementu sprzętowego.
Funkcja WYSZUKAJ. PIONOWO z funkcjami zagnieżdżonymi, takimi jak KOLUMNA, zapewnia potężną metodę wyszukiwania danych w tabeli, używając innych danych jako odnośnika wyszukiwania.
