Генеричка формула
SUMPRODUCT(--(A:A=A1))
Резиме
Предговор
Ово је досадно дугачак увод, али контекст је важан, извините!
Ако покушате да избројите веома дуге бројеве (више од 16 цифара) у опсегу са ЦОУНТИФ, можда ћете видети нетачне резултате због грешке у начину на који одређене функције рукују дугим бројевима, чак и када су ти бројеви сачувани као текст. Размотрите доњи екран. Сва бројања у колони Д су нетачна - иако је сваки број у колони Б јединствен, бројање које је вратио ЦОУНТИФ сугерише да су ови бројеви дупликати.
=COUNTIF(data,B5)
Овај проблем је повезан са начином на који Екцел рукује бројевима. Екцел може да обрађује само 15 значајних цифара, а ако у Екцел унесете број са више од 15 цифара, видећете да се пратеће цифре тихо претварају у нулу. Горе наведени проблем са бројањем произилази из овог ограничења.
Обично то ограничење можете избећи уношењем дугих бројева као текста, започињањем броја једним наводником ('999999999999999999) или форматирањем ћелија као текст пре уноса. Све док вам није потребно да изводите математичке операције на броју, ово је добро решење и омогућава вам да унесете изузетно дугачке бројеве за ствари попут бројева кредитних картица и серијских бројева, а да притом не изгубите ниједан број.
Међутим, ако покушате да користите ЦОУНТИФ за бројање броја са више од 15 цифара (чак и када је сачуван као текст), можда ћете видети непоуздане резултате. То се догађа зато што ЦОУНТИФ у одређеном тренутку током обраде дугу вредност враћа у број, покрећући горе описано ограничење од 15 цифара. Без присутних свих цифара, неки бројеви се могу бројати као дупликати када се броје са ЦОУНТИФ.
Решење
Једно решење је замена формуле ЦОУНТИФ формулом која користи СУМ или СУМПРОДУЦТ. У приказаном примеру формула у Е5 изгледа овако:
=SUMPRODUCT(--(data=B5))
Формула користи именовани опсег „подаци“ (Б5: Б9) и генерише тачан број за сваки број помоћу СУМПРОДУЦТ.
Објашњење
Прво, израз унутар СУМПРОДУЦТ упоређује све вредности у именованом опсегу „подаци“ са вредношћу из колоне Б у тренутном реду. Резултат је низ ИСТИНА / НЕТОЧНИ резултата.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Даље, двоструки негатив присиљава вредности ТРУЕ / ФАЛСЕ на вредности 1/0.
=SUMPRODUCT((1;0;0;0;0))
Коначно, СУМПРОДУЦТ једноставно сумира ставке у низу и враћа резултат.
Варијанта формуле низа
Такође можете користити функцију СУМ уместо СУМПРОДУЦТ, али ово је формула низа и мора се унети са цонтрол + схифт + ентер:
(=SUM(--(B:B=B5)))
Остале функције са овим проблемом
Нисам то сам верификовао, али изгледа да неколико функција има исти проблем, укључујући СУМИФ, СУМИФС, ЦОУНТИФ, ЦОУНТИФС, АВЕРАГЕИФ и АВЕРАГЕИФС.
