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

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

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

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

  • Как применить функцию ко всем значениям в выбранных ячейках?
  • Количество строк с условиями, встречающимися в двух столбцах
  • Как я могу фильтровать данные на основе элементов в списке?
  • Excel Преобразование строк в столбцы
  • Как вы видите нижнюю часть действительно высоких ячеек в Excel?
  • Остановить Excel, распознающий тире
  • Как получить или скопировать фоновое изображение в Excel?
  • Если Menber Active, чем имя залива, еще проверьте другое в excel
  • 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) (А затем автоматически скопируйте формулу вниз по всем строкам ваших данных). Затем вы можете отсортировать всю таблицу по этому столбцу, чтобы ваши данные были разделены на две группы: описания с «парком» в них и без них. Добавьте еще один столбец, например, с «едой» в них. Затем, между «пищей» и «парком», вы можете сортировать (используя обе колонки) в четырех группах: «Без единого слова», «с едой», с «парком» и с обоими.

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

    Interesting Posts

    Как получить доступ к VPN-серверу с частным IP-адресом?

    Ноутбук не выключается, когда крышка закрыта, но когда крышка открыта

    Как я могу физически уничтожить данные с отказавшего жесткого диска?

    Как получить горизонтальные линии слева и справа от заголовка в Microsoft Word?

    Существуют ли какие-либо инструменты GUI для управления созданием символических ссылок?

    Хостинг веб-сайта у себя дома: порт блокирования ISP 80?

    Как удалить значок корзины с рабочего стола?

    Сохранение местоположений окон

    Предоставление доступа к Интернету из окон в Android через Bluetooth

    Почему так мало приложений 64-бит?

    Часы Windows XP установлены неправильно после возобновления сна

    Баш говорит, что файл не существует, но он

    Настройка предпочтительного сетевого адаптера для просмотра

    Чистая установка Mac OS X 10.6 с использованием Upgrade DVD

    Импортируйте закладки Google Chrome из Windows в Mozilla Firefox в Ubuntu

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