☰

MS excel-根据关键字分配“类别”

tag-icon • tag-icon microsoft-excel
MS excel-根据关键字分配“类别”

我有一个包含费用的 Excel 文件（花费的金额在一列中），在下一列中，我有简短的描述，主要由多个单词组成。我想“简化”描述并为每个描述分配一个或两个单词，这些单词将位于其旁边的另一列中。问题是描述不是“统一的”，例如，我可以使用“商务午餐”、“XXX 餐厅的商务晚餐”、“与记者喝咖啡”等字符串，我想为这些描述分配“食物”标签。还有遵循类似模式的不同类别。

我的想法是创建另一个表格（在不同的表单上） - 在一列中我有关键字，如“咖啡”、“午餐”、“晚餐”，在它们旁边的列中我有我想要分配的标签，即“食物”。我使用近似匹配的 vlookup 函数，但它返回了错误的结果。由于某种原因，列表中单词的顺序似乎会影响结果，即使存在部分匹配（字符串中的一个单词完全匹配），vlookup 也会忽略它并返回其他内容。例如，我有“酒店 xxx 的停车场”，在表格中我有“停车场”-“旅行费用”对，vlookup 返回“食物”标签。

你能帮我解决这个问题吗？（你会建议不同的方法吗？）

答案1

您需要FIND()and/orSEARCH()函数。用法：

FIND(find_text, within_text)

                                                                返回第一个文本字符串在第二个文本字符串中                                                                 的起始位置（从位置 1 开始）

因此FIND("lunch", "lunch with customer")返回 1，并FIND("lunch", "business lunch")返回 10。如果在第二个字符串中找不到第一个字符串，则返回#VALUE!错误值。  除了区分大小写之外，它SEARCH()类似于不区分大小写。因此FIND()FIND()SEARCH()

FIND("lunch", "Lunch with customer")返回#VALUE!
但
SEARCH("lunch", "Lunch with customer")返回 1

我假设您想要使用SEARCH()不区分大小写的。

您将需要设置如下数组：

                                                        关键字列表

最好在单独的工作表中执行此操作；我们将其称为Key-Sheet。然后，在您的数据表上：如果您的自由格式描述在列中A （从单元格开始A1），请在单元格中输入以下内容B1：

=MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1))

并按Ctrl+ Shift+ Enter，使其成为“数组公式”。 （它将显示在公式栏中的括号中。）说明：

  • SEARCH('Key-Sheet'!$A$1:$A$7,$A1)– 对于关键表列中的每个关键字（“咖啡”、“午餐”、“晚餐”等），在数据表A当前行、列的描述中搜索它（例如，“商务午餐”）。这将创建一个A大批包含 { #VALUE!; 10; #VALUE!; … }（（本例中）有七个元素，每个关键字一个；第二个显示“lunch”的结果，位于 中'Key-Sheet'!A2）。
  • IFERROR(…,LEN($A1)+1)– 将#VALUE!值替换为15，其中LEN("business lunch")+1，不可能是有效的返回值SEARCH()（事实上，它高于任何可能的有效返回值SEARCH()），但它是一个有效数字。所以现在我们的数组是 { 15; 10; ; 15… }。
  • MIN(…)– 从数组中提取最小值：在此示例中为10。通常，这将是 的（第一次）成功返回SEARCH()。
  • =MATCH(…, …)– 请注意，第二个参数MATCH()与上面的第一个项目符号相同。因此，我们10在数组 { #VALUE!; 10; #VALUE!; … } 中查找。这将返回 的位置10，即 2，对应于A1数据表（“商务午餐”）包含“午餐”，它位于 Key-Sheet 的第二行。

要获取费用类别，只需对BKey-Sheet 的列进行索引即可。将单元格设置C1为=OFFSET('Key-Sheet'!$B$1,B1-1,0)。（这不需要是数组公式。）

                                费用数据

请注意（如上所述），如果费用描述包含多个关键字，则只会找到第一个关键字。

如果你不想考虑中间值，你可以直接计算

=OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0)

这做需要是一个数组公式。

PSFIND()和SEARCH()函数有一个可选的第三个参数：

SEARCH(find_text, within_text, [start_num])

所以

SEARCH("cigar", "Sometimes a cigar is just a cigar.")返回 13
但
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17)返回 29

我看不出你有什么理由使用它。

答案2

正如泰森所说，“接近/近似”匹配不适用于单词。引用帮助文件：

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

这意味着如果您在“1,2,5,8,12”中查找值“7”，则返回的值将是“5”，这是最接近 7 但不大于 7 的值。

如果没有某种广泛的编程和对单个单词和语法分析的评估，就无法轻松地完成您想做的事情。

您应该做的是训练自己在最初输入数据时输入某种“类别代码”，然后使用备忘录栏来记录“其他详细信息”......例如“01-食物和饮料”、“带老板出去吃饭庆祝他的生日”。

如果您已经拥有大量数据，这可能很难做到，您可以使用一些技巧来加快速度（尽管您仍然需要进行大量手动整理工作）。

首先添加一个列，检查描述中是否有单词“park”，如果未找到则返回 0，如果找到则返回 1。类似于“=If(Search("park",A1)>1,1,0)”（然后将公式自动复制到数据的所有行）。然后，您可以根据该列对整个表格进行排序，这样您的数据就会分为两组：描述中包含“park”和不包含“park”的描述。添加另一列，例如，包含“food”的描述。然后，在“food”和“park”之间，您可以（使用两列）将其排序为四组：不包含任何单词的组、包含“food”的组、包含“park”的组和同时包含两者的组。

重复此操作，您可以快速整理出明显属于某一类别的组，用类别代码标记它们，然后在进行其他单词搜索时忽略它们，直到所有内容均已分类。

相关内容