همه چیز درباره مرتب کردن (Sort) داده‌ها در اکسل

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

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

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

• نحوه‌ی مرتب کردن الفبایی در اکسل
  • سریع‌ترین روش برای مرتب‌سازی الفبایی در اکسل
  • مرتب‌سازی الفبایی و نگه داشتن سطرها در کنار هم
  • مرتب کردن الفبایی و فیلتر کردن
  • مرتب کردن چند ستون به ترتیب حروف الفبا
  • مرتب‌سازی سطرها به صورت حروف الفبا
  • مشکلات مرتب کردن الفبایی در اکسل
• مرتب کردن بر اساس رنگ سلول در اکسل
• مرتب کردن سلول‌ها بر اساس رنگ فونت در اکسل
• مرتب کردن بر اساس آیکن‌های سلول
• نحوه‌ی فیلتر کردن سلول‌ها بر اساس رنگ در اکسل
• مرتب‌سازی داده‌ها با ترتیب دلخواه (با استفاده از یک لیست سفارشی)

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

به طور کلی، ۳ روش اصلی برای سورت کردن الفبایی در اکسل وجود دارد: دکمه A-Z یا Z-A، ویژگی Sort و فیلتر. در زیر راهنمایی‌های دقیق در مورد هر روش را پیدا خواهید کرد.

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

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

1. سلولی را در ستون مورد نظر برای مرتب‌سازی انتخاب کنید.
2. در تب Data، در گروه Sort and Filter، برای مرتب‌سازی صعودی بر روی A-Z، و یا برای مرتب‌سازی نزولی بر روی Z-A کلیک کنید. انجام شد!

همین دکمه‌ها از تب Home > گروه Editing > سپس Sort and Filter نیز در دسترس هستند:

در هر صورت، اکسل لیست شما را فوراً به صورت الفبایی سورت می‌کند:

نکته: پس از انجام مرتب‌سازی و قبل از انجام هر کار دیگری، به نتایج دقیق نگاه کنید. اگر چیزی اشتباهی به نظر رسید، روی دکمه‌ی Undo کلیک کنید تا ترتیب اصلی بازیابی شود.

به صورت الفبایی مرتب‌سازی کنید و سطرها را در کنار هم نگه دارید

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

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

در بعضی مواقع، بیشتر وقتی که تنها یک یا چند سلول در وسط مجموعه داده‌های شما انتخاب می‌شوند، اکسل مطمئن نیست که کدام قسمت از داده‌ها را مرتب کند بنابراین از شما دستورالعمل‌ها را سؤال می‌کند. اگر می‌خواهید کل جدول را مرتب کنید، گزینه‌ی پیش‌فرض Expand the selection را باقی بگذارید و بر روی Sort کلیک کنید:

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

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

افزودن فیلتر به جدول آسان است:

1. یک یا چند عنوان ستون را انتخاب کنید.
2. در تب Home، در گروه Editing بر روی Sort and Filter > سپس Filter کلیک کنید.
3. پیکان‌های کشویی کوچک در هر یک از عناوین ستون‌ها ظاهر می‌شوند. در ستونی که می‌خواهید به ترتیب حروف الفبا مرتب کنید، روی پیکان کشویی کلیک کنید و Sort A to Z را انتخاب کنید:

ستون بلافاصله به صورت حروف الفبا مرتب می‌شود و یک فلش کوچک به سمت بالا روی دکمه‌ی فیلتر، ترتیب مرتب‌سازی (صعودی) را نشان می‌دهد:

برای معکوس کردن ترتیب، از فهرست کشویی فیلتر Sort Z to A را انتخاب کنید.

برای حذف فیلتر کافیست دوباره روی دکمه Filter کلیک کنید.

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

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

به عنوان نمونه، بیایید یک ستون دیگر را به جدول خود اضافه کنیم، و سپس نوشته‌ها را به ترتیب حروف الفبا ابتدا بر اساس Region و سپس بر اساس Name ترتیب دهید:

برای انجام این کار، مراحل زیر را انجام دهید:

1. کل جدول مورد نظر برای سورت کردن را انتخاب کنید.
   در بیشتر موارد، شما فقط می‌توانید یک سلول را انتخاب کنید و اکسل بقیه داده‌های شما را به طور خودکار انتخاب می‌کند، اما این یک روش مستعد خطا است، به ویژه هنگامی که در بین داده‌های شما شکاف‌هایی وجود دارد (سلول‌های خالی).
2. در تب داده‌ها، در گروه Sort & Filter، دکمه‌ی Sort را بزنید.
   
3. کادر محاوره‌ای Sort با اولین سطح مرتب‌سازی ایجاد‌شده‌ی خودکار به همان صورت که اکسل مناسب ببینید نشان داده خواهد شد
   .در جعبه‌ی کشویی Sort بر اساس، ستونی را که می‌خواهید ابتدا به صورت الفبایی مرتب کنید، در مثال ما Region، انتخاب کنید. در دو کادر دیگر، تنظیمات پیش‌فرض را رها کنید: Sort بر روی – Cell values و Order بر روی – A to Z:
   
   نکته: اگر اولین جعبه‌ی کشویی به جای عناوین، حروف ستون را نشان می‌دهد، تیک جعبه‌ی My data has headers را در قسمت بالا و سمت راست بزنید. در صورتی که مجموعه داده‌ی شما دارای عنوان نیست، تیک آن را بردارید.
4. برای افزودن سطح بعدی بر روی دکمه‌ی Add Level کلیک کنید و گزینه‌های ستون دیگر را‌ انتخاب کنید.
   در این مثال، سطح دوم مقادیر موجود در ستون Name را به صورت حروف الفبا از A تا Z مرتب می‌کند:
   
   نکته: اگر مرتب‌سازی بر اساس چندین ستون با یک معیارست، به جای اAdd Leve، بر روی Copy Level کلیک کنید. در این حالت، تنها کافی‌ست در جعبه‌ی اول ستون دیگری را انتخاب کنید.
5. در صورت لزوم سطوح مرتب‌سازی بیشتری اضافه کنید، و OK را کلیک کنید.

اکسل داده‌های شما را به ترتیب مشخص‌شده مرتب می‌کند. همان‌طور که در تصویر زیر نشان داده شده‌است، جدول ما دقیقاً مطابق آن‌چه که می‌خواستیم به صورت الفبایی مرتب شده‌است: ابتدا بر اساس Region و سپس بر اساس Name:

نحوه‌ی مرتب‌سازی سطرها به صورت حروف الفبا در اکسل

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

1. 1. محدوده‌ی مورد نظر برای مرتب کردن را انتخاب کنید. اگر جدول شما دارای برچسب‌های ردیف است که نباید جابجا شوند، آن‌ها را انتخاب نکنید.
   2. به تب Data> گروه Sort and Filter بروید و Sort را کلیک کنید:
      
   3. در کادر محاوره‌ای Sort بر روی Options… کلیک کنید
   4. در کادر محاوره‌ای کوچک Options ظاهرشده، گزینه‌ی Sort left to right را انتخاب کنید و برای بازگشت به Sort بر روی OK کلیک کنید
      

از لیست کشویی Sort by، عدد ردیف مورد نظر برای مرتب‌سازی الفبایی را انتخاب کنید (ردیف 1 در این مثال). در دو جعبه‌ی دیگر، مقادیر پیش‌فرض به خوبی انتخاب شده، بنابراین ما آن‌ها را نگه می‌داریم (Cell Values در جعبه‌ی Sort on، و A to Z در جعبه‌ی Order)، و OK را کلیک کنید:

در نتیجه، اولین ردیف در جدول ما به ترتیب حروف الفبا سورت شده‌است، و بقیه‌ی داده‌ها بر اساس آن ترتیب‌بندی مجدد می‌شوند، با حفظ تمام ارتباطات بین مدخل‌ها:

مشکلات مرتب کردن الفبایی در اکسل

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

ستون‌ها و ردیف‌های خالی یا پنهان

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

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

عناوین ستون غیرقابل تشخیص

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

هنگام استفاده از کادر محاوره‌ای Sort، اطمینان حاصل کنید که چک‌باکس My data has headers انتخاب شده‌است.

مرتب کردن بر اساس رنگ سلول در اکسل

مرتب‌سازی سلول‌های اکسل بر اساس رنگ ساده‌ترین کار در مقایسه با شمارش، جمع‌بندی و حتی فیلتر کردن است. نه کد VBA و نه فرمولی مورد نیاز است. ما به سادگی قصد داریم از ویژگی Custom Sort در کلیه‌ی نسخه‌های مدرن اکسل ۲۰۱۹، ۲۰۱۶، ۲۰۱۳، ۲۰۱۰ و ۲۰۰۷ استفاده کنیم.

1. جدول یا محدوده‌ای از سلول‌هایتان را انتخاب کنید.
2. در تب Home > گروه Editing، روی دکمه‌ی Sort & Filter کلیک کرده و Custom Sort… را انتخاب کنید
   
3. در پنجره‌ی محاوره‌ای Sort، تنظیمات زیر را از چپ به راست مشخص کنید.
   • در Sort by ستونی که می‌خواهید مرتب کنید (ستون Delivery در مثال ما)
   • در Sort on گزینه‌ی Cell Color
   • در Order رنگ سلولی که می‌خواهید در بالا بیاید
   • موقعیت On Top را انتخاب کنید

   

4. بر روی دکمه‌ی Copy Level کلیک کنید تا یک سطح دیگر با همان تنظیمات قبلی اضافه شود. سپس، زیر Order، رنگ اولویت دوم را انتخاب کنید. به همان روش به تعداد رنگ‌های مختلف در جدول خود سطوح دیگر را اضافه کنید.
   
5. OK را کلیک کنید و چک کنید که آیا سطرهای شما به درستی مرتب شده‌اند یا خیر.
   در جدول ما، ترتیب “Past Due” در بالا قرار گرفته، سپس ردیف‌های “Due in” آمده، و در آخر دستورات “Delivered”، دقیقاً همانطور که می‌خواستیم.
   
   نکته: اگر سلول‌های شما با رنگ‌های مختلفی رنگ‌آمیزی شده‌است، لازم نیست برای هر یک از آن‌ها یک قانون قالب‌بندی ایجاد کنید. شما می‌توانید قوانینی را برای آن دسته از رنگ‌هایی ایجاد کنید که برای شما مهم هستند، به عنوان مثال آیتم‌های “Past due” را در مثال بالا مرتب کرده و سایر ردیف‌های دیگر را به ترتیب فعلی باقی می‌گذاریم.

اگر مرتب‌سازی سلول‌ها تنها با یک رنگ چیزی است که شما به دنبال آن هستید، یک راه سریع‌تر نیز وجود دارد. به سادگی بر روی پیکان AutoFilter (فیلتر خودکار) در کنار ستون مورد نظر برای مرتب‌سازی کلیک کنید، از فهرست کشویی Sort by color را انتخاب کنید، و سپس رنگ سلول‌هایی را که می‌خواهید در بالا یا پایین باشد انتخاب کنید. BTW، همچنین می‌توانید از این‌جا به کادر محاوره‌ای “Custom Sort” دسترسی پیدا کنید، همان‌طور که در قسمت سمت راست تصویر زیر مشاهده می‌کنید.

مرتب کردن سلول‌ها بر اساس رنگ فونت در اکسل

در حقیقت، مرتب‌سازی بر اساس رنگ فونت در اکسل کاملاً مشابه با مرتب‌سازی بر اساس رنگ پس‌زمینه است. شما دوباره از ویژگی Custom Sort استفاده می‌کنید (Home > Sort & Filter > Custom Sort…)، اما این بار همان‌طور که در تصویر زیر نشان داده شده‌است، Font Color را در زیر “Sort on” انتخاب کنید.

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

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

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

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

همان‌طور که می‌بینید، سفارش‌های بزرگ با تعداد بیش از 6 با نمادهای قرمز رنگ، سفارشات در اندازه‌ی متوسط دارای آیکون‌های زرد و سفارشات کوچک دارای آیکون‌های سبز هستند. اگر می‌خواهید مهمترین سفارشات در بالای لیست قرار داشته باشد، از همان ویژگی که قبلاً گفته شد، از ویژگی Custom Sort استفاده کنید و مرتب‌سازی بر اساس Cell Icon را انتخاب کنید.

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

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

اگر می‌خواهید ردیف‌ها را در ورک‌شیت خود با استفاده از رنگ‌های یک ستون خاص فیلتر کنید، می‌توانید از گزینه‌ی Filter by Color موجود در اکسل ۲۰۱۰، اکسل ۲۰۱۳، اکسل ۲۰۱۶ و اکسل ۲۰۱۹ استفاده کنید.

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

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

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

مثلاً، ما لیستی از کارهای خانگی هفتگی داریم و می‌خواهیم آنها را طبق روز یا اولویت مرتب کنیم.

1. شما با انتخاب داده‌هایی که می‌خواهید سورت کنید، شروع می‌کنید و سپس کادر محاوره‌ای Sort را دقیقاً مانند زمانی که مرتب‌سازی بر اساس چندین ستون یا نام ستون‌ها انجام شده‌است، باز می‌کنیم (تب Data > دکمه‌ی Sort).
2. در جعبه‌ی Sort by، ستونی را که می‌خواهید مرتب‌سازی کنید انتخاب کنید، در مورد ما این ستون Day است زیرا می‌خواهیم وظایف خود را بر اساس روزهای هفته مرتب‌سازی کنیم. سپس همان‌طور که در تصویر مشاهده می‌شود، Custom List را در زیر Order انتخاب کنید:
3. در کادر محاوره‌ای Custom Lists، لیست مورد نظر را انتخاب کنید. از آنجا که نام‌های اختصاری روز را در ستون‌های روز داریم، لیست سفارشی مربوطه را انتخاب می‌کنیم و OK را کلیک می‌کنیم.
   
   تمام! اکنون ستون وظایف خانگی خود را بر اساس روز هفته مرتب کرده‌ایم:
   
   توجه: اگر می‌خواهید چیزی را در داده‌های خود تغییر دهید، به خاطر داشته باشید که داده‌های جدید یا تغییریافته به صورت خودکار مرتب نمی‌شوند. شما باید بر روی دکمه‌ی Reapply در تب Data ، در گروه Sort & Filter کلیک کنید:

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

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

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

مراحل 1 و 2 را که در بالا توضیح داده شده‌است را انجام دهید، و هنگامی که کادر محاوره‌ای Custom Lists را باز کردید، NEW LIST را در ستون سمت چپ در زیر Custom Lists انتخاب کنید و ورودی‌ها را مستقیماً در جعبه ورودی List در سمت راست تایپ کنید. به یاد داشته باشید که ورودی‌های خود را دقیقاً به همان ترتیبی که می‌خواهید مرتب‌سازی شوند، از بالا به پایین تایپ کنید:

روی Add کلیک کنید و خواهید دید که لیست سفارشی تازه ایجادشده به لیست‌های سفارشی موجود اضافه شده‌است، سپس OK را کلیک کنید:

و در این‌جا وظایف خانه‌ی ما، به صورت مرتب‌شده با اولویت آمده‌است:

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

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

و سرانجام، ما کارهای خانگی هفتگی خود را با بالاترین منطق طبقه‌بندی کرده‌ایم، ابتدا با روز هفته و سپس با اولویت!

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