В моей банковской выписке есть столбец описания, в котором указано, куда я потратил деньги или откуда я их получил. На основе этой информации я вручную заполняю столбец, чтобы иметь возможность категоризировать эти расходы - все транзакции клиента A, все транзакции Amazon. Это основано на том, что я читаю описание и нахожу ключевое слово. Могу ли я автоматизировать этот процесс? Я пробовал с очень длинной формулой - if(search("amazon",a1),"Amazon"), elseif(search ........ Это слишком сложно и подвержено ошибкам. Там будет около 20-30 категорий.
решение1
Подход вашей формулы может приблизить вас, но, как вы говорите, длинные формулы трудно диагностировать, а с ПОИСКОМ вам приходится иметь дело с ошибочными значениями, когда цель не найдена. Если вы разобьете проблему на отдельные ключевые слова, то найдете простое решение. Вот версия, и я пошагово разберу, что я сделал.
Столбец A содержит описания. Столбец B — ключевое слово, найденное для транзакции. Столбцы, начинающиеся с C, предназначены для ваших целевых ключевых слов, одна цель как заголовок столбца в каждом столбце. В качестве одноразовой задачи заполните ваши ключевые слова. Вы можете добавить ключевое слово в любое время и просто скопировать формулы в новый столбец из соседнего столбца.
Формулу в C2 можно копировать вдоль и поперек. Каждая ячейка в этих столбцах ключевых слов ищет ключевое слово этого столбца в описании этой строки. Формула, которую я использовал в C2, следующая:
=IF(ISERROR(SEARCH(C$1,$A2)),"",C$1)
Это ищет целевое ключевое слово и отображает его, если оно найдено. В противном случае отображается пробел. Если ваше целевое слово не всегда является ключевым словом, вы можете заменить последний параметр в формуле ключевым словом. Однако тогда вы не сможете просто скопировать формулу в любую ячейку без изменений.
Формула в B2:
=C2 & D2 & E2 &...
(Пробелы добавлены для удобства чтения.) Если вы считаете, что можете добавить больше ключевых слов, вы можете изначально объединить несколько дополнительных столбцов в формулу. Это просто объединяет результаты поиска ключевых слов для строки. Должно быть только одно совпадающее ключевое слово, поэтому, объединив его с кучей пробелов для других ключевых слов, вы получите совпадающее ключевое слово для транзакции. Если ни одно из ключевых слов не совпадает, ячейка будет пустой. Если вы добавите больше ключевых слов позже, убедитесь, что B2 содержит новые ссылки на столбцы, а затем скопируйте их вниз по столбцу.
Эстетика
Если вы не хотите видеть столбцы с вспомогательными ключевыми словами, вы можете скрыть их или разместить в области, которая не видна.
Если вы хотите сделать все в одной формуле, которая не опирается на вспомогательные столбцы, просто начните с этого подхода для построения формулы. Это позволяет вам создать одну короткую формулу и скопировать ее в другие столбцы и строки, чтобы проверить, что все работает. На этом этапе вы можете превратить B2 в комплексную формулу путем подстановки.
В B2 замените ссылку C2 формулой в C2 (скопируйте и вставьте формулу в C2, за исключением знака равенства, чтобы заменить ссылку на ячейку C2), и повторите для каждой ссылки на ячейку. Теперь у вас есть формула, которая ссылается только на список ключевых слов (который вы можете переместить в вертикальный список, если хотите).
Если вам не нужен даже список ключевых слов, замените ссылки на ячейки ключевых слов на сами ключевые слова. Тогда у вас будет полностью самодостаточная формула. Когда B2 будет вам по вкусу, скопируйте ее вниз по столбцу.