☰

MS Excel - назначение «категорий» на основе ключевых слов

tag-icon • tag-icon microsoft-excel
MS Excel - назначение «категорий» на основе ключевых слов

У меня есть файл excel с расходами (сумма потраченных денег в одном столбце), а в следующем столбце у меня краткое описание, которое в основном состоит из нескольких слов. Я хочу «упростить» описание и назначить одно или два слова для каждого описания, которые будут в другом столбце рядом с ним. Проблема в том, что описание не «унифицировано», например, у меня могут быть строки типа «бизнес-ланч», «бизнес-ужин в ресторане XXX», «кофе с журналистами» и т. д., и я хотел бы назначить этим описаниям метку «еда». Существуют также различные категории, которые следуют похожему шаблону.

Моя идея состояла в том, чтобы создать еще одну таблицу (на другом листе) - в одном столбце у меня есть ключевые слова, такие как "кофе", "обед", "ужин", а в столбце рядом с ними я ставлю метки, которые хочу назначить, а именно "еда". Я использовал функцию vlookup с приблизительным совпадением, но она возвращает мне неверные результаты. По какой-то причине порядок слов в списке, похоже, влияет на результаты, и даже если есть частичное совпадение (точное в одном слове строки), vlookup игнорирует его и возвращает что-то другое. Например, у меня есть "парковка у отеля xxx", а в таблице у меня есть пара "парковка"-"дорожные расходы", vlookup возвращает метку "еда".

Можете ли вы помочь мне решить эту проблему? (есть ли другой подход, который вы могли бы предложить?)

решение1

Вам нужна функция FIND()and/or 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!; … } (семь элементов (в этом примере), по одному на ключевое слово; второй показывает результат для «lunch», который находится в 'Key-Sheet'!A2).
  • IFERROR(…,LEN($A1)+1)– замените #VALUE!значения на 15, которое, будучи LEN("business lunch")+1, не может быть допустимым возвращаемым значением из SEARCH()(и которое, на самом деле, больше любого возможного допустимого возвращаемого значения из SEARCH()), но которое является допустимым числом. Так что теперь наш массив { 15; ; 10; 15… }.
  • MIN(…)– извлечь минимальное значение из массива: в этом примере 10. В общем случае это будет (первый) успешный возврат из SEARCH().
  • =MATCH(…, …)– обратите внимание, что второй параметр to MATCH()такой же, как и первый маркер выше. Поэтому мы ищем 10в массиве { #VALUE!; 10; #VALUE!; … }. Это возвращает позицию 10, которая равна 2, что соответствует тому факту, что A1в листе данных («бизнес-ланч») содержится «ланч», который находится во 2-й строке Key-Sheet.

Чтобы получить категорию расходов, это просто вопрос индексации в столбце BKey-Sheet. Установите ячейку 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)

Этотделаетдолжна быть формула массива.

P.S. Функции 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

Я не вижу смысла вам его использовать.

решение2

Как сказал Тайсон, совпадение "близко/приблизительно" не подразумевается для слов. Процитирую файл справки:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Это означает, что если вы ищете значение «7» в «1,2,5,8,12», возвращенное значение будет «5», что является ближайшим значением к 7, которое не больше 7.

Нет простого способа сделать то, что вы хотите, без обширного программирования и оценки как отдельных слов, так и грамматического анализа.

Вам следует приучить себя вводить некий «код категории» при первоначальном вводе данных, а затем использовать столбец для заметок для «дополнительных сведений»... например, «01-Еда и напитки», «Пригласил босса на ужин в честь его дня рождения».

Если у вас уже есть большой объем данных, и это может быть затруднительно, вы можете прибегнуть к нескольким трюкам, чтобы ускорить процесс (хотя вам все равно придется вручную выполнять множество операций по сортировке).

Начните с добавления столбца, который проверяет описание на наличие слова "park" и возвращает 0, если не найдено, 1, если найдено... что-то вроде "=If(Search("park",A1)>1,1,0)" (а затем автоматически копирует формулу вниз по всем строкам ваших данных). Затем вы можете отсортировать всю таблицу по этому столбцу, так что ваши данные будут разделены на две группы: описания с "park" и без. Добавьте еще один столбец, например, для тех, где есть "food". Затем между "food" и "park" вы можете отсортировать (используя оба столбца) по четырем группам: без ни одного слова, с "food", с "park" и с обоими.

Повторяя это неоднократно, вы сможете быстро отсортировать группы, которые явно относятся к той или иной категории, пометить их кодом категории и игнорировать их в дальнейшем, выполняя дополнительные поиски слов, пока все не будет отнесено к категории.

Связанный контент