Вам нужна функция FIND() и / или SEARCH() .
Использование:
FIND(find_text, within_text)
возвращает начальную позицию первой текстовой строки
во второй текстовой строке (начиная с позиции 1)
Таким образом, FIND("lunch", "lunch with customer") возвращает 1, а FIND("lunch", "business lunch") возвращает 10.
Если первая строка не найдена во второй, это возвращает # #VALUE! значение ошибки.
SEARCH() похож на FIND() за исключением того, что FIND() чувствителен к регистру, а SEARCH() - нет. Так
FIND("lunch", "Lunch with customer") возвращает # #VALUE!
но
SEARCH("lunch", "Lunch with customer") возвращает 1
Я предполагаю, что вы захотите использовать SEARCH() , без учета регистра.
Вы захотите настроить массив следующим образом:
Вероятно, лучше сделать это на отдельном листе; давайте назовем это Key-Sheet .
Затем на листе данных: если описание в свободной форме находится в столбце A (начиная с ячейки A1), введите в ячейку B1:
=MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1))
и нажмите Ctrl+Shift+Enter, чтобы сделать его «формулой массива».
(Он будет отображаться в строке формул в фигурных скобках.)
Объяснение:
SEARCH('Key-Sheet'!$A$1:$A$7,$A1) - для каждого ключевого слова из столбца A таблицы ключей ("кофе", "обед", "ужин" и т.д.) Найдите его в описании в текущей строке, столбец A таблицы данных (например, «бизнес-ланч»).
Это создаст массив, содержащий { #VALUE!; 10 ; #VALUE!; …} (Семь элементов (в этом примере), по одному на ключевое слово; второй показывает результат для "ланча", который находится в 'Key-Sheet'!A2).IFERROR(…,LEN($A1)+1) - замените # #VALUE! значения с 15 , которые, будучи LEN("business lunch")+1 , не могут быть допустимым возвращаемым значением из SEARCH() (и которое, фактически, выше любого возможного действительного возвращаемого значения из SEARCH()), но который является действительным числом.
Итак, теперь наш массив { 15 ; 10 ; 15 ; …}MIN(…) - извлечь минимальное значение из массива: в данном примере 10 .
В общем, это будет (первый) успешный возврат из SEARCH() .=MATCH(…, …) - обратите внимание, что второй параметр MATCH() такой же, как и первый пункт выше.
Итак, мы ищем 10 в массиве { #VALUE!; 10 ; #VALUE!; …}
Это возвращает позицию 10 , то есть 2, что соответствует тому факту, что A1 на листе данных («бизнес-ланч») содержит "обед", который находится во 2-й строке таблицы ключей.
Чтобы получить категорию расходов, достаточно просто внести указатель в столбец B таблицы.
Установите для ячейки C1 значение =OFFSET('Key-Sheet'!$B$1,B1-1,0) .
(Это не обязательно должна быть формула массива.)
Обратите внимание (как указано выше), что, если описание расхода содержит несколько ключевых слов, будет найдено только первое.
Если вы не хотите беспокоиться о промежуточном значении, вы можете просто вычислить
=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)
Это должно быть формулой массива.
PS функции FIND() и SEARCH() имеют необязательный третий аргумент:
SEARCH(find_text, within_text, [start_num])
Так
SEARCH("cigar", "Sometimes a cigar is just a cigar.") возвращает 13
но
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17) возвращается 29
Я не вижу смысла для вас использовать его.
