Vispārēja formula
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Kopsavilkums
Lai uzskaitītu unikālas teksta vērtības diapazonā ar kritērijiem, varat izmantot masīva formulu, kuras pamatā ir funkcijas FREQUENCY un MATCH. Parādītajā piemērā formula G6 ir:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+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 tā jāievada ar vadības taustiņu + shift + enter.
Paskaidrojums
Šī ir sarežģīta formula, kas izmanto FREQUENCY, lai skaitītu skaitliskās vērtības, kas iegūtas ar funkciju MATCH. Strādājot no iekšpuses uz āru, funkcija MATCH tiek izmantota, lai iegūtu katras vērtības pozīciju, kas parādās datos:
MATCH(B5:B11,B5:B11,0)
MATCH rezultāts ir šāds masīvs:
(1;1;3;1;1;6;7)
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 4 reizes, viņš šajā masīvā 4 reizes parādās kā skaitlis 1.
Ārpus funkcijas MATCH kritēriju piemērošanai tiek izmantota funkcija IF, kas šajā gadījumā ietver testēšanu, ja projekts ir "omega" (no šūnas G5):
IF(C5:C11=G5 // filter on "omega"
IF funkcija darbojas kā filtrs, ļaujot iziet caur MATCH vērtībām tikai tad, ja tās ir saistītas ar "omega". Rezultāts ir šāds masīvs:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Filtrētais masīvs tiek piegādāts tieši funkcijai FREQUENCY kā argumentu data_array . Pēc tam funkciju ROW izmanto, lai izveidotu secīgu skaitļu sarakstu katrai datu vērtībai:
ROW(B3:B12)-ROW(B3)+1
Tādējādi tiek izveidots šāds masīvs:
(1;2;3;4;5;6;7;8;9;10)
kas kļūst par argumentu bins_array FILTER. Šajā brīdī mums ir:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY atgriež skaitļu masīvu, kas norāda katras datu masīvā esošās vērtības skaitu, sakārtotu pēc bin. Kad skaitlis jau ir saskaitīts, FREQUENCY atgriezīs nulli. FREQUENCY rezultāts ir šāds masīvs:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Piezīme: FREQUENCY vienmēr atgriež masīvu ar vēl vienu vienumu nekā bins_array .
Šajā brīdī mēs varam pārrakstīt formulu šādi:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Mēs pārbaudām, vai vērtības ir lielākas par nulli, kas pārvērš skaitļus par TRUE vai FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Tad mēs izmantojam dubultnegatīvu, lai piespiestu loģiskās vērtības 1s un 0s:
=SUM((1;0;0;0;0;1;1;0))
Visbeidzot, funkcija SUM atgriež 3 kā gala rezultātu.
Piezīme: šī ir masīva formula, un tā jāievada, izmantojot Control + Shift + Enter.
Darbība ar tukšām šūnām diapazonā
Ja kāda diapazona šūna ir tukša, jums būs jāpielāgo formula, lai tukšās šūnas netiktu ievadītas funkcijā MATCH, kas radīs kļūdu. To var izdarīt, pievienojot citu ligzdotu IF funkciju, lai pārbaudītu, vai nav tukšu šūnu:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Ar diviem kritērijiem
Ja jums ir divi kritēriji, varat paplašināt formulas loģiku, pievienojot citu ligzdotu IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Kur c1 = kritēriji1, c2 = kritēriji2 un vals = vērtību diapazons.
Ar loģisko loģiku
Izmantojot loģisko loģiku, jūs varat samazināt ligzdotos IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Tas atvieglo papildu kritēriju pievienošanu un pārvaldību.
