การกรอง PivotTable หลายรายการพร้อมกัน

เมื่อสร้างรายงานที่ซับซ้อนและโดยเฉพาะอย่างยิ่งแดชบอร์ดใน Microsoft Excel บ่อยครั้งจำเป็นต้องกรองตารางสาระสำคัญหลายตารางพร้อมกัน เรามาดูกันว่าสิ่งนี้สามารถนำไปใช้ได้อย่างไร

วิธีที่ 1: ตัวแบ่งส่วนข้อมูลทั่วไปสำหรับการกรองจุดหมุนบนแหล่งข้อมูลเดียวกัน

หากเดือยถูกสร้างขึ้นบนพื้นฐานของตารางข้อมูลต้นทางเดียว วิธีที่ง่ายที่สุดคือการใช้ตารางเหล่านี้เพื่อกรองพร้อมกัน ส่วน เป็นตัวกรองปุ่มกราฟิกที่เชื่อมต่อกับตารางสาระสำคัญทั้งหมดพร้อมกัน

หากต้องการเพิ่ม ให้เลือกเซลล์ใดก็ได้ในข้อมูลสรุปและบนแท็บ การวิเคราะห์ เลือกทีม วางชิ้น (วิเคราะห์ — ตัวแบ่งส่วนข้อมูลแทรก). ในหน้าต่างที่เปิดขึ้น ให้เลือกช่องสำหรับคอลัมน์ที่คุณต้องการกรองข้อมูลและคลิก OK:

ตามค่าเริ่มต้น ตัวแบ่งส่วนข้อมูลที่สร้างขึ้นจะกรองเฉพาะจุดหมุนที่สร้างขึ้นเท่านั้น อย่างไรก็ตาม การใช้ปุ่ม รายงานการเชื่อมต่อ (รายงานการเชื่อมต่อ) แถบ ชิ้น (ชิ้น) เราสามารถเพิ่มตารางสรุปอื่นๆ ลงในรายการตารางที่กรองได้อย่างง่ายดาย:

วิธีที่ 2 การแบ่งส่วนทั่วไปสำหรับการกรองข้อมูลสรุปจากแหล่งต่างๆ

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

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

สมมติว่าเรามีสองตารางสำหรับค่าขายและค่าขนส่งเป็นข้อมูลป้อนเข้า:

สมมติว่าเรากำลังเผชิญกับงานสร้างบทสรุปของเราเองสำหรับแต่ละรายการ แล้วกรองข้อมูลเหล่านั้นพร้อมๆ กันตามเมืองที่มีส่วนร่วม

เราทำสิ่งต่อไปนี้:

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

2. โหลดตารางทั้งสองลงในแบบจำลองโดยใช้ปุ่ม เพิ่มไปยังโมเดลข้อมูล บนแท็บ Power Pivot

จะไม่สามารถเชื่อมโยงตารางเหล่านี้ในแบบจำลองได้โดยตรง เนื่องจากในขณะที่ Power Pivot รองรับเฉพาะความสัมพันธ์แบบหนึ่ง-ต่อ-กลุ่ม กล่าวคือ ต้องการให้ตารางใดตารางหนึ่งไม่ซ้ำกันในคอลัมน์ที่เรากำลังเชื่อมโยง เรามีเหมือนกันทั้งสองตารางในสนาม เมือง มีการทำซ้ำ ดังนั้นเราจึงจำเป็นต้องสร้างตารางค้นหาระดับกลางอีกรายการหนึ่งพร้อมรายชื่อเมืองที่ไม่ซ้ำจากทั้งสองตาราง วิธีที่ง่ายที่สุดในการทำเช่นนี้คือการใช้ฟังก์ชัน Add-in ของ Power Query ซึ่งมีอยู่ใน Excel ตั้งแต่เวอร์ชัน 2016 (และสำหรับ Excel 2010-2013 จะสามารถดาวน์โหลดได้ฟรีจากเว็บไซต์ของ Microsoft)

3. เมื่อเลือกเซลล์ใดก็ได้ในตาราง "อัจฉริยะ" เราจะโหลดเซลล์ทีละเซลล์ใน Power Query ด้วยปุ่ม จากตาราง/ช่วง แถบ ข้อมูล (ข้อมูล — จากตาราง/ช่วง) จากนั้นในหน้าต่าง Power Query ให้เลือก on หลัก ทีม ปิดและโหลด – ปิดและโหลดใน (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่…) และตัวเลือกการนำเข้า เพียงแค่สร้างการเชื่อมต่อ (สร้างการเชื่อมต่อเท่านั้น):

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

5. ลบคอลัมน์ทั้งหมดยกเว้นคอลัมน์ เมืองโดยคลิกขวาที่ชื่อและเลือกคำสั่ง ลบคอลัมน์อื่นๆ (ลบคอลัมน์อื่น) จากนั้นลบชื่อเมืองที่ซ้ำกันทั้งหมดโดยคลิกขวาที่ส่วนหัวของคอลัมน์อีกครั้งและเลือกคำสั่ง ลบรายการที่ซ้ำกัน (ลบรายการที่ซ้ำกัน):

6. รายการอ้างอิงที่สร้างขึ้นจะถูกอัปโหลดไปยังตัวแบบข้อมูลผ่าน หน้าแรก — ปิดและโหลด — ปิดและโหลดใน (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่…) และเลือกตัวเลือก เพียงแค่สร้างการเชื่อมต่อ (สร้างการเชื่อมต่อเท่านั้น) และที่สำคัญที่สุด! – เปิดช่องทำเครื่องหมาย เพิ่มข้อมูลนี้ลงในโมเดลข้อมูล (เพิ่มข้อมูลนี้ลงในโมเดลข้อมูล):

7. ตอนนี้เราทำได้แล้ว กลับไปที่หน้าต่าง Power Pivot (tab PowerPivot - ปุ่ม การจัดการ) เปลี่ยนเป็น มุมมองแผนภูมิ (มุมมองแผนภาพ) และเชื่อมโยงตารางการขายและค่าขนส่งของเราผ่านไดเร็กทอรีกลางของเมืองที่สร้างขึ้น (โดยการลากฟิลด์ระหว่างตาราง):

8. ตอนนี้คุณสามารถสร้างตารางเดือยที่จำเป็นสำหรับโมเดลที่สร้างขึ้นโดยใช้ปุ่ม ตารางสรุป (ตารางเดือย) on หลัก (บ้าน) แท็บในหน้าต่าง Power Pivot และโดยการเลือกเซลล์ใดๆ ใน pivot ใดๆ บนแท็บ การวิเคราะห์ เพิ่มปุ่มสไลซ์ วางชิ้น (วิเคราะห์ — ตัวแบ่งส่วนข้อมูลแทรก) แล้วเลือกสไลซ์ในกล่องรายการ เมือง ในไดเร็กทอรีที่เพิ่ม:

ตอนนี้โดยคลิกที่ปุ่มที่คุ้นเคย รายงานการเชื่อมต่อ on แถบสไลซ์ (ตัวแบ่งส่วนข้อมูล — รายงานการเชื่อมต่อ) เราจะเห็นข้อมูลสรุปทั้งหมดของเรา เนื่องจากตอนนี้สร้างจากตารางที่มาที่เกี่ยวข้องกัน ยังคงเปิดใช้งานช่องทำเครื่องหมายที่ขาดหายไปและคลิกที่ OK – และตัวแบ่งส่วนข้อมูลของเราจะเริ่มกรองตารางสาระสำคัญที่เลือกทั้งหมดพร้อมกัน