أكثر من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.

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

الخطوة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 VLOOKUP للقيمة الثانية أو n أو الأخيرة المطابقة في Excel

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

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

افترض أن لديك قائمة أسماء في العمود 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 And Return The Last Matching Value في الحصول على القيمة الأخيرة المطابقة بالتفصيل.

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)

النتيجة:

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

 2.5.2 VLOOKUP للقيم المطابقة بين قيمتين أو تاريخين باستخدام ميزة سهلة

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

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

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

في Excel، يمكن استخدام الرموز النائبة ضمن دالة 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 To Return Blank Or Specific Value Instead Of0 Or 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، والآن ترغب في إرجاع جميع الأسماء أفقيًا من "US" كما هو موضح في الصورة أدناه. لحل هذه المهمة، يرجى النقر هنا للحصول على النتيجة خطوة بخطوة.

 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 وجمع جميع القيم المطابقة في الصفوف والأعمدة معًا

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

هنا، يمكنك استخدام دالة 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 Values Across Multiple Worksheets" لإنجاز هذه المهمة.

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

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

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

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

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

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

1. في ورقة العمل التي تحتوي على البيانات التي تريد استخدام VLOOKUP عليها، انقر بزر الماوس الأيمن على تبويب الورقة واختر "عرض الكود" من القائمة. انظر الصورة:
   
2. في نافذة "Microsoft Visual Basic for Applications" المفتوحة، يرجى نسخ كود VBA أدناه إلى نافذة الكود.
3. كود VBA1: 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"، انقر على "إدراج" > "وحدة"، ثم انسخ كود VBA2 أدناه إلى نافذة "الوحدة".
2. كود VBA2: 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. بعد إدراج الأكواد أعلاه، انقر على "أدوات" > "مراجع" في نافذة "Microsoft Visual Basic for Applications". ثم ضع علامة على مربع "Microsoft Scripting Runtime" في مربع الحوار "المراجع – 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، كما هو موضح في الصورة التالية؟ إذا كان الأمر كذلك، يمكن أن تساعدك الدالة المعرفة من قبل المستخدم أدناه في إنجاز هذه المهمة.

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

1. اضغط باستمرار على مفاتيح "ALT" + "F11" لفتح نافذة "Microsoft Visual Basic for Applications".
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)

   
   

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

النتيجة:

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