Екцел

Ћелија садржи једну од многих ствари

Cell Contains One Many Things

Екцел формула: Ћелија садржи једну од многих ствариОпшта формула | _+_ | Резиме

Да бисте тестирали да ли ћелија садржи један од многих низова, можете користити формулу засновану на ПРЕТРАГА , ИСНУМБЕР и СУМПРОДУЦТ функције. Формула у Ц5, копирана, је:



= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,A1)))>0

где ствари је именовани опсег Е5: Е7.

Објашњење

Циљ овог примера је да тестира сваку ћелију у Б5: Б14 да види да ли садржи било који низ у именовани опсег ствари (Е5: Е7). Ови низови се могу појавити било куда у ћелији, па је ово дословни проблем „садржи“. Формула у Ц5, копирана, је:





 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Ова формула се заснива на друга формула који проверава ћелију за један подниз. Ако ћелија садржи подниз, формула враћа ТРУЕ. Ако није, формула враћа ФАЛСЕ:

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

Када СЕАРЦХ функција пронађе низ, враћа позицију тог низа као број. Ако СЕАРЦХ не пронађите стринг, он враћа #ВАЛУЕ! грешка. То значи да ће ИСНУМБЕР вратити ТРУЕ ако постоји подударање, а ФАЛСЕ ако није.



У овом примеру циљ је проверити више од једног низа, па функцији СЕАРЦХ дајемо а списак жица у именовани опсег ствари . Пошто постоје 3 низа у ствари ('црвено', 'зелено' и 'плаво'), СЕАРЦХ враћа 3 резултата у арраи овако:

бројач већи од и мањи од
 
 ISNUMBER ( SEARCH (substring,B5)) // test for substring

Пошто 'црвено' и 'плаво' нису пронађени, СЕАРЦХ враћа #ВАЛУЕ! грешка. Међутим, пошто се 'зелена' појављује при крају текста у ћелији Б5, СЕАРЦХ враћа 23 (тј. 'Зелена' почиње на 23. знаку).

Овај низ се враћа директно у ИСНУМБЕР функција , који претвара ставке у низу у ТРУЕ или ФАЛСЕ:

 
{#VALUE!#VALUE!23}

Логично, ако имамо само једно ТРУЕ у низу, знамо да ћелија садржи бар један од низова које тражимо. Најлакши начин да проверите да ли је ТРУЕ је да додате све вредности заједно. То можемо учинити са СУМПРОДУЦТ функција , али прво морамо да присилимо ТРУЕ / ФАЛСЕ вредности на 1с и 0с са а двоструко негативно (--) овако:

 
 ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

Ово даје нови низ који садржи само 1с и 0с:

како форматирати табелу у екцелу
 
--{FALSEFALSETRUE} // coerce to 1s and 0s

који се испоручује директно СУМПРОДУЦТ -у:

 
{001}

Са само једним низом за обраду, СУМПРОДУЦТ сумира ставке у низу и враћа резултат. Сваки резултат различит од нуле значи да имамо „погодак“, па додајемо> 0 да бисмо приморали коначни резултат на ТРУЕ или ФАЛСЕ:

 
= SUMPRODUCT ({001}) // returns 1

Имајте на уму да ће свака комбинација подударања вратити број већи од нуле и узроковати да формула врати ТРУЕ.

Са тешко кодираном листом

Није потребно користити опсег за листу стрингова које треба тражити. Можете користити и константа низа . На пример, да бисте проверили „црвено“, „плаво“ или „зелено“, можете да користите формулу попут ове:

 
= SUMPRODUCT ({001})>0 // returns TRUE

СУМ функција

Историјски гледано, СУМПРОДУЦТ често се појављује у формуле низа , јер може изворно да обрађује низове, без контроле + схифт + ентер . Ово чини формулу „пријатнијом“ за већину корисника. Ин Екцел 365 , који рукује низовима завичајно , СУМ функција може се користити уместо СУМПРОДУЦТ без контроле + схифт + ентер:

добити дан у недељи од датума екцел
 
= SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

Спречавање лажних подударања

Један проблем са овим приступом је што можете добити лажна подударања из поднизова који се појављују унутар дужих речи. На пример, ако покушате да упоредите 'др', можда ћете пронаћи и 'Андреа', 'пиће', 'суво', итд. Пошто се 'др' појављује унутар ових речи. То се дешава зато што СЕАРЦХ аутоматски врши подударање „садржи“.

За брзо хаковање, можете додати размак око речи за претрагу (нпр. 'Др' или 'др') како бисте избегли хватање 'др' у другој речи. Али ово неће успети ако се „др“ појави прво или последње у ћелији, или се појави са интерпункцијом.

Ако вам треба прецизније решење, једна од опција је да то учините нормализује текст прво у а помоћна колона , водећи рачуна да се дода и водећи и задњи простор. Затим користите формулу на овој страници за настали текст.

Прилози Датотека ћелија садржи једну од многих ствари.клск Аутор Даве Брунс


^