المحتويات
يشرح هذا البرنامج التعليمي الصغير كيفية عمل وظيفة VPR (VLOOKUP) حساس لحالة الأحرف ، يعرض العديد من الصيغ الأخرى التي يمكن لبرنامج Excel البحث عنها بطريقة حساسة لحالة الأحرف ، ويشير إلى نقاط القوة والضعف في كل وظيفة.
أعتقد أن كل مستخدم Excel يعرف الوظيفة التي تؤدي البحث العمودي. هذا صحيح ، إنها وظيفة VPR. ومع ذلك ، قلة من الناس يعرفون ذلك VPR ليست حساسة لحالة الأحرف ، أي أن الأحرف الصغيرة والكبيرة متطابقة بالنسبة لها.
هنا مثال سريع يوضح عدم القدرة VPR التعرف على التسجيل. افترض في خلية A1 يحتوي على القيمة "الفاتورة" والخلية A2 - "بيل" ، الصيغة:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... سيوقف البحث عن "bill" لأن هذه القيمة تأتي أولاً في القائمة ، وتستخرج القيمة من الخلية B1.
لاحقًا في هذه المقالة ، سأوضح لك كيفية القيام بذلك VPR حساسية الموضوع. بالإضافة إلى ذلك ، سنتعلم بعض الوظائف الإضافية التي يمكنها إجراء عمليات بحث حساسة لحالة الأحرف في Excel.
سنبدأ بأبسط - أنظر إلى (LOOKUP) و SUMPRODUCT (SUMPRODUCT) ، والتي ، للأسف ، لها العديد من القيود المهمة. بعد ذلك ، سوف نلقي نظرة فاحصة على الصيغة الأكثر تعقيدًا فهرس + تطابق (INDEX + MATCH) ، والذي يعمل بشكل لا تشوبه شائبة في أي موقف ومع أي مجموعة بيانات.
وظيفة VLOOKUP حساسة لحالة الأحرف
كما تعلمون بالفعل ، الوظيفة المعتادة VPR غير حساس لحالة الأحرف. ومع ذلك ، هناك طريقة لجعلها حساسة لحالة الأحرف. للقيام بذلك ، تحتاج إلى إضافة عمود إضافي إلى الجدول ، كما هو موضح في المثال التالي.
افترض في عمود B هناك معرّفات منتجات (صنف) وتريد استخراج سعر المنتج والتعليق المقابل من الأعمدة C и D. المشكلة هي أن المعرفات تحتوي على أحرف صغيرة وكبيرة. على سبيل المثال ، قيم الخلية B4 (001Tvci3u) و B5 (001Tvci3U) تختلف فقط في حالة الحرف الأخير ، u и U على التوالي.
كما يمكنك أن تتخيل ، صيغة البحث المعتادة
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
سيعود $ 90 ، منذ القيمة 001Tvci3u يقع في نطاق البحث قبل 001Tvci3U. لكن هذا ليس ما نحتاجه ، أليس كذلك؟
للبحث مع وظيفة VPR في Excel الحساسة لحالة الأحرف ، سيتعين عليك إضافة عمود مساعد وملء خلاياه بالصيغة التالية (حيث B هو عمود البحث):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
تقسم هذه الصيغة القيمة المطلوبة إلى أحرف منفصلة ، وتستبدل كل حرف برمزه (على سبيل المثال ، بدلاً من A في 65 ، بدلا من ذلك a كود 97) ثم يدمج هذه الرموز في سلسلة فريدة من الأرقام.
بعد ذلك ، نستخدم وظيفة بسيطة VPR للبحث الحساس لحالة الأحرف:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
التشغيل السليم للوظيفة VPR تعتمد الحساسية لحالة الأحرف على عاملين:
- يجب أن يكون عمود المساعد هو العمود الموجود في أقصى اليسار في النطاق القابل للعرض.
- يجب أن تحتوي القيمة التي تبحث عنها على رمز حرف بدلاً من القيمة الحقيقية.
كيفية استخدام وظيفة CODE بشكل صحيح
تفترض الصيغة التي تم إدراجها في خلايا العمود الإضافي أن جميع قيم البحث لها نفس عدد الأحرف. إذا لم يكن الأمر كذلك ، فأنت بحاجة إلى معرفة الأرقام الأصغر والأكبر وإضافة العديد من الميزات خطأ مرجّح (IFERROR) كم عدد الأحرف هو الفرق بين أقصر وأطول قيمة تم البحث عنها.
على سبيل المثال ، إذا كانت أقصر قيمة بحث هي 3 أحرف والأطول هو 5 أحرف ، فاستخدم هذه الصيغة:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
للوظيفة بستر (MID) أنت تقدم الوسيطات التالية:
- الحجة الأولى - نص (النص) هو النص أو مرجع الخلية الذي يحتوي على الأحرف المراد استخراجها (في حالتنا هو B2)
- الحجة الأولى - start_num (start_position) هو موضع أول تلك الأحرف المراد استخلاصها. تدخل 1 في الوظيفة الأولى بستر, 2 - في الوظيفة الثانية بستر وما إلى ذلك.
- الحجة الأولى - num_chars (number_of_characters) - يحدد عدد الأحرف المراد استخلاصها من النص. نظرًا لأننا نحتاج فقط إلى حرف واحد في كل وقت ، فإننا نكتب في جميع الوظائف 1.
القيود: المسمى الوظيفي VPR ليس هو الحل الأفضل لعمليات البحث الحساسة لحالة الأحرف في Excel. أولاً ، يلزم إضافة عمود إضافي. ثانيًا ، تقوم الصيغة بعمل جيد فقط إذا كانت البيانات متجانسة ، أو إذا كان العدد الدقيق للأحرف في القيم التي تم البحث عنها معروفًا. إذا لم تكن هذه هي حالتك ، فمن الأفضل استخدام أحد الحلول التي نعرضها أدناه.
وظيفة LOOKUP للبحث الحساس لحالة الأحرف
المسمى الوظيفي أنظر إلى (LOOKUP) ذات الصلة VPR، ومع ذلك ، فإن تركيبته تسمح بإجراء عمليات بحث حساسة لحالة الأحرف دون إضافة عمود إضافي. للقيام بذلك ، استخدم أنظر إلى جنبا إلى جنب مع الوظيفة EXACT (دقيق).
إذا أخذنا البيانات من المثال السابق (بدون عمود إضافي) ، فستتعامل الصيغة التالية مع المهمة:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
عمليات البحث عن الصيغة في النطاق أكسنومك: أكسنومك تطابق تام مع قيمة الخلية F2 حساس لحالة الأحرف وإرجاع القيمة من العمود B للصف نفسه.
اعجاب VPRوظيفة أنظر إلى يعمل بالتساوي مع القيم النصية والرقمية ، كما ترى في لقطة الشاشة أدناه:
المهم! من أجل الوظيفة أنظر إلى بشكل صحيح ، يجب فرز القيم الموجودة في عمود البحث بترتيب تصاعدي ، أي من الأصغر إلى الأكبر.
اسمحوا لي أن أشرح بإيجاز كيف تعمل الوظيفة EXACT في الصيغة الموضحة أعلاه ، فهذه هي النقطة الأساسية.
المسمى الوظيفي EXACT يقارن القيمتين النصيتين في الوسيطتين الأولى والثانية ويعيد القيمة TRUE إذا كانتا متطابقتين تمامًا ، أو FALSE إذا لم تكن كذلك. من المهم بالنسبة لنا أن الوظيفة EXACT حساسية الموضوع.
دعونا نرى كيف تعمل الصيغة عرض + بالضبط:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- المسمى الوظيفي EXACT يقارن قيمة الخلية F2 مع كل العناصر في عمود A (A2: A7). إرجاع TRUE إذا تم العثور على تطابق تام ، وإلا FALSE.
- منذ أن أعطيت وسيطة الوظيفة الأولى أنظر إلى القيمة TRUE ، تستخرج القيمة المقابلة من العمود المحدد (في حالتنا ، العمود B) فقط إذا تم العثور على تطابق تام ، حساس لحالة الأحرف.
أتمنى أن يكون هذا التفسير واضحًا والآن أنت تفهم الفكرة الرئيسية. إذا كان الأمر كذلك ، فلن تواجه أي صعوبات مع الوظائف الأخرى التي سنقوم بتحليلها بشكل أكبر ، لأن. كلهم يعملون على نفس المبدأ.
القيود: يجب فرز البيانات الموجودة في عمود البحث بترتيب تصاعدي.
SUMPRODUCT - يبحث عن قيم نصية ، حساسة لحالة الأحرف ، لكنه يعرض أرقامًا فقط
كما فهمت بالفعل من العنوان ، SUMPRODUCT (SUMPRODUCT) هي وظيفة أخرى في Excel ستساعدك على إجراء بحث حساس لحالة الأحرف ، ولكنها ستعيد القيم الرقمية فقط. إذا كان هذا الخيار لا يناسبك ، فيمكنك المتابعة على الفور إلى الحزمة فهرس + تطابق، مما يعطي حلاً لأية حالة ولأي نوع من أنواع البيانات.
أولاً ، اسمحوا لي أن أشرح بإيجاز بناء جملة هذه الوظيفة ، فهذا سيساعدك على فهم الصيغة التالية لحالة الأحرف بشكل أفضل.
المسمى الوظيفي SUMPRODUCT يضاعف عناصر المصفوفات المحددة ويعيد مجموع النتائج. يبدو بناء الجملة كما يلي:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
نظرًا لأننا نحتاج إلى بحث حساس لحالة الأحرف ، فإننا نستخدم الوظيفة EXACT (دقيق) من المثال السابق كأحد المضاعفات:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
كما تتذكر، EXACT يقارن قيمة الخلية F2 مع كل العناصر في عمود A. إرجاع TRUE إذا تم العثور على تطابق تام ، وإلا FALSE. في العمليات الحسابية ، يأخذ Excel TRUE كـ 1و FALSE لـ 0إضافي SUMPRODUCT يضرب هذه الأرقام ويجمع النتائج.
لا تحسب الأصفار لأنها تعطي دائمًا عند ضربها 0. دعنا نلقي نظرة فاحصة على ما يحدث عند وجود تطابق تام في عمود A وجدت وعاد 1… دور SUMPRODUCT يضاعف الرقم الموجود في العمود B on 1 وإرجاع النتيجة - نفس الرقم بالضبط! هذا لأن نتائج المنتجات الأخرى صفر ، ولا تؤثر على المجموع الناتج.
للأسف الوظيفة SUMPRODUCT لا يمكن العمل مع القيم النصية والتواريخ حيث لا يمكن مضاعفتها. في هذه الحالة ، سوف تتلقى رسالة خطأ #القيمة! (#VALUE!) كما في خلية F4 في الصورة أدناه:
القيود: ترجع القيم الرقمية فقط.
INDEX + MATCH - بحث حساس لحالة الأحرف عن أي نوع بيانات
أخيرًا ، نحن قريبون من صيغة بحث غير محدودة وحساسة لحالة الأحرف تعمل مع أي مجموعة بيانات.
يأتي هذا المثال أخيرًا ، ليس لأن الأفضل يُترك للحلوى ، ولكن لأن المعرفة المكتسبة من الأمثلة السابقة ستساعدك على فهم الصيغة الحساسة لحالة الأحرف بشكل أفضل وأسرع. فهرس + تطابق (فهرس + تطابق).
كما خمنت على الأرجح ، مجموعة الوظائف أكثر انكشافاً и INDEX يستخدم في Excel كبديل أكثر مرونة وقوة لـ VPR. ستشرح المقالة استخدام INDEX و MATCH بدلاً من VLOOKUP تمامًا كيفية عمل هاتين الدالتين معًا.
سألخص فقط النقاط الرئيسية:
- المسمى الوظيفي أكثر انكشافاً (MATCH) تبحث عن قيمة في نطاق معين وترجع موضعها النسبي ، أي الصف و / أو رقم العمود ؛
- بعد ذلك ، الوظيفة INDEX تُرجع الدالة (INDEX) قيمة من عمود و / أو صف محدد.
للصيغة فهرس + تطابق يمكن البحث بشكل حساس لحالة الأحرف ، ما عليك سوى إضافة وظيفة واحدة إليه. ليس من الصعب تخمين ما هو عليه مرة أخرى EXACT (دقيق):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
في هذه الصيغة EXACT يعمل بنفس الطريقة مع الوظيفة أنظر إلىوتعطي نفس النتيجة:
لاحظ أن الصيغة فهرس + تطابق محاطة بأقواس متعرجة هي صيغة صفيف ويجب عليك إكمالها بالضغط كترل + شيفت + إنتر.
لماذا يعتبر INDEX + MATCH أفضل حل للبحث الحساس لحالة الأحرف؟
المزايا الرئيسية للحزمة INDEX и أكثر انكشافاً:
- لا يتطلب اضافة عمود مساعد بعكس VPR.
- لا يتطلب فرز عمود البحث على عكس أنظر إلى.
- يعمل مع جميع أنواع البيانات - أرقام ونصوص وتواريخ.
هذه الصيغة تبدو مثالية ، أليس كذلك؟ في الواقع ، ليس كذلك. وهذا هو السبب.
افترض أن الخلية الموجودة في عمود قيمة الإرجاع المرتبط بقيمة البحث فارغة. ما النتيجة التي ستعود بها الصيغة؟ رقم؟ دعونا نرى ما تعيده الصيغة بالفعل:
عفوًا ، ترجع الصيغة صفرًا! قد لا تكون هذه مشكلة كبيرة إذا كنت تعمل بقيم نصية خالصة. ومع ذلك ، إذا كان الجدول يحتوي على أرقام ، بما في ذلك الأصفار "الحقيقية" ، تصبح هذه مشكلة.
في الواقع ، تتصرف جميع صيغ البحث الأخرى (VLOOKUP و LOOKUP و SUMPRODUCT) التي ناقشناها سابقًا بنفس الطريقة. لكنك تريد الصيغة المثالية ، أليس كذلك؟
لجعل الصيغة حساسة لحالة الأحرف فهرس + تطابق ممتاز ، ضعه في وظيفة IF (IF) التي ستختبر خلية ذات قيمة إرجاع وإرجاع نتيجة فارغة إذا كانت فارغة:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
في هذه الصيغة:
- B هو عمود بقيم الإرجاع
- 1+ هو رقم يحول الموضع النسبي للخلية التي ترجعها الوظيفة أكثر انكشافاً، إلى العنوان الحقيقي للخلية. على سبيل المثال ، في وظيفتنا أكثر انكشافاً مجموعة البحث معينة أكسنومك: أكسنومك، أي الموضع النسبي للخلية A2 سوف 1، لأنها الأولى في المصفوفة. لكن الموقع الفعلي للخلية A2 في العمود 2، لذلك نضيف 1لتعويض الاختلاف والحصول على الوظيفة غير مباشر (غير مباشر) استرد القيمة من الخلية المرغوبة.
توضح الصور أدناه الصيغة المصححة الحساسة لحالة الأحرف فهرس + تطابق في العمل. تقوم بإرجاع نتيجة فارغة إذا كانت الخلية التي تم إرجاعها فارغة.
أعدت كتابة الصيغة في أعمدة ب: دلملاءمة شريط الصيغة في لقطة الشاشة.
إرجاع الصيغة 0إذا كانت الخلية التي تم إرجاعها تحتوي على صفر.
إذا كنت تريد الارتباط INDEX и أكثر انكشافاً عرض بعض الرسائل عندما تكون قيمة الإرجاع فارغة ، يمكنك كتابتها في آخر علامات الاقتباس ("") من الصيغة ، على سبيل المثال ، مثل هذا:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
