المحتويات
يشرح هذا الدرس كيفية التعامل بسرعة مع حالة وظيفة VPR (VLOOKUP) لا يريد العمل في Excel 2013 و 2010 و 2007 و 2003 ، وكيفية تحديد الأخطاء الشائعة وإصلاحها والتغلب على القيود VPR.
في العديد من المقالات السابقة ، استكشفنا الجوانب المختلفة للوظيفة VPR في Excel. إذا كنت قد قرأتها بعناية ، فيجب أن تكون الآن خبيرًا في هذا المجال. ومع ذلك ، فإنه ليس بدون سبب يعتقد العديد من خبراء Excel VPR واحدة من أكثر الميزات تعقيدًا. يحتوي على مجموعة من القيود والميزات التي أصبحت مصدرًا للعديد من المشكلات والأخطاء.
ستجد في هذه المقالة تفسيرات بسيطة للأخطاء #في (# N / A) ، # NAME؟ (#اسم و #القيمة! (#VALUE!) التي تظهر عند العمل مع الوظيفة VPRوكذلك تقنيات وطرق التعامل معها. سنبدأ بالحالات الأكثر شيوعًا والأسباب الأكثر وضوحًا وراء ذلك. VPR لا يعمل ، لذلك من الأفضل دراسة الأمثلة بالترتيب الذي وردت به في المقالة.
إصلاح خطأ # N / A في دالة VLOOKUP في Excel
في الصيغ ذات الامتداد VPR رسالة خطأ #في (# N / A) تعني غير متوفرة (لا توجد بيانات) - يظهر عندما يتعذر على Excel العثور على القيمة التي تبحث عنها. يمكن أن يحدث هذا لعدة أسباب.
1. الخطأ الإملائي في القيمة المطلوبة
فكرة جيدة للتحقق من هذا العنصر أولاً! تحدث الأخطاء الإملائية غالبًا عندما تعمل بكميات كبيرة جدًا من البيانات ، تتكون من آلاف الأسطر ، أو عندما تتم كتابة القيمة التي تبحث عنها في صيغة.
2. خطأ # N / A عند البحث عن تطابق تقريبي مع VLOOKUP
إذا كنت تستخدم صيغة مع شرط بحث مطابق تقريبي ، أي وسيطة مجموعة البحث (range_lookup) تساوي TRUE أو لم يتم تحديدها ، قد تقوم الصيغة بالإبلاغ عن خطأ # N / A في حالتين:
- القيمة المطلوب البحث عنها أقل من أصغر قيمة في المصفوفة التي يتم البحث عنها.
- لم يتم فرز عمود البحث بترتيب تصاعدي.
3. الخطأ # N / A عند البحث عن تطابق تام مع VLOOKUP
إذا كنت تبحث عن تطابق تام ، أي حجة مجموعة البحث (range_lookup) هي FALSE ولم يتم العثور على القيمة الدقيقة ، وستقوم الصيغة أيضًا بالإبلاغ عن خطأ # N / A. تعرف على المزيد حول كيفية البحث عن التطابقات الدقيقة والتقريبية مع إحدى الوظائف VPR.
4. لم يتم وضع عمود البحث في أقصى اليسار
كما تعلم على الأرجح ، أحد أهم القيود VPR إنه لا يمكن أن يكون وجهه إلى اليسار ، لذلك يجب أن يكون عمود البحث في الجدول الخاص بك في أقصى اليسار. من الناحية العملية ، غالبًا ما ننسى هذا الأمر ، مما يؤدي إلى صيغة غير فعالة وخطأ. # N / A.
القرار: إذا لم يكن من الممكن تغيير بنية البيانات بحيث يكون عمود البحث في أقصى اليسار ، يمكنك استخدام مجموعة من الوظائف INDEX (فهرس) و أكثر انكشافاً (MATCH) كبديل أكثر مرونة لـ VPR.
5. يتم تنسيق الأرقام كنص
مصدر آخر للخطأ # N / A في الصيغ مع VPR هي أرقام بتنسيق نصي في الجدول الرئيسي أو جدول البحث.
يحدث هذا عادةً عند استيراد معلومات من قواعد بيانات خارجية ، أو عند كتابة علامة اقتباس أحادية قبل رقم للاحتفاظ بالصفر البادئ.
تظهر العلامات الأكثر وضوحًا للرقم في تنسيق النص في الشكل أدناه:
بالإضافة إلى ذلك ، يمكن تخزين الأرقام بالتنسيق العلاجات العامة (عام). في هذه الحالة ، لا توجد سوى ميزة ملحوظة واحدة - تتم محاذاة الأرقام إلى الحافة اليسرى للخلية ، بينما تتم محاذاة بشكل افتراضي إلى الحافة اليمنى.
القرار: إذا كانت قيمة واحدة ، فما عليك سوى النقر فوق رمز الخطأ وتحديد قم بالتحويل إلى رقم (تحويل إلى رقم) من قائمة السياق.
إذا كان هذا هو الوضع مع العديد من الأرقام ، فحددها وانقر بزر الماوس الأيمن على المنطقة المحددة. في قائمة السياق التي تظهر ، حدد تنسيق الخلايا (تنسيق الخلايا)> علامة التبويب رقم الهاتف (رقم)> تنسيق رقم الهاتف (رقمي) والصحافة OK.
6. توجد مسافة في البداية أو في النهاية
هذا هو السبب الأقل وضوحًا للخطأ. # N / A فى مهمة VPR، نظرًا لأنه من الصعب بصريًا رؤية هذه المساحات الإضافية ، خاصة عند العمل باستخدام جداول كبيرة ، عندما تكون معظم البيانات خارج الشاشة.
الحل 1: مسافات إضافية في الجدول الرئيسي (حيث تكون وظيفة VLOOKUP)
إذا ظهرت مسافات إضافية في الجدول الرئيسي ، يمكنك التأكد من أن الصيغ تعمل بشكل صحيح من خلال إرفاق الوسيطة ابحث عن القيمة (lookup_value) في دالة TRIM (تقليم):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
الحل 2: مسافات إضافية في جدول البحث (في عمود البحث)
إذا كانت هناك مسافات إضافية في عمود البحث - طرق بسيطة # N / A في الصيغة مع VPR لا يمكن تجنبه. بدلاً من VPR يمكنك استخدام صيغة صفيف مع مجموعة من الوظائف INDEX (فهرس)، أكثر انكشافاً (مباراة) و 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))
نظرًا لأن هذه صيغة صفيف ، لا تنس الضغط على كترل + شيفت + إنتر بدلاً من المعتاد أدخللإدخال الصيغة بشكل صحيح.
خطأ #VALUE! في الصيغ باستخدام VLOOKUP
في معظم الحالات ، يُبلغ Microsoft Excel عن خطأ #القيمة! (#VALUE!) عندما لا تتطابق القيمة المستخدمة في الصيغة مع نوع البيانات. بخصوص VPR، فعادة ما يكون هناك سببان للخطأ #القيمة!.
1. القيمة التي تبحث عنها أطول من 255 حرفًا
كن حذرا: الوظيفة VPR لا يمكن البحث عن قيم تحتوي على أكثر من 255 حرفًا. إذا تجاوزت القيمة التي تبحث عنها هذا الحد ، فستتلقى رسالة خطأ. #القيمة!.
القرار: استخدم مجموعة من الميزات فهرس + تطابق (فهرس + تطابق). يوجد أدناه صيغة تعمل بشكل جيد لهذه المهمة:
=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 لن يعمل وسيبلغ عن خطأ #القيمة! (حتى إذا كان المصنف الذي يحتوي على جدول البحث مفتوحًا حاليًا).
لمزيد من المعلومات حول الوظيفة VPRبالرجوع إلى ملف Excel آخر ، راجع الدرس: البحث في مصنف آخر باستخدام VLOOKUP.
3. الوسيطة Column_num أقل من 1
من الصعب تخيل موقف يدخل فيه شخص ما قيمة أقل من 1للإشارة إلى العمود الذي يتم استخراج القيمة منه. على الرغم من أنه من الممكن إذا تم حساب قيمة هذه الوسيطة بواسطة دالة Excel أخرى متداخلة داخل VPR.
لذا ، إذا حدث أن الحجة col_index_num (العمود رقم) أقل من 1وظيفة VPR سيبلغ أيضًا عن خطأ #القيمة!.
إذا كانت الحجة col_index_num (العمود رقم) أكبر من عدد الأعمدة في المصفوفة المحددة ، VPR سيبلغ عن خطأ #REF! (#SSYL!).
خطأ #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 تُرجع القيمة من العمود المحدد المقابل لأول تطابق تم العثور عليه. ومع ذلك ، يمكنك أن تجعله يستخرج التكرار الثاني أو الثالث أو الرابع أو أي تكرار آخر للقيمة التي تريدها. إذا كنت بحاجة إلى استخراج جميع القيم المكررة ، فستحتاج إلى مجموعة من الوظائف INDEX (فهرس)، الأقل (صغيرة و خط (صف).
3. تمت إضافة عمود أو إزالته من الجدول
لسوء الحظ ، الصيغ VPR التوقف عن العمل في كل مرة يتم فيها إضافة عمود جديد أو إزالته من جدول البحث. يحدث هذا بسبب بناء الجملة VPR يتطلب منك تحديد النطاق الكامل للبحث ورقم العمود المحدد لاستخراج البيانات. بطبيعة الحال ، يتغير كل من النطاق المحدد ورقم العمود عند حذف عمود أو إدراج عمود جديد.
القرار: ومرة أخرى ، فإن الوظائف في عجلة من أمرها للمساعدة INDEX (فهرس) و أكثر انكشافاً (مباراة). في الصيغة فهرس + تطابق أنت تحدد بشكل منفصل أعمدة البحث والاسترجاع ، ونتيجة لذلك ، يمكنك حذف أو إدراج أي عدد تريده من الأعمدة دون القلق بشأن الاضطرار إلى تحديث جميع صيغ البحث ذات الصلة.
4. تكون مراجع الخلايا مشوشة عند نسخ صيغة
هذا العنوان يشرح جوهر المشكلة بشكل شامل ، أليس كذلك؟
القرار: استخدم دائمًا مراجع الخلايا المطلقة (مع الرمز $) في سجلات النطاق ، على سبيل المثال 2 دولار أسترالي: 100 دولارات كندية or $ أ: $ ج. في شريط الصيغة ، يمكنك التبديل بسرعة بين نوع الارتباط بالنقر فوق F4.
VLOOKUP - العمل مع الدالتين IFERROR و ISERROR
إذا كنت لا تريد إخافة المستخدمين برسائل خطأ # N / A, #القيمة! or # NAME؟، يمكنك إظهار خلية فارغة أو رسالتك الخاصة. يمكنك القيام بذلك عن طريق وضع VPR في وظيفة خطأ مرجّح (IFERROR) في Excel 2013 و 2010 و 2007 أو استخدام مجموعة من الوظائف إذا + ISERROR (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 (إذا) و يوشيبكا (ISERROR) مثل هذا:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
على سبيل المثال ، الصيغة إذا + ISERROR + VLOOKUP، على غرار الصيغة IFERROR + 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 وجعل الصيغ تعمل بشكل صحيح.