Za pomocą formuły tablicowej w programie Excel można utworzyć formułę odnośnika, która używa wielu kryteriów, aby znaleźć informacje w bazie danych lub tabeli danych. Formuła tablicy obejmuje zagnieżdżanie funkcji MATCH wewnątrz funkcji INDEX.
Ten samouczek zawiera przykład krok po kroku tworzenia formuły odnośnika, która używa wielu kryteriów, aby znaleźć dostawcęWidżety tytanowe w przykładowej bazie danych. Postępuj zgodnie ze wskazówkami, jak łatwo utworzyć własne niestandardowe formuły odnośników.
Przygotuj swój skoroszyt programu Excel za pomocą danych
Aby wykonać kroki opisane w tym samouczku, zacznij od wprowadzenia przykładowych danych do następujących komórek, jak pokazano na powyższym obrazku. Wiersze 3 i 4 są puste, aby uwzględnić formułę tablicową utworzoną podczas tego samouczka.
- Wprowadź najwyższy zakres danych w komórkach D1 do F2
- Wprowadź drugi zakres do komórek D5 do F11
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 tych opisanych powyżej są dostępne w tym samouczku Basic Excel Formatting.
02 z 08Utwórz funkcję INDEX w programie Excel
The INDEKS Funkcja jest jedną z niewielu w programie Excel, który ma wiele formularzy. Funkcja ma Formularz Array i a Formularz referencyjny. Formularz Array zwraca rzeczywiste dane z bazy danych lub tabeli danych, natomiast formularz referencyjny podaje odwołanie do komórki lub lokalizację danych w tabeli.
W tym samouczku wykorzystamy formularz Array, ponieważ chcemy poznać nazwę dostawcy dla widżetów tytanowych, a nie odwołanie do tego dostawcy w naszej bazie danych. Wykonaj następujące kroki, aby utworzyć funkcję INDEX:
- Kliknij komórkę F3 aby stała się aktywną komórką - tutaj wejdziemy w funkcję zagnieżdżoną.
- Kliknij na Formuły kartę menu wstążki.
- Wybierać Wyszukiwanie i odniesienie z paska wstążki, aby otworzyć rozwijaną funkcję.
- Kliknij INDEKS na liście, aby wywołać Wybierz Argumenty Okno dialogowe.
- Wybierz array, row_num, col_num opcja w oknie dialogowym.
- Kliknij dobrze aby otworzyć okno dialogowe funkcji INDEX; to otworzy Konstruktor formuł w Excelu.
- w Kreator formuł, Kliknij na Szyk linia.
- Zaznacz komórki D6 do F11, klikając i przeciągając, w arkuszu roboczym, aby wprowadzić zakres do konstruktora.
W starszych wersjach programu Excel Kreator formuł zostanie zastąpiony przez Argumenty funkcji okno. Zastosuj te same kroki, które wymieniono w tym samouczku do okna.
03 z 08Uruchom zagnieżdżoną funkcję MATCH
Podczas zagnieżdżania jednej funkcji w innej nie można otworzyć kreatora formuł drugiej lub zagnieżdżonej funkcji o wprowadź niezbędne argumenty. Funkcja zagnieżdżona musi być wpisana jako jedna z posortowanych w pierwszej funkcji.
Podczas ręcznego wprowadzania funkcji argumenty funkcji są oddzielane przecinkiem.
Pierwszym krokiem do wprowadzenia zagnieżdżonej funkcji MATCH jest wejście do Wartość_skrzynka argument. Wartość szukana będzie lokalizacją lub odwołaniem do komórki dla szukanego terminu, który chcemy dopasować do bazy danych.
Zwykle wartość klasy Lookup przyjmuje tylko jedno kryterium lub termin. Aby wyszukać wiele kryteriów, musimy rozszerzyć wartość Lookup_v; tjego działanie polega na łączeniu lub łączeniu dwóch lub więcej odwołań do komórek razem przy użyciu symbolu ampersand - &.
- w Kreator formuł, Kliknij na Wiersz_num linia.
- Wpisz nazwę funkcji MECZ a następnie otwarty nawias okrągły.
- Kliknij komórkę D3 aby wprowadzić odwołanie do komórki w oknie dialogowym.
- Wpisz ampersand po odwołaniu do komórki D3 w celu dodania drugiego odwołania do komórki.
- Kliknij komórkę E3 aby wprowadzić drugie odwołanie do komórki.
- Wpisz przecinek po odwołaniu do komórki E3 aby ukończyć wpis funkcji MATCH Wartość_skrzynka argument.
W ostatnim kroku samouczka, nasz Wartości_skrzynki zostaną wprowadzone do komórek D3 i E3 arkusza roboczego.
04 z 08Wypełnij Zagnieżdżoną funkcję MATCH
Ten krok obejmuje dodanie Lookup_array argument dla zagnieżdżonego MECZ funkcjonować. Pole Lookup_tray jest zakresem komórek, które funkcja MATCH wyszuka, aby znaleźć argument Lookup_value dodany w poprzednim kroku samouczka.
Ponieważ zidentyfikowaliśmy dwa pola wyszukiwania w argumencie Lookup_array, musimy zrobić to samo dla pola Lookup_array. Funkcja MATCH wyszukuje tylko jedną tablicę dla każdego podanego terminu, a więc wprowadza wiele tablic, z których ponownie korzystamy ze znaków ampersand, aby połączyć te tablice razem.
Te kroki należy wprowadzić po przecinku wprowadzonym w poprzednim kroku na Wiersz_num linia w INDEKS funkcjonować.
- Kliknij na Wiersz_num linia po przecinku, aby umieścić punkt wstawiania na końcu bieżącego wpisu.
- Zaznacz komórki D6 do D11 w arkuszu roboczym do wprowadzenia zakresu - jest to pierwsza tablica, której funkcją jest wyszukiwanie.
- Wpisz ampersand po odwołaniach do komórek D6: D11 ponieważ chcemy, aby funkcja przeszukiwała dwie tablice.
- Zaznacz komórki E6 do E11 w arkuszu roboczym do wprowadzenia zakresu - jest to druga tablica, której funkcją jest wyszukiwanie.
- Wpisz przecinek po odwołaniu do komórki E3 w celu uzupełnienia wpisu MECZ Funkcje Lookup_array argument.
- Zostawić INDEKS okno dialogowe funkcji otwarte dla następnego kroku w samouczku.
Dodaj argument typu MATCH
Trzecim i ostatnim argumentem funkcji MATCH jest Match_type argument; informuje program Excel, jak dopasować Wartość_skrzynka z wartościami w panelu Lookup_tray - dostępne opcje to 1, 0 lub -1.
Ten argument jest opcjonalny. Jeśli zostanie pominięty, funkcja używa wartości domyślnej 1.
- Jeśli Match_type = 1 lub jest pominięty: MATCH znajduje największą wartość, która jest mniejsza lub równa wartości szukanego_znaku. Dane Lookup_array muszą być posortowane w porządku rosnącym.
- Jeśli Match_type = 0: MATCH znajduje pierwszą wartość, która jest dokładnie równa wartości Lookup_value. Dane Lookup_array można sortować w dowolnej kolejności.
- Jeśli Match_type = -1: MATCH znajduje najmniejszą wartość, która jest większa lub równa wartości Lookup_value. Dane Lookup_array muszą być posortowane w porządku malejącym.
Te kroki należy wprowadzić po przecinku wprowadzonym w poprzednim kroku na Wiersz_num linia w INDEKS funkcjonować.
- Po przecinku na Wiersz_num linia, wpisz zero - 0 - ponieważ chcemy, aby funkcja zagnieżdżona zwracała dokładne dopasowania do terminów wprowadzonych w komórkach D3 i E3.
- Wpisz okrągły nawias okrągły ) - aby ukończyć MECZ funkcjonować.
- Zostawić Kreator formuł otwórz następny krok w samouczku.
Kończenie funkcji INDEX
Teraz, gdy MECZ funkcja zostanie zakończona przeniesiemy do trzeciej linii Konstruktora formuł i wprowadzimy ostatni argument dla INDEKS funkcjonować. Trzecim i ostatnim argumentem jest Column_num argument, który informuje program Excel o numerze kolumny w zakresie D6 do F11; to tutaj znajdzie informacje, które chcemy zwrócić przez funkcję. W tym przypadku dostawca dla widgety tytanowe.
- Kliknij na Column_num linia.
- Wprowadź numer trzy - 3 - w tej linii, ponieważ szukamy danych w trzeciej kolumnie zakresu D6 do F11.
- Ponownie opuść Kreator formuł otwórz następny krok w samouczku.
Tworzenie formuły macierzy
Przed zamknięciem Kreator formuł, musimy włączyć naszą funkcję zagnieżdżoną do formuły tablicowej; to właśnie pozwala naszej funkcji na wyszukiwanie wielu terminów w tabeli danych. W tym samouczku staramy się dopasować dwa terminy: Widżety z kolumny 1 i Tytan z kolumny 2.
Tworzenie formuły tablicowej w Excelu odbywa się przez naciśnięcie klawisza CTRL, PRZESUNIĘCIE, i WCHODZIĆ klawisze na klawiaturze w tym samym czasie. Po naciśnięciu funkcja będzie otoczona nawiasami klamrowymi, co oznacza, że funkcja jest teraz tablicą.
- Zamknij Kreator formuł klikając przycisk Gotowe przycisk.
- Następnie wybierz komórkę F4 i naciśnij Wchodzić klawisz, aby wyświetlić formułę.
- Aby przekonwertować wzór na tablicę, jednocześnie naciśnij CTRL + SHIFT + ENTER na klawiaturze.
- Jeśli zrobione poprawnie a # N / A błąd pojawi się w komórce F3 - komórce, w której wprowadziliśmy funkcję.
- The # N / A błąd pojawia się w komórce F3 ponieważ komórki D3 i E3 są puste. D3 i E3 to komórki, w których powiedzieliśmy funkcję, aby znaleźć Wartości_skrzynki. Po dodaniu danych do tych dwóch komórek błąd zostanie zastąpiony informacjami z bazy danych.
Dodanie kryteriów wyszukiwania
Na koniec dodamy wyszukiwane terminy do naszego arkusza roboczego. Jak wspomniano w poprzednim kroku, staramy się dopasować warunki Widżety z kolumny 1 i Tytan z kolumny 2. Jeśli i tylko jeśli nasza formuła znajdzie dopasowanie dla obu terminów w odpowiednich kolumnach w bazie danych, zwróci wartość z trzeciej kolumny.
- Kliknij komórkę D3.
- Rodzaj Widżety i naciśnij Wchodzić klawisz na klawiaturze.
- Kliknij komórkę E3.
- Rodzaj Tytan i naciśnij Wchodzić klawisz na klawiaturze.
- Nazwa dostawcy Widgets Inc. powinien pojawić się w komórce F3 - lokalizacja funkcji, ponieważ jest to jedyny dostawca, który sprzedaje gadżety Titanium.
- Po kliknięciu komórki F3 pełna funkcja pojawia się na pasku formuły nad arkuszem.
{= INDEX (D6: F11, MATCH (D3 i E3, D6: D11 i E6: E11, 0), 3)}
W naszym przykładzie był tylko jeden dostawca widżetów tytanowych. Jeśli istnieje więcej niż jeden dostawca, dostawca wymieniony jako pierwszy w bazie danych jest zwracany przez funkcję.
