یک برگه اکسل به هم ریخته می تواند یک کابوس باشد – فضاهای اکسپرس ، قالب بندی متناقض ، متن تصادفی در همه جا. اما با توابع مناسب ، می توانید آن را در عرض چند ثانیه تمیز کرده و داده های خود را در واقع قابل استفاده کنید.
یک برگه اکسل به هم ریخته می تواند یک کابوس باشد – فضاهای اکسپرس ، قالب بندی متناقض ، متن تصادفی در همه جا. اما با توابع مناسب ، می توانید آن را در عرض چند ثانیه تمیز کرده و داده های خود را در واقع قابل استفاده کنید.
5
دورتر
فضاهای ولگرد در صفحه گسترده شما می تواند دو ورودی یکسان را متفاوت کند ، با اعتبار داده ها آشفته شود و حتی فرمول های شما را از بین ببرد. خوشبختانه ، عملکرد تریم با از بین بردن تمام فضاهای اضافی از متون شما (به جز فضاهای منفرد بین کلمات) همه چیز را حل می کند. این عملکرد برای مرتب کردن نام ها ، آدرس ها و غیره ، به ویژه در برگه هایی که از منابع دیگر وارد می کنید بسیار عالی است.
بیایید بگوییم سلول A1 حاوی آلیس است ، با فضاهای اضافی در همه جا. با استفاده از عملکرد تریم ، آلیس را بدون هیچ فضایی باز می گرداند.
به جای مراجعه به یک سلول و گسترش فرمول ، می توانید تمام داده های خود را نیز به صورت یک حرکت ارجاع دهید. به عنوان مثال ، می توانم از فرمول زیر برای تمیز کردن تمام فضاهای ولگرد خود استفاده کنم.
= تریم (A1: G5)
تریم اغلب اولین خط دفاع شما در پاکسازی داده ها است. بسیاری از کارکردهای متنی دیگر وقتی داده های خود را کاهش داده اید بهتر کار می کنند ، بنابراین این مرحله را رد نکنید.
4
IFERROR
من فوراً هر زمان که Div/0 ، N/A ، ارزش! ، و سایر خطاهای فانتزی اکسل را که در صفحات گسترده من قرار دارد ، احساس آماتور می کنم. شاید من همیشه اینگونه احساس نکنم ، اما فقط دیدن آنها باعث می شود من (و همکارانم) از چیزی که خیلی خوب می تواند یک مسئله کوچک باشد ، اضطراب داشته باشم.
IFError چیزی را نشان می دهد و باعث اضطراب کمتری می شود. این شرکت با Office 2019 و اشتراک های Microsoft 365 کار می کند ، بنابراین بسیار در دسترس است. این عملکرد یک فرمول را ارزیابی می کند و اگر فرمول منجر به خطا شود ، یک مقدار مشخص را برمی گرداند.
به عنوان مثال ، به جای استفاده از فرمولی که #نام را برمی گرداند؟ وقتی هیچ مسابقه ای پیدا نشود ، مانند موارد زیر:
= vlookup (A1 ، جدول 1،2 ، نادرست)
به جای آن می توانید از فرمول زیر استفاده کنید تا یک پیام سفارشی را برگردانید:
= ifError (vlookup (A1 ، جدول 1،2 ، نادرست) ، “یافت نشد”)
صفحه گسترده شما فوراً حرفه ای تر به نظر می رسد ، و رئیس شما متوقف می شود که چرا پیام های خطا در همه جا وجود دارد. به علاوه ، شما می توانید هر آنچه را که دوست دارید برگردانید – سلولهای بغل ، خط ، پیام های سفارشی یا حتی محاسبات جایگزین – اگر و در هنگام بروز خطاها.
3
تمیز
هنگامی که داده ها را از PDF ، وب سایت ها و سیستم های میراث وارد می کنید یا حتی پرونده های PDF را به صفحه گسترده اکسل تبدیل می کنید ، تمایل دارید برخی از شخصیت های نامرئی را حمل کنید. مواردی مانند شکسته شدن خط و نمادهای پنهان ، که نمی توانید ببینید ، می توانید در کنار هم قرار دهید و فرمول های خود را بشکنید.
عملکرد تمیز تمام شخصیت های غیر قابل چاپ را از متن شما حذف می کند و نسخه قابل پیش بینی تر و فرمول تری را پشت سر می گذارد.
همچنین می توانید با عملکرد تریم تمیز کنید. بسیاری از افراد جوان اکسل از این به عنوان دسته کوچک موسیقی جاز برای داده های وارداتی استفاده می کنند:
= تریم (تمیز (متن))
Clean اولین 32 کاراکتر غیر قابل چاپ را در کد 7 بیتی ASCII (مقادیر 0 تا 31) حذف می کند ، که برای پرونده های مبتنی بر ویندوز کار می کند. اگر داده های شما از MACOS یا منابع وب تهیه شده است ، ممکن است برای تمیز کردن مزاحمت های Unicode به ابزارهای اضافی نیاز داشته باشید.
2
متون
بعضی اوقات ، تمام داده های شما به یک سلول واحد تبدیل می شوند ، که توسط کاما ، نیمه رنگها ، دوره ها یا استراحت های خط جدا می شوند. نیاز به استخراج داده های خود در ردیف ها و ستون های مناسب به صورت دستی درد است.
Textsplit ، موجود در Excel 365 و Excel 2021 ، همانند جادوگر متن به ستون ، اما به شکل فرمول کار می کند ، و اگر با داده های هماهنگ برخورد می کنید بسیار عالی است. این عملکرد می تواند با استفاده از محدود کننده هایی که مشخص می کنید ، متن را در ستون ها یا ردیف های پایین تقسیم کنید.
اگر سلول A1 حاوی ADA ، Uche ، Ada.uche@email.com باشد ، سپس با استفاده از فرمول زیر می توانید متن را به سه ستون جداگانه تقسیم کنید: ADA ، Uche و Ada.uche@email.com.
= textsplit (a1 ، “،”)
حتی می توانید بر اساس ردیف یا ستون تقسیم شوید و چندین محدود کننده را کنترل کنید. به عنوان مثال ، اگر سلول A2 حاوی سیب ، موز ؛ گیلاس است ، سپس از فرمول زیر استفاده کنید:
= textsplit (a1 ، {“،” ، “؛”})
این متن را به سیب ، موز و گیلاس تقسیم می کند.
به طور پیش فرض ، TextSplit این مقادیر را در ستون ها پر می کند. اگر می خواستید که آنها به جای آنها به ردیف های جداگانه تقسیم شوند ، چهارمین استدلال را که به طور پیش فرض نادرست یا حذف شده است ، تنظیم کنید:
= textsplit (a2 ، “،” ، “؛” ، ، true)
این امر باعث می شود اپل در سلول I1 ، موز در J1 ، گیلاس در I2 و تاریخ J2 باشد.
توجه کنید که شما می خواهید از یک تعیین کننده (مانند یک قسمت) برای تقسیم کلمات به ستون ها و دیگری (مانند یک دوره) استفاده کنید تا نشان دهید که یک ردیف جدید باید از کجا شروع شود.
در نتیجه ، این عملکرد می تواند کاملاً پیچیده باشد. این اغلب نیاز به برنامه ریزی دقیق از تعیین کنندگان شما و ترکیب بالقوه TextSplit با سایر توابع برای دستیابی به طرح مورد نظر خود دارد. اما برای استخراج ، Textsplit عالی عمل می کند.
1
جبهه
بعد از تقسیم ، ممکن است بخواهید قطعات را به عقب بکشید. TextJoin به شما کمک می کند تا چندین رشته متنی را با تعیین کننده مورد نظر خود ادغام کنید و حتی به شما امکان می دهد از خالی ها پرش کنید.
بیایید بگوییم که شما در ستون A ، نام های میانی در ستون B (برخی از سلول ها خالی هستند) و نام خانوادگی در ستون C. می توانید از فرمول زیر استفاده کنید تا نام های کامل را بدون فضاهای اضافی از سلولهای نام میانی خالی ایجاد کنید.
= TextJoin (“” ، درست ، A1 ، B1 ، C1)
پس از استفاده از فرمول ، نام های کاملی مانند Cardi B یا Mary Jane Watson دریافت خواهید کرد.
استدلال واقعی به اکسل می گوید که سلولهای خالی را نادیده می گیرد ، بنابراین شما شکاف های ناخوشایند یا کاما آویزان نمی گیرید.
صفحات گسترده کثیف تا زمانی که آنها را مرتب نکنید ، هرج و مرج است. اما با توابع عالی اکسل ، شما نیازی به ساعت های تعمیر دستی ندارید. بنابراین ، دفعه بعد که یک صفحه گسترده را باز می کنید که باعث می شود چشمان شما پیچیده باشد ، دقیقاً می دانید چه کاری باید انجام دهید.