انتقل إلى المحتوى الرئيسي

20+ من أمثلة VLOOKUP لمستخدمي Excel للمبتدئين والمتقدمين

تعد وظيفة VLOOKUP واحدة من أكثر الوظائف شيوعًا في Excel. سيقدم هذا البرنامج التعليمي كيفية استخدام وظيفة VLOOKUP في Excel مع عشرات الأمثلة الأساسية والمتقدمة خطوة بخطوة.


مقدمة عن وظيفة VLOOKUP - النحو والحجج

في Excel ، تعد وظيفة VLOOKUP وظيفة قوية لمعظم مستخدمي Excel ، فهي تتيح لك البحث عن قيمة في أقصى يسار نطاق البيانات ، وإرجاع قيمة مطابقة في نفس الصف من العمود الذي حددته كما هو موضح في لقطة الشاشة التالية .

صيغة دالة VLOOKUP:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

الحجج:

ابحث عن القيمة (مطلوب): القيمة التي تريد البحث عنها. يمكن أن تكون قيمة (رقم أو تاريخ أو نص) أو مرجع خلية. يجب أن يكون في العمود الأول من نطاق table_array. 

Table_array (مطلوب): نطاق البيانات أو الجدول حيث يوجد عمود قيمة البحث وعمود قيمة النتيجة.

Col_index_num (مطلوب): رقم العمود الذي يحتوي على قيم الإرجاع. يبدأ بـ 1 من العمود الموجود في أقصى اليسار في صفيف الجدول.

مجموعة البحث (اختياري): قيمة منطقية تحدد ما إذا كانت وظيفة VLOOKUP هذه ستعرض مطابقة تامة أو مطابقة تقريبية.

  • تطابق تقريبي - 1 / TRUE / محذوف (افتراضي): إذا لم يتم العثور على تطابق تام ، تبحث الصيغة عن أقرب تطابق - أكبر قيمة أصغر من قيمة البحث.
    إشعار: في هذه الحالة ، يجب عليك فرز عمود البحث (العمود الموجود في أقصى اليسار من نطاق البيانات) بترتيب تصاعدي ، وإلا فسيعرض نتيجة خطأ أو خطأ # N / A.
  • تطابق تام - 0 / خطأ: يستخدم هذا للبحث عن قيمة تساوي بالضبط قيمة البحث. إذا لم يتم العثور على تطابق تام ، سيتم إرجاع قيمة الخطأ # N / A.

ملاحظات الوظيفة:

  • تبحث وظيفة Vlookup فقط عن قيمة من اليسار إلى اليمين.
  • تقوم وظيفة Vlookup بإجراء بحث غير حساس لحالة الأحرف.
  • إذا كانت هناك قيم مطابقة متعددة تستند إلى قيمة البحث ، فسيتم إرجاع أول قيمة متطابقة فقط باستخدام وظيفة Vlookup.

أمثلة أساسية على VLOOKUP

في هذا القسم ، سنتحدث عن بعض صيغ Vlookup التي استخدمتها كثيرًا.

2.1 مطابقة تامة ومطابقة تقريبية VLOOKUP

 2.1.1 قم بعمل مطابقة تامة لـ VLOOKUP

عادةً ، إذا كنت تبحث عن تطابق تام مع وظيفة VLOOKUP ، فأنت تحتاج فقط إلى استخدام FALSE كوسيطة أخيرة.

على سبيل المثال ، للحصول على درجات الرياضيات المقابلة بناءً على أرقام التعريف المحددة ، يرجى القيام بذلك على النحو التالي:

يرجى نسخ الصيغة أدناه ولصقها في خلية فارغة (هنا ، أحدد G2) ، ثم اضغط أدخل مفتاح للحصول على النتيجة:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

ملاحظة: في الصيغة أعلاه ، هناك أربع وسيطات:

  • F2 هي الخلية التي تحتوي على القيمة C1005 التي تريد البحث عنها ؛
  • A2: D7 هي مصفوفة الجدول التي تجري فيها البحث ؛
  • 3 هو رقم العمود الذي يتم إرجاع القيمة المتطابقة منه ؛ (بمجرد أن تحدد الوظيفة المعرف - C1005 ، ستنتقل إلى العمود الثالث من صفيف الجدول ، وتعيد القيم في نفس الصف مثل رقم المعرف - C1005.)
  • خاطئة يشير إلى المطابقة التامة.

كيف تعمل صيغة VLOOKUP؟

أولاً ، يبحث عن المعرف - C1005 في العمود الموجود في أقصى يسار الجدول. ينتقل من أعلى إلى أسفل ويجد القيمة في الخلية A6.

بمجرد العثور على القيمة ، ينتقل إلى اليمين في العمود الثالث ويستخرج القيمة فيه.

لذلك ، ستحصل على النتيجة كما هو موضح أدناه:

ملحوظة: إذا لم يتم العثور على قيمة البحث في العمود الموجود في أقصى اليسار ، فإنها تُرجع الخطأ # N / A.
🤖 مساعد Kutools AI: إحداث ثورة في تحليل البيانات على أساس: التنفيذ الذكي   |  إنشاء التعليمات البرمجية  |  إنشاء صيغ مخصصة  |  تحليل البيانات وإنشاء الرسوم البيانية  |  استدعاء وظائف Kutools...
الميزات الشعبية: البحث عن التكرارات أو تمييزها أو تحديدها   |  حذف الصفوف الفارغة   |  دمج الأعمدة أو الخلايا دون فقدان البيانات   |   جولة بدون صيغة 
سوبر بحث: معايير متعددة VLookup  |   VLookup ذات القيمة المتعددة  |   VLookup عبر أوراق متعددة   |   بحث غامض 
قائمة منسدلة متقدمة: إنشاء القائمة المنسدلة بسرعة   |  القائمة المنسدلة التابعة   |  قائمة منسدلة متعددة التحديد 
مدير العمود: إضافة عدد محدد من الأعمدة  |  نقل الأعمدة   |  إظهار الأعمدة  |  مقارنة النطاقات والأعمدة 
الميزات المميزة: التركيز على الشبكة   |  عرض تصميم   |   شريط الفورمولا الكبير   |  مدير المصنفات والأوراق  |  مكتبة الموارد   |  منتقي التاريخ  |  اجمع أوراق العمل   |  تشفير/فك تشفير الخلايا    إرسال رسائل البريد الإلكتروني عن طريق القائمة   |  سوبر تصفية   |   مرشح خاص (بالخط العريض / المائل ...) ...
أفضل 15 مجموعة أدوات12 نص الأدوات (إضافة نص, إزالة الأحرف، ...)   |   +50 رسم الأنواع (مخطط جانت، ...)   |   40+ عملي الصيغ (احسب العمر على أساس تاريخ الميلاد، ...)   |   19 إدخال الأدوات (أدخل رمز الاستجابة السريعة, إدراج صورة من المسار، ...)   |   12 تحويل الأدوات (أرقام إلى كلمات, نتيجة تحويل عملة، ...)   |   7 دمج وتقسيم الأدوات (الجمع بين الصفوف المتقدمة, تقسيم الخلايا، ...)   |   اكثر كثير

Kutools for Excel يضم أكثر من 300 ميزة, التأكد من أن ما تحتاجه هو مجرد نقرة واحدة...

 
 2.1.2 قم بعمل مطابقة تقريبية لـ VLOOKUP

التطابق التقريبي مفيد للبحث عن القيم بين نطاقات البيانات. إذا لم يتم العثور على التطابق التام ، فسيرجع VLOOKUP التقريبي أكبر قيمة أصغر من قيمة البحث.

على سبيل المثال ، إذا كان لديك نطاق البيانات التالي ، ولم تكن الطلبات المحددة موجودة في عمود الطلبات ، فكيف تحصل على أقرب خصم لها في العمود ب؟

الخطوة 1: قم بتطبيق صيغة VLOOKUP واملأها على الخلايا الأخرى

انسخ والصق الصيغة التالية في خلية حيث تريد وضع النتيجة ، ثم اسحب مقبض التعبئة لأسفل لتطبيق هذه الصيغة على خلايا أخرى.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

النتيجة:

الآن ، ستحصل على المطابقات التقريبية بناءً على القيم المعطاة ، انظر الصورة:

الملاحظات:

  • في الصيغة أعلاه:
    • D2 هي القيمة التي تريد إرجاع معلوماتها النسبية ؛
    • A2: B9 هو نطاق البيانات
    • 2 يشير إلى رقم العمود الذي يتم إرجاع القيمة المتطابقة الخاصة بك ؛
    • الحقيقة يشير إلى المطابقة التقريبية.
  • سترجع المطابقة التقريبية أكبر قيمة أصغر من قيمة البحث المحددة الخاصة بك إذا لم يتم العثور على تطابق تام.
  • لاستخدام وظيفة VLOOKUP للحصول على قيمة مطابقة تقريبية ، يجب عليك فرز العمود الموجود في أقصى اليسار من نطاق البيانات بترتيب تصاعدي ، وإلا ستُرجع نتيجة خاطئة.

2.2 قم بعمل VLOOKUP حساس لحالة الأحرف في Excel

بشكل افتراضي ، تقوم وظيفة VLOOKUP بإجراء بحث غير حساس لحالة الأحرف مما يعني أنها تتعامل مع الأحرف الصغيرة والكبيرة على أنها متطابقة. في وقت ما ، قد تحتاج إلى إجراء بحث حساس لحالة الأحرف في Excel ، وقد لا تحلها وظيفة VLOOKUP العادية. في هذه الحالة ، يمكنك استخدام وظائف بديلة مثل INDEX و MATCH مع وظيفة EXACT ، أو وظائف LOOKUP و EXACT.

على سبيل المثال ، لدي نطاق البيانات التالي الذي يحتوي عمود المعرف على سلسلة نصية بأحرف كبيرة أو صغيرة ، والآن ، أريد إرجاع درجة الرياضيات المقابلة لرقم المعرف المحدد.

الخطوة 1: قم بتطبيق أي صيغة واحدة واملأها على الخلايا الأخرى

يرجى نسخ ولصق أي من الصيغ أدناه في خلية فارغة حيث تريد الحصول على النتيجة. بعد ذلك ، حدد خلية الصيغة ، واسحب مقبض التعبئة لأسفل إلى الخلايا التي تريد تعبئة هذه الصيغة فيها.

الصيغة شنومكس: بعد لصق الصيغة ، الرجاء الضغط كترل + شيفت + إنتر مفاتيح.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

الصيغة شنومكس: بعد لصق الصيغة ، الرجاء الضغط أدخل الرئيسية.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

النتيجة:

ثم ستحصل على النتائج الصحيحة التي تحتاجها. انظر لقطة الشاشة:

الملاحظات:

  • في الصيغة أعلاه:
    • A2: A10 هو العمود الذي يحتوي على القيم المحددة التي تريد البحث عنها ؛
    • F2 هي قيمة البحث ؛
    • C2: C10 هو العمود الذي سيتم عرض النتيجة منه.
  • إذا تم العثور على تطابقات متعددة ، فستقوم هذه الصيغة دائمًا بإرجاع آخر تطابق.

2.3 قيم VLOOKUP من اليمين إلى اليسار في Excel

تبحث الدالة VLOOKUP دائمًا عن قيمة في العمود الموجود في أقصى اليسار من نطاق البيانات وترجع القيمة المقابلة من عمود إلى اليمين. إذا كنت تريد إجراء VLOOKUP عكسيًا مما يعني البحث عن قيمة محددة في العمود الأيمن وإرجاع قيمتها المقابلة في العمود الأيسر كما هو موضح أدناه:

انقر لمعرفة التفاصيل خطوة بخطوة حول هذه المهمة ...


2.4 VLOOKUP هي القيمة المطابقة الثانية أو التاسعة أو الأخيرة في Excel

عادةً ، إذا تم العثور على قيم مطابقة متعددة عند استخدام وظيفة Vlookup ، فسيتم إرجاع أول سجل مطابق فقط. في هذا القسم ، سأتحدث عن كيفية الحصول على القيمة المطابقة الثانية أو التاسعة أو الأخيرة في نطاق البيانات.

 2.4.1 VLOOKUP وإرجاع القيمة المطابقة الثانية أو التاسعة

لنفترض أن لديك قائمة بالأسماء في العمود A ، الدورة التدريبية التي اشتروها في العمود B. الآن ، أنت تبحث عن الدورة التدريبية الثانية أو التاسعة التي اشتراها العميل المحدد. انظر لقطة الشاشة:

هنا ، قد لا تحل وظيفة VLOOKUP هذه المهمة مباشرة. ولكن ، يمكنك استخدام دالة INDEX كبديل.

الخطوة 1: تطبيق وملء الصيغة على الخلايا الأخرى

على سبيل المثال ، للحصول على قيمة المطابقة الثانية بناءً على المعايير المحددة ، يرجى تطبيق الصيغة التالية في خلية فارغة ، ثم الضغط على كترل + شيفت + إنتر مفاتيح معًا للحصول على النتيجة الأولى. ثم حدد خلية الصيغة ، واسحب مقبض التعبئة لأسفل إلى الخلايا التي تريد تعبئة هذه الصيغة فيها.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

النتيجة:

الآن ، تم عرض جميع القيم المتطابقة الثانية بناءً على الأسماء المحددة مرة واحدة.

ملحوظة: في الصيغة أعلاه:

  • A2: A14 هو النطاق الذي يحتوي على جميع قيم البحث ؛
  • B2: B14 هو نطاق القيم المطابقة التي تريد العودة منها ؛
  • E2 هي قيمة البحث ؛
  • 2 يشير إلى القيمة المطابقة الثانية التي تريد الحصول عليها ، لإرجاع القيمة المطابقة الثالثة ، ما عليك سوى تغييرها إلى 3.
 2.4.2 VLOOKUP وإرجاع آخر قيمة مطابقة

إذا كنت ترغب في البحث عن آخر قيمة مطابقة وإرجاعها كما هو موضح في لقطة الشاشة أدناه ، فهذا VLOOKUP وإرجاع آخر قيمة مطابقة قد يساعدك البرنامج التعليمي في الحصول على آخر قيمة مطابقة بالتفصيل.


2.5 تطابق قيم VLOOKUP بين قيمتين أو تاريخين

في بعض الأحيان ، قد ترغب في البحث عن قيم بين قيمتين أو تاريخين وإرجاع النتائج المقابلة كما هو موضح في لقطة الشاشة أدناه. في مثل هذه الحالة ، يمكنك استخدام وظيفة LOOKUP بدلاً من وظيفة VLOOKUP مع جدول تم فرزه.

 2.5.1 يطابق VLOOKUP القيم بين قيمتين أو تواريخ معطاة

الخطوة 1: ترتيب البيانات وتطبيق الصيغة التالية

يجب أن يكون جدولك الأصلي عبارة عن نطاق بيانات تم فرزه. ثم انسخ أو أدخل الصيغة التالية في خلية فارغة ، ثم اسحب مقبض التعبئة لملء هذه الصيغة إلى الخلايا الأخرى التي تحتاجها.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

النتيجة:

والآن ، ستحصل على جميع السجلات المتطابقة بناءً على القيمة المحددة ، انظر لقطة الشاشة:

الملاحظات:

  • في الصيغة أعلاه:
    • A2: A6 هو نطاق القيم الأصغر ؛
    • B2: B6 هو مدى الأعداد الكبيرة.
    • E2 هي قيمة البحث التي تريد الحصول على قيمتها المقابلة ؛
    • C2: C6 هو العمود الذي تريد إرجاع القيمة المقابلة منه.
  • يمكن أيضًا استخدام هذه الصيغة لاستخراج القيم المتطابقة بين تاريخين كما هو موضح أدناه:
 2.5.2 تقوم VLOOKUP بمطابقة القيم بين قيمتين أو تواريخ معطاة بميزة سهلة الاستخدام

إذا وجدت صعوبة في تذكر وفهم الصيغة أعلاه ، فإني سأقدم لك هنا أداة سهلة - كوتولس ل إكسيل، بما لديها LOOKUP بين قيمتين يمكنك إرجاع العنصر المقابل بناءً على القيمة المحددة أو التاريخ بين قيمتين أو تاريخين بسهولة.

  1. انقر كوتولس > سوبر لوك > LOOKUP بين قيمتين لتمكين هذه الميزة.
  2. ثم حدد العمليات من مربع الحوار بناءً على بياناتك.
ملاحظات: لتطبيق هذه الميزة ، يجب عليك تنزيل Kutools لبرنامج Excel مع نسخة تجريبية مجانية مدتها 30 يومًا أولا.


2.6 استخدام أحرف البدل للمطابقات الجزئية في وظيفة VLOOKUP

في Excel ، يمكن استخدام أحرف البدل ضمن وظيفة VLOOKUP ، والتي تسمح لك بإجراء تطابق جزئي على قيمة بحث. على سبيل المثال ، يمكنك استخدام VLOOKUP لإرجاع القيمة المتطابقة من جدول بناءً على جزء من قيمة البحث.

لنفترض أن لدي مجموعة من البيانات كما هو موضح أدناه ، أريد استخراج النتيجة بناءً على الاسم الأول (وليس الاسم الكامل). كيف يمكن حل هذه المهمة في Excel؟

الخطوة 1: تطبيق وملء الصيغة على الخلايا الأخرى

يرجى نسخ الصيغة التالية أو إدخالها في خلية فارغة ، ثم اسحب مقبض التعبئة لملء هذه الصيغة إلى الخلايا الأخرى التي تحتاجها:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

النتيجة:

وتم إرجاع جميع النتائج المتطابقة كما هو موضح في لقطة الشاشة أدناه:

ملحوظة: في الصيغة أعلاه:

  • E2 & "*" هي معايير الرياضيات الجزئية. هذا يعني أنك تبحث عن أي قيمة تبدأ بالقيمة الموجودة في الخلية E2. (البدل "*"يشير إلى أي حرف واحد أو أي أحرف)
  • A2: C11 هو نطاق البيانات حيث تريد البحث عن القيمة المطابقة ؛
  • 3 يعني إرجاع قيمة المطابقة من العمود الثالث من نطاق البيانات ؛
  • خطأ يشير إلى الرياضيات الدقيقة. (عند استخدام أحرف البدل ، يجب عليك تعيين الوسيطة الأخيرة في الوظيفة على أنها FALSE أو 0 لتمكين وضع المطابقة التامة في وظيفة VLOOKUP.)
نصائح:
  • للبحث عن القيم المطابقة التي تنتهي بقيمة معينة وإرجاعها ، يجب وضع حرف البدل "*" أمام القيمة. الرجاء تطبيق هذه الصيغة:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

  • للبحث عن القيمة المتطابقة وإرجاعها استنادًا إلى جزء من السلسلة النصية ، سواء كان النص المحدد في bebinning أو في نهايتها أو في منتصفها ، تحتاج فقط إلى إرفاق مرجع الخلية أو النص بعلامتين نجميتين (*) على كلا الجانبين. الرجاء القيام بهذه الصيغة
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


2.7 قيم VLOOKUP من ورقة عمل أخرى

عادة ، قد تضطر إلى العمل مع أكثر من ورقة عمل واحدة ، ويمكن استخدام وظيفة VLOOKUP للبحث عن البيانات من ورقة أخرى كما هو الحال في ورقة عمل واحدة.

على سبيل المثال ، لديك ورقتي عمل على النحو المبين أدناه ، للبحث عن البيانات المقابلة وإرجاعها من ورقة العمل التي حددتها ، يرجى اتباع الخطوات التالية:

الخطوة 1: تطبيق وملء الصيغة على الخلايا الأخرى

الرجاء إدخال الصيغة أدناه أو نسخها في خلية فارغة حيث تريد الحصول على العناصر المتطابقة. بعد ذلك ، اسحب مقبض التعبئة لأسفل إلى الخلايا التي تريد تطبيق هذه الصيغة.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

النتيجة:

ستحصل على النتائج المقابلة حسب حاجتك ، انظر الصورة:

ملحوظة: في الصيغة أعلاه:

  • A2 يمثل قيمة البحث ؛
  • "ورقة البيانات"! A2: C15 يشير إلى البحث عن القيم من النطاق A2: C15 في ورقة العمل المسماة ورقة البيانات ؛ (إذا كان اسم الورقة يحتوي على مسافة أو أحرف ترقيم ، فيجب عليك إرفاق اسم الورقة بعلامات اقتباس فردية ، وإلا يمكنك استخدام اسم الورقة مباشرةً مثل = VLOOKUP (A2، Datasheet! $ A $ 2: $ C $ 15,3,0،XNUMX،XNUMX)).
  • 3 هو رقم العمود الذي يحتوي على البيانات المتطابقة التي تريد العودة منها ؛
  • 0 يعني إجراء مطابقة تامة.

2.8 قيم VLOOKUP من مصنف آخر

سيتحدث هذا القسم عن البحث وإرجاع القيم المطابقة من مصنف مختلف باستخدام الدالة VLOOKUP.

على سبيل المثال ، لنفترض أن لديك مصنفين. يحتوي المصنف الأول على قائمة بالمنتجات وتكاليفها. في المصنف الثاني ، تريد استخراج التكلفة المقابلة لكل عنصر منتج كما هو موضح أدناه لقطة الشاشة.

الخطوة 1: تطبيق وملء الصيغة

افتح كلا المصنفين اللذين تريد استخدامهما ، ثم طبق الصيغة التالية في خلية حيث تريد وضع النتيجة في المصنف الثاني ، ثم اسحب هذه الصيغة وانسخها إلى الخلايا الأخرى التي تحتاجها

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

النتيجة:

الملاحظات:

  • في الصيغة أعلاه:
    • B2 يمثل قيمة البحث ؛
    • '[Product list.xlsx] Sheet1'! A2: B6 يشير إلى البحث من النطاق A2: B6 على الورقة المسماة Sheet1 من قائمة منتج المصنف ؛ (المرجع إلى المصنف محاط بأقواس مربعة ، ويتم وضع المصنف + الورقة بالكامل بين علامتي اقتباس مفردة.)
    • 2 هو رقم العمود الذي يحتوي على البيانات المتطابقة التي تريد العودة منها ؛
    • 0 يشير إلى إرجاع تطابق تام.
  • إذا تم إغلاق مصنف البحث ، فسيتم عرض مسار الملف الكامل لمصنف البحث في الصيغة كما هو موضح في لقطة الشاشة التالية:

2.9 إرجاع نص فارغ أو نص محدد بدلاً من 0 أو خطأ # N / A

عادةً ، عند استخدام دالة VLOOKUP لإرجاع قيمة مقابلة ، إذا كانت الخلية المطابقة فارغة ، فستُرجع 0. وإذا لم يتم العثور على القيمة المطابقة ، فستحصل على قيمة خطأ # N / A كما هو موضح في لقطة الشاشة أدناه. إذا كنت تريد عرض خلية فارغة أو قيمة محددة بدلاً من 0 أو # N / A ، فهذا VLOOKUP لإرجاع قيمة فارغة أو محددة بدلاً من 0 أو N / A البرنامج التعليمي قد يقدم لك معروفًا.


أمثلة متقدمة على VLOOKUP

3.1 بحث ثنائي الاتجاه (VLOOKUP في الصف والعمود)

في بعض الأحيان ، قد تحتاج إلى إجراء بحث ثنائي الأبعاد ، مما يعني البحث عن قيمة في كل من الصف والعمود في نفس الوقت. على سبيل المثال ، إذا كان لديك نطاق البيانات التالي ، وقد تحتاج إلى الحصول على القيمة لمنتج معين في ربع محدد. سيقدم هذا القسم صيغة للتعامل مع هذه الوظيفة في Excel.

في Excel ، يمكنك استخدام مجموعة من وظائف VLOOKUP و MATCH لإجراء بحث ثنائي الاتجاه.

الرجاء تطبيق الصيغة التالية في خلية فارغة ، ثم الضغط على أدخل مفتاح للحصول على النتيجة.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

ملحوظة: في الصيغة أعلاه:

  • G2 هي قيمة البحث في العمود التي تريد الحصول على القيمة المقابلة بناءً عليها ؛
  • A2: E7 هو جدول البيانات الذي ستبحث منه ؛
  • H1 هي قيمة البحث في الصف التي تريد الحصول على القيمة المقابلة بناءً عليها ؛
  • A2: E2 هي خلايا رؤوس الأعمدة ؛
  • خاطئة يشير إلى الحصول على تطابق تام.

3.2 قيمة مطابقة VLOOKUP بناءً على معيارين أو أكثر

من السهل عليك البحث عن قيمة المطابقة بناءً على معيار واحد ، ولكن إذا كان لديك معياران أو أكثر ، فماذا يمكنك أن تفعل؟

 3.2.1 قيمة مطابقة VLOOKUP بناءً على معيارين أو أكثر مع الصيغ

في هذه الحالة ، يمكن أن تساعدك وظائف LOOKUP أو MATCH و INDEX في Excel على حل هذه المهمة بسرعة وسهولة.

على سبيل المثال ، لدي جدول البيانات أدناه ، لإرجاع السعر المطابق بناءً على المنتج والحجم المحددين ، قد تساعدك الصيغ التالية.

الخطوة 1: طبّق أي صيغة واحدة

الصيغة شنومكس: بعد لصق الصيغة ، الرجاء الضغط أدخل الرئيسية.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

الصيغة شنومكس: بعد لصق الصيغة ، الرجاء الضغط كترل + شيفت + إنتر مفاتيح.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

النتيجة:

الملاحظات:

  • في الصيغ أعلاه:
    • A2: A12 = G1 يعني البحث عن معايير G1 في النطاق A2: A12 ؛
    • B2: B12 = G2 يعني البحث عن معايير G2 في النطاق B2: B12 ؛
    • D2: D12 is النطاق الذي تريد إرجاع القيمة المقابلة منه.
  • إذا كان لديك أكثر من معيارين ، فأنت تحتاج فقط إلى ضم المعايير الأخرى إلى الصيغة ، مثل:
    =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
    =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
 3.2.2 قيمة مطابقة VLOOKUP بناءً على معيارين أو أكثر بميزة ذكية

قد يكون من الصعب تذكر الصيغ المعقدة المذكورة أعلاه والتي يجب تطبيقها بشكل متكرر ، مما قد يؤدي إلى إبطاء كفاءة عملك. لكن، كوتولس ل إكسيل يقدم بحث متعدد الشروط ميزة تسمح لك بإرجاع النتيجة المقابلة بناءً على شرط واحد أو أكثر بنقرات عديدة فقط.

  1. انقر كوتولس > سوبر لوك > بحث متعدد الشروط لتمكين هذه الميزة.
  2. ثم حدد العمليات من مربع الحوار بناءً على بياناتك.
ملاحظات: لتطبيق هذه الميزة ، يجب عليك تنزيل Kutools لبرنامج Excel مع نسخة تجريبية مجانية مدتها 30 يومًا أولا.


3.3 VLOOKUP لإرجاع قيم متعددة بمعيار واحد أو أكثر

في Excel ، تبحث الدالة VLOOKUP عن قيمة وتقوم بإرجاع أول قيمة مطابقة فقط إذا تم العثور على عدة قيم مقابلة. في بعض الأحيان ، قد ترغب في إرجاع جميع القيم المقابلة في صف أو في عمود أو في خلية واحدة. سيتحدث هذا القسم عن كيفية إرجاع قيم المطابقة المتعددة بشرط واحد أو أكثر في مصنف.

 3.3.1 VLOOKUP جميع القيم المطابقة بناءً على شرط واحد أو أكثر أفقياً

بافتراض أن لديك جدول بيانات يحتوي على البلد والمدينة والأسماء في النطاق A1: C14 ، والآن ، فأنت تريد إرجاع جميع الأسماء أفقيًا والتي تأتي من "US" كما هو موضح أدناه في لقطة الشاشة. لحل هذه المهمة ، من فضلك انقر هنا للحصول على النتيجة خطوة بخطوة.

 3.3.2 VLOOKUP جميع القيم المطابقة بناءً على شرط واحد أو أكثر عموديًا

إذا كنت بحاجة إلى Vlookup وإرجاع جميع القيم المطابقة عموديًا بناءً على معايير محددة كما هو موضح أدناه ، الرجاء النقر هنا للحصول على الحل بالتفصيل.

 3.3.3 VLOOKUP جميع القيم المطابقة بناءً على شرط واحد أو أكثر في خلية واحدة

إذا كنت تريد Vlookup وإرجاع قيم متطابقة متعددة في خلية واحدة بفاصل محدد ، يمكن أن تساعدك الوظيفة الجديدة لـ TEXTJOIN على حل هذه المهمة بسرعة وسهولة.

الملاحظات:

  • لا تتوفر وظيفة TEXTJOIN إلا في Excel 2019 و Excel 365 والإصدارات الأحدث.
  • إذا كنت تستخدم Excel 2016 والإصدارات السابقة ، فالرجاء استخدام الوظيفة المحددة بواسطة المستخدم في المقالة أدناه:
  • Vlookup لإرجاع قيم متعددة في خلية واحدة في Excel

3.4 VLOOKUP لإرجاع صف كامل للخلية المتطابقة

في هذا القسم ، سأتحدث عن كيفية استرداد الصف الكامل للقيمة المتطابقة باستخدام وظيفة VLOOKUP.

الخطوة 1: تطبيق وملء الصيغة التالية

يرجى نسخ أو كتابة الصيغة أدناه في خلية فارغة حيث تريد إخراج النتيجة ، ثم اضغط أدخل مفتاح للحصول على القيمة الأولى. بعد ذلك ، اسحب خلية الصيغة إلى اليمين حتى يتم عرض بيانات الصف بأكمله.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

النتيجة:

الآن ، يمكنك رؤية بيانات الصف بالكامل يتم إرجاعها. انظر لقطة الشاشة:
وظيفة doc vlookup 50 1

ملحوظة: في الصيغة أعلاه:

  • F2 هي قيمة البحث التي تريد إرجاع الصف بأكمله بناءً عليها ؛
  • A1: D12 هو نطاق البيانات الذي تريد البحث عن قيمة البحث منه ؛
  • A1 يشير إلى رقم العمود الأول ضمن نطاق البيانات الخاصة بك ؛
  • خاطئة يشير إلى البحث الدقيق.

نصيحة:

  • إذا تم العثور على عدة صفوف بناءً على القيمة المتطابقة ، لإرجاع جميع الصفوف المقابلة ، يرجى تطبيق الصيغة أدناه ، ثم الضغط على كترل + شيفت + إنتر مفاتيح معًا للحصول على النتيجة الأولى. ثم اسحب مقبض التعبئة إلى اليمين. وبعد ذلك ، استمر في سحب مقبض التعبئة لأسفل عبر الخلايا للحصول على جميع الصفوف المتطابقة. انظر العرض أدناه:
    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
    وظيفة doc vlookup 51 2

3.5 VLOOKUP المتداخلة في Excel

في بعض الأحيان ، قد تحتاج إلى البحث عن قيم مرتبطة ببعضها البعض عبر جداول متعددة. في هذه الحالة ، يمكنك دمج عدة دالة VLOOKUP معًا للحصول على القيمة النهائية.

على سبيل المثال ، لدي ورقة عمل تحتوي على جدولين منفصلين. يسرد الجدول الأول جميع أسماء المنتجات جنبًا إلى جنب مع البائع المقابل لها. يسرد الجدول الثاني إجمالي المبيعات لكل بائع. الآن ، إذا كنت تريد العثور على مبيعات كل منتج ، كما هو موضح في لقطة الشاشة التالية ، فيمكنك دمج وظيفة VLOOKUP لإنجاز هذه المهمة.
وظيفة doc vlookup 53 1

الصيغة العامة لوظيفة VLOOKUP المتداخلة هي:

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

ملحوظة:

  • ابحث عن القيمة هي القيمة التي تبحث عنها ؛
  • Table_array1, Table_array2 هي الجداول التي توجد فيها قيمة البحث وقيمة الإرجاع ؛
  • col_index_num1 يشير إلى رقم العمود في الجدول الأول للعثور على البيانات المشتركة الوسيطة ؛
  • col_index_num2 يشير إلى رقم العمود في الجدول الثاني الذي تريد إرجاع القيمة المطابقة ؛
  • 0 يستخدم لمطابقة تامة.

الخطوة 1: تطبيق وملء الصيغة التالية

الرجاء تطبيق الصيغة التالية في خلية فارغة ، ثم اسحب مقبض التعبئة لأسفل إلى الخلايا التي تريد تطبيق هذه الصيغة.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

النتيجة:

الآن ستحصل على النتيجة كما هو موضح في الصورة التالية:

ملحوظة: في الصيغة أعلاه:

  • G3 يحتوي على القيمة التي تبحث عنها ؛
  • A3: B7, D3: E7 هي نطاقات الجدول التي توجد فيها قيمة البحث وقيمة الإرجاع ؛
  • 2 هو رقم العمود في النطاق لإرجاع القيمة المطابقة منه.
  • 0 يشير إلى حساب VLOOKUP بالضبط.

3.6 تحقق مما إذا كانت القيمة موجودة بناءً على بيانات قائمة في عمود آخر

يمكن أن تساعدك وظيفة VLOOKUP أيضًا في التحقق مما إذا كانت القيم موجودة بناءً على قائمة البيانات في عمود آخر. على سبيل المثال ، إذا كنت تريد البحث عن الأسماء في العمود C والعودة فقط إلى "نعم" أو "لا" إذا تم العثور على الاسم أم لا في العمود "أ" كما هو موضح أدناه لقطة الشاشة.
وظيفة doc vlookup 56 1

الخطوة 1: تطبيق وملء الصيغة التالية

يرجى تطبيق الصيغة التالية في خلية فارغة ، ثم اسحب مقبض التعبئة لأسفل إلى الخلايا التي تريد ملء هذه الصيغة.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

النتيجة:

وستحصل على النتيجة كما تريد ، انظر لقطة الشاشة:

ملحوظة: في الصيغة أعلاه:

  • C2 هي قيمة البحث التي تريد التحقق منها ؛
  • A2: A10 هي قائمة النطاق من حيث يتم التحقق مما إذا كان سيتم العثور على قيم البحث أم لا ؛
  • خاطئة يشير إلى الحصول على تطابق تام.

3.7 VLOOKUP وجمع كل القيم المتطابقة في صفوف أو أعمدة

عند العمل باستخدام البيانات الرقمية ، قد تحتاج إلى استخراج القيم المتطابقة من جدول وجمع الأرقام في عدة أعمدة أو صفوف. سيقدم هذا القسم بعض الصيغ التي يمكن أن تساعدك في إنجاز هذه المهمة.

 3.7.1 VLOOKUP وجمع كل القيم المتطابقة في صف أو صفوف متعددة

افترض أن لديك قائمة منتجات بالمبيعات لعدة أشهر ، كما هو موضح في لقطة الشاشة التالية. الآن ، تحتاج إلى جمع جميع الطلبات في جميع الأشهر بناءً على المنتجات المحددة.

الخطوة 1: تطبيق وملء الصيغة التالية

يرجى نسخ الصيغة التالية أو إدخالها في خلية فارغة ، ثم الضغط على كترل + شيفت + إنتر مفاتيح معًا للحصول على النتيجة الأولى. بعد ذلك ، اسحب مقبض التعبئة لأسفل لنسخ هذه الصيغة إلى الخلايا الأخرى التي تحتاجها.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

النتيجة:

تم جمع جميع القيم الموجودة في صف من القيمة المطابقة الأولى معًا ، انظر الصورة:

ملحوظة: في الصيغة أعلاه:

  • H2 هي الخلية التي تحتوي على القيمة التي تبحث عنها ؛
  • A2: F9 هو نطاق البيانات (بدون رؤوس الأعمدة) الذي يتضمن قيمة البحث والقيم المتطابقة ؛
  • 2,3,4,5,6 {} هي أرقام الأعمدة المستخدمة لحساب إجمالي النطاق ؛
  • خاطئة يشير إلى تطابق تام.

نصيحة: إذا كنت تريد جمع كل التطابقات في صفوف متعددة ، فيرجى استخدام الصيغة التالية:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 VLOOKUP وجمع كل القيم المتطابقة في عمود أو عدة أعمدة

إذا كنت تريد جمع القيمة الإجمالية للأشهر المحددة كما هو موضح في لقطة الشاشة أدناه. قد لا تساعدك وظيفة VLOOKUP العادية ، هنا ، يجب عليك تطبيق وظائف SUM و INDEX و MATCH معًا لإنشاء صيغة.

الخطوة 1: قم بتطبيق الصيغة التالية

قم بتطبيق الصيغة أدناه في خلية فارغة ، ثم اسحب مقبض التعبئة لأسفل لنسخ هذه الصيغة إلى خلايا أخرى.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

النتيجة:

الآن ، تم تجميع القيم المتطابقة الأولى بناءً على الشهر المحدد في عمود معًا ، انظر الصورة:

ملحوظة: في الصيغة أعلاه:

  • H2 هي الخلية التي تحتوي على القيمة التي تبحث عنها ؛
  • B1: F1 هي رؤوس الأعمدة التي تحتوي على قيمة البحث ؛
  • B2: F9 هو نطاق البيانات الذي يحتوي على القيم الرقمية التي تريد جمعها.

نصيحة: من أجل VLOOKUP وجمع كل القيم المتطابقة في عدة أعمدة ، يجب عليك استخدام الصيغة التالية:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 VLOOKUP ولخص القيم المتطابقة الأولى أو جميع القيم المتطابقة بميزة قوية

ربما يصعب عليك تذكر الصيغ أعلاه ، في هذه الحالة ، سأوصي بميزة قوية - البحث و المجموع of كوتولس ل إكسيل، باستخدام هذه الميزة ، يمكنك Vlookup وجمع القيم المطابقة الأولى أو جميع القيم المطابقة في الصفوف أو الأعمدة بأسهل ما يمكن.

  1. انقر كوتولس > سوبر لوك > لوكاب وسوم لتمكين هذه الميزة.
  2. ثم حدد العمليات من مربع الحوار حسب حاجتك.
ملاحظات: لتطبيق هذه الميزة ، يجب عليك تنزيل Kutools لبرنامج Excel مع نسخة تجريبية مجانية مدتها 30 يومًا أولا.
 3.7.4 VLOOKUP وجمع كل القيم المتطابقة في كل من الصفوف والأعمدة

إذا كنت ترغب في جمع القيم عندما تحتاج إلى مطابقة كل من العمود والصف ، على سبيل المثال ، للحصول على القيمة الإجمالية للمنتج Sweater في شهر Mar كما هو موضح أدناه لقطة الشاشة.

هنا ، يمكنك استخدام الدالة SUMPRODCT لإنجاز هذه المهمة.

الرجاء تطبيق الصيغة التالية في خلية ، ثم الضغط على أدخل مفتاح للحصول على النتيجة ، انظر لقطة الشاشة:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

ملحوظة: في الصيغة أعلاه:

  • B2: F9 هو نطاق البيانات الذي يحتوي على القيم الرقمية التي تريد جمعها ؛
  • B1: F1 هي رؤوس الأعمدة التي تحتوي على قيمة البحث التي تريد جمعها بناءً على ؛
  • I2 هي قيمة البحث داخل رؤوس الأعمدة التي تبحث عنها ؛
  • A2: A9 هي رؤوس الصفوف التي تحتوي على قيمة البحث التي تريد جمعها بناءً على ؛
  • H2 هي قيمة البحث داخل رؤوس الصفوف التي تبحث عنها.

3.8 VLOOKUP لدمج جدولين بناءً على الأعمدة الرئيسية

في عملك اليومي ، عند تحليل البيانات ، قد تحتاج إلى جمع كل المعلومات الضرورية في جدول واحد بناءً على عمود رئيسي واحد أو أكثر. لإنجاز هذه المهمة ، يمكنك استخدام دالتي INDEX و MATCH بدلاً من وظيفة VLOOKUP.

 3.8.1 VLOOKUP لدمج جدولين بناءً على عمود مفتاح واحد

على سبيل المثال ، لديك جدولين ، الجدول الأول الذي يحتوي على بيانات المنتجات والأسماء ، والجدول الثاني يحتوي على بيانات المنتجات والطلبات ، والآن تريد دمج هذين الجدولين عن طريق مطابقة عمود المنتج الشائع في جدول واحد.

الخطوة 1: تطبيق وملء الصيغة التالية

الرجاء تطبيق الصيغة التالية في خلية فارغة. بعد ذلك ، اسحب مقبض التعبئة لأسفل إلى الخلايا التي تريد تطبيق هذه الصيغة

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

النتيجة:

الآن ، ستحصل على جدول مدمج به عمود الترتيب الذي ينضم إلى الجدول الأول بناءً على بيانات العمود الأساسي.

ملحوظة: في الصيغة أعلاه:

  • A2 هي قيمة البحث التي تبحث عنها ؛
  • F2: F8 هو نطاق البيانات التي تريد إرجاع القيم المطابقة لها ؛
  • E2: E8 هو نطاق البحث الذي يحتوي على قيمة البحث.
 3.8.2 VLOOKUP لدمج جدولين على أساس أعمدة مفاتيح متعددة

إذا كان الجدولان اللذان تريد ضمهما يحتويان على عدة أعمدة رئيسية ، لدمج الجداول بناءً على هذه الأعمدة المشتركة ، يرجى اتباع الخطوات أدناه.

الصيغة العامة هي:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

ملحوظة:

  • جدول البحث هو نطاق البيانات الذي يحتوي على بيانات البحث والسجلات المطابقة ؛
  • lookup_value1 هو المعيار الأول الذي تبحث عنه ؛
  • lookup_range1 هي قائمة البيانات تحتوي على المعايير الأولى ؛
  • lookup_value2 هو المعيار الثاني الذي تبحث عنه ؛
  • lookup_range2 هي قائمة البيانات تحتوي على المعيار الثاني ؛
  • return_column_number يشير إلى رقم العمود في lookup_table الذي تريد إرجاع القيمة المطابقة.

الخطوة 1: قم بتطبيق الصيغة التالية

الرجاء تطبيق الصيغة أدناه في خلية فارغة حيث تريد وضع النتيجة ، ثم الضغط على كترل + شيفت + إنتر مفاتيح معًا للحصول على القيمة المطابقة الأولى ، انظر لقطة الشاشة:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

الخطوة 2: املأ الصيغة بالخلايا الأخرى

بعد ذلك ، حدد خلية الصيغة الأولى ، واسحب مقبض التعبئة لنسخ هذه الصيغة إلى خلايا أخرى حسب حاجتك:

نصائح: في Excel 2016 أو الإصدارات الأحدث ، يمكنك أيضًا استخدام ملف Power Query ميزة لدمج جدولين أو أكثر في جدول واحد بناءً على الأعمدة الرئيسية. الرجاء الضغط لمعرفة التفاصيل خطوة بخطوة.

3.9 قيم مطابقة VLOOKUP عبر أوراق عمل متعددة

هل سبق لك أن احتجت إلى إجراء VLOOKUP عبر أوراق عمل متعددة في Excel؟ على سبيل المثال ، إذا كان لديك ثلاث أوراق عمل مع نطاقات بيانات ، وتريد استرداد قيم محددة بناءً على معايير من هذه الأوراق ، يمكنك اتباع البرنامج التعليمي خطوة بخطوة قيم VLOOKUP عبر أوراق عمل متعددة لإنجاز هذه المهمة.


تحافظ قيم VLOOKUP المتطابقة على تنسيق الخلية

عند البحث عن القيم المتطابقة ، لن يتم الاحتفاظ بتنسيق الخلية الأصلي مثل لون الخط ولون الخلفية وتنسيق البيانات وما إلى ذلك. للاحتفاظ بتنسيق الخلية أو البيانات ، سيقدم هذا القسم بعض الحيل لحل الوظائف.

4.1 قيمة مطابقة VLOOKUP والحفاظ على لون الخلية وتنسيق الخط

كما نعلم جميعًا ، يمكن لوظيفة VLOOKUP العادية فقط استرداد قيمة المطابقة من نطاق بيانات آخر. ومع ذلك ، قد تكون هناك حالات ترغب فيها في الحصول على القيمة المقابلة جنبًا إلى جنب مع تنسيق الخلية ، مثل لون التعبئة ولون الخط ونمط الخط. في هذا القسم ، سنناقش كيفية استرداد القيم المطابقة مع الحفاظ على تنسيق المصدر في Excel.

يرجى القيام بالخطوات التالية للبحث وإرجاع القيمة المطابقة مع تنسيق الخلية:

الخطوة 1: انسخ الكود 1 في وحدة كود الورقة

  1. في ورقة العمل تحتوي على البيانات التي تريد VLOOKUP ، انقر بزر الماوس الأيمن فوق علامة تبويب الورقة وحدد عرض الرمز من قائمة السياق. انظر لقطة الشاشة:
  2. في فتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة ، يرجى نسخ رمز فبا أدناه في نافذة التعليمات البرمجية.
  3. كود VBA 1: VLOOKUP للحصول على تنسيق الخلية مع قيمة البحث
  4. Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice
        Dim I As Long
        Dim xKeys As Long
        Dim xDicStr As String
        On Error Resume Next
        Application.ScreenUpdating = False
        xKeys = UBound(xDic.Keys)
        If xKeys >= 0 Then
            For I = 0 To UBound(xDic.Keys)
                xDicStr = xDic.Items(I)
                If xDicStr <> "" Then
                    Range(xDic.Keys(I)).Interior.Color = _
                    Range(xDic.Items(I)).Interior.Color
                    Range(xDic.Keys(I)).Font.FontStyle = _
                    Range(xDic.Items(I)).Font.FontStyle
                    Range(xDic.Keys(I)).Font.Size = _
                    Range(xDic.Items(I)).Font.Size
                    Range(xDic.Keys(I)).Font.Color = _
                    Range(xDic.Items(I)).Font.Color
                    Range(xDic.Keys(I)).Font.Name = _
                    Range(xDic.Items(I)).Font.Name
                    Range(xDic.Keys(I)).Font.Underline = _
                    Range(xDic.Items(I)).Font.Underline
                Else
                    Range(xDic.Keys(I)).Interior.Color = xlNone
                End If
            Next
            Set xDic = Nothing
        End If
        Application.ScreenUpdating = True
    End Sub
    

الخطوة 2: انسخ الكود 2 في نافذة الوحدة النمطية

  1. لا يزال في ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة، ثم انسخ رمز VBA 2 أدناه في نافذة الوحدة النمطية.
  2. كود VBA 2: VLOOKUP للحصول على تنسيق الخلية مع قيمة البحث
  3. Public xDic As New Dictionary
    Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
        Dim xFindCell As Range
        On Error Resume Next
        Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
        If xFindCell Is Nothing Then
            LookupKeepFormat = ""
            xDic.Add Application.Caller.Address, ""
        Else
            LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
            xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
        End If
    End Function
    

الخطوة 3: حدد خيار VBAproject

  1. بعد إدخال الرموز أعلاه ، انقر فوق الأدوات > مراجع حسابات في ال ميكروسوفت فيسوال باسيك للتطبيقات نافذة او شباك. ثم تحقق من ملف وقت تشغيل البرمجة لـ Microsoft مربع الاختيار في المراجع - VBAProject صندوق المحادثة. انظر لقطات الشاشة:
  2. ثم اضغط OK لإغلاق مربع الحوار ، ثم احفظ وأغلق نافذة التعليمات البرمجية.

الخطوة 4: اكتب الصيغة للحصول على النتيجة

  1. الآن ، ارجع إلى ورقة العمل ، وقم بتطبيق الصيغة التالية. وبعد ذلك ، اسحب مقبض التعبئة لأسفل للحصول على جميع النتائج جنبًا إلى جنب مع تنسيقها. انظر لقطة الشاشة:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

ملحوظة: في الصيغة أعلاه:

  • E2 هي القيمة التي ستبحث عنها ؛
  • A1: C10 هو نطاق الجدول
  • 3 هو رقم عمود الجدول الذي تريد استرداد القيمة المتطابقة منه.

4.2 احتفظ بتنسيق التاريخ من القيمة المرجعة لـ VLOOKUP

عند استخدام الدالة VLOOKUP للبحث وإرجاع قيمة بتنسيق التاريخ ، قد يتم عرض النتيجة التي تم إرجاعها كرقم. للاحتفاظ بتنسيق التاريخ في النتيجة التي تم إرجاعها ، يجب عليك تضمين وظيفة VLOOKUP في دالة TEXT.

الخطوة 1: تطبيق وملء الصيغة التالية

الرجاء تطبيق الصيغة أدناه في خلية فارغة. ثم اسحب مقبض التعبئة لنسخ هذه الصيغة إلى خلايا أخرى.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

النتيجة:

تم إرجاع جميع التواريخ المتطابقة كما هو موضح أدناه:

ملحوظة: في الصيغة أعلاه:

  • E2 هي قيمة البحث ؛
  • A2: C9 هو نطاق البحث ؛
  • 3 هو رقم العمود الذي تريد إرجاع القيمة إليه ؛
  • خاطئة يشير إلى الحصول على تطابق تام ؛
  • mm/dd/yyy هو تنسيق التاريخ الذي تريد الاحتفاظ به.

4.3 إرجاع تعليق الخلية من VLOOKUP

هل احتجت في أي وقت إلى استرداد بيانات الخلية المطابقة والتعليق المرتبط بها باستخدام VLOOKUP في Excel ، كما هو موضح في لقطة الشاشة التالية؟ إذا كان الأمر كذلك ، يمكن أن تساعدك الوظيفة المعرفة من قبل المستخدم الواردة أدناه في إنجاز هذه المهمة.

الخطوة 1: انسخ الكود إلى وحدة نمطية

  1. اضغط باستمرار ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
  2. انقر إدراج > وحدة، ثم انسخ والصق الكود التالي في نافذة الوحدة النمطية.
    كود VBA: Vlookup وإرجاع قيمة مطابقة مع تعليق الخلية:
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    'Updateby Extendoffice
        Application.Volatile
        Dim xRet As Variant 'could be an error
        Dim xCell As Range
        xRet = Application.Match(LookVal, FTable.Columns(1), FType)
        If IsError(xRet) Then
            VlookupComment = "Not Found"
        Else
            Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
            VlookupComment = xCell.Value
            With Application.Caller
                If Not .Comment Is Nothing Then
                    .Comment.Delete
                End If
                If Not xCell.Comment Is Nothing Then
                    .AddComment xCell.Comment.Text
                End If
            End With
        End If
    End Function
  3. ثم احفظ وأغلق نافذة الكود.

الخطوة 2: اكتب الصيغة للحصول على النتيجة

  1. الآن ، أدخل الصيغة التالية ، واسحب مقبض التعبئة لنسخ هذه الصيغة إلى خلايا أخرى. سيعيد كل من القيم المتطابقة والتعليقات في وقت واحد ، انظر الصورة:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

ملحوظة: في الصيغة أعلاه:

  • D2 هي قيمة البحث التي تريد إرجاع قيمتها المقابلة ؛
  • A2: B9 هو جدول البيانات الذي تريد استخدامه ؛
  • 2 هو رقم العمود الذي يحتوي على القيمة المطابقة التي تريد إرجاعها ؛
  • خاطئة يشير إلى الحصول على تطابق تام.

4.4 أرقام VLOOKUP مخزنة كنص

على سبيل المثال ، لدي نطاق من البيانات حيث يكون رقم المعرف في الجدول الأصلي بتنسيق رقمي ويتم تخزين رقم المعرف في خلايا البحث كنص ، وقد تواجه خطأ # N / A عند استخدام وظيفة VLOOKUP العادية. في هذه الحالة ، لاسترداد المعلومات الصحيحة ، يمكنك التفاف دالتي TEXT و VALUE داخل دالة VLOOKUP ، وفيما يلي الصيغة لتحقيق ذلك:

الخطوة 1: تطبيق وملء الصيغة التالية

الرجاء تطبيق الصيغة التالية في خلية فارغة ، ثم اسحب مقبض التعبئة لأسفل لنسخ هذه الصيغة.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

النتيجة:

الآن ، ستحصل على النتائج الصحيحة كما هو موضح أدناه:

الملاحظات:

  • في الصيغة أعلاه:
    • D2 هي قيمة البحث التي تريد إرجاع قيمتها المقابلة ؛
    • A2: B8 هو جدول البيانات الذي تريد استخدامه ؛
    • 2 هو رقم العمود الذي يحتوي على القيمة المطابقة التي تريد إرجاعها ؛
    • 0 يشير إلى الحصول على تطابق تام.
  • تعمل هذه الصيغة أيضًا بشكل جيد إذا لم تكن متأكدًا من مكان وجود أرقام ومكان وجود نص.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations

جدول المحتويات