部分内容对比替换

Aotenjo168

A列是希望的结果。
B列的款号没有特别规律，如果它含有C列的需要对译的关键词，则在A列生成C列对应之D的对译后的替换完成款号。

表格02.rar (6.45 KB, 下载次数: 13)

2021-1-28 20:28 上传

点击文件名下载附件

高

这个公式有点长，公式本身就比较长，加上你的数据里有的是有"-"，有的是有"_"，导致公式又长了一倍。
另外，数组公式三键结束。

hcm19522

~~~~~~~~~~~~~

0126

=IFERROR(INDEX(SUBSTITUTE(B2,{"-","_","-","_","-","_"}&$C$2:$C$9&{"-","-","_","_",";",";"},{"-","_","-","_","-","_"}&$D$2:$D$9&{"-","-","_","_",";",";"}),MAX(ROW($C$2:$C$9)*ISNUMBER(FIND({"-","_","-","_","-","_"}&$C$2:$C$9&{"-","-","_","_",";",";"},B2)))-1,MAX({1,2,3,4,5,6}*ISNUMBER(FIND({"-","_","-","_","-","_"}&$C$2:$C$9&{"-","-","_","_",";",";"},B2)))),B2)

数组函数

cutecpu

=IFERROR(FILTERXML("<a>"&TEXTJOIN(,,"<b>"&SUBSTITUTE(B2,{"-","_","-","_","-","_"}&C$2:C$9&{"-","-","_","_",";",";"},{"-","_","-","_","-","_"}&D$2:D$9&{"-","-","_","_",";",";"})&"</b>")&"</a>","//b[.!='"&B2&"']"),B2)

祝順心，南無阿彌陀佛！

Aotenjo168

非常感谢各位。难题是不能设为几个最佳答案啊。。。

Excel2016好像不支持TEXTJOIN函数。

Aotenjo168

0126 发表于 2021-1-29 13:23
=IFERROR(INDEX(SUBSTITUTE(B2,{"-","_","-","_","-","_"}&$C$2:$C$9&{"-","-","_","_",";",";"},{"-","_", ...

符合要求。谢谢。

Aotenjo168

高 发表于 2021-1-29 08:39
这个公式有点长，公式本身就比较长，加上你的数据里有的是有"-"，有的是有"_"，导致公式又长了一倍。
另外 ...

符合要求。但有个问题，就是颜色对照的C列和D列其实处在另外一个表格的，这里便于直观说明要求而放在一个表里。如果把C列和D列的对比内容放在另外表格的C列和D列，则代码结果好像有问题。

Aotenjo168

cutecpu 发表于 2021-1-29 14:30
=IFERROR(FILTERXML(""&TEXTJOIN(,,""&SUBSTITUTE(B2,{"-","_","-","_","-","_"}&C$2:C$9&{"-","-","_","_" ...

阿弥陀佛~我怎么着都要去装个office2019。

高

Aotenjo168 发表于 2021-1-29 20:26
符合要求。但有个问题，就是颜色对照的C列和D列其实处在另外一个表格的，这里便于直观说明要求而放在一个 ...

是不是三键结束没按好按键啊？