Ten samouczek nauczy Cię, jak używać kombinacji INDEKS I DOPASUJ do wykonywania wyszukiwań w Excelu i Arkuszach Google.
INDEKS I DOPASOWANIE, Idealna Para
Przyjrzyjmy się bliżej niektórym sposobom łączenia funkcji INDEKS i PODAJ.POZYCJĘ. Funkcja PODAJ.POZYCJĘ służy do zwracania względnej pozycji elementu w tablicy, podczas gdy funkcja INDEKS może pobrać element z tablicy o określonej pozycji. Ta synergia między nimi pozwala im wykonywać prawie każdy rodzaj wyszukiwania, którego możesz potrzebować.
Kombinacja INDEX / MATCH była historycznie używana jako zamiennik funkcji WYSZUKAJ.PIONOWO. Jednym z głównych powodów jest możliwość wyszukiwania w lewo (patrz następna sekcja).
Uwaga: nowa funkcja XLOOKUP może teraz wykonywać wyszukiwania lewostronne.
Wyszukaj w lewo
Skorzystajmy z poniższej tabeli statystyk koszykówki:
Chcemy znaleźć gracza Boba #. Ponieważ gracz # znajduje się po lewej stronie kolumny nazwy, nie możemy użyć funkcji WYSZUKAJ.PIONOWO.
Zamiast tego moglibyśmy wykonać podstawowe żądanie DOPASUJ, aby obliczyć wiersz Boba
=DOPASUJ(H2, B2:B5; 0)
Spowoduje to wyszukanie dokładnego dopasowania słowa „Bob”, a więc nasza funkcja zwróci liczbę 2, ponieważ „Bob” znajduje się w 2NS pozycja.
Następnie możemy użyć funkcji INDEX, aby zwrócić odtwarzacz #, odpowiadający wierszowi. Na razie wpiszmy ręcznie „2” do funkcji:
=INDEKS(A2:A5; 2)
Tutaj INDEKS odwołuje się do A3, ponieważ jest to 2NS komórki w zakresie A2:A5 i zwróć wynik 42. W celu osiągnięcia naszego ogólnego celu możemy połączyć te dwa elementy w:
=INDEKS(A2:A5; PODAJ.POZYCJĘ(H2;B2:B5;0))
Zaletą jest to, że byliśmy w stanie zwrócić wynik z kolumny po lewej stronie miejsca, w którym szukaliśmy.
Wyszukiwanie dwuwymiarowe
Spójrzmy na nasz stół sprzed:
Tym razem jednak chcemy uzyskać konkretną statystykę. Wołaliśmy, że chcemy wyszukać zbiórki w komórce H1. Zamiast pisać kilka instrukcji JEŻELI, aby określić, z której kolumny uzyskać wynik, możesz ponownie użyć funkcji PODAJ.POZYCJĘ. Funkcja INDEX pozwala określić wartość wiersza i wartość kolumny. Dodamy tutaj kolejną funkcję PODAJ.POZYCJĘ, aby określić, którą kolumnę chcemy. To będzie wyglądać
=DOPASUJ(H1, A1:E1, 0)
Nasza komórka w H1 to rozwijane menu, które pozwala nam wybrać kategorię, której szukamy, a następnie nasza komórka MATCH określa, do której kolumny w tabeli należy. Podłączmy ten nowy bit do naszej poprzedniej formuły. Zauważ, że musimy dostosować pierwszy argument, aby był dwuwymiarowy, ponieważ nie chcemy już tylko wyniku z kolumny A.
=INDEKS(A2:E5; PODAJ.POZYCJĘ(H2, B2:B5; 0); PODAJ.POZYCJĘ(H1, A1:E1; 0))
W naszym przykładzie chcemy znaleźć zbiórki dla Charliego. Nasza formuła oceni to w następujący sposób:
=INDEKS(A2:E5, PODAJ.POZYCJĘ("Karol", B2:B5, 0), PODAJ.POZYCJĘ("Zebrania", A1:E1, 0)) =INDEKS(A2:E5, 3, 4) =D4 =6
Stworzyliśmy teraz elastyczną konfigurację, która umożliwia użytkownikowi pobranie dowolnej wartości z naszej tabeli bez konieczności pisania wielu formuł lub rozgałęziających instrukcji IF.
Wiele sekcji
Nie jest często używany, ale INDEKS ma piąty argument, który można podać, aby określić, który powierzchnia w argumencie jeden do użycia. Oznacza to, że potrzebujemy sposobu na przekazanie wielu obszarów do pierwszego argumentu. Możesz to zrobić, używając dodatkowego zestawu nawiasów. Ten przykład ilustruje, jak można pobrać wyniki z różnych tabel w arkuszu za pomocą funkcji INDEKS.
Oto układ, którego będziemy używać. Mamy statystyki dla trzech różnych kwartałów gry.
W komórkach H1: H3 stworzyliśmy listy rozwijane sprawdzania poprawności danych dla naszych różnych wyborów. Lista rozwijana dla kwartału pochodzi z J2:J4. Wykorzystamy to do innego polecenia MATCH, aby określić, którego obszaru użyć. Nasz wzór w H4 będzie wyglądał tak:
=INDEKS((A3:E6, A10:E13, A17:E20), PODAJ.POZYCJĘ(H2, B3:B6, 0), PODAJ.POZYCJĘ(H1, A2:E2, 0), PODAJ.POZYCJĘ(H3, J2:J4, 0))
Omówiliśmy już, jak działają dwie wewnętrzne funkcje PODAJ.POZYCJĘ, więc skupmy się na pierwszym i ostatnim argumencie:
=INDEKS((A3:E6, A10:E13, A17:E20),… , PODAJ.POZYCJĘ(H3, J2:J4, 0))
W pierwszym argumencie przydzieliliśmy funkcji INDEKS wiele tablic, umieszczając je wszystkie w nawiasach. Innym sposobem, w jaki możesz to zrobić, jest użycie formuły - zdefiniuj nazwę. Możesz zdefiniować nazwę o nazwie „Moje Tabele” z definicją
=INDEKS(MojaTabela,POZYCJA(H2,Tabela1347[Nazwa],0),POZYCJA(H1,Tabela1347[#Nagłówki],0),POZYCJA(H3,J2:J4,0))
Wróćmy do całego stwierdzenia. Nasze różne funkcje DOPASUJ dokładnie wskażą funkcji INDEX, gdzie szukać. Najpierw ustalimy, że „Charlie” to 3r & D wiersz. Następnie chcemy „Zbiórki”, czyli 4NS kolumna. Wreszcie ustaliliśmy, że chcemy uzyskać wynik z 2NS Tabela. Formuła oceni to w ten sposób:
=INDEKS((A3:E6, A10:E13, A17:E20), PODAJ.POZYCJĘ(H2, B3:B6, 0), PODAJ.POZYCJĘ(H1, A2:E2, 0), PODAJ.POZYCJĘ(H3, J2:J4, 0)) =INDEKS((A3:E6, A10:E13, A17:E20), 3, 4, 2) =INDEKS(A10:E13, 3, 4) =D13 =14
Jak wspomnieliśmy na początku tego przykładu, ograniczasz się do tego, aby tabele znajdowały się w tym samym arkuszu. Jeśli potrafisz napisać prawidłowe sposoby informowania indeksu, z którego wiersza, kolumny i/lub obszaru chcesz pobrać dane, INDEX będzie ci bardzo dobrze służył.
Arkusze Google -INDEKS I DOPASUJ
Wszystkie powyższe przykłady działają dokładnie tak samo w Arkuszach Google, jak w Excelu.
