Is there a coalesce-like function in Excel?

问题:

I need to fill a cell with the first non-empty entry in a set of columns (from left to right) in the same row - similar to coalesce() in SQL.

In the following example sheet

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

I want to put a cell function in each cell of row A such that I will get:

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

I know I could do this with a cascade of IF functions, but in my real sheet, I have 30 columns to select from, so I would be happy if there were a simpler way.

回答1:

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

This is an Array Formula. After entering the formula, press CTRL + Shift + Enter to have Excel evaluate it as an Array Formula. This returns the first nonblank value of the given range of cells. For your example, the formula is entered in the column with the header "a"

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

回答2:

Or if you want to compare individual cells, you can create a Coalesce function in VBA:

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

And then call it in Excel. In your example the formula in A1 would be:

=Coalesce(B1, C1, D1)

回答3:

Taking the VBA approach a step further, I've re-written it to allow a combination of both (or either) individual cells and cell ranges:

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

So now in Excel you could use any of the following formulas in A1:

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

回答4:

Inside the array enter the variables that are not allowed.

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

    Dim v As Variant

    For Each v In Fields
        If IsError(Application.Match(v, Array("", " ", 0), False)) Then
            Coalesce = v
            Exit Function
        End If
    Next
    Coalesce = ""

End Function