我有一张如下的表格:
| Line | string-column | tag-column |
| ----- | --------------- | ---------- |
| 1 | free txt string | |
| 2 | free txt string | |
| 3 | free txt string | |
| 4 | free txt string | |
| 5 | free txt string | |
我想要做的是根据第二列中的自由文本字符串在“TAG”列中填写一个单词。我有一个类似
(TAG1) -> string-column contains any/multiple of ("sting11", "string12", "string13", ...)
(TAG2) -> string-column contains any/multiple of ("sting21", "string22", "string23", ...)
(TAG3) -> string-column contains any/multiple of ("sting31", "string32", "string33", ...)
(TAG4) -> string-column contains any/multiple of ("sting41", "string42", "string43", ...)
(TAG-NA) -> string-column contains none of the strings before or from multiple lines (e.g.: "string41" & "string23")
这是一个非常简单的映射,如果需要,可以存储在另一个表中。问题是如何使用标签列单元格中的公式实现映射?(TAG-NA -> 无字符串或多个标签匹配)
我目前开始的是:
=IF(ISERROR(FIND("string11";B1;1));"Not found";"TAG1")
这将检查 TAG1 是否只匹配一个可能的字符串(多个字符串中的一个)。如果我将此公式嵌套 3 次,我就可以捕获当前列表中的所有 TAG1 形式(但可以扩展),并且我必须对每个标签执行此操作。这将导致公式无法维护。
我知道我必须找到一种方法来:
- 立即搜索其中是否有任何“TAG1”字符串
- 嵌套该解决方案。
Excel 中是否有允许这样做的公式函数?或者是否有更好的方法来映射它?(不使用 VBA 等)
答案1
数据如下:
在单元格 C2 中输入以下公式:
=IF(SUM(IFERROR(FIND(IF(ISBLANK($B$14:$E$17),"@@@","^"&$B$14:$E$17&"^"),"^"&SUBSTITUTE(B2," ","^ ^")&"^"),""))=0,$A$18,TEXTJOIN(", ",1,IFERROR(INDEX($A$14:$A$17,N(IF(1,AGGREGATE(15,6,IF(FIND(IF(ISBLANK($B$14:$E$17),"@@@","^"&$B$14:$E$17&"^"),"^"&SUBSTITUTE(B2," ","^ ^")&"^"),ROW($B$14:$B$17)-ROW($B$14)+1),ROW($A$14:$A$17)-ROW($A$14)+1)))),"")))
根据需要调整范围...
解释:
此公式对数组(即对多个值)使用 FIND 函数。如果发现匹配项,它将返回 1 到 4 之间的数字(或您拥有的标签数量减去 NA-TAG),然后使用该数字检索(使用 INDEX 函数)相应标签的列表,并使用 TEXTJOIN 函数将它们连接起来(如果有多个匹配项)。
的原因@@@
是为了替换示例数据中可能返回匹配项的空白单元格(如单元格 E14),因此该单元格将被替换为@@@
,它永远不会返回匹配项(除非您的数据中的某处有“@@@”,我敢打赌你没有)
添加(插入符号)的原因^
:我在公式中动态地将插入符号放在每个单词的前面和后面,这样就不会找到部分匹配。否则,如果您在某处有“tag34”,那么“tag3”将显示为匹配项,因为它实际上是“tag34”的子字符串。但是,^tag34^
不是^tag3^
...的匹配项