如何用空白替换 excel 中 FILTER 函数输出中的重复值，在输出中留下空白-解网

问：

我在 excel 中有一个人员列表及其地址。我有一个下拉列表，允许我选择一条道路，这导致我的 FILTER 函数返回该道路上的所有地址。每个人都有一个与其姓名关联的地址，因此对于具有多个居民的属性，输出包含重复项。我想用空白替换重复项，以便地址出现在第一个居民的姓名旁边，而不是第二个居民的姓名旁边。详情请见下文：

这个公式：

=BYROW(FILTER(H:M, N:N=P1),LAMBDA(r,TEXTJOIN(", ",TRUE,r)))

返回类似于以下内容的结果：

1 Fake Road，库尔斯维尔， AB12 3CD 1 Fake Road，库尔斯维尔， AB12 3CD 1 Fake Road，库尔斯维尔， AB12 3CD

1 Fake Road，库尔斯维尔， AB12 3CD
3 Fake Road，库尔斯维尔， AB12 3CD
7 Fake Road，库尔斯维尔， AB12 3CD 2 Fake Road，库尔斯维尔， AB12 3CD

2 Fake Road，库尔斯维尔， AB12 3CD
4 Fake Road，库尔斯维尔， AB12 3CD
6 Fake Road， Coolsville， AB12 3CD 8 Fake Road， Coolsville， AB12 3CD

8 Fake Road， Coolsville， AB12 3CD
10 Fake Road， Coolsville， AB12 3CD

列 H：M 包含地址的各个部分：

H： 1 Fake Road
I： Coolsville
J：邮政编码

等。

N 列具有与其余地址信息相邻的道路名称列表。P1 是一个下拉列表，其中包含 N 列中的唯一道路名称。

我怎样才能改变我的公式，只返回每个地址的第一个实例，同时在以前的重复项所在的地方留下空行，例如：

1 Fake Road，库尔斯维尔， AB12 3CD 空白行空白行空白行 3 Fake Road，库尔斯维尔， AB12 3CD
7 Fake Road，库尔斯维尔， AB12 3CD
2 Fake Road，库尔斯维尔， AB12 3CD 空白行 4 Fake Road，库尔斯维尔， AB12 3CD
6 Fake Road，库尔斯维尔， AB12 3CD
8 Fake Road，库尔斯维尔， AB12 3CD

空白行

10 Fake Road，库尔斯维尔， AB12 3光盘

我尝试了 ChatGPT 的各种建议，包括：

=LET(output, BYROW(FILTER(H:M, N:N=P1, LAMBDA(r, 
  TEXTJOIN(", ", TRUE, r))),n,SEQUENCE(ROWS(output)),
  IF(COUNTIF(INDEX(output,,1):INDEX(output,n,1), 
  INDEX(output,n,1))>1, "", INDEX(output,n,1)))

这有点超出了我的理解，但似乎返回了一个！计算 IF（COUNTIF 部分时出现 VALUE 错误。

我上面的原始公式在 R 列中，所以我使用这个单独的公式来创建我想要的传真：

=IF(COUNTIF($R$2:R2, R2) > 1, "", R2)

这在 S 列中，基本上完全返回我想要的内容：

1 Fake Road，库尔斯维尔， AB12 3CD 空白行空白行空白行 3 Fake Road，库尔斯维尔， AB12 3CD
7 Fake Road，库尔斯维尔， AB12 3CD
2 Fake Road，库尔斯维尔， AB12 3CD 空白行 4 Fake Road，库尔斯维尔， AB12 3CD
6 Fake Road，库尔斯维尔， AB12 3CD
8 Fake Road，库尔斯维尔， AB12 3CD

空白行

10 Fake Road，库尔斯维尔， AB12 3光盘

但是，我必须将此公式复制到 FILTER 数组的输出填充的尽可能多的行中，这是手动的，并在未占用的行中保留“0”。

编辑：由Mayukh Bhattacharya解决

您的第二个解决方案解决了它，非常感谢！一个出色的解决方案，完全解决了我的问题。至于为什么我需要空格，这可能是我本可以解决的问题，但我正在将一些数据整理成一种格式，然后导出。我只需要将每个地址列出一次作为标题，下面将是居民的姓名列表。即

1 Fake Street， Coolsville， AB12 3DC
约翰·史密斯卡罗尔·史密斯琳达·史密斯

2 Fake Street， Coolsville， AB12 3DC
彼得·汤姆森珍妮·汤姆森

3 Fake Street， Coolsville， AB12 3DC
等

这让我可以这样做，这意味着我不需要在其他地方做更多的格式化。

excel 公式数组公式

只是为了将数据整理成更易于导出的布局。我想根据所选道路生成格式化的 pdf，其中每个地址列出一次，并列出相邻的居民列表。我现在有一个地址列表，下面有正确数量的空白行。在下一列中，我有姓名列表，因此它显示为“A 列第 1 行 - 地址”、“B 列第 1 行至第 3 行 - 居民姓名”。然后从 A 列第 4 行开始 - 下一个地址，B 列第 4 行 - 居民姓名等。如果这有意义？可能是一种更聪明的方法，但这对我有用！

答：

0赞 Mayukh Bhattacharya 11/18/2023 #1

从给定的所需输出中，您似乎正在尝试完成以下任务，如果是这样，那么您可以尝试使用以下公式：

• 细胞中使用的配方C1

=LET(
     resultfrombyrowfilter, A1#,
     BYROW(resultfrombyrowfilter,LAMBDA(x,
     LET(y,INDEX(resultfrombyrowfilter,1),IF(COUNTIF(y:x,x)>1,"",x)))))

我试图重现您的数据示例，并提出了这个，如果您尝试使用一个公式一次返回所有公式，您也可以尝试一下，尽管正如 @P.b Sir 在上面的评论中所述，为什么您真的需要这些空白，如果您只需要唯一值，您只能使用该函数。UNIQUE()

• 细胞中使用的配方H1

=LET(
     joiner, BYROW(FILTER(A1:C26,D1:D26=F1), LAMBDA(x, TEXTJOIN(", ",1,x))),
     IF(SCAN(0,joiner=DROP(VSTACK("",joiner),-1),LAMBDA(a,b,IF(b,a+1,1)))=1,joiner,""))

编辑：

我只需要将每个地址列为一个标题，标题下方将是居民姓名列表。即

1 Fake Street，库尔斯维尔， AB12 3DC

约翰·史密斯

卡罗尔·史密斯

琳达·史密斯

2 Fake Street，库尔斯维尔， AB12 3DC

彼得·汤姆森

珍妮·汤姆森

3 Fake Street，库尔斯维尔， AB12 3DC

等等等等

这让我可以这样做，这意味着它更多的格式化，我不需要在其他地方做。

• 细胞中使用的配方H3

=LET(
     α,C2:C19&", "&D2:D19&", "&E2:E19,
     φ, MAP(UNIQUE(α),LAMBDA(z,z&"/"&TEXTJOIN("/",,FILTER(A2:A19&" "&B2:B19,α=z))&"*")),
     TEXTSPLIT(TEXTJOIN("/",,REPT(φ,TAKE(UNIQUE(C2:F19),,-1)=H1)),,{"/","*"}))

强制性警告：根据 MSFT 文档，在较大的数组上使用函数，如果生成的字符串超过字符数（单元格限制），则返回错误。请注意此注意事项。TEXTJOIN()32767TEXTJOIN()#VALUE!

您的第二个解决方案解决了它，非常感谢！一个出色的解决方案，可以完全解决我的问题。至于为什么我需要空格，这可能是我本可以解决的问题，但我正在将一些数据整理成一种格式，然后导出。我只需要将每个地址列为一个标题，下面将是居民姓名列表。

0赞 P.b 11/18/2023

然后，不要添加空格，而是将其包装在 unique：？=UNIQUE(BYROW(FILTER(H:M, N:N=P1),LAMBDA(r,TEXTJOIN(", ",TRUE,r))))

0赞 Michael Howard-Sorrell 11/18/2023

如果我这样做了，就不清楚居民属于哪个地址。例如，如果我使用唯一地址，则地址之间不会留下空格，因此在 A 列中，它可能会列出 20 个唯一地址，填充第 1-20 行。在 B 列中，居民列表可能是 37 人，填充第 1-37 行。显然，不是 1 个人住在地址 1-19，然后是剩下的 18 个人住在地址 20 - 我需要空间，以便我一目了然地看到一个地址和下一个地址的居民开始的地方，并排在两列中。

0赞 P.b 11/18/2023

然后我问你，你到底想实现什么最终目标。

0赞 DjC 11/18/2023 #2

看起来你已经对原始问题有了很好的答案;但是，您对 OP 所做的编辑表示完全不同的所需输出，街道地址显示为标题，后跟居民姓名，并在下一个地址之前加上空白行。要使用单细胞数组公式获得这些结果，请尝试如下操作：

在上面所示的示例中，单元格中使用的公式为：I3

=LET(arr, FILTER(A2:E19, F2:F19=I1),
n, BYROW(TAKE(arr,, 2), LAMBDA(r, TEXTJOIN(" ",, r))),
a, BYROW(DROP(arr,, 2), LAMBDA(r, TEXTJOIN(", ",, r))),
u, UNIQUE(a),
DROP(SORT(VSTACK(HSTACK(u, u), HSTACK(a, n), IFNA(HSTACK(u, ""), ""))),, 1))

此示例中使用的基本逻辑是创建一个“排序字段”，该字段由筛选的地址列表以及堆叠在筛选结果上方和下方的唯一地址列表组成。堆叠数组只是按排序字段排序，然后从最终结果中删除该排序字段。干杯！

上一个：VBA 连接单元格范围，用“OR”的分隔符分隔，并在到达空白单元格时停止？

下一个：报告文件夹和子文件夹权限以及所有文件

如何用空白替换 excel 中 FILTER 函数输出中的重复值，在输出中留下空白

How can I replace duplicate values in the output of a FILTER function in excel with blanks, leaving gaps in the output

评论

编辑：

评论