Excel如何將不同工作表的數據匯總到一起?
如下圖所示,3張工作表中的人數是不同的,人員也不是不全相同的人,現在要把所有人都列在一個表裡並且月份也合到一起顯示。
用VBA實現,親測可用。有解決問題的,記得點贊哦~
【321收藏,只有42贊什麼情況???】
方法:
1、新建一個Excel工作薄,工作簿名稱任你定義。
2、打開此工作薄,將其中一個Sheet重命名為"匯總"。
3、在其下任一個工作表標籤上點擊右鍵,選擇「查看代碼」。
4、在打開的VBA編輯窗口中粘貼以下代碼:
Sub 合併工作簿內的多個Sheet到同一個Sheet並執行匯總()
Dim i, j As Integer i是數據源表的最後一行,j是目標表(數據表)的最後一行
Dim sht As Worksheet
Application.ScreenUpdating = False 關閉屏幕刷新
先要刪除所有數據
Sheets("匯總").Range("a2:z65536").ClearContents
匯總表增加表頭
Sheets("匯總").Range("a1") = "摘要"
Sheets("匯總").Range("b1") = "人名"
Sheets("匯總").Range("c1") = "業績"
複製數據
For Each sht In Sheets
If sht.Name &<&> "匯總" Then
i = sht.Range("a65536").End(xlUp).Row
j = Sheets("匯總").Range("b65536").End(xlUp).Row
sht.Range("a2:z" i).Copy Sheets("匯總").Range("b" j + 1)
複製摘要
sht.Range("b1").Copy Sheets("匯總").Range("a" (j + 1) ": a" (j + i - 1))
End If
Next
對匯總表進行透視
Sheets("匯總").Range("A1:C" j + i - 1).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="匯總!R1C1:R" j + i - 1 "C3", Version:=6).CreatePivotTable TableDestination:="匯總!R1C6", TableName:="數據透視表6", DefaultVersion:=6
Sheets("匯總").Cells(1, 6).Select
With ActiveSheet.PivotTables("數據透視表6")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("數據透視表6").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("數據透視表6").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("數據透視表6").PivotFields("摘要")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("數據透視表6").PivotFields("人名")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("數據透視表6").AddDataField ActiveSheet.PivotTables("數據透視表6" _
).PivotFields("業績"), "求和項:業績", xlSum
With ActiveSheet.PivotTables("數據透視表6")
.ColumnGrand = False
.RowGrand = False
End With
透視表粘貼為數值型
ActiveSheet.PivotTables("數據透視表6").PivotSelect "", xlDataAndLabel, True
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
MsgBox "執行完畢!"
Application.ScreenUpdating = True 打開屏幕刷新
End Sub
5、關閉VBA編輯窗口。
6、在excel中,工具---宏---宏,選「合併工作簿的各個sheet到同一個sheet」,然後「執行」。
7、在打開的對話窗口中,選擇你要合併的幾個工作薄文件。
8、等待。。。。ok!
親測可用,效果如下圖所示:

有解決問題的,記得點贊!點贊!點贊!
搞不定的可私信我,歡迎交流
兄弟問題:
如何將多個 Excel 工作簿的工作表合併成一個工作表?
如何快速的合併多個 Excel 工作簿成為一個工作簿?
R語言版:
如果是在多個工作簿:
library(tidyverse)
library(readxl)
files = list.files("exercise/", pattern = "xlsx", full.names = TRUE)
df = map(files, read_xlsx) %&>%
reduce(full_join, by = "人名") # 讀入+依次做全連接合併
writexl::write_xlsx(df, "新工作簿.xlsx") # 寫出
如果是在一個工作簿的多個工作表:
path = "工作簿.xlsx"
df &<- map(excel_sheets(path), ~ read_excel(path, sheet = .)) %&>%
reduce(full_join, by = "人名") # 讀入+依次做全連接合併
writexl::write_xlsx(df, "新數據簿.xlsx") # 寫出
這裡分享一個用函數處理的方法,簡單易學
操作步驟
- 在總表中,列舉豎向目錄:人名,列舉橫向目錄:業績

2. 用公式Vlookup,在總表中查詢各個分表的原始數據

注意:所選數據區域需要用$固定,比如B$2表示 B「2」位置被固定住了,再往下拖動時"2"的引用位置不會動
這樣能得出這樣的結果,有部分"#N/A"引用錯誤( 此錯誤表明值對函數或公式不可用,比如小明在4月沒有業績)
3. 用IF+ ISERROR公式刪除"#N/A"。
公式邏輯是如果C11="#N/A"的話,顯示為空格」「;如果不是的話,顯示實際業績。

你的問題再複雜也能用這倆個公式的組合快速算出幾名員工的業績。
當然,邏輯上說一個月的績效分幾個table顯示,似乎不通。會用EXCEL的人一般不會這麼做。
在不同頁面操作視頻如下
EXCEL將不同頁面的數據匯總到一張總表
首先合併所有文件縱向
然後將合併的人名分類

依次將3個表的數據對比到總表

最終結果

好多推薦VBA,感覺有點殺雞用牛刀的意思。擼好多代碼的時間其實幾秒鐘就可以通過Power Query搞定~
-----------------------------------------分割線----------------------------------------
先道個歉,看到表要合併就想當然的想到了「合併查詢」這個功能。我學藝不精,先給誤導觀眾道個歉。
現在開始圖文步驟操作,會比VBA省時省力很多,Excel既有的東西咱們要用~別萬事不決VBA~ 23333333
操作步驟:
1. 先把要合併的表用control + t 把表建好。(這個就不截圖了,大家都會)
2. 選中其中一個表格,點到到「數據」板塊里,然後點「自表格」

4. 進入PQ以後,把要用到的表也拉到PQ裡面


5. 點擊「添加查詢」合併每個表,把要用的表雙擊點進去


6. 選中兩列,然後列上有單擊,點「合併列」


輸入號新的列名!

6.5 右鍵列,點「重命名」改名字(強迫症,因為新列為「人名.1」)

7. 然後點「分組依據」--&> 「高級「;要分組的是人名,所以上面選「人名」,底下添加分組,你要的結果是每月每人的業績,所以選「求和」;「柱」這裡選要求和的月份。

8. 不要管「null」直接按「關閉並上載」TADA~

PQ有一點好就是你原表做改動,你PQ製作出來的表裡面的數據也會跟著變動。
具體實際操作可以看B站上孫興華的視頻
鏈接是這個:https://b23.tv/Rnimjy
可以用VBA合併。有需要的話,私信聯繫。
wps可以,我在我要自學網上看的,剛好看到。wps 2019視頻教程(表格篇),2-5章節合併或拆分工作表。免費的,就十來分鐘。你可以自己去看看
Σ&>―(〃°ω°〃)?→
看的頭疼
其實可以很簡單
金山文檔開發一個6到飛起的功能——合併表格。
簡單的講就是無論多少表格,不用一個一個打開文件複製再粘貼,只要格式相似,所有表格信息都可以直接合併到一張表格里。
操作方式為
1在電腦打開金山文檔 並且打開所需合併的各個表格。
2菜單欄選擇「數據管理」——「合併表格」——「添加文件」,把所需合併表格的鏈接複製進去。


3點擊「開始合併」,所有表格數據全部匯總到一張表格。

你們真苯,其實一個sumif函數就完了
推薦閱讀:
※求問哪個軟體適合建立這樣的表格?
※Excel為什麼沒有WPS處理流暢?
※Excel複製粘貼不了。總是提示單元格格式太多?
※請問有公司只用excel做數據分析嘛?
※Excel表格怎麼同步修改?
TAG:MicrosoftExcel | Excel使用 | 電子表格 |