Excel SUM و INDIRECT Dynamic Range Formula

يحتوي Microsoft Excel على بعض الحيل الرائعة واستخدام صيغ النطاق الديناميكي SUM و INDIRECT هي طريقتان للتعامل مع البيانات التي لديك بسهولة.

SUM - INDIRECT Formula Overview

يؤدي استخدام الدالة INDIRECT في صيغ Excel إلى تسهيل تغيير نطاق مراجع الخلايا المستخدمة في الصيغة دون الحاجة إلى تحرير الصيغة نفسها.

يمكن استخدام INDIRECT مع عدد من الوظائف التي تقبل مرجع خلية كوسيطة مثل وظائف OFFSET و SUM.

في الحالة الأخيرة ، يمكن استخدام INDIRECT كوسيطة للدالة SUM إنشاء نطاق ديناميكي من مراجع الخلايا التي تضيفها الدالة SUM.

يقوم INDIRECT بذلك عن طريق الإشارة إلى البيانات الموجودة في الخلايا بشكل غير مباشر من خلال موقع وسيط.

مثال: SUM - INDIRECT Formula يستخدم لإجمالي نطاق ديناميكي من القيم

يعتمد هذا المثال على البيانات الموضحة في الصورة أعلاه.

صيغة SUM - INDIRECT التي تم إنشاؤها باستخدام خطوات البرنامج التعليمي أدناه هي:

= SUM (غير مباشر ("D" و E1 & ": D" & E2))

في هذه الصيغة ، تحتوي الوسيطة الدالة INDIRECT المتداخلة على مراجع للخلايا E1 و E2. الأرقام في هذه الخلايا ، 1 و 4 ، عند دمجها مع باقي حجة INDIRECT ، تشكل مراجع الخلية D1 و D4.

ونتيجة لذلك ، فإن نطاق الأرقام الذي تم جمعه بواسطة الدالة SUM هو البيانات الموجودة في نطاق الخلايا من D1 إلى D4 - وهو 50.

عن طريق تغيير الأرقام الموجودة في الخلايا E1 و E2 ؛ ومع ذلك ، يمكن تغيير النطاق المراد تجميعه بسهولة.

يستخدم هذا المثال أولاً الصيغة المذكورة أعلاه لإجمالي البيانات في الخلايا D1: D4 ثم قم بتغيير النطاق summed إلى D3: D6 بدون تحرير الصيغة في الخلية F1.

01 من 03

دخول الصيغة - الخيارات

قم بإنشاء نطاق ديناميكي في صيغ Excel. © تيد الفرنسية

تتضمن خيارات إدخال الصيغة ما يلي:

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

في هذه الحالة ، يمكن استخدام مربع حوار الدالة SUM لتبسيط الصيغة إلى حد معين. نظرًا لأن الدالة INDIRECT يتم تداخلها داخل SUM ، يجب إدخال الدالة INDIRECT ووسائطها يدويًا.

تستخدم الخطوات أدناه مربع الحوار SUM لإدخال الصيغة.

دخول بيانات البرنامج التعليمي

Cell Data D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4
  1. أدخل البيانات التالية في الخلايا D1 إلى E2

بدء تشغيل SUM - INDIRECT Formula - فتح مربع حوار دالة SUM

  1. انقر فوق الخلية F1 - هذا هو المكان الذي سيتم عرض نتائج هذا المثال
  2. انقر فوق علامة التبويب صيغ من قائمة الشريط
  3. اختر Math & Trig من الشريط لفتح القائمة المنسدلة الدالة
  4. انقر على SUM في القائمة لفتح مربع الحوار الخاص بالوظيفة

02 من 03

دخول الدالة INDIRECT - انقر لعرض صورة أكبر

انقر لمشاهدة صورة بشكل اكبر. © تيد الفرنسية

تحتاج صيغة INDIRECT إلى إدخالها كوسيطة للدالة SUM.

في حالة الدوال المتداخلة ، لا يسمح Excel بفتح مربع الحوار الخاص بالوظيفة الثانية لإدخال الوسيطات الخاصة به.

لذلك ، يجب إدخال الدالة INDIRECT يدويًا في سطر Number1 بمربع الحوار SUM Function.

  1. في مربع الحوار ، انقر فوق سطر Number1
  2. أدخل الدالة INDIRECT التالية: INDIRECT ("D" & E1 & ": D" & E2)
  3. انقر فوق "موافق" لإكمال الدالة وإغلاق مربع الحوار
  4. يجب أن يظهر رقم 50 في الخلية F1 لأن هذا هو إجمالي البيانات الموجودة في الخلايا من D1 إلى D4
  5. عند النقر فوق الخلية F1 ، تظهر الصيغة الكاملة = SUM (INDIRECT ("D" & E1 & ": D" & E2)) في شريط الصيغة أعلى ورقة العمل

كسر وظيفة غير مباشر

لإنشاء نطاق ديناميكي في العمود D باستخدام INDIRECT ، يجب علينا دمج الحرف D في وسيطة الدالة INDIRECT مع الأرقام الموجودة في الخلايا E1 و E2.

يتم ذلك عن طريق ما يلي:

لذلك ، يتم تعريف نقطة بداية النطاق بواسطة الأحرف: "D" و E1 .

المجموعة الثانية من الأحرف: ": D" & E2 تجمع بين النقطتين ونقطة النهاية. يتم ذلك لأن النقطتين هي حرف نصي ، وبالتالي ، يجب تضمين داخل علامات اقتباس.

يتم استخدام علامة الضم الثالث في الوسط لسَلسَلة الجزئين في وسيطة واحدة:

"D" & E1 & ": D" & E2

03 من 03

Dynamically تغيير نطاق SUM الخاص بالوظيفة

ديناميكيا تغيير نطاق الصيغة. © تيد الفرنسية

بيت القصيد من هذه المعادلة هو تسهيل تغيير النطاق الذي تم جمعه بواسطة الدالة SUM بدون الحاجة إلى تحرير وسيطة الدالة.

من خلال تضمين الدالة INDIRECT في الصيغة ، سيؤدي تغيير الأرقام في الخلايا E1 و E2 إلى تغيير نطاق الخلايا التي تقرأها الدالة SUM.

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

  1. انقر فوق الخلية E1
  2. اكتب الرقم 3
  3. اضغط على مفتاح Enter على لوحة المفاتيح
  4. انقر فوق الخلية E2
  5. اكتب الرقم 6
  6. اضغط على مفتاح Enter على لوحة المفاتيح
  7. يجب أن تتغير الإجابة في الخلية F1 إلى 90 - وهو إجمالي الأرقام الموجودة في الخلايا من D3 إلى D6
  8. مزيد من اختبار الصيغة عن طريق تغيير محتويات الخلايا B1 و B2 إلى أي أرقام بين 1 و 6

غير مباشر و # REF! قيمة الخطأ

#REF! ستظهر قيمة الخطأ في الخلية F1 إذا كانت وسيطة الدالة INDIRECT: