Kokkuvõte
Mitme kriteeriumiga kahepoolse ligikaudse vasteotsingu sooritamiseks võite kriteeriumide rakendamiseks kasutada massiivi valemit, mis põhineb INDEXil ja MATCHil. Funktsiooni IF abil. Näidatud näites on valem K8-s:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
kus andmed (D6: H16), läbimõõt (D5: H5), materjal (B6: B16) ja kõvadus (C6: C16) on nimetatud vahemikud, mida kasutatakse ainult mugavuse huvides.
Märkus: see on massiivi valem ja see tuleb sisestada klahvidega Control + Tõst + Enter
Selgitus
Eesmärk on otsida etteandekiirus materjali, kõvaduse ja puuritera läbimõõdu põhjal. Etteandekiiruse väärtused on nimetatud vahemiku andmetes (D6: H16).
Seda saab teha kahesuunalise INDEX- ja MATCH-valemiga. Üks funktsioon MATCH töötab välja rea numbri (materjal ja kõvadus) ning teine funktsioon MATCH leiab veeru numbri (läbimõõt). Funktsioon INDEX tagastab lõpptulemuse.
Näidatud näites on valem K8-s:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Ainult loetavuse jaoks on lisatud reavahed).
Keeruline on see, et materjali ja kõvadusega tuleb hakkama saada koos. Peame piirama MATCHi antud materjali kõvaduse väärtustega (madala süsinikusisaldusega teras näites).
Saame seda teha funktsiooniga IF. Sisuliselt kasutame IF-i ebaoluliste väärtuste "viskamiseks" enne vaste otsimist.
Üksikasjad
Funktsioonile INDEX antakse massiivi jaoks nimetatud vahemiku andmed (D6: H16). Esimene funktsioon MATCH töötab välja rea numbri:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Õige rea leidmiseks peame tegema materjali täpse vaste ja ligikaudse kõvaduse vaste. Teeme seda funktsiooni IF abil, et esmalt ebaoluline kõvadus välja filtreerida:
IF(material=K5,hardness) // filter
Testime kõiki materjali väärtusi (B6: B16), et näha, kas need vastavad väärtusele K5 ("madala süsinikusisaldusega teras"). Kui jah, antakse kõvadus läbi. Kui ei, siis tagastab IF vale. Tulemuseks on selline massiiv:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Pange tähele, et ainsad säilinud väärtused on madala süsinikusisaldusega terasega seotud väärtused. Ülejäänud väärtused on nüüd FALSE. See massiiv tagastatakse otse funktsioonile MATCH otsingu_kaardina.
Vaste otsinguväärtus pärineb K6-st, mis sisaldab antud kõvadust 176. MATCH on konfigureeritud ligikaudseks vasteks, seades match_type väärtuseks 1. Nende seadetega MATCH ignoreerib FALSE väärtusi ja tagastab täpse vaste või järgmise väikseima väärtuse positsiooni .
Märkus: kõvaduse väärtused tuleb iga materjali jaoks järjestada kasvavas järjekorras.
Kui kõvadus on antud 176, tagastab MATCH 6, mis edastatakse otse INDEX-ile rea numbrina. Nüüd saame algse valemi ümber kirjutada järgmiselt:
=INDEX(data,6,MATCH(K7,diameter,1))
Teine MATCH-valem leiab õige veeru numbri, tehes läbimõõduga ligikaudse vaste:
MATCH(K7,diameter,1) // get column num
Märkus: D5: H5 läbimõõduga väärtused tuleb sortida kasvavas järjekorras.
Otsingu väärtus pärineb K7-st (0,75) ja otsingu_joon on nimeline vahemiku läbimõõt (D5: H5).
Nagu varemgi, on MATCH seatud ligikaudsele vastele, seades match_type väärtuseks 1.
Kui läbimõõt on 0,75, tagastab MATCH 3, mis edastatakse veeru numbrina otse funktsioonile INDEX. Algne valem on nüüd järgmine:
=INDEX(data,6,3) // returns 0.015
INDEX tagastab lõpptulemuse 0,015, mis on väärtus F11.
