การวิเคราะห์ข้อมูล 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 ถูกกรองลงในไทม์ไลน์นี้