“Excel สร้าง Combination ของ 2 ตารางได้ไหมครับ?” ตั้มถามผมในเช้าวันหนึ่ง
“ยังไงเหรอตั้ม?” ผมถามกลับด้วยความงงงวย
“คืออย่างนี้ครับพี่ สมมติมีตาราง Cost Center แสดงข้อมูล 3 แถว ตาราง GL แสดงข้อมูล 4 แถว ผมอยากสร้างตารางใหม่ที่แสดงการจับคู่ของ Cost Center และ GL ทั้งหมด 12 แถวน่ะครับ”
“ตอนนี้ใช้วิธีถึก เหนื่อยมากเลยพี่”
“ใช้ VBA เขียนคำสั่งวนลูปก็ได้นี่” ผมตอบ
“ผมใช้ VBA ไม่ค่อยเป็นน่ะครับ มีวิธีอื่นไหมครับ?” ตั้มถาม
“ตอนนี้ใช้ Excel เวอร์ชันอะไร?” ผมถามกลับ
“Excel 2016 ครับพี่”
“ถ้างั้นมีวิธีล่ะ สองนาทีเสร็จ“ ผมตอบ
“จริงดิพี่! ทำไงอ่ะ?” ตั้มร้องด้วยความตื่นเต้น
บทความนี้คือวิธีที่ผมตอบตั้มครับ ^__^
สมมติตาราง Cost Center หน้าตาแบบนี้
ตาราง GL หน้าตาแบบนี้
เทคนิคหลักของวิธีนี้คือใช้ฟังก์ชันที่ชื่อว่า CROSSJOIN ซึ่งเป็นสูตร DAX (Data Analysis eXpression) สรุปได้ 4 ขั้นตอนดังนี้
- เปลี่ยนข้อมูลให้เป็น Table
- สร้าง Data Model
- สร้างตารางใหม่โดยใช้ Existing Connections
- ปรับตารางโดยใช้สูตร DAX ที่ชื่อว่า EVALUATE และ CROSSJOIN
1 เปลี่ยนข้อมูลให้เป็น Table
คลิกข้อมูลในตาราง (เซลล์ใดก็ได้) แล้วไปที่ Insert/ Table
(อาจใช้คีย์บอร์ดชอร์ทคัต Ctrl+T ก็ได้)
จากนั้นคลิก OK
ได้ Table แล้ว
ควรตั้งชื่อ Table ทุกครั้ง เวลาใช้งานจะได้อ้างอิงสะดวก
วิธีการตั้งชื่อ Table ให้คลิกเซลล์ใดก็ได้ใน Table แล้วไปที่ Table/ Design/ พิมพ์ชื่อที่ต้องการ (ด้านล่าง Table Name:)
เช่นเคสนี้ตั้งชื่อว่า CostCenter
ใช้วิธีเดียวกันเปลี่ยนตาราง GL ให้เป็น Table และตั้งชื่อว่า GL
2 สร้าง Data Model
เมื่อสร้าง Table ทั้งสองเสร็จแล้ว ขั้นตอนต่อไปคือสร้าง Data Model จาก Table ทั้งสองนี้
Data Model คืออะไร?
ขออธิบายแบบภาพรวมนะครับ
Data Model คือ โครงข่ายข้อมูลที่มีได้ตั้งแต่ 1 ตาราง (หรือจะเป็นสิบตารางก็ได้) โดยสามารถสร้างความสัมพันธ์ (Relationship) เพื่อเชื่อมตารางทั้งหมดเข้าด้วยกันได้
Data Model ถูกนำมาใช้ตั้งแต่ Excel 2013 จุดประสงค์หลักเพื่อเป็นฐานข้อมูลของ Power Pivot (รวมถึงฐานข้อมูลของ Power BI)
ขออนุญาตข้ามความรู้พื้นฐานและทฤษฎีเรื่อง Data Model ไปก่อนนะครับ ไม่งั้นบทความนี้จะยาวมาก ^^
มี Table แล้ว จะทำให้เป็น Data Model ได้ยังไง?
ใช้ Power Pivot ครับ
(เดิมที Power Pivot มีเฉพาะใน Excel บางเวอร์ชันเท่านั้น ถ้าใช้ Office 365 ก็มีเฉพาะเวอร์ชันที่เป็น Pro-Plus, E3 และ E5 แต่ไมโครซอฟต์เพิ่งประกาศวันที่ 22 พฤษภาคม 2018 ว่า Power Pivot สามารถใช้ได้กับทุกเวอร์ชันของ Office 365 https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9018823-make-power-pivot-available-in-all-versions-of-exce)
คลิกเซลล์ใดก็ได้ใน Table แล้วไปที่ Power Pivot/ Add to Data Model
จะมีหน้าต่างใหม่ขึ้นมา นั่นคือหน้าต่าง Power Pivot
จากหน้าต่างนี้จะพบว่า Data Model ถูกสร้างขึ้นมาแล้ว และมีข้อมูลเพียงตารางเดียวนั่นคือ CostCenter
เราต้องนำ Table GL เข้ามาใน Data Model นี้ด้วย
วิธีการก็เหมือนเดิม นั่นคือ คลิกเซลล์ใดก็ได้ใน Table แล้วไปที่ Power Pivot/ Add to Data Model
จะพบว่า Table GL ถูกเพิ่มเข้ามาใน Data Model แล้ว
(สังเกตแถบล่างของหน้าต่าง Power Pivot จะแสดงชื่อ CostCenter และ GL)
แล้วถ้าไม่มี Power Pivot ล่ะ?
ปัญหาของวิธีนี้คือ ถ้าใช้ Excel 2013 แบบธรรมดา (ที่ไม่ใช่เวอร์ชัน Professional) จะไม่มี Power Pivot
งั้นทำยังไงดี?
ถ้าทำแบบตรงๆไม่ได้ งั้นก็ทำแบบ “อ้อมๆ” แทนละกัน
นั่นคือสร้าง Pivot Table และเลือกให้ Pivot Table นั้นสร้างจาก Data Model จุดประสงค์เพื่อให้ Pivot Table สร้าง Data Model ขึ้นมานั่นเอง (เทคนิคนี้ใช้ได้กับ Excel 2013/ 2016 เท่านั้น ใช้กับ Excel 2007/ 2010 ไม่ได้)
เริ่มจาก คลิกข้อมูลใน Table (เช่น Table CostCenter) แล้วไปที่ Insert/ Pivot Table
ให้เลือกออปชันที่ชื่อว่า Add this data to the Data Model แล้วกด OK
จะมีชีตใหม่ให้สร้าง Pivot Table แต่เคสนี้ไม่ต้องสร้าง Pivot Table ปล่อยทิ้งไว้แบบนั้นเลย
ด้วยวิธีนี้ แม้ Pivot Table ไม่ได้ถูกสร้าง แต่ Data Model ถูกสร้างแล้ว
จะเช็คได้อย่างไรว่า Data Model ถูกสร้างขึ้นมาแล้ว?
เช็คได้ง่ายๆครับ ไปที่ริบเบิน Data/ Manage Data Model
หน้าต่าง Power Pivot จะป็อปอัพขึ้นมา สังเกตข้อมูลและมุมล่างซ้ายจะพบว่า Table CostCenter ถูกเพิ่มเข้ามาใน Data Model แล้ว
คราวนี้นำ Table GL เข้ามาใน Data Model บ้าง
ใช้วิธีเดียวกัน นั่นคือคลิกข้อมูลใดๆใน Table GL แล้วไปที่ Insert/ Pivot Table
เลือกออปชัน Add this data to the Data Model แล้วกด OK
จะมีชีตใหม่ให้สร้าง Pivot Table แต่เคสนี้ก็ไม่ต้องสร้าง Pivot Table เช่นกัน
ถ้าคลิก Data/ Manage Data Model จะพบว่า Table GL ถูกเพิ่มเข้าไปใน Data Model แล้ว
(ถ้ายังไม่ปิดหน้าต่างนี้ จะพบว่า Table GL ถูกเพิ่มเข้าไปโดยอัตโนมัติ)
(เราไม่จำเป็นต้องปิดหน้าต่าง Power Pivot เปิดค้างไว้จนกว่าจะเสร็จก็ได้)
3 สร้างตารางใหม่โดยใช้ Existing Connections
เมื่อสร้าง Data Model แล้ว ขั้นตอนต่อไปคือสร้าง Table แสดงผลลัพธ์ที่ต้องการนั่นเอง
Table นี้สร้างโดยใช้เทคนิคที่ชื่อว่า Existing Connections
เริ่มจากคลิกตำแหน่งที่ต้องการสร้าง Table (เช่นเคสนี้คือเซลล์ G1) จากนั้นไปที่ริบเบิน Data/ Existing Connections
จะมีหน้าต่าง Existing Connections แสดงขึ้นมา ให้เลือกแท็ป Tables แล้วเลือก Table ที่สร้างไว้แล้วขึ้นมา (เช่นเคสนี้เลือก Table ชื่อ GL) แล้วคลิก Open
จะมีหน้าต่าง Import Data ขึ้นมา ถามว่าจะ Import ข้อมูลแบบใด ให้เลือก Table (ค่าดีฟอลต์ก็คือ Table) แล้วคลิก OK
จะพบว่าได้ตารางใหม่หน้าตาเหมือน Table GL เป๊ะ
เพื่อความไม่สับสน ขอเปลี่ยนสีตารางนี้ให้เป็นสีอื่นนะครับ ^__^
(คลิก Table/ Design/ เลือกสีที่ต้องการ)
Table ที่สร้างใหม่นี้ผมขอเรียกว่า “ตารางเสมือน” เป็นตารางที่ลิงค์ข้อมูลมาจาก Table GL แต่ยังไม่ใช่แบบที่ต้องการ
การจะปรับให้ได้หน้าตาแบบที่ต้องการ ต้องใช้สูตร DAX
4 ปรับตารางโดยใช้สูตร DAX ที่ชื่อว่า EVALUATE และ CROSSJOIN
วิธีการปรับตารางคือ คลิกขวาที่ตารางเสมือน Table/ Edit DAX
จะได้หน้าต่างนี้ขึ้นมา
ตรง Command Type ให้เปลี่ยนจาก Table เป็น DAX
และพิมพ์ใน Expression ว่า
EVALUATE
CROSSJOIN(CostCenter, GL)
จากนั้นคลิก OK
ได้ตารางแบบที่ต้องการแล้ว ^__^
ฮัดช่า !!
EVALUATE คือคำสั่งให้แสดงข้อมูล โดยให้แสดงผลลัพธ์ของฟังก์ชัน DAX ที่ชื่อว่า CROSSJOIN
CROSSJOIN คือฟังก์ชันที่ใช้รวมตารางเข้าด้วยกัน โครงสร้างฟังก์ชันคือ
CROSSJOIN(Table1, Table2,…)
Table1 ในที่นี้คือ Table CostCenter
Table2 ในที่นี้คือ Table GL
ผลลัพธ์ก็ดังที่เห็น ^__^
ถ้าต้องการสลับคอลัมน์ใหม่ ก็ทำได้ตรงๆเลย
หรือถ้าต้องการเรียงข้อมูลตาม Cost Center ก็คลิกดร็อปดาวน์คอลัมน์ CostCenter แล้วเลือก Sort Smallest to Largest ได้เลย
เจ๋งไหมล่ะ ^__^
แล้วถ้าข้อมูลมีการเปลี่ยนแปลงล่ะ?
เป็นคำถามที่ดีมากครับ ตารางนี้คำนวณจากสูตร DAX และเชื่อมกับ Data Model ถ้าข้อมูลที่เป็นตารางต้นทางเปลี่ยน ข้อมูลในตารางนี้ก็จะเปลี่ยนตามไปด้วย
เช่น เพิ่ม GL 705 (Misc) เข้าไปใน Table GL
ถ้าต้องการให้ GL 705 เพิ่มเข้าไปในตาราง ก็คลิกขวาที่ตาราง เลือก Refresh
GL 705 ถูกเพิ่มเข้ามาแล้ว !!
สุดยอดไหมล่ะ ^__^
จริงๆแล้วผลลัพธ์หน้าตาแบบนี้มีชื่อทางเทคนิคว่า Cross Join บางคนอาจเรียกว่า Cartesian Join มักพบในเคสซับซ้อน หรือเป็นเคสที่เกี่ยวกับการจัดการฐานข้อมูล
Cross Join ใน Excel ไม่มีวิธีสร้างตรงๆ ถ้าเป็นเวอร์ชันเก่าอาจสร้างโดยใช้ VBA, Pivot Wizard หรือ Microsoft Query ซึ่งทุกวิธีล้วนมีข้อดี-ข้อเสีย
แต่ถ้าใครใช้ Office 365 บอกเลยว่าต้องลองวิธี DAX CROSSJOIN นี้
แล้วจะร้อง ว้าว! แน่นอน ^__^
.
ถ้าสนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาได้เลยครับ
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^
ถ้าสนใจวิธี Microsoft Query อ่านรายละเอียดได้จากบทความนี้ครับ
http://www.contextures.com/excelmsquerycartesian.html (เขียนโดยคุณ Debra Dalgleish)
ถ้าสนใจวิธี Pivot Wizard และ VBA อ่านรายละเอียดได้จากกระทู้นี้ครับ
