W tym artykule dowiemy się, jak wyodrębnić indeks kolumn z tabeli w programie Excel.
Scenariusz:
Wiele razy, pracując z długimi rozproszonymi danymi, musimy najpierw wyczyścić dane, zanim zaczniemy nad nimi pracować. Zajmuje to trochę czasu, a chciałeś po prostu wyodrębnić kilka zapytań. Aby wyodrębnić dane, zazwyczaj używasz funkcji WYSZUKAJ.PIONOWO. Ale gdy mamy długie dane z wieloma polami kolumn, robi się bałagan, ponieważ funkcja WYSZUKAJ.PIONOWO wymaga prawidłowego indeksu kolumny jako liczby, w przeciwnym razie formuła zwraca błąd. Właśnie tam, poniżej znajduje się wyjaśnienie formuły tego rodzaju problemu.
Jak rozwiązać problem?
W tym celu użyjemy funkcji DOPASUJ. Jeśli nie słyszałeś o tej funkcji, przyzwyczaj się do niej. Jest to funkcja wyszukiwania programu Excel, która zwraca indeks wartości wyszukiwania w tablicy. Tutaj musimy uzyskać numer indeksu nazwy kolumny. Następnie przejdziemy do kolejnego kroku Jak wyszukać wartości w tabelach za pomocą funkcji PODAJ.POZYCJĘ. Poniżej znajduje się wzór ogólny
Wzór ogólny:
| = DOPASUJ(nazwa_kolumny, nagłówek_tabeli, 0) |
nazwa_kolumny : wartość wyszukiwania
table_header : tablica nagłówków tabeli
0 : wyszukaj dokładne dopasowanie
Przykład :
Wszystko to może być trudne do zrozumienia. Rozumiemy formułę z wyjaśnieniem i przykładem. Tutaj mamy tabelę danych w arkuszu 1 ($ A 1: $ U 9995). Mamy więc nagłówek tabeli jako A1:U1 (pierwszy wiersz tabeli).
Zobaczmy poniższą formułę do zastosowania na stole.
Użyj formuły
| =DOPASUJ(C4,Arkusz1!$A$1:$U$1,0) |
Wyjaśnienie:
- Funkcja MATCH wyszukuje wartość w komórce C4 „Identyfikator zamówienia”
- Arkusz1! $ A $ 1: $ U $ 1 to argument tablicy wyszukiwania.
- Podano 0 argumentów, aby wyszukać dokładne dopasowanie.
Jak widać indeks kolumny Order ID w tabeli to 2.. Ale używanie nagłówka tabeli jako pełnego skrótu jest bardzo irytujące, więc używamy nazwanego zakresu dla tablicy nagłówków tabeli. Dowiedz się więcej o nazwanych zakresach tutaj. Nazwany zakres używany dla nagłówka tabeli (Arkusz1!$A$1:$U$1) to „nagłówek”.
Użyj formuły.
| = PODAJ.POZYCJĘ(C4,nagłówek;0) |
Tutaj powyższy obrazek wyjaśnia dwie rzeczy. Pierwsza to kolumna indeksu stanu w tabeli to 11, a druga to nazwany zakres "nagłówek" działa dobrze. Skopiuj formułę dla pozostałych nazw kolumn, używając Ctrl + D lub przeciągając w dół od prawej dolnej krawędzi używaną komórkę.
Tutaj mamy całą kolumnę Index. Teraz możemy użyć jako danych wejściowych do funkcji WYSZUKAJ.PIONOWO, jak pokazano poniżej.
Indeks MATCH w funkcji WYSZUKAJ.PIONOWO:
Teraz mamy rozwiązanie, jak uzyskać indeks kolumn tabeli. Możemy użyć formuły jako danych wejściowych do funkcji WYSZUKAJ.PIONOWO. Na przykład potrzebujemy nazwy produktu kupionej przez klienta „Pete Kriz”.
Użyj wzoru:
| =WYSZUKAJ.PIONOWO(D10;Tabela;PODSTAW(E9;nagłówek;0);0) |
Uwaga: upewnij się, że wartość wyszukiwania w D10 (Pete Kriz) musi znajdować się w pierwszej kolumnie tabeli.
Jak widać formuła zwraca nazwę produktu z nazwy klienta w tabeli. Generalnie nie używamy funkcji MATCH z funkcją WYSZUKAJ.PIONOWO, ponieważ wartość wyszukiwania musi znajdować się w pierwszej kolumnie, co rzadko się zdarza. Używamy więc kombinacji funkcji INDEX i PODAJ.POZYCJĘ. Dowiedz się więcej o tym, jak wyszukać wartość za pomocą funkcji INDEKS i PODAJ.POZYCJĘ.
Oto wszystkie uwagi obserwacyjne dotyczące stosowania wzoru.
Uwagi:
- Formuła działa zarówno dla tekstu, jak i liczb.
- Funkcja zwraca błąd #NA, jeśli argument tablicy wyszukiwania funkcji PODAJ.POZYCJĘ nie ma tej samej długości co tablica tabeli.
- Formuła zwraca błąd, jeśli szukana_wartość nie jest zgodna z wartością w tabeli szukana_tablica.
- Funkcja dopasowuje dokładną wartość, ponieważ argument typu dopasowania do funkcji PODAJ.POZYCJĘ wynosi 0.
- Użyj argumentu -1 dla mniej niż, 0 dla dokładnego dopasowania i 1 dla większego niż dopasowanie wyszukiwania.
- Wartości wyszukiwania można podać jako odwołanie do komórki lub bezpośrednio za pomocą symbolu cudzysłowu ( " ) w formule jako argumentów.
Mam nadzieję, że zrozumiałeś Jak wyodrębnić indeks kolumn z tabeli w programie Excel. Zapoznaj się z dodatkowymi artykułami na temat wartości wyszukiwania programu Excel i funkcji programu Excel 2019 tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Użyj INDEKSU i DOPASUJ, aby wyszukać wartość : Funkcja INDEX & MATCH do wyszukiwania wartości zgodnie z wymaganiami.
Zakres SUMA z INDEKSEM w Excelu : Użyj funkcji INDEX, aby znaleźć SUMA wartości zgodnie z wymaganiami.
Jak korzystać z funkcji INDEKS w programie Excel? : Znajdź indeks tablicy za pomocą funkcji INDEX wyjaśnionej na przykładzie.
Jak korzystać z funkcji PODAJ.POZYCJĘ w programie Excel : Znajdź pozycję PODAJ.POZYCJĘ w tablicy przy użyciu wartości INDEX wewnątrz funkcji PODAJ.POZYCJĘ wyjaśnionej na przykładzie.
Jak korzystać z funkcji WYSZUKAJ w programie Excel : Znajdź wartość wyszukiwania w tablicy za pomocą funkcji WYSZUKAJ wyjaśnionej na przykładzie.
Popularne artykuły:
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.