Note: The other languages of the website are Google-translated. Back to English
تسجيل الدخول  \/ 
x
or
x
حساب جديد  \/ 
x

or

وظيفة VLOOKUP مع بعض الأمثلة الأساسية والمتقدمة في Excel

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

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

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

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

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

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

5. قم بتنزيل نماذج ملفات VLOOKUP


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

صيغة دالة VLOOKUP:

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

الحجج:

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

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

Col_index_num: عدد العمود الذي سيتم عرض القيمة المتطابقة منه. يبدأ بـ 1 من العمود الموجود في أقصى اليسار في صفيف الجدول.

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

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

الملاحظات:

  • 1. تبحث وظيفة Vlookup فقط عن القيمة من اليسار إلى اليمين.
  • 2. إذا كانت هناك قيم مطابقة متعددة بناءً على قيمة البحث ، فسيتم إرجاع أول قيمة متطابقة فقط باستخدام وظيفة Vlookup.
  • 3. سيعيد قيمة الخطأ # N / A إذا تعذر العثور على قيمة البحث.

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

1. قم بعمل المطابقة التامة Vlookup والمطابقة التقريبية Vlookup

قم بعمل تطابق تام Vlookup في Excel

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

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

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

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

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

الملاحظات:

  • 1. في الصيغة أعلاه ، F2 هي القيمة التي تريد إرجاع قيمتها المطابقة ، A2: D7 هي مصفوفة الجدول ، العدد 3 هو رقم العمود الذي يتم إرجاع القيمة المطابقة منه و خاطئة يشير إلى المطابقة التامة.
  • 2. إذا لم يتم العثور على قيمة المعايير في نطاق البيانات ، سيتم عرض قيمة الخطأ # N / A.

قم بعمل تطابق تقريبي Vlookup في Excel

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

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

1. أدخل الصيغة التالية في خلية حيث تريد وضع النتيجة:

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

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

الملاحظات:

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

2. قم بإجراء بحث حساس لحالة الأحرف في Excel

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

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

الصيغة 1: استخدام وظائف EXACT و INDEX و MATCH

1. الرجاء إدخال صيغة المصفوفة أدناه أو نسخها في خلية فارغة حيث تريد الحصول على النتيجة:

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

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

الملاحظات:

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

الصيغة 2: استخدام دالات البحث والضبط

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

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

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

الملاحظات:

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

3 قيم Vlookup من اليمين إلى اليسار في Excel

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

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


4 ابحث عن القيمة المطابقة الثانية أو التاسعة أو الأخيرة في Excel

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

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

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

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

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

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

ملاحظة:

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

Vlookup وإرجاع آخر قيمة مطابقة

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


5 Vlookup تطابق القيم بين قيمتين أو تواريخ معينة

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

يطابق Vlookup القيم بين قيمتين محددتين أو تواريخ مع الصيغة

1. أولاً ، يجب أن يكون الجدول الأصلي عبارة عن نطاق بيانات تم فرزه. ثم انسخ أو أدخل الصيغة التالية في خلية فارغة:

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

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

الملاحظات:

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

يقوم Vlookup بمطابقة القيم بين قيمتين محددتين أو تواريخ مع ميزة مفيدة

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


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

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

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

1. وظيفة Vlookup العادية لا تعمل بشكل صحيح ، تحتاج إلى ربط النص أو مرجع الخلية بحرف بدل ، يرجى نسخ أو إدخال الصيغة التالية في خلية فارغة:

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

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

الملاحظات:

  • 1. في الصيغة أعلاه ، E2 & "*" هي قيمة البحث ، القيمة في E2 و * أحرف البدل (تشير "*" إلى أي حرف واحد أو أي أحرف) ، A2: C11 هو نطاق البحث ، الرقم 3 العمود الذي يحتوي على القيمة المراد إرجاعها.
  • 2. Vlookup عند استخدام أحرف البدل ، يجب عليك تعيين وضع المطابقة التامة مع FALSE أو 0 للوسيطة الأخيرة في وظيفة Vlookup.

نصائح:

1. ابحث عن القيم المطابقة التي تنتهي بقيمة محددة وأعدها ، يرجى تطبيق هذه الصيغة: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

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


7. قيم Vlookup من ورقة عمل أخرى

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

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

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

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

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

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

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

8. قيم Vlookup من مصنف آخر

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

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

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

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

2. بعد ذلك ، اسحب هذه الصيغة وانسخها إلى الخلايا الأخرى التي تحتاجها ، انظر الصورة:

الملاحظات:

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

9. Vlookup وإرجاع نص فارغ أو محدد بدلاً من 0 أو قيمة الخطأ # N / A

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


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

1. بحث ثنائي الاتجاه باستخدام وظيفة Vlookup (Vlookup في الصف والعمود)

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

الصيغة 1: استخدام وظائف VLOOKUP و MATCH

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

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

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

  • H1: قيمة البحث في العمود التي تريد الحصول على القيمة المقابلة بناءً عليها ؛
  • A2: E6: نطاق البيانات بما في ذلك رؤوس الصفوف ؛
  • H2: قيمة البحث في الصف التي تريد الحصول على القيمة المقابلة بناءً عليها ؛
  • A1: E1: خلايا رؤوس الأعمدة.

الصيغة 2: استخدام الدالتين INDEX و MATCH

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

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

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

  • B2: E6: نطاق البيانات لإرجاع العنصر المطابق منه ؛
  • H1: قيمة البحث في العمود التي تريد الحصول على القيمة المقابلة بناءً عليها ؛
  • A2: A6: تحتوي رؤوس الصفوف على المنتج الذي تريد البحث عنه.
  • H2: قيمة البحث في الصف التي تريد الحصول على القيمة المقابلة بناءً عليها ؛
  • B1: E1: رؤوس الأعمدة تحتوي على ربع السنة الذي تريد البحث عنه.

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

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

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

الصيغة 1: استخدام وظيفة LOOKUP

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

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

الملاحظات:

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

الصيغة 2: استخدام الدالتين INDEXT و MATCH

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

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

ثم اضغط على Ctrl + Shift + Enter معًا للحصول على القيمة النسبية التي تحتاجها. انظر لقطة الشاشة:

الملاحظات:

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

3 Vlookup لإرجاع قيم مطابقة متعددة بشرط واحد أو أكثر

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

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

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

من أجل Vlookup وإرجاع جميع القيم المطابقة بناءً على قيمة واحدة محددة أفقيًا ، فإن الصيغة العامة هي:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
ملاحظة: m هو رقم صف الخلية الأولى في نطاق الإرجاع ناقص 1.
      n هو رقم العمود لخلية الصيغة الأولى مطروحًا منه 1.

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

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

نصائح:

إذا كانت هناك قيم متطابقة مكررة في القائمة التي تم إرجاعها ، لتجاهل التكرارات ، يرجى استخدام هذه الصيغ ، ثم الضغط على أدخل للحصول على النتيجة الأولى: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

استمر في إدخال هذه الصيغة: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") في خلية بجانب النتيجة الأولى ، ثم اضغط على كترل + شيفت + إنتر مفاتيح معًا للحصول على النتيجة الثانية ، ثم اسحب هذه الصيغة إلى الخلايا اليمنى للحصول على جميع القيم المطابقة الأخرى حتى يتم عرض خلية فارغة ، انظر لقطة الشاشة:


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

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

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
ملاحظة: m هو رقم صف الخلية الأولى في نطاق الإرجاع ناقص 1.
      n هو رقم العمود لخلية الصيغة الأولى مطروحًا منه 1.

1. قم بتطبيق الصيغة التالية في خلية فارغة حيث تريد إخراج النتيجة:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

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

ملاحظة: لمزيد من المعايير ، ما عليك سوى الانضمام إلى lookup_value و lookup_range في الصيغة ، مثل: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


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

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

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

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
ملاحظة: m هو رقم صف الخلية الأولى في نطاق الإرجاع ناقص 1.
      n هو رقم صف خلية الصيغة الأولى مطروحًا منه 1.

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

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

نصائح:

لتجاهل التكرارات في قيم المطابقة التي تم إرجاعها ، يرجى استخدام هذه الصيغ: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

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


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

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

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
ملاحظة: m هو رقم صف الخلية الأولى في نطاق الإرجاع ناقص 1.
      n هو رقم صف خلية الصيغة الأولى مطروحًا منه 1.

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. ثم اسحب خلية الصيغة لأسفل إلى خلايا أخرى حتى يتم عرض خلية فارغة ، انظر لقطة الشاشة:

ملاحظة: لمزيد من المعايير ، ما عليك سوى الانضمام إلى lookup_value و lookup_range في الصيغة ، مثل: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


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

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

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

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

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

نصائح:

لتجاهل التكرارات في قيم المطابقة التي تم إرجاعها ، يرجى استخدام هذه الصيغ: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


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

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

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

الملاحظات:

1. لا تتوفر وظيفة TEXTJOIN إلا في Excel 2019 و Office 365.

2. إذا كنت تستخدم Excel 2016 والإصدارات السابقة ، فالرجاء استخدام الوظيفة المحددة بواسطة المستخدم في المقالات التالية:


4. Vlookup لإرجاع صف كامل أو كامل من الخلية المتطابقة

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

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

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

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

ملاحظة: في الصيغة أعلاه ، 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)),"")، ثم اضغط كترل + شيفت + إنتر مفاتيح معًا للحصول على النتيجة الأولى ، ثم اسحب مقبض التعبئة إلى الخلايا مباشرةً ، انظر لقطة الشاشة:

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


5 قم بعمل وظيفة Vlookup متعددة (متداخلة Vlookup) في Excel

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

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

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

ملاحظة:

  • ابحث عن القيمة: القيمة التي تبحث عنها ؛
  • Table1, Table2, Table3، ...: الجداول التي توجد بها قيمة البحث وقيمة الإرجاع ؛
  • العقيد: رقم العمود في الجدول الذي تريد إرجاع القيمة المطابقة منه.
  • 0: يستخدم هذا لمطابقة تامة.

1. الرجاء تطبيق الصيغة التالية في خلية فارغة حيث تريد وضع النتيجة:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

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

الملاحظات:

  • 1. في الصيغة أعلاه ، J3 هي القيمة التي تبحث عنها ؛ A3: B7, D3: E7, G3: H7 هي نطاقات الجدول التي توجد فيها قيمة البحث وقيمة الإرجاع ؛ الرقم 2 هو رقم العمود في النطاق لإرجاع القيمة المطابقة منه.
  • 2. إذا تعذر العثور على قيمة البحث ، يتم عرض قيمة خطأ ، لاستبدال الخطأ بنص يمكن قراءته ، يرجى استخدام هذه الصيغة: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

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

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

1. الرجاء تطبيق الصيغة التالية في خلية فارغة:

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

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

ملاحظة: في الصيغة أعلاه ، C2 هي قيمة البحث التي تريد التحقق منها ؛ A2: A10 هي قائمة النطاق من حيث سيتم العثور على قيم البحث ؛ الرقم 1 هو رقم العمود حيث تريد جلب القيمة في النطاق الخاص بك.


7 ابحث واجمع كل القيم المتطابقة في صفوف أو أعمدة

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

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

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

بحث وجمع القيم المتطابقة الأولى في صف:

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

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

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

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


ابحث واجمع كل القيم المتطابقة في صفوف متعددة:

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

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

ملاحظة: في الصيغة أعلاه: H2 هي قيمة البحث التي تبحث عنها ؛ A2: A9 هي رؤوس الصفوف التي تحتوي على قيمة البحث ؛ B2: F9 نطاق البيانات للقيم الرقمية التي تريد جمعها.


ابحث واجمع كل القيم المتطابقة في عمود أو أعمدة متعددة

Vlookup وجمع القيم المتطابقة الأولى في عمود:

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

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

=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))

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


ابحث وقم بتلخيص القيم المتطابقة الأولى أو جميع القيم المتطابقة بميزة قوية

ربما يصعب عليك تذكر الصيغ أعلاه ، في هذه الحالة ، سأوصي بميزة مفيدة - البحث و المجموع of كوتولس ل إكسيل، مع هذه الميزة ، يمكنك الحصول على النتيجة بسهولة قدر الإمكان.    انقر لتنزيل Kutools for Excel الآن!


ابحث واجمع كل القيم المتطابقة في كل من الصفوف والأعمدة

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

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

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

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


8 Vlookup لدمج جدولين بناءً على عمود رئيسي واحد أو أكثر

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

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

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

الصيغة 1: استخدام وظيفة VLOOKUP

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

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

ملاحظة: في الصيغة أعلاه ، A2 هي القيمة التي تبحث عنها ، E2: F8 هو الجدول للبحث ، الرقم 2 هو رقم العمود في الجدول المراد استرداد القيمة منه.

الصيغة 2: استخدام الدالتين INDEX و MATCH

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

يرجى نسخ أو إدخال الصيغة أدناه في خلية فارغة ، ثم انسخ الصيغة أسفل العمود ، وتم ربط عمود الطلب بالجدول الأول ، انظر الصورة:

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

ملاحظة: في الصيغة أعلاه ، A2 هي قيمة البحث التي تبحث عنها ، E2: E8 هو نطاق البيانات التي تريد إرجاعها ، F2: F8 هو نطاق البحث الذي يحتوي على قيمة البحث.


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

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

الصيغة العامة لدمج جدولين بناءً على عدة أعمدة رئيسية هي:

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

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

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

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

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

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

9 قيم مطابقة Vlookup عبر أوراق عمل متعددة

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


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

1 Vlookup للحصول على تنسيق الخلية (لون الخلية ، لون الخط) مع قيمة البحث

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

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

1. في ورقة العمل تحتوي على البيانات التي تريدها إلى Vlookup ، انقر بزر الماوس الأيمن فوق علامة تبويب الورقة وحدد عرض الرمز من قائمة السياق. انظر لقطة الشاشة:

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

كود VBA 1: Vlookup للحصول على تنسيق الخلية مع قيمة البحث

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

3. لا يزال في ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة، ثم انسخ رمز VBA 2 أدناه في نافذة الوحدة النمطية.

كود VBA 2: Vlookup للحصول على تنسيق الخلية مع قيمة البحث

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

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

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

ملاحظة: في الصيغة أعلاه ، E2 هي القيمة التي ستبحث عنها ، A1: C10 هو نطاق الجدول والرقم 3 هو رقم عمود الجدول الذي تريد إرجاع القيمة المتطابقة إليه.

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


2 احتفظ بتنسيق التاريخ من القيمة المرجعة من Vlookup

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

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

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

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


3 Vlookup وإرجاع قيمة مطابقة مع تعليق الخلية

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

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. ثم احفظ وأغلق نافذة الكود ، أدخل هذه الصيغة: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) في خلية فارغة لتحديد النتيجة ، ثم اسحب مقبض التعبئة لنسخ هذه الصيغة إلى خلايا أخرى ، والآن ، يتم إرجاع القيم المتطابقة وكذلك التعليقات في الحال ، انظر لقطة الشاشة:

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


4. تعامل مع النص والأرقام الحقيقية في Vlookup

على سبيل المثال ، لدي مجموعة من البيانات ، ورقم المعرف في الجدول الأصلي هو تنسيق الأرقام ، في خلية البحث المخزنة كنص ، عند تطبيق وظيفة Vlookup العادية ، يتم عرض نتيجة خطأ # N / A على النحو التالي لقطة الشاشة مبين. في هذه الحالة ، كيف يمكنك الحصول على المعلومات الصحيحة إذا كان رقم البحث والرقم الأصلي في الجدول لهما تنسيق بيانات مختلف؟

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

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

الملاحظات:

  • 1. في الصيغة أعلاه ، D2 هي قيمة البحث التي تريد إرجاع قيمتها المقابلة ، A2: B8 هو جدول البيانات الذي تريد استخدامه ، الرقم 2 هو رقم العمود الذي يحتوي على القيمة المطابقة التي تريد إرجاعها.
  • 2. تعمل هذه الصيغة أيضًا بشكل جيد إذا لم تكن متأكدًا من مكان وجود الأرقام ومكان النص.

قم بتنزيل نماذج ملفات VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى...
  • دمج الخلايا / الصفوف / الأعمدة وحفظ البيانات. تقسيم محتوى الخلايا ؛ اجمع الصفوف المكررة والمجموع / المتوسط... منع تكرار الخلايا؛ قارن النطاقات...
  • حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
  • نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
  • المفضلة وإدراج الصيغ بسرعةوالنطاقات والمخططات والصور ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات...
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
  • اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF...
  • تجميع الجدول المحوري حسب رقم الأسبوع واليوم من الأسبوع والمزيد ... إظهار الخلايا غير المؤمنة والمغلقة بألوان مختلفة قم بتمييز الخلايا التي لها صيغة / اسم...
علامة تبويب kte 201905
  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
أوفيسيتاب القاع
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.