Üldine valem
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Kokkuvõte
Ainult unikaalsete väärtuste väljavõtmiseks loendist või veerust saate kasutada massiivivalemit, mis põhineb INDEX, MATCH ja COUNTIF. Näidatud näites on D5 valem, mis on kopeeritud, järgmine:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
kus "nimekiri" on nimega vahemik B5: B11.
Märkus: see on massiivi valem ja see tuleb sisestada, kasutades juhtklahvi + shift + enter.
Selgitus
Selle valemi tuum on INDEX-iga põhiline otsing:
=INDEX(list,row)
Teisisõnu, andke INDEX-ile loend ja rea number ja INDEX otsib väärtuse, mida unikaalsesse loendisse lisada.
Raske töö on INDEXi andmiseks ROW-numbri nuputamine, et saaksime ainult unikaalseid väärtusi. Seda tehakse MATCHi ja COUNTIF-iga ning peamine trikk on siin:
COUNTIF($D$4:D4,list)
Siin loeb COUNTIF, mitu korda ainulaadses loendis olevad üksused ilmuvad põhiloendis, kasutades vahemiku laiendavat viidet $ D $ 4: D4.
Laienev viide on ühelt poolt absoluutne, teiselt poolt suhteline. Sel juhul laieneb valemi allapoole viide, et lisada unikaalsesse loendisse rohkem ridu.
Pange tähele, et viide algab D4-st, üks rida unikaalse loendi esimese ainulaadse kirje kohal. See on tahtlik - me tahame ainulaadses loendis üksusi * juba * loendada ja me ei saa praegust lahtrit lisada ilma ringviite loomiseta. Niisiis, alustame ülaltoodud real.
Tähtis: veenduge, et ainulaadse loendi pealkirja ei kuvata põhiloendis.
COUNTIF-i kriteeriumide jaoks kasutame põhiloendit ennast. Kui on antud mitu kriteeriumi, tagastab COUNTIF massiivis mitu tulemust. Igal uuel real on meil järgmine massiiv:
(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
Märkus. COUNTIF tegeleb mitme kriteeriumiga suhtega "OR" (st COUNTIF (vahemik, ("punane", "sinine", "roheline")) loeb punast, sinist või rohelist.
Nüüd on meil massiivid, mida peame leidma positsioonide (rea numbrite) leidmiseks. Selleks kasutame nullväärtuste leidmiseks täpse vaste jaoks seadistatud MATCH-i. Kui me paneme COUNTIFi loodud massiivid ülal MATCH-i, siis saame siin:
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)
MATCH otsib üksused üles, otsides nullide arvu (st otsides üksusi, mida veel unikaalses loendis ei kuvata). See töötab, sest MATCH tagastab alati esimese vaste duplikaatide olemasolul.
Lõpuks sisestatakse positsioonid INDEX-i rea numbritena ja INDEX tagastab sellel positsioonil oleva nime.
Massiivivaba versioon LOOKUP-iga
Paindliku funktsiooni LOOKUP abil saate unikaalsete üksuste väljavõtmiseks luua mittemassiivse valemi:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Valemi ülesehitus sarnaneb ülaltoodud valemiga INDEX MATCH, kuid LOOKUP saab massiivioperatsiooni käsitleda loomulikult.
- COUNTIF tagastab laieneva vahemiku $ D $ 4: D4 kõigi väärtuste loendi loendist
- Nulliga võrdlemine loob massiivi TRUE ja FALSE väärtusi
- Number 1 jagatakse massiiviga, luues massiivi 1s ja # DIV / 0 viga
- Sellest massiivist saab otsingu_vektor LOOKUPi sees
- Otsingu väärtus 2 on suurem kui mis tahes väärtused otsinguvektoris
- LOOKUP vastab otsingumassiivi viimasele veatule väärtusele
- LOOKUP tagastab tulemuse_vektoris vastava väärtuse, nimega vahemiku "nimekiri"
Eemaldage üks kord ilmuvad üksused
Eespool toodud valikut LOOKUP on loogika abil lihtne laiendada. Ainult üks kord lähteandmetes kuvatavate unikaalsete üksuste loendi väljavõtmiseks võite kasutada järgmist valemit.
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Ainus lisand on teine COUNTIF-avaldis:
COUNTIF(list,list)=1
Siin tagastab COUNTIF massiivi üksuste loendusi järgmiselt:
(2;2;2;2;2;1;2)
mida võrreldakse 1-ga, mille tulemuseks on massiivi TÕENE / VÄÄR:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
mis toimivad "filtrina", et piirata väljundit üksustega, mis esinevad lähteandmetes vaid ühe korra.
