تابع subtotal در اکسل چیست؟ + آموزش استفاده به زبان ساده

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

تابع 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 در اکسل بسیار قدرتمند و کاربردی است و در عین حال، از نظر نحوه محاسبه داده‌ها یک قابلیت خاص محسوب می‌شود. در ادامه توضیحات مفصل‌تری درباره این تابع و نحوه استفاده از آن را می‌بینیم.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

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

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

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

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

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

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

1=SUBTOTAL(9,C2:C4)

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

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

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

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

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

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

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

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

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

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

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

کلیک کنید

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

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

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

۲. افزودن اولین سطح Subtotal

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

۳. افزودن سطوح داخلی Subtotal

پس از ایجاد Subtotal خارجی، به زبانه Data بروید و دوباره گزینه Subtotal را بزنید تا یک سطح داخلی Subtotal اضافه شود. حالا مراحل زیر را دنبال کنید:

• در کادر At each، دومین ستون موردنظر برای گروه‌بندی داده را برگزینید.
• کادر Use Function را روی تابع موردنظر برای خلاصه‌سازی بگذارید.
• در بخش Add Subtotal نیز ستون‌هایی را انتخاب کنید که می‌خواهید Subtotal روی آن‌ها محاسبه شود. این ستون می‌تواند با ستون موجود در سطح خارجی Subtotal یکسان یا متفاوت باشد.

در نهایت تیک گزینه Replace current subtotals را بردارید. انجام این کار بسیار مهم است زیرا از نوشته شدن مجدد سطوح خارجی Subtotal جلوگیری می‌کند.

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

در نتیجه اکسل مجموع‌ها را برای هر کالا در هر منطقه‌ای حساب می‌کند که می‌توانید نمونه آن را در تصویر زیر ببینید.

افزودن Subtotalهای متفاوت برای یک ستون

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

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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

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

ترفندهای کار با Subtotalها در اکسل

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

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

کلیک کنید

نمایش یا پنهان کردن جزئیات تابع subtotal در اکسل

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

این گزینه‌ها به شرح زیر هستند:

• عدد «۱» فقط جمع نهایی یا Grand Total را نشان می‌دهد.
• آخرین عدد، هم Subtotalها و هم مقادیر تکی را نمایش می‌دهد.
• اعداد میانی نیز برای نمایش گروه‌بندی‌ها استفاده می‌شوند. این مورد به تعداد Subtotalهایی که اضافه کرده‌اید بستگی دارد. ممکن است تعداد «۲» ،«۳» یا بیشتر عدد میانی داشته باشید.

در این مثال، عدد «۲» گروه‌بندی بر اساس ستون منطقه را نشان می‌دهد.

حالا اگر روی عدد «۳» کلیک کنیم، دومین Subtotla که برای محاسبه میانگین بوده است نیز نشان داده می‌شود.

برای نمایش یا پنهان کردن ردیف‌های داده برای هر Subtotal نیز از دکمه‌های «منفی» (-) و «مثبت» (+) استفاده می‌کنیم.

به‌جای آن همچنین می‌توانید روی گزینه‌های «Show Details»‌ یا «Hide Details» در زبانه Data و بخش Outline نیز کلیک کنید.

کپی کردن ردیف‌های Subtotal

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

پس برای کپی کردن فقط ردیف‌های قابل مشاهده شامل Subtotalها، ابتدا به کمک اعداد Outline یا دکمه‌های منفی و مثبت، تنها ردیف‌های موردنظر برای کپی را قابل مشاهده کنید. سپس روی یکی از سلول‌های Subtotal بزنید و بعد دکمه‌های «Ctrl+A» را روی کیبورد فشار دهید. حالا که تمام سلول‌های Subtotal انتخاب شده‌اند به زبانه Home بروید و از بخش «Editing» و منوی «Find & Selct»، گزینه «Go to Special» را بزنید.

حالا در صفحه جدید تیک گزینه «Visible cells only» را بزنید و دکمه OK را فشار دهید.

به‌جای آن می‌توانید پس از انتخاب سلول‌های موردنظر، دکمه‌های Alt + ;   را نیز روی کیبورد فشار دهید. حالا دکمه‌های «Ctrl+C» را بزنید تا محدوده انتخابی، کپی شود. بعد به کاربرگ مقصد بروید، روی یک سلول کلیک کنید و دکمه‌های «Ctrl+V» را فشار دهید تا محتوای کپی شده، پیست شود.

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

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

روش تغییر دادن Subtotal

برای اعمال تنظیمات و تغییرات روی Subtotalهای موجود، ابتدا یک سلول Subtotal را انتخاب کرده و در زبانه Data روی گزینه Subtotal کلیک کنید. در پنجره جدید می‌توانید تمام تغییرات موردنظر خود را انجام دهید، یعنی ستون کلیدی، تابع خلاصه‌سازی یا مقادیر محاسبه Subtotal را به دلخواه عوض کنید.

حتما تیک گزینه «Replace current subtotals» را بزنید و بعد دکمه OK را فشار دهید. اگر چندین Subtotal را روی یک مجموعه داده اعمال کرده باشید، امکان ویرایش آن‌ها وجود نخواهد داشت. تنها راه این است که تمام Subtotalهای موجود را حذف کرده و دوباره از اول با تغییرات جدید اضافه کنید.

حذف کردن تابع Subtotal در اکسل

برای حذف کردن Subtotal، ابتدا یکی از سلول‌های گروه Subtotal را انتخاب کرده و سپس در زبانه Data، گروه Outline، روی گزینه Subtotal کلیک کنید. در پنجره جدید نیز گزینه «Remove All» را بزنید. با این کار داده‌های شما از حالت گروه‌بندی خارج شده و تمام Subtotalهای موجود روی آن حذف می‌شوند.

سخن پایانی

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

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