Pam有两列数据。在A列中有简单的标识符,例如A,B,C等。在B列中有一系列整数值。

她可以按标识符对数据进行排序,其次可以按整数排序。现在她希望在C列中有一个公式,该公式可以将特定标识符的所有整数值连接起来。因此,如果A1:A4全部包含标识符A,则在单元格C1中,她希望将B1:B4中的所有值连接起来并用逗号分开,例如“ 11、17、19、25”。由于每个标识符的行数可以不同,因此Pam不确定如何进行串联。

完成此操作的最简单方法是使用宏,可以将其创建为用户定义的函数。这是一个示例:

Function CatSame(c As Range) As String     Application.Volatile     sTemp = ""

iCurCol = c.Column     If iCurCol = 3 Then         If c.Row = 1 Then             sLast = ""

Else             sLast = c.Offset(-1, -2)

End If         If c.Offset(0, -2) <> sLast Then             J = 0             Do                 sTemp = sTemp & ", " & c.Offset(J, -1)

J = J + 1             Loop While c.Offset(J, -2) = c.Offset(J - 1, -2)

sTemp = Right(sTemp, Len(sTemp) - 2)

End If     End If     CatSame = sTemp End Function

此函数基本上采用传递给它的值(单元格引用),并验证该单元格引用是否适用于C列。如果是,则它将基于A列中的值开始连接B列中的值。仅如果A列中的值与其上方的行中的值不同,则返回串联的字符串。

假设您的标识符在A列中,并且要连接的值在B列中,则可以在C列中放置以下内容:

=CatSame(C1)

将其复制到C列中的所需位置,最终得到Pam想要的结果。

一种更通用的功能是某种类似于VLOOKUP的功能,但会带回一个与您要查找的内容匹配的值的串联列表。考虑以下函数:

Function VLookupAll(vValue, rngAll As Range, _   iCol As Integer, Optional sSep As String = ", ")

Dim rCell As Range     Dim rng As Range     On Error GoTo ErrHandler

Application.Volatile     Set rng = Intersect(rngAll, rngAll.Columns(1))

For Each rCell In rng         If rCell.Value = vValue Then _           VLookupAll = VLookupAll & sSep & _           rCell.Offset(0, iCol).Value     Next rCell

If VLookupAll = "" Then         VLookupAll = CVErr(xlErrNA)

Else         VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))

End If ErrHandler:

If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)

End Function

此函数最多包含四个参数。第一个是您要在查找中匹配的值。在Pam的实例中,这将是您想要的标识符,例如A,B或C。第二个参数是要在其中查找匹配项的单元格范围(在本例中为A列)。

第三个参数是偏移量(相对于第二个参数的范围)

代表您想要连接的值。您可以通过以下方式使用该函数:

=VLookupAll("B",A1:A99,1)

如果要在值之间指定其他定界符,则可以使用可选的第四个参数来完成。例如,以下代码返回一个字符串,其中用破折号分隔每个值:

=VLookupAll("B",A1:A99,1,"-")

到目前为止,解决方案都集中在使用宏上。原因很简单:没有一种基于公式的解决方案可以满足Pam的需要。使用嵌套的IF语句评估A列中的内容不能很好地工作,因为您对IF语句的嵌套深度有限制。

如果您不介意将串联的值放在A列中标识符的最后一个实例中,则可以使用公式和中间结果。首先将此公式放在单元格C1中:

=B1

此公式应进入单元格C2:

=IF(A2=A1,C1 & ", " & B2, B2)

将此公式向下复制到尽可能多的行。最终结果是C列中的连接值序列越来越长,每次运行中最长的与A列中的最后一个顺序标识符位于同一行。然后,您可以将以下内容放在C列的所有适用单元格中D:

=IF(LEN(C2)>LEN(C1),"",C1)

此公式仅显示列C中最长的字符串,这是Pam所需的开始。

注意:

如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(9199)适用于Microsoft Excel 2007、2010、2013、2016、2019和Office 365中的Excel。您可以在此处为Excel的较早菜单界面找到此技巧的版本: