Vispārēja formula
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Kopsavilkums
Lai uzskaitītu unikālas teksta vērtības diapazonā, varat izmantot formulu, kurā tiek izmantotas vairākas funkcijas: FREQUENCY, MATCH, ROW un SUMPRODUCT. Parādītajā piemērā F5 formula ir:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
kas atgriež 4, jo B5 ir 4 unikāli nosaukumi: B14.
Piezīme. Cits veids, kā uzskaitīt unikālās vērtības, ir izmantot funkciju COUNTIF. Šī ir daudz vienkāršāka formula, taču tā var darboties lēnām lielās datu kopās. Izmantojot Excel 365, varat izmantot vienkāršāku un ātrāku formulu, kuras pamatā ir UNIKĀLA.
Paskaidrojums
Šī formula ir sarežģītāka nekā līdzīga formula, kas izmanto FREQUENCY unikālo skaitlisko vērtību skaitīšanai, jo FREQUENCY nedarbojas ar skaitliskām vērtībām. Rezultātā liela daļa formulas vienkārši pārveido skaitliskos datus skaitliskos datos, ar kuriem FREQUENCY var rīkoties.
Strādājot no iekšpuses uz āru, funkcija MATCH tiek izmantota, lai iegūtu katra elementa pozīciju, kas parādās datos:
MATCH(B5:B14,B5:B14,0)
MATCH rezultāts ir šāds masīvs:
(1;1;1;4;4;6;6;6;9;9)
Tā kā MATCH vienmēr atgriež pirmās atbilstības pozīciju, vērtības, kas datos parādās vairāk nekā vienu reizi, atgriež to pašu pozīciju. Piemēram, tā kā sarakstā “Džims” parādās 3 reizes, viņš šajā masīvā 3 reizes parādās kā skaitlis 1.
Šis masīvs tiek ievadīts frekvencē FREQUENCY kā arguments datu_darījums . Bins_array arguments ir veidots no šo daļu ar formulu:
ROW(B5:B14)-ROW(B5)+1)
kas veido secīgu skaitļu sarakstu katrai datu vērtībai:
(1;2;3;4;5;6;7;8;9;10)
Šajā brīdī FREQUENCY tiek konfigurēts šādi:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY atgriež skaitļu masīvu, kas norāda katra datu masīva skaitļa skaitu, sakārtotu pēc bin. Kad skaitlis jau ir saskaitīts, FREQUENCY atgriezīs nulli. Šī ir galvenā iezīme šīs formulas darbībā. FREQUENCY rezultāts ir šāds masīvs:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Piezīme: FREQUENCY vienmēr atgriež masīvu ar vēl vienu vienumu nekā bins_array .
Tagad mēs varam pārrakstīt formulu šādi:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Pēc tam mēs pārbaudām vērtības, kas ir lielākas par nulli (> 0), kas pārvērš skaitļus par TRUE vai FALSE, pēc tam izmantojiet dubultnegatīvu (-), lai TRUE un FALSE vērtības pārvērstu par 1s un 0s. Tagad mums ir:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Visbeidzot, SUMPRODUCT vienkārši saskaita skaitļus un atgriež kopējo summu, kas šajā gadījumā ir 4.
Darbība ar tukšajām šūnām
Iztukšojot diapazona šūnas, formula atgriezīs kļūdu # N / A. Lai apstrādātu tukšas šūnas, varat izmantot sarežģītāku masīva formulu, kas izmanto IF funkciju, lai filtrētu tukšās vērtības:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Piezīme: IF pievienošana padara to masīva formulā, kurai nepieciešama control-shift-enter.
Plašāku informāciju skatiet šajā lapā.
Citi veidi, kā uzskaitīt unikālās vērtības
Ja jums ir programma Excel 365, varat izmantot funkciju UNIKĀLA, lai uzskaitītu unikālās vērtības ar daudz vienkāršāku formulu.
Pagrieziena tabula ir arī lielisks veids, kā uzskaitīt unikālās vērtības.
