โมเดลข้อมูลใน Excel | จะสร้างโมเดลข้อมูลได้อย่างไร? (พร้อมตัวอย่าง)

โมเดลข้อมูลใน Excel คืออะไร?

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

คำอธิบาย

  • ช่วยให้สามารถรวมข้อมูลจากหลายตารางโดยการสร้างความสัมพันธ์ตามคอลัมน์ทั่วไป
  • โมเดลข้อมูลถูกใช้อย่างโปร่งใสโดยให้ข้อมูลแบบตารางที่สามารถใช้ใน Pivot Table ใน Excel และ Pivot Charts ใน excel รวมตารางเข้าด้วยกันทำให้สามารถวิเคราะห์ได้อย่างครอบคลุมโดยใช้ Pivot Tables, Power Pivot และ Power View ใน Excel
  • โมเดลข้อมูลช่วยให้โหลดข้อมูลลงในหน่วยความจำของ Excel
  • บันทึกไว้ในหน่วยความจำโดยที่เราไม่สามารถมองเห็นได้โดยตรง จากนั้น Excel จะได้รับคำสั่งให้เชื่อมโยงข้อมูลซึ่งกันและกันโดยใช้คอลัมน์ทั่วไป ส่วน 'Model' ของ Data Model หมายถึงตารางทั้งหมดที่เกี่ยวข้องกัน
  • Data Model สามารถเข้าถึงข้อมูลทั้งหมดที่ต้องการได้แม้ว่าข้อมูลจะอยู่ในหลายตารางก็ตาม หลังจากสร้างแบบจำลองข้อมูลแล้ว Excel จะมีข้อมูลอยู่ในหน่วยความจำ ด้วยข้อมูลในหน่วยความจำข้อมูลจึงสามารถเข้าถึงได้หลายวิธี

ตัวอย่าง

คุณสามารถดาวน์โหลดเทมเพลต Excel แบบจำลองข้อมูลได้ที่นี่ - เทมเพลต Excel ของโมเดลข้อมูล

ตัวอย่าง # 1

หากเรามีชุดข้อมูลสามชุดที่เกี่ยวข้องกับพนักงานขาย: ชุดแรกประกอบด้วยข้อมูลรายได้ชุดที่สองประกอบด้วยรายได้ของพนักงานขายและชุดที่สามประกอบด้วยค่าใช้จ่ายของพนักงานขาย

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

  • แปลงชุดข้อมูลเป็นวัตถุตาราง:

เราไม่สามารถสร้างความสัมพันธ์กับชุดข้อมูลธรรมดาได้ ตัวแบบข้อมูลทำงานได้กับออบเจ็กต์ตาราง Excel เท่านั้น เพื่อทำสิ่งนี้:

  • ขั้นตอนที่ 1 -คลิกที่ใดก็ได้ในชุดข้อมูลจากนั้นคลิกที่แท็บ 'แทรก' จากนั้นคลิกที่ 'ตาราง' ในกลุ่ม 'ตาราง'

  • ขั้นตอนที่ 2 -เลือกหรือยกเลิกการเลือกตัวเลือก: 'ตารางของฉันมีส่วนหัว' แล้วคลิกตกลง

  • ขั้นตอนที่ 3 -เมื่อเลือกตารางใหม่แล้วให้ป้อนชื่อของตารางใน 'ชื่อตาราง' ในกลุ่ม 'เครื่องมือ'

  • ขั้นตอนที่ 4 -ตอนนี้เราจะเห็นว่าชุดข้อมูลแรกถูกแปลงเป็นวัตถุ 'ตาราง' ในการทำซ้ำขั้นตอนเหล่านี้สำหรับชุดข้อมูลอีกสองชุดเราจะเห็นว่าพวกเขาได้รับการแปลงเป็นวัตถุ 'ตาราง' ดังต่อไปนี้

การเพิ่มอ็อบเจ็กต์ 'Table' ไปยัง Data Model: ผ่าน Connections หรือ Relationships

ผ่านทาง Connections

  • เลือกตารางหนึ่งตารางและคลิกที่แท็บ 'ข้อมูล' จากนั้นคลิกที่ 'การเชื่อมต่อ'

  • ในกล่องโต้ตอบผลลัพธ์จะมีไอคอน "เพิ่ม" ขยายเมนูแบบเลื่อนลงของ 'Add' และคลิกที่ 'Add to the Data Model'

  • คลิกที่ 'ตาราง' ในกล่องโต้ตอบผลลัพธ์จากนั้นเลือกตารางใดตารางหนึ่งแล้วคลิก 'เปิด'

ในการดำเนินการนี้แบบจำลองข้อมูลสมุดงานจะถูกสร้างขึ้นด้วยตารางเดียวและกล่องโต้ตอบจะปรากฏขึ้นดังนี้:

ดังนั้นหากเราทำขั้นตอนเหล่านี้ซ้ำกับอีกสองตารางเช่นกัน Data Model จะมีตารางทั้งสามตาราง

ตอนนี้เราจะเห็นว่าทั้งสามตารางปรากฏในการเชื่อมต่อสมุดงาน

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

สร้างความสัมพันธ์: เมื่อทั้งสองชุดข้อมูลเป็นวัตถุตารางเราสามารถสร้างความสัมพันธ์ระหว่างชุดข้อมูลเหล่านี้ได้ เพื่อทำสิ่งนี้:

  • คลิกที่แท็บ 'ข้อมูล' จากนั้นคลิกที่ 'ความสัมพันธ์'

  • เราจะเห็นกล่องโต้ตอบว่างเปล่าเนื่องจากไม่มีการเชื่อมต่อในปัจจุบัน

  • คลิกที่ 'ใหม่' และกล่องโต้ตอบอื่นจะปรากฏขึ้น

  • ขยายรายการแบบเลื่อนลง 'ตาราง' และ 'ตารางที่เกี่ยวข้อง': กล่องโต้ตอบ 'สร้างความสัมพันธ์' จะปรากฏขึ้นเพื่อเลือกตารางและคอลัมน์ที่จะใช้สำหรับความสัมพันธ์ ในการขยาย 'ตาราง' ให้เลือกชุดข้อมูลที่เราต้องการวิเคราะห์ในบางวิธีและใน 'ตารางที่เกี่ยวข้อง' เลือกชุดข้อมูลที่มีค่าการค้นหา
  • ตารางการค้นหาใน excel เป็นตารางขนาดเล็กในกรณีที่มีความสัมพันธ์แบบหนึ่งต่อหลายความสัมพันธ์และไม่มีค่าซ้ำในคอลัมน์ทั่วไป ในการขยาย "คอลัมน์ (ต่างประเทศ)" ให้เลือกคอลัมน์ทั่วไปในตารางหลักใน "คอลัมน์ที่เกี่ยวข้อง (หลัก)" เลือกคอลัมน์ทั่วไปในตารางที่เกี่ยวข้อง

  • เมื่อเลือกการตั้งค่าทั้งสี่นี้แล้วให้คลิกที่ 'ตกลง' กล่องโต้ตอบจะปรากฏขึ้นดังต่อไปนี้เมื่อคลิก 'ตกลง'

หากเราทำขั้นตอนเหล่านี้ซ้ำเพื่อเชื่อมโยงอีกสองตาราง: ตารางรายรับพร้อมตารางค่าใช้จ่ายจากนั้นตารางเหล่านี้จะเกี่ยวข้องในแบบจำลองข้อมูลด้วยดังนี้

ขณะนี้ Excel สร้างความสัมพันธ์เบื้องหลังโดยการรวมข้อมูลในแบบจำลองข้อมูลตามคอลัมน์ทั่วไป: รหัสพนักงานขาย (ในกรณีนี้)

ตัวอย่าง # 2

ตอนนี้สมมติว่าในตัวอย่างข้างต้นเราต้องการสร้าง Pivot Table ที่ประเมินหรือวิเคราะห์วัตถุตาราง:

  • คลิกที่ 'แทรก' -> 'ตาราง Pivot'

  • ในกล่องโต้ตอบผลลัพธ์ให้คลิกที่ตัวเลือกที่ระบุว่า: 'ใช้แหล่งข้อมูลภายนอก' จากนั้นคลิกที่ 'เลือกการเชื่อมต่อ'

  • คลิกที่ 'ตาราง' ในกล่องโต้ตอบผลลัพธ์และเลือกแบบจำลองข้อมูลสมุดงานที่มีสามตารางแล้วคลิก 'เปิด'

  • เลือกตัวเลือก 'แผ่นงานใหม่' ในตำแหน่งและคลิกที่ 'ตกลง'

  • บานหน้าต่างเขตข้อมูลตาราง Pivot จะแสดงวัตถุตาราง

  • ตอนนี้การเปลี่ยนแปลงใน Pivot Table สามารถทำได้ตามต้องการเพื่อวิเคราะห์วัตถุในตารางตามต้องการ

ตัวอย่างเช่นในกรณีนี้หากเราต้องการหารายได้รวมหรือรายได้สำหรับพนักงานขายรายใดรายหนึ่งตาราง Pivot จะถูกสร้างขึ้นดังนี้:

นี่เป็นความช่วยเหลือที่ยิ่งใหญ่ในกรณีของแบบจำลอง / ตารางที่มีข้อสังเกตจำนวนมาก

ดังนั้นเราจะเห็นว่า Pivot Table ใช้ Data Model ทันที (เลือกโดยเลือกการเชื่อมต่อ) ในหน่วยความจำ Excel เพื่อแสดงความสัมพันธ์ระหว่างตาราง

สิ่งที่ต้องจำ

  • การใช้ Data Model ทำให้เราสามารถวิเคราะห์ข้อมูลจากตารางหลาย ๆ ตารางพร้อมกันได้
  • ด้วยการสร้างความสัมพันธ์กับ Data Model เราเหนือกว่าความจำเป็นในการใช้ VLOOKUP, SUMIF, ฟังก์ชัน INDEX และ MATCH เนื่องจากเราไม่จำเป็นต้องรับคอลัมน์ทั้งหมดภายในตารางเดียว
  • เมื่อนำเข้าชุดข้อมูลใน Excel จากแหล่งภายนอกโมเดลจะถูกสร้างขึ้นโดยปริยาย
  • ความสัมพันธ์ของตารางสามารถสร้างขึ้นโดยอัตโนมัติหากเรานำเข้าตารางที่เกี่ยวข้องซึ่งมีความสัมพันธ์ของคีย์หลักและคีย์ต่างประเทศ
  • ในขณะที่สร้างความสัมพันธ์คอลัมน์ที่เรากำลังเชื่อมต่อในตารางควรมีชนิดข้อมูลเดียวกัน
  • ด้วยตาราง Pivot ที่สร้างขึ้นด้วย Data Model เราสามารถเพิ่มตัวแบ่งส่วนข้อมูลและแบ่งตาราง Pivot บนฟิลด์ใดก็ได้ที่เราต้องการ
  • ข้อดีของฟังก์ชั่น Data Model over LOOKUP () คือต้องใช้หน่วยความจำน้อยกว่ามาก
  • Excel 2013 รองรับความสัมพันธ์แบบหนึ่งต่อหนึ่งหรือหนึ่งถึงหลายความสัมพันธ์เท่านั้นกล่าวคือตารางใดตารางหนึ่งต้องไม่มีค่าซ้ำกันในคอลัมน์ที่เรากำลังเชื่อมโยง