เวลาออกแบบตารางเพื่อเป็นฐานข้อมูลของ 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 จุดด้วยกันคือ
- มีคอลัมน์ที่บ่งบอกว่าข้อมูลนั้นอยู่ปีอะไร จากภาพคือคอลัมน์ A
- มีคอลัมน์ที่บ่งบอกว่าข้อมูลนั้นเป็นข้อมูลประเภทใด เช่น เป็นข้อมูลจริง (Actual) หรือเป็นเป้าหมายที่วางเอาไว้ (Goal) จากภาพคือคอลัมน์ B
- แบ่งตัวเลขออกเป็น 4 ส่วน (ในที่นี้คือตัวเลข Volume)
- ส่วนแรกคือ Volume หรือก็คือข้อมูลที่มีอยู่แล้ว (คอลัมน์ F)
- ส่วนที่สองคือ Actual_Vol เพื่อแสดงข้อมูลเฉพาะส่วนที่เป็น Actual ที่ปีที่เราสนใจเท่านั้น เช่น ปี 2016 (คอลัมน์ G)
- ส่วนที่สามคือ Goal_Vol เพื่อแสดงข้อมูลเฉพาะส่วนที่เป็นเป้าหมายที่วางเอาไว้ (Goal) เช่น เป้าหมายของปี 2016 (คอลัมน์ H)
- ส่วนที่สี่คือ LY_Vol (Last Year Volume) เพื่อแสดงข้อมูลเฉพาะส่วนปีที่แล้ว เช่น ข้อมูลของปี 2015 (2016-1) (คอลัมน์ I)
- บ่งบอกว่าเป็นข้อมูลประเภทใด (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 เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^