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

Question

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

3071

Pulse 2017-02-10 в 18:17

Мой босс хочет, чтобы наши банковские транзакции классифицировались вручную. Сейчас моя задача состоит в том, чтобы пройти через банковские транзакции и распределить их по категориям в зависимости от того, что представляет собой каждая позиция. Для случайных расходов (только один раз) имеет смысл посмотреть их и указать, к какой категории они относятся, но для повторяющихся вещей (одна и та же компания, предоставляющая одни и те же услуги каждый раз) имеет смысл автоматизировать их, особенно когда их много. транзакций.

Банковские операции экспортируются в Excel по дате, описанию, сумме, типу (дебетовый кредит и т. Д.).

Я хочу, чтобы Excel прошел через «Столбец описания» и превратил его во что-то простое и удобочитаемое. Пример Описание столбец содержит «ADP компании xyx 4003». Я хочу, чтобы excel возвратил «payroll», следующая ячейка содержит «# 3k322 Phil Marqu LLC», а я хочу, чтобы он возвратил «Cafe supplies». Я хочу сделать то же самое для депозитов, например, «Депозит WEB PMTS» должен стать «онлайн-продажи»

Таким образом, я могу затем создать таблицу и отсортировать по категориям, т. Е. «Расходные материалы для кафе» и «онлайн-продажи», чтобы создать простой ежемесячный отчет, показывающий поступающие и выводимые деньги.

Моя идеальная ситуация связана с отдельной справочной таблицей, которую я могу продолжать добавлять к ней, в которой будет 2 столбца. Столбец A будет соответствовать подстроке, а столбец B будет возвращаться. Пример, который я могу поместить в столбец A "# 3k322", потому что я знаю, что он всегда будет отображаться для поставщика принадлежностей для кафе, а столбец B будет "поставками для кафе", и если в будущем мы добавим еще одно кафе, поставляющее поставщика, я могу просто добавить еще одну строку к моей справочной таблице.

Я много читал в Интернете об индексах, матчах и vlookup, но я не профессионал Excel, и мне трудно понять это. Я узнаю все функции, которые вы мне бросаете. Я очень мотивирован, чтобы сделать это.

Спасибо.

0

2 ответа на вопрос

3

0

user19702 2017-02-10 в 19:08

Я создал макрос для аналогичного проекта, в основном назначив категорию для записи.

Предупреждение: это неэффективно. не имеет большого значения для личного использования обычно.

Требования:

две таблицы:

один с именами транзакций в первом столбце, пустой столбец 2

один с уникальными именами транзакций в первом столбце и их категории в столбце 2

нет пустых строк

изменить на for i = 1 - Xравное количество обрабатываемых транзакций

измените строку p на количество категорий, которые у вас есть

см .: https://stackoverflow.com/questions/23025031/excel-compare-two-columns-from-one-sheet-copy-entire-row-on-match-to-new-sheet

Sub AddDepartment() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim i As Long, j As Long, p As Long Dim isMatch As Boolean  Set ws1 = ActiveWorkbook.Sheets("Sheet1") 'transactions sheet Set ws2 = ActiveWorkbook.Sheets("Sheet2") 'categorized sheet  'Initial position of first element in sheet2 p = 1  For i = 1 To 100 'last row of transaction sheet necessary isMatch = False For j = 1 To 100 'last row of category sheet necessary If ws1.Cells(i, 1).Value = ws2.Cells(j, 1).Value Then 'if transactions.transactions = category.transactions ws2.Cells(j, 2).Copy ws1.Cells(p, 2) 'write category.category to transactions.category isMatch = True p = p + 1 End If Next j If isMatch = False Then ws1.Cells(p, 2) = "OTHER" 'set anything that does not match to "other" category p = p + 1 End If Next i End Sub

Спасибо, что нашли время ответить. Как бы я внедрил это в мою ситуацию? Я думаю, что мне нужно немного больше рекомендаций, так как я использовал макрос только один раз, и это было намного проще. Pulse 7 лет назад 0

создайте новый модуль в окне Visual Basic и вставьте код. Убедитесь, что ваш лист транзакций называется «Лист1», а лист категорий - «Лист2», и запустите макрос. Попробуйте в новой тестовой книге user19702 7 лет назад 0

Accepted Answer · 2017-02-10 20:07:44

Вы можете создать индексный массив, в котором вы пишете ключевое слово в столбце и соответствующую категорию во втором столбце той же строки:

# 3k322 Кафе поставляет
WEB Онлайн продажи

ключевое слово всегда должно содержаться в уже
имеющемся описании. Создайте новый пустой столбец рядом с описанием для категории и напишите эту формулу перед первым описанием:

=OFFSET($I$2,SUMPRODUCT(--ISNUMBER(FIND($I$2:$I$4,B2,1))*ROW($I$2:$I$4))-2,1)

Где $ I $ 2: $ I $ 4 - столбец ключевых слов, начинающийся с I2
J2: J4 - столбец соответствующих категорий
B2 - первое описание
Sumproduct найдет ключевое слово, соответствующее описанию в B2, и даст ему номер строки
Смещение найдет категорию, соответствующую найденной строке -2, потому что I2 - источник, а 1 - для второго столбца.
Вы можете перетащить формулу вниз, оставив $, где я написал ее в формуле, и измените ссылку, чтобы она соответствовала вашему
обновлению данных
в Для добавления категории без изменения формулы используйте следующее

=OFFSET($I$2,SUMPRODUCT(--ISNUMBER(FIND(INDIRECT($L$1),B2,1))*ROW(INDIRECT($L$1)))-2,1)

где L1 - ссылка на столбец ключевых слов, каждый раз, когда вы меняете категории (добавляете или удаляете), пишите в L1 ссылки: $ I $ 2: $ I $ 4
Не забывайте писать ключевое слово точно так, как оно написано в описании ( поиск в верхнем или нижнем регистре чувствителен к регистру)
INDIRECT прочитает L1 и преобразует его в $ I $ 2: $ I $ 4

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

2 ответа на вопрос

Похожие вопросы