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

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

معمولا دیده‌اید که در زیر (انتهای) جداول یک سطر جمع مانند تصویر زیر وجود دارد و به سادگی اینکار با تابع SUM انجام می‌شود:

تابع SUM در اکسل

بگذارید قبل از ادامه، چند سوال احتمالی شما را پاسخ بگویم:

1) چرا بین جدول و سطر جمع، یک سطر خالی وجود دارد؟

پاسخ: به دلیل آنکه این سطر جدا از جدول باشد. در غیر اینصورت در هنگام SORT کردن و یا FILTER این سطر جزئی از جدول فرض می‌شود و SORT و یا FILTER خواهد شد. به شما هم توصیه می‌کنم که همیشه اینکار را انجام دهید.

2) آیا می‌توانم به جای نوشتن D3:D8 بنویسم D:D که کل ستون را شامل شود؟

پاسخ: خیــــــــــــــــــــــر!!!! مبادا اینکار را کنید. نباید فرمول در داخل منطقه‌ای باشد که روی آن محاسبه‌ای را انجام می‌دهد. اگر اینکار را کنید، نتیجه این فرمول عدد 0 می‌شود و خطای Circular Reference را خواهید دید.

حال بیایید این جدول را بر اساس ستون نوع «فیلتر» کنیم و ببیند که آیا نتیجه تابع SUM تغییر می‌کند یا نه ؟

تفاوت SUM و SUBTOTAL

می‌بینیم که نتیجه تابع SUM تغییر نکرد، اگر اینکار را با سایر توابع اکسل مانند AVERAGE, COUNT, SUMIFS, VLOOKUP و ... انجام دهید، خواهید دید که این توابع هیچ واکنشی به فیلتر بودن و یا نبودن جدول ما نشان نمی‌دهند، بنابراین این نتیجه را می‌گیریم:

نتیجه: تابع SUM و هیچ تابع دیگری در اکسل به فلیتر حساس نیست بجز ؟؟؟

معرفی SUBTOTAL در اکسل

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

ویژگی اصلی SUBTOTAL آن است که به فیلتر حساس است.

یعنی SUBTOTAL فقط بر روی داده‌هایی محاسبه انجام می‌دهد که دیده می‌شوند و آنچه را که دیده نمی‌شوند را در محاسبه منظور نمی‌کند.

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

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

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

چاقوی ارتش سوئیس

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

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

نوشتن فرمول SUBTOTAL

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

لیستی از کارهایی که SUBTOTAL انجام می دهد

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

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

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

کدهای تابع SUBTOTAL

اجازه بدهید فعلا تفاوت این کدها را نگویم و فرض کنید که هر 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 نگاهی بیندازید، خواهید دید که کدها دو دسته هستند. یکی بین عدد 1 تا 11 و دیگری بین عدد 101 تا 111.

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

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

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

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

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

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

شماره ردیف بعد از فیلتر

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

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

شماره ردیف بعد از فیلتر

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

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

این عدد برابر است با تعداد سلول‌های پـُری که از ابتدای ستون «نوع» (یعنی B3) تا سلول زرد می‌توانم ببینم.

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

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 در اکسل مراجعه کنید، این متن را خواهید دید:

مفهوم Nested Subtal چیست

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

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

محاسبه جمع تو در تو با subtotal

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

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

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

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

مهمترین قابلیت AGGREAGE نادیده گرفتن خطاهای در محاسبه است.

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

تابع AGGREGATE در اکسل

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

ورودیهای تابع AGGREGATE

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

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

کد رفتار aggreage
0 یا هیچی

نادیده گرفتن SUBTOTAL و AGGREGATEهایی که Nested هستند.

1

نادیده گرفتن سطرهای Hide شده و SUBTOTAL و AGGREGATEهایی که Nested هستند.

2

نادیده گرفتن مقادیر خطا و SUBTOTAL و AGGREGATEهایی که Nested هستند.

3

نادیده گرفتن سطرهای Hide شده و مقادیر خطا و SUBTOTAL و AGGREGATEهایی که Nested هستند.

4

هیچ چیزی را نادیده نگیر

5

فقط سطرهای Hide را نادیده بگیر

6

فقط مقادیر خطا را نادیده بگیر

7

سطرهای Hide و همچنین مقادیر خطا را نادیده بگیر

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

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

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

مثال تابع aggregate در اکسل

در فرمول سلول سبز رنگ، عدد 9 یعنی AGGREGATE باید «جمع» داده‌ها را محاسبه کند و عدد 3 یعنی اینکه:
* سلول‌های Hide شده را نادیده بگیرد. منظور از Hide ها همان سلولهایی است که دیده نمی‌شوند. بنابراین تعداد پاهای عقاب  که در سلول D4 است، در جمع محاسبه نمی‌شود.
* مقادیر خطا یعنی سلولهای D7 , D8 را نادیده بگیرد.
* Nested Subtotal ها را نادیده بگیرد، یعنی سلول D6 و همچنین Nested Aggreage را نادیده بگیرد یعنی سلول D10 که در این مثال زرد رنگ شده اند.

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

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

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

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

سطر جمع در TABLE

 

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

به این مطلب پاسخ دهید