Wyszukaj i pobierz cały wiersz
Aby wyszukać i pobrać cały wiersz danych, dopasowując określoną wartość, możesz użyć INDEKS i MATCH funkcje do tworzenia formuły tablicowej.
Wyszukaj i pobierz cały wiersz na podstawie określonej wartości
Zsumuj cały wiersz na podstawie określonej wartości
Dalsza analiza całego wiersza na podstawie określonej wartości
Wyszukaj i pobierz cały wiersz na podstawie określonej wartości
Aby uzyskać lista sprzedaży dokonana przez Jimmy'ego zgodnie z powyższą tabelą możesz najpierw użyć funkcji MATCH, aby zwrócić pozycję sprzedaży dokonanej przez Jimmy'ego, która zostanie przekazana do INDEX w celu pobrania wartości na pozycji.
Ogólna składnia
=INDEX(return_range,MATCH(lookup_value,lookup_array,0),0)
√ Uwaga: To jest formuła tablicowa, która wymaga wprowadzenia za pomocą Ctrl + Shift + Wchodzę.
- zakres_zwrotów: Zakres zawierający cały wiersz, który chcesz zwrócić. 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 sprzedawcy.
- szukana_tablica: Zakres komórek, w których należy dopasować lookup_value. Tutaj odnosi się do zakresu nazw.
- typ_dopasowania 0: Wymusza MATCH, aby znaleźć pierwszą wartość, która jest dokładnie równa lookup_value.
aby uzyskać lista sprzedaży dokonana przez Jimmy'ego, 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,MECZ(„Jimmy”,B5: B11,0),0)
Możesz też użyć odwołania do komórki, aby uczynić formułę dynamiczną:
=INDEKS(C5:F11,MECZ(I5,B5: B11,0),0)
Wyjaśnienie formuły
=INDEX(C5:F11,MATCH(I5,B5:B11,0),0)
- DOPASUJ(I5,B5:B11,0): Połączenia typ_dopasowania 0 wymusza, aby funkcja MATCH zwróciła pozycję Jimmy, wartość w I5, w zasięgu B5: B11, który jest 7 ponieważ Jimmy jest na 7pozycja.
- INDEKS(C5:F11,DOPASUJ(I5,B5:B11,0),0) = INDEKS(C5:F11,7,0): Funkcja INDEX zwraca wszystkie wartości w 7-ty rząd zakresu C5:F11 w tablicy takiej jak ta: 6678,3654,3278,8398 {}. 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ły wiersz na podstawie określonej wartości
Teraz, gdy mamy wszystkie informacje o sprzedaży, które Jimmy zrobił, aby uzyskać roczna wielkość sprzedaży dokonanej przez Jimmy, możemy po prostu dodać funkcję SUMA do formuły, aby zsumować wszystkie wartości sprzedaży z listy.
Ogólna składnia
=SUM(INDEX(return_range,MATCH(lookup_value,lookup_array,0),0))
W tym przykładzie, aby uzyskać roczna wielkość sprzedaży dokonanej przez Jimmy, skopiuj lub wprowadź poniższą formułę w komórce I7 i naciśnij Wchodzę aby uzyskać wynik:
=SUMA(INDEKS(C5:F11,MECZ(I5,B5: B11,0),0))
Wyjaśnienie formuły
=SUM(INDEX(C5:F11,MATCH(I5,B5:B11,0),0))
- DOPASUJ(I5,B5:B11,0): Połączenia typ_dopasowania 0 wymusza, aby funkcja MATCH zwróciła pozycję Jimmy, wartość w I5, w zasięgu B5: B11, który jest 7 ponieważ Jimmy jest na 7pozycja.
- INDEKS(C5:F11,DOPASUJ(I5,B5:B11,0), 0) = INDEKS(C5:F11,7, 0): Funkcja INDEX zwraca wszystkie wartości w 7-ty rząd zakresu C5:F11 w tablicy takiej jak ta: 6678,3654,3278,8398 {}. 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.
- SUMA(INDEKS(C5:F11,DOPASUJ(I5,B5:B11,0)),0) = SUMA(6678,3654,3278,8398 {}): Funkcja SUMA sumuje wszystkie wartości w tablicy, a następnie pobiera roczną wielkość sprzedaży dokonanej przez Jimmy'ego, $22,008.
Dalsza analiza całego wiersza na podstawie określonej wartości
Aby dodatkowo przetworzyć sprzedaż dokonaną przez Jimmy'ego, możesz po prostu dodać do formuły inne funkcje, takie jak SUMA, ŚREDNIA, MAX, MIN, DUŻA itp.
Na przykład, aby uzyskać średnia sprzedaży Jimmy'ego w każdym kwartale, możesz skorzystać ze wzoru:
=ŚREDNIA(INDEKS(C5:F11,MECZ(I5,B5: B11,0),0))
Aby dowiedzieć się najniższa sprzedaż dokonana przez Jimmy'ego, użyj jednej z poniższych formuł:
=MIN(INDEKS(C5:F11,MECZ(I5,B5: B11,0),0))
OR
=MAŁY(INDEKS(C5:F11,MECZ(I5,B5: B11,0),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łą kolumnę
Aby wyszukać i pobrać całą kolumnę, dopasowując określoną wartość, formuła INDEKS i DOPASUJ wyświadczą ci przysługę.
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.
