จากนี้มาดูบทย่อของการสร้างรายงานเพื่อผู้บริหารกันครับ

กว่าจะหายอดรวมของการขายและการรับสินค้าตามวันที่ที่ต้องการด้วยสูตร

=SUMIFS(Receive,Date,G7,Product,H7) หรือ
=SUMPRODUCT((Date=G7)*(Product=H7)*Receive)

ต้องรู้จักการสร้าง Range Name แล้วกดปุ่ม F3 เพื่อนำชื่อ Receive, Date, Product ลิงก์มาใส่ในสูตรได้ทันทีโดยไม่ต้องเสียเวลาหยิบเมาส์ไปลากพื้นที่

พอจะเจาะลึกหายอดสูงสุดของสินค่าในวันที่ที่ต้องการให้ใช้สูตร

{=MAX(IF((Date=G7)*(Product=H7),Receive))}
ซึ่งสูตรนี้ต้องกดปุ่ม Ctrl+Shift+Enter และสามารถนำไปใช้กับ Excel ได้ทุกรุ่น ยืดหยุ่นกว่าการใช้สูตรรุ่นใหม่ที่ใช้ได้กับรุ่น 365 เท่านั้น

เมื่อจะหายอดค่าเฉลี่ย แม้จะสามารถใช้สูตร AverageIF ได้ด้วย แต่สูตรนี้หาค่าผิดพลาดเพราะนับรายการที่มียอดเท่ากับ 0 มาหาค่าเฉลี่ยด้วย

ทางที่ดีกว่า ให้หาค่าเฉลี่ยจากการนำยอดรวมที่หาได้ไปหารด้วยจำนวนนับที่หาได้ด้วยสูตร
{=COUNT(IF((Date=G7)*(Product=H7)*(Receive>0),Receive))}

จากนั้นในอนาคต เมื่อจะเพิ่มรายการ ให้ใช้คำสั่ง Insert > Table เพื่อช่วยทำให้พื้นที่ตารางที่ตั้งชื่อไว้ปรับขนาดตามจำนวนรายการที่เพิ่มขึ้นโดยอัตโนมัติ

คลิกที่นี่เพื่อ Download ตัวอย่างนี้

😵‍💫 โยน Pivot Table ทิ้งไป ใช้ SumProduct ดีกว่า

การทำรายงานนำเสนอผู้บริหาร (Management Report) ต้องกระชับและจับประเด็นสำคัญให้เห็นเด่นชัดภายในหน้าเดียว ถ้าใช้ Excel ก็ต้องหาทางทำให้ยืดหยุ่น หัวหน้าต้องการดูเรื่องอะไรก็ต้องหามาให้ได้ในพริบตา

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

แทนที่จะใช้ Pivot Table หนีไปใช้สูตร Sum Array หรือ SumProduct ขึ้นมานำเสนอยอดรวมที่ต้องการให้เห็นกับตาทันทีดีกว่า

🧐 จำหลักไว้ว่า ถ้าเงื่อนไขเป็นแบบเท่ากับ และเป็นเรื่องเดียวกันให้ใช้บวก ถ้าเงื่อนไขต่างเรื่องกันให้ใช้คูณ โดยไม่ต้องเสียเวลาไปใช้ IF ซ้อน IF

➕ แบบบวก เช่น ต้องการหายอดรวมของสินค้ารหัส a001 กับ a002 ซึ่งให้มองว่าเงื่อนไขเป็นเรื่องเดียวกันเพราะเป็นรหัสทั้งคู่

=SumProduct( ( (ตารางรหัส = “a001”) + (ตารางรหัส = “a002”) ) * ตารางจำนวนสินค้า)

✖️ แบบคูณ เช่น ต้องการหายอดรวมของสินค้ารหัส a001 ที่ลูกค้านาย ก ซื้อในวันนี้ ซึ่งรหัส ชื่อลูกค้า วันที่เป็นต่างเรื่องกัน

=SumProduct( (ตารางรหัส = “a001”) * (ตารางเก็บชื่อลูกค้า = “นาย ก”) * (ตารางเก็บวันที่เกิดรายการ = Today()) * ตารางจำนวนสินค้า)

🧐 จำเพิ่มอีกนิดว่า กรณีที่ใช้บวกจะต้องใส่วงเล็บเปิดซ้อนกัน 3 ชั้น เพราะต้องจับเรื่องเดียวกันทั้งหมดมาบวกกันให้เสร็จก่อน ส่วนแบบคูณใส่วงเล็บเปิดเพียง 2 ชั้นเท่านั้น

ถ้าใช้ Excel 365/2021 จะเปลี่ยนคำว่า SumProduct เป็น Sum ก็ได้ ทำงานแทนได้ แต่ถ้าใช้ Excel รุ่นก่อนต้องกดปุ่ม Ctrl+Shift+Enter พร้อมกันเพื่อทำให้มีวงเล็บปีกกา { } ปิดหัวท้ายสูตร

ถ้าใครถนัดสูตร SumIF หรือ SumIFS จะเลือกใช้แบบที่ถนัดก็ได้ แต่จะใช้ได้กับกรณีต่างเรื่องเท่านั้น ซึ่งผมจะชอบ SumProduct มากกว่าเพราะไม่ใช่แค่ใช้ได้ทั้งแบบเรื่องเดียวกันและต่างเรื่องแล้ว ยังสามารถแกะสูตรแต่ละส่วนได้ว่า Excel คิดทีละขั้นยังไง

SumProduct ทำหน้าที่ยังไง คลิกที่นี่

ถ้าสงสัยว่าทำไมต้องบวก ดูคำอธิบายได้ที่
https://www.excelexperttraining.com/book/index.php/course-manuals/excel-expert-tips-tricks-and-traps/array-if-add

ที่มาของแบบคูณ
https://www.excelexperttraining.com/book/index.php/course-manuals/excel-expert-tips-tricks-and-traps/array-if-multiply