从可变数量的单元格连接值(Microsoft Excel)
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的较早菜单界面找到此技巧的版本: