Saskaitiet unikālas teksta vērtības ar kritērijiem

Lai saskaitītu unikālas teksta vērtības diapazonā ar kritērijiem, varat izmantot masīva formulu, kuras pamatā ir BIEŽUMS un MATCH funkcijas. Parādītajā piemērā G6 formula ir šāda:

{= SUM (--( FREQUENCY ( IF (criteria, MATCH (vals,vals,0)), ROW (vals)- ROW (vals.first)+1)>0))}

kas atgriež 3, jo pie projekta Omega strādāja trīs dažādi cilvēki.

Piezīme: šī ir masīva formula un jāievada ar taustiņu + shift + enter.

Šī ir sarežģīta formula, kas izmanto FREQUENCY, lai saskaitītu skaitliskās vērtības, kas iegūtas, izmantojot funkciju MATCH. Strādājot no iekšpuses, MATCH funkcija tiek izmantota, lai iegūtu katras datos redzamās vērtības pozīciju:

 
{= SUM (--( FREQUENCY ( IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0)), ROW (B5:B11)- ROW (B5)+1)>0))}

MATCH rezultāts ir šāds masīvs:

 
 MATCH (B5:B11,B5:B11,0)

Tā kā MATCH vienmēr atgriež pozīciju pirmais atbilstība, vērtības, kas vairāk nekā vienu reizi parādās datos, atgriež to pašu pozīciju. Piemēram, tā kā “Džims” sarakstā parādās 4 reizes, viņš šajā masīvā parādās 4 reizes kā skaitlis 1.

Ārpus MATCH funkcijas, IF funkcija tiek izmantots, lai piemērotu kritērijus, kas šajā gadījumā ietver pārbaudi, vai projekts ir “omega” (no šūnas G5):

 
{1131167}

Funkcija IF darbojas kā filtrs, ļaujot MATCH vērtībām iziet cauri tikai tad, ja tās ir saistītas ar “omega”. Rezultāts ir šāds masīvs:

 
 IF (C5:C11=G5 // filter on 'omega'

Filtrētais masīvs tiek piegādāts tieši funkcijai FREQUENCY kā datu_masīvs arguments. Tālāk, ROW funkcija tiek izmantots, lai izveidotu a secīgs ciparu saraksts par katru datu vērtību:

 
{FALSEFALSEFALSE1167} // after filtering

Tādējādi tiek izveidots šāds masīvs:

 
 ROW (B3:B12)- ROW (B3)+1

kas kļūst par bins_array arguments FILTER. Šajā brīdī mums ir:

 
{12345678910}

FREQUENCY atgriež skaitļu masīvu, kas norāda skaitu katrai datu masīva vērtībai, sakārtots pēc bin. Kad skaitlis jau ir saskaitīts, FREQUENCY atgriezīs nulli. FREQUENCY rezultāts ir šāds:

 
 FREQUENCY ({FALSEFALSEFALSE1167},{1234567})

Piezīme: FREQUENCY vienmēr atgriež masīvu ar vēl vienu vienību nekā bins_array .

kā atrast vidējos rādītājus programmā Excel

Šajā brīdī mēs varam pārrakstīt formulu šādi:

 
{20000110} // result from FREQUENCY

Mēs pārbaudām vērtības, kas ir lielākas par nulli, kas pārvērš skaitļus par PATIESI vai FALSE:

 
= SUM (--({20000110}>0))

Tad mēs izmantojam a dubultnegatīvs lai piespiestu loģiskās vērtības uz 1s un 0s:

 
= SUM (--({TRUEFALSEFALSEFALSEFALSETRUETRUEFALSE}))

Visbeidzot, SUM funkcija atgriež 3 kā gala rezultātu.

Piezīme: šī ir masīva formula, un tā jāievada, izmantojot Control + Shift + Enter.

Darbojas ar tukšām šūnām diapazonā

Ja visas diapazona šūnas ir tukšas, jums ir jāpielāgo formula, lai novērstu tukšu šūnu iekļaušanu MATCH funkcijā, kas radīs kļūdu. To var izdarīt, pievienojot citu ligzdotu IF funkciju, lai pārbaudītu, vai nav tukšu šūnu:

 
= SUM ({10000110})

Ar diviem kritērijiem

Ja jums ir divi kritēriji, varat paplašināt formulas loģiku, pievienojot citu ligzdotu IF:

 
{= SUM (--( FREQUENCY ( IF (B5:B11'', IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0))), ROW (B5:B11)- ROW (B5)+1)>0))}

Kur c1 = kritērijs1, c2 = kritērijs2 un valsis = vērtību diapazons.

Ar Būla loģiku

Ar Būla loģika , jūs varat samazināt ligzdotie IF :

 
{= SUM (--( FREQUENCY ( IF (c1, IF (c2, MATCH (vals,vals,0))), ROW (vals)- ROW (vals.1st)+1)>0))}

Tādējādi ir vieglāk pievienot un pārvaldīt papildu kritērijus.