Ištraukite unikalius elementus iš sąrašo

Norėdami iš sąrašo ar stulpelio išskirti tik unikalias vertes, galite naudoti masyvo formulę, pagrįstą INDEX, MATCH ir COUNTIF. Pateiktame pavyzdyje D5 formulė, nukopijuota, yra tokia:

{= INDEX (list, MATCH (0, COUNTIF (uniques,list),0))}

kur „sąrašas“ yra pavadintas diapazonas B5: B11.

Pastaba: tai yra masyvo formulė ir turi būti įvestas naudojant „Control“ + „Shift“ + „Enter“.

„Excel“ formulė dienoms tarp datų

Šios formulės esmė yra pagrindinė paieška su INDEX:

 
{= INDEX (list, MATCH (0, COUNTIF ($D:D4,list),0))}

Kitaip tariant, nurodykite INDEX sąrašą ir eilutės numerį, o INDEX nuskaitys vertę, kurią pridės prie unikalaus sąrašo.

Sunkus darbas yra išsiaiškinti eilutės numerį, kad būtų pateiktas INDEX, kad gautume tik unikalias vertes. Tai daroma naudojant MATCH ir COUNTIF, o pagrindinis triukas yra čia:

 
= INDEX (list,row)

Čia, C.OUNTIF skaičiuoja, kiek kartų unikaliame sąraše esantys elementai pasirodo pagrindiniame sąraše, naudojant besiplečianti nuoroda asortimentui,$ 4 $: D4.

Plečianti nuoroda yra abtirpus vienoje pusėje, giminaitis kitoje. Tokiu atveju, nukopijavus formulę, nuoroda bus išplėsta ir į unikalų sąrašą įtraukta daugiau eilučių.

Atkreipkite dėmesį įnuorodaprasideda D4,viena eilutė aukščiau pirmasis unikalus įrašas unikaliame sąraše. Tai tyčia - mes norime suskaičiuoti elementus * jau * unikaliame sąraše ir negalime įtraukti dabartinio langelio nesukūrę apskritos nuorodos. Taigi, mes pradedame nuo eilutės aukščiau.

Svarbu: įsitikinkite, kad unikaliojo sąrašo antraštė nerodoma pagrindiniame sąraše.

COUNTIF kriterijams naudojame patį pagrindinį sąrašą. Pateikus kelis kriterijus, COUNTIF pateiks kelis rezultatus masyvas . Kiekvienoje naujoje eilutėje turime tokį masyvą:

 
 COUNTIF ($D:D4,list)

Pastaba: „COUNTIF“ tvarko kelis kriterijus, turinčius ryšį „ARBA“ (t. Y. COUNTIF (diapazonas, {„raudona“, „mėlyna“, „žalia“})) skaičiuoja raudoną, mėlyną arba žalią.

Excel formulė mažesnė arba lygi

Dabar mes turime masyvus, kurių mums reikia norint rasti pozicijas (eilučių numerius). Tam mes naudojame MATCH, nustatytą tiksliam atitikimui, kad rastume nulines reikšmes. Jei COUNTIF sukurtus masyvus įdėsime į MATCH, štai ką gausime:

 
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // row 8

„MATCH“ suranda elementus ieškodama nulio skaičiaus (t. Y. Ieškodama elementų, kurie dar nepasirodė unikaliame sąraše). Tai veikia, nes „MATCH“ visada grąžina pirmąsias rungtynes, kai yra dublikatų.

Galiausiai, pozicijos įtraukiamos į INDEX kaip eilutės numeriai, o INDEX grąžina tos pozicijos pavadinimą.

Ne masyvo versija su LOOKUP

Naudodami lanksčią LOOKUP funkciją galite sukurti ne masyvo formulę, kad išskirtumėte unikalius elementus:

 
 MATCH (0,{0000000},0) // 1 (Joe)  MATCH (0,{1000100},0) // 2 (Bob)  MATCH (0,{1100101},0) // 3 (Sue)  MATCH (0,{1111101},0) // 6 (Aya)

Formulės konstrukcija yra panaši į aukščiau pateiktą INDEX MATCH formulę, tačiau LOOKUP gali tinkamai atlikti masyvo operaciją.

• „COUNTIF“ grąžina kiekvienos „iš sąrašo“ vertės reikšmes plečiantis asortimentas $ 4 $: D4
• Lyginant su nuliu, sukuriamas TRUE ir FALSE reikšmių masyvas
• Skaičius 1 padalijamas iš masyvo, sukuriamas 1s ir #DIV/0 klaidų masyvas
• Šis masyvas tampa lookup_vector viduje LOOKUP
• Peržiūros vertė 2 yra didesnė už bet kokias lookup_vector reikšmes
• LOOKUP atitiks paskutinę be klaidų reikšmę paieškos masyve
• LOOKUP grąžina atitinkamą reikšmę rezultato_vektoriuje, pavadintame diapazone „sąrašas“

Ištraukite elementus, kurie pasirodo tik vieną kartą

Aukščiau pateiktą LOOKUP formulę lengva išplėsti loginė logika . Norėdami išskirti unikalių elementų, kurie tik vieną kartą rodomi šaltinio duomenyse, sąrašą, galite naudoti tokią formulę:

 
= LOOKUP (2,1/( COUNTIF ($D:D4,list)=0),list)

Vienintelis papildymas yra antroji COUNTIF išraiška:

„excel counttif“ yra didesnis arba lygus

 
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)

Čia COUNTIF pateikia tokį elementų skaičiaus masyvą:

 
 COUNTIF (list,list)=1

kurie lyginami su 1, todėl gaunamas TIKROS/NETIKROS reikšmių masyvas:

 
{2222212}

kurie veikia kaip „filtras“ ir apriboja išvestį iki elementų, kurie yra tik vieną kartą šaltinio duomenyse.

In „Excel 365“ , UNIKALI funkcija yra geriausias būdas išgauti unikalias vertybes.