“Excel สร้าง Combination ของ 2 ตารางได้ไหมครับ?” ตั้มถามผมในเช้าวันหนึ่ง

“ยังไงเหรอตั้ม?” ผมถามกลับด้วยความงงงวย

“คืออย่างนี้ครับพี่ สมมติมีตาราง Cost Center แสดงข้อมูล 3 แถว ตาราง GL แสดงข้อมูล 4 แถว ผมอยากสร้างตารางใหม่ที่แสดงการจับคู่ของ Cost Center และ GL ทั้งหมด 12 แถวน่ะครับ”

“ตอนนี้ใช้วิธีถึก เหนื่อยมากเลยพี่” 

“ใช้ VBA เขียนคำสั่งวนลูปก็ได้นี่” ผมตอบ

“ผมใช้ VBA ไม่ค่อยเป็นน่ะครับ มีวิธีอื่นไหมครับ?” ตั้มถาม

“ตอนนี้ใช้ Excel เวอร์ชันอะไร?” ผมถามกลับ

“Excel 2016 ครับพี่”

“ถ้างั้นมีวิธีล่ะ สองนาทีเสร็จ ผมตอบ

“จริงดิพี่! ทำไงอ่ะ?” ตั้มร้องด้วยความตื่นเต้น

บทความนี้คือวิธีที่ผมตอบตั้มครับ ^__^

สมมติตาราง Cost Center หน้าตาแบบนี้

CostCenter

ตาราง GL หน้าตาแบบนี้

GL.png

เทคนิคหลักของวิธีนี้คือใช้ฟังก์ชันที่ชื่อว่า CROSSJOIN ซึ่งเป็นสูตร DAX (Data Analysis eXpression) สรุปได้ 4 ขั้นตอนดังนี้

  1. เปลี่ยนข้อมูลให้เป็น Table
  2. สร้าง Data Model
  3. สร้างตารางใหม่โดยใช้ Existing Connections
  4. ปรับตารางโดยใช้สูตร DAX ที่ชื่อว่า EVALUATE และ CROSSJOIN

 

1 เปลี่ยนข้อมูลให้เป็น Table

คลิกข้อมูลในตาราง (เซลล์ใดก็ได้) แล้วไปที่ Insert/ Table

(อาจใช้คีย์บอร์ดชอร์ทคัต Ctrl+T ก็ได้)

Insert_Table.png

จากนั้นคลิก OK

CreateTable.png

ได้ Table แล้ว

Table_CostCenter

ควรตั้งชื่อ Table ทุกครั้ง เวลาใช้งานจะได้อ้างอิงสะดวก

วิธีการตั้งชื่อ Table ให้คลิกเซลล์ใดก็ได้ใน Table แล้วไปที่ Table/ Design/ พิมพ์ชื่อที่ต้องการ (ด้านล่าง Table Name:)

เช่นเคสนี้ตั้งชื่อว่า CostCenter

TableName.png

ใช้วิธีเดียวกันเปลี่ยนตาราง GL ให้เป็น Table และตั้งชื่อว่า GL

TableName_GL.png

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 ได้ยังไง?

ถ้าใช้ Office 365 แบบ Pro-Plus, E3 หรือ E5 การสร้าง Data Model นั้นง่ายมากๆ

คลิกเซลล์ใดก็ได้ใน Table แล้วไปที่ Power Pivot/ Add to Data Model

PowerPivot_DataModel.png

จะมีหน้าต่างใหม่ขึ้นมา นั่นคือหน้าต่าง Power Pivot

จากหน้าต่างนี้จะพบว่า Data Model ถูกสร้างขึ้นมาแล้ว และมีข้อมูลเพียงตารางเดียวนั่นคือ CostCenter

DataModel1.png

เราต้องนำ Table GL เข้ามาใน Data Model นี้ด้วย

วิธีการก็เหมือนเดิม นั่นคือ คลิกเซลล์ใดก็ได้ใน Table แล้วไปที่ Power Pivot/ Add to Data Model

PowerPivot_DataModel2.png

จะพบว่า Table GL ถูกเพิ่มเข้ามาใน Data Model แล้ว

DataModel2.png

(สังเกตแถบล่างของหน้าต่าง Power Pivot จะแสดงชื่อ CostCenter และ GL)

แล้วถ้าไม่ได้ใช้ Office 365 แบบ Pro-Plus, E3 หรือ E5 ล่ะ?

ปัญหาของวิธีนี้คือ บางคนไม่ได้ใช้ Office 365 แบบ Pro-Plus, E3 หรือ E5

บางคนอาจใช้ Office 365 แบบ Home ซึ่งไม่มีความสามารถนี้ (ไม่มี Power Pivot)

(Office 365 มีหลายเวอร์ชัน แต่มี Power Pivot เพียง 3 เวอร์ชันเท่านั้น)

หรือถ้าใช้ Excel 2013 แบบธรรมดา (ที่ไม่ใช่เวอร์ชัน Professional) ก็ไม่มี Power Pivot เช่นกัน

งั้นทำยังไงดี?

ถ้าทำแบบตรงๆไม่ได้ งั้นก็ทำแบบ “อ้อมๆ” แทนละกัน

นั่นคือสร้าง Pivot Table และเลือกให้ Pivot Table นั้นสร้างจาก Data Model จุดประสงค์เพื่อให้ Pivot Table สร้าง Data Model ขึ้นมานั่นเอง

เริ่มจาก คลิกข้อมูลใน Table (เช่น Table CostCenter) แล้วไปที่ Insert/ Pivot Table

Insert_PivotTable.png

ให้เลือกออปชันที่ชื่อว่า Add this data to the Data Model แล้วกด OK

PivotTable_AddToDataModel.png

จะมีชีตใหม่ให้สร้าง Pivot Table แต่เคสนี้ไม่ต้องสร้าง Pivot Table ปล่อยทิ้งไว้แบบนั้นเลย

PivotTable_2.png

ด้วยวิธีนี้ แม้ Pivot Table ไม่ได้ถูกสร้าง แต่ Data Model ถูกสร้างแล้ว

จะเช็คได้อย่างไรว่า Data Model ถูกสร้างขึ้นมาแล้ว?

เช็คได้ง่ายๆครับ ไปที่ริบเบิน Data/ Manage Data Model

ManageDataModel.png

หน้าต่าง Power Pivot จะป็อปอัพขึ้นมา สังเกตข้อมูลและมุมล่างซ้ายจะพบว่า Table CostCenter ถูกเพิ่มเข้ามาใน Data Model แล้ว

PowePivot_3.png

คราวนี้นำ Table GL เข้ามาใน Data Model บ้าง

ใช้วิธีเดียวกัน นั่นคือคลิกข้อมูลใดๆใน Table GL แล้วไปที่ Insert/ Pivot Table

Insert_PivotTable2.png

เลือกออปชัน Add this data to the Data Model แล้วกด OK

PivotTable_AddToDataModel2.png

จะมีชีตใหม่ให้สร้าง Pivot Table แต่เคสนี้ก็ไม่ต้องสร้าง Pivot Table เช่นกัน

PivotTable_3.png

ถ้าคลิก Data/ Manage Data Model จะพบว่า Table GL ถูกเพิ่มเข้าไปใน Data Model แล้ว

(ถ้ายังไม่ปิดหน้าต่างนี้ จะพบว่า Table GL ถูกเพิ่มเข้าไปโดยอัตโนมัติ)

(เราไม่จำเป็นต้องปิดหน้าต่าง Power Pivot เปิดค้างไว้จนกว่าจะเสร็จก็ได้)

PowePivot_4.png

3 สร้างตารางใหม่โดยใช้ Existing Connections

เมื่อสร้าง Data Model แล้ว ขั้นตอนต่อไปคือสร้าง Table แสดงผลลัพธ์ที่ต้องการนั่นเอง

Table นี้สร้างโดยใช้เทคนิคที่ชื่อว่า Existing Connections

เริ่มจากคลิกตำแหน่งที่ต้องการสร้าง Table (เช่นเคสนี้คือเซลล์ G1) จากนั้นไปที่ริบเบิน Data/ Existing Connections

ExistingConnections.png

จะมีหน้าต่าง Existing Connections แสดงขึ้นมา ให้เลือกแท็ป Tables แล้วเลือก Table ที่สร้างไว้แล้วขึ้นมา (เช่นเคสนี้เลือก Table ชื่อ GL) แล้วคลิก Open

ExistingConnections_2_GL.png

จะมีหน้าต่าง Import Data ขึ้นมา ถามว่าจะ Import ข้อมูลแบบใด ให้เลือก Table (ค่าดีฟอลต์ก็คือ Table) แล้วคลิก OK

ExistingConnections_3.png

จะพบว่าได้ตารางใหม่หน้าตาเหมือน Table GL เป๊ะ

ExistingConnections_4_GL.png

เพื่อความไม่สับสน ขอเปลี่ยนสีตารางนี้ให้เป็นสีอื่นนะครับ ^__^

(คลิก Table/ Design/ เลือกสีที่ต้องการ)

ExistingConnections_5_GL.png

Table ที่สร้างใหม่นี้ผมขอเรียกว่า “ตารางเสมือน” เป็นตารางที่ลิงค์ข้อมูลมาจาก Table GL แต่ยังไม่ใช่แบบที่ต้องการ

การจะปรับให้ได้หน้าตาแบบที่ต้องการ ต้องใช้สูตร DAX

 

4 ปรับตารางโดยใช้สูตร DAX ที่ชื่อว่า EVALUATE และ CROSSJOIN

วิธีการปรับตารางคือ คลิกขวาที่ตารางเสมือน Table/ Edit DAX

Edit_DAX.png

จะได้หน้าต่างนี้ขึ้นมา

Edit_DAX_2.png

ตรง Command Type ให้เปลี่ยนจาก Table เป็น DAX

และพิมพ์ใน Expression ว่า

EVALUATE
CROSSJOIN(CostCenter, GL)

จากนั้นคลิก OK

Edit_DAX_3.png

ได้ตารางแบบที่ต้องการแล้ว ^__^

Combination_1.png

ฮัดช่า !!

EVALUATE คือคำสั่งให้แสดงข้อมูล โดยให้แสดงผลลัพธ์ของฟังก์ชัน DAX ที่ชื่อว่า CROSSJOIN

CROSSJOIN คือฟังก์ชันที่ใช้รวมตารางเข้าด้วยกัน โครงสร้างฟังก์ชันคือ

CROSSJOIN(Table1, Table2,…)

Table1 ในที่นี้คือ Table CostCenter

Table2 ในที่นี้คือ Table GL

ผลลัพธ์ก็ดังที่เห็น ^__^

ถ้าต้องการสลับคอลัมน์ใหม่ ก็ทำได้ตรงๆเลย

Combination_2.png

หรือถ้าต้องการเรียงข้อมูลตาม Cost Center ก็คลิกดร็อปดาวน์คอลัมน์ CostCenter แล้วเลือก Sort Smallest to Largest ได้เลย

Combination_3.png

เจ๋งไหมล่ะ ^__^

Combination_4.png

แล้วถ้าข้อมูลมีการเปลี่ยนแปลงล่ะ?

เป็นคำถามที่ดีมากครับ ตารางนี้คำนวณจากสูตร DAX และเชื่อมกับ Data Model ถ้าข้อมูลที่เป็นตารางต้นทางเปลี่ยน ข้อมูลในตารางนี้ก็จะเปลี่ยนตามไปด้วย

เช่น เพิ่ม GL 705 (Misc) เข้าไปใน Table GL

AddData_1.png

ถ้าต้องการให้ GL 705 เพิ่มเข้าไปในตาราง ก็คลิกขวาที่ตาราง เลือก Refresh

AddData_2.png

GL 705 ถูกเพิ่มเข้ามาแล้ว !!

AddData_3.png

สุดยอดไหมล่ะ ^__^

จริงๆแล้วผลลัพธ์หน้าตาแบบนี้มีชื่อทางเทคนิคว่า Cross Join บางคนอาจเรียกว่า Cartesian Join มักพบในเคสซับซ้อน หรือเป็นเคสที่เกี่ยวกับการจัดการฐานข้อมูล

Cross Join ใน Excel ไม่มีวิธีสร้างตรงๆ ถ้าเป็นเวอร์ชันเก่าอาจสร้างโดยใช้ VBA, Pivot Wizard หรือ Microsoft Query ซึ่งทุกวิธีล้วนมีข้อดี-ข้อเสีย

แต่ถ้าใครใช้ Office 365 บอกเลยว่าต้องลองวิธี DAX CROSSJOIN นี้

แล้วจะร้อง ว้าว! แน่นอน ^__^

.

ถ้าสนใจไฟล์ตัวอย่างของบทความนี้ ดาวน์โหลดเพื่อศึกษาได้เลยครับ

CROSSJOIN_Completed_180706

.

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^

ถ้าสนใจวิธี Microsoft Query  อ่านรายละเอียดได้จากบทความนี้ครับ

http://www.contextures.com/excelmsquerycartesian.html (เขียนโดยคุณ Debra Dalgleish)

ถ้าสนใจวิธี Pivot Wizard และ VBA อ่านรายละเอียดได้จากกระทู้นี้ครับ

https://stackoverflow.com/questions/26999604/is-there-a-way-to-perform-a-cross-join-or-cartesian-product-in-excel