ตาราง Excel Pivot - คู่มือฉบับย่อ

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

คุณสมบัติหลักของ PivotTable มีดังนี้ -

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

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

การสร้าง PivotTable

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

คุณจะได้เรียนรู้วิธีสร้าง PivotTable จากช่วงข้อมูลหรือตาราง Excel ในบท - การสร้าง PivotTable จากตารางหรือช่วง

Excel ช่วยให้คุณสร้าง PivotTable ได้อย่างมีประสิทธิภาพยิ่งขึ้นจากหลายตารางแหล่งข้อมูลที่แตกต่างกันและแหล่งข้อมูลภายนอก มีชื่อว่า PowerPivot ซึ่งทำงานบนฐานข้อมูลที่เรียกว่า Data Model คุณจะได้เรียนรู้เครื่องมือไฟฟ้าของ Excel เหล่านี้ในบทช่วยสอนอื่น ๆ ในไลบรารีบทช่วยสอนนี้

ก่อนอื่นคุณต้องรู้เกี่ยวกับ PivotTable ตามที่อธิบายไว้ในบทช่วยสอนนี้ก่อนที่คุณจะเข้าร่วมในเครื่องมือไฟฟ้า

เค้าโครง PivotTable - เขตข้อมูลและพื้นที่

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

คุณจะได้เรียนรู้เกี่ยวกับเขตข้อมูลและพื้นที่ PivotTable ในบท - เขตข้อมูล PivotTable และพื้นที่ PivotTable

การสำรวจข้อมูลด้วย PivotTable

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

คุณจะเห็นภาพรวมของตัวเลือกเหล่านี้ในบท - การสำรวจข้อมูลด้วย PivotTable

สรุปค่า

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

คุณจะได้เรียนรู้วิธีการใช้ประเภทการคำนวณบน PivotTable ในบท - การสรุปค่าตามประเภทการคำนวณต่างๆ

การอัปเดต PivotTable

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

คุณจะได้เรียนรู้วิธีต่างๆในการรีเฟรชข้อมูลในบท - การอัปเดต PivotTable

รายงาน PivotTable

หลังจากสำรวจและสรุปข้อมูลด้วย PivotTable แล้วคุณจะนำเสนอเป็นรายงาน รายงาน PivotTable เป็นแบบโต้ตอบโดยมีความพิเศษที่แม้แต่คนที่ไม่คุ้นเคยกับ Excel ก็สามารถใช้งานได้โดยสัญชาตญาณ เนื่องจากลักษณะไดนามิกโดยธรรมชาติพวกเขาจะช่วยให้คุณเปลี่ยนมุมมองได้อย่างรวดเร็วของรายงานเพื่อแสดงระดับรายละเอียดที่ต้องการหรือเพื่อมุ่งเน้นไปที่รายการเฉพาะที่ผู้ชมแสดงความสนใจ

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

คุณสามารถสร้าง PivotTable จากช่วงข้อมูลหรือจากตาราง Excel ในทั้งสองกรณีแถวแรกของข้อมูลควรมีส่วนหัวของคอลัมน์

หากคุณแน่ใจในช่องที่จะรวมอยู่ใน PivotTable และเค้าโครงที่คุณต้องการให้มีคุณสามารถเริ่มต้นด้วย PivotTable ที่ว่างเปล่าและสร้าง PivotTable

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

การสร้าง PivotTable จากช่วงข้อมูล

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

ในการสร้าง PivotTable จากช่วงข้อมูลนี้ให้ทำดังต่อไปนี้ -

ตรวจสอบว่าแถวแรกมีส่วนหัว คุณต้องการส่วนหัวเพราะจะเป็นชื่อเขตข้อมูลใน PivotTable ของคุณ
ตั้งชื่อช่วงข้อมูลเป็น SalesData_Range
คลิกที่ช่วงข้อมูล - SalesData_Range
คลิกแท็บ INSERT บน Ribbon

คลิก PivotTable ในกลุ่มตาราง Create PivotTable กล่องโต้ตอบปรากฏขึ้น

ในกล่องโต้ตอบสร้าง PivotTable ภายใต้ Choose the data that you want to analyzeคุณสามารถเลือกตารางหรือช่วงจากสมุดงานปัจจุบันหรือใช้แหล่งข้อมูลภายนอก

ในขณะที่คุณสร้าง PivotTable จากช่วงข้อมูลให้เลือกสิ่งต่อไปนี้จากกล่องโต้ตอบ -

เลือก Select a table or range.
ในกล่องตาราง / ช่วงพิมพ์ชื่อช่วง - SalesData_Range
เลือกแผ่นงานใหม่ภายใต้เลือกตำแหน่งที่คุณต้องการวางรายงาน PivotTable แล้วคลิกตกลง

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

แผ่นงานใหม่ถูกแทรกลงในสมุดงานของคุณ แผ่นงานใหม่ประกอบด้วย PivotTable ที่ว่างเปล่า ตั้งชื่อเวิร์กชีต - Range-PivotTable

ดังที่คุณสามารถสังเกตได้ PivotTable Fieldsรายการจะปรากฏทางด้านขวาของแผ่นงานซึ่งมีชื่อส่วนหัวของคอลัมน์ในช่วงข้อมูล นอกจากนี้บน Ribbon เครื่องมือ PivotTable - ANALYZE และ DESIGN จะปรากฏขึ้น

การเพิ่มเขตข้อมูลใน PivotTable

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

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

คลิกที่พนักงานขายฟิลด์ในรายการเขตข้อมูล PivotTable แล้วลากไปยังพื้นที่แถว
คลิกช่อง Month ในรายการ PivotTable Fields แล้วลากไปยังพื้นที่ ROWS
คลิกที่ Order Amount และลากไปที่พื้นที่ ∑ VALUES

PivotTable แรกของคุณพร้อมแล้วดังที่แสดงด้านล่าง

สังเกตว่าคอลัมน์สองคอลัมน์ปรากฏใน PivotTable โดยคอลัมน์หนึ่งมีป้ายกำกับแถวที่คุณเลือกไว้ ได้แก่ พนักงานขายและเดือนและคอลัมน์ที่สองมีผลรวมของยอดการสั่งซื้อ นอกจากยอดรวมของยอดสั่งซื้อเดือนที่ชาญฉลาดสำหรับพนักงานขายแต่ละคนแล้วคุณยังจะได้รับผลรวมย่อยที่แสดงยอดขายทั้งหมดของบุคคลนั้น หากคุณเลื่อนแผ่นงานลงคุณจะพบแถวสุดท้ายเป็นผลรวมทั้งหมดซึ่งแสดงถึงยอดขายทั้งหมด

คุณจะได้เรียนรู้เพิ่มเติมเกี่ยวกับการสร้าง PivotTables ตามความต้องการเมื่อคุณดำเนินการผ่านบทช่วยสอนนี้

การสร้าง PivotTable จากตาราง

พิจารณาตาราง Excel ต่อไปนี้ที่มีข้อมูลการขายเดียวกันกับในส่วนก่อนหน้านี้ -

ตาราง Excel จะมีชื่อโดยเนื้อแท้และคอลัมน์จะมีส่วนหัวซึ่งเป็นข้อกำหนดในการสร้าง PivotTable สมมติว่าชื่อตารางคือ SalesData_Table

ในการสร้าง PivotTable จากตาราง Excel นี้ให้ทำดังต่อไปนี้ -

คลิกที่ตาราง - SalesData_Table
คลิกแท็บ INSERT บน Ribbon
คลิก PivotTable ในกลุ่มตาราง Create PivotTable กล่องโต้ตอบปรากฏขึ้น

คลิกเลือกตารางหรือช่วง
ในกล่องตาราง / ช่วงพิมพ์ชื่อตาราง - SalesData_Table
เลือกแผ่นงานใหม่ภายใต้ Choose where you want the PivotTable report to be placed. คลิกตกลง

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

คุณสามารถเพิ่มเขตข้อมูลลงใน PivotTable ได้ตามที่คุณเห็นในส่วน - การเพิ่มเขตข้อมูลใน PivotTable ก่อนหน้านี้ในบทนี้

การสร้าง PivotTable ด้วย PivotTables ที่แนะนำ

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

ในการสร้าง PivotTable จากตาราง Excel SalesData-Table โดยใช้ PivotTables ที่แนะนำให้ดำเนินการดังนี้ -

คลิกที่ตาราง SalesData-Table
คลิกที่แท็บ INSERT
คลิก PivotTables ที่แนะนำในกลุ่มตาราง กล่องโต้ตอบ PivotTables ที่แนะนำจะปรากฏขึ้น

ในกล่องโต้ตอบ PivotTables ที่แนะนำ PivotTables ที่กำหนดเองที่เป็นไปได้ที่เหมาะกับข้อมูลของคุณจะปรากฏขึ้น

คลิกที่ตัวเลือก PivotTable แต่ละตัวเพื่อดูตัวอย่างทางด้านขวา
คลิกที่ PivotTable - จำนวนการสั่งซื้อโดยพนักงานขายและเดือนแล้วคลิกตกลง

คุณจะได้รับตัวอย่างทางด้านขวา

PivotTable ที่เลือกจะปรากฏบนแผ่นงานใหม่ในสมุดงานของคุณ

คุณจะเห็นได้ว่ามีการเลือกเขตข้อมูล PivotTable - พนักงานขายภูมิภาคจำนวนคำสั่งซื้อและเดือน ในจำนวนนี้ภูมิภาคและพนักงานขายอยู่ในพื้นที่ ROWS เดือนอยู่ในพื้นที่ COLUMNS และผลรวมของจำนวนคำสั่งซื้ออยู่ในพื้นที่ ∑ VALUES

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

PivotTable Fields คือบานหน้าต่างงานที่เชื่อมโยงกับ PivotTable บานหน้าต่างงานเขตข้อมูล PivotTable ประกอบด้วยเขตข้อมูลและพื้นที่ ตามค่าเริ่มต้นบานหน้าต่างงานจะปรากฏที่ด้านขวาของหน้าต่างพร้อมกับฟิลด์ที่แสดงด้านบนพื้นที่

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

ที่ด้านล่างของ Task Pane คุณจะพบตัวเลือก - Defer Layout Update โดยมีปุ่ม UPDATE อยู่ข้างๆ

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

ในบทนี้คุณจะเข้าใจรายละเอียดเกี่ยวกับฟิลด์ ในบทถัดไปคุณจะเข้าใจรายละเอียดเกี่ยวกับพื้นที่

บานหน้าต่างงานเขตข้อมูล PivotTable

คุณสามารถค้นหาบานหน้าต่างงานเขตข้อมูล PivotTable บนแผ่นงานที่คุณมี PivotTable หากต้องการดูบานหน้าต่างงานเขตข้อมูล PivotTable ให้คลิกที่ PivotTable ในกรณีที่ไม่แสดงบานหน้าต่างงานเขตข้อมูล PivotTable ให้ตรวจสอบ Ribbon สำหรับสิ่งต่อไปนี้ -

คลิกแท็บวิเคราะห์ภายใต้เครื่องมือ PIVOTTABLE บน Ribbon
ตรวจสอบว่ามีการเลือกรายการเขตข้อมูล (กล่าวคือเน้น) ในกลุ่มแสดงหรือไม่
หากไม่ได้เลือกรายการเขตข้อมูลให้คลิกรายการนั้น

บานหน้าต่างงานเขตข้อมูล PivotTable จะแสดงทางด้านขวาของหน้าต่างพร้อมด้วยชื่อเรื่อง - เขตข้อมูล PivotTable

การย้ายบานหน้าต่างงานเขตข้อมูล PivotTable

ด้านขวาของชื่อ PivotTable ฟิลด์ของ PivotTable

บานหน้าต่างงานคุณจะได้พบกับปุ่ม สิ่งนี้แสดงถึงตัวเลือกบานหน้าต่างงาน

คลิกที่ปุ่ม ตัวเลือกบานหน้าต่างงาน - ย้ายขนาดและปิดจะปรากฏในรายการแบบเลื่อนลง

คุณสามารถย้ายบานหน้าต่างงาน PivotTables ไปที่ใดก็ได้ที่คุณต้องการในหน้าต่างดังนี้ -

คลิกย้ายในรายการแบบเลื่อนลง

ปุ่มปรากฏบนบานหน้าต่างงาน
คลิก

ไอคอนแล้วลากบานหน้าต่างไปยังตำแหน่งที่คุณต้องการวาง คุณสามารถวาง Task Pane ถัดจาก PivotTable ตามที่ระบุด้านล่าง

คุณสามารถวาง Task Pane ทางด้านซ้ายของหน้าต่างตามที่ระบุด้านล่าง

การปรับขนาดบานหน้าต่างงานเขตข้อมูล PivotTable

คุณสามารถปรับขนาดบานหน้าต่างงาน PivotTables ได้เช่นเพิ่ม / ลดความยาวและ / หรือความกว้างของบานหน้าต่างงานดังนี้ -

คลิกที่ตัวเลือกบานหน้าต่างงาน

ซึ่งอยู่ทางด้านขวาของชื่อเรื่อง - เขตข้อมูล PivotTable
คลิกที่ขนาดในรายการแบบเลื่อนลง
ใช้สัญลักษณ์ ⇔ เพื่อเพิ่ม / ลดความกว้างของบานหน้าต่างงาน
ใช้สัญลักษณ์ ⇕ เพื่อเพิ่ม / ลดความกว้างของบานหน้าต่างงาน

ในพื้นที่ ∑ VALUES เพื่อให้มองเห็นผลรวมของจำนวนคำสั่งซื้อได้อย่างสมบูรณ์คุณสามารถปรับขนาดบานหน้าต่างงานได้ตามที่ระบุด้านล่าง

เขตข้อมูล PivotTable

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

ตารางและเขตข้อมูลที่เกี่ยวข้องพร้อมกล่องกาเครื่องหมายแสดงถึงข้อมูล PivotTable ของคุณ ในขณะที่คุณสามารถเลือก / ยกเลิกการเลือกฟิลด์แบบสุ่มคุณสามารถเปลี่ยน PivotTable ได้อย่างรวดเร็วโดยเน้นข้อมูลสรุปที่คุณต้องการรายงานหรือนำเสนอ

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

เหนือรายการเขตข้อมูลคุณจะพบการดำเนินการเลือกเขตข้อมูลเพื่อเพิ่มในรายงาน ทางด้านขวาคุณจะพบปุ่ม

ซึ่งแสดงถึงเครื่องมือ

คลิกที่ปุ่มเครื่องมือ

ในรายการแบบเลื่อนลงคุณจะพบสิ่งต่อไปนี้ -

ห้าตัวเลือกเค้าโครงที่แตกต่างกันสำหรับฟิลด์และพื้นที่
สองตัวเลือกสำหรับการเรียงลำดับของฟิลด์ในรายการเขตข้อมูล -
- เรียงลำดับ A ถึง Z
- จัดเรียงในลำดับแหล่งข้อมูล

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

โดยปกติคุณสามารถรักษาคำสั่งเริ่มต้นได้ อย่างไรก็ตามในบางครั้งคุณอาจพบเขตข้อมูลจำนวนมากในตารางและอาจไม่คุ้นเคยกับฟิลด์เหล่านี้ ในกรณีนี้คุณสามารถจัดเรียงฟิลด์ตามลำดับตัวอักษรได้โดยคลิกที่ - เรียง A ถึง Z ในรายการเครื่องมือแบบเลื่อนลง จากนั้นรายการเขตข้อมูล PivotTable จะมีลักษณะดังนี้ -

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

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

มีพื้นที่ PivotTable สี่พื้นที่ -

ROWS.
COLUMNS.
FILTERS.
∑ VALUES (อ่านว่าค่าสรุป)

ข้อความ - Drag fields between areas below ปรากฏเหนือพื้นที่

ด้วยพื้นที่ PivotTable คุณสามารถเลือก -

เขตข้อมูลใดที่จะแสดงเป็นแถว (พื้นที่ ROWS)
ฟิลด์ใดที่จะแสดงเป็นคอลัมน์ (พื้นที่ COLUMNS)
วิธีสรุปข้อมูลของคุณ (พื้นที่ ∑ VALUES)
ตัวกรองสำหรับฟิลด์ใด ๆ (พื้นที่ FILTERS)

คุณสามารถลากฟิลด์ข้ามพื้นที่เหล่านี้และสังเกตว่าเค้าโครง PivotTable เปลี่ยนแปลงไปอย่างไร

แถว

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

คุณสามารถเลือกที่จะลากเขตข้อมูลไปยังพื้นที่แถว ฟิลด์ที่ใส่ในพื้นที่ ROWS จะปรากฏเป็นแถวใน PivotTable โดยที่ Row Labels เป็นค่าของฟิลด์ที่เลือก

ตัวอย่างเช่นพิจารณาตารางข้อมูลการขาย

ลากพนักงานขายภาคสนามไปยังพื้นที่แถว
ลากเขตข้อมูลเดือนไปยังพื้นที่แถว

PivotTable ของคุณปรากฏขึ้นพร้อมกับคอลัมน์หนึ่งคอลัมน์ที่มีป้ายกำกับแถว - พนักงานขายและเดือนและแถวสุดท้ายเป็นผลรวมทั้งหมดตามที่ระบุด้านล่าง

คอลัมน์

คุณสามารถลากเขตข้อมูลไปยังพื้นที่คอลัมน์

ฟิลด์ที่ใส่ในพื้นที่ COLUMNS จะปรากฏเป็นคอลัมน์ใน PivotTable โดยที่ Column Labels เป็นค่าของฟิลด์ที่เลือก

ลากเขตข้อมูลภูมิภาคไปยังพื้นที่คอลัมน์ PivotTable ของคุณจะปรากฏขึ้นพร้อมกับคอลัมน์แรกที่มีป้ายกำกับแถว - พนักงานขายและเดือนถัดไปสี่คอลัมน์ที่มีป้ายชื่อคอลัมน์ - ภูมิภาคและคอลัมน์สุดท้ายยอดรวมตามที่ระบุด้านล่าง

ลากเขตข้อมูล Month จาก ROWS ถึง COLUMNS
ลากเขตข้อมูลภูมิภาคจาก COLUMNS ไปยัง ROWS เค้าโครง PivotTable ของคุณเปลี่ยนไปตามที่ระบุด้านล่าง

คุณจะเห็นว่าตอนนี้มีเพียงห้าคอลัมน์เท่านั้น - คอลัมน์แรกที่มีป้ายกำกับแถวสามคอลัมน์ที่มีป้ายชื่อคอลัมน์และคอลัมน์สุดท้ายที่มีผลรวมทั้งหมด

จำนวนแถวและคอลัมน์จะขึ้นอยู่กับจำนวนค่าที่คุณมีในฟิลด์เหล่านั้น

∑ ค่านิยม

การใช้ PivotTable เบื้องต้นคือการสรุปค่า ดังนั้นโดยการวางฟิลด์ที่คุณต้องการสรุปข้อมูล∑ VALUES คุณมาถึงตารางสรุป

ลากฟิลด์ Order Amount ไปที่ ∑ VALUES.
ลากเขตข้อมูลภูมิภาคไปที่ด้านบนของพนักงานขายเขตข้อมูลในพื้นที่แถว ขั้นตอนนี้คือการเปลี่ยนลำดับการซ้อน คุณจะได้เรียนรู้การซ้อนในบทนี้ - การทำรังใน PivotTable ในบทช่วยสอนนี้

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

ฟิลเตอร์

พื้นที่ตัวกรองคือการวางตัวกรองใน PivotTable สมมติว่าคุณต้องการแสดงผลลัพธ์แยกกันสำหรับภูมิภาคที่เลือกเท่านั้น

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

ดังที่คุณสังเกตได้ (ALL) จะปรากฏในตัวกรองตามค่าเริ่มต้นและ PivotTable จะแสดงข้อมูลสำหรับค่าทั้งหมดของภูมิภาค

คลิกที่ลูกศรทางด้านขวาของตัวกรอง
ทำเครื่องหมายที่ช่อง - เลือกหลายรายการ

ช่องทำเครื่องหมายจะปรากฏขึ้นสำหรับตัวเลือกทั้งหมดในรายการแบบเลื่อนลง โดยค่าเริ่มต้นกล่องทั้งหมดจะถูกเลือก

ทำเครื่องหมายในช่อง - เหนือและใต้
ล้างกล่องอื่น ๆ คลิกตกลง

PivotTable ได้รับการเปลี่ยนแปลงเพื่อให้สอดคล้องกับข้อมูลที่กรอง

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

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

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

คุณสามารถทำสิ่งต่อไปนี้ด้วย PivotTable -

จัดเรียงข้อมูล
กรองข้อมูล
ซ้อนช่อง PivotTable
ขยายและยุบเขตข้อมูล
จัดกลุ่มและยกเลิกการจัดกลุ่มค่าฟิลด์

การเรียงลำดับและการกรองข้อมูล

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

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

การทำรังการขยายและการยุบฟิลด์

คุณสามารถซ้อนช่องใน PivotTable เพื่อแสดงลำดับชั้นได้หากเกี่ยวข้องกับข้อมูลของคุณ คุณจะได้เรียนรู้สิ่งนี้ในบทนี้ - การซ้อนใน PivotTable ในบทช่วยสอนนี้

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

การจัดกลุ่มและการยกเลิกการจัดกลุ่มค่าฟิลด์

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

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

พิจารณา PivotTable ต่อไปนี้ซึ่งคุณมีข้อมูลสรุปข้อมูลการขายตามภูมิภาคพนักงานขายที่ชาญฉลาดและมีรายเดือน

การเรียงลำดับในเขตข้อมูล

คุณสามารถจัดเรียงข้อมูลใน PivotTable ด้านบนบนฟิลด์ที่อยู่ในแถวหรือคอลัมน์ - ภูมิภาคพนักงานขายและเดือน

ในการจัดเรียง PivotTable กับพนักงานขายภาคสนามให้ดำเนินการดังนี้ -

คลิกลูกศร

ในป้ายกำกับแถว
เลือกพนักงานขายในกล่องเลือกฟิลด์จากรายการดรอปดาวน์

ตัวเลือกการเรียงลำดับต่อไปนี้จะปรากฏขึ้น -

เรียงลำดับ A ถึง Z
เรียง Z ถึง A
ตัวเลือกการจัดเรียงเพิ่มเติม

นอกจากนี้ฟิลด์พนักงานขายจะเรียงลำดับจากน้อยไปมากตามค่าเริ่มต้น คลิกSort Z to A. ฟิลด์พนักงานขายจะเรียงลำดับจากมากไปหาน้อย

ในทำนองเดียวกันคุณสามารถจัดเรียงฟิลด์ในคอลัมน์ - เดือนโดยคลิกที่ลูกศร

ในป้ายชื่อคอลัมน์

การเรียงลำดับผลรวมย่อย

สมมติว่าคุณต้องการจัดเรียง PivotTable ตามยอดสั่งซื้อทั้งหมด - สูงสุดไปต่ำสุดในทุกภูมิภาค นั่นคือคุณต้องการจัดเรียง PivotTable บนผลรวมย่อย

คุณจะเห็นว่าไม่มีลูกศร

สำหรับผลรวมย่อย คุณยังคงสามารถจัดเรียง PivotTable บนผลรวมย่อยได้ดังนี้ -

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

ผลรวมย่อยในคอลัมน์ผลรวมทั้งหมดจะเรียงลำดับจากค่าสูงสุดไปต่ำสุดในทุกภูมิภาค

ในทำนองเดียวกันหากคุณต้องการจัดเรียง PivotTable บนพื้นที่ผลรวมย่อยอย่างชาญฉลาดให้ทำดังต่อไปนี้ -

คลิกขวาที่ผลรวมย่อยของภูมิภาคใดก็ได้ในคอลัมน์ผลรวมทั้งหมด
คลิกเรียงลำดับในรายการแบบเลื่อนลง
คลิกเรียงลำดับจากมากที่สุดไปหาน้อยที่สุดในรายการแบบเลื่อนลงที่สอง PivotTable จะได้รับการจัดเรียงตามผลรวมย่อยตามภูมิภาคที่ชาญฉลาด

ดังที่คุณสังเกตได้ว่าภาคใต้มียอดการสั่งซื้อสูงสุดในขณะที่ภาคเหนือมีจำนวนต่ำสุด

คุณยังสามารถจัดเรียง PivotTable ตามจำนวนเดือนทั้งหมดได้ดังนี้ -

คลิกขวาที่ผลรวมย่อยใด ๆ ในแถวผลรวมทั้งหมด
เลือกเรียงลำดับจากรายการแบบเลื่อนลง
เลือกเรียงลำดับจากมากที่สุดไปหาน้อยที่สุดจากรายการแบบเลื่อนลงที่สอง

PivotTable จะเรียงตามจำนวนเดือนทั้งหมดที่ชาญฉลาด

คุณสามารถสังเกตได้ว่าเดือนกุมภาพันธ์มียอดสั่งซื้อสูงสุดในขณะที่เดือนมีนาคมมีจำนวนต่ำสุด

ตัวเลือกการจัดเรียงเพิ่มเติม

สมมติว่าคุณต้องการจัดเรียง PivotTable ตามภูมิภาคจำนวนเงินทั้งหมดที่ชาญฉลาดในเดือนมกราคม

คลิกที่ลูกศร

ใน Row Labels
เลือกตัวเลือกการจัดเรียงเพิ่มเติมจากรายการแบบเลื่อนลง Sort (Region) กล่องโต้ตอบปรากฏขึ้น

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

คลิกช่องที่มี Region
คลิกผลรวมของยอดสั่งซื้อ

คลิก More Optionsปุ่ม. More Sort Options (Region) กล่องโต้ตอบปรากฏขึ้น

ดังที่คุณสามารถสังเกตได้ภายใต้เรียงตามผลรวมจะถูกเลือก ภายใต้สรุปลำดับการจัดเรียงปัจจุบันจะได้รับเป็นSort Region by Sum of Order Amount ตามลำดับจากน้อยไปมาก

คลิก Values in selected column: ภายใต้จัดเรียงตาม
พิมพ์ B5 ในช่องด้านล่าง

ดังที่คุณสามารถสังเกตได้ภายใต้สรุปลำดับการจัดเรียงปัจจุบันจะได้รับดังนี้ -

จัดเรียงภูมิภาคตาม Sum of Order Amountจากน้อยไปหามากโดยใช้ค่าในคอลัมน์นี้: มกราคม คลิกตกลง
กล่องโต้ตอบจัดเรียง (ภูมิภาค) จะปรากฏขึ้น เลือกจากมากไปหาน้อย (Z ถึง A) ตาม: ภายใต้ตัวเลือกการเรียงลำดับ

ภายใต้สรุปลำดับการจัดเรียงปัจจุบันจะได้รับดังนี้ -

จัดเรียงภูมิภาคตามผลรวมของจำนวนคำสั่งซื้อจากมากไปหาน้อยโดยใช้ค่าในคอลัมน์นี้: มกราคม คลิกตกลง PivotTable จะจัดเรียงตามภูมิภาคโดยใช้ค่าในเดือนมกราคม

ดังที่คุณสังเกตได้ในเดือนมกราคมตะวันตกมียอดสั่งซื้อสูงสุดในขณะที่ทิศตะวันตกเฉียงเหนือมีจำนวนต่ำสุด

การจัดเรียงข้อมูลด้วยตนเอง

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

วางเคอร์เซอร์บนลูกศร

ใน Row Labels หรือ Column Labels

AutoSort จะปรากฏขึ้นโดยแสดงลำดับการจัดเรียงปัจจุบันสำหรับแต่ละฟิลด์ใน PivotTable ตอนนี้สมมติว่าคุณต้องการเรียงลำดับเขตข้อมูลภูมิภาคตามลำดับ - ตะวันออกตะวันตกเหนือและใต้ คุณสามารถทำได้ด้วยตนเองดังต่อไปนี้ -

คลิกลูกศร

ใน Row Labels
เลือกภูมิภาคในกล่องเลือกฟิลด์จากรายการดรอปดาวน์
คลิก More Sort Options. กล่องโต้ตอบจัดเรียง (ภูมิภาค) จะปรากฏขึ้น
เลือกด้วยตนเอง (คุณสามารถลากรายการเพื่อจัดเรียงใหม่ได้)
คลิกตกลง

ภายใต้สรุปลำดับการจัดเรียงปัจจุบันจะถูกกำหนดให้เป็นรายการลากของเขตข้อมูลภูมิภาคเพื่อแสดงตามลำดับใด ๆ

คลิกที่ตะวันออกแล้วลากไปด้านบน ในขณะที่คุณลากไปทางทิศตะวันออกแถบสีเขียวแนวนอนจะปรากฏขึ้นทั่วทั้งแถว

ลากซ้ำกับรายการอื่น ๆ ของเขตข้อมูลภูมิภาคจนกว่าคุณจะได้รับการจัดเรียงที่ต้องการ

คุณสามารถสังเกตสิ่งต่อไปนี้ -

รายการของเขตข้อมูลที่ซ้อนกัน - พนักงานขายยังย้ายไปพร้อมกับรายการเขตข้อมูลภูมิภาคที่เกี่ยวข้อง นอกจากนี้ค่าในคอลัมน์อื่น ๆ ก็ย้ายตามไปด้วย
หากคุณวางเคอร์เซอร์บนลูกศร

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

Note- คุณไม่สามารถใช้การลากรายการของฟิลด์ที่อยู่ในพื้นที่ ∑ VALUES ของรายการเขตข้อมูล PivotTable ด้วยตนเอง ดังนั้นคุณไม่สามารถลากค่า Sum of Order Amount ใน PivotTable นี้ได้

การตั้งค่าตัวเลือกการจัดเรียง

ในส่วนก่อนหน้านี้คุณได้เรียนรู้วิธีตั้งค่าตัวเลือกการจัดเรียงสำหรับฟิลด์เป็นแบบแมนนวล คุณมีตัวเลือกการจัดเรียงเพิ่มเติมที่คุณสามารถตั้งค่าได้ดังนี้ -

คลิกลูกศร

ใน Row Labels
เลือกภูมิภาคในกล่องเลือกฟิลด์
คลิกตัวเลือกการจัดเรียงเพิ่มเติม กล่องโต้ตอบจัดเรียง (ภูมิภาค) จะปรากฏขึ้น
คลิกปุ่มตัวเลือกเพิ่มเติม

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

ภายใต้การเรียงลำดับอัตโนมัติคุณสามารถเลือกหรือยกเลิกการเลือกช่อง - เรียงลำดับโดยอัตโนมัติทุกครั้งที่มีการอัปเดตรายงานเพื่ออนุญาตหรือหยุดการเรียงลำดับอัตโนมัติเมื่อใดก็ตามที่มีการอัปเดตข้อมูล PivotTable

ยกเลิกการเลือกช่อง - Sort automatically ทุกครั้งที่มีการอัปเดตรายงาน

ตอนนี้ตัวเลือกลำดับการจัดเรียงคีย์แรกพร้อมใช้งานแล้ว คุณสามารถใช้ตัวเลือกนี้เพื่อเลือกลำดับแบบกำหนดเองที่คุณต้องการใช้

คลิกช่องใต้ลำดับการจัดเรียงคีย์แรก

ดังที่คุณสามารถสังเกตได้รายการที่กำหนดเองของวันในสัปดาห์และเดือนของปีจะอยู่ในรายการแบบเลื่อนลง คุณสามารถใช้สิ่งเหล่านี้หรือคุณสามารถใช้รายการที่กำหนดเองเช่นสูงปานกลางต่ำหรือรายการขนาด S, M, L, XL ที่ไม่เรียงตามลำดับตัวอักษร

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

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

ภายใต้จัดเรียงตามคุณสามารถคลิก Grand Total or Valuesในคอลัมน์ที่เลือกเพื่อจัดเรียงตามค่าเหล่านี้ ตัวเลือกนี้ใช้ไม่ได้เมื่อคุณตั้งค่าการเรียงลำดับเป็นด้วยตนเอง

สิ่งที่ควรพิจารณาขณะจัดเรียง PivotTables

เมื่อคุณจัดเรียงข้อมูลใน PivotTable โปรดจำสิ่งต่อไปนี้ -

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

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

การกรองโดยใช้ตัวแบ่งส่วนข้อมูล
การกรองโดยใช้ตัวกรองรายงาน
การกรองข้อมูลด้วยตนเอง
การกรองโดยใช้ตัวกรองฉลาก
การกรองโดยใช้ตัวกรองค่า
การกรองโดยใช้ตัวกรองวันที่
การกรองโดยใช้ตัวกรอง 10 อันดับแรก
การกรองโดยใช้ไทม์ไลน์

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

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

ตัวกรองรายงาน

คุณสามารถกำหนดตัวกรองให้กับฟิลด์ใดฟิลด์หนึ่งเพื่อให้คุณสามารถเปลี่ยนแปลง PivotTable แบบไดนามิกตามค่าของฟิลด์นั้น

ลากภูมิภาคจากแถวไปยังตัวกรองในพื้นที่ PivotTable

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

คุณจะสังเกตว่า

ค่าพนักงานขายปรากฏเป็นแถว
ค่าเดือนปรากฏในคอลัมน์
ตัวกรองภูมิภาคจะปรากฏที่ด้านบนโดยเลือกค่าเริ่มต้นเป็นทั้งหมด
มูลค่าสรุปคือยอดรวมของยอดสั่งซื้อ
- ยอดรวมของยอดสั่งซื้อพนักงานขายที่ชาญฉลาดปรากฏในคอลัมน์ยอดรวมทั้งหมด
- ยอดรวมของยอดสั่งซื้อรายเดือนปรากฏในแถวยอดรวม
คลิกที่ลูกศรในช่องทางขวาของ Filter Region

รายการดรอปดาวน์ที่มีค่าของเขตข้อมูลภูมิภาคปรากฏขึ้น ทำเครื่องหมายในช่องSelect Multiple Items.

โดยค่าเริ่มต้นกล่องทั้งหมดจะถูกเลือก ยกเลิกการเลือกช่อง (All). กล่องทั้งหมดจะไม่ถูกเลือก

จากนั้นทำเครื่องหมายในช่อง - ทิศใต้และทิศตะวันตกแล้วคลิกตกลง

ข้อมูลที่เกี่ยวข้องกับภูมิภาคใต้และตะวันตกเท่านั้นที่จะสรุปได้

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

การกรองด้วยตนเอง

คุณยังสามารถกรอง PivotTable ได้โดยเลือกค่าของเขตข้อมูลด้วยตนเอง คุณสามารถทำได้โดยคลิกที่ลูกศร

ในเซลล์ป้ายชื่อแถวหรือป้ายชื่อคอลัมน์

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

คลิกที่ลูกศร

ในเซลล์ Column Labels

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

ยกเลิกการเลือกช่อง (เลือกทั้งหมด) ที่ด้านบนของรายการค่า
ทำเครื่องหมายในช่องของค่าที่คุณต้องการแสดงใน PivotTable ของคุณในกรณีนี้คือเดือนกุมภาพันธ์แล้วคลิกตกลง

PivotTable จะแสดงเฉพาะค่าที่เกี่ยวข้องกับค่าเขตข้อมูลเดือนที่เลือก - กุมภาพันธ์ คุณสามารถสังเกตได้ว่าลูกศรการกรองเปลี่ยนเป็นไอคอน

เพื่อระบุว่ามีการใช้ตัวกรอง วางเคอร์เซอร์บน

ไอคอน

คุณสามารถสังเกตได้ว่าจะปรากฏขึ้นเพื่อระบุว่ามีการใช้ตัวกรองด้วยตนเองในฟิลด์ - เดือน

หากคุณต้องการเปลี่ยนค่าการเลือกตัวกรองให้ทำดังต่อไปนี้ -

คลิก

ไอคอน
เลือก / ยกเลิกการเลือกช่องของค่า

หากไม่เห็นค่าทั้งหมดของเขตข้อมูลในรายการให้ลากที่จับที่มุมล่างขวาของรายการแบบเลื่อนลงเพื่อขยาย หรือถ้าคุณทราบค่านี้ให้พิมพ์ลงในกล่องค้นหา

สมมติว่าคุณต้องการใช้ตัวกรองอื่นกับ PivotTable ที่กรองด้านบน ตัวอย่างเช่นคุณต้องการแสดงข้อมูลของ Walters, Chris สำหรับเดือนกุมภาพันธ์ คุณต้องปรับแต่งการกรองของคุณโดยเพิ่มตัวกรองอื่นสำหรับพนักงานขายภาคสนาม ดังที่คุณสังเกตได้พนักงานขายเป็นส่วนหนึ่งของ Row Labels

คลิกที่ลูกศร

ในเซลล์ Row Labels

รายการค่าของเขตข้อมูล - ภูมิภาคจะปรากฏขึ้น เนื่องจากภูมิภาคอยู่ในระดับนอกของพนักงานขายในลำดับที่ซ้อนกัน คุณยังมีตัวเลือกเพิ่มเติม - เลือกฟิลด์ คลิกที่กล่องเลือกฟิลด์

คลิกพนักงานขายจากรายการแบบเลื่อนลง รายการค่าของเขตข้อมูล - พนักงานขายจะแสดงขึ้น
ยกเลิกการเลือก (เลือกทั้งหมด) และทำเครื่องหมายที่ Walters, Chris
คลิกตกลง

PivotTable จะแสดงเฉพาะค่าที่เกี่ยวข้องกับค่าฟิลด์เดือนที่เลือก - กุมภาพันธ์และค่าฟิลด์พนักงานขาย - Walters, Chris

ลูกศรการกรองสำหรับ Row Labels จะเปลี่ยนเป็นไอคอน

เพื่อระบุว่ามีการใช้ตัวกรอง วางเคอร์เซอร์บน

ไอคอนบนป้ายชื่อแถวหรือป้ายชื่อคอลัมน์

กล่องข้อความจะปรากฏขึ้นเพื่อระบุว่ามีการใช้ตัวกรองด้วยตนเองในฟิลด์ - เดือนและพนักงานขาย

ดังนั้นคุณสามารถกรอง PivotTable ด้วยตนเองตามจำนวนฟิลด์และตามจำนวนค่าใดก็ได้

กรองตามข้อความ

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

ข้อมูลมีรายละเอียดของพนักงาน - EmployeeID, Title, BirthDate, MaritalStatus, Gender and HireDate นอกจากนี้ข้อมูลยังมีระดับผู้จัดการของพนักงาน (ระดับ 0 - 4)

สมมติว่าคุณต้องทำการวิเคราะห์เกี่ยวกับจำนวนพนักงานที่รายงานไปยังพนักงานที่ระบุตามตำแหน่งงาน คุณสามารถสร้าง PivotTable ได้ตามที่ระบุด้านล่าง

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

คลิกที่ลูกศร

ในเซลล์ Row Labels
เลือกชื่อในกล่องเลือกฟิลด์จากรายการแบบหล่นลง
คลิกที่ตัวกรองฉลาก
คลิกที่มีในรายการแบบเลื่อนลงที่สอง

กล่องโต้ตอบตัวกรองฉลาก (ชื่อเรื่อง) จะปรากฏขึ้น พิมพ์ Manager ในช่องถัดจากมี คลิกตกลง

PivotTable จะถูกกรองเป็นค่า Title ที่มี "Manager"

คลิก

ไอคอน

คุณสามารถเห็นสิ่งที่

ปรากฏขึ้นเพื่อระบุสิ่งต่อไปนี้ -

ใช้ตัวกรองป้ายกำกับในฟิลด์ - ชื่อและ
ตัวกรองฉลากที่ใช้คืออะไร

กรองตามค่า

คุณอาจต้องการทราบตำแหน่งของพนักงานที่มีพนักงานมากกว่า 25 คนรายงานต่อพวกเขา สำหรับสิ่งนี้คุณสามารถใช้ตัวกรองค่าในฟิลด์ชื่อได้ดังนี้ -

คลิกที่ลูกศร

ในเซลล์ Row Labels
เลือก Title ในกล่องเลือกฟิลด์จากรายการดรอปดาวน์
คลิกที่ตัวกรองค่า
เลือกมากกว่าหรือเท่ากับจากรายการแบบเลื่อนลงที่สอง

กล่องโต้ตอบตัวกรองค่า (ชื่อเรื่อง) จะปรากฏขึ้น พิมพ์ 25 ในช่องด้านขวา

PivotTable จะถูกกรองเพื่อแสดงตำแหน่งพนักงานที่มีพนักงานมากกว่า 25 คนรายงานต่อพวกเขา

กรองตามวันที่

คุณอาจต้องการแสดงข้อมูลของพนักงานทั้งหมดที่ได้รับการว่าจ้างในปีงบประมาณ 2015-15 คุณสามารถใช้ตัวกรองข้อมูลได้ดังต่อไปนี้ -

รวมฟิลด์ HireDate ไว้ใน PivotTable ตอนนี้คุณไม่ต้องการข้อมูลผู้จัดการดังนั้นให้ลบฟิลด์ ManagerLevel ออกจาก PivotTable

เมื่อคุณมีฟิลด์วันที่ใน PivotTable แล้วคุณสามารถใช้ตัวกรองวันที่ได้

คลิกลูกศร

ในเซลล์ Row Labels
เลือก HireDate ในกล่องเลือกฟิลด์จากรายการดรอปดาวน์
คลิกตัวกรองวันที่
Seelct Between จากรายการแบบเลื่อนลงที่สอง

กล่องโต้ตอบตัวกรองวันที่ (HireDate) จะปรากฏขึ้น พิมพ์ 4/1/2557 และ 31/3/2558 ในช่องวันที่สองช่อง คลิกตกลง

ตารางสาระสำคัญจะถูกกรองเพื่อแสดงข้อมูลเฉพาะที่มี HireDate ระหว่างวันที่ 1 ^{เซนต์}เมษายน 2014 และวันที่ 31 ^{เซนต์}มีนาคม 2015

คุณสามารถจัดกลุ่มวันที่เป็นไตรมาสได้ดังนี้ -

คลิกขวาที่วันที่ใดก็ได้ Grouping กล่องโต้ตอบปรากฏขึ้น
พิมพ์ 4/1/2557 ในกล่องเริ่มต้นที่. ทำเครื่องหมายในช่อง
พิมพ์ครั้งที่ 3/31/2558 ในช่องลงท้ายเวลา. ทำเครื่องหมายในช่อง
คลิก Quarters ในช่องด้านล่าง By.

วันที่จะถูกจัดกลุ่มเป็นสี่ส่วนใน PivotTable คุณสามารถทำให้ตารางดูกะทัดรัดได้โดยการลากฟิลด์ HireDate จากพื้นที่ ROWS ไปยังพื้นที่ COLUMNS

คุณจะสามารถทราบจำนวนพนักงานที่ได้รับการว่าจ้างในช่วงปีงบประมาณไตรมาสที่ชาญฉลาด

การกรองโดยใช้ตัวกรอง 10 อันดับแรก

คุณสามารถใช้ตัวกรอง 10 อันดับแรกเพื่อแสดงค่าสองสามอันดับแรกหรือสองสามค่าล่างสุดของฟิลด์ใน PivotTable

คลิกลูกศร

ในเซลล์ Row Labels
คลิกตัวกรองค่า
คลิก 10 อันดับแรกในรายการแบบเลื่อนลงที่สอง

กล่องโต้ตอบตัวกรอง 10 อันดับแรก (ชื่อเรื่อง) จะปรากฏขึ้น

ในช่องแรกคลิกที่ด้านบน (คุณสามารถเลือกด้านล่างได้เช่นกัน)
ในช่องที่สองให้ป้อนตัวเลขพูด 7
ในช่องที่สามคุณมีสามตัวเลือกที่คุณสามารถกรองได้
- คลิกที่รายการเพื่อกรองตามจำนวนรายการ
- คลิกที่เปอร์เซ็นต์เพื่อกรองตามเปอร์เซ็นต์
- คลิกที่ผลรวมเพื่อกรองตามผลรวม
เมื่อคุณนับรหัสพนักงานแล้วให้คลิกรายการ
ในช่องที่สี่ให้คลิกที่ฟิลด์จำนวนรหัสพนักงาน
คลิกตกลง

ค่าเจ็ดอันดับแรกตามจำนวน EmployeeID จะแสดงใน PivotTable

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

การกรองโดยใช้ไทม์ไลน์

หาก PivotTable ของคุณมีฟิลด์วันที่คุณสามารถกรอง PivotTable โดยใช้ไทม์ไลน์

สร้าง PivotTable จากข้อมูลพนักงานที่คุณใช้ก่อนหน้านี้และเพิ่มข้อมูลลงในตัวแบบข้อมูลในกล่องโต้ตอบสร้าง PivotTable

ลากชื่อเขตข้อมูลไปยังพื้นที่แถว
ลากฟิลด์ EmployeeID ไปที่พื้นที่ ∑ VALUES และเลือกนับสำหรับการคำนวณ

คลิกที่ PivotTable
คลิกที่แท็บ INSERT
คลิกไทม์ไลน์ในกลุ่มฟิลเตอร์ กล่องโต้ตอบแทรกไทม์ไลน์จะปรากฏขึ้น

เลือกช่อง HireDate
คลิกตกลง ไทม์ไลน์จะปรากฏในแผ่นงาน
เครื่องมือไทม์ไลน์ปรากฏบน Ribbon

ดังที่คุณสังเกตได้ทุกช่วงเวลา - ในเดือนจะแสดงบนไทม์ไลน์

คลิกที่ลูกศรถัดจาก - MONTHS
เลือก QUARTERS จากรายการแบบเลื่อนลง การแสดงไทม์ไลน์จะเปลี่ยนเป็นทุกช่วงเวลา - ในไตรมาส

คลิก 2014 Q1
กดปุ่ม Shift ค้างไว้แล้วลากไปที่ 2014 Q4 ระยะเวลาไทม์ไลน์ถูกเลือกเป็น Q1 - Q4 2014
PivotTable จะถูกกรองตามระยะเวลานี้

การล้างตัวกรอง

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

การล้างตัวกรองทั้งหมดใน PivotTable

คุณสามารถล้างตัวกรองทั้งหมดที่ตั้งค่าไว้ใน PivotTable ได้ในคราวเดียวดังนี้ -

คลิกแท็บ HOME บน Ribbon
คลิกจัดเรียงและกรองในกลุ่มการแก้ไข
เลือกล้างจากรายการแบบเลื่อนลง

การล้างฉลากวันที่หรือตัวกรองค่า

ในการล้างป้ายชื่อวันที่หรือตัวกรองค่าให้ทำดังต่อไปนี้ -

คลิกที่ไอคอนใน Row Labels หรือ Column Labels
คลิกที่

<ชื่อฟิลด์> ซึ่งคุณต้องการล้างตัวกรองในกล่องเลือกฟิลด์ในรายการดรอปดาวน์
คลิกที่ล้างตัวกรองจาก <ชื่อไฟล์> ที่ปรากฏในรายการแบบเลื่อนลง
คลิกตกลง ตัวกรองเฉพาะจะถูกล้าง

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

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

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

การใส่ตัวแบ่งส่วนข้อมูล

สมมติว่าคุณต้องการกรอง PivotTable นี้ตามฟิลด์ - ภูมิภาคและเดือน

คลิกที่ ANALYZE ภายใต้ PIVOTTABLE TOOLS บน Ribbon
คลิกที่แทรกตัวแบ่งส่วนข้อมูลในกลุ่มตัวกรอง กล่องโต้ตอบแทรกตัวแบ่งส่วนข้อมูลจะปรากฏขึ้น ประกอบด้วยเขตข้อมูลทั้งหมดจากตารางข้อมูลของคุณ
เลือกช่องภูมิภาคและเดือน
คลิกตกลง

ตัวแบ่งส่วนข้อมูลสำหรับแต่ละฟิลด์ที่เลือกจะปรากฏพร้อมกับค่าทั้งหมดที่เลือกโดยค่าเริ่มต้น เครื่องมือตัวแบ่งส่วนข้อมูลจะปรากฏบน Ribbon เพื่อทำงานกับการตั้งค่าตัวแบ่งส่วนข้อมูลรูปลักษณ์และความรู้สึก

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

ดังที่คุณสังเกตได้ว่าตัวแบ่งส่วนข้อมูลแต่ละตัวมีค่าทั้งหมดของเขตข้อมูลที่แสดงถึงและค่าจะแสดงเป็นปุ่ม ตามค่าเริ่มต้นค่าทั้งหมดของเขตข้อมูลจะถูกเลือกและด้วยเหตุนี้ปุ่มทั้งหมดจึงถูกไฮไลต์

สมมติว่าคุณต้องการแสดง PivotTable สำหรับภูมิภาคทางใต้และตะวันตกและสำหรับเดือนกุมภาพันธ์และมีนาคม

คลิกที่ทิศใต้ในตัวแบ่งส่วนข้อมูลสำหรับภูมิภาค เฉพาะทิศใต้เท่านั้นที่จะถูกเน้นในตัวแบ่งส่วนข้อมูล - ภูมิภาค
กดปุ่ม Ctrl ค้างไว้และคลิกที่ตะวันตกในตัวแบ่งส่วนข้อมูลสำหรับภูมิภาค
คลิกที่กุมภาพันธ์ในตัวแบ่งส่วนข้อมูลสำหรับเดือน
กดปุ่ม Ctrl ค้างไว้และคลิกที่มีนาคมในตัวแบ่งส่วนข้อมูลสำหรับเดือน

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

ในการเพิ่ม / ลบค่าของเขตข้อมูลออกจากตัวกรองให้กดปุ่ม Ctrl ค้างไว้และคลิกที่ปุ่มเหล่านั้นในตัวแบ่งส่วนข้อมูลของเขตข้อมูล

การล้างตัวกรองในตัวแบ่งส่วนข้อมูล

หากต้องการล้างตัวกรองในตัวแบ่งส่วนข้อมูลให้คลิก

ที่มุมขวาบนของตัวแบ่งส่วนข้อมูล

การลบตัวแบ่งส่วนข้อมูล

สมมติว่าคุณต้องการลบตัวแบ่งส่วนข้อมูลสำหรับเขตข้อมูลภูมิภาค

คลิกขวาที่ตัวแบ่งส่วนข้อมูล - ภูมิภาค
คลิกลบ“ ภูมิภาค” ในรายการแบบเลื่อนลง

เครื่องมือตัวแบ่งส่วนข้อมูล

เมื่อคุณแทรกตัวแบ่งส่วนข้อมูลเครื่องมือตัวแบ่งส่วนข้อมูลจะปรากฏบนแท็บ Ribbon พร้อมตัวเลือก หากต้องการดูเครื่องมือตัวแบ่งส่วนข้อมูลให้คลิกที่ตัวแบ่งส่วนข้อมูล

ดังที่คุณสามารถสังเกตได้ภายใต้แท็บเครื่องมือตัวแบ่งส่วนข้อมูล - ตัวเลือกคุณมีหลายตัวเลือกในการเปลี่ยนรูปลักษณ์ของตัวแบ่งส่วนข้อมูลซึ่งรวมถึง -

คำอธิบายตัวแบ่งส่วนข้อมูล
การตั้งค่าตัวแบ่งส่วนข้อมูล
รายงานการเชื่อมต่อ
บานหน้าต่างการเลือก

คำอธิบายตัวแบ่งส่วนข้อมูล

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

คลิกที่ตัวแบ่งส่วนข้อมูลสำหรับภูมิภาค
คลิกแท็บ OPTIONS บน Ribbon

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

คลิกที่กล่องคำอธิบายตัวแบ่งส่วนข้อมูลในกลุ่มตัวแบ่งส่วนข้อมูลบน Ribbon
ลบภูมิภาค กล่องถูกล้าง
พิมพ์ Location ในกล่องแล้วกด Enter คำอธิบายตัวแบ่งส่วนข้อมูลจะเปลี่ยนเป็นตำแหน่งและสิ่งเดียวกันนี้จะแสดงเป็นส่วนหัวในตัวแบ่งส่วนข้อมูล

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

การตั้งค่าตัวแบ่งส่วนข้อมูล

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

คลิกที่ตัวแบ่งส่วนข้อมูล - ตำแหน่ง
คลิกแท็บ OPTIONS บน Ribbon คุณสามารถค้นหาการตั้งค่าตัวแบ่งส่วนข้อมูลในกลุ่มตัวแบ่งส่วนข้อมูลบน Ribbon คุณยังสามารถค้นหาการตั้งค่าตัวแบ่งส่วนข้อมูลในรายการแบบเลื่อนลงเมื่อคุณคลิกขวาที่ตัวแบ่งส่วนข้อมูล
คลิกการตั้งค่าตัวแบ่งส่วนข้อมูล กล่องโต้ตอบการตั้งค่าตัวแบ่งส่วนข้อมูลจะปรากฏขึ้น

ดังที่คุณสังเกตได้สิ่งต่อไปนี้ได้รับการแก้ไขสำหรับตัวแบ่งส่วนข้อมูล -

ชื่อแหล่งที่มา
ชื่อที่จะใช้ในสูตร

คุณสามารถเปลี่ยนสิ่งต่อไปนี้สำหรับตัวแบ่งส่วนข้อมูล -

Name.
ส่วนหัว - คำอธิบายภาพ
แสดงส่วนหัว
ตัวเลือกการเรียงลำดับและการกรองสำหรับรายการที่แสดงบนตัวแบ่งส่วนข้อมูล

รายงานการเชื่อมต่อ

คุณสามารถเชื่อมต่อ PivotTables ที่แตกต่างกันกับตัวแบ่งส่วนข้อมูลได้โดยมีข้อใดข้อหนึ่งต่อไปนี้ที่ดี -

PivotTables ถูกสร้างขึ้นโดยใช้ข้อมูลเดียวกัน
PivotTable หนึ่งรายการถูกคัดลอกและวางเป็น PivotTable เพิ่มเติม
PivotTables หลายรายการถูกสร้างขึ้นบนแผ่นงานแยกกันโดยมีแสดงหน้าตัวกรองรายงาน

พิจารณา PivotTables ต่อไปนี้ที่สร้างจากข้อมูลเดียวกัน -

ตั้งชื่อ PivotTable ด้านบนเป็น PivotTable-Top และด้านล่างเป็น PivotTable-Bottom
คลิกที่ PivotTable ด้านบน
แทรกตัวแบ่งส่วนข้อมูลสำหรับภูมิภาคเขตข้อมูล
เลือกตะวันออกและเหนือบนตัวแบ่งส่วนข้อมูล

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

คลิกที่ตัวแบ่งส่วนข้อมูล - ภูมิภาค เครื่องมือ SLICER จะปรากฏบน Ribbon
คลิกแท็บ OPTIONS บน Ribbon

คุณจะพบการเชื่อมต่อรายงานในกลุ่มตัวแบ่งส่วนข้อมูลบน Ribbon คุณยังสามารถค้นหา Report Connections ในรายการดรอปดาวน์เมื่อคุณคลิกขวาที่ตัวแบ่งส่วนข้อมูล

คลิก Report Connections ในกลุ่มตัวแบ่งส่วนข้อมูล

Report Connectionsกล่องโต้ตอบปรากฏขึ้น ทำเครื่องหมายในช่อง PivotTable-Top และกล่องอื่น ๆ จะไม่ถูกเลือก ทำเครื่องหมายที่ช่อง PivotTable-Bottom ด้วยแล้วคลิกตกลง

PivotTable ด้านล่างจะถูกกรองไปยังรายการที่เลือก - ตะวันออกและเหนือ

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

บานหน้าต่างการเลือก

คุณสามารถปิดและเปิดการแสดงตัวแบ่งส่วนข้อมูลบนแผ่นงานได้โดยใช้บานหน้าต่างการเลือก

คลิกที่ตัวแบ่งส่วนข้อมูล - ตำแหน่ง
คลิกแท็บ OPTIONS บน Ribbon
คลิกบานหน้าต่างการเลือกในกลุ่มจัดเรียงบน Ribbon บานหน้าต่างการเลือกจะปรากฏทางด้านขวาของหน้าต่าง

ดังที่คุณสังเกตได้ชื่อของตัวแบ่งส่วนข้อมูลทั้งหมดจะแสดงรายการในบานหน้าต่างการเลือก ทางด้านขวาของชื่อคุณจะพบสัญลักษณ์การมองเห็นซึ่ง

ระบุว่าตัวแบ่งส่วนข้อมูลสามารถมองเห็นได้บนแผ่นงาน

คลิก

สัญลักษณ์สำหรับเดือน สัญลักษณ์

จะเปลี่ยนเป็นสัญลักษณ์

แสดงว่าตัวแบ่งส่วนข้อมูลถูกซ่อนอยู่ (มองไม่เห็น)

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

คลิกที่

สัญลักษณ์สำหรับเดือน
สัญลักษณ์

จะเปลี่ยนเป็นสัญลักษณ์

แสดงว่าขณะนี้ตัวแบ่งส่วนข้อมูลสามารถมองเห็นได้

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

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

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

ในบทนี้คุณจะเข้าใจลำดับการซ้อนของฟิลด์และวิธีที่คุณสามารถเปลี่ยนลำดับการซ้อนได้

ลำดับการซ้อนของเขตข้อมูล

พิจารณาตัวอย่างข้อมูลการขายซึ่งคุณได้วางฟิลด์ตามลำดับต่อไปนี้ -

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

ใน PivotTable ค่าในแถวจะแสดงตามลำดับนี้ตามที่ระบุด้านล่าง

ดังที่คุณสามารถสังเกตได้ค่าของฟิลด์ที่สองในลำดับการซ้อนจะถูกฝังอยู่ภายใต้ค่าแต่ละค่าของฟิลด์แรก

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

การเปลี่ยนลำดับการสร้างรัง

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

คลิกที่พนักงานขายฟิลด์ในพื้นที่แถวและลากไปที่ด้านล่างของเขตข้อมูลภูมิภาค ดังนั้นคุณได้เปลี่ยนลำดับการซ้อนเป็น - ภูมิภาคแรกและพนักงานขายถัดไปดังนี้ -

PivotTable ที่ได้จะเป็นดังที่ระบุด้านล่าง -

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

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

ในแผ่นงานที่มี PivotTable Ribbon จะมีเครื่องมือ PivotTable พร้อมด้วยแท็บ ANALYZE และ DESIGN แท็บ ANALYZE มีคำสั่งต่างๆที่จะช่วยให้คุณสำรวจข้อมูลใน PivotTable ได้ คำสั่งแท็บ DESIGN จะเป็นประโยชน์ในการจัดโครงสร้าง PivotTable ด้วยตัวเลือกรายงานและตัวเลือกสไตล์ต่างๆ

คุณจะได้เรียนรู้คำสั่ง ANALYZE ในบทนี้ คุณจะได้เรียนรู้คำสั่งการออกแบบในบทที่ - รายงานความงามด้วย PivotTables

วิเคราะห์คำสั่ง

คำสั่งบนแท็บ Ribbon ของ ANALYZE มีดังต่อไปนี้ -

การขยายและการยุบฟิลด์
การจัดกลุ่มและการยกเลิกการจัดกลุ่มค่าฟิลด์
การตั้งค่าฟิลด์ที่ใช้งานอยู่
ตัวเลือก PivotTable

การขยายและการยุบฟิลด์

หากคุณมีฟิลด์ที่ซ้อนกันใน PivotTable คุณสามารถขยายและยุบรายการเดียวหรือคุณสามารถขยายและยุบรายการทั้งหมดของฟิลด์ที่ใช้งานอยู่

พิจารณา PivotTable ต่อไปนี้ซึ่งคุณมีฟิลด์พนักงานขายซ้อนอยู่ภายใต้ฟิลด์ภูมิภาค

คลิก

สัญลักษณ์ทางด้านซ้ายของตะวันออก รายการทางตะวันออกของเขตข้อมูลภูมิภาคจะยุบ

ดังที่คุณสังเกตได้ว่ารายการอื่น ๆ - เหนือใต้และตะวันตกของเขตข้อมูลภูมิภาคจะไม่ถูกยุบ หากคุณต้องการยุบรายการใดรายการหนึ่งให้ทำซ้ำขั้นตอนที่คุณทำกับ East

คลิกที่

สัญลักษณ์ทางด้านซ้ายของ East รายการทางตะวันออกของเขตข้อมูลภูมิภาคจะขยาย

หากคุณต้องการยุบรายการทั้งหมดของเขตข้อมูลในครั้งเดียวให้ทำดังต่อไปนี้ -

คลิกรายการใด ๆ ของเขตข้อมูล - ภูมิภาค
คลิกแท็บวิเคราะห์บน Ribbon
คลิกยุบฟิลด์ในกลุ่มฟิลด์ที่ใช้งานอยู่

รายการทั้งหมดของเขตข้อมูลภูมิภาคจะถูกยุบ

หากคุณต้องการขยายรายการทั้งหมดของเขตข้อมูลพร้อมกันให้ทำดังต่อไปนี้ -

คลิกที่รายการใดก็ได้ในฟิลด์ - ภูมิภาค
คลิกแท็บวิเคราะห์บน Ribbon
คลิกขยายฟิลด์ในกลุ่ม Active Field

รายการทั้งหมดของเขตข้อมูลภูมิภาคจะถูกขยาย

การจัดกลุ่มและการยกเลิกการจัดกลุ่มค่าฟิลด์

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

เลือกรายการตะวันออกและเหนือของเขตข้อมูลภูมิภาคใน PivotTable พร้อมกับรายการเขตข้อมูลพนักงานขายที่ซ้อนกัน
คลิกแท็บวิเคราะห์บน Ribbon
คลิกการเลือกกลุ่มในกลุ่ม - กลุ่ม

รายการ - ตะวันออกและเหนือจะถูกจัดกลุ่มภายใต้ชื่อ Group1 นอกจากนี้ทิศใต้ใหม่จะถูกสร้างขึ้นภายใต้การซ้อนของทิศใต้และทิศตะวันตกใหม่ถูกสร้างขึ้นภายใต้การซ้อนกันของทิศตะวันตก

คุณยังสามารถสังเกตได้ว่ามีการเพิ่มเขตข้อมูลใหม่ - Region2 ในรายการเขตข้อมูล PivotTable ซึ่งปรากฏในพื้นที่ ROWS

เลือกรายการภาคใต้และตะวันตกของเขตข้อมูลภูมิภาค 2 ใน PivotTable พร้อมกับรายการเขตข้อมูลภูมิภาคและพนักงานขายที่ซ้อนกัน
คลิกแท็บวิเคราะห์บน Ribbon
คลิกการเลือกกลุ่มในกลุ่ม - กลุ่ม

รายการ - ทางใต้และตะวันตกของเขตข้อมูลภูมิภาคจะถูกจัดกลุ่มภายใต้ชื่อ Group2

ในการยกเลิกการจัดกลุ่มให้ทำดังต่อไปนี้ -

คลิกที่ชื่อกลุ่ม
คลิกแท็บวิเคราะห์
คลิกยกเลิกการจัดกลุ่มในกลุ่ม - กลุ่ม

การจัดกลุ่มตามฟิลด์วันที่

พิจารณา PivotTable ต่อไปนี้ซึ่งคุณมีข้อมูลพนักงานที่สรุปโดย Count of EmployeeID ซึ่งได้รับการว่าจ้างที่ชาญฉลาดและมีตำแหน่งที่ชาญฉลาด

สมมติว่าคุณต้องการจัดกลุ่มข้อมูลนี้ตามเขตข้อมูล HireDate ที่เป็นเขตข้อมูลวันที่เป็นปีและไตรมาส

คลิกที่รายการวันที่ใน PivotTable
คลิกแท็บวิเคราะห์บน Ribbon
คลิก Group Field ในกลุ่ม - Group

กล่องโต้ตอบการจัดกลุ่มจะปรากฏขึ้น

กำหนดวันที่สำหรับ - เริ่มต้นที่และสิ้นสุดที่
เลือกไตรมาสและปีในกล่องภายใต้โดย ในการเลือก / ยกเลิกการเลือกหลายรายการให้กดปุ่ม Ctrl ค้างไว้
คลิกตกลง

ค่าฟิลด์ HireDate จะถูกจัดกลุ่มเป็น Quarters โดยซ้อนกันในปี

หากคุณต้องการยกเลิกการจัดกลุ่มนี้คุณสามารถทำได้ดังที่แสดงไว้ก่อนหน้านี้โดยคลิก Ungroup ในกลุ่ม - จัดกลุ่มบน Ribbon

การตั้งค่าฟิลด์ค่าที่ใช้งานอยู่

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

สมมติว่าคุณต้องการตั้งค่าตัวเลือกสำหรับเขตข้อมูลภูมิภาค

คลิกที่ตะวันออก บน Ribbon ในกลุ่ม Active Field ในกล่อง Active Field ภูมิภาคจะแสดงขึ้น
คลิกที่ Field Settings. กล่องโต้ตอบการตั้งค่าฟิลด์จะปรากฏขึ้น

คุณสามารถตั้งค่ากำหนดของคุณสำหรับฟิลด์ - ภูมิภาค

ตัวเลือก PivotTable

คุณสามารถตั้งค่าตัวเลือก PivotTable ตามความต้องการของคุณ

คลิกที่ PivotTable
คลิกแท็บวิเคราะห์
คลิกตัวเลือกในกลุ่ม PivotTable

PivotTable Optionsกล่องโต้ตอบปรากฏขึ้น คุณสามารถตั้งค่ากำหนดของคุณในกล่องโต้ตอบ

คุณสามารถสรุป PivotTable ได้โดยวางเขตข้อมูลในพื้นที่ ∑ VALUES ในบานหน้าต่างงานเขตข้อมูล PivotTable ตามค่าเริ่มต้น Excel จะใช้การสรุปเป็นผลรวมของค่าของเขตข้อมูลในพื้นที่ ∑ VALUES อย่างไรก็ตามคุณมีประเภทการคำนวณอื่น ๆ เช่น Count, Average, Max, Min เป็นต้น

ในบทนี้คุณจะได้เรียนรู้วิธีตั้งค่าประเภทการคำนวณตามวิธีที่คุณต้องการสรุปข้อมูลใน PivotTable

ผลรวม

พิจารณา PivotTable ต่อไปนี้ซึ่งคุณมีข้อมูลการขายแบบสรุปตามภูมิภาคพนักงานขายที่ชาญฉลาดและเป็นรายเดือน

ดังที่คุณสามารถสังเกตได้เมื่อคุณลากฟิลด์ Order Amount ไปที่พื้นที่ ∑ VALUES ฟิลด์จะแสดงเป็นผลรวมของจำนวนคำสั่งซื้อซึ่งแสดงว่าการคำนวณถูกนำมาใช้เป็นผลรวม ใน PivotTable ที่มุมซ้ายบนจะแสดงผลรวมของจำนวนคำสั่งซื้อ นอกจากนี้คอลัมน์ผลรวมทั้งหมดและแถวผลรวมทั้งหมดจะแสดงสำหรับผลรวมย่อยที่ชาญฉลาดในแถวและคอลัมน์ตามลำดับ

การตั้งค่าฟิลด์ค่า

ด้วยการตั้งค่าฟิลด์ค่าคุณสามารถตั้งค่าประเภทการคำนวณใน PivotTable ของคุณ คุณยังสามารถตัดสินใจว่าคุณต้องการแสดงค่าของคุณอย่างไร

คลิกที่ผลรวมของยอดสั่งซื้อในพื้นที่ ∑ VALUES
เลือกการตั้งค่าฟิลด์ค่าจากรายการดรอปดาวน์

กล่องโต้ตอบ Value Field Settings จะปรากฏขึ้น

ชื่อแหล่งที่มาคือฟิลด์และชื่อที่กำหนดเองคือผลรวมของฟิลด์ ประเภทการคำนวณคือผลรวม คลิกShow Values As แท็บ

ในกล่องแสดงค่าเป็น No Calculationจะปรากฏขึ้น คลิกShow Values Asกล่อง. คุณสามารถดูวิธีแสดงมูลค่ารวมได้หลายวิธี

% ของยอดรวมทั้งหมด

คุณสามารถแสดงค่าใน PivotTable เป็น% ของผลรวมทั้งหมด

ในกล่องชื่อที่กำหนดเองพิมพ์% ของผลรวมทั้งหมด
คลิกที่กล่องแสดงค่าเป็น
คลิกที่% ของผลรวมทั้งหมดในรายการแบบเลื่อนลง คลิกตกลง

PivotTable สรุปค่าเป็น% ของผลรวมทั้งหมด

ดังที่คุณสามารถสังเกตได้ผลรวมของยอดสั่งซื้อที่มุมบนซ้ายของ PivotTable และในพื้นที่ ∑ VALUES ในบานหน้าต่าง PivotTable Fields จะเปลี่ยนเป็นชื่อที่กำหนดเองใหม่ -% ของผลรวมทั้งหมด

คลิกที่ส่วนหัวของคอลัมน์ผลรวมทั้งหมด
พิมพ์% ของผลรวมทั้งหมดในแถบสูตร ทั้งส่วนหัวคอลัมน์และแถวจะเปลี่ยนเป็น% ของผลรวมทั้งหมด

% ของคอลัมน์ทั้งหมด

สมมติว่าคุณต้องการสรุปค่าเป็น% ของยอดรวมในแต่ละเดือน

คลิกที่ผลรวมของยอดสั่งซื้อในพื้นที่ ∑ VALUES
เลือกการตั้งค่าฟิลด์ค่าจากรายการดรอปดาวน์ กล่องโต้ตอบ Value Field Settings จะปรากฏขึ้น
ในกล่องชื่อที่กำหนดเองพิมพ์% ของยอดรวมเดือน
คลิกที่กล่องแสดงค่าเป็น
เลือก% ของคอลัมน์ทั้งหมดจากรายการแบบเลื่อนลง
คลิกตกลง

PivotTable สรุปค่าเป็น% ของผลรวมคอลัมน์ ในคอลัมน์เดือนคุณจะพบค่าเป็น% ของยอดรวมของเดือนที่ระบุ

คลิกที่ส่วนหัวของคอลัมน์ผลรวมทั้งหมด
พิมพ์% ของคอลัมน์ทั้งหมดในแถบสูตร ทั้งส่วนหัวคอลัมน์และแถวจะเปลี่ยนเป็น% ของคอลัมน์ทั้งหมด

% ของแถวทั้งหมด

คุณสามารถสรุปค่าเป็น% ของผลรวมของภูมิภาคและ% ของยอดรวมของพนักงานขายได้โดยเลือกกล่อง% ของผลรวมแถวในแสดงค่าเป็นในกล่องโต้ตอบการตั้งค่าฟิลด์ค่า

นับ

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

ยกเลิกการเลือกจำนวนคำสั่งซื้อ
ลากบัญชีไปที่พื้นที่ ∑ VALUES ผลรวมของบัญชีจะแสดงในพื้นที่ ∑ VALUES
คลิกที่ผลรวมของบัญชี
เลือกการตั้งค่าฟิลด์ค่าจากรายการดรอปดาวน์ กล่องโต้ตอบ Value Field Settings จะปรากฏขึ้น
ในกล่องสรุปค่าตามกล่องให้เลือกนับ ชื่อที่กำหนดเองจะเปลี่ยนเป็นจำนวนบัญชี
คลิกตกลง

จำนวนบัญชีจะแสดงดังที่แสดงด้านล่าง -

เฉลี่ย

สมมติว่าคุณต้องการสรุป PivotTable ตามค่าเฉลี่ยของภูมิภาค Order Amount อย่างชาญฉลาดพนักงานขายฉลาดและฉลาดเป็นเดือน

ยกเลิกการเลือกบัญชี
ลากจำนวนคำสั่งซื้อไปที่พื้นที่ ∑ VALUES ผลรวมของจำนวนคำสั่งซื้อจะแสดงในพื้นที่ ∑ VALUES
คลิกที่ผลรวมของยอดสั่งซื้อ
คลิกที่การตั้งค่าฟิลด์ค่าในรายการแบบเลื่อนลง กล่องโต้ตอบ Value Field Settings จะปรากฏขึ้น
ในช่องสรุปค่าตามกล่องคลิกที่ค่าเฉลี่ย ชื่อที่กำหนดเองจะเปลี่ยนเป็นค่าเฉลี่ยของจำนวนคำสั่งซื้อ
คลิกตกลง

ค่าเฉลี่ยจะแสดงดังภาพด้านล่าง -

คุณต้องตั้งค่ารูปแบบตัวเลขของค่าใน PivotTable เพื่อให้แสดงได้ง่ายขึ้น

คลิกที่ค่าเฉลี่ยของจำนวนการสั่งซื้อในพื้นที่ ∑ VALUES
คลิกที่การตั้งค่าฟิลด์ค่าในรายการแบบเลื่อนลง กล่องโต้ตอบ Value Field Settings จะปรากฏขึ้น
คลิกที่ปุ่มรูปแบบตัวเลข

กล่องโต้ตอบ Format Cells จะปรากฏขึ้น

คลิกที่หมายเลขภายใต้หมวดหมู่
พิมพ์ 2 ในกล่องตำแหน่งทศนิยมแล้วคลิกตกลง

ค่า PivotTable จะถูกจัดรูปแบบเป็นตัวเลขที่มีทศนิยมสองตำแหน่ง

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

สูงสุด

สมมติว่าคุณต้องการสรุป PivotTable ด้วยค่าสูงสุดของ Order Amount ตามภูมิภาคพนักงานขายที่ชาญฉลาดและเป็นรายเดือน

คลิกที่ผลรวมของยอดสั่งซื้อ
เลือกการตั้งค่าฟิลด์ค่าจากรายการดรอปดาวน์ กล่องโต้ตอบ Value Field Settings จะปรากฏขึ้น
ในช่องสรุปค่าตามกล่องคลิกสูงสุด ชื่อที่กำหนดเองจะเปลี่ยนเป็น Max of Order Amount

PivotTable จะแสดงขอบเขตค่าสูงสุดที่ชาญฉลาดพนักงานขายฉลาดและฉลาดรอบเดือน

คลิกที่ส่วนหัวของคอลัมน์ผลรวมทั้งหมด
พิมพ์ Max Order Amount ในแถบสูตร ทั้งส่วนหัวคอลัมน์และแถวจะเปลี่ยนเป็นจำนวนสั่งซื้อสูงสุด

นาที

สมมติว่าคุณต้องการสรุป PivotTable ด้วยค่าต่ำสุดของขอบเขต Order Amount อย่างชาญฉลาดพนักงานขายฉลาดและฉลาดเป็นเดือน

คลิกที่ผลรวมของยอดสั่งซื้อ
คลิกที่การตั้งค่าฟิลด์ค่าในรายการแบบเลื่อนลง กล่องโต้ตอบ Value Field Settings จะปรากฏขึ้น
ในช่องสรุปค่าตามกล่องคลิก Min. ชื่อที่กำหนดเองจะเปลี่ยนเป็นจำนวนสั่งซื้อขั้นต่ำ

PivotTable จะแสดงภูมิภาคค่าต่ำสุดที่ชาญฉลาดพนักงานขายฉลาดและฉลาดรอบเดือน

คลิกที่ส่วนหัวของคอลัมน์ผลรวมทั้งหมด
พิมพ์ Min Order Amount ในแถบสูตร ทั้งส่วนหัวคอลัมน์และแถวจะเปลี่ยนเป็นจำนวนสั่งซื้อขั้นต่ำ

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

ในบทนี้คุณจะได้เรียนรู้วิธีต่างๆในการอัปเดตเค้าโครงและ / หรือรีเฟรชข้อมูลใน PivotTable

กำลังอัปเดตเค้าโครง PivotTable

คุณสามารถตัดสินใจได้ว่าจะอัปเดต PivotTable ของคุณเมื่อใดก็ตามที่คุณเปลี่ยนแปลงเค้าโครงหรือจะอัปเดตโดยทริกเกอร์แยกต่างหาก

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

ตรวจสอบตัวเลือก - Defer Layout Update.

ปุ่ม UPDATE ที่อยู่ข้างๆจะเปิดใช้งาน หากคุณทำการเปลี่ยนแปลงใด ๆ กับพื้นที่ PivotTable การเปลี่ยนแปลงจะมีผลหลังจากที่คุณคลิกที่ปุ่ม UPDATE เท่านั้น

กำลังรีเฟรชข้อมูล PivotTable

เมื่อข้อมูลของ PivotTable มีการเปลี่ยนแปลงในแหล่งที่มาข้อมูลเดียวกันนี้สามารถแสดงใน PivotTable ได้โดยการรีเฟรช

คลิกที่ PivotTable
คลิกแท็บวิเคราะห์บน Ribbon
คลิกรีเฟรชในกลุ่มข้อมูล

มีตัวเลือกต่างๆในการรีเฟรชข้อมูลในรายการแบบเลื่อนลง -

Refresh - เพื่อรับข้อมูลล่าสุดจากแหล่งที่เชื่อมต่อกับเซลล์ที่ใช้งานอยู่
Refresh All - หากต้องการรับข้อมูลล่าสุดโดยการรีเฟรชแหล่งข้อมูลทั้งหมดในสมุดงาน
Connection Properties - เพื่อตั้งค่าคุณสมบัติการรีเฟรชสำหรับการเชื่อมต่อสมุดงาน

การเปลี่ยนแหล่งข้อมูลของ PivotTable

คุณสามารถเปลี่ยนช่วงของข้อมูลต้นทางของ PivotTable ตัวอย่างเช่นคุณสามารถขยายแหล่งข้อมูลเพื่อรวมแถวข้อมูลจำนวนมากขึ้น

อย่างไรก็ตามหากข้อมูลต้นทางมีการเปลี่ยนแปลงอย่างมากเช่นมีคอลัมน์มากขึ้นหรือน้อยลงให้ลองสร้าง PivotTable ใหม่

คลิกที่ PivotTable PIVOTTABLE TOOLS ปรากฏบน Ribbon
คลิกแท็บวิเคราะห์
คลิกเปลี่ยนแหล่งข้อมูลในกลุ่มข้อมูล

เลือกเปลี่ยนแหล่งข้อมูลจากรายการแบบเลื่อนลง

กล่องโต้ตอบเปลี่ยนแหล่งข้อมูล PivotTable จะปรากฏขึ้นและแหล่งข้อมูลปัจจุบันจะถูกเน้น

เลือกตารางหรือช่วงที่คุณต้องการรวมไว้ในกล่องตาราง / ช่วงภายใต้เลือกตารางหรือช่วง คลิกตกลง

แหล่งข้อมูลสำหรับ PivotTable จะเปลี่ยนเป็นตาราง / ช่วงข้อมูลที่เลือก

การเปลี่ยนเป็นแหล่งข้อมูลภายนอก

ถ้าคุณต้องการเปลี่ยนแหล่งข้อมูลสำหรับ PivotTable ของคุณที่เป็นแหล่งข้อมูลภายนอกคุณควรสร้าง PivotTable ใหม่ อย่างไรก็ตามหากตำแหน่งที่ตั้งของแหล่งข้อมูลภายนอกของคุณมีการเปลี่ยนแปลงตัวอย่างเช่นชื่อฐานข้อมูล SQL Server ของคุณเหมือนกัน แต่ถูกย้ายไปยังเซิร์ฟเวอร์อื่นหรือฐานข้อมูล Access ของคุณถูกย้ายไปยังเครือข่ายอื่นที่ใช้ร่วมกันคุณสามารถเปลี่ยน การเชื่อมต่อข้อมูลปัจจุบันของคุณเพื่อให้สอดคล้องกัน

คลิกที่ PivotTable
คลิกแท็บวิเคราะห์บน Ribbon
คลิก Change Data Sourceในกลุ่มข้อมูล Change PivotTable Data Source กล่องโต้ตอบปรากฏขึ้น
คลิก Choose Connection ปุ่ม.

Existing Connections กล่องโต้ตอบปรากฏขึ้น

เลือกการเชื่อมต่อทั้งหมดในกล่องแสดง การเชื่อมต่อทั้งหมดในสมุดงานของคุณจะปรากฏขึ้น
คลิก Browse for More ปุ่ม.

Select Data Source หน้าต่างจะปรากฏขึ้น

คลิกที่ปุ่มแหล่งที่มาใหม่
ทำตามขั้นตอนตัวช่วยสร้างการเชื่อมต่อข้อมูล

ถ้าแหล่งข้อมูลของคุณอยู่ในสมุดงาน Excel อื่นให้ทำดังต่อไปนี้ -

คลิกที่กล่องชื่อไฟล์
เลือกชื่อไฟล์สมุดงาน

การลบ PivotTable

คุณสามารถลบ PivotTable ได้ดังนี้ -

คลิกที่ PivotTable
คลิกแท็บวิเคราะห์บน Ribbon
คลิกเลือกในกลุ่มการดำเนินการ

เลือก Entire PivotTableจากรายการแบบเลื่อนลง PivotTable ทั้งหมดจะถูกเลือก

กดปุ่ม Delete PivotTable จะถูกลบ

ถ้า PivotTable อยู่บนแผ่นงานแยกต่างหากคุณยังสามารถลบ PivotTable ได้โดยการลบทั้งแผ่นงาน

คลิกขวาที่แท็บแผ่นงานแล้วเลือก Delete จากรายการแบบเลื่อนลง

ทั้งแผ่นงานพร้อมกับ PivotTable จะถูกลบ

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

เนื่องจากรายงาน PivotTable เป็นแบบอินเทอร์แอกทีฟคุณจึงทำการเปลี่ยนแปลงที่จำเป็นได้อย่างรวดเร็วเพื่อเน้นผลลัพธ์ที่เฉพาะเจาะจงเช่นแนวโน้มข้อมูลการสรุปข้อมูล ฯลฯ ในขณะที่นำเสนอ คุณยังสามารถให้ตัวชี้นำภาพเช่นตัวกรองรายงานตัวแบ่งส่วนข้อมูลไทม์ไลน์ PivotCharts และอื่น ๆ ให้กับผู้รับเพื่อให้พวกเขาเห็นรายละเอียดที่ต้องการได้

ในบทนี้คุณจะได้เรียนรู้วิธีต่างๆในการทำให้รายงาน PivotTable ของคุณน่าสนใจด้วยภาพที่ช่วยให้สามารถสำรวจข้อมูลได้อย่างรวดเร็ว

ลำดับชั้น

คุณได้เรียนรู้วิธีการซ้อนฟิลด์เพื่อสร้างลำดับชั้นในบท - การซ้อนใน PivotTable ในบทช่วยสอนนี้ คุณยังได้เรียนรู้วิธีจัดกลุ่ม / ยกเลิกการจัดกลุ่มข้อมูลใน PivotTable ในบท - การใช้เครื่องมือ PivotTable เราจะนำตัวอย่างบางส่วนเพื่อแสดงวิธีสร้างรายงาน PivotTable แบบโต้ตอบพร้อมลำดับชั้น

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

ตัวอย่างเช่นสมมติว่าคุณมีข้อมูลการขายสำหรับปีบัญชี 2015-16 สำหรับภูมิภาค - ตะวันออกเหนือใต้และตะวันตกตามที่ระบุด้านล่าง

สร้าง PivotTable ดังที่แสดงด้านล่าง

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

สมมติว่าคุณมีฟิลด์วันที่ในข้อมูลของคุณดังที่แสดงด้านล่าง

ในกรณีนี้คุณสามารถจัดกลุ่มข้อมูลตามฟิลด์วันที่ได้ดังนี้ -

สร้าง PivotTable

ดังที่คุณสังเกตได้ว่า PivotTable นี้ไม่สะดวกในการเน้นข้อมูลสำคัญ

จัดกลุ่มฟิลด์ PivotTable ตามวันที่ (คุณได้เรียนรู้การจัดกลุ่มในบท - การสำรวจข้อมูลด้วยเครื่องมือ PivotTable ในบทช่วยสอนนี้)
วางฟิลด์พนักงานขายในพื้นที่ตัวกรอง
กรองป้ายชื่อคอลัมน์เป็นภูมิภาคตะวันออก

ตัวกรองรายงาน

สมมติว่าคุณต้องการรายงานสำหรับพนักงานขายแต่ละคนแยกกัน คุณสามารถทำได้ดังนี้ -

ตรวจสอบให้แน่ใจว่าคุณมีฟิลด์พนักงานขายในพื้นที่ตัวกรอง
คลิกที่ PivotTable
คลิกแท็บวิเคราะห์บน Ribbon
คลิกลูกศรถัดจากตัวเลือกในกลุ่ม PivotTable
เลือกแสดงหน้าตัวกรองรายงานจากรายการดรอปดาวน์

Show Report Filter Pagesกล่องโต้ตอบปรากฏขึ้น เลือกฟิลด์พนักงานขายแล้วคลิกตกลง

แผ่นงานแยกต่างหากสำหรับแต่ละค่าของฟิลด์พนักงานขายจะถูกสร้างขึ้นโดยมีการกรอง PivotTable เป็นค่านั้น

แผ่นงานจะถูกตั้งชื่อตามค่าของเขตข้อมูลซึ่งมองเห็นได้บนแท็บของแผ่นงาน

ตัวแบ่งส่วนข้อมูล

คุณสมบัติที่ซับซ้อนอีกอย่างที่คุณมีใน PivotTables คือตัวแบ่งส่วนข้อมูลที่สามารถใช้เพื่อกรองฟิลด์ด้วยสายตา

คลิกที่ PivotTable
คลิกแท็บวิเคราะห์
คลิกแทรกตัวแบ่งส่วนข้อมูลในกลุ่มตัวกรอง
คลิก Order Date, Quarters and Yearsในกล่องโต้ตอบแทรกตัวแบ่งส่วนข้อมูล ตัวแบ่งส่วนข้อมูลสามตัว - วันที่สั่งซื้อไตรมาสและปีจะถูกสร้างขึ้น
ปรับขนาดของตัวแบ่งส่วนข้อมูลเพิ่มคอลัมน์เพิ่มเติมสำหรับปุ่มบนตัวแบ่งส่วนข้อมูล
สร้างตัวแบ่งส่วนข้อมูลสำหรับพนักงานขายและเขตข้อมูลภูมิภาคด้วย
เลือกสไตล์ตัวแบ่งส่วนข้อมูลเพื่อให้เขตข้อมูลวันที่ถูกจัดกลุ่มเป็นสีเดียวและอีกสองช่องจะได้สีที่ต่างกัน
ยกเลิกการเลือกเส้นตาราง

อย่างที่คุณเห็นไม่เพียง แต่คุณมีรายงานเชิงโต้ตอบเท่านั้น แต่ยังมีรายงานที่น่าสนใจอีกด้วยซึ่งสามารถเข้าใจได้ง่าย

ไทม์ไลน์ใน PivotTable

เมื่อคุณมีฟิลด์วันที่ใน PivotTable การแทรกไทม์ไลน์ยังเป็นตัวเลือกในการสร้างรายงานเกี่ยวกับสุนทรียศาสตร์

สร้าง PivotTable กับพนักงานขายในพื้นที่ ROWS และภูมิภาคในพื้นที่ COLUMNS
แทรกไทม์ไลน์สำหรับฟิลด์วันที่สั่งซื้อ
กรองไทม์ไลน์เพื่อแสดงข้อมูล 5 เดือนตั้งแต่พฤศจิกายน 2015 ถึงมีนาคม 2016

คำสั่งการออกแบบ

PIVOTTABLE TOOLS - DESIGN คำสั่งบน Ribbon ให้คุณมีตัวเลือกในการจัดรูปแบบ PivotTable รวมถึงสิ่งต่อไปนี้ -

Layout
ตัวเลือกรูปแบบ PivotTable
สไตล์ PivotTable

เค้าโครง

คุณสามารถมีเค้าโครง PivotTable ตามความต้องการของคุณดังต่อไปนี้ -

Subtotals
ผลรวมทั้งหมด
เค้าโครงรายงาน
แถวว่าง

PivotTable Layout – Subtotals

คุณมีตัวเลือกว่าจะแสดงหรือไม่ Subtotalsหรือไม่. โดยค่าเริ่มต้นผลรวมย่อยจะแสดงที่ด้านบนสุดของกลุ่ม

ดังที่คุณสามารถสังเกตกลุ่มที่ไฮไลต์ - ตะวันออกผลรวมย่อยจะอยู่ที่ด้านบนสุดของกลุ่ม คุณสามารถเปลี่ยนตำแหน่งของผลรวมย่อยได้ดังนี้ -

คลิกที่ PivotTable
คลิกแท็บออกแบบบน Ribbon
คลิกผลรวมย่อยในกลุ่มตัวเลือกเค้าโครง
คลิกแสดงผลรวมย่อยทั้งหมดที่ด้านล่างของกลุ่ม

ผลรวมย่อยจะปรากฏที่ด้านล่างของแต่ละกลุ่ม

หากคุณไม่ต้องรายงานผลรวมย่อยคุณสามารถเลือก - ไม่ต้องแสดงผลรวมย่อย

ผลรวมทั้งหมด

คุณสามารถเลือกที่จะแสดงผลรวมสูงสุดหรือไม่ก็ได้ คุณมีชุดค่าผสมที่เป็นไปได้สี่แบบ -

ปิดสำหรับแถวและคอลัมน์
เปิดสำหรับแถวและคอลัมน์
เปิดสำหรับแถวเท่านั้น
เปิดสำหรับคอลัมน์เท่านั้น

โดยค่าเริ่มต้นจะเป็นชุดค่าผสมที่สอง - เปิดสำหรับแถวและคอลัมน์

เค้าโครงรายงาน

คุณสามารถเลือกเค้าโครงรายงานได้หลายแบบซึ่งเป็นรูปแบบที่เหมาะสมกับข้อมูลของคุณมากที่สุด

แบบฟอร์มขนาดกะทัดรัด
แบบฟอร์มร่าง
รูปแบบตาราง

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

เค้าโครงรายงานเริ่มต้นคือรูปแบบกระชับที่คุณคุ้นเคย

Compact Form

แบบฟอร์มขนาดกะทัดรัดปรับ PivotTable ให้เหมาะสมสำหรับการอ่าน อีกสองรูปแบบจะแสดงส่วนหัวของเขตข้อมูลด้วย

คลิกที่ Show ในแบบฟอร์มโครงร่าง

คลิก Show ในรูปแบบตาราง

พิจารณาเค้าโครง PivotTable ต่อไปนี้โดยที่เดือนฟิลด์จะซ้อนอยู่ภายใต้เขตข้อมูลภูมิภาค -

ดังที่คุณสังเกตได้ป้ายกำกับเดือนจะซ้ำกันและเป็นค่าเริ่มต้น

คลิกอย่าทำซ้ำป้ายกำกับรายการ ป้ายกำกับเดือนจะแสดงเพียงครั้งเดียวและ PivotTable จะดูชัดเจน

แถวว่าง

เพื่อให้รายงาน PivotTable ของคุณแตกต่างกันมากขึ้นคุณสามารถแทรกบรรทัดว่างหลังแต่ละรายการได้ คุณสามารถลบเส้นว่างเหล่านี้ได้ทุกเมื่อในภายหลัง

คลิก Insert Blank Line after Each Item.

ตัวเลือกรูปแบบ PivotTable

คุณมีตัวเลือกรูปแบบ PivotTable ดังต่อไปนี้ -

ส่วนหัวของแถว
ส่วนหัวของคอลัมน์
แถวแถบ
คอลัมน์สี

ตามค่าเริ่มต้นกล่องสำหรับส่วนหัวแถวและส่วนหัวคอลัมน์จะถูกเลือก ตัวเลือกเหล่านี้มีไว้สำหรับการแสดงการจัดรูปแบบพิเศษสำหรับแถวแรกและคอลัมน์แรกตามลำดับ ทำเครื่องหมายในช่องBanded Rows.

เลือกช่อง Banded Columns

สไตล์ PivotTable

คุณสามารถเลือกสไตล์ PivotTable ได้หลายแบบ เลือกสิ่งที่เหมาะกับรายงานของคุณ ตัวอย่างเช่นหากคุณเลือก Pivot Style Dark 5 คุณจะได้รับสไตล์ต่อไปนี้สำหรับ PivotTable