คุณคิดว่า Pivot Table มีข้อจำกัดไหมครับ?

ในมุมมองของผมแล้ว ข้อจำกัดหนึ่งของ Pivot Table คือ

ไม่สามารถจัดเรียงข้อมูลในรูปแบบที่ต้องการได้ 100% 

แม้ว่า Pivot Table เวอร์ชันใหม่จะมีออปชั่น (More Sort Options) ให้เลือกการจัดเรียงแบบ Ascending หรือ Descending ก็ตาม แต่ก็ยังมีข้อจำกัดบางประเภทอยู่ เช่น ถ้าข้อมูลดิบที่ให้มามีค่า

a, b, c, d

แต่เราต้องการทำตารางสรุปให้แสดงค่า

a, b, c, d, e

โดยแสดงว่ามีค่า e ในตารางสรุป แต่ให้แสดงข้อมูลเป็นศูนย์

จะเห็นว่าค่า e ไม่มีในข้อมูลดิบที่ให้มาใช่ไหมครับ?

ถ้าค่า e ไม่มีในข้อมูล การทำให้ค่า e ปรากฏในตารางสรุป Pivot Table ไม่สามารถทำได้ครับ มันจะแสดงผลเฉพาะค่าที่มีก็คือ a, b, c, d เท่านั้น

ทำไม Pivot Table ถึงทำไม่ได้ล่ะ?

เพราะว่าในสารบบของ Pivot Table ไม่มีค่า e อยู่ไงครับ Pivot Table จะไม่มีทางรู้เลยว่าเราต้องการให้แสดงค่า e อยู่ในตารางสรุป เรียกได้ว่า Pivot Tale ไม่รู้จักค่า e เลยด้วยซ้ำ

ถ้าเราใช้ข้อมูลจาก Pivot Table นี้เป็นตารางอ้างอิงสำหรับอีกตารางหนึ่ง ลำดับและขอบเขตของข้อมูลมีโอกาสผิดเพี้ยนสูง การเขียนสูตรหรือโค้ดในอีกตารางหนึ่งเพื่อดึงข้อมูลจาก Pivot Table นี้ก็จะยากขึ้น อาจถึงขั้นปวดตับได้ นี่จึงเป็นสาเหตุที่ผมมักบอกเสมอว่า

ให้ทำ Pivot Table เป็นรีพอร์ตสุดท้าย อย่าใช้เพื่อเป็นตารางอ้างอิงสำหรับอีกตารางหนึ่ง

จริงๆแล้วอาจมีวิธีแก้ง่ายๆ คือ เพิ่มค่า e เข้าไปในข้อมูลดิบเลย ใส่ให้มีค่าเท่ากับศูนย์ และขยายขอบเขตข้อมูลของ Pivot Table ให้ครอบคลุมค่า e ที่เพิ่มขึ้น จากนั้นรีเฟรช Pivot Table อีกครั้ง

ก็มีวิธีแก้อยู่แล้วไม่ใช่เหรอ?

ถ้าตารางนี้ไม่มีการอัพเดทอีกเลยในอนาคต วิธีนี้สามารถแก้ไขได้ครับ แต่ในความเป็นจริงแล้วข้อมูลมักอัพเดทน่ะสิ

ถ้าเราใช้วิธีนี้ เราก็ต้องทำแบบนี้ทุกครั้งซึ่งคงทำให้เราเซ็งจิตพอควร วันดีคืนดีดันลืมขึ้นมา ตารางสรุปก็จะผิดพลาด โดนด่าอีกต่างหาก มาทำเองไหมล่ะเฟ๊ย!!

งั้นต้องทำยังไงล่ะ???

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

a, b, c, d, e

แล้วใช้ SUMIFS หรือ SUM Array ดึงข้อมูลจากข้อมูลดิบที่มีมาในตารางสรุปครับ

กรณีนี้ส่วนใหญ่จะเป็นการสรุปข้อมูลที่มีเงื่อนไขมากกว่าหนึ่ง ดังนั้นเราไม่สามารถใช้ SUMIF ได้ (สรุปข้อมูลได้เพียงเงื่อนไขเดียว)

สมมติว่าเราต้องการสรุปข้อมูลโดยใช้สองเงื่อนไข นั้นคือ

Product (a, b, c, d, e)

และ

Month (Jan, Feb, Mar, …, Dec)

ถ้าใช้ SUMIFS (ไม่ใช่ SUMIF นะครับ) จะเขียนสูตรว่า

=SUMIFS(Volume, Product, Product Criteria, Month, Month Criteria)

ถ้าใช้ Sum Array จะเขียนสูตรว่า

{=SUM((Product=Product Criteria)*(Month=Month Criteria)*Volume)}

วงเล็บปีกกาในสูตรไม่ได้เกิดจากการพิมพ์นะครับ เกิดจากการกด Ctrl+Alt+Enter แทนที่จะกด Enter ธรรมดาครับ

ใครถนัดใช้ SUM Array สามารถใช้แทน SUMIFS ได้อย่างสมบูรณ์นะครับ (แต่ SUMIFS ไม่สามารถแทน SUM Array ได้อย่างสมบูรณ์) จริงๆแล้วทั้งสองสูตรต่างมีข้อดีและข้อด้อยในตัวเอง ถ้ามีโอกาสจะมารีวิวให้อ่านกันนะครับ

สำหรับบางคนที่ถนัดการใช้ SUMPRODUCT ในรูปแบบของ

=SUMPRODUCT((Product=Product Criteria)*(Month=Month Criteria)*Volume)

ก็สามารถใช้แทนได้นะครับ ขึ้นอยู่กับว่าคุณถนัดการเขียนสูตรแบบไหน

เมื่อเราสร้างตารางสรุปที่ต้องการขึ้นมาเองแล้ว (โดยไม่ได้สร้างจาก Pivot Table) หากเรานำข้อมูลในตารางนี้ไปใช้อ้างอิงกับตารางอื่นก็สามารถทำได้สบายบรื๋อ

งั้นก็ใช้วิธีนี้แทน Pivot Table ไปเลยสิ ไม่ต้องใช้ Pivot Table แล้ว!

ในทางปฏิบัติแล้ว วิธีนี้ใช้เวลามากกว่า Pivot Table พอสมควร ผมแนะนำว่าเก็บไว้ใช้กรณีที่ติดปัญหาจริงๆดีกว่าครับ

การสร้างตารางแบบนี้ขึ้นมา เราต้องสร้างและเขียนสูตรเองทั้งหมดนะครับ ซึ่งแตกต่างจากการใช้ Pivot Table ที่สามารถดึงข้อมูลที่ต้องการมาได้เลย บางคนทำคล่องๆ ใช้เวลา 2 นาทีก็เสร็จแล้ว

งั้นมีวิธีอื่นอีกไหม?

โดยส่วนตัวผมยังมองว่าในอนาคต Power Pivot จะตอบโจทย์ตรงนี้ครับ (หรืออาจตอบโจทย์ไปแล้วก็ได้ ผมเองก็ตามอัพเดทไม่ทันเหมือนกัน แหะๆ) เพราะหลักการของ Power Pivot ก็คือการสร้างคิวรี่ (Query) นั่นเอง ซึ่งเป็นหลักการของการจัดการฐานข้อมูล เพียงแต่วิธีการอาจดูยุ่งยากกว่า และต้องสร้างตารางมาสเตอร์ดาต้า

ช่วงนี้ผมกำลังเล่น Power Pivot อยู่ ถ้าเจออะไรเด็ดๆ จะหยิบมาเล่าให้ฟังนะครับ

อย่างไรก็ตาม อย่าเพิ่งด่วนสรุปว่าปัญหาที่คุณกำลังเจอใน Pivot Table คือข้อจำกัดของมันทั้งหมดนะครับ ผมเองก็เคยคิดแบบนั้นมาก่อน แต่พอมาศึกษาอย่างจริงจังและลองใช้ทุกๆออปชันที่มี ก็ทำให้เข้าใจว่าปัญหาส่วนใหญ่แล้วสามารถแก้ไขได้ครับ

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

Pivot Table เป็นเรื่องง่าย เพียงแต่ต้องทำความเข้าใจเล็กน้อยครับ ^_^

.

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

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