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

Question

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

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

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

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

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

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

Спасибо.

Answer 1

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

# 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

Answer 2

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

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

Требования:

две таблицы:

один с именами транзакций в первом столбце, пустой столбец 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