متن فیلم

بعد از مواجه درست با داده‌های نادرست و حذف ردیف‌های خالی و داده‌های تکراری، در این ویدیو به تغییر حروف بزرگ و کوچک متن، رفع خطاهای قالب‌بندی تاریخ و حذف فضاهای خالی از داده‌ها می‌پردازیم. وقتی داده‌ها را از منابع مختلف جمع‌آوری یا دریافت می‌کنید، کاملاً عادی هست که متوجه شوید داده‌های شما حاوی متن با حروف بزرگ و کوچک هستند؛ یعنی برخی با حروف بزرگ، برخی با حروف کوچک و برخی با حرف بزرگ در ابتدای جمله proper case (که به عنوان sentence case نیز شناخته می‌شود). برخی از این موارد ممکن است عمدی باشند؛ اما اغلب اینطور نیست. اکسل دکمه تغییر حالت (Change case) مانند مایکروسافت ورد ندارد، بنابراین باید از روش‌های دیگری مانند بکارگیری توابع برای انجام این کار پاکسازی داده‌ها استفاده کنید. یعنی توابع UPPER، LOWER و PROPER. می‌توانید از این توابع برای کمک به تغییر حالت متن در داده‌های خود استفاده کنید. می‌توانید ببینید که ردیف هدر در اینجا فقط از کاراکترهای حروف بزرگ استفاده می‌کند، بنابراین اگر می‌خواهید آن را به حالت مناسب تغییر دهید، باید ردیف دیگری برای قرار دادن تابع در آن اضافه کنید. به این ردیف، ردیف کمکی می‌گویند. استفاده از تابع PROPER ساده است؛ فقط کافیست علامت مساوی را تایپ کنید، و این کارها را به ترتیب انجام دهید، درج تابع PROPER، پرانتز باز ، مرجع سلول - در این مورد A1 – و در آخر پرانتز را ببندید و Enter را بزنید. در اینجا می‌توانید ببینید که نتیجه در A2 در حالت proper case صحیح است. اکنون می‌توانید با استفاده از Fill Handle در A2، فرمول را به سمت راست ستون X بکشید... اما وقتی ستون‌های زیادی دارید، این کار می‌تواند بسیار مشکل باشد، بنابراین بیایید راه دیگری را امتحان کنیم. به جای کشیدن، می‌توانید از کلیدهای کلید جهتی راستShift+ برای انتخاب ستون‌ها تا X استفاده کنید، سپس F2 را فشار دهید تا مکان‌نما در سلول A2 قرار گیرد، کلید CTRL را نگه دارید و Enter را فشار دهید. ممکن است فکر کنید که اکنون می‌توانید سطر اصلی را حذف کنید؛ اما ببینید وقتی این کار را انجام می‌دهید چه اتفاقی می‌افتد؛ با خطای REF مواجه می‌شوید زیرا فرمول به یک مرجع نامعتبر ارجاع می‌دهد و سلول‌های سطر هدر اکنون فقط حاوی فرمول ناموفق هستند، نه متن هدر واقعی. بنابراین، باید آن را لغو کنید و در عوض، محتویات ردیف کمکی را به ردیف ۱ کپی کنید، اما هنگام چسباندن، باید گزینه چسباندن مقادیر (Paste Values) را انتخاب کنید. اکنون سلول‌های ردیف سربرگ فقط حاوی متن سربرگ هستند و می‌توانید ردیف کمکی را در ردیف ۲ حذف کنید. حال بیایید از تابع UPPER برای تغییر متن از proper case به حروف بزرگ استفاده کنیم. یک ستون در سمت راستِ ستونی که می‌خواهید تغییر دهید قرار دهید. این یک ستون «کمکی» خواهد بود. سپس فرمول حاوی تابع UPPER را در اولین سلول داده در این ستون کمکی جدید تایپ می‌کنید. باز هم، این یک فرمول ساده است؛ شما علامت مساوی، سپس به ترتیب UPPER، پرانتز باز، مرجع سلول - در این مورد T2 - را تایپ می‌کنید و در آخر پرانتز را می‌بندید و Enter را می‌زنید. می‌توانید ببینید که نتیجه نام کشور با حروف بزرگ است و سپس می‌توانید آن فرمول را با دوبار کلیک کردن روی نماد Fill Handle در بقیه ستون کپی کنید. مانند قبل، محتویات ستون کمکی را کپی کرده و در ستون اصلی پیست می‌کنید، اما از گزینه Paste Values استفاده کنید. اکنون می‌توانید ستون کمکی را حذف کنید. در مرحله بعد، از تابع LOWER برای تغییر متن از حالت proper case به حروف کوچک استفاده خواهیم کرد. مانند قبل، یک ستون در سمت راست ستونی که می‌خواهید تغییر دهید، قرار می‌دهید. این یک ستون «کمکی» دیگر خواهد بود. سپس فرمول حاوی تابع LOWER را در اولین سلول داده در ستون کمکی تایپ می‌کنید. باز هم، این یک فرمول بسیار ساده است؛ شما علامت مساوی، و سپس به ترتیب LOWER، پرانتز باز، مرجع سلول - در این مورد K2 - را تایپ می‌کنید و در آخر پرانتز را می‌بندید و Enter را می‌زنید. می‌توانید ببینید که نتیجه، داده‌های خط تولید با حروف کوچک است و اکنون می‌توانید فرمول را با دوبار کلیک کردن دوباره روی Fill Handle به بقیه ستون کپی کنید. مانند قبل، محتویات ستون کمکی را در ستون اصلی کپی و جای‌گذاری می‌کنید، اما مطمئن شوید که از گزینه Paste Values استفاده می‌کنید. اکنون می‌توانید ستون کمکی را حذف کنید. دریافت داده‌هایی که ترکیبی از قالب‌های تاریخ هستند یا از قالب تاریخی استفاده می‌کنند که برای منطقه شما مناسب نیست، کاملاً رایج است. اکنون بیایید به نحوه تغییر قالب برخی از تاریخ‌ها نگاهی بیندازیم. می‌توانید ببینید که این قالب تاریخ در حال حاضر از یک مقدار روز ۲ رقمی، ماه ۲ رقمی و سال ۴ رقمی استفاده می‌کند. وقتی کادر محاوره‌ای «فرمت اعداد» را باز می‌کنید، در کادر «محل» می‌توانید ببینید که این یک قالب تاریخ انگلیسی (بریتانیا) است. شما می‌خواهید از قالب تاریخ ایالات متحده استفاده کنید، بنابراین ابتدا «محل» را به انگلیسی (ایالات متحده) تغییر می‌دهید. در این لیست، می‌توانید چندین گزینه تاریخ برای انتخاب ببینید؛ بیایید یکی را انتخاب کنیم که از نام کامل ماه، سپس «روز» دو رقمی و «سال» چهار رقمی استفاده می‌کند. سپس می‌توانید این قالب را به بقیه سلول‌های تاریخ کپی کنید. با این حال، اگر می‌خواهید این تاریخ‌ها را با استفاده از قالب سفارشی خود قالب‌بندی کنید، می‌توانید این کار را نیز انجام دهید. در لیست «قالب اعداد»، «Custom» را انتخاب می‌کنید و سپس یک قالب موجود را که مشابه قالب مورد نظر شماست انتخاب می‌کنید و به سادگی آن را برای ایجاد یک قالب سفارشی جدید تغییر می‌دهید. در اینجا ما روز، سپس ماه سه حرفی و سپس سال چهار رقمی را خواهیم داشت. برای اعمال این قالب تاریخ سفارشی جدید به بقیه ستون، می‌توانید از ابزار Format Painter استفاده کنید، یا می‌توانید بقیه ستون را انتخاب کرده و قالب سفارشی جدید را از لیست Custom در کادر محاوره‌ای Number Format انتخاب کنید. ممکن است متوجه شوید که داده‌های شما دارای مقداری فضای خالی هستند؛ یعنی فضاهای ناخواسته در داده‌های شما. در اینجا می‌توانید ببینید که ما در ابتدا، انتها و وسط داده‌های خود چند فضای خالی داریم. ابتدا نگاهی به کارهایی که می‌توانید برای پاک کردن این فضاهای ناخواسته در داده‌های خود با استفاده از ویژگی Find & Replace در اکسل انجام دهید، خواهیم داشت. بنابراین ابتدا همه داده‌ها را انتخاب می‌کنید، سپس در تب Home، روی Find & Select و سپس Replace کلیک می‌کنید. برای خلاص شدن از شر فضاهای خالی، یک فضای خالی دوگانه در کادر «Find what» و یک فضای خالی تکی در کادر «Replace with» وارد می‌کنید. سپس روی Find Next کلیک می‌کنید. و برای هر موردی که می‌خواهید تغییر دهید، Replace را انتخاب می‌کنید. می‌توانید روی Replace All کلیک کنید تا همه اصلاحات را یکجا انجام دهید، این حالت زمانی مفید است که کاملاً از تغییرات مطمئن باشید، در غیر اینصورت بهتر است هر کدام را به ترتیب بررسی و جایگزین کنید. اگر مجموعه داده بسیار بزرگی دارید، می‌توانید Replace All را انتخاب کنید تا در زمان زیادی صرفه‌جویی کنید. بنابراین با استفاده از ویژگی Find & Select از شر اکثر آن فضاهای سفید ناخواسته خلاص شدیم، اما نه همه آنها؛ ما با استفاده از این ویژگی، فضاهای دوتایی را حذف کردیم، اما در ابتدا و انتهای برخی از سلول‌ها، تعدادی فضای تکی نیز باقی مانده است. شما نمی‌توانید از Find & Select برای حذف فضاهای تکی استفاده کنید، در غیر این صورت تمام فضاهای موجود در داده‌های خود - از جمله فضاهای استاندارد بین کلمات - را که نمی‌خواهید حذف کنید، از دست خواهید داد. اما، ابزار دیگری وجود دارد که می‌توانید برای پاک کردن فضاها از سلول‌ها از آن استفاده کنید، و آن تابع TRIM است. برای استفاده از تابع TRIM، یک بار دیگر یک ستون کمکی وارد ‌کنید. استفاده از تابع TRIM ساده است؛ فقط کافیست علامت مساوی، سپس به ترتیب TRIM، پرانتز باز، ارجاع سلول - در این مورد M2 - را تایپ کنید، در آخر پرانتز را ببندید و Enter را فشار دهید. سپس می‌توانید روی نماد Fill Handle دوبار کلیک کنید تا این فرمول در بقیه ستون کپی شود. حالا باید محتویات ستون جدید N را در ستون M کپی کنید و دوباره به یاد داشته باشید که با استفاده از گزینه Paste Values آن را جایگذاری کنید. اکنون می‌توانید ببینید که آن فضاهای اشتباه حذف شده‌اند، یا به عبارت دقیق‌تر، کوتاه شده‌اند. و در آخر، می‌توانید ستون کمکی را حذف کنید. در این ویدیو، یاد گرفتیم که چگونه حروف بزرگ و کوچک متن، قالب‌بندی تاریخ و فاصله‌های خالی را در داده‌ها تغییر دهیم. در ویدیوی بعدی، نحوه استفاده از ویژگی‌های Flash Fill و Text to Columns در اکسل را برای کمک به پاکسازی داده‌ها مورد بحث قرار خواهیم داد.