تابع subtotal در اکسل چیست؟

به طور کلی، تابع Subtotal، جمع دسته‌ای از اعداد است که سپس به دسته‌های دیگر اضافه خواهد شد تا مجموع کل به‌دست بیاید. البته در مایکروسافت اکسل، قابلیت Subtotal تنها به محاسبه مجموع زیر دسته‌های مقادیر در مجموعه داده محدود نیست. بلکه به شما اجازه می‌دهد با استفاده از توابعی مانند «Sum» ،«Count» ،«Average» ،«Max» ،«Min» و موارد دیگر، داده‌های خود را دسته‌بندی و خلاصه‌سازی کنید.

اموزش اکسل

به علاوه این تابع در واقع، سلسله مراتبی از گروه‌های مختلف می‌سازد که به عنوان «نمای کلی» یا «Outline» شناخته می‌شود. نمای کلی این امکان را به شما می‌دهد که جزئیات هر زیرمجموعه را نمایش دهید یا مخفی کنید، یا تنها خلاصه‌ای از زیرمجموعه‌ها و جمع‌های کل اصلی را ببینید. به عنوان نمونه، نتیجه تابع Subtotal در اکسل می‌تواند شبیه به تصویر زیر باشد.

نحوه افزودن تابع Subtotal در اکسل

برای افزودن سریع تابع Subtotal، مراحل زیر را دنبال کنید.

۱. مرتب سازی داده منبع

برای استفاده از تابع Subtotal در اکسل نیاز است داده منبع شما به روشی مناسب، مرتب شده باشد و هیچ سلول خالی‌ نداشته باشد. برای حذف ردیف های خالی در اکسل می‌توانید از قابلیت «Find And Replace» کمک بگیرید.

پس قبل از به کار بردن این تابع، ستون موردنظر را به درستی گروه‌بندی و مرتب کنید. ساده‌ترین روش برای انجام این کار نیز کمک گرفتن از فیلتر در اکسل است. به این منظور ابتدا کل ستون موردنظر را انتخاب کرده و وارد زبانه Data شوید، سپس روی دکمه «Filter» کلیک کنید. حالا خواهید دید که فلشی در کنار نام ستون پدیدار می‌شود. روی این فلش بزنید و یکی از گزینه‌های زیر را برای مرتب‌سازی برگزینید:

• Sort A to Z: مرتب‌سازی به ترتیب حروف الفبا
• Sort Z to A:‌ مرتب‌سازی برعکس ترتیب حروف الفبا
• Sort by Color: بر اساس  رنگ

همچنین در منوی پایین می‌توانید تیک گزینه‌های موردنظر برای نمایش یا پنهان کردن گزینه‌ها بزنید.

۲. افزودن زیرمجموعه‌ها

سلولی را در مجموعه داده خود انتخاب کرده، به زبانه Data بروید و از منوی «Outline» روی گزینه Subtotal کلیک کنید.

اگر قصد دارید Subtotal را تنها برای بخشی از داده‌های خود اضافه کنید، قبل از کلیک روی گزینه Subtotal، این بخش را به کمک ماوس برگزینید.

فرمول نویسی اکسل

۳. تعریف گزینه‌های تابع Subtotal در اکسل

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

• در کادر «At each change»، ستونی را برگزینید که داده موردنظر برای گروه‌‌بندی را در خود دارد.
• گزینه «Use function» را روی یکی از حالت‌های زیر تنظیم کنید:
  • Sum: جمع در اکسل
  • Count: شمارش سلول‌های غیر خالی (این گزینه فرمول‌های تابع Counta را مورد استفاده قرار می‌دهد)
  • Average: میانگین اعداد
  • Max: نمایش بزرگ‌ترین عدد
  • Min: نمایش کوچک‌ترین عدد
  • Product: حاصل ضرب سلول‌ها
  • Count Numbers: شمارش سلول‌های حاوی عدد (این گزینه از فرمول‌های Count استفاده می‌کند)
  • StdDev: محاسبه انحراف معیار یک جامعه بر اساس نمونه‌ای از اعداد
  • StdDevp: محاسبه انحراف معیار بر اساس کل جامعه اعداد
  • Var: تخمین واریانس یک جامعه بر اساس نمونه‌ای از اعداد
  • Varp: تخمین واریانس یک جامعه بر اساس کل جامعه اعداد
• در کادر «Add subtotal to» نیز تیک گزینه ستونی را بزنید که می‌خواهید Subtotal آن را محاسبه کنید.

در مثال زیر، داده را بر اساس ستون «منطقه» گروه‌بندی کرده و از تابع Sum برای محاسبه مجموع داده‌ها در ستون‌ «تعداد فروش» استفاده کرده‌ایم.

همچنین شما می‌توانید یکی از گزینه‌های زیر را نیز به دلخواه انتخاب کنید:

• برای افزودن یک «Page Break» به طور خودکار، پس از هر Subtotal، تیک گزینه «Page break between groups» را بزنید.
• برای نمایش یک ردیف خلاصه سازی‌شده بالای ردیف‌های جزئیات، تیک گزینه «Summary below data» را بردارید. برای نمایش ردیف خلاصه، پایین ردیف‌های جزئیات، باید تیک همین گزینه را بزنید.
• برای نوشتن مجدد تمام Subtotalهای موجود، تیک گزینه «Replace current subtotals»‌ را بزنید.

در نهایت دکمه «OK» را فشار دهید تا تغییرات اعمال شوند. نتیجه نهایی را می‌توانید در تصویر زیر ببینید. ردیف مربوط به خلاصه سازی یا Subtotal، زیر هریک از ردیف‌های جزئیات، محاسبه شده و قرار گرفته است. در نهایت نیز مجموع نهایی با نام «Grand Total»‌ دیده می‌شود.

در صورتی که با تغییر اعداد مجموعه، مقادیر محاسبه شده با تابع Subtotal در اکسل، مجددا محاسبه نشدند، باید به مسیر «File > Options> Formulas > Calculation options» بروید و گزینه «Workbook Calculation» را روی حالت «Automatic» بگذارید.

۳ نکته مهم درباره تابع Subtotal در اکسل

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

۱. استفاده از Subtotal تنها روی ردیف‌های قابل مشاهده

به طور خلاصه، باید گفت تابع subtotal در اکسل تنها مقادیر سلول‌های قابل مشاهده را محاسبه کرده و سلول‌های فیلتر شده را نادیده می‌گیرد. با این حال مقادیر سلول‌هایی را که به صورت دستی با انتخاب گزینه‌های «Hide» و «Unhide» در بخش «Format» از زبانه Data یا با راست‌کلیک کردن روی سلول‌ها و انتخاب گزینه «Hide»، پنهان شده‌اند را شامل می‌شود. در ادامه توضیحات این مورد را می‌بینیم.

 به کار بردن قابلیت Subtotal در اکسل، فرمول Subtotal را ایجاد خواهد کرد که عملیاتی مانند Sum ،Count یا Max را انجام می‌دهد. این تابع با عدد موجود در اولین آرگومان (Function_num) تعریف می‌شود که به یکی از مجموعه‌های زیر تعلق دارد:

• ۱-۱۱: سلول‌های فیلتر شده را نادیده می‌گیرد اما ردیف‌هایی که به صورت دستی پنهان شده‌اند را محاسبه می‌کند.
• ۱۰۱-۱۱۱: تمام سلول‌های پنهان شده را نادیده می‌گیرد (فیلتر شده و پنهان شده به صورت دستی)

قابلیت Subtotal اکسل، فرمول‌هایی را تولید می‌کند که اعداد تابعی ۱-۱۱ را به کار می‌برند.

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

=SUBTOTAL(9,C2:C4)

که در این فرمول عدد «۹»، نشان دهنده تابع Sum است و محدوده «C2:C4» اولین گروه از سلول‌ها برای محاسبه Subtotal را مشخص می‌کند.

حالا اگر مثلا پرتقال‌ها و لیموها را فیلتر کنیم، این دو مورد به طور خودکار از محاسبه حذف می‌شوند. اما اگر این سلول‌ها را به صورت دستی پنهان کنیم، مقدار آن‌ها نیز در محاسبه Subtotal در نظر گرفته خواهد شد. تفاوت این دو مورد در جمع فیلتر در اکسل را می‌توانید در تصویر زیر ببینید.

برای اینکه فرمول سلول‌های پنهان شده را نیز در محاسبه لحاظ نکرده و تنها سلول‌های قابل مشاهده را در نظر بگیرد، فرمول Subtotal خود را با جابه‌جا کردن اعداد ۱-۱۱ با ۱۰۱-۱۱۱ تغییر دهید. در مثال زیر، عدد ۹ را با ۱۰۹ عوض کرده‌ایم تا در هر دو حالت نیز تنها مقدار موجود در سلول‌های قابل مشاهده در نتیجه نشان داده شود.

۲. محاسبه مجموع کل از روی داده اصلی

همان‌طور که در مثال قبلی دیدید، با استفاده از تابع Subtotal در اکسل علاوه بر محاسبه زیر مجموع، یک مجموع کل نیز تحت عنوان Grand Total به‌دست می‌آید. خوب است بدانید برای محاسبه این مقدار به‌جای مقادیر Subtotal، از داده اصلی استفاده می‌شود.

به عنوان مثال، در نمونه زیر استفاده از تابع Subtotal برای محاسبه میانگین در اکسل، تابع Average، تمام اعداد موجود در محدوده «C2:C11» را حساب می‌کند و مقادیر ردیف‌های Subtotal را نادیده خواهد گرفت. به کمک تصویر زیر می‌توانید این مفهوم را بهتر درک کنید.

۳. Subtotal در جدول اکسل در دسترس نیست

اگر مشاهده کردید که دکمه Subtotal در نوار ابزار شما به رنگ خاکستری درآمده است و نمی‌شود از آن استفاده کرد، احتمالا در حال کار با جداول اکسل باشید. از آنجا که تابع Subtotal در اکسل برای جدول‌ها قابل استفاده نیست، باید جدول خود را به مجموعه‌ای عادی از سلول‌ها تبدیل کرده یا قالب جدول را حذف کنید.

رسم نمودار اکسل

نحوه افزودن چند تابع Subtotal در اکسل

در مثال قبلی نحوه افزودن یک سطح از تابع Subtotal در اکسل را دیدیم. حالا می‌خواهیم از این تابع برای گروه‌های داخلی که درون گروه‌های متناظر خارجی قرار دارند، استفاده کنیم. به طور خاص، قصد داریم ابتدا داده را بر اساس ستون «منطقه» گروه‌بندی کرده و سپس بر اساس ستون «کالا» جدا کنیم. در ادامه مراحل انجام این کار را می‌بینیم.

۱. مرتب سازی داده بر اساس چند ستون در اکسل

هنگام استفاده از چند تابع Subtotal در اکسل، مهم است که از قبل داده خود را بر اساس تمام گروه‌های موردنظر برای محاسبه Subtotal، مرتب کرده باشید. برای انجام این کار به زبانه Data بروید و در بخش «Sort & Filter» گزینه «Sort» را بزنید. حالا مرتب‌سازی را در دو سطح یا بیشتر انجام دهید.

دکمه OK را فشار دهید و به مجموعه داده برگردید. حالا خواهید دید که داده‌های ستون‌های «منطقه» و «کالا» بر اساس حروف الفبا مرتب شده‌اند.