Vispārēja formula
SUMPRODUCT(--(A:A=A1))
Kopsavilkums
Priekšvārds
Šis ir kaitinoši garš ievads, taču konteksts ir svarīgs, piedodiet!
Ja mēģināt skaitīt ļoti garus skaitļus (16 un vairāk ciparus) diapazonā ar COUNTIF, iespējams, ka tiks parādīti nepareizi rezultāti, jo kļūda ir saistīta ar to, kā noteiktas funkcijas apstrādā garus skaitļus, pat ja šie skaitļi tiek glabāti kā teksts. Apsveriet zemāk redzamo ekrānu. Visi skaitļi D slejā ir nepareizi - kaut arī katrs B slejas skaitlis ir unikāls, COUNTIF atgrieztais skaitlis liek domāt, ka šie skaitļi ir dublikāti.
=COUNTIF(data,B5)
Šī problēma ir saistīta ar to, kā Excel apstrādā ciparus. Excel var apstrādāt tikai 15 nozīmīgus ciparus, un, ja programmā Excel ievadīsit skaitli, kurā ir vairāk nekā 15 cipari, redzēsiet, ka pēdējie cipari tiek klusi pārveidoti par nulli. Iepriekš minētā skaitīšanas problēma rodas no šī ierobežojuma.
Parasti jūs varat izvairīties no šī ierobežojuma, ievadot garus skaitļus kā tekstu, vai nu sākot skaitli ar vienu pēdiņu ('99999999999999999999), vai pirms ievadīšanas formatējot šūnu (-es) kā Teksts. Kamēr jums nav jāveic matemātiskas darbības ar numuru, tas ir labs risinājums, un tas ļauj ievadīt īpaši garus numurus tādām lietām kā kredītkaršu numuri un sērijas numuri, nezaudējot nevienu skaitli.
Tomēr, ja mēģināt izmantot COUNTIF, lai skaitītu skaitli ar vairāk nekā 15 cipariem (pat ja tie tiek glabāti kā teksts), iespējams, redzēsit neuzticamus rezultātus. Tas notiek tāpēc, ka COUNTIF kādā procesa laikā iekšēji pārvērš garo vērtību par skaitli, izraisot iepriekš aprakstīto 15 ciparu ierobežojumu. Bez visiem cipariem dažus skaitļus var skaitīt kā dublikātus, skaitot ar COUNTIF.
Risinājums
Viens risinājums ir aizstāt formulu COUNTIF ar formulu, kurā tiek izmantota SUM vai SUMPRODUCT. Parādītajā piemērā formula E5 izskatās šādi:
=SUMPRODUCT(--(data=B5))
Formulā tiek izmantots nosauktais diapazons "dati" (B5: B9), un ar SUMPRODUCT tiek ģenerēts pareizais skaitlis katram skaitlim.
Paskaidrojums
Pirmkārt, izteiksme SUMPRODUCT iekšpusē salīdzina visas vērtības nosauktajā diapazonā "dati" ar vērtību no B slejas pašreizējā rindā. Tā rezultātā tiek iegūts TRUE / FALSE rezultātu masīvs.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Pēc tam dubultnegatīvs TRUE / FALSE vērtības piespiež 1/0 vērtībām.
=SUMPRODUCT((1;0;0;0;0))
Visbeidzot, SUMPRODUCT vienkārši summē masīva vienumus un atgriež rezultātu.
Masīva formulas variants
Varat arī izmantot funkciju SUM, nevis SUMPRODUCT, taču šī ir masīva formula, kas jāievada ar vadības taustiņu + shift + enter:
(=SUM(--(B:B=B5)))
Citas funkcijas ar šo problēmu
Es pats to neesmu pārbaudījis, bet šķiet, ka vairākām funkcijām ir viena problēma, tostarp SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF un AVERAGEIFS.
