وبلاگ

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

مقدمه

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

شاید اگر الان بدانید که اکسل اینکار را براحتی و فقط در عرض چند مرحله ساده انجام می دهد خیلی حسرت بخورید!

اکسل ( Excel ) قابلیتی پیشرفته بعنوان Outline دارد که با استفاده از آن می توانید داده ها را براحتی و بصورت کاملاً خودکار گروه بندی و جمع جزء و کل را به انتهای هر گروه و انتهای داده های خود اضافه نمایید.

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

مثال

مرحله اول: آماده سازی داده های خام

داده های خام فایل اکسل مثال را می توانید در محدوده سلول های A1 تا F11 مطابق تصویر زیر مشاهده نمایید.

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

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

مرحله دوم: انجام خودکار عملیات گروه بندی داده ها، افزودن جمع جزء‌ و جمع کل توسط اکسل

۱- مطابق شکل از سربرگ Data را در اکسل خود انتخاب نمایید.

۲- با کلیک روی گزینه Outline ابزارهای درون آن را مطابق شکل در حالت مشاهده قرار دهید.

۳- مطابق شکل روی ابزار Subtotal کلیک نمایید تا اکسل صفحه تنظیمات ابزار را به شما نمایش دهد.

۴- اکسل بعد از کلیک روی ابزار Subtotal تنظیمات محاسبه جمع جزء و جمع کل را در یک کادر محاوره ای مطابق شکل زیر نمایش خواهد داد.

نکته
اکسل بصورت خودکار سطر اول را بعنوان سطر عناوین در نظر می گیرد و هیچ محاسباتی رو آن انجام نمی دهد.

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

قسمت اول: At each change in

اگر روی کلید کشوی این قسمت از تنظیمات کلیک نمایید اکسل عناوین موجود در سطر عناوین را مانند شکل زیر نمایش خواهد داد.

در این قسمت اکسل از شما می خواهد که مشخص نمایید که کدام ستون را مبنای ایجاد سطر جمع جزء باید قرار دهد. اکسل ستونی را که شما در این قسمت انتخاب نمایید سطر به سطر بررسی می نماید و در صورتی بوجود هر تغییری یه سطر جمع جزء ایجاد خواهد نمود.

در اکسل این آموزش ما ستون گروه را انتخاب نموده ایم.

نکته مهم
قبل از اینکه اقدام انتخاب ستون مبنای ایجاد سطر جمع جزء داده های ستون را باید مرتب نموده باشید. اگر این کار را از قبل انجام ندهید احتمالاً نتیجه دلخواهتان حاصل نخواهد شد.

قسمت دوم: Use function

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

در اکسل این آموزش ما تابع Sum را انتخاب نموده ایم.

قسمت سوم: Add subtotal to

در این قسمت باید به اکسل بگویید که تابعی که در قسمت دوم انتخاب نموده اید را در سطر جمع جزء روی کدام یک از ستون ها باید درج نماید.

ما در اکسل این آموزش ستون ها را مطابق تصویر زیر انتخاب نموده ایم.

قسمت چهارم: سایر تنظیمات

در این قسمت اکسل مطابق تصویر زیر سه انتخاب را در اختیار شما قرار داده است که توضیح هر یک را در زیر مشخص نموده ایم.

اگر جعبه انتخاب اول ( Replace current subtotals ) در حالت انتخاب قرار داشته باشد اکسل بصورت خودکار سطرهای جمع جزء جدید را جایگزین سطرهای جمع جزء قبلی (در صورت وجود) می نماید.

در جعبه انتخاب دوم ( Page break between groups ) می توانید مشخص نمایید که اکسل بین گروه ها یک شاخص صفحه ( Page break ) ایجاد نماید. این تنظیم برای وقتی که می خواهید هر گروه در یک صفحه مجزا چاپ شود ایده آل می باشد.

کاربرد جعبه انتخاب سوم ( Summary below data ) برای این است که اکسل بصورت خودکار جمع کل را زیر آخرین گروه داده اضافه نماید.

ما در اکسل خودمان تنظیمات پیش فرض اکسل را که طبق تصویر هم مشخص می باشد تغییر ندادیم.

مرحله سوم: مشاهده نتیجه

بعد از اعمال تنظیمات در کادر محاوره ای تنظیمات ابزار Subtotal می توانید با کلیک روی کلید Ok نتیجه را مشاهده نمایید.

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

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

اگر به سمت راست شماره سطرها دقت نمایید (مکان مشخص شده در تصویر بالا) متوجه خواهید شد که اکسل بصورت خودکار سه شماره اضافه نموده است.

اگر روی هر یک از آنها کلیک نمایید با مشاهده کاربرد هر کدام احتمالاً شگفت زده خواهید شد.

امیدواریم این آموزش برای شما مفید واقع شود.

فایل پیوست این آموزش