สร้างความสัมพันธ์ใน Excel แม้มีค่าซ้ำ: คู่มือขั้นตอนละเอียด

หลายครั้งเราอาจต้องสร้าง Relationships ใน Excel ด้วยค่า Duplicate เนื่องจากชุดข้อมูลมีคอลัมน์ร่วมที่ใช้ได้ แต่ถ้าทั้งสองตารางมีค่า Duplicate กระบวนการจะยากขึ้น หากสนใจวิธีสร้างเวิร์กชีตหลายแผ่นจากรายการค่าเซลล์ บทความนี้อาจช่วยได้ ในที่นี้จะพูดถึงวิธีสร้างความสัมพันธ์กับค่าเซลล์ซ้ำใน Excel พร้อมอธิบายละเอียด

ดาวน์โหลดสมุดงานฝึกปฏิบัติด้านล่าง

2 วิธีง่ายสร้าง Relationship ใน Excel กับค่า Duplicate

ใช้ชุดข้อมูลด้านล่างสร้าง ความสัมพันธ์ ระหว่าง 2 ตารางใน Excel ด้วยค่า Duplicate ทั้งสองชุดข้อมูลมีค่า Duplicate คอลัมน์ร่วมคือ Product Id Relationship จะอิงจากคอลัมน์ร่วมนี้

1. การผนวกตารางร่วม (Appending Common Table)

เนื่องจากมีค่า Duplicate ในทั้งสองตาราง ไม่สามารถสร้าง Relationship ระหว่างพวกเขาใน Excel ได้ เนื่องจาก Relationship นี้ต้องการ one-to-one Relationship ซึ่ง Excel ไม่รองรับตอนนี้ จะนำเข้าตารางใน Power Query และผนวก (append) รวมกัน หลังจากนั้นลบ Duplicate ออกจากตารางที่ผนวกแล้ว ในขั้นสุดท้าย สร้าง Relationships กับตารางใหม่นี้กับตารางเดิม 2 ตาราง นี่คือวิธีสร้าง Relationship ใน Excel ด้วยค่า Duplicate

ขั้นตอน

• มี 2 ตารางแยกที่ต้องสร้าง Relationship โดยอิงคอลัมน์ร่วม Product Id
• โดยทั่วไป เพื่อสร้าง Relationships ระหว่างตาราง ต้องมีค่าไม่ซ้ำในตารางอย่างน้อย 1 ตาราง
• แต่นี่มีค่า Duplicate ในคอลัมน์ Product Id ของทั้งสองตาราง

• เพื่อแสดงว่าทำไมไม่สามารถสร้าง Relationship ระหว่างตารางที่นำเสนอ ให้ทำตามขั้นตอนด้านล่าง ซึ่งจะพยายามสร้าง relationship ระหว่าง 2 ตารางนี้
• สร้างตารางก่อน เลือกช่วงเซลล์ B4:C16 แล้วคลิกคำสั่ง Table จากแท็บ Insert

• ในกล่องโต้ตอบ Create Table ยืนยันช่วงตารางอีกครั้ง
• ติ๊ก My table has headers

• ทำซ้ำสร้างตารางที่ 2 เลือกช่วง B4:C16 คลิก Table จาก Insert

• ในกล่องโต้ตอบ Create Table ยืนยันช่วง ติ๊ก My table has headers

• หลังสร้างตารางแล้ว สร้าง Pivot Table จาก 2 ตารางที่สร้าง
• เลือกตารางแรก ไป Insert > PivotTable > From Table/Range

• ในกล่องโต้ตอบถัดไป คลิก New Worksheet
• ติ๊ก Add this data to the Data Model
• กด OK

• เลือกตารางที่ 2 ไป Insert > PivotTable > From Table/Range

• ในกล่องโต้ตอบ คลิก New Worksheet, ติ๊ก Add this data to the Data Model, กด OK

• ในเวิร์กชีตใหม่ จะเห็นเมนู PivotTable Fields
• แผงด้านข้างมีทั้ง 2 ตาราง คลิก All เพื่อดูทั้งคู่

• ลากฟิลด์ Product Id ไป區域 Filter ด้านล่าง
• ลาก Revenue และ Profit จาก Table2 และ Cost และ Quantity จาก Table3
• จะเห็น Revenue และ Profit แสดงค่าถูกต้อง แต่ Quantity และ Cost แสดงค่าเดียวกันทุกแถว ซึ่งผิดปกติ
• เพราะไม่มี relationship ระหว่างตาราง
• ต้องสร้าง Relationship ก่อน

• วางเคอร์เซอร์ในตาราง ใดก็ได้ เปิดใช้งาน Power Pivot ใน Excel แล้วคลิกแท็บ Power Pivot
• คลิก Add to Data Model

• ใน Data Model จะเห็นทั้ง 2 ตารางโหลดแล้ว

• สลับไป Diagram view โดยคลิก Diagram view ในแท็บ Home

• จากแท็บ Design คลิก Create Relationship

• ในกล่องโต้ตอบ Create Relationship เลือก Table2 และ Table3 ในกล่องดรอปดาวน์
• ด้านล่างจะเห็นเครื่องหมายเตือน
• สรุปเตือน: มีค่า Duplicate ในทั้งสองคอลัมน์ ไม่สามารถสร้าง Relationship ได้ เพราะต้องเป็น many-to-many ซึ่งไม่รองรับตอนนี้
• กด Cancel

• ตอนนี้จะเปิด Power Query และจากนั้นจะผนวก (append) 2 ตารางแยก
• ใช้ตารางที่ผนวกแล้ว สร้าง Relationships ที่จะช่วยได้ผลลัพธ์ที่ต้องการ
• เลือก Table2
• จากแท็บ Data ไป Get Data > From Other Sources > From Table/Range

• จะมีหน้าต่างใหม่ และในหน้าต่างนั้น Table2 โหลดแล้ว

• ทำซ้ำกับ Table3

• คลิกขวา Table2 จากเมนูบริบท คลิก Add as New Query

• จะมี Query ใหม่ปรากฏด้านล่าง
• คลิกขวา List จากเมนูบริบท คลิก To Table

• ในหน้าต่างต่อไป ไม่ต้องทำอะไร กด OK

• คลิกขวา Table2 อีกครั้ง จากเมนูบริบท คลิก Add as New Query

• จะมี Query ใหม่ด้านล่าง
• คลิกขวา List จากเมนูบริบท คลิก To Table

• ในหน้าต่างต่อไป ไม่ต้องทำอะไร กด OK

• ตอนนี้มี 2 คอลัมน์แยกจากตารางเดิม อยู่ใน Query ต่างกัน

• จะ Append Product Id และ Product Id (2)
• เลือกตาราง Product Id คลิก Append Queries จากกลุ่ม Combine
• จากเมนูดรอปดาวน์ คลิก Append Queries

• หน้าต่างด้านล่างจะปรากฏ
• ในเมนูนั้น คลิก Product Id (2) จากตัวเลือก Table to append
• กด OK

• Product Id (2) ผนวกกับ Product Id เรียบร้อย
• ในตารางที่ผนวกแล้ว คลิกขวา เลือก Remove Duplicates

• ค่า Duplicates ถูกลบทั้งหมด
• ปิดตาราง Power Query กด Keep ตอนออก

• ตารางที่สร้างใหม่จะโหลดในแผ่นแยก
• ไปที่แผ่นชื่อ Product Id
• คลิก Add to Data Model จากแท็บ Power Pivot

• ตารางใหม่พร้อมใช้ใน Power Pivot
• ในแท็บ Design คลิก Create Relationship

• ในหน้าต่าง Create Relationship เลือก Table2 และ Product Id
• กด OK

• ใน Diagram view เห็น Relationship ระหว่าง Table2 และ Product Id

• ในหน้าต่าง Create Relationship เลือก Table3 และ Product Id
• กด OK

• ใน Diagram view เห็น Relationship ระหว่าง Table3 และ Product Id

• กลับไปที่รายงาน Pivot Table เดิม
• ลบฟิลด์เดิมทั้งหมด ใส่ฟิลด์ดังภาพ
• เริ่มเพิ่มฟิลด์ Product Id ใน Rows
• ลาก Revenue ไป Values
• เห็นปัญหาเดิม แต่มีพื้นที่เตือนสีเหลือง บ่งบอกมี Relationship ระหว่างตาราง
• คลิก Auto-Detect

• หลังคลิก Auto Detect กล่องโต้ตอบด้านล่างจะปรากฏ
• จะใช้เวลาสักครู่เชื่อม Relationship
• หลังจากนั้น Relationship จะถูกค้นพบและเชื่อมโยง
• กด Close

• ลาก Revenue, Profit จาก Table2_2
• ลาก Quantity, Cost จาก Table3_2
• ถ้ามีเตือนสีเหลือง Relationship คลิก Auto Detect อีกครั้ง
• ตอนนี้ Pivot Table แสดงค่าตาม Relationship ที่สร้างแล้ว

2. ลบค่า Duplicate

วิธีนี้จะลบค่าเซลล์ Duplicate ของทั้งสองตาราง แล้วสร้าง one-to-one relationship ระหว่างพวกเขา การกำจัด Duplicate จะใช้คำสั่ง Power Query ด้วยตารางที่ไม่มี Duplicate สร้าง Relationship ใน Excel ด้วยค่า Duplicate ได้

ขั้นตอน

• มี 2 ตารางแยก ต้องสร้าง Relationship อิงคอลัมน์ร่วม Product Id
• โดยทั่วไป ต้องมีค่าไม่ซ้ำในตารางอย่างน้อย 1 ตาราง
• แต่นี่มีค่า Duplicate ในคอลัมน์ Product Id ของทั้งสองตาราง

• สร้างตาราง เลือกช่วง B4:C16 คลิก Table จาก Insert

• ใน Create Table ยืนยันช่วง ติ๊ก My table has headers

• ทำซ้ำสร้างตารางที่ 2 เลือก B4:C16 คลิก Table จาก Insert

• ใน Create Table ยืนยันช่วง ติ๊ก My table has headers

• เปิด Power Query ลบค่า Duplicate จากตาราง
• เลือก Table1
• จากแท็บ Data ไป Get Data > From Other Sources > From Table/Range

• หน้าต่างใหม่เปิด Table1 โหลดแล้ว

• ทำซ้ำกับ Table2

• ใน Table1 คลิกขวา Product Id เลือก Remove Duplicates

• ค่า Duplicates ถูกลบทั้งหมด

• ทำซ้ำกับ Table2 คลิกขวาตาราง เลือก Remove Duplicates

• หลังลบ Duplicates ตารางจะเห็นดังภาพ

• คลิก Close & Load To จากแท็บ Home

• ตารางโหลดในแผ่นต่างกัน
• จะเพิ่มใน Pivot Table
• เลือก Table1 ที่โหลดแล้ว ไป Insert > PivotTable > From Table/Range

• ในกล่องโต้ตอบ คลิก Existing Worksheet และตำแหน่งตาราง
• ติ๊ก Add this data to the Data Model
• กด OK

• เลือก Table2 ที่โหลดแล้ว ไป Insert > PivotTable > From Table/Range

• ในกล่องโต้ตอบ คลิก Existing Worksheet และตำแหน่ง ติ๊ก Add this data to the Data Model กด OK

• ลาก Product Id จาก Table1_2 ไป Rows
• ลาก Revenue, Profit จาก Table1_2 และ Cost, Quantity จาก Table2_2 ไป Values
• ค่า Cost และ Quantity ใน Pivot Table ผิดปกติ แสดงค่าเดียวกันทุกแถว

• คลิก CREATE บนกล่องเตือนสีเหลือง

• ในหน้าต่าง Edit Relationship เลือก Table2_2 และ Table 1_2 ในเมนูดรอปดาวน์แรกและสอง
• เลือก Product Id ในทั้ง Column (Foreign) และ Related Column (Primary)
• กด OK

• หลังกด OK จะเห็น Relationship แก้ปัญหาแล้ว
• คอลัมน์ Cost และ Profit แสดงค่าถูกต้อง

สิ่งที่ควรจำ

• ขณะสร้าง Relationships ระหว่าง 2 ตารางในวิธีที่ 2 คอลัมน์ร่วมที่อยู่ในฟิลด์ Rows ควรเป็นจากตาราง Related Column (Primary) มิฉะนั้นจะสร้างค่าผิดใน Pivot Table
• ติ๊ก Add data to the data model จำเป็น มิฉะนั้นไม่สามารถเชื่อมต่อได้

สรุป

สรุปแล้ว ปัญหาการสร้าง Relationship ใน Excel ด้วยค่า Duplicate ได้รับคำตอบ 2 วิธี

มีสมุดงานให้ดาวน์โหลดฝึกวิธีเหล่านี้

ถามคำถามหรือให้ข้อเสนอแนะได้ในส่วนความคิดเห็น ข้อเสนอแนะสำหรับชุมชน Exceldemy จะขอบคุณมาก