كيفية تنفيذ البحث الرأسي عبر أوراق عمل متعددة؟
يُعتبر VLOOKUP واحدة من أكثر الوظائف استخدامًا في Excel للبحث واسترجاع البيانات. ومع ذلك، عند التعامل مع أوراق عمل متعددة، لن تكون وظيفة VLOOKUP المباشرة كافية لأنها تعمل ضمن نطاق واحد فقط. لنفترض أن لدي ثلاث أوراق عمل تحتوي على نطاقات بيانات، وأريد الآن الحصول على جزء من القيم المقابلة بناءً على معايير من هذه الأوراق الثلاثة. يشرح هذا الدليل كيفية تنفيذ VLOOKUP متعدد الأوراق باستخدام طرق مختلفة.
 |  |  |  |  |
البحث عن القيم باستخدام صيغة صفيف
البحث عن القيم باستخدام Kutools لـ Excel
البحث عن القيم باستخدام صيغة عادية
البحث عن القيم باستخدام صيغة صفيف
للاستخدام هذه الصيغة الصفيف، يجب عليك إعطاء هذه الأوراق الثلاثة اسم نطاق، يرجى سرد أسماء أوراق العمل في ورقة عمل جديدة كما هو موضح في لقطة الشاشة التالية:
1. أعطِ هذه الأوراق اسم نطاق، حدد أسماء الأوراق، واكتب اسمًا في مربع الاسم المجاور لشريط الصيغة، في هذه الحالة، سأكتب Sheetlist كاسم النطاق، ثم اضغط على مفتاح Enter.
2. بعد ذلك، يمكنك إدخال الصيغة الطويلة التالية في الخلية المحددة:
=VLOOKUP(A2,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A$2:$B$6"),A2)>0),0))&"'!$A$2:$B$6"),2,FALSE)3. ثم اضغط على Ctrl + Shift + Enter معًا للحصول على أول قيمة مقابلة، وبعد ذلك اسحب مقبض التعبئة إلى الأسفل للخلايا التي تريد تطبيق هذه الصيغة عليها، وسيتم إرجاع جميع القيم ذات الصلة لكل صف كما يلي:
ملاحظات:
1. في الصيغة أعلاه:
- A2: هي مرجع الخلية الذي تريد إرجاع قيمته المقابلة؛
- Sheetlist: هو اسم النطاق لأسماء أوراق العمل التي تم إنشاؤها في الخطوة الأولى؛
- A2:B6: هو نطاق البيانات للأوراق التي تحتاج إلى البحث فيها؛
- 2: يشير إلى رقم العمود الذي يتم فيه إرجاع القيمة المتطابقة.
2. إذا كانت القيمة المحددة التي تبحث عنها غير موجودة، سيتم عرض قيمة #N/A.
البحث عن القيم باستخدام Kutools لـ Excel
يوفر لك Kutools لـ Excel ميزة فعالة وبسيطة - "بحث متقدم عبر عدة أوراق"، مما يساعدك على تنفيذ استعلامات VLOOKUP بسهولة عبر أوراق عمل متعددة. بدون الحاجة إلى صيغ معقدة، يمكنك البحث بسرعة واستخراج البيانات المطلوبة من عدة أوراق. ببضع نقرات فقط، يمكنك إكمال مطابقة ودمج البيانات من جداول متعددة، مما يحسن بشكل كبير كفاءة العمل. بالإضافة إلى ذلك، يدعم Kutools حفظ المخططات المستخدمة مسبقًا، مما يجعل من السهل استخدامها مباشرة في المهام المستقبلية.
بعد تثبيت Kutools لـ Excel، يرجى القيام بما يلي:
1. انقر فوق "Kutools" > "بحث متقدم" > "بحث في عدة أوراق"، انظر لقطة الشاشة:
2. في مربع الحوار "بحث في عدة أوراق"، يرجى القيام بالعمليات التالية:
- حدد خلايا الإخراج وخلايا القيم المطلوبة من قسم "نطاق الإخراج" و"قيم البحث" على حدة؛
- ثم انقر فوق زر "إضافة" لتحديد وإضافة نطاق البيانات من الأوراق الأخرى واحدًا تلو الآخر إلى قائمة "نطاق البيانات".
- أخيرًا، انقر فوق زر موافق.
النتيجة: تم إرجاع جميع السجلات المتطابقة، انظر لقطات الشاشة:
| | | |  |
انقر لتنزيل Kutools لـ Excel وتجربته مجانًا الآن!
البحث عن القيم باستخدام صيغة عادية
إذا كنت لا ترغب في إنشاء اسم نطاق ولا تعرف الكثير عن صيغ الصفيف، هناك أيضًا صيغة عادية يمكنها مساعدتك.
1. يرجى كتابة الصيغة التالية في الخلية التي تحتاج إليها:
=IFERROR(VLOOKUP($A2,Sheet1!$A$2:$B$6,2,FALSE),IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$6,2,FALSE),VLOOKUP($A2,Sheet3!$A$2:$B$6,2,FALSE)))2. ثم اسحب مقبض التعبئة إلى أسفل للنطاق الذي تريد تطبيق هذه الصيغة عليه، انظر لقطة الشاشة:
ملاحظات:
1. في الصيغة أعلاه:
- A2: هو مرجع الخلية الذي تريد إرجاع قيمته المقابلة؛
- Sheet1, Sheet2, Sheet3: هي أسماء الأوراق التي تحتوي على البيانات التي تريد استخدامها؛
- A2:B6: هو نطاق البيانات للأوراق التي تحتاج إلى البحث فيها؛
- 2: يشير إلى رقم العمود الذي يتم فيه إرجاع القيمة المتطابقة.
2. لفهم هذه الصيغة بسهولة أكبر، في الواقع تتكون الصيغة الطويلة من عدة وظائف VLOOKUP مرتبطة بوظيفة IFERROR. إذا كان لديك المزيد من الأوراق، ما عليك سوى إضافة وظيفة VLOOKUP بالتزامن مع IFERROR بعد الصيغة.
3. إذا كانت القيمة المحددة التي تبحث عنها غير موجودة، سيتم عرض قيمة #N/A.
الاستنتاج:
قدم هذا الدليل ثلاث طرق مميزة لتنفيذ VLOOKUP عبر أوراق عمل متعددة، لتلبية احتياجات مستخدمين مختلفين ومستويات المهارة:
- صيغة الصفيف: طريقة قوية للمستخدمين المتقدمين، تمكن صيغ الصفيف عمليات البحث الديناميكية والمرونة عبر أوراق متعددة دون الحاجة إلى أدوات إضافية. على الرغم من أنها تتطلب بعض الخبرة في الصيغ، إلا أنها فعالة وفعالة لمجموعات البيانات المعقدة.
- Kutools لـ Excel: اختيار ممتاز للمستخدمين الذين يبحثون عن حل سهل الاستخدام ومُبسّط. باستخدام أدواته المدمجة، يُبسّط Kutools عملية البحث عبر الأوراق المتعددة، مما يوفر الوقت والجهد، خاصةً للمستخدمين الذين ليسوا على دراية بوظائف Excel المتقدمة.
- صيغة عادية: استخدام دوال IFERROR أو IF المتداخلة هو طريقة عملية وبسيطة لتحقيق عمليات البحث عبر أوراق متعددة. هذا النهج مثالي لمجموعات البيانات الصغيرة أو عندما تحتاج إلى حل سريع دون أدوات إضافية أو تقنيات متقدمة.
اختيار الطريقة المناسبة يعتمد على مدى معرفتك بـ Excel، تعقيد مهمتك، والأدوات المتاحة لديك. إذا كنت مهتمًا باستكشاف المزيد من نصائح وحيل Excel، يقدم موقعنا آلاف الدروس لمساعدتك على إتقان Excel.
مقالات ذات صلة:
- Vlookup لمطابقة القيم من الأسفل إلى الأعلى في Excel
- عادةً، يمكن لوظيفة Vlookup مساعدتك في العثور على البيانات من الأعلى إلى الأسفل للحصول على أول قيمة مطابقة من القائمة. ولكن في بعض الأحيان، تحتاج إلى البحث من الأسفل إلى الأعلى لاستخراج آخر قيمة مطابقة. هل لديك أي أفكار جيدة للتعامل مع هذه المهمة في Excel؟
- Vlookup وإرجاع الصف بأكمله للقيمة المطابقة في Excel
- عادةً، يمكنك استخدام Vlookup لإرجاع القيمة المطابقة من نطاق البيانات، ولكن هل حاولت العثور على وإرجاع الصف بأكمله بناءً على معايير محددة كما هو موضح في لقطة الشاشة التالية.
- Vlookup وربط القيم المطابقة المتعددة في Excel
- كما نعلم جميعًا، يمكن لوظيفة Vlookup في Excel مساعدتنا في البحث عن قيمة وإرجاع البيانات المقابلة في عمود آخر، لكن بشكل عام، يمكنها فقط الحصول على أول قيمة متعلقة إذا كان هناك بيانات متطابقة متعددة. في هذه المقالة، سأتحدث عن كيفية البحث عن القيم وربط القيم المطابقة المتعددة في خلية واحدة أو قائمة عمودية.
- Vlookup عبر أوراق متعددة وجمع النتائج في Excel
- لنفترض أن لدي أربع أوراق عمل لها نفس التنسيق، وأريد الآن العثور على "TV set" في عمود المنتج لكل ورقة، والحصول على العدد الإجمالي للطلبات عبر تلك الأوراق كما هو موضح في لقطة الشاشة التالية. كيف يمكنني حل هذه المشكلة بطريقة سهلة وسريعة في Excel؟
- Vlookup وإرجاع القيمة المطابقة في القائمة المُرشحة
- يمكن لوظيفة VLOOKUP مساعدتك في العثور على وإرجاع أول قيمة مطابقة بشكل افتراضي سواء كان النطاق طبيعيًا أو قائمة مُرشحة. في بعض الأحيان، قد ترغب فقط في البحث وإرجاع القيم المرئية فقط إذا كانت هناك قائمة مُرشحة. كيف يمكنك التعامل مع هذه المهمة في Excel؟
أفضل أدوات الإنتاجية لمكتب العمل
عزز مهاراتك في Excel مع Kutools لـ Excel، واختبر الكفاءة كما لم يحدث من قبل. Kutools لـ Excel يقدم أكثر من300 ميزة متقدمة لزيادة الإنتاجية وتوفير وقت الحفظ. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
Office Tab يقدم واجهة التبويب لـ Office، ويجعل عملك أسهل بكثير
- تمكين تحرير وقراءة المستندات عبر التبويبات في Word وExcel وPowerPoint.
- افتح وأنشئ عدة مستندات في تبويبات جديدة ضمن نفس النافذة، بدلاً من فتحها في نوافذ منفصلة.
- يزيد إنتاجيتك بنسبة50%، ويقلل مئات النقرات بالماوس يومياً!