วิธีการแก้ไขข้อผิดพลาด #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

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

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

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

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

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

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

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

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

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

หมายเหตุ: 24 กันยายน 2018 - สูตรอาร์เรย์แบบไดนามิก - ถ้าคุณมี Microsoft 365 เวอร์ชันปัจจุบันและอยู่ในแชนเนลการเผยแพร่ Insider ที่เร็วก่อนใคร คุณจะสามารถป้อนสูตรในเซลล์ด้านบนซ้ายของช่วงผลลัพธ์ แล้วกด 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 ค้นหาค่าที่ตรงกันโดยประมาณแทนค่าที่ตรงกันพอดี ไม่มีการจับคู่ที่ใกล้เคียงสําหรับ "กล้วย" และ "ลูกแพร์" มาก่อนตัวอักษร "พีช" ในกรณีนี้ การใช้ 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 เพื่อคำนวณแผ่นงานใหม่

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

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

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

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

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