اكسل

ابحث في أوراق عمل متعددة عن القيمة

Search Multiple Worksheets

صيغة Excel: ابحث في أوراق عمل متعددة عن القيمةصيغة عامة
= COUNTIF ( INDIRECT ('''&sheetname&''!'&'range'),criteria)
ملخص

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





 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),$C)

السياق - عينة البيانات

يحتوي المصنف على 4 أوراق عمل إجمالاً. الورقة 1 و Sheet2 ، و Sheet3 يحتوي كل منها على 1000 اسم أولي عشوائي يبدو كالتالي:

بيانات نموذجية - ابحث في المصنف بأكمله أو أوراق متعددة





تفسير

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

كيفية الحصول على pi في Excel

من خلال العمل من الداخل إلى الخارج ، يتم استخدام هذا التعبير لبناء مرجع كامل للورقة:



 
'''&B7&''!'&'1:1048576'

تتم إضافة علامات الاقتباس الفردية للسماح بأسماء الأوراق بمسافات ، وعلامة التعجب هي بناء جملة قياسي للنطاقات التي تتضمن اسم ورقة. النص '1: 1048576' هو نطاق يتضمن كل صف في ورقة العمل.

بعد تقييم B7 ، وسلسلة القيم ، يرجع التعبير أعلاه:

كيفية فرز البيانات في التفوق باستخدام الصيغة
 
''Sheet1'!1:1048576'

الذي يذهب إلى دالة غير مباشرة كوسيطة 'ref_text'. تقوم INDIRECT بتقييم هذا النص وإرجاع مرجع قياسي لكل خلية في الورقة 1 . ينتقل هذا إلى دالة COUNTIF كنطاق. يتم توفير المعايير على أنها المرجع المطلق إلى C4 (مؤمن بحيث يمكن نسخ الصيغة لأسفل العمود C).

تقوم COUNTIF بعد ذلك بإرجاع عدد كل الخلايا التي لها قيمة تساوي 'ماري' ، 25 في هذه الحالة.

كيفية إنشاء مخطط مركب في Excel

ملاحظة: COUNTIF ليست حساسة لحالة الأحرف.

يحتوي في مقابل يساوي

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

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1048576'),'*'&C4&'*')

الآن ستعد COUNTIF الخلايا التي تحتوي على السلسلة الفرعية 'John' في أي مكان في الخلية.

أداء

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

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

 
= COUNTIF ( INDIRECT ('''&B7&''!'&'1:1000'),$C)
المؤلف ديف برونز


^