สูตร Array

การใช้สูตร Array เป็นเสมือนการใช้ตารางในอุดมคติ โดยสร้างสูตรอ้างถึงตารางในอุดมคตินี้ลงไปในเซลล์ๆเดียวแทน ส่งผลให้ไฟล์มีขนาดเล็กลง และคำนวณเร็วขึ้น เนื่องจากไม่ต้องเปลืองพื้นที่ตารางอย่างแต่ก่อน

อย่างไรก็ตาม ตัวสูตร Array จะทำให้ Excel เสียเวลาคำนวณนานกว่าเดิม ดังนั้นจึงต้องเลือกใช้สูตร Array ให้เหมาะกับเหตุการณ์ พยายามเลือกใช้สูตร Array เพื่อทำให้ไฟล์มีขนาดเล็กลงต่างจากเดิมมาก

โครงสร้างตารางในอุดมคติของ Array

ในตารางในอุดมคติของ Array ไม่มีตำแหน่ง Row หรือ Column กำหนดไว้ให้เห็นชัดเหมือนตารางของจริง แต่ Array จะใช้เครื่องหมาย comma , เพื่อแสดงว่า ค่าถัดไปยังอยู่ใน Row เดิม และใช้เครื่องหมาย semi-colon ; เพื่อแสดงว่าค่าถัดไปขึ้น Row ใหม่ และเมื่อสร้างสูตร Array จะต้องมีเครื่องหมายวงเล็บปีกกาปิดหัวท้ายลำดับค่าด้วยเสมอ เพื่อทำให้ Excel ยอมรับค่าแบบ Array เช่น

ค่าตามแนวนอน ขนาด 1 Row x 3 Column
={1,2,3}

ค่าตามแนวตั้ง ขนาด 3 Row x 1 Column
={1;2;3}

ค่าตามแนวนอนและแนวตั้ง ขนาด 2 Row x 3 Column
={1,2,3;4,5,6}

เพื่อความสะดวกในการใช้งาน แทนที่จะต้องเสียเวลาพิมพ์ตัวเลขลงไปในสูตร Array โดยตรง โดยทั่วไปมักสร้างสูตร Array อ้างถึงตำแหน่งเซลล์ หรือ Range Name ของค่าที่อยู่ในตารางแทน เช่น ถ้าเซลล์ A1:A3 มีชื่อ Range Name ว่า MyCell และแต่ละเซลล์มีตัวเลข 1, 2, 3 ตามลำดับ สูตร Array ต่อไปนี้จะมีค่าเหมือนกัน

={1;2;3}
=A1:A3
=MyCell

และเมื่อนำ Array ไปหายอดรวมโดยใช้สูตร Sum จะได้คำตอบเท่ากับ 6 เท่ากันทุกสูตร

=Sum({1;2;3})
=Sum(A1:A3)
=Sum(MyCell)

อยากเก่ง Array ต้องแกะสูตรเป็น

ในตัวอย่างข้างต้น ให้คลิกลงไปในเซลล์ที่มีสูตร =A1:A3 หรือ =MyCell จากนั้นกดปุ่ม F2 แล้วกดตามด้วยปุ่ม F9 จะพบว่า Excel แปลงตำแหน่งอ้างอิงเป็น ={1;2;3}

หลักการใช้สูตร Array ในการคำนวณ

1. พยายามเลือกใช้สูตรซึ่ง Excel เตรียมไว้ให้ซึ่งทำงานแบบ Array ได้ในตัวอยู่แล้วก่อน เช่น SumIf, CountIf, SumProduct, หรือสูตรในกลุ่ม Lookup เพราะสูตรเหล่านี้ใช้เวลาคำนวณน้อยกว่าสูตร Array ที่เราสร้างขึ้นเอง
2. ในกรณีที่ต้องการสั่งให้ Excel นำค่าใน Array มาคำนวณทีละตำแหน่ง ให้กดปุ่ม Ctrl+Shift+Enter แทน Enter เพื่อสร้างสูตร Array ซึ่งจะพบว่า Excel สร้างเครื่องหมายวงเล็บปีกกา {} ปิดหัวท้ายสูตรให้อัตโนมัติ
3. แทนที่จะต้องเสียเวลากดปุ่ม F2 ตามด้วย F9 เพื่อแกะสูตร Array ที่สร้างลงไปในเซลล์เดียว ให้สร้างสูตร Array สูตรเดียวลงไปในเซลล์หลายเซลล์พร้อมกัน เพื่อทำให้ Excel กระจายค่าที่คำนวณได้ตามลำดับให้เห็นชัดเจนในแต่ละเซลล์ทันที

ตัวอย่าง คำนวณหามูลค่ารวมสินค้า

แทนที่จะต้องไล่คำนวณมูลค่ารวมทีละบรรทัดแล้วจึงนำผลมาหายอดรวมทั้งหมด
=(A2*B2)+(A3*B3)+(A4*B4)
=(1*10)+(2*20)+(3*30)
=10+40+90
=140

เราสามารถเลือกใช้สูตร SumProduct ซึ่งทำงานแบบ Array ในตัว โดยใช้ ตำแหน่งเซลล์แบบเป็นช่วง หรือ Range Name ทำให้แกะสูตรง่ายขึ้นมาก ดังนี้

=SumProduct(A2:A4,B2:B4)
=SumProduct(Quantity*UnitPrice)
=SumProduct(Quantity,UnitPrice)

เมื่อเปลี่ยนมาใช้สูตร Array ซึ่งจะต้องกดปุ่ม Ctrl+Shift+Enter แทน Enter จะเกิด { } ปิดหัวท้ายสูตรให้ทันที (ห้ามพิมพ์ { } ลงไปเอง)

{=Sum(A2:A4*B2:B4)}
{=Sum(Quantity*UnitPrice)}

ขอให้ทดลองแกะสูตรโดยคลิกเลือกแต่ละตำแหน่งบน Formula Bar โดยลากทับ แต่ละส่วนของ A2:A4, B2:B4, Quantity, UnitPrice แล้วกด F9 จะพบว่า สูตร Array นี้จะนำตัวเลขตามแนวตั้งมารอคูณกันทีละตำแหน่ง

=SUM({1;2;3}*{10;20;30})

จากนั้นให้คลิกเลือกเฉพาะส่วนของ {1;2;3}*{10;20;30} แล้วกด F9 จะเกิดผลลัพธ์การคูณแต่ละตำแหน่งเป็น

=SUM({10;40;90})

จากนั้นให้เลือกสูตรทั้งหมดบน Formula Bar แล้วกด F9 จะเห็นผลลัพธ์ 140

หมายเหตุ : สูตร SumProduct ที่ใช้เครื่องหมาย comma , จะไม่ยอมแสดงให้เห็นผลการคำนวณทีละขั้น จึงแนะนำให้ใช้เครื่องหมายคูณ * แทนที่ทุกตำแหน่งที่ใช้ , จะได้แกะสูตรได้เช่นเดียวกับสูตร Array