Загальна формула
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 порівнює всі значення в названому діапазоні "дані" зі значенням зі стовпця B поточного рядка. Це призводить до масиву результатів 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.
