สูตรอาร์เรย์ที่สปิลล์ที่คุณพยายามใส่จะขยายเกินช่วงของเวิร์กชีต ลองอีกครั้งด้วยช่วงหรืออาร์เรย์ที่เล็กลง
ในตัวอย่างต่อไปนี้ การย้ายสูตรไปยังเซลล์ F1 จะแก้ไขข้อผิดพลาด และสูตรจะสปิลล์อย่างถูกต้อง
สาเหตุทั่วไป: การอ้างอิงคอลัมน์แบบเต็ม
มีวิธีการสร้างสูตร VLOOKUP ที่เข้าใจผิดบ่อยโดยการระบุอาร์กิวเมนต์ lookup_value ก่อน Excel ที่สามารถทํา อาร์เรย์แบบไดนามิก ได้ Excel จะพิจารณาเฉพาะค่าบนแถวเดียวกันกับสูตรและละเว้นค่าอื่นๆ เนื่องจาก VLOOKUP ต้องการเพียงค่าเดียวเท่านั้น ด้วยการแนะนําอาร์เรย์แบบไดนามิก Excel จะพิจารณาค่าทั้งหมดที่ให้มากับ lookup_value ซึ่งหมายความว่า ถ้าทั้งคอลัมน์ถูกกําหนดให้เป็นอาร์กิวเมนต์ lookup_value Excel จะพยายามค้นหาค่า 1,048,576 ค่าทั้งหมดในคอลัมน์ เมื่อเสร็จสิ้นมันจะพยายามหกใส่ลงในกริดและมีแนวโน้มที่จะตีปลายกริดส่งผลให้เกิด #SPILL! ข้อผิดพลาด
ตัวอย่างเช่น เมื่อวางในเซลล์ E2 ดังตัวอย่างด้านล่าง สูตร =VLOOKUP(A:A,A:C,2,FALSE) จะค้นหาเฉพาะ ID ในเซลล์ A2 เท่านั้น อย่างไรก็ตาม ในอาร์เรย์แบบไดนามิก Excel สูตรจะทําให้เกิด #SPILL! เนื่องจาก Excel จะค้นหาทั้งคอลัมน์ ให้ส่งกลับผลลัพธ์ 1,048,576 รายการ แล้วกดจุดสิ้นสุดของเส้นตาราง Excel
มี 3 วิธีง่ายๆ ในการแก้ไขปัญหานี้:
| # | วิธี | สูตร |
|---|---|---|
| 1 | อ้างอิงเฉพาะค่าการค้นหาที่คุณสนใจ สูตรลักษณะนี้จะส่งกลับอาร์เรย์แบบไดนามิก แต่จะไม่ทํางานกับตาราง Excel
|
=VLOOKUP(A2:A7,A:C,2,FALSE) |
| 2 | อ้างอิงเฉพาะค่าบนแถวเดียวกัน แล้วคัดลอกสูตรลงมา สไตล์สูตรแบบดั้งเดิมนี้ใช้ได้ในตาราง แต่จะไม่ส่งกลับอาร์เรย์แบบไดนามิก
|
=VLOOKUP(A2,A:C,2,FALSE) |
| 3 | ขอให้ Excel ดําเนินการอินเทอร์เซกชันโดยนัยโดยใช้ตัวดําเนินการ @ แล้วคัดลอกสูตรลงมา รูปแบบของสูตรนี้ใช้ได้ในตาราง แต่จะไม่ส่งกลับอาร์เรย์แบบไดนามิก
|
=VLOOKUP(@A:A,A:C,2,FALSE) |
ต้องการความช่วยเหลือเพิ่มเติมไหม
คุณสามารถสอบถามผู้เชี่ยวชาญใน ชุมชนด้านเทคนิคของ Excel หรือรับการสนับสนุนใน ชุมชนได้เสมอ