Как классифицировать банковские транзакции в Excel?
Управление личными или корпоративными финансами часто требует анализа подробного списка ежемесячных банковских транзакций. Эти записи могут включать самые разные описания — от ресторанов и магазинов до коммунальных платежей и подписок. Отслеживать и анализировать расходы становится гораздо проще, когда каждую транзакцию можно отнести к определённой категории, например «Takeout», «Groceries», «Utilities» или «Family fee». Автоматизировав классификацию в Excel на основе ключевых слов из описаний транзакций, вы каждый месяц будете получать чёткую картину своих расходов и моделей трат.
Как показано на скриншоте ниже, предположим, у вас есть исходные данные с названиями поставщиков или услуг в столбце B, и вы хотите присвоить каждой транзакции упрощённую категорию (например, любая транзакция, содержащая «)Mc Donalds», должна быть помечена как «Takeout», а «Walmart» — как «Family fee») в соответствии с вашими личными или корпоративными правилами. В этом пошаговом руководстве рассматриваются несколько практических решений — от формульных подходов до расширенной автоматизации.
Содержание:
Категоризация банковских транзакций с помощью формулы в Excel
Код VBA — автоматизация категоризации с помощью макроса на основе заранее определённого списка
Другие встроенные методы Excel — использование Power Query с логикой условных столбцов
Категоризация банковских транзакций с помощью формулы в Excel
Если вы предпочитаете простой, основанный на формулах подход к категоризации банковских транзакций в Excel, следуйте этим шагам. Этот метод идеально подходит для тех, кто хочет получить быстрые результаты и готов поддерживать небольшую справочную таблицу с ключевыми словами и категориями.
Сначала создайте два вспомогательных столбца за пределами основного списка транзакций: в одном укажите ключевые слова (соответствующие возможному содержанию описания транзакции), а в другом — категории, которые вы хотите сопоставить с каждым ключевым словом.
1. В данном примере перечислите все ключевые слова для сопоставления (например, «McDonald’s», «Walmart» и т. д.) в столбце A со строки 30 по 41, а соответствующие названия категорий («Takeout», «Family fee» и т. д.) — в столбце B со строки 30 по 41.
Если ваш список ключевых слов или категорий расширяется или часто меняется, просто скорректируйте диапазоны, чтобы они охватывали все необходимые данные. См. скриншот:
2. Далее щелкните по первой ячейке в нужном вам выходном столбце (например, F3 рядом с последним столбцом ваших данных), затем введите эту формулу массива. Нажмите Ctrl + Shift + Enter (а не просто Enter), чтобы подтвердить ввод, поскольку это формула массива. Она найдёт первое совпадение ключевого слова в описании и вернёт соответствующую категорию.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),"Other")
После того как формула заработает для первой строки, протяните маркер автозаполнения вниз по столбцу, чтобы применить формулу категоризации ко всем остальным строкам транзакций.
Пояснение параметров:
Анализ преимуществ и недостатков:Подход на основе формул обеспечивает быструю настройку и минимальное обслуживание, если правила категоризации почти не меняются. Однако по мере усложнения списка ключевых слов или при частом обновлении категорий управление вспомогательными столбцами и формулами может стать обременительным — в таком случае стоит рассмотреть автоматизацию с помощью VBA или Power Query.
Практический совет: Если в описании встречается несколько ключевых слов из вашего списка, категория будет определяться по первому найденному. Чтобы задать приоритет определённому ключевому слову, разместите его выше в справочном столбце.
Типичные проблемы и их решение:Если результаты не соответствуют ожиданиям, дважды проверьте диапазон поиска и убедитесь, что ключевые слова написаны последовательно и полностью. Также проверьте наличие лишних пробелов или различий в форматировании описаний транзакций.
Код VBA — автоматизация категоризации с помощью макроса, сопоставляющего описания транзакций с категориями на основе заранее определённого списка
Это решение использует макрос VBA для автоматизации сопоставления транзакций с категориями, обеспечивая повышенный контроль и масштабируемость. Оно особенно подходит пользователям с большим объёмом транзакций, а также тем, кто хочет сделать справочник «ключевое слово — категория» динамичным и свести к минимуму ручное управление формулами.
Применимый сценарий: Когда список транзакций длинный, обновления происходят часто или вы хотите избавиться от необходимости поддерживать ручные формулы, макросы VBA справляются с задачей эффективнее: они обрабатывают каждое описание и присваивают нужную категорию на основе настраиваемой логики и подсказок.
Пошаговые действия:
- Создайте список «ключевое слово — категория», аналогичный вспомогательным столбцам, применяемым в формуле (например, в столбцах A и B начиная со строки 30).
- Нажмите Alt + F11, чтобы открыть редактор Visual Basic для приложений. В окне VBA выберите Вставка > Модуль, чтобы добавить новый модуль.
Скопируйте и вставьте следующий код в модуль:
Sub CategorizeTransactions()
Dim lastRow As Long
Dim i As Long
Dim descCell As Range
Dim kwRow As Long
Dim kwRange As Range
Dim catRange As Range
Dim kwCount As Long
Dim catResult As String
Dim matched As Boolean
On Error Resume Next
xTitleId = "KutoolsforExcel"
kwCount = Cells(Rows.Count, "A").End(xlUp).Row - 29
Set kwRange = Range("A30:A" & 29 + kwCount)
Set catRange = Range("B30:B" & 29 + kwCount)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To lastRow
Set descCell = Cells(i, "B")
catResult = "Other"
matched = False
For kwRow = 1 To kwCount
If InStr(1, descCell.Value, kwRange.Cells(kwRow, 1).Value, vbTextCompare) > 0 Then
catResult = catRange.Cells(kwRow, 1).Value
matched = True
Exit For
End If
Next kwRow
Cells(i, "F").Value = catResult
Next i
End SubКак использовать:
- Нажмите кнопку
Выполнить или клавишу F5 в редакторе VBA, чтобы запустить макрос. Макрос обработает каждое описание транзакции в столбце B, сопоставит его со списком ключевых слов и запишет соответствующую категорию (или «Other», если совпадений не найдено) в столбец F той же строки. - Вы можете настроить диапазоны и задать количество строк непосредственно внутри макроса в соответствии с расположением ваших данных — например, указать строку, с которой начинаются описания транзакций, или определить местоположение вспомогательного списка. Убедитесь, что в списке ключевых слов отсутствуют пустые ячейки, а категории чёткие и уникальные для удобной и надёжной ссылки.
Выполнить или клавишу F5 в редакторе VBA, чтобы запустить макрос. Макрос обработает каждое описание транзакции в столбце B, сопоставит его со списком ключевых слов и запишет соответствующую категорию (или «Other», если совпадений не найдено) в столбец F той же строки.Анализ преимуществ и недостатков:Решение на основе VBA легко адаптируется под более сложные правила, может быть запущено повторно после обновления списка ключевых слов или категорий и избавляет от необходимости использовать формулы массива. Однако для работы макросов требуется включить автоматизированные операции в Excel, что может быть неприемлемо в некоторых средах или для отдельных пользователей.
Практический совет: Сохраняйте макрос VBA в многократно используемом файле и всегда делайте резервную копию данных перед запуском макросов — на случай случайной перезаписи.
Рекомендации по устранению неполадок:Если категории не обновляются должным образом, проверьте соответствие списков ключевых слов и категорий, а также отсутствие скрытых символов в ячейках. VBA нечувствителен к регистру при использовании «vbTextCompare», но несоответствия всё равно могут возникать из-за различий в форматировании.
Другие встроенные методы Excel — использование Power Query для настройки категоризации на основе правил с помощью логики условных столбцов
Если вы предпочитаете современный подход к автоматизации без программирования, Power Query предлагает надёжное решение для категоризации банковских транзакций. Этот метод идеально подходит для импорта данных из CSV-файлов и онлайн-источников, а также для работы с динамическими и изменяющимися правилами категоризации — он централизует логику и обеспечивает простое обновление без сложных формул или скриптов VBA.
Пошаговые действия:
- Сначала убедитесь, что ваши транзакции оформлены в виде таблицы или чётко структурированного диапазона данных. Выберите любую ячейку в таблице, затем перейдите в меню Данные > Из таблицы/диапазона, чтобы загрузить данные в Power Query.
- В редакторе Power Query нажмите Добавить столбец > Условный столбец, чтобы создать новый столбец с правилами категоризации.
- Задайте правила сопоставления, например:
- Если Описаниесодержит «Mc Donalds», выведите «Takeout»
- Если Описаниесодержит «Walmart», выведите «Family fee»
- В противном случае выведите «Other»
- Нажмите OK, чтобы создать условный столбец, затем выберите Закрыть и загрузить, чтобы вернуть результаты категоризации в Excel.
Анализ преимуществ и недостатков:Power Query обеспечивает гибкость при изменении правил, отлично интегрируется с импортом внешних данных и мгновенно обновляет результаты при изменении транзакций или правил. Он также эффективнее справляется с очень большими наборами данных по сравнению с ручными формулами. Однако для работы с Power Query требуется базовая настройка и знакомство с его интерфейсом, что может быть в новинку для некоторых пользователей.
Практические советы:Располагайте правила в порядке приоритета (сверху вниз) в диалоговом окне «Условный столбец»; будет применяться первое совпадающее правило. Вы можете легко редактировать, добавлять или удалять правила в Power Query, не изменяя формулы на основном листе Excel.
Напоминания об ошибках: Если вы импортируете новые транзакции, а результат не обновляется, как ожидалось, нажмите Обновить на вкладке «Данные» в Excel. Обращайте внимание на правописание и точное совпадение ключевых слов в Power Query — даже незначительные несоответствия могут помешать корректному присвоению категории.
Рекомендация по устранению неполадок:Если категории отображаются некорректно, проверьте правила условного столбца на наличие конфликтов или пропущенных случаев. Перед применением изменений ко всему набору данных рекомендуется протестировать несколько примеров записей.
В заключение: независимо от того, отдаёте ли вы предпочтение формулам массива, гибкости макросов VBA или упрощённой автоматизации с помощью Power Query, Excel предоставляет множество практичных решений для эффективной категоризации банковских транзакций. Всегда следите за актуальностью правил категоризации и согласованностью результатов по мере изменения данных транзакций или критериев их классификации.
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек