การใช้สูตร Array IF แบบเงื่อนไขเดียว

โปรดสังเกตว่าใน Range ID มีรหัส a001 และ a002 เป็นรายการที่บันทึกซ้ำ ซึ่งถ้ามีรหัสไม่ซ้ำแล้วต้องการหายอด Amount ของ a001 จะสามารถหาคำตอบโดยใช้สูตร Vlookup ได้ทันที แต่เมื่อมีรายการซ้ำ ถ้าใช้สูตร Vlookup ก็จะได้ยอดของ a001 รายการแรกเพียงรายการเดียว หากต้องการหายอดรวม Amount ของ a001 หรือจะแยกหายอดแต่ละรายการที่ซ้ำของ a001 ก็ต้องอาศัยสูตรคำนวณแบบ Array มาช่วย

ถ้าไม่ใช้สูตร Array IF จะหายอดรวม Amount ของรหัส a001 จากการคำนวณทีละขั้นได้อย่างไร

1. เพื่อทำให้ตารางแสดงเฉพาะพื้นที่ซึ่งเกี่ยวข้องกับการคำนวณเท่านั้น จึงขอลบพื้นที่ส่วนของ Num และ Name ทิ้งไป แล้ว Insert เพิ่ม column C และ E มาใช้คำนวณทีละขั้น
2. ตั้งชื่อ Check ให้กับเซลล์ C3:C7 แล้วสร้างสูตร =Id=$G$3 จะพบว่าได้คำตอบเป็น TRUE; FALSE; TRUE; FALSE; FALSE ตามลำดับ โดยตำแหน่งที่เป็น True คือตำแหน่งของรหัส a001 นั่นเอง
3. เซลล์ E3:E7 สร้างสูตร =IF(Check,Amount,0) จะพบว่าถ้าตำแหน่งใดใน Check มีค่าเท่ากับ True สูตร IF นี้จะเก็บค่า Amount มาใช้ตามเดิม แต่ถ้าไม่ใช่จะใส่ค่า 0 แทน
4. เซลล์ E8 สร้างสูตร =SUM(E3:E7) ได้คำตอบเท่ากับ 40 ซึ่งเป็นยอดรวมแยกประเภทของรหัส a001 ตามต้องการ

แทนที่จะต้องสร้างตารางเพิ่มเติมเพื่อคำนวณทีละขั้น เราสามารถลัดหาคำตอบยอดรวมแยกประเภทของรหัส a001 ที่ต้องการโดยพิจารณาแยกพื้นที่ตารางเป็น 2 ส่วน

1. Id เป็นส่วนของ Array ที่ทำหน้าที่คืนค่า True หรือ False จากการนำไปเปรียบเทียบค่ากับรหัสที่ต้องการ
2. Amount เป็นส่วนของ Array ที่เป็นตัวเลขหรือค่าคำตอบเรื่องที่ต้องการ

จากนั้นเราสามารถหาคำตอบยอดรวมแยกประเภทของรหัส a001 ซึ่งบันทึกไว้ในเซลล์ G3 เป็นเซลล์สำหรับใส่รหัสที่ต้องการ แล้วใช้สูตรต่อไปนี้หาผลลัพธ์ที่ต้องการ

1. สูตร SUMIF โดยใช้สูตร =SUMIF(Id,$G$3,Amount) หรือ
2. สูตร Array SumIF โดยใช้สูตร { =SUM(IF(Id=$G$3,Amount))} โดยต้องกดปุ่ม Ctrl+Shift+Enter ด้วยจึงจะเกิดวงเล็บปีกกา { } ปิดหัวท้ายสูตร หรือ
3. สูตร SumArray โดยใช้สูตร { =SUM((Id=$G$3)*Amount)} โดยต้องกดปุ่ม Ctrl+Shift+Enter ด้วยจึงจะเกิดวงเล็บปีกกา { } ปิดหัวท้ายสูตร หรือ
4. สูตร SumProduct โดยใช้สูตร =SUMPRODUCT((Id=$G$3)*Amount) โดยไม่ต้องกดปุ่ม Ctrl+Shift+Enter ด้วยแต่อย่างใด

สูตรทั้งสี่แบบใช้หลักการคำนวณแบบเดียวกัน คือ

1. Id เป็นส่วนของ Array ที่ทำหน้าที่คืนค่า True หรือ False จากการนำไปเปรียบเทียบค่ากับรหัส a001 ที่ต้องการ จะคืนค่าออกมาเป็น Array
   {TRUE;FALSE;TRUE;FALSE;FALSE}
2. Amount เป็นส่วนของ Array ที่เป็นตัวเลขหรือค่าคำตอบเรื่องที่ต้องการ
   {10;20;30;40;50}
3. เมื่อนำ Array {TRUE;FALSE;TRUE;FALSE;FALSE} มาเทียบตำแหน่งกับ Array {10;20;30;40;50} จะได้ Array ตัวเลขเฉพาะตัวเลขคำตอบที่ต้องการออกมาเป็น
   {10;FALSE;30;FALSE;FALSE}
4. จากนั้นเมื่อนำผล {10;FALSE;30;FALSE;FALSE} ไปหายอดรวมด้วยสูตร Sum จึงหาคำตอบเป็น 40 ตามต้องการ

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

1. สูตร SUMIF ให้ใช้กับโจทย์ที่ง่ายไปตลอดเพราะสูตรนี้รับเงื่อนไขได้เพียงเงื่อนไขเดียว และสูตรนี้ใช้เวลาคำนวณเร็วที่สุดในบรรดาสี่สูตรที่กล่าวถึงอยู่นี้
2. สูตร Array SumIF สูตรนี้เป็นสูตรพิเศษ เพราะเราสามารถเปลี่ยนสูตร Sum ด้านหน้าไปเป็นสูตร Average, Count, Max, Min, หรือ Small เพื่อหายอดอื่นๆตามสูตรที่ใช้แทนนั้น และถ้ามีหลายเงื่อนก็สามารถซ้อน IF เข้าไปได้อีก
   
   { =SUM(IF(Id=$G$3,Amount))} หายอดรวมของ a001 ได้เท่ากับ 40
   { =AVERAGE(IF(Id=$G$3,Amount))} หาค่าเฉลี่ยของ a001 ได้เท่ากับ 20
   { =COUNT(IF(Id=$G$3,Amount))} นับยอดของ a001 ได้เท่ากับ 2
   { =MAX(IF(Id=$G$3,Amount))} หายอดสูงสุดของ a001 ได้เท่ากับ 30
   { =MIN(IF(Id=$G$3,Amount))} หายอดต่ำสุดของ a001 ได้เท่ากับ 10
3. สูตร SumArray เป็นสูตรที่ผมนิยมใช้เพราะใช้มานานและพบว่าสามารถหาคำตอบที่ต้องการได้เสมอแม้จะลิงค์ข้ามแฟ้มมาจากแฟ้มที่ไม่ได้เปิดก็ยังทำงานได้ และถ้ามีหลายเงื่อนไข การใช้วงเล็บหลายๆชุดช่วยตรวจสอบเงื่อนไขยังง่ายกว่าที่จะใช้สูตร IF ซ้อนกันเข้าไป
4. สูตร SumProduct เป็นสูตรยอดนิยมของคนทั่วไปเพราะไม่จำเป็นต้องกดปุ่ม Ctrl+Shift+Enter ด้วยแต่อย่างใด ซึ่งแท้จริงแล้วโครงสร้างภายในวงเล็บของสูตรนี้ก็เหมือนกับของสูตร SumArray นั่นเอง