我如何修改这个 Excel 公式?

我如何修改这个 Excel 公式?

我有这个问题:我正在使用 Vlookup 从多个单元格中检索数据,例如: Vlookup (A1, A3:K20, 2, 0)&","& Vlookup (A1, A3:K20, 3, 0)&","&Vlookup (A1, A3:K20, 4, 0)&","&等等。

因此返回值类似于:John、Mery、Sam。

然而有时列中的值不存在,返回单元格值如下所示:John,,Sam。

如果单元格为空,如何告诉 Excel 不显示逗号?(或者如果单元格为空则跳过该单元格)

答案1

由于您使用的是 Excel 2016,因此您可以使用 TEXTJOIN 内置函数。将每个 VLOOKUP 结果作为输入参数传递给它。指定 , 作为分隔符,并将 Ignore Empty 设置为 TRUE。

在此示例中,原始查找表位于单元格 D3:G8 中。

I3:J8 是输出表。I3:I8 是将与原始查找表的第一列匹配的查找值列。假设查找值存在于表中,并且只有后续列中的数据可以为空。

现在在 J3 中输入以下公式并根据需要将其向下拖动。

=TEXTJOIN(",",TRUE,VLOOKUP(I3,$D$3:$G$8,2,TRUE),VLOOKUP(I3,$D$3:$G$8,3,TRUE),VLOOKUP(I3,$D$3:$G$8,4,TRUE))

请参阅下面的屏幕截图。

在此处输入图片描述

答案2

您没有表明您使用的是早期版本的 Excel,因此patkim的答案可能对您很有帮助。但对于使用早期版本的用户,可以采取一些措施。

我想到了一些简单的方法。

如果您希望以最简单的方式,您可以按原样“包装”整个公式,在函数中SUBSTITUTE()告诉它用空字符(“”)替换任何出现的“空格逗号”或用单个“空格”替换“空格逗号空格”。

因此,就像这样:

=SUBSTITUTE( VLOOKUP(A1, A3:K20, 2, 0)&" ," & VLOOKUP(A1, A3:K20, 3, 0)&" ," & VLOOKUP(A1, A3:K20, 4, 0), " ,", ",")

(公式、图片和问题文本之间存在矛盾的信息。两个部分似乎只在部分之间放置了一个逗号,而另一个部分放置了一个“空格逗号”……我决定图片控制想法并修改了上面的内容以适应它。但是,如果在连接中仅使用逗号,则两个或多个缺失数据会导致连续出现多个逗号,并且只会处理成对的逗号,但即使这样它也不是“递归的”,所以仍然可以有多个逗号。在连接中使用“空格逗号”可以解决这个问题。)

更复杂,但易于做到,并且在一年后查看公式时可能更容易理解,那就是将每个部分包装在一个IF()函数中,测试其输出VLOOKUP()是否为空(“”),如果是,则返回任何内容(“”),如果不是,则返回查找结果加上连接的“逗号空格”(在最后一种情况下,仅返回查找结果)。

因此,就像这样:

=IF(VLOOKUP(A1, A3:K20, 2, 0) = "", "", VLOOKUP(A1, A3:K20, 2, 0)&",") & IF(VLOOKUP(A1, A3:K20, 3, 0) = "", "", VLOOKUP(A1, A3:K20, 3, 0)&",") & VLOOKUP(A1, A3:K20, 4, 0)

相关内容