W tym artykule dowiemy się, jak wyszukać najbliższe dopasowanie za pomocą funkcji INDEKS i DOPASUJ w programie Excel.
Scenariusz:
Na przykład, musimy znaleźć najbliższe liczbowo dopasowanie i odpowiadające mu wyniki z tabeli. Potrzebujemy ustalonej formuły, która pomoże w znalezieniu najbliższego dopasowania, aby uzyskać wymaganą wartość.
Jak rozwiązać problem?
Aby formuła zrozumiała, najpierw musimy trochę zrewidować następujące funkcje
- funkcja INDEKS
- Funkcja PODAJ.POZYCJĘ
Teraz stworzymy formułę korzystając z powyższych funkcji. Funkcja MATCH zwróci indeks najbliższego dopasowania z zakresu. Funkcja INDEKS przyjmuje indeks wiersza jako argument i zwraca odpowiednie wymagane wyniki.
Formuła ogólna:
{ = INDEX ( dane , MATCH ( MIN ( ABS ( wartość - dane ) ) , ABS ( wartość - dane ) , 0 ) ) }
dane : tablica wartości dat
wartość : podana wartość daty, gdzie wynik jest najbliższy
typ_dopasowania : 1 (dokładnie lub następny najmniejszy) lub 0 (dokładne dopasowanie) lub -1 (dokładnie lub następny największy)
NIE RÓB użyj nawiasów klamrowych ręcznie. Nawiasy klamrowe stosowane za pomocą Ctrl + Shift + Enter zamiast po prostu Wejść.
Przykład:
Powyższe stwierdzenia mogą być trudne do zrozumienia. Zrozummy to, używając wzoru w przykładzie
Tutaj mamy kilka losowych wartości dat i podaną wartość daty.
Nazwany zakres używany dla danych (D4:D12).
Tutaj użyliśmy nazwanego zakresu zamiast tablicy odwołań do komórek, ponieważ jest to łatwe do zrozumienia. Musimy poszukać daty najbliższej podanej w komórce G6.
Użyj powyższego wzoru, aby uzyskać najbliższą datę.
Użyj wzoru:
{ = INDEKS ( dane , MATCH ( MIN ( ABS ( dane - G ) ) ) , ABS ( dane - G6 ) , 0 ) ) }
NIE używaj nawiasów klamrowych ręcznie. Nawiasy klamrowe stosowane za pomocą Ctrl + Shift + Enter zamiast po prostu Wejść.
Wyjaśnienie:
- Funkcja ABS zwraca dodatnią liczbę całkowitą po odjęciu dokonanym po (data - G6) wartościach dat.
- ABS ( data - G6 ) tworzy tablicę, odejmując wszystkie wartości dat od podanej wartości daty w komórce G6. Zwrócona tablica to.
{ 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 }
- MIN(ABS(dane-G6)) zwraca najbliższą wartość z tablicy zwróconej z powyższego kroku.
= INDEKS ( dane , PODAJ.POZYCJĘ ( 6 , { 160; 49 ; 105 ; 1048 ; 600 ; 6 ; 702 ; 822 ; 673 } , 0 ) )
- Funkcja PODAJ.POZYCJĘ dopasowuje najbliższe dopasowanie wartości minimalnej w zwróconej tablicy i zwraca indeks wiersza do funkcji INDEX.
- Funkcja INDEX znajduje wartość, która ma zwrócony indeks ROW.
data nazwany zakres używany w tablicy dat. Teraz użyj Ctrl + Shift + Enter w miejscu tylko Enter top uzyskaj wynik, ponieważ jest to formuła tablicowa. Formuła tablicowa to formuła, w której tablica wartości przechodzi z jednego wyniku funkcji jako argument do funkcji zewnętrznej.
Jak widać na powyższym zrzucie, uzyskaliśmy najbliższe dopasowanie z podanej daty w tabeli. Wyodrębnij szczegóły z tabeli posługując się powyższym wzorem.
Oto kilka uwag obserwacyjnych podczas korzystania z powyższego wzoru.
Uwagi:
- 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Ę ma wartość 0.
- 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.
- NIE używaj nawiasów klamrowych ręcznie. Nawiasy klamrowe stosowane za pomocą Ctrl + Shift + Enter zamiast po prostu Wejść albo zwraca błąd.
Mam nadzieję, że zrozumiałeś, jak wyszukać najniższe lub najmniejsze dopasowanie za pomocą funkcji INDEX & MATCH w programie Excel. Zapoznaj się z dodatkowymi artykułami na temat wartości wyszukiwania programu Excel i funkcji programu Excel 2019 tutaj. Zachęcamy do składania zapytań poniżej w polu komentarza. Z pewnością Ci pomożemy.
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 stronie 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.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Znajdź wartość wyszukiwania w tablicy za pomocą funkcji WYSZUKAJ.PIONOWO wyjaśnionej na przykładzie.
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność
Edytuj listę rozwijaną
Absolutne odniesienie w Excelu
Jeśli z formatowaniem warunkowym
Jeśli z symbolami wieloznacznymi
Funkcja CONCAT w programie Excel 2019
Funkcja MAXIFS programu Excel 2019