☰

如何对列表进行分类？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-excel-2007 microsoft-excel-2013
如何对列表进行分类？

我想通过匹配某些字符串来对列表进行分类/排序。我之前找不到解决方案，所以希望这种方法更简单。

示例列表：

[A]
The apple is the pomaceous fruit of the apple tree
Apples grow on deciduous trees which are large if grown from seed
Apples are an important ingredient in many desserts, such as apple pie
Puréed apples are generally known as apple sauce
A banana is an edible fruit produced by several kinds of large plants
Worldwide, there is no sharp distinction between "bananas" and "plantains"
The term "banana" is also used as the common name for the plants
Orange is the colour of saffron, pumpkins and apricots
The colour orange is named after the appearance of the ripe orange fruit
In ancient Egypt, artists used an orange mineral pigment called realgar
Apple, orange and banana smoothie
Eating an orange and banana exceed allowable sugar intake
Kale or borecole (Brassica oleracea Acephala Group) is a vegetable
Until the end of the Middle Ages, kale was one of the most common green vegetables

所寻找的字符串及其分类方式（不区分大小写）：

Apple = Apple
Apple Pie = Dessert
Banana = Banana
Orange = Orange
(anything not categorized) = Vegetables
(multiple found strings) = Multiple --> if this isn't possible it's fine

列表旁边的列可能会显示以下内容：

[B]
Apple
Apple
Pie
Apple
Banana
Banana
Banana
Orange
Orange
Orange
Multiple
Multiple
Vegetables
Vegetables

然后我只需使用排序/过滤。谢谢！

答案1

我会使用 Power Query 插件来解决这个问题。这需要几个步骤，但不需要代​​码或更改输入数据结构。

我已经构建了一个原型，您可以查看或下载它 - 在我的 One Drive 中是“Power Query 演示 - 搜索关键字列表并进行分类”：

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

基本上，我的技术是构建一个初步查询来加载类别列表并分配一个虚拟合并键，然后使用虚拟合并键将其与要搜索的文本合并。这会为每个输入行 x 每个类别生成一行。然后我使用 Text.Contains 函数计算类别，最后使用 Group By 返回原始行集。

此时，您将拥有一个规范化的表，非常适合通过过滤或使用数据透视表和/或数据透视图进行探索。

答案2

事实上，你希望它是动态的和层次化的（苹果派优先于苹果），这使得它有点困难，但如果你愿意对它进行静态编程，你可以做这样的事情：

Row 1 - Your search text
Row 2 - Your result text

B1=Apple
B2=Apple
B3=If(Len($A2)>LEN(SUBTITUTE(LOWER($A2),LOWER(B$1),"")),B$2,"")
C1=Apple Pie
C2=Deserts
D1=Orange
D2=Orange
Drag B3 across and down

您所做的就是用空字符替换“apple”的实例，然后计算字母数以查看字母数是否少于原始字母数。通常，这是一个区分大小写的操作，但我首先将要比较的两个文本都使用了小写。如果该列的搜索文本有匹配项，这将在每一列中输出结果文本。

为了合并层次结构，您可以将 B 的列更改为 IF(LEN(C2)>0,"",NORMAL FORMULA)，这样如果 C 列已经有值，该列就不会显示 APPLE。NORMAL FORMULA 只是上面 B3 的公式。

然后你可以用一个计数器来测量你有多少次点击

=IF(COUNTA(B2:D2)=0,"Vegitables",IF(COUNTA(B2:D2)>1,"Multiple",B2&C2&D2))

如果有 0 个匹配项，则为蔬菜，如果有多个，则为多个，否则您将只填写一个字段，因此您可以通过连接结果来获得最终答案。

此外，我确实想出了一个公式，它只需要一个动态列表，就可以计算出你有多少个匹配项。这是一个数组函数，所以你必须输入它而不使用 {}，然后不要按 Enter，而是按 ctrl+shift+enter

{=SUM(--(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($F$1:$F$6),""))>0))}

虽然这会失败，因为对于其中包含“苹果派”的任何东西，它都会失败，因为它同时包含苹果和派，但是它获胜了，因为它可以根据 F 列中提供的动态列表进行操作。

相关内容