در این مطلب آموزشی قصد داریم با نحوه استفاده از ابزار subtotal در اکسل آشنا شویم. ویژگی Subtotal اکسل راهی عالی برای درج و محاسبه خودکار جمع، تعداد، میانگین و … داده های شما با فشار دادن یک دکمه است. با من تا انتهای این مقاله آموزشی همراه باشید.
نحوه استفاده از ابزار Subtotal در اکسل
قبل از استفاده از ابزار Subtotal در اکسل، یک مرحله اولیه وجود دارد که باید رعایت کنید. ابتدا مطمئن شوید که دادههای شما در ستونی که قصد انجام محاسبات خودکار(جمع، تعداد، میانگین و …) را دارید، مرتب شده باشد زیرا گروهبندی دادهها در ابزار Subtotal براساس فیلد(ستون) مرتب شده صورت خواهد گرفت. در ادامه برای درک بهتر از نحوه عملکرد این ابزار، آموزش را با یک مثال کاربردی دنبال خواهیم کرد.
جدولی از کالا و میزان موجودی هریک را در اختیار داریم:
قصد داریم گروهبندی کالا براساس ستون نام کالا صورت گیرد. از این رو با انتخاب این ستون(فیلد):
اقدام به مرتبسازی از نوع صعودی و یا نزولی آن به دلخواه خواهیم نمود (مقاله آموزش انواع مرتبسازی در اکسل):
و سپس با انتخاب جدول دادههای مرتبشده، از سربرگ Data برروی ابزار Subtotal کلیک و یا از کلیدمیانبر Alt + A + B استفاده کنید:

با کلیک برروی ابزار Subtotal از سربرگ Data، با پنجره Subtotal همانند تصویر زیر روبرو خواهید شد:
همانطور که در تصویر فوق مشاهده می کنید پنجره subtotal شامل موارد زیر است:
- قسمت At each change in: تعیین ستون(فیلد) مرتب شده جهت تفکیک و گروهبندی محاسبات.
- قسمت Use function: تعیین نوع محاسبات خودکار.
- تابع SUM: محاسبه جمع اعداد.
- تابع COUNT: محاسبه تعداد اعداد.
- تابع AVERAGE: محاسبه میانگین اعداد.
- تابع MAX: استخراج بزرگترین عدد.
- تابع MIN: استخراج کوچکترین عدد.
- تابع PRODUCT: محاسبه ضرب اعداد در یکدیگر.
- تابع StdDev: محاسبه انحراف معیار نمونه.
- تابع StdDevp: محاسبه انحراف معیار جامعه.
- تابع Var: محاسبه واریانس نمونه.
- تابع Varp: محاسبه واریانس جامعه.
- قسمت Add subtotal to: در این قسمت باید تیتر ستونی که می خواهیم عملیات انتخاب شده در بخش use function، روی آن صورت گیرد، را انتخاب کنیم.
- گزینه Replace current subtotals: با غیرفعال کردن این گزینه، امکان اعمال چند Subtotal بر روی محدوده مورد نظر فراهم میشود.
- گزینه Page break between groups: اگر این گزینه را انتخاب کنیم، برای پرینت گرفتن از جدول، صفحات طوری تنظیم می شوند که هر گروه از ابتدای یک صفحه جداگانه شروع و پرینت گرفته می شوند.
- گزینه Summary below data: با فعال کردن این گزینه، سطرهای شامل اطلاعات هر گروه در زیر هر دستهبندی قرار می گیرند. در غیر این صورت این سطر، در بالای هر دستهبندی نمایش داده می شود.
با توجه توضیحات فوق، ابتدا عنوان ستون تفکیک و گروهبندی محاسبات را تعیین خواهیم نمود. در نظر داشته باشین که دادههای موجود در ستون تفکیک میبایست از قبل بصورت صعودی و یا نزولی مرتب شده باشند زیرا این پشت سرهم قرار گرفتن دادهها باعث ایجاد دستهبندی میگردد. از این رو ستون نام کالا به عنوان ستون تفکیک معرفی میگردد:
سپس نوع محاسبات خودکار را تعیین خواهیم نمود:
و در ادامه، ستونی که محاسبات برروی آن انجام خواهد شد را تعیین خواهیم نمود:
در پایان با کلیک کردن برروی OK، نتیجه این گروهبندی و محاسبات خودکار را برروی ستون محاسباتی مشاهده خواهید نمود:
سطحبندی (Outline) در ابزار Subtotal اکسل
پس از اعمال Subtotal، شاهد ایجاد چندین سطح در سمت چپ کاربرگ خواهیم بود. سطحبندی به شما این امکان را می دهد تا سطرها یا ستون های مورد نیاز و کاربردی را مشاهده کنید.
در مثال بالا با ایجاد Subtotal، سه سطح در اختیار ما قرار گرفت(توجه داشته باشید اکسل تا هشت سطح را پشتیبانی میکند). در ادامه به بررسی این سه سطح ایجاد شده، در کاربرگ خواهیم پرداخت.
سطح یک حداقل جزئیات را نمایش دهد. در این مثال، این سطح شامل Grand total است که مجموع کل دادههای ستون موجودی نهایی(ستون محاسباتی) می باشد:
سطح دوم، برای نمایش جزئیات بیشتر ارائه شده است که فقط سطرهای Total و مقدار کل (Grand total) را نمایش می دهد:
و درپایان نیز سطح 3 ، تمام جزئیات برگه اکسل را نمایش میدهد:
همانطور که در تصاویر فوق نیز مشخص شده است، در سمت راست برگه اکسل علامتهای + و – وجود دارد. دکمه های با علامت مثبت، دکمه نمایش جزئیات (Show Detail) نامیده می شوند و با استفاده از آن ها می توانید گروه های بیشتری را نمایان کنید و دکمه های با علامت منفی، دکمه های مخفی کردن جزئیات (Hide Detail) نامیده می شوند و با استفاده از آن ها می توانید گروه ها را مخفی کنید. در تصویر زیر با انتخاب اولین دکمه +، اولین گروه نمایش داده شده و به دکمه – تبدیل شده است.
نحوه حذف سطوح Outline در ابزار Subtotal اکسل
- برروی سربرگ Data کلیک کنید.
- برروی ابزار Subtotal کلیک و یا از کلیدمیانبر Alt + A + B استفاده کنید.
- در پایان برروی دکمه Remove All کلیک کنید.
مشاهده میکنید که سطحبندی حذف و جدول دادهها به حالت اولیه خود تغییر کرد:
به منظور درک بهتر شما علاقهمندان به یادگیری مباحث شیرین نرمافزار اکسل، ویدیو آموزشی با عنوان آموزش ابزار Subtotal در اکسل آماده نمودیم که در ادامه میتوانید آن را تماشا کنید: