บทความนี้ให้เคล็ดลับพื้นฐานเกี่ยวกับวิธี รวมไฟล์ Excel ตามคอลัมน์ บางครั้งเราอาจมีข้อมูลต่างๆ เกี่ยวกับคนหรือสิ่งของเดียวกัน متناثرในสมุดงาน (Workbook) หลายไฟล์ จึงต้องรวมข้อมูลเหล่านั้นไว้ในชีทเดียว ในที่นี้เรามีข้อมูล ชื่อ และ ตำแหน่ง ในไฟล์หนึ่ง และ ชื่อ กับ เงินเดือน ในอีกไฟล์หนึ่ง เราจะรวมแสดง ชื่อ, ตำแหน่ง และ เงินเดือน ในชีทเดียว
3 วิธีรวมไฟล์ Excel ตามคอลัมน์
1. ใช้ฟังก์ชัน VLOOKUP รวมไฟล์ตามคอลัมน์
การใช้ VLOOKUP เป็นวิธีที่มีประสิทธิภาพมาก เราจะดึง คอลัมน์เงินเดือน จากไฟล์ Merge Files (lookup) มาไว้ในไฟล์ Merge Files
ขั้นตอน:
- สร้าง คอลัมน์เงินเดือน ในไฟล์ Merge Files แล้วพิมพ์สูตรต่อไปนี้ในเซลล์ D5:
=VLOOKUP($B5,'[Merge Files (lookup).xlsx]lookup'!$B$5:$C$11,2,FALSE)
สูตรนี้จะค้นหาค่าในเซลล์ B5 ภายในช่วง B5:C11 ของไฟล์ Merge Files (lookup) (ต้องใช้ Absolute Reference) และคืนค่าเงินเดือนที่ตรงกัน Column index number เป็น 2 เพราะเงินเดือนอยู่คอลัมน์ที่ 2 ใช้ FALSE เพื่อค้นหาค่าที่ตรงกันแม่นยำ
- กด ENTER จะได้เงินเดือนของ Jason Campbell (ชื่อใน B5)
- ลาก Fill Handle ลงไปเพื่อ AutoFill เซลล์ด้านล่าง
هكذا คุณสามารถรวมไฟล์ Excel ตามคอลัมน์ด้วย VLOOKUP ได้
2. รวมไฟล์ Excel ตามคอลัมน์ด้วยฟังก์ชัน INDEX และ MATCH
อีกวิธีหนึ่งคือใช้ INDEX และ MATCH ร่วมกัน
ขั้นตอน:
- สร้างคอลัมน์เงินเดือนใน Merge Files พิมพ์สูตรในเซลล์ D5:
=INDEX('[Merge Files (lookup).xlsx]lookup'!$C$5:$C$11,MATCH($B5,'[Merge Files (lookup).xlsx]lookup'!$B$5:$B$11,0))
MATCH ค้นหาค่าใน B5 และคืน หมายเลขแถว จากไฟล์ Lookup แล้ว INDEX ดึงเงินเดือนจากช่วง C5:C11 ต้องใช้ Absolute Reference เพื่อป้องกันข้อผิดพลาด
- กด ENTER ได้เงินเดือนของ Jason Campbell
- ลาก Fill Handle เพื่อ AutoFill เซลล์ด้านล่าง
3. ใช้ Power Query Editor รวมไฟล์ Excel ตามคอลัมน์
ถ้าคิดว่าสูตรยาก ให้ใช้ Power Query Editor จากแท็บ Data
ขั้นตอน:
- เปิดชีทใหม่ >> Data >> Get Data >> From File >> From Excel Workbook
- เลือกไฟล์ Merge File >> Open
- ในหน้าต่าง Navigator เลือกชีท power query (ที่เก็บชื่อและตำแหน่ง) >> Load >> Load To
- เลือก Only Create Connection >> OK (จะเพิ่มใน Queries & Connections)
- ทำซ้ำ: Data >> Get Data >> From File >> From Excel Workbook เลือกไฟล์ Merge Files (lookup)
- ใน Navigator เลือกชีท salary (ที่เก็บชื่อและเงินเดือน) >> Load To >> Only Create Connection
- ไปที่ Data >> Get Data >> Combine Queries >> Merge
- ในหน้าต่าง Merge: เลือก power query จากดรอปดาวน์แรก, salary จากดรอปดาวน์ที่สอง >> คลิกคอลัมน์ Name ของทั้งสองคิวรี >> OK
- ใน Power Query Editor: คลิกไอคอนขยายในคอลัมน์ salary เลือก Salary >> OK
- จะเห็น Name, Designation, Salary รวมกัน >> คลิก Close & Load
ข้อมูลจะแสดงใน Excel Table ใหม่บนชีทใหม่
ส่วนฝึกปฏิบัติ
นี่คือชุดข้อมูลของบทความ สำหรับฝึกปฏิบัติด้วยตัวเอง
สรุป
บทความนี้แสดงวิธีง่ายๆ 3 วิธีในการรวมไฟล์ Excel ตามคอลัมน์ หากกรอกข้อมูลด้วยมือจะใช้เวลานานมาก จึงพัฒนาสูตรและคำสั่งเพื่อรวมไฟล์อัตโนมัติ หากมีไอเดียดีกว่าหรือข้อเสนอแนะ กรุณาแชร์ในกล่องความคิดเห็น จะช่วยให้บทความต่อไปดีขึ้น
บทความที่เกี่ยวข้อง
- เปรียบเทียบและรวมสมุดงาน Excel (3 ขั้นตอนง่าย)
- รวมไฟล์ Excel หลายไฟล์เป็นชีทเดียว (4 วิธี)
- รวมสมุดงานหลายเล่มเป็นสมุดงานเดียวใน Excel (6 วิธี)
