Excel 365 Dashboards ตอนที่ 6 (Season 2) : มหัศจรรย์ยกกำลังสองของสูตร CountIF

Season 1 จบลงไปแล้ว พระเอกกำลังเข้าตาจนตรงสูตร CountIF ที่กลับโครงสร้างข้างในให้เป็น IFCount ติดกับดักตรงพื้นที่ ซึ่งอ้างอิงไว้แค่ไม่กี่เซลล์ ยากที่จะเพิ่มช่องให้รับตัวเลือกในอนาคตที่อาจมีมากกว่าเดิม

Season 2 นี้ พระเอกเปิดอาวุธลับออกมาจัดการแก้ปัญหาเรียบร้อย เป็นสิ่งที่หลายคนนึกไม่ถึงกันมาก่อน นั่นคือ

1. พื้นที่ตารางที่ใช้รับค่า Product Choices ไม่จำกัดแค่แนวตั้ง แต่สามารถเป็นพื้นที่ขนาดใหญ่ขึ้น มีรูปร่างเป็นสี่เหลี่ยมหลาย row หลาย column ก็ได้

 

 

แค่ขยายพื้นที่ของ Product Choices ให้มีขนาดตามต้องการแล้วตั้งชื่อ Range Name ให้กับพื้นที่

จากนั้นนำชื่อไปใช้ในสูตรใหม่เป็น
=SUM(( ((COUNTIF(ProductChoices,Product)>=1)+(COUNTA(ProductChoices)=0) )*Sales))

ในอนาคตหากต้องการขยายพื้นที่ให้เข้าไปทำที่เมนู Formulas > Name Manager แก้ขนาดพื้นที่ในชื่อ ProductChoices โดยไม่ต้องเสียเวลาไปแก้ในสูตร
 

2. สามารถใช้หลายพื้นที่มาเพิ่มได้อีก โดยใช้สูตร VStack ของ Excel 365 มาเชื่อมหลายพื้นที่เข้าด้วยกัน ทำให้สูตร CountIF สามารถอ้างอิงกับพื้นที่ตารางที่จะขยายตัวในอนาคตแบบ Dynamic Array ได้ด้วย

เมื่อต้องการเพิ่มจำนวนพื้นที่ให้ใช้สูตร =VSTACK(ProductChoices1,ProDuctChoices2)
นำหลายตารางมาต่อกัน จะวางไว้ต่างชีทกันก็ยังได้

พอได้ตารางที่ต่อกันเสร็จแล้ว ให้ตั้งชื่อ Range Name อ้างอิงกับเซลล์แรกของตาราง VStack เช่น ตามภาพนี้ตั้งชื่อว่า
PDChoices
=$E$17#

จากนั้นนำชื่อไปใช้ในสูตรใหม่เป็น
=SUM(( ((COUNTIF(PDChoices,Product)>=1)+(COUNTA(PDChoices)=0) )*Sales))

Download ตัวอย่างได้จาก
https://drive.google.com/file/d/1gL-RBtLw6lSezQUrtLvABDljey2fcbtb/view?usp=sharing