Excel把数据通过工作表名批量分类

显示全部楼层 · 2022-1-22 07:14

问题:
Excel把数据通过工作表名批量分类

哪位大佬解答一下通过前面的表明把一样的一行数据放一起

推荐答案:
代码如下：
Sub classify()
Application.DisplayAlerts = False
Dim x, y, n, j
Dim ar, br, cr(1 To 10000, 1 To 2)
Dim sh As Worksheet
Dim d
Set d = CreateObject(&quot;scripting.dictionary&quot;)
For m = Sheets.Count() To 1 Step -1
If Worksheets(m).Name <> &quot;Sheet1&quot; Then
      Worksheets(m).Delete
End If
Next m
ar = Sheet1.Range(&quot;a2:b&quot; & Range(&quot;a65536&quot;).End(3).Row)
For x = 1 To UBound(ar)
d(ar(x, 1)) = &quot;&quot;
Next
br = d.keys
For n = 0 To UBound(br)
Set sh = Worksheets.Add(after:=Worksheets(Sheets.Count()))
sh.Name = br(n)
sh.Range(&quot;a1:b1&quot;) = Array(&quot;表名&quot;, &quot;数据&quot;)
For y = 1 To UBound(ar)
   If br(n) = ar(y, 1) Then
         j = j + 1
         cr(j, 1) = ar(y, 1)
         cr(j, 2) = ar(y, 2)
      End If
Next y
sh.Range(&quot;a2&quot;).Resize(j, 2) = cr
j = 0: Erase cr
Next n
Application.DisplayAlerts = True
End Sub
亲测有效

其他答案:
函数运行效果如下：

只要在E1单元格输入：表名
      E2单元格输入数组公式：（公式输完按CTRL+SHIFT+ENTER结束公式）
      =IFERROR(INDEX(B$1:B$9,SMALL(IF($A$1:$A$9=$E$1,ROW(A$1:A$9),&quot;&quot;),ROW(A1))),&quot;&quot;)
      下拉公式到你需要的行

[软件] Excel把数据通过工作表名批量分类