excel - 在 Excel 中对银行交易进行分类

Question

我有一个 Excel 格式的银行交易导出列表，我想尽可能快速、简单地对其进行分类。我怀疑仅使用数组公式就可以做到这一点，但 VBA 函数同样有用。

场景

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

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

日期、描述和金额均由我的银行预先填写。我在 D 列中填写每笔交易的类别。

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

需求

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

我想要的输出如下所示:

| 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 列，

逻辑很简单:

• 当我输入手动类别时，Excel 会执行两项操作:

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

• 如果 Excel 遇到包含我的规则之一中使用的文本的说明，它会填充 F 列中的相关类别。

好处

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

迄今为止我最好的尝试

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

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

Answer 1

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

我的想法很简单:根据关键词制定一套分类规则。如果在描述中找到关键字，则会应用规则并设置类别。对使用 VBA 或 PowerShell 的想法不满意，继续四处查看并发现以下帖子:

how-to-group-excel-items-based-on-custom-rules作者:John Bustos(请注明作者)

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

1. 规则在两列中定义(关键字 - 类别) - 如果我们假设它们位于 F 列和 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
   

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

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

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

最后，这要归功于 John Bustos，he is the one that provided the solution here 。我发现他的解决方案很简单并且非常容易实现，所以我想将其包含在这里，因为通过“Excel 中的自动分类”进行搜索并没有立即找到它。我不得不尝试其他搜索词。