สูตรทั่วไปใน Excel คำนวณกับค่าเดียวแล้วคืนผลลัพธ์ค่าเดียว แต่ Array Formula (สูตรอาเรย์) คำนวณกับ “ชุดของค่า” พร้อมกันทั้งหมด แล้วรวมผลลัพธ์เป็นค่าเดียวหรือหลายค่าก็ได้ เหมาะกับงานที่ SUMIFS หรือ COUNTIFS ทำไม่ได้ตรงๆ เช่น เงื่อนไขซับซ้อนหลายชั้น หรือคำนวณข้ามหลายคอลัมน์พร้อมกัน บทความนี้จะพาไปดูวิธีใช้สูตรอาเรย์แบบเดิม (Legacy CSE) ที่ต้องกด Ctrl+Shift+Enter ซึ่งยังพบเจอได้บ่อยในไฟล์เก่าหรือสูตรที่คัดลอกมาจากอินเทอร์เน็ต
Array Formula คืออะไร
เวลาปกติ สูตรอย่าง =A2*B2 จะคำนวณเซลล์เดียวกับเซลล์เดียว แต่สูตรอาเรย์สามารถคำนวณ A2:A10 กับ B2:B10 ทั้งชุดพร้อมกันในขั้นตอนเดียว โดยไม่ต้องสร้างคอลัมน์ช่วยคำนวณทีละแถว ก่อนยุค Excel 365 การป้อนสูตรแบบนี้ต้องกด Ctrl+Shift+Enter แทนการกด Enter ธรรมดา ซึ่ง Excel จะใส่วงเล็บปีกกา { } ล้อมสูตรให้อัตโนมัติเพื่อบอกว่าเป็นสูตรอาเรย์
วิธีป้อนสูตร Array แบบเดิม
พิมพ์สูตรตามปกติในเซลล์ แต่แทนที่จะกด Enter ให้กด Ctrl+Shift+Enter พร้อมกัน Excel จะเติมวงเล็บปีกกา { } ครอบสูตรให้เอง (ห้ามพิมพ์วงเล็บปีกกาเองด้วยมือ เพราะจะกลายเป็นข้อความธรรมดา ไม่ใช่สูตรอาเรย์จริง)
ตัวอย่าง: รวมยอดขายตามเงื่อนไขโดยไม่ใช้คอลัมน์ช่วย
สมมติต้องการรวมยอดขายเฉพาะสินค้า “เสื้อ” โดยไม่อยากเพิ่มคอลัมน์ช่วย สามารถใช้สูตรอาเรย์ผสม SUM กับ IF ได้
{=SUM(IF(B2:B6="เสื้อ",C2:C6))}| A | B | C | |
|---|---|---|---|
| 1 | รหัส | สินค้า | ยอดขาย |
| 2 | P01 | เสื้อ | 500 |
| 3 | P02 | กางเกง | 300 |
| 4 | P03 | เสื้อ | 700 |
| 5 | P04 | รองเท้า | 200 |
| 6 | P05 | เสื้อ | 100 |
สูตรนี้ทำงานโดยให้ Excel เช็คทีละแถวว่า B2:B6 ตรงกับ “เสื้อ” หรือไม่ ถ้าตรงจะดึงค่า C มารวม ผลลัพธ์คือ 500+700+100 = 1,300 โดยไม่ต้องสร้างคอลัมน์ช่วยแยกกรองก่อน
ตัวอย่าง: นับแบบมีเงื่อนไข OR หลายค่า
ถ้าต้องการนับแถวที่ตรงกับเงื่อนไข A หรือ B (ไม่ใช่ AND) สูตรอาเรย์ช่วยได้โดยไม่ต้องพึ่ง COUNTIFS ซ้อนกันหลายชั้น
{=SUM((A2:A6="สาขา A")+(A2:A6="สาขา C"))}| A | |
|---|---|
| 1 | สาขา |
| 2 | สาขา A |
| 3 | สาขา B |
| 4 | สาขา C |
| 5 | สาขา A |
| 6 | สาขา B |
ในสูตรนี้ Excel จะเปรียบเทียบทุกแถวกับทั้งสองเงื่อนไข แล้วแปลง TRUE/FALSE เป็น 1/0 ก่อนบวกกัน ได้ผลลัพธ์เป็นจำนวนแถวที่ตรงกับเงื่อนไขใดเงื่อนไขหนึ่ง (สาขา A มี 2 แถว + สาขา C มี 1 แถว = 3)
Excel เวอร์ชันใหม่ไม่ต้องกด Ctrl+Shift+Enter แล้ว
ถ้าใช้ Excel 365 หรือ Excel 2021 ขึ้นไป ระบบมี Dynamic Array ที่คำนวณสูตรแบบอาเรย์ให้อัตโนมัติเพียงกด Enter ธรรมดา ไม่ต้องกด Ctrl+Shift+Enter อีกต่อไป และสูตรบางตัวอย่าง FILTER, SORT, UNIQUE ก็ถูกออกแบบมาให้ทำงานแบบอาเรย์โดยธรรมชาติอยู่แล้ว หากเปิดไฟล์เก่าที่มีสูตร Legacy CSE อยู่ Excel รุ่นใหม่ยังคงรองรับและแสดงวงเล็บปีกกาตามเดิม แต่ถ้าเขียนสูตรใหม่ แนะนำให้ลองใช้ฟังก์ชัน Dynamic Array แทนก่อน เพราะอ่านง่ายกว่าและไม่ต้องจำการกดปุ่มพิเศษ
ข้อควรระวัง
- สูตรอาเรย์ที่ครอบข้อมูลจำนวนมาก (หลายพัน-หมื่นแถว) จะคำนวณช้ากว่าสูตรปกติ เพราะต้องประมวลผลทุกเซลล์พร้อมกัน
- ถ้าแก้ไขสูตรอาเรย์แล้วลืมกด Ctrl+Shift+Enter ซ้ำ อาจได้ผลลัพธ์ผิดเพี้ยนโดยไม่มี error แจ้งเตือน
- อย่าพิมพ์วงเล็บปีกกา
{ }เองด้วยมือ ต้องให้ Excel ใส่ให้อัตโนมัติผ่านการกด Ctrl+Shift+Enter เท่านั้น - ดูเพิ่มเติมที่ สูตร SUMPRODUCT ใน Excel ซึ่งทำงานแบบอาเรย์ได้โดยไม่ต้องกด Ctrl+Shift+Enter และ สูตร FILTER ใน Excel ทางเลือกยุคใหม่แทน Array Formula
สรุป
Array Formula แบบเดิมช่วยให้คำนวณเงื่อนไขซับซ้อนได้โดยไม่ต้องสร้างคอลัมน์ช่วย ป้อนสูตรแล้วกด Ctrl+Shift+Enter แทน Enter ธรรมดา Excel จะเติมวงเล็บปีกกาให้อัตโนมัติ ถ้าใช้ Excel 365 ขึ้นไป ลองพิจารณาใช้ Dynamic Array อย่าง FILTER หรือ SUMPRODUCT แทน เพราะใช้งานง่ายกว่าและไม่ต้องจำปุ่มลัดพิเศษ
