สูตรอาร์เรย์ที่สปิลล์ซึ่งคุณกําลังพยายามใส่จะขยายเกินช่วงของเวิร์กชีต ลองอีกครั้งกับช่วงหรืออาร์เรย์ที่เล็กลง
ในตัวอย่างต่อไปนี้ การย้ายสูตรไปยังเซลล์ 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 หรือรับการสนับสนุนใน ชุมชนได้เสมอ