مرتب کردن سریع داده ها با استفاده از دکمه های Sort
ساده ترین و البته سریعترین راهی که برای مرتب سازی داده ها در اکسل وجود دارد، استفاده از دکمه های sort است که با دستورهای A-Z و Z-A در تب data قرار دارند. اما توجه داشته باشید که فقط زمانی از این دکمه ها استفاده کنید که ردیف یا ستون خالی در بین داده های شما وجود نداشته باشد. برای مرتب سازی داده ها در اکسل با استفاده از دکمه های sort به شیوه زیر عمل کنید:
- یکی از سلول های ستونی که میخواهید مرتب کنید را انتخاب کنید.
- در تب data اکسل، برای مرتب سازی از کوچک به بزرگ، گزینه A-Z و برای مرتب سازی از بزرگ به کوچک گزینه Z-A را انتخاب کنید.
- قبل از اینکه هر کار دیگری انجام دهید، بررسی کنید که عملیات مرتب سازی به درستی انجام شده باشد. در صورتی که داده ها به درستی مرتب نشده اند، گزینه UNDO در تولبار را کلیک کنید.
مشکلات و معایب مرتب سازی داده ها در اکسل
همانطور که گفتیم استفاده از دکمه های A-Z و Z-A اکسل اگرچه مرتب سازی را به سرعت انجام میدهند ولی دارای معایب و مشکلاتی نیز هستند.
در صورتی که ردیف یا ستون خالی در بین داده های شما وجود داشته باشد، ممکن است بخشی از داده ها سورت شوند و بخشی دیگر در نظر گرفته نشود. برای مثال ممکن است شما در نهایت لیستی از شماره تلفن ها را داشته باشید که هیچ تطابقی با اسامی شما ندارند یا آدرس یک مشتری در کنار همان مشتری قرار نگیرد!!
برای رفع این مشکل در زمانی که داده های خود را بر اساس یک ستون مرتب سازی میکنید، به شیوه زیر عمل کنید:
- یکی از سلول ها را در ستونی که میخواهید مرتب کنید انتخاب کنید.
- Ctrl + A را فشار دهید تا کل محدوده انتخاب شود.
- محدوده انتخاب شده را بررسی کنید و مطمئن شوید که تمام داده ها انتخاب شده اند. برای مثال در تصویر زیر، ستون E مخفی شده است در نتیجه داده های موجود در ستون های سمت راست، انتخاب نشده اند.
- در صورتی که تمام داده ها انتخاب نشده اند، مشکل خالی بودن ستون یا ردیف را رفع کنید و یا از دیالوگ باکس Sort که در قسمت بعد توضیح داده خواهد شد استفاده کنید.
- در تب data اکسل، برای مرتب سازی از کوچک به بزرگ، گزینه A-Z و برای مرتب سازی از بزرگ به کوچک گزینه Z-A را انتخاب کنید.
مرتب سازی بر اساس دو یا چند ستون
در صورتی که میخواهید دو یا چند ستون را در اکسل مرتب کنید، پیشنهاد میکنیم از پنجره Sort استفاده کنید. در این مثال، جدولی که قصد مرتب کردن آن را داریم شامل اطلاعات فردی است و بر اساس زیر مرتب سازی خواهد شد:
- اول بر اساس جنسیت (Gender)
- دوم بر اساس ایالت جغرافیایی (State)
- در نهایت بر اساس سال تولد (Birth year)
برای مرتب کردن داده ها بر اساس سه ستون به شیوه زیر عمل کنید:
- تمام سلول ها را انتخاب کنید.
- در تب data اکسل و در گروه Sort & Filter دکمه Sort را انتخاب کنید.
- دکمه Add Level را انتخاب کنید تا اولین سطح از مرتب سازی ایجاد شود
- شبیه به شکل زیر و از قسمت sort by اولین ستونی که میخواهید دسته بندی بر اساس آن انجام شود را انتخاب کند. در این مثال ستون Gender را انتخاب میکنیم.
نکته: در صورتی که عناوین ستون در لیست ظاهر شده به درستی نمایش داده نمیشود، تیک گزینه my data has headers را فعال کنید.
- از لیست کشویی Sort on، گزینه ی مورد نیاز خود را انتخاب کنید. ما داده ها خود را بر اساس مقدار آنها مرتب خواهیم کرد در نتیجه گزینه value را انتخاب میکنیم.
- در قسمت Order یکی از گزینه ها را انتخاب کنید.( بسته به نیاز خود)
- حال برای اضافه کردن یک سطح دیگر مرتب سازی یکبار دیگر گزینه Add level را انتخاب میکنیم و به ترتیب داده ها را بر اساس ستون های State و birth year نیز مرتب میکنیم.
- پس از تنظیم تمام سطوح مرتب سازی، گزینه ok را انتخاب کنید.
حال داده های شما بر اساس ستون های مشخص شده و به ترتیب انتخاب سطوح مرتب سازی، سورت شده اند:
- ستون Gender در مرحله اول مبنای مرتب سازی قرار گرفته است، در نتیجه تمام نام های با جنسیت female (مونث) در ابتدا قرار گرفته اند.
- سپس، داده ها بر اساس ایالت دسته بندی شده اند، در نتیجه جنسیت های مونث ایالت آلاسکا و سپس جنسیت مونث ایالت آلاباما و … در بالای لیست قرار گرفته اند.
- در نهایت و در سطح سوم ، سال تولد مبنا قرار گرفته است.
مرتب سازی داده ها در اکسل به روش Custom sort
زمانی که به روش های معمول داده های خود را مرتب سازی میکنید، میتوانید ترتیب داده ها را به صورت بزرگتر به کوچکتر، الف به ی و … انتخاب کنید. علاوه بر این روش های استاندارد، شما میتوانید ترتیب های دیگری مثل روزهای هفته یا ماه و … را نیز مبنای مرتب سزای خود قرار دهید. در مثال زیر ما عملیات دسته بندی را بر اساس نام روزهای هفته انجام خواهیم داد.
برای انجام مرتب سازی بر اساس یک لیست Custom میتوانید به شیوه زیر عمل کنید:
- یکی از سلول های ستونی را که میخواهید مرتب سازی کنید را انتخاب کنید.
- کلیدهای ترکیبی Ctrl + A را انتخاب کنید تا کل محدوده انتخاب شود.
- محدوده انتخاب شده را بررسی کنید تا مطمئن شوید که تمام سلول ها انتخاب شده اند.
- در تب Home اکسل، در گروه editing، گزینه sort&filter و سپس Custom sort را انتخاب کنید.
- در پنجره ظاهر شده، در ستون Sort by ستون day را انتخاب کنید.
- در قسمت order، گزینه custom list را انتخاب کنید.
- در پنجره Custom، یکی از لیست های Custom(بسته به نیاز خود) را انتخاب کنید و سپس ok را دوبار کلیک کنید تا پنجره بسته شود.
همانطور که مشاهده میکنید ستون day بر اساس روزهای هفته به جای حروف الفبا، دسته بندی شده است.
مرتب سازی یک ردیف در اکسل
اگرچه معمولا مرتب سازی داده ها به دلیلی ساختار جداول، بر اساس ستون انجام میگیرد اما شما میتوانید داده های خود را بر اساس ردیف نیز مرتب کنید. در این مثال، جدولی از فروش ماهانه را مرتب خواهیم کرد. در نتیجه این عملیات ماه با بیشترین فروش کل در سمت چپ قرار خواهد گرفت. برای انجام این کار از منو راست کلیک استفاده میکنیم.
برای سورت کردن داده ها بر اساس ردیف در اکسل، به صورت زیر عمل کنید:
- یکی از سلول ها را در ردیفی که میخواهید مرتب سازی کنید انتخاب کنید.
- کلیدهای Ctrl+A را فشار دهید تا کل محدوده هایلایت شود.
- محدوده انتخاب شده را بررسی کنید تا مطمئن شوید تمام سلول های مورد نظر هایلایت شده است.
- در ردیفی که میخواهید سورت کنید، راست کلیک کنید و از منو ظاهر شده گزینه Sort و سپس Custom sort را انتخاب کنید.
- در باکس Sort by پنجره Sort ستون Day را انتخاب کنید.
- در لیست کشویی Order گزینه Custom list را کلیک کنید.
- در بالای پنجره Sort گزینه Option را انتخاب کنید.
- در پنجره Options، در قسمت orientation گزینه Sort left to right را انتخاب کنید.
- Ok را کلیک کنید تا پنجره Options بسته شود
- از لیست Sort by، ردیفی را که میخواهید مرتب سازی کنید. از آنجایی که ردیفها عنوان ندارند، شماره ردیف مورد نظر را انتخاب کنید.
- گزینه مورد نظر در قسمت Sort on و دیگر قسمت ها را انتخاب کنید و ok را بزنید.
همانطور که مشاهده میکنید، داده ها در ردیف مورد نظر بر اساس مقدار مرتب سازی شده است.
مرتب کردن داده ها بر اساس آیکن سلول (Cell icon)
زمانی که جدول اکسل ایجاد میکنید، یا یک فیلتر اتوماتیک بر روی یک لیست اعمال میکنید، بر روی هرکدام از سلول های عنوان، یک فلش ایجاد میشود. با کلیک بر روی این فلش مجموعه ای از گزینه های دسته بندی و فیلترینگ ظاهر میشود که میتوانید از هرکدام از آنها استفاده کنید.
در صورتی که شما آیکن های فرمت دهی شرطی را به یکی از ستون ها اعمال کنید، شما میتوانید عملیات مرتب سازی را بر اساس این آیکن ها نیز اعمال کنید. در تصویر زیر، به ستون Quantity آیکن های چراغ راهنما اضافه شده است.
پس از اضافه شدن آیکن چراغ راهنما، میتوانید به روش زیر عملیات مرتب سازی بر اساس آیکن را انجام دهید:
- بر روی سلولی که حاوی آیکنی است که میخواهید در بالای لیست قرار گیرد، راست کلیک کنید.
- در منو راست کلیک، گزینه Sort را انتخاب کنید.
- شبیه به تصویر زیر گزینه Put Selected Cell Icon On Top را انتخاب کنید.
مقادیر با آیکن انتخاب شده در بالای لیست قرار گرفتند ولی دیگر آیتم ها مرتب نشده باقی ماندند.
مرتب سازی دادها در اکسل بر اساس رنگ
یکی دیگر از روش های سورت کردن داده ها در اکسل، مبنا قرار دادن رنگ سلول ها و انجام مرتب سازی بر اساس رنگ است. این روش نیز تفاوت زیادی با روش های گفته شده ندارد که در این قسمت با استفاده از یک مثال توضیح خواهیم داد.
- در یکی از سلول های مجموعه داده خود کلیک کنید.
- در تب DATA، و گروه Sort & Filter گزینه Sort را انتخاب کنید.
- ستونی که میخواهید مرتب سازی بر اساس آن صورت گیرد را در گروه Sort by و گزینه Cell color را در قسمت Sort on مشخص کنید.
- رنگ مورد نظر را از لیست Order انتخاب کنید.
- دوبار گزینه Copy level را کلیک کنید و رنگ های دیگر را انتخاب کنید.
- OK را بزنید.
نتایج به شکل زیر نمایش داده خواهد شد.
مرتب سازی داده ها بر اساس لیست کشویی عنوان
پس از اضافه کردن آیکن های سلول، میتوانید مرتب سازی بر اساس آیکن را از طریق لیست کشویی موجود در عنوان ستون نیز انجام دهید.
من یک فایل دارم که شامل اسامی رانندگان و مقدار بار حملی انها است. مقذار باری که هر راننده حمل میکند را میخواهم بدست بیاورم منتهی ممکن است درلیست اسامی رانندگان، اسم یک راننده دو بار امده باشد و میخواهم که مجموع بارهای حملی اون راننده هم در یک جدول دیگر در زیر همین جدول نشان دهد.
از پیوت تیبل استفاده کنید.
آموزش ساخت پیوت تیبل در اکسل
سلام خسته نباشید. من میخواستم ی سری داده رو ک داخل ورد دارم بیارمش تو اکسل بدون اینکه مجبور باشم هر قسمتش رو تایپ کنم و تو اکسل خودش جدول بندی بشه ممکنه ؟
سلام
ممکنه. ولی روش انجامش بستگی داره دقیقا منظورتون از جدول بندی شدن و … چی هست.
سلام من میخوام زمانی که اکسلی که درست کردم بر اساس رنگ SORTکنم و بعد از این کار اگر سر و ستون جدیدی ایجاد کردم و رنگ دادم اتوماتیک خودش بره زیر رنگ مورد نظر قرار بگیره چطور اینکار را کنم؟؟
به صورت کلی وقتی بخواید یک فرایند رو در اکسل اتوماتیک کنید باید از vba و ماکرونویسی کمک بگیرید. اینکه میخواید اگر ستون جدید اضافه کردید توی اون هم این فرایند رو اعمال کنه یک مقدار پیچیده هست. فعلا مرتب سازی توی یک ستون رو انجام بدید و بعد از اینکه به ماکرونویسی مسلط شدید می تونید کارهای بیشتری انجام بدید. اگر با وی بی آی و ماکرونویسی اشنایی ندارید فیلم های آموزش زیر رو ببینید: دوره رایگان ماکرونویسی در اکسل برای مرتب کردن رنگ ها توی یک ستون اول یک ماکرو ضبط کنید و یک Shortkey بهش بدید. (مثلا… مطالعه بیشتر »
سلام…یک سری اعداد درهم به صورت ستونی داریم..میخواهم این اعداد را صرف نظر از بزرک یا کوچک بودن فقط از پایین به بالا ردیف کنم…یعنی اینورت کنم از پایین به بالا..جطوراینکاررا انجام بدهم؟..ممنون
سلام
یک ستون از اعداد 1 تا ردیفی که دارید تولید کنید. برای این کار دو دوتا سلول پشت سر هم اعداد 1 و 2 رو بنویسید و هر دو سلول انتخاب کنید. کنار باز انتخابی یک مربع سبز کوچگ نشون میده، روی اون دوبار کلیک کنید تا برای همه سلول ها عدد تولید بشه. حالا این ستون جدید رو مرتب کنید (از بزرگ به کوچک) تا دیتا مورد نظر برعکس بشه.
متشکرم
با سلام فرضا بنده چند فروشگاه دارم و با استفاده از کد ملی به افراد روغن داده ام و هر کدام از فروشگاه های من یک ستون با تعدادی کد ملی به من داده حال بنده این ستونها را داخل یک فایل اکسل البته در ستونهای متفاوت میگذارم و میخواهم که کد ملی های هر ستون که مشابهی در ستون بعدی دارند در یک ردیف روبروی هم قرار گیرند تا متوجه شوم کدام خریدار توانسته از کدام فروشگاه های من مجددا روغن بگیرد لطفا مرا راهنمایی فرمایید البته بنده همه جوره سورت کردم ولی آنچه منظور من بود انجام نشد با… مطالعه بیشتر »
سلام این مسئله رو میشه با چندین روش انجام داد ولی یکی از ساده ترین روش به شرح زیر هست: مرحله اول: باید در کنار کد ملی ها یک ستون درست کنید که اسم فروشگاه باشه. یعنی مشخص باشه هر کد ملی برای چه فروشگاهی هست. مرحله دوم: همه کد ملی ها رو فروشگاه ها رو زیر هم قرار بدید. مرحله سوم: در ستون سوم با استفاده از تابع countif برای هر سطر تعداد کد ملی ها رو شمارش کنید. (یعنی کد ملی که در سطر دوم هست چند بار در کل فروشگاه ها اومده) مرحله چهارم: ستون کد ملی… مطالعه بیشتر »
سلام دستور پیداکردن اسامی تکراری وله ترتیب کردن براساس حروف الفبا
سلام وقت بخیر.ممنون از اطلاعات مفیدتون.من در جدول اکسل یه سری داده وارد کردم که اشتباها الان بصورت حروف الفبا مرتب شده.من میخوام برش گردونم به حالت اولیه ای که ثبت زده بودم.چطور میشه اینکارو کرد؟
سلام
اگر فایل ذخیره کردید و بستید نمیشه کاری کرد، ولی در این موقع می تونید ctrl+z بزنید تا به مرحله قبل از مرتب سازی برگردید و بعد فایل ببندید.
سلام ستونی که هم اعداد داره هم کلمه رو چجوری سورت کنیم؟
منظورتون از چطور سورت کنیم مشخص کنید.
سلام از چند قسمت گزارش میگیرم با فونت و اندازه مختلف با شکل جارو میزنم یکسان میکنم ولی تاریخ سورت نمیشه
سلام
گزینه format painter فقط شکل ظاهری رو تغییر میده. در صورتی که برای مرتب سازی مقدار سلول مهم هست نه ظاهرش. احتمالا تاریخ ها رو درست وارد نکردید و مثلا اسپس قبل یا بعدش داره یا ….
خسته نباشید ، من میخوام وقتی یه ستون رو سورت کنم در صورت تکراری بودن یه خونه اون رو با رنگ متمایز نشون بده ، راهنمایی میکنید . تشکر
اگر منظورتون این هست که مثلا اگر مقدار اول تکراری بود آبی بشه و اگر یک مقدار دیگه تکراری بود قرمز بشه و … با VBA قابل انجام هست.