Есть ли подобная функция в Excel?

Мне нужно заполнить ячейку первой непустой записью в наборе столбцов (слева направо) в той же строке, которая похожа на coalesce() в SQL.

В следующем примере

---------------------------------------
|     |  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 столбцов для выбора, поэтому я был бы счастлив, если бы был более простой способ.

Ответ 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

Ответ 2

Я использовал:

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

Это проверяет, является ли первое поле, которое вы хотите использовать, пустым, а затем используйте другое. Вы можете использовать "вложенный, если", когда у вас есть несколько полей.

Ответ 3

Или, если вы хотите сравнить отдельные ячейки, вы можете создать функцию Coalesce в 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

А затем вызовите его в Excel. В вашем примере формула в A1 будет:

=Coalesce(B1, C1, D1)

Ответ 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)

Ответ 5

Внутри массива введите переменные, которые не разрешены.

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

Ответ 6

Если вы знаете, что не будет никакого перекрытия между столбцами, или хотите перекрытие, то это довольно быстрый способ решения проблемы объединения. Приведенная ниже формула относится не к вашим значениям и столбцам, а скорее к моему макету, поэтому вам необходимо изменить его, чтобы он был актуален.

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