بیشتر کاربران Excel، میانبر Ctrl + F را به عنوان روشی سریع برای پیدا کردن متن یا مقدار مشخص در یک جدول میشناسند. احتمالاً با Ctrl + H نیز آشنا هستید، اما شاید تصور کنید این میانبر فقط برای جایگزین کردن یک مقدار با مقدار دیگر کاربرد دارد.
من نیز سالها متوجه نشدم Ctrl + H تا چه اندازه میتواند کاربردی باشد. از مرتب کردن دادههای واردشده از منابع مختلف گرفته تا برطرف کردن مشکلات قالببندی، قابلیت Find and Replace یکی از کاربردیترین و در عین حال کمتر مورد توجه قرارگرفته ابزارهای پاکسازی داده در Excel است.
در ادامه شیوه استفاده از کلید میانبر Ctrl + H در اکسل را به صورت کامل و تصویری توضیح میدهیم. سادهگو را دنبال کنید.
در چند ثانیه هر چیزی را در کل Workbook جایگزین کنید
کاربرد واضح Ctrl + H همچنان یکی از مفیدترین قابلیتهای آن است
میانبر Ctrl + H که پنجره Find and Replace در Excel را باز میکند، برای جایگزین کردن یک کلمه، عدد یا عبارت در شیت فعال بسیار کاربردی است؛ اما میتوان از آن به عنوان ابزاری برای ویرایش کل Workbook نیز استفاده کرد.
به عنوان مثال، اگر نام فردی در چندین شیت تکرار شده باشد یا بخواهید یک کد پروژه را در سراسر یک Workbook گزارشدهی تغییر دهید، اصلاح دستی تکتک شیتها تنها باعث اتلاف وقت خواهد شد.
به جای این کار، اجازه دهید Find and Replace تمام شیتها را در یک مرحله بررسی و ویرایش کند:
- یک سلول را در Workbook انتخاب کنید.
- کلیدهای Ctrl + H را فشار دهید تا پنجره Find and Replace باز شود.
- مقدار موردنظر برای تغییر را در کادر Find what و مقدار جدید را در Replace with وارد کنید.
- روی Options کلیک کنید تا تنظیمات پیشرفته نمایش داده شود.
- منوی کشویی Within را از Sheet به Workbook تغییر دهید.
- ابتدا روی Find All کلیک کنید و نتایج را بررسی کنید تا مطمئن شوید قرار نیست اطلاعات اشتباهی تغییر کند.
- پس از اطمینان، روی Replace All کلیک کنید تا تمام سلولهای منطبق در کل Workbook بهروزرسانی شوند.
به عنوان مثال، در یکی از فایلهای من تمام موارد "Samuel Jackson" در تمام Worksheetها با "Samuel L Jackson" جایگزین شد، بدون اینکه مجبور باشم هر شیت را به صورت جداگانه بررسی کنم.
این قابلیت بهخصوص در فایلهای بزرگ گزارشدهی یا Workbookهایی که تعداد زیادی شیت دارند، میتواند ساعتها در زمان کاربر صرفهجویی کند.
دادههای نامرتب را بدون نوشتن فرمول پاکسازی کنید
Wildcardها Ctrl + H را به ابزاری شگفتانگیز برای پاکسازی تبدیل میکنند
دادهها به ندرت دقیقاً با همان ساختاری که انتظار دارید در اختیار شما قرار میگیرند. ممکن است فهرستی را از یک وبسایت کپی کنید، یک فایل CSV دانلود کنید یا اطلاعاتی را از برنامه دیگری خروجی بگیرید. در چنین شرایطی، معمولاً کدها، برچسبها یا متنهای اضافی نیز همراه دادهها وارد Excel میشوند.
برای پاکسازیهای بزرگتر، معمولاً Power Query گزینه مناسبتری است. اما اگر فقط لازم باشد الگوهای متنی تکراری را حذف کنم یا یک مجموعه داده کوچک را قبل از ادامه کار مرتب کنم، Ctrl + H معمولاً بسیار سریعتر عمل میکند.
با استفاده از Wildcardها، Find and Replace تا حدی شبیه استفاده از یک فرمول است، با این تفاوت که نیازی نیست فرمولی بنویسید. کافی است الگوی موردنظر را مشخص کنید تا Excel بخش تکراری کار را انجام دهد.
Excel در Find and Replace دو Wildcard اصلی را پشتیبانی میکند:
- علامت ستاره (*) هر تعداد کاراکتر را نشان میدهد.
- علامت سؤال (?) دقیقاً یک کاراکتر را نشان میدهد.
به عنوان مثال، فرض کنید فهرستی از نامها را وارد Excel کردهاید و در کنار هر نام یک کد شناسه نیز قرار دارد؛ مانند:
Emma Davis(ID-48392)
اگر بخواهید این کدهای اضافی را از تمام موارد حذف کنید، کافی است عبارت (ID*) را در کادر Find what وارد کنید.
با این کار به Excel میگویید به دنبال پرانتز باز، عبارت ID و تمام کاراکترهایی بگردد که پس از آن قرار گرفتهاند. اگر کادر Replace with را خالی بگذارید، Excel کل کد شناسه را حذف میکند و نام را دستنخورده باقی میگذارد.
از آنجا که Wildcardها میتوانند دامنه جستوجوی بسیار گستردهای داشته باشند، همیشه پیش از جایگزینی حجم زیادی از دادهها، نتایج را بررسی کنید.
اگر همان الگو در بخش دیگری از Worksheet وجود دارد که نمیخواهید تغییر کند، بهتر است ابتدا محدوده مشخصی را انتخاب کنید و سپس Find and Replace را اجرا کنید.
Wildcard علامت سؤال دقیقتر است، زیرا فقط با یک کاراکتر مطابقت دارد. با این حال، نکته مهم این است که مشخص کنید گزینه Match entire cell contents در تنظیمات Find and Replace فعال باشد یا خیر.
اگر این گزینه را فعال کنید، جستوجوی Cable-? مواردی مانند Cable-1 و Cable-2 و Cable-3 و Cable-4 را پیدا میکند، اما مواردی مانند Cable-10 و Cable-20 و Cable-Pro را نادیده میگیرد.
اگر این گزینه فعال نباشد، Excel میتواند کاراکترهای منطبق را درون عبارتهای طولانیتر نیز پیدا و جایگزین کند؛ موضوعی که ممکن است به تغییرات ناخواسته در دادهها منجر شود.
قالببندی را بدون تغییر مقادیر عوض کنید
Find and Replace میتواند قالب اعداد را در چند ثانیه تغییر دهد
Find and Replace فقط مقادیر موجود در سلولها را بررسی نمیکند؛ این ابزار میتواند قالببندی سلولها را نیز جستوجو کند.
این قابلیت شامل رنگ، فونت، حاشیه و حتی قالب نمایش اعداد میشود. قالببندی اعداد بهخصوص زمانی کاربردی است که یک گزارش بزرگ دارید و یک قالب مشخص در جدولها یا Worksheetهای مختلف تکرار شده است.
در چنین شرایطی، اصلاح دستی قالببندی هر بخش میتواند زمان زیادی ببرد.
به عنوان مثال، فرض کنید چند جدول دارید که اعداد بزرگ در آنها بر اساس یک Custom Number Format به صورت هزارگان و با علامت K نمایش داده میشوند تا فضای کمتری اشغال کنند.
اما با افزایش اعداد، تصمیم میگیرید نمایش آنها را به قالب مرتبتر میلیون و با علامت M تغییر دهید، بدون اینکه مقدار واقعی سلولها تغییر کند. همچنین میخواهید علامت دلار نیز به اعداد اضافه شود.
Find and Replace میتواند این کار را انجام دهد:
- در پنجره Find and Replace، در کنار Find what روی Format کلیک کنید.
- در تب Number پنجره Find Format، گزینه Custom را انتخاب کنید و عبارت 0.0,"K" را وارد کنید تا سلولهایی با قالب هزارگان پیدا شوند.
- در کنار Replace with روی Format کلیک کنید.
- در تب Number گزینه Custom را انتخاب کنید و عبارت $0.0,,"M" را وارد کنید تا قالب میلیون همراه با علامت دلار اعمال شود.
- روی Find All کلیک کنید تا مطمئن شوید Excel سلولهای صحیح را انتخاب کرده است. سپس در صورت اطمینان روی Replace All کلیک کنید.
در Workbookهای دیگر نیز میتوانید از همین روش برای تغییر قالبهای سفارشی استفاده کنید؛ به عنوان مثال تغییر واحد پول، تعداد ارقام اعشار، درصد یا نحوه نمایش تاریخ بدون اینکه مقدار واقعی سلولها تغییر کند.
پس از پایان کار، منوی کشویی کنار دکمههای Format را باز کنید و گزینههای Clear Find Format و Clear Replace Format را انتخاب کنید.
Excel تنظیمات قالببندی قبلی را حتی پس از بسته شدن پنجره Find and Replace نیز به خاطر میسپارد. بنابراین اگر این تنظیمات را پاک نکنید، ممکن است جستوجوهای بعدی رفتار عجیبی داشته باشند و تصور کنید Find and Replace به درستی کار نمیکند.
کاراکترهای نامرئی را از دادههای واردشده حذف کنید
Ctrl + J شکست خطهایی را پیدا میکند که Excel معمولاً پنهان میکند
این احتمالاً محبوبترین ترفند Ctrl + H برای من است؛ زیرا Excel تقریباً هیچ نشانهای از وجود آن ارائه نمیدهد.
زمانی که دادهها را از فرمهای وب، ایمیلها یا فایلهای PDF کپی میکنم، گاهی شکست خطهای مخفی داخل سلولها قرار میگیرند. این کاراکترهای نامرئی باعث میشوند متن داخل یک سلول در چند خط نمایش داده شود، ارتفاع ردیفها به هم بریزد و حتی برخی فرمولهای متنی نیز به درستی عمل نکنند.
از آنجا که این شکست خطها قابل مشاهده نیستند، وارد کردن یک فاصله معمولی در کادر Find what نمیتواند آنها را پیدا کند.
راهکار این است که کاراکتر مخفی Line Feed را مستقیماً وارد کادر جستوجو کنید:
- ستونی را که حاوی متنهای چندخطی و نامرتب است انتخاب کنید.
- در پنجره Find and Replace داخل کادر Find what کلیک کنید و کلیدهای Ctrl + J را فشار دهید. ممکن است کادر خالی به نظر برسد یا یک نقطه کوچک و چشمکزن نمایش دهد.
- جداکننده موردنظر خود را در کادر Replace with وارد کنید؛ به عنوان مثال فاصله، ویرگول، دونقطه یا هر علامت نگارشی دیگری که میخواهید در متن نهایی نمایش داده شود.
- روی Replace All کلیک کنید تا متنهای چندخطی به عبارتهای مرتب و تکخطی تبدیل شوند.
اگر جستوجوی بعدی در Excel رفتار عجیبی داشت، ابتدا کادر Find what را بررسی کنید. Excel ممکن است تنظیمات قبلی Find and Replace را تا زمانی که آنها را پاک نکنید، حفظ کند.
میانبرهای کوچک میتوانند روش کار شما با Excel را تغییر دهند
شورتکات Ctrl + H یکی از آن قابلیتهای Excel است که در نگاه اول ساده به نظر میرسد، اما وقتی وارد گزینههای آن میشوید، متوجه میشوید امکانات بسیار بیشتری در اختیار شما قرار میدهد.
پس از اینکه استفاده درست از آن را یاد گرفتم، Ctrl + H به یکی از اولین میانبرهایی تبدیل شد که هر زمان یک Workbook نیاز به پاکسازی و مرتبسازی داشته باشد، سراغ آن میروم.
از جایگزینی اطلاعات در تمام Worksheetها گرفته تا حذف الگوهای تکراری، اصلاح قالببندی و حتی پاک کردن کاراکترهای نامرئی، Find and Replace میتواند بسیاری از کارهای تکراری را تنها با چند کلیک انجام دهد.
این قابلیت همچنین یادآوری خوبی است که در Excel، همیشه قدرتمندترین ابزارها آنهایی نیستند که در ظاهر بیشترین گزینهها را دارند. گاهی یک میانبر ساده صفحهکلید مانند Ctrl + H میتواند بخش قابل توجهی از زمان کاری شما را ذخیره کند.
سادهگو
