استخدم الدالة INDIRECT في صيغ Excel لتغيير نطاق مراجع الخلايا المستخدمة في صيغة دون الحاجة إلى تحرير الصيغة نفسها. يضمن ذلك استخدام نفس الخلايا ، حتى عند تغيير جدول البيانات.
ملحوظة: تنطبق التعليمات الواردة في هذه المقالة على Excel 2019 و Excel 2016 و Excel 2013 و Excel 2010 و Excel for Mac و Excel Online.
استخدم نطاق ديناميكي باستخدام صيغة COUNTIF - INDIRECT
يمكن استخدام الدالة INDIRECT مع عدد من الوظائف التي تقبل مرجع خلية كوسيطة ، مثل دالات SUM و COUNTIF.
يؤدي استخدام INDIRECT كوسيطة لـ COUNTIF إلى إنشاء نطاق ديناميكي من مراجع الخلايا التي يمكن حسابها بواسطة الدالة إذا كانت قيم الخلية تفي بمعايير. ويقوم بذلك عن طريق تحويل بيانات النص ، التي يشار إليها أحيانًا باسم سلسلة نصية ، إلى مرجع خلية.
يعتمد هذا المثال على البيانات الموضحة في الصورة أعلاه. صيغة COUNTIF - INDIRECT التي تم إنشاؤها في البرنامج التعليمي هي:
= COUNTIF (غير مباشرة (E1 و":" & E2)، "> 10")
في هذه الصيغة ، تحتوي الوسيطة للدالة INDIRECT على:
- تشير الخلية إلى E1 و E2 ، والتي تحتوي على البيانات النصية D1 و D6.
- عامل النطاق ، القولون (:) محاطة بعلامات اقتباس مزدوجة (' ') التي تحول القولون إلى سلسلة نصية.
- عطفان (&) يستخدمان لسَلسَتة ، أو الانضمام معاً ، القولون مع المراجع الخلية E1 و E2.
والنتيجة هي أن INDIRECT يحول السلسلة النصية D1: D6 إلى مرجع خلية ويمررها إلى الدالة COUNTIF ليتم احتسابها إذا كانت الخلايا المرجعية أكبر من 10.
تقبل الدالة INDIRECT أي مدخلات نصية. يمكن أن تكون هذه الخلايا في ورقة العمل التي تحتوي على مراجع نصية أو خلية نصية يتم إدخالها مباشرة إلى الدالة.
Dynamically تغيير نطاق الصيغة
تذكر أن الهدف هو إنشاء صيغة ذات نطاق ديناميكي. يمكن تغيير النطاق الديناميكي دون تحرير الصيغة نفسها.
عن طريق تغيير بيانات النص الموجودة في الخلايا E1 و E2 ، من D1 و D6 إلى D3 و D7 ، يمكن تغيير النطاق الذي تم جمعه بواسطة الدالة بسهولة من D1: D6 إلى D3: D7. هذا يلغي الحاجة إلى تحرير الصيغة مباشرة في الخلية G1.
تقوم الدالة COUNTIF في هذا المثال بحساب الخلايا التي تحتوي على أرقام فقط إذا كانت أكبر من 10. على الرغم من أن أربعة من الخلايا الخمس في نطاق D1: D6 تحتوي على بيانات ، تحتوي ثلاث خلايا فقط على أرقام. يتم تجاهل الخلايا الفارغة أو التي تحتوي على بيانات نصية بواسطة الدالة.
عد النص مع COUNTIF
لا تقتصر وظيفة COUNTIF على حساب البيانات الرقمية. كما تحسب الخلايا التي تحتوي على نص عن طريق التحقق مما إذا كانت تتطابق مع نص معين.
للقيام بذلك ، يتم إدخال الصيغة التالية في الخلية G2:
= COUNTIF (غير مباشرة (E1 و":" & E2)، "اثنين")
في هذه الصيغة ، ترجع الدالة INDIRECT الخلايا B1 إلى B6. تقوم دالة COUNTIF بإجمالي عدد الخلايا التي تحتوي على القيمة النصية اثنان فيهم.
في هذه الحالة ، تكون النتيجة 1.
COUNTA و COUNTBLANK و INDIRECT
دالات Excel الأخرى هي COUNTA ، التي تقوم بحساب الخلايا التي تحتوي على أي نوع من البيانات مع تجاهل الخلايا الفارغة أو الفارغة فقط ، و COUNTBLANK ، التي تحسب فقط الخلايا الفارغة أو الفارغة في نطاق.
نظرًا لأن كلا هاتين الدالتين لهما بنية متشابهة لدالة COUNTIF ، فيمكن استبدالهما بالمثال أعلاه باستخدام INDIRECT لإنشاء الصيغ التالية:
= COUNTA (غير مباشرة (E1 و":" & E2))= COUNTBLANK (غير مباشرة (E1 و":" & E2)
بالنسبة للنطاق D1: D6 ، ترجع COUNTA إجابة 4 ، حيث تحتوي أربع من الخلايا الخمس على بيانات. ترجع COUNTBLANK إجابة 1 نظرًا لوجود خلية فارغة واحدة فقط في النطاق. الفائدة من استخدام الدالة INDIRECT في كل هذه الصيغ هي أنه يمكن إدراج خلايا جديدة في أي مكان في النطاق. يتحول النطاق ديناميكيًا داخل الوظائف المختلفة ، ويتم تحديث النتائج وفقًا لذلك. بدون دالة INDIRECT ، يجب تعديل كل وظيفة لتتضمن جميع الخلايا السبع ، بما في ذلك الخلية الجديدة. تتمثل فوائد الدالة INDIRECT في أنه يمكن إدراج قيم النص كمراجع للخلايا وتحديث النطاقات ديناميكيًا عند تغيير جدول البيانات. هذا يجعل صيانة جدول البيانات بشكل عام أسهل بكثير ، خاصة لجداول البيانات الكبيرة جدًا. لماذا استخدام وظيفة غير مباشرة؟
