การวิเคราะห์ข้อมูล Excel - PivotTables

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

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

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

การสร้าง PivotTable

ในการสร้าง PivotTables ตรวจสอบให้แน่ใจว่าแถวแรกมีส่วนหัว

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

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

  • ในกล่องตาราง / ช่วงพิมพ์ชื่อตาราง
  • คลิกแผ่นงานใหม่เพื่อบอก Excel ว่าจะเก็บ PivotTable ไว้ที่ใด
  • คลิกตกลง

PivotTable ว่างและรายการเขตข้อมูล PivotTable จะปรากฏขึ้น

PivotTables ที่แนะนำ

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

  • คลิกตารางข้อมูล

  • คลิกที่แท็บ INSERT

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

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

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

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

เขตข้อมูล PivotTable

ส่วนหัวในตารางข้อมูลของคุณจะปรากฏเป็นฟิลด์ใน PivotTable

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

พื้นที่ PivotTable

คุณยังสามารถเปลี่ยนเค้าโครงของ PivotTable ได้ทันที คุณสามารถใช้พื้นที่ PivotTable เพื่อทำสิ่งนี้ให้สำเร็จ

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

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

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

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

การทำรังใน PivotTable

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

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

PivotTable ผลลัพธ์มีดังนี้ -

ในพื้นที่ PivotTable ในแถวให้คลิกภูมิภาคแล้วลากไปด้านล่างพนักงานขายเพื่อให้มีลักษณะดังนี้ -

ลำดับการซ้อนจะเปลี่ยนไปและ PivotTable ที่เป็นผลลัพธ์จะเป็นดังนี้ -

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

ฟิลเตอร์

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

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

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

คุณจะเห็นว่า -

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

คลิกลูกศรในกล่องทางด้านขวาของขอบเขตตัวกรอง รายการดรอปดาวน์พร้อมค่าของเขตข้อมูลจะปรากฏขึ้น

  • ตรวจสอบตัวเลือก Select Multiple Items. กล่องกาเครื่องหมายปรากฏขึ้นสำหรับค่าทั้งหมด
  • เลือกทิศใต้และทิศตะวันตกและยกเลิกการเลือกค่าอื่น ๆ แล้วคลิกตกลง

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

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

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

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

  • คลิกวิเคราะห์ภายใต้ PIVOTTABLE TOOLS บน Ribbon

  • คลิกแทรกตัวแบ่งส่วนข้อมูลในกลุ่มตัวกรอง กล่องแทรกตัวแบ่งส่วนข้อมูลจะปรากฏขึ้น ประกอบด้วยฟิลด์ทั้งหมดจากข้อมูลของคุณ

  • เลือกฟิลด์ภูมิภาคและเดือน คลิกตกลง

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

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

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

การสรุปค่าโดยการคำนวณอื่น ๆ

ในตัวอย่างที่ผ่านมาคุณได้เห็นการสรุปค่าโดย Sum อย่างไรก็ตามคุณสามารถใช้การคำนวณอื่น ๆ ได้หากจำเป็น

ในรายการเขตข้อมูล PivotTable

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

กล่อง Value Field Settings จะปรากฏขึ้น การคำนวณหลายประเภทปรากฏเป็นรายการภายใต้ฟิลด์สรุปค่าโดย -

  • เลือกนับในรายการ
  • ชื่อที่กำหนดเองจะเปลี่ยนเป็นจำนวนบัญชีโดยอัตโนมัติ คลิกตกลง

PivotTable สรุปค่าบัญชีตามจำนวน

เครื่องมือ PivotTable

ทำตามขั้นตอนด้านล่างเพื่อเรียนรู้การใช้เครื่องมือ PivotTable

  • เลือก PivotTable

เครื่องมือ PivotTable ต่อไปนี้ปรากฏบน Ribbon -

  • ANALYZE
  • DESIGN

วิเคราะห์

บางส่วนของ ANALYZE คำสั่ง Ribbon คือ -

  • ตั้งค่าตัวเลือก PivotTable
  • การตั้งค่าฟิลด์ค่าสำหรับฟิลด์ที่เลือก
  • ขยายฟิลด์
  • ยุบฟิลด์
  • แทรกตัวแบ่งส่วนข้อมูล
  • แทรกไทม์ไลน์
  • รีเฟรชข้อมูล
  • เปลี่ยนแหล่งข้อมูล
  • ย้าย PivotTable
  • แก้คำสั่งซื้อ (หากมีการคำนวณเพิ่มเติม)
  • PivotChart

ออกแบบ

บางส่วนของ DESIGN คำสั่ง Ribbon คือ -

  • เค้าโครง PivotTable
    • ตัวเลือกสำหรับผลรวมย่อย
    • ตัวเลือกสำหรับผลรวมทั้งหมด
    • แบบฟอร์มรายงาน
    • ตัวเลือกสำหรับแถวว่าง
  • ตัวเลือกรูปแบบ PivotTable
  • สไตล์ PivotTable

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

คุณสามารถขยายหรือยุบรายการทั้งหมดของเขตข้อมูลที่เลือกได้สองวิธี -

  • โดยเลือกสัญลักษณ์
    หรือ
    ทางด้านซ้ายของฟิลด์ที่เลือก
  • โดยการคลิกช่องขยายหรือยุบเขตข้อมูลบนริบบิ้นวิเคราะห์

โดยการเลือกสัญลักษณ์ขยาย
หรือสัญลักษณ์ยุบ
ทางด้านซ้ายของเขตข้อมูลที่เลือก

  • เลือกเซลล์ที่มีทิศตะวันออกใน PivotTable
  • คลิกสัญลักษณ์ยุบ
    ทางด้านซ้ายของตะวันออก

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

Expand

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

คลิกสัญลักษณ์ขยาย

ทางด้านซ้ายของตะวันออก รายการทั้งหมดด้านล่างตะวันออกจะปรากฏขึ้น

ใช้ ANALYZE บน Ribbon

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

  • คลิกเซลล์ที่มีทิศตะวันออกใน PivotTable
  • คลิกแท็บวิเคราะห์บน Ribbon
  • คลิกยุบฟิลด์ในกลุ่มฟิลด์ที่ใช้งานอยู่

รายการทั้งหมดของเขตข้อมูลตะวันออกใน PivotTable จะยุบ

คลิกขยายฟิลด์ในกลุ่ม Active Field

รายการทั้งหมดจะปรากฏขึ้น

รายงานรูปแบบการนำเสนอ

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

  • คลิกตะวันออกใน PivotTable
  • คลิกวิเคราะห์
  • คลิกการตั้งค่าฟิลด์ในกลุ่มฟิลด์ที่ใช้งานอยู่ กล่องโต้ตอบการตั้งค่าฟิลด์จะปรากฏขึ้น
  • คลิกแท็บ Layout & Print
  • เลือกแทรกบรรทัดว่างหลังป้ายกำกับแต่ละรายการ

แถวว่างจะแสดงหลังจากแต่ละค่าของเขตข้อมูลภูมิภาค

คุณสามารถแทรกแถวว่างจากไฟล์ DESIGN แท็บด้วย

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

PivotTable ในรูปแบบเค้าร่างพร้อมกับสไตล์ที่เลือกจะแสดงขึ้น

ไทม์ไลน์ใน PivotTables

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

สร้าง PivotTable จากช่วงนี้ด้วย -

  • สถานที่และพนักงานขายในแถวตามลำดับนั้น
  • สินค้าในคอลัมน์
  • ผลรวมของจำนวนเงินในการสรุปค่า
  • คลิก PivotTable
  • คลิกที่แท็บ INSERT
  • คลิกไทม์ไลน์ในกลุ่มฟิลเตอร์ เส้นเวลาแทรกจะปรากฏขึ้น

คลิกวันที่และคลิกตกลง กล่องโต้ตอบไทม์ไลน์จะปรากฏขึ้นและเครื่องมือไทม์ไลน์จะปรากฏบน Ribbon

  • ในกล่องโต้ตอบไทม์ไลน์เลือก MONTHS
  • จากรายการแบบเลื่อนลงให้เลือก QUARTERS
  • คลิก 2014 Q2
  • กดปุ่ม Shift ค้างไว้แล้วลากไปที่ 2014 Q4

ไทม์ไลน์ถูกเลือกเป็น Q2 - Q4 2014

PivotTable ถูกกรองลงในไทม์ไลน์นี้