การสลับการคำนวณใน PivotTable ด้วยตัวแบ่งส่วนข้อมูล

ตัวแบ่งส่วนข้อมูลในตาราง Pivot สามารถใช้ได้ไม่เฉพาะในวิธีคลาสสิกเท่านั้น - เพื่อกรองข้อมูลต้นฉบับ แต่ยังสามารถใช้สลับไปมาระหว่างการคำนวณประเภทต่างๆ ในพื้นที่ค่า:

การดำเนินการนี้ง่ายมาก สิ่งที่คุณต้องมีคือสูตรสองสามสูตรและตารางเสริม เราจะทำสิ่งนี้ทั้งหมดไม่ใช่ในการสรุปตามปกติ แต่ในข้อมูลสรุปที่สร้างตามแบบจำลองข้อมูล Power Pivot

ขั้นตอนที่ 1. การเชื่อมต่อ Add-in ของ Power Pivot

ถ้าแท็บของ Add-in ของ Power Pivot ไม่ปรากฏใน Excel ของคุณ คุณจะต้องเปิดใช้งานก่อน มีสองตัวเลือกสำหรับสิ่งนี้:

• แถบ ผู้พัฒนา - ปุ่ม ส่วนเสริม COM (ผู้พัฒนา — ส่วนเสริม COM)
• ไฟล์ – ตัวเลือก – ส่วนเสริม – COM Add-in – Go (ไฟล์ — ตัวเลือก — ส่วนเสริม — COM-Add-in — ไปที่)

หากวิธีนี้ไม่ได้ผล ให้ลองเริ่ม Microsoft Excel ใหม่

ขั้นตอนที่ 2: โหลดข้อมูลลงใน Power Pivot Data Model

เราจะมีสองตารางเป็นข้อมูลเริ่มต้น:

อย่างแรกคือตารางที่มียอดขายซึ่งเราจะสร้างสรุปในภายหลัง ที่สองคือตารางเสริมที่ป้อนชื่อสำหรับปุ่มของสไลซ์ในอนาคต

ตารางทั้งสองนี้จำเป็นต้องแปลงเป็น "สมาร์ท" (ไดนามิก) ด้วยแป้นพิมพ์ลัด Ctrl+T หรือทีม หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง) และเป็นที่พึงปรารถนาที่จะให้ชื่อสามัญแก่พวกเขาบนแท็บ นวกรรมิก (ออกแบบ). ให้เป็นเช่นนั้น ตัวอย่างเช่น การขาย и บริการ.

หลังจากนั้น แต่ละตารางจะต้องโหลดลงในตัวแบบข้อมูล – สำหรับสิ่งนี้เราใช้บนแท็บ PowerPivot ปุ่ม เพิ่มไปยังโมเดลข้อมูล (เพิ่มไปยังโมเดลข้อมูล).

ขั้นตอนที่ 3 สร้างการวัดเพื่อกำหนดปุ่มที่กดบนสไลซ์

ฟิลด์จากการคำนวณใน PivotTable ตามตัวแบบข้อมูลเรียกว่า มาตรการ. มาสร้างการวัดที่จะแสดงชื่อของปุ่มที่ถูกกดบนสไลซ์ในอนาคต ในการดำเนินการนี้ ในตารางใดๆ ของเรา ให้เลือกเซลล์ว่างในแผงการคำนวณด้านล่าง และป้อนโครงสร้างต่อไปนี้ลงในแถบสูตร:

ที่นี่ชื่อของวัดมาก่อน (กดปุ่ม) จากนั้นตามด้วยเครื่องหมายทวิภาคและเครื่องหมายเท่ากับ สูตรคำนวณโดยใช้ฟังก์ชัน VALUES DAX รวมอยู่ใน Power Pivot

หากคุณทำซ้ำสิ่งนี้ไม่ใช่ใน Power Pivot แต่ใน Power BI เครื่องหมายทวิภาคก็ไม่จำเป็นและแทน VALUES คุณสามารถใช้คู่หูที่ทันสมัยกว่านี้ได้ - ฟังก์ชั่น ค่าที่เลือก.

เราไม่ใส่ใจกับข้อผิดพลาดในส่วนล่างของหน้าต่างที่ปรากฏหลังจากป้อนสูตร ซึ่งเกิดขึ้น เนื่องจากเรายังไม่มีข้อมูลสรุปและส่วนที่มีบางสิ่งถูกคลิก

ขั้นตอนที่ 4 สร้างการวัดสำหรับการคำนวณบนปุ่มที่กด

ขั้นตอนต่อไปคือการสร้างการวัดสำหรับตัวเลือกการคำนวณที่แตกต่างกันขึ้นอยู่กับมูลค่าของการวัดก่อนหน้า กดปุ่ม. สูตรนี้ซับซ้อนกว่าเล็กน้อย:

มาทำลายมันทีละส่วน:

1. ฟังก์ชัน สวิตช์ – อะนาล็อกของ IF ที่ซ้อนกัน – ตรวจสอบการปฏิบัติตามเงื่อนไขที่ระบุและส่งกลับค่าที่แตกต่างกันขึ้นอยู่กับการปฏิบัติตามเงื่อนไขบางอย่าง
2. ฟังก์ชัน จริง() – ให้ตรรกะ "จริง" เพื่อให้เงื่อนไขที่ตรวจสอบในภายหลังโดยฟังก์ชัน SWITCH ทำงานได้ก็ต่อเมื่อตรงตามเงื่อนไข กล่าวคือ ความจริง
3. จากนั้นเราจะตรวจสอบค่าของการวัดที่กดปุ่มและคำนวณผลลัพธ์สุดท้ายสำหรับสามตัวเลือกที่แตกต่างกัน – เป็นผลรวมของค่าใช้จ่าย การตรวจสอบโดยเฉลี่ย และจำนวนผู้ใช้ที่ไม่ซ้ำ ในการนับค่าที่ไม่ซ้ำ ให้ใช้ฟังก์ชัน ความแตกต่างและสำหรับการปัดเศษ – รอบที่.
4. หากไม่ตรงตามเงื่อนไขสามข้อข้างต้น อาร์กิวเมนต์สุดท้ายของฟังก์ชัน SWITCH จะแสดงขึ้น เราตั้งค่าให้เป็นฟังก์ชันจำลองโดยใช้ฟังก์ชัน ว่างเปล่า().

ขั้นตอนที่ 5. สร้างสรุปและเพิ่มสไลซ์

ยังคงต้องส่งคืนจาก Power Pivot ไปยัง Excel และสร้างตาราง Pivot สำหรับข้อมูลและการวัดทั้งหมดของเรา ในการดำเนินการนี้ ในหน้าต่าง Power Pivot บน หลัก คำสั่งแท็บเลือก ตารางสรุป (หน้าแรก — ตาราง Pivot).

แล้ว:

1. เราโยนสนาม ผลิตภัณฑ์ จากตารางที่ การขาย สู่พื้นที่ แถว (แถว).
2. ลงสนามที่นั่น ผล จากตารางที่ บริการ.
3. คลิกขวาที่สนาม ผลและเลือกทีม เพิ่มเป็นชิ้น (เพิ่มเป็นตัวแบ่งส่วนข้อมูล).
4. โยนวัดที่สอง สรุป จากตารางที่ บริการ สู่พื้นที่ ความคุ้มค่า (ค่า).

อันที่จริงนี่คือกลอุบายทั้งหมด ตอนนี้คุณสามารถคลิกที่ปุ่มตัวแบ่งส่วนข้อมูล – และผลรวมในตาราง Pivot จะสลับไปยังฟังก์ชันที่คุณต้องการ

ความงาม🙂

• ประโยชน์ของ Pivot ตามตัวแบบข้อมูล
• การวิเคราะห์ตามแผนในตารางสาระสำคัญบน Power Pivot
• สร้างฐานข้อมูลใน Excel โดยใช้ Power Pivot add-in