Wyszukaj i pobierz całą kolumnę
Aby wyszukać i pobrać całą kolumnę, dopasowując określoną wartość, an INDEKS i MATCH formuła zrobi ci przysługę.
Wyszukaj i pobierz całą kolumnę na podstawie określonej wartości
Zsumuj całą kolumnę na podstawie określonej wartości
Dalsza analiza całej kolumny na podstawie określonej wartości
Wyszukaj i pobierz całą kolumnę na podstawie określonej wartości
Dostać lista sprzedaży w II kwartale zgodnie z powyższą tabelą można najpierw użyć funkcji PODAJ.POZYCJĘ, aby zwrócić pozycję sprzedaży w II kwartale, a która zostanie przekazana do INDEKSU w celu pobrania wartości na pozycji.
Ogólna składnia
=INDEX(return_range,0,MATCH(lookup_value,lookup_array,0))
√ Uwaga: To jest formuła tablicowa, która wymaga wprowadzenia za pomocą Ctrl + Shift + Wchodzę.
- zakres_zwrotów: Zakres, z którego formuła kombinacji ma zwracać listę sprzedaży z II kwartału. Tutaj odnosi się do zakresu sprzedaży.
- szukana_wartość: Wartość formuły kombinacji użytej do znalezienia odpowiednich informacji o sprzedaży. Tutaj odnosi się do danego kwartału.
- szukana_tablica: Zakres komórek, w których należy dopasować lookup_value. Tutaj odnosi się do nagłówków kwartałów.
- typ_dopasowania 0: Wymusza MATCH, aby znaleźć pierwszą wartość, która jest dokładnie równa lookup_value.
aby uzyskać lista sprzedaży w II kwartale, skopiuj lub wprowadź poniższą formułę w komórce I6, naciśnij Ctrl + Shift + Wchodzę, a następnie kliknij dwukrotnie komórkę i naciśnij F9 aby uzyskać wynik:
=INDEKS(C5:F11,0,PASUJ(„Q2”,C4:F4, 0))
Możesz też użyć odwołania do komórki, aby uczynić formułę dynamiczną:
=INDEKS(C5:F11,0,PASUJ(I5,C4:F4, 0))
Wyjaśnienie formuły
=INDEX(C5:F11,0,MATCH(I5,C4:F4,0))
- DOPASUJ(I5,C4:F4,0): Połączenia typ_dopasowania 0 wymusza, aby funkcja MATCH zwróciła pozycję Q2, wartość w I5, w zasięgu C4:F4, który jest 2.
- INDEKS(C5:F11, 0,DOPASUJ(I5,C4:F4,0)) = INDEKS(C5:F11, 0,2): Funkcja INDEX zwraca wszystkie wartości w 2druga kolumna zakresu C5:F11 w tablicy takiej jak ta: {7865;4322;8534;5463;3252;7683;3654}. Zwróć uwagę, że aby tablica była widoczna w programie Excel, należy dwukrotnie kliknąć komórkę, w której wprowadzono formułę, a następnie nacisnąć F9.
Zsumuj całą kolumnę na podstawie określonej wartości
Ponieważ mamy teraz pod ręką listę sprzedaży, aby uzyskać Całkowita wielkość sprzedaży w drugim kwartale byłby dla nas łatwym przypadkiem. Wystarczy dodać do formuły funkcję SUMA, aby zsumować wszystkie wartości sprzedaży z listy.
Ogólna składnia
=SUM(INDEX(return_range,0,MATCH(lookup_value,lookup_array,0)))
W tym przykładzie, aby uzyskać Całkowita wielkość sprzedaży w drugim kwartale, skopiuj lub wprowadź poniższą formułę w komórce I8 i naciśnij Wchodzę aby uzyskać wynik:
=SUMA(INDEKS(C5:F11,0,PASUJ(I5,C4:F4,0)))
Wyjaśnienie formuły
=SUM(INDEX(C5:F11,0,MATCH(I5,C4:F4,0)))
- DOPASUJ(I5,C4:F4,0): Połączenia typ_dopasowania 0 wymusza, aby funkcja MATCH zwróciła pozycję Q2, wartość w I5, w zasięgu C4:F4, który jest 2.
- INDEKS(C5:F11, 0,DOPASUJ(I5,C4:F4,0)) = INDEKS(C5:F11, 0,2): Funkcja INDEX zwraca wszystkie wartości w 2druga kolumna zakresu C5:F11 w tablicy takiej jak ta: {7865;4322;8534;5463;3252;7683;3654}.
- SUMA(INDEKS(C5:F11, 0,DOPASUJ(I5,C4:F4,0))) = SUMA({7865;4322;8534;5463;3252;7683;3654}): Funkcja SUMA sumuje wszystkie wartości w tablicy, a następnie pobiera całkowity wolumen sprzedaży w II kwartale, $40,773.
Dalsza analiza całej kolumny na podstawie określonej wartości
W celu dodatkowego przetworzenia na liście sprzedaży Q2 można po prostu dodać do formuły inne funkcje, takie jak SUMA, ŚREDNIA, MAX, MIN, DUŻY itp.
Na przykład, aby uzyskać średnia wielkość sprzedaży w II kwartale, możesz skorzystać ze wzoru:
=ŚREDNIA(INDEKS(C5:F11,0,PASUJ(I5,C4:F4,0)))
Aby dowiedzieć się najwyższa sprzedaż w II kwartale, użyj jednej z poniższych formuł:
=MAX(INDEKS(C5:F11,0,PASUJ(I5,C4:F4,0)))
OR
=DUŻY(INDEKS(C5:F11,0,PASUJ(I5,C4:F4,0)),1)
Powiązane funkcje
Funkcja Excel INDEX zwraca wyświetlaną wartość na podstawie podanej pozycji z zakresu lub tablicy.
Funkcja Excel PODAJ.POZYCJĘ wyszukuje określoną wartość w zakresie komórek i zwraca względną pozycję wartości.
Powiązane formuły
Wyszukaj i pobierz cały wiersz
Aby wyszukać i pobrać cały wiersz danych przez dopasowanie określonej wartości, możesz użyć funkcji INDEKS i PODAJ.POZYCJĘ w celu utworzenia formuły tablicowej.
Dokładne dopasowanie z INDEX i MATCH
Jeśli potrzebujesz znaleźć informacje podane w Excelu o konkretnym produkcie, filmie, osobie itp., powinieneś dobrze wykorzystać kombinację funkcji INDEKS i PODAJ.POZYCJĘ.
Przybliżone dopasowanie z INDEX i MATCH
Czasami musimy znaleźć przybliżone dopasowania w programie Excel, aby ocenić wydajność pracowników, ocenić wyniki uczniów, obliczyć opłatę pocztową na podstawie wagi itp. W tym samouczku omówimy, jak używać funkcji INDEKS i DOPASUJ do pobierania wyniki, których potrzebujemy.
Wyszukiwanie z uwzględnieniem wielkości liter
Być może wiesz, że możesz połączyć funkcje INDEKS i DOPASUJ lub użyć funkcji WYSZUKAJ.PIONOWO do wyszukiwania wartości w programie Excel. Jednak w wyszukiwaniach nie jest rozróżniana wielkość liter. Tak więc, aby przeprowadzić dopasowanie z uwzględnieniem wielkości liter, powinieneś skorzystać z funkcji DOKŁADNIE i WYBIERZ.
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)
- Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
- Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
- Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
- Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
