Santrauka
Norėdami atlikti apytikslę abipusę atitikties paiešką su keliais kriterijais, galite naudoti masyvo formulę, pagrįstą INDEX ir MATCH, naudodami funkciją IF, jei norite taikyti kriterijus. Parodytame pavyzdyje formulė K8 yra:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
kur duomenys (D6: H16), skersmuo (D5: H5), medžiaga (B6: B16) ir kietumas (C6: C16) yra įvardijami tik patogumui.
Pastaba: tai yra masyvo formulė ir ją reikia įvesti paspaudus „Control + Shift + Enter“
Paaiškinimas
Tikslas yra ieškoti tiekimo greitį pagal medžiagą, kietumą ir grąžto skersmenį. Tiekimo normos vertės yra nurodytuose diapazono duomenyse (D6: H16).
Tai galima padaryti naudojant dvipusę INDEX ir MATCH formulę. Viena MATCH funkcija nustato eilutės numerį (medžiaga ir kietumas), o kita MATCH funkcija nustato stulpelio numerį (skersmenį). INDEX funkcija grąžina galutinį rezultatą.
Parodytame pavyzdyje formulė K8 yra:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Eilutės pertraukos pridėtos tik skaitomumui).
Keblu yra tai, kad medžiagą ir kietumą reikia tvarkyti kartu. Turime apriboti MATCH iki tam tikros medžiagos (žemo anglies plieno, pateiktame pavyzdyje) kietumo vertės.
Tai galime padaryti su IF funkcija. Iš esmės mes naudojame IF, kad „išmestume“ nereikšmingas vertybes prieš ieškodami atitikties.
Išsami informacija
INDEX funkcijai suteikiami įvardyti diapazono duomenys (D6: H16) kaip masyvui. Pirmoji funkcija MATCH nustato eilutės numerį:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Norėdami rasti teisingą eilutę, turime tiksliai atitikti medžiagą ir apytikslį kietumo atitikimą. Mes tai darome naudodami IF funkciją, kad pirmiausia filtruotume nereikšmingą kietumą:
IF(material=K5,hardness) // filter
Mes išbandome visas medžiagos vertes (B6: B16), norėdami sužinoti, ar jos atitinka K5 reikšmę („mažai anglies turinčio plieno“). Jei taip, kietumo vertė perduodama. Jei ne, IF grąžina FALSE. Rezultatas yra toks masyvas:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Atkreipkite dėmesį, kad vienintelės išlikusios vertės yra susijusios su mažai anglies turinčiu plienu. Kitos vertės dabar yra NETIESA. Šis masyvas grąžinamas tiesiai į funkciją MATCH kaip lookup_array.
Atitikties paieškos vertė gaunama iš K6, kuriame nurodytas kietumas yra 176. MATCH yra sukonfigūruotas apytiksliai atitikčiai, nustatant match_type į 1. Naudodamiesi šiais parametrais, MATCH ignoruoja FALSE reikšmes ir grąžina tikslios atitikties arba kitos mažiausios vertės poziciją. .
Pastaba: kietumo vertės turi būti rūšiuojamos didėjančia kiekvienos medžiagos tvarka.
Pateikus kietumą kaip 176, MATCH grąžina 6, pristatytą tiesiai į INDEX kaip eilutės numerį. Dabar galime perrašyti pradinę formulę taip:
=INDEX(data,6,MATCH(K7,diameter,1))
Antroji MATCH formulė nustato tinkamą stulpelio numerį, atlikdama apytikslį skersmens atitikimą:
MATCH(K7,diameter,1) // get column num
Pastaba: D5: H5 skersmens vertės turi būti rūšiuojamos didėjimo tvarka.
Paieškos vertė gaunama iš K7 (0,75), o „lookup_array“ yra pavadintas diapazono skersmuo (D5: H5).
Kaip ir anksčiau, „MATCH“ yra nustatytas kaip apytikslis atitikimas nustatant „match_type“ į 1.
Kai skersmuo nurodomas kaip 0,75, MATCH grąžina 3, kuris tiesiogiai pateiktas funkcijai INDEX kaip stulpelio numeris. Pradinė formulė dabar išspręsta:
=INDEX(data,6,3) // returns 0.015
INDEX pateikia galutinį rezultatą 0,015, reikšmę iš F11.
