Šajā apmācībā jūs uzzināsit, kā saskaitīt unikālas vērtības programmā Excel, izmantojot formulas (funkcijas COUNTIF un SUMPRODUCT).
Kā saskaitīt unikālas vērtības programmā Excel
Pieņemsim, ka mums ir datu kopa, kā parādīts zemāk:
Šajā apmācībā es nosaukšu diapazonu A2: A10 kā NAMES. Turpmāk mēs izmantosim šo nosaukto diapazonu formulās.
Skatiet arī: Kā izveidot nosauktos diapazonus programmā Excel.
Šajā datu kopā ir atkārtojums diapazonā NAMES. Lai iegūtu unikālo vārdu skaitu no šīs datu kopas (A2: A10), mēs varam izmantot COUNTIF un SUMPRODUCT funkciju kombināciju, kā parādīts zemāk:
= SUMPRODUCT (1/COUNTIF (NAMES, NAMES))
Kā šī formula darbojas?
Lai labāk izprastu, sadalīsim šo formulu:
- COUNTIF (NAMES, NAMES)
- Šī formulas daļa atgriež masīvu. Iepriekš minētajā piemērā tas būtu {2; 2; 3; 1; 3; 1; 2; 3; 2}. Skaitļi šeit norāda, cik reizes vērtība parādās noteiktā šūnu diapazonā.
Piemēram, vārds ir Bobs, kas sarakstā parādās divreiz, tāpēc tas Bobam atdotu skaitli 2. Līdzīgi Stīvs notiek trīs reizes, un līdz ar to Stīvam tiek atdota 3.
- Šī formulas daļa atgriež masīvu. Iepriekš minētajā piemērā tas būtu {2; 2; 3; 1; 3; 1; 2; 3; 2}. Skaitļi šeit norāda, cik reizes vērtība parādās noteiktā šūnu diapazonā.
- 1/COUNTIF (NAMES, NAMES)
- Šī formulas daļa atgrieztu masīvu - {0.5; 0.5; 0.333333333333333; 1; 0.333333333333333; 1; 0.5; 0.333333333333333; 0.5}
Tā kā mēs esam sadalījuši 1 ar masīvu, tas atgriež šo masīvu.
Piemēram, pirmais atgrieztā masīva elements bija 2. Kad 1 tiek dalīts ar 2, tas atgriež .5.
- Šī formulas daļa atgrieztu masīvu - {0.5; 0.5; 0.333333333333333; 1; 0.333333333333333; 1; 0.5; 0.333333333333333; 0.5}
- SUMPRODUCT (1/COUNTIF (NAMES, NAMES))
- SUMPRODUCT vienkārši pievieno visus šos skaitļus. Ņemiet vērā: ja Bobs sarakstā parādās divreiz, iepriekš minētais masīvs atgriež .5 visur, kur sarakstā parādījās Boba vārds. Līdzīgi, tā kā Stīvs sarakstā parādās trīs reizes, masīvs atgriež .3333333 ikreiz, kad parādās Stīva vārds. Kad mēs pievienojam numurus katram vārdam, tas vienmēr atgriezīsies 1. Un, ja mēs pievienosim visus skaitļus, tas atgriezīs kopējo unikālo vārdu skaitu sarakstā.
Šī formula darbojas labi, līdz diapazonā nav nevienas tukšas šūnas. Bet, ja jums ir tukšas šūnas, tas atgriezīs #DIV/0! kļūda.
Kā rīkoties ar tukšām šūnām?
Vispirms sapratīsim, kāpēc tas atgriež kļūdu, ja diapazonā ir tukša šūna. Pieņemsim, ka mums ir datu kopa, kā parādīts zemāk (šūna A3 ir tukša):
Ja mēs izmantojam to pašu formulu, ko izmantojām iepriekš, formulas daļa COUNTIF atgriež masīvu {2; 0; 3; 1; 3; 1; 2; 3; 1}. Tā kā šūnā A3 nav teksta, tā skaits tiek atgriezts kā 0.
Un tā kā mēs dalām 1 ar visu šo masīvu, tas atgriež #DIV/0! kļūda.
Lai apstrādātu šo dalīšanas kļūdu tukšu šūnu gadījumā, izmantojiet šādu formulu:
= SUMPRODUCT ((1/COUNTIF (NAMES, NAMES & ””)))
Viena no izmaiņām šajā formulā ir funkcijas COUNTIF kritēriju daļa. Mēs esam izmantojuši NAMES & ””, nevis NAMES. To darot, formula atgriezīs tukšo šūnu skaitu (agrāk tā atgriezās 0, kur bija tukša šūna).
PIEZĪME. Šī formula tukšās šūnas uzskaitītu kā unikālu vērtību un atgrieztu to rezultātā.
Iepriekš minētajā piemērā rezultātam jābūt 5, bet tas atgriež 6, jo tukšā šūna tiek skaitīta kā viena no unikālajām vērtībām.
Šeit ir formula, kas rūpējas par tukšajām šūnām un neuzskaita to galarezultātā:
= SUMPRODUCT ((NAMES ””)/COUNTIF (NAMES, NAMES & ””))
Šajā formulā 1, nevis skaitītājs, mēs esam izmantojuši VĀRDUS ””. Tas atgriež masīvu TRUEs un FALSE. Tas atgriež FALSE ikreiz, kad ir tukša šūna. Tā kā aprēķinos TRUE ir 1 un FALSE ir 0, tukšās šūnas netiek skaitītas, jo skaitītājs ir 0 (FALSE).
Tagad, kad mums ir sagatavots formulas pamata skelets, mēs varam iet soli tālāk un saskaitīt dažādus datu veidus.
Kā saskaitīt programmā Excel unikālas vērtības, kas ir teksts
Mēs izmantosim to pašu iepriekš aprakstīto jēdzienu, lai izveidotu formulu, kurā tiks skaitītas tikai unikālas teksta vērtības.
Šeit ir formula, kas programmā Excel saskaitīs unikālas teksta vērtības:
= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””)))
Viss, ko esam paveikuši, kā skaitītāju izmantojām formulu ISTEXT (NAMES). Ja šūna satur tekstu, tā atgriež TRUE, un, ja tā nav, FALSE. Tas neskaitīs tukšās šūnas, bet skaitīs šūnas, kurām ir tukša virkne (“”).
Kā saskaitīt programmā Excel unikālas vērtības, kas ir skaitliskas
Šeit ir formula, kas programmā Excel saskaitīs unikālas skaitliskās vērtības
= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAMES, NAMES & ””))
Šeit kā skaitītāju mēs izmantojam ISNUMBER (NAMES). Ja šūna satur skaitlisku datu tipu, tā atgriež TRUE, un, ja tā nav, FALSE. Tas neskaita tukšās šūnas.
