我有一个电子表格:
Company 1 | ID1
Company 1 | ID2
Company 1 | ID3
Company 2 | ID4
Company 2 | ID5
Company 2 | ID6
Company 3 | ID7
Company 3 | ID8
Company 3 | ID9
我需要将每家公司合并到一行,并将其所有 ID 合并到右侧的列中。每家公司的 ID 数量可以不同。
最终结果如下:
Company 1 | ID1 | ID2 | ID3
Company 2 | ID4 | ID5 | ID6
Company 3 | ID7 | ID8 | ID9
我该如何实现这一点?最好使用 VBA。
答案1
此单一公式解决方案假设每个公司都有相同的行数:
输入以下公式D2
,然后按 ctrl-enter/copy-paste/fill-down&right 进入表格的其余部分:
=""&INDEX($A:$B,ROW($D$2)+3*INT(ROW()-ROW($D$2))+MAX(0,COLUMN()-COLUMN($D$2)-1),2-(COLUMN()=COLUMN($D$2)))
请注意,此公式适用于每个公司的任意(相同)行数,并且会在添加/删除行或列时进行正确调整。
但是,如果将公式所在的表格复制/剪切并粘贴到其他地方,公式将会中断。$D$2
需要更改 s 以匹配新表格位置的左上角数据单元格,才能使其再次正常工作。
如果每个公司的行数可变,则所需的单个公式是:
=IFERROR(IF(COLUMN()=COLUMN($D$2),""&INDEX($A:$A,MATCH(D1,$A:$A,0)+COUNTIF($A:$A,D1)),INDEX(INDEX($B:$B,MATCH($D2,$A:$A,0)):INDEX($B:$B,MATCH($D2,$A:$A,0)+COUNTIF($A:$A,$D2)-1),COLUMN()-COLUMN($D$2))),"")
答案2
以下是 VBA 替代方案:
'============================================================================================
' Module : <any standard module>
' Version : 0.1.0
' Part : 1 of 1
' References : N/A
' Source : https://superuser.com/a/1335738/763880
'============================================================================================
Option Explicit
Public Sub UnPivot()
Dim ¡ As Long
Const s_SourceColumns As String = "A:B"
Const s_TargetColumn As String = "D"
Const b_HasHeader As Boolean = True
Dim lngSourceRows As Long
lngSourceRows = ActiveSheet.Columns(s_SourceColumns).Cells(Rows.Count, 1).End(xlUp).Row + CLng(b_HasHeader)
Dim varSource As Variant
varSource = ActiveSheet.Columns(s_SourceColumns).Resize(RowSize:=lngSourceRows + 1).Offset(RowOffset:=-CLng(b_HasHeader)).Value2
Dim idxNewCompany As Long: idxNewCompany = LBound(varSource, 1)
Dim strNewCompany As String: strNewCompany = varSource(idxNewCompany, 1)
Dim varUnPivotedData() As Variant
ReDim varUnPivotedData(1 To lngSourceRows)
varUnPivotedData(1) = strNewCompany
Dim celNextTargetStart As Range
Set celNextTargetStart = ActiveSheet.Columns(s_TargetColumn).Resize(RowSize:=1).Offset(RowOffset:=-CLng(b_HasHeader))
Application.ScreenUpdating = False
For ¡ = LBound(varSource, 1) To UBound(varSource, 1) - 1
varUnPivotedData(¡ - idxNewCompany + 2) = varSource(¡, 2)
If varSource(¡ + 1, 1) <> strNewCompany Then
ReDim Preserve varUnPivotedData(1 To ¡ - idxNewCompany + 2)
celNextTargetStart.Resize(ColumnSize:=UBound(varUnPivotedData)).Value2 = varUnPivotedData
Set celNextTargetStart = celNextTargetStart.Offset(RowOffset:=1)
idxNewCompany = ¡ + 1
strNewCompany = varSource(idxNewCompany, 1)
ReDim varUnPivotedData(1 To lngSourceRows)
varUnPivotedData(1) = strNewCompany
End If
Next ¡
Application.ScreenUpdating = True
End Sub
只需更改代码顶部的常量即可适合您的情况。
答案3
怎么运行的:
- 选择 A2:A11 并命名范围公司。
- 选择 B2:B11 并命名范围ID。
在单元格 C2 中写入此数组公式生成唯一的公司列表:
{=INDEX($A$2:$A$11, MATCH(0, COUNTIF($C$1:C1, $A$2:$A$11), 0))}
注意: 用以下公式完成上述书写Ctrl+Shift+Enter并填写。
在单元格 D2 中写入此数组公式,最后用Ctrl+Shift+Enter和先向右填充,然后向下填充。
{=IFERROR(INDEX(IDs,SMALL(IF(Company=$C2,ROW(IDs)-MIN(ROW(IDs))+1),COLUMNS($C$2:C2))),"")}
笔记,
如果每个公司的 ID 数量不定,这个公式就能顺利运行。
您将获得如屏幕截图所示的列中的 ID 列表。