รวมชีต Excel ได้อย่างมีประสิทธิภาพด้วย Power Query – ไม่ต้องก็อปปี้-วาง

ปัญหาที่พบเมื่อคัดลอกองค์ประกอบต่างๆ ของชีตงานไปยังอีกชีตหนึ่ง คือ คุณอาจไม่ได้รับรูปแบบจริงขององค์ประกอบที่ต้องการ การคัดลอกและวางจำนวนมากขององค์ประกอบลงในชีตงานใน Microsoft Excel อาจใช้เวลามาก ในบทความนี้เราจะเรียนรู้กระบวนการว่า Power Query รวมไฟล์ Excel หลายไฟล์อย่างไร และวิธีรวมข้อมูลจากหลายชีตงานเป็นชีตเดียว โดยไม่ต้องคัดลอกและวาง

คุณสามารถดาวน์โหลดสมุดงาน Excel ฟรีได้ที่นี่ และฝึกฝนด้วยตัวเอง

3 วิธีง่ายๆ ในการรวมชีต Excel โดยไม่ต้องคัดลอกและวาง

ในบทความนี้ คุณจะเห็น 3 วิธีง่ายๆ ในการรวมชีต Excel โดยไม่ต้องคัดลอกและวาง ในวิธีแรก ผมจะรวมข้อมูลจาก หลายชีต เป็นชีตเดียว ในวิธีที่สอง ผมจะใช้เครื่องมือ Power Query ของ Excel ส่วนวิธีสุดท้าย จะใช้โค้ด VBA เพื่อรวมชีต Excel หลายชีต

เพื่อแสดงทุกขั้นตอน ผมจะใช้ชุดข้อมูล 2 ชุด ใน 2 ชีตที่ต่างกัน

1. รวมข้อมูลจากหลายชีตงานด้วยคุณสมบัติ Consolidate

สมมติว่าคุณมี 2 ชีตงาน ที่มีชื่อสินค้าเหมือนกัน จำนวนยอดขายใน 3 เดือนถูกกำหนด และคุณต้องการหายอดขายรวมของแต่ละสินค้าในแต่ละเดือน นั่นคือเวลาที่คุณต้องใช้คุณสมบัติ Consolidate ของ Excel เพื่อทำภารกิจนี้ ดูขั้นตอนต่อไปนี้เพื่อเข้าใจคุณสมบัตินี้

ขั้นตอนที่ 1:

• ขั้นแรก ให้เปิดชีตงานว่างในสมุดงานเดียวกัน และเลือกเซลล์ที่ต้องการให้แสดงผลลัพธ์
• จากนั้น ไปที่แท็บ Data ของริบบอน และเลือก Consolidate ซึ่งอยู่ภายใต้กลุ่ม Data Tools

ขั้นตอนที่ 2:

• คุณจะเห็นกล่องโต้ตอบ Consolidate
• ที่นี่ จากรายการแบบเลื่อนลง Function ให้เลือก Sum
• จากนั้น ในกล่อง Reference ให้ป้อนช่วงข้อมูลจากชุดข้อมูลแรก
• ต่อไป ให้ทำเครื่องหมายทั้งสองตัวเลือกภายใต้ Use labels in หัวข้อ
• สุดท้าย กด Add

ขั้นตอนที่ 3:

• อีกครั้ง ในกล่อง Reference ให้แทรกช่วงข้อมูลของชุดข้อมูลที่สอง
• หลังจากแทรกแล้ว กด Add
• สุดท้าย คลิก OK

ขั้นตอนที่ 4:

• หลังจากกด OK คุณจะเห็นข้อมูลบางส่วนถูกแทรกในชีตงานใหม่ ที่มีชื่อสินค้าต่างๆ พร้อมยอดขายรายเดือน
• ในชีตงานนี้ ยอดขายรายเดือนถูกรวมกันแล้ว

ขั้นตอนที่ 5:

• ในชีตงานใหม่ คุณจะเหว่หัวข้อ Product Name หายไป
• เพราะมันอยู่ที่เซลล์ B4 ในทั้งสองชีต และในคุณสมบัติ Consolidate เซลล์นี้ถูกนำมาเป็นเซลล์อ้างอิง
• ดังนั้น หลังจากใช้ Consolidate ข้อความของเซลล์อ้างอิงจะไม่แสดง
• จึงควรจัดรูปแบบชีตงานใหม่พร้อมข้อมูลที่เพิ่มเข้ามา

อ่านเพิ่มเติม: การใช้ VLOOKUP เพื่อรวมสองชีตใน Excel (พร้อมขั้นตอนง่ายๆ)

2. ใช้เครื่องมือ Power Query รวมชีต Excel

Excel นำเสนอคุณสมบัติใหม่คือ Power Query ซึ่งสามารถทำหลายอย่างที่เวอร์ชันก่อนหน้าไม่สามารถทำได้ ด้วย Power Query การรวมข้อมูลหลายชุดจากชีตงานต่างๆ เป็นชีตเดียวทำได้ง่ายมาก นอกจากนี้ ยังสามารถ รวมสองชีต Excel ตามคอลัมน์เดียวกัน ได้อีกด้วย ดังนั้น เพื่อเรียนรู้เพิ่มเติม โปรดทำตามขั้นตอนด้านล่าง

ขั้นตอนที่ 1:

• ขั้นแรก ผมจะแปลงชุดข้อมูลทั้งสองเป็นตาราง
• เลือกช่วงข้อมูล B4:E8 ของชุดข้อมูลแรก
• ไปที่แท็บ Insert ของริบบอน และเลือก Table

ขั้นตอนที่ 2:

• คุณจะเห็นกล่องโต้ตอบ Create Table
• ป้อนช่วงข้อมูลที่ถูกต้อง แล้วเลือก OK

ขั้นตอนที่ 3:

• ช่วงข้อมูลจะแปลงเป็นตาราง Excel แล้ว
• แปลงชุดข้อมูลที่สองเป็นตารางด้วยขั้นตอนเดียวกัน

ขั้นตอนที่ 4:

• เปิดชีตงานใหม่ ไปที่แท็บ Data ของริบบอน
• จากกลุ่ม Get & Transform Data เลือก Get Data

ขั้นตอนที่ 5:

• จากคำสั่งก่อนหน้า เลือก From File
• จากนั้นเลือก From Excel Workbook เพื่อโหลดชีตงานทั้งหมดสำหรับการรวม

ขั้นตอนที่ 6:

• ไปที่ตำแหน่งที่สมุดงานถูกวางซึ่งมีชีตสำหรับรวม
• เลือกสมุดงาน และกด Import

ขั้นตอนที่ 7:

• หลังจากนำเข้า คุณจะเห็นกล่องโต้ตอบ Navigator
• ทำเครื่องหมาย Select multiple items สำหรับการรวม
• เลือกตารางทั้งหมดจากสมุดงานที่ต้องการรวม
• กด Load

ขั้นตอนที่ 8:

• คุณจะเห็นหน้าต่าง Queries & Connections หลังจากโหลดตารางแล้ว

ขั้นตอนที่ 9:

• ไปที่คำสั่ง Get Data จากแท็บ Data อีกครั้ง
• จากเมนูแบบเลื่อนลง เลือก Combine Queries
• จากเมนูที่สอง เลือก Merge

ขั้นตอนที่ 10:

• คุณจะเห็นหน้าต่าง Merge สำหรับเลือกชีต
• เลือกตารางที่ต้องการรวม
• เลือกคอลัมน์ที่เหมือนกันในทั้งสองตาราง
• กด OK

ขั้นตอนที่ 11:

• คุณจะเห็นชุดข้อมูลที่โหลดมาถูกรวมกันแล้ว
• ตารางที่สองยังไม่ได้ขยายเต็มที่
• เพื่อขยายตาราง ให้คลิกไอ콘ที่มุมขวาบนของหัวคอลัมน์

ขั้นตอนที่ 12:

• กล่องโต้ตอบใหม่จะปรากฏ
• ยกเลิกการเลือกหัวคอลัมน์ที่ต้องการให้เหมือนกันในชีตใหม่
• ยกเลิกเครื่องหมาย Use original column name as prefix
• กด OK

ขั้นตอนที่ 13:

• คุณจะเห็นชีตที่รวมแล้วใน Power Query
• เพื่อนำเข้าสู่ชีตงาน ให้เลือก Close & Load

ขั้นตอนที่ 14:

• สุดท้าย คุณจะเห็นข้อมูลจากชีตงานที่รวมแล้วในชีตเดียว ตามคอลัมน์เดียวกัน

ขั้นตอนที่ 15:

• เพิ่มเติม หากคุณต้องการต่อยอดชีตงาน (Append) คือ รวมชีตงานตามแถว หลังจากแปลงชุดข้อมูลทั้งสองเป็นตารางแล้ว ให้เปิดชีตงานใหม่
• ไปที่คำสั่ง Get Data จากแท็บ Data เลือก Combine Queries

ขั้นตอนที่ 16:

• คุณจะเห็นหน้าต่าง Append
• เลือกหมายเลขตารางที่ต้องการต่อยอด
• เลือกตารางทั้งหมดตามลำดับ
• กด OK

ขั้นตอนที่ 17:

• Power Query จะต่อยอดตารางทั้งสองจากชีตงานเป็นตารางเดียว
• เลือก Close and Load เพื่อนำตารางที่รวมแล้วไปยังชีตงานหลัก

ขั้นตอนที่ 18:

• ผลลัพธ์สุดท้ายจะมีลักษณะดังภาพ

อ่านเพิ่มเติม: วิธีรวมชีต Excel เป็นสมุดงานเดียว (4 วิธีที่เหมาะสม)

3. ใช้โค้ด VBA รวมชีต Excel โดยไม่ต้องคัดลอกและวาง

ในขั้นตอนสุดท้าย ผมจะใช้โค้ด VBA สำหรับรวมชีตงานหลายชีตโดยไม่ต้องคัดลอกและวาง ผมไม่ต้องใช้คุณสมบัติหรือเครื่องมือจาก Excel ผมจะแทรกลำดับและคำสั่งที่จำเป็นในโค้ด และหลังจากรัน มันจะทำการรวมโดยอัตโนมัติ ไปตามขั้นตอนด้านล่างเพื่อเข้าใจดีขึ้น

ขั้นตอนที่ 1:

• เปิดชีตงานใหม่ ไปที่แท็บ Developer ของริบบอน
• จากกลุ่ม Code เลือก Visual Basic

ขั้นตอนที่ 2:

• คุณจะเห็นหน้าต่าง VBA
• จากแท็บ Insert เลือก Module

ขั้นตอนที่ 3:

• คัดลอกโค้ดต่อไปนี้และวางในโมดูล
• หลังจากคัดลอก ให้บันทึกโค้ด

'Giving name of sub procedure Sub Merge_Excel_Worksheets() 'Declaring variables Dim x As Integer 'Enabling error handling On Error Resume Next 'Seleting a new worksheet for showing result Sheets(1).Select Worksheets.Add 'Naming the new workhseet Sheets(1).Name = "Merged" 'Selecting first sheet for merging Sheets(2).Activate 'Addressing the cell range Range("B4:E8").EntireRow.Select 'Selecting the destination for new cell range Selection.Copy Destination:=Sheets(1).Range("B4:E8") 'Starting the for loop For x = 2 To Sheets.Count 'Activating x number of sheets for merging Sheets(x).Activate 'Selecting the range for merging Range("B4:E8").Select 'Selecting the current region Selection.CurrentRegion.Select 'Selecting the tables without selecting the headers Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select 'Merging all data in the new sheet Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(5) Next End Sub

การอธิบายโค้ด VBA

• ขั้นแรก ตั้งชื่อฟังก์ชันหรือกระบวนงานย่อย

Sub Merge_Excel_Worksheets()

• ขั้นที่สอง ประกาศตัวแปร

Dim x As Integer

• ขั้นที่สาม เปิดใช้งานการจัดการข้อผิดพลาด

On Error Resume Next

• ขั้นที่สี่ เลือกชีตงานสำหรับแสดงผลลัพธ์

Sheets(1).Select Worksheets.Add

• ขั้นที่ห้า ตั้งชื่อชีตงานที่สร้างใหม่

Sheets(1).Name = "Merged"

• เลือกชีตงานที่มีชุดข้อมูลแรกสำหรับรวม

Sheets(2).Activate

• กำหนดช่วงเซลล์ของชุดข้อมูลแรก

Range("B4:E8").EntireRow.Select

• เลือกช่วงเซลล์ปลายทางหลังจากรวม

Selection.Copy Destination:=Sheets(1).Range("B4:E8")

• เริ่มลูป For

For x = 2 To Sheets.Count

• เปิดใช้งานชีตงานทั้งหมดในสมุดงานสำหรับรวม และเลือกช่วงข้อมูลของพวกเขา

Sheets(x).Activate Range("B4:E8").Select

• เลือกช่วงข้อมูลของชีตงานเหล่านั้นและรวมทั้งหมดในชีต Merged

Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select Selection.Copy Destination:=Sheets(1).Range("B65536").End(xlUp)(5)

ขั้นตอนที่ 4:

• กด F5 หรือปุ่ม Run เพื่อรันโค้ด
• ก่อนกด ให้วางเคอร์เซอร์ไว้ในโมดูล

ขั้นตอนที่ 5:

• หลังจากรันโค้ด คุณจะเห็นชีตงานใหม่พร้อมข้อมูลที่รวมแล้วในรูปแบบดิบ

ขั้นตอนที่ 6:

• เพิ่มหัวข้อและจัดรูปแบบชีตตามต้องการ

ใช้ Power Query รวมไฟล์ Excel หลายไฟล์จากโฟลเดอร์

ในส่วนข้างต้น คุณเห็นขั้นตอนการรวมชีต Excel โดยไม่ต้องคัดลอกวาง ตอนนี้ผมจะสาธิตวิธีรวมไฟล์ Excel หลายไฟล์จากโฟลเดอร์ โดยใช้ Power Query ของ Excel อีกครั้ง ดูขั้นตอนด้านล่าง

ขั้นตอนที่ 1:

• ใช้สมุดงานที่มียอดขายเดือน January
• เลือกช่วงข้อมูล B4:C18 คลิกแท็บ Insert
• จากกลุ่ม Tables เลือก Table

ขั้นตอนที่ 2:

• ในกล่องโต้ตอบ Create Table หลังตรวจสอบเกณฑ์ทั้งหมด เลือก OK

ขั้นตอนที่ 3:

• คำสั่งนี้จะแปลงชุดข้อมูลเป็นรูปแบบตาราง
• บันทึกสมุดงานนี้ในโฟลเดอร์เฉพาะ

ขั้นตอนที่ 4:

• จัดเก็บสมุดงานทั้งหมดที่จะรวมไว้ในโฟลเดอร์เดียวกัน และแปลงช่วงข้อมูลทั้งหมดเป็นรูปแบบตารางตามขั้นตอนก่อนหน้า

ขั้นตอนที่ 5:

• เปิดชีตงานใหม่ จากแท็บ Data เลือก Get Data
• จากเมนูแบบเลื่อนลงเลือก From File แล้วเลือก From Folder

ขั้นตอนที่ 6:

• คุณจะเห็นหน้าต่างใหม่ที่มีสมุดงานทั้งหมด
• เลือก Combine and Transform Data

ขั้นตอนที่ 7:

• คุณจะเห็นหน้าต่าง Combine Files
• ดูไฟล์ตัวอย่างและชุดข้อมูลพร้อมกับมัน
• ในการตั้งค่า Parameter ให้เลือก Table แทนที่จะเป็น sheet name
• กด OK

ขั้นตอนที่ 8:

• สมุดงานทั้งหมดถูกรวมเป็นหนึ่งเดียวแล้ว
• เพื่อนำเข้าสู่ชีตงานใหม่ ให้เลือก Close & Load

ขั้นตอนที่ 9:

• ชีตงานใหม่พร้อมสมุดงานที่รวมแล้วจะมีลักษณะดังภาพ

ใช้ CMD รวมไฟล์ Excel เป็นไฟล์เดียว

ในส่วนสุดท้ายของบทความนี้ ผมจะแสดงขั้นตอนการ รวมไฟล์ Excel หลายไฟล์เป็นไฟล์เดียว โดยใช้ CMD สำหรับการทำเช่นนี้ เราต้องแปลงสมุดงานจากรูปแบบ .xlsx เป็น .csv ดูขั้นตอนด้านล่าง

ขั้นตอนที่ 1:

• ใช้สมุดงาน 3 ไฟล์เดียวกับที่แสดงในส่วนก่อนหน้า
• จากสมุดงานใดก็ได้ ไปที่แท็บ File ของริบบอน

ขั้นตอนที่ 2:

• จากหน้าต่าง Home เลือก Save As แล้ว Browse

ขั้นตอนที่ 3:

• สร้างโฟลเดอร์ในไดรฟ์ C: ของคุณเป็นปลายทางบันทึก
• ในประเภทการบันทึก เลือก CSV เป็นรูปแบบ
• กด Save เพื่อบันทึกไฟล์

ขั้นตอนที่ 4:

• คุณจะเห็นสมุดงานทั้งหมดในโฟลเดอร์ที่ระบุ

ขั้นตอนที่ 5:

• คัดลอกเส้นทางของโฟลเดอร์ Sales File CSV ที่จัดเก็บไฟล์ Excel ในรูปแบบ CSV
• เพื่อให้มีตัวเลือก Copy path ให้ขยายริบบอนโดยคลิกสัญลักษณ์ลูกศรในมุมขวาบน
• เลือกโฟลเดอร์ Sales File CSV และคลิก Copy path

ขั้นตอนที่ 6:

• กด Windows key + R พร้อมกันเพื่อเปิด Run
• ในกล่อง Open พิมพ์ cmd และคลิก OK

ขั้นตอนที่ 7:

• พิมพ์ cd เว้นวรรค แล้ววางเส้นทางโฟลเดอร์ที่คัดลอกใน ขั้นตอนที่ 5
• กด Enter

ขั้นตอนที่ 8:

• หลังกด Enter จะเห็นไดเรกทอรีจากบรรทัดก่อนหน้า
• พิมพ์ copy *.csv Merged.csv
• ต้องเว้นวรรคหลังคำว่า copy
• ผมเลือก Merged เป็นชื่อไฟล์ใหม่

ขั้นตอนที่ 9:

• หลังกด Enter โค้ดนี้จะรวมสมุดงานทั้งหมดโดยอัตโนมัติ

ขั้นตอนที่ 10:

• กลับไปที่โฟลเดอร์ที่บันทึกไฟล์ CSV คุณจะเห็นไฟล์ที่รวมแล้ว

ขั้นตอนที่ 11:

• เปิดไฟล์ และคุณจะพบข้อมูลทั้งหมดในรูปแบบดิบ

ขั้นตอนที่ 12:

• เนื่องจากมีข้อมูลจากหลายชีต ให้เลื่อนลงเพื่อดูข้อมูลที่เหลือ

ขั้นตอนที่ 13:

• บันทึกไฟล์นี้อีกครั้งในรูปแบบ .xlsx

ขั้นตอนที่ 14:

• เปิดไฟล์ Excel และจัดรูปแบบชีตดังภาพ

ขั้นตอนที่ 15:

• เลื่อนลงเพื่อดูชีตทั้งหมดสำหรับชุดข้อมูลขนาดใหญ่

สรุป

จบบทความนี้แล้ว หวังว่าจะเป็นประโยชน์ หลังอ่านคุณจะสามารถรวมชีต Excel ได้โดยไม่ต้องคัดลอกวาง กรุณาแบ่งปันคำถามหรือข้อเสนอแนะในส่วนความคิดเห็นด้านล่าง

ทีม ExcelDemy เฝ้าระวังความต้องการของคุณเสมอ หลังจากความคิดเห็น ให้เวลาสักครู่ เราจะตอบด้วยวิธีแก้ที่ดีที่สุด

บทความที่เกี่ยวข้อง

• วิธีรวมทุกชีตเป็นชีตเดียวใน Excel (6 วิธีเร็ว)