co-create
บทความนี้คือส่วนหนึ่งของโครงการ ร่วมสร้าง “หนังสือคู่มือ Excel ที่เจ๋งที่สุด” ใครที่มี comment เพื่อแนะนำ ปรับปรุงหนังสือได้ คุณจะได้เครดิตในฐานะผู้ร่วมเขียน ลงในหนังสือที่จะพิมพ์จริงๆ ด้วย! อ่านรายละเอียด และดูสารบัญหนังสือ คลิ๊กที่นี่


 

PivotTable คืออะไร?

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

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

ตัวอย่างการสรุปผล

จากข้อมูลยุบยับในลักษณะ Database แบบในบทที่ผ่านๆ มา

data-source

เราสามารถนำมาสรุปผลใน PivotTable แบบง่ายๆ เช่นตารางนี้ได้เลย

pivot-sample-01

หรือสามารถเพิ่มมุมมองของข้อมูลที่เจาะรายละเอียดมากขึ้นได้อย่างง่ายดาย

pivot-sample-02

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

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

PivotTable ไม่ยากอย่างที่คิด

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

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

 

 

Step การใช้ Pivot Table

1. คิดผลลัพธ์ที่อยากได้

ให้นึกภาพในใจ หรือออกแบบผลลัพธ์คร่าวๆ ว่าเป้าหมายสุดท้ายแล้วเราต้องการจะรู้อะไร แล้วจะต้องสรุปผลอะไรออกมา บ่อยครั้งที่เราต้องการข้อมูลสรุปไปทำกราฟต่อ ซึ่งเราอาจต้องคิดไปถึงกราฟสุดท้ายที่อยากได้เลย จากนั้นก็ต้องไปหาข้อมูลสนับสนุนที่จะนำมาทำตาราง Pivot อีกทีให้ได้ ซึ่งก็คือการคิดย้อนกลับ (Backward Thinking) นั่นเอง

2. เตรียมข้อมูล

ต้องเตรียมข้อมูล (Source Data) ให้อยู่ในลักษณะ Database ก่อน คือบรรทัดบนสุดของบริเวณข้อมูลที่ต้องการนำมา Pivot ต้องเป็นชื่อคอลัมน์ ห้ามเป็นช่องว่าง ซึ่งการอาจจะต้องมีการจัดการข้อมูลเป็นพิเศษในบางเรื่อง ซึ่งพูดไปแล้วในบทก่อนหน้านี้ เช่น

  • ทำการ Cleansing ข้อมูลให้ มีรูปแบบที่ถูกต้อง เช่น อาจต้อง TRIM ช่องว่างออก
  • Convert ข้อมูลให้อยู่ในรูปแบบที่จัดการง่ายขึ้น เช่น แปลงวันที่ในรูปแบบ Text (20140831) ให้เป็นวันที่จริงๆ (31/08/2014)
  • สร้างคอลัมน์ช่วยเหลือ (Helper Column) ขึ้นมาเพื่อให้สามารถ Pivot ข้อมูลได้ง่ายขึ้น เช่น ถ้าในรายงานเราจะต้องมานั่งเลือก Filter จากหลายๆ คอลัมน์ในแบบเดิมๆ ทุกครั้ง แทนที่เราจะต้องกด Filter ทีละ Field เราก็รวม criteria นั้น สร้างไว้ใน Field ใหม่ ด้วยการใช้ฟังก์ชั่นทางตรรกะมาช่วย เช่น =AND(A1= “แบบ1”,B1>10,C1<2000) แล้วก็จะออกมาเป็น TRUE/FALSE เลย เป็นต้น

เทคนิคการเตรียมข้อมูล เพื่อให้รองรับการเพิ่มข้อมูลในอนาคต

คนที่ไม่ได้คิดเรื่อง Source Data ให้ดี จะเจอปัญหาอย่างหนึ่ง คือ พอเวลามีข้อมูลต้นทาง (Source Data) มีจำนวนบรรทัดเพิ่มมากขึ้น จะต้องมาแก้ Source data ของ PivotTable ใหม่ทุกครั้ง ซึ่งเสียเวลา และเสี่ยงต่อการทำผิดสูง แต่มีวิธีแก้ 2 วิธี ที่ผมอยากจะแนะนำ

  1. เตรียมข้อมูลให้เป็น Table แบบเดียวกับที่พูดถึงในบทที่แล้ว เพราะมีข้อดี คือ หากในอนาคตข้อมูล  Source Data ของเราเพิ่มขึ้นอีก เราจะสามารถใช้ความสามารถเรื่อง Dynamic Range ซึ่งจะทำให้เราไม่ต้องแก้ไข  Source Data อีกเลย
  2. ให้เลือกข้อมูลทั้งคอลัมน์ไปเลย (เลือกทุกแถว) วิธีนี้ใช้กรณีที่ไม่ได้ทำ Source Data เป็น Table แต่มีข้อเสียเปรียบกว่าวิธีแรก คือ อาจต้อง Filter ข้อมูลที่เป็น Blank ออกอีกทีใน PivotTable ซึ่งจะมีอธิบายในขั้นตอนถัดไป

3. เริ่ม Pivot แล้วทำการปรับแต่งให้ได้รูปแบบตามต้องการ

  • ให้คลิ๊กที่ส่วนใดส่วนหนึ่งของตาราง Database ที่เตรียมไว้
    แล้วกด
    [Insert] –> Tables –> PivotTable
  • ตรวจดูก่อนว่ามันคลุมตารางครบหรือไม่ ?
    • ถ้าเตรียมข้อมูล Database ดีในแบบที่ผมแนะนำไปมันก็จะครบอยู่แล้ว
    • หากมีการใช้ Tableเป็นSource Dataมันจะขึ้นชื่อTableมาให้เลย ซึ่งไม่ต้องมานั่งตรวจให้เสียเวลาอีกเช่นกัน
  • เลือกได้ว่าจะให้ข้อมูลที่ Pivot แล้วไปสร้างเป็น Sheet ใหม่ หรือไว้ใน Sheet ที่มี

Pivot-Source

 

Field List

เวลาสร้างตาราง Pivot ขึ้นมาแล้ว หัวตารางของข้อมูลต้นฉบับของเรา จะกลายเป็น Field List ของ PivotTable ซึ่งจะวางอยู่ด้านขวาบน เปรียบเหมือน Item ที่ให้เราหยิบไปใช้ทำอะไรได้มากมาย

เราสามารถลาก Field List แต่ละอันลงไปใน Slot ทั้งสี่อัน ที่อยู่บริเวณข้างล่างขวามือ แต่ละอันมีความหมายดังนี้

Pivot-FieldList-add

  • Report Filterทำหน้าที่คัดกรองข้อมูล ให้แสดงเฉพาะข้อมูลบรรทัดเดียวกับที่ Filter ไว้
  • Column Labelนำข้อมูลมาไว้ที่หัวตาราง Pivot (ด้านบน) เพื่อจัดกลุ่มแบบคอลัมน์ โดยมันจะแสดง item ทั้งหมดที่ไม่ซ้ำกันใน Field ที่คุณเลือกมาไว้ที่หัวตาราง Pivot (ด้านบน)
  • Row Labelนำข้อมูลมาไว้ที่ด้านซ้ายของตาราง Pivot เพื่อจัดกลุ่มแบบแถว โดยมันจะแสดง item ทั้งหมดที่ไม่ซ้ำกันใน Field ที่คุณเลือกมาไว้ที่ด้านซ้ายของตาราง Pivot
  • Valuesเป็นการคำนวณสรุปผล ทำได้หลายรูปแบบ เช่น SUM, Count, Average, Max, Min โดยอาจแสดงได้หลายรูปแบบ เช่น รูปแบบปกติ, %ของทั้งหมด, %ของแถว,%ของคอลัมน์, การรวมแบบสะสมค่า เป็นต้น

โดยField List 1 อัน สามารถลากลงไปใช้ในช่อง Values ซ้ำได้มากกว่า 1 ครั้ง เช่น อันแรกเราเอาไปหาค่า SUM อันที่สองเราสามารถเอา Field เดิมไปหาค่า MAX หรือจะเปลี่ยนรูปแบบการแสดงผลให้ต่างกันได้ เป็นต้น

ลองลาก Field List หลายๆ แบบ

ลาก Field จำนวนเงินที่จ่าย มาที่ช่อง Value 1 อัน : มันจะทำการคำนวณสรุปผลข้อมูลให้ แบบนี้คือเหมือนการ SUM ข้อมูลทั้งหมดแบบไม่มีเงื่อนไข หรือการแยกประเภทใดๆ ทั้งสิ้น

layout-1

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

layout-1-2

ลาก Field ผู้ซื้อ มาที่ Row Label : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกัน

layout-2

ลองลาก Field จำนวน มาที่ช่อง Value เพิ่มอีก 1 อัน :

layout-3

ลองลาก Field จำนวน มาที่ช่อง Value ซ้ำเป็นอันที่ 2 ก็ยังได้ : ในบทต่อไป ผมจะสอนวิธีเปลี่ยนจาก SUM เป็นการสรุปอย่างอื่นได้อีกครับ เช่น Count, Average

layout-3-2

 

ลองย้าย Values  : จาก Column Labels มาที่ Row Label

layout-4

ลากอีก Field สินค้า มาไว้ที่ Report Filter : แล้วลองเลือก Dropdown คัดกรองดู

layout-5

ลากอีก Field ผู้ขาย มาที่ Column Label : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกัน

layout-6

ลากอีก Field ผู้ขาย มาที่ RowLabel ให้ซ้อนกับผู้ซื้อ : มันจะแสดง item ทั้งหมดที่ไม่ซ้ำกัน

layout-7

ลองย้าย Values : ไปยังหลายๆ ตำแหน่ง เช่น สลับลำดับใน Row หรือ ย้ายกลับไป Column

layout-7-2

layout-7-3

วิธีการแปลความหมายตาราง Pivot Table

ตัวอย่าง ผมลองลาก Field ต่างๆ มาลง 4 Slot ข้างล่างตามรูป

Pivot-Basic

วิธีตีความคือให้ดูว่า ข้อมูลที่กำลังสรุปผลเป็นการ Summary จาก Field ไหนบ้าง โดยต้องดูว่าข้อมูลมัน Cross กันแบบไหน ทั้ง 4 slot เลย

ตัวอย่าง ในช่อง C12 หมายถึง

นาย c (row) / ซื้อสินค้าใดๆก็ตาม (ไม่ได้ filter เจาะจงสินค้า) / กับ sales ค (column) / ด้วยเครดิตการ์ด (row) / เป็นจำนวนรวม 1000 บาท (Values – สรุปด้วย SUM)

เห็นมั๊ยครับว่า PivotTable นี่สามารถอ่านผลลัพธ์ได้ง่ายและรวดเร็วจริงๆ!!