Vispārēja formula
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Kopsavilkums
Lai uzskaitītu unikālas skaitliskās vērtības diapazonā, varat izmantot formulu, kuras pamatā ir funkcijas FREQUENCY, SUM un IF. Parādītajā piemērā darbinieku skaits parādās diapazonā B5: B14. G6 formula ir:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
kas atgriež 2, jo A korpusā ir 2 unikāli darbinieku ID.
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter, ja vien neizmantojat Excel 365.
Paskaidrojums
Piezīme: Pirms Excel 365 Excel nebija īpašas funkcijas, lai uzskaitītu unikālās vērtības. Šī formula parāda vienu veidu, kā skaitīt unikālās vērtības, ja vien tās ir skaitliskas. Ja jums ir teksta vērtības vai teksta un skaitļu kombinācija, jums būs jāizmanto sarežģītāka formula.
Funkcija Excel FREQUENCY atgriež frekvences sadalījumu, kas ir kopsavilkuma tabula, kas satur skaitlisko vērtību biežumu, kas sakārtots "tvertnēs". Mēs to šeit izmantojam kā apļveida ceļu, lai uzskaitītu unikālas skaitliskās vērtības. Lai piemērotu kritērijus, mēs izmantojam IF funkciju.
Strādājot no iekšpuses uz āru, vispirms filtrējam vērtības ar IF funkciju:
IF(C5:C14="A",B5:B14) // filter on building A
Šīs operācijas rezultāts ir šāds masīvs:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Ievērojiet, ka visi B ēkas ID tagad ir FALSE. Šis masīvs tiek tieši piegādāts funkcijai FREQUENCY kā data_array . Par bins_array , mēs piegādājam pašas ID:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Izmantojot šo konfigurāciju, FREQUENCY atgriež zemāk esošo masīvu:
(4;0;0;0;2;0;0;0;0;0;0)
Rezultāts ir nedaudz noslēpumains, bet nozīme ir 905 parādās četras reizes, un 773 parādās divas reizes. FALSE vērtības tiek automātiski ignorētas.
FREQUENCY ir īpaša funkcija, kas automātiski atgriež nulli visiem skaitļiem, kas jau ir parādījušies datu masīvā, tāpēc pēc skaitļa sastādīšanas vērtības ir nulles. Šī ir funkcija, kas ļauj šai pieejai darboties.
Pēc tam tiek pārbaudīts, vai katra no šīm vērtībām ir lielāka par nulli:
(4;0;0;0;2;0;0;0;0;0;0)>0
Rezultāts ir šāds masīvs:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Katra saraksta TRUE apzīmē unikālu numuru sarakstā, un mums vienkārši jāpieskaita TRUE vērtības ar SUM. Tomēr SUM masīvā nepapildinās loģiskās vērtības, tāpēc mums vispirms jāpiespiež vērtības 1 vai nulle. Tas tiek darīts ar dubultnegatīvu (-). Rezultāts ir tikai 1 vai 0 masīvs:
(1;0;0;0;1;0;0;0;0;0;0)
Visbeidzot, SUM saskaita šīs vērtības un atgriež kopējo summu, kas šajā gadījumā ir 2.
Vairāki kritēriji
Jūs varat paplašināt formulu, lai apstrādātu vairākus šādus kritērijus:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
