Összegzés
Kétféle megközelítéssel, több kritériummal történő közelítő keresés elvégzéséhez használhatja az INDEX és a MATCH alapú tömbképletet, az IF függvény segítségével a feltételek alkalmazásához. A bemutatott példában a K8 képlete a következő:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
ahol az adatokat (D6: H16), az átmérőt (D5: H5), az anyagot (B6: B16) és a keménységet (C6: C16) nevezzük csak a kényelem érdekében használt tartományoknak.
Megjegyzés: ez egy tömbképlet, amelyet a Control + Shift + Enter billentyűkombinációval kell megadni
Magyarázat
A cél az előtolás keresése az anyag, a keménység és a fúrófej átmérője alapján. Előtolás értékek a megnevezett tartományban adatok (D6: H16).
Ez kétirányú INDEX és MATCH képlettel történhet. Az egyik MATCH függvény kidolgozza a sorszámot (anyag és keménység), a másik MATCH függvény pedig az oszlopszámot (átmérő). Az INDEX függvény visszaadja a végeredményt.
A bemutatott példában a K8 képlete a következő:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Csak az olvashatóság érdekében adunk sortörést).
Az a trükk, hogy az anyagot és a keménységet együtt kell kezelni. Korlátoznunk kell a MATCH-ot egy adott anyag keménységi értékeire (alacsony szén-dioxid-tartalmú acél a bemutatott példában).
Ezt megtehetjük az IF függvénnyel. Lényegében az IF-t használjuk a lényegtelen értékek "eldobására", mielőtt egyezést keresünk.
Részletek
A INDEX függvény kap a megnevezett tartományban adatok (D6: H16), mint a tömbben. Az első MATCH függvény kidolgozza a sorszámot:
MATCH(K6,IF(material=K5,hardness),1) // get row num
A megfelelő sor megkereséséhez pontos egyezést kell megtenni az anyagon, és hozzávetőlegesen a keménységen. Ezt úgy végezzük, hogy az IF függvény segítségével először kiszűrjük a lényegtelen keménységet:
IF(material=K5,hardness) // filter
Az anyag összes értékét (B6: B16) teszteljük, hogy megegyezzenek-e a K5-ben megadott értékkel ("alacsony szén-dioxid-kibocsátású acél"). Ha igen, akkor a keménységi érték átkerül. Ha nem, akkor az IF HAMIS értéket ad vissza. Az eredmény egy ilyen tömb:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Vegye figyelembe, hogy az alacsony fennmaradó értékek csak az alacsony szén-dioxid-tartalmú acélhoz tartoznak. A többi érték most HAMIS. Ez a tömb közvetlenül a MATCH függvényhez kerül visszakeresésre.
Az egyezés keresési értéke a K6-ból származik, amely a megadott keménységet, 176. A MATCH hozzávetőleges egyezésre van konfigurálva azáltal, hogy a match_type értéket 1-re állítja. Ezekkel a beállításokkal a MATCH figyelmen kívül hagyja a FALSE értékeket és visszaadja a pontos egyezés vagy a következő legkisebb érték pozícióját .
Megjegyzés: a keménységi értékeket növekvő sorrendben kell rendezni minden anyaghoz.
176-os keménységként a MATCH 6-ot ad vissza, közvetlenül az INDEX-be szállítva sorszámként. Most így írhatjuk át az eredeti képletet:
=INDEX(data,6,MATCH(K7,diameter,1))
A második MATCH képlet megtalálja a helyes oszlopszámot azáltal, hogy hozzávetőlegesen egyezik az átmérővel:
MATCH(K7,diameter,1) // get column num
Megjegyzés: A D5: H5 átmérőjű értékeket növekvő sorrendben kell rendezni.
A kikeresési érték származik K7 (0,75), és a lookup_array van a megnevezett tartományban átmérője (D5: H5).
Az előzőekhez hasonlóan a MATCH hozzávetőleges egyezésre van állítva azáltal, hogy a match_type értéket 1-re állítja.
Ha az átmérő értéke 0,75, akkor a MATCH 3-at ad vissza, amelyet közvetlenül az INDEX funkcióhoz szállítanak oszlopszámként. Az eredeti képlet most feloldódik:
=INDEX(data,6,3) // returns 0.015
Az INDEX 0,015 végeredményt ad vissza, az F11 értékét.
