Výukový program pre Excel: Obojsmerné vyhľadávanie s približným indexom INDEX a MATCH

V tomto videu sa pozrieme na to, ako vytvoriť obojsmerné vyhľadávanie pomocou indexov INDEX a MATCH pomocou približnej zhody.

Máme jednoduchú kalkulačku nákladov, ktorá vyhľadáva náklady na základe šírky a výšky materiálu. Zápas musí byť približný. Napríklad ak je šírka 250 a výška 325, správny výsledok je 1 800 dolárov.

Ak je šírka 450 a výška zostáva na 325, správny výsledok je 3 600 dolárov.

Vzorec, ktorý robí toto vyhľadávanie, môžeme zostaviť pomocou indexov INDEX a MATCH.

Najskôr poďme, aby INDEX fungoval ako dôkaz koncepcie, a to zakódovaním časti vzorca. Je to skvelý spôsob, ako sa ubezpečiť, že máte správny nápad skôr, ako začnete.

Takže s údajmi v našej tabuľke ako poľom a so šírkou 450 a výškou 325 bude INDEX potrebovať na načítanie správnej hodnoty v tabuľke číslo riadku 3 a číslo stĺpca 4. . To funguje dobre, ale samozrejme sa to nezmení, pretože hodnoty sú napevno.

Ďalej teda nastavíme MATCH funkcie, ktoré potrebujeme na výpočet týchto hodnôt.

Aby sme získali hodnotu pre šírku, čo je číslo riadku v INDEXe, použijeme ako vyhľadávacie pole vyhľadávaciu hodnotu z M7 a hodnoty v stĺpci B. Pre typ zhody chceme použiť 1 na približnú zhodu, pretože hodnoty sú zoradené vzostupne. Výsledok je 4.

Na získanie výšky, ktorá je stĺpcom vo vnútri INDEXU, použijeme opäť MATCH s hodnotou z M8, výškové hodnoty z riadku 6. Znova je typ zhody nastavený na 1 pre približnú zhodu. Výsledok je 3.

Teraz, keď zmením šírku na 350 a výšku na 550, dostaneme novú sadu výsledkov.

Tieto hodnoty sú presne to, čo pre INDEX potrebujeme. Takže teraz jednoducho skopírujem a prilepím funkcie MATCH do pôvodného vzorca INDEX.

Šírka zodpovedá číslu riadku.

A výška pôjde pre číslo stĺpca.

Teraz máme dynamické vyhľadávanie, ktoré správne počíta náklady na základe šírky a výšky, pričom predstavuje približnú zhodu.