Yhteenveto
Voit suorittaa kaksisuuntaisen likimääräisen haun haun useilla ehdoilla käyttämällä hakemistoon INDEX ja MATCH perustuvaa matriisikaavaa ja IF-funktion avulla kriteerejä. Esitetyssä esimerkissä K8: n kaava on:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
missä tiedot (D6: H16), halkaisija (D5: H5), materiaali (B6: B16) ja kovuus (C6: C16) on nimetty alueiksi, joita käytetään vain mukavuuden vuoksi.
Huomaa: tämä on matriisikaava ja se on syötettävä painamalla Control + Vaihto + Enter
Selitys
Tavoitteena on etsiä syöttönopeus materiaalin, kovuuden ja poranterän halkaisijan perusteella. Syöttönopeuden arvot ovat nimettyjen alueiden tiedoissa (D6: H16).
Tämä voidaan tehdä kaksisuuntaisella INDEX- ja MATCH-kaavalla. Yksi MATCH-funktio selvittää rivinumeron (materiaali ja kovuus), ja toinen MATCH-funktio löytää sarakkeen numeron (halkaisijan). INDEX-funktio palauttaa lopputuloksen.
Esitetyssä esimerkissä K8: n kaava on:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Rivinvaihdot lisätty vain luettavuuden vuoksi).
Hankala on, että materiaalia ja kovuutta on käsiteltävä yhdessä. Meidän on rajoitettava MATCH koskemaan tietyn materiaalin (vähähiilinen teräs esitetyssä esimerkissä) kovuusarvoihin.
Voimme tehdä tämän IF-toiminnolla. Pohjimmiltaan käytämme IF: tä "heittämään" epäolennaiset arvot ennen kuin etsimme ottelua.
Yksityiskohdat
INDEX-toiminnolle annetaan nimetty alue- data (D6: H16) kuten matriisille. Ensimmäinen MATCH-toiminto selvittää rivinumeron:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Oikean rivin löytämiseksi meidän on tehtävä tarkka ottelu materiaalilla ja likimääräinen kovuus. Suoritamme tämän käyttämällä IF-toimintoa suodattamaan ensin merkityksetön kovuus:
IF(material=K5,hardness) // filter
Testaamme kaikki materiaalin arvot (B6: B16) sen selvittämiseksi, vastaavatko ne arvoa K5 ("vähähiilinen teräs"). Jos näin on, kovuusarvo kulkee läpi. Jos ei, IF palauttaa FALSE. Tuloksena on tällainen taulukko:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Huomaa, että ainoat elossa olevat arvot ovat vähähiiliseen teräkseen liittyviä arvoja. Muut arvot ovat nyt FALSE. Tämä taulukko palautetaan suoraan MATCH-funktioon hakukenttänä.
Vastaavuuden hakuarvo tulee K6: sta, joka sisältää annetun kovuuden 176. MATCH on määritetty likimääräiseksi otteluksi asettamalla match_type arvoksi 1. Näillä asetuksilla MATCH jättää FALSE-arvot huomiotta ja palauttaa tarkan vastaavuuden tai seuraavaksi pienimmän arvon sijainnin. .
Huomaa: kovuusarvot on lajiteltava nousevaan järjestykseen jokaiselle materiaalille.
Kun kovuus on annettu 176, MATCH palauttaa arvon 6, joka toimitetaan suoraan INDEX: lle rivinumerona. Voimme nyt kirjoittaa alkuperäisen kaavan uudelleen näin:
=INDEX(data,6,MATCH(K7,diameter,1))
Toinen MATCH-kaava löytää oikean sarakkeen numeron suorittamalla likimääräinen halkaisijan vastaavuus:
MATCH(K7,diameter,1) // get column num
Huomaa: halkaisijan D5: H5 arvot on lajiteltava nousevassa järjestyksessä.
Haun arvo tulee K7: stä (0,75), ja haku_array on nimetty alueen halkaisija (D5: H5).
Kuten aiemmin, MATCH asetetaan likimääräiseksi otteluksi asettamalla match_type arvoksi 1.
Halkaisijan ollessa 0,75 MATCH palauttaa 3, joka toimitetaan suoraan INDEX-funktiolle sarakkeen numerona. Alkuperäinen kaava ratkaisee nyt:
=INDEX(data,6,3) // returns 0.015
INDEX palauttaa lopullisen tuloksen 0,015, arvon F11.
