เพิ่มพลังให้กับการวิเคราะห์ข้อมูลของคุณโดยการสร้างความสัมพันธ์จากตารางต่างๆ ความสัมพันธ์คือการเชื่อมต่อระหว่างสองตารางที่มีข้อมูล: หนึ่งคอลัมน์ในแต่ละตารางเป็นพื้นฐานสําหรับความสัมพันธ์ เมื่อต้องการดูว่าเหตุใดความสัมพันธ์จึงมีประโยชน์ ให้จินตนาการว่าคุณติดตามข้อมูลสําหรับคําสั่งซื้อของลูกค้าในธุรกิจของคุณ คุณสามารถติดตามข้อมูลทั้งหมดในตารางเดียวที่มีโครงสร้างดังนี้:
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 โดยตรง
การอนุมานของความสัมพันธ์
ในบางกรณี ความสัมพันธ์ระหว่างตารางจะถูกเชื่อมโยงโดยอัตโนมัติ ตัวอย่างเช่น ถ้าคุณสร้างความสัมพันธ์ระหว่างตารางสองชุดแรกด้านล่าง จะมีการอนุมานว่ามีความสัมพันธ์อยู่ระหว่างอีกสองตารางและความสัมพันธ์จะถูกสร้างขึ้นโดยอัตโนมัติ
ผลิตภัณฑ์ และ ประเภท -- สร้างด้วยตนเอง
ประเภท และ ประเภทย่อย -- สร้างด้วยตนเอง
ผลิตภัณฑ์ และ ประเภทย่อย -- ความสัมพันธ์ถูกอ้างถึง
เพื่อให้ความสัมพันธ์ถูกเชื่อมโยงโดยอัตโนมัติ ความสัมพันธ์จะต้องไปในทิศทางเดียวดังที่แสดงด้านบน ถ้าความสัมพันธ์เริ่มต้นอยู่ระหว่างกัน ตัวอย่างเช่น การขายและผลิตภัณฑ์ และ การขายและลูกค้า ความสัมพันธ์จะไม่ถูกอนุมาน ทั้งนี้เนื่องจากความสัมพันธ์ระหว่างผลิตภัณฑ์และลูกค้าเป็นความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่ม