Генеричка формула
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Резиме
Да бисте категоризовали текст помоћу кључних речи са подударањем „садржи“, можете да користите функцију СЕАРЦХ, уз помоћ ИНДЕКС-а и МАТЦХ-а. У приказаном примеру, формула у Ц5 је:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
где је кључна реч именовани опсег Е5: Е14, а категорија именовани опсег Ф5: Ф14.
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер.
Објашњење
У основи, ово је функција ИНДЕКС и МАТЦХ.
Унутар функције МАТЦХ користимо функцију СЕАРЦХ за претрагу ћелија у колони Б за сваку наведену кључну реч у наведеним кључним речима опсега (Е5: Е14):
SEARCH(keywords,B5)
Будући да тражимо више ставки (у наведеним кључним речима опсега ), вратићемо више резултата попут овог:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Вредност! грешка се јавља када СЕАРЦХ не може да пронађе текст. Када СЕАРЦХ пронађе подударање, враћа број који одговара положају текста унутар ћелије.
Да бисмо ове резултате променили у употребљивији формат, користимо функцију ИСНУМБЕР која претвара све вредности у ТРУЕ / ФАЛСЕ на следећи начин:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Овај низ улази у функцију МАТЦХ као лоокуп_арраи, са лоокуп_валуе постављеним као ТРУЕ. МАТЦХ враћа позицију прве ТРУЕ коју пронађе у низу (7 у овом случају) који је функцији ИНДЕКС дат као ров_нум:
=INDEX(categories,7)
ИНДЕКС као коначни резултат враћа 7. ставку у категоријама „Аутоматски“.
Уз КСЛООКУП
Помоћу функције КСЛООКУП ова формула се може поједноставити. КСЛООКУП може да користи исту логику која се користи унутар горње функције МАТЦХ, па је еквивалентна формула:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
КСЛООКУП проналази прву ИСТИНУ у низу и враћа одговарајућу вредност из категорија .
Спречавање лажних подударања
Један од проблема овог приступа је што можете добити лажна подударања из поднизова који се појављују у дужим речима. На пример, ако покушате да подударите са „др“, можда ћете пронаћи и „Андреа“, „пиће“, „суво“ итд., Јер се у тим речима појављује „др“. То се догађа зато што СЕАРЦХ аутоматски подудара „садржи“.
За брзо хаковање можете додати простор око речи за претрагу (нпр. „Др“ или „др“) како бисте избегли да „др“ ухватите у другој речи. Али ово неће успети ако се „др“ појави прво или последње у ћелији или се појави са интерпункцијом итд.
Ако вам је потребно тачније решење, једна од могућности је да се текст прво нормализује у помоћну колону, водећи рачуна да се дода и водећи и пратећи простор. Тада можете да тражите читаве речи окружене размацима.
