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

۱۰۳۰۹ بازدید
آخرین به‌روزرسانی: ۲۸ خرداد ۱۴۰۲
زمان مطالعه: ۱۰ دقیقه
تابع subtotal در اکسل چیست؟ + آموزش استفاده به زبان ساده

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

997696

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

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

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

خروجی تابع 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 برای محاسبه مجموع داده‌ها در ستون‌ «تعداد فروش» استفاده کرده‌ایم.

افزودن گزینه های subtotal در اکسل

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

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

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

نتیجه تابع subtotal در اکسل

در صورتی که با تغییر اعداد مجموعه، مقادیر محاسبه شده با تابع 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، فرمول زیر را تولید می‌کند:

1=SUBTOTAL(9,C2:C4)

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

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

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

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

تغییر فرمول برای محاسبه سلول های قابل مشاهده

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

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

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

grand average در اکسل

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

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

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

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

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

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

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

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

مرتب سازی در اکسل

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

در این مرحله یکی از سلول‌های مجموعه داده خود را برگزینید و اولین مرحله خروجی از 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 روی یک ستون

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

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

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

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

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

نمایش و پنهان کردن نتایج ساب توتال

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

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

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

نمایش سطوح مختلف تابع subtotal

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

نمایش سطوح میانی تابع subtotal

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

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

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

گزینه های show details و hide details

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

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

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

کپی کردن ردیف های subtotal در اکسل

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

گزینه visible cells only

به‌جای آن می‌توانید پس از انتخاب سلول‌های موردنظر، دکمه‌های 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 روی یک ستون را بررسی کردیم و در انتها دیدیم که چطور می‌شود با داده‌های نتیجه نهایی Subtotal کار کرد.

بر اساس رای ۳ نفر
آیا این مطلب برای شما مفید بود؟
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
ablebits
نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *