Вопрос или проблема
У меня есть список строк, описывающих мой банковский отчет. И я хочу категоризировать их с помощью формулы.
Категоризация основана на ключевых словах. Если описание содержит ключевое слово, то оно будет возвращать категорию. Проблема в том, что я не могу найти способ поиска ключевого слова, когда порядок не совпадает.
Например, у меня есть строка “aaa bbb ccc”, она работает нормально, когда я ищу “aaa ccc” с помощью regexmatch, но не работает, когда я пытаюсь искать “ccc aaa”. Я также пробовал использовать комбинации формул SEARCH, MAP, LAMBDA, но безуспешно.
Вот пример случая.
https://docs.google.com/spreadsheets/d/1W-8P3Tj1M0ICm5-4pNnX9oM_NLU9pmUpp2k6QP7ObpM/edit?gid=1763746401#gid=1763746401
Объяснение:
- Столбец A листа описания содержит описания, которые нужно категоризировать.
- Столбец B листа описания содержит мои формулы regex, map и lambda. Успешно сработало в B2, однако в B4 возникла ошибка.
- Столбец C листа описания содержит мою формулу поиска, map и lambda.
- Столбец A листа ключевых слов содержит ключевые слова (разделенные запятыми), а столбец B содержит категорию.
Проблема видна в описании!B4, где возвращается ошибка, поскольку “kkk ttt” не может быть сопоставлено с “ttt,kkk”.
Есть ли способ решить эту проблему? Мне нужна массивная формула, чтобы не тянуть формулу вниз снова и снова, и чтобы было проще поддерживать. Помощь с листами или колонками ок, если это массиовая формула.
Ответ или решение
Для решения вашей задачи по категоризации описаний на основе ключевых слов в Google Sheets, мы можем использовать формулы массива и функционал LAMBDA, чтобы обойти ограничения поиска с учетом порядка слов.
Шаг 1: Подготовка данных
Убедитесь, что у вас есть две таблицы:
- Sheet1 (или ваш основной лист), где в столбце A находятся описания.
- Sheet2 (или лист с ключевыми словами), где в столбце A находятся ключевые слова, разделенные запятыми, а в столбце B – соответствующие категории.
Шаг 2: Формула
В ячейке B2 вашего основного листа (Sheet1) добавьте следующую формулу для реализации поиска ключевых слов с учетом неупорядоченности:
=ARRAYFORMULA(IF(A2:A="", "",
IFERROR(VLOOKUP(
FLATTEN(
UNIQUE(
TRIM(TRANSPOSE(SPLIT(A2:A, " ")))
)
),
FILTER(SPLIT(FLATTEN(Sheet2!A:A & "|" & Sheet2!B:B), "|"), ROW(Sheet2!A:A) > 0),
2, FALSE),
"Не найдено"
))
))Объяснение формулы
- SPLIT(A2:A, " "): Делит каждое описание на отдельные слова.
- TRANSPOSE и UNIQUE: Создают массив уникальных слов из каждого описания.
- FLATTEN: Преобразует многомерный массив в одномерный, что позволяет использовать его в дальнейших вычислениях.
- FILTER: Находит ключевые слова из Sheet2 и соответствующие категории, если ключевые слова присутствуют в описании.
- VLOOKUP: Сопоставляет найденные уникальные ключевые слова с категориями и возвращает их.
- IFERROR: Если поиск не успешен, возвращает сообщение "Не найдено".
Шаг 3: Применение формулы
Эта формула автоматически заполнит столбец B, и вам не нужно будет перетаскивать ее вниз. Она будет работать для всех ваших описаний, если в столбце A есть данные.
Заключение
С помощью предложенной формулы можно эффективно классифицировать строки из вашего банка описаний, независимо от порядка появления ключевых слов. Надеюсь, это решение будет полезным и упростит вашу задачу. Если у вас возникнут дополнительные вопросы или потребуется доработка формулы, не стесняйтесь обращаться!