Vispārēja formula
=SUMPRODUCT(1/COUNTIF(data,data))
Kopsavilkums
Lai uzskaitītu unikālo vērtību skaitu šūnu diapazonā, varat izmantot formulu, kuras pamatā ir funkcijas COUNTIF un SUMPRODUCT. Izrādes piemērā F6 formula ir šāda:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Paskaidrojums
Strādājot no iekšpuses, COUNTIF ir konfigurēts vērtībām diapazonā B5: B14, kā kritērijus izmantojot visas šīs pašas vērtības:
COUNTIF(B5:B14,B5:B14)
Tā kā mēs piešķiram 10 kritēriju vērtības, mēs iegūstam masīvu ar 10 šādiem rezultātiem:
(3;3;3;2;2;3;3;3;2;2)
Katrs skaitlis apzīmē skaitu - "Jim" parādās 3 reizes, "Sue" parādās 2 reizes utt.
Šis masīvs ir konfigurēts kā dalītājs ar skaitītāju 1. Pēc sadalīšanas mēs iegūstam vēl vienu masīvu:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Visas vērtības, kas diapazonā notiek tikai vienu reizi, parādīsies kā 1s, bet vērtības, kas notiek vairākas reizes, tiks rādītas kā daļējas vērtības, kas atbilst vairākām. (ti, vērtība, kas datos parādās 4 reizes, ģenerēs 4 vērtības = 0,25).
Visbeidzot, funkcija SUMPRODUCT summē visas masīva vērtības un atgriež rezultātu.
Darbība ar tukšajām šūnām
Viens veids, kā rīkoties ar tukšām vai tukšām šūnām, ir pielāgot formulu šādi:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Savienojot datiem tukšu virkni (""), mēs novēršam nulles nonākšanu COUNTIF izveidotajā masīvā, ja datos ir tukšas šūnas. Tas ir svarīgi, jo nulle dalītājā izraisīs formulas # DIV / 0 kļūdu. Tas darbojas, jo, izmantojot kritērijiem tukšu virkni (""), tiks skaitītas tukšas šūnas.
Tomēr, lai gan šī formulas versija neizraisīs kļūdu # DIV / 0, izmantojot tukšas šūnas, tajā tiks iekļautas tukšās šūnas. Ja vēlaties no skaita izslēgt tukšās šūnas, izmantojiet:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Tā rezultātā tiek atcelts tukšo šūnu skaits, padarot skaitītāju nulli saistītajam skaitam.
Lēna veiktspēja?
Šī ir forša un eleganta formula, taču tā tiek aprēķināta daudz lēnāk nekā formulas, kas unikālo vērtību skaitīšanai izmanto FREQUENCY. Lielākām datu kopām varat pārslēgties uz formulu, kuras pamatā ir funkcija FREQUENCY. Šeit ir skaitlisko vērtību formula un teksta vērtību formula.
