Blog

รวม Sheet เป็น Sheet เดียว ใน Excel จะทำอย่างไรดี ? มันก็มีวิธีให้เลือกทำได้หลายแบบอยู่เหมือนกัน ทั้งใช้เครื่องมือใน Excel ที่มีมาให้แล้ว และเขียน VBA เพิ่มเติม

ตามปกติ ถ้ามีข้อมูลน้อย ๆ แค่ Sheet 2 Sheet อาจจะใช้วิธี copy and paste ต่อท้ายกันไปเรื่อย ๆ แต่ถ้ามันมีจำนวนหลาย Sheet ก็คงต้องหาตัวช่วยสักหน่อย วิธีนี้เหมือนกับการนำข้อมูลเข้าเหมือนกัน ดัดแปลงใช้กับการนำเข้าข้อมูลที่เป็น Texf File ก็ได้

แต่ก่อนอื่นต้องดูก่อนว่า แต่ละ Sheet ที่จะดึงเข้ามารวมเป็น Sheet เดียวกันนั้น มีหัวตารางเหมือนกันหรือเปล่า ถ้าเป็นแบบเดียวกันก็ง่ายหน่อย แต่ถ้าคนละแบบก็ต้องเสียเวลาเพิ่มขึ้น ลองมาดูกันทีละแบบ

รวม Sheet ด้วย power query

Power Query เป็นอีกเครื่องมือที่ช่วยให้การทำงานรวดเร็วขึ้น มันทำงานตามชื่อเลย คือ คัดเลือกตกแต่งข้อมูล เพื่อให้เอามาใช้ใน Excel ได้สะดวกรวดเร็วขึ้น

เช่น ถ้าเราต้องการดึงข้อมูลจาก Excel เราก็ทำโดย กดที่ ribbon DATA คลิกที่ Get Data -> From File -> From Workbook จากนั้นให้เลือกไฟล์ Excel ที่ต้องการ เมื่อขึ้นหน้าต่างขึ้นมาให้เลือก Sheet ที่ต้องการ

(หมายเหตุ สำหรับ Excel รุ่นเก่า ๆ ต้องดาวน์โหลด Add-Ins มาใช้

รายละเอียดแบบละเอียด ลองอ่านที่เว็บของไมโครซอฟต์ดูนะจ๊ะ ถ้ามีโอกาสจะเขียนถึงการใช้ Power Query อีกครั้งหนึ่ง

แต่สิ่งหนึ่งที่ต้องระวังคือ การดึงข้อมูลจาก Sheet ด้วย power query จะใช้ข้อมูลจากแถวแรกมาเป็นหัวตารางโดยอัตโนมัติ นั่นหมายความว่า ถ้าแถวแรกเป็นช่องว่าง มันจะตั้งหัวตารางเป็น Column1 2 3 4 ไปเรื่อย ๆ จนถึงคอลัมน์สุดท้ายที่มีข้อมูลอยู่

ดังนั้น ถ้าเราจัดระเบียบข้อมูลเป็นอย่างดีไว้ล่วงหน้าก่อนแล้วก็ไม่ต้องกังวลหรือจัดการอะไรอีก

ไปที่ DATA เลือก Get Data แล้วเลือก From File > From Workbook

เมื่อเลือกไฟล์ที่ต้องการแล้ว หน้าตาจะเป็นแบบนี้

อันนี้ไม่ต้องตกใจว่า เอ๊ะ มันมีแค่ไม่กี่ Sheet ทำไมโผล่มาเต็มเลย คือมันมีทั้ง Table และอื่น ๆ มาด้วย

ให้คลิกที่ select multiple items แล้วเลือกที่ sheet ที่ต้องการ แล้วคลิก load จากนั้นลองไปที่  Get Data แล้วเลือก Combine Queries > Append พอมีหน้าต่างขึ้นมาก็คลิกเลือก Sheet ที่ต้องการ Add เข้าไป คลิก OK แล้วก็คลิก load

เพียงเท่านี้ก็เรียบร้อย สำหรับการจัดรูปแบบคอลัมน์ไว้แบบเดียวกันเรียบร้อยแล้ว

รวม Sheet ด้วย VBA

เหมือนเคย เปิด VBA Editor ขึ้นมา (กด Alt + F11 ก็ได้)

สำหรับการเขียนโค้ด VBA เต็ม ๆ มีดังนี้

Sub AppendAllSheets()
    Dim ws As Worksheet
    Dim r As Range
    Dim rTarget As Range
    Dim DataAll As String
    DataAll = "ALL"
    Application.ScreenUpdating = False
    For Each ws In Worksheets
        If ws.Name <> DataAll Then
            With Sheets(DataAll)
                Set rTarget = .Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End With
            Set r = ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))
                r.SpecialCells(xlCellTypeConstants).EntireRow.Copy
                rTarget.PasteSpecial xlPasteValues
        End If
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

ตามโค้ดที่นำเสนอนี้ เตือนไว้ก่อนว่า ยังมีข้อเสียอยู่หลายประการ เช่น ถ้าใน sheet ใด มี Filter ติดไว้ การ copy ข้อมูลมารวมกันจะมาเฉพาะที่ Filter ไว้เท่านั้น ไม่ได้มาทั้งหมด

สำหรับ Sheet ปลายทางที่ให้แสดงผลการรวมข้อมูล ในนี้กำหนดไว้ว่าชื่อ All ซึ่งถ้าไม่ตั้งชื่อ Sheet เป็น All ก็ขอให้เปลี่ยน “ALL” เป็น “ชื่อ Sheet ที่ต้องการ” ได้เลย

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

Set r = ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp))

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