การรวมเป็นวิธีหนึ่งในการยุบ สรุป หรือจัดกลุ่มข้อมูล เมื่อคุณเริ่มต้นด้วยข้อมูลดิบจากตารางหรือแหล่งข้อมูลอื่น ข้อมูลมักจะเป็นแบบราบเรียบ หมายความว่ามีรายละเอียดจํานวนมาก แต่ยังไม่ได้จัดระเบียบหรือจัดกลุ่มในลักษณะใดๆ การขาดข้อมูลสรุปหรือโครงสร้างอาจทําให้ค้นหารูปแบบในข้อมูลได้ยาก ส่วนสําคัญของการสร้างแบบจําลองข้อมูลคือการกําหนดการรวมที่ทําให้รูปแบบง่ายขึ้น เป็นนามธรรม หรือสรุปเพื่อตอบคําถามทางธุรกิจที่เฉพาะเจาะจง
การรวมทั่วไปส่วนใหญ่ เช่น การรวมที่ใช้ AVERAGE,COUNT,DISTINCTCOUNT,MAX, MIN หรือ SUM สามารถสร้างใน หน่วยวัด ได้โดยอัตโนมัติโดยใช้ผลรวมอัตโนมัติ การรวมชนิดอื่นๆ เช่น AVERAGEX, COUNTX, COUNTROWS หรือ SUMX จะส่งกลับตาราง และจําเป็นต้องมีสูตรที่สร้างโดยใช้ Data Analysis Expressions (DAX)
ทําความเข้าใจเกี่ยวกับการรวมใน Power Pivot
การเลือก กลุ่มสําหรับการรวม
เมื่อคุณรวมข้อมูล คุณสามารถจัดกลุ่มข้อมูลตามแอตทริบิวต์ เช่น ผลิตภัณฑ์ ราคา ภูมิภาค หรือวันที่ แล้วกําหนดสูตรที่ใช้ได้กับข้อมูลทั้งหมดในกลุ่ม ตัวอย่างเช่น เมื่อคุณสร้างผลรวมสําหรับปี คุณกําลังสร้างการรวม ถ้าคุณสร้างอัตราส่วนของปีนี้เมื่อเทียบกับปีก่อนหน้า และแสดงเป็นเปอร์เซ็นต์ แสดงว่าเป็นการรวมชนิดอื่น
การตัดสินใจว่าจะจัดกลุ่มข้อมูลอย่างไรนั้นเกิดจากคําถามทางธุรกิจ ตัวอย่างเช่น การรวมสามารถตอบคําถามต่อไปนี้:
จํานวน มีธุรกรรมกี่รายการในหนึ่งเดือน?
ค่าเฉลี่ย ยอดขายเฉลี่ยในเดือนนี้ตามพนักงานขายคืออะไร
ค่าน้อยที่สุดและมากที่สุด ย่านการขายใดที่ห้าอันดับแรกในแง่ของหน่วยที่ขายได้?
เมื่อต้องการสร้างการคํานวณที่ตอบคําถามเหล่านี้ คุณต้องมีข้อมูลโดยละเอียดที่มีตัวเลขที่จะนับหรือรวม และข้อมูลตัวเลขนั้นต้องเกี่ยวข้องกับกลุ่มที่คุณจะใช้เพื่อจัดระเบียบผลลัพธ์ในทางใดทางหนึ่ง
ถ้าข้อมูลนั้นยังไม่มีค่าที่คุณสามารถใช้สําหรับการจัดกลุ่ม เช่น ประเภทผลิตภัณฑ์หรือชื่อของภูมิภาคทางภูมิศาสตร์ที่ร้านตั้งอยู่ คุณอาจต้องการแนะนํากลุ่มให้กับข้อมูลของคุณโดยการเพิ่มประเภท เมื่อคุณสร้างกลุ่มใน Excel คุณต้องพิมพ์หรือเลือกกลุ่มที่คุณต้องการใช้จากคอลัมน์ในเวิร์กชีตของคุณด้วยตนเอง อย่างไรก็ตาม ในระบบความสัมพันธ์ ลําดับชั้น เช่น ประเภทสําหรับผลิตภัณฑ์ มักจะถูกจัดเก็บไว้ในตารางที่แตกต่างจากตารางข้อเท็จจริงหรือค่า โดยปกติ ตารางประเภทจะเชื่อมโยงกับข้อมูลข้อเท็จจริงด้วยคีย์บางชนิด ตัวอย่างเช่น สมมติว่าคุณพบว่าข้อมูลของคุณประกอบด้วยรหัสผลิตภัณฑ์ แต่ไม่มีชื่อของผลิตภัณฑ์หรือประเภทของผลิตภัณฑ์ เมื่อต้องการเพิ่มประเภทลงในเวิร์กชีต Excel แบบแฟลต คุณจะต้องคัดลอกในคอลัมน์ที่มีชื่อประเภท ด้วย Power Pivot คุณสามารถนําเข้าตารางประเภทผลิตภัณฑ์ไปยังตัวแบบข้อมูลของคุณ สร้างความสัมพันธ์ระหว่างตารางที่มีข้อมูลตัวเลขและรายการประเภทผลิตภัณฑ์ แล้วใช้ประเภทในการจัดกลุ่มข้อมูล สําหรับข้อมูลเพิ่มเติม ให้ดูที่ สร้างความสัมพันธ์ระหว่างตาราง
การเลือกฟังก์ชันสําหรับการรวม
หลังจากที่คุณได้ระบุและเพิ่มการจัดกลุ่มเพื่อใช้ คุณต้องตัดสินใจว่าจะใช้ฟังก์ชันทางคณิตศาสตร์ใดสําหรับการรวม บ่อยครั้งที่คําว่า การรวม ถูกใช้เป็นคําเหมือนสําหรับการดําเนินการทางคณิตศาสตร์หรือทางสถิติที่ใช้ในการรวม เช่น ผลรวม ค่าเฉลี่ย ค่าต่ําสุด หรือจํานวนนับ อย่างไรก็ตาม Power Pivot ทําให้คุณสามารถสร้างสูตรแบบกําหนดเองสําหรับการรวม นอกเหนือจากการรวมมาตรฐานที่พบในทั้ง Power Pivot และ Excel
ตัวอย่างเช่น มีชุดค่าและการจัดกลุ่มเดียวกันกับที่ใช้ในตัวอย่างก่อนหน้านี้ คุณสามารถสร้างการรวมแบบกําหนดเองที่ตอบคําถามต่อไปนี้:
จํานวนที่กรองแล้ว มีธุรกรรมในหนึ่งเดือนกี่รายการ ไม่รวมกรอบเวลาการบํารุงรักษาสิ้นเดือน
อัตราส่วนที่ใช้ค่าเฉลี่ยในช่วงเวลาต่างๆ การเติบโตหรือลดลงของยอดขายเมื่อเทียบกับช่วงเวลาเดียวกันของปีที่แล้วเป็นเปอร์เซ็นต์เท่าใด
ค่าต่ําสุดและค่าสูงสุดที่จัดกลุ่ม ย่านการขายใดบ้างที่ได้รับการจัดอันดับสูงสุดสําหรับแต่ละหมวดหมู่ผลิตภัณฑ์หรือสําหรับการส่งเสริมการขายแต่ละรายการ
การเพิ่มการรวมให้กับสูตรและ PivotTable
เมื่อคุณมีแนวคิดทั่วไปเกี่ยวกับวิธีการจัดกลุ่มข้อมูลของคุณเพื่อให้มีความหมาย และค่าที่คุณต้องการทํางานด้วย คุณสามารถตัดสินใจว่าจะสร้าง PivotTable หรือสร้างการคํานวณภายในตาราง Power Pivot ขยายและปรับปรุงความสามารถดั้งเดิมของ Excel ในการสร้างการรวม เช่น ผลรวม จํานวน หรือค่าเฉลี่ย คุณสามารถสร้างการรวมแบบกําหนดเองใน Power Pivot ได้ภายในหน้าต่าง Power Pivot หรือภายในพื้นที่ PivotTable ของ Excel
- ใน คอลัมน์จากการคํานวณ คุณสามารถสร้างการรวมที่คํานึงถึงบริบทของแถวปัจจุบันเพื่อดึงข้อมูลแถวที่เกี่ยวข้องจากตารางอื่น แล้วรวม นับ หรือหาค่าเฉลี่ยของค่าเหล่านั้นในแถวที่เกี่ยวข้อง
- ใน การวัด คุณสามารถสร้างการรวมแบบไดนามิกที่ใช้ทั้งตัวกรองที่กําหนดไว้ภายในสูตร และตัวกรองที่กําหนดโดยการออกแบบของ PivotTable และการเลือกตัวแบ่งส่วนข้อมูล ส่วนหัวของคอลัมน์ และส่วนหัวของแถว หน่วยวัดที่ใช้การรวมมาตรฐานสามารถสร้างได้ใน Power Pivot โดยใช้ผลรวมอัตโนมัติ หรือโดยการสร้างสูตร คุณยังสามารถสร้างหน่วยวัดโดยนัยโดยใช้การรวมมาตรฐานใน PivotTable ใน Excel
การเพิ่มการจัดกลุ่มลงใน PivotTable
เมื่อคุณออกแบบ PivotTable ให้คุณลากเขตข้อมูลที่แสดงการจัดกลุ่ม ประเภท หรือลําดับชั้น ไปยังส่วนคอลัมน์และแถวของ PivotTable เพื่อจัดกลุ่มข้อมูล จากนั้น ให้คุณลากเขตข้อมูลที่มีค่าตัวเลขลงในพื้นที่ค่า เพื่อให้สามารถนับ หาค่าเฉลี่ย หรือหาผลรวมได้
ถ้าคุณเพิ่มประเภทลงใน PivotTable แต่ข้อมูลประเภทไม่เกี่ยวข้องกับข้อมูลข้อเท็จจริง คุณอาจได้รับข้อผิดพลาดหรือผลลัพธ์ที่ไม่ปกติ โดยปกติแล้ว Power Pivot จะพยายามแก้ไขปัญหา โดยการตรวจหาและแนะนําความสัมพันธ์โดยอัตโนมัติ สําหรับข้อมูลเพิ่มเติม ให้ดูที่ ทํางานกับความสัมพันธ์ใน PivotTable
คุณยังสามารถลากเขตข้อมูลลงใน ตัวแบ่งส่วนข้อมูล เพื่อเลือกบางกลุ่มข้อมูลสําหรับการดู ตัวแบ่งส่วนข้อมูลช่วยให้คุณจัดกลุ่ม เรียงลําดับ และกรองผลลัพธ์แบบโต้ตอบได้ใน PivotTable
การทํางานกับการจัดกลุ่มในสูตร
คุณยังสามารถใช้การจัดกลุ่มและประเภทเพื่อรวมข้อมูลที่เก็บอยู่ในตารางได้ด้วยการสร้างความสัมพันธ์ระหว่างตาราง แล้วสร้างสูตรที่ใช้ความสัมพันธ์ดังกล่าวในการค้นหาค่าที่เกี่ยวข้อง
กล่าวคือ ถ้าคุณต้องการสร้างสูตรที่จัดกลุ่มค่าตามประเภท ก่อนอื่นคุณต้องใช้ความสัมพันธ์เพื่อเชื่อมต่อตารางที่มีข้อมูลรายละเอียดและตารางที่มีประเภท แล้วจึงสร้างสูตร
สําหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการสร้างสูตรที่ใช้การค้นหา ให้ดูที่ การค้นหาในสูตร Power Pivot
การใช้ตัวกรองในการรวม
ฟีเจอร์ใหม่ใน Power Pivot คือความสามารถในการนําตัวกรองไปใช้กับคอลัมน์และตารางของข้อมูล ไม่เฉพาะในส่วนติดต่อผู้ใช้และภายใน PivotTable หรือแผนภูมิ แต่ในสูตรที่คุณใช้คํานวณการรวมด้วย ตัวกรองสามารถใช้ได้ในสูตรทั้งในคอลัมน์จากการคํานวณและใน S
ตัวอย่างเช่น ในฟังก์ชันการรวม DAX ใหม่ คุณสามารถระบุทั้งตารางเป็นอาร์กิวเมนต์แทนการระบุค่าที่จะรวมหรือนับได้ ถ้าคุณไม่ได้นําตัวกรองใดๆ ไปใช้กับตารางนั้น ฟังก์ชันการรวมจะทํางานกับค่าทั้งหมดในคอลัมน์ที่ระบุของตาราง อย่างไรก็ตาม ใน DAX คุณสามารถสร้างตัวกรองแบบไดนามิกหรือแบบคงที่บนตาราง เพื่อให้การรวมดําเนินการกับชุดย่อยที่แตกต่างกันของข้อมูลโดยขึ้นอยู่กับเงื่อนไขของตัวกรองและบริบทปัจจุบัน
เมื่อรวมเงื่อนไขและตัวกรองไว้ในสูตร คุณสามารถสร้างการรวมที่เปลี่ยนแปลงโดยขึ้นอยู่กับค่าที่ระบุในสูตร หรือที่เปลี่ยนแปลงโดยขึ้นอยู่กับการเลือกของแถว ส่วนหัว และส่วนหัวของคอลัมน์ใน PivotTable
สําหรับข้อมูลเพิ่มเติม ให้ดูที่ กรองข้อมูลในสูตร
การเปรียบเทียบฟังก์ชันการรวมของ Excel และฟังก์ชันการรวมของ DAX
ตารางต่อไปนี้แสดงรายการฟังก์ชันการรวมมาตรฐานบางอย่างที่ Excel มีให้ และมีลิงก์ไปยังการใช้งานฟังก์ชันเหล่านี้ใน Power Pivot เวอร์ชัน DAX ของฟังก์ชันเหล่านี้ทํางานเหมือนกับเวอร์ชัน Excel มากโดยอาจมีข้อแตกต่างเล็กน้อยในไวยากรณ์และการจัดการข้อมูลบางชนิด
ฟังก์ชันการรวม Standard
| ฟังก์ชัน | ใช้ |
|---|---|
| AVERAGE | ส่งกลับค่าเฉลี่ย (ค่าเฉลี่ยเลขคณิต) ของตัวเลขทั้งหมดในคอลัมน์ |
| AVERAGEA | ส่งกลับค่าเฉลี่ย (ค่าเฉลี่ยเลขคณิต) ของค่าทั้งหมดในคอลัมน์ จัดการค่าข้อความและค่าที่ไม่ใช่ตัวเลข |
| COUNT | นับจํานวนค่าตัวเลขในคอลัมน์ |
| COUNTA | นับจํานวนค่าในคอลัมน์ที่ไม่ว่างเปล่า |
| MAX | ส่งกลับค่าตัวเลขที่ใหญ่ที่สุดในคอลัมน์ |
| MAXX | ส่งกลับค่าที่มากที่สุดจากชุดนิพจน์ที่ประเมินจากตาราง |
| MIN | ส่งกลับค่าตัวเลขที่น้อยที่สุดในคอลัมน์ |
| MINX | ส่งกลับค่าที่น้อยที่สุดจากชุดนิพจน์ที่ประเมินเหนือตาราง |
| SUM | บวกตัวเลขทั้งหมดในคอลัมน์ |
ฟังก์ชันการรวม DAX
DAX มีฟังก์ชันการรวมที่ช่วยให้คุณระบุตารางที่จะดําเนินการรวม ดังนั้น แทนที่จะเป็นเพียงการเพิ่มหรือหาค่าเฉลี่ยค่าในคอลัมน์ ฟังก์ชันเหล่านี้ช่วยให้คุณสร้างนิพจน์ที่กําหนดข้อมูลที่จะรวมแบบไดนามิกได้
ตารางต่อไปนี้แสดงฟังก์ชันการรวมที่พร้อมใช้งานใน DAX
| ฟังก์ชัน | ใช้ |
|---|---|
| AVERAGEX | หาค่าเฉลี่ยของชุดนิพจน์ที่ประเมินจากตาราง |
| COUNTAX | นับชุดนิพจน์ที่ประเมินบนตาราง |
| COUNTBLANK | นับจํานวนค่าว่างในคอลัมน์ |
| COUNTX | นับจํานวนแถวทั้งหมดในตาราง |
| COUNTROWS | นับจํานวนแถวที่ส่งกลับจากฟังก์ชันตารางที่ซ้อนกัน เช่น ฟังก์ชัน filter |
| SUMX | ส่งกลับผลรวมของชุดนิพจน์ที่ประเมินจากตาราง |
ความแตกต่างระหว่างฟังก์ชันการรวมของ DAX และ Excel
แม้ว่าฟังก์ชันเหล่านี้จะมีชื่อเดียวกันกับฟังก์ชันต่างๆ ใน Excel แต่จะใช้เครื่องมือการวิเคราะห์ในหน่วยความจําของ Power Pivot และถูกเขียนใหม่เพื่อให้ทํางานกับตารางและคอลัมน์ได้ คุณไม่สามารถใช้สูตร DAX ในเวิร์กบุ๊ก Excel ได้และในทางกลับกันด้วย สามารถใช้ได้เฉพาะในหน้าต่าง Power Pivot และใน PivotTable ที่ยึดตามข้อมูล Power Pivot เท่านั้น นอกจากนี้ แม้ว่าฟังก์ชันจะมีชื่อเหมือนกัน แต่ลักษณะการทํางานอาจแตกต่างกันเล็กน้อย สําหรับข้อมูลเพิ่มเติม ให้ดูที่หัวข้ออ้างอิงฟังก์ชันเฉพาะ
วิธีที่คอลัมน์ได้รับการประเมินในการรวมจะแตกต่างจากวิธีที่ Excel จัดการการรวม ตัวอย่าง อาจ ช่วย แสดง ให้ เห็น ได้
สมมติว่าคุณต้องการหาผลรวมของค่าในคอลัมน์ Amount ในตาราง Sales ดังนั้นคุณจึงสร้างสูตรต่อไปนี้
=SUM('Sales'[Amount])
ในกรณีที่ง่ายที่สุด ฟังก์ชันจะรับค่าจากคอลัมน์ที่ไม่ได้กรอง และผลลัพธ์จะเหมือนกับใน Excel ซึ่งจะบวกค่าในคอลัมน์ Amount เสมอ อย่างไรก็ตาม ใน Power Pivot สูตรจะถูกแปลความว่า "รับค่าในจํานวนสําหรับแต่ละแถวของตารางยอดขาย แล้วบวกค่าแต่ละค่าเหล่านั้น Power Pivot จะประเมินแถวแต่ละแถวที่มีการรวม และคํานวณค่าสเกลาเดียวสําหรับแต่ละแถว แล้วทําการรวมค่าเหล่านั้น ดังนั้น ผลลัพธ์ของสูตรอาจแตกต่างกันถ้ามีการใช้ตัวกรองกับตาราง หรือถ้าค่าถูกคํานวณโดยยึดตามการรวมอื่นๆ ที่อาจถูกกรอง สําหรับข้อมูลเพิ่มเติม ให้ดูบริบทในสูตร DAX
ฟังก์ชันตัวแสดงเวลาของ DAX
นอกเหนือจากฟังก์ชันการรวมตารางที่อธิบายไว้ในส่วนก่อนหน้าแล้ว DAX ยังมีฟังก์ชันการรวมที่ทํางานกับวันที่และเวลาที่คุณระบุ เพื่อให้ตัวแสดงเวลาในตัว ฟังก์ชันเหล่านี้ใช้ช่วงของวันที่เพื่อรับค่าที่เกี่ยวข้องและรวมค่า คุณยังสามารถเปรียบเทียบค่าข้ามช่วงวันที่ได้
ตารางต่อไปนี้แสดงรายการฟังก์ชันตัวแสดงเวลาที่สามารถใช้สําหรับการรวม
| ฟังก์ชัน | ใช้ |
|---|---|
|
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR |
คํานวณค่าที่จุดสิ้นสุดปฏิทินของช่วงเวลาที่ระบุ |
|
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR |
คํานวณค่าที่ช่วงสุดท้ายปฏิทินของช่วงเวลาก่อนหน้าช่วงเวลาที่ระบุ |
|
TOTALMTD TOTALYTD TOTALQTD |
คํานวณค่าในช่วงที่เริ่มต้นที่วันแรกของช่วงเวลาและสิ้นสุดที่วันที่ล่าสุดในคอลัมน์วันที่ที่ระบุ |
ฟังก์ชันอื่นๆ ในส่วนฟังก์ชันตัวแสดงเวลา (ฟังก์ชันตัวแสดงเวลา) เป็นฟังก์ชันที่สามารถใช้เพื่อดึงข้อมูลวันที่หรือช่วงของวันที่แบบกําหนดเองเพื่อใช้ในการรวม ตัวอย่างเช่น คุณสามารถใช้ฟังก์ชัน DATESINPERIOD เพื่อส่งกลับช่วงของวันที่ และใช้ชุดของวันที่นั้นเป็นอาร์กิวเมนต์ให้กับฟังก์ชันอื่นเพื่อคํานวณการรวมแบบกําหนดเองสําหรับวันที่เหล่านั้นเท่านั้น