Yleinen kaava
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Yhteenveto
Voit purkaa kaikki osumat osittaisen vastaavuuden perusteella käyttämällä taulukkoa, joka perustuu INDEX- ja AGGREGATE-funktioihin, tuella ISNUMBER ja SEARCH. Esitetyssä esimerkissä kaava G5: ssä on:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
seuraavilla nimetyillä alueilla: "haku" = D5, "ct" = D8, "data" = B5: B55.
Huomaa: tämä on matriisikaava, mutta se ei vaadi control + shift + enter, koska AGGREGATE voi käsitellä matriiseja natiivisti.
Selitys
Tämän kaavan ydin on INDEX-funktio, jossa AGGREGATE-funktiota käytetään selvittämään "n: n osuma" jokaiselle riville ote-alueella:
INDEX(data,nth_match_formula)
Lähes kaikki työ on selvittää ja raportoida, mitkä "data" -rivit vastaavat hakumerkkijonoa, ja raportoida kunkin vastaavan arvon sijainti INDEX: lle. Tämä tehdään AGGREGATE-toiminnolla, joka on määritetty seuraavasti:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Ensimmäinen argumentti, 15, kertoo AGGREGATEn käyttäytymään PIENEKSI ja palauttamaan n: nneksi pienimmät arvot. Toinen argumentti, 6, on vaihtoehto sivuuttaa virheet. Kolmas argumentti on lauseke, joka tuottaa joukon vastaavia tuloksia (kuvattu alla). Neljäs argumentti, F5, toimii kuten "k" SMALL-muodossa määritelläksesi "n: nnen" arvon.
AGGREGATE toimii matriiseilla, ja alla oleva lauseke rakentaa taulukon kolmannelle argumentille AGGREGATE:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Tässä ROW-funktiota käytetään muodostamaan matriisi suhteellisista rivinumeroista, ja ISNUMBER ja SEARCH käytetään yhdessä hakumerkkijonon vastaamiseksi datan arvoihin, mikä tuottaa TOSI- ja EPÄTOSI-arvot.
Älykäs bitti on jakaa rivinumerot hakutuloksilla. Tällaisessa matemaattisessa operaatiossa TOSI käyttäytyy kuin 1 ja FALSE käyttäytyy kuin nolla. Tulos on, että positiiviseen vastaavuuteen liittyvät rivinumerot jaetaan 1: llä ja selviävät toiminnasta, kun taas vastaamattomiin arvoihin liittyvät rivinumerot tuhoutuvat ja niistä tulee # DIV / 0-virheitä. Koska AGGREGATE on asetettu jättämään virheet huomiotta, se jättää huomioimatta # DIV / 0 -virheet ja palauttaa n: n pienimmän luvun jäljellä olevista arvoista käyttämällä sarakkeessa F olevaa numeroa n: nneksi.
Suorituskyvyn hallinta
Kuten kaikki matriisikaavat, tämä kaava on "kallis" resursseiltaan suurella tietojoukolla. Suorituskykyyn kohdistuvien vaikutusten minimoimiseksi koko INDEX- ja MATCH-kaava kääritään IF: ään näin:
=IF(F5>ct,"",formula)
jossa nimetty alue "ct" (D8) pitää tätä kaavaa:
=COUNTIF(data,"*"&search&"*")
Tämä tarkistus estää kaavan INDEX- ja AGGREGATE-osan suorittamisen, kun kaikki vastaavat arvot on purettu.
Taulukko PIENI
Jos Excel-versiossasi ei ole AGGREGATE-funktiota, voit käyttää vaihtoehtoista kaavaa, joka perustuu PIENIIN ja JOS:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Huomaa: tämä on matriisikaava ja se on syötettävä näppäimillä + + shift + enter.
