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

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

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

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

چرا از کد 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 و روشهای گزارشگیری اشاره شده است که برای گزارشهای تعاملی مناسبترند.

MATH & TRIGS-SUBTOTAL – خانه کوییز ایران

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

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

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