วิธีการแก้ไขข้อผิดพลาด #N/A

โดยทั่วไป ข้อผิดพลาด #N/A จะระบุว่าสูตรไม่พบสิ่งที่ขอให้ค้นหา

วิธีแก้ไขยอดนิยม

สาเหตุที่พบบ่อยที่สุดของข้อผิดพลาด #N/A จะเกี่ยวกับฟังก์ชัน XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP หรือ MATCH ถ้าสูตรไม่สามารถค้นหาค่าอ้างอิงได้ ตัวอย่างเช่น ค่าการค้นหาของคุณไม่มีอยู่ในข้อมูลต้นฉบับ

ไม่มีค่าค้นหาอยู่ สูตรในเซลล์ E2 คือ =VLOOKUP(D2,$D$6:$E$8,2,FALSE) ไม่พบค่า Banana ดังนั้น สูตรจะส่งกลับข้อผิดพลาด #N/A

ในกรณีนี้ จะไม่มี “Banana” แสดงอยู่ในตารางค้นหา ดังนั้น VLOOKUP จะส่งกลับคข้อผิดพลาด #N/A

วิธีแก้ไข: ตรวจสอบให้แน่ใจว่าค่าการค้นหาที่อยู่ในข้อมูลต้นฉบับ หรือใช้ตัวจัดการข้อผิดพลาด เช่น IFERROR ในสูตร ตัวอย่างเช่น =IFERROR(FORMULA(),0) ที่ระบุว่า:

=IF(สูตรของคุณประเมินเป็นข้อผิดพลาด แล้วแสดง 0 มิฉะนั้น จะแสดงผลลัพธ์ของสูตร)

คุณสามารถใช้ “” เพื่อไม่แสดงสิ่งใดเลย หรือแทนที่ข้อความของคุณเอง: =IFERROR(FORMULA(),”ข้อความแสดงข้อผิดพลาดที่นี่”)

ถ้าคุณต้องการความช่วยเหลือเกี่ยวกับข้อผิดพลาด #N/A กับฟังก์ชันเฉพาะ เช่น VLOOKUP หรือ INDEX/MATCH ให้เลือกหนึ่งในตัวอย่างต่อไปนี้:
นอกจากนี้ การเรียนรู้เกี่ยวกับฟังก์ชันทั่วไปบางส่วนที่เกิดข้อผิดพลาดนี้อาจเป็นประโยชน์ เช่น XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP หรือ MATCH

หากคุณไม่แน่ใจว่าต้องทําอะไรในตอนนี้หรือต้องการความช่วยเหลือแบบใด คุณสามารถค้นหาคําถามที่คล้ายกันใน Microsoft Community หรือโพสต์คําถามของคุณเอง

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

ค่าการค้นหาและแหล่งข้อมูลคือชนิดข้อมูลที่ต่างกัน ตัวอย่างเช่น คุณพยายามที่จะใส่การอ้างอิง VLOOKUP เป็นตัวเลข แต่ข้อมูลต้นฉบับถูกจัดเก็บเป็นข้อความ

ชนิดของค่าไม่ถูกต้อง ตัวอย่างแสดงสูตร VLOOKUP ที่ส่งกลับข้อผิดพลาด #N/A เนื่องจากรายการค้นหาถูกจัดรูปแบบเป็ฯตัวเลข แต่ตารางค้นหาถูกจัดรูปแบบเป็นข้อความ

วิธีแก้ไข: ตรวจสอบให้แน่ใจว่าชนิดข้อมูลเหมือนกัน คุณสามารถตรวจสอบรูปแบบเซลล์โดยการเลือกเซลล์หรือช่วงของเซลล์ แล้วคลิกขวาและเลือก จัดรูปแบบเซลล์ > ตัวเลข (หรือกด Ctrl+1) และเปลี่ยนรูปแบบตัวเลขถ้าจำเป็น

กล่องโต้ตอบจัดรูปแบบเซลล์กำลังแสดงแท็บตัวเลขและเลือกตัวเลือกข้อความไว้

: ถ้าคุณต้องการบังคับการเปลี่ยนแปลงรูปแบบของทั้งคอลัมน์ ก่อนอื่น ให้นำรูปแบบที่คุณต้องการไปใช้ จากนั้น คุณจะสามารถใช้ ข้อมูล > แปลงข้อความเป็นคอลัมน์ > เสร็จสิ้น

คุณสามารถใช้ฟังก์ชัน ตัดแต่ง เพื่อนำช่องว่างนำหน้าหรือต่อท้ายออก ตัวอย่างต่อไปนี้ใช้การตัดแต่งแบบซ้อนกันภายในฟังก์ชัน VLOOKUP เพื่อนำช่องว่างนำหน้าออกจากชื่อใน A2:A7 แล้วส่งกลับชื่อแผนก

=VLOOKUP(D2,TRIM(A2:B7),2,FALSE)

: สูตรอาร์เรย์แบบไดนามิก - ถ้าคุณมีเวอร์ชันปัจจุบันของ Microsoft 365 และอยู่ในช่องทางการวางจําหน่าย Insider Fast คุณสามารถใส่สูตรในเซลล์ด้านบนซ้ายของช่วงผลลัพธ์ จากนั้นกด Enter เพื่อยืนยันสูตรเป็นสูตรอาร์เรย์แบบไดนามิก มิฉะนั้น ต้องใส่สูตรเป็นสูตรอาร์เรย์ดั้งเดิม โดยเลือกช่วงผลลัพธ์ ป้อนสูตรในเซลล์ด้านบนซ้ายของช่วงผลลัพธ์ แล้วกด Ctrl + Shift + Enter เพื่อยืนยัน Excel จะแทรกวงเล็บปีกกาที่จุดเริ่มต้นและส่วนท้ายของสูตรให้คุณ สำหรับข้อมูลเพิ่มเติมเกี่ยวกับสูตรอาร์เรย์ ดูที่ คำแนะนำและตัวอย่างของสูตรอาร์เรย์

ตามค่าเริ่มต้น ฟังก์ชันที่ค้นหาข้อมูลในตารางต้องถูกเรียงลำดับตามลำดับน้อยไปหามาก อย่างไรก็ตาม ฟังก์ชันเวิร์กชีต VLOOKUP และ HLOOKUP จะมีอาร์กิวเมนต์ range_lookup ที่แนะนำฟังก์ชันในการค้นหาค่าที่ตรงกัน แม้ว่าจะไม่ได้จัดเรียงตาราง เมื่อต้องการค้นหาค่าที่ตรงกัน ให้ตั้งค่าอาร์กิวเมนต์ range_lookup เป็น FALSE โปรดทราบว่าการใช้ TRUE ที่บอกให้ฟังก์ชันค้นหาค่าที่ตรงกันโดยประมาณ นอกจากอาจทำให้ได้ผลลัพธ์ #N/A แล้ว ยังอาจส่งกลับผลลัพธ์ที่ผิดพลาดตามที่เห็นในตัวอย่างต่อไปนี้

ตัวอย่างของการใช้ VLOOKUP กับอาร์กิวเมนต์ TRUE range_lookup สามารถทำให้เกิดผลลัพธ์ที่ผิดพลาด

ในตัวอย่างนี้ ไม่เพียง “Banana” ส่งกลับข้อผิดพลาด #N/A แต่ “Pear” ยังส่งกลับราคาที่ไม่ถูกต้อง ซึ่งเกิดขึ้นโดยการใช้อาร์กิวเมนต์ TRUE ที่บอกให้ VLOOKUP ค้นหาค่าที่ตรงกันโดยประมาณแทนที่จะเป็นค่าที่ตรงกัน ไม่มีค่าที่ใกล้เคียงกับ “Banana” และ “Pear” ที่มาก่อน “Peach” ตามลำดับตัวอักษร ในกรณีนี้ การใช้ VLOOKUP กับอาร์กิวเมนต์ FALSE อาจส่งกลับราคาที่ถูกต้องสำหรับ “Pear” แต่ “Banana” ยังคงมีผลลัพธ์เป็นข้อผิดพลาด #N/A เนื่องจากไม่มี “Banana” ที่สอดคล้องกันในรายการค้นหา

ถ้าคุณกำลังใช้ฟังก์ชัน MATCH ให้ลองเปลี่ยนค่าของอาร์กิวเมนต์ match_type เพื่อระบุลำดับการจัดเรียงของตาราง เมื่อต้องการหาข้อมูลที่ตรงกันทุกประการ ให้ตั้งค่าอาร์กิวเมนต์ match_type เป็น 0 (ศูนย์)

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

ในตัวอย่างนี้ เซลล์ E2 มีช่วงการอ้างอิงที่ไม่ตรงกัน:

=SUM(IF(A2:A11=D2,B2:B5))

เมื่อต้องการให้สูตรคำนวณอย่างถูกต้อง จะต้องเปลี่ยนแปลงเพื่อให้ทั้งสองช่วงแสดงแถว 2 – 11

=SUM(IF(A2:A11=D2,B2:B11))

ตัวอย่างของ #N/A ที่ใส่ลงในเซลล์ ที่ป้องกันไม่ให้คำนวณสูตร SUM ได้อย่างถูกต้อง

ในกรณีนี้ พฤษภาคม-ธันวาคมมีค่า #N/A ดังนั้น จึงไม่สามารถคำนวณผลรวมได้และส่งกลับข้อผิดพลาด #N/A

เมื่อต้องการแก้ไขปัญหานี้ ให้ตรวจสอบไวยากรณ์ของสูตรของฟังก์ชันที่คุณกำลังใช้ และใส่อาร์กิวเมนต์ที่จำเป็นทั้งหมดในสูตรที่ส่งกลับข้อผิดพลาด ซึ่งอาจจำเป็นต้องไปที่ Visual Basic Editor (VBE) เพื่อตรวจสอบฟังก์ชัน คุณสามารถเข้าถึง VBE ได้จากแท็บนักพัฒนา หรือด้วยการกด ALT+F11

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

เมื่อต้องการแก้ไขปัญหานี้ ให้ตรวจสอบว่าอาร์กิวเมนต์ในฟังก์ชันนั้นถูกต้อง และถูกใช้ในตำแหน่งที่ถูกต้อง

เมื่อต้องการแก้ไขปัญหานี้ ให้กด Alt+Ctrl+F9 เพื่อคำนวณแผ่นงานใหม่

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

Excel จะโหลดตัวช่วยสร้างให้คุณโดยอัตโนมัติ:

เมื่อคุณคลิกแต่ละอาร์กิวเมนต์ Excel จะให้ข้อมูลที่เหมาะสมสำหรับแต่ละรายการ

#N/A อาจมีประโยชน์! เป็นหลักปฏิบัติทั่วไปที่จะใช้ #N/A เมื่อใช้ข้อมูลที่เหมือนกับตัวอย่างต่อไปนี้สำหรับแผนภูมิ เนื่องจากค่า #N/A จะไม่ลงจุดบนแผนภูมิ นี่คือตัวอย่างของลักษณะแผนภูมิที่มีค่า 0 และ #N/A

ในตัวอย่างก่อนหน้า คุณจะเห็นว่าค่า 0 มีการลงจุดและแสดงเป็นเส้นแนวราบทางด้านล่างของแผนภูมิ และจากนั้นจะแสดงผลรวม ในตัวอย่างต่อไปนี้ คุณจะเห็นค่า 0 ถูกแทนที่ด้วย #N/A