Tutorial Excel: căutare bidirecțională cu INDEX și MATCH aproximativ

Cuprins

Comenzi rapide asociate

În acest videoclip, vom analiza cum să construim o căutare bidirecțională cu INDEX și MATCH, folosind o potrivire aproximativă.

Aici avem un calculator de cost simplu, care caută costul în funcție de lățimea și înălțimea unui material. Meciul trebuie să fie aproximativ. De exemplu, dacă lățimea este de 250 și înălțimea de 325, rezultatul corect este de 1.800 USD.

Dacă lățimea este de 450, iar înălțimea rămâne la 325, rezultatul corect este de 3.600 USD.

Putem construi o formulă care face această căutare folosind INDEX și MATCH.

În primul rând, să facem ca INDEX să funcționeze ca o dovadă a conceptului, codificând o parte din formulă. Acesta este un mod excelent de a vă asigura că aveți ideea corectă, înainte de a începe.

Deci, cu datele din tabelul nostru ca matrice și cu o lățime de 450 și o înălțime de 325, INDEX va avea nevoie de un număr de rânduri de 3 și un număr de coloane de 4, pentru a recupera valoarea corectă din tabel . Acest lucru funcționează bine, dar, bineînțeles, nu se va schimba, deoarece valorile sunt codificate hard.

Deci, în continuare, să configurăm funcțiile MATCH de care avem nevoie pentru a calcula aceste valori.

Pentru a obține valoarea lățimii, care este numărul rândului din INDEX, vom folosi valoarea de căutare din M7 și valorile din coloana B, ca matrice de căutare. Pentru tipul de potrivire, dorim să folosim 1 pentru potrivirea aproximativă, deoarece valorile sunt sortate în ordine crescătoare. Rezultatul este 4.

Pentru a obține înălțimea, care este coloana din INDEX, vom folosi din nou MATCH cu valoarea din M8, valorile înălțimilor din rândul 6. Din nou, tipul de potrivire a fost setat din nou la 1 pentru potrivirea aproximativă. Rezultatul este 3.

Acum, dacă schimb lățimea la 350 și înălțimea la 550, vom obține un nou set de rezultate.

Aceste valori sunt exact ceea ce avem nevoie pentru INDEX. Deci, acum voi copia și lipi pur și simplu funcțiile MATCH în formula originală INDEX.

Lățimea intră pentru numărul rândului.

Și înălțimea merge pentru numărul coloanei.

Acum avem o căutare dinamică care calculează corect costul în funcție de lățime și înălțime, adică potrivirea aproximativă.