تابع SUBTOTAL در اکسل
تابع SUBTOTAL جزو توابع ریاضی اکسل است. این تابع یک محاسبات معین (مانند میانگین، مجموع و …) را بر روی مجموعه‌ای از مقادیر معین انجام می‌دهد.
توجه: کلمه Sub به معنی زیر یا جزء می باشد، بنابراین کلمه Subtotal به معنای جمع جزء می باشد. اما باید توجه داشت که کار این تابع تنها جمع کردن نیست و ماهیت کاملاً متفاوتی نسبت به سایر توابع اکسل دارد.
آرگومان‌ها یا پارامترهای ورودی
آرگومان اول (function_num): یک عدد صحیح است که نوع محاسبات موردنظر را تعیین می‌کند (مانند میانگین، مجموع و …). مقدار آن بین 1-11 و 101-111 است. در جدول زیر مقادیر این آرگومان و نوع محاسبات آن آمده است.
| function_num (شامل مقادیر مخفی) |
function_num (نادیده گرفتن مقادیر مخفی) |
تابع |
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
همان‌طور که مشاهده می‌شود در جدول فرق برای مثال تابع 1 و 101 یک عمل را انجام می دهند، پس تفاوت این دو در چیست؟
فرض کنید بعضی از سطرها را Hide (مخفی) کرده باشید و بخواهید (و یا نخواهید) آن‌ها را هنگام فیلتر کردن در محاسبه دخیل کنید. برای همین منظور دو کد قرار داده شده است.
– کدهای 1 تا 11 برای داده‌هایی می‌باشد که اگر سطرهای آن توسط شما Hide شده باشند، باز هم در محاسبات Subtotal در نظر گرفته خواهد شد.
– کدهای 101 تا 111 برای داده‌هایی می‌باشد که اگر سطرهای آن Hide شده باشند، در محاسبات Subtotal در نظر گرفته نمی‌شود.
آرگومان دوم، سوم، … (ref1, [ref2], …): محدوده یا محدوده‌هایی که محاسبات آرگومان اول بر روی آن انجام خواهد شد.
مثال‌ها
برای آشنایی با عملکرد تابع Subtotal نگاهی به فرمول‌های تصویر زیر و توضیحات آن بیندازید.
| ◢ | A | B | C | |||||
| 1 | مقدار | فرمول | نتیجه | |||||
| 2 | 8 | =SUBTOTAL(1,A2:A4) | 5 | |||||
| 3 | 6 | =SUBTOTAL(4,A2:A4) | 8 | |||||
| 4 | 1 | =SUBTOTAL(9,A2:A4) | 15 | |||||
در مثال فوق، به ترتیب میانگین، ماکسیمم و مجموع داده‌های بازه A2:A4 محاسبه شده است.
خطاهای تابع
خطای :#NUM! این خطا زمانی رخ می‌دهد کە در محاسبات تقسیم بر صفر رخ دهد (یافتن میانگین، واریانس، انحراف معیار و … محدوده‌ی سلول‌هایی که هیچ مقداری عددی در آن‌ها وجود ندارد)
| ◢ | A | B | C | |||||
| 1 | مقدار | فرمول | نتیجه | |||||
| 2 | =SUBTOTAL(1,A2:A4) | #DIV/0! | ||||||
| 3 | ||||||||
| 4 | ||||||||
خطای #VALUE!: این خطا زمانی رخ می‌دهد کە مقدار آرگومان اول (function_num)، مقداری بین 1-11 و 101-111 نباشد.
| ◢ | A | B | C | |||||
| 1 | مقدار | فرمول | نتیجه | |||||
| 2 | 23 | =SUBTOTAL(20,A2:A4) | #VALUE! | |||||
| 3 | 15 | |||||||
| 4 | 34 | |||||||
خداقوت. مثل همیشه عالی و کامل. سپاس
سلام. لطفا راهنمایی کنید که چگونه می توانم تابع SUBTOTAL را با COUNTIF ادغام کنم.
به عبارتی دستور COUNTIF با اعمال فیلتر کردن تغییر کند.
درود. فرض کنیم می‌خواهید دستور COUNTIF را بر روی محدوده B2:B11 اعمال کنید و شرط شما این باشد که مقدار آن محدوده برابر XX باشد. فرمول آن بدین قرار است:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),–(B2:B11=”XX”))
توجە کنید مقادیر محدودە (B2:B11 و B2) و مقدار شرط (XX) را با مقادیر خودتان جایگزین کنید.
سپاس فراوان
من در حال تهیه فایل اکسل برای صدور فاکتور و… هستم.
لطفا راهنمایی کنید چگونه میتوانم تابع sumif ، max , min, AVERAGE را در شرایط اعمال فیلتر به دست بیاورم.
برای میانگین:
=SUBTOTAL(101,محدوده)
برای ماکسیمم:
=SUBTOTAL(104,محدوده)
برای ماکسیمم:
=SUBTOTAL(105,محدوده)
برای SUMIF تابع SUBTOTAL مقداری ندارد از فرمول زیر استفاده کنید:
=SUMPRODUCT(SUBTOTAL(9,OFFSET(محدوده جمع,ROW(محدوده شرط)-MIN(ROW(محدوده شرط)),,1)),(محدوده شرط=”مقدار شرط”)+0)
فوق العاده بود. بسیار ممونم
سلام. ببخشید مجددا مزاحم شدم.
لطفا راهنمایی کنید چگونه میتوانم مقدار ماکزیمم و مینیمم و میانگین یک ستون اعداد را را در شرایط اعمال فیلتر در سایر ستونها به دست بیاورم.
من در فرمول های
=SUMPRODUCT(SUBTOTAL(9,OFFSET(محدوده جمع,ROW(محدوده شرط)-MIN(ROW(محدوده شرط)),,1)),(محدوده شرط=”مقدار شرط”)+0)
مقدار 9 به 4 یا 5 یا 105 یا 105 تغییر دادم ولی خروجی به من نداد.
و
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B11,ROW(B2:B11)-ROW(B2),0,1)),–(B2:B11=”XX”))
مقدار 3 به 4 یا 5 یا 105 یا 105 تغییر دادم ولی خروجی به من نداد.
سلام.
لطفا نیز راهنمایی کنید چگونه میتوانم مقدار ماکزیمم و مینیمم و میانگین یک ستون اعداد را حالت شرط در سایر ستونها به دست بیاورم.
سلام. با عرض پوزش
دو سوال قبلی را بد پرسیده ام.
سوالم اینکه چگونه میتوانم مقدار ماکزیمم و مینیمم و میانگین یک ستون اعداد را در حالت اعمال فیلتر و شرط در سایر ستونها به دست بیاورم.
یعنی اگر :
ستون A2:A10 – شامل اسامی علی، نقی ، حسن باشد
ستون B2:B10 شامل سال تولد باشد
ستون C2:C10 شامل قد باشد.
حال میخواهم فرمولی بنویسم که اگر روی ستون B فیلتر قرار بدم ، ماکزیمم/مینیمم قد فردی که اسمشان علی است چه مقدار است؟