Обща формула
SUMPRODUCT(--(A:A=A1))
Обобщение
Предговор
Това е досадно дълго въведение, но контекстът е важен, извинете!
Ако се опитате да преброите много дълги числа (16+ цифри) в диапазон с COUNTIF, може да видите неправилни резултати, поради грешка в това как определени функции обработват дълги числа, дори когато тези числа се съхраняват като текст. Помислете за екрана по-долу. Всички преброявания в колона D са неправилни - въпреки че всяко число в колона B е уникално, броят, върнат от COUNTIF, предполага, че тези числа са дублирани.
=COUNTIF(data,B5)
Този проблем е свързан с начина, по който Excel обработва числа. Excel може да обработва само 15 значими цифри и ако въведете число с повече от 15 цифри в Excel, ще видите крайните цифри, преобразувани безшумно в нула. Проблемът с броенето, споменат по-горе, произтича от тази граница.
Обикновено можете да избегнете това ограничение, като въведете дълги числа като текст, или като стартирате номера с единична кавичка ('999999999999999999), или като форматирате клетките като Текст преди въвеждане. Докато не е необходимо да извършвате математически операции с номер, това е добро решение и ви позволява да въвеждате изключително дълги числа за неща като номера на кредитни карти и серийни номера, без да губите никакви числа.
Ако обаче се опитате да използвате COUNTIF, за да преброите число с повече от 15 цифри (дори когато се съхранява като текст), може да видите ненадеждни резултати. Това се случва, защото COUNTIF вътрешно преобразува дългата стойност обратно в число в даден момент по време на обработката, задействайки 15-цифреното ограничение, описано по-горе. Без всички налични цифри, някои числа могат да се броят като дубликати, когато се броят с COUNTIF.
Решение
Едно от решенията е да замените формулата COUNTIF с формула, която използва SUM или SUMPRODUCT. В показания пример формулата в E5 изглежда така:
=SUMPRODUCT(--(data=B5))
Формулата използва посочения диапазон "данни" (B5: B9) и генерира правилния брой за всяко число с SUMPRODUCT.
Обяснение
Първо, изразът в SUMPRODUCT сравнява всички стойности в посочения диапазон "данни" със стойността от колона В в текущия ред. Това води до масив от TRUE / FALSE резултати.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
След това двойното отрицателно принуждава стойностите TRUE / FALSE до стойности 1/0.
=SUMPRODUCT((1;0;0;0;0))
И накрая, SUMPRODUCT просто сумира елементите в масива и връща резултата.
Вариант на формула на масив
Можете също да използвате функцията SUM вместо SUMPRODUCT, но това е формула на масив и трябва да се въведе с control + shift + enter:
(=SUM(--(B:B=B5)))
Други функции с този проблем
Не съм проверил това сам, но изглежда, че няколко функции имат един и същ проблем, включително SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF и AVERAGEIFS.
