Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel สำหรับเว็บ Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel for iPad Excel for iPhone Excel สำหรับแท็บเล็ต Android Excel สำหรับโทรศัพท์ Android

สูตร Excel ที่ส่งกลับชุดของค่าหรือที่เรียกว่าอาร์เรย์ จะส่งกลับค่าเหล่านี้ไปยังเซลล์ที่อยู่ใกล้เคียง ลักษณะการทํางานนี้เรียกว่า การสปิลล์

สูตรที่สามารถส่งกลับอาร์เรย์ที่มีขนาดตัวแปรเรียกว่าสูตร อาร์เรย์แบบไดนามิก สูตรที่กําลังส่งกลับอาร์เรย์ที่สปิลล์สําเร็จสามารถเรียกว่า สูตรอาร์เรย์ที่สปิลล์ ได้สําเร็จ 

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

สปิลล์หมายความว่าอย่างไร

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

สปิลล์หมายความว่าสูตรส่งผลให้มีหลายค่า และค่าเหล่านั้นถูกวางในเซลล์ที่อยู่ใกล้เคียง ตัวอย่างเช่น =SORT(D2:D11,1,-1) ซึ่งเรียงลําดับอาร์เรย์ในลําดับจากมากไปหาน้อย จะส่งกลับอาร์เรย์ที่สอดคล้องกันซึ่งมีความสูง 10 แถว แต่คุณเพียงต้องใส่สูตรในเซลล์ด้านซ้ายบน หรือ F2 ในกรณีนี้ และจะสปิลล์ลงในเซลล์ F11 โดยอัตโนมัติ

เรียงลําดับค่าในเซลล์ D2:D11 ด้วย =SORT(D2:D11,1,-1)

ประเด็นสำคัญ

  • เมื่อคุณกด Enter เพื่อยืนยันสูตรของคุณ Excel จะปรับขนาดช่วงผลลัพธ์ให้คุณแบบไดนามิก และวางผลลัพธ์ลงในแต่ละเซลล์ภายในช่วงนั้น

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

  • สูตรอาร์เรย์ที่สปิลล์ไม่ได้รับการสนับสนุนในตาราง Excel ดังนั้นคุณควรวางลงในเส้นตารางภายนอกตาราง ตารางเหมาะสมที่สุดสําหรับการเก็บแถวและคอลัมน์ของข้อมูลอิสระ

  • เมื่อคุณใส่สูตรอาร์เรย์ที่สปิลล์ เมื่อคุณเลือกเซลล์ใดๆ ภายในพื้นที่สปิลล์ Excel จะวางเส้นขอบที่เน้นรอบๆ ช่วง เส้นขอบจะหายไปเมื่อคุณเลือกเซลล์ภายนอกพื้นที่

    สูตรอาร์เรย์ที่มีช่วงผลลัพธ์ที่ถูกเน้นด้วยเส้นขอบสีน้ำเงิน

  • เฉพาะเซลล์แรกในพื้นที่สปิลล์เท่านั้นที่สามารถแก้ไขได้ ถ้าคุณเลือกเซลล์อื่นในพื้นที่สปิลล์ สูตรจะปรากฏให้เห็นในแถบสูตร แต่ข้อความเป็น "แบบโกสต์" และไม่สามารถเปลี่ยนแปลงได้ ถ้าคุณต้องการอัปเดตสูตร คุณควรเลือกเซลล์ด้านบนซ้ายในช่วงอาร์เรย์ เปลี่ยนตามต้องการ จากนั้น Excel จะอัปเดตส่วนที่เหลือของพื้นที่สปิลล์ให้คุณโดยอัตโนมัติเมื่อคุณกด Enter

    รูปภาพของสูตรอาร์เรย์แบบโกสต์ ซึ่งหมายความว่าไม่สามารถแก้ไขได้ เนื่องจากไม่ใช่เซลล์แรกในช่วงอาร์เรย์

  • สูตรทับซ้อนกัน - ไม่สามารถป้อนข้อมูลสูตรอาร์เรย์ได้ถ้ามีสิ่งใดที่บล็อกช่วงผลลัพธ์ และถ้าเกิดกรณีนี้ขึ้น Excel จะส่งกลับข้อผิดพลาด #SPILL! ที่ระบุว่ามีการบล็อก ถ้าคุณเอาการบล็อกออก สูตรจะสปิลล์ตามที่คาดไว้ ในตัวอย่างด้านล่าง ช่วงผลลัพธ์ของสูตรซ้อนทับกับช่วงอื่นที่มีข้อมูล และจะแสดงด้วยเส้นขอบแบบจุดที่ทับซ้อนกับเซลล์ที่มีค่าที่ระบุว่าไม่สามารถสปิลล์ได้ ลบข้อมูลการบล็อกหรือคัดลอกที่อื่น และสูตรจะสปิลล์ตามที่คาดไว้

    รูปภาพของ #SPILL! ข้อผิดพลาดที่ระบุว่าผลลัพธ์สูตรอาร์เรย์กําลังประสบกับการบล็อกที่ป้องกันไม่ให้เกิดการสปิลล์

  • สูตรอาร์เรย์ดั้งเดิมที่ใส่ผ่าน CTRL+SHIFT+ENTER (CSE) จะยังคงได้รับการสนับสนุนด้วยเหตุผลความเข้ากันได้แบบย้อนกลับ แต่ไม่ควรใช้อีกต่อไป ถ้าคุณต้องการ คุณสามารถแปลงสูตรอาร์เรย์ดั้งเดิมเป็นสูตรอาร์เรย์แบบไดนามิกโดยการค้นหาเซลล์แรกในช่วงอาร์เรย์ คัดลอก ข้อความของสูตร ลบ ช่วงทั้งหมดของอาร์เรย์ดั้งเดิม แล้วใส่สูตรอีกครั้งในเซลล์ด้านซ้ายบน ก่อนที่จะอัปเกรดสูตรอาร์เรย์ดั้งเดิมเป็นสูตรอาร์เรย์แบบไดนามิก คุณควรตระหนักถึง ความแตกต่างในการคํานวณบางอย่างระหว่างทั้งสอง

  • Excel จำกัดการสนับสนุนอาร์เรย์แบบไดนามิกระหว่างเวิร์กบุ๊ก และสถานการณ์สมมตินี้จะได้รับการสนับสนุนเมื่อเวิร์กบุ๊กทั้งสองเปิดอยู่ ถ้าคุณปิดเวิร์กบุ๊กแหล่งข้อมูล สูตรอาร์เรย์แบบไดนามิกที่ลิงก์ใดๆ จะส่งกลับเป็น ข้อผิดพลาด #REF! เมื่อรีเฟรช

ต้องการความช่วยเหลือเพิ่มเติมไหม

คุณสามารถสอบถามผู้เชี่ยวชาญใน Excel Tech Community หรือรับการสนับสนุนใน ชุมชน

ดูเพิ่มเติม

ฟังก์ชัน FILTER

ฟังก์ชัน RANDARRAY

ฟังก์ชัน SEQUENCE

ฟังก์ชัน SORT

ฟังก์ชัน SORTBY

ฟังก์ชัน UNIQUE

ข้อผิดพลาด #SPILL! ใน Excel

ตัวดําเนินการอินเทอร์เซกชันโดยนัย: @

ต้องการความช่วยเหลือเพิ่มเติมหรือไม่

ต้องการตัวเลือกเพิ่มเติมหรือไม่

สํารวจสิทธิประโยชน์ของการสมัครใช้งาน เรียกดูหลักสูตรการฝึกอบรม เรียนรู้วิธีการรักษาความปลอดภัยอุปกรณ์ของคุณ และอื่นๆ

ชุมชนช่วยให้คุณถามและตอบคําถาม ให้คําติชม และรับฟังจากผู้เชี่ยวชาญที่มีความรู้มากมาย