نحوه استفاده از محدوده پویا در اکسل با COUNTIF و INDIRECT

2024 نویسنده: Abigail Brown | [email protected]. آخرین اصلاح شده: 2023-12-17 06:46

چه باید بدانیم

• عملکرد INDIRECT محدوده مراجع سلولی را در یک فرمول بدون ویرایش فرمول تغییر می دهد.
• از INDIRECT به عنوان آرگومان COUNTIF برای ایجاد محدوده دینامیکی از سلول‌ها که معیارهای مشخص شده را برآورده می‌کنند استفاده کنید.
• معیارها توسط تابع INDIRECT ایجاد می شوند و تنها سلول هایی که معیارها را برآورده می کنند شمارش می شوند.

این مقاله نحوه استفاده از تابع INDIRECT در فرمول های اکسل را برای تغییر محدوده مراجع سلولی استفاده شده در فرمول بدون نیاز به ویرایش خود فرمول توضیح می دهد. این تضمین می کند که از همان سلول ها استفاده می شود، حتی زمانی که صفحه گسترده شما تغییر می کند.اطلاعات برای Excel 2019، Excel 2016، Excel 2013، Excel 2010، Excel برای Mac و Excel Online اعمال می شود.

استفاده از محدوده دینامیکی با COUNTIF - فرمول غیر مستقیم

تابع INDIRECT را می توان با تعدادی از توابع استفاده کرد که مرجع سلول را به عنوان آرگومان می پذیرند، مانند توابع SUM و COUNTIF.

استفاده از INDIRECT به عنوان آرگومان برای COUNTIF، یک محدوده دینامیکی از مراجع سلولی ایجاد می کند که اگر مقادیر سلول دارای معیاری باشند، می توانند توسط تابع شمارش شوند. این کار را با تبدیل داده های متنی، که گاهی اوقات به عنوان رشته متنی از آن یاد می شود، به یک مرجع سلولی انجام می دهد.

این مثال بر اساس داده های نشان داده شده در تصویر بالا است. فرمول COUNTIF - INDIRECT ایجاد شده در آموزش این است:

=COUNTIF(INDIRECT(E1&":"&E2)، ">10")

در این فرمول، آرگومان برای تابع INDIRECT شامل:

• سلول به E1 و E2 اشاره دارد که حاوی داده های متنی D1 و D6 است.
• عملگر محدوده، دو نقطه (:) احاطه شده توسط دو علامت نقل قول (" ") که دو نقطه را به یک متن تبدیل می کند. رشته.
• دو آمپرساند (&) که برای الحاق یا به هم پیوستن کولون با ارجاع سلول E1 و E2 استفاده می شود.

نتیجه این است که INDIRECT رشته متنی D1:D6 را به یک مرجع سلولی تبدیل می کند و آن را به تابع COUNTIF ارسال می کند تا اگر سلول های ارجاع شده بزرگتر از 10 باشند، شمارش شود.

عملکرد INDIRECT هر ورودی متنی را می پذیرد. اینها می‌توانند سلول‌هایی در کاربرگ باشند که حاوی متن یا مراجع سلول متنی هستند که مستقیماً در تابع وارد می‌شوند.

تغییر پویا محدوده فرمول

به یاد داشته باشید، هدف ایجاد فرمولی با محدوده دینامیکی است. یک محدوده پویا را می توان بدون ویرایش خود فرمول تغییر داد.

با تغییر داده‌های متنی واقع در سلول‌های E1 و E2، از D1 و D6 به D3 و D7، محدوده جمع‌شده توسط تابع را می‌توان به راحتی از D1:D6 به D3:D7 تغییر داد. با این کار نیازی به ویرایش مستقیم فرمول در سلول G1 نیست.

تابع COUNTIF در این مثال فقط سلول‌های حاوی اعداد را در صورتی می‌شمارد که بزرگ‌تر از 10 باشند. حتی اگر چهار سلول از پنج سلول در محدوده D1:D6 حاوی داده‌ها هستند، تنها سه سلول حاوی اعداد هستند. سلول هایی که خالی هستند یا حاوی داده های متنی هستند توسط تابع نادیده گرفته می شوند.

شمارش متن با COUNTIF

عملکرد COUNTIF به شمارش داده های عددی محدود نمی شود. همچنین سلول‌هایی را که حاوی متن هستند، با بررسی اینکه آیا با متن خاصی مطابقت دارند، شمارش می‌کند.

برای انجام این کار، فرمول زیر در سلول G2 وارد می شود:

=COUNTIF(INDIRECT(E1&":"&E2)، "دو")

در این فرمول، تابع INDIRECT به سلول های B1 تا B6 اشاره می کند. تابع COUNTIF تعداد سلول‌هایی را که مقدار متن دو را در خود دارند جمع‌آوری می‌کند.

در این مورد، نتیجه 1 است.

COUNTA، COUNTBLANK و INDIRECT

دو تابع دیگر شمارش اکسل عبارتند از COUNTA، که سلول‌های حاوی هر نوع داده را می‌شمارند و تنها سلول‌های خالی یا خالی را نادیده می‌گیرند، و COUNTBLANK، که فقط سلول‌های خالی یا خالی را در یک محدوده شمارش می‌کند.

از آنجایی که هر دوی این توابع نحوی مشابه با تابع COUNTIF دارند، می توان آنها را در مثال بالا با INDIRECT جایگزین کرد تا فرمول های زیر ایجاد شود:

=COUNTA(INDIRECT(E1&":"&E2))
=COUNTBLANK(INDIRECT(E1&":"&E2)
برای محدوده D1:D6، COUNTA پاسخ 4 را برمی‌گرداند، زیرا چهار سلول از پنج سلول حاوی داده هستند. COUNTBLANK پاسخ 1 را برمی‌گرداند زیرا تنها یک سلول خالی در محدوده وجود دارد.

چرا از یک تابع غیر مستقیم استفاده کنیم؟
مزیت استفاده از تابع INDIRECT در همه این فرمول‌ها این است که سلول‌های جدید را می‌توان در هر جایی از محدوده درج کرد.
محدوده به صورت پویا در داخل توابع مختلف جابجا می شود و نتایج بر این اساس به روز می شوند.





بدون تابع INDIRECT، هر تابع باید ویرایش شود تا شامل تمام 7 سلول، از جمله سلول جدید باشد.
مزایای تابع INDIRECT این است که مقادیر متنی را می توان به عنوان مرجع سلول درج کرد و هر زمان که صفحه گسترده شما تغییر کرد، به صورت پویا محدوده ها را به روز می کند.
این کار نگهداری کلی صفحه گسترده را بسیار آسان تر می کند، به خصوص برای صفحات گسترده بسیار بزرگ.