Bendroji formulė
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Santrauka
Norėdami iš sąrašo ar stulpelio išskleisti tik unikalias reikšmes, galite naudoti masyvo formulę, pagrįstą INDEX, MATCH ir COUNTIF. Parodytame pavyzdyje D5 formulė, nukopijuota žemiau, yra:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
kur „sąrašas“ yra pavadintas diapazonas B5: B11.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti naudojant „Control + Shift“ + „Enter“.
Paaiškinimas
Šios formulės esmė yra pagrindinė paieška su INDEX:
=INDEX(list,row)
Kitaip tariant, suteikite INDEX sąrašą ir eilutės numerį, o INDEX gaus vertę, kurią norite pridėti prie unikalaus sąrašo.
Sunkus darbas yra išsiaiškinti ROW numerį, kuris suteiks INDEX, kad gautume tik unikalias vertes. Tai daroma naudojant MATCH ir COUNTIF, o pagrindinis triukas yra čia:
COUNTIF($D$4:D4,list)
Čia COUNTIF suskaičiuoja, kiek kartų elementai, esantys jau unikaliame sąraše, rodomi pagrindiniame sąraše, naudojant išplėstinę diapazono nuorodą $ D $ 4: D4.
Besiplečianti nuoroda yra absoliuti vienoje pusėje, palyginti su kita. Šiuo atveju, kai formulė nukopijuojama žemyn, nuoroda išsiplės, įtraukdama daugiau eilučių į unikalų sąrašą.
Atkreipkite dėmesį, kad nuoroda prasideda D4, viena eilute virš pirmo unikalaus įrašo, unikaliame sąraše. Tai yra sąmoninga - norime suskaičiuoti elementus *, kurie jau yra *, į unikalų sąrašą ir negalime įtraukti dabartinės langelio, nesukūrę žiedinės nuorodos. Taigi, mes pradedame nuo eilutės aukščiau.
Svarbu: įsitikinkite, kad unikalaus sąrašo antraštė nerodoma pagrindiniame sąraše.
Pagal COUNTIF kriterijus naudojame patį pagrindinį sąrašą. Nurodžius kelis kriterijus, COUNTIF pateiks kelis masyvo rezultatus. Kiekvienoje naujoje eilutėje turime skirtingą masyvą:
(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
Pastaba: „COUNTIF“ tvarko kelis kriterijus su sąsaja „OR“ (ty COUNTIF (diapazonas, („raudona“, „mėlyna“, „žalia“)) skaičiuoja raudoną, mėlyną arba žalią.
Dabar turime masyvus, kuriuos turime rasti pozicijoms (eilių numeriams). Tam mes naudojame MATCH, sukurtą tiksliajai atitikčiai, norėdami rasti nulines reikšmes. Jei aukščiau esančio COUNTIF sukurtus masyvus įdėsime į MATCH, tai gausime:
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“ suranda elementus ieškodamas nulio skaičiaus (ty ieškodamas elementų, kurie dar nerodomi unikaliame sąraše). Tai veikia, nes MATCH visada grąžina pirmąsias rungtynes, kai yra dublikatų.
Galiausiai pozicijos įvedamos į INDEX kaip eilutės numeriai, o INDEX grąžina pavadinimą toje pozicijoje.
Ne masyvo versija su LOOKUP
Galite sukurti ne masyvo formulę, kad išskirtumėte unikalius elementus naudodamiesi lanksčia LOOKUP funkcija:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Formulės konstrukcija yra panaši į aukščiau pateiktą INDEX MATCH formulę, tačiau LOOKUP masyvo operaciją gali atlikti natūraliai.
- COUNTIF pateikia kiekvienos reikšmės iš „sąrašo“ skaičiaus besiplečiančiame diapazone $ D $ 4: D4
- Palyginus su nuliu, sukuriama TRUE ir FALSE reikšmių masyvas
- Skaičius 1 padalinamas iš masyvo, sukuriant masyvą 1s ir # DIV / 0 klaidų
- Šis masyvas tampa „lookup_vector“ LOOKUP viduje
- 2 paieškos vertė yra didesnė nei bet kurios reikšmės, esančios „lookup_vector“
- LOOKUP atitiks paskutinę ne masyvo reikšmę paieškos masyve
- LOOKUP grąžina atitinkamą rezultato_vektoriaus reikšmę, pavadintą diapazoną „sąrašas“
Ištraukite elementus, kurie pasirodo tik vieną kartą
Aukščiau pateiktą LOOKUP formulę lengva išplėsti naudojant loginę logiką. Norėdami išskleisti unikalių elementų, kurie šaltinio duomenyse rodomi tik vieną kartą, sąrašą, galite naudoti tokią formulę:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Vienintelis papildymas yra antroji COUNTIF išraiška:
COUNTIF(list,list)=1
Čia COUNTIF pateikia tokių elementų masyvą:
(2;2;2;2;2;1;2)
kurie lyginami su 1, todėl gaunama TRUE / FALSE reikšmių masyvas:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
kurie veikia kaip „filtras“, kad išvestis būtų apribota elementais, kurie šaltinio duomenyse pasitaiko tik vieną kartą.
