Excel 中是否有类似于 coalesce 的函数？

Question

我需要在同一行中从左到右填充一个单元格，使其填充为第一个非空的条目，类似于 SQL 中的 coalesce() 函数。

以下是示例表格：

---------------------------------------
|     |  A   |   B   |   C   |    D   |
---------------------------------------
|  1  |      |   x   |   y   |    z   |
---------------------------------------
|  2  |      |       |   y   |        |
---------------------------------------
|  3  |      |       |       |    z   |
---------------------------------------

我想在A行的每个单元格中放置一个单元格函数，以便我能得到：

---------------------------------------
|     |  A   |   B   |   C   |    D   |
---------------------------------------
|  1  |  x   |   x   |   y   |    z   |
---------------------------------------
|  2  |  y   |       |   y   |        |
---------------------------------------
|  3  |  z   |       |       |    z   |
---------------------------------------

我知道可以通过一系列IF函数来实现这个目标，但在我的实际表格中，需要从30列中进行选择，因此如果有更简单的方法就好了。

- Fabian

Answer 1

=INDEX(B2:D2,MATCH(FALSE,ISBLANK(B2:D2),FALSE))

这是一个数组公式。在输入公式后，按下 CTRL + Shift + Enter 使 Excel 将其作为数组公式进行计算。它将返回给定单元格范围中第一个非空值。对于您的示例，该公式将输入到标题为 "a" 的列中。

    A   B   C   D
1   x   x   y   z
2   y       y   
3   z           z

- Howard Renollet

---

非常有用。如果您正在使用它来合并一系列vlookup的结果，请将“isblank”替换为“isna”。 - user2023861

有没有一种方法可以反转这个过程 - 首先偏爱最右边的列？ - Tim Barrass

@TimBarrass 你可能不再需要这个了，但是如果要从右到左尝试这个：=INDEX(B2:D2,1+SUM(IF(B2:D2=B2:D2,1,0))-MATCH(FALSE,INDEX(B2:D2,1,N(IF({1},SUM(IF(B2:D2=B2:D2,1,0))+COLUMN(B2)-COLUMN(B2:D2))))&""="",FALSE)) - Ryan Sparks

对我来说效果很好，谢谢。我只需要使用=INDEX(B2:D2,MATCH(FALSE,B2:D2="",FALSE))，因为我的空白是由前面的函数/公式产生的。 - Renier Wessels

Answer 2

我使用了：

=IF(ISBLANK(A1),B1,A1)

这会测试第一个想要使用的字段是否为空，如果为空，则使用另一个字段。当您有多个字段时，可以使用“嵌套if”。

这将检测您想要使用的第一个字段是否为空，如果为空，则使用另一个字段。当您有多个字段时，可以使用“嵌套if”。

- neil collins

---

它只测试了两个字段，想象一下你在许多字段中搜索第一个非空字段，比如整行。因此，它并不完全适用。 - msciwoj

Answer 3

或者如果你想要比较单个单元格，你可以在VBA中创建一个Coalesce函数：

Public Function Coalesce(ParamArray Fields() As Variant) As Variant

    Dim v As Variant

    For Each v In Fields
        If "" & v <> "" Then
            Coalesce = v
            Exit Function
        End If
    Next
    Coalesce = ""

End Function

然后在Excel中调用它。在您的示例中，A1单元格中的公式应为：

=Coalesce(B1, C1, D1)

- AndyMc

---

我更喜欢这个解决方案，这是比使用数组公式更标准的公式用法。谢谢。 - Matt Roy

@MattRoy并不是很“标准”，因为它强制你将工作簿保存为XLSB或XLSA。 - iDevlop

我对VBA完全不了解，但是当我输入函数后，如何运行它呢？当我尝试使用它时，单元格公式会出现#NAME错误。 - Michael Sheaver

这里也是，出现了“#名称”错误。我将文件保存为.xlsm格式，但没有帮助。我需要把代码放在哪里？ - Florian Kusche

Answer 4

将VBA的方法进一步发展，我改写了它以允许同时选取单个单元格和单元格范围（或仅选取其中之一）：

Public Function Coalesce(ParamArray Cells() As Variant) As Variant

    Dim Cell As Variant
    Dim SubCell As Variant

    For Each Cell In Cells
        If VarType(Cell) > vbArray Then
            For Each SubCell In Cell
                If VarType(SubCell) <> vbEmpty Then
                    Coalesce = SubCell
                    Exit Function
                End If
            Next
        Else
            If VarType(Cell) <> vbEmpty Then
                Coalesce = Cell
                Exit Function
            End If
        End If
    Next
    Coalesce = ""

End Function

现在在Excel中，您可以在A1单元格使用以下任何公式：

=Coalesce(B1, C1, D1)
=Coalesce(B1, C1:D1)
=Coalesce(B1:C1, D1)
=Coalesce(B1:D1)

- AndyMc

Answer 5

如果您知道列之间不会有任何重叠，或者想要重叠，那么这是解决合并的一种非常快捷的方式。下面的公式不适用于您的值和列，而是针对我的模拟表格，因此您需要进行调整以使其相关。

=LEFT(TRIM(CONCATENATE(Q38,R38,S38,T38,U38,V38,W38,X38,Y38)),1)

- Aaron Rainey

Answer 6

在 Excel 中更新的 IFS 函数中，您无需嵌套。 您可以尝试以下操作：

1. 创建一个空白单元格到右侧
然后输入。

=IFS(ISBLANK(A1),B1,ISBLANK(A1),C1,ISBLANK(A1),D1)

选中列并根据需要进行复制粘贴。

- Arron D

Answer 7

如果您只想合并为0，这是非常常见的用例，您可以简单地在一个单一值周围使用SUM()函数。作为一种方便的方式，它将所有空白都视为零，并且非常方便，因为它非常简短。

不像其他答案那样是通用解决方案，但在许多情况下，这是一个有用的快捷方式。

- SilverbackNet

Answer 8

使用MS365中提供的新的Excel数组函数，您可以执行以下操作：

=LET(
RangeForCoalesce, #YourRangeGoesHere,
CHOOSECOLS(TOROW(RangeForCoalesce, 1), 1)
)

Excel在处理动态范围方面变得非常高效。

- ComparableCC

Answer 9

来晚了，借鉴了@AndyMC的答案，您可以使用以下方法来评估vlookup、index(match())等函数，以合并您的公式语句。

Public Function Coalesce(ParamArray Fields() As Variant) As Variant

    Dim v As Variant

    For Each v In Fields
        
        If Not IsError(v) Then
            
            Coalesce = v
            
            Exit Function
        End If
    Next
    Coalesce = CVErr(xlErrNA)

End Function

并将其用作工作表中的以下内容：=Coalesce(INDEX(SHEET1!$A:$AF,MATCH(Main!$Q36,SHEET1!$I:$I,0),MATCH(Main!D$34,SHEET1!$1:$1,0)),INDEX(SHEET2!A:CR,MATCH(Main!$Q36,SHEET2!$M:$M,0),MATCH("SOMETHING",SHEET2!$1:$1,0)))

对于第一个不返回#N/A的语句，它将返回实际匹配的值。

- Maeaex1

Answer 10

根据您要检查的单元格数量，您可以链接多个ISBLANK检查。

例如，在检查A、B、C列时：

=IF(ISBLANK(A1),IF(ISBLANK(B1),C1,B1),A1)

对于 A、B、C 和 D 列：

=IF(ISBLANK(A1),IF(ISBLANK(B1),IF(ISBLANK(C1),D1,C1),B1),A1)

......等等。

- WEFX