เพิ่มพลังให้กับการวิเคราะห์ข้อมูลของคุณด้วยการสร้างความสัมพันธ์ที่แตกต่างจากตารางต่างๆ ความสัมพันธ์เป็นการเชื่อมต่อระหว่างสองตารางที่มีข้อมูล: หนึ่งคอลัมน์ในแต่ละตารางเป็นพื้นฐานในความสัมพันธ์ เมื่อต้องการดูว่าเหตุใดความสัมพันธ์จึงมีประโยชน์ ให้สมมติว่าคุณติดตามข้อมูลใบสั่งซื้อของลูกค้าในธุรกิจของคุณ คุณสามารถติดตามข้อมูลทั้งหมดในตารางเดียวที่มีโครงสร้างดังนี้:
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
ถ้าคุณใช้ Add-in Power Pivot เพื่อนําเข้าตารางจากฐานข้อมูลเดียวกัน Power Pivot จะสามารถตรวจหาความสัมพันธ์ระหว่างตารางต่างๆ โดยยึดตามคอลัมน์ใน [วงเล็บเหลี่ยม] และสามารถสร้างความสัมพันธ์เหล่านี้ในตัวแบบข้อมูลที่สร้างเบื้องหลังได้ For more information, see Automatic Detection and Inference of Relationships in this article. ถ้าคุณนําเข้าตารางจากหลายแหล่งข้อมูล คุณสามารถสร้างความสัมพันธ์ด้วยตนเองตามที่อธิบายไว้ใน สร้างความสัมพันธ์ระหว่างตารางสองตาราง
ความสัมพันธ์จะยึดตามคอลัมน์ในแต่ละตารางที่มีข้อมูลเดียวกัน ตัวอย่างเช่น คุณสามารถเชื่อมโยงตารางลูกค้า กับตาราง ใบสั่งซื้อ ได้ ถ้าแต่ละตารางมีคอลัมน์ที่จัดเก็บ รหัสลูกค้า ในตัวอย่าง ชื่อคอลัมน์เหมือนกัน แต่ไม่ใช่ความต้องการ แถวหนึ่งอาจเป็น CustomerID และ CustomerNumber อื่น ตราบใดที่แถวทั้งหมดในตารางใบสั่งซื้อมี ID ที่จัดเก็บไว้ในตารางลูกค้าด้วย
ในฐานข้อมูลเชิงสัมพันธ์ มีคีย์อยู่หลายชนิด โดยปกติแล้ว แป้นคือคอลัมน์ที่มีคุณสมบัติพิเศษ การความเข้าใจวัตถุประสงค์ของแต่ละคีย์จะช่วยให้คุณจัดการตัวแบบข้อมูลแบบหลายตารางที่ให้ข้อมูลกับรายงาน PivotTable, PivotChart Power Viewได้
แม้ว่าจะมีคีย์อยู่หลายชนิด แต่สิ่งต่อไปนี้มีความสําคัญที่สุดต่อจุดประสงค์ของเราที่นี่:
-
คีย์หลัก: ระบุแถวในตารางแบบไม่ระบุชื่อ เช่น CustomerIDในตาราง ลูกค้า
-
คีย์อื่น (หรือคีย์เพิ่มเติม): คอลัมน์อื่นที่ไม่ใช่คีย์หลักซึ่งไม่ซ้้ากัน ตัวอย่างเช่น ตารางพนักงานอาจเก็บรหัสพนักงานและหมายเลขประกันสังคม ซึ่งทั้งสองหมายเลขไม่ไม่เหมือนกัน
-
Foreign Key:คอลัมน์ที่อ้างอิงคอลัมน์เฉพาะในตารางอื่น เช่นรหัสลูกค้า ในตารางใบสั่งซื้อ ซึ่งอ้างอิงรหัสลูกค้าในตารางลูกค้า
ใน ตัวแบบข้อมูล คีย์หลักหรือคีย์อื่นจะเรียกว่าคอลัมน์ที่เกี่ยวข้อง ถ้าตารางมีทั้งคีย์หลักและคีย์อื่น คุณสามารถใช้คีย์ใดคีย์หนึ่งเป็นพื้นฐานของความสัมพันธ์ของตารางได้ Foreign Key หมายถึงคอลัมน์ต้นฉบับหรือเพียงคอลัมน์ ในตัวอย่างของเรา ความสัมพันธ์จะถูกกําหนดระหว่างCustomerIDในตารางใบสั่งซื้อ(คอลัมน์) และรหัสลูกค้าในตารางลูกค้า (คอลัมน์การค้นหา) ถ้าคุณนําเข้าข้อมูลจากฐานข้อมูลเชิงสัมพันธ์ ตามค่าเริ่มต้น Excel Foreign Key จากตารางหนึ่งและคีย์หลักที่สอดคล้องกันจากตารางอื่น อย่างไรก็ตาม คุณสามารถใช้คอลัมน์ใดๆ ที่มีค่าเฉพาะของคอลัมน์การค้นหาได้
ความสัมพันธ์ระหว่างลูกค้าและใบสั่งซื้อเป็นความสัมพันธ์แบบหนึ่ง-ต่อ-กลุ่ม ลูกค้าทุกคนสามารถมีใบสั่งซื้อได้หลายใบ แต่ใบสั่งซื้อหนึ่งรายการไม่สามารถมีลูกค้าได้หลายราย ความสัมพันธ์ของตารางที่สําคัญอีกรายการหนึ่งคือแบบหนึ่ง-ต่อ-หนึ่ง ในตัวอย่างของเรา ตาราง CustomerDiscounts ซึ่งกําหนดอัตราส่วนลดเดียวของลูกค้าแต่ละคน มีความสัมพันธ์แบบหนึ่ง-ต่อ-หนึ่งกับตารางลูกค้า
ตารางนี้แสดงความสัมพันธ์ระหว่างตารางสามตาราง (ลูกค้า, CustomerDiscountsและ ใบสั่งซื้อ):
ความสัมพันธ์ |
ประเภท |
คอลัมน์การค้นหา |
คอลัมน์ |
---|---|---|---|
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 หลายค่าในคอลัมน์การค้นหาได้ |
ชนิดข้อมูลที่เข้ากันได้ |
ชนิดข้อมูลในคอลัมน์ต้นฉบับและคอลัมน์การค้นหาต้องเข้ากันได้ For more information about data types, see Data types supported in Data Models. |
ใน ตัวแบบข้อมูล คุณไม่สามารถสร้างความสัมพันธ์ของตารางได้ ถ้าคีย์เป็นคีย์ผสม คุณยังถูกห้ามให้สร้างความสัมพันธ์แบบหนึ่ง-ต่อ-หนึ่งและหนึ่ง-ต่อ-กลุ่ม ความสัมพันธ์ชนิดอื่นๆ ไม่ได้รับการสนับสนุน
คีย์ผสมและคอลัมน์การค้นหา
คีย์ผสมประกอบด้วยคอลัมน์มากกว่าหนึ่งคอลัมน์ ตัวแบบข้อมูลไม่สามารถใช้คีย์ผสมได้: ตารางต้องมีคอลัมน์หนึ่งคอลัมน์ที่ระบุแต่ละแถวในตารางไม่เหมือนกันเสมอ ถ้าคุณนําเข้าตารางที่มีความสัมพันธ์ที่มีอยู่โดยยึดตามคีย์ผสม ตัวช่วยสร้างการนําเข้าตารางใน Power Pivot จะละเว้นความสัมพันธ์นั้นเนื่องจากความสัมพันธ์นั้นสามารถสร้างในรูปแบบไม่ได้
เมื่อต้องการสร้างความสัมพันธ์ระหว่างตารางสองตารางที่มีหลายคอลัมน์ซึ่งระบุคีย์หลักและคีย์ต่างประเทศ ก่อนอื่นให้รวมค่าเพื่อสร้างคอลัมน์คีย์เดียวก่อนสร้างความสัมพันธ์ คุณสามารถสิ่งนี้ได้ก่อนที่คุณจะนําเข้าข้อมูล หรือโดยการสร้างคอลัมน์ที่คํานวณใน ตัวแบบข้อมูล โดยใช้ Add-in Power Pivot
ความสัมพันธ์แบบกลุ่มต่อกลุ่ม
ตัวแบบข้อมูลไม่สามารถมีความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่มได้ คุณไม่สามารถเพิ่มตารางเชื่อมต่อในรูปแบบ อย่างไรก็ตาม คุณสามารถใช้ฟังก์ชัน DAX เพื่อโมเดลความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่มได้
การรวมกับตัวเองและการวนรอบ
การรวมกับตัวเองไม่ได้รับอนุญาตจากตัวแบบข้อมูล การรวมกับตัวเองเป็นความสัมพันธ์แบบเป็นกระแสๆ ระหว่างตารางและตัวตารางเอง การรวมกับตัวเองมักจะถูกใช้เพื่อกําหนดล.ก. ป.แม่-ลูก For example, you could join an Employees table to itself to produce a hierarchy that shows the management chain at a business.
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 ข้อมูลในตาราง It detects possible relationships that have not been defined, and suggests appropriate columns to include in those relationships.
อัลกอริทึมการตรวจหาจะใช้ข้อมูลเชิงสถิติเกี่ยวกับค่าและ Metadata ของคอลัมน์เพื่อทำการอนุมานเกี่ยวกับความน่าจะเป็นของความสัมพันธ์
-
ชนิดข้อมูลในคอลัมน์ที่เกี่ยวข้องทั้งหมดควรเข้ากันได้ ในการตรวจหาอัตโนมัติ เฉพาะชนิดข้อมูลจํานวนเต็มและข้อความเท่านั้นที่ได้รับการสนับสนุน For more information about data types, see Data types supported in Data Models.
-
เพื่อให้ตรวจพบความสัมพันธ์เสร็จเรียบร้อยแล้ว จํานวนคีย์เฉพาะในคอลัมน์การค้นหาต้องมากกว่าค่าในตารางในหลายด้าน กล่าวคือ คอลัมน์คีย์บนด้านต่างๆ ของความสัมพันธ์จะต้องไม่ประกอบด้วยค่าใดๆ ที่ไม่อยู่ในคอลัมน์คีย์ของตารางการค้นหา ตัวอย่างเช่น สมมติว่าคุณมีตารางที่แสดงรายการผลิตภัณฑ์ที่มี ID ผลิตภัณฑ์ (ตารางการค้นหา) และตารางยอดขายที่แสดงรายการยอดขายของแต่ละผลิตภัณฑ์ (ด้านต่างๆ ของความสัมพันธ์) ถ้าระเบียนยอดขายของคุณมี ID ของผลิตภัณฑ์ที่ไม่มี ID ที่สอดคล้องกันในตารางผลิตภัณฑ์ ความสัมพันธ์ไม่สามารถสร้างโดยอัตโนมัติ แต่คุณอาจสร้างด้วยตนเองได้ เมื่อต้องการExcelความสัมพันธ์คุณต้องอัปเดตตารางค้นหาผลิตภัณฑ์ด้วย ID ของผลิตภัณฑ์ที่หายไปก่อน
-
ตรวจสอบให้แน่ใจว่าชื่อของคอลัมน์หลักในหลายด้านนั้นคล้ายกับชื่อของคอลัมน์หลักในตารางค้นหา ชื่อไม่เหมือนกันทั้งหมด ตัวอย่างเช่น ในการตั้งค่าธุรกิจ คุณมักจะมีชุดรูปแบบบนชื่อของคอลัมน์ที่มีข้อมูลเดียวกันเป็นหลัก ได้แก่ Emp ID, EmployeeID, ID พนักงาน, EMP_IDและอื่นๆ อัลกอริทึมจะตรวจหาชื่อที่คล้ายกันและกําหนดความน่าจะเป็นที่สูงกว่าให้กับคอลัมน์ที่มีชื่อคล้ายกันหรือตรงกันทุกชื่อ ดังนั้น เมื่อต้องการเพิ่มความน่าจะเป็นในการสร้างความสัมพันธ์ คุณสามารถลองเปลี่ยนชื่อคอลัมน์ในข้อมูลที่คุณนําเข้าไปยังข้อมูลที่คล้ายกับคอลัมน์ในตารางที่มีอยู่ของคุณ ถ้าคุณExcelความสัมพันธ์ที่เป็นไปได้หลายรายการ แล้วความสัมพันธ์นั้นจะไม่สร้างความสัมพันธ์
ข้อมูลนี้อาจช่วยให้คุณเข้าใจถึงสาเหตุที่ตรวจไม่พบความสัมพันธ์ทั้งหมด หรือวิธีที่การเปลี่ยนแปลงใน Metadata เช่น ชื่อเขตข้อมูลและชนิดข้อมูล สามารถปรับปรุงผลลัพธ์ของการตรวจหาความสัมพันธ์อัตโนมัติได้ For more information, see Troubleshoot Relationships.
การตรวจหาอัตโนมัติสำหรับชุดที่มีชื่อ
ความสัมพันธ์จะไม่ถูกตรวจพบโดยอัตโนมัติระหว่างชุดที่มีชื่อและเขตข้อมูลที่เกี่ยวข้องใน PivotTable คุณสามารถสร้างความสัมพันธ์เหล่านี้ได้ด้วยตนเอง ถ้าคุณต้องการใช้การตรวจหาความสัมพันธ์อัตโนมัติ ให้เอาชุดที่มีชื่อออกและเพิ่มแต่ละเขตข้อมูลจากชุดที่มีชื่อไปยัง PivotTable โดยตรง
การอนุมานของความสัมพันธ์
ในบางกรณี ความสัมพันธ์ระหว่างตารางจะเชื่อมโยงกันโดยอัตโนมัติ ตัวอย่างเช่น ถ้าคุณสร้างความสัมพันธ์ระหว่างตารางสองชุดแรกด้านล่าง ความสัมพันธ์จะถูกอนุมานให้อยู่ระหว่างอีกสองตารางหนึ่ง และความสัมพันธ์จะถูกสร้างโดยอัตโนมัติ
ผลิตภัณฑ์ และ ประเภท -- สร้างด้วยตนเอง
ประเภท และ ประเภทย่อย -- สร้างด้วยตนเอง
ผลิตภัณฑ์ และ ประเภทย่อย -- ความสัมพันธ์ถูกอ้างถึง
เพื่อให้ความสัมพันธ์มีการเชื่อมโยงโดยอัตโนมัติ ความสัมพันธ์จะต้องไปในทิศทางเดียว ดังที่แสดงไว้ด้านบน ถ้าความสัมพันธ์เริ่มต้นอยู่ระหว่างนั้น ตัวอย่างเช่น การขายและผลิตภัณฑ์ และ การขายและลูกค้า ความสัมพันธ์จะไม่ถูกอนุมาน เนื่องจากความสัมพันธ์ระหว่าง ผลิตภัณฑ์ และ ลูกค้า เป็นความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่ม