اكسل

حدد النطاق بناءً على قيمة الخلية

Define Range Based Cell Value

صيغة Excel: حدد النطاق بناءً على قيمة الخليةصيغة عامة
= SUM (firstcell: INDEX (data,rows,cols))
ملخص

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



 
= SUM (C5: INDEX (data,J5,J6))

حيث 'البيانات' هي نطاق مسمى B5:G9.

تفسير

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





كيفية إضافة الأس في Excel

داخل دالة الجمع ، يكون المرجع الأول هو الخلية الأولى في النطاق الذي يغطي جميع الخلايا الممكنة:

 
= SUM (C5:

للحصول على الخلية الأخيرة ، نستخدم INDEX. هنا ، نمنح INDEX ملف نطاق مسمى 'البيانات' ، وهي أقصى نطاق ممكن للقيم ، وكذلك القيم من J5 (الصفوف) و J6 (الأعمدة). لا تُرجع الدالة INDEX نطاقًا ، بل تُرجع فقط خلية واحدة في ذلك الموقع ، E9 في المثال:



العد إذا كانت الخلية تحتوي على نص معين
 
 INDEX (data,J5,J6) // returns E9

يتم تقليل الصيغة الأصلية إلى:

 
= SUM (C5:E9)

التي تُرجع 300 ، مجموع كل القيم في C5: E9.

كيفية إدراج فاصل صفحة في Excel 2010

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

بديل مع OFFSET

يمكنك إنشاء صيغ مماثلة باستخدام ملف دالة OFFSET ، ظاهر أدناه:

 
= SUM ( OFFSET (C5,0,0,J5,J6)) // sum = AVERAGE ( OFFSET (C5,0,0,J5,J6)) // average

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

المؤلف ديف برونز


^