پث‌وایز

آموزش اکسل و گوگل شیتدرس ۷ از ۱۲

تمیز کردن داده‌های نامرتب توی چند دقیقه

حذف فاصله‌های اضافه، جدا کردن نام و نام خانوادگی، چسبوندن دوباره‌ی ستون‌ها به هم و خلاص شدن از ردیف‌های تکراری.

چیزی که از این درس می‌بری

  • TRIM فاصله‌های اضافه رو پاک می‌کنه؛ شایع‌ترین دلیلی که تطابق‌ها پیدا نمی‌شن.
  • توی یه ستون کمکی تمیزکاری کن، به صورت مقدار پیست کن، بعد ستون کمکی رو پاک کن.
  • Text to Columns یا SPLIT یه ستون رو جدا می‌کنه؛ & و TEXTJOIN ستون‌ها رو به هم می‌چسبونن.
فهرست این درس۶
  1. TRIM مشکل نامرئی رو حذف می‌کنه
  2. تمیز کردن یه ستون بدون خرابکاری
  3. یه ستون به چند تا ستون
  4. چند تا ستون به یه ستون
  5. حذف ردیف‌های تکراری
  6. مرور درس

۰۱فاصله‌ها

TRIM مشکل نامرئی رو حذف می‌کنه

فرمول =TRIM(A2) فاصله‌های اول و آخر متن رو پاک می‌کنه و فاصله‌های دوتایی وسط متن رو هم یکی می‌کنه. مثلاً « سارا احمدی » تبدیل می‌شه به «سارا احمدی». فاصله‌های اضافه دلیل شماره‌ی ۱ هستن که SUMIF یا توابع جستجو، ردیف‌هایی رو که در ظاهر عین هم هستن پیدا نکنن.

۰۲قدم‌به‌قدم

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

  1. یه ستون کمکی اضافه کن

    کنار ستون نامرتب A، توی سلول B2 بنویس =TRIM(A2) و تا پایین ستون ادامه‌ش بده.

  2. ستون تمیز رو کپی کن

    ستون B رو انتخاب و کپی‌ش کن.

  3. به صورت مقدار پیست کن

    روی ستون A با Paste Special → Values only پیست کن (توی گوگل شیت با Ctrl+Shift+V). حالا ستون A متن تمیز داره، نه فرمول.

  4. ستون کمکی رو پاک کن

    دیگه به ستون B نیازی نداری.

خودت رو امتحان کنسؤال ۱ از ۴

ندا بعد از نوشتن =TRIM(A2) توی ستون B، می‌تونه ستون A رو فوراً پاک کنه و اسم‌های تمیز رو نگه داره.

  1. درست
  2. نادرست
جواب رو ببین

نادرست

غلطه. فرمول‌های ستون B هنوز دارن از ستون A می‌خونن، پس اگه A رو پاک کنی همه‌شون خطای #REF! می‌شن. اول B رو به صورت مقدار پیست کن، بعد A رو پاک کن.

۰۳جدا کردن متن

یه ستون به چند تا ستون

«سارا احمدی» توی یه سلوله و نام و نام خانوادگی رو جدا از هم می‌خوای؟ توی اکسل: Data → Text to Columns، گزینه‌ی Delimited رو انتخاب کن و تیک Space رو بزن. توی گوگل شیت: فرمول =SPLIT(A2, " ") بخش‌ها رو توی سلول‌های سمت راست پخش می‌کنه. همین روش با ویرگول برای «Tehran, Iran» هم جواب می‌ده.

۰۴چسبوندن متن

چند تا ستون به یه ستون

علامت & متن‌ها رو به هم می‌چسبونه. فرمول =B2 & " " & C2 نام، یه فاصله و نام خانوادگی رو کنار هم می‌ذاره. تابع TEXTJOIN هم همین کار رو روی یه محدوده‌ی کامل با جداکننده‌ای که خودت انتخاب می‌کنی انجام می‌ده: =TEXTJOIN(", ", TRUE, B2:D2).

خودت رو امتحان کنسؤال ۲ از ۴

شهر توی B2 و کشور توی C2 قرار داره. بابک می‌خواد توی D2 به «Shiraz, Iran» برسه. کدوم فرمول؟

  1. =B2+C2
  2. =B2 & C2
  3. =B2 & ", " & C2
  4. =SUM(B2, ", ", C2)
جواب رو ببین

=B2 & ", " & C2

درسته. & متن‌ها رو به هم می‌چسبونه، و ویرگول و فاصله هم بین دو تا سلول داخل گیومه قرار می‌گیرن.

۰۵تکراری‌ها

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

اگه یه مشتری ۲ بار وارد بشه، همه‌ی آمارها باد می‌کنه! توی اکسل: Data → Remove Duplicates، بعد تیک ستون‌هایی که تکراری بودن رو مشخص می‌کنن (مثلاً شماره تلفن) رو بزن. توی گوگل شیت: Data → Data cleanup → Remove duplicates. دفعه‌ی اول روی یه کپی از شیت کار کن.

خودت رو امتحان کنسؤال ۳ از ۴

هر مشکل رو به راه‌حلش وصل کن

  • « علی رضایی »
  • «علی رضایی» باید بشه ۲ تا ستون
  • نام و نام خانوادگی باید بشن یه ستون
  • یه سفارش ۳ بار تکرار شده

& یا TEXTJOINRemove duplicatesTRIMText to Columns یا SPLIT

جواب رو ببین
  • « علی رضایی » → TRIM
  • «علی رضایی» باید بشه ۲ تا ستون → Text to Columns یا SPLIT
  • نام و نام خانوادگی باید بشن یه ستون → & یا TEXTJOIN
  • یه سفارش ۳ بار تکرار شده → Remove duplicates

خودت رو امتحان کنسؤال ۴ از ۴

فرمول COUNTIF مهسا می‌گه ۴۰ مشتری از تهران هستن، اما فیلتر ۵۲ نفر رو نشون می‌ده. اول از همه باید چی‌کار کنه؟

  1. به‌جاش از SUMIF استفاده کنه
  2. ستون شهر رو با TRIM تمیز کنه، به صورت مقدار پیست کنه، بعد دوباره بشماره
  3. لیست رو بر اساس شهر مرتب کنه
  4. فرمول COUNTIF رو از اول بنویسه
جواب رو ببین

ستون شهر رو با TRIM تمیز کنه، به صورت مقدار پیست کنه، بعد دوباره بشماره

درسته. احتمالاً توی بعضی سلول‌ها «Tehran » یا « Tehran» نوشته شده. فیلتر این‌ها رو چشمی گروه‌بندی می‌کنه، ولی COUNTIF تطابق دقیق می‌خواد. با TRIM کردن، شمارش درست می‌شه.

۰۶جمع‌بندی

مرور درس

  • TRIM فاصله‌های اضافه رو پاک می‌کنه؛ شایع‌ترین دلیلی که تطابق‌ها پیدا نمی‌شن.
  • توی یه ستون کمکی تمیزکاری کن، به صورت مقدار پیست کن، بعد ستون کمکی رو پاک کن.
  • Text to Columns یا SPLIT یه ستون رو جدا می‌کنه؛ & و TEXTJOIN ستون‌ها رو به هم می‌چسبونن.
  • ردیف‌های تکراری رو روی یه کپی حذف کن و ستون‌هایی رو انتخاب کن که ملاک تکرار هستن.

به سبک خودت یادش بگیر.

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

Cafe Bazaar Myket توی اپ شخصی‌سازیش کن