Обща формула
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Обобщение
За да категоризирате текста с помощта на ключови думи със съвпадение „съдържа“, можете да използвате функцията SEARCH с помощта на INDEX и MATCH. В показания пример формулата в C5 е:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
където ключовите думи е наименованият диапазон E5: E14, а категориите е наименуваният диапазон F5: F14.
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.
Обяснение
В основата си това е функция INDEX и MATCH.
Във функцията MATCH използваме функцията SEARCH, за да търсим клетки в колона B за всяка изброена ключова дума в ключовите думи с посочен диапазон (E5: E14):
SEARCH(keywords,B5)
Тъй като търсим няколко елемента (в ключовите думи с наименованието ), ще получим обратно няколко резултата като този:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Стойността! възниква грешка, когато SEARCH не може да намери текста. Когато SEARCH намери съвпадение, той връща число, което съответства на позицията на текста в клетката.
За да променим тези резултати в по-използваем формат, използваме функцията ISNUMBER, която преобразува всички стойности в TRUE / FALSE по следния начин:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Този масив влиза във функцията MATCH като lookup_array, като lookup_value е зададен като TRUE. MATCH връща позицията на първата ИСТИНА, която намира в масива (7 в този случай), която се предоставя на функцията INDEX като номер_ред:
=INDEX(categories,7)
INDEX връща 7-ия елемент в категориите „Автоматично“ като краен резултат.
С XLOOKUP
С функцията XLOOKUP тази формула може да бъде опростена донякъде. XLOOKUP може да използва същата логика, използвана вътре в функцията MATCH по-горе, така че еквивалентната формула е:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP локализира първата TRUE в масива и връща съответната стойност от категориите .
Предотвратяване на фалшиви съвпадения
Един проблем при този подход е, че може да получите фалшиви съвпадения от поднизове, които се появяват в по-дълги думи. Например, ако се опитате да съчетаете „dr“, можете също да намерите „Andrea“, „drink“, „dry“ и т.н., тъй като „dr“ се появява вътре в тези думи. Това се случва, защото SEARCH автоматично прави съвпадение „съдържа“.
За бърз хак можете да добавите място около думите за търсене (т.е. „dr“ или „dr“), за да избегнете улавянето на „dr“ с друга дума. Но това ще се провали, ако „dr“ се появи първо или последно в клетка или се появи с пунктуация и т.н.
Ако се нуждаете от по-точно решение, една от възможностите е да нормализирате текста първо в помощна колона, като внимавате да добавите и водещо и последващо пространство. След това можете да търсите цели думи, заобиколени от интервали.
