หนึ่งในสิ่งที่ฉันแสดงมากที่สุดเกี่ยวกับ PowerPivot for Excel คือความสามารถในการเพิ่มตารางการค้นหาลงในชุดข้อมูลของคุณ ข้อมูลส่วนใหญ่ที่คุณใช้งานไม่ได้มีทุกสาขาที่คุณต้องการสำหรับการวิเคราะห์ของคุณ ตัวอย่างเช่นคุณอาจมีฟิลด์วันที่ แต่ต้องจัดกลุ่มข้อมูลตามไตรมาส คุณสามารถเขียนสูตรได้ แต่ง่ายต่อการสร้างตารางค้นหาแบบธรรมดาภายในสภาพแวดล้อมของ PowerPivot
นอกจากนี้คุณยังสามารถใช้ตารางค้นหานี้สำหรับการจัดกลุ่มอื่นเช่นชื่อเดือนและครึ่งปีแรก / ครึ่งหลังของปี ในข้อกำหนดในการเก็บข้อมูลคุณเป็นจริงในการสร้างตารางมิติข้อมูลวันที่ ในบทความนี้ฉันจะให้ตารางข้อมูลขนาดตัวอย่างสองชุดเพื่อเพิ่มประสิทธิภาพโครงการ PowerPivot for Excel ของคุณ
ตารางขนาดตัวอักษร (ค้นหาใหม่)
ลองดูตารางที่มีข้อมูลคำสั่งซื้อ (ข้อมูล Contoso จาก Microsoft รวมถึงชุดข้อมูลชุดนี้) สมมติว่าตารางมีฟิลด์สำหรับลูกค้าวันที่สั่งคำสั่งรวมและประเภทใบสั่ง เราจะเน้นที่ฟิลด์ประเภทคำสั่งซื้อ สมมติว่าฟิลด์ประเภทคำสั่งมีค่าเช่น:
- เน็ตบุ๊ค
- เดสก์ท็
- จอภาพ
- โปรเจ็คเตอร์
- เครื่องพิมพ์
- สแกนเนอร์
- กล้องดิจิทัล
- กล้องดิจิตอล SLR
- กล้องฟิล์ม
- กล้องวิดีโอ
- โทรศัพท์สำนักงาน
- โทรศัพท์สมาร์ท
- พีดีเอ
- โทรศัพท์มือถือ
ในความเป็นจริงคุณจะมีรหัสสำหรับเหล่านี้ แต่เพื่อให้ตัวอย่างนี้ง่ายสมมติว่านี่เป็นค่าที่แท้จริงในตารางใบสั่ง
การใช้ PowerPivot for Excel ทำให้คุณสามารถจัดกลุ่มคำสั่งซื้อตามประเภทคำสั่งได้อย่างง่ายดาย จะทำอย่างไรถ้าคุณต้องการการจัดกลุ่มอื่น ตัวอย่างเช่นสมมติว่าคุณต้องมี "หมวดหมู่" เช่นคอมพิวเตอร์กล้องและโทรศัพท์ ตารางสั่งซื้อไม่มีฟิลด์ "หมวดหมู่" แต่คุณสามารถสร้างเป็นตารางค้นหาใน PowerPivot for Excel ได้โดยง่าย
ตารางค้นหาตัวอย่างทั้งหมดอยู่ใน ตารางที่ 1 นี่คือขั้นตอน:
- ขั้นตอนที่ 1: คุณต้องมีรายการที่แตกต่างจากฟิลด์ประเภทสำหรับตารางค้นหาของคุณ นี่จะเป็นช่องค้นหาของคุณ จากชุดข้อมูลของคุณให้สร้างรายการค่าที่แตกต่างจากฟิลด์ประเภทคำสั่ง ป้อนรายการ "ประเภทต่างๆ" ลงในสมุดงาน Excel ป้ายกำกับคอลัมน์ Type
- ขั้นที่ 2: ในคอลัมน์ถัดจากคอลัมน์การค้นหาของคุณ (พิมพ์) ให้เพิ่มฟิลด์ใหม่ที่คุณต้องการจัดกลุ่ม ในตัวอย่างของเราให้เพิ่มคอลัมน์ที่มีป้ายชื่อว่า Category
- ขั้นที่ 3: สำหรับแต่ละค่าในรายการค่าที่แตกต่างกันของคุณ (ประเภทในตัวอย่างนี้) ให้เพิ่มค่า "Category" ที่สอดคล้องกัน ในตัวอย่างง่ายๆของเราให้ป้อนคอมพิวเตอร์เครื่องหรือโทรศัพท์ลงในหมวดหมู่
- ขั้นตอนที่ 4: คัดลอกตารางข้อมูลประเภทและประเภทลงในคลิปบอร์ดของคุณ
- ขั้นตอนที่ 5: เปิดสมุดงาน Excel ด้วยข้อมูลคำสั่งซื้อใน PowerPivot for Excel เปิดหน้าต่าง PowerPivot คลิกวางซึ่งจะนำมาในตารางค้นหาใหม่ของคุณ ให้ชื่อตารางและตรวจสอบว่าคุณได้เลือก "ใช้แถวแรกเป็นส่วนหัวของคอลัมน์" คลิกตกลง คุณได้สร้างตารางค้นหาใน PowerPivot แล้ว
- ขั้นตอนที่ 6: สร้างความสัมพันธ์ระหว่างฟิลด์ Type ในตาราง Order และประเภทในฟิลด์ lookup table คลิกริบบิ้น Design และเลือก Create Relationship ทำการเลือกในกล่องโต้ตอบสร้างความสัมพันธ์และคลิกสร้าง
เมื่อคุณสร้าง PivotTable ใน Excel โดยใช้ข้อมูล PowerPivot คุณจะสามารถจัดกลุ่มตามหมวดหมู่ใหม่ได้ โปรดจำไว้ว่า PowerPivot for Excel สนับสนุนเฉพาะ Inner Joins เท่านั้น หากคุณมี "ประเภทคำสั่ง" ที่หายไปจากตารางค้นหาระเบียนทั้งหมดที่ตรงกันสำหรับประเภทนั้นจะหายไปจาก PivotTable ใด ๆ ที่ยึดตามข้อมูล PowerPivot คุณจะต้องตรวจสอบเป็นครั้งคราว
ตารางวันที่ (ค้นหา)
ตารางการค้นหาวันที่มักจะต้องการในโครงการ PowerPivot for Excel ส่วนใหญ่ของคุณ ชุดข้อมูลส่วนใหญ่มีฟิลด์วันที่บางประเภท มีฟังก์ชันคำนวณปีและเดือน
อย่างไรก็ตามหากคุณต้องการข้อความเดือนที่เกิดขึ้นจริงหรือไตรมาสคุณต้องเขียนสูตรที่ซับซ้อน การใส่ตารางวันที่ (การค้นหา) เป็นเรื่องง่ายมากและตรงกับเดือนในชุดข้อมูลหลักของคุณ คุณจะต้องเพิ่มคอลัมน์ลงในตารางคำสั่งซื้อของคุณเพื่อแสดงหมายเลขเดือนจากฟิลด์วันที่สั่ง สูตร DAX สำหรับ "month" ในตัวอย่างของเราคือ "= MONTH ([Order Date]) ซึ่งจะแสดงตัวเลขระหว่าง 1 ถึง 12 สำหรับแต่ละระเบียนตารางมิติข้อมูลของเราจะระบุค่าอื่นซึ่งเชื่อมโยงไปยังหมายเลขเดือน จะช่วยให้คุณมีความยืดหยุ่นในการวิเคราะห์ของคุณตารางมิติข้อมูลวันตัวอย่างแบบสมบูรณ์อยู่ด้านล่างใน ตารางที่ 2
มิติข้อมูลวันที่หรือตารางค้นหาจะประกอบด้วยระเบียน 12 รายการ คอลัมน์เดือนจะมีค่าเป็น 1 - 12 คอลัมน์อื่น ๆ จะรวมถึงข้อความเดือนที่ย่อข้อความเต็มเดือนไตรมาส ฯลฯ ต่อไปนี้เป็นขั้นตอน:
- ขั้นที่ 1: คัดลอกตารางจากตารางที่ 2 ด้านล่างและวางลงใน PowerPivot คุณสามารถสร้างตารางนี้ใน Excel แต่ฉันช่วยคุณประหยัดเวลา คุณควรจะสามารถวางได้โดยตรงจากข้อมูลที่เลือกด้านล่างหากคุณใช้ Internet Explorer PowerPivot ดึงข้อมูลการจัดรูปแบบตารางในการทดสอบของฉัน หากคุณกำลังใช้เบราเซอร์อื่นคุณอาจต้องวางลงใน Excel ก่อนและคัดลอกจาก Excel เพื่อรับรูปแบบตาราง
- ขั้นตอนที่ 2: เปิดสมุดงาน Excel ด้วยข้อมูลคำสั่งซื้อใน PowerPivot for Excel เปิดหน้าต่าง PowerPivot คลิกวางซึ่งจะนำตารางการค้นหาของคุณที่คัดลอกมาจากตารางด้านล่างหรือจาก Excel ให้ชื่อตารางและตรวจสอบว่าคุณได้เลือก "ใช้แถวแรกเป็นส่วนหัวของคอลัมน์" คลิกตกลง คุณได้สร้างตารางค้นหาวันที่ใน PowerPivot แล้ว
- ขั้นที่ 3 : สร้างความสัมพันธ์ระหว่างเขตข้อมูลเดือนในตารางใบสั่งและฟิลด์ MonthNumber ในตารางค้นหา คลิกริบบิ้น Design และเลือก Create Relationship ทำการเลือกในกล่องโต้ตอบสร้างความสัมพันธ์และคลิกสร้าง
อีกครั้งด้วยการเพิ่มมิติข้อมูลวันที่คุณจะสามารถจัดกลุ่มข้อมูลในตารางสาระสำคัญของคุณโดยใช้ค่าต่างๆจากตารางค้นหาวันที่ การจัดกลุ่มตามไตรมาสหรือชื่อของเดือนจะคลี่คลาย
ตารางมิติข้อมูลตัวอย่าง (ค้นหา)
ตารางที่ 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 |