Általános képlet
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Összegzés
Ha csak egyedi értékeket szeretne kinyerni egy listából vagy oszlopból, használhatja az INDEX, MATCH és COUNTIF alapú tömbképletet. A bemutatott példában a D5 képlete le van másolva:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
ahol a "lista" a B5: B11 elnevezett tartomány.
Megjegyzés: ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
Magyarázat
Ennek a képletnek a lényege egy alapkeresés az INDEX használatával:
=INDEX(list,row)
Más szavakkal, adja meg az INDEX-nek a listát és a sorszámot, és az INDEX lekér egy értéket, amelyet hozzáad az egyedi listához.
A kemény munka az INDEX megadásához szükséges ROW szám kitalálása, hogy csak egyedi értékeket kapjunk. Ez a MATCH és a COUNTIF használatával történik, és a fő trükk itt van:
COUNTIF($D$4:D4,list)
Itt a COUNTIF megszámolja, hogy az egyedi listában már szereplő elemek hányszor jelennek meg a fő listában, a tartomány kibővítő hivatkozásával, $ D $ 4: D4.
A táguló referencia az egyik oldalon abszolút, a másikon relatív. Ebben az esetben a képlet lefelé másolásával a hivatkozás kibővül, és további sorokat tartalmaz az egyedi listában.
Vegye figyelembe, hogy a hivatkozás a D4-ben kezdődik, egy sorral az első egyedi bejegyzés felett, az egyedi listában. Ez szándékos - szeretnénk * már * tételeket megszámolni az egyedi listában, és nem vehetjük fel az aktuális cellát körkörös hivatkozás létrehozása nélkül. Tehát a fenti sorban kezdjük.
Fontos: győződjön meg arról, hogy az egyedi lista címsora nem jelenik meg a fő listában.
A COUNTIF kritériumaihoz magát a fő listát használjuk. Több kritérium megadása esetén a COUNTIF több eredményt ad vissza egy tömbben. Minden új sorban más és más tömb áll rendelkezésünkre:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Megjegyzés: A COUNTIF több feltételt kezel "VAGY" kapcsolattal (azaz a COUNTIF (tartomány, ("piros", "kék", "zöld")) piros, kék vagy zöld színnel számol.
Megvannak a tömbök, amelyekre szükségünk van a pozíciók (sorszámok) megkereséséhez. Ehhez a pontos egyezésre beállított MATCH-ot használjuk, hogy nulla értéket találjunk. Ha a COUNTIF fenti tömbjeit a MATCH-ba tesszük, a következőket kapjuk:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
A MATCH az elemeket úgy keresi meg, hogy nulla számot keres (azaz olyan elemeket keres, amelyek még nem jelennek meg az egyedi listában). Ez működik, mert a MATCH mindig megadja az első mérkőzést, ha vannak duplikátumok.
Végül a pozíciók sorszámként kerülnek az INDEX-be, és az INDEX visszaadja a nevet ebben a pozícióban.
Nem tömb verzió LOOKUP-tal
Nem tömbös képletet készíthet egyedi elemek kinyerésére a rugalmas LOOKUP funkció használatával:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
A képlet felépítése hasonló a fenti INDEX MATCH képlethez, de a LOOKUP natív módon képes kezelni a tömb műveletet.
- A COUNTIF a $ D $ 4: D4 kibővülő tartományban lévő "lista" értékeinek eredményeit adja vissza
- A nullához viszonyítva TRUE és FALSE értékek tömbje jön létre
- Az 1-es szám el van osztva a tömbnel, így 1-es és # DIV / 0 hibás tömb jön létre
- Ez a tömb lesz a lookup_vector a LOOKUP belsejében
- A 2 keresési értéke nagyobb, mint a look_vector bármely értéke
- A LOOKUP megegyezik a keresési tömb utolsó nem hibás értékével
- A LOOKUP a result_vector megfelelő értékét adja vissza, a "list" nevű tartományt
Csak egyszer megjelenő elemek kivonása
A fenti LOOKUP képlet könnyen bővíthető logikai logikával. A forrásadatokban csak egyszer megjelenő egyedi elemek listájának kibontásához használhatja a következő képletet:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Az egyetlen kiegészítés a második COUNTIF kifejezés:
COUNTIF(list,list)=1
Itt a COUNTIF egy ilyen számú tömböt ad vissza:
(2;2;2;2;2;1;2)
amelyeket 1-hez hasonlítunk, így IGAZ / HAMIS értékek tömböt kapunk:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
amelyek "szűrőként" működnek, hogy a kimenetet a forrásadatokban csak egyszer előforduló elemekre korlátozzák.
