根据 A 列的通用值合并 B 列的值

本文介绍了根据 A 列的通用值合并 B 列的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在 Excel 中，如何根据 Column A 上的通用值合并 Column B 的值?

In Excel, how I can merge values of Column B based on common values on Column A?

基本上我需要的是这样的东西

Basically what I need is some thing like this

推荐答案

您可以使用此 UDF:

You can use this UDF:

Function TEXTJOINIFS(rng As Range, delim As String, ParamArray arr() As Variant) Dim rngarr As Variant rngarr = Intersect(rng, rng.Parent.UsedRange).Value Dim condArr() As Boolean ReDim condArr(1 To Intersect(rng, rng.Parent.UsedRange).Rows.Count) As Boolean Dim i As Long For i = LBound(arr) To UBound(arr) Step 2 Dim colArr() As Variant colArr = Intersect(arr(i), arr(i).Parent.UsedRange).Value Dim j As Long For j = LBound(colArr, 1) To UBound(colArr, 1) If Not condArr(j) Then Dim charind As Long charind = Application.Max(InStr(arr(i + 1), ">"), InStr(arr(i + 1), "<"), InStr(arr(i + 1), "=")) Dim opprnd As String If charind = 0 Then opprnd = "=" Else opprnd = Left(arr(i + 1), charind) End If Dim t As String t = """" & colArr(j, 1) & """" & opprnd & """" & Mid(arr(i + 1), charind + 1) & """" If Not Application.Evaluate(t) Then condArr(j) = True End If Next j Next i For i = LBound(rngarr, 1) To UBound(rngarr, 1) If Not condArr(i) Then TEXTJOINIFS = TEXTJOINIFS & rngarr(i, 1) & delim End If Next i TEXTJOINIFS = Left(TEXTJOINIFS, Len(TEXTJOINIFS) - Len(delim)) End Function

你可以这样称呼它:

=IF(MATCH(A1,A:A,0)=ROW(A1),TEXTJOINIFS(B:B,", ",A:A,A1),"")

现在不管数据是否排序，它只会将输出放在列 A 中的值第一次出现的列 C 中.

Now it does not matter if the data is sorted or not it will only put the output in column C where the value in Column A first appears.