Excel ขั้นสูง - แบบจำลองข้อมูล

Excel 2013 มีคุณสมบัติการวิเคราะห์ข้อมูลที่มีประสิทธิภาพ คุณสามารถสร้างแบบจำลองข้อมูลจากนั้นสร้างรายงานเชิงโต้ตอบที่น่าทึ่งโดยใช้ Power View คุณยังสามารถใช้ประโยชน์จากฟีเจอร์และความสามารถของ Microsoft Business Intelligence ใน Excel, PivotTables, Power Pivot และ Power View

Data Modelใช้สำหรับการสร้างแบบจำลองที่สามารถรวมข้อมูลจากแหล่งต่างๆโดยการสร้างความสัมพันธ์ระหว่างแหล่งข้อมูล แบบจำลองข้อมูลรวมตารางทำให้สามารถวิเคราะห์ได้อย่างครอบคลุมโดยใช้ PivotTables, Power Pivot และ Power View

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

Step 1 - เปิดสมุดงานเปล่าใหม่ใน Excel

Step 2 - คลิกที่ไฟล์ DATA แท็บ

Step 3 - ในไฟล์ Get External Data กลุ่มคลิกที่ตัวเลือก From Access. Select Data Source กล่องโต้ตอบจะเปิดขึ้น

Step 4 - เลือก Events.accdbไฟล์ฐานข้อมูลการเข้าถึงเหตุการณ์

Step 5 - Select Table หน้าต่างแสดงไฟล์ tables พบในฐานข้อมูลปรากฏขึ้น

Step 6- ตารางในฐานข้อมูลคล้ายกับตารางใน Excel ตรวจสอบไฟล์‘Enable selection of multiple tables’แล้วเลือกตารางทั้งหมด จากนั้นคลิกOK.

Step 7 - Import Dataหน้าต่างจะปรากฏขึ้น เลือกไฟล์PivotTable Reportตัวเลือก ตัวเลือกนี้จะนำเข้าตารางไปยัง Excel และเตรียม PivotTable สำหรับวิเคราะห์ตารางที่นำเข้า สังเกตว่าช่องทำเครื่องหมายที่ด้านล่างของหน้าต่าง -‘Add this data to the Data Model’ ถูกเลือกและปิดใช้งาน

Step 8 - ข้อมูลถูกนำเข้าและไฟล์ PivotTable ถูกสร้างขึ้นโดยใช้ตารางที่นำเข้า

คุณได้นำเข้าข้อมูลลงใน Excel และไฟล์ Data Modelถูกสร้างขึ้นโดยอัตโนมัติ ตอนนี้คุณสามารถสำรวจข้อมูลในห้าตารางซึ่งมีความสัมพันธ์ที่กำหนดไว้ในตารางเหล่านี้

สำรวจข้อมูลโดยใช้ PivotTable

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

ใน PivotTable Fieldsคลิกที่ลูกศรข้างตาราง - Medalsเพื่อขยายเพื่อแสดงเขตข้อมูลในตารางนั้น ลากไฟล์NOC_CountryRegion ฟิลด์ใน Medals ตารางไปที่ COLUMNS พื้นที่.

Step 2 - ลาก Discipline จาก Disciplines ตารางไปที่ ROWS พื้นที่.

Step 3 - ตัวกรอง Disciplineเพื่อแสดงเฉพาะกีฬาห้าประเภท: ยิงธนูดำน้ำฟันดาบสเก็ตลีลาและสปีดสเก็ต ซึ่งสามารถทำได้ทั้งในPivotTable Fields พื้นที่หรือจาก Row Labels กรองใน PivotTable เอง

Step 4 - ใน PivotTable Fields, จาก Medals ตารางลากเหรียญไปที่ VALUES พื้นที่.

Step 5 - จากไฟล์ Medals ตารางให้เลือก Medal อีกครั้งแล้วลากลงในไฟล์ FILTERS พื้นที่.

Step 6 - คลิกปุ่มรายการแบบเลื่อนลงทางด้านขวาของไฟล์ Column ป้ายกำกับ

Step 7 - เลือก Value Filters จากนั้นเลือก Greater Than…

Step 8 - คลิก OK.

มูลค่า Filters dialog กล่องสำหรับนับเหรียญ is greater than ปรากฏขึ้น

Step 9 - ประเภท 80 ใน Right Field.

Step 10 - คลิก OK.

PivotTable จะแสดงเฉพาะภูมิภาคเหล่านั้นซึ่งมีมากกว่า 80 เหรียญ

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

ถ้าคุณไม่นำเข้าตารางในเวลาเดียวกันหรือถ้าข้อมูลมาจากแหล่งอื่นหรือถ้าคุณเพิ่มตารางใหม่ลงในสมุดงานของคุณคุณต้องสร้าง Relationships ท่ามกลาง Tables โดยตัวคุณเอง.

สร้างความสัมพันธ์ระหว่างตาราง

ความสัมพันธ์ช่วยให้คุณวิเคราะห์คอลเล็กชันข้อมูลใน Excel และสร้างรายงานที่น่าสนใจและสวยงามจากข้อมูลที่คุณนำเข้า

Step 1 - Insert แผ่นงานใหม่

Step 2- สร้างตารางใหม่พร้อมข้อมูลใหม่ ตั้งชื่อตารางใหม่เป็นSports.

Step 3 - ตอนนี้คุณสามารถสร้างความสัมพันธ์ระหว่างตารางใหม่นี้กับตารางอื่น ๆ ที่มีอยู่แล้วในไฟล์ Data Modelใน Excel เปลี่ยนชื่อ Sheet1 เป็นMedals และ Sheet2 เป็น Sports.

บน Medals sheet, ใน PivotTable Fields Listคลิก All. รายการตารางที่มีอยู่ทั้งหมดจะปรากฏขึ้น ตารางที่เพิ่มใหม่ -Sports จะปรากฏขึ้นด้วย

Step 4 - คลิกที่ Sports. ในรายการเขตข้อมูลที่ขยายให้เลือกSports. ข้อความ Excel ให้คุณสร้างความสัมพันธ์ระหว่างตาราง

Step 5 - คลิกที่ CREATE. Create Relationship กล่องโต้ตอบจะเปิดขึ้น

Step 6- ในการสร้างความสัมพันธ์ตารางใดตารางหนึ่งต้องมีคอลัมน์ของค่าที่ไม่ซ้ำกันไม่ซ้ำกัน ในDisciplines ตาราง, SportIDคอลัมน์มีค่าดังกล่าว โต๊ะSports ที่เราสร้างขึ้นก็มีไฟล์ SportIDคอลัมน์. ในTableเลือก Disciplines.

Step 7 - ใน Column (Foreign)เลือก SportID

Step 8 - ใน Related Tableเลือก Sports.

Step 9 - ใน Related Column (Primary)SportID จะถูกเลือกโดยอัตโนมัติ คลิกOK.

Step 10 - PivotTable ได้รับการแก้ไขเพื่อให้สอดคล้องกับการเพิ่มใหม่ Data Fieldกีฬา. ปรับลำดับของฟิลด์ในพื้นที่ Rows เพื่อรักษาHierarchy. ในกรณีนี้,Sport ควรเป็นอันดับแรกและ Discipline ควรจะเป็นลำดับถัดไปเป็น Discipline จะซ้อนอยู่ใน Sport เป็นหมวดหมู่ย่อย