☰

公式仅从水平范围中提取唯一值放入单个单元格中，以逗号分隔

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-365
公式仅从水平范围中提取唯一值放入单个单元格中，以逗号分隔

我有一个电子表格，要求用户在多行中为其分配的列中放置一个值。

管理区域使用了一系列其他列，其中有一列应该仅列出用户在相应行中输入的唯一值。

示例数据

在此处输入图片描述

因此，以上面的截图为例：

  • 用户被分配一列来填写（例如 K 列），他们需要在其中输入若干行中的值，例如 I、R、C 等。
  • 管理区域需要在另一列中总结这些内容，通过指示该行的所有唯一值并用逗号分隔（例如，在单元格 I9 中，他们将输入 I、R、C）。

我被要求以某种方式自动化这一过程，以便管理区域不必手动识别每行的唯一值并输入它们。目前我想到最好的方法是以下方法：

  • 在后面的列中（假设单元格 AA9），我使用 UNIQUE 函数，如下所示：=UNIQUE(K9:O9,1)。然后，这会用范围内的每个唯一值填充以下单元格，仅填充一次。也就是说，AA9 将有 I，AB9 有 R，AC9 有 C，依此类推。
  • 在单元格 I9 中我使用如下公式：=K9&", "&L9&", "&M9&", "&N9&", "&O9

这确实实现了预期的结果（尽管它确实对任何空白单元格显示零（0）值，这很烦人），但在我看来，必须有一种更优雅的方法来实现这一点。

问题

有没有办法在单元格 I9 中使用一个公式来完成我正在使用的两个公式的工作（即，它同时完成当前 AA9 和 I9 中的工作），返回 I9 中所有用逗号分隔的唯一值，而无需在 AA、AB 等处使用辅助列。

答案1

要处理单个单元格中多个逗号分隔条目的问题，请尝试：

=TEXTJOIN(",",TRUE,FILTERXML("<t><s>" &SUBSTITUTE(TEXTJOIN(",",TRUE,K27:N27),",","</s><s>")&"</s></t>","//s[not(preceding-sibling::* = .)]"))

算法

  • 使用逗号分隔符连接单元格
  • XML为每个逗号分隔的子字符串创建一个
  • 使用xPath仅返回唯一节点的
  • FILTERXML用逗号分隔符连接函数的结果

如果你有 Mac 但没有该FILTERXML功能，还有其他解决方案

在此处输入图片描述

如果有必要，你可以对结果进行排序

编辑 MAC Office 365 解决方案

=TEXTJOIN(",",TRUE,UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,K27:N27),",",REPT(" ",99)),IF(SEQUENCE(99,,0)=0,1,SEQUENCE(99,,0)*99),99))))

答案2

使用TEXTJOIN功能=UNIQUE(K9:O9;1)

=TEXTJOIN(",";TRUE;UNIQUE(K9:O9;1))

由于您使用的区域设置与我的不同，因此 请将其更改;为。,在此处输入图片描述

相关内容