Категоризация банковских выписок в Google Sheets

Я пытаюсь сгруппировать расходы в банковской выписке с помощью Google Sheets. Я хотел бы знать, сколько было потрачено на газ, продукты питания и т. Д.

Данные

Выписка из банка

Банковские расходы импортируются в следующем формате:

+----------+-----------------------------------------------------------+---------+------+ | Date | Description | Amount | Type | +----------+-----------------------------------------------------------+---------+------+ | 01/01/18 | CARD INT USPS INT CITY STATE INT CARD INT | -AMOUNT | | | 01/01/18 | CREDIT INT CURRENCY CITY STATE | +AMOUNT | | | 01/01/18 | CARD INT GROCERY STORE #0001 CITY STATE INT CARD INT | -AMOUNT | | | 01/01/18 | CARD INT DIFF GROCERY STORE #0002 CITY STATE INT CARD INT | -AMOUNT | | | 01/01/18 | CARD INT GAS STATION #0001 CITY STATE INT CARD INT | -AMOUNT | | | 01/01/18 | CARD INT DIFF GAS STATION #0005 CITY STATE INT CARD INT | -AMOUNT | | | 01/01/18 | CREDIT INT CURRENCY CITY STATE | +AMOUNT | | +----------+-----------------------------------------------------------+---------+------+ 

Тип таблицы

Я сделал таблицу типов, которая имеет следующие данные:

+-----------+--------------------+ | Type | Keyword | +-----------+--------------------+ | Gas | GAS STATION | | Gas | DIFF GAS STATION | | Groceries | GROCERY STORE | | Groceries | DIFF GROCERY STORE | +-----------+--------------------+ 

Алгоритм

То, что я думаю сделать, это:

• Для каждого ключевого слова в таблице типов
  • Если описание из таблицы выписок по счету содержит его
    • Установите тип столбца Заявление банка таблице на тип столбца Тип таблицы

Желаемый результат

Таким образом, таблица банковских выписок будет:

+----------+-----------------------------------------------------------+---------+-----------+ | Date | Description | Amount | Type | +----------+-----------------------------------------------------------+---------+-----------+ | 01/01/18 | CARD INT USPS INT CITY STATE INT CARD INT | -AMOUNT | FALSE | | 01/01/18 | CREDIT INT CURRENCY CITY STATE | +AMOUNT | FALSE | | 01/01/18 | CARD INT GROCERY STORE #0001 CITY STATE INT CARD INT | -AMOUNT | Groceries | | 01/01/18 | CARD INT DIFF GROCERY STORE #0002 CITY STATE INT CARD INT | -AMOUNT | Groceries | | 01/01/18 | CARD INT GAS STATION #0001 CITY STATE INT CARD INT | -AMOUNT | Gas | | 01/01/18 | CARD INT DIFF GAS STATION #0005 CITY STATE INT CARD INT | -AMOUNT | Gas | | 01/01/18 | CREDIT INT CURRENCY CITY STATE | +AMOUNT | FALSE | +----------+-----------------------------------------------------------+---------+-----------+ 

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

Попытка решения

В столбце «Тип» таблицы «Выписка по счету» ближе всего я добился успеха:

=INDEX(Type!A:A,MATCH(TRUE,ISNUMBER(SEARCH(Type!B:B,B10))),0) 

Это должно:

1. ПОИСК (тип! B: B, B10) - поиск в таблице типов по всем ключевым словам и сопоставление с описанием (не уверен, что в качестве первого аргумента можно принять коллекцию)
2. MATCH (TRUE, ISNUMBER ((результат из 1.))) - приведение результатов к TRUE или FALSE для каждой строки
3. = ИНДЕКС (Тип! A: A, (результат из 2.), 0) - Получить тип из таблицы типов

Благодарен за любую помощь, которую вы можете оказать