เนื้อหา
บทเรียนนี้อธิบายวิธีจัดการกับสถานการณ์อย่างรวดเร็วโดยฟังก์ชัน VPR (VLOOKUP) ไม่ต้องการทำงานใน Excel 2013, 2010, 2007 และ 2003 และวิธีระบุและแก้ไขข้อผิดพลาดทั่วไปและเอาชนะข้อจำกัด VPR.
ในบทความก่อนหน้านี้ เราได้สำรวจแง่มุมต่างๆ ของฟังก์ชันแล้ว VPR ใน Excel หากคุณได้อ่านอย่างระมัดระวัง ตอนนี้คุณควรเป็นผู้เชี่ยวชาญในสาขานี้ อย่างไรก็ตาม ผู้เชี่ยวชาญ Excel หลายคนก็เชื่อโดยไม่มีเหตุผล VPR หนึ่งในคุณสมบัติที่ซับซ้อนมากขึ้น มันมีข้อจำกัดและคุณสมบัติมากมายที่เป็นต้นเหตุของปัญหาและข้อผิดพลาดมากมาย
ในบทความนี้ คุณจะพบคำอธิบายง่ายๆ เกี่ยวกับข้อผิดพลาด #ที่ (#ไม่มี), # NAME? (#NAME?) และ #VALUE! (#VALUE!) ที่ปรากฏขึ้นเมื่อทำงานกับฟังก์ชัน VPRตลอดจนเทคนิคและวิธีการจัดการกับมัน เราจะเริ่มต้นด้วยกรณีที่พบบ่อยที่สุดและเหตุผลที่ชัดเจนที่สุดว่าทำไม VPR ใช้งานไม่ได้ดังนั้นจึงควรศึกษาตัวอย่างตามลำดับที่ระบุไว้ในบทความ
แก้ไขข้อผิดพลาด #N/A ในฟังก์ชัน VLOOKUP ใน Excel
ในสูตรด้วย VPR ข้อความข้อผิดพลาด #ที่ (#N/A) หมายถึง ไม่สามารถใช้ได้ (ไม่มีข้อมูล) – ปรากฏขึ้นเมื่อ Excel ไม่พบค่าที่คุณต้องการ สิ่งนี้สามารถเกิดขึ้นได้จากหลายสาเหตุ
1. ค่าที่ต้องการสะกดผิด
เป็นความคิดที่ดีที่จะตรวจสอบรายการนี้ก่อน! การพิมพ์ผิดมักเกิดขึ้นเมื่อคุณทำงานกับข้อมูลจำนวนมาก ซึ่งประกอบด้วยหลายพันบรรทัด หรือเมื่อค่าที่คุณกำลังมองหาถูกเขียนลงในสูตร
2. ข้อผิดพลาด #N/A เมื่อค้นหาการจับคู่โดยประมาณกับ VLOOKUP
หากคุณใช้สูตรที่มีเงื่อนไขการค้นหาที่ตรงกันโดยประมาณ เช่น ข้อโต้แย้ง range_lookup (range_lookup) เป็น TRUE หรือไม่ได้ระบุ สูตรของคุณอาจรายงานข้อผิดพลาด # N / A ในสองกรณี:
- ค่าที่จะค้นหานั้นน้อยกว่าค่าที่น้อยที่สุดในอาร์เรย์ที่กำลังค้นหา
- คอลัมน์การค้นหาไม่ได้เรียงลำดับจากน้อยไปมาก
3. #N/A ข้อผิดพลาดเมื่อค้นหาการจับคู่แบบตรงทั้งหมดกับ VLOOKUP
หากคุณกำลังมองหาการจับคู่แบบตรงทั้งหมด เช่น อาร์กิวเมนต์ range_lookup (range_lookup) เป็น FALSE และไม่พบค่าที่แน่นอน สูตรจะรายงานข้อผิดพลาดด้วย # N / A. เรียนรู้เพิ่มเติมเกี่ยวกับวิธีการค้นหาการจับคู่แบบตรงทั้งหมดและโดยประมาณด้วยฟังก์ชัน VPR.
4. ค้นหาคอลัมน์ไม่ซ้ายสุด
อย่างที่คุณอาจทราบ ข้อจำกัดที่สำคัญที่สุดประการหนึ่ง VPR เนื่องจากไม่สามารถหันหน้าไปทางซ้ายได้ ดังนั้นคอลัมน์ค้นหาในตารางของคุณจึงต้องอยู่ซ้ายสุด ในทางปฏิบัติ เรามักจะลืมเรื่องนี้ไป ซึ่งนำไปสู่สูตรที่ไม่ทำงานและมีข้อผิดพลาด # N / A.
การตัดสินใจ: หากไม่สามารถเปลี่ยนโครงสร้างข้อมูลเพื่อให้คอลัมน์ค้นหาอยู่ซ้ายสุด คุณสามารถใช้ฟังก์ชันต่างๆ ร่วมกันได้ ดัชนี (ดัชนี) และ เปิดเผยมากขึ้น (MATCH) เป็นทางเลือกที่ยืดหยุ่นกว่าสำหรับ VPR.
5. ตัวเลขถูกจัดรูปแบบเป็นข้อความ
แหล่งที่มาของข้อผิดพลาดอื่น # N / A ในสูตรด้วย VPR เป็นตัวเลขในรูปแบบข้อความในตารางหลักหรือตารางค้นหา
ซึ่งมักจะเกิดขึ้นเมื่อคุณนำเข้าข้อมูลจากฐานข้อมูลภายนอก หรือเมื่อคุณพิมพ์เครื่องหมายอะพอสทรอฟีก่อนตัวเลขเพื่อเก็บศูนย์นำหน้า
เครื่องหมายที่ชัดเจนที่สุดของตัวเลขในรูปแบบข้อความแสดงในรูปด้านล่าง:
นอกจากนี้ยังสามารถจัดเก็บตัวเลขในรูปแบบ General (ทั่วไป). ในกรณีนี้ มีเพียงคุณลักษณะเดียวที่เห็นได้ชัดเจน คือ ตัวเลขจะจัดชิดขอบด้านซ้ายของเซลล์ ขณะที่โดยค่าเริ่มต้น ตัวเลขจะจัดชิดขอบด้านขวา
การตัดสินใจ: หากเป็นค่าเดียว ให้คลิกที่ไอคอนข้อผิดพลาดแล้วเลือก แปลงเป็นตัวเลข (แปลงเป็นตัวเลข) จากเมนูบริบท
หากเป็นสถานการณ์ที่มีตัวเลขจำนวนมาก ให้เลือกและคลิกขวาที่พื้นที่ที่เลือก ในเมนูบริบทที่ปรากฏขึ้น ให้เลือก จัดรูปแบบเซลล์ (จัดรูปแบบเซลล์) > tab จำนวน (ตัวเลข) > รูปแบบ จำนวน (ตัวเลข) แล้วกด OK.
6. มีช่องว่างตอนต้นหรือตอนท้าย
นี่เป็นสาเหตุที่ชัดเจนน้อยที่สุดสำหรับข้อผิดพลาด # N / A ในการทำงาน VPRเนื่องจากเป็นการยากที่จะมองเห็นช่องว่างเพิ่มเติมเหล่านี้ โดยเฉพาะอย่างยิ่งเมื่อทำงานกับตารางขนาดใหญ่ เมื่อข้อมูลส่วนใหญ่อยู่นอกหน้าจอ
โซลูชันที่ 1: ช่องว่างเพิ่มเติมในตารางหลัก (โดยที่ฟังก์ชัน VLOOKUP คือ)
หากมีการเว้นวรรคเพิ่มเติมในตารางหลัก คุณสามารถมั่นใจได้ว่าสูตรทำงานได้อย่างถูกต้องโดยใส่อาร์กิวเมนต์ lookup_value (lookup_value) ลงในฟังก์ชัน TRIM (ทริม):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
โซลูชันที่ 2: ช่องว่างเพิ่มเติมในตารางค้นหา (ในคอลัมน์ค้นหา)
หากมีช่องว่างเพิ่มเติมในคอลัมน์ค้นหา – วิธีง่ายๆ # N / A ในสูตรด้วย VPR ไม่สามารถหลีกเลี่ยงได้ แทน VPR คุณสามารถใช้สูตรอาร์เรย์ร่วมกับฟังก์ชันต่างๆ ได้ ดัชนี (ดัชนี), เปิดเผยมากขึ้น (MATCH) หรือ TRIM (ทริม):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
เนื่องจากเป็นสูตรอาร์เรย์ อย่าลืมกด Ctrl + Shift + Enter แทนปกติ เข้าสู่เพื่อป้อนสูตรให้ถูกต้อง
เกิดข้อผิดพลาด #VALUE! ในสูตรที่มี VLOOKUP
ในกรณีส่วนใหญ่ Microsoft Excel จะรายงานข้อผิดพลาด #VALUE! (#VALUE!) เมื่อค่าที่ใช้ในสูตรไม่ตรงกับชนิดข้อมูล เกี่ยวกับ VPRมักมีสาเหตุสองประการที่ทำให้เกิดข้อผิดพลาด #VALUE!.
1. ค่าที่คุณกำลังค้นหายาวเกิน 255 ตัวอักษร
ระวัง: ฟังก์ชัน VPR ไม่สามารถค้นหาค่าที่มีอักขระเกิน 255 ตัว หากค่าที่คุณต้องการเกินขีดจำกัดนี้ คุณจะได้รับข้อความแสดงข้อผิดพลาด #VALUE!.
การตัดสินใจ: ใช้คุณสมบัติมากมาย ดัชนี + การจับคู่ (ดัชนี + การแข่งขัน). ด้านล่างนี้เป็นสูตรที่จะทำงานได้ดีสำหรับงานนี้:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. ไม่ได้ระบุเส้นทางแบบเต็มไปยังสมุดงานการค้นหา
หากคุณกำลังดึงข้อมูลจากเวิร์กบุ๊กอื่น คุณต้องระบุพาธแบบเต็มไปยังไฟล์นั้น โดยเฉพาะอย่างยิ่ง คุณต้องใส่ชื่อเวิร์กบุ๊ก (รวมถึงส่วนขยาย) ในวงเล็บเหลี่ยม [ ] ตามด้วยชื่อแผ่นงาน ตามด้วยเครื่องหมายอัศเจรีย์ โครงสร้างทั้งหมดนี้ต้องอยู่ในเครื่องหมายอะพอสทรอฟี ในกรณีที่ชื่อหนังสือหรือแผ่นงานมีช่องว่าง
นี่คือโครงสร้างที่สมบูรณ์ของฟังก์ชัน VPR เพื่อค้นหาในหนังสือเล่มอื่น:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
สูตรจริงอาจมีลักษณะดังนี้:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
สูตรนี้จะค้นหาค่าของเซลล์ A2 ในคอลัมน์ B บนแผ่น Sheet1 ในสมุดงาน ราคาใหม่ และดึงค่าที่สอดคล้องกันออกจากคอลัมน์ D.
หากละเว้นส่วนใดส่วนหนึ่งของเส้นทางของตาราง ฟังก์ชันของคุณ VPR จะไม่ทำงานและจะรายงานข้อผิดพลาด #VALUE! (แม้ว่าเวิร์กบุ๊กที่มีตารางค้นหาจะเปิดอยู่ก็ตาม)
สำหรับข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชัน VPRอ้างอิงไฟล์ Excel อื่น ดูบทเรียน: ค้นหาสมุดงานอื่นโดยใช้ VLOOKUP
3. อาร์กิวเมนต์ Column_num น้อยกว่า 1
ยากที่จะจินตนาการถึงสถานการณ์ที่มีคนป้อนค่าน้อยกว่า 1เพื่อระบุคอลัมน์ที่จะดึงค่าออกมา แม้ว่าจะเป็นไปได้ถ้าค่าของอาร์กิวเมนต์นี้คำนวณโดยฟังก์ชัน Excel อื่นที่ซ้อนกันอยู่ภายใน VPR.
ดังนั้นถ้าเกิดว่าข้อโต้แย้ง col_index_num (column_number) น้อยกว่า 1ฟังก์ชัน VPR จะรายงานข้อผิดพลาดด้วย #VALUE!.
ถ้าข้อโต้แย้ง col_index_num (column_number) มากกว่าจำนวนคอลัมน์ในอาร์เรย์ที่กำหนด VPR จะรายงานข้อผิดพลาด #REF! (#เอสซิล!).
เกิดข้อผิดพลาด #NAME? ใน VLOOKUP
กรณีที่ง่ายที่สุดคือความผิดพลาด # NAME? (#NAME?) – จะปรากฏขึ้นหากคุณเขียนชื่อฟังก์ชันโดยไม่ได้ตั้งใจโดยมีข้อผิดพลาด
การแก้ปัญหานั้นชัดเจน – ตรวจสอบการสะกดคำของคุณ!
VLOOKUP ไม่ทำงาน (ข้อจำกัด คำเตือน และการตัดสินใจ)
นอกจากรูปแบบที่ค่อนข้างซับซ้อนแล้ว VPR มีข้อจำกัดมากกว่าฟังก์ชัน Excel อื่นๆ เนื่องจากข้อจำกัดเหล่านี้ ดูเหมือนสูตรง่าย ๆ กับ VPR มักจะนำไปสู่ผลลัพธ์ที่ไม่คาดคิด ด้านล่างนี้คุณจะพบวิธีแก้ปัญหาสำหรับสถานการณ์ทั่วไปหลายประการที่ VPR มันผิด.
1. VLOOKUP ไม่คำนึงถึงขนาดตัวพิมพ์
ฟังก์ชัน VPR ไม่แยกความแตกต่างระหว่างตัวพิมพ์เล็กและตัวพิมพ์ใหญ่และตัวพิมพ์เล็กและตัวพิมพ์ใหญ่เหมือนกัน ดังนั้น หากมีหลายองค์ประกอบในตารางที่แตกต่างกันเฉพาะในกรณีที่ ฟังก์ชัน VLOOKUP จะคืนค่าองค์ประกอบแรกที่พบ โดยไม่คำนึงถึงกรณี
การตัดสินใจ: ใช้ฟังก์ชัน Excel อื่นที่สามารถทำการค้นหาแนวตั้ง (LOOKUP, SUMPRODUCT, INDEX และ MATCH) ร่วมกับ EXACTA ที่แยกแยะกรณี สำหรับรายละเอียดเพิ่มเติม คุณสามารถเรียนรู้จากบทเรียน - 4 วิธีในการทำให้ VLOOKUP พิจารณาตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ใน Excel
2. VLOOKUP ส่งคืนค่าแรกที่พบ
อย่างที่ทราบกันดีอยู่แล้วว่า VPR ส่งคืนค่าจากคอลัมน์ที่กำหนดซึ่งตรงกับการจับคู่แรกที่พบ อย่างไรก็ตาม คุณสามารถแยกค่าที่ 2, 3, 4 หรือการทำซ้ำอื่นๆ ของค่าที่คุณต้องการได้ หากคุณต้องการแยกค่าที่ซ้ำกันทั้งหมด คุณจะต้องใช้ฟังก์ชันร่วมกัน ดัชนี (ดัชนี), น้อยที่สุด (เล็ก) และ สาย (แถว).
3. เพิ่มหรือลบคอลัมน์ในตาราง
เสียดายสูตร VPR หยุดทำงานทุกครั้งที่มีการเพิ่มหรือลบคอลัมน์ใหม่ลงในตารางค้นหา สิ่งนี้เกิดขึ้นเนื่องจากไวยากรณ์ VPR คุณต้องระบุช่วงทั้งหมดของการค้นหาและหมายเลขคอลัมน์เฉพาะสำหรับการดึงข้อมูล โดยธรรมชาติแล้ว ทั้งช่วงที่กำหนดและหมายเลขคอลัมน์จะเปลี่ยนไปเมื่อคุณลบคอลัมน์หรือแทรกคอลัมน์ใหม่
การตัดสินใจ: และอีกครั้งที่ฟังก์ชั่นกำลังรีบเข้าไปช่วย ดัชนี (ดัชนี) และ เปิดเผยมากขึ้น (การแข่งขัน). ในสูตร ดัชนี + การจับคู่ คุณกำหนดคอลัมน์การค้นหาและดึงข้อมูลแยกกัน ด้วยเหตุนี้ คุณจึงสามารถลบหรือแทรกคอลัมน์ได้มากเท่าที่คุณต้องการโดยไม่ต้องกังวลว่าจะต้องอัปเดตสูตรการค้นหาที่เกี่ยวข้องทั้งหมด
4. การอ้างอิงเซลล์จะอ่านไม่ออกเมื่อคัดลอกสูตร
หัวข้อนี้อธิบายสาระสำคัญของปัญหาอย่างละเอียดถี่ถ้วนใช่ไหม
การตัดสินใจ: ใช้การอ้างอิงเซลล์แบบสัมบูรณ์เสมอ (พร้อมสัญลักษณ์ $) ในบันทึกช่วงเช่น $ ก $ 2: $ C $ 100 or $ A: $ C. ในแถบสูตร คุณสามารถเปลี่ยนประเภทลิงก์ได้อย่างรวดเร็วโดยคลิก F4.
VLOOKUP – ทำงานกับฟังก์ชัน IFERROR และ ISERROR
หากคุณไม่ต้องการทำให้ผู้ใช้ตกใจด้วยข้อความแสดงข้อผิดพลาด # N / A, #VALUE! or # NAME?คุณสามารถแสดงเซลล์ว่างหรือข้อความของคุณเองได้ คุณสามารถทำได้โดยวาง VPR เป็นฟังก์ชัน การอ้างอิง (IFERROR) ใน Excel 2013, 2010 และ 2007 หรือใช้ฟังก์ชันมากมาย IF+SERROR (IF+ISERROR) ในเวอร์ชันก่อนหน้า
VLOOKUP: การทำงานกับฟังก์ชัน IFERROR
ไวยากรณ์ฟังก์ชัน การอ้างอิง (IFERROR) เป็นเรื่องง่ายและพูดเพื่อตัวเอง:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
นั่นคือ สำหรับอาร์กิวเมนต์แรก คุณใส่ค่าที่จะตรวจสอบข้อผิดพลาด และสำหรับอาร์กิวเมนต์ที่สอง คุณระบุสิ่งที่จะส่งกลับหากพบข้อผิดพลาด
ตัวอย่างเช่น สูตรนี้จะคืนค่าเซลล์ว่างหากไม่พบค่าที่คุณต้องการ:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
หากคุณต้องการแสดงข้อความของคุณเองแทนข้อความแสดงข้อผิดพลาดมาตรฐานของฟังก์ชัน VPRให้ใส่เครื่องหมายคำพูดดังนี้
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: การทำงานกับฟังก์ชัน ISERROR
ตั้งแต่หน้าที่ การอ้างอิง ปรากฏใน Excel 2007 เมื่อทำงานในเวอร์ชันก่อนหน้า คุณจะต้องใช้ชุดค่าผสม IF (IF) และ อีโอชิบก้า (ISERROR) เช่นนี้
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
ตัวอย่างเช่น สูตร ถ้า+ISERROR+VLOOKUPคล้ายกับสูตร ตัวอ้างอิง+VLOOKUPแสดงด้านบน:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
นั่นคือทั้งหมดสำหรับวันนี้ ฉันหวังว่าบทช่วยสอนสั้นๆ นี้จะช่วยคุณจัดการกับข้อผิดพลาดที่อาจเกิดขึ้นทั้งหมด VPR และทำให้สูตรของคุณทำงานได้อย่างถูกต้อง