การจัดการข้อผิดพลาด SPILL

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

ลองนึกภาพเหมือนการพยายามนำรถบัสไปจอดในพื้นที่ที่มีรถยนต์จอดอยู่แล้ว รถบัสไม่ได้มีปัญหา แต่พื้นที่จอดถูกกีดขวาง

สาเหตุที่พบบ่อยที่สุดคือมีข้อมูลอยู่ในเซลล์อย่างน้อยหนึ่งเซลล์ของช่วงที่ผลลัพธ์จะกระจาย หาก =UNIQUE(A2:A12) ต้องเติมข้อมูลลงใน C1:C3 แต่ C2 มีค่าที่ไม่ควรอยู่ การกระจายผลลัพธ์จะถูกขวาง

วิธีแก้ทำได้ง่าย: เลือกเซลล์เริ่มต้น ดูเส้นประที่ล้อมช่วงการกระจาย แล้วล้างเซลล์ใดก็ตามภายในช่วงนั้นที่ไม่ว่าง

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

คุณยังสามารถคลิกสามเหลี่ยมคำเตือนข้างเซลล์ แล้วเลือก Select Obstructing Cells เพื่อไปยังเซลล์ที่เป็นปัญหาโดยตรง จากนั้นจึงลบข้อมูลในเซลล์เหล่านั้น

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

หากผลลัพธ์ของคุณอยู่บนแถบชื่อหรือป้ายชื่อที่ผสานกัน ให้ยกเลิกการผสานเซลล์เหล่านั้น (แท็บ Home, ปุ่มสลับ Merge and Center) แล้วสูตรจะแสดงผลแบบกระจายได้อย่างถูกต้อง

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

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

ตารางของ Excel (สร้างด้วย Ctrl+T) ไม่อนุญาตให้อาร์เรย์แบบไดนามิกกระจายผลลัพธ์ภายในตาราง เนื่องจากตารางกำหนดให้แต่ละเซลล์แก้ไขได้

หากสูตรที่กระจายผลลัพธ์อยู่ในคอลัมน์ของตาราง คุณจะได้รับ #SPILL! ให้แปลงตารางกลับเป็นช่วงข้อมูลปกติ (Table Design, Convert to Range) หรือย้ายสูตรออกไปไว้นอกตาราง

การอ้างอิงสูตรที่กระจายผลลัพธ์ไปยังทั้งคอลัมน์อาจทำให้เกิด #SPILL! ได้เช่นกัน เนื่องจากผลลัพธ์มีขนาดใหญ่มากและอาจชนกับเนื้อหาอื่นหรือเกินขีดจำกัด

ตัวอย่างเช่น =A:A*2 พยายามกระจายผลลัพธ์ถึงหนึ่งล้านแถว ให้จำกัดการอ้างอิงไว้เฉพาะข้อมูลจริง เช่น =A2:A100*2 เพื่อให้การกระจายผลลัพธ์มีขนาดพอดีและกำหนดขอบเขตได้ชัดเจน

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

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

สิ่งสำคัญที่ควรจำไว้คือ ข้อผิดพลาด #SPILL! แทบไม่เคยหมายความว่าตรรกะของสูตรไม่ถูกต้อง การคำนวณเสร็จสมบูรณ์แล้ว มีเพียงการวางผลลัพธ์เท่านั้นที่ไม่สำเร็จ

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

คุณพิมพ์ =SORT(UNIQUE(A2:A20)) ใน D1 แล้วได้ผลลัพธ์เป็น #SPILL! คุณเลือก D1 เห็นเส้นประครอบคลุม D1:D5 และสังเกตว่ามีบันทึกเก่าอยู่ใน D3

คุณลบเนื้อหาใน D3 ทันทีนั้น สูตรก็กระจายรายการค่าที่ไม่ซ้ำกันซึ่งเรียงลำดับแล้วลงใน D1:D5 ไม่จำเป็นต้องแก้ไขสูตรเลย

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

เมื่ออ้างอิงการกระจายผลลัพธ์อื่น ให้ใช้ตัวดำเนินการ # เช่น =SUM(D1#) เพื่อให้สูตรปรับตามขนาดช่วงที่เปลี่ยนแปลง สร้างผลลัพธ์ในพื้นที่โล่ง และ #SPILL! จะเกิดขึ้นไม่บ่อย

ทดสอบความเข้าใจเกี่ยวกับข้อผิดพลาด SPILL

คุณได้เรียนรู้ว่า #SPILL! เป็นปัญหาด้านการวางผลลัพธ์ ไม่ใช่ข้อผิดพลาดของตรรกะ:

• โดยส่วนใหญ่จะมีเซลล์หนึ่งในช่วงการกระจายผลลัพธ์ที่ ไม่ว่าง
• เซลล์ที่ผสาน และ ตาราง จะขัดขวางการกระจายผลลัพธ์
• หลีกเลี่ยงการอ้างอิง ทั้งคอลัมน์ ซึ่งทำให้ผลลัพธ์ใหญ่เกินไป
• ใช้ เลือกเซลล์ที่กีดขวาง และคำอธิบายข้อผิดพลาดเพื่อค้นหาสาเหตุ

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