Uwaga: Poniższy artykuł pomoże Ci w: Kiedy używać funkcji dopasowania indeksu zamiast funkcji WYSZUKAJ.PIONOWO w programie Excel
Ci z was, którzy są dobrze zorientowani w przewyższać prawdopodobnie znają tę funkcję WYSZUKAJ.PIONOWO. (WYSZUKAJ.PIONOWO) Funkcja WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) służy do wyszukiwania wartości w innej komórce na podstawie pasującego tekstu w tym samym wierszu.
Jeśli nie znasz funkcji WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) , możesz przeczytać mój poprzedni post na temat korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel (jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel).
Bez względu na to, jaka jest potężna, Funkcja WYSZUKAJ.PIONOWO(VLOOKUP) ma ograniczenie dotyczące struktury odpowiedniej tabeli wyszukiwania, aby formuła działała.
Ten artykuł pokaże Ci ograniczenia, w ramach których nie możesz korzystać z funkcji WYSZUKAJ.PIONOWO i zaprezentuje inną funkcję w (WYSZUKAJ.PIONOWO)przewyższać zatytułowany DOPASOWANIE DO INDEKSU(INDEX-MATCH) , co może rozwiązać ten problem.
INDEKS DOPASUJ Przykład Excela
Korzystając z poniższego przykładu arkusza kalkulacyjnego Excel, mamy listę nazwisk właścicieli samochodów i nazwę samochodu. W tym przykładzie postaramy się uzyskać identyfikator pojazdu(Identyfikator samochodu) na podstawie modelu samochód(Model samochodu) wymieniony pod wieloma właścicielami, jak pokazano poniżej:
Na osobnym arkuszu o nazwie typ samochodu mamy prostą bazę danych samochodów z identyfikator(ID) , model samochodu(Model samochodu) i kolor(kolor) .
Przy tym ustawieniu stołu funkcja WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) może działać tylko wtedy, gdy dane, które chcemy pobrać, znajdują się w kolumnie po prawej stronie tego, co próbujemy dopasować (pole ” Model samochodu “).(model samochodu)
Innymi słowy, przy tej strukturze tabeli, ponieważ staramy się ją odwzorować na podstawie modelu samochód(Model samochodu), jedyne informacje, które możemy uzyskać, to kolor(Kolor) (nie identyfikator(ID) jako kolumna identyfikator(ID) znajduje się po lewej stronie kolumny modele samochodów).(model samochodu)
Wynika to z faktu, że w WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) wartość odnośnika powinna być wyświetlana w pierwszej kolumnie, a kolumny odnośnika powinny znajdować się po prawej stronie. Żaden z tych warunków nie jest spełniony w naszym przykładzie.
Dobrą wiadomością jest to, że DOPASOWANIE DO INDEKSU może nam w tym pomóc. W praktyce jest to właściwie połączenie dwóch funkcji przewyższać które mogą działać indywidualnie: funkcja INDEKS i funkcja (INDEKS)MECZ(MECZ) .
Jednak w ramach tego artykułu porozmawiamy tylko o ich połączeniu w celu odtworzenia funkcji WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) .
Na początku formuła może wydawać się nieco długa i onieśmielająca. Jednak po kilkukrotnym użyciu nauczysz się składni na pamięć.
Oto pełna formuła w naszym przykładzie:
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
Oto podział dla każdej sekcji
=INDEKS( — „=” wskazuje początek formuły w komórce, a INDEKS(INDEX) to pierwsza część funkcji, której używamy przewyższać .
Typ samochodu!$A$2:$A$5 – kolumny na arkuszu typ samochodu, gdzie zawarte są dane, które chcielibyśmy otrzymać. W tym przykładzie identyfikator(ID) każdego modele samochodów.(Model samochodu.)
MECZ((MATCH() to druga część funkcji) przewyższać którego używamy.
B4 to komórka zawierająca wyszukiwany tekst, którego używamy ( Model samochodu(Model samochodu) .
CarType! $B$2:$B$5 – kolumny na arkuszu typ samochodu z danymi, których użyjemy do dopasowania do wyszukiwanego tekstu.
0)) – Aby określić, że wyszukiwany tekst musi dokładnie odpowiadać tekstowi w odpowiedniej kolumnie (tj. CarType! $B$2:$B$5 ). Jeśli nie zostanie znalezione dokładne dopasowanie, formuła zwraca #Nie dotyczy .
Uwaga: pamiętaj o podwójnym nawiasie zamykającym na końcu tej funkcji „))” i przecinkach między argumentami.(Uwaga: pamiętaj o podwójnym nawiasie zamykającym na końcu tej funkcji „))” i przecinkach między argumentami.)
Osobiście odmówiłem WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) i teraz użyj INDEX-MATCH, ponieważ może zrobić więcej niż WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) .
Funkcje DOPASOWANIE DO INDEKSU(INDEX-MATCH) mają również inne zalety w stosunku do WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) :
- Szybsze obliczenia(Szybsze obliczenia)
Kiedy pracujemy z dużymi zbiorami danych, gdzie samo obliczenie może zająć dużo czasu ze względu na wiele funkcji WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) , po zastąpieniu wszystkich tych formuł przez INDEKS WYSZUKIWANIA(INDEX-MATCH) ogólne obliczenia będą szybsze.
- Nie trzeba liczyć względnych kolumn(Nie ma potrzeby liczenia kolumn względnych)
Jeśli w naszej tabeli przeglądowej kluczowy tekst, który chcemy znaleźć, znajduje się w kolumnie C a dane, które musimy uzyskać, znajdują się w kolumnie AQ będziemy musieli wiedzieć/policzyć, ile kolumn znajduje się między kolumną C a kolumną AQ, gdy używasz WYSZUKAJ.PIONOWO.(WYSZUKAJ.PIONOWO) .
Z funkcjami DOPASOWANIE DO INDEKSU(INDEX-MATCH) możemy bezpośrednio wybrać kolumnę indeksu (tj. kolumnę AQ), w której potrzebujemy uzyskać dane i wybrać kolumnę do dopasowania (tj. kolumnę C).
- Wygląda ciężej(Wygląda bardziej skomplikowanie)
WYSZUKAJ.PIONOWO(WYSZUKAJ.PIONOWO) jest obecnie dość powszechny, ale niewiele osób wie o łącznym użyciu funkcji INDEX-MATCH.
Dłuższa linia w funkcji DOPASOWANIE DO INDEKSU(INDEX-MATCH) pomaga wyglądać jak ekspert dzięki złożonym i zaawansowanym funkcjom przewyższać . Cieszyć się!
Ci z Was, którzy dobrze znają się na Excelu, najprawdopodobniej bardzo dobrze znają WYSZUKAJ.PIONOWO funkcjonować. The WYSZUKAJ.PIONOWO Funkcja służy do znajdowania wartości w innej komórce na podstawie pasującego tekstu w tym samym wierszu.
Jeśli nadal jesteś nowy w WYSZUKAJ.PIONOWO funkcji, możesz sprawdzić mój poprzedni post na temat korzystania z funkcji WYSZUKAJ.PIONOWO w programie Excel.
tak potężny, jak jest, WYSZUKAJ.PIONOWO ma ograniczenia dotyczące struktury pasującej tabeli referencyjnej, aby formuła działała.
Ten artykuł pokaże Ci ograniczenia, gdzie WYSZUKAJ.PIONOWO nie można użyć i wprowadzić inną funkcję w Excelu o nazwie DOPASOWANIE DO INDEKSU które mogą rozwiązać problem.
INDEKS DOPASUJ Przykład Excela
Korzystając z poniższego przykładowego arkusza kalkulacyjnego Excel, mamy listę właścicieli samochodów i nazwę samochodu. W tym przykładzie postaramy się uchwycić identyfikator samochodu na podstawie model samochodu wymienione pod wieloma właścicielami, jak pokazano poniżej:
Na osobnym arkuszu o nazwie typ samochodumamy prostą bazę danych samochodów z ID, model samochodu oraz kolor.
Przy tej konfiguracji stołu, WYSZUKAJ.PIONOWO funkcja może działać tylko wtedy, gdy dane, które chcemy pobrać, znajdują się w kolumnie po prawej stronie tego, co próbujemy dopasować (model samochodu pole).
Innymi słowy, przy tej strukturze tabeli, ponieważ próbujemy dopasować ją na podstawie model samochodujedyne informacje, jakie możemy uzyskać, to kolor (Nie ID jak ID kolumna znajduje się na lewo od model samochodu kolumna.)
Dzieje się tak, ponieważ w przypadku funkcji WYSZUKAJ.PIONOWO wartość wyszukiwania musi pojawić się w pierwszej kolumnie, a kolumny wyszukiwania muszą znajdować się po prawej stronie. Żaden z tych warunków nie jest spełniony w naszym przykładzie.
dobra wiadomość jest taka, DOPASOWANIE DO INDEKSU będą mogli nam w tym pomóc. W praktyce jest to połączenie dwóch funkcji Excela, które mogą działać indywidualnie: INDEKS funkcja i MECZ funkcjonować.
Jednak na potrzeby tego artykułu omówimy tylko połączenie tych dwóch w celu odtworzenia funkcji WYSZUKAJ.PIONOWO.
Formuła może początkowo wydawać się nieco długa i onieśmielająca. Jednak po kilkukrotnym użyciu nauczysz się składni na pamięć.
Oto pełna formuła w naszym przykładzie:
=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))
Oto podział dla każdej sekcji
=INDEKS( – „=” wskazuje początek formuły w komórce i INDEKS to pierwsza część funkcji Excela, której używamy.
Typ samochodu!$A$2:$A$5 – kolumny na arkuszu typ samochodu gdzie znajdują się dane, które chcielibyśmy odzyskać. W tym przykładzie ID każdego model samochodu.
MECZ( – Druga część funkcji Excela, z której korzystamy.
B4 – Komórka zawierająca wyszukiwany tekst, którego używamy (model samochodu).
CarType! $B$2:$B$5 – Kolumny na arkuszu typ samochodu z danymi, których użyjemy do dopasowania do tekstu wyszukiwania.
0)) – Aby wskazać, że wyszukiwany tekst musi dokładnie pasować do tekstu w pasującej kolumnie (tj CarType! $B$2:$B$5). Jeśli dokładne dopasowanie nie zostanie znalezione, formuła zwraca #Nie dotyczy.
Osobiście odszedłem od funkcji WYSZUKAJ.PIONOWO i teraz używam INDEX-MATCH, ponieważ jest on w stanie zrobić więcej niż WYSZUKAJ.PIONOWO.
The DOPASOWANIE DO INDEKSU funkcje mają również inne zalety w porównaniu do WYSZUKAJ.PIONOWO:
- Szybsze obliczenia
Gdy pracujemy z dużymi zestawami danych, w których samo obliczenie może zająć dużo czasu ze względu na wiele funkcji WYSZUKAJ.PIONOWO, zauważysz, że po zastąpieniu wszystkich tych formuł funkcją INDEX-MATCH, ogólne obliczenia będą obliczane szybciej.
- Nie ma potrzeby liczenia względnych kolumn
Jeśli nasza tabela referencyjna zawiera tekst klucza, który chcemy przeszukać w kolumnie C a dane, które musimy uzyskać, są w kolumnie AQbędziemy musieli wiedzieć/policzyć, ile kolumn znajduje się między kolumną C a kolumną AQ podczas korzystania z funkcji WYSZUKAJ.PIONOWO.
Za pomocą funkcji INDEX-MATCH możemy bezpośrednio wybrać kolumnę indeksu (tj. kolumnę AQ), w której musimy pobrać dane i wybrać kolumnę do dopasowania (tj. kolumnę C).
- Wygląda na bardziej skomplikowaną
WYSZUKAJ.PIONOWO jest obecnie dość powszechna, ale niewiele osób wie o używaniu funkcji INDEX-MATCH razem.
Dłuższy ciąg w funkcji INDEX-MATCH pomaga sprawić, że będziesz wyglądać jak ekspert w obsłudze złożonych i zaawansowanych funkcji programu Excel. Cieszyć się!
