Excel képlet: Az összes egyezés kibontása segítő oszloppal -

Általános képlet

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

Összegzés

Az Excelben több egyezés kinyerésének egyik módja az INDEX és a MATCH használata egy segítő oszloppal, amely megjelöli az egyező adatokat. Ezzel elkerülhető a fejlettebb tömbképlet összetettsége. A bemutatott példában a H6 képlete a következő:

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

ahol a ct (G3), az adatok (B3: E52) és a segítő (E3: E52) tartományokat neveznek meg.

Magyarázat

Az egynél több mérkőzést lekereső képletekkel történő kihívás a duplikátumok (azaz több egyezés) kezelése. Az olyan keresési képletek, mint a VLOOKUP és az INDEX + MATCH, könnyen megtalálják az első mérkőzést, de sokkal nehezebb megkeresni az "összes mérkőzést", ha a kritériumok egynél több találatot találnak.

Ez a képlet úgy foglalkozik ezzel a kihívással, hogy egy segítő oszlopot használ, amely olyan numerikus értéket ad vissza, amely több gyufa egyszerű kinyerésére használható. A segítő oszlop képlete így néz ki:

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

A segítő oszlop az adatok minden sorát teszteli, hogy a C oszlop osztálya megegyezik-e az I3 értékével, a D oszlopban található épület pedig a J3 értékével. Mindkét logikai tesztnek vissza kell adnia az IGAZ értéket, hogy az ÉS visszaadja az IGAZ értéket.

Minden sorhoz az AND függvény eredményét hozzáadjuk a segítő oszlop "fenti értékéhez", hogy létrehozzuk a számlálást. Ennek a képletnek a gyakorlati hatása egy növekvő számláló, amely csak akkor változik, ha (új) egyezést találnak. Ezután az érték ugyanaz marad, amíg a következő mérkőzést meg nem találják. Ez azért működik, mert az IGAZ / HAMIS eredményeket az AND adja vissza, az 1/0 értékekre kényszerítik az összeg művelet részeként. A FALSE eredmények nem adnak semmit, az IGAZ eredmények pedig 1-et.

Visszatérve a kinyerési területre, a H oszlopban található Név keresési képlete így néz ki:

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

Belülről kifelé haladva a képlet INDEX + MATCH része megkeresi az első talált egyezés nevét, a G oszlop sorszámát használva egyezési értékként:

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

Az INDEX mind a 3 adatoszlopot tömbként kapja meg (tartomány neve "data"), a MATCH pedig úgy van konfigurálva, hogy pontos egyezési módban (a 3. argumentum nullára állítva) egyezzen a segítő oszlopon belüli sorszámmal (a megnevezett tartomány "helper"). .

Itt nyilvánvalóvá válik a képlet okossága. A segítő oszlop nyilvánvalóan duplikátumokat tartalmaz, de ez nem számít, mert a MATCH csak az első értéknek felel meg. Tervezés szerint minden "első érték" megfelel az adattábla megfelelő sorának.

Az I. és J oszlop képletei megegyeznek H-val, kivéve az oszlopszámot, amelyet minden esetben eggyel növelünk.

Az IF utasítás, amely beburkolja az INDEX / MATCH képletet, egyszerű funkciót hajt végre - ellenőrzi a kibontási terület minden egyes sorszámát, hogy a sor száma kisebb-e vagy egyenlő-e a G3-ban megadott értékkel (nevezett tartomány: "ct"), amely az összes egyező rekord teljes száma. Ha igen, akkor az INDEX / MATCH logika fut. Ha nem, akkor az IF üres karakterláncot ad ki ("").

A G3 képlete (amelynek neve: "ct") egyszerű:

=MAX(helper)

Mivel a segítő oszlopban a maximális érték megegyezik a teljes meccsszámmal, a MAX funkcióra van szükségünk.

Megjegyzés: A kinyerési területet manuálisan kell konfigurálni, hogy a lehető legtöbb adatot kezelje (pl. 5, 10, 20, stb.). Ebben a példában csak 5 sorra van korlátozva, hogy a munkalap kompakt maradjon.

Ezt a technikát Mike Girvin Control + Shift + Enter könyvében tanultam meg.