Bendroji formulė
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Santrauka
Norėdami ieškoti reikšmių naudodami INDEX ir MATCH, naudodami kelis kriterijus, galite naudoti masyvo formulę. Parodytame pavyzdyje formulė H8 yra:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su klavišais + Shift + Enter, išskyrus „Excel 365“.
Paaiškinimas
Tai yra tobulesnė formulė. Norėdami sužinoti pagrindus, žr. Kaip naudoti INDEX ir MATCH.
Paprastai „INDEX MATCH“ formulė sukonfigūruojama nustatant „MATCH“, kad būtų galima peržiūrėti vieno stulpelio diapazoną ir pateikti atitikimą pagal pateiktus kriterijus. Nesudėjus reikšmių pagalbiniame stulpelyje ar pačioje formulėje, negalima pateikti daugiau nei vieno kriterijaus.
Ši formulė veikia pagal šį apribojimą, naudodama loginę logiką, kad sukurtumėte vienetų ir nulių masyvą, kad būtų rodomos eilutės, atitinkančios visus 3 kriterijus, tada naudodami MATCH, kad atitiktų pirmuosius 1 rastus. Laikinas vienetų ir nulių masyvas generuojamas naudojant šį fragmentą:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Čia mes palyginame elementą H5 su visais elementais, dydį H6 su visais dydžiais ir spalvą H7 su visomis spalvomis. Pradinis rezultatas yra trys tokių TRUE / FALSE masyvai:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Patarimas: norėdami pamatyti šiuos rezultatus, naudokite F9. Tiesiog pasirinkite išraišką formulės juostoje ir paspauskite F9.
Matematikos operacija (daugyba) TRUE FALSE reikšmes paverčia 1s ir 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Padauginę turime vieną tokį masyvą:
(0;0;1;0;0;0;0)
kuris perduodamas į funkciją MATCH kaip paieškos masyvas, kurio paieškos vertė yra 1:
MATCH(1,(0;0;1;0;0;0;0))
Šiuo metu formulė yra standartinė INDEX MATCH formulė. Funkcija MATCH grąžina 3 į INDEX:
=INDEX(E5:E11,3)
o INDEX grąžina galutinį 17,00 USD rezultatą.
Masyvo vizualizacija
Aukščiau paaiškintus masyvus gali būti sunku vizualizuoti. Žemiau pateiktame paveikslėlyje parodyta pagrindinė idėja. B, C ir D stulpeliai atitinka pavyzdyje pateiktus duomenis. F stulpelis sukurtas padauginus tris stulpelius kartu. Tai masyvas, perduotas MATCH.
Ne masyvo versija
Prie šios formulės galima pridėti kitą INDEKSĄ, išvengiant būtinybės įvesti kaip masyvo formulę su valdikliu + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
INDEX funkcija gali natūraliai tvarkyti masyvus, todėl antrasis INDEKSAS pridedamas tik norint „sugauti“ masyvą, sukurtą naudojant loginę logikos operaciją, ir vėl grąžinti tą patį masyvą į MATCH. Norėdami tai padaryti, „INDEX“ sukonfigūruota su nuline eile ir vienu stulpeliu. Dėl nulinės eilutės triuko INDEX grąžina 1 stulpelį iš masyvo (kuris ir taip yra vienas stulpelis).
Kodėl norėtumėte ne masyvo versijos? Kartais žmonės pamiršta įvesti masyvo formulę su „Control“ + „Shift“ + „Enter“ ir formulė pateikia neteisingą rezultatą. Taigi ne masyvo formulė yra „neperšaunama“. Tačiau kompromisas yra sudėtingesnė formulė.
Pastaba: „Excel 365“ nereikia specialiai įvesti masyvo formulių.
