تابع SUBTOTAL به منظور انجام محاسباتی مثل جمع، میانگین گیری، مینیمم یا ….بدون لحاظ کردن سلولهای پنهان استفاده میشود. به عبارت دیگر در این تابع تنها سلولهای فعال (نه سلولهای فیلتر شده یا پنهان شده) درون محاسبات لحاظ میشوند.
به طور مثال در تصویر زیر اطلاعات مربوط به فروش یک شرکت از سه نوع محصول به همراه درآمد حاصله آورده شده است و بهمنظور محاسبه جمع درآمدهای حاصله از تابع SUM استفادهشده است.
در این فایل اگر یک یا چند ردیف را بهصورت ذیل پنهان کنیم، مشاهده میشود که نتیجه حاصل از تابع SUM تغییری نمیکند و همچنان جمع تمامی دادهها بهعنوان خروجی نمایش داده میشود.
یا در حالتی دیگر اگر بر روی یکی از سلولها کلیک کنیم و از تبHome قسمت Sort & Filter دستور Filter را فعال کنیم و از قسمت کالا فقط محصول ۲ را برای نمایش دادن انتخاب کنیم (محصول ۲ را فیلتر کنیم) ، مشاهده میشود که همچنان نتیجه تابع SUM بدون تغییر باقی میماند.
درحالیکه جمع حاصل از درآمد مقادیری که در صفحهنمایش داده میشود متفاوت از حاصل تابع SUM است.
همانطور که مشاهده میشود حاصل عبارت
=SUM(E3:E8)
برابر با جمع سلولهای محدوده E3 تا E8 جدا از اینکه کدام ردیف از این محدوده پنهان است یا فیلتر شده است. حال اگر بخواهیم تنها سلولهای قابلمشاهده از محدوده E3 تا E8 را با یکدیگر جمع کنیم چه باید کرد؟
ورودی های تابع SUBTOTAL
در مواقع اینچنین میتوان از تابع SUBTOTAL استفاده کرد که در آن مقادیر فیلتر شده یا مخفیشده در محاسبات لحاظ نخواهد شد.این تابع شامل تمامی توابع تجمیعی اکسل میباشد و در ورودی اول این تابع مشخص میشود کدام تابع تجمیعی محاسبه شود بهگونهای که بعد از واردکردن مقدار =SUBTOTAL( لیست توابع بهصورت ذیل مشاهده میشود و کاربر باید از بین حالات پیشنهادی یکی را انتخاب کند و دکمه Tab را بزند یا شماره مربوط به آن تابع را در ورودی اول وارد کند.
همانطور که مشاهده میشود در بین حالات هر تابع دو بار تکرار شده است برای مثال عدد ۹ و ۱۰۹ هر دو بیانگر تابع SUM میباشند، تفاوت این عدد دو آن است که در حالتی که در فایل اکسل یک یا چند ردیف پنهان شوند (بدون فیلتر کردن) در تابع SUBTOTAL گزینههای ۱ تا ۱۱ آن ردیفها را نیز در محاسبه لحاظ میکنند درحالیکه حالات ۱۰۰ به بالا آن ردیفها را در محاسبه لحاظ نمیکنند. برای مثال تابع
=SUBTOTAL(9
,……. مقادیر مربوط به سلولهای پنهانشده (منظور سلولهایی است که ردیف یا ستون آن پنهانشده است نه فیلتر) را نیز در محاسبات خود لحاظ میکند درحالیکه تابع
=SUBTOTAL(109
,…. مقادیر مربوط به سلولهای پنهان را در محاسبات لحاظ نمیکند.
بعد از تعیین نوع محاسبات در ورودی اول تابع SUBTOTAL در ورودیهای بعدی محدوده مدنظر برای انجام محاسبات وارد میشود.
برای مثال اگر ردیفهای ۴، ۵ و ۶ را پنهان کنیم محاسبات تابع SUM و SUBTOTAL به صورت ۹ و عدد ۱۰۹ به شکل ذیل خواهد بود.
و در حالت دیگر اگر از فیلتر استفاده کنیم تا تنها محصول ۲ نمایش داده شود، محاسبات بهصورت ذیل خواهد شد.
سلام ممنونم مطلب عالی بود با تشکر