چگونه اکسل اعداد را به درستی مرتب نکنیم

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

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

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

نحوه بررسی تعداد کاراکترها در هر سلول

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

یک راه سریع و آسان برای بررسی وجود کاراکترهای اضافی در کنار اعداد با استفاده از عملکرد LEN وجود دارد.

برای بررسی تعداد کاراکترها در هر سلول با استفاده از تابع LEN:

1. روی یک سلول خالی در همان ردیف با اولین سلول داده ای که می خواهید بررسی کنید کلیک کنید.
2. تایپ کنید =LEN(
   
   
3. اولین سلول در داده های خود را انتخاب کنید.
   
4. تایپ کنید ) و فشار دهید وارد.
   
5. فرمول تعداد کاراکترهای سلول شما را محاسبه می کند.
6. برای اعمال فرمول بر روی تمام داده‌های خود، روی دستگیره کشیدن در گوشه سمت راست پایین سلول کلیک کرده و نگه دارید.
   
7. به پایین داده های خود بکشید.
   
8. مکان نما را رها کنید و فرمول به طور خودکار پر می شود.
9. هر مقدار را با تعداد ارقامی که در سلول مربوطه می بینید مقایسه کنید.
10. اگر نتیجه فرمول شما بیشتر از تعداد ارقام سلول شما باشد، می دانید که کاراکترهای اضافی در سلول وجود دارد که قابل مشاهده نیستند. در مثال زیر، سلول یک رقمی را نشان می دهد، اما LEN به ما می گوید که دو کاراکتر در سلول وجود دارد.
    
11. برای حذف آنها مراحل زیر را دنبال کنید.

حذف کاراکترهای غیر قابل چاپ

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

خوشبختانه، یک تابع اکسل برای انجام این کار دقیقاً طراحی شده است.

برای حذف کاراکترهای غیر قابل چاپ در اکسل:

1. یک سلول خالی در همان ردیف با اولین ورودی داده خود پیدا کنید.
2. تایپ کنید =CLEAN(
   
   
3. روی سلول اول داده های خود کلیک کنید.
   
4. تایپ کنید ) و فشار دهید وارد.
   
5. سلول جدید شما اکنون فقط باید دارای یک عدد باشد و نویسه‌های غیرقابل چاپ حذف شوند.
6. برای اعمال فرمول بر روی تمام سلول های خود، روی دستگیره کشیدن در گوشه سمت راست پایین سلول کلیک کرده و نگه دارید.
   
7. روی تمام سلول هایی که می خواهید فرمول ظاهر شود، به سمت پایین بکشید.
8. اکنون باید نویسه‌های غیرقابل چاپ حذف شوند.
   
9. با استفاده از عملکرد LEN همانطور که در بالا توضیح داده شد می توانید بررسی کنید که کاراکترهای اضافی حذف شده اند.
10. ستون جدید ایجاد شده خود را کپی کنید.
11. در سلول اول داده اصلی کلیک راست کرده و انتخاب کنید چسب ویژه > چسباندن مقادیر.
    
12. اکنون می توانید ستونی را که برای تمیز کردن سلول های خود ایجاد کرده اید حذف کنید.
13. دوباره سعی کنید داده‌های خود را مرتب کنید تا ببینید آیا این مشکل را برطرف کرده است.
14. اگر داده ها هنوز به درستی مرتب نمی شوند، احتمالاً برخی از اعداد شما همچنان به صورت متن قالب بندی شده اند. برای رفع این مشکل بخش زیر را بررسی کنید.

فاصله ها را حذف کنید

عملکرد CLEAN بسیاری از کاراکترهای غیر قابل چاپ را حذف می کند، اما همه آنها را حذف نمی کند. این تابع فقط 32 کاراکتر غیر قابل چاپ اول را در کد ASCII (0-31) حذف می کند. اگر نویسه غیر قابل چاپ در سلول شما خارج از این محدوده باشد، CLEAN آن را حذف نمی کند.

متأسفانه، هر فضای خالی که رایج ترین کاراکتر غیرچاپی در صفحات گسترده اکسل است، کد ASCII 32 است و بنابراین با CLEAN حذف نمی شود. برای خلاص شدن از شر فضاهای اضافی، باید از تابع TRIM استفاده کنیم.

برای حذف فاصله از اعداد با استفاده از TRIM:

1. یک سلول خالی در همان ردیف به عنوان شماره اول در لیستی که می‌خواهید مرتب کنید انتخاب کنید.
2. تایپ کنید =TRIM(
   
   
3. سلول بالای داده های خود را انتخاب کنید.
   
4. تایپ کنید ) و فشار دهید وارد.
   
5. اکنون هر فضای خالی باید از سلول حذف شود.
6. با کلیک کردن و نگه داشتن دستگیره کشیدن در گوشه سمت راست پایین سلول، فرمول را به سلول های باقیمانده اعمال کنید.
   
7. روی تمام سلول هایی که می خواهید فرمول را در آنها کپی کنید، به سمت پایین بکشید.
8. همه سلول‌های شما اکنون باید فاصله‌ها را حذف می‌کردند.
   
9. این سلول های جدید را کپی کنید.
10. در سلول بالای داده اصلی خود کلیک راست کرده و انتخاب کنید Paste Special > Paste Values.
    
11. می توانید ستون جدید خود را حذف کنید زیرا دیگر به آن نیاز ندارید.
12. دوباره سعی کنید داده های خود را مرتب کنید. اگر باز هم کار نمی کند، ممکن است برخی از سلول ها به صورت متن قالب بندی شوند، بنابراین باید مراحل زیر را امتحان کنید.

اطمینان حاصل کنید که سلول ها به عنوان متن قالب بندی نشده اند

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

ابتدا باید سلول ها را به عدد تبدیل کنید.

برای تبدیل سلول های قالب بندی شده به صورت متن به اعداد:

1. اگر مراحل بالا را دنبال کرده اید و برخی از سلول ها دارای مثلث های سبز کوچک در گوشه سلول ها هستند، این نشان دهنده خطای اکسل است.
   
2. تمام سلول ها و a را برجسته کنید هشدار نماد ظاهر خواهد شد.
   
3. کلیک کنید بر روی هشدار نماد و انتخاب کنید تبدیل به شماره.
   
4. اگر هیچ مثلث سبزی در سلول های خود نمی بینید، تایپ کنید 1 در یک سلول خالی و سپس آن سلول را کپی کنید.
   
5. تمام داده های خود را برجسته کنید، سپس کلیک راست کرده و انتخاب کنید چسب ویژه.
   
6. انتخاب کنید تکثیر کردن و کلیک کنید خوب.
   
7. سلول های شما اکنون باید اعداد باشند.

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

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

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

1. در یک سلول خالی در همان ردیف با مقدار بالای خود کلیک کنید.
2. تایپ کنید =VALUE(TRIM(CLEAN(
   
   
3. سلول بالای داده های خود را انتخاب کنید.
   
4. تایپ کنید ))) و فشار دهید وارد.
   
5. روی دستگیره کشیدن سلول خود کلیک کنید و نگه دارید.
   
6. روی سلول های باقی مانده به پایین بکشید.
7. رها کنید و سلول های دیگر به طور خودکار پر می شوند.
   
8. سلول ها را کپی کنید، سپس روی سلول اول داده های اصلی خود کلیک راست کنید.
9. انتخاب کنید Paste Special > Paste Values.
   
10. سعی کنید سلول های جدید خود را مرتب کنید. آنها اکنون باید طبق انتظار مرتب شوند.

رفع مشکلات رایج اکسل

دانستن اینکه چگونه اکسل را به درستی مرتب سازی نکنید، می تواند ساعت ها شما را از ناامیدی نجات دهد. با این حال، حتی مراحل بالا در همه موارد کار نمی کند. اگر سلول شما حاوی یک نویسه غیر قابل چاپ است که توسط CLEAN حذف نشده است، ممکن است داده های شما هنوز به درستی مرتب نشده باشند. اگر اینطور است و هیچ چیز دیگری کار نمی کند، از LEN برای پیدا کردن سلول های متخلف استفاده کنید و سپس به سادگی کل سلول را حذف کنید و مقدار را به صورت دستی وارد کنید. ممکن است ظریف نباشد، اما باید کار کند!

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