在 Excel 中对银行交易进行分类 [英] Categorizing bank transactions in Excel

问题描述

我在 Excel 中有一个导出的银行交易列表，我想尽可能快速和简单地对其进行分类.我怀疑这仅适用于数组公式，但 VBA 函数同样有用.

I have an exported list of bank transactions in Excel, which I'd like to categorize as quickly and simply as possible. I suspect this is possible just with array formulas, but a VBA function would be equally useful.

场景

我的电子表格标题如下所示:

My spreadsheet header looks like this:

| A    | B           | C      | D        |
==========================================
| Date | Description | Amount | Category |
------------------------------------------

日期、描述和金额是从我的银行预先填充的.我在 D 列中为每笔交易填写了类别.

Date, Description and Amount come pre-populated from my bank. I fill in the category for each transaction in column D.

这很好，但很耗时，因为每个类别都必须单独手动输入.

This is fine, but time-consuming, because each category has to be entered individually and manually.

需求

我希望根据动态生成和应用的规则在我尚未输入手动类别的行中自动填充类别.

I want categories to auto-populate in rows where I haven't entered a manual category, based on rules that are dynamically generated and applied.

我想要的输出如下所示:

The output I want looks like this:

| A        | B             | C       | D           | E         | F           |
==============================================================================
| Date     | Description   | Amount  | Manual cat. | Rule      | Auto cat.   |
------------------------------------------------------------------------------
| 04/08/12 | Starbucks NYC |  -$5.42 | Coffee      | starbucks | Coffee      |
| 04/09/12 | Wal-Mart 468  | -$54.32 | Supermarket | wal-mart  | Supermarket |
| 04/10/12 | Starbucks SF  |  -$3.68 |             |           | Starbucks   |

如您所见，我在 D 列中输入了手动"类别.无论我在何处执行此操作，我都在 E 列中输入了分类规则".然后 Excel 会使用我的条目自动填充 F 列，

As you can see, I have entered 'manual' categories in Column D. Wherever I have done this, I have entered a categorization 'rule' in Column E. Excel is then using my entries to auto-populate Column F,

逻辑很简单:

• 在我输入手动类别时，Excel 会做两件事:

• Where I enter a manual category, Excel does two things:

1. 在 F 列中填充我的手册类别.
2. 使用在 E 列中输入的文本创建规则.

如果 Excel 遇到包含在我的规则中使用的文本的描述，它会填充 F 列中的相关类别.

Where Excel comes across a description that contains text used in one of my rules, it populates the relevant category in Column F.

好处

这将使整理交易列表、添加类别和相关规则变得非常简单.没有类别的交易将在规则适用的情况下自动填充，在规则不适用的情况下留空.应用规则给出错误类别的交易可以更正，并提供新规则.

This would make it very simple to whiz down the list of transactions, adding categories and associated rules. Transactions without a category would be auto-populated where a rule applies, and left blank where a rule does not apply. Transactions where a rule has been applied giving the wrong category could be corrected, and a new rule supplied.

迄今为止我最好的尝试

我已经创建了一种仅使用公式来执行此操作的方法，但它具有三个缺点:

I have created a way of doing this using only formulas, but it has three drawbacks:

1. 它需要创建与事务一样多的列.
2. 没有列出类别和相关规则的便捷方法.
3. 无法更改规则的应用顺序.

推荐答案

我也在寻找自动分类过程.上面的选项看起来非常强大，但比我想要的要复杂得多.

I was also looking for an auto-categorization process. The options above seem really powerful but way more complicated than what I wanted.

我的想法很简单:制定一套基于关键词的分类规则.如果在描述中找到关键字，则应用规则并设置类别.对使用 VBA 或 PowerShell 的想法不满意，一直环顾四周，发现以下帖子:

My idea is simple: develop a set of categorization rules based on keywords. If a keyword is found on the description, the rule is applied and the category set. Not happy with the idea of using VBA or PowerShell, kept looking around and found the following post:

how-to-group-excel-items-based-on-custom-rules 作者:John Bustos(请相信他)

how-to-group-excel-items-based-on-custom-rules by John Bustos (please credit him)

John 的解决方案使用了一种非常简单的方法:

John's solution uses a very simple approach:

1. 规则在两列(关键字 - 类别)中定义 - 如果我们假设它们在 F 和 G 列中:

1. Rules are defined in two columns (Keyword - Category) - if we assume that they are in columns F and G:

Column F     Column G
Keyword      Category
Starbucks    Coffee shop
Wal-Mart     Supermarket
Safeway      Supermarket
In-N-Out     Fast Food
Comcast      Internet Service
Verizon      Mobile Phone Service

然后将此 ARRAY 公式添加到要插入类别的单元格，该类别指向要检查规则的单元格(假设是单元格 A2):

Then add this ARRAY formula to the cell where you want to insert the category pointing to the cell that you want to check for the rule (let's assume is cell A2):

=IFERROR(INDEX(G$2:G$7,MATCH(TRUE,ISNUMBER(SEARCH(F$2:F$7,A2)),0)),"Other")

记住使用 CTRL+SHIFT+ENTER 以确保它作为数组公式输入.如果您有更多规则，则需要更改范围高度.之后，您可以简单地将公式填充到您需要分类的所有行.此外，分类使用第一个规则并坚持该规则，因此如果您在一个目标单元格中​​存在两个不同的关键字，则将应用第一个关键字分类规则.规则必须手动创建，当单元格显示其他"时，表示没有找到关键字.

Remember to use CTRL+SHIFT+ENTER to make sure that it goes in as an array formula. If you have more rules, you will need to change the range height. Afterwards you can simply populate down the formula to all the rows that you need to categorize. Also, the categorization uses the first rule and sticks to that, so if you have two different keywords present in one of the target cells, the first keyword categorization rule will be applied. The rules have to be created manually, when a cell shows "Other" it means that there are no keywords found.

最后，归功于 John Bustos，他是在这里提供解决方案的那个.我发现他的解决方案非常简单且易于实施，因此我想将其包含在此处，因为通过excel 中的自动分类"进行搜索并没有立即想到.我不得不尝试其他搜索词.

Finally, the credit goes to John Bustos, he is the one that provided the solution here. I found his solution to be simple and extremely easy to implement so I wanted to include it here because searching by "auto categorization in excel" did not come up with it immediately. I had to try other search words.

这篇关于在 Excel 中对银行交易进行分类的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！