Vispārēja formula
=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")
Kopsavilkums
Viens veids, kā iegūt vairākas atbilstības programmā Excel, ir INDEX un MATCH izmantošana ar palīga kolonnu, kas atzīmē atbilstošos datus. Tas ļauj izvairīties no sarežģītākas sarežģītākas masīva formulas. Parādītajā piemērā formula H6 ir:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
kur ct (G3), dati (B3: E52) un palīgs (E3: E52) ir nosaukti diapazoni.
Paskaidrojums
Izaicinājums ar uzmeklēšanas formulām, kas izgūst vairāk nekā vienu spēli, ir dublikātu (ti, vairāku spēļu) pārvaldīšana. Uzmeklēšanas formulas, piemēram, VLOOKUP un INDEX + MATCH, var viegli atrast pirmo atbilstību, taču ir daudz grūtāk meklēt "visas spēles", ja kritēriji atrod vairāk nekā vienu atbilstību.
Šī formula risina šo izaicinājumu, izmantojot palīga kolonnu, kas atgriež skaitlisko vērtību, kuru var izmantot, lai viegli izvilktu vairākas atbilstības. Palīdzības kolonnas formula izskatās šādi:
=SUM(E2,AND(C3=$I$3,D3=$J$3))
Palīga kolonna pārbauda katru datu rindu, lai noskaidrotu, vai departaments C slejā atbilst vērtībai I3 un ēka D slejā atbilst vērtībai J3. Abiem loģiskajiem testiem jāatgriež TRUE, lai AND atgrieztos TRUE.
Katrai rindai rezultāts no funkcijas AND tiek pievienots palīgkolonas "vērtībai virs", lai ģenerētu skaitīšanu. Šīs formulas praktiskais efekts ir pieaugošs skaitītājs, kas mainās tikai tad, kad tiek atrasta (jauna) atbilstība. Tad vērtība paliek nemainīga, līdz tiek atrasta nākamā spēle. Tas darbojas, jo TRUE / FALSE rezultāti atgriežas ar AND un tiek piespiesti 1/0 vērtībām summas operācijas ietvaros. FALSE rezultāti nepievieno neko, un TRUE rezultāti papildina 1.
Atpakaļ ekstrakcijas apgabalā nosaukuma H slejas uzmeklēšanas formula izskatās šādi:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
Strādājot no iekšpuses, formulas daļa INDEX + MATCH meklē pirmās atrastās atbilstības nosaukumu, kā atbilstības vērtību izmantojot G slejas rindas numuru:
INDEX(data,MATCH($G6,helper,0),1)
INDEX saņem visas 3 datu kolonnas kā masīvu (nosauktais diapazons "dati"), un MATCH ir konfigurēts tā, lai precīzās atbilstības režīmā (3. arguments iestatīts uz nulli) saskaņotu rindas numuru palīga kolonnā (nosauktais diapazons "palīgs"). .
Šeit kļūst acīmredzama formulas gudrība. Palīga kolonnā acīmredzami ir dublikāti, taču tas nav svarīgi, jo MATCH sakritīs tikai ar pirmo vērtību. Pēc noformējuma katra "pirmā vērtība" atbilst pareizajai datu tabulas rindai.
Formulas I un J kolonnās ir vienādas ar H, izņemot kolonnas numuru, kuru katrā gadījumā palielina par vienu.
IF paziņojums, kas aptver INDEX / MATCH formulu, veic vienkāršu funkciju - tas pārbauda katru rindas numuru ekstrakcijas apgabalā, lai redzētu, vai rindas numurs ir mazāks vai vienāds ar vērtību G3 (nosauktais diapazons "ct"), kas ir visu atbilstošo ierakstu kopējais skaits. Ja tā, tiek palaista INDEX / MATCH loģika. Ja nē, IF izvada tukšu virkni ("").
G3 formula (nosaukta diapazons "ct") ir vienkārša:
=MAX(helper)
Tā kā maksimālā vērtība palīga kolonnā ir tāda pati kā kopējais atbilstības skaits, mums ir vajadzīga tikai funkcija MAX.
Piezīme: ieguves apgabals ir manuāli jākonfigurē, lai apstrādātu tik daudz datu, cik nepieciešams (ti, 5 rindas, 10 rindas, 20 rindas utt.). Šajā piemērā tas ir ierobežots līdz 5 rindām, lai darblapa būtu kompakta.
Šo tehniku iemācījos Maika Girvina grāmatā Control + Shift + Enter.
FILTER funkcija
Ja jums ir Excel dinamiskā masīva versija, funkciju FILTER ir daudz vieglāk iegūt visus atbilstošos datus.
