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

เวลาออกแบบตารางเพื่อเป็นฐานข้อมูลของ Pivot Table ผมมักออกแบบให้ดูวุ่นวายเล็กน้อย แบบนี้ครับData_TemplateDesign_160206

จากภาพด้านบน จะเห็นได้ว่ามีคอลัมน์ที่เก็บตัวเลข Volume ตั้ง 4 คอลัมน์ นั่นคือ

  • Volume
  • Actual_Vol
  • Goal_Vol
  • LY_Vol

นั่นสิ! ทำไมต้องทำแบบนี้ด้วย มีประโยขน์ยังไงหรือ?

เหตุผลที่ต้องออกแบบตารางแบบนี้ เพราะผมต้องการสร้างรีพอร์ต ด้วย Pivot Table ให้ได้หน้าตาแบบนี้ครับ

PivotTable_VarianceAnalysis_160206.png

นั่นคือสร้าง Pivot Table ที่แสดงตัวเลข

  • ปีนี้ (Actual)
  • เป้าหมาย (Goal)
  • ปีที่แล้ว (LY, Last Year)
  • ความแตกต่างระหว่างปีนี้กับเป้าหมาย (vsGoal, Actual – Goal)
  • ความแตกต่างระหว่างปีนี้กับปีที่แล้ว (vsLY, Actual – LY)

เจ้า “ความแตกต่าง” (vsGoal, vsLY) เกิดจากการคำนวณในพิเวิทเทเบิล (Calculated Field) นั่นเอง!

ถ้าไม่แบ่งเป็น 3 คอลัมน์ ไม่มีทางคำนวณได้ง่ายๆแน่นอน

ทั้งนี้ยังสามารถใช้ Slicer เพื่อปรับเปลี่ยนประเภทสินค้า และเดือน ที่ต้องการให้แสดงในรีพอร์ตได้อีกด้วย

หรือออกแบบมาเพื่อสร้าง Pivot Chart ที่หน้าตาแบบนี้ครับ

PivotChart_Comparison_160206.png

มีกราฟหลายอย่างจัง ดูยังไงหรือ?

  • Actual คือกราฟแท่ง (Column Chart)
  • Goal คือกราฟพื้นที่ (Area Chart) สีเทาๆฟ้าๆด้านหลัง
  • LY คือกราฟเส้น (Line Chart) สีฟ้า

ถ้าต้องการรีพอร์ตหน้าตาเหมือนสองรูปด้านบนนี้ การจัดเก็บตารางด้วยฟอร์แมตปกติ เช่น

NormalDataFormat_160206.png

ไม่มีทางจัดทำ Pivot Table, Pivot Chart แบบนี้ได้เลย

หรือถ้าทำได้ ก็ต้องทุ่มเท “พลังงานงานอย่างมาก” ถ้ามีอะไรเปลี่ยนแปลง ก็ต้องทุ่มเท “พลังงานอย่างมาก” ใหม่อีกครั้ง

ที่สำคัญคือ ออกแบบแล้วต้องไดนามิกส์

คำว่าไดนามิกส์ แปลว่า

  • ใช้รูปแบบตารางนี้เก็บข้อมูลปีอื่นๆรวมกันได้ (ใส่ข้อมูลต่อท้ายไปเลย)
  • ไม่จำเป็นว่า Actual ต้องเป็นปี 2016 เสมอไป สามารถปรับเปลี่ยนเป็นปี 2015 หรือปีอื่นๆได้ตามต้องการ

รวมถึงยังคงใช้ความสามารถของ Column Label ได้ตามปกติ เช่น สร้างรีพอร์ตหน้าตาแบบนี้ ด้วย Pivot Chart

PivotChart_5YearTrend_160206.png

งงตรง 4 คอลัมน์อ่ะ อธิบายหน่อยสิ?

เราต้องออกแบบตารางให้ได้หน้าประมาณนี้ครับ

Data_LY_160206

จุดสังเกตมี 4 จุดด้วยกันคือ

  1. มีคอลัมน์ที่บ่งบอกว่าข้อมูลนั้นอยู่ปีอะไร จากภาพคือคอลัมน์ A
  2. มีคอลัมน์ที่บ่งบอกว่าข้อมูลนั้นเป็นข้อมูลประเภทใด เช่น เป็นข้อมูลจริง (Actual) หรือเป็นเป้าหมายที่วางเอาไว้ (Goal) จากภาพคือคอลัมน์ B
  3. แบ่งตัวเลขออกเป็น 4 ส่วน (ในที่นี้คือตัวเลข Volume)
    • ส่วนแรกคือ Volume หรือก็คือข้อมูลที่มีอยู่แล้ว (คอลัมน์ F)
    • ส่วนที่สองคือ Actual_Vol เพื่อแสดงข้อมูลเฉพาะส่วนที่เป็น Actual ที่ปีที่เราสนใจเท่านั้น เช่น ปี 2016 (คอลัมน์ G)
    • ส่วนที่สามคือ Goal_Vol เพื่อแสดงข้อมูลเฉพาะส่วนที่เป็นเป้าหมายที่วางเอาไว้ (Goal) เช่น เป้าหมายของปี 2016 (คอลัมน์ H)
    • ส่วนที่สี่คือ LY_Vol (Last Year Volume) เพื่อแสดงข้อมูลเฉพาะส่วนปีที่แล้ว เช่น ข้อมูลของปี 2015 (2016-1) (คอลัมน์ I)
  4. บ่งบอกว่าเป็นข้อมูลประเภทใด (Actual, Goal) และปีใด (2016, 2015) ในส่วนต้นของตาราง จากภาพคือ บริเวณเซลล์ G1 ถึง I2 เพื่อใช้อ้างอิงสูตร

ออกแบบตารางเช่นนี้แล้ว เขียนสูตรคอลัมน์ Actual_Vol ให้หน้าตาประมาณนี้ครับ

=IF(AND($A6=G$1,$B6=G$2),$F6,0)

Actual_Formula_160206.png

เพื่อกำหนดว่า ให้ดึงข้อมูลเฉพาะปี 2016 และ Category = Actual เท่านั้น

ส่วนคอลัมน์ Goal_Vol เขียนสูตรให้ได้หน้าตาประมาณนี้ครับ

=IF(AND($A6=H$1,$B6=H$2),$F6,0)

Goal_Formula_160206.png

เพื่อกำหนดว่า ให้ดึงข้อมูลเฉพาะปี 2016 และ Category = Goal เท่านั้น

ส่วนคอลัมน์ LY_Vol เขียนสูตรให้ได้หน้าตาประมาณนี้ครับ

=IF(AND($A6=I$1,$B6=I$2),$F6,0)

LY_Formula_160206.png

เพื่อกำหนดว่า ให้ดึงข้อมูลเฉพาะปี 2015 และ Category = Actual เท่านั้น

จริงๆแล้วถ้าใช้ดอลลาร์ไซน์ร่วมในการเขียนสูตร เขียนทีเดียวแล้วลากไปทางขวาได้เลยครับ ^^

เมื่อเขียนสูตรเสร็จแล้ว นำข้อมูลทั้งหมดมาใส่ลงไปในคอลัมน์ A-F

(Column G, H, I เป็นสูตรนะครับ ไม่ต้องแปะข้อมูลลงไป แค่ลากสูตรจนถึงบรรทัดสุดท้ายก็พอ)

ถ้าเขียนสูตรถูก ข้อมูลปี 2016, Category = Actual จะได้หน้าตาแบบนี้ครับ

Data_Actual_160206.png

นั่นคือ มีตัวเลขขึ้นมาเฉพาะคอลัมน์ที่เป็น Actual_Vol เท่านั้น ส่วนคอลัมน์ Goal_Vol, LY_Vol มีค่าเป็นศูนย์

ข้อมูลปี 2016, Category = Goal จะได้หน้าตาแบบนี้ครับ

Data_Goal_160206

นั่นคือ มีตัวเลขขึ้นมาเฉพาะคอลัมน์ที่เป็น Goal_Vol เท่านั้น ส่วนคอลัมน์ Actual_Vol, LY_Vol มีค่าเป็นศูนย์

ทั้งนี้เราสามารถนำข้อมูลปีอื่นๆมาใส่ในฐานข้อมูลเดียวกันเลย ไม่จำเป็นต้องแปลงหน้าตาแต่อย่างใด

เช่น นำข้อมูลปี 2014 มาใส่ต่อท้ายให้ได้หน้าตาแบบนี้ครับ

Data_2014_160206

จะเห็นได้ว่าข้อมูลในคอลัมน์ Actual_Vol, Goal, LY_Vol มีค่าเป็นศูนย์ 

ซึ่งถูกต้องแล้ว เพราะในที่นี้เราตั้งให้ Actual = 2016

Goal ก็คือเป้าหมายของปี 2016

LY (Last Year) เท่ากับ 2015 (2016-1)

ดังนั้นข้อมูลปี 2014 จึงไม่เกี่ยวข้องกับ 3 คอลัมน์นั้นเลย

แล้วจะใส่ข้อมูลปี 2014 ลงไปทำไมล่ะ?

ถ้าเราต้องการสร้างรีพอร์ตด้วย Pivot Table ที่แสดงข้อมูลย้อนหลัง

เช่น รีพอร์ตที่แสดงข้อมูลย้อนหลัง 5 ปีแบบนี้

PivotTable_5YearByProduct_160206.png

เราจำเป็นต้องใส่ข้อมูลปี 2014 รวมถึงปีอื่นๆ เช่น

  • 2013
  • 2012

ต่อท้ายลงไปในตารางด้วยครับ

ตัวเลข Actual_Vol, Goal_Vol, LY_Vol (คอลัมน์ G, H, I) ของข้อมูลเหล่านี้จะเป็นศูนย์ก็ไม่เป็นไร เพราะเรานำข้อมูลในคอลัมน์ Volume (คอลัมน์ F) มาใช้ครับ

หรือเราสามารถสร้างรีพอร์ตด้วย Pivot Chart ที่แสดงข้อมูลย้อนหลัง 5 ปี แบบนี้ครับ

PivotChart_5YearTrend_160206.png

หรือสร้างรีพอร์ตด้วย Pivot Chart ให้เป็นกราฟเทอร์โมมิเตอร์แบบนี้ครับ

Thermometer Chart

แล้วถ้าต้องการเซ็ตให้ Actual = 2015, LY = 2014 ล่ะ ทำยังไงดี?

ง่ายมากครับ แค่เปลี่ยนตัวแปรด้านบนจาก 2016 เป็น 2015 แบบนี้ครับ

Data_2015Comparison_160602.png

จากนั้นคลิกขวาบนพิเวิทเทเบิลใดๆ แล้วกด Refresh

เท่านี้ข้อมูล Actual ในทุกๆ Pivot Table และ Pivot Chart ก็จะเปลี่ยนเป็น 2015 แล้วครับ ^_^

อ้อ! LY ก็จะเปลี่ยนเป็น 2014 ด้วยนะครับ ^_^

.

อย่างไรก็ตาม สำหรับเคสนี้ ถ้าเราสร้างพิเวิทเทเบิล หรือพิเวิทชาร์ตด้วย Power Pivot แล้ว

เราไม่ต้องสร้างคอลัมน Actual_Vol, Goal_Vol, LY_Vol (คอลัมน์ G, H, I) ลงไปในฐานข้อมูลโดยตรงก็ได้

แต่ไปสร้างใน Power Pivot ด้วย DAX Formula

หรือสร้างฟิลด์คำนวณ (Measure) ขึ้นมาแทนครับ

.

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

ใครสนใจ สามารถดาวน์โหลดไฟล์ตัวอย่างได้เลยครับ ทุกอย่างที่ผมเขียนในบทความนี้ อยู่ในไฟล์นี้ทั้งหมดครับ

Actual_Goal_LY_Data_PivotTable_PivotChart_160206

.

ผมเองเพิ่งค้นพบเทคนิคเหล่านี้เมื่อไม่นานนี้ อาจดูเพิ่มขั้นตอนเล็กน้อยนะครับ แต่รับรองว่าไม่ยาก

ถ้าคุณใช้มันจนช่ำชองแล้วล่ะก็ คุณจะคิดเหมือนผมว่า

เสียดาย..น่าจะรู้ตั้งนานแล้ว!!

.

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

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

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

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

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

  1. ซับซ้อน ต้องขอใช้เวลาแบบตอนมีสติเต็มร้อย มาอ่านทบทวนอีกรอบ ขอบคุณมาก ๆครับ

      1. อาจารย์มีคำปรึกษาดังนี้ ผมทำกราฟใน pivot table ที่เป็น 4 Chart (ใน power pivot) จากนั้น copy 4 chart จาก 4 เป็น 12 หลังจากนั้นทำ chart เพื่อแสดงข้อมูล BMI ของพนักงานทั้ง 12 คน ตัวอย่างผลลัพท์คือเปลี่ยนข้อมูลคนที่ 1 แล้วคนที่ 3 ก็เปลี่ยนตามแต่คนที่ 2 ไม่เปลี่ยน(ปกติ) แก้ไขอย่างไรครับ รบกวนอาจารย์ตอบกลับ หรือส่งมาที่ th.nirut@gmail.com

  2. ขอบคุณมากนะครับ

    ผมขอถามหน่อยครับว่า ในไฟล์ที่พี่ให้มาในชีท “กราฟเทอร์โมมิเตอร์”
    – ถ้าเราเลือกปี 2012 2013 2014 (ไม่มี goal) แล้วกลับมาเลือกปี 2015 2016
    มันจะไม่คงสภาพ “กราฟเทอร์โมมิเตอร์” แบบนี้ต้องทำยังไงครับ
    – อีกคำถามคือช่อง Preserve cell formatting on update (อยู่ใต้ Autofit column widths on update) ใช้ทำอะไรครับ

    ผมชอบบทความพี่มากๆเลยครับ ทำมาเรื่อยๆนะครับ จะคอยติดตาม ^^

    1. การที่ไม่คงสภาพกราฟเทอร์โมมิเตอร์เนืองจาก อาจคลิกเลือกข้อมูลที่ไม่มีตัวเลข กราฟจะแสดงว่าไม่มีข้อมูล แต่พอคลิกข้อมูลอื่นที่มีตัวเลข กราฟจะไม่สามารถคงความเป็นเทอร์โมมิเตอร์ได้
      นี่คือข้อจำกัดของกราฟที่สร้างจาก Pivot Chart ครับ แต่ถ้าสร้างกราฟธรรมดา (ไม่ใช่ Pivot Chart) จะไม่พบปัญหานี้ครับ

    2. สำหรับเรื่อง Preserve cell… คือการคงสภาพฟอร์แมตที่เราปรับไว้ให้เป็นแบบนี้ ถ้าอัพเดตข้อมูล หรือคลิกเลือกข้อมูลอื่นๆ ฟอร์แมตจะไม่เปลี่ยนครับ

      ปกติออปชั่นนี้จะถูกเลือกไว้เสมอครับ (Default) ถ้าไม่เลือกไว้ ฟอร์มแมตที่เราปรับเอาไว้ (เช่น รูปแบบตัวเลข, สี) จะเปลี่ยนไปทันทีที่เกิดการอัพเดตครับ

  3. อยากรู้ pivot table
    การที่ ดึงข้อมูลตัวเลขยอดขาย เป็นรายเดือน
    แต่พอลากมา มันชอบ ขึ้น count. ตลอด แล้วต้องมานั่งเปลี่ยนเป็น. Sum. ทุกครั้ง
    มีเทคนิค ให้มันดีฟ้อ เป็น sum ได้มะคะ
    รบกวนขอคำแนะนำด้วยนะคะ
    ขอบคุณมากๆ นะคะ

  4. สอบถามอาจารย์ใน Sheet Variance Anlysis ครับ Row ที่ขึ้นเป็นสีลักษณะคล้ายว่ามี Chart อยู่ใน Cell อาจารย์พอจะแนะนำวิธีในส่วนนี้ได้ไหมครับ

  5. ทำยังไง เมื่อตอนดับคลิกเลือกรายการที่ดู แล้วให้มันวิ่งไปโชว์ที่ชีตข้อมูลหลักคะ และลบข้อมูลที่โชว์ได้ โดยข้อมูลหลักยังคงอยู่

    ***มือสมัครเล่นคะ

  6. ได้ความรู้มากไว้มาเรียนรู้ขอบคุณค่ะ

  7. ยอดเยี่ยมมากครับ

    ขอถามนึดนึงครับ Slicer สามารถเลือกได้มากว่า 1 หรือไม่ครับ
    เช่น เลือกภาคเหนือ และภาคใต้ เพราะต้องดูข้อมูลแค่ 2 ภาค
    ผมทดลองทำแล้ว ให้เลือกแค่ทีล่ะ 1 ภาคครับ

  8. สุดยอดค่ะ ได้ความรู้มากและสามารถนำไปประยุกต์ใช้งานได้ รบกวนถาม Slicer แนวนอนทำยังค่ะ

  9. ขอสอบถามค่ะ ถ้าเราจะทำ condition format เลือก Use a formula… ใน Pivot table ดังนี้
    1. ยอดขายเฉลี่ยต่อวันคือ 3.33 % ถ้าวันที่1ขายได้ 3.00% ให้ไฮไลท์เป็นตัวสีแดง วันที่ 2 เป้าจะเพิ่มเป็น 6.66% แล้วถ้าวันที่ 1และ 2 ขายได้ 8.5 % ให้ไฮไลท์เป็นสีเขียว สะสมไปเรื่อยๆจนถึงสินเดือน ต้องใช้สูตรแบบไหนคะ

  10. เทพมากครับ กำลังหาตัวช่วยพอดีเลย ขอบคุณมากครับ

  11. ข้อมูลเป็นประโยชน์มากครับ ขอสนับสนุนนะครับ

Leave a Reply to วิศวกรรีพอร์ต Cancel reply

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