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

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

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

شما می توانید در تب Data در گروه Outline به دستور Subtotal دسترسی داشته باشید.
برای درک بهتر نحوه عملکرد این ابزار، آموزش را با یک مثال کاربردی دنبال می کنیم.
در جدول داده زیر، قصد داریم انتهای هر شهر، جمع “مبلغ کل” مربوط به آن را محاسبه کنیم.

قبل از هر اقدامی لازم است داده های ستون مورد نظر را با استفاده از ابزار مرتب سازی اکسل، مرتب کنیم تا نام هر شهر در ستون پراکنده نباشد.

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

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

At each change in

این قسمت ردیف اول را به عنوان تیتر شناسایی می کند و از ما می خواهد تیتر ستون مورد نظر برای اعمال Subtotal یا به عبارتی گروه بندی را انتخاب کنیم. در این نمونه ما ستون “عنوان شعبه” را انتخاب می کنیم.

Use function

در این قسمت عملیات مختلفی مانند جمع (SUM)، میانگین (AVERAGE)، شمارش (COUNT) و موارد دیگر ارائه می شود. به دلیل اینکه ما قصد جمع زدن مقادیر را داریم پس SUM را انتخاب می کنیم.

Add subtotal to

در این قسمت باید تیتر ستونی که می خواهیم عملیات انتخاب شده در بخش Use function، روی آن صورت گیرد، را انتخاب کنیم. ما برای این قسمت ستون “مبلغ کل” را در نظر گرفته ایم.

بعد از تکمیل موارد بالا، با کلیک کردن OK پنجره را می بندیم.

مشاهده می کنید اسامی هر شهر در یک گروه جداگانه قرار گرفته و جمع “مبلغ کل” مربوط به هر شهر نوشته شده است. بنابراین هر قسمتی که شهر تغییر کرده، یک گروه ایجاد شده  و اطلاعات مربوط به هر شهر در یک سطر جداگانه نمایش داده می شود.

اگر به پنجره Subtotal بیشتر توجه کنید متوجه می شوید که ۳ گزینه در قسمت پایینی ارائه شده که هر کدام امکانات خاصی را در اختیار شما قرار می دهند:

Replace current subtotals

این گزینه امکان اعمال چند Subtotal را بر روی محدوده مورد نظر فراهم می کند. به عنوان مثال، اگر مراحل قبلی را دوباره انجام دهیم با این تفاوت که به جای SUM، تابع AVERAGE را انتخاب کنیم، انتخاب گزینه Replace Current Subtotals، باعث جایگزین شدن Subtotal جدید با Subtotal قبلی می شود. همچنین نتیجه برداشتن تیک این گزینه نمایش هر دو Subtotal به صورت شکل زیر است.

Page break between groups

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

Summary below data

اگر این گزینه در حالت انتخاب شده باشد، سطرهای شامل اطلاعات هر گروه، در زیر هر دسته قرار می گیرند، در غیر این صورت این سطر بالای هر گروه نمایش داده می شود.

نحوه حذف کردن Subtotal

گزینه Remove All در پنجره Subtotal مربوط به حذف مراحل انجام شده است و تمامی داده های برگه را از حالت گروه بندی خارج می کند.

مشاهده گروه ها بر اساس سطوح مختلف

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

۱- سطح یک حداقل جزئیات را نمایش دهد. در این مثال، این سطح شامل Grand total است که مجموع کل داده های ستون “مبلغ کل” می باشد.

۲- برای نمایش جزئیات بیشتر، سطح دوم ارائه شده است که فقط زیرمجموعه ها (سطرهای Total) و مقدار کل (Grand total) را نمایش می دهد.

۳- سطح ۳، تمامی جزئیات برگه اکسل را شامل می شود.

همانطور که در تصاویر مشخص است، در سمت چپ برگه اکسل علامتهای + و – وجود دارد. دکمه های با علامت مثبت، دکمه نمایش جزئیات (Show Detail) نامیده می شوند و با استفاده از آن ها می توانید گروه های بیشتری را نمایان سازید. همچنین دکمه های با علامت منفی دکمه های مخفی کردن جزئیات (Hide Detail) نامیده می شوند و با استفاده از آن ها می توانید گروه ها را مخفی کنید. در تصویر زیر با انتخاب اولین دکمه +، اولین گروه نمایش داده شده و این دکمه به – تبدیل شده است.

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