☰

在两个不同的单元格中匹配逗号分隔的值，并在第三个单元格中显示非重复的值

tag-icon • tag-icon microsoft-excel worksheet-function
在两个不同的单元格中匹配逗号分隔的值，并在第三个单元格中显示非重复的值

我有 2 列数据。A 列是数据集内所有可能的值，在一个单元格中用逗号分隔。B 列是该数据集内需要删除/排除的值的列表，也在一个单元格中用逗号分隔。我需要在第三列中显示数据集值减去排除项，同样在一个单元格中用逗号分隔。因此本质上 AB=C，但单个单元格中有多个数据点。

例子在图片链接中。

如果你无法打开链接，它基本上看起来像这样：

数据集（A） 除外责任（B） 期望输出（C）
1,2,3,4,5 3,4 1,2,5
蓝色、绿色、粉色、红色 绿色,红色 蓝色,粉色

一些附加信息：数据集和排除项对于几乎每个行项目都是唯一的，我需要评估数千个行项目。此外，每个数据集中的可能值的数量以及排除项的数量因行项目而异。因此，第 1 行可能有 4 个值在集合中，2 个排除项，但第 2 行可能有 50 个值在集合中，18 个排除项。但是，数据集值和排除值将始终按相同的顺序排列。因此，如果我的数据点是数字 1 到 5，则第一个单元格将显示为“1,2,3,4,5”。假设我需要排除 1、3 和 5，第二个单元格将显示为“1,3,5”，而不是“3,5,1”或任何类似的非连续顺序。你们明白了。

我尝试过 Remove Duplicates 函数，并尝试使用 +IF 和 +TEXTJOIN 公式构建某些东西，但失败了。如果可能的话，我宁愿不用数组。我不能/不会将文本列成列，因为每行都有不同数量的数据集值和排除值。而且对于某些行项目，我必须创建的行数会相当高。

我将非常感谢别人的帮助/时间/专业知识！

答案1

如果您拥有具有LET和FILTER和FILTERXML功能的 Office 365，则可以使用：

=TEXTJOIN(",",TRUE,
     LET(data,FILTERXML("<t><s>" & SUBSTITUTE(A2,",","</s><s>") & "</s></t>","//s"),
              excl,FILTERXML("<t><s>" & SUBSTITUTE("," & B2,",","</s><s>") & "</s></t>","//s"),
              FILTER(data,ISNA(MATCH(data,excl,0)))))

在此处输入图片描述

  • FILTERXML可以将每个列表拆分为一个值数组
  • MATCH#N/A如果在排除集中未找到数据集中的值， 则将返回。
    • ISNA(将NA错误转换为TRUE
  • FILTER然后将返回与函数TRUE输出匹配的数据集元素MATCH

答案2

主意

拆分排除项，替换数据，重复。最后，‘加载’答案。

如何

普京 ：

顺序 标签 细胞 公式
0 答案位置 F2 =LEN(B2)-LEN(SUBSTITUTE(B2,",",""))
1 数据集（添加逗号） G2 =A2&","
1 排除字符串（添加逗号） 氢气 =B2&","
2 第一个排除元素 I2 =LEFT(H2,FIND(",",H2,1))
3 第一次被替换 J2 =SUBSTITUTE(G2,I2,"")
4 剩余排除字符串 K2 =SUBSTITUTE(H2,I2,"")
5 回答 E2 =偏移（J2,0，F2*3）
6 期望输出（C） C2 =LEFT(E2,LEN(E2)-1)

选择 I2:K2 并向右拖动。（提示：如果您将标签放在 I1:K1 中，您也可以拖动它。'1st' 将自动更改为 '2nd'++。）

以下是它在我的屏幕上的样子：

例子

为了清晰起见，我按照“工作原理”而不是“位置”的顺序排列公式。如果解决方案有效/可理解/不可理解，请分享。

答案3

只是一个想法您可以使用该TEXTSPLIT函数来查找匹配项。并且可能将其反转以排除它们：

= TEXTJOIN(",";TRUE;
    IFERROR(
        IF(FIND(TEXTSPLIT(A1;",";;TRUE;1);B1)>0;
            TEXTSPLIT(A1;",";;TRUE;1);
            0);
        ""))

相关内容