گروه بندی داده ها در اکسل | افزودن خودکار جمع جزء و جمع کل به داده ها

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

مقدمه

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

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

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

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

مثال

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

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

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

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

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

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

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

انتخاب سربرگ Data در اکسل

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

انتخاب ابزار Outline از سربرگ Data

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

انتخاب ابزار Subtotal در اکسل

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

پنجره تنظیمات Subtotal در اکسل

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

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

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

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

کاربرد منوی At each change in در اکسل

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

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

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

قسمت دوم: Use function

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

کاربرد منوی Use function در اکسل

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

قسمت سوم: Add subtotal to

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

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

کاربرد منوی Add subtotal to در اکسل

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

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

سایر تنظیمات در دسترس در ابزار Subtotal

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

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

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

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

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

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

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

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

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

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

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

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

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

این پست دارای 12 نظر است

  1. مجتبی

    مطالب آموزنده است ولی تصاویر هیچکدام از مطالب باز نمیشود، اگر امکان دارد مشکل را حل نمائید

    1. sadegh_abshenas

      ممنون از بازخورد شما. مجددا بررسی بفرمایید.

  2. unlawlBup

    PubMed Paick JS Choi HK Kim SC Ahn TY Kim JJ Park JK. [url=https://bestadalafil.com/]cialis for sale[/url] Cialis health reform bill covers cialis https://bestadalafil.com/ – buy cialis online Is Cephalexin A Form Of Amoxicillin Wxdkxx

  3. Quality articles or reviews is the crucial to invite the users to visit the
    site, that’s what this web site is providing.

دیدگاهتان را بنویسید