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

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

مقدمة عن دالة VLOOKUP – البنية والمعطيات

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

بنية دالة VLOOKUP:

المعطيات:

ملاحظات الدالة:

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

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

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

2.1.1 تنفيذ VLOOKUP بمطابقة دقيقة

عادةً، للعثور على تطابق دقيق باستخدام دالة VLOOKUP، يكفي أن تُدخل FALSE كآخر معطى.

على سبيل المثال، للحصول على درجات الرياضيات المقابلة استنادًا إلى أرقام الهوية المحددة، يُرجى اتباع ما يلي:

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

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

كيف تعمل دالة VLOOKUP؟

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

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

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

2.1.2 إجراء بحث تقريبي باستخدام VLOOKUP

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

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

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

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

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

النتيجة:

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

2.2 إجراء بحث VLOOKUP حساسية الحالة في Excel

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

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

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

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

الصيغة 1: بعد لصق الصيغة، اضغط معًا على مفاتيح «Ctrl» + «Shift» + «Enter».

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

الصيغة 2: بعد لصق الصيغة، اضغط على مفتاح «Enter».

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

النتيجة:

بعد ذلك، ستحصل على النتائج الصحيحة التي تحتاجها. راجع لقطة الشاشة التالية:

2.3 البحث عن قيم VLOOKUP من اليمين إلى اليسار في Excel

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

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

2.4 البحث عن القيمة الثانية أو الـ nth أو الأخيرة باستخدام VLOOKUP في Excel

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

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

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

هنا، قد لا تكون دالة VLOOKUP قادرة على تنفيذ هذه المهمة مباشرةً، لكن يمكنك استخدام دالة INDEX كبديل فعّال.

الخطوة 1: طبّق الصيغة واملأها في الخلايا الأخرى

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

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

النتيجة:

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

2.4.2 البحث باستخدام VLOOKUP وإرجاع آخر قيمة مطابقة

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

2.5 البحث عن القيم المطابقة بين قيمتين أو تاريخين باستخدام VLOOKUP

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

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

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

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

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

النتيجة:

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

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

إذا وجدت صعوبة في تذكُّر أو فهم الصيغة السابقة، فها هي أداة سهلة تساعدك: «Kutools لـ Excel». باستخدام ميزتها «البحث عن البيانات بين قيمتين»، يمكنك بسهولة إرجاع العنصر المقابل بناءً على قيمة أو تاريخ يقع بين قيمتين أو تاريخين.

1. انقر «Kutools» > «بحث متقدم» > «البحث عن البيانات بين قيمتين» لتفعيل هذه الميزة.
2. ثم اختر العمليات من مربع الحوار وفقًا لبياناتك.

2.6 استخدام الرموز البديلة (Wildcards) للتطابقات الجزئية في دالة VLOOKUP

في Excel، يمكنك استخدام الرموز البديلة (Wildcards) داخل دالة VLOOKUP لإجراء تطابق جزئي مع قيمة البحث. على سبيل المثال، يُمكنك استخدام VLOOKUP لإرجاع القيمة المطابقة من جدول بناءً على جزءٍ فقط من قيمة البحث.

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

الخطوة 1: طبّق الصيغة واملأها في الخلايا الأخرى

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

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

النتيجة:

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

2.7 البحث عن قيم VLOOKUP من ورقة عمل أخرى

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

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

الخطوة 1: طبّق الصيغة واملأها في الخلايا الأخرى

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

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

النتيجة:

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

2.8 البحث عن قيم VLOOKUP من ملف عمل آخر

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

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

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

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

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,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 معًا لإجراء بحث ثنائي الاتجاه.

يرجى إدخال الصيغة التالية في خلية فارغة، ثم اضغط مفتاح «Enter» للحصول على النتيجة.

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

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

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

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

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

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

الخطوة 1: طبّق أيًا من الصيغ التالية

الصيغة 1: أدخل الصيغة التالية، ثم اضغط «Enter».

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

الصيغة 2: أدخل الصيغة التالية، ثم اضغط «Ctrl» + «Shift» + «Enter».

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

النتيجة:

3.2.2 قيمة مطابقة باستخدام VLOOKUP بناءً على معيارين أو أكثر مع Kutools لـ Excel

قد يصعب تذكّر الصيغ المعقدة المذكورة أعلاه التي تحتاج إلى تطبيقها مرارًا وتكرارًا، مما قد يُبطئ كفاءة عملك. لكن «Kutools لـ Excel» يوفّر لك ميزة «البحث – البحث بشروط متعددة»، التي تتيح لك استرجاع النتيجة المطلوبة بناءً على شرط واحد أو أكثر بنقرات قليلة فقط!

1. انقر **«Kutools»** > **«بحث متقدم»** > **«البحث – البحث بشروط متعددة»** لتفعيل هذه الميزة.
2. ثم اختر العمليات من مربع الحوار بما يتوافق مع بياناتك.

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

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

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

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

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

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

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

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

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

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

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

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

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

النتيجة:

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

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

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

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

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

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

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

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

النتيجة:

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

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

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

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

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

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

النتيجة:

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

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

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

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

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

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

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

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

النتيجة:

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

3.7.2 استخدام VLOOKUP وجمع جميع القيم المطابقة في عمود أو عدة أعمدة

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

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

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

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

النتيجة:

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

3.7.3 استخدام VLOOKUP وجمع القيمة المطابقة الأولى أو جميع القيم المطابقة مع Kutools لـ Excel

قد تكون الصيغ السابقة صعبة التذكُّر، وفي هذه الحالة أنصحك بميزة قوية من «Kutools لـ Excel» تُسمى «البحث والمجموع». فباستخدامها، يمكنك تطبيق VLOOKUP لجمع القيمة المطابقة الأولى أو جميع القيم المطابقة عبر الصفوف أو الأعمدة بكل سهولة ممكنة.

1. انقر «Kutools» > «بحث متقدم» > «البحث والمجموع» لتفعيل هذه الميزة.
2. ثم اختر العمليات من مربع الحوار وفقًا لاحتياجاتك.

3.7.4 استخدام VLOOKUP وجمع جميع القيم المطابقة في الصفوف والأعمدة معًا

إذا أردت جمع القيم عند الحاجة إلى مطابقة عمود وصفٍّ معينين معًا—مثل الحصول على إجمالي مبيعات المنتج «سترة» في شهر «مارس»، كما هو موضح في لقطة الشاشة أدناه.

هنا، يمكنك الاستفادة من دالة SUMPRODUCT لإتمام هذه المهمة بسلاسة.

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

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

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

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

3.8.1 استخدام VLOOKUP لدمج جدولين بناءً على العمود الرئيسي واحد

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

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

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

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

النتيجة:

الآن، ستحصل على جدول مدمج يحتوي على عمود الطلبات، المرتبط بالجدول الأول استنادًا إلى بيانات العمود الرئيسي.

3.8.2 استخدام VLOOKUP لدمج جدولين بناءً على العمود الرئيسي متعددة

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

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

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

يُرجى تطبيق الصيغة أدناه في خلية فارغة حيث تريد وضع النتيجة، ثم اضغط مفاتيح «Ctrl» + «Shift» + «Enter» معًا للحصول على أول قيمة مطابقة، انظر لقطة الشاشة:

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

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

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

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

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

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

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

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

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

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

الخطوة 1: انسخ الكود 1 إلى وحدة كود الورقة

1. في ورقة العمل التي تحتوي على البيانات التي تريد تطبيق دالة VLOOKUP عليها، انقر بزر الماوس الأيمن على تبويب الورقة، ثم اختر «View Code» من القائمة السياقية. انظر لقطة الشاشة:
   
2. في نافذة «Microsoft Visual Basic for Applications» المفتوحة، يُرجى نسخ كود VBA أدناه ولصقه في نافذة الكود.
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
   

5. 

الخطوة 2: انسخ الكود 2 إلى نافذة الوحدة

1. ما زلت في نافذة «Microsoft Visual Basic for Applications»، انقر «Insert» > «Module»، ثم انسخ كود VBA أدناه والصقه في نافذة «Module».
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
   

4. 

الخطوة 3: حدد الخيار لمشروع VBA

1. بعد إدراج الأكواد أعلاه، انقر **«Tools» > «References»** في نافذة **«Microsoft Visual Basic for Applications»**، ثم فعِّل خانة الاختيار **«Microsoft Scripting Runtime»** في مربع الحوار **«References – VBAProject»**. راجع لقطات الشاشة التالية:
2. بعد ذلك، انقر «موافق» لإغلاق مربع الحوار، ثم احفظ وأغلق نافذة الكود.

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

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

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

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

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

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

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

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

النتيجة:

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

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

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

الخطوة 1: انسخ الكود إلى وحدة (Module)

1. اضغط معًا على مفتاحَي «ALT» و"F11" لفتح نافذة «Microsoft Visual Basic for Applications».
2. انقر «Insert» > «Module»، ثم انسخ والصق الكود التالي في نافذة «Module».
   كود 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)

   
   

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

النتيجة:

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