Генеричка формула
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Резиме
Да бисте бројали ћелије које не садрже много различитих низова, можете користити прилично сложену формулу засновану на функцији ММУЛТ. У приказаном примеру, формула у Ф5 је:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
где је „податак“ именовани опсег Б5: Б14, а „изузетак“ именовани опсег Д5: Д7.
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер
Предговор
Ова формула је компликована захтевом „садржи“. Ако вам је само потребна формула за бројање ћелија које нису * једнаке * многим стварима, можете да користите једноставнију формулу засновану на функцији МАТЦХ. Такође, ако имате ограничен број низова које желите изузети, можете користити функцију ЦОУНТИФС на следећи начин:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Међутим, овим приступом морате унијети нови пар аргумената опсега / критеријума за сваки низ који желите изузети. Супротно томе, формула објашњена у наставку може да обрађује велики број низова како би се изузели унесени директно на радни лист.
Коначно, ова формула је сложена. Јавите ми ако имате једноставнију формулу за предлагање :)
Објашњење
Језгро ове формуле је БРОЈ и ПРЕТРАГА:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Овде транспонујемо ставке у именованом опсегу „изузми“, а затим преносимо резултат у ТРАЖЕЊЕ као „пронађи текст“, а „подаци“ као „у тексту“. Функција СЕАРЦХ враћа 2д низ вредности ТРУЕ и ФАЛСЕ, 10 редова по 3 колоне, овако:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
За сваку вредност у „подацима“ имамо 3 резултата (по један по низу за претрагу) који су или грешке #ВРЕДНОСТ или бројеви. Бројеви представљају положај пронађеног текстуалног низа, а грешке представљају текстуалне низове који нису пронађени. Иначе, функција ТРАНСПОСЕ је потребна за генерисање низа 10 к 3 комплетних резултата.
Овај низ се уноси у ИСНУМБЕР да би добио ТРУЕ ФАЛСЕ вредности, које претварамо у 1с и 0с помоћу двоструког негативног (-) оператора. Резултат је низ попут овог:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
који улази у функцију ММУЛТ као низ1. Поштујући правила множења матрице, број колона у низу1 мора бити једнак броју редова у низу2. За генерисање низа2 користимо функцију РОВ овако:
ROW(exclude)^0
Ово даје низ од 1с, 3 реда по 1 колону:
(1;1;1)
који улази у ММУЛТ као низ2 . Након множења низа, имамо низ димензионисан тако да одговара оригиналним подацима:
(2;1;0;0;1;1;0;0;0;2)
У овом низу било који број који није нула представља вредност у којој је пронађен бар један од изузетих низова. Нула означавају да нису пронађени изузети низови. Да бисмо присилили све вредности које нису нула на 1, користимо веће од нуле:
(2;1;0;0;1;1;0;0;0;2)>0
који креира још један низ или вредности ТРУЕ и ФАЛСЕ:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Наш коначни циљ је да избројимо само текстуалне вредности код којих нису пронађене изузете струне, па морамо ове вредности преокренути. То радимо одузимањем низа од 1. Ово је пример логичке логике. Математичка операција аутоматски присиљава вредности ТРУЕ и ФАЛСЕ на 1с и 0с и коначно имамо низ за повратак у функцију СУМ:
=SUM((0;0;1;1;0;0;1;1;1;0))
Функција СУМ даје коначни резултат 5.
