آموزش ابزار group (گروه بندی) در اکسل
معمولا داده ها در صفحه های گسترده اکسل سطح های متفاوتی دارند و تفکیک این سط,ح هنگام خوندن داده ها دشوار است.
در اینجا باید از گروه بندی و طرح بندی داده ها استفاده کنید. گروه بندی سلول ها خواندن اطلاعات را آسان تر می کند و حتی کمک می کند بعضی کارها در طرح به صورت خودکار انجام شوند. گروه بندی و طرح بندی داده ها کمی پیچیده به نظر می رسد اما برای داده های تو در تو بسیار مناسب است.
* این آموزش مخصوص نسخه اکسل ۲۰۱۹ برای ویندوز است ولی اگر نسخه دیگری در دسترس دارید، مشکلی نیست، باز هم همین مراحل را به طور مشابه دنبال کنید.
ویدیوی آموزشی
داده های تو در تو چیست؟ و چرا آن ها را گروه بندی کنید؟
قبل از شروع کار، مفهوم “داده ها با سطوح مختلف” و یا “داده های تو در تو” را توضیح می دهیم.
در تصویر زیر یک صفحه گسترده اکسل را مشاهده می کنید:
سطوح داده ها اینگونه به نظر می رسد:
- Classes
- Semester total
- Classes
- Semester total
- Year total
- Classes
- Semester total
- Classes
- Semester total
- Year total
همانطور که می بینید، سطح های داده سازگاری وجود دارد که تکرار می شوند و در داخل یکدیگر قرار دارند.
داده های تو در تو الزاما اینقدر تکرار نمی شوند اما معنای خودشان را به شما نشان می دهند.
- چرا باید برای داده های تو در تو یک طرح کلی ایجاد کنید؟ ایجاد طرح کلی باعث می شود تا سازماندهی و خوانایی داده ها آسان تر شود به خصوص اگر صفحه گسترده تان بسیار پیچیده باشد. همچنین مرور و استخراج داده ها را بسیار ساده تر می کند.
گروه بندی داده ها
اولین کاری که باید انجام دهید اینه که داده های خود را گروه بندی کنید. این برای اکسل مشخص می کند که کدام قسمت از داده ها به یکدیگر تعلق دارند و در چه سطحی قرار می گیرند.
ساده ترین راه این است که اکسل به طور خودکار داده های تان را طرح بندی کند.
به تب Data در نوار ریبون بروید و در قسمت Group روی گزینه Auto Outline کلیک کنید.
حالا به سمت چپ داده ها در صفحه گسترده خود نگاه کنید:
هر کدام از میله ها یک سطح سازماندهی را نشان می دهند. میله های داخلی رو به سمت راست نشانگر سطوح فرعی هستند.
در این مثال، اکسل داده ها را به خوبی گروه بندی کرده است و داده ها در سه سطح جدا تفکیک شده اند.
اگر اکسل داده ها را به درستی گروه بندی نکند، خودتان می توانید سلول ها را به صورت دستی گروه بندی کنید. این کار را روی مثال امتحان می کنیم.
اول مسیر Ungroup-> Clear Outline را دنبال کنید:
می بینید که میله های سمت چپ صفحه حذف می شوند و برمی گردیم به جایی که شروع کردیم. حالا ردیف های ۲ تا ۱۳ را انتخاب کنید:
این سطرها را برای تشکیل یک مجموعه داده بزرگ انتخاب کردیم: همه داده های Year1.
روی گزینه Group در تب Data کلیک کنید تا نواری در سمت چپ صفحه ظاهر شود و سطرهای انتخاب شده را پوشش دهد:
در نتیجه این مجموعه ردیف ها تشکیل یک گروه می دهند.
خب حالا میخواهیم یک گروه تو در تو ایجاد کنیم. ردیف های ۲ تا ۶ را انتخاب و روی گزینه Group کلیک کنید:
حالا ردیف های ۸ تا ۱۲ را انتخاب و دوباره روی گزینه Group کلیک کنید.
در مرحله بعد همین گروه ها را برای Year2 می سازیم. در نهایت طرحی خواهیم داشت مشابه با چیزی که اکسل به صورت خودکار ایجاد کرد.
نکته مهم: ستون های گروهی
در این مثال ردیف ها را گروه بندی کردیم اما می توانید ستون ها را نیز گروه بندی کنید. ستون های مورد نظر برای گروه بندی را انتخاب و روی دکمه Group کلیک کنید. دقیقاً مانند گروه بندی ردیف ها می باشد.
گروه بندی داده های ستونی زمانی مناسب است که داده ها به جای بالا به پایین، چپ به راست حرکت کنند. معمولا برای داده هایی استفاده می شود که در یک بازه زمانی گسترش می یابند.
افزودن داده به طرح
معمولا صفحات گسترده کامل نیستند و احتمالاً داده های بیشتری در ادامه به طرح اضافه خواهد شد. این نکته را روی مثال امتحان می کنیم. یک ردیف جدید از داده ها با نام Grand total اضافه می کنم.
این سطح بیرونی ترین و بالاترین سطح طرح است و باید از اکسل بخواهیم که آن را هم پوشش دهد.
افزودن این سطح به طرح آسان است: سطرهای ۲ تا ۲۷ را انتخاب کنید (ردیف هایی که در Grand total هستند) و گزینه Group را بزنید.
همانطور که می بینید سطح جدیدی در طرح کلی ایجاد می شود.
نکته مهم: گروه بندی مجدد خودکار
با اضافه کردن داده های بیشتر به طرح خود برای گروه بندی آن ها می توانید روی هر نقطه از مجموعه داده بایستید و روی گزینه Auto Outline از قسمت Group کلیک کنید.
اکسل از شما سؤال می کند که آیا می خواهید طرح فعلی را اصلاح کنید و وقتی که روی دکمه OK کلیک کنید، طرح تان با داده های جدید به روز رسانی خواهید شد.
از طرح ها برای مشاهده سریع داده ها استفاده کنید.
حالا که یک طرح کلی داریم چه کاری می توانیم با آن انجام دهیم.
با این آموزش اکسل صفر تا صد اکسل، رو توی کمترین زمان ممکن یاد بگیر.بهترین پک آموزش اکسل در ایران همین الان خرید و دانلود کنید!
یکی از مفیدترین کارهایی که می توانید انجام دهید اینه که گروه ها را ببندید و نمایش آن ها را پنهان کنید. برای بستن هر گروه روی کادر پایین نوار گروه کلیک کنید:
این کار باعث می شود که خواندن صفحه های گسترده پیچیده آسان تر شود.
همچنین می توانید با کلیک کردن روی شماره های بالای طرح، همه گروه ها را در یک سطح خاص ببندید.
عدد ۱ حداقل مقدار داده را نشان می دهد در حالی که بزرگ ترین عدد همه داده های موجود را شامل می شود.
گروه های فرعی خودکار
اکسل می تواند به صورت خودکار ردیف های فرعی را نیز اضافه کند. در مثال زیر می بینید که هیچ سطر زیرمجموعه و فرعی مانند مثال اول وجود ندارد و یه ستون برای Year اضافه شده است.
برای محاسبه خودکار زیر مجموعه ها بر روی گزینه Subtotal کلیک کنید:
در منوی باز شده لیستی از گزینه ها برای ساخت زیرمجموعه مشاهده می کنید.
در این مثال از لیست At each change in گزینه “Year” و از لیست Add subtotal toگزینه “Students” را انتخاب می کنیم و سپس دکمه OK را می زنیم.
اکسل ردیف های فرعی را برای هر Year ایجاد می کند.
می توانیم همین فرآیند را تکرار کنیم و به جای گزینه Year، “Session” را انتخاب کنیم (توجه داشته باشید که تیک Replace current subtotals را بردارید).
نکته مهم: ترتیب ساخت زیرمجموعه ها مهم است. اگر زیرمجموعه “Session” را قبل از Year بسازید، نتیجه کار متفاوت خواهد شد:
برای جلوگیری از این اتفاق ابتدا بالاترین سطح را در طرح خود بسازید سپس رو به زیرمجموعه های داخلی تر حرکت کنید.
اگر هنوز طرحی ایجاد نکردید هنوز هم می توانید از دستور Subtotal استفاده کنید.
یک طرح کلی برای شما ایجاد می کند و دو مرحله را یکجا انجام می دهد!
سبک دهی خطوط زیرمجموعه
در این مثال هر خط زیرمجموعه به طور واضح پر رنگ شده اند. اکسل می تواند زیر مجموعه ها را به طور خودکار سبک دهی کند.
سلول ها را در طرح خود انتخاب کنید و روی فلش گوشه پایین- راست قسمت Outline در نوار کلیک کنید:
در پنجره باز شده روی دکمه Apply Styles کلیک کنید تا اکسل قالب بندی سطرهای سطوح فرعی و کلی را تغییر دهد.
و اگر می خواهید که در ادامه نیز این سبک ها به طور خودکار اعمال شوند، تیک گزینه Automatic styles را بزنید.
نکات و ترفندهایی برای طرح ها
وقتی با طرح ها کار می کنید باید چند نکته را در خاطر داشته باشید. اول اینکه وقتی تعدادی گروه بسته شده دارید و ردیف های قابل مشاهده را انتخاب می کنید، در واقع ردیف های پنهان شده را نیز انتخاب می کنید دقیقاً مانند وقتی که ردیف ها را به روش سنتی مخفی می کنید.
برای انتخاب تنها ردیف های قابل مشاهده (مثلاً اگر می خواهید آنها را کپی کنید) باید از روش دیگری استفاده کنید.
وقتی نمی خواهید گروه های بسته شده را کپی کنید، به تب Home بروید و از آنجا روی دکمه Find & Select کلیک کنید و گزینه Go To Special را انتخاب کنید:
همچنین می توانید از گروه ها و طرح ها برای ساخت گراف ها و نمودارهای زیر مجموعه های داده ها استفاده کنید.
به نمودار زیر نگاه کنید، این نمودار شامل همه داده های صفحه گسترده مثال است:
اگر بیشتر گروه ها را ببندیم و نمودار مشابهی ایجاد کنیم، یک نمودار کوچکتر ساخته می شود.
صفحات گسترده خود را با گروه بندی مدیریت کنید
گروه بندی و طرح بندی ممکنه در ابتدا پیچیده به نظر برسد اما با انجام تمرین می توانید آیتم ها را گروه بندی کنید، زیرمجموعه بسازید و صفحات گسترده خود را بهتر مدیریت کنید.
اگر با داده های تو در تو کار می کنید، ارزش زمان گذاشتن را دارد!
دروس کاربردی برای همه کاربران
دانلود فیلم های آموزش صفر تا صد اکسل +جزوه
سلام وقت شما بخیر من یک لیست طولانی رو در طول زمان نوشتم که تعداد سلول های اون(ستونی) بسیار زیاد هست. حالا میخوام قیمت هر کالایی که بصورت متغیر در جابه جای ستون وجود داره رو تعیین کنم که به واسطه فرمول IF موفق به انجام اینکار نمیشم اگر امکانش هست راهنمایی کنید سپاسگزارم
پاسخ