Генеричка формула
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Резиме
Да бисте бројали ћелије које нису једнаке ни са једном од многих ствари, можете да користите формулу засновану на функцијама МАТЦХ, ИСНА и СУМПРОДУЦТ. У приказаном примеру, формула у ћелији Ф5 је:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
где је „податак“ именовани опсег Б5: Б16, а „изузетак“ именовани опсег Д5: Д7.
Објашњење
Прво, мало контекста. Обично, ако имате само неколико ствари које не желите да избројите, можете да користите ЦОУНТИФС на овај начин:
=COUNTIFS(range,"apple",range,"orange")
Али ово се не прилагођава најбоље ако имате листу многих ствари, јер ћете морати да додате додатни опсег / критеријум за сваку ствар коју не желите да рачунате. Било би много лакше направити листу и предати референцу на ову листу као део критеријума. Управо то чини формула на овој страници.
У основи, ова формула користи функцију МАТЦХ за проналажење ћелија које нису једнаке „а“, „б“ или „ц“ са овим изразом:
MATCH(data,exclude,0)
Имајте на уму да су вредност претраживања и низ прегледа „обрнути“ у односу на нормалну конфигурацију - ми пружамо све вредности из именованог опсега „подаци“ као вредности претраживања и дајемо све вредности које желимо да искључимо у именованом опсегу „изузми“. Пошто МАТЦХ-у дајемо више од једне вредности претраживања, у низу попут овог добијамо више резултата:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
У суштини, МАТЦХ нам даје положај одговарајућих вредности као број, а за све остале вредности враћа # Н / А.
# Н / А резултати су они који нас занимају јер представљају вредности које нису једнаке „а“, „б“ или „ц“. Сходно томе, користимо ИСНА да присилимо ове вредности на ТРУЕ, а бројеве на ФАЛСЕ:
ISNA(MATCH(data,exclude,0)
Тада користимо двоструки негатив да присилимо ТРУЕ на 1 и ФАЛСЕ на нулу. Добијени низ унутар СУМПРОДУЦТ изгледа овако:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Са само једним низом за обраду, СУМПРОДУЦТ сумира и враћа коначни резултат, 4.
Напомена: Коришћење СУМПРОДУЦТ уместо СУМ избегава потребу за употребом цонтрол + схифт + ентер.
Бројање минус подударање
Други начин за бројање ћелија које нису једнаке било којој од неколико ствари је бројање свих вредности и одузимање подударања. То можете урадити са формулом попут ове:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
Овде ЦОУНТА враћа број свих празних ћелија. Функција ЦОУНТИФ, с обзиром на именовани опсег "изузети", вратиће три бројача, по један за сваку ставку на листи. СУМПРОДУЦТ збраја зброј и овај број се одузима од броја свих непразних ћелија. Коначни резултат је број ћелија које нису једнаке вредности у „изузми“.
Литерал садржи логику типа
Формула на овој страници рачуна се логиком „једнако је“. Ако требате да избројите ћелије које не садрже много низова, при чему садржи значи да се низ може појавити било где у ћелији, требат ће вам сложенија формула.
