PowerPivot for Excel - ตารางค้นหาใน Data Warehouse

หนึ่งในสิ่งที่ฉันแสดงมากที่สุดเกี่ยวกับ PowerPivot for Excel คือความสามารถในการเพิ่มตารางการค้นหาลงในชุดข้อมูลของคุณ ข้อมูลส่วนใหญ่ที่คุณใช้งานไม่ได้มีทุกสาขาที่คุณต้องการสำหรับการวิเคราะห์ของคุณ ตัวอย่างเช่นคุณอาจมีฟิลด์วันที่ แต่ต้องจัดกลุ่มข้อมูลตามไตรมาส คุณสามารถเขียนสูตรได้ แต่ง่ายต่อการสร้างตารางค้นหาแบบธรรมดาภายในสภาพแวดล้อมของ PowerPivot

นอกจากนี้คุณยังสามารถใช้ตารางค้นหานี้สำหรับการจัดกลุ่มอื่นเช่นชื่อเดือนและครึ่งปีแรก / ครึ่งหลังของปี ในข้อกำหนดในการเก็บข้อมูลคุณเป็นจริงในการสร้างตารางมิติข้อมูลวันที่ ในบทความนี้ฉันจะให้ตารางข้อมูลขนาดตัวอย่างสองชุดเพื่อเพิ่มประสิทธิภาพโครงการ PowerPivot for Excel ของคุณ

ตารางขนาดตัวอักษร (ค้นหาใหม่)

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

• เน็ตบุ๊ค
• เดสก์ท็
• จอภาพ
• โปรเจ็คเตอร์
• เครื่องพิมพ์
• สแกนเนอร์
• กล้องดิจิทัล
• กล้องดิจิตอล SLR
• กล้องฟิล์ม
• กล้องวิดีโอ
• โทรศัพท์สำนักงาน
• โทรศัพท์สมาร์ท
• พีดีเอ
• โทรศัพท์มือถือ

ในความเป็นจริงคุณจะมีรหัสสำหรับเหล่านี้ แต่เพื่อให้ตัวอย่างนี้ง่ายสมมติว่านี่เป็นค่าที่แท้จริงในตารางใบสั่ง การใช้ PowerPivot for Excel ทำให้คุณสามารถจัดกลุ่มคำสั่งซื้อตามประเภทคำสั่งได้อย่างง่ายดาย

จะทำอย่างไรถ้าคุณต้องการการจัดกลุ่มอื่น ตัวอย่างเช่นสมมติว่าคุณต้องมีการจัดกลุ่มหมวดหมู่เช่นคอมพิวเตอร์กล้องถ่ายรูปและโทรศัพท์ ตารางใบสั่งไม่ได้มีฟิลด์หมวดหมู่ แต่คุณสามารถสร้างเป็นตารางค้นหาใน PowerPivot for Excel ได้โดยง่าย ตารางค้นหาตัวอย่างทั้งหมดอยู่ด้านล่างนี้ ตารางที่ 1 .

นี่คือขั้นตอน:

• ขั้นตอนที่ 1: คุณต้องมีรายการที่แตกต่างจากฟิลด์ประเภทสำหรับตารางค้นหาของคุณ นี่จะเป็นช่องค้นหาของคุณ จากชุดข้อมูลของคุณให้สร้างรายการค่าที่แตกต่างจากฟิลด์ประเภทคำสั่ง ป้อนรายการที่แตกต่างของประเภทลงในสมุดงาน Excel ตั้งชื่อคอลัมน์ ชนิด .
• ขั้นตอนที่ 2: ในคอลัมน์ถัดจากคอลัมน์การค้นหาของคุณ (ประเภท) ให้เพิ่มฟิลด์ใหม่ที่คุณต้องการจัดกลุ่ม ในตัวอย่างของเราให้เพิ่มคอลัมน์ที่มีป้ายกำกับชื่อว่า ประเภท .
• ขั้นที่ 3: สำหรับแต่ละค่าในรายการค่าที่แตกต่างกันของคุณ (ประเภทในตัวอย่างนี้) ให้เพิ่มรายการที่ตรงกัน ประเภท ค่า ในตัวอย่างง่ายๆของเราให้ป้อนอย่างใดอย่างหนึ่ง คอมพิวเตอร์ , กล้อง หรือ โทรศัพท์ เข้าไปใน ประเภท คอลัมน์.
• ขั้นตอนที่ 4: คัดลอก ชนิด และ ประเภท ตารางข้อมูลลงในคลิปบอร์ดของคุณ
• ขั้นตอนที่ 5: เปิดสมุดงาน Excel ด้วยข้อมูลคำสั่งซื้อใน PowerPivot for Excel เปิดหน้าต่าง PowerPivot คลิกวางซึ่งจะนำมาในตารางค้นหาใหม่ของคุณ ให้ตารางชื่อและตรวจดูให้แน่ใจว่าได้ตรวจสอบ ใช้แถวแรกเป็นส่วนหัวของคอลัมน์. คลิก ตกลง. คุณได้สร้างตารางค้นหาใน PowerPivot แล้ว
• ขั้นตอนที่ 6: สร้างความสัมพันธ์ระหว่าง ชนิด ในฟิลด์ ใบสั่ง ตารางและ ประเภท ในตารางค้นหา คลิกที่ ออกแบบ ริบบิ้นและเลือก สร้างความสัมพันธ์. ทำการเลือกใน สร้างความสัมพันธ์ โต้ตอบและคลิก สร้าง.

เมื่อคุณสร้าง PivotTable ใน Excel โดยใช้ข้อมูล PowerPivot คุณจะสามารถจัดกลุ่มตามกลุ่มใหม่ได้ ประเภท สนาม โปรดจำไว้ว่า PowerPivot for Excel สนับสนุนเฉพาะ Inner Joins เท่านั้น ถ้าคุณมี ประเภทการสั่งซื้อ หายไปจากตารางค้นหาของคุณทั้งหมดของระเบียนที่สอดคล้องกันสำหรับชนิดที่จะหายไปจาก PivotTable ใด ๆ ขึ้นอยู่กับข้อมูล PowerPivot คุณจะต้องตรวจสอบเป็นครั้งคราว

ตารางวันที่ (ค้นหา)

ตารางการค้นหาวันที่มักจะต้องการในโครงการ PowerPivot for Excel ส่วนใหญ่ของคุณ ชุดข้อมูลส่วนใหญ่มีฟิลด์วันที่บางประเภท มีฟังก์ชันคำนวณปีและเดือน

อย่างไรก็ตามหากคุณต้องการข้อความเดือนที่เกิดขึ้นจริงหรือไตรมาสคุณต้องเขียนสูตรที่ซับซ้อน การใส่ตารางวันที่ (การค้นหา) เป็นเรื่องง่ายมากและตรงกับเดือนในชุดข้อมูลหลักของคุณ คุณจะต้องเพิ่มคอลัมน์ลงในตารางคำสั่งซื้อของคุณเพื่อแสดงหมายเลขเดือนจากฟิลด์วันที่สั่ง สูตร DAX สำหรับ เดือน ในตัวอย่างของเราคือ = MONTH (วันที่สั่งซื้อ). ค่านี้จะแสดงตัวเลขระหว่าง 1 ถึง 12 สำหรับแต่ละระเบียน ตารางมิติข้อมูลของเราจะระบุค่าทดแทนซึ่งเชื่อมโยงกับหมายเลขเดือน ซึ่งจะช่วยให้คุณมีความยืดหยุ่นในการวิเคราะห์ของคุณ ตารางมิติข้อมูลวันตัวอย่างที่สมบูรณ์อยู่ด้านล่างนี้ ตารางที่ 2 .

มิติข้อมูลวันที่หรือตารางค้นหาจะประกอบด้วยระเบียน 12 รายการ คอลัมน์เดือนจะมีค่าเป็น 1 - 12 คอลัมน์อื่น ๆ จะรวมถึงข้อความเดือนที่ย่อข้อความเต็มเดือนไตรมาส ฯลฯ ต่อไปนี้เป็นขั้นตอน:

• ขั้นตอนที่ 1: คัดลอกตารางจากตารางที่ 2 ด้านล่างและวางลงใน PowerPivot คุณสามารถสร้างตารางนี้ใน Excel แต่ฉันช่วยคุณประหยัดเวลา คุณควรจะสามารถวางได้โดยตรงจากข้อมูลที่เลือกด้านล่างหากคุณใช้ Internet Explorer หรือ Edge PowerPivot ดึงข้อมูลการจัดรูปแบบตารางในการทดสอบของฉัน หากคุณกำลังใช้เบราเซอร์อื่นคุณอาจต้องวางลงใน Excel ก่อนและคัดลอกจาก Excel เพื่อรับรูปแบบตาราง
• ขั้นตอนที่ 2: เปิดสมุดงาน Excel ด้วยข้อมูลคำสั่งซื้อใน PowerPivot for Excel เปิดหน้าต่าง PowerPivot คลิก แปะ ซึ่งจะนำตารางการค้นหาของคุณที่คัดลอกมาจากตารางด้านล่างหรือจาก Excel ให้ตารางชื่อและตรวจดูให้แน่ใจว่าได้ตรวจสอบ ใช้แถวแรกเป็นส่วนหัวของคอลัมน์. คลิก ตกลง. คุณได้สร้างตารางค้นหาวันที่ใน PowerPivot แล้ว
• ขั้นตอนที่ 3สร้างความสัมพันธ์ระหว่าง เดือน ในฟิลด์ ใบสั่ง ตารางและ MonthNumber ในตารางค้นหา คลิกที่ ออกแบบ ริบบิ้นและเลือก สร้างความสัมพันธ์. ทำการเลือกใน สร้างความสัมพันธ์ โต้ตอบและคลิก สร้าง.

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

ตารางมิติข้อมูลตัวอย่าง (ค้นหา)

ตารางที่ 1

ชนิด	ประเภท
เน็ตบุ๊ค	คอมพิวเตอร์
เดสก์ท็	คอมพิวเตอร์
จอภาพ	คอมพิวเตอร์
โปรเจคเตอร์และหน้าจอ	คอมพิวเตอร์
เครื่องพิมพ์สแกนเนอร์และแฟกซ์	คอมพิวเตอร์
คอมพิวเตอร์ตั้งค่าและบริการ	คอมพิวเตอร์
อุปกรณ์เสริมสำหรับคอมพิวเตอร์	คอมพิวเตอร์
กล้องดิจิทัล	กล้อง
กล้องดิจิตอล SLR	กล้อง
กล้องฟิล์ม	กล้อง
กล้องวิดีโอ	กล้อง
กล้องถ่ายรูปและกล้องวิดีโอ	กล้อง
โทรศัพท์ที่บ้านและที่ทำงาน	โทรศัพท์
โทรศัพท์ระบบสัมผัสหน้าจอ	โทรศัพท์
โทรศัพท์สมาร์ทและพีดีเอ	โทรศัพท์

ตารางที่ 2

MonthNumber	MonthTextShort	MonthTextFull	ย่าน	เทอม
1	ม.ค.	มกราคม	ไตรมาสที่ 1	H1
2	กุมภาพันธ์	กุมภาพันธ์	ไตรมาสที่ 1	H1
3	ทำลาย	มีนาคม	ไตรมาสที่ 1	H1
4	เมษายน	เมษายน	ไตรมาสที่ 2	H1
5	อาจ	อาจ	ไตรมาสที่ 2	H1
6	มิถุนายน	มิถุนายน	ไตรมาสที่ 2	H1
7	กรกฎาคม	กรกฎาคม	ไตรมาสที่ 3	H2
8	สิงหาคม	สิงหาคม	ไตรมาสที่ 3	H2
9	กันยายน	กันยายน	ไตรมาสที่ 3	H2
10	ตุลาคม	ตุลาคม	ไตรมาสที่ 4	H2
11	พฤศจิกายน	พฤศจิกายน	ไตรมาสที่ 4	H2
12	ธันวาคม	ธันวาคม	ไตรมาสที่ 4	H2