کلمه Sub به معنی زیر یا جز می باشد.پس کلمه Subtotal به معنی جمع جز می باشد.اما توجه کنید که کار این تابع تنها جمع کردن نمی باشد.بلکه ماهیت کاملا متفاوتی نسبت به سایر توابع اکسل دارد و کار کردن با آن متفاوت می باشد. ولی بسیار بسیار یادگیری و استفاده از آن ساده و کاربردی می باشد.

پیش نیاز این مطلب، آموزش ابزار Filter در اکسل می باشد و این تابع را با ابزار Subtotal اشتباه نگیرید.

جدول زیر را در نظر بگیرید.

آموزش کار با ابزار filter در excel

از شما می خواهند جمع کل فروش خالص را حساب کنید.

این کار بسیار ساده می باشد و با تابع Sum این کار را انجام می دهیم.

اگر از ما میانگین کل فروش خالص را بخواهند با تابع Average  این کار را انجام می دهیم.

آموزش subtotal در اکسل

حالا از شما می خواهند که جمع کل را فقط در مناطق شمال بیان کنید.

ابتدا به کمک ابزار Filter این کار را انجام می دهیم.ولی همان طور که می بینید جمع و میانگین همان جمع و میانگین کل می باشد.در حالی که ما جمع و میانگین فقط در مناطق شمال را می خواهیم.

جمع بعد از فیلتر

برای اینکه این مشکل را برطرف کنیم نیاز داریم از تابع Subtotal استفاده کنیم.پس با ما همراه باشید تا آموزش این ابزار را بیاموزید.

 ساختار تابع subtotal به این شکل می باشد:

SUBTOTAL(function_num,ref1,[ref2],…)

SUBTOTAL(مرجع 2],مرجع1,شماره تابع],…)

اگر به ساختار این تابع توجه کنید کاملا با ساختار سایر توابع فرق دارد.

شماره تابع:

در این تابع برای هر کدام از عملیات های جمع،میانگین،و….یک شماره در نظر گرفته است. مثلا اگر بخواهید یک ستون را جمع کنید از عدد 9 یا 109 استفاده خواهید کرد.این کد به تابع می فهماند که باید عملیات جمع را انجام دهد.یا اگر بخواهیم میانگین بگیریم از کد 1 یا 101 استفاده خواهیم کرد.این کد به تابع می فهماند که باید عملیات میانگین را انجام دهد.

مرجع:

در قسمت قبلی گفتیم نوع عملیات را مشخص می کنیم ولی باید به تابع بفهمانیم کدام ستون یا داده ها را جمع کند.در این قسمت به تابع می فهمانیم باید چه قسمت هایی را با هم جمع کند.در مثال از سلول D2:D29 می باشد.

یعنی تابع ما برای عملیات جمع کردن در حالت فیلتر به این شکل خواهد شد:

=SUBTOTAL(109,D2:D29)

و برای عملیات میانگین در حالت فیلتر به این شکل خواهد شد:

=SUBTOTAL(101,D2:D29)

نتیجه مانند تصویر زیر خواهد بود:

جمع و میانگین با subtotal

حالا اگر از شما بخواهند جمع فروش خالص در مناطق شمال و شهرستان تهران چقدر بوده است،کافیست فقط عملیات فیلتر را انجام دهید!!! تابع Subtotal خودش این کار را انجام میدهد.

subtotal

همان طور که مشاهده می کنید تابع Subtotal یک سری عملیات را در حالت فیلتر انجام میدهد.

اما چند سوال مهم :

1) تابع Subtotal چه شماره هایی را قبول می کند؟

جواب:طبق جدول زیر شماره های 101 تا 111 و شماره های 1تا11 را قبول می کند و مشاهده خواهید کرد که هر کد چه عملی را انجام میدهد.

hide سطرهای مخفی در subtotal

2)با توجه به جواب سوال1، چرا به عنوان مثال تابع 1و 101 یک عمل را انجام می دهند؟تفاوت این دو در چیست؟

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

کد های 1 تا11 برای داده هایی می باشد که اگر سطرهای آن توسط شما Hide شده باشد باز هم در محاسبات subtotal در نظر گرفته خواهد شد.

کدهای101 تا 111 برای داده هایی می باشد که اگر سطرهای آن توسط شما Hide شده باشد در محاسبات subtotal در نظر گرفته نمی شود.

3) اگر در میان داده ها دارای خطا باشیم و بخواهیم این عملیات ها را انجام دهیم، چه می کنیم؟ جواب

بارگذاری بیشتر مطالب مرتبط
بارگذاری در Excel

۲ نظرات

  1. نرگس

    ۱۳۹۶/۰۷/۰۳ at ۰۹:۰۴

    بسیار ممنونم از آموزش مفیدتون….‌.حدا خیرتون بده واقعا جامع و آموزنده بود……

    پاسخ

  2. وحید مهبودی

    ۱۳۹۶/۰۴/۱۹ at ۱۱:۵۴

    در مورد تابع sub total این تابع در زمانی درست خواهد بود که فیلتر وجود داشته باشه اگر فیلتر رو برداریم داده رو اشتباه خواهد داد آیا فرمولی داریم که این خاصیت رو داشته باشه ؟

    پاسخ

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *