آموزش کامل و قدم‌به‌قدم Subtotal در اکسل

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

Subtotal چیست و چه کاربردی دارد؟

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

به زبان ساده، Subtotal به شما کمک می‌کند رکوردهای یک ستون را بر اساس دسته‌بندی موجود در ستون دیگر تحلیل کنید؛ مثلاً:

• جمع فروش هر ماه
• میانگین حقوق هر واحد سازمانی
• شمارش تعداد محصولات هر دسته
• و…

مزیت اصلی Subtotal نسبت به توابع عادی چیست؟

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

به‌طور خلاصه، Subtotal ویژگی‌های کلیدی زیر را ارائه می‌دهد:

• محاسبه فقط روی داده‌های قابل‌مشاهده
• گروه‌بندی خودکار و ساخت ساختار درختی
• امکان جمع، میانگین، شمارش و… بدون نیاز به فرمول‌نویسی دستی
• هماهنگی کامل با Filter و Sort

مثال استفاده از Subtotal اکسل

فیلم آموزش Subtotal اکسل به صورت گام به گام + پروژه عملی

آموزش گام‌به‌گام استفاده از Subtotal در اکسل

گام اول: داده‌ها را برای Subtotal آماده کنید

برای اینکه Subtotal به درستی کار کند، جدول شما باید ساختار مشخصی داشته باشد:

• ستون‌ها باید عنوان داشته باشند.
• داده‌ها نباید ردیف خالی بین خود داشته باشند.
• ستونی که قرار است براساس آن گروه‌بندی شود باید منظم باشد.

مثلاً اگر می‌خواهید جمع فروش هر «ماه» را جداگانه محاسبه کنید، باید ستون ماه را مرتب‌سازی (Sort) کنید تا Subtotal بتواند گروه‌ها را تشخیص دهد.

گام دوم: داده‌ها را Sort کنید

قبل از اجرای Subtotal، باید ستون موردنظر را مرتب کنید. برای این کار:

1. کل جدول را انتخاب کنید.
2. از سربرگ Data گزینه Sort را بزنید.
3. ستونی که می‌خواهید براساس آن گروه‌بندی شود (مانند ماه، دسته کالا، واحد سازمانی و…) را انتخاب کنید.
4. گزینه OK را بزنید.

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

گام سوم: اجرای ابزار Subtotal

پس از مرتب‌سازی، نوبت اجرای Subtotal است:

1. به سربرگ Data بروید.
2. روی گزینه Subtotal کلیک کنید.

پنجره‌ای باز می‌شود که شامل سه تنظیم اصلی است:

• At Each Change In: ستونی که قرار است براساس تغییر آن گروه‌بندی شود.
• Use Function: نوع محاسبه (مانند Sum، Average، Count و…)
• Add Subtotal To: ستون‌هایی که محاسبه روی آن انجام می‌شود.

بعد از انتخاب گزینه‌ها و زدن OK، Subtotal به‌صورت خودکار گروه‌بندی‌ها و نتایج را ایجاد می‌کند.

انواع محاسبات قابل انجام با Subtotal

در Subtotal اکسل می‌توانید ۱۱ نوع محاسبه مختلف انجام دهید. برخی از مهم‌ترین آن‌ها عبارت‌اند از:

• Sum: جمع گروهی
• Average: میانگین گروهی
• Count: شمارش تعداد
• Max: بزرگ‌ترین مقدار
• Min: کوچک‌ترین مقدار
• Product: ضرب مقادیر
• StdDev: انحراف معیار

برای هر نوع تحلیل، کافی است نوع محاسبه را در قسمت Use Function انتخاب کنید.

درک ساختار Outline در Subtotal

بعد از اجرای Subtotal، سمت چپ صفحه اکسل ساختاری شبیه درخت (Outline) ظاهر می‌شود. این بخش به شما اجازه می‌دهد در یک کلیک:

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

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

ویرایش و حذف Subtotal

ویرایش Subtotal

اگر نیاز دارید نوع محاسبه را تغییر دهید یا ستون دیگری را جمع‌بندی کنید، کافی است دوباره وارد Subtotal شوید و تنظیمات جدید را اعمال کنید. هنگام اعمال تنظیم جدید، اگر گزینه Replace Current Subtotals تیک داشته باشد، محاسبات قبلی جایگزین می‌شود.

حذف Subtotal

برای حذف کامل Subtotal:

1. به سربرگ Data بروید.
2. روی Subtotal کلیک کنید.
3. گزینه Remove All را بزنید.

با این کار تمام گروه‌بندی‌ها و جمع‌بندی‌ها حذف می‌شود.

تفاوت SUBTOTAL با AGGREGATE چیست؟

تابع SUBTOTAL نسخه ساده‌تر AGGREGATE است. تابع AGGREGATE چند قابلیت اضافه نیز دارد مانند:

• امکان نادیده گرفتن خطاها
• امکان انتخاب نوع داده‌های نادیده‌گرفته‌شده
• عملکرد بهتر برای تحلیل‌های پیچیده‌تر

اما برای کارهای روزمره، Subtotal اکسل بهترین و سریع‌ترین گزینه است.

استفاده از Subtotal همراه با Filter

یکی از قابلیت‌های فوق‌العاده مفید Subtotal، هماهنگی کامل آن با Filter است. به‌محض اینکه یک فیلتر روی داده‌ها اعمال کنید:

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

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

استفاده از تابع SUBTOTAL به‌جای ابزار Subtotal

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

• نمی‌خواهید ساختار Outline ایجاد شود.
• فقط می‌خواهید محاسبه پویا روی داده‌های قابل‌مشاهده داشته باشید.

ساختار تابع به شکل زیر است:

=SUBTOTAL(function_num, range)

مثال:

=SUBTOTAL(9, C2:C200)

در اینجا ۹ به معنای SUM است.

اگر از اعدادی مانند ۱۰۹، ۱۰۳ یا ۱۰۴ استفاده کنید، تابع حتی ردیف‌های مخفی‌شده دستی را نیز نادیده می‌گیرد.

ترفندهای حرفه‌ای برای کار با Subtotal

۱. محاسبه چندگانه روی یک ستون

اگر می‌خواهید برای هر گروه هم جمع داشته باشید، هم میانگین:

• یک بار Subtotal را با Sum اجرا کنید.
• بار دوم Subtotal را با Average اجرا کنید و تیک Replace Current Subtotals را بردارید.

اکنون زیر هر گروه دو نوع خروجی دارید.

۲. حذف سریع Outline بدون حذف داده‌ها

اگر فقط می‌خواهید گروه‌بندی‌ها حذف شوند، کافی است داده‌ها را انتخاب و از سربرگ Data > Group > Ungroup را بزنید. این کار ساختار کلی را حذف می‌کند.

۳. تبدیل Subtotal به Pivot Table

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

موارد استفاده واقعی از Subtotal در کارهای اداری

Subtotal اکسل در بسیاری از پروژه‌ها کاربرد مستقیم دارد. چند نمونه واقعی:

• گزارش فروش ماهانه
• گزارش حقوق هر واحد
• گزارش محصول بر اساس دسته‌بندی
• تحلیل موجودی انبار
• گزارش هزینه‌های مالی در پروژه‌ها

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

خطاهای رایج هنگام کار با Subtotal

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

با رعایت این نکات، نتایج شما همیشه دقیق و حرفه‌ای خواهد بود.

جمع‌بندی

Subtotal اکسل یکی از ابزارهای اساسی برای تحلیل داده است. چه با داده‌های فروش کار کنید، چه گزارش‌های اداری بسازید، چه انبارگردانی انجام دهید، Subtotal شما را از فرمول‌نویسی‌های پیچیده نجات می‌دهد. این ابزار امکان گروه‌بندی، جمع‌بندی و تحلیل سریع را فراهم می‌کند، و با Filter و Sort نیز هماهنگ است. اگر گزارش‌های روزانه، ماهانه یا پروژه‌ای دارید، یادگیری کامل این ابزار یک ضرورت است.

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

اگر دوست دارید ۷ مهارت بین‌المللی ICDL (مبانی کامپیوتر، ویندوز ، اینترنت، ورد، پاورپوینت، اکسل و اکسس) را به صورت کاملا رایگان یاد بگیرید و گواهینامه ICDL دریافت کنید، همین الان بر روی تصویر زیر کلیک کنید.