ผมเคยเขียนบทความเกี่ยวกับ Array Formula เมื่อหลายปีมาแล้ว และนี่ก็คงถึงเวลาที่ต้อง Refresh เนื้อหาใหม่ซักหน่อย เพราะใน Excel ยุคใหม่อย่าง Excel 365 ที่รองรับ Dynamic Array นั้นทำให้การเขียนสูตรแบบ Array ง่ายขึ้นและเจ๋งขึ้นมากๆ เลย
สารบัญ
Array Formula คืออะไร?
สำหรับคนที่ยังไม่รู้จักว่า Array Formula คืออะไร ผมก็คงจะต้องอธิบายก่อนแหละ ว่ามันคือวิธีการเขียนสูตรที่มีลักษณะประมาณนี้
- สูตรที่ให้ผลลัพธ์ออกมาหลายค่า
- สูตรที่เราใส่ Input แบบหลายค่าลงไปใน Argument ของ Function ที่ปกติแล้วเราจะใส่แบบค่าเดียว
ซึ่งข้อดีของมันคือการที่เราเขียนสูตรคำนวณที่ซับซ้อนได้ในช่องเดียว แบบไม่ต้องทดออกมาใน Cell อื่น แบบตอนที่เขียนสูตรตามปกติ
ฟังแล้วก็ยังงงๆ เนอะ ไปดูตัวอย่างดีกว่า…
วิธีการเขียนสูตรแบบ Array
ถ้าเป็น Excel 365 ที่รองรับ Dynamic Array
หากผมเขียนสูตรแบบนี้ ในช่อง E7 แล้วกด Enter
=B2:B5-C2:C5จะสามารถแสดงผลลัพธ์แบบพุ่งพรวดออกมาทีเดียว 4 ช่องได้เลย โดยที่ ผลลัพธ์แต่ละตัวเกิดจาก สมาชิกแต่ละตัวของ Range แรก ลบด้วย สมาชิกแต่ละตัวของ Range ที่สอง ทำให้มีผลลัพธ์ออกมา 4 ตัว นั่นเอง และใน Excel 365 มันสามารถเอาผลลัพธ์ 4 ตัวโปรยลงไปแสดงในหลายๆ ช่องได้ (เรียกว่า Spill โดยที่สูตรจริงๆ อยู่ในช่อง E7 ช่องเดียวนะ ช่องอื่นไม่ได้มีสูตรอยู่จริง)
แต่ถ้าเป็น Excel version เก่า
จะต้องลากคลุมพื้นที่ E7:E10 ก่อน แล้วเขียนสูตร
=B2:B5-C2:C5แล้วค่อยกด Ctrl+Shift+Enter ถึงจะได้ผลเหมือนกัน ซึ่งจะเห็นใน Formula Bar มีเครื่องหมายปีกกา { } มาครอบสูตรโดยอัตโนมัติ
ถ้าเผลอกด Enter ธรรมดาก็จะขึ้น Error แบบ #VALUE! ทันที เพราะว่า Excel version เก่าไม่สามารถแสดงผลลัพธ์หลายค่าได้จากการใส่สูตรในช่องเดียว
ซึ่งจะเห็นว่าใน Excel version 365 นั้นง่ายกว่าเยอะมากๆ เลย
เราสามารถซ้อนสูตรเพื่อรวบคำตอบให้เหลือค่าเดียวก็ได้
Array ที่ให้ผลลัพธ์หลายค่า เราสามารถซ้อนสูตรเพื่อรวบคำตอบให้เหลือค่าเดียวก็ได้ เช่น เขียนฟังก์ชันสรุปข้อมูลเช่น SUM, AVERAGE, MAX.. พวกนี้ครอบลงไป
=MAX(B2:B5-C2:C5)ซึ่งถ้าเป็น Excel Version เก่า เราก็ยังต้องกด Ctrl+Shift+Enter อยู่ดีนะ ถึงจะไม่ Error
การตรวจสอบสูตรด้วยการกด F9
เวลาเขียนสูตรใน Excel ยิ่งเขียนซับซ้อนมากขึ้น เราก็ยิ่งจะต้องตรวจสอบสูตรให้เป็น ไม่งีั้นจะยิ่งงงหนักเลย ซึ่งวิธีการตรวจสอบก็คือ Hightlight สูตรส่วนที่ต้องการแล้วกด F9
เช่น Highlight แบบนี้
กด F9 แล้วจะเห็นข้อมูลออกมา 4 อันอยู่ในเครื่องหมายปีกกา (สัญลักษณ์ของ Array) ข้อมูลแต่ละอันคั่นด้วย semicolon แปลว่าอยู่คนละบรรทัด (ถ้าคั่นด้วย comma แสดงว่าอยู่คนละคอลัมน์)
ตรวจสอบเสร็จอย่าลืมกด Esc เพื่อ Cancel ไม่งั้นมันจะบันทึกค่าคงที่นี้ลงไปในสูตรเลย
มิติของ Array
หากเราเอา Array 2 ตัวที่มีมิติหลายๆ แบบ มาทำอะไรกันซักอย่างจะให้ผลยังไง มาดูกัน
1 มิติแนวตั้งทั้งคู่
ผลลัพธ์ก็เป็นแนวตั้ง
1 มิติแนวนอนทั้งคู่
ผลลัพธ์ก็เป็นแนวนอน
เดี๋ยวลองกด F9 ให้ดู จะเห้นว่าคราวนี้คั่นด้วยเครื่องหมาย Comma แล้ว เพราะอยู่คนละคอลัมน์
1 มิติแนวตั้ง กับ 1 มิติแนวนอน
ผลลัพธ์กลายเป็นตาราง ขนาดเท่ากับแนวตั้ง x แนวนอน
ถ้าอันนี้กด F9 จะเห็นว่าคั่นด้วย Comma ก่อนเพื่อขึ้นคอลัมน์ใหม่ พอขึ้นบรรทัดใหม่ก็จะคั่นด้วย Semicolon
ตาราง 2 มิติทั้งคู่ ขนาดเท่ากัน
ผลลัพธ์ก็เป็นตารางขนาดเท่าเดิม
อย่างไรก็ตาม หากตารางขนาดไม่เท่ากัน มันจะ Error ในส่วนที่จับคู่กันไม่ได้
ตาราง 2 มิติ กับข้อมูลแนวตั้ง
ข้อมูลแนวตั้งเหมือนจะถูกขยายขนาดให้มีคอลัมน์เท่ากับตาราง 2 มิตินั้น โดยทุกคอลัมน์มีค่าเท่ากัน
อย่างไรก็ตาม ข้อมูลแนวตั้งต้องมีจำนวนแถวเท่ากับข้อมูลในตารางนะ ไม่งั้นมันจะ Error ในส่วนที่จับคู่กันไม่ได้
ตาราง 2 มิติ กับข้อมูลแนวนอน
ข้อมูลแนวนอนเหมือนจะถูกขยายขนาดให้มีจำนวนแถวเท่ากับตาราง 2 มิตินั้น โดยทุกแถวมีค่าเท่ากัน
อย่างไรก็ตาม ข้อมูลแนวนอนต้องมีจำนวนคอลัมน์เท่ากับข้อมูลในตารางนะ ไม่งั้นมันจะ Error ในส่วนที่จับคู่กันไม่ได้
กรอก Input แบบ Array ลงไปในฟังก์ชัน
เราสามารถใส่ Input แบบ Array ลงไปในฟังก์ชันที่ปกติรับค่าค่าเดียวได้ครับ เช่นอันนี้เราจะหาว่าชื่อที่ยาวที่สุดมีกี่ตัวอักษร
ปกติ LEN จะรับ input แค่ตัวเดียว แต่นี่เราใส่ไปทีเดียว 5 ตัวในแนวตั้ง มันก็เลยให้ผลลัพธ์ออกมา 5 ตัวในแนวตั้งด้วยเช่นกัน
=LEN(A2:A6)
สุดท้ายค่อยรวบด้วย MAX ครอบเข้าไป ทำให้ผลลัพธ์กลับมาเหลือตัวเดียวอีกครั้ง
=MAX(LEN(A2:A6))
และถ้าเราอยากได้ว่าชื่อที่ยาวที่สุดชื่ออะไรด้วย ก็ใช้พวก index match มาช่วยก็ได้ เช่น
=INDEX(A2:A6,MATCH(C2,LEN(A2:A6),0))
สังเกตุว่าใน MATCH เราก็ใส่ LEN ซ้อนลงไปได้เลยเหมือนกัน เพื่อให้มันให้ค่ามาเป็นจำนวนตัวอักษรแต่ละชื่อก่อน จะได้ MATCH หาเลข 17 ได้เจอ
ตอนต่อไป
เดี๋ยวตอนต่อไปเราจะมาดูตัวอย่างการเขียนที่เจ๋งยิ่งขึ้นกว่านี้ นั่นก็คือการเขียน array formula แบบมีเงื่อนไขครับ
