تابع SUBTOTAL اکسل - جمع سلولهای فیلتر شده

 اما بگذارید نگاهی بیندازیم به معنی کلمه SUBTOTAL!! زیرا بیشتر افراد تصور می‌کنند که SUBTOTAL جمع می‌زند و کلمه TOTAL را «جمع» ترجمه می‌کنند. این تصور غلطی است. اگر ما TOTAL را به کلمه «حاصل» ترجمه کنیم، و SUB را به «جزء»، آنگاه ترجمه دقیق‌تری از SUBTOTAL خواهیم داشت. یعنی «محاسبه جزئی» از داده‌ها و نه همه داده‌ها.

در واقع من بحث ترجمه SUBTOTAL با این منظور در میان آوردم تا به شما بگویم تا به اشتباه تصور نکنید که قرار است «فقط» با این تابع «جمع» داده‌ها را محاسبه کنیم. بلکه:

بله! دقیقا 11 محاسبه مختلف با یک تابع که بر روی «جزئی» از داده‌ها انجام خواهد شد.

یعنی اگر شما خواستید AVERAGE (میانگین) سلول‌های فیلتر شده را محاسبه کنید، همچنان باید از SUBTOTAL استفاده کنید که در ادامه توضیح می‌دهم.

نوشتن فرمول SUBTOTAL در اکسل

هنگامی که SUBTOTAL را در اکسل می‌نویسید و علامت پرانتز را باز می‌کنید به صورت خودکار (اتوماتیک ) اکسل لیستی از تمامی 11 کاری که SUBTOTAL انجام می‌دهد را برای شما نمایش می‌دهد:

بیایید نگاهی دقیق به این لیست بیندازیم:

الف) قبل از هر تابعی یک عدد نوشته شده است که به آن «کد تابع در SUBTOTAL» می‌گوییم.

ب) اگر لیست را دقیق تر نگاه کنید، می‌بینید که هر تابعی 2 بار تکرار شده است !

اجازه بدهید فعلا تفاوت این کدها را نگویم و فرض کنید که هر 2 یکسان عمل می‌کنند. (زیرا واقعا و در عمل مهم نیست)

نکته) اگر این لیست را ندید، یکبار علامت پرانتزی که باز کرده‌اید را پاک کنید و مجدد آن را تایپ کنید.

بنابراین اولین کاری که در نوشتن SUBTOTAL در اکسل انجام می‌دهد، این است که باید به تابع بگویید که باید برای شما چه محاسبه‌ای را انجام دهد و اینکار را با نوشتن این کدها باید انجام دهید. یعنی اگر خواستید که تابع SUBTOTAL برای شما سلول‌های فیلتر شده را «جمع» بزند، باید بنویسید:

=SUBTOTAL(9      
یا      
=SUBTOTAL(109

و این فرمول را در ذهنتان اینگونه باید بخوانید: «ساب توتال جمع بزن ... »

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

=SUBTOTAL(1      
یا      
=SUBTOTAL(101

و این فرمول را در ذهنتان اینگونه باید بخوانید: «ساب توتال میانگین بگیر ... »

سوال) آیا باید کدها را حفظ کنم؟

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

سوال) آیا می‌توانم به جای نوشتن کد تابع، نام تابع را بنویسم؟

پاسخ: خیر. اولین ورودی تابع SUBTOTAL یک عدد است.

سوال) فرق بین کد 9 و 109 چیست؟

پاسخ: تا انتهای همین مقاله را بخوانید و فعلا مهم نیست.

حال باید مشخص کنید که تابع SUBTOTAL باید بر روی چه سلول‌هایی محاسبه را انجام دهد و آدرس این سلول‌ها را به عنوان ورودی بعدی تابع SUBTOTAL خواهیم نوشت.

=SUBTOTAL(109, D3:D8)

نکته: اگر تنظیمات Control Panel --> Region شما بر روی فارسی باشد، باید به جای علامت کاما در فرمول بالا، علامت سمی کالن یعنی «;» را بنویسید.

و اگر هم اکنون سلول‌ها را فیلتر کنید، خواهید دید که SUBTOTAL برای شما فقط جمع سلول‌های که دیده می‌شوند را محاسبه می‌کند:

تفاوت کد 9 و 109 در subtotal چیست؟

اگر مجدد به لیست توابع SUBTOTAL نگاهی بیندازید، خواهید دید که کدها دو دسته هستند. یکی بین عدد 1 تا 11 و دیگری بین عدد 101 تا 111.

کدهای سه رقمی (یعنی بین 101 تا 111) هم به «فیلتر حساس هستند» و همچنین به «سلولهای Hide شده» ! اما کدهای بین 1 تا 11 فقط و فقط به «فیلتر حساس» هستند. 

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

توجه داشته باشید که کسی  در اکسل و داده‌های بزرگ، سطرها را Hide نمی‌کنیم و به همین دلیل در گفتم که آنچنان مهم نیست و معمولا همه از کد 109 استفاده می‌کنند.

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

یکی از کاربردهای بسیار مشهور تابع SUBTOTAL برای «شماره گذاری اتوماتیک سطرها (ردیف‌ها) بعد از فیلتر کردن است» و معمولا کاربران اکسل می‌پرسند "چطور می‌توانم شماره‌ ردیف (سطرها) منظمی بعد از فیلتر کردن داده‌ها داشته باشم؟".

مثلا در تصویر زیر می‌بینید که ما چیزهایی که 0 پا دارند را فیلتر کرده‌ایم و شماره ردیف‌ها در اکسل نامنظم شده است.

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

من قبل از هرچیز باید بگویم که توضیح دقیق این فرمول به صورت نوشتاری کمی سخت است و امیداورم که در توضیحش موفق شوم. به تصویر زیر دقت کنید:

برای ساده‌تر درک فرمولی که قرار است بنویسیم، یکی از سلول‌های وسط لیست مانند سلول A6 که زرد شده است را در نظر بگیرید. اگر از شما بپرسم که به عنوان شماره ردیف چه عددی باید در این سلول نوشته شود، چه پاسخی می‌دهید؟

لطفا لحظه‌ای تمرکز کنید. پاسخ دقیق آن است که بگویید:

حال همین فرمول را می‌نویسیم.

1) چون گفته‌اید «می‌توانم ببینم» (چون شاید برخی Filter شده باشند) ،بنابراین از SUBTOTAL استفاده می‌کنیم:

=SUBTOTAL(

2) چون گفتید «سلول‌های پُر»، بنابراین باید از کد تابع COUNTA استفاده کنیم یعنی عدد 103 :

=SUBTOTAL(103,

3) گفتید «از ابتدای ستون نوع»، بنابراین باید به سلول B3 اشاره کنیم و چون قرار است که این فرمول را برای سایر سطرها بعدا کپی کنیم، باید این آدرس را به صورت مطلق با علامت $ بنویسیم:

=SUBTOTAL(103, $B$3

4) چون گفتید «تا»، باید از علامت «:» استفاده کنید:

=SUBTOTAL(103, $B$3:

5) چون گفتید «سلول زرد رنگ» و سلول زرد رنگ در سطر 6 قرار داد و از قبل هم شمارشتان بر اساس ستون B بود، پس می نویسیم:

=SUBTOTAL(103, $B$3:B6)

6) این فرمول را برای همه سلولها کپی کنید، تا  شماره ردیف‌ها همواره به صورت صحیح نمایش داده شوند:

پاسخ به سوال‌های احتمالی شما:

سوال 1) چرا ستون «نوع» را شمارش کرده‌ایم؟

پاسخ: برای شماره سطر ما باید چیزی را بشماریم و چون در این لیست همواره همه‌ی ستون «نوع» پر است، بنابراین این ستون را شمارش کردیم، البته در این مثال می‌توانیم ستون «اسم» را هم شمارش کنیم.

سوال 2) تفاوت تابع COUNT و COUNTA چیست؟

پاسخ: تابعCOUNT تعداد سلول‌های «عددی» را شمارش می‌کند در حالی که تابع COUNTA سلول‌های «پر» را شمارش می‌کند.

سوال 3) آیا در فرمول قبلی باید قبل از B حتما باید علامت $ باشد؟

پاسخ: خیر! چون این فرمول فقط در جهت عمودی (سطرها) کپی می‌شود، کافی است که قبل از عدد 3 یک $ بگذاریم.

سوال 4) علامت $ در فرمول چیست؟

پاسخ: ما در اکسل مفهوم بسیار مهمی به نام آدرس مطلق و آدرس نسبی داریم. اگر این مفهوم را نمی‌دانید به شما توصیه می‌کنم که حتما آن را یاد بگیرید.

Nested Subtotal چیه؟

اگر در Help تابع SUBTOTAL در اکسل مراجعه کنید، این متن را خواهید دید:

در این متن نوشته شده است، که تابع SUBTOTAL در هنگام محاسبه Nested Subtotalها را نادیده می‌گیرد. اجازه دهید توضیح دقیقی بدهم. تا اینجا یادگرفتیم که تابع SUBTOTAL به «فیلتر» حساس است و همچنین می‌تواند به «Hide» ها هم حساس باشد و اکنون می‌خواهم بگویم که SUBTOTAL «به خودش هم حساس است» .

یعنی تابع SUBTOTAL هنگام محاسبه، سایر SUBTOTALهایی که در داخل محدوده‌اش وجود (که اصطلاحا Nested Subtotal می‌گویند) دارند را نادیده می‌گیرد. به تصویر زیر دقت کنید:

تابعی که در سلول D10 نوشته شده است، مقادیر دو سلول زرد رنگ را نادیده می‌گیرد. در غیر اینصورت جمع D3:D11 باید عدد 24 (یعنی دو برابر) می‌شد.

تذکر مهم: توصیه نمی‌کنم که از این روش برای ساخت گزارش‌ها استفاده کنید زیرا احتمال اشتباهات سهوی بسیار بالا است.

معرفی تابع AGGREGATE ، نسل جدید SUBTOTAL

AGGREGATE تابعی است که از نسخه 2010 به اکسل اضافه شد و علاوه بر تمامی ویژگی‌های SUBTOTAL دارای چندین قابلیت جدید است.

شاید بدانید که هر محاسبه‌ای روی مقادیر خطا مانند VALUE# و NAME# و ... انجام شود، نتیجه‌اش قابل محاسبه نیست و خطا خواهد شد. اما با تابع AGGREAGE می‌توانیم روی سلول‌هایی که ممکن است برخی از آنها خطایی داشته باشند، محاسباتی را انجام دهیم. همچنین باید بدانید که با AGGREAGE می‌توانیم 19محاسبه مختلف را انجام دهیم (درSUBTOTAL ما 11 تابع داشتیم) . که در تصویر زیر می‌توانید آنها را ببیند.

اجازه دهید که شکل کلی تابع AGGREGATE را بررسی کنیم:

ورودی 1) کد تابع را وارد کنید: دقیقا مانند SUBTOTAL باید اول مشخص کنیم که قرار است AGGREAGE چه کاری را انجام دهد و باید «کد» تابع مورد نظر را اینجا بنویسیم.

ورودی 2) باید به تابع AGGREGATE بگوییم که باید چه چیزهایی را نادیده بگیرد و چه چیزهایی را محاسبه کند. در اینجا هم باید یک «کد» را وارد کنیم. این کدها در جدول زیر شرح داده شده است.

نکته: منظور از Hide یعنی سطرهایی که در اثر Hide کردن و یا Filter کردن دیده نمی‌شوند.

ورودی 3) باید بگوییم که کجا داده‌های ما هستند تا تابع بر روی آنها محاسبه‌ای را انجام دهد.

به فرمول سلول سبز رنگ در مثال زیر دقت کنید:

سایر نکته‌های:

1) تابع SUBTOTAL و AGGREGATE برای کار بر روی سطرها طراحی شده‌اند و مخفی کردن ستون‌ها در نتیجه‌ آنها تاثیری ندارد.

2) اگر ابتدا داده‌ها را فیلتر کنید و سپس در زیر یکی از ستون‌های عددی کلید = + ALT (کلید ALT و مساوی) را بزنید، خواهید دید که به جای تابع SUM ، برای شما SUBTOTAL می‌نویسید.

3) اگر داده‌های شما Table است، لازم نیست که SUBTOTAL را بنویسید، گزینه TOTAL ROW را فعال کنید و سپس کافی است که در آخرین سطر TABLE هر تابعی را که می‌خواهید را انتخاب کنید.

خوش باشید / فرشید میدانی