تابع SUBTOTAL در اکسل — راهنمای کامل، کاربردها و نکات حرفه‌ای

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

چرا از SUBTOTAL استفاده کنیم؟

• محاسبه فقط روی سطرهای قابل‌مشاهده پس از فیلتر (مثلاً SUM پس از استفاده از AutoFilter).
• اجتناب از شمارش یا جمع‌گیری دوباره مقادیری که قبلاً با SUBTOTAL محاسبه شده‌اند (SUBTOTAL روی نتایج سایر SUBTOTALها تأثیر نمی‌گذارد).
• انعطاف‌پذیری با انتخاب توابع مختلف (جمع، میانگین، شمارش و …).

فرمت کلی تابع

ساختار تابع به شکل زیر است:

=SUBTOTAL(function_num, ref1, [ref2], ...)

در اینجا function_num عددی است که نوع محاسبه را تعیین می‌کند و ref1، ref2 محدوده(ها)ی داده هستند.

جدول شماره‌های تابع و معانی آن‌ها

نکته مهم: اعداد 1 تا 11 و 101 تا 111 مشابه هم هستند اما رفتارشان نسبت به ردیف‌های مخفی شده متفاوت است. به طور خلاصه:

• 1–11: مقادیر ردیف‌های مخفی‌شده با فیلتر (AutoFilter) را نادیده می‌گیرد اما ردیف‌هایی که به صورت دستی (Hide Row) مخفی شده‌اند را محاسبه می‌کند.
• 101–111: علاوه بر نادیده گرفتن ردیف‌های فیلترشده، ردیف‌های مخفی‌شده به‌صورت دستی را نیز نادیده می‌گیرد.

مثال‌های عملی

فرض کنید ستون B شامل مقادیر فروش است و روی جدول، فیلتر اعمال کرده‌اید. برای جمع مقادیر فقط در سطرهای قابل‌دیدن:

=SUBTOTAL(9, B2:B100)

این فرمول جمع (SUM) را فقط روی سطرهای غیر فیلترشده محاسبه می‌کند، اما اگر برخی ردیف‌ها را با راست‌کلیک و Hide مخفی کرده باشید، آن‌ها را محاسبه می‌کند.

اگر می‌خواهید هم ردیف‌های فیلترشده و هم ردیف‌های مخفی‌شده دستی حذف شوند:

=SUBTOTAL(109, B2:B100)

این نسخه (109) جمع را فقط روی ردیف‌هایی انجام می‌دهد که واقعاً قابل‌مشاهده در صفحه هستند؛ هم فیلتر و هم Hide را نادیده می‌گیرد.

مثال ترکیبی: شمارش آیتم‌های قابل‌مشاهده

=SUBTOTAL(3, C2:C200)

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

نکات پیشرفته و بهترین شیوه‌ها

• اگر از جدول اکسل (Table) استفاده می‌کنید، SUBTOTAL به خوبی با آن کار می‌کند و وقتی سطرها فیلتر می‌شوند نتایج به‌روزرسانی می‌شود.
• SUBTOTAL نتایج سایر SUBTOTALها را در محدوده ورودی نادیده می‌گیرد، بنابراین می‌توانید در پایان هر گروه در یک لیست از SUBTOTAL استفاده کنید بدون اینکه چندبار محاسبه شود.
• برای محاسباتی که باید ردیف‌های مخفی‌شده دستی را هم نادیده بگیرند، از اعداد 101–111 استفاده کنید.
• اگر با خطاها مواجهید یا بخشی از داده‌ها خطا دارند، تابع AGGREGATE گزینه‌های پیشرفته‌تری برای نادیده گرفتن خطاها و هشدارها ارائه می‌دهد؛ اما SUBTOTAL معمولاً ساده‌تر و سریع‌تر است.

نمونه کاربرد واقعی (Case Study)

فرض کنید تیم فروش شما گزارش روزانه را فیلتر می‌کند تا فقط فروش یک منطقه خاص را ببیند. اگر در پایین جدول بخواهید جمع فروشِ فقط برای آن منطقه نمایش داده شود، کافی است از SUBTOTAL(9, فروش) استفاده کنید. مدیر وقتی فیلتر را تغییر می‌دهد، جمع به صورت خودکار به روز می‌شود و نیازی به تغییر فرمول نیست.

اشکالات متداول و رفع آن‌ها

• وقتی فرمول SUBTOTAL شامل رنجی است که شامل سلول‌های حاوی نتایج SUBTOTAL دیگر است، این نتایج دوبار شمرده نخواهند شد — این رفتار معمولاً مطلوب است ولی گاهی باعث عدم‌شناسایی خطا توسط کاربر می‌شود.
• اگر می‌خواهید از SUBTOTAL در محاسبات پیچیده‌تر استفاده کنید (مثلاً شرطی)، ترکیب آن با SUMPRODUCT یا استفاده از ستون‌های کمکی در جدول می‌تواند کارآمد باشد.
• دقت کنید که در نسخه‌های قدیمی‌تر اکسل (بسیار قدیمی)، رفتارهای مرتبط با 101–111 ممکن است موجود نباشد؛ بنابراین اگر نیاز به سازگاری با نسخه‌های قدیمی دارید، از روش‌های جایگزین استفاده کنید.

خلاصه

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

برای محاسبات پیچیده‌تر یا نادیده گرفتن خطاها، توابعی مانند AGGREGATE را نیز بررسی کنید اما برای اغلب نیازهای گزارش‌گیری مبتنی بر فیلتر، SUBTOTAL راه‌حل ساده و مطمئن است.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال