5 เคล็ด(ไม่)ลับ(แล้ว) กับ Pivot Table!

คุณเคยหงุดหงิดเพราะเจ้า Pivot Table ไหมครับ?

ผมคนนึงล่ะที่เคยหงุดหงิดกับมันแบบสุดๆ ทำอะไรก็จุกจิก ติดขัดนู่นนี่นั่นไปหมด

จะเอาแบบนี้ ดั๊นเป็นอีกแบบนึงตลอด

โค-ตะ-ระ เซ็ง….

เกิดเป็นความแค้นฝังหุ่น ชาตินี้ไม่ต้องพบเจอกัน ลาขาด

ผมเลยกลายเป็นคนใช้ pivot table ไม่เป็น

และบอกทุกคนว่า pivot table มีข้อเสียนู่นนั่นนี่ บลา บลา บลา…

24209136_s.jpg

เพื่อนร่วมงานของผมคนหนึ่ง ชอบใช้ pivot table มาก เค้าใช้มันแทบจะทุกงาน

พอแอบดูไฟล์ของเค้า ไม่เห็นติดตัดปัญหาเหมือนที่ผมเจอเลย

เฮ้ย!! มันเป็นไปได้ยังไง!

ผมจึงถามเค้าว่าเจอปัญหาพวกนี้ไหม?

จึงได้ถึงบางอ๋อ (ถัดจากบางอ้อไปหนึ่งเพลิน ^^) ว่ามันมีเคล็ดลับ!

ถ้ารู้เคล็ดลับเหล่านี้แล้วไซร้ คุณจะรัก pivot table ขึ้นมาทันทีเลยล่ะครับ ^__^

1. สร้างคอลัมน์คำนวณอัตโนมัติใน pivot table

ถ้าต้องการคิดราคาสินค้าเป็นรายกลุ่มใน pivot table ปกติเราทำกันยังไงครับ?

คงไม่สามารถนำราคาของสินค้าแต่ละตัวในกลุ่มมาบวกกันใช่ไหมครับ

ไม่งั้นข้อมูลออกทะเลอันดามันแน่นอน

วิธีที่ถูกต้องคือ นำยอดขายรวม หารด้วยปริมาณรวมของสินค้ากลุ่มนั้นๆ

ซึ่งไม่สามารถทำได้ ถ้าไม่รู้วิธีสร้างสูตรคำนวณ (Calculated Field) ใน pivot table

เราสามารถสร้างสูตรคำนวณใน pivot table ได้ง่ายมากครับ

เลือกที่แท็ป Options คลิกปุ่ม Fields, Items & Sets แล้วเลือก Calculated Field

จากนั้นก็ใส่สูตรตามที่ต้องการ

เวลาใส่สูตร ให้มองเสมือนว่าชื่อฟิลด์ (Field) ก็คือชื่อหัวคอลัมน์ในตารางข้อมูลดิบนั่นเอง

ลองดูนะครับ แล้วคุณจะรักเจ้า pivot table ขึ้นมาทันใด ^__^

slide51

.

2. ปรับความกว้างของคอลัมน์ให้ได้ขนาดนี้ทุกครั้ง

เคยไหมครับ เวลาปรับความกว้างของแต่ละคอลัมน์ใน pivot table ให้พอดีเป๊ะแล้ว

พอกดรีเฟรชทีไร ความกว้างที่ปรับไว้ ต้องหดหรือขยายเหมือนก่อนปรับทุกทีสิน่า

ฮ่วย!

วิธีแก้ ง่ายนิดเดียวครับ

คลิกขวาที่ตรงไหนของ pivot table ก็ได้ แล้วเลือก Pivot Table Options

FitColumnSize_Step1_PivotTable_150521

จากนั้นติ๊กเครื่องหมายตรง Autofit column width on update ออกไป

FitColumnSize_Step2_PivotTable_150521

ปรับครั้งเดียว และไม่ต้องปรับอีกเลย

แต่ต้องปรับแแบบนี้กับทุกตารางนะครับ ถ้ามี 3 pivot table ก็ต้องทำแบบนี้ 3 ครั้ง

เท่านี้ปัญหาคอลัมน์หดขยายก็จะไม่มารบกวนคุณอีกแน่นอนครับ

slide23

.

3. เรียงลำดับข้อมูลจากน้อยไปมาก (มากไปน้อย)

เคยหงุดหงิดไหมครับว่า pivot table มันเรียงลำดับข้อมูลไม่ได้เรื่องเลย

ไม่รู้เรียงภาษาอะไร ไม่เคยได้ดั่งใจซักครั้ง

ปัญหานี้จะหมดไป ถ้าสั่งให้มันเรียงข้อมูล (sort) ตามเงื่อนไขที่เราต้องการครับ

เช่น ถ้าต้องการให้เรียงลำดับประเทศ (Country) จากประเทศที่มียอดขายสูง ไปยอดขายต่ำ

คลิกที่ลูกศรตรงคอลัมน์ประเทศ แล้วเลือก More Sort Options

SortOption1_PivotTable_150521

จากนั้น เลือกได้เลยครับว่า อยากให้เรียงลำดับด้วยเงื่อนไขอะไร เช่น

  • ยอดขาย จากมากไปหาน้อย
  • ยอดขาย จากน้อยไปหามาก
  • ลำดับตัวอักษรตัวแรกของประเทศ จาก A – Z
  • ลำดับตัวอักษรตัวแรกของประเทศ จาก Z – A

SortOption2_PivotTable_150521

slide24

.

4. จัดอันดับลูกค้า Top 10

ส่งข้อมูลลูกค้า Top 10 มาให้ผมหน่อย!!!

สั่งง๊ายง่ายเนาะ แต่ทำมันจะง่ายหรือเปล่าเนี่ย…

ง่ายมากครับ! ถ้าเรารู้จักการใช้ฟิลเตอร์ใน pivot table

วิธีการคือสร้าง pivot table ขึ้นมาก่อน โดยมีคอลัมน์หนึ่งเป็นชื่อลูกค้า

คลิกที่ลูกศรตรงคอลัมน์ชื่อลูกค้า แล้วเลือก Value Filters เลือก Top 10

Top10_Step1_PivotTable_150521

สามารถเลือกได้เลยครับว่าจะเรียงลำดับ Top 10 ด้วยปริมาณ ราคา หรือยอดขาย

ตามสั่งเลย ว่างั้น

หรือจะแถมให้เป็น Top 20 ก็ได้นะครับ Pivot Table มีความสามารถรองรับอยู่แล้ว

หรือแถม Bottom 10 ให้อีกก็ยังได้ จัดเต็มไปเลย จะได้ไม่ต้องสั่งซ้ำ ^_^

Top10_Step2_PivotTable_150521

slide26

.

5. เอาฟังก์ชัน GETPIVOTDATA ออกไป!!

เวลาดึงข้อมูลจาก pivot table เพื่อไปทำอะไรบางอย่าง เราจะเจอกับเจ้าฟังก์ชันที่ไม่ได้รับเชิญตัวนี้ครับ

GETPIVOTDATA(…………………………………….)

มันมาเสมอ โดยเราที่ไม่เคยร้องขอ

แถมสูตรยาวเฟื้อย อ่านไม่รู้เรื่อง

มีวิธีเอามันออกไหม?

มีครับ คลิกที่ Tab Options จากนั้นคลิกที่ Options

แล้วติ๊กเอาเจ้า Generate GetPivotData ออกไปครับ

ทำครั้งเดียวก็พอครับ คอมพ์เราจะจำแบบนี้ไปตลอด

ทำแค่ครั้งเดียว ใช้ได้กับทุกไฟล์

เท่านี้เจ้าฟังก์ชัน GETPIVOTDATA ก็จะไม่มารังความอีกต่อไป ^__^

slide36

อย่างไรก็ตาม จริงๆแล้วเจ้าฟังก์ชัน GETPIVOTDATA มีประโยชน์นะครับ

แต่ในทางปฏิบัติ เรามักไม่ต้องการแบบนั้น ก็เอามันออกซะ

แล้วมันก็จะหายไปจากชีวิตครับ

..

อันที่จริงเทคนิคเหล่านี้เรียกว่าเป็นเคล็ดลับคงไม่ได้แล้ว หลายคนอาจคุ้นเคยกับมันเป็นอย่างดี

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

เคล็ด(ไม่)ลับ ของ pivot table ยังไม่หมดแค่นี้นะครับ

ถ้าสนใจ อ่านได้จากบทความเหล่านี้ครับ

9 สิ่งควร(ต้อง)รู้ กับ Pivot Table

10 เทคนิค Pivot Table ที่คุณอาจไม่เคยรู้มาก่อน

เทคนิค Pivot Table ที่คุณไม่รู้ ไม่ได้แล้ว!

สิ่งเล็กๆที่ละไว้ในฐานที่(ไม่)เข้าใจใน Pivot Table

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

ขอให้สนุกกับ pivot table ทุกคนครับ ^_^

.

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

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

วิศวกรรีพอร์ต

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

25 thoughts on “5 เคล็ด(ไม่)ลับ(แล้ว) กับ Pivot Table!

  1. มีคำถามค่ะ
    1) ไม่ทราบว่า การเอาfunction Get pivot data ออกไปใช้กรณีไหนค่ะ หมายความว่า เรา convert ค่าให้จากฟังก์ชั่นเป็น value หรือเปล่าค่ะ
    2) และกรณีsort หากมี pivot เป็น row value 3 ตัว ตัวแรกเป็นชื่อประเทศ , ตัวที่สองเป็น supplier ID ตัวที่สามเป็น supplier name และมียอด spend อยู่ใน ∑value กรณีที่ ต้องการเรียงประเทศตามอักษร A-Z ต่อมาต้องการให้เรียง top 10 supplier ตามยอด spend ต้องไปคลิกลูกศรที่ header ไหนค่ะ

  2. ขอบคุณมากครับ สำหรับเคล็ดลับดีดีครับ

  3. บทความน่าสนใจ และให้ความรู้ที่สามารถนำไป apply ได้เป็นอย่างดี ขอบคุณมากนะค่ะ

  4. ตัวเลขที่มี , อย่างเช่น 1,000 เวลากดรีเฟรสแล้ว มันกลับมาเป็น 1000 ค่ะ แก้ตรงไหนค่ะ

  5. ขอสอบถามครับ กรณีที่เราใส่ Filter Top10 และนำไปทำเป็นกราฟ หากไม่กรองใดๆ ใน Slicer ยังคงแสดง Top10 แต่เมื่อมีการกรองข้อมูลใดๆ จาก Slicer ปรากฎว่า ข้อมูลมาทั้งหมด ไม่ทราบว่าจะต้องแก้ไขปัญหานี้อย่างไรครับ

    1. ถ้าเป็นการกรองข้อมูลคนละประเภทกัน ให้เลือกออปชัน allow multiple filters per field จะทำได้
      แต่ถ้าเป็นข้อมูลประเภทเดียวกัน ต้องใช้ Power Pivot ครับ

      1. ต้องขอขอบคุณมากนะครับ ท่าทางจะต้องใช้ Power Pivot แต่ไม่มีความรู้ใดๆ เลย

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.