请问EXCEL2007如何只设置公式将EXCEL中将满足相同条件的单元格内容合并到指定单元格中(不设辅助列)?如下图例。
2016及以上版本
E2输入 =TEXTJOIN(",",1,IF($A$2:$A$17=D2,$B$2:$B$17,""))
数组公式,先按住CTRL+SHIFT,最后回车,使得编辑栏公式两端出现花括号{ }
公式下拉
如果是2007版,ALT+F11打开VBE编辑器,插入模块,粘贴以下代码(粗体字),E2输入 公式:
=TEXTJOIN(",",1,IF($A$2:$A$100=D2,$B$2:$B$100,""))
CTRL+SHIFT,最后回车,下拉
以下为代码:
Function textjoin(fgf, tf, texts)
Application.Volatile True
On Error GoTo xxx
Dim arr, a As String, pp As String
pp = Join(WorksheetFunction.Transpose(texts), "-")
arr = WorksheetFunction.Transpose(texts)
GoTo yyy
xxx:
arr = WorksheetFunction.Transpose(WorksheetFunction.Transpose(texts))
yyy:
If Abs(-tf) >= 1 Then
For i = 1 To UBound(arr)
If arr(i) = "" Then arr(i) = Application.Rept("^", 1000)
Next
arr = Filter(arr, Application.Rept("^", 1000), False)
End If
textjoin = Join(arr, fgf)
End Function