Applies ToExcel for Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016
เบราว์เซอร์ของคุณไม่สนับสนุนวิดีโอ ติดตั้ง Microsoft Silverlight, Adobe Flash Player หรือ Internet Explorer 9

เพิ่มพลังให้กับการวิเคราะห์ข้อมูลของคุณโดยการสร้างความสัมพันธ์จากตารางต่างๆ ความสัมพันธ์คือการเชื่อมต่อระหว่างสองตารางที่มีข้อมูล: หนึ่งคอลัมน์ในแต่ละตารางเป็นพื้นฐานสําหรับความสัมพันธ์ เมื่อต้องการดูว่าเหตุใดความสัมพันธ์จึงมีประโยชน์ ให้จินตนาการว่าคุณติดตามข้อมูลสําหรับคําสั่งซื้อของลูกค้าในธุรกิจของคุณ คุณสามารถติดตามข้อมูลทั้งหมดในตารางเดียวที่มีโครงสร้างดังนี้:

CustomerID

ชื่อ

อีเมล

อัตราส่วนลด

รหัสใบสั่งซื้อ

วันที่สั่งซื้อ

ผลิตภัณฑ์

ปริมาณ

1

Ashton

chris.ashton@contoso.com

.05

256

2010-01-07

กล้องดิจิทัลขนาดเล็ก

11

1

Ashton

chris.ashton@contoso.com

.05

255

2010-01-03

กล้อง SLR

15

2

Jaworski

michal.jaworski@contoso.com

.10

254

2010-01-03

กล้องถ่ายภาพยนตร์รุ่นประหยัด

27

วิธีนี้สามารถทํางานได้ แต่เกี่ยวข้องกับการจัดเก็บข้อมูลที่ซ้ําซ้อนจํานวนมาก เช่น ที่อยู่อีเมลของลูกค้าสําหรับทุกคําสั่งซื้อ ที่เก็บข้อมูลมีราคาถูก แต่ถ้าที่อยู่อีเมลเปลี่ยนแปลง คุณต้องตรวจสอบให้แน่ใจว่าคุณอัปเดตทุกแถวสําหรับลูกค้ารายนั้น โซลูชันหนึ่งสําหรับปัญหานี้คือการแยกข้อมูลออกเป็นหลายตารางและกําหนดความสัมพันธ์ระหว่างตารางเหล่านั้น นี่คือวิธีการที่ใช้ในฐานข้อมูลเชิงสัมพันธ์ เช่น SQL Server ตัวอย่างเช่น ฐานข้อมูลที่คุณนําเข้าอาจแสดงข้อมูลการสั่งซื้อโดยใช้ตารางที่เกี่ยวข้องกันสามตาราง ดังนี้

ลูกค้า

[CustomerID]

ชื่อ

อีเมล

1

Ashton

chris.ashton@contoso.com

2

Jaworski

michal.jaworski@contoso.com

CustomerDiscounts

[CustomerID]

อัตราส่วนลด

1

.05

2

.10

การสั่งซื้อ

[CustomerID]

รหัสใบสั่งซื้อ

วันที่สั่งซื้อ

ผลิตภัณฑ์

ปริมาณ

1

256

2010-01-07

กล้องดิจิทัลขนาดเล็ก

11

1

255

2010-01-03

กล้อง SLR

15

2

254

2010-01-03

กล้องถ่ายภาพยนตร์รุ่นประหยัด

27

ความสัมพันธ์มีอยู่ภายในตัวแบบข้อมูล ซึ่งเป็นความสัมพันธ์ที่คุณสร้างอย่างชัดเจน หรือความสัมพันธ์ที่ Excel สร้างขึ้นในนามของคุณโดยอัตโนมัติเมื่อคุณนําเข้าหลายตารางพร้อมกัน คุณยังสามารถใช้ add-in Power Pivot เพื่อสร้างหรือจัดการตัวแบบได้ สําหรับรายละเอียด ให้ดูที่ สร้างตัวแบบข้อมูลใน Excel

ถ้าคุณใช้ Power Pivot Add-in เพื่อนําเข้าตารางจากฐานข้อมูลเดียวกัน Power Pivot สามารถตรวจหาความสัมพันธ์ระหว่างตารางโดยยึดตามคอลัมน์ที่อยู่ใน [วงเล็บ] และสามารถสร้างความสัมพันธ์เหล่านี้ขึ้นใหม่ในตัวแบบข้อมูลที่สร้างเบื้องหลังได้ สําหรับข้อมูลเพิ่มเติม ให้ดูที่ การตรวจหาอัตโนมัติและการอนุมานของความสัมพันธ์ ในบทความนี้ ถ้าคุณนําเข้าตารางจากหลายแหล่งข้อมูล คุณสามารถสร้างความสัมพันธ์ได้ด้วยตนเองตามที่อธิบายไว้ใน สร้างความสัมพันธ์ระหว่างสองตาราง

ความสัมพันธ์จะยึดตามคอลัมน์ในแต่ละตารางที่มีข้อมูลเดียวกัน ตัวอย่างเช่น คุณสามารถเชื่อมโยงตารางลูกค้ากับตารางใบสั่งซื้อได้ ถ้าแต่ละตารางมีคอลัมน์ที่จัดเก็บ ID ลูกค้า ในตัวอย่าง ชื่อคอลัมน์จะเหมือนกัน แต่ชื่อนี้ไม่ใช่ข้อกําหนด แถวหนึ่งอาจเป็น CustomerID และ CustomerNumber อีกแถวหนึ่ง ตราบใดที่แถวทั้งหมดในตารางใบสั่งซื้อมี ID ที่ถูกเก็บไว้ในตารางลูกค้าด้วย

ในฐานข้อมูลเชิงสัมพันธ์ มีคีย์หลายชนิด โดยทั่วไปคีย์คือคอลัมน์ที่มีคุณสมบัติพิเศษ การทําความเข้าใจวัตถุประสงค์ของคีย์แต่ละแป้นสามารถช่วยให้คุณจัดการตัวแบบข้อมูลแบบหลายตารางที่ให้ข้อมูลกับรายงาน PivotTable, PivotChart หรือ Power View ได้

แม้ว่าจะมีคีย์หลายประเภท แต่สิ่งเหล่านี้เป็นสิ่งสําคัญที่สุดสําหรับจุดประสงค์ของเราที่นี่:

  • คีย์หลัก: ระบุแถวในตารางอย่างไม่ซ้ํากัน เช่น รหัสลูกค้าในตารางลูกค้า

  • คีย์สํารอง (หรือคีย์ตัวเลือก): คอลัมน์อื่นที่ไม่ใช่คีย์หลักที่ไม่ซ้ํากัน ตัวอย่างเช่น ตารางพนักงานอาจเก็บรหัสพนักงานและหมายเลขประกันสังคม ซึ่งทั้งสองหมายเลขจะไม่ซ้ํากัน

  • Foreign Key: คอลัมน์ที่อ้างอิงถึงคอลัมน์ที่ไม่ซ้ํากันในตารางอื่น เช่น รหัสลูกค้า ในตาราง ใบสั่งซื้อ ซึ่งอ้างอิงถึง รหัสลูกค้า ในตารางลูกค้า

ในตัวแบบข้อมูล คีย์หลักหรือคีย์สํารองจะเรียกว่าคอลัมน์ที่เกี่ยวข้อง ถ้าตารางมีทั้งคีย์หลักและคีย์สํารอง คุณสามารถใช้คีย์ใดคีย์หนึ่งเป็นพื้นฐานของความสัมพันธ์ของตารางได้ Foreign Key จะเรียกว่าคอลัมน์ต้นฉบับหรือเฉพาะคอลัมน์ ในตัวอย่างของเรา ความสัมพันธ์จะถูกกําหนดระหว่าง CustomerID ในตาราง ใบสั่งซื้อ (คอลัมน์) และ รหัสลูกค้า ในตาราง ลูกค้า (คอลัมน์การค้นหา) ถ้าคุณนําเข้าข้อมูลจากฐานข้อมูลเชิงสัมพันธ์ ตามค่าเริ่มต้น Excel จะเลือก Foreign Key จากตารางหนึ่งและคีย์หลักที่สอดคล้องกันจากอีกตารางหนึ่ง อย่างไรก็ตาม คุณสามารถใช้คอลัมน์ใดๆ ที่มีค่าที่ไม่ซ้ํากันสําหรับคอลัมน์การค้นหาได้

ความสัมพันธ์ระหว่างลูกค้าและใบสั่งซื้อเป็นความสัมพันธ์แบบหนึ่ง-ต่อ-กลุ่ม ลูกค้าทุกคนสามารถมีคําสั่งซื้อได้หลายรายการ แต่คําสั่งซื้อไม่สามารถมีลูกค้าหลายรายได้ ความสัมพันธ์ของตารางที่สําคัญอีกความสัมพันธ์หนึ่งต่อหนึ่ง ในตัวอย่างของเราที่นี่ ตาราง CustomerDiscounts ซึ่งกําหนดอัตราส่วนลดเดียวสําหรับลูกค้าแต่ละราย จะมีความสัมพันธ์แบบหนึ่ง-ต่อ-หนึ่งกับตารางลูกค้า

ตารางนี้แสดงความสัมพันธ์ระหว่างตารางสามตาราง (Customers, CustomerDiscounts และ Orders):

ความสัมพันธ์

ประเภท

คอลัมน์การค้นหา

คอลัมน์

Customers-CustomerDiscounts

หนึ่งต่อหนึ่ง

Customers.CustomerID

CustomerDiscounts.CustomerID

Customers-Orders

หนึ่งถึงจำนวนมาก

Customers.CustomerID

Orders.CustomerID

หมายเหตุ: ความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่มไม่ได้รับการสนับสนุนในตัวแบบข้อมูล ตัวอย่างของความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่มคือความสัมพันธ์โดยตรงระหว่างผลิตภัณฑ์และลูกค้า ซึ่งลูกค้าสามารถซื้อผลิตภัณฑ์จํานวนมากและผลิตภัณฑ์เดียวกันสามารถซื้อได้โดยลูกค้าหลายคน

หลังจากความสัมพันธ์ใดๆ ถูกสร้างขึ้นโดยทั่วไปแล้ว Excel จะต้องคํานวณสูตรใดๆ ที่ใช้คอลัมน์จากตารางในความสัมพันธ์ที่สร้างขึ้นใหม่ใหม่ การประมวลผลอาจใช้เวลาสักครู่ ขึ้นอยู่กับจํานวนข้อมูลและความซับซ้อนของความสัมพันธ์ สําหรับรายละเอียดเพิ่มเติม ให้ดูที่ คํานวณสูตรใหม่

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

ในมุมมองไดอะแกรม ความสัมพันธ์ที่ใช้งานอยู่จะเป็นเส้นทึบและเส้นที่ไม่ได้ใช้งานจะเป็นเส้นประ ตัวอย่างเช่น ใน AdventureWorksDW2012 DimDate จะมีคอลัมน์ DateKey ซึ่งเกี่ยวข้องกับคอลัมน์ที่แตกต่างกันสามคอลัมน์ในตาราง FactInternetSales: OrderDate, DueDate และ ShipDate ถ้าความสัมพันธ์ที่ใช้งานอยู่อยู่ระหว่าง DateKey และ OrderDate นั่นคือความสัมพันธ์เริ่มต้นในสูตร เว้นแต่คุณจะระบุเป็นอย่างอื่น

คุณสามารถสร้างความสัมพันธ์ได้เมื่อเป็นไปตามข้อกำหนดต่อไปนี้:

เกณฑ์

คำอธิบาย

ตัวระบุเฉพาะสำหรับแต่ละตาราง

แต่ละตารางต้องมีคอลัมน์เดียวที่ระบุแถวแต่ละแถวในตารางนั้นโดยไม่ซ้ํากัน คอลัมน์นี้มักเรียกว่าคีย์หลัก

คอลัมน์การค้นหาเฉพาะ

ค่าข้อมูลในคอลัมน์การค้นหาต้องไม่ซ้ํากัน กล่าวอีกนัยหนึ่งคอลัมน์ไม่สามารถมีรายการที่ซ้ํากันได้ ในตัวแบบข้อมูล ค่า Null และสตริงว่างจะเทียบเท่ากับค่าว่าง ซึ่งเป็นค่าข้อมูลที่แตกต่างกัน ซึ่งหมายความว่าคุณไม่สามารถมีค่า Null หลายค่าในคอลัมน์การค้นหาได้

ชนิดข้อมูลที่เข้ากันได้

ชนิดข้อมูลในคอลัมน์ต้นฉบับและคอลัมน์การค้นหาต้องเข้ากันได้ สําหรับข้อมูลเพิ่มเติมเกี่ยวกับชนิดข้อมูล ให้ดูที่ ชนิดข้อมูลที่สนับสนุนในตัวแบบข้อมูล

ในตัวแบบข้อมูล คุณไม่สามารถสร้างความสัมพันธ์ของตารางได้ถ้าคีย์เป็นคีย์ผสม นอกจากนี้ คุณยังถูกจํากัดให้สร้างความสัมพันธ์แบบหนึ่ง-ต่อ-หนึ่งและแบบหนึ่ง-ต่อ-กลุ่ม ชนิดความสัมพันธ์อื่นๆ ไม่ได้รับการสนับสนุน

คีย์ผสมและคอลัมน์การค้นหา

คีย์ผสมประกอบด้วยคอลัมน์มากกว่าหนึ่งคอลัมน์ ตัวแบบข้อมูลไม่สามารถใช้คีย์ผสมได้: ตารางต้องมีหนึ่งคอลัมน์ที่ระบุแต่ละแถวในตารางโดยไม่ซ้ํากันเสมอ ถ้าคุณนําเข้าตารางที่มีความสัมพันธ์ที่มีอยู่โดยยึดตามคีย์ผสม ตัวช่วยสร้างการนําเข้าตารางใน Power Pivot จะละเว้นความสัมพันธ์นั้นเนื่องจากไม่สามารถสร้างในตัวแบบได้

เมื่อต้องการสร้างความสัมพันธ์ระหว่างสองตารางที่มีหลายคอลัมน์ที่กําหนดคีย์หลักและคีย์นอก ก่อนอื่นให้รวมค่าเพื่อสร้างคอลัมน์คีย์เดียวก่อนที่จะสร้างความสัมพันธ์ คุณสามารถทําได้ก่อนที่คุณจะนําเข้าข้อมูล หรือโดยการสร้างคอลัมน์จากการคํานวณในตัวแบบข้อมูลโดยใช้ Add-in Power Pivot

ความสัมพันธ์แบบกลุ่มต่อกลุ่ม

ตัวแบบข้อมูลไม่สามารถมีความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่มได้ คุณไม่สามารถเพิ่มตารางเชื่อมต่อในตัวแบบได้ อย่างไรก็ตาม คุณสามารถใช้ฟังก์ชัน DAX เพื่อจําลองแบบความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่มได้

การรวมกับตัวเองและการวนรอบ

การรวมกับตัวเองไม่ได้รับอนุญาตในตัวแบบข้อมูล การรวมกับตัวเองเป็นความสัมพันธ์แบบเรียกใช้ซ้ําระหว่างตารางและตัวตารางเอง การรวมกับตัวเองมักจะใช้เพื่อกําหนดลําดับชั้นหลัก-รอง ตัวอย่างเช่น คุณสามารถรวมตารางพนักงานเข้ากับตัวตารางเองเพื่อสร้างลําดับชั้นที่แสดงสายการจัดการในธุรกิจ

Excel ไม่อนุญาตให้สร้างการวนรอบระหว่างความสัมพันธ์ในเวิร์กบุ๊ก กล่าวอีกนัยหนึ่งห้ามใช้ชุดความสัมพันธ์ต่อไปนี้

ตาราง 1, คอลัมน์ a ถึง ตาราง 2, คอลัมน์ f    

ตาราง 2, คอลัมน์ f ถึง ตาราง 3, คอลัมน์ n    

ตาราง 3, คอลัมน์ n ถึง ตาราง 1, คอลัมน์ a    

ถ้าคุณพยายามสร้างความสัมพันธ์ที่ทำให้มีการสร้างการวนรอบ อาจทำให้เกิดข้อผิดพลาดได้

หนึ่งในข้อดีของการนำเข้าข้อมูลโดยใช้ Add-in ของ Power Pivot ก็คือ ในบางครั้ง Power Pivot สามารถตรวจหาความสัมพันธ์และสร้างความสัมพันธ์ใหม่ในตัวแบบข้อมูลที่สร้างใน Excel ได้

เมื่อคุณนําเข้าหลายตาราง Power Pivot จะตรวจหาความสัมพันธ์ที่มีอยู่ระหว่างตารางโดยอัตโนมัติ นอกจากนี้ เมื่อคุณสร้าง PivotTable Power Pivot จะวิเคราะห์ข้อมูลในตาราง โดยตรวจพบความสัมพันธ์ที่เป็นไปได้ที่ยังไม่ได้กําหนด ไว้ และแนะนําคอลัมน์ที่เหมาะสมเพื่อรวมไว้ในความสัมพันธ์เหล่านั้น

อัลกอริทึมการตรวจหาจะใช้ข้อมูลเชิงสถิติเกี่ยวกับค่าและ Metadata ของคอลัมน์เพื่อทำการอนุมานเกี่ยวกับความน่าจะเป็นของความสัมพันธ์

  • ชนิดข้อมูลในคอลัมน์ที่เกี่ยวข้องทั้งหมดควรเข้ากันได้ สําหรับการตรวจหาอัตโนมัติ ชนิดข้อมูลจํานวนเต็มและข้อความเท่านั้นที่ได้รับการสนับสนุน สําหรับข้อมูลเพิ่มเติมเกี่ยวกับชนิดข้อมูล ให้ดูที่ ชนิดข้อมูลที่สนับสนุนในตัวแบบข้อมูล

  • เพื่อให้ตรวจพบความสัมพันธ์ได้สําเร็จ จํานวนของคีย์ที่ไม่ซ้ํากันในคอลัมน์การค้นหาต้องมากกว่าค่าในตารางในหลายด้าน กล่าวอีกนัยหนึ่ง คอลัมน์คีย์บนด้านต่างๆ ของความสัมพันธ์ต้องไม่มีค่าใดๆ ที่ไม่ได้อยู่ในคอลัมน์หลักของตารางการค้นหา ตัวอย่างเช่น สมมติว่าคุณมีตารางที่แสดงรายการผลิตภัณฑ์ที่มีรหัสผลิตภัณฑ์ (ตารางการค้นหา) และตารางยอดขายที่แสดงยอดขายสําหรับแต่ละผลิตภัณฑ์ (ด้านจํานวนมากของความสัมพันธ์) ถ้าระเบียนการขายของคุณมี ID ของผลิตภัณฑ์ที่ไม่มี ID ที่สอดคล้องกันในตารางผลิตภัณฑ์ ความสัมพันธ์นั้นจะไม่สามารถสร้างได้โดยอัตโนมัติ แต่คุณอาจสามารถสร้างด้วยตนเองได้ เมื่อต้องการให้ Excel ตรวจหาความสัมพันธ์ คุณต้องอัปเดตตารางการค้นหาผลิตภัณฑ์ด้วยรหัสของผลิตภัณฑ์ที่หายไปก่อน

  • ตรวจสอบให้แน่ใจว่าชื่อของคอลัมน์คีย์ในหลายด้านคล้ายกับชื่อของคอลัมน์คีย์ในตารางการค้นหา ชื่อไม่จําเป็นต้องเหมือนกันทุกประการ ตัวอย่างเช่น ในการตั้งค่าธุรกิจ คุณมักจะมีชุดรูปแบบบนชื่อของคอลัมน์ที่มีข้อมูลเดียวกันเป็นหลัก ได้แก่ Emp ID, EmployeeID, Employee ID, EMP_ID และอื่นๆ อัลกอริทึมจะตรวจหาชื่อที่คล้ายกัน และกําหนดความน่าจะเป็นที่สูงกว่าให้กับคอลัมน์เหล่านั้นที่มีชื่อเหมือนกันหรือตรงกับทุกประการ ดังนั้น เมื่อต้องการเพิ่มความน่าจะเป็นของการสร้างความสัมพันธ์ คุณสามารถลองเปลี่ยนชื่อคอลัมน์ในข้อมูลที่คุณนําเข้าไปยังสิ่งที่คล้ายกับคอลัมน์ในตารางที่มีอยู่ของคุณ ถ้า Excel พบความสัมพันธ์ที่เป็นไปได้หลายความสัมพันธ์ ก็จะไม่มีการสร้างความสัมพันธ์ขึ้น

ข้อมูลนี้อาจช่วยให้คุณเข้าใจว่าทําไมจึงไม่ตรวจพบความสัมพันธ์ทั้งหมด หรือวิธีที่การเปลี่ยนแปลงใน Metadata เช่น ชื่อเขตข้อมูลและชนิดข้อมูล สามารถปรับปรุงผลลัพธ์ของการตรวจหาความสัมพันธ์อัตโนมัติได้ สําหรับข้อมูลเพิ่มเติม ให้ดู แก้ไขปัญหาความสัมพันธ์

การตรวจหาอัตโนมัติสำหรับชุดที่มีชื่อ

ความสัมพันธ์จะไม่ถูกตรวจพบโดยอัตโนมัติระหว่างชุดที่มีชื่อและเขตข้อมูลที่เกี่ยวข้องใน PivotTable คุณสามารถสร้างความสัมพันธ์เหล่านี้ด้วยตนเองได้ ถ้าคุณต้องการใช้การตรวจหาความสัมพันธ์อัตโนมัติ ให้เอาชุดที่มีชื่อแต่ละชุดออก และเพิ่มแต่ละเขตข้อมูลจากชุดที่มีชื่อลงใน PivotTable โดยตรง

การอนุมานของความสัมพันธ์

ในบางกรณี ความสัมพันธ์ระหว่างตารางจะถูกเชื่อมโยงโดยอัตโนมัติ ตัวอย่างเช่น ถ้าคุณสร้างความสัมพันธ์ระหว่างตารางสองชุดแรกด้านล่าง จะมีการอนุมานว่ามีความสัมพันธ์อยู่ระหว่างอีกสองตารางและความสัมพันธ์จะถูกสร้างขึ้นโดยอัตโนมัติ

ผลิตภัณฑ์ และ ประเภท -- สร้างด้วยตนเอง

ประเภท และ ประเภทย่อย -- สร้างด้วยตนเอง

ผลิตภัณฑ์ และ ประเภทย่อย -- ความสัมพันธ์ถูกอ้างถึง

เพื่อให้ความสัมพันธ์ถูกเชื่อมโยงโดยอัตโนมัติ ความสัมพันธ์จะต้องไปในทิศทางเดียวดังที่แสดงด้านบน ถ้าความสัมพันธ์เริ่มต้นอยู่ระหว่างกัน ตัวอย่างเช่น การขายและผลิตภัณฑ์ และ การขายและลูกค้า ความสัมพันธ์จะไม่ถูกอนุมาน ทั้งนี้เนื่องจากความสัมพันธ์ระหว่างผลิตภัณฑ์และลูกค้าเป็นความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่ม

ต้องการความช่วยเหลือเพิ่มเติมหรือไม่

ต้องการตัวเลือกเพิ่มเติมหรือไม่

สํารวจสิทธิประโยชน์ของการสมัครใช้งาน เรียกดูหลักสูตรการฝึกอบรม เรียนรู้วิธีการรักษาความปลอดภัยอุปกรณ์ของคุณ และอื่นๆ

ชุมชนช่วยให้คุณถามและตอบคําถาม ให้คําติชม และรับฟังจากผู้เชี่ยวชาญที่มีความรู้มากมาย