اگر تا حالا از تابع SUM برای جمع‌زدن داده‌ها استفاده کرده باشید، احتمالاً متوجه شده‌اید که وقتی داده‌ها را فیلتر می‌کنید، جمع همچنان تمام مقادیر (حتی مخفی‌شده‌ها) را حساب می‌کند. 😕
اینجاست که تابع SUBTOTAL وارد میدان می‌شود!
تابعی هوشمند که می‌تواند فقط داده‌های قابل‌مشاهده را محاسبه کند و با انواع توابع آماری کار کند.
📘 تعریف تابع SUBTOTAL
تابع SUBTOTAL برای انجام محاسبات آماری (مثل جمع، میانگین، شمارش و…) روی مجموعه‌ای از داده‌ها استفاده می‌شود.
این تابع می‌تواند طوری تنظیم شود که فقط سلول‌های قابل مشاهده (Visible Cells) را در محاسبات لحاظ کند.
🔢 ساختار تابع SUBTOTAL در اکسل
=SUBTOTAL(function_num, range1, [range2], ...)
| آرگومان | توضیح |
|---|---|
| function_num | عددی که مشخص می‌کند چه نوع محاسبه‌ای انجام شود (مثلاً جمع، میانگین و…) |
| range1 | محدوده داده‌ها |
| range2,… | محدوده‌های اضافی (اختیاری) |
🧩 کدهای عملکردی تابع SUBTOTAL
| عدد تابع (function_num) | عملکرد | مثال |
|---|---|---|
| 1 | AVERAGE (میانگین) | =SUBTOTAL(1, A1:A10) |
| 2 | COUNT (تعداد سلول‌های عددی) | =SUBTOTAL(2, A1:A10) |
| 3 | COUNTA (تعداد سلول‌های غیرخالی) | =SUBTOTAL(3, A1:A10) |
| 9 | SUM (جمع کل) | =SUBTOTAL(9, A1:A10) |
| 10 | VAR (واریانس) | =SUBTOTAL(10, A1:A10) |
| 11 | STDEV (انحراف معیار) | =SUBTOTAL(11, A1:A10) |
💡 نکته:
اگر عدد تابع را بین 101 تا 111 بنویسید، SUBTOTAL به‌صورت خودکار سلول‌های فیلترشده یا مخفی‌شده را نادیده می‌گیرد.
🧮 مثال کاربردی از تابع SUBTOTAL
فرض کنید جدولی از فروش دارید:
| محصول | مبلغ فروش |
|---|---|
| A | 1200 |
| B | 2500 |
| C | 1800 |
| D | 3000 |
حالا اگر داده‌ها را فیلتر کنید و فقط محصولات A و C نمایش داده شوند:
- فرمول
=SUM(B2:B5)عدد 8500 را نمایش می‌دهد (بدون توجه به فیلتر) - اما فرمول
=SUBTOTAL(9, B2:B5)عدد 3000 را برمی‌گرداند ✅
چون فقط سلول‌های قابل مشاهده را جمع می‌کند.
💡 تفاوت SUBTOTAL و SUM در اکسل
| ویژگی | SUM | SUBTOTAL |
|---|---|---|
| فیلتر را در نظر می‌گیرد؟ | ❌ خیر | ✅ بله |
| قابلیت چند تابع مختلف | ❌ فقط جمع | ✅ بیش از 10 تابع مختلف |
| سلول‌های مخفی را حساب می‌کند؟ | ✅ بله | ❌ خیر |
| استفاده در جدول‌های پویا | محدود | گسترده |
🧰 نکته حرفه‌ای: SUBTOTAL در جدول‌های فیلترشده
اگر لیست خود را با گزینه‌ی Filter یا Table Format (Ctrl+T) تنظیم کنید، اکسل به‌صورت خودکار SUBTOTAL را در پایین جدول نمایش می‌دهد.
مثلاً در یک لیست فروش، وقتی فیلتر می‌کنید، عدد پایین ستون فروش به‌صورت زنده به‌روزرسانی می‌شود — چون تابع SUBTOTAL پشت صحنه در حال کار است.
⚙️ استفاده از SUBTOTAL در توابع تو در تو (Nested)
شما حتی می‌توانید SUBTOTAL را داخل توابع دیگر استفاده کنید!
برای مثال، ترکیب آن با IF برای محاسبه‌ی مشروط:
=SUBTOTAL(9,OFFSET(B2,0,0,COUNTA(B:B)-1,1))
این فرمول به صورت داینامیک، جمع سلول‌های قابل مشاهده را در محدوده‌ی پویا حساب می‌کند.
🧩 نکته طلایی: حذف خطا در SUBTOTAL
اگر در محدوده داده‌ها خطاهایی مثل #DIV/0! یا #VALUE! دارید، از ترکیب تابع IFERROR استفاده کنید:
=SUBTOTAL(9,IFERROR(B2:B10,0))
(برای اعمال در کل محدوده باید با Ctrl + Shift + Enter تایید شود تا فرمول آرایه‌ای اجرا شود.)
🎥 فیلم آموزش رایگان تابع SUBTOTAL در آنی‌لِرن
در پلتفرم آنی‌لِرن (AnyLearn)، آموزش ویدیویی تابع SUBTOTAL همراه با مثال‌های واقعی از گزارش‌های فروش و فیلتر داده‌ها منتشر شده است.
💬 سوالات متداول درباره تابع SUBTOTAL
۱. آیا SUBTOTAL فقط برای جمع استفاده می‌شود؟
خیر، برای میانگین، شمارش، انحراف معیار و چندین محاسبه دیگر هم قابل استفاده است.
۲. تفاوت عددهای 9 و 109 در تابع چیست؟
عدد 109 فقط سلول‌های قابل‌مشاهده را محاسبه می‌کند، در حالی‌که عدد 9 تمام سلول‌ها را شامل می‌شود.
۳. آیا می‌توان SUBTOTAL را در PivotTable استفاده کرد؟
PivotTable خودش از SUBTOTAL در پشت صحنه استفاده می‌کند.
۴. چطور بفهمم SUBTOTAL روی داده‌های فیلترشده اعمال شده؟
اگر در هنگام فیلتر مقدار تغییر کند، یعنی تابع روی داده‌های قابل مشاهده فعال است.
🧾 جمع‌بندی: SUBTOTAL، ابزار تحلیل هوشمند در اکسل
تابع SUBTOTAL یکی از قدرتمندترین توابع اکسل برای مدیریت و تحلیل داده‌های فیلترشده است.
با یادگیری آن می‌توانید گزارش‌های دقیق‌تر، حرفه‌ای‌تر و داینامیک‌تری بسازید.
در آنی لرن (AnyLearn)، آموزش این تابع همراه با ویدیو، تمرین عملی و پروژه واقعی در دسترس است.
نظر کاربران
هنوز نظری ثبت نشده است. شما اولین نفر باشید.