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

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل، در فصل گذشته به بررسی بخش سوم قالب بندی شرطی (Conditional Formatting) پرداختیم، در این مقاله به آموزش مرتب‌سازی در اکسل می پردازیم.

تقریباً تمامی کاربران مایکروسافت اکسل حداقل یکی دو بار ستون‌هایی از داده را بر اساس الفبا و یا مقادیر عددی مرتب کرده‌اند. بیایید نگاهی به برخی از مثال‌ها بیندازیم تا ببینیم مرتب‌سازی در اکسل 2019 چقدر فوق‌العاده و قدرتمند است.

سؤالات پاسخ داده شده در این فصل:

• چگونه می‌توان داده‌های معاملات فروش را به‌گونه‌ای مرتب کرد تا این معاملات در مرحله اول بر اساس نام فروشندگان، سپس بر اساس محصولات، پس از آن بر اساس کالاهای فروخته شده و دست آخر بر اساس نظم زمانی قدیمی‌ترین معاملات تا جدیدترین آنها مرتب شوند؟
• همواره تمایل داشتم داده‌ها را بر اساس رنگ سلول یا رنگ فونت آنها مرتب کنم. آیا این کار در اکسل 2019 امکان‌پذیر است؟
• از مجموعه آیکون‌های توضیح داده شده در فصل 24 به نام قالب‌بندی شرطی بسیار خوشم می‌آید. آیا می‌توان داده‌ها را بر اساس نوع آیکون‌ها در سلول مرتب کرد؟
• کاربرگی شامل ستونی حاوی ماه‌هایی است که در آنها فروش داشته‌ایم. وقتی که فروش‌ها را بر اساس این ستون مرتب می‌سازم یا ماه آوریل (اولین ماه از لحاظ الفبا) یا ماه اکتبر (آخرین ماه بر اساس الفبا) در بالای ستون قرار می‌گیرند. چگونه می‌توانم فروش‌ها را بر اساس این ستون به شیوه‌ای مرتب کنم که معاملات ماه ژانویه در بالا و پس از آن فوریه قرار گیرد و همین روال ادامه پیدا کند؟
• آیا می‌توان داده‌ها را بدون استفاده از کادر محاوره‌ای Sort مرتب کرد؟
• اغلب اوقات می بایست فهرستی از شهرها را که شرکت در آنها دفتر دارد را تایپ کنم. آیا می‌توانم فهرستی از مشتریان ایجاد نمایم که به من اجازه دهد نام اولین شهر را در لیست تایپ کنم و بعد نشانگر ماوس را به سمت پائین بکشم تا به شکلی خودکار نام باقی شهرها را در سلول‌ها درج کند؟

پاسخ به سؤالات این فصل

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

JAC شرکت کوچک فروشنده لوازم آرایشی است. کاربرگ Makeup در فایل Makeupsorttemp.xlsx در پوشه templates این فصل (تصویر 1-25 را ببینید) حاوی داده‌های محتوی اطلاعات معاملات مالی زیرمی باشد:

• شماره معامله
• نام فروشنده
• تاریخ معامله
• محصول فروخته شده
• تعداد واحد فروخته شده
• دلارهای کسب شده
• مکان معامله

تصویر 1-25 داده‌های مربوط به معاملات مالی پیش از مرتب شدن

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

• معاملات به‌صورت الفبایی بر اساس نام فروشنده مرتب شوند. می‌خواهیم این ترتیب بر اساس نظم A تا Z معمول باشد تا تمامی معاملات مربوط به Ashley در ابتدا و تمامی معاملات مربوط به Zaret در انتهای لیست قرار گیرند.
• معاملات هر شخص بر اساس محصولات مرتب شود؛ بنابراین به دنبال معاملات مربوط به خط چشم Ashley، معاملات مربوط به کرم‌های زیرسازی Ashley ظاهر شده و به همین ترتیب ادامه پیدا کند.
• برای هر فروشنده و محصول، معاملات بر اساس تعداد واحدهای فروخته شده (بر اساس ارقام نزولی) مرتب شود.
• اگر فروشنده‌ای دو یا سه فروش از همان محصول و همان تعداد واحدهای فروش داشته باشد، مبادلات وی بر اساس ترتیب زمانی فروش مرتب شود.

در نسخه اکسل پیش از اکسل 2007، مرتب کردن با بیش از سه معیار کار مشکلی محسوب می‌شد. اکنون اکسل به شما اجازه می‌دهد تا 64 معیار در یک‌روند مرتب‌سازی اعمال نمایید. برای مرتب کردن داده‌های فروش، ابتدا می‌بایست داده‌ها را انتخاب کنیم (محدوده سلول E3:K1894) دوراه ساده برای انتخاب این داده‌ها وجود دارد:

• قراردادن نشانگر ماوس در گوشه سمت چپ بالای داده‌ها (سلول E3) و بعد فشاردادن کلیدهای Ctrl+Shift+Right arrow و به دنبال آن فشردن کلیدهای Ctrl+Shift+ Down arrow.
• قراردادن نشانگر ماوس در هرجایی از محدوده سلول و فشردن کلیدهای Ctrl+Shift+*.

حالا در تب Data در نوار منو، در گروه گزینه‌های Sort&Filter بر روی گزینه Sort کلیک می‌کنیم تا کادر محاوره‌ای Sort همان‌طور که در تصویر 2-25 نشان‌داده‌شده ظاهر شود.

ازآنجاکه ردیف 3 حاوی سرعنوان ستون داده‌ها می‌باشد، گزینه My Data Has Headers(داده‌های من عنوان‌بندی دارند) در قسمت بالای سمت راست کادر محاوره‌ای را تیک می‌زنیم. سپس چهار معیار زیر را به ترتیبی که نشان‌داده‌شده انتخاب می‌کنیم:

ستون نام را به‌گونه‌ای تنظیم می‌کنیم تا مقادیر (منظور محتویات سلول‌ها است) به نظم الفبایی A-Z مرتب شوند. در لیست Sort By در قسمت زیرین بخش Column عبارت Name را انتخاب می‌کنیم، در لیست Sort On گزینه Value را که به طور پیش‌فرض نوشته شده دست‌نخورده می‌گذاریم. فهرست بخش Order به شکل پیش‌فرض بر A-Z قرار داده شده چرا که من در قسمت Sort By نام را انتخاب کرده‌ایم و آن را به حال خود بدون تغییر رها می‌کنیم.

می‌خواهیم ستون کالاها را به نظم و ترتیب الفبای A-Z مرتب کنیم. برای انجام این کار روی گزینه Add Level کلیک می‌کنیم تا یک معیار مرتب‌سازی دیگر (Then by یا سپس این معیار) در زیر معیار قبلی Sort Leve By (مرتب‌سازی بر اساس) قرار دهیم. از لیست Column عبارت Product یا محصول را انتخاب می‌کنیم و در لیست Sort On گزینه Value را دست‌نخورده باقی می‌گذاریم و همین‌طور در لیست Order یا نظم، ترتیب A-Z را به همان شکل باقی می‌گذاریم.

حالا می‌خواهیم ستون واحدها را از بیشترین واحد به فروش رفته تا کمترین واحد مرتب کنیم. برای این کار روی گزینه Add level کلیم کرده و بعد برای سطح معیار جدید از لیست Column گزینه Units را انتخاب می‌کنیم در لیست Sort On گزینه Value را دست‌نخورده باقی گذاشته و بعد در لیست Order گزینه largest To Smallest(بیشترین به کمترین) را انتخاب می‌کنیم.

حالا می‌خواهیم ستون Date را به‌گونه‌ای مرتب کنیم که مقادیر به نظم زمانی از قدیمی‌ترین فروش‌ها تا جدیدترین مرتب شوند. ابتدا یک سطح جدید اضافه می‌کنیم، در لیست Column گزینه Date را انتخاب می‌کنیم و در لیست Order گزینه Oldest to Newest را دست‌نخورده رها می‌کنیم.

حالا این کادر محاوره‌ای شبیه تصویر 3-25 به نظر می‌رسد.

روی دکمه Ok کلیک می‌کنیم تا تنظیمات مرتب‌سازی اعمال شود. نتیجه نهایی مرتب‌سازی ما در تصویر 4-25 نشان‌داده‌شده است.

تصویر 3-25 تنظیم کادر محاوره‌ای Sort برای مثال مرتب‌سازی فروش

تصویر 4-25 داده‌های مرتب شده معاملات فروش

توجه کنید که تمام معاملات اشلی در ابتدا فهرست شده‌اند، در ابتدا فروش‌های خط چشم و به دنبال آن فروش کرم زیرسازی و بقیه وارد شده‌اند. معاملات فروش خط چشم از بیشترین واحد به کمترین واحدهای فروخته شده مرتب شده‌اند. در صورت وجود موارد مشابه و یکسان فروش (ستون واحدها، ردیف شش و هفت را مشاهده کنید) معاملات به ترتیب زمانی مرتب شده‌اند. در تصویر 3-25 می‌بینید که اکسل ابتدا اولین سطح مرتب‌سازی شما (نام) و بعد دومین سطح (محصولات) و به همین روال را مرتب‌سازی می‌کند.

با استفاده از کادر محاوره‌ای Sort می‌توانید به‌سادگی معیارهای مرتب‌سازی را اضافه (Add Level)، آنها را حذف (Delete Level)، تنظیمات مشخص‌کننده سطحی از مرتب‌سازی را کپی (Copy Level) یا مشخص کنید که داده‌های شما در کجا دارای سرعنوان هستند (اگر این گزینه را انتخاب نکنید، گزینه‌های Column با حروف الفبا نام‌گذاری می‌شوند مثل ستون E) با انتخاب گزینه Options می‌توانید فرایند مرتب‌سازی را به بزرگی یا کوچکی حروف حساس کرده و یا حتی داده‌ها را به‌گونه‌ای مرتب کنید که هریک از اقلام داده در ستون متفاوتی فهرست شوند (به‌جای وضعیت معمولی که در آن هریک از اقلام در ردیفی فهرست می‌شوند).

همواره تمایل داشتم داده‌ها را بر اساس رنگ سلول یا رنگ فونت آنها مرتب کنم. آیا این کار در اکسل 2019 امکان‌پذیر است؟

در اکسل 2019، مرتب‌سازی بر اساس یک سلول یا رنگ فونت بسیار ساده است. کاربرگ Makeup را در فایل Makeupsorttemp.xlsx در نظر بگیرید. بسیاری از نام‌ها در ستون F با رنگ‌های متفاوتی مشخص شده‌اند. مثلاً Cici در سلول F620 با رنگ قرمز مشخص شده و Colleen در سلول F833 به رنگ زرد مشخص شده است. فرض کنید که می‌خواهید نام‌هایی که به رنگ سبز هستند در بالا قرار گرفته و به دنبال آنها نام‌های به رنگ زرد و سپس نام‌های قرمز ردیف شده و باقی ردیف‌ها در پایین آنها قرار گیرند. برای مرتب‌سازی ستون Name بر اساس رنگ به‌سادگی محدوده موردنظر برای مرتب‌سازی (E3:K1894) را انتخاب کنید، سپس روی گزینه Sort در تب Data کلیک کنید و سپس روی گزینه Add level کلیک کنید. بعد انتخاب ستون Name بر قسمت Sort On کلیک کرده و گزینه Cell Color را انتخاب کنید (انتخاب گزینه Font Color مرتب‌سازی را بر اساس رنگ فونت انجام می‌دهد) برای اولین سطح، رنگ سبز را از لیست Order انتخاب کنید و تنظیمات On Top را به حال خود رها کنید. سپس دو سطح دیگر اضافه کنید. برای سطح دوم مرتب‌سازی رنگ زرد را انتخاب کنید و برای سطح سوم رنگ قرمز را انتخاب کنید. کادر محاوره‌ای کامل شده در تصویر 5-25 نشان‌داده‌شده است. روی دکمه Ok کلیک کنید تا مرتب‌سازی کامل شود. نتیجه مرتب‌سازی در کاربرگ Colors از فایل Makeupsort.xlsx در پوشه Practice Files همین فصل قرار دارد و در تصویر 6-26 نشان‌داده‌شده است.

تصویر 5-25 تنظیم کادر محاوره‌ای Sort برای مرتب‌سازی بر اساس رنگ

تصویر 6-25 نتیجه مرتب‌سازی بر اساس رنگ

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

برای مرتب‌سازی بر اساس آیکون‌ها به‌سادگی گزینه Cell Icon را از فهرست Sort On در کادر محاوره‌ای Sort انتخاب می‌کنیم. (در تب Data روی گزینه Sort کلیک کنید) سپس در لیست Order آیکونی را که می‌خواهید در بالای اولین سطح باشد را انتخاب می‌کنید و برای هر آیکون که می‌خواهید مرتب شود یک سطح جدید اضافه نمایید.

کاربرگی شامل ستونی حاوی ماه‌هایی است که در آنها فروش داشته‌ایم. وقتی که فروش‌ها را بر اساس این ستون مرتب می‌سازیم یا آوریل (اولین ماه از لحاظ الفبا) یا اکتبر (آخرین ماه بر اساس الفبا) در بالای ستون قرار می‌گیرند. چگونه می‌توان فروش‌ها را بر اساس این ستون به شیوه‌ای مرتب کنیم که معاملات ماه ژانویه در بالا و پس از آن فوریه قرار گیرد و همین روال ادامه پیدا کند؟

کاربرگ Dates در فایل Makeupsorttemp.xlsx حاوی فهرستی از ماه‌ها می‌باشد (تصویر 25-7 را ببینید). می‌خواهیم ماه‌ها را به‌گونه‌ای مرتب کنیم که بر اساس نظم زمانی با ماه ژانویه در آغاز نمایش داده شوند. کار را با انتخاب محدوده D6:D15 آغاز کرده و بعد در تب Data روی گزینه Sort کلیک می‌کنیم، پس از آن در قسمت Sort By عبارت Column D و در قسمت Sort On گزینه Values را انتخاب می‌کنیم و به این شکل ستون D را برای مرتب شدن بر اساس مقادیر آماده می‌کنیم. در بخش Order گزینه Custom List را انتخاب کرده و بعد در کادر محاوره‌ای Custom List گزینه‌ای را که با ماه‌های ژانویه، فوریه، مارس (نزدیک انتهای کادر محاوره‌ای Custom list در سمت چپ) آغاز می‌شود را انتخاب می‌کنیم. توجه کنید که می‌توانید ستون را بر اساس روزهای هفته نیز مرتب کنید. روی دکمه Ok کلیک می‌کنیم تا انتخاب را تمام کرده و به کادر محاوره‌ای Sort بازگردیم. کادر محاوره‌ای تکمیل شده در تصویر 25-8 نشان‌داده‌شده و نتیجه آن نیز در تصویر 25-9 نشان‌داده‌شده است.

تصویر 7-25 ماه‌هایی که باید مرتب شوند.

تصویر 8-25 تنظیم کادر محاوره‌ای Sort برای مرتب کردن بر اساس ماه

تصویر 9-25 ماه‌های مرتب شده بر اساس ترتیب زمانی

توجه کنید که می‌توان در کادر محاوره‌ای Custom لیست مرتب‌سازی دلخواه را ایجاد کنید. برای انجام این کار ابتدا گزینه New List را انتخاب می‌کنیم. سپس در قسمت List Entries (مدخل لیست‌ها) مداخل را به نظمی که می‌خواهید مرتب شوند تایپ کنید و بعد روی گزینه Add یا اضافه‌کردن کلیک نمایید. حالا می‌بینید که لیست جدید به‌عنوان یک انتخاب به منو اضافه شده است. مثلاً اگر اسامی Jack، Jhon و Alan را در بخش List Entries اضافه کنید (در خطوط متفاوت و جدا شده توسط کاما) تمامی مداخل با اسم Jack در ابتدا لیست شده و پس از آن لیست‌های مربوط به Jhon و در انتها لیست‌های مربوط به Alan آورده خواهند شد.

آیا می‌توان داده‌ها را بدون استفاده از کادر محاوره‌ای Sort مرتب کرد؟

برخی اوقات مرتب‌سازی داده‌ها بدون استفاده از کادر محاوره‌ای Sort کار مناسب‌تری است. برای تصویرکردن چگونگی انجام این امر، ابتدا فرض کنید که می‌خواهید داده‌های معاملات فروش را در کاربرگ Makeup در فایل Makeupsort.xlsx به‌گونه‌ای مرتب کنید که مبادلات ابتدا بر اساس نام فروشنده و بعد بر اساس محصول و بعد بر اساس واحدهای فروخته شده و بالاخره بر اساس نظم زمانی از قدیمی‌ترین تا آخرین معاملات مرتب شوند. برای شروع، در ابتدا کم‌اهمیت‌ترین ستون را برای مرتب‌سازی انتخاب می‌کنیم که ستون تاریخ است (G3:G1894). سپس در گروه گزینه‌های Sort&Filter در تب Data روی گزینه Sort A-Z کلیک می‌کنیم (این گزینه را در قسمت بالای سمت چپ تصویر 10-25 ببینید) و در کادر محاوره‌ای Sort Warning گزینه Expand The Selection را انتخاب می‌کنیم و روی دکمه Sort کلیک می‌کنیم تا تمامی ستون‌ها مرتب شوند. دکمه Sort A To Z داده‌های عددی را به‌گونه‌ای مرتب می‌کنند که کمترین ارقام یا قدیمی‌ترین تاریخ‌ها در بالا قرار گیرند و یا متون را چنان مرتب می‌کنند که متونی که با حرف A شروع می‌شوند بر متون شروع شده با حرف B تفوق داشته باشند و به همین روال ادامه پیدا می‌کنند.

تصویر 10-25 دکمه‌های Sort و Filter

همان گونه که انتظار داشتیم، دکمه Sort Z To A داده‌های عددی را از بزرگ‌ترین عدد یا آخرین تاریخ در بالای ستون مرتب می‌کند و همچنین داده‌های متنی را به‌گونه‌ای مرتب می‌کند که حرف Z پیش از حرف Y بیاید.

حالا ستون‌ها را بر اساس دومین ستونی که دارای اهمیت کمتری است (ستون واحدها) با انتخاب گزینه Z To A مرتب می‌کنیم، چرا که می‌خواهیم فروش‌های بیشتر در بالا قرار گیرند. سپس آنها را بر اساس محصول با انتخاب گزینه A To Z مرتب می‌کنیم و دست آخر بر اساس فروشنده و با انتخاب گزینه A To Z مرتب می‌کنیم. این قدم‌ها همان نتایج نشان‌داده‌شده در تصویر 4-25 را به ما می‌دهد.

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

فرض کنید شرکت شما دفاتر تجاری ای در شهرهای نشان‌داده‌شده در تصویر 11-25 دارد (فایلی به نام Listtemp.xlsx را از پوشه Templates این فصل مشاهده کنید)

تصویر 11-25 فهرست شهرهایی که در ایجاد لیست دلخواه مورداستفاده قرار گرفته.

محدوده G6:G13 حاوی فهرستی از نام شهرهایی است که قرار است برای یک لیست دلخواه از آنها استفاده شود.

برای ایجاد یک لیست دلخواه (Custom List) به ترتیب زیر عمل کنید:

• روی تب فایل در نوارابزار کلیک کنید، بر گزینه Options در انتهای منوی سمت چپ کلیک کرده و سپس بر گزینه Advanced در سمت چپ کادر محاوره‌ای Excel Options کلیک کنید. به پایین کادر رفته و در انتهای بخش General روی دکمه Edit Custom List کلیک کنید.
• کادر محاوره‌ای Custom Lists را مشابه تصویر 12-25 می‌بینید. روی کادر Import List From Cells (نزدیک پایین کادر) کلیک کنید، محدوده G6:G13 را که حاوی لیست می‌باشد را انتخاب کرده و سپس روی گزینه Import کلیک کنید. می‌بینید که لیست شهرها به مجموعه از پیش تعیین شده لیست‌های دلخواه در سمت چپ اضافه می‌شود. حالا روی OK کلیک کنید تا کادر محاوره‌ای Custom List بسته شود و دوباره روی OK کلیک کنید تا کادر محاوره‌ای Excel Options نیز بسته شود.