SQL Excel 中是否有类似合并的函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20103881/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a coalesce-like function in Excel?
提问by Fabian
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 列可供选择，所以如果有更简单的方法我会很高兴。
回答by Howard Renollet
=INDEX(B2:D2,MATCH(FALSE,ISBLANK(B2:D2),FALSE))
This is an Array Formula. After entering the formula, press CTRL+ Shift+ Enterto 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
回答by neil collins
I used:
我用了：
=IF(ISBLANK(A1),B1,A1)
This tests the if the first field you want to use is blank then use the other. You can use a "nested if" when you have multiple fields.
这将测试您要使用的第一个字段是否为空白，然后使用另一个。当您有多个字段时，您可以使用“嵌套 if”。
回答by AndyMc
Or if you want to compare individual cells, you can create a Coalesce function in VBA:
或者，如果要比较单个单元格，可以在 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
And then call it in Excel. In your example the formula in A1 would be:
然后在 Excel 中调用它。在您的示例中，A1 中的公式为：
=Coalesce(B1, C1, D1)
回答by AndyMc
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)
回答by Aaron Rainey
If you know there will not be any overlap across columns, or want the overlap, then this is a pretty fast way to solve for a coalesce. The below formula does not apply to your values and columns, but rather to my mock-up so you will need to adjust to make it relevant.
如果您知道列之间不会有任何重叠，或者想要重叠，那么这是解决合并问题的一种非常快速的方法。以下公式不适用于您的值和列，而是适用于我的模型，因此您需要进行调整以使其相关。
=LEFT(TRIM(CONCATENATE(Q38,R38,S38,T38,U38,V38,W38,X38,Y38)),1)
回答by Felipe
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
