كيفية استخدام النطاق الديناميكي في Excel مع COUNTIF و INDIRECT
2024 مؤلف: Abigail Brown | [email protected]. آخر تعديل: 2023-12-17 06:38
ما يجب معرفته
تعمل الدالة غير المباشرة على تغيير نطاق مراجع الخلايا في صيغة بدون تحرير الصيغة.
استخدم INDIRECT كوسيطة لـ COUNTIF لإنشاء نطاق ديناميكي من الخلايا التي تفي بالمعايير المحددة.
يتم تحديد المعايير بواسطة الدالة غير المباشرة ، ويتم حساب الخلايا التي تفي بالمعايير فقط.
تشرح هذه المقالة كيفية استخدام الدالة INDIRECT في صيغ Excel لتغيير نطاق مراجع الخلايا المستخدمة في الصيغة دون الحاجة إلى تحرير الصيغة نفسها. يضمن ذلك استخدام نفس الخلايا ، حتى عندما يتغير جدول البيانات.تنطبق المعلومات على Excel 2019 و Excel 2016 و Excel 2013 و Excel 2010 و Excel for Mac و Excel Online.
استخدم نطاقًا ديناميكيًا باستخدام COUNTIF - الصيغة غير المباشرة
يمكن استخدام الدالة غير المباشرة مع عدد من الوظائف التي تقبل مرجع الخلية كوسيطة ، مثل الدالتين SUM و COUNTIF.
يؤدي استخدام INDIRECT كوسيطة لـ COUNTIF إلى إنشاء نطاق ديناميكي من مراجع الخلايا التي يمكن حسابها بواسطة الدالة إذا كانت قيم الخلية تفي بالمعايير. يقوم بذلك عن طريق تحويل البيانات النصية ، التي يشار إليها أحيانًا باسم سلسلة نصية ، إلى مرجع خلية.
Image
يعتمد هذا المثال على البيانات الموضحة في الصورة أعلاه. صيغة COUNTIF - INDIRECT التي تم إنشاؤها في البرنامج التعليمي هي:
=COUNTIF (غير مباشر (E1 &":" & E2)، ">10")
في هذه الصيغة ، تحتوي وسيطة الدالة غير المباشرة على:
تشير الخلية إلى E1 و E2 ، والتي تحتوي على البيانات النصية D1 و D6.
عامل النطاق ، النقطتان (:) محاطة بعلامات اقتباس مزدوجة ("") التي تحول النقطتين إلى نص سلسلة
علامتا عطف (&) تُستخدمان لربط النقطتين أو ربطهما معًا بمراجع الخلية E1 و E2.
النتيجة هي أن INDIRECT يحول السلسلة النصية D1: D6 إلى مرجع خلية ويمررها إلى دالة COUNTIF ليتم حسابها إذا كانت الخلايا المشار إليها أكبر من 10.
تقبل الوظيفة غير المباشرة أي مدخلات نصية. يمكن أن تكون هذه خلايا في ورقة العمل تحتوي على نص أو مراجع خلايا نصية يتم إدخالها مباشرة في الوظيفة.
تغيير نطاق الصيغة ديناميكيًا
تذكر أن الهدف هو إنشاء صيغة ذات نطاق ديناميكي. يمكن تغيير النطاق الديناميكي دون تحرير الصيغة نفسها.
عن طريق تغيير البيانات النصية الموجودة في الخلايا 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 (INDIRECT (E1 &":" & E2))
=COUNTBLANK (INDIRECT (E1 &":" & E2)
بالنسبة للنطاق D1: D6 ، تُرجع COUNTA إجابة من 4 ، نظرًا لأن أربعة من الخلايا الخمس تحتوي على بيانات. تُرجع COUNTBLANK إجابة 1 نظرًا لوجود خلية فارغة واحدة فقط في النطاق.
لماذا تستخدم دالة غير مباشرة؟
فائدة استخدام الوظيفة غير المباشرة في كل هذه الصيغ هي أنه يمكن إدراج خلايا جديدة في أي مكان في النطاق.
يتغير النطاق ديناميكيًا داخل الوظائف المختلفة ، ويتم تحديث النتائج وفقًا لذلك.
Image
بدون الوظيفة غير المباشرة ، يجب تحرير كل وظيفة لتشمل جميع الخلايا السبعة ، بما في ذلك الخلية الجديدة.
تتمثل مزايا الوظيفة غير المباشرة في إمكانية إدراج القيم النصية كمراجع للخلايا ، وأنها تقوم بتحديث النطاقات ديناميكيًا كلما تغير جدول البيانات.
هذا يجعل الصيانة الشاملة لجداول البيانات أسهل بكثير ، خاصة لجداول البيانات الكبيرة جدًا.
تحسب الدالة COUNTIF في Excel عدد الخلايا التي تطابق معايير معينة ، والتي يمكن أن تكون مفيدة في عدد من الحالات. فيما يلي كيفية استخدام COUNTIF مع cirteria واحد في Excel
للتحكم في النطاق الترددي هو تحديد مقدار النطاق الترددي الذي يمكن أن تستخدمه بعض الأجهزة أو البرامج. يساعد التحكم في النطاق الترددي في الحد من ازدحام الشبكة
