Vispārēja formula
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
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, kas nokopēta uz leju, ir:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
kur "saraksts" ir nosauktais diapazons B5: B11.
Piezīme: šī ir masīva formula, un tā jāievada, izmantojot vadības taustiņu + shift + enter.
Paskaidrojums
Šīs formulas kodols ir pamata meklēšana ar INDEX:
=INDEX(list,row)
Citiem vārdiem sakot, norādiet INDEX sarakstu un rindas numuru, un INDEX izgūs vērtību, ko pievienot unikālajam sarakstam.
Smagais darbs ir ROW numura noteikšana, lai piešķirtu INDEX, lai mēs iegūtu tikai unikālas vērtības. Tas tiek darīts ar MATCH un COUNTIF, un galvenais triks ir šeit:
COUNTIF($D$4:D4,list)
Šeit COUNTIF saskaita, cik reižu unikālajā sarakstā esošie vienumi parādās galvenajā sarakstā, izmantojot diapazona paplašinošo atsauci $ D $ 4: D4.
Vienā pusē paplašinoša atsauce ir absolūta, no otras puses - relatīva. Šajā gadījumā, formulu nokopējot uz leju, atsauce tiks paplašināta, iekļaujot vairāk rindu unikālajā sarakstā.
Ņemiet vērā, ka atsauce sākas ar D4, vienu rindu virs pirmā unikālā ieraksta unikālajā sarakstā. Tas ir tīši - mēs vēlamies unikālajā sarakstā uzskaitīt vienumus *, kas jau ir *, un mēs nevaram iekļaut pašreizējo šūnu, neveidojot apļveida atsauci. Tātad, mēs sākam no rindas iepriekš.
Svarīgi: pārliecinieties, vai unikālā saraksta virsraksts neparādās galvenajā sarakstā.
COUNTIF kritērijiem mēs izmantojam pašu galveno sarakstu. Piešķirot vairākus kritērijus, COUNTIF masīvā atgriezīs vairākus rezultātus. Katrā jaunajā rindā mums ir atšķirīgs šāds masīvs:
(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
Piezīme: COUNTIF apstrādā vairākus kritērijus ar “OR” saistību (ti, COUNTIF (diapazons, (“sarkans”, “zils”, “zaļš”)) skaita sarkanu, zilu vai zaļu.
Tagad mums ir masīvi, kas mums nepieciešami, lai atrastu pozīcijas (rindu numurus). Lai atrastu nulles vērtības, mēs izmantojam MATCH, kas iestatīts precīzai atbilstībai. Ja iepriekš COUNTIF izveidotos masīvus ievietosim MATCH, mēs to iegūsim šādi:
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 atrod vienumus, meklējot nulles skaitu (ti, meklējot vienumus, kas vēl neparādās unikālajā sarakstā). Tas darbojas, jo MATCH vienmēr atgriež pirmo maču, 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
Izmantojot elastīgo funkciju LOOKUP, varat izveidot formātu bez masīva, lai iegūtu unikālus vienumus:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Formulas uzbūve ir līdzīga iepriekšējai INDEX MATCH formulai, taču LOOKUP ar masīva darbību var rīkoties dabiski.
- COUNTIF atgriež katras vērtības skaitu no saraksta paplašināšanas diapazonā $ D $ 4: D4
- Salīdzinot ar nulli, tiek izveidots TRUE un FALSE vērtību masīvs
- Numurs 1 tiek dalīts ar masīvu, izveidojot masīvu 1s un # DIV / 0 kļūdas
- Šis masīvs kļūst par lookup_vector iekšā LOOKUP
- Uzmeklēšanas vērtība 2 ir lielāka nekā jebkuras vērtības uzmeklēšanas_vektorā
- MEKLĒŠANA sakritīs ar pēdējo kļūdas vērtību uzmeklēšanas masīvā
- LOOKUP atgriež rezultātu_vector atbilstošo vērtību, nosaukto diapazonu “saraksts”
Izvelciet vienumus, kas parādās tikai vienu reizi
Iepriekšminēto formulu LOOKUP ir viegli paplašināt, izmantojot loģisko loģiku. Lai izgūtu unikālo vienumu sarakstu, kas avota datos parādās tikai vienu reizi, varat izmantot šādu formulu:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Vienīgais papildinājums ir otrā COUNTIF izteiksme:
COUNTIF(list,list)=1
Šeit COUNTIF atgriež šādu vienību skaitu:
(2;2;2;2;2;1;2)
kas tiek salīdzināti ar 1, kā rezultātā tiek iegūts TRUE / FALSE vērtību masīvs:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
kas darbojas kā "filtrs", lai ierobežotu izvadi uz vienumiem, kas avota datos parādās tikai vienu reizi.
