Samouczek programu Excel: Dwukierunkowe wyszukiwanie z przybliżoną wartością INDEKSU i PODAJNIKA

Spisie treści

Powiązane skróty

W tym filmie przyjrzymy się, jak zbudować dwukierunkowe wyszukiwanie za pomocą INDEKSU i PODAJNIKA, używając przybliżonego dopasowania.

Tutaj mamy prosty kalkulator kosztów, który wyszukuje koszt na podstawie szerokości i wysokości materiału. Dopasowanie musi być przybliżone. Na przykład, jeśli szerokość wynosi 250, a wysokość 325, poprawny wynik to 1800 USD.

Jeśli szerokość wynosi 450, a wysokość pozostaje na poziomie 325, prawidłowy wynik to 3600 USD.

Możemy zbudować formułę, która wyszukuje za pomocą INDEX i MATCH.

Najpierw spraw, aby INDEKS działał jako dowód słuszności koncepcji, zakodowując na stałe część formuły. To świetny sposób na upewnienie się, że masz dobry pomysł, zanim zaczniesz.

Tak więc, mając dane w naszej tabeli jako tablicę, oraz przy szerokości 450 i wysokości 325, INDEKS będzie potrzebował wiersza o numerze 3 i kolumny o numerze 4, aby pobrać poprawną wartość w tabeli . Działa to dobrze, ale oczywiście się nie zmieni, ponieważ wartości są zakodowane na stałe.

Następnie skonfigurujmy funkcje MATCH, których potrzebujemy do obliczenia tych wartości.

Aby uzyskać wartość szerokości, która jest numerem wiersza w INDEKSIE, użyjemy wartości wyszukiwania z M7 i wartości w kolumnie B jako tablicy przeglądowej. W przypadku typu dopasowania chcemy użyć 1 do przybliżonego dopasowania, ponieważ wartości są sortowane w kolejności rosnącej. Wynik to 4.

Aby uzyskać wysokość, która jest kolumną wewnątrz INDEKSU, ponownie użyjemy PODAJ.POZYCJĘ z wartością z M8, wartościami wysokości z wiersza 6. Ponownie, typ dopasowania ponownie ustawiony na 1 dla przybliżonego dopasowania. Wynik to 3.

Teraz, jeśli zmienię szerokość na 350, a wysokość na 550, otrzymamy nowy zestaw wyników.

Te wartości są dokładnie tym, czego potrzebujemy do INDEKSU. Więc teraz po prostu skopiuję i wkleję funkcje MATCH do oryginalnej formuły INDEKSU.

Szerokość odpowiada numerowi wiersza.

A wysokość to numer kolumny.

Mamy teraz dynamiczne wyszukiwanie, które poprawnie oblicza koszt na podstawie szerokości i wysokości, stosując przybliżone dopasowanie.