Excel

Izņemiet no saraksta unikālus priekšmetus

Extract Unique Items From List

Excel formula: iegūstiet unikālus vienumus no sarakstaVispārīgā formula | _+_ | Kopsavilkums

Lai no saraksta vai kolonnas iegūtu tikai unikālas vērtības, varat izmantot masīva formulu, kuras pamatā ir INDEX, MATCH un COUNTIF. Parādītajā piemērā D5 formula, nokopēta uz leju, ir šāda:





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

kur 'saraksts' ir nosaukts diapazons B5: B11.

Piezīme: tas ir masīva formula un tas ir jāievada, izmantojot taustiņu kombināciju control + shift + enter.





kā noapaļot vērtības Excel
Skaidrojums

Šīs formulas pamatā ir pamata uzmeklēšana ar INDEX:

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

Citiem vārdiem sakot, norādiet INDEX sarakstu un rindas numuru, un INDEX iegūs vērtību, ko pievienot unikālajam sarakstam.



Smagais darbs ir noskaidrot Rindas numuru, lai dotu INDEX, lai mēs iegūtu tikai unikālas vērtības. Tas tiek darīts, izmantojot MATCH un COUNTIF, un galvenais triks ir šeit:

 
= INDEX (list,row)

Lūk, C.OUNTIF saskaita, cik reizes unikālajā sarakstā esošie vienumi parādās galvenajā sarakstā, izmantojot paplašinot atsauci diapazonam,$ 4 $: D4.

Paplašināta atsauce ir abšķīsts vienā pusē, radinieks otrā. Šajā gadījumā, kad formula tiek nokopēta, atsauce tiks paplašināta, iekļaujot vairāk rindu unikālajā sarakstā.

Ievērojietatsaucesākas D4,viena rinda virs pirmais unikālais ieraksts unikālajā sarakstā. Tas ir apzināti - mēs vēlamies uzskaitīt vienumus * jau * unikālajā sarakstā, un mēs nevaram iekļaut pašreizējo šūnu, neradot apļveida atsauci. Tātad, mēs sākam iepriekšējā rindā.

Svarīgi: pārliecinieties, ka unikālā saraksta virsraksts nav redzams galvenajā sarakstā.

COUNTIF kritērijiem mēs izmantojam pašu galveno sarakstu. Ja tiek doti vairāki kritēriji, COUNTIF atgriezīs vairākus rezultātus masīvs . Katrā jaunā rindā mums ir atšķirīgs masīvs:

 
 COUNTIF ($D:D4,list)

Piezīme. COUNTIF apstrādā vairākus kritērijus ar saistību “VAI” (ti, COUNTIF (diapazons, {'sarkans', 'zils', 'zaļš'})) skaitās sarkans, zils vai zaļš.

izmantojiet komandu izveidot no atlases, lai izveidotu nosaukto diapazonu datu tabulai

Tagad mums ir masīvi, kas nepieciešami, lai atrastu pozīcijas (rindu numurus). Šim nolūkam mēs izmantojam MATCH, kas iestatīta precīzai atbilstībai, lai atrastu nulles vērtības. Ja COUNTIF iepriekš izveidotos masīvus ievietojam MATCH, mēs iegūstam tālāk norādīto.

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

MATCH atrod vienumus, meklējot nulles skaitli (t.i., meklējot vienumus, kas vēl neparādās unikālajā sarakstā). Tas darbojas, jo MATCH vienmēr atgriež pirmo atbilstību, ja ir dublikāti.

Visbeidzot, pozīcijas tiek ievadītas INDEX kā rindu numuri, un INDEX atgriež nosaukumu šajā pozīcijā.

Bez masīva versija ar LOOKUP

Varat izveidot formātu, kas nav masīvs, lai iegūtu unikālus vienumus, izmantojot elastīgo LOOKUP funkciju:

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

Formulas uzbūve ir līdzīga iepriekš minētajai INDEX MATCH formulai, taču LOOKUP var apstrādāt masīva darbību sākotnēji.

  • COUNTIF atgriež katras vērtības skaitļus no saraksta diapazona paplašināšana $ 4 $: D4
  • Salīdzinot ar nulli, tiek izveidots TRUE un FALSE vērtību masīvs
  • Skaitlis 1 tiek dalīts ar masīvu, izveidojot 1s un #DIV/0 kļūdu masīvu
  • Šis masīvs kļūst par lookup_vector iekš LOOKUP
  • Uzmeklēšanas vērtība 2 ir lielāka nekā jebkura vērtība lookup_vector
  • MEKLĒŠANA sakrīt ar pēdējo bez kļūdas vērtību uzmeklēšanas masīvā
  • MEKLĒŠANA atgriež atbilstošo vērtību rezultāta vektorā, nosauktajā diapazonā “saraksts”

Izvelciet vienumus, kas parādās tikai vienu reizi

Iepriekš minēto LOOKUP formulu ir viegli pagarināt Būla loģika . Lai iegūtu unikālu vienumu sarakstu, kas avota datos parādās tikai vienu reizi, varat izmantot šādu formulu:

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

Vienīgais papildinājums ir COUNTIF otrā izteiksme:

kā aprēķināt irr uz excel
 
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)

Šeit COUNTIF atgriež šādu vienību masīvu:

 
 COUNTIF (list,list)=1

ko salīdzina ar 1, iegūstot masīvu ar TRUE/FALSE vērtībām:

 
{2222212}

kas darbojas kā “filtrs”, lai ierobežotu izvadi līdz vienumiem, kas avota datos parādās tikai vienu reizi.

In Excel 365 , UNIKĀLA funkcija ir labākais veids, kā iegūt unikālas vērtības.

Autors Deivs Bruns


^