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

نحوه پیاده‌سازی گروه بندی در اکسل روی سطرها

سه روش برای گروه‌بندی سطرها در جدول اکسل به‌شرح زیر وجود دارد.

• گروه‌بندی خودکار
• گروه‌بندی دستی
• گروه‌بندی با استفاده از تابع SUBTOTAL

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

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

حال در ادامه، هر یک از روش‌ها را همراه مثال به‌طور کامل تشریح می‌کنیم.

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

فرض می‌کنیم سه سری داده مختلف در جدول به شرح زیر در اختیار داریم.

برای گروه‌بندی خودکار داده‌ها به ترتیب مراحل زیر را انجام می‌دهیم.

۱. یک سلول از سری اول داده‌ها را انتخاب می‌کنیم.

۲. در زبانه «Data» از نوار بالایی اکسل به بخش «Group» می‌رویم و سپس روی «Auto Outline» کلیک می‌کنیم.

۳. همان‌طور که در تصویر مشخص است، گروه‌بندی سطرها با نمایش میله‌هایی در قسمت راست کاربرگ و نمایش دو باکس شماره‌دار ۱ و ۲ در قسمت بالا، انجام می‌گیرد.

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

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

۱. کل سطر و ستون‌های جدول را انتخاب می‌کنیم.

۲. بعد از ورود به زبانه «Data» روی بخش «Group» کلیک می‌کنیم. در باکس باز شده، گزینه «Rows» و سپس دکمه «OK» را انتخاب می‌کنیم.

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

۴. برای ایجاد سطح‌های بعدی، به ترتیب هر سری از ردیف داده‌ها را جداگانه انتخاب می‌کنیم و فرایند را برای هر سری تکرار می‌کنیم. به‌عنوان مثال، در مورد سری داده‌های «قیمت خرید پرتقال»، سطرهای A2 تا A4 را انتخاب می کنیم. بعد از ورود به بخش «Group» و انتخاب دکمه «Rows» این سری از داده‌ها به‌عنوان سطح دوم گروه‌بندی می‌شوند.

۵. با انجام مراحل فوق در مورد دو سری دیگر، سه سطح از داده‌ها به‌شکل جداگانه گروه‌بندی می‌شوند.

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

گروه‌بندی سطرها و انجام محاسبات در زیرگروه‌ها با تابع SUBTOTAL

برخی اوقات سری داده‌های جدول اکسل به شکلی است که علاوه بر گروه‌بندی می‌خواهیم عملیات خاصی را نیز در هر زیرگروه انجام دهیم. به‌عنوان مثال می‌توانیم به‌شکل هم‌زمان در هر گروه عملیات جمع، حداقل، حداکثر، میانگین داده‌ها در اکسل و شمارش آن‌ها را نیز انجام دهیم. در این حالت بر حسب نیاز، ترکیبی از تابع SUBTOTAL را همراه با سایر توابع اکسل مانند MIN و MAX و AVG و تابع COUNT استفاده می‌کنیم.

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

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

۱. فرض می‌کنیم جدول داده‌هایی به شرح تصویر زیر داریم.

۲ بعد از انتخاب سلول دلخواه در جدول، از زبانه «Data»، بخش «Group» و «Subtotal» را انتخاب می‌کنیم.

۳. با کلیک روی بخش «Subtotal» پنجره‌ای مانند تصویر زیر باز می‌شود.

پنجره «subtotal» دارای بخش‌های اصلی زیر است.

• بخش At each change in: بخشی از جدول که می‌خواهیم گروه‌بندی و محاسبات را بر اساس تغییرات در آن انجام دهیم.
• بخش Use function: توابعی مانند SUM و MAX و AVG و سایر موارد مشابه که می‌خواهیم با استفاده از آن‌ها محاسبات مد نظر را انجام دهیم. این توابع پراستفاده اکسل همانند تصویر از منوی آبشاری قابل تغییر هستند.
• بخش Add subtotal to: بخش‌هایی که در نظر داریم محاسبات مورد نظر روی آن‌ها انجام گیرد.

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

• بخش Replace Current subtotal: فعال کردن تیک مربوط به این باکس به‌معنای آن است که با اجرای دستور «subtotal» در هر بار، محاسبات قبلی حذف و محاسبات جدید جایگزین آن می‌شوند.
• بخش Page break between groups: فعال بودن تیک این گزینه به آن معنا است که اکسل به‌صورت خودکار هر گروه از داده‌ها را در یک صفحه جداگانه قرار می‌دهد. این ویژگی به‌خصوص در چاپ گزارش‌ها بسیار مفید است.
• بخش Summery below data: فعال بودن تیک این گزینه به آن معنا است که نتایج محاسبات درست بلافاصله در انتهای داده‌ها نمایش داده می‌شوند.

۴. بعد از کلیک روی دکمه «OK» همانند تصویر زیر نتیجه محاسبه همراه با درج میله گروه‌بندی در سمت راست مشخص است.

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

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

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

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

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

نحوه پیاده‌سازی گروه بندی در اکسل روی ستون‌ها

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

۱. برای درست کردن لایه اولیه، کل سطر و ستون‌های جدول را انتخاب می‌کنیم. سپس از طریق مسیر Data>Group، در پنجره باز شده، بخش «Columns» را علامت می‌زنیم.

۲. لایه اولیه به شکل تصویر زیر در خواهد آمد.

۳. در این مرحله با انتخاب ستون‌های دلخواه از جدول، به‌عنوان مثال ستون‌های A تا C یا ستون‌های A تا B، عملیات گروه‌بندی را به همین شکل تکرار می‌کنیم.

پنهان کردن داده‌ها بعد از گروه‌ بندی در اکسل

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

در مثال فرضی مورد اشاره، با کلیک روی عدد ۲، همه سری داده‌ها ظاهر می‌شوند. اما اگر تعداد داده‌ها بیشتر باشد، در این حالت دو سری از داده در صفحه اکسل مشخص خواهند بود.

البته این کار با استفاده از علامت‌ «-» در کنار میله‌ها هم قابل انجام است. به‌عنوان مثال در گروه‌بندی سطرها برای جدول مورد اشاره، اگر به قسمت سمت راست کاربرگ اکسل نگاه کنیم، باکس‌های دارای علامت «-» مشخص هستند.

با کلیک روی هر یک از علامت‌های «-» سطح گروه‌بندی شده مربوط به آن پنهان می‌شود. همچنین با کلیک بر علامت‌های «+» سطوح بار دیگر ظاهر خواهند شد.

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

حذف گروه‌بندی سطر یا ستون در اکسل بسیار ساده و مشابه یکدیگر است. به‌عنوان مثال برای حذف گروه‌بندی ستون‌ها کافی است مراحل را از زبانه «Data» به شکل زیر انجام دهیم.

۱. بعد از انتخاب ستون‌های مورد نظر برای حذف گروه‌بندی روی بخش «Ungroup» کلیک می‌کنیم.

۲. در پنجره باز شده گزینه «Columns» را انتخاب می‌کنیم.

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

حذف گروه‌بندی سطرها نیز به همین شکل است. با این تفاوت که بعد از کلیک روی بخش «Ungroup» گزینه «Rows» را علامت می‌زنیم.

جمع‌بندی پایانی

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