مرتب سازی در اکسل — راهنمای کاربردی

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

به منظور آشنایی بیشتر با نحوه کار با اکسل بهتر است ابتدا نوشتارهای دیگر مجله فرادرس که در این رابطه منتشر شده‌اند را مطالعه کنید. به همین منظور مطالب آموزش مقدماتی اکسل (Excel) — به زبان ساده و فرمول نویسی در اکسل – آموزش مقدماتی را به شما پیشنهاد می‌کنیم. از طرفی خواندن مدیریت نوار (Ribbon) اکسل با چهار ترفند کاربردی — راهنمای جامع و چگونه با «Conditional Formatting» سطرها را در اکسل برجسته کنیم؟ — آموزش گام ‌به ‌گام نیز خالی از لطف نیست.

مرتب سازی در اکسل

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

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

اکسل به عنوان یک برنامه کاربردی، برای ثبت، ذخیره و انجام عملیات روی داده‌ها ایجاد شده است. ساختار نگهداری داده‌ها در این نرم‌افزار به شکل جدولی است. معمولا متغیرها یا ویژگی‌ها را در ستون‌ها مشخص کرده و مقادیر مربوط به هر یک از این متغیرها را برای افراد یا هویت‌ها متفاوت، در سطرها ثبت می‌کنیم. این ساختار را معمولا «ساختار جدول» (Table Structure) می‌نامیم.

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

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

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

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

مراحل مرتب سازی در اکسل

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

فیلم آموزش اکسل – فیلتر و کاربرد آن در ساخت گزارش - مقدماتی و پیشرفته در فرادرس

کلیک کنید

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

این گام‌ها به صورت زیر هستند.

1. انتخاب ستون (یا ستون‌هایی) از کاربرگ که باید براساس مقدار آن‌ها، سطرهای جدول جابجا شوند. به این ترتیب ملاک یا مبنای مرتب‌سازی جدول تعیین می‌شود. این ستون ممکن است شامل مقادیر متنی یا عددی باشد. در گام یا مرحله بعدی مشخص می‌کنیم که ترتیب قرار گیری سطرها براساس این ستون باید به چه شکلی صورت گیرد.
2. انتخاب نوع ترتیب در مرحله دوم صورت می‌گیرد. در این مرحله مشخص می‌کنید که با توجه به نوع مقادیر ستون ملاک، باید سطرها با نظم صعودی (Ascending) یا نزولی (Descending) ظاهر شوند.

تعیین ملاک مرتب سازی در اکسل

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

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

هنگام اجرای دستور مرتب سازی در این حالت پنجره‌ای مانند تصویر بالا ظاهر می‌شود که با انتخاب گزینه Expand the selection همه ناحیه اطلاعاتی برای مرتب سازی به کار می‌رود ولی اگر می‌خواهید فقط ناحیه انتخابی، مرتب شوند گزینه Continue with the current selection را انتخاب کنید، سپس دکمه Sort را بزنید.

مرتب سازی صعودی یا نزولی در اکسل

با توجه به نوع مقادیری که در ستون ملاک قرار گرفته است نحوه مرتب سازی تعیین می‌شود. اگر مقادیر این ستون متنی باشد، نحوه مرتب‌سازی می‌تواند از «الف» تا «ی» (در انگلیسی از A تا Z) باشد که به آن مرتب سازی صعودی گفته می‌شود. البته می‌توانید برای مقادیر متنی از مرتب‌سازی نزولی یا از «ی» تا «الف» (در انگلیسی از Z تا A) نیز استفاده کنید. این امر به این بستگی دارد که اولویت جستجوی شما به چه شکل است.

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

برای مقادیر عددی نیز مرتب سازی صعودی یا نزولی با توجه به ترتیب اعداد مشخص می‌شود. اگر مرتب سازی «صعودی» (Ascending) را انتخاب کرده باشید، مقادیر از کم به زیاد در جدول نظم پیدا می‌کنند. در حالیکه هنگام استفاده از مرتب سازی «نزولی» (Descending)، مقادیر از زیاد به کم چیده می‌شوند.

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

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

نکته: اگر ستون که مربوط به ملاک مرتب سازی است، شامل متن و عدد باشد، ابتدا مقادیر عددی قرار گرفته، سپس مقادیر متنی ظاهر می‌شوند. همچنین سلول‌هایی که شامل ترکیب عدد و متن هستند، از چپ به راست برای متن لاتین (و از راست به چپ برای متن فارسی) جداسازی صورت گرفته و مرتب می‌شوند. برای مثال عبارت «متن ۱۰» از نظر اکسل عبارت کوچکتری نسبت به «متن ۱» است، زیرا صفر درست در مکانی قرار گرفته که در عبارت دوم عدد ۱ دیده می‌شود و چون ۰ کوچکتر از ۱ است، «متن ۱۰» در لیستی که به صورت صعودی مرتب شده باشد، زودتر دیده خواهد شد.

مرتب سازی در اکسل بر اساس چند ستون با حفظ اولویت

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

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

کلیک کنید

این کار را در نسخه‌های جدید اکسل می‌توان تا ۲۵۵ ملاک یا اولویت انجام داد. در تصویر زیر نحوه دسترسی به دستور مرتب سازی براساس اولویت مشخص شده است. همانطور که مشخص شده، با فعال سازی یکی از سلول‌های جدول اطلاعاتی از برگه Data دکمه Sort را در بخش Sort & Filter انتخاب کنید.

با انجام این کار یک پنجره جدید ظاهر خواهد شد. در این پنجره، ستون یا ملاک اول را در سطر اول (Sort by) و ملاک یا ستون‌های بعدی را براساس اولویت در سطرهای بعدی کادر Then by مشخص کنید. اگر می‌خواهید اولویت‌ها را جابجا کنید از دکمه‌های Δ یا ∇ استفاده کرده و برای حذف هر یک از ملاک‌ها نیز دکمه Delete Level را در این پنجره انتخاب کنید.

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

نکته: ممکن است سطر اول جدول اطلاعاتی یا کاربرگ اکسل، شامل اسامی ستون‌ها باشد. در این حالت این سطر نباید هنگام مرتب‌سازی جابجا شود. برای مشخص کردن این امر در اکسل کافی است در پنجره مربوط به دستور مرتب‌سازی (Sort) گزینه My list has headers را فعال کنید تا اکسل متوجه شود از سطر اول به عنوان مقادیر اطلاعاتی استفاده نکند. این کار همچنین باعث می‌شود که اسامی ستون‌ها از سطر اول برداشت شده و در قسمت کادر Columns ظاهر شوند.

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

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

• بخش Column محلی است که یک لیست از ستون یا ملاک مرتب سازی قابل استفاده در ناحیه اطلاعاتی را مشخص می‌کند. به این موضوع توجه داشته باشید که اگر گزینه My data has header را انتخاب کرده باشید، مقادیر مربوط به سطر اول ناحیه اطلاعاتی به عنوان اسامی ستون‌ها در این قسمت دیده می‌شود. در غیر اینصورت نام ستون‌های کاربرگ اکسل (A , B ...) در این لیست ظاهر خواهند شد. در تصویر ۳، نمونه‌ای از اقلامی که در این بخش ظاهر می‌شود با توجه به کاربرگ پس زمینه، دیده می‌شود.

• بخش Sort on به نوع قلم اطلاعاتی اشاره دارد که باید براساس آن جدول مرتب شود. اگر می‌خواهید مرتب سازی را براساس مقادیر سلول مربوط به ملاک مرتب‌سازی، نظم دهید، گزینه value را انتخاب کنید. توجه داشته باشید که رنگ (Font Color - Cell Color) و نماد (Cell Icon) نیز می‌تواند برای مرتب سازی در اکسل به کار رود. در قسمت مرتب سازی سفارشی به این نحوه نظم دهی داده‌ها خواهیم پرداخت.

• قسمت Order، مرتب‌سازی را برحسب صعودی یا نزولی و حتی سفارشی تعیین می‌کند. اگر مقادیر مربوط به ملاک مرتب‌سازی، متنی باشند گزینه‌های A to Z یا Z to A در این بخش قرار می‌گیرند. ولی برای مقادیر عددی انتخاب‌های Smallest to Largest یا Largest to Smallest برای نمایش مرتب سازی صعودی یا نزولی ظاهر خواهند شد.

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

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

نکته: مرتب سازی با چند ملاک (با حفظ اولویت) را می‌توان به کمک فیلتر هم اجرا کرد. بعد از این که جدول را فیلترگذاری کردید، با باز کردن لیست فیلتر مربوط به هر یک از ستون‌ها، گزینه‌های مرتب سازی ظاهر می‌شوند. البته توجه داشته باشید که این مرتب سازی با توجه به ترتیب به کارگیری فیلترها اولویت بندی می‌شود.

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

مرتب سازی سفارشی

خوشبختانه در نسخه‌های جدید اکسل، نه تنها می‌توان از مرتب‌سازی برحسب محتویات عددی و متنی و قواعد ترتیب حروف و اعداد استفاده کرد، بلکه مرتب سازی سفارشی، برحسب لیست‌های مرتب شده یا لیست دلخواه (Custom List)، رنگ سلول (Cell Color) یا نمادهای (Cell Icons) به کار رفته در سلول‌ها نیز امکان‌پذیر است. در ادامه به هر یک از این موارد خواهیم پرداخت.

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

مرتب سازی در اکسل براساس لیست دلخواه

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

نکته: برای انتخاب اقلام مربوط به لیست سفارشی (Custom List) بهتر است با ورود به تنظیم لیست‌های سفارشی، اقلام مرتب شده خود را معرفی کرده تا در قسمت Order ترتیب صعودی یا نزولی آن را مشاهده کنید.

مرتب سازی براساس رنگ سلول‌ها

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

به این کار، رنگ‌های سلول نیز می‌تواند مبنایی برای مرتب‌سازی یا دسته‌بندی کردن سلول‌ها توسط دستور Sort باشد. کافی است مطابق با تصویر 6 دستور Cell color را انتخاب کرده و رنگی که ملاک دسته‌بندی سطرهاست را انتخاب کنید. به این موضوع نیز توجه داشته باشید که فقط یک رنگ را می‌توانید برای مرتب‌سازی انتخاب کنید. در حقیقت این نحوه مرتب سازی، سطرها را به دو دسته تقسیم می‌کند. دسته اول، سطرهایی است که با رنگ مورد نظر شما مطابقت دارد و دسته دوم نیز سطرهایی است که به رنگ دلخواه شما نیستند.

مرتب سازی براساس نماد سلول‌ها

هنگامی که سلول‌ها را با استفاده از «قالب‌بندی شرطی» (Conditional Formatting)، نمادگذاری می‌کنید، می‌توانید این نمادها (Icons)‌ را مبنای مرتب سازی قرار دهید. به این معنی که تمامی مقادیری که دارای نماد یکسان هستند در یک گروه و در کنار یکدیگر در لیست یا جدول مرتب شده، ظاهر می‌شوند.

برای آنکه در مورد نحوه استفاده از قالب‌بندی شرطی و ایجاد نماد در سلول‌های کاربرگ اطلاعاتی اکسل بیشتر بدانید متن چگونه با «Conditional Formatting» سطرها را در اکسل برجسته کنیم؟ — آموزش گام‌ به‌ گام را مطالعه کنید. در تصویر زیر نتیجه به کارگیری نمادها برای مرتب سازی مشخص شده است. در تصویر ۱۰، مقداری که نسبت به میانگین دور یا نزدیک هستند با دایره‌های رنگی مشخص شده‌اند. آن‌هایی که کمتر از میانگین باشند با دایره‌هایی با رنگ قرمز و مقادیر نزدیک به میانگین با دایره‌های زرد رنگ و اعدادی که از میانگین بزرگتر باشند، با دایره سبز رنگ مشخص شده‌اند. به این ترتیب مرتب سازی را براساس این گونه نمادها می‌توان انجام داد.

مرتب سازی به صورت سطری

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

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

کلیک کنید

برای انجام این کار در پنجره تنظیمات مرتب‌سازی (Sort Options) که در تصویر زیر دیده می‌شود، گزینه Sort left to right را فعال کنید. به این ترتیب، به جای ستون‌ها، سطرها در بخش مرتب‌سازی دیده می‌شوند. ملاک مرتب‌سازی را در این بخش یک با چند سطر از جدول اطلاعاتی انتخاب کنید. با فشردن دکمه Sort، مرتب‌سازی جدول به شکل سطری (از چپ به راست -Sort left to right) صورت خواهد گرفت.

خلاصه و جمع‌بندی

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

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