สูตร Excel ที่ส่งกลับชุดของค่าหรือที่เรียกว่าอาร์เรย์ จะส่งกลับค่าเหล่านี้ไปยังเซลล์ที่อยู่ใกล้เคียง ลักษณะการทํางานนี้เรียกว่า การสปิลล์
สูตรที่สามารถส่งกลับอาร์เรย์ที่มีขนาดตัวแปรได้จะเรียกว่าสูตรอาร์เรย์แบบไดนามิก สูตรที่กําลังส่งกลับอาร์เรย์ที่กําลังสปิลล์สําเร็จสามารถเรียกได้ว่าเป็นสูตรอาร์เรย์ที่สปิลล์
ต่อไปนี้เป็นบันทึกย่อบางอย่างเพื่อช่วยให้คุณเข้าใจและใช้สูตรชนิดเหล่านี้
สปิลล์หมายความว่าอย่างไร
หมายเหตุ
สูตรอาร์เรย์รุ่นเก่า หรือที่เรียกว่าสูตร อาร์เรย์ดั้งเดิม จะส่งกลับผลลัพธ์ที่มีขนาดคงที่เสมอ และจะสปิลล์ลงในจํานวนเซลล์เดียวกันเสมอ ลักษณะการทํางานสปิลล์ที่อธิบายไว้ในหัวข้อนี้ใช้ไม่ได้กับสูตรอาร์เรย์แบบดั้งเดิม
สปิลล์หมายความว่าสูตรส่งผลให้มีหลายค่า และค่าเหล่านั้นถูกวางในเซลล์ที่อยู่ใกล้เคียง ตัวอย่างเช่น =SORT(D2:D11,1,-1) ซึ่งเรียงลําดับอาร์เรย์ในลําดับจากมากไปหาน้อย จะส่งกลับอาร์เรย์ที่สอดคล้องกันซึ่งมีความสูง 10 แถว แต่คุณเพียงต้องใส่สูตรในเซลล์ด้านซ้ายบน หรือ F2 ในกรณีนี้ และจะสปิลล์ลงในเซลล์ F11 โดยอัตโนมัติ
ประเด็นสำคัญ
- เมื่อคุณกด Enter เพื่อยืนยันสูตรของคุณ Excel จะปรับขนาดช่วงผลลัพธ์ให้คุณแบบไดนามิก และวางผลลัพธ์ลงในแต่ละเซลล์ภายในช่วงนั้น
- ถ้าคุณกําลังเขียนสูตรอาร์เรย์แบบไดนามิกเพื่อดําเนินการกับรายการข้อมูล อาจเป็นประโยชน์ในการวางไว้ใน ตาราง Excelจากนั้น ใช้ การอ้างอิงที่มีแบบแผน เพื่ออ้างอิงถึงข้อมูล ทั้งนี้เนื่องจากการอ้างอิงที่มีแบบแผนจะปรับโดยอัตโนมัติเมื่อมีการเพิ่มหรือเอาแถวออกจากตาราง
- สูตรอาร์เรย์ที่สปิลล์ไม่ได้รับการสนับสนุนในตาราง Excel ดังนั้นคุณควรวางลงในเส้นตารางภายนอกตาราง ตารางเหมาะสมที่สุดสําหรับการเก็บแถวและคอลัมน์ของข้อมูลอิสระ
- เมื่อคุณใส่สูตรอาร์เรย์ที่สปิลล์ เมื่อคุณเลือกเซลล์ใดๆ ภายในพื้นที่สปิลล์ Excel จะวางเส้นขอบที่เน้นรอบๆ ช่วง เส้นขอบจะหายไปเมื่อคุณเลือกเซลล์ภายนอกพื้นที่
- เฉพาะเซลล์แรกในพื้นที่สปิลล์เท่านั้นที่สามารถแก้ไขได้ ถ้าคุณเลือกเซลล์อื่นในพื้นที่สปิลล์ สูตรจะปรากฏให้เห็นในแถบสูตร แต่ข้อความเป็น "แบบโกสต์" และไม่สามารถเปลี่ยนแปลงได้ ถ้าคุณต้องการอัปเดตสูตร คุณควรเลือกเซลล์ด้านบนซ้ายในช่วงอาร์เรย์ เปลี่ยนตามต้องการ จากนั้น Excel จะอัปเดตส่วนที่เหลือของพื้นที่สปิลล์ให้คุณโดยอัตโนมัติเมื่อคุณกด Enter
- สูตรทับซ้อนกัน - ไม่สามารถป้อนข้อมูลสูตรอาร์เรย์ได้ถ้ามีสิ่งใดที่บล็อกช่วงผลลัพธ์ และถ้าเกิดกรณีนี้ขึ้น Excel จะส่งกลับข้อผิดพลาด #SPILL! ที่ระบุว่ามีการบล็อก ถ้าคุณเอาการบล็อกออก สูตรจะสปิลล์ตามที่คาดไว้ ในตัวอย่างด้านล่าง ช่วงผลลัพธ์ของสูตรซ้อนทับกับช่วงอื่นที่มีข้อมูล และจะแสดงด้วยเส้นขอบแบบจุดที่ทับซ้อนกับเซลล์ที่มีค่าที่ระบุว่าไม่สามารถสปิลล์ได้ ลบข้อมูลการบล็อกหรือคัดลอกที่อื่น และสูตรจะสปิลล์ตามที่คาดไว้
- สูตรอาร์เรย์ดั้งเดิมที่ใส่ผ่าน CTRL+SHIFT+ENTER (CSE) จะยังคงได้รับการสนับสนุนด้วยเหตุผลความเข้ากันได้แบบย้อนกลับ แต่ไม่ควรใช้อีกต่อไป ถ้าคุณต้องการ คุณสามารถแปลงสูตรอาร์เรย์ดั้งเดิมเป็นสูตรอาร์เรย์แบบไดนามิกโดยการค้นหาเซลล์แรกในช่วงอาร์เรย์ คัดลอก ข้อความของสูตร ลบ ช่วงทั้งหมดของอาร์เรย์ดั้งเดิม แล้วใส่สูตรอีกครั้งในเซลล์ด้านซ้ายบน ก่อนที่จะอัปเกรดสูตรอาร์เรย์ดั้งเดิมเป็นสูตรอาร์เรย์แบบไดนามิก คุณควรตระหนักถึง ความแตกต่างในการคํานวณบางอย่าง ระหว่างทั้งสอง
- Excel จำกัดการสนับสนุนอาร์เรย์แบบไดนามิกระหว่างเวิร์กบุ๊ก และสถานการณ์สมมตินี้จะได้รับการสนับสนุนเมื่อเวิร์กบุ๊กทั้งสองเปิดอยู่ ถ้าคุณปิดเวิร์กบุ๊กต้นฉบับ สูตรอาร์เรย์แบบไดนามิกที่ถูกลิงก์ใดๆ จะส่งกลับข้อผิดพลาด #REF! เมื่อมีการรีเฟรช
ต้องการความช่วยเหลือเพิ่มเติมไหม
คุณสามารถสอบถามผู้เชี่ยวชาญใน ชุมชนด้านเทคนิคของ Excel หรือรับการสนับสนุนใน ชุมชนได้เสมอ