20+ من أمثلة VLOOKUP لمستخدمي Excel للمبتدئين والمتقدمين
تعد وظيفة VLOOKUP واحدة من أكثر الوظائف شيوعًا في Excel. سيقدم هذا البرنامج التعليمي كيفية استخدام وظيفة VLOOKUP في Excel مع عشرات الأمثلة الأساسية والمتقدمة خطوة بخطوة.
جدول المحتويات:
1. مقدمة من وظيفة VLOOKUP - النحو والحجج
- 2.1 مطابقة تامة ومطابقة تقريبية VLOOKUP
- 2.2 VLOOKUP حساس لحالة الأحرف
- 2.3 VLOOKUP من اليمين إلى اليسار
- 2.4 VLOOKUP هي القيمة المطابقة الثانية أو التاسعة أو الأخيرة
- 2.5 VLOOKUP بين قيمتين أو تاريخين محددين
- 2.6 استخدام أحرف البدل للمطابقات الجزئية في وظيفة VLOOKUP
- 2.7 قيم VLOOKUP من ورقة عمل أخرى
- 2.8 قيم VLOOKUP من مصنف آخر
- 2.9 VLOOKUP وإرجاع نص فارغ أو محدد بدلاً من 0 أو قيمة الخطأ # N / A
- 3.1 بحث ثنائي الاتجاه باستخدام وظيفة VLOOKUP (VLOOKUP في الصف والعمود)
- 3.2 قيمة مطابقة VLOOKUP بناءً على معيارين أو أكثر
- 3.3 VLOOKUP لإرجاع قيم مطابقة متعددة بشرط واحد أو أكثر
- 3.4 VLOOKUP لإرجاع صف كامل أو كامل للخلية المتطابقة
- 3.5 القيام بوظيفة VLOOKUP متعددة (VLOOKUP متداخلة) في Excel
- 3.6 VLOOKUP للتحقق مما إذا كانت القيمة موجودة بناءً على بيانات قائمة في عمود آخر
- 3.7 VLOOKUP وجمع كل القيم المتطابقة في صفوف أو أعمدة
- 3.8 VLOOKUP لدمج جدولين بناءً على عمود رئيسي واحد أو أكثر
- 3.9 قيم مطابقة VLOOKUP عبر أوراق عمل متعددة
قم بتنزيل نماذج ملفات VLOOKUP
أمثلة Vlookup الأساسية | أمثلة متقدمة Vlookup | VLOOKUP الحفاظ على تنسيق الخلية
مقدمة عن وظيفة VLOOKUP - النحو والحجج
في Excel ، تعد وظيفة VLOOKUP وظيفة قوية لمعظم مستخدمي Excel ، فهي تتيح لك البحث عن قيمة في أقصى يسار نطاق البيانات ، وإرجاع قيمة مطابقة في نفس الصف من العمود الذي حددته كما هو موضح في لقطة الشاشة التالية .
صيغة دالة VLOOKUP:
الحجج:
ابحث عن القيمة (مطلوب): القيمة التي تريد البحث عنها. يمكن أن تكون قيمة (رقم أو تاريخ أو نص) أو مرجع خلية. يجب أن يكون في العمود الأول من نطاق table_array.
Table_array (مطلوب): نطاق البيانات أو الجدول حيث يوجد عمود قيمة البحث وعمود قيمة النتيجة.
Col_index_num (مطلوب): رقم العمود الذي يحتوي على قيم الإرجاع. يبدأ بـ 1 من العمود الموجود في أقصى اليسار في صفيف الجدول.
مجموعة البحث (اختياري): قيمة منطقية تحدد ما إذا كانت وظيفة VLOOKUP هذه ستعرض مطابقة تامة أو مطابقة تقريبية.
- تطابق تقريبي - 1 / TRUE / محذوف (افتراضي): إذا لم يتم العثور على تطابق تام ، تبحث الصيغة عن أقرب تطابق - أكبر قيمة أصغر من قيمة البحث.
إشعار: في هذه الحالة ، يجب عليك فرز عمود البحث (العمود الموجود في أقصى اليسار من نطاق البيانات) بترتيب تصاعدي ، وإلا فسيعرض نتيجة خطأ أو خطأ # N / A. - تطابق تام - 0 / خطأ: يستخدم هذا للبحث عن قيمة تساوي بالضبط قيمة البحث. إذا لم يتم العثور على تطابق تام ، سيتم إرجاع قيمة الخطأ # N / A.
ملاحظات الوظيفة:
- تبحث وظيفة Vlookup فقط عن قيمة من اليسار إلى اليمين.
- تقوم وظيفة Vlookup بإجراء بحث غير حساس لحالة الأحرف.
- إذا كانت هناك قيم مطابقة متعددة تستند إلى قيمة البحث ، فسيتم إرجاع أول قيمة متطابقة فقط باستخدام وظيفة 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.
بمجرد العثور على القيمة ، ينتقل إلى اليمين في العمود الثالث ويستخرج القيمة فيه.
لذلك ، ستحصل على النتيجة كما هو موضح أدناه:
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 بين قيمتين يمكنك إرجاع العنصر المقابل بناءً على القيمة المحددة أو التاريخ بين قيمتين أو تاريخين بسهولة.
- انقر كوتولس > سوبر لوك > LOOKUP بين قيمتين لتمكين هذه الميزة.
- ثم حدد العمليات من مربع الحوار بناءً على بياناتك.
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 البرنامج التعليمي قد يقدم لك معروفًا.
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 بناءً على معيارين أو أكثر بميزة ذكية
قد يكون من الصعب تذكر الصيغ المعقدة المذكورة أعلاه والتي يجب تطبيقها بشكل متكرر ، مما قد يؤدي إلى إبطاء كفاءة عملك. لكن، كوتولس ل إكسيل يقدم بحث متعدد الشروط ميزة تسمح لك بإرجاع النتيجة المقابلة بناءً على شرط واحد أو أكثر بنقرات عديدة فقط.
- انقر كوتولس > سوبر لوك > بحث متعدد الشروط لتمكين هذه الميزة.
- ثم حدد العمليات من مربع الحوار بناءً على بياناتك.
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)
النتيجة:
الآن ، يمكنك رؤية بيانات الصف بالكامل يتم إرجاعها. انظر لقطة الشاشة:
ملحوظة: في الصيغة أعلاه:
- 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)),"")
3.5 VLOOKUP المتداخلة في Excel
في بعض الأحيان ، قد تحتاج إلى البحث عن قيم مرتبطة ببعضها البعض عبر جداول متعددة. في هذه الحالة ، يمكنك دمج عدة دالة VLOOKUP معًا للحصول على القيمة النهائية.
على سبيل المثال ، لدي ورقة عمل تحتوي على جدولين منفصلين. يسرد الجدول الأول جميع أسماء المنتجات جنبًا إلى جنب مع البائع المقابل لها. يسرد الجدول الثاني إجمالي المبيعات لكل بائع. الآن ، إذا كنت تريد العثور على مبيعات كل منتج ، كما هو موضح في لقطة الشاشة التالية ، فيمكنك دمج وظيفة VLOOKUP لإنجاز هذه المهمة.
الصيغة العامة لوظيفة VLOOKUP المتداخلة هي:
ملحوظة:
- ابحث عن القيمة هي القيمة التي تبحث عنها ؛
- 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 والعودة فقط إلى "نعم" أو "لا" إذا تم العثور على الاسم أم لا في العمود "أ" كما هو موضح أدناه لقطة الشاشة.
الخطوة 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 وجمع القيم المطابقة الأولى أو جميع القيم المطابقة في الصفوف أو الأعمدة بأسهل ما يمكن.
- انقر كوتولس > سوبر لوك > لوكاب وسوم لتمكين هذه الميزة.
- ثم حدد العمليات من مربع الحوار حسب حاجتك.
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 لدمج جدولين على أساس أعمدة مفاتيح متعددة
إذا كان الجدولان اللذان تريد ضمهما يحتويان على عدة أعمدة رئيسية ، لدمج الجداول بناءً على هذه الأعمدة المشتركة ، يرجى اتباع الخطوات أدناه.
الصيغة العامة هي:
ملحوظة:
- جدول البحث هو نطاق البيانات الذي يحتوي على بيانات البحث والسجلات المطابقة ؛
- 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: املأ الصيغة بالخلايا الأخرى
بعد ذلك ، حدد خلية الصيغة الأولى ، واسحب مقبض التعبئة لنسخ هذه الصيغة إلى خلايا أخرى حسب حاجتك:
3.9 قيم مطابقة VLOOKUP عبر أوراق عمل متعددة
هل سبق لك أن احتجت إلى إجراء VLOOKUP عبر أوراق عمل متعددة في Excel؟ على سبيل المثال ، إذا كان لديك ثلاث أوراق عمل مع نطاقات بيانات ، وتريد استرداد قيم محددة بناءً على معايير من هذه الأوراق ، يمكنك اتباع البرنامج التعليمي خطوة بخطوة قيم VLOOKUP عبر أوراق عمل متعددة لإنجاز هذه المهمة.
تحافظ قيم VLOOKUP المتطابقة على تنسيق الخلية
عند البحث عن القيم المتطابقة ، لن يتم الاحتفاظ بتنسيق الخلية الأصلي مثل لون الخط ولون الخلفية وتنسيق البيانات وما إلى ذلك. للاحتفاظ بتنسيق الخلية أو البيانات ، سيقدم هذا القسم بعض الحيل لحل الوظائف.
4.1 قيمة مطابقة VLOOKUP والحفاظ على لون الخلية وتنسيق الخط
كما نعلم جميعًا ، يمكن لوظيفة VLOOKUP العادية فقط استرداد قيمة المطابقة من نطاق بيانات آخر. ومع ذلك ، قد تكون هناك حالات ترغب فيها في الحصول على القيمة المقابلة جنبًا إلى جنب مع تنسيق الخلية ، مثل لون التعبئة ولون الخط ونمط الخط. في هذا القسم ، سنناقش كيفية استرداد القيم المطابقة مع الحفاظ على تنسيق المصدر في Excel.
يرجى القيام بالخطوات التالية للبحث وإرجاع القيمة المطابقة مع تنسيق الخلية:
الخطوة 1: انسخ الكود 1 في وحدة كود الورقة
- في ورقة العمل تحتوي على البيانات التي تريد VLOOKUP ، انقر بزر الماوس الأيمن فوق علامة تبويب الورقة وحدد عرض الرمز من قائمة السياق. انظر لقطة الشاشة:
- في فتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة ، يرجى نسخ رمز فبا أدناه في نافذة التعليمات البرمجية.
- كود 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
الخطوة 2: انسخ الكود 2 في نافذة الوحدة النمطية
- لا يزال في ميكروسوفت فيسوال باسيك للتطبيقات الإطار، انقر فوق إدراج > وحدة، ثم انسخ رمز 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
الخطوة 3: حدد خيار VBAproject
- بعد إدخال الرموز أعلاه ، انقر فوق الأدوات > مراجع حسابات في ال ميكروسوفت فيسوال باسيك للتطبيقات نافذة او شباك. ثم تحقق من ملف وقت تشغيل البرمجة لـ Microsoft مربع الاختيار في المراجع - VBAProject صندوق المحادثة. انظر لقطات الشاشة:
- ثم اضغط OK لإغلاق مربع الحوار ، ثم احفظ وأغلق نافذة التعليمات البرمجية.
الخطوة 4: اكتب الصيغة للحصول على النتيجة
- الآن ، ارجع إلى ورقة العمل ، وقم بتطبيق الصيغة التالية. وبعد ذلك ، اسحب مقبض التعبئة لأسفل للحصول على جميع النتائج جنبًا إلى جنب مع تنسيقها. انظر لقطة الشاشة:
=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: انسخ الكود إلى وحدة نمطية
- اضغط باستمرار ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
- انقر إدراج > وحدة، ثم انسخ والصق الكود التالي في نافذة الوحدة النمطية.
كود 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
- ثم احفظ وأغلق نافذة الكود.
الخطوة 2: اكتب الصيغة للحصول على النتيجة
- الآن ، أدخل الصيغة التالية ، واسحب مقبض التعبئة لنسخ هذه الصيغة إلى خلايا أخرى. سيعيد كل من القيم المتطابقة والتعليقات في وقت واحد ، انظر الصورة:
=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 يشير إلى الحصول على تطابق تام.
- تعمل هذه الصيغة أيضًا بشكل جيد إذا لم تكن متأكدًا من مكان وجود أرقام ومكان وجود نص.
أفضل أدوات إنتاجية المكتب
عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
جدول المحتويات
- 1. مقدمة من وظيفة VLOOKUP
- 2. أمثلة أساسية على VLOOKUP
- 2.1 فلوكوب دقيق وتقريبي
- تطابق تام
- تطابق تقريبي
- 2.2 Vlookup حساس لحالة الأحرف
- 2.3 Vlookup من اليمين إلى اليسار
- 2.4 انظر إلى الثاني أو التاسع أو الأخير قيمة مطابقة
- القيمة المطابقة الثانية أو التاسعة
- آخر قيمة مطابقة
- 2.5 النظر بين قيمتين
- باستخدام الصيغة
- باستخدام ميزة مفيدة - Kutools
- 2.6 مباراة جزئية Vlookup
- 2.7 Vlookup من ورقة عمل أخرى
- 2.8 Vlookup من مصنف آخر
- 2.9 إصلاح قيمة خطأ 0 أو # N / A في Vlookup
- 3. أمثلة متقدمة على VLOOKUP
- 3.1 بحث ثنائي الاتجاه
- 3.2 Vlookup على أساس المزيد من المعايير
- باستخدام الصيغ
- باستخدام ميزة ذكية - Kutools
- 3.3 قيم مطابقة متعددة Vlookup
- إرجاع القيم أفقيا
- إرجاع القيم عموديًا
- إرجاع القيم في خلية واحدة
- 3.4 صف كامل Vlookup
- 3.5 متداخلة Vlookup
- 3.6 تحقق من وجود القيمة
- 3.7 نظر و مجموع
- في الصفوف
- في الأعمدة
- مع ميزة قوية - Kutools
- في كل من الصفوف والأعمدة
- 3.8 Vlookup لدمج جدولين
- بعمود مفتاح واحد
- بعدة أعمدة رئيسية
- 3.9 Vlookup عبر أوراق عمل متعددة
- 4. VLOOKUP والحفاظ على تنسيق الخلية
- 4.1 حافظ على تنسيق اللون والخط
- 4.2 احتفظ بتنسيق التاريخ
- 4.3 احتفظ بتعليق الخلية
- 4.4 الأرقام المخزنة كنص
- أفضل أدوات إنتاجية المكتب
- التعليقات