ภาพรวม
#SPILL! จะเกิดขึ้นเมื่อสูตรของเรามีค่าที่ส่งกลับมากกว่า 1 ค่า (multiple results) หรือจะเรียกว่า dynamic array และที่นี้ผลลัพธ์ที่ได้ มันล้นกรอบหรือขอบเขตที่ Excel จะวางข้อมูลให้เราได้ ซึ่งอาจเกิดได้หลายกรณี ไม่ว่าจะเป็น
ก่อนจะไปดูรายละเอียด ไฟล์ตัวอย่างดาวน์โหลดได้ที่ลิงค์นี้ครับ
Range ที่จะใส่ข้อมูลไม่ว่าง
หรือพูดได้ว่า ถ้าผลลัพธ์ที่จะส่งกลับมาจะวางทับข้อมูลเดิมที่มีอยู่ในเซลล์ Excel จะไม่ทำ คงเพราะกลัวข้อมูลเราหาย 👍 ดังนั้น Excel จะแสดง error เป็น #SPILL!
กำหนดขนาดที่แน่นอนของ range ไม่ได้
ส่วนมากเกิดขึ้นจากขนาดของ spill range หรือพื้นที่ที่จะใส่ข้อมูลนั้นไม่คงที่ โดยมักจะถูกปรับขนาดไปตามผลการคำนวณในแต่ละรอบ เช่น
=SEQUENCE(RANDBETWEEN(1,1000))
โดย error นี้มักจะเกิดขึ้นจากการใช้ฟังก์ชัน RAND, RANDARRAY และ RANDBETWEEN
หรือฟังก์ชันฟังก์ชันที่มีการเปลี่ยนค่าไปเรื่อย ๆ (volatile functions) หรือค่าไม่คงที่ จะถูกคำนวณใหม่เสมอหากมีการเปลี่ยนแปลงใด ๆ ใน worksheet หรือถูกสั่งให้คำนวณใหม่ ฟังก์ชันเหล่านี้ได้แก่ OFFSET, INDIRECT, NOW และ TODAY เป็นต้น และก็ 3 ฟังก์ชันด้านบนด้วย
⚠️ error ในกรณีจะเกิดเป็นบางครั้ง ไม่ทุกครั้งที่มีการคำนวณ หรือเป็น ๆ หาย ๆ
หากต้องใช้ไฟล์ร่วมกับคนอื่น ถ้าเครื่องเราไม่ error อย่าคาดหวังว่าเครื่องคนอื่นจะไม่ error (โดยเฉพาะเครื่องหัวหน้าเรา 😁)
เกินขอบเขต worksheet ของ Excel
ตัวอย่างดังภาพด้านล่าง สูตรในเซลล์ F1 จะเป็น =VLOOKUP(A:A,A:D,4,FALSE) ซึ่งก็ควรจะดูเฉพาะข้อมูลใน A2 แต่เพราะคุณลักษณะของ dynamic array ทำให้ผลลัพธ์ไหลลงไปจนถึงแถวที่ 1,048,576 ซึ่งเป็นแถวสุดท้ายของ Excel และอาจทำให้เกิด #SPILL! ขึ้น (⚠️เป็นบางครั้ง)
โดยมีวิธีแก้ไขดังนี้
กำหนด range ให้ชัดเจน
โดยกำหนดให้ lookup เฉพาะเจาะจงใน range ที่เราสนใจเท่านั้น เช่นสูตรในตัวอย่างควรจะเป็น
=VLOOKUP(A2:A27,A:D,4,FALSE)ไม่ระบุค่าที่จะ lookup เป็น range
หรือระบุไปเลยว่า ค่าที่การนำไปเปรียบเทียบคือ A1 แล้วใช้ auto fill แทน หรือลากลงมาเรื่อย ๆ เอา ซึ่งค่าต่อไปก็จะเป็น A2, A3, A4, …
ตัวอย่างสูตรในช่อง A2 ก็จะเป็น
=VLOOKUP(A2,A:D,4,FALSE)⚠️ กรณีนี้คือ เราจะไม่ใช้คุณสมบัติ dynamic array
ใช้ @ operator
ที่จริงแล้วผลลัพธ์ของวิธีนี้ก็ไม่ต่างจากด้านบนนัก โดยเฉพาะคุณสมบัติ dynamic array ก็จะหายไปด้วย ⚠️
โดยเป็นนำ @ (Implicit intersection) operator มาใช้เหมือนกับใน table แต่ไม่ได้อยู่ใน table โดย @ operator จะเป็นการบังคับให้ Excel ส่งค่ากลับมาเพียงค่าเดียว ไม่เอาแบบ array (รายการที่มีข้อมูลมากกว่า 1 ) ซึ่ง
- ถ้าเป็ํนค่าเดียวอยู่แล้ว ก็จะได้ค่านั้นล่ะ
- ถ้าเป็น range จะให้ค่าที่อยู่ในแถวนั้น ๆ แต่อยู่ในคอลัมภ์แรก (ที่เราระบุไว้ด้วย @) ที่สูตรของเราอยู่กลับมา
- ถ้าเป็น array จะให้ค่าที่อยู่ซ้ายสุดกลับมา
💡จะเห็นว่าทั้ง 3 วิธีด้านบน มีจุดประสงค์เดียวกันคือ เราจะใช้ค่าตั้งต้นที่จะ lookup เป็นเพียงแค่ค่าเดียว ไม่งั้น Excel จะงง และแสดง error ออกมา
Dynamic array ใช้ใน Format as Table ไม่ได้
ดังนั้นถ้าเราใส่สูตรที่ให้ค่ากลับมาเป็น dynamic array สิ่งที่ได้ก็จะเป็น #SPILL! มาแทน วิธีการก็ตือ เปลี่ยน table นั้นกลับมาเป็น range ธรรมดา โดยไปที่ Table Design > Tools > Convert to range
อย่างเช่นสูตรใน F2 จะเป็น
=SORTBY(tblEmployees[emp_id],tblEmployees[preferred_name],-1)ถ้าอยู่ใน table จะ error ดังภาพ
แต่ถ้าสูตรเดียวกัน แต่ไม่ได้อยู่ใน table ตามภาพข้างบนคืออยู่ใน I2 จะทำงานได้ปกติ
หน่วยความจำ (RAM) เต็ม
บางที array ของผลลัพธ์หรือเราไปอ้างอิง range ที่กินหน่วยความจำมากเกินไป ทำให้ Excel จะหยุดทำงาน (crash) ได้ เลยป้องกันตัวเองโดยการแสดง error ให้เราเห็นเป็น #SPILL!
วิธีแก้ไขจะเรียกว่า ไม่มี ก็ได้ คือ เราต้องพยายามอย่าไปใช้ array หรือ range ที่ทำให้หน่วยความจำเต็ม
หรือไม่ก็แก้ทาง Hardware คือ เพิ่มหน่วยความจำ แต่กรณีนี้สำหรับไฟล์ที่ใช้งานร่วมกันก็จะมีปัญหา
มีเซลล์ที่ถูกผสาน (merged) คั่นอยู่
กรณีนี้จะคล้ายกับ Excel เจอข้อมูลอยู่ใน spill range แต่เป็น cell ที่ถูก merge ซึ่ง dynamic array ไม่สนับสนุน ถึงเซลล์นั้น ๆ จะว่า Excel ก็จะไม่วางข้อมูลลงไป
ซึ่งวิธีแก้ไขก็คือ Unmerge Cells ซะ หรือไม่ก็ย้ายสูตรไปไว้ที่อื่นที่ spill range จะไม่พาดผ่านมาโดนเซลล์ที่ถุก merge ไว้
💡โดยถ้าเราเลือกเซลล์ที่สูตรอยู่จะเห็น spill range ในเส้นประสีน้ำเงินที่ Excel ทำไว้ให้อยู่แล้ว
ใส่สูตรที่ทำให้ Excel งง
กรณีนี้อาจเกิดขึ้นได้หากเราใส่สูตรที่ขาดอาร์กิวเมนต์บางตัวที่จำเป็นในการประมวล แต่ดันเลือกที่จะไม่ใส่ก็ได้เป็นต้น
หรือสูตรนั้น ๆ อาจทำให้ Excel คำนวณไม่ได้ หรือได้ค่ามาแบบงงงง
วิธีแก้ไขคือ ให้ตรวจสอบสูตรดี ๆ โดยเฉพาะการในเซลล์นั้น ๆ มีสูตรที่ใช้หลายฟังก์ชันและการคำนวณร่วมกัน แนะนำให้ใช้ Formulas > Evaluate Formula หรือไม่ก็ลองแยกสูตรใส่ในหลาย ๆ เซลล์ แล้วดูว่าผลการคำนวณในแต่ละขั้นตอนเป็นไปตามที่เรา และ Excel คาดหวังไว้หรือเปล่า
Leave a Reply