تمرین ۲۰: توابع IF و SUBTOTAL برای شمارش سلول‌های دارای داده

شمارش سلول‌های دارای داده با توابع IF و SUBTOTAL در اکسل

مقدمه

در دنیای تحلیل داده‌ها با اکسل، یکی از نیازهای رایج، شمارش سلول‌هایی است که دارای داده هستند؛ به‌ویژه زمانی که بخشی از داده‌ها فیلتر شده‌اند یا نیاز به شمارش شرطی داریم. توابع IF و SUBTOTAL در اکسل ابزارهای قدرتمندی هستند که در کنار هم می‌توانند این نیاز را به‌صورت پویا و دقیق برطرف کنند. در این مقاله، نحوه استفاده ترکیبی از این دو تابع برای شمارش سلول‌های دارای داده را بررسی می‌کنیم و یک سناریوی کاربردی برای درک بهتر ارائه خواهیم داد.

🔑 کلمات کلیدی

تابع IF در اکسل، تابع SUBTOTAL در اکسل، شمارش سلول‌های دارای داده، شمارش شرطی در اکسل، فیلتر داده‌ها در اکسل، آموزش اکسل، توابع ترکیبی اکسل، تحلیل داده با اکسل

تابع IF در اکسل برای اعمال شرط روی داده‌ها استفاده می‌شود. این تابع بررسی می‌کند که آیا یک شرط برقرار است یا نه، و بر اساس آن خروجی متفاوتی ارائه می‌دهد. از طرف دیگر، تابع SUBTOTAL برای انجام محاسباتی مانند جمع، میانگین، شمارش و… روی داده‌های فیلتر شده کاربرد دارد.

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

این فرمول اکسل ترکیبی از شرط و تابع SUBTOTAL هست که برای شمارش سلول‌های غیرخالی در محدوده‌ای خاص استفاده می‌شه. بیایم مرحله‌به‌مرحله تحلیلش کنیم:

🧠 ساختار کلی فرمول:

فرمول پیشنهادی برای این کار به شکل زیر است:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),–(A2:A100<>””))

🔍 توضیح اجزای فرمول:

• SUBTOTAL(103,…): تابع SUBTOTAL با کد 103 برای شمارش سلول‌های غیرخالی در حالت فیلتر شده استفاده می‌شود.
• OFFSET(…): برای ایجاد محدوده‌های پویا برای هر سلول در محدوده A2:A100.
• ROW(A2:A100)-ROW(A2): برای تنظیم فاصله ردیف‌ها در تابع OFFSET.
• –(A2:A100<>””): بررسی اینکه سلول خالی نباشد و تبدیل نتیجه به عدد (TRUE=1, FALSE=0).
• SUMPRODUCT(…): برای جمع کردن نتایج شرطی و فیلتر شده.

این فرمول به‌صورت پویا فقط سلول‌های پر را که در حالت فیلتر شده قابل مشاهده هستند، شمارش می‌کند.

🎯 هدف فرمول:

این فرمول در واقع یک شمارنده‌ی پویا برای سلول‌های غیرخالی در ستون E هست، که فقط وقتی E4 مقدار داشته باشه، شمارش رو انجام می‌ده. این روش معمولاً در لیست‌های فیلترشده یا فرم‌های ورود داده استفاده می‌شه تا تعداد ورودی‌های معتبر رو نمایش بده.

سناریوی کاربردی 1

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

محدوده داده‌ها:

• ستون A: نام مشتری
• ستون B: ایمیل
• ستون C: وضعیت فعال بودن (فعال / غیرفعال)

فرمول پیشنهادی:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(B2:B100,ROW(B2:B100)-ROW(B2),0,1)),–(B2:B100<>””)*(C2:C100=”فعال”))

این فرمول فقط ایمیل‌های وارد شده برای مشتریان فعال را در حالت فیلتر شده شمارش می‌کند.

📌سناریوی کاربردی 2:

فرض کن در سلول‌های E2 تا E4 داده‌هایی وارد شده باشه:

• اگر E2 = "Ali", E3 = "", E4 = "Sara"
  → فرمول مقدار 2 رو برمی‌گردونه چون فقط E2 و E4 پر هستن.

این فرمول وقتی با فیلتر در یک ستون استفاده بشه، رفتار خاص و هوشمندانه‌ای داره. بیایم دقیق بررسیش کنیم:

🧠 ساختار فرمول:

=IF(E4<>””, SUBTOTAL(103, $E$2:E4), “”)

📌 تحلیل با در نظر گرفتن فیلتر:

1. SUBTOTAL با کد 103:

• کد 103 در تابع SUBTOTAL معادل COUNTA هست، اما با یک تفاوت مهم:
• فقط سلول‌های قابل مشاهده (یعنی سلول‌هایی که با فیلتر مخفی نشده‌اند) رو می‌شماره.
• این یعنی اگر روی ستون E فیلتر اعمال بشه، سلول‌های مخفی‌شده در شمارش لحاظ نمی‌شن.

2. محدوده پویا تا E4:

• محدوده‌ی شمارش از E2 تا E4 هست.
• وقتی این فرمول در ردیف‌های مختلف ستون E استفاده بشه (مثلاً در E5، E6، E7 و…)، هر ردیف فقط تا خودش رو بررسی می‌کنه.
• این باعث می‌شه که شمارنده‌ی ترتیبی برای ردیف‌های قابل مشاهده ایجاد بشه.

🎯 کاربرد در جدول‌های فیلترشده:

فرض کن یک جدول داری با داده‌هایی در ستون E، و روی اون فیلتر اعمال کردی (مثلاً فقط آیتم‌هایی با مقدار خاص نمایش داده می‌شن). حالا این فرمول در هر ردیف فقط وقتی مقدار داره و قابل مشاهده هست، یک عدد ترتیبی نشون می‌ده.

✅ نتیجه:

• ردیف‌هایی که فیلتر شده و قابل مشاهده هستن → شماره‌گذاری می‌شن.
• ردیف‌هایی که خالی یا مخفی‌شده هستن → خروجی "" (خالی) می‌گیرن.

📊 مثال تصویری ساده:

✨ نکته حرفه‌ای:

اگر بخوای شماره‌گذاری فقط برای ردیف‌های قابل مشاهده باشه، این فرمول عالیه. معمولاً در داشبوردها یا فرم‌هایی که کاربر داده‌ها رو فیلتر می‌کنه، برای نمایش شماره ردیف‌های فعال استفاده می‌شه.

حالا با یک تمرین عملی در محیط واقعی اکسل این مسئله رو مرور می‌کنیم. لازم به یادآوری هست که اسامی شهرها با قابلیت SLICER فیلتر شدند.

سوالات متداول:

+ - هدف اصلی ترکیب IF با SUBTOTAL چیست؟

ترکیب IF با تابع SUBTOTAL برای شمارش یا نمایش نتایج فقط در ردیف‌هایی استفاده می‌شود که فیلتر نشده یا قابل‌دیدن هستند؛ به‌عبارت دیگر می‌توان شمارش سلول‌های غیرخالی را طوری انجام داد که ردیف‌های پنهان توسط فیلتر در نتیجه لحاظ نشوند.

+ - چرا از کد 103 در SUBTOTAL استفاده می‌کنیم؟

کد 103 برای شمارش سلول‌های غیرخالی در حالت فیلتر شده استفاده می‌شود. این کد فقط سلول‌هایی را که قابل مشاهده هستند و داده دارند، شمارش می‌کند.

+ - چه کدی برای SUBTOTAL باید استفاده شود تا فقط سلول‌های قابل‌دیدن شمارش شوند؟

برای شمارش سلول‌های غیرخالی در سطرهای قابل‌دیدن از کد 103 داخل SUBTOTAL استفاده می‌شود که معادل COUNTA ولی فقط روی ردیف‌های قابل‌دیدن عمل می‌کند.

+ - فرمول کلی برای شمارش سلول‌های غیرخالی در محدوده فیلترشده چیست؟

یک نمونه محاسباتی که در مطلب آمده استفاده از SUMPRODUCT همراه با SUBTOTAL و OFFSET است تا برای هر ردیف بررسی شود که قابل‌دیدن است و سپس غیرخالی بودن آن حساب شود؛ ساختار کلی مثال در مطلب به صورت زیر آمده است و نحوه کارکرد اجزایش توضیح داده شده است:

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),--(A2:A100<>"")).

+ - نقش OFFSET و آرایه ROW در فرمول چیست؟

OFFSET همراه با آرایه ROW برای ایجاد مرجعی تک‌سلولی به‌ازای هر ردیف محدوده به‌کار می‌رود تا SUBTOTAL(103, …) بتواند قابل‌دیدن بودن هر ردیف را جداگانه ارزیابی کند؛ عبارت ROW(A2:A100)-ROW(A2) برای محاسبه جابه‌جایی نسبت به ابتدا استفاده می‌شود.

+ - چطور می‌توان شرط اضافه‌تری (مثلاً شمارش فقط مقدار «فعال») به فرمول افزود؟

می‌توان در SUMPRODUCT شرط‌ دیگری را ضرب کرد، مثلاً (C2:C100="فعال") تا فقط ردیف‌هایی که هم قابل‌دیدن‌اند و هم مقدار ستون C برابر "فعال" است شمرده شوند؛ مثال ترکیبی نیز در مطلب نشان داده شده است.

+ - چگونه می‌توان نتیجه را در ستون هر ردیف نشان داد تا هنگام افزودن ردیف جدید به‌روز شود؟

در مثال عملی برای نمایش تجمعی تا ردیف جاری از فرمول IF استفاده شده است:
=IF(E4<>"", SUBTOTAL(103, $E$2:E4), "")
با این روش هرگاه سلول جاری خالی نباشد، SUBTOTAL روی بازه تا آن سلول اجرا می‌شود و در غیر این صورت رشته خالی نمایش داده می‌شود.

+ - محدودیت‌ها یا نکات احتیاطی این روش چیست؟

این روش کمی سنگین است چون از توابع آرایه‌ای و OFFSET استفاده می‌کند؛ برای لیست‌های بسیار بزرگ ممکن است عملکرد کند شود. همچنین اگر ردیف‌ها با روش‌هایی غیر از فیلتر (مثلاً پنهان دستی) مخفی شده باشند، رفتار SUBTOTAL با کدهای متفاوت می‌تواند متفاوت باشد و باید توجه شود که کد 103 مخصوص ردیف‌های فیلترشده قابل‌دیدن است.

+ - آیا راه‌حل ساده‌تری بدون SUMPRODUCT و OFFSET وجود دارد؟

برای برخی سناریوها می‌توان از توابع پیشرفته‌تر یا Power Query/PivotTable استفاده کرد تا نیاز به محاسبات پیچیده در سلول‌ها حذف شود؛ در همان مطلب به ایده استفاده از Slicer و روش‌های گزارش‌گیری اشاره شده است که برای گزارش‌های تعاملی مناسب‌ترند.

+ - مثالی ساده برای درک خروجی فرمول‌ها بزنید؟

اگر در سلول‌های E2:E4 به‌ترتیب مقادیر "Ali"، ""، "Sara" قرار داشته باشد، فرمول نمونه SUMPRODUCT با SUBTOTAL، تعداد 2 ردیف غیرخالی قابل‌دیدن را بازمی‌گرداند (ردیف‌های E2 و E4).