Łącząc ExcelaWYSZUKAJ.PIONOWO funkcja zKOLUMNA funkcja możemy stworzyć formułę wyszukiwania, która pozwala zwrócić wiele wartości z jednego wiersza bazy danych lub tabeli danych.
01 z 10Zwróć wiele wartości za pomocą funkcji WYSZUKAJ.PIONOWO Excel
Wykonanie poniższych czynności tworzy formułę odnośnika widoczną na powyższym obrazku, która zwróci wiele wartości z pojedynczego rekordu danych.
Formuła odnośnika wymaga, aby KOLUMNA funkcja jest zagnieżdżona wewnątrz WYSZUKAJ.PIONOWO. Zagnieżdżanie funkcji obejmuje wprowadzenie drugiej funkcji jako jednego z argumentów dla pierwszej funkcji.
W tym samouczku, KOLUMNA funkcja zostanie wprowadzona jako indeks kolumny numer argument dla WYSZUKAJ.PIONOWO. Ostatni krok w samouczku polega na skopiowaniu formuły odnośnika do dodatkowych kolumn w celu pobrania dodatkowych wartości dla wybranej części.
Wprowadź dane samouczka
Pierwszym krokiem w samouczku jest wprowadzenie danych do arkusza programu Excel. Aby wykonać kroki opisane w samouczku, wprowadź dane pokazane na powyższym obrazku do następujących komórek:
- Wprowadź najwyższy zakres danych w komórki D1 do G1.
- Wprowadź drugi zakres do komórki D4 do G10.
Zostaną wprowadzone kryteria wyszukiwania i formuła wyszukiwania utworzona podczas tego samouczka wiersz 2 arkusza roboczego.
Samouczek nie uwzględnia formatowania widocznego na obrazie, ale nie wpłynie to na działanie formuły odnośnika. Informacje na temat opcji formatowania podobnych do opisanych powyżej są dostępne w tym samouczku Basic Excel Formatting.
03 z 10Tworzenie nazwanego zakresu dla tabeli danych
Nazwany zakres to prosty sposób na odniesienie się do zakresu danych w formule. Zamiast wpisywania odniesień do komórek dla danych, wystarczy wpisać 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ą zatem alternatywą do używania 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 dla danych (wiersz 4), ale tylko same dane.
- Atrakcja komórki D5 do G10 w arkuszu roboczym, aby je wybrać.
- Kliknij na Nazwa Box znajduje się powyżej kolumna A.
- Rodzaj Stół do Nazwa Box.
- wciśnij Wchodzić klawisz na klawiaturze.
- Komórki D5 do G10 teraz ma nazwę zakresu Stół. Będziemy używać nazwy dla WYSZUKAJ.PIONOWO tablica tablicowa argument później w samouczku.
Otwieranie okna dialogowego WYSZUKAJ.PIONOWO
Chociaż możliwe jest wpisanie naszej formuły wyszukiwania bezpośrednio w komórce w arkuszu, wiele osób ma trudności z utrzymaniem prostej składni - szczególnie w przypadku złożonej formuły, takiej jak ta, której używamy w tym samouczku.
Alternatywą w tym przypadku jest użycie WYSZUKAJ.PIONOWO Okno dialogowe. Prawie wszystkie funkcje Excela mają okno dialogowe, które pozwala na wprowadzenie każdego z argumentów funkcji w osobnym wierszu.
Kroki te
- Kliknij komórka E2 arkusza roboczego - miejsce, w którym będą wyświetlane wyniki dwuwymiarowej formuły wyszukiwania.
- Kliknij na Karta Formuły z faborek.
- Kliknij na Wyszukiwanie i referencje opcja w faborek aby otworzyć rozwijaną funkcję.
- Kliknij WYSZUKAJ.PIONOWO na liście, aby otworzyć okno dialogowe funkcji.
Wprowadzanie argumentu wartości odnośnika
Zwykle lookup wartość dopasowuje pole danych w pierwszej kolumnie tabeli danych. W naszym przykładzie, lookup wartość odnosi się do nazwy części sprzętowej, o której chcemy znaleźć informacje. Dopuszczalne typy danych dla lookup wartość są danymi tekstowymi, wartościami logicznymi, liczbami i odwołaniami do komórek.
Bezwzględne odwołania do komórek
Zwykle, gdy formuły są kopiowane w Excelu, odwołania do komórek zmieniają się, odzwierciedlając ich nową lokalizację. Jeśli to się zdarzy, D2, odwołanie do komórki dla lookup wartość, zmieni się, gdy formuła zostanie skopiowana, tworząc błędy w komórki F2 i G2.
Bezwzględne odwołania do komórek nie zmieniają się, gdy formuły są kopiowane.
Aby zapobiec błędom, skonwertujemy odwołanie do komórki D2 w bezwzględne odwołanie do komórki. Bezwzględne odwołania do komórek tworzone są przez naciśnięcie klawisza F4 klawisz na klawiaturze. Robi to dodaje znaki dolara wokół odwołania do komórki, takie jak $ D 2 $.
- Kliknij na lookup_value linia w oknie dialogowym.
- Kliknij komórka D2 aby dodać to odwołanie do komórki do lookup_value line - jest to komórka, w której wpiszemy nazwę części, o której szukamy informacji
- Bez przesuwania punktu wstawiania naciśnij F4 klucz na klawiaturze do przekonwertowania D2 do bezwzględnego odwołania do komórki $ D 2 $.
- Zostawić WYSZUKAJ.PIONOWO okno dialogowe funkcji otwarte dla następnego kroku w samouczku.
Wprowadzanie argumentu Array tabeli
Tablica tablicowa jest tabelą danych wyszukiwaną przez wyszukiwarkę w celu znalezienia potrzebnych informacji.Tablica tablicy musi zawierać co najmniej dwie kolumny danych.
Pierwsza kolumna zawiera argument wartości wyszukiwania (poprzedni krok w samouczku), natomiast druga kolumna zostanie przeszukana przez formułę odnośnika w celu znalezienia określonych przez nas informacji.
Argument tablicowy należy wprowadzić jako zakres zawierający odwołania do komórki dla tabeli danych lub jako nazwę zakresu.
- Kliknij na table_array linia w oknie dialogowym.
- Rodzaj Stół wprowadzić nazwę zakresu dla tego argumentu.
- Zostawić WYSZUKAJ.PIONOWO okno dialogowe funkcji otwarte dla następnego kroku w samouczku.
Zagnieżdżanie funkcji COLUMN
Zwykle WYSZUKAJ.PIONOWO zwraca tylko dane z jednej kolumny tabeli danych i ta kolumna jest ustawiona przez numer indeksu kolumny argument.
W tym przykładzie mamy jednak trzy kolumny, z których chcemy zwrócić dane, dlatego potrzebujemy sposobu na łatwą zmianę numer indeksu kolumny bez edytowania naszej formuły odnośnika - tutaj jest KOLUMNA funkcja wchodzi.
Wprowadzając go jako numer indeksu kolumny argument, zmieni się, gdy zostanie skopiowana formuła odnośnika komórka D2 do komórki E2 i F2 później w samouczku.
Zagnieżdżanie funkcji
The KOLUMNA funkcja działa zatem jako WYSZUKAJ.PIONOWO„s numer indeksu kolumny argument; osiąga się to przez zagnieżdżanie KOLUMNA funkcja wewnątrz WYSZUKAJ.PIONOWO w Col_index_num wiersz okna dialogowego.
Ręczne wprowadzanie funkcji COLUMN
Podczas zagnieżdżania funkcji program Excel nie pozwala nam otworzyć okna dialogowego drugiej funkcji, aby wprowadzić jego argumenty. The KOLUMNA funkcja musi być wprowadzona ręcznie w Col_index_num linia. The KOLUMNA funkcja ma tylko jeden argument - Odniesienie Argument, który jest odwołaniem do komórki.
Wybór argumentu referencyjnego funkcji COLUMN
The KOLUMNA funkcją funkcji jest zwrócenie numeru kolumny podanej jako Odniesienie argument. Innymi słowy, zamienia literę kolumny na liczbę z kolumna A jako pierwsza kolumna, kolumna B drugi i tak dalej.
Od pierwszego pola danych zwracamy cenę produktu, który znajduje się w kolumnie drugiej tabeli danych. Możemy wybrać odwołanie do komórki dla dowolnej komórki kolumna B jako Odniesienie Argument w celu uzyskania numeru 2 dla Col_index_num argument.
- w WYSZUKAJ.PIONOWO okno dialogowe funkcji, kliknij przycisk Col_index_num linia.
- Wpisz nazwę funkcji kolumna a następnie otwarty okrągły wspornik.
- Kliknij komórka B1 w arkuszu roboczym, aby wprowadzić odwołanie do komórki jako Odniesienie argument.
- Wpisz a okrągły nawój okrągły uzupełnić KOLUMNA funkcjonować
- Zostawić WYSZUKAJ.PIONOWO okno dialogowe funkcji otwarte dla następnego kroku w samouczku.
Wprowadzanie argumentu wyszukiwania zakresu VLOOKUP
WYSZUKAJ.PIONOWO„s Zakres wyszukiwania Argument jest wartością logiczną (PRAWDZIWE lub FAŁSZYWY), który wskazuje, czy chcesz WYSZUKAJ.PIONOWO znaleźć dokładne lub przybliżone dopasowanie do Wartość_skrzynka.
- Jeśli PRAWDZIWE lub jeśli ten argument zostanie pominięty: WYSZUKAJ.PIONOWO zwraca dokładne dopasowanie do Wartość_skrzynkaLub, jeśli nie znaleziono dokładnego dopasowania, WYSZUKAJ.PIONOWO zwraca następną największą wartość. Aby uzyskać wzór, dane w pierwszej kolumnie Table_array muszą być posortowane w porządku rosnącym.
- Jeśli FAŁSZYWY: WYSZUKAJ.PIONOWO użyje tylko ścisłego dopasowania do Wartość_skrzynka. Jeśli są dwie lub więcej wartości w pierwszej kolumnie Table_array które pasują do wartości wyszukiwania, używana jest pierwsza znaleziona wartość. Jeśli nie znaleziono dokładnego dopasowania, a # N / A błąd jest zwracany.
W tym samouczku, ponieważ szukamy konkretnych informacji o konkretnym elemencie sprzętu, ustawimy go Zakres wyszukiwania równy Fałszywy.
- Kliknij na Zakres wyszukiwania linia w oknie dialogowym.
- Wpisz słowo Fałszywy w tej linii, aby wskazać, że chcemy WYSZUKAJ.PIONOWO aby zwrócić dokładne dopasowanie danych, których szukamy.
- Kliknij dobrze aby uzupełnić formułę wyszukiwania i zamknąć okno dialogowe.
- Ponieważ nie wprowadziliśmy jeszcze kryteriów wyszukiwania komórka D2 na # Błąd N / A będzie obecny w komórka E2.
- Ten błąd zostanie poprawiony, gdy dodamy kryteria wyszukiwania w ostatnim kroku samouczka.
Kopiowanie formuły odnośnika
Formuła odnośnika służy do pobierania danych z wielu kolumn tabeli danych naraz. Aby to zrobić, formuła odnośnika musi znajdować się we wszystkich polach, z których chcemy uzyskać informacje.
W tym samouczku chcemy, aby pobierał dane z kolumny 2, 3, i 4 tabeli danych - jest to cena, numer części i nazwa dostawcy po wprowadzeniu częściowej nazwy jako Wartość_skrzynka.
Ponieważ dane są ułożone w regularnym wzorze w arkuszu, możemy skopiować formułę wyszukiwania w komórka E2 do komórki F2 i G2.
Po skopiowaniu formuły program Excel zaktualizuje względne odwołanie do komórki w pliku KOLUMNA funkcja (komórka B1), aby odzwierciedlić nową lokalizację formuły.
Ponadto program Excel nie zmienia bezwzględnego odwołania do komórki $ D 2 $ i nazwany zakres Stół gdy formuła jest kopiowana.
Istnieje więcej niż jeden sposób kopiowania danych w programie Excel, ale prawdopodobnie najprostszym sposobem jest użycie Wypełnij uchwyt.
- Kliknij komórka E2, gdzie znajduje się formuła odnośnika, aby stała się aktywną komórką.
- Umieść Wskaźnik myszy nad czarny kwadrat w prawym dolnym rogu - wskaźnik zmieni się na znak plus, oznaczający uchwyt napełniania.
- Kliknij lewym przyciskiem myszy i przeciągnij uchwyt wypełniania przez do komórka G2.
- Wydanie przycisk myszy i komórka F3 powinien zawierać dwuwymiarową formułę wyszukiwania.
- Jeśli zrobione poprawnie, komórki F2 i G2 powinien teraz zawierać także # N / A błąd występujący w komórka E2.
Wprowadzanie kryteriów wyszukiwania
Po skopiowaniu formuły odnośnika do wymaganych komórek można użyć jej do pobrania informacji z tabeli danych. Aby to zrobić, wpisz nazwa przedmiotu chcesz odzyskać do Wartość_skrzynka komórka (D2) i naciśnij Wchodzić klawisz na klawiaturze.
Po wykonaniu każda komórka zawierająca formułę wyszukiwania powinna zawierać inną część danych o szukanym elemencie sprzętowym.
- Kliknij komórka D2 w arkuszu roboczym.
- Rodzaj Widget w komórka D2 i naciśnij Wchodzić klawisz na klawiaturze.
- Poniższe informacje powinny być wyświetlane w komórki 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
- Przetestuj WYSZUKAJ.PIONOWO formuła tablicowa dalej, wpisując nazwę innych części do komórka D2 i obserwując wyniki w komórki E2 do G2
