پاکسازی داده در اکسل؛ از فرمول‌های پرکاربرد تا مراحل اصلی عملیات

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

 

فرمت‌های رایج فایل داده

دولت‌ها و سایر منابع اطلاعاتی داده‌های خود را به فرمت‌های متنوعی منتشر می‌کنند. در اینجا به چند مورد از رایج‌ترین آنها اشاره می‌کنیم:

DBF: Database format

database format غالبا برای ذخیره کردن مجموعه‌های بزرگ داده و سازماندهی کردن آنها استفاده می‌شود. تعداد زیادی اپلیکیشن فایل‌های dbf را تولید می‌کنند یا امکان خواندن آنها را دارند. بعضی اوقات سازمان‌ها داده‌های خام را به این شکل تهیه می‌کنند. دو اپلیکیشن رایجی که برای خواندن فایل‌های dbf استفاده می‌شوند Microsoft Access و OpenOffice هستند. Excel و Google Docs از این فایل‌ها پشتیبانی نمی‌کنند.

DBF: Database format

CSV: Comma separated value

فایل‌های CSV شاید بیشتر از همه‌ی انواع دیگر فایل‌های داده از سوی دولت‌ها منتشر شده‌اند و تقریبا بوسیله‌ی هر اپلیکیشن صفحه گسترده‌ای قابل خواندن هستند. داده به صورت یک فایل متنی(text file) است که هر سلول آن با یک ویرگول از هم جدا می‌شوند و هر پاراگراف(یک یا چند خطی که با یک enter از خط یا خطوط قبلی متمایز می‌شود) یک ردیف را شکل می‌دهد.

CSV: Comma separated value

TSV: Tab separated values

فرمت TSV یک فایل متنی دیگر است که در آن سلول‌ها به جای ویرگول با tab از یکدیگر جدا می‌شوند.

TSV: Tab separated values

Fixed width

Fixed width هم یک نوع فایل متنی است، اما با CSV و TSV تفاوت دارد. در این نوع فایل‌ها هر ورودی با یک یا چند space جدا می‌شود تا یک شبکه‌‌ای که به خوبی هم‌تراز شده به وجود بیاید. در تصویر زیر space با کاراکترهای دیگر جایگزین شده تا شکل فایل بهتر دیده شود. Excel و OpenOffice می‌توانند این فایل‌ها را باز کنند.

Fixed width

XML: Extensible markup language

در XML داده‌ها به طور سلسله مراتبی و شبیه به روشی که HTML کار می‌کند مرتب می‌شود. هر ردیف داده بوسیله‌ی باز و بسته شدن تگ‌ها(tags) شرح داده می‌شود. داده‌ی موجود در هر ردیف هم توسط مجموعه‌ی تگ‌های دیگری که از عنوان ستون‌ها برداشت شده تعریف می‌شود. این فرمت برای استخراج داده برای استفاده در صفحات وب کاربرد دارد. اکسل فایل‌های XML را تا حدودی با موفقیت باز می‌کند.

XML: Extensible markup language

JSON: JavsScript Object Notation

مانند XML، کاربرد JSON هم عمدتا در استخراج داده به منظور استفاده در صفحات وب و اپلیکیشن‌ها (بوسیله‌ JavaScript) است. Google Refine بعضی از فایل‌های JSON را می‌تواند باز کند.

 

چند فرمول‌ پرکاربرد در پاکسازی داده
CONCATENATE

وقتی داده‌ها را پاکسازی می‌کنید بهترین حالت اینست که ستون‌های داده را به وسیله یک ردیف که به آن ردیف‌عنوان(header row) گفته می‌شود برچسب‌گذاری(عنوان‌گذاری) کنید. بعضی منابع، داده‌هایی را منتشر می‌کنند که ۲، ۳، ۴ و یا ۵ ردیف‌عنوان دارند. اگر داده‌های شما تعداد کمی ستون داشته باشد خیلی آسان می‌توانید با cut و paste کردن، آنها را یکی کنید اما وقتی تعداد ستون‌ها زیاد باشد باید به دنبال راه دیگری بروید.

فایل data-cleaning.xls را دانلود و باز کنید. در این فایل سه عنوان‌ردیف و به دنبال آنها سه ردیف خالی وجود دارد.

در سلول D4 تایپ کنید:  (CONCATENATE(D1,D2,D3=

CONCATENATE

 

این تابع همه‌ی متن‌‌ها را بر می‌دارد و به این صورت در یک سلول قرار می‎دهد: Violentcrimerate

ما نیاز داریم تا بین کلمه‌ها فضای خالی ایجاد کنیم تا متن، قابل خواندن بشود. برای این کار ” “& (یک space بین دو دبل کوتیشن) قرار می‌دهیم. فرمول به این صورت تغییر پیدا می‌کند: (CONCATENATE(D1&” ” ,D2&” “,D3= تا بین کلمه‌ها فضای خالی به وجود بیاید.

CONCATENATE

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

CONCATENATE

حالا کلیک کنید و علامت جمع را به سمت چپ(یا راست) بکشید.

CONCATENATE

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

CONCATENATE

برای اینکه فرمول را برای کل ردیف اعمال کنید، ابتدا سلول A4 را انتخاب کنید، سپس کلید شیفت(Shift) را نگه دارید و T4 را انتخاب کنید. با این کار کل ردیف انتخاب می‌شود. حالا کلید کنترل(Ctrl) را همراه با R نگه دارید. با این کار فرمول برای تمام سلول‌های انتخاب شده تا اولین سلولی که فرمول دارد لحاظ می‌شود.

نکته: اگر می‌خواهید همین کار را برای یک ستون انجام دهید کلید کنترل(Ctrl) را همراه با D نگه دارید. در این حالت بالاترین سلول باید فرمول داشته باشد.

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

 

TRIM

به سلول A1 نگاه کنید، خالی است. خالی بودن بعضی از سلول‌ها می‌تواند پیامدهایی داشته باشد. به سلول A4 توجه کنید، ما بوسیله‌ی فرمول قبلی دو space به دو سلول خالی اضافه کردیم و حالا قبل از عنوان Year دو space اضافی وجود دارد. اکسل این امکان را به ما می‌دهد که با استفاده از تابع TRIM آنها را از بین ببریم. TRIM به دنبال فضاهای خالی موجود در آغاز و پایان هر سلول می‌گردد و آنها را حذف می‌کند. در سلول A5 تایپ کنید: (TRIM(A4=

TRIM

وقتی مشاهده کردید که فضاهای خالی از بین رفته‌اند، سلول A5 را انتخاب کنید و سپس برای انتخاب ردیف مورد نظر، کلیدهای Ctrl ،Shift و فلش راست را نگه دارید. (یا همزمان روی سلول T5 کلیک کنید و کلید Shift را نگه دارید). برای اعمال فرمول کلیدهای R و Ctrl را فشار دهید و نتیجه را ملاحظه کنید.

در اکسل شما این امکان را دارید که چند فرمول را با هم ترکیب کنید. در این مورد می‌توانید TRIM و CONCATENATE را با هم ترکیب کنید تا در زمان هم صرفه‌جویی شود:

((TRIM(CONCATENATE(A1&” “,A2&” “,A3=

 

Paste Special

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

Paste Special

ما این مشکل را با یک جادوی کوچک به نام Paste Special دور می‌زنیم.

برای انتخاب کل ردیف ۵ روی برچسب ردیف ۵ کلیک کنید. آن ردیف را copy کرده، سپس روی برچسب ردیف ۶ کلیک کنید و آن را انتخاب کنید. حالا به منوی Edit بروید و Paste Special را انتخاب کنید. یک پنجره با دسته‌ای از گزینه‌های مختلف باز می‌شود. در بخش Paste گزینه‌ی Values را انتخاب کنید.

 

روی OK کلیک کنید، مقادیر(values) در سلول‌ها paste می‌شوند. Paste Special معادل فرمان Paste Values در Google Spreadsheets است. حالا توابع، دیگر وجود ندارند و می‌توانید ردیف‌های ۱ تا ۵ را حذف کنید. پنج ردیف اول را انتخاب کنید و به منوی Edit بروید و Delete را انتخاب کنید.

 

وارد کردن فایل‌های CSV به اکسل

روند یکسانی برای وارد کردن هر نوع از فایل‌های متنی محدود شده یا با عرض ثابت به اکسل یا OpenOffice وجود دارد. در اینجا قصد داریم تا با فایل CSV کار کنیم.

فایل data-import.csv را دانلود کنید.

یک صفحه گسترده‌ی جدید باز کنید و منوی File بروید و Import را انتخاب کنید. یک پنجره‌ی جدید باز خواهد شد. CSV را انتخاب کنید و روی Import کلیک کنید.

وارد کردن فایل‌های CSV به اکسل

فایلی که دانلود کرده‌اید را پیدا کنید و آن را انتخاب کنید. پنجره‎ی import wizard باز می‌شود. مطئمن شوید که Delimited انتخاب شده است و بر روی Next کلیک کنید.

 

اطمینان پیدا کنید که فقط Comma در بخش Delimiters انتخاب شده است و سپس بر روی Next کلیک کنید.

وارد کردن فایل‌های CSV به اکسلحالا پنجره‌ی جدیدی باز می‌شود که در آن عملیات مهمی صورت می‌گیرد. روی دکمه‌ی Advanced کلیک کنید و خاطر جمع شوید که Decimal separator روی ممیز(.) و بخش Thousands separator خالی باشد. اگر thousands separator را تغییر ندهید اکسل ویرگول اضافه می‌کند و در صورتیکه بخواهید یک فایل خروجی کامل به فرمت CSV داشته باشید، مجبور خواهید شد که ویرگول‌ها را حذف کنید.

وارد کردن فایل‌های CSV به اکسل

حالا به گزینه‌های Column data format نگاه کنید. General به طور پیش فرض انتخاب شده و برای همه‌‌ی ستون‌ها اعمال شده است. شما می‌توانید نتیجه‌ی آن را در Data preview ببینید.

وارد کردن فایل‌های CSV به اکسل

حالت general در تشخیص متن و عدد خیلی خوب عمل می‌کند. استثنای مهم وقتی است که ستون‌های متنی از اعداد تشکیل شده باشند. صفحه‌ی پیش‌نمایش را بالا و پایین کنید تا County FIPS را ببینید. “۰۰۱” کد فدرالی استاندارد برای شناسایی Baker County در Oregon است. وقتی که اعداد به اکسل وارد می‌شوند، صفرهای اضافی آنها حذف خواهند شد. در نتیجه‌ی تنظیمات General در ستون ایالت(State) صفرها از قلم افتاده و “.۱” نشان داده می‌شود. ستون County بدون صفرها، دیگر نمی‌تواند با فایل نقشه‌‌ی Census GIS متصل(join) شود. این موضوع حفظ صفرهای ابتدایی را بسیار بااهمیت می‌کند، بنابراین روی ستون County کلیک کنید و  Column data format را به Text تغییر دهید. حالا اکسل، صفرها را هنگام وارد کردن فایل حفط می‌کند.

وارد کردن فایل‌های CSV به اکسل

شما همچنین می‌توانید یک ستون را انتخاب و آن را Skip کنید(کنار بگذارید). این امکان برای مجموعه داده‌های بسیار بزرگ ارزشمند است ولی بهتر است هر چیزی که امکان دارد را وارد(Import) کنید. روی Finish کلیک کرده و فایل را ذخیره کنید.

وارد کردن فایل‌های CSV به اکسل

 

نکات مهم در پاکسازی داده 

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

ردیف‌های خالی را حذف کنید

برای انتخاب یک ردیف روی برچسب(شماره‌)آن کلیک کیند. حالا کلید shift یا کلیدهای کنترل را برای انتخاب چند ردیف نگه دارید. سپس کلیک راست کرده و Delete را انتخاب کنید.

 سلول‌هایی که حاوی توضیحات و … هستند را پاک کنید 

کاراکترهای ویژه را حذف کنید 

کاراکترهای ویژه شامل &،!،~ و تگ‌های html می‌شوند. با استفاده از دستور Find آنها را پیدا کنید و بوسیله‌ی فرمان Replace با کارکترهای دیگر جایگزین کنید. اگر شک دارید، آنها را با خط ربط(-) یا زیرخط(_) جایگزین کنید. شما همچنین می‌توانید بخش Replace را خالی بگذارید تا کاراکترهای مورد نظرتان حذف شوند.

املای کلمه‌ها را بررسی کنید 

املای کلمات اهمیت خاص خود را دارد بخصوص زمانی که می‌خواهید داده‌هایتان را با یک فایل نقشه‌ی GIS مرتبط کنید. یک اشتباه می‌تواند همه چیز را دچار اشکال کند. به حروف بزرگ(در الفبای انگلیسی و …) و مخفف‌ها هم توجه ویژه‌ای داشته باشید تا در کار شما خللی ایجاد نشود.

در سلول‌های عددی Format را تعیین کنید 

اگر می‌خواهید داده‌ی پاکسازی شده‌ی خود را به صورت یک فایل CSV، استخراج(export) کنید باید تمام ویرگول‌ها را از سلول‌هایی که شامل عدد می‌شوند پاک کنید. اگر این کار را انجام ندهید ویرگول‌ها باعث می‌شوند که در داده‌ی شما سلول‌های اضافی ایجاد شود.

سلول‌هایی که نیاز به تغییر دارند را انتخاب کنید. در منوی Format بروید و Cells را انتخاب کنید تا پنجره‌ای جدید باز شود. روی تب Numbers کلیک کنید و در بخش Number ،Category را انتخاب کنید. تعداد اعداد اعشاری را تعیین کنید. مطمئن شوید گزینه‌ی “Use 1000 separator” انتخاب نشده است و سپس دکمه‌ی OK را فشار دهید.

کل داده را copy و در یک صفحه‌ی جدید paste کنید

ستون‌ها و ردیف‌های بی‌اهمیت را حذف کنید تا مجموعه‌ داده کوچکتر شود

داده‌های خود را با کمک توابع دست‌کاری کنید 

این مرحله را برای آخر کار نگه دارید. با این روش شما فقط روی داده‌هایی که لازم دارید عملیات مورد نیاز را انجام می‌دهید. داده‌ی حاصل را به یک صفحه‌ی جدید منتقل کنید. با استفاده از دستور Paste Special مقادیر را باقی نگه دارید و فرمول‌ها را حذف کنید.

از صفحه‌ی داده‌ها به صورت یک فایل CSV خروجی بگیرید

 

مراحل کاری پیشنهادی 
  • اگر فایل CSV را وارد می‌کنید، صفرهای پیش از اعداد را حفظ کنید
  • مطمئن بشوید که داده به درستی وارد(import) شده است و ستون‌های داده با عنوان‌هایشان تطابق دارد
  • فورا یک نسخه کپی از داده‌هایتان ذخیره کنید
  • ردیف‌های خالی را حذف کنید
  • عنوان ستون‌ها را در یک ردیف قرار دهید
  • تمام ستون‌های عددی را طوری format کنید که ویرگول‌ها حذف شوند
  • کاراکترهای ویژه(&, ! , ~ و …) را پیدا و جایگزین کنید. اگر شک دارید به جای آنها space، زیر خط(_) یا خط پیوند(-) قرار دهید
  • املای کلمات را بررسی کنید.(بخصوص در ستون‌هایی که قصد دارید با فایل‌های دیگر مرتبط کنید)
  • حروف بزرگ(در الفبای انگلیسی و …) را بررسی کنید(بخصوص در ستون‌هایی که قصد دارید با فایل‌های دیگر مرتبط کنید)
  • کلمات اختصاری(مخفف) را بررسی کنید(بخصوص در ستون‌هایی که قصد دارید با فایل‌های دیگر مرتبط کنید)
  • حاصل‌جمع‌ها را از داده‌‌های اصلی(حداقل به اندازه‌ی یک ردیف یا ستون) جدا نگه دارید تا بتوانید صحت آنها را مجددا بررسی کنید. این کار باعث می‌شود تا داده‌هایتان دست‌نخورده باقی بماند.
  • در آخر دستکاری در ستون‌ها را انجام دهید.
  • آنچه نیاز ندارید را حذف کنید.

 

فرمول‌های دم‌دستی
تابع
عملکرد
فرمول
گرفتن داده یا اعمال تابع روی داده‌ای در صفحه‌ی متفاوت Sheet1!A1=
اعمال یک تابع بر روی یک ستون(نگه داشتن Shift و همزمان انتخاب سلول‌ها – تابع باید در بالاترین سلول قرار گرفته باشد) Control-d
AVERAGE محاسبه‌ی میانگین (AVERAGE(A2:A10=
MEDIAN محاسبه‌ی میانه (MEDIAN(A2:A10=
محاسبه‌ی درصد(A2 عدد کوچکتر است) A2/B2*100=
محاسبه‌ی تغییر درصدی(B2 عدد جدید و A2 عدد قدیمی است) B2/A2)-1)*100))=
EXACT بررسی یکسان بودن مقادیر سلول‌ها (EXACT(A2:A10=
COUNTIF شمارش تعداد سلول‌هایی که مطابق شرایط داده شده باشند(برابر بودن با یک عدد یا متن، در یک محدوده قرار گرفتن) (COUNTIF(A2:A10, 705=
TRIM حذف space از ابتدا و انتهای متن (TRIM(A2:A10=
CLEAN حذف تمامی کاراکترهای غیرقابل‌چاپ از متن (CLEAN(A2:A10=
CONCATENATE اتصال دو سلول متنی به هم و تبدیل آنها به یک سلول متنی (با قرار دادن ” “& بعد از A2 می‌توانید یک فاصله بین دو متن ایجاد می‌کند) (CONCATENATE(A2, B2=
LOWER تبدیل همه‌ی حروف متن به حروف کوچک (LOWER(A2:A10=
UPPER تبدیل همه‌ی حروف متن به حروف بزرگ (UPPER(A2:A10=
PROPER تبدیل حرف اول متن به حروف بزرگ (PROPER(A2:A10=
FIXED گرد کردن اعداد اعشاری(اگر پارامتر دوم منفی باشد دهگان و صدگان و … گرد می‌شود) و ارائه‌ی آن به صورت مقدار متنی (FIXED(E2,1=
MID جدا کردن یک یا چند کاراکتر(از مکان مشخص شده) از یک مقدار متنی (MID(A2, 1,5=
RIGHT جدا کردن یک یا چند کاراکتر از سمت راست یک مقدار متنی (RIGHT(A2,5=
LEFT جدا کردن یک یا چند کاراکتر از سمت چپ یک مقدار متنی (LEFT(A2,4=
REPLACE حذف قسمتی از یک متن و جایگزین یا اضافه کردن متن جدید (“REPLACE(A2,6,5,”4=
SUBSTITUTE پیدا کردن بخشی از متن سلول و جایگزین کردن آن با متن جدید (“SUBSITU(A2,”Ore”,”OR=

 

 

 

 

 

 

 

 

منبع: Berkeley Advanced Media Institute

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *