Excel檔案間資料自動處理

更新於 發佈於 閱讀時間約 4 分鐘

目的:

由A EXCEL檔內取得的資料,經加工處理後,移至另外一個B EXCEL檔內。

來達成兩個檔案間資料的交流存取,並作成巨集執行,以避免人為操作錯誤,提升作業效率。


1 流程說明

1.1 來源A EXCEL檔內之資料複製至 B EXCEL檔

1.2 於B EXCEL檔取得資料,依據需求作加工處理後,產出另一個sheet

1.3 上述兩個作業,設計成兩個巨集與按鈕,只要按下按鈕便自動完成


2 作業說明

2.1 先打開關聯的兩個 EXCEL 檔

2.2 依需求設計如下兩個巨集:

(1)複製來源EXCEL,出力更新資料至目地EXCEL

(2)加工處理產出所需之EXCEL工作表內容

2.3 EXCEL Macro 巨集作成步驟:

(1) 先於EXCEL檔案/ 選項 --> 啟用巨集

(2) 於檔案/ 選項 --> 開啟 " 開發人員"

(3) 開發人員-> 按“錄製巨集” 或 “ 巨集”以便作巨集編輯

依下圖進入巨集編輯

raw-image


2.4配置兩個按鈕

按鈕作成:

EXCEL開發人員-> 插入 -> 表單控制項 ->按鈕圖示新作成->

再於按鈕右鍵 -> 指定巨集


3 實例說明

以某單位志工別服務時數之EXCEL檔內資料為例,經加工編輯處理後,產出所需的工作表(sheet)內容格式,再存至另一個EXCEL 檔

3.1 兩個EXCEL 檔準備

(3.1.1)志工服務時數

(3.1.2) 志工時數條VHPrint.xls

3.2 兩個巨集設計

(3.2.1) 兩個巨集存放於志工時數條VHPrint.xls內,

(3.2.2) 巨集名稱

*M1)巨集UpdateSheet,執行步驟:

STEP1刪除志工時數條VHPrint 檔內 requests sheet內容

STEP2 志工時數條檔 requests sheet 資料範圍選取後,copy取得

STEP3上述取得之資料,paste to 志工時數條VHPrint.xls檔內

執行完後顯示志工人數


*M2)巨集ReduceVHP, 執行步驟:

STEP 1 刪除志工時數條VHPrint檔內 VHPrint sheet del

STEP 2 新VHPrint sheet內資料,由requests sheet取得再加工處理

處理內容包括:

*P1)出力標題列,追加兩個欄位,"社工人員" 與"志工名字"

*P2) 來源檔內,每一志工有三列資料,經加工簡化為一列

來源檔內,每一位志工資料有三列

raw-image


經加工處理簡化為一列,如下

raw-image

(3.2.3) 巨集編輯,設計撰寫,測試執行,於如下環境:

raw-image


3.3巨集按鈕作成

   (3.3.1) 本例兩個按鈕名稱 為

     ”更新時數條內容” ,指定巨集為 巨集UpdateSheet

     ”簡化時數條” ,指定巨集為 巨集ReduceVHP

按鈕作成如下:

raw-image

(3.3.2)  簡化時數條按鈕,按下後,結束會出力加工編輯後之sheet,並顯示志工人數,參考如下

raw-image


4. 改善效果:

4.1 程式自動執行刪除舊資料,取得最新資料,完成加工處理

4.2 按下巨集按鈕,便可輕易完成,提高作業效率

5 將來運用

5.1其他業務可仿效,設計vba讓手動作業,自動處理

5.2 加工編輯後之sheet,可與WORD合併表格,作列印處理


6 本例巨集設計如下 :

6.1 巨集UpdateSheet

Sub 巨集UpdateSheet()
Dim crow As Integer
Dim i As Integer

Application.WindowState = xlNormal
'Windows("志工時數條VHPrint.xls").Activate
'Selection.SpecialCells(xlCellTypeVisible).Select
'Sheets("Requests").Cells.ClearContents

' STEP 1 刪除志工時數條VHP 檔內 requests sheet del

Workbooks("志工時數條VHPrint.xls").Sheets("Requests").Activate
crow = Range("A" & Cells.Rows.Count).End(xlUp).Row
For i = crow To 1 Step -1
Rows(i).EntireRow.Delete
Next i
'MsgBox "VHP del end ??"

' STEP 2 原始志工時數條檔 requests sheet 資料範圍選取後,copy取得

Workbooks("志工時數條.xls").Sheets("Requests").Activate
'---must A1
Range("A1").Select

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'MsgBox "copy ? "

' STEP 3 將上述取得之requests sheet資料,paste to 志工時數條VHPrint.xls檔內

Workbooks("志工時數條VHPrint.xls").Sheets("Requests").Activate

Selection.SpecialCells(xlCellTypeVisible).Select
Sheets("Requests").Select
'must A1,paste from to A1----
Range("A1").Select
ActiveSheet.Paste

Application.CutCopyMode = False
'Workbooks("志工時數條VHPrint.xls").Sheets("Requests").Activate
crow = Range("A" & Cells.Rows.Count).End(xlUp).Row
MsgBox "Requests sheet was updated" & "; 志工數=" & (crow - 1) / 3
End Sub


6.2 巨集ReduceVHP

Sub 巨集ReduceVHP()
Dim crow As Integer
Dim outR As Integer

Dim i, iR, pos As Integer
Dim PIC, VS As String

'Windows("志工時數條VHPrint.xls").Activate

' STEP 1 刪除志工時數條VHPrint檔內 VHPrint sheet del

Workbooks("志工時數條VHPrint.xls").Sheets("VHPrint").Activate
crow = Range("A" & Cells.Rows.Count).End(xlUp).Row
For i = crow To 1 Step -1
Rows(i).EntireRow.Delete
Next i
'MsgBox "old VHPrint sheet del end ??"

' STEP 2VHP sheet內資料,由requests sheet取得

Sheets("Requests").Activate
crow = Range("A" & Cells.Rows.Count).End(xlUp).Row

' VHP sheet內row 1 標題列取得
Range("A1:E1").Copy Sheets("VHPrint").Range("A1:E1")
Range("A1:B1").Copy Sheets("VHPrint").Range("F1:G1")

Sheets("VHPrint").Cells(1, 6).Value = "社工人員"
Sheets("VHPrint").Cells(1, 7).Value = "志工名字"

VS = "老人關懷服務 文書"
PIC = "黃X萍"

outR = 2
For i = 2 To crow Step 3

iR = i + 2
' range("A4:E4") copy to 另一sheet之range("A2:E2")
Range("A" & iR, "E" & iR).Copy Destination:=Sheets("VHPrint").Range("A" & outR, "E" & outR)

' 為取得 原始儲存格之格式 先copy取得 F, G之儲存格格式
Range("E" & iR).Copy Destination:=Sheets("VHPrint").Range("F" & outR, "G" & outR)

' Sheets("VHPrint").Range(G,outR)儲存格內容,由Range(A,i)左邊起取出字串至 "】"
pos = InStr(Range("A" & i).Value, "】")
Sheets("VHPrint").Range("G" & outR).Value = Left(Range("A" & i).Value, pos)

Sheets("VHPrint").Cells(outR, 2).Value = VS
Sheets("VHPrint").Cells(outR, 6).Value = PIC

outR = outR + 1
Next
'MsgBox "get ok??"

Sheets("VHPrint").Activate
Range("A1").Select
MsgBox "VHPrint sheet was reduced" & "; 志工人數=" & outR - 2

End Sub

-----by linct-----


留言
avatar-img
留言分享你的想法!
avatar-img
linct的沙龍
64會員
191內容數
樂趣體驗紀錄,沙龍房間有歌唱,生活,科技
linct的沙龍的其他內容
2025/03/31
比爾·蓋茲於1999年出版的《數位神經系統》一書,至今仍具有參考價值。本文探討該書的核心概念,如何在當今的科技環境下應用數位神經系統,並提出一些需要調整的部分。
Thumbnail
2025/03/31
比爾·蓋茲於1999年出版的《數位神經系統》一書,至今仍具有參考價值。本文探討該書的核心概念,如何在當今的科技環境下應用數位神經系統,並提出一些需要調整的部分。
Thumbnail
2025/01/26
此程式利用批次檔(bat)結合vbscript,自動篩選Windows事件紀錄,並產生Excel報表,方便管理人員統計與分析系統事件。
Thumbnail
2025/01/26
此程式利用批次檔(bat)結合vbscript,自動篩選Windows事件紀錄,並產生Excel報表,方便管理人員統計與分析系統事件。
Thumbnail
2025/01/20
此程式 可將 CSV 檔案資料轉換並更新至 Excel 檔案,減少檔案大小並方便傳輸。程式可自訂 CSV、Excel 檔案路徑及更新範圍,多種儲存格式與應用場景,如讀取 TXT 檔案、保留 Excel 巨集公式及設定排程自動執行等。
Thumbnail
2025/01/20
此程式 可將 CSV 檔案資料轉換並更新至 Excel 檔案,減少檔案大小並方便傳輸。程式可自訂 CSV、Excel 檔案路徑及更新範圍,多種儲存格式與應用場景,如讀取 TXT 檔案、保留 Excel 巨集公式及設定排程自動執行等。
Thumbnail
看更多
你可能也想看
Thumbnail
介紹朋友新開的蝦皮選物店『10樓2選物店』,並分享方格子與蝦皮合作的分潤計畫,註冊流程簡單,0成本、無綁約,推薦給想增加收入的讀者。
Thumbnail
介紹朋友新開的蝦皮選物店『10樓2選物店』,並分享方格子與蝦皮合作的分潤計畫,註冊流程簡單,0成本、無綁約,推薦給想增加收入的讀者。
Thumbnail
當你邊吃粽子邊看龍舟競賽直播的時候,可能會順道悼念一下2300多年前投江的屈原。但你知道端午節及其活動原先都與屈原毫無關係嗎?這是怎麼回事呢? 本文深入探討端午節設立初衷、粽子、龍舟競渡與屈原自沉四者。看完這篇文章,你就會對端午、粽子、龍舟和屈原的四角關係有新的認識喔。那就讓我們一起解開謎團吧!
Thumbnail
當你邊吃粽子邊看龍舟競賽直播的時候,可能會順道悼念一下2300多年前投江的屈原。但你知道端午節及其活動原先都與屈原毫無關係嗎?這是怎麼回事呢? 本文深入探討端午節設立初衷、粽子、龍舟競渡與屈原自沉四者。看完這篇文章,你就會對端午、粽子、龍舟和屈原的四角關係有新的認識喔。那就讓我們一起解開謎團吧!
Thumbnail
某單位志工服務時數記錄在檔案內,需匯入衛福部志工系統,但由於志工多、檔案多,進行人工計算耗時費工且常累計算錯。故提此法改善,提高效率。文章內容包含了作業流程、作業說明、實例說明、改善前後、展開運用和設計編寫。
Thumbnail
某單位志工服務時數記錄在檔案內,需匯入衛福部志工系統,但由於志工多、檔案多,進行人工計算耗時費工且常累計算錯。故提此法改善,提高效率。文章內容包含了作業流程、作業說明、實例說明、改善前後、展開運用和設計編寫。
Thumbnail
在工作中,我們經常需要處理各種報表,而其中一項任務就是每天手動更新報表。這可能是一個繁瑣且容易被遺忘的工作,但有幸的是,我們可以利用VBA(Visual Basic for Applications)和Windows工作排程器來自動化這個過程,讓我們在不知不覺中完成這項任務。
Thumbnail
在工作中,我們經常需要處理各種報表,而其中一項任務就是每天手動更新報表。這可能是一個繁瑣且容易被遺忘的工作,但有幸的是,我們可以利用VBA(Visual Basic for Applications)和Windows工作排程器來自動化這個過程,讓我們在不知不覺中完成這項任務。
Thumbnail
你知道嗎?無論是哪個 Excel 功能,我們都可以透過 RPA 將 Excel 與其他應用程式進行串接及互動,因此 RPAI 數位優化器這次要來分享的是如何透過 Microsoft Power Automate 進行 RPA 流程開發,呼叫你手中的各種巨集檔!
Thumbnail
你知道嗎?無論是哪個 Excel 功能,我們都可以透過 RPA 將 Excel 與其他應用程式進行串接及互動,因此 RPAI 數位優化器這次要來分享的是如何透過 Microsoft Power Automate 進行 RPA 流程開發,呼叫你手中的各種巨集檔!
Thumbnail
你是否曾經在辦公室中需要製作大量的獎狀?繁瑣的工作讓你感到頭痛嗎?別擔心!在這篇文章中,將介紹一個使用Excel中的郵件格式來快速製作上千個獎狀的技巧。這個技巧不僅能節省大量的時間和精力,還能提高你的工作效率,讓你在職場中輕鬆應對大量的獎狀製作需求。在工作中,我們經常會遇到需要製作獎狀的情況。
Thumbnail
你是否曾經在辦公室中需要製作大量的獎狀?繁瑣的工作讓你感到頭痛嗎?別擔心!在這篇文章中,將介紹一個使用Excel中的郵件格式來快速製作上千個獎狀的技巧。這個技巧不僅能節省大量的時間和精力,還能提高你的工作效率,讓你在職場中輕鬆應對大量的獎狀製作需求。在工作中,我們經常會遇到需要製作獎狀的情況。
Thumbnail
在職場上,組織架構圖是一種常用的圖表,用來展示公司或組織的人員結構和職位關係。製作組織架構圖可以幫助員工快速了解公司的組織架構,明確自己的職位和工作範圍,並促進部門間的溝通和協作。Excel是一款常用的辦公軟體,它提供了多種圖表製作工具,包括組織架構圖。使用Excel製作組織架構圖可以快速生成
Thumbnail
在職場上,組織架構圖是一種常用的圖表,用來展示公司或組織的人員結構和職位關係。製作組織架構圖可以幫助員工快速了解公司的組織架構,明確自己的職位和工作範圍,並促進部門間的溝通和協作。Excel是一款常用的辦公軟體,它提供了多種圖表製作工具,包括組織架構圖。使用Excel製作組織架構圖可以快速生成
Thumbnail
一. 如何批量產出100個Excel工作表 每次要建立多個Excel工作表還是在用複製新增嗎?如果是一兩個還好,但如果是一個月或是100呢?這樣下去,下班時間離我越來越遙遠了。今天教你一個讓你準時下班的秘訣!只需10秒,你就能輕鬆地批量產出100個Excel工作表。
Thumbnail
一. 如何批量產出100個Excel工作表 每次要建立多個Excel工作表還是在用複製新增嗎?如果是一兩個還好,但如果是一個月或是100呢?這樣下去,下班時間離我越來越遙遠了。今天教你一個讓你準時下班的秘訣!只需10秒,你就能輕鬆地批量產出100個Excel工作表。
Thumbnail
只要三步驟即可快速統一變更檔名,提高職場工作力!
Thumbnail
只要三步驟即可快速統一變更檔名,提高職場工作力!
Thumbnail
如果有多個資料來源,例如下面範例有4個季別的資料,如何將這些數據一起樞紐進行分析呢? 方式有很多種哦,今天就來分享使用樞紐分析表樞紐分析圖精靈來達成這個需求,這個功能呢在一般的EXCEL不會出現,要手動呼叫他才會出現唷,步驟比較繁瑣,我們直接看影片教學吧🤩🤩 覺得分享有幫助到你在工作上提
Thumbnail
如果有多個資料來源,例如下面範例有4個季別的資料,如何將這些數據一起樞紐進行分析呢? 方式有很多種哦,今天就來分享使用樞紐分析表樞紐分析圖精靈來達成這個需求,這個功能呢在一般的EXCEL不會出現,要手動呼叫他才會出現唷,步驟比較繁瑣,我們直接看影片教學吧🤩🤩 覺得分享有幫助到你在工作上提
Thumbnail
從電腦桌面、檔案的管理,就可觀察出人的工作能力。 有些桌面是滿滿滿的Word、Excel、PPT、資料夾、程式...,還有檔名不同但內容相同、檔名相同但進度不同、多胞胎檔案散布各處....。 光要找到對的資料,就先耗費心神,大大影響工作效率。終於找到檔案可以開始作業,但戰鬥力被消磨掉不知道剩幾%了。
Thumbnail
從電腦桌面、檔案的管理,就可觀察出人的工作能力。 有些桌面是滿滿滿的Word、Excel、PPT、資料夾、程式...,還有檔名不同但內容相同、檔名相同但進度不同、多胞胎檔案散布各處....。 光要找到對的資料,就先耗費心神,大大影響工作效率。終於找到檔案可以開始作業,但戰鬥力被消磨掉不知道剩幾%了。
Thumbnail
此篇文章我整理了一點日常工作小貼士,不是那種:如何開好會議、如何製作簡報,這種主題明確、有很完整的工作重點、技巧分享,就是幾點小小的我自己的體會。
Thumbnail
此篇文章我整理了一點日常工作小貼士,不是那種:如何開好會議、如何製作簡報,這種主題明確、有很完整的工作重點、技巧分享,就是幾點小小的我自己的體會。
追蹤感興趣的內容從 Google News 追蹤更多 vocus 的最新精選內容追蹤 Google News