مرتب‌سازی، فیلتر و جدول در اکسل؛ به‌علاوه‌ی حل مشکل «ی» و «ک» فارسی

قبل از هر کاری: داده‌ی مرتب

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

• فقط یک ردیف عنوان در بالای جدول
• بدون ردیف یا ستون کاملاً خالی وسط داده‌ها
• بدون خانه‌های ادغام‌شده (Merge) داخل جدول

تبدیل محدوده به جدول (Ctrl+T)

روی یکی از خانه‌های داده کلیک کنید و Ctrl+T را بزنید. اگر داده‌ها ردیف عنوان دارند، تیک My table has headers را بگذارید و OK بزنید. جدول اکسل چند مزیت دارد:

• دکمه‌ی فیلتر خودکار روی هر ستون
• ردیف‌های راه‌راه که خواندن را راحت می‌کند
• فرمولی که در یک ردیف بنویسید، خودکار تا آخر ستون کپی می‌شود
• ردیف جمع: از زبانه‌ی Table Design گزینه‌ی Total Row را بزنید تا زیر جدول جمع، میانگین یا تعداد را انتخاب کنید

مرتب‌سازی

مرتب‌سازی ساده: روی یک خانه از ستون مورد نظر کلیک کنید و از زبانه‌ی Data دکمه‌ی A به Z یا Z به A را بزنید.

مرتب‌سازی چندسطحی: مثلاً اول بر اساس شهر و بعد در هر شهر بر اساس نمره از زیاد به کم:

1. از زبانه‌ی Data روی Sort بزنید.
2. در Sort by ستون شهر را انتخاب کنید.
3. Add Level را بزنید و در Then by ستون نمره را با ترتیب Largest to Smallest انتخاب کنید.
4. OK بزنید.

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

فیلتر

1. اگر داده را جدول نکرده‌اید، با Ctrl+Shift+L دکمه‌های فیلتر را روشن کنید.
2. روی فلش کنار عنوان ستون بزنید و مقدارهای مورد نظر را تیک بزنید.
3. برای شرط عددی، از Number Filters گزینه‌ای مثل Greater Than را انتخاب کنید؛ مثلاً نمره‌ی بیشتر از ۱۵.
4. برای متن، از Text Filters گزینه‌ی Contains را بزنید؛ مثلاً همه‌ی نام‌هایی که «محمد» دارند.

برای برداشتن همه‌ی فیلترها، دوباره Ctrl+Shift+L را بزنید.

حذف ردیف‌های تکراری

از زبانه‌ی Data روی Remove Duplicates بزنید و ستون‌هایی را تیک بزنید که تکراری بودن بر اساس آن‌ها سنجیده شود. اکسل ردیف‌های تکراری را حذف می‌کند و می‌گوید چند ردیف حذف شد. قبل از این کار یک نسخه از فایل نگه دارید، چون حذف دائمی است.

ثابت کردن ردیف عنوان

در جدول‌های بلند، با پایین رفتن ردیف عنوان دیگر دیده نمی‌شود. از زبانه‌ی View گزینه‌ی Freeze Panes و بعد Freeze Top Row را بزنید تا ردیف اول همیشه بالای صفحه بماند.

رنگی کردن خودکار (قالب‌بندی شرطی)

ستون نمره را انتخاب کنید، از زبانه‌ی Home روی Conditional Formatting بزنید و از Highlight Cells Rules گزینه‌ی Less Than را انتخاب کنید. عدد ۱۰ را بنویسید تا نمره‌های زیر ۱۰ قرمز شوند. گزینه‌ی Data Bars هم کنار هر عدد یک نوار رنگی به اندازه‌ی آن عدد می‌کشد.

مشکل «ی» و «ک» در فایل‌های فارسی

یکی از رایج‌ترین مشکل‌های اکسل فارسی این است که فیلتر، جستجو یا VLOOKUP اسمی را که جلوی چشمتان است پیدا نمی‌کند. دلیلش معمولاً این است که بخشی از داده با صفحه‌کلید عربی تایپ شده و «ي» و «ك» عربی دارد. این حروف شبیه «ی» و «ک» فارسی دیده می‌شوند ولی برای اکسل حروف متفاوتی‌اند.

راه حل:

1. Ctrl+H را بزنید تا پنجره‌ی Find and Replace باز شود.
2. در Find what حرف «ي» عربی را بگذارید (از یکی از خانه‌هایی که مشکل دارد کپی کنید) و در Replace with حرف «ی» فارسی را تایپ کنید.
3. Replace All را بزنید.
4. همین کار را برای «ك» عربی و «ک» فارسی تکرار کنید.

برای اینکه این مشکل دوباره پیش نیاید، در ویندوز صفحه‌کلید «فارسی استاندارد» (Persian Standard) را نصب کنید. توضیحش را در تایپ فارسی در ورد آورده‌ایم.

اگر هنوز فرمول‌های پایه را مرور نکرده‌اید، ۱۰ فرمول پرکاربرد اکسل را ببینید.