گاهی اطلاعات موجود در Worksheet اکسل 🔢 آن قدر زیاد و پیچیده است که با نگاه کردن به آن گیج می‌شویم. در چنین حالتی، یکی از بهترین شیوه‌ها برای سامان دادن داده‌ها این است که آنان را گروه بندی کنیم. گروه بندی هم در سطح ستون و هم سطر امکان‌پذیر است و قابلیت‌های زیادی دارد. در این مطلب از کلاسیک وب به آموزش کامل نحوه دسته بندی در اکسل میپردازیم.

چگونه در اکسل دسته بندی کنیم؟

?How to Group in Excel

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

نحوه دسته بندی ردیف ها در اکسل

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

گروه بندی سطرها در اکسل به صورت خودکار

اگر داده‌های شما شامل یک سطح از اطلاعات هستند، سریع‌تری راه‌حل این است که ردیف‌ها را دسته‌بندی کنید.

روی یکی از سلول‌های ردیف موردنظر خود کلیک کنید. سپس به سربرگ Data بروید. روی فلش زیر Group کلیک کنید. سپس Auto Outline را انتخاب کنید.

نکته: اگر ردیف‌های summary بالای گروهی از ردیف‌های Detail قرار دارند، قبل از دسته‌بندی، به سربرگ Data بروید. Outline group را انتخاب کنید. سپس در پنجره باز شده تیک Summary rows below detail را بزنید.

وقتی گروه ایجاد شود، می‌توانید با کلیک روی – کنار آن اطلاعاتش را ببندید و با کلیک روی + اطلاعات هر گروه را باز کنید. با کلیک روی یکی از اعداد ۱، ۲ و ۳ در سمت چپ و بالای Worksheet می‌توانید هم سطرها را در یک خاص جمع با گسترش دهید.

گروه بندی سطرها در اکسل به صورت دستی: بخش اول

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

نکته: در چنین حالتی اطلاعات شما نباید دارای ردیف‌های پنهان باشد. و گرنه دسته‌بندی اشتباه انجام می‌شود.

ایجاد گروه Outer

ابتدا زیرمجموعه بزرگ‌تری از اطلاعات را انتخاب کنید. ردیف‌های Detail و Summary آن را هم انتخاب کنید.

در مثال زیر برای گروه‌بندی تمامی اطلاعات ردیف ۹ یا East total، ما ردیف‌های ۲ تا ۸ را انتخاب کردیم.

در سربرگ Data، روی Outline group کلیک کنید و سپس روی Group بزنید. در پنجره باز شده تیک Rows را بزنید و روی OK کلیک کنید.

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

این کار را برای دسته‌بندی کردن سایر ردیف‌های موردنظر خود هم انجام دهید. در این مثال، می‌خواهیم ردیف‌های North را هم دسته‌بندی کنیم. برای همین باید ردیف‌های ۱۰ تا ۱۶ را انتخاب کنید. سپس روی Data tab> Group button> Rows کلیک کنید. این بخش هم گروه‌بندی می‌شود:

برای انجام سریع‌تر این کار، به جای انتخاب Group می‌توانید دکمه‌های Shift + Alt  را به همراه دکمه جهت راست فشار دهید.

گروه بندی سطرها در اکسل به صورت دستی: بخش دوم

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

برای این کار باید تمامی ردیف‌های Detail بالای ردیف Summary را انتخاب کنید. سپس روی Group کلیک کنید.

به عنوان مثال، می‌خواهیم گروه Apples را در بخش East گروه‌بندی کنیم. ردیف ۲ و ۳ را انتخاب می‌کنیم و روی Group می‌زنیم. برای گروه‌بندی Orange، ردیف ۵ تا ۷ را انتخاب می‌کنیم و سپس Group را می‌زنیم. برای North هم می‌توان همین کار را کرد.

اضافه کردن سطوح بیشتر گروه‌بندی

اگر اطلاعات جدیدی به ورک شیت اضافه شد، می‌توانید آنان را هم گروه‌بندی کنید. به عنوان مثال، در اینجا ردیف Grand total را اضافه کردیم. برای انجام ایت کار باید تمامی ردیف‌ها به جز ردیف Grand total را انتخاب کنید (۲ الی ۱۷).  سپس Data tab > Group button > Rows را انتخاب کنید.

همان طور که در تصویر می‌بینید، اطلاعات ما در ۴ سطح دسته‌بندی شدند:

Level 1: Grand total

Level 2: Region totals

Level 3: Item subtotals

Level 4: Detail rows

رده بندی در اکسل: نحوه پنهان کردن و باز کردن اطلاعات ردیف‌های گروه‌بندی شده

یکی از ویژگی‌های تقسیم بندی در اکسل این است که می‌توانید اطلاعات یک گروه را ببندید و راحت‌تر داده‌های ردیف‌های دیگر را مشاهده کنید. برای این کار کافی است روی – در پایین نوار یک گروه بزنید. در مثال زیر می‌خواهید داده‌های ردیف‌های East را پنهان کنیم و تنها East total را داشته باشیم:

روش دیگر این است که روی یکی از ردیف‌های گروه موردنظر کلیک کنید. سپس به سربرگ Data بروید و روی Outline group بزنید. سپس Hide Detail را انتخاب کنید.

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

در مثال ما ۴ سطح وجود دارد.

۱: فقط ردیف ۱۸ یا Grand total نمایش داده خواهد شد.

۲: نمایش ردیف‌های ۹، ۱۷ و ۱۸ یا Grand total و Region subtotals

۳: ردیف‌های ۴، ۸، ۱۸، ۱۳، ۱۶، ۱۷ و ۱۸ یا Region subtotals، Grand total و Item subtotals نمایش داده می‌شوند.

۴: تمامی ردیف‌ها را نشان می‌دهد.

برای نمایش اطلاعات هم کافی است روی + بزنید یا Show Detail را در سربرگ Data و Outline group انتخاب کنید.

روش دیگر برای پنهان کردن این است که دکمه های ALT+SHIFT+- را فشار دهید. برای نمایش اطلاعات هم از ALT+SHIFT+= استفاده کنید.

دسته بندی در اکسل: حذف Outline یا گروهی خاص از ردیف‌ها

در دسته بندی در اکسل می‌توانید یک Outline کامل را حذف کنید. برای این کار به سربرگ Data و سپس Outline group بروید. حال روی فلش زیر Ungroup بزنید. سپس Clear Outline را انتخاب کنید.

حذف Outline موجب حذف داده نمی‌شود.

اگر برخی ردیف‌ها را بسته یا پنهان کرده باشید، پس از انجام این کار، امکان دارد آن ردیف‌ها پنهان باقی بمانند.

پس از حذف کامل Outline نمی‌توانید با کلیک روی Undo یا Ctrl + Z آن را برگردانید. باید دوباره آن را ایجاد کنید.

برای از بین بردن گروه خاصی از ردیف‌ها بدون حذف تمام گروه، روی ردیف موردنظر کلیک کنید. به Data tab > Outline group بروید و روی دکمه Ungroup بزنید یا Shift + Alt  را به همراه دکمه جهت سمت چپ فشار دهید.

در پنجره باز شده تیک Rows را بزنید و OK را کلیک کنید.

در این مثال، می‌خواهیم گروه‌های Apples Subtotal و Oranges Subtotal را حذف کنیم اما گروه East Total را نگه داریم.

برای یادگیری ویرایش و تنظیم اندازه جدول در اکسل، مطلب آموزش تنظیم اندازه، سطر، ستون، سلول و… جدول در اکسل را مطالعه کنید.

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

پیش از گروه بندی ستون در اکسل، مطمئن شوید که هر ردیف که می‌خواهید از آن Outline بسازید، دارای لیبلی در اولین ستون باشد. در ضمین هیچ ردیف یا ستون خالی نباید وجود داشته باشد.

اگر ستون summary شما در سمت چپ ستون‌های Detail قرار دارد، در سربرگ Data و در بخش Outline، روی فلش بزنید.

در پنجره باز شده تیک Summary columns to right of detail را بزنید و روی OK کلیک کنید.

برای ایجاد Outline داده‌ها به صورت خودکار، روی سلولی کلیک کنید. در سربرگ Data و در بخش Outline، روی فلش زیر Group بزنید و Auto Outline را انتخاب کنید.

به صورت دستی هم می‌توانید Outline ایجاد کنید. در مثال ما، ستون A شامل لیبل‌ها می‌شود. تمامی ستون‌های جزئی و زیرمجموعه را انتخاب کنید. اگر تمامی ستون را انتخاب نکنید، وقتی روی Group کلیک کردید، از شما پرسیده خواهد شد که می‌خواهید ردیف را گروه‌بندی کنید یا ستون را.

ستون Grand total را انتخاب نکنید.

در سربرگ Data و در بخش Outline روی Group کلیک کنید.

حال نوار گروه در بالای آن ایجاد می‌شود. برای گروه‌بندی ستون‌های سطح دو و بالاتر، روی ستون‌های Detail که در مجاور ستون Summary قرار دارند، کلیک کنید.

شما می‌توانید گروه‌های مختلفی در هر سطح ایجاد کنید.

برای حذف گروه، ستون موردنظر را انتخاب کنید. سپس روی سربرگ Data بزنید. در بخش Outline نیز روی Ungroup بزنید.

نحوه دسته بندی اعداد در اکسل و موارد دیگر: جا به جا کردن گروه

وقتی به گروه بندی اعداد در اکسل پرداختید یا هر داده دیگری را دسته‌بندی کردید، می‌توانید گروه‌ها را در ورک شیت جا به جا کنید. برای این کار روی گروه موردنظر راست کلیک کنید. سپس گزینه Copy یا Move را انتخاب کنید. حال مکان موردنظر خود را انتخاب کنید. در نهایت، روی OK بزنید.

برای کپی کردن می‌توانید روی Create a Copy کلیک کنید. سپس محل موردنظر را برای کپی انتخاب کنید.

برای یادگیری کپی فرمول در اکسل، مطلب آموزش کامل ۸ روش کپی فرمول در Excel را مطالعه کنید.

گروه بندی شیت ها در اکسل و ایجاد Styleهای مختلف

شما می‌توانید برای ردیف‌ها و ستون‌ها از استایل‌های مختلف استفاده کنید:

ردیف‌ها:

RowLevel_1

RowLevel_2

ستون‌ها:

ColLevel_1

ColLevel_2

این استایل‌ها به شکل Bold، ایتالیک و سایر فرمت‌های متنی نوشته می‌شوند و ستون‌ها و ردیف‌های مختلف را از هم مشخص می‌کنند. این تغییرات را هم در هنگام ایجاد گروه و هم پس از ایجاد آن می‌توانید اعمال کنید.

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

تیک Automatic styles را بزنید.

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

تیک Automatic styles را بزنید و روی Apply Styles کلیک کنید.

گروه بندی در اکسل: انتخاب و کپی کردن گروه‌های نمایان

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

برای ابن که تنها ردیف‌های نمایان را انتخاب کنید و ردیف‌های پنهان را کپی نکنید، با ماوس خود ردیف نمایان را انتخاب کنید. به عنوان مثال، ما تمامی ردیف‌های Detail را جمع کردیم و حال ردیف‌های Summary را انتخاب می‌کنیم.

به سربرگ Home بروید و روی Editing group کلیک کنید. سپس روی Find & Select > Go To Special کلیک کنید یا Ctrl + G را فشار دهید و Go To Special را بزنید. در پنجره باز شده، Visible cells only را انتخاب کنید و روی OK بزنید.

در تصویر زیر می‌توانید ببینید که ردیف‌های نمایان انتخاب شده‌اند. ردیف‌های پنهان با نواری سفید مشخص شده‌اند.

حال با Ctrl + C می‌توانید ردیف‌ها را کپی کنید و با Ctrl + V آنان را در جای دیگری Paste کنید.

رده بندی در اکسل: کپی کردن داده‌های Outline و پنهان کردن آن

اگر نمی‌توانید نمادهای ۱، ۲ و ۳ و همچنین + و – را مشاهده کنید، به File > Options > Advanced بروید. سپس در بخش Display options for this worksheet، تیک Show outline symbols if an outline is applied را بزنید.

حال با استفاده از این نمادها، اطلاعاتی را که نمی‌خواهید کپی کنید، پنهان کنید.

سپس طیف ردیف‌های Summary را انتخاب کنید. به سربرگ Home و سپس Editing بروید. روی Find & Select بزنید و سپس روی Go To بزنید.

سپس روی Go To Special کلیک کنید و Visible cells only را انتخاب کنید. در نهایت روی OK بزنید.

برای پنهان کردن یک Outline هم کافی است تیک Show outline symbols if an outline is applied را بردارید.

رده بندی در اکسل: ایجاد Subtotal

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

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

ابتدا ورک شیت را با توجه به داده‌هایی که می‌خواهید برای آن Subtotal ایجاد کنید، مرتب کنید. ما از سایز کوچک به بزرگ آن‌ها را دسته‌بندی کردیم.

وارد سربرگ Data شوید و Subtotal را انتخاب کنید.

در پنجره Subtotal روی فلش At each change in کلیک کنید. نام ستون موردنظر را وارد کنید. ما T-Shirt Size را وارد کردیم.

در Use function کاری را که قرار است این Subtotal انجام دهد، انتخاب کنید. ما Count را انتخاب کردیم تا تعداد را شمارش کنیم.

در بخش Add subtotal to نیز ستونی را انتخاب کنید که قرار است محاسبات Subtotal بر آن اعمال شود. ما T-Shirt Size را انتخاب کردیم.

در مثال ما می‌توانید مشاهده کنید که داده‌ها بر اساس سایز از کوچک به بزرگ دسته‌بندی شده‌اند و مجموع هر دسته‌بندی را می‌توانید زیر آن مشاهده کنید.

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

گروه بندی در اکسل: ایجاد گزارشی خلاصه با چارت

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

ابتدا داده‌های خود را گروه‌بندی کنید. سپس جزییات را با کلیک روی ۱، ۲ و ۳ و همین طور + یا – پنهان کنید. به گونه‌ای که فقط مجموع هر بخش نمایان باشد. به تصویر زیر توجه کنید:

داده Summary موردنظر خود را که می‌خواهید تبدیل به چارت شود، انتخاب کنید. به عنوان مثال، برای ایجاد چارت دو مجموع Buchanan و Davolio، بدون مجموع کل، باید سلول‌های A1 را تا C19 انتخاب کنید. سپس به Insert > Charts > Recommended Charts بروید. روی سربرگ All Charts بزنید و نوع چارت را انتخاب کنید.

ما Clustered Column را انتخاب کردیم.

اگر در فهرست داده‌ها چیزی را پنهان یا نمایان کنید، در چارت نیز تغییراتی ایجاد می‌شود.

نظرات و پیشنهادات شما؟

در این مطلب از کلاسیک وب به آموزش کامل دسته بندی اطلاعات در اکسل پرداختیم. شما از کدام روش برای گروه بندی اطلاعات استفاده کردید؟ لطفا نظرات و پیشنهادات خود را در بخش دیدگاه با ما در میان بگذارید.