คุณคิดว่า Pivot Table ไม่ค่อยเวิร์คหรือเปล่าครับ?

ถ้าใช่ แสดงว่าคุณคือ 1 ใน 2 ประเภทนี้

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

Pivot_Problemเท่าที่ผมเคยเจอ ส่วนใหญ่จะเป็นแบบที่ 2 ซึ่งเป็นเรื่องปกติ เพราะ Pivot Table มีอะไรจุ๊กจิ๊กกุ๊กกิ๊กมากมาย (แต่ไม่มีกิ๊กนะ ^^)

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

ผมขอเรียกการทำความรู้จักนี้ว่า 9 สิ่งควร(ต้อง)รู้ กับ Pivot Table

1 สรีระของพิเวิทเทเบิล

สรีระของพิเวิทเทเบิล มี 4 ส่วนด้วยกันคือ

  • Filter: ตัวกรองชั้นแรกสุด เพื่อเลือกเฉพาะข้อมูลที่ต้องการให้แสดงในพิเวิทเทเบิล
  • Row Label: คือข้อมูลที่ต้องการให้แสดงด้านซ้ายของตาราง แสดงข้อมูลจากบนลงล่าง ส่วนใหญ่มักใช้กับข้อมูลที่เป็นตัวหนังสือ เช่น ชื่อลูกค้า ชื่อสินค้า
  • Column Label: คือข้อมูลที่ต้องการให้แสดงในแถวบนสุดของตาราง แสดงข้อมูลจากซ้ายไปขวา เช่น เดือน, เขตการขาย
  • Value: คือตัวเลขที่แสดงในตาราง มักแสดงในรูปแบบของ
    1. ผลรวม (ผลบวก) ส่วนใหญ่เราใช้แบบนี้
    2. จำนวนนับ (นับอย่างเดียว ไม่ต้องรวมหรือหาค่าเฉลี่ย)
    3. ค่าเฉลี่ย

ข้อแนะนำเกี่ยวกับ Column Label คือ ซอยย่อยแล้วต้องมีไม่มากเกินไป เช่น

  • ไตรมาส: ซอยย่อยได้ทั้งหมด 4 อย่าง
  • เดือน: ซอยย่อยได้ทั้งหมด 12 อย่าง
  • ภาคของประเทศไทย: ซอยย่อยได้ประมาณ 6-7 อย่าง (ถ้าแยกกรุงเทพและปริมณฑลเป็น 1 ภาค)

แบบนี้โอเค

แต่ถ้าเป็นข้อมูลบางอย่าง เช่น

  • จังหวัดในประเทศไทย ซอยย่อยได้ถึง 77 อย่าง
  • ชื่อสินค้า อาจซอยย่อยได้ถึง 100 อย่าง

แบบนี้ไม่เหมาะกับการใช้เป็น Column Label เพราะทำออกมาแล้วมีคอลัมน์เยอะมาก ดูลายตาไปหมด สุดท้ายก็ไม่มีคนอ่านรีพอร์ตนี้

Slide1มีคำถามนึงที่ผมเคยได้รับ และคิดว่าน่าสนใจคือ

“อะไรควรเป็น Filter และ อะไรควรเป็น Row Label?”

หลายคนสับสนกับสองส่วนนี้ ไม่รู้ว่าต่างกันยังไง

จริงๆแล้ว ทั้งสองส่วนคล้ายคลึงกันในเรื่องของการกรองข้อมูล แต่ส่วนสำคัญที่แตกต่างกันคือ

ต้องการแสดงให้เห็นข้อมูลทางด้านซ้ายมือหรือเปล่า?

ถ้าเรานำ “ชื่อลูกค้า” ไปใส่ไว้ในส่วนของ Filter เช่น กำหนดว่าแสดงเฉพาะข้อมูลของ Tesco, Central และ Big C เท่านั้น

พิเวิทเทเบิลจะแสดงเฉพาะตัวเลขที่เกียวข้องกับ Tesco, Central และ Big C (ข้อมูลของลูกค้ารายอื่น เช่น The Mall, Siam Paragon ถูกตัดออก) และจะไม่แสดงชื่อ Tesco, Central และ Big C ในด้านซ้ายมือของพิเวิทเทเบิลเลย

แต่ถ้าเรานำ ชื่อลูกค้า ไปใส่ไว้ใน Row Label และกรองเฉพาะข้อมูลของ Tesco, Central และ Big C มาแสดง

พิเวิทเทเบิลจะแสดงค่า Tesco, Central และ Big C ให้เห็นจะๆทางด้านซ้ายมือของตารางเลย หนึ่งบรรทัดต่อหนึ่งลูกค้า (ข้อมูลของลูกค้าอื่นๆ เช่น The Mall, Siam Paragon ถูกตัดออกไป เพราะไม่ได้ถูกเลือกให้แสดงในตาราง)

อนึ่ง คิดถึงพอสังเขป (มุกนี้รู้อายุเลยนะนี่ ^^) ถ้าใครเคยลองเล่น Power Pivot จะพบว่า

สรีระของ Power Pivot แตกต่างจากสรีระของ Pivot Table เล็กน้อย

เพราะพิเวิทเทเบิลที่สร้างจาก Power Pivot มีองค์ประกอบเพิ่มอีก 2 ส่วนคือ

  • Slicers Vertical
  • Slicer Horizontal

หรือพูดง่ายๆก็คือเราสามารถผนวก Slicer ให้เป็นส่วนหนึ่งของพิเวิทเทเบิลได้นั่นเอง

PowerPivotFieldList_151219

แต่… ต้องเป็นพิเวิทเทเบิลที่สร้างโดย Power Pivot เท่านั้น

สำหรับคนที่ใช้พิเวิทเทเบิลแบบธรรมดา สามารถสร้าง Slicer ได้นะครับ เป็นฟีเจอร์ที่มีตั้งแต่เวอร์ชั่น 2010 เป็นต้นไป เพียงแต่เลย์เอาต์อาจไม่เนียนกริ๊ปเหมือนของ Power Pivot ^_^

.

2 ไม่อยากได้ SubTotal ทำไงดี?

ก่อนหน้านี้ ผมไม่ชอบพิเวิทเทเบิลเลย เพราะรำคาญเจ้า Subtotal มาก!

ภายหลังพอรู้ว่า สามารถเลือกฟอร์แมตให้ไม่ต้องแสดง Subtotal ได้ ถึงสำเหนียกว่าเซ่ออยู่ตั้งนาน T_T

วิธีนำ Subtotal ออกก็ง่ายมากครับ

คลิกขวาที่ฟิลด์นั้น/เลือก Subtotal ออกไปเลย

Slide2หรืออาจคลิกที่ริบบอน

Design/Subtotals/Do Not Show Subtotals ก็ได้เหมือนกัน แล้วแต่สะดวกครับ ^_^

DoNotShowSubtotals_151219

.

3 เลือกเฉพาะ Grand Total ที่ต้องการ

ผมว่าเจ้าพิเวิทเทเบิลนี่ชอบบังคับเรานะ นอกจากจะยัดเยียด Subtotal มาแล้ว ยังชอบยัดเยียดเจ้า Grand Total มาให้อีก

บางครั้งเราก็อยากได้นะ แต่บางครั้งก็ไม่อยากนี่สิ ปัญหาคือจะเอาออกยังไงดี?

ง่ายมากครับ ไปที่

Design/Grand Totals

แล้วเลือกว่าต้องการให้โชว์เฉพาะ Grand Total ตามแนวนอน (Rows Only) หรือตามแนวตั้ง (Columns Only) ตามใจชอบเลยครับ

Slide3

.

4 ปรับชื่อฟิลด์ตัวเลขตามใจฉัน

นอกจากเรื่อง Grand Total แล้ว ผมยังเบื่อเจ้าพิเวิทเวลาตั้งชื่อฟิลด์ที่เป็นตัวเลขด้วย

สมมติว่าฟิลด์ที่เป็นตัวเลขชื่อ “Amount” พอเราดึงเจ้านี่มาไว้ในส่วน Value แล้วเลือกให้แสดงค่าแบบผลรวม เอ็กเซลดั๊นเปลี่ยนชื่อให้เป็น

“Sum of Amount” ซะงั้น โดยที่เราไม่ได้ขอเล๊ย!

ปัญหาคือ เราอยากให้มันโชว์แค่ “Amount” เฉยๆ (แม้ว่าต้องการให้คิดแบบ SUM ก็ตาม) แต่พอตัด Sum of ทิ้ง เหลือแค่คำว่า “Amount”

เอ็กเซลมันไม่ยอมอีก ฮ่วย!

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

วิธีแก้ง่ายๆคือ

ใส่ ช่องว่าง (” “) ที่ด้านหน้า หรือ ด้านหลัง ของชื่อฟิลด์

เช่น ” Amount” หรือ “Amount “

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

เท่านี้เอ็กเซลก็ยอมโดยดุษฎีแล้วครับ ^_^

.

5 แยกข้อมูลหนึ่งคอลัมน์ต่อหนึ่งฟิลด์

เมื่อเราจับชื่อฟิลด์มากกว่าหนึ่งฟิลด์ลงไปในส่วนของ Row Label แล้ว (เช่น พื้นที่การขาย กับ ชื่อลูกค้า) เจ้าพิเวิทมันชอบยัดเยียดทั้งสองฟิลด์เข้าไปอยู่ในคอลัมน์เดียวกัน (ถ้าใช้เวอร์ชันตั้งแต่ 2007 เป็นต้นไป) เช่น จับยัดเข้าคอลัมน์ A คอลัมน์เดียวเฉยเลย

แต่..เราอยากให้แยก พื้นที่การขาย อยู่คอลัมน์ A ส่วน ชื่อลูกค้า อยู่คอลัมน์ B ทำยังไงดี?

รูปแบบ “ยัดเยียด” เข้าคอลัมน์เดียว คือรูปแบบที่เรียกว่า Compact Form

ส่วนรูปแบบที่แยกหนึ่งคอลัมน์ต่อหนึ่งฟิลด์ เรียกว่า Tabular Form

วิธีการเปลี่ยนจาก Compact Form เป็น Tabular Form ก็ง่ายมากครับ

ไปที่ Design/Report Layout/Show in Tabular Form

Slide5โดยส่วนตัวแล้ว ผมแนะนำให้ใช้รูปแบบที่เป็น Compact Form มากกว่านะครับ (แม้อาจไม่ชินในตอนแรกก็ตาม) เพราะเห็นโครงสร้างของข้อมูลขัดเจน รวมทั้งอาจหุบ (Collapse) หรือขยาย (Expand) ข้อมูลได้ตามใจชอบอีกด้วย

คราวนี้ ถ้าเราต้องการให้เจ้าพิเวิทแสดงข้อมูลซ้ำ เช่น พื้นที่การขาย “ภาคเหนือ” มีลูกค้า 3 เจ้า เราต้องการให้แสดงชื่อ “ภาคเหนือ” ทั้งสามบรรทัดเลย (ปกติเอ็กเซลจะแสดง “ภาคเหนือ” แค่บรรทัดแรกบรรทัดเดียว) ทำยังไงดี?

ง่ายมากครับ แค่เลือก

Design/Report Layout/Repeat All Item Labels 

เท่านี้ก็เรียบร้อย

.

6 ปรับข้อมูลผิดพลาดให้เป็นศูนย์

สมมติเราสร้างฟิลด์คำนวณชื่อ “Price”

สูตรที่ใช้คือ

=Amount/Volume

ดูเผินๆเหมือนจะโอเค แต่วิธีการนี้อาจเกิดข้อผิดพลาด ถ้า Volume ของข้อมูลใดๆเป็นศูนย์

เจ้าพิเวิทจะขึ้นค่าว่า

#DIV/0!

เราอยากให้โชว์ค่าแบบนี้ในรีพอร์ตไหมครับ?

บางคนอาจโอเค แต่ถ้าไม่โอเค ให้ทำแบบนี้ครับ

คลิกขวา/Pivot Table Options/Layout & Format/For error value show

ใส่ค่า 0 ลงไป หรืออาจติ๊กเลือกอย่างเดียวแล้วปล่อยให้เป็นค่าว่างก็ได้ครับ (ถ้าอยากให้แสดงความผิดพลาดเป็นค่าว่าง)

Slide6.

7 ลบข้อมูลทิ้งไปแล้ว ไม่ต้องแสดงในดร็อปดาวน์อีก

เคยไหมครับ เวลาลบข้อมูลบางอย่าง (เช่น Customer Group ชื่อ Silver) ทิ้งจากตารางข้อมูลดิบแล้ว แต่เจ้าข้อมูลนั้นยังหน้าด้านโชว์อยู่ที่ดร็อปดาวน์ลิตส์อี๊ก!

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

คลิกขวา/Pivot Table Options/Data/Number of items to retain per field ให้เลือก None

Slide7

เท่านี้อะไรที่ลบทิ้งไปแล้ว ก็ไม่โผล่มากวนใจในดร็อปดาวน์ลิตส์อีกครับ ^_^

.

8 Label Filter และ Value Filter พร้อมกัน

ปกติเราสามารถฟิลเตอร์ข้อมูลในส่วน Row Label ได้ใช่ไหมครับ ไม่ว่าจะเป็น

  • Label Filter ฟิลเดอร์จากชื่อ เช่น เลือกเฉพาะโค้ดที่ลงท้ายด้วยเลข 3, โค้ดที่ขึ้นด้วยเลข 4, จังหวัดที่มีคำว่า “นคร”
  • Value Filter ฟิลเตอร์จากค่าของฟิลด์ที่เป็นตัวเลข เช่น ยอดขายมากกว่า 1,000 ผลกำไรน้อยกว่า 200

แต่.. ถ้าเราต้องการฟิลเตอร์โค้ดที่ลงท้ายด้วยเลข 3 และมียอดขายมากกว่า 1,000  หรือก็คือฟิลเตอร์ทั้งแบบ Label Filter และ Value Filter พร้อมกัน พิเวิทมันไม่ยอมน่ะสิ!

พิเวิทให้เราเลือกฟิลเตอร์ได้เพียงแบบเดียว ฮ่วย! (อีกแล้ว)

ปัญหานี้จะไม่เป็นปัญหา ถ้าเรารู้เทคนิคนี้ครับ

คลิกขวาฟิลด์ที่ต้องการกรองข้อมูล/Pivot Table Options/Totals & Filters/Allow multiple filters per field

Slide8เท่านี้ก็ฟิลเตอร์สองอย่างพร้อมกันได้แล้วครับ ^_^

.

9 ดูรีพอร์ตอย่างเดียว ห้ามปรับใดๆทั้งสิ้น

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

ทำไมต้องทำแบบนั้นล่ะ?

ขอยกตัวอย่างเป็นเคสนะครับ สมมติคุณเป็นคนสร้างรีพอร์ตจากพิเวิทเทเบิล แล้วส่งให้กับเซลล์ทุกทีมในบริษัท

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

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

จริงๆแล้วทำได้ครับ แต่ต้องมีเทคนิคเล็กน้อย วิธีการคือ

ก็อปปี้เฉพาะเวิร์คชีตที่เป็นพิเวิทเทเบิลมายังอีกไฟล์นึง (อาจใช้วิธีคลิกขวาที่ชื่อเวิร์คชีต/More or Copy…/Create a copy แล้วเลือก new book ก็ได้ครับ) ไม่ต้องเอาเวิร์คชีตที่เป็นข้อมูลดิบมานะครับ

จากนั้น คลิกขวาพิเวิทเทเบิลที่เราเพิ่งก็อปปี้มา/Pivot Table Options/Data/ติ๊ก Save source data with file ออกไป

Slide9

กดเซฟ แล้วส่งไฟล์โลด

แต่ถ้าส่งไฟล์ให้ผิดคน อันนี้ไม่รู้จะช่วยยังไงนะครับ ^__^

.

ผมเคยเขียนเทคนิคเกี่ยวกับพิเวิทเทเบิลในบทความที่ชื่อว่า 5 เคล็ด(ไม่)ลับ(แล้ว) กับ Pivot Table!! ยังมีเทคนิคอื่นๆที่ไม่ได้กล่าวในบทความนี้ ใครสนใจ คลิกอ่าน ที่นี่ ครับ

งั้นถ้าอ่านบทความนั้น แล้วก็บทความนี้ เท่านี้เราก็เป็นเทพพิเวิทแล้วสิ?

คงไม่ถึงขั้นเทพพิเวิทหรอกครับ แต่น่าจะตอบโจทย์การใช้ประมาณ 70% แล้ว

ยังมีอีกหลายเทคนิคที่น่าสนใจ ใครอยากค้นคว้าเพิ่มเติม แนะนำเว็บนี้ครับ

http://www.pivot-table.com/

งั้นถ้าอ่านบทความในเว็บนี้จบ ก็เป็นเทพพิเวิทแล้วสิ?

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

ถ้าคุณมาถึงจุดนั้นแล้ว คุณต้องก้าวอีกขั้นไปสู่ก้าวที่ชื่อว่า

Power Pivot

แล้วคุณจะพบอะไรที่น่าสนุกอีกมากมาย เช่น Create Linked Table, DAX Formula, Measure

อาจดูวุ่นวาย แต่จุดเด่นของ Power Pivot คือ คำนวณอย่างรวดเร็ว ขนาดของไฟล์เล็กลงอย่างไม่น่าเชื่อ รวมถึงความสามารถในการสร้าง Dashboard

ฟังดูก็น่าสนุกแล้วใช่ไหมครับ

ถ้าตอนนี้เรายังไปไม่ถึง Power Pivot หรือยังไม่ถึงขั้น “เทพพิเวิท” งั้นลองฝึกเทคนิคจากบทความของผม จนกลายเป็น “ผู้ช่ำชอง” พิเวิทเทเบิลกันก่อนไหมครับ ^_^

.

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^