"จะนำตัวเลขในตาราง Pivot ไปใช้สร้างรายงานนำเสนอหัวหน้าต่อได้อย่างไร"
เป็นปัญหาโลกแตกที่ผู้ใช้ Pivot Table ต้องคิดต่อเมื่อสร้างตาราง Pivot ขึ้นมาแล้ว บางคนหาทางใช้สูตร VLookup บ้าง Match Index บ้างเพื่อดึงค่ามาใช้ แต่สูตรเหล่านี้จะทำงานไม่ได้ทันทีเมื่อโครงสร้างตาราง Pivot ต่างไปจากเดิม เช่น เมื่อสั่ง Refresh หรือมีการโยกย้ายตำแหน่งของชื่อหัวตารางจากแนวนอนไปเป็นแนวตั้ง
ทางออกที่หลายคนใช้กันก็มักใช้วิธี Copy ไป Paste แบบ Values เพื่อทำให้โครงสร้างคงเดิมตลอดก่อนแล้วจึงค่อยใช้สูตรดึงค่าไปใช้ต่ออีกที
บทความนี้จะสมมติว่า ไหนๆคุณก็อุตส่าห์สร้างตาราง Pivot ขึ้นมาแล้ว จะนำตัวเลขในตารางไปสร้างรายงานต่ออย่างไรดี โดยถือว่าคุณไม่เคยรู้จักสูตร SumProduct หรือสูตร Array และไม่ได้คิดจะใช้ก็แล้วกัน ซึ่งถ้าใครใช้สูตร SumProduct เป็นก็ไม่ต้องเสียเวลาสร้างตาราง Pivot ขึ้นมาก่อนหรอก เพราะสามารถหาค่าจากตารางฐานข้อมูลมาสรุปให้หัวหน้าได้โดยตรง โดยจะออกแบบตารางรายงานให้มีหน้าตาตามใจหัวหน้าแบบใดก็ได้
ก่อนอื่นสูตรที่จะดึงค่าจากตาราง Pivot ได้นั้นไม่ใช่สูตร PLookup หรอก สูตรชื่อนี้ยังไม่มีในโลกแต่ให้ใช้สูตรชื่อ GetPivotData
สูตร GetPivotData เป็นสูตรที่มีความอัจฉริยะในตัวเอง เพียงพิมพ์เครื่องหมายเท่ากับแล้วคลิกลงไปที่ตัวเลขในตาราง Pivot ก็จะเกิดสูตรนี้ขึ้นมาให้เอง โดยคุณไม่ต้องเสียเวลาพิมพ์ชื่อสูตรหรือใส่อะไรในวงเล็บเองแต่อย่างใด เช่น
=GETPIVOTDATA("Sales", $A$4, "Month", "March", "Product", "Produce", "Salesperson", "Buchanan")
สูตรนี้กำลังหายอดรวมของ Sales ในตาราง Pivot ซึ่งมีหัวมุมตารางเริ่มที่เซลล์ A4 โดยเป็นยอดรวมแยกประเภทของเดือน March ของสินค้าชื่อ Produce ซึ่งพนักงานขายชื่อ Buchanan
ผู้ใช้ Pivot Table เป็นประจำน่าจะเคยเจอสูตรนี้มาก่อน ทราบแต่ว่าเป็นสูตรที่ช่วยดึงตัวเลขจากตาราง Pivot มาใช้ต่อแบบตายตัว แต่อาจไม่ทราบว่าจะดัดแปลงสูตรนี้ให้หาค่าอื่นแบบยืดหยุ่นได้อย่างไร บางคนเห็นว่าสูตรนี้เกะกะจึงสั่งยกเลิกการสร้างสูตรนี้ไปอย่างน่าเสียดาย
แทนที่จะปล่อยให้สูตรหาค่าเดิมไปตลอด ให้แก้ไขค่าคงที่ในวงเล็บให้ลิงก์ค่าที่ต้องการหามาจากเซลล์อื่นๆแทน สูตรนี้ก็จะหายอดอื่นๆได้ด้วยแล้วแต่จะใส่ค่าอะไรลงไปในเซลล์ที่ลิงก์มา
=GETPIVOTDATA("Sales", $A$4, "Month", "March", "Product", "Produce", "Salesperson", "Buchanan")
แก้เป็น
=GETPIVOTDATA("Sales", $A$4, "Month", B10, "Product", C10, "Salesperson", D10)
เมื่อคุณกรอกชื่อเดือน ชื่อสินค้า และชื่อพนักงานขายในเซลล์ B10, C10, และ D10 เปลี่ยนไปก็จะทำให้สูตร GetPivotData หาคำตอบตามเงื่อนไขเหล่านั้นมาให้เสมอ และยังคงหายอดรวมที่ต้องการได้ตลอดแม้มีการปรับเปลี่ยนโครงสร้างของตาราง Pivot ก็ตาม
พอคุณดัดแปลงสูตรนี้เป็นควรเลิกใส่ใจกับการปรับแต่งตาราง Pivot ให้สวยงาม อย่ามัวเสียเวลาปรับแต่งโครงสร้างตาราง คิดเสียว่า Pivot Table เป็นตัวกลางสำหรับใช้หายอดรวมต่อเท่านั้น แค่สร้างตาราง Pivot เสร็จก็ให้ใช้สูตร GetPivotData ดึงตัวเลขไปใช้กับตารางที่มีโครงสร้างและรูปแบบตามที่หัวหน้าต้องการได้ง่ายกันกว่าเยอะเลย
ก่อนจะจบเนื้อหานี้ แทนที่จะต้องเสียเวลาใช้ Pivot Table แล้วใช้สูตร GetPivotData คุณสามารถใช้สูตร SumIFS หรือ SumProduct ลัดหาคำตอบที่ต้องการได้โดยตรง โดยสร้างสูตรตามนี้
=SumIFS(Sales, Month, B10, Product, C10, Salesperson, D10)
=SumProduct( (Month=B10) * (Product=C10) * (Salesperson=D10) * Sales)
=SumProduct(--(Month=B10) ,--(Product=C10) ,--(Salesperson=D10) , Sales)
ทั้งนี้ Sales, Month, Product, Saleperson เป็น Range หรือ Range Name ของข้อมูลแต่ละเรื่อง
สร้างสูตรเป็นจะช่วยหาคำตอบใหม่ได้ในพริบตา แต่ถ้าใช้ Pivot Table ต้องเสียแรงเสียเวลาใช้มือไปคลิกคำสั่งบนเมนูทุกครั้งที่ข้อมูลมีการเปลี่ยนแปลง นั่นหมายถึงต้องบอกหัวหน้าว่า รอไปก่อนเถอะ
