Excel 公式关联类别/子类别对并生成列表

Question

我有五列，它们决定了文章的 ID 以及与文章关联的类别。数据示例如下：

article_id   category_id   subcategory_id   2nd_category_id   2nd_subcategory_id

94           C02           M1001        
96           C06
98           C06
101          C03           M1001        
108          C01           M1001        
110          C01           M1001        
111          C03           M1003            C02               M1001
114          C01                            C02
115          C01           M1001            C01               M1002

从上面的介绍来看，一篇文章似乎可以归入四个类别。实际上，它被分配到一个或两个类别，每个类别都有一个可选的子类别。（有六个父类别。每个类别最多可以有四个子类别。文件中大约有 11,000 个条目（即行/文章）。）不幸的是，子类别代码名称不是全局唯一的。例如，类别 C01 是“树木”，类别 C02 是水果。但 C01 子类别 M1001 是常绿植物，而 C02 子类别 M1001 是苹果。请注意，如果至少有一个分配与子类别相结合，则一篇文章可以分配到同一个类别两次 - 在上面的示例中，文章 115 被分配到 C01 两次。

我需要做的是创建一个公式，将它们聚合为一个以逗号分隔的字段值，列出已识别的类别和子类别。

我怎样才能做到这一点？ 我想这有三个部分：

1. 在 category_id 列和 2nd_category_id 列上执行 Find+Replace，将父类别值替换为适当的字符串名称。因此，C01 变为 Trees。

2. 使用某种公式，根据 category_id 中的字符串值，用其名称替换 subcategory_id 值。对 2nd_subcategory_id 重复此操作。如果不存在 subcategory_id，则将值留空。

3. 使用另一个公式将值复制到新列中，尽可能消除重复条目（例如，一篇文章可能被分配给 C01（从子类别继承的父类别）和 M1001（子类别）和 C01（第二类别）。在这种情况下，公式应在新列中提供“Trees, Evergreen, Trees”的值。不需要重复输入“Trees”，因此新列值中只需要存在“Trees, Evergreen”。

也许我把事情复杂化了，其实有一个非常简单的方法可以实现这一点。也许不是。有什么指点吗？

我想要创建的一个例子如下：

article   category   subcategory   category2   subcat2   categories
94        C02        M1001                               Fruits, Apples
96        C06                                            Seeds
98        C06                                            Seeds
101       C03        M1001                               Plants, Shrubs
108       C01        M1001                               Trees, Evergreens
110       C01        M1001                               Trees, Evergreens
111       C03        M1003         C02         M1001     Plants, Climbers, Fruits, Apples
112       C06                                            Seeds
113       C01                                            Trees
114       C01                      C02                   Trees, Fruits
115       C01        M1001         C01         M1002     Trees, Evergreens, Deciduous

Answer 1

我会给你一些答案：

1. 在 Excel 工作簿中的某个位置（可能在不同的表上）创建两个查找表：

   C01   Trees
   C02   Fruits
   C03   Plants
    ⋮     ︙ 
   C06   Seeds
   

   和

   C01_M1001   Evergreens
   C01_M1002   Deciduous
       ⋮         ︙ 
   C02_M1001   Apples
       ⋮         ︙ 
   C03_M1001   Shrubs
   C03_M1003   Climbers
       ⋮         ︙ 
   

2. 将单元格W2-设置Z2为如下所示的内容：

   • W2→=B2
   • X2→=IF(C2="", "", B2 & "_" & C2)
   • Y2→=IF(D2=B2, "", D2)
   • Z2→=IF(E2="", "", D2 & "_" & E2)

3. 现在使用查找表将上述内容更改为类别/子类别名称。我不会解释这方面的细节，因为它们在 Excel 文档和超级用户答案中都有详尽的介绍。

4. 看生成以逗号分隔的单元格内容列表，不包括空白 以找到制作categories清单的方法。