„Excel“ formulė: išskleiskite visas rungtynes su pagalbiniu stulpeliu -

Turinys

Bendroji formulė

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Santrauka

Vienas iš būdų išskleisti kelias atitiktis programoje „Excel“ yra naudoti INDEX ir MATCH su pagalbiniu stulpeliu, kuris žymi atitinkančius duomenis. Tai leidžia išvengti sudėtingesnės masyvo formulės. Parodytame pavyzdyje formulė H6 yra:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

kur ct (G3), duomenys (B3: E52) ir pagalbininkas (E3: E52) yra pavadinti diapazonais.

Paaiškinimas

Iššūkis naudojant paieškos formules, kurios gauna daugiau nei vieną atitiktį, yra dublikatų (ty kelių atitikmenų) valdymas. Tokios paieškos formulės kaip „VLOOKUP“ ir „INDEX + MATCH“ gali lengvai rasti pirmąją atitiktį, tačiau žymiai sunkiau ieškoti „visų atitikčių“, kai kriterijai randa daugiau nei vieną atitikmenį.

Ši formulė sprendžia šį iššūkį naudojant pagalbinį stulpelį, kuris pateikia skaitinę vertę, kurią galima naudoti norint lengvai išskirti kelias atitiktis. Pagalbos stulpelio formulė atrodo taip:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Pagalbinis stulpelis tikrina kiekvieną duomenų eilutę norėdamas sužinoti, ar C stulpelio skyrius atitinka I3 reikšmę, o D stulpelio pastatas atitinka J3 vertę. Abu loginiai testai turi pateikti TRUE, kad IR grąžintų TRUE.

Kiekvienos eilutės rezultatas iš funkcijos AND pridedamas prie aukščiau esančios vertės pagalbinio stulpelio, kad būtų sugeneruotas skaičius. Praktinis šios formulės poveikis yra didėjantis skaitiklis, kuris keičiasi tik tada, kai randama (nauja) atitiktis. Tada vertė išlieka ta pati, kol bus rasta kita atitiktis. Tai veikia, nes TRUE / FALSE rezultatai grįžta pagal AND ir yra verčiami į 1/0 reikšmes kaip sumos operacijos dalį. FALSE rezultatai nieko neprideda, o TRUE rezultatai prideda 1.

Atgal į ištraukimo sritį H stulpelio pavadinimo paieškos formulė atrodo taip:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Dirbant iš vidaus, formulės dalis „INDEX + MATCH“ ieško pirmosios rastos atitikties pavadinimo, naudodama G stulpelio eilutės numerį kaip atitikties vertę:

INDEX(data,MATCH($G6,helper,0),1)

INDEX gauna visus 3 duomenų stulpelius kaip masyvą (pavadintas diapazonas „duomenys“), o MATCH sukonfigūruotas taip, kad atitiktų eilutės numerį pagalbininko stulpelyje (pavadintame diapazone „pagalbininkas“) tikslaus atitikimo režimu (3 argumentas nustatytas į nulį) .

Čia išryškėja formulės sumanumas. Pagalbiniame stulpelyje akivaizdžiai yra dublikatų, tačiau tai nesvarbu, nes MATCH atitiks tik pirmąją vertę. Pagal konstrukciją kiekviena „pirmoji vertė“ atitinka teisingą duomenų lentelės eilutę.

I ir J stulpelių formulės yra tokios pačios kaip H, išskyrus stulpelių skaičių, kuris kiekvienu atveju padidinamas vienu.

IF sakinys, apvyniojantis formulę INDEX / MATCH, atlieka paprastą funkciją - patikrina kiekvieną eilutės numerį ištraukimo srityje, norėdamas sužinoti, ar eilutės numeris yra mažesnis ar lygus G3 reikšmei (pavadintas diapazonas „ct“), kuri yra visų visų atitinkančių įrašų skaičiaus. Jei taip, vykdoma INDEX / MATCH logika. Jei ne, IF pateikia tuščią eilutę ("").

G3 formulė (pavadinta diapazonu „ct“) yra paprasta:

=MAX(helper)

Kadangi didžiausia pagalbininko stulpelio vertė yra tokia pati kaip ir bendras atitikčių skaičius, mums reikia tik MAX funkcijos.

Pastaba: ištraukimo sritį reikia rankiniu būdu sukonfigūruoti, kad būtų apdorota tiek duomenų, kiek reikia (ty 5 eilutės, 10 eilučių, 20 eilučių ir kt.). Šiame pavyzdyje apribota 5 eilučių, kad darbalapis būtų kompaktiškas.

Šios technikos išmokau Mike'o Girvino knygoje „Control + Shift + Enter“.