1:新建個(gè)sheet(如圖ALL,里面數(shù)據(jù)是空的)
2右擊查看代碼
復(fù)制代碼進(jìn)去,運(yùn)行(F5)
Sub hb()
Dim bt, i, r, c, n, first As Long
bt = 1 '表頭行數(shù)拯勉，多行改為對(duì)應(yīng)數(shù)值
Cells.Clear
For i = 1 To Sheets.Count
? ? If Sheets(i).Name <> ActiveSheet.Name Then
? ? ? ? If first = 0 Then
? ? ? ? ? ? c = Sheets(i).Cells(1, Columns.Count).End(xlToLeft).Column
? ? ? ? ? ? Sheets(i).Range("A1").Resize(bt, c).Copy Range("A1")
? ? ? ? ? ? n = bt + 1: first = 1
? ? ? ? End If
? ? ? ? r = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
? ? ? ? Sheets(i).Range("A" & bt + 1).Resize(r - 1, c).Copy Range("A" & n)
? ? ? ? n = n + r - bt
? ? End If
Next
End Sub
運(yùn)行后,數(shù)據(jù)進(jìn)來(lái)如圖
復(fù)制A列到E, 選擇E列,數(shù)據(jù)->刪除重復(fù)項(xiàng)
選擇F2, =SUMIF(A:A,E2,B:B)
下拉就得到了:
