Podsumowanie
Aby przeprowadzić dwukierunkowe przybliżone wyszukiwanie dopasowania z wieloma kryteriami, możesz użyć formuły tablicowej opartej na indeksie i PODAJ.POZYCJĘ z pomocą funkcji JEŻELI w celu zastosowania kryteriów. W przedstawionym przykładzie formuła w K8 to:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
gdzie dane (D6: H16), średnica (D5: H5), materiał (B6: B16) i twardość (C6: C16) to nazwane zakresy używane wyłącznie dla wygody.
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą kombinacji Control + Shift + Enter
Wyjaśnienie
Celem jest sprawdzenie posuwu na podstawie materiału, twardości i średnicy wiertła. Wartości prędkości posuwu znajdują się w nazwanych danych zakresu (D6: H16).
Można to zrobić za pomocą dwukierunkowej formuły INDEX i MATCH. Jedna funkcja PODAJ.POZYCJĘ oblicza numer wiersza (materiał i twardość), a druga funkcja PODAJ.POZYCJĘ wyszukuje numer kolumny (średnicę). Funkcja INDEKS zwraca wynik końcowy.
W przedstawionym przykładzie formuła w K8 to:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Dodane podziały wierszy tylko dla czytelności).
Problem polega na tym, że materiał i twardość należy traktować razem. Musimy ograniczyć MATCH do wartości twardości dla danego materiału (stal niskowęglowa w pokazanym przykładzie).
Możemy to zrobić za pomocą funkcji JEŻELI. Zasadniczo używamy IF, aby „wyrzucić” nieistotne wartości, zanim zaczniemy szukać dopasowania.
Detale
Funkcja INDEKS otrzymuje nazwane dane zakresu (D6: H16) jak dla tablicy. Pierwsza funkcja MATCH oblicza numer wiersza:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Aby zlokalizować właściwy rząd, musimy dokładnie dopasować materiał i przybliżone dopasowanie twardości. Robimy to za pomocą funkcji JEŻELI, aby najpierw odfiltrować nieistotną twardość:
IF(material=K5,hardness) // filter
Testujemy wszystkie wartości w materiale (B6: B16), aby sprawdzić, czy zgadzają się z wartością w K5 („Stal niskowęglowa”). Jeśli tak, wartość twardości jest przenoszona. Jeśli nie, funkcja JEŻELI zwraca FAŁSZ. Rezultatem jest tablica taka:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Zauważ, że jedynymi zachowanymi wartościami są te związane ze stalą niskowęglową. Pozostałe wartości są teraz FAŁSZEM. Ta tablica jest zwracana bezpośrednio do funkcji PODAJ.POZYCJĘ jako tablica_wyszukiwania.
Wartość wyszukiwania dla dopasowania pochodzi z K6, która zawiera podaną twardość, 176. MATCH jest skonfigurowane do przybliżonego dopasowania, ustawiając match_type na 1. Z tymi ustawieniami, PODAJNIK ignoruje wartości FAŁSZ i zwraca pozycję dokładnego dopasowania lub kolejnej najmniejszej wartości .
Uwaga: wartości twardości należy sortować w kolejności rosnącej dla każdego materiału.
Przy twardości podanej jako 176 funkcja PODAJ.POZYCJĘ zwraca 6, dostarczoną bezpośrednio do INDEKSU jako numer wiersza. Możemy teraz przepisać oryginalną formułę w następujący sposób:
=INDEX(data,6,MATCH(K7,diameter,1))
Druga formuła PODAJ.POZYCJĘ znajduje prawidłowy numer kolumny, wykonując przybliżone dopasowanie średnicy:
MATCH(K7,diameter,1) // get column num
Uwaga: wartości średnicy D5: H5 należy posortować w kolejności rosnącej.
Wartość wyszukiwania pochodzi z K7 (0,75), a lookup_array to średnica nazwanego zakresu (D5: H5).
Jak poprzednio, MATCH jest ustawione na przybliżone dopasowanie, ustawiając match_type na 1.
Przy średnicy podanej jako 0,75 funkcja PODAJ.POZYCJĘ zwraca 3, dostarczone bezpośrednio do funkcji INDEKS jako numer kolumny. Oryginalna formuła jest teraz następująca:
=INDEX(data,6,3) // returns 0.015
INDEKS zwraca wynik końcowy 0,015, wartość z F11.
