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

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

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

• Volume
• Actual_Vol
• Goal_Vol
• LY_Vol

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

จุดสังเกตมี 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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 2013
• 2012

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

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

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

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

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

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

จากนั้นคลิกขวาบนพิเวิทเทเบิลใดๆ แล้วกด 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 เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

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