บทความนี้จะช่วยให้คุณเข้าใจสาเหตุทั้งหมดของข้อผิดพลาด #SPILL รวมถึงวิธีแก้ไขใน Excel 365
#หก! เป็นข้อผิดพลาดของ Excel ชนิดใหม่ที่ส่วนใหญ่เกิดขึ้นเมื่อสูตรที่สร้างผลลัพธ์การคำนวณหลายรายการพยายามแสดงผลลัพธ์ในช่วงการรั่วไหล แต่ช่วงนั้นมีข้อมูลอื่นอยู่แล้ว
ข้อมูลการบล็อกอาจเป็นอะไรก็ได้ เช่น ค่าข้อความ เซลล์ที่ผสาน อักขระช่องว่าง หรือแม้แต่เมื่อไม่มีที่เพียงพอสำหรับส่งคืนผลลัพธ์ วิธีแก้ปัญหานั้นง่าย ไม่ว่าจะล้างช่วงของข้อมูลการบล็อกหรือเลือกอาร์เรย์ว่างของเซลล์ที่ไม่มีข้อมูลประเภทใดเลย
ข้อผิดพลาดการรั่วไหลมักเกิดขึ้นเมื่อคำนวณสูตรอาร์เรย์แบบไดนามิก เนื่องจากสูตรอาร์เรย์แบบไดนามิกเป็นสูตรที่ส่งออกผลลัพธ์เป็นหลายเซลล์หรืออาร์เรย์ มาดูรายละเอียดเพิ่มเติมและทำความเข้าใจสิ่งที่ทำให้เกิดข้อผิดพลาดนี้ใน Excel และวิธีแก้ไข
อะไรทำให้เกิดข้อผิดพลาดการรั่วไหล?
ตั้งแต่การเปิดตัวอาร์เรย์ไดนามิกในปี 2018 สูตรของ Excel สามารถจัดการค่าได้หลายค่าในคราวเดียวและส่งคืนผลลัพธ์ในเซลล์มากกว่าหนึ่งเซลล์ อาร์เรย์แบบไดนามิกคืออาร์เรย์ที่ปรับขนาดได้ซึ่งอนุญาตให้สูตรส่งกลับผลลัพธ์หลายรายการไปยังช่วงของเซลล์บนเวิร์กชีตตามสูตรที่ป้อนในเซลล์เดียว
เมื่อสูตรอาร์เรย์แบบไดนามิกส่งกลับผลลัพธ์หลายรายการ ผลลัพธ์เหล่านี้จะกระจายไปยังเซลล์ข้างเคียงโดยอัตโนมัติ ลักษณะการทำงานนี้เรียกว่า 'การรั่วไหล' ใน Excel และช่วงของเซลล์ที่ผลลัพธ์ออกมาเรียกว่า 'ระยะการหก' ช่วงการรั่วไหลจะขยายหรือหดตัวโดยอัตโนมัติตามค่าแหล่งที่มา
หากสูตรพยายามเติมช่วงการหกด้วยผลลัพธ์หลายรายการ แต่ถูกบล็อกโดยบางสิ่งในช่วงนั้น จะเกิดข้อผิดพลาด #SPILL
ตอนนี้ Excel มี 9 ฟังก์ชันที่ใช้ฟังก์ชัน Dynamic Array เพื่อแก้ปัญหา ซึ่งรวมถึง:
- ลำดับ
- กรอง
- ขนย้าย
- เรียงลำดับ
- เรียงลำดับ
- รันดาร์เรย์
- มีเอกลักษณ์
- XLOOKUP
- XMATCH
สูตรอาร์เรย์แบบไดนามิกมีเฉพาะใน 'Excel 365' และขณะนี้ยังไม่ได้รับการสนับสนุนโดยซอฟต์แวร์ Excel ออฟไลน์ใดๆ (เช่น Microsoft Excel 2016, 2019)
ข้อผิดพลาดจากการรั่วไหลไม่ได้เกิดจากการขัดขวางข้อมูลเท่านั้น แต่ยังมีหลายสาเหตุที่ทำให้คุณได้รับ #ข้อผิดพลาดจากการหกรั่วไหล ให้เราสำรวจสถานการณ์ต่างๆ ที่คุณอาจพบกับ #SPILL! ข้อผิดพลาดและวิธีแก้ไข
ระยะการรั่วไหลไม่ว่างเปล่า
สาเหตุหลักประการหนึ่งของข้อผิดพลาดในการหกคือช่วงการหกไม่ว่างเปล่า ตัวอย่างเช่น หากคุณกำลังพยายามแสดงผลลัพธ์ 10 รายการ แต่ถ้ามีข้อมูลใด ๆ ในเซลล์ใด ๆ ในพื้นที่การหก สูตรจะส่งกลับ #SPILL! ข้อผิดพลาด.
ตัวอย่างที่ 1:
ในตัวอย่างด้านล่าง เราได้ป้อนฟังก์ชัน TRANSPOSE ในเซลล์ C2 เพื่อแปลงช่วงแนวตั้งของเซลล์ (B2:B5) เป็นช่วงแนวนอน (C2:F2) แทนที่จะเปลี่ยนคอลัมน์เป็นแถว Excel จะแสดง #SPILL! ข้อผิดพลาด.
และเมื่อคุณคลิกที่เซลล์สูตร คุณจะเห็นเส้นขอบสีน้ำเงินประซึ่งระบุพื้นที่/ช่วงการรั่วไหล (C2:F2) ที่จำเป็นในการแสดงผลลัพธ์ดังที่แสดงด้านล่าง นอกจากนี้ คุณจะสังเกตเห็นป้ายเตือนสีเหลืองพร้อมเครื่องหมายอัศเจรีย์
เพื่อทำความเข้าใจสาเหตุของข้อผิดพลาด ให้คลิกไอคอนคำเตือนที่อยู่ถัดจากข้อผิดพลาด และดูข้อความในบรรทัดแรกที่ไฮไลต์เป็นสีเทา อย่างที่คุณเห็น มันบอกว่า 'ระยะการรั่วไหลไม่ว่างเปล่า' ที่นี่
ปัญหาที่นี่คือเซลล์ในช่วงการรั่วไหลของ D2 และ E2 มีอักขระข้อความ (ไม่ว่างเปล่า) ดังนั้นจึงเกิดข้อผิดพลาด
สารละลาย:
วิธีแก้ปัญหานั้นง่าย ไม่ว่าจะล้างข้อมูล (ไม่ว่าจะย้ายหรือลบ) ที่อยู่ในช่วงการรั่วไหล หรือย้ายสูตรไปยังตำแหน่งอื่นที่ไม่มีสิ่งกีดขวาง
ทันทีที่คุณลบหรือย้ายการบล็อก Excel จะเติมข้อมูลในเซลล์ด้วยผลลัพธ์ของสูตรโดยอัตโนมัติ ที่นี่ เมื่อเราล้างข้อความใน D2 และ E2 สูตรจะสลับคอลัมน์เป็นแถวตามที่ตั้งใจไว้
ตัวอย่างที่ 2:
ในตัวอย่างด้านล่าง แม้ว่าช่วงการรั่วไหลจะว่างเปล่า แต่สูตรยังคงแสดงการหกรั่วไหล! ข้อผิดพลาด. เป็นเพราะการรั่วไหลไม่ได้ว่างเปล่าจริง ๆ มันมีช่องว่างที่มองไม่เห็นในเซลล์ใดเซลล์หนึ่ง
เป็นการยากที่จะระบุตำแหน่งของอักขระช่องว่างหรืออักขระที่มองไม่เห็นอื่น ๆ ที่ซ่อนอยู่ในสิ่งที่ดูเหมือนจะเป็นเซลล์ว่าง หากต้องการค้นหาเซลล์ดังกล่าวที่มีข้อมูลที่ไม่ต้องการ ให้คลิก Error floatie (สัญญาณเตือน) และเลือก 'Select Obstructing Cells' จากเมนู แล้วระบบจะนำคุณไปยังเซลล์ที่มีข้อมูลที่ขัดขวาง
ดังที่คุณเห็นในภาพหน้าจอด้านล่าง เซลล์ E2 มีอักขระเว้นวรรคสองตัว เมื่อคุณล้างข้อมูลเหล่านั้น คุณจะได้ผลลัพธ์ที่เหมาะสม
บางครั้ง อักขระที่มองไม่เห็นอาจเป็นข้อความที่จัดรูปแบบด้วยสีฟอนต์เดียวกันกับสีเติมของเซลล์หรือค่าเซลล์ที่กำหนดรูปแบบด้วยรหัสตัวเลข ;;; เมื่อคุณกำหนดรูปแบบค่าในเซลล์เองด้วย ;;; จะซ่อนทุกอย่างในเซลล์นั้น โดยไม่คำนึงถึงสีแบบอักษรหรือสีของเซลล์
ช่วงการรั่วไหลประกอบด้วยเซลล์ที่ผสาน
บางครั้ง #SPILL! ข้อผิดพลาดเกิดขึ้นเมื่อช่วงการรั่วไหลประกอบด้วยเซลล์ที่ผสาน สูตรอาร์เรย์แบบไดนามิกใช้ไม่ได้กับเซลล์ที่ผสาน ในการแก้ไขปัญหานี้ สิ่งที่คุณต้องทำคือยกเลิกการผสานเซลล์ในช่วงการหกหรือย้ายสูตรไปยังช่วงอื่นที่ไม่มีเซลล์ที่ผสาน
ในตัวอย่างด้านล่าง แม้ว่าช่วงการรั่วไหลจะว่างเปล่า (C2:CC8) สูตรจะส่งกลับข้อผิดพลาดการรั่วไหล เป็นเพราะเซลล์ C4 และ C5 ถูกผสานเข้าด้วยกัน
เพื่อให้แน่ใจว่าเซลล์ที่ผสานเป็นสาเหตุที่ทำให้คุณได้รับข้อผิดพลาด ให้คลิกที่สัญญาณเตือนและตรวจสอบสาเหตุ – 'ช่วงการรั่วไหลได้รวมเซลล์แล้ว'
สารละลาย:
หากต้องการยกเลิกการผสานเซลล์ ให้เลือกเซลล์ที่ผสาน จากนั้นบนแท็บ "หน้าแรก" ให้คลิกปุ่ม "ผสาน & ศูนย์" แล้วเลือก "ยกเลิกการผสานเซลล์"
หากคุณมีปัญหาในการค้นหาเซลล์ที่ผสานในสเปรดชีตขนาดใหญ่ของคุณ ให้คลิกที่ตัวเลือก 'เลือกสิ่งกีดขวางเซลล์' จากเมนูป้ายเตือนเพื่อข้ามไปยังเซลล์ที่ผสาน
ช่วงการรั่วไหลในตาราง
สูตรอาร์เรย์ที่หกไม่ได้รับการสนับสนุนในตาราง Excel ควรป้อนสูตรอาร์เรย์แบบไดนามิกในเซลล์เดียว ถ้าคุณป้อนสูตรอาร์เรย์ที่หกในตารางหรือเมื่อพื้นที่การหกตกลงไปในตาราง คุณจะได้รับข้อผิดพลาดการรั่วไหล เมื่อสิ่งนี้เกิดขึ้น ให้ลองแปลงตารางเป็นช่วงปกติหรือย้ายสูตรออกไปนอกตาราง
ตัวอย่างเช่น เมื่อเราป้อนสูตรช่วงที่หกต่อไปนี้ในตาราง Excel เราจะได้รับข้อผิดพลาดการรั่วไหลในทุกเซลล์ของตาราง ไม่ใช่แค่เซลล์สูตร เป็นเพราะ Excel จะคัดลอกสูตรที่ป้อนในตารางไปยังทุกเซลล์ในคอลัมน์ของตารางโดยอัตโนมัติ
นอกจากนี้ คุณจะได้รับข้อผิดพลาดในการหกเมื่อสูตรพยายามกระจายผลลัพธ์ในตาราง ในภาพหน้าจอด้านล่าง พื้นที่การรั่วไหลอยู่ในตารางที่มีอยู่ ดังนั้นเราจึงได้รับข้อผิดพลาดการรั่วไหล
เพื่อยืนยันสาเหตุเบื้องหลังข้อผิดพลาดนี้ ให้คลิกที่สัญญาณเตือนและดูสาเหตุของข้อผิดพลาด - 'ช่วงการหกในตาราง'
สารละลาย:
ในการแก้ไขข้อผิดพลาด คุณจะต้องเปลี่ยนตาราง Excel กลับเป็นช่วง ในการทำเช่นนั้น ให้คลิกขวาที่ใดก็ได้ภายในตาราง คลิก 'ตาราง' จากนั้นเลือกตัวเลือก 'แปลงเป็นช่วง' หรือคุณสามารถคลิกซ้ายที่ใดก็ได้ภายในตาราง จากนั้นไปที่แท็บ 'การออกแบบตาราง' และเลือกตัวเลือก 'แปลงเป็นช่วง'
ไม่ทราบระยะการรั่วไหล
ถ้า Excel ไม่สามารถกำหนดขนาดของอาร์เรย์ที่หกได้ จะทำให้เกิดข้อผิดพลาดเกี่ยวกับการรั่วไหล ในบางครั้ง สูตรนี้ช่วยให้อาร์เรย์ไดนามิกปรับขนาดระหว่างการคำนวณแต่ละครั้งได้ หากขนาดของอาร์เรย์ไดนามิกเปลี่ยนแปลงตลอดเวลาระหว่างการคำนวณผ่านและไม่สมดุล จะทำให้เกิด #SPILL! ข้อผิดพลาด.
ข้อผิดพลาดเกี่ยวกับการรั่วไหลประเภทนี้มักเกิดขึ้นเมื่อใช้ฟังก์ชันระเหย เช่น RAND, RANDARRAY, RANDBETWEEN, OFFSET และ INDIRECT
ตัวอย่างเช่น เมื่อเราใช้สูตรด้านล่างในเซลล์ B3 เราจะได้รับข้อผิดพลาดของการรั่วไหล:
=ลำดับ(RANDBETWEEN(1, 500))ในตัวอย่าง ฟังก์ชัน RANDBETWEEN จะคืนค่าจำนวนเต็มแบบสุ่มระหว่างตัวเลข 1 ถึง 500 และผลลัพธ์จะเปลี่ยนแปลงอย่างต่อเนื่อง และฟังก์ชัน SEQUENCE ไม่ทราบจำนวนค่าที่จะสร้างในอาร์เรย์การหก ดังนั้น ข้อผิดพลาด #SPILL
คุณยังสามารถยืนยันสาเหตุของข้อผิดพลาดได้โดยคลิกที่ป้ายเตือน - 'ไม่ทราบช่วงการรั่วไหล'
สารละลาย:
ในการแก้ไขข้อผิดพลาดสำหรับสูตรนี้ ทางเลือกเดียวของคุณคือใช้สูตรอื่นในการคำนวณของคุณ
ระยะการรั่วไหลใหญ่เกินไป
ในบางครั้ง คุณอาจใช้สูตรที่แสดงช่วงที่ล้นออกมาซึ่งใหญ่เกินกว่าที่เวิร์กชีตจะจัดการได้ และอาจขยายเกินขอบของเวิร์กชีต เมื่อสิ่งนั้นเกิดขึ้น คุณอาจได้รับ #SPILL! ข้อผิดพลาด. ในการแก้ไขปัญหานี้ คุณสามารถลองอ้างอิงช่วงที่ระบุหรือเซลล์หนึ่งเซลล์แทนทั้งคอลัมน์ หรือใช้อักขระ '@' เพื่อเปิดใช้งานจุดตัดโดยนัย
ในตัวอย่างด้านล่าง เรากำลังพยายามคำนวณ 20% ของยอดขายในคอลัมน์ A และส่งคืนผลลัพธ์ในคอลัมน์ B แต่กลับได้รับข้อผิดพลาดจากการหกรั่วไหล
สูตรใน B3 จะคำนวณ 20% ของค่าใน A3 จากนั้น 20% ของค่าใน A4 เป็นต้น มันสร้างผลลัพธ์มากกว่าหนึ่งล้านรายการ (1,048,576) และกระจายผลลัพธ์ทั้งหมดในคอลัมน์ B โดยเริ่มต้นในเซลล์ B3 แต่จะถึงจุดสิ้นสุดของเวิร์กชีต มีพื้นที่ไม่เพียงพอสำหรับแสดงผลทั้งหมด ดังนั้นเราจึงได้รับข้อผิดพลาด #SPILL
ดังที่คุณเห็นสาเหตุของข้อผิดพลาดนี้คือ - 'ระยะการรั่วไหลใหญ่เกินไป'
โซลูชั่น:
ในการแก้ปัญหานี้ ลองเปลี่ยนทั้งคอลัมน์ด้วยช่วงที่เกี่ยวข้องหรือการอ้างอิงเซลล์เดียว หรือเพิ่มตัวดำเนินการ @ เพื่อทำการแยกโดยนัย
แก้ไข 1: คุณสามารถลองอ้างอิงช่วงมากกว่าทั้งคอลัมน์ได้ ที่นี่ เราเปลี่ยนช่วงทั้งหมด A:A ด้วย A3:A11 ในสูตร และสูตรจะเติมผลลัพธ์ให้กับช่วงโดยอัตโนมัติ
แก้ไข 2: แทนที่ทั้งคอลัมน์ด้วยการอ้างอิงเซลล์ในแถวเดียวกัน (A3) จากนั้นคัดลอกสูตรลงไปที่ช่วงโดยใช้จุดจับเติม
แก้ไข 3: คุณยังสามารถลองเพิ่มตัวดำเนินการ @ ก่อนการอ้างอิงเพื่อทำการแยกโดยปริยาย ซึ่งจะแสดงผลลัพธ์ในเซลล์สูตรเท่านั้น
จากนั้นคัดลอกสูตรจากเซลล์ B3 ไปยังช่วงที่เหลือ
บันทึก: เมื่อคุณแก้ไขสูตรที่หก คุณจะแก้ไขได้เฉพาะเซลล์แรกในพื้นที่/ช่วงการหก คุณสามารถดูสูตรในเซลล์อื่นๆ ของช่วงการรั่วไหลได้ แต่สูตรดังกล่าวจะเป็นสีเทาและไม่สามารถอัปเดตได้
ความจำเต็ม
หากคุณใช้สูตรอาร์เรย์ที่หกซึ่งทำให้ Excel มีหน่วยความจำไม่เพียงพอ อาจทำให้เกิดข้อผิดพลาด #SPILL ภายใต้สถานการณ์ดังกล่าว ให้ลองอ้างอิงอาร์เรย์หรือช่วงที่เล็กกว่า
ไม่รู้จัก / ทางเลือกอื่น
คุณยังสามารถได้รับข้อผิดพลาดเกี่ยวกับการรั่วไหล แม้ว่า Excel จะไม่รู้จักหรือไม่สามารถประนีประนอมสาเหตุของข้อผิดพลาดได้ ในกรณีเช่นนี้ ให้ตรวจสอบสูตรของคุณอีกครั้งและตรวจดูให้แน่ใจว่าพารามิเตอร์ทั้งหมดของฟังก์ชันถูกต้อง
ตอนนี้ คุณรู้สาเหตุและวิธีแก้ปัญหาทั้งหมดสำหรับ #SPILL แล้ว! ข้อผิดพลาดใน Excel 365