คุณเคยใช้ VLOOKUP เพื่อดึงคอลัมน์จากตารางหนึ่งลงในอีกตารางหนึ่งหรือไม่ ตอนนี้Excelตัวแบบข้อมูลที่มีอยู่แล้วภายใน VLOOKUP ล้าสมัย คุณสามารถสร้างความสัมพันธ์ระหว่างตารางข้อมูลสองตารางโดยยึดตามข้อมูลที่ตรงกันในแต่ละตาราง จากนั้น คุณสามารถสร้างแผ่นงานPower View PivotTable และรายงานอื่นๆ ที่มีเขตข้อมูลจากแต่ละตาราง แม้ว่าตารางจะมาจากแหล่งข้อมูลที่ต่างกัน ตัวอย่างเช่น ถ้าคุณมีข้อมูลยอดขายของลูกค้า คุณอาจต้องการนําเข้าและเชื่อมโยงข้อมูลตัวแสดงเวลาเพื่อ วิเคราะห์รูปแบบ การขายตามปีและเดือน
ตารางทั้งหมดในเวิร์กบุ๊กจะแสดงเป็นรายการเขตข้อมูล PivotTable และ Power View
เมื่อคุณนําเข้าตารางที่เกี่ยวข้องจากฐานข้อมูลเชิงสัมพันธ์ Excelมักจะสามารถสร้างความสัมพันธ์เหล่านั้นใน ตัวแบบข้อมูล ซึ่งสร้างอยู่เบื้องหลังได้ For all other cases, you'll need to create relationships manually.
-
ตรวจสอบให้แน่ใจว่าเวิร์กบุ๊กมีอย่างน้อยสองตาราง และแต่ละตารางมีคอลัมน์ที่สามารถแมปกับคอลัมน์ในตารางอื่นได้
-
เลือกเลือกอย่างใดอย่าง หนึ่งต่อไปนี้ จัดรูปแบบข้อมูลเป็นตาราง หรือนํา เข้าข้อมูลภายนอกเป็นตาราง ในเวิร์กชีตใหม่
-
ให้ชื่อที่มีความหมายในแต่ละตารางได้ โดยใน เครื่องมือตารางให้คลิก ออกแบบ > ชื่อตาราง > ใส่ชื่อ
-
ตรวจสอบคอลัมน์ในตารางใดตารางหนึ่งว่ามีค่าข้อมูลเฉพาะโดยไม่มีรายการที่คัดลอก Excelจะสร้างความสัมพันธ์ได้เท่านั้น ถ้าคอลัมน์หนึ่งมีค่าเฉพาะอยู่
ตัวอย่างเช่น ในการเชื่อมโยงยอดขายของลูกค้าเข้ากับตัวแสดงเวลา ทั้งสองตารางจะต้องระบุวันที่ในรูปแบบเดียวกัน (ตัวอย่างเช่น 1/1/2555) และต้องมีอย่างน้อยหนึ่งตาราง (ตัวแสดงเวลา) แสดงวันที่แต่ละวันเพียงครั้งเดียวภายในคอลัมน์
-
คลิก ข้อมูล > ความสัมพันธ์
ถ้า ความสัมพันธ์ เป็นสีเทา แสดงว่าเวิร์กบุ๊กของคุณมีเพียงหนึ่งตาราง
-
ในกล่องโต้ตอบ จัดการความสัมพันธ์ ให้คลิก ใหม่
-
ในกล่อง สร้าง ความสัมพันธ์ ให้คลิกลูกศรของตาราง แล้วเลือกตารางจากรายการ ในความสัมพันธ์แบบหนึ่ง-ต่อ-กลุ่ม ตารางนี้ควรจะอยู่หลายด้าน เมื่อใช้ตัวอย่างลูกค้าของเราและตัวแสดงเวลา คุณจะต้องเลือกตารางยอดขายของลูกค้าก่อน เนื่องจากยอดขายมากมายมีแนวโน้มที่จะเกิดขึ้นในแต่ละวัน
-
For Column (Foreign), select the column that contains the data that is related to Related Column (Primary). ตัวอย่างเช่น ถ้าคุณมีคอลัมน์วันที่ในทั้งสองตาราง คุณจะเลือกคอลัมน์นั้นในตอนนี้
-
สำหรับ ตารางที่เกี่ยวข้อง ให้เลือกตารางที่มีคอลัมน์ของข้อมูลอย่างน้อยหนึ่งคอลัมน์ ซึ่งสัมพันธ์กับตารางที่คุณเพิ่งเลือกใน ตาราง
-
สำหรับ คอลัมน์ที่เกี่ยวข้อง (หลัก) ให้เลือกคอลัมน์ที่มีค่าไม่ซ้ำซึ่งตรงกับค่าในคอลัมน์ที่คุณเลือกไว้ใน คอลัมน์
-
คลิก ตกลง
เพิ่มเติมเกี่ยวกับความสัมพันธ์ระหว่างตารางต่างๆ ใน Excel
บันทึกย่อเกี่ยวกับความสัมพันธ์
-
คุณจะทราบว่ามีความสัมพันธ์อยู่หรือไม่ เมื่อคุณลากเขตข้อมูลจากตารางต่างๆ ลงในรายการเขตข้อมูล PivotTable ถ้าคุณไม่ได้รับพร้อมท์ให้สร้างความสัมพันธ์ Excelมีข้อมูลความสัมพันธ์ที่ต้องใช้ในการเชื่อมโยงข้อมูลอยู่แล้ว
-
การสร้างความสัมพันธ์จะคล้ายกับการใช้ VLOOKUPs โดยคุณต้องมีคอลัมน์ที่มีข้อมูลที่ตรงกัน Excelสามารถอ้างอิงโยงแถวในตารางหนึ่งกับแถวของอีกตารางหนึ่งได้ ในตัวอย่างตัวแสดงเวลา ตารางลูกค้าต้องมีค่าวันที่ที่มีอยู่ในตารางตัวแสดงเวลาด้วย
-
ในรูปแบบข้อมูล ความสัมพันธ์ของตารางอาจเป็นแบบหนึ่ง-ต่อ-หนึ่ง (ซึ่งแต่ละคนจะเป็นผู้นั่งเป็นคนเดียว) หรือแบบหนึ่ง-ต่อ-กลุ่ม (แต่ละเที่ยวบินมีผู้เป็นนักท่องเที่ยวหลายคน) แต่ไม่ใช่แบบกลุ่มต่อกลุ่ม ความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่มจะส่งผลให้เกิดข้อผิดพลาดการขึ้นต่อกันแบบวงกลม เช่น "ตรวจพบการขึ้นต่อกันแบบวงกลม" ข้อผิดพลาดนี้จะเกิดขึ้นถ้าคุณสร้างการเชื่อมต่อโดยตรงระหว่างสองตารางที่เป็นการเชื่อมต่อแบบกลุ่ม-ต่อ-กลุ่ม หรือการเชื่อมต่อทางอ้อม (สายการเชื่อมโยงของความสัมพันธ์ของตารางที่เป็นแบบหนึ่ง-ต่อ-กลุ่มภายในความสัมพันธ์แต่ละรายการ แต่แบบกลุ่ม-ต่อ-กลุ่ม เมื่อดูสิ้นสุด อ่านเพิ่มเติมเกี่ยวกับ ความสัมพันธ์ระหว่างตารางต่างๆ ใน ตัวแบบข้อมูล
-
ชนิดข้อมูลในสองคอลัมน์ต้องเข้ากันได้ ดูรายละเอียดที่ ชนิดข้อมูลExcelตัวแบบข้อมูล
-
วิธีอื่นๆ ในการสร้างความสัมพันธ์ที่อาจใช้งานได้ง่ายยิ่งขึ้น โดยเฉพาะอย่างยิ่ง ถ้าคุณไม่แน่ใจว่าจะใช้คอลัมน์ใด ให้ดูที่สร้างความสัมพันธ์ในมุมมองไดอะแกรมใน Power Pivot
ตัวอย่าง: เชื่อมโยงข้อมูลตัวแสดงเวลากับข้อมูลเที่ยวบินของสายการบิน
คุณสามารถเรียนรู้เกี่ยวกับความสัมพันธ์ของตารางและตัวแสดงเวลาได้โดยใช้ข้อมูลฟรีบน Microsoft Azure Marketplace ชุดข้อมูลเหล่านี้บางชุดมีขนาดใหญ่มาก ซึ่งต้องมีการเชื่อมต่ออินเทอร์เน็ตความเร็วสูงเพื่อเสร็จสิ้นการดาวน์โหลดข้อมูลในระยะเวลาที่เหมาะสม
-
เริ่ม Add-in PowerPivot ใน Microsoft Excel และเปิดหน้าต่าง PowerPivot
-
คลิกรับข้อมูล>จากบริการ>จาก Microsoft Azure Marketplace โฮมเพจ Microsoft Azure Marketplace จะเปิดขึ้นในตัวช่วยสร้างการนําเข้าตาราง
-
ภายใต้ Price ให้คลิก Free
-
ภายใต้ Category ให้คลิก Science & Statistics
-
ค้นหาDateStreamแล้วคลิก สมัครสมาชิก
-
ใส่บัญชี Microsoft ของคุณ แล้วคลิกลงชื่อเข้าใช้ ตัวอย่างของข้อมูลควรปรากฏในหน้าต่าง
-
เลื่อนไปที่ด้านล่าง แล้วคลิก Select Query
-
คลิก Next
-
เลือก BasicCalendarUS แล้วคลิก เสร็จสิ้น เพื่อนําเข้าข้อมูล ด้วยการเชื่อมต่ออินเทอร์เน็ตความเร็วสูง การนําเข้าควรใช้เวลาประมาณหนึ่งนาที เมื่อเสร็จแล้ว คุณควรจะเห็นรายงานสถานะของแถวที่ถ่ายโอน 73,414 แถว คลิก ปิด
-
คลิก รับข้อมูลภายนอก > จากบริการข้อมูล > จาก Microsoft Azure Marketplace เพื่อนำเข้าชุดข้อมูลชุดที่สอง
-
ภายใต้ Type ให้คลิก Data
-
ภายใต้ Price ให้คลิก Free
-
ค้นหา US Air Carrier Flight Delays และคลิก Select
-
เลื่อนไปที่ด้านล่าง แล้วคลิก Select Query
-
คลิก Next
-
คลิก เสร็จสิ้น เพื่อนำเข้าข้อมูล ด้วยการเชื่อมต่ออินเทอร์เน็ตความเร็วสูง การนําเข้าอาจใช้เวลา 15 นาที เมื่อเสร็จแล้ว คุณควรจะเห็นรายงานสถานะของแถวที่ถ่ายโอน 2,427,284 แถว คลิก ปิด ตอนนี้คุณควรมีสองตารางในโมเดลข้อมูล เมื่อต้องการสร้างความสัมพันธ์ เราต้องการคอลัมน์ที่เข้ากันได้ในแต่ละตาราง
-
โปรดสังเกตว่า DateKey ใน BasicCalendarUS อยู่ในรูปแบบ 1/1/2012 12:00:00 AM ตาราง On_Time_Performance ยังมีคอลัมน์วันที่เวลา FlightDateซึ่งมีค่าที่ระบุในรูปแบบเดียวกัน: 1/1/2012 12:00:00 AM คอลัมน์สองคอลัมน์มีข้อมูลที่ตรงกัน ชนิดข้อมูลเดียวกัน และมีคอลัมน์อย่างน้อยหนึ่งคอลัมน์ (DateKey) จะมีเฉพาะค่าเฉพาะเท่านั้น ในหลายขั้นตอนถัดไป คุณจะใช้คอลัมน์เหล่านี้เพื่อสร้างความสัมพันธ์ระหว่างตาราง
-
ในหน้าต่าง Power Pivot ให้คลิก PivotTable เพื่อสร้าง PivotTable ในเวิร์กชีตใหม่หรือเวิร์กชีตที่มีอยู่แล้ว
-
ในรายการเขตข้อมูล ให้ขยาย On_Time_PerformanceArrDelayMinutes เพื่อเพิ่มลงในพื้นที่ ค่า ใน PivotTable คุณควรจะเห็นจํานวนเวลารวมของเที่ยวบินล่าช้าตามที่วัดเป็นนาที
-
ขยาย BasicCalendarUS แล้วคลิก MonthInCalendar เพื่อเพิ่มลงในพื้นที่แถว
-
โปรดสังเกตว่า ในตอนนี้ PivotTable จะแสดงรายการแบบเดือน แต่ผลรวมของนาทีจะเหมือนกันทุกเดือน การทําซ้ํา ค่าที่เหมือนกันจะระบุความสัมพันธ์ที่ต้องใช้
-
ในรายการเขตข้อมูล ใน "อาจต้องใช้ความสัมพันธ์ระหว่างตาราง" ให้คลิก สร้าง
-
ในตารางที่เกี่ยวข้อง ให้เลือก On_Time_Performance และในคอลัมน์ที่เกี่ยวข้อง (หลัก) ให้เลือก FlightDate
-
ในตาราง ให้เลือกBasicCalendarUSและในคอลัมน์ (Foreign)ให้เลือก DateKeyคลิกตกลง เพื่อสร้างความสัมพันธ์
-
โปรดสังเกตว่า ในตอนนี้ผลรวมของนาทีที่เที่ยวบินล่าช้าจะแตกต่างกันไปในแต่ละเดือน
-
ใน BasicCalendarUS ให้ลาก YearKey ไปยังพื้นที่แถวเหนือ MonthInCalendar
ขณะนี้ คุณสามารถแบ่งความล่าช้าได้ตามปีและเดือน หรือตามค่าอื่นๆ ในปฏิทิน
เคล็ดลับ: ตามค่าเริ่มต้น เดือนจะแสดงรายการเรียงตามตัวอักษร ด้วยการใช้ add-in Power Pivot คุณสามารถเปลี่ยนการเรียงล <4> <4>เพื่อให้เดือนปรากฏตามล <4> <4> <4>เวลาได้
-
ตรวจสอบให้แน่ใจว่า ตารางBasicCalendarUS เปิดอยู่ในหน้าต่าง Power Pivot
-
บน หน้าแรกของตาราง ให้คลิก เรียงลำดับตามคอลัมน์.
-
ใน เรียงลำดับ ให้เลือก MonthInCalendar
-
ใน ตาม ให้เลือก MonthOfYear.
ในตอนนี้ PivotTable จะเรียงล.กประเภทแต่ละเดือน-ปีผสม (ตุลาคม 2554, พฤศจิกายน 2554) ตามจํานวนเดือนภายในปี (10, 11) การเปลี่ยนการเรียงล.ก. เป็นเรื่องง่ายเนื่องจากตัวดึงข้อมูล DateStream มีคอลัมน์ที่จําเป็นทั้งหมดเพื่อใช้งานสถานการณ์นี้ ถ้าคุณใช้ตารางตัวแสดงเวลาอื่น ขั้นตอนของคุณจะแตกต่างออกไป
"อาจจำเป็นต้องมีความสัมพันธ์ระหว่างตาราง"
เมื่อคุณเพิ่มเขตข้อมูลลงใน PivotTable คุณจะได้รับการแจ้งให้ทราบถ้าจำเป็นต้องมีความสัมพันธ์ของตารางในเขตข้อมูลที่คุณเลือกใน PivotTable
แม้ว่าExcelจะสามารถบอกคุณเมื่อต้องมีความสัมพันธ์ แต่ไม่สามารถบอกคุณได้ว่าจะใช้ตารางและคอลัมน์ใด หรือความสัมพันธ์ของตารางเป็นไปได้หรือไม่ ลองปฏิบัติตามขั้นตอนเหล่านี้เพื่อรับคําตอบที่คุณต้องการ
ขั้นตอนที่ 1: กำหนดว่าตารางใดที่จะระบุในความสัมพันธ์
ถ้าโมเดลของคุณมีตารางเพียงไม่กี่ตาราง อาจเห็นได้ในทันทีว่าตารางใดที่คุณต้องใช้ แต่ในโมเดลขนาดใหญ่ คุณอาจใช้ความช่วยเหลือบางอย่างได้ วิธีหนึ่งคือการใช้มุมมองไดอะแกรมใน Add-in Power Pivot มุมมองไดอะแกรมมีการแสดงภาพของตารางทั้งหมดในตัวแบบข้อมูล เมื่อใช้มุมมองไดอะแกรม คุณสามารถระบุว่าตารางใดแยกจากส่วนที่เหลือของตัวแบบได้อย่างรวดเร็ว
หมายเหตุ: คุณสามารถสร้างความสัมพันธ์ที่ไม่ชัดเจนซึ่งไม่ถูกต้องเมื่อใช้ในรายงาน PivotTable หรือรายงานการPower Viewออก สมมติว่าตารางของคุณทั้งหมดเกี่ยวข้องกันในบางวิธีกับตารางอื่นๆ ในรูปแบบข้อมูล แต่เมื่อคุณพยายามรวมเขตข้อมูลจากตารางต่างๆ คุณจะได้รับข้อความ "อาจต้องใช้ความสัมพันธ์ระหว่างตาราง" สาเหตุที่เป็นไปได้มากที่สุดคือ คุณพบความสัมพันธ์แบบกลุ่ม-ต่อ-กลุ่ม ถ้าคุณตามสายการเชื่อมโยงของความสัมพันธ์ของตารางที่เชื่อมต่อกับตารางที่คุณต้องการใช้ คุณอาจจะพบว่าคุณมีความสัมพันธ์ของตารางแบบหนึ่ง-ต่อ-กลุ่มอย่างน้อยสองความสัมพันธ์ ไม่มีวิธีแก้ไขปัญหาชั่วคราวง่ายๆ ที่ใช้ได้ในทุกสถานการณ์ แต่คุณอาจ ลองสร้าง คอลัมน์ที่คํานวณเพื่อรวมคอลัมน์ที่คุณต้องการใช้เป็นตารางเดียว
ขั้นตอนที่ 2: ค้นหาคอลัมน์ที่สามารถใช้เพื่อสร้างเส้นทางจากตารางหนึ่งไปยังตารางถัดไป
หลังจากที่คุณได้ระบุว่าจะยกเลิกการเชื่อมต่อตารางใดจากส่วนที่เหลือของตัวแบบ ให้iรีวิวคอลัมน์เพื่อกำหนดว่าคอลัมน์อื่นที่อยู่ในตารางอื่นๆ ในตัวแบบมีค่าตรงกัน
ตัวอย่างเช่น สมมติว่าคุณมีตัวแบบที่มียอดขายผลิตภัณฑ์ตามพื้นที่ และให้คุณนําเข้าข้อมูลด้านประชากรในภายหลังเพื่อดูว่ามีความสัมพันธ์ระหว่างแนวโน้มด้านประชากรและยอดขายในแต่ละพื้นที่หรือไม่ เนื่องจากข้อมูลประชากรมาจากแหล่งข้อมูลอื่น ตารางจะถูกแยกออกจากส่วนที่เหลือของตัวแบบตั้งแต่แรก เมื่อต้องการรวมข้อมูลด้านประชากรกับส่วนที่เหลือของตัวแบบ คุณจะต้องค้นหาคอลัมน์ในตารางข้อมูลประชากรตารางใดตารางหนึ่งที่สอดคล้องกับข้อมูลประชากรที่คุณใช้อยู่แล้ว ตัวอย่างเช่น ถ้าข้อมูลประชากรถูกจัดระเบียบตามภูมิภาค และข้อมูลการขายของคุณระบุภูมิภาคที่การขายเกิดขึ้น คุณสามารถเชื่อมโยงชุดข้อมูลสองชุดได้โดยการค้นหาคอลัมน์ทั่วไป เช่น รัฐ รหัสไปรษณีย์ หรือภูมิภาค เพื่อให้การค้นหา
นอกจากค่าที่ตรงกันแล้ว มีข้อกำหนดเพิ่มเติมสองสามข้อสำหรับการสร้างความสัมพันธ์ ได้แก่
-
ค่าข้อมูลในคอลัมน์การค้นหาต้องไม่เป็นค่าเฉพาะ กล่าวคือ คอลัมน์ไม่สามารถมีรายการที่คัดลอกได้ ใน ตัวแบบข้อมูล ค่า null และสตริงว่างจะเทียบเท่ากับค่าว่างที่เป็นค่าของข้อมูลที่แตกต่างกัน ซึ่งหมายความว่าคุณไม่สามารถมีค่า Null หลายค่าในคอลัมน์การค้นหาได้
-
ชนิดข้อมูลของทั้งคอลัมน์ต้นฉบับและคอลัมน์การค้นหาต้องเข้ากันได้ For more information about data types, see Data types in Data Models.
เมื่อต้องการเรียนรู้เพิ่มเติมเกี่ยวกับความสัมพันธ์ของตาราง ให้ดูที่ ความสัมพันธ์ระหว่างตารางในตัวแบบข้อมูล
