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.

我需要在同一行(从左到右)的一组列中填充第一个非空条目—类似于SQL中的coalesce()。

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行的每个单元格中放入一个单元格函数，这样我就会得到:

---------------------------------------
|     |  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.

我知道我可以用一连串的IF函数来做这个，但是在我的实际表格中，我有30列可以选择，所以如果有更简单的方法我会很高兴。

4 个解决方案

#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"

这是一个数组公式。输入公式后，按CTRL + Shift + Enter键让Excel将其作为数组公式计算。这将返回给定单元格范围的第一个非空值。对于您的示例，公式在列中以“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:

或者如果你想比较单个的细胞，你可以在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:

然后用Excel表示。在你的例子中，A1中的公式是:

=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:

进一步使用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

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

在Excel中你可以使用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