MS excel – присвоение «категорий» на основе ключевых слов

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

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

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

2 Solutions collect form web for “MS excel – присвоение «категорий» на основе ключевых слов”

Вы хотите функцию 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 Key-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) 

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


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

Я не вижу причин для его использования.

Как сказал Тайсон, «закрыть / приблизить» Матч не предназначен для слов. Чтобы процитировать файл справки:

 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-Food and Drink», «Взяли босса Ужин на день рождения ".

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

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

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

  • Сравните ячейки в двух разных таблицах и извлеките данные из одного места в другое, если совпадение найдено
  • Можете ли вы вернуть панель «show full cell content» (элемент пользовательского интерфейса) в новой ленте Excel 2007+?
  • Excel Преобразование строк в столбцы
  • Сводная таблица - суммирование нескольких независимых столбцов данных
  • Формула Excel отображает формулу, а не результат
  • Учет уникальных значений в столбце, а также фильтрация по другим столбцам
  • Как присоединиться / объединить 2 таблицы рабочих таблиц с помощью 3-й таблицы в Excel?
  • Гистограмма Excel
  • Документ Excel не может открыть
  • Могу ли я «subtotal» с произвольной функцией, например, конкатенацией строки?
  • Когда использовать Ctrl + Shift + Enter и когда использовать Enter в Excel?
  • Excel - Формулы, которые отображаются на нескольких листах?
  • Interesting Posts

    Сколько параллельных подключений RDP на Windows 7 Pro, Enterprise и Ultimate?

    Подключение к сети Wi-Fi через терминал Mac

    Как я могу проверить, правильно ли работает nginx обратный прокси + apache?

    Microsoft Word 2010 – Условное форматирование

    Каковы требования к полноэкранным консолям в Windows 7?

    Как я могу удалить этот большой пробел над моими сносками в MS Word?

    Как сообщить MS Excel использовать нестандартный десятичный разделитель в графиках и диаграммах?

    Как заблокировать запуск программ (Windows 7)

    Восстановить grub mbr и / boot с помощью компакт-диска ubuntu 9.04 live

    Общие сведения о переадресации портов и NAT

    Ping server / domain и захватить его IP-адрес в Linux

    Порт Ethernet на ThinkPad не подключается, но тот, что на ультрабазе (док-станция)

    В торренте, что такое семя, корм и равный?

    Найти все шрифты, используемые в файле Photoshop

    Как отключить содержимое HTML5 в популярных браузерах, таких как Firefox и Chrome?

    Давайте будем гением компьютера.