پث‌وایز

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

لیست کشویی و فرمت شرطی در اکسل

با لیست کشویی از دم در جلوی ورود داده‌های غلط رو بگیر، و با قانون‌های رنگی که خودشون به‌روز می‌شن کاری کن خطاها به چشم بیان.

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

  • لیست کشویی Data Validation جلوی غلط‌های تایپی رو می‌گیره و دسته‌بندی‌ها رو یکدست نگه میداره.
  • گزینه‌های لیست کشویی رو توی شیت خودشون بذار و قانون رو به اون محدوده وصل کن.
  • فرمت شرطی (Conditional Formatting) وقتی شرط برقرار باشه سلول‌ها رو رنگی می‌کنه و خودش به‌روز می‌شه.
فهرست این درس۵
  1. بذار انتخاب کنن، نه اینکه تایپ کنن
  2. ساخت لیست کشویی دسته‌بندی‌ها
  3. فرمت شرطی (Conditional Formatting)
  4. رنگی کردن کل ردیف با فرمول شرطی
  5. مرور درس

۰۱لیست کشویی

بذار انتخاب کنن، نه اینکه تایپ کنن

ابزار اعتبارسنجی داده (Data Validation) ورودی‌های مجاز سلول رو محدود می‌کنه. کاربردی‌ترین حالتش لیست کشوییه: Food، Transport، Rent، Bills. نه غلط املایی پیش میاد، نه «food » با فاصله اضافی، تا SUMIFها و پیوت‌تیبل‌هات کاملاً دقیق بمونن. توی اکسل: Data → Data Validation → Allow: List. توی گوگل شیت: Data → Data validation → Dropdown.

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

ساخت لیست کشویی دسته‌بندی‌ها

  1. یه بار گزینه‌ها رو بنویس

    توی یه شیت به اسم Lists، دسته‌بندی‌ها رو توی A2:A8 تایپ کن.

  2. سلول‌هایی رو که می‌خوای کنترل کنی انتخاب کن

    مثلاً کل ستون دسته‌بندی C2:C500.

  3. قانون رو به لیستت وصل کن

    گزینه List (توی اکسل) یا Dropdown from a range (توی گوگل شیت) رو انتخاب کن و =Lists!$A$2:$A$8 رو وارد کن.

  4. تکلیف ورودی‌های اشتباه رو مشخص کن

    تنظیم کن هر چیزی خارج از این لیست رو رد کنه تا هیچ اشتباهی ذخیره نشه.

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

۵ نفر شیت هزینه‌های احسان رو پر می‌کنن و دسته‌بندی‌ها مدام به شکل «Food»، «food»، «Foods» و «Groceries» ثبت می‌شن. چی بیشتر از همه کمک میکنه؟

  1. از همه بخواد که بیشتر دقت کنن
  2. هر هفته روی این ستون تابع TRIM رو اجرا کنه
  3. یه لیست کشویی که فقط دسته‌بندی‌های توافق‌شده رو مجاز بدونه
  4. ستون رو بر اساس حروف الفبا مرتب کنه
جواب رو ببین

یه لیست کشویی که فقط دسته‌بندی‌های توافق‌شده رو مجاز بدونه

درسته. لیست کشویی کلاً امکان تایپ متفاوت رو می‌گیره. تمیزکاری بعد از ورود داده هم مفیده، ولی پیشگیری کم‌خرج‌تره.

۰۳قانون‌های رنگی

فرمت شرطی (Conditional Formatting)

قانون فرمت شرطی (Conditional Formatting) وقتی یه شرط درست باشه سلول رو رنگی میکنه، و با هر بار تغییر داده خودش دوباره چک می‌کنه. می‌تونی مبالغ بالاتر از بودجه، تاریخ‌های گذشته یا داده‌های تکراری رو رنگی کنی. توی اکسل: Home → Conditional Formatting. توی گوگل شیت: Format → Conditional formatting.

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

هر هدف رو به قانون مناسبش وصل کن

  • پیدا کردن شماره‌فاکتور تکراری
  • علامت‌گذاری هزینه‌های بالای ۵،۰۰۰،۰۰۰
  • نمایش کارهای عقب‌افتاده
  • دیدن بزرگی اعداد در یه نگاه

Cell value greater than 5000000Colour scale or data barsDate is before todayHighlight duplicate values

جواب رو ببین
  • پیدا کردن شماره‌فاکتور تکراری → Highlight duplicate values
  • علامت‌گذاری هزینه‌های بالای ۵،۰۰۰،۰۰۰ → Cell value greater than 5000000
  • نمایش کارهای عقب‌افتاده → Date is before today
  • دیدن بزرگی اعداد در یه نگاه → Colour scale or data bars

۰۴کل ردیف

رنگی کردن کل ردیف با فرمول شرطی

گزینه Use a formula (توی اکسل) یا Custom formula is (توی گوگل شیت) رو انتخاب کن، کل جدول رو انتخاب کن، و فرمول ردیف اول رو با قفل کردن ستون بنویس: =$E2="Unpaid". علامت $ قبل از E باعث می‌شه همه سلول‌های ردیف به ستون E نگاه کنن، و چون شماره ردیف حرکت میکنه، کل ردیف‌هایی که وضعیتشون Unpaid باشه قرمز می‌شن.

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

توی فرمت شرطی برای محدوده A2:F200، فرمول =E2="Unpaid" (بدون علامت دلار) کل ردیف‌ها رو درست رنگی می‌کنه.

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

نادرست

غلطه. بدون $E، شرط ستون‌ها رو هم جابه‌جا می‌کنه: سلول B2 می‌ره F2 رو چک میکنه، C2 می‌ره G2 رو و رنگ‌ها جای اشتباه میفتن. فرمول =$E2="Unpaid" ستون رو قفل می‌کنه و می‌ذاره ردیف حرکت کنه.

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

رویا می‌خواد هر وقت موعد کاری در ستون D گذشت، کل ردیف اون کار قرمز بشه. برای محدوده A2:F100 از کدوم فرمول سفارشی باید استفاده کنه؟

  1. =D2<TODAY()
  2. =$D2<TODAY()
  3. =$D$2<TODAY()
  4. =TODAY()<$D2
جواب رو ببین

=$D2<TODAY()

درسته. $D ستون ددلاین رو برای همه سلول‌ها قفل نگه میداره، ردیف حرکت میکنه، و «کوچک‌تر از امروز» هم یعنی موعدش گذشته.

۰۵جمع‌بندی

مرور درس

  • لیست کشویی Data Validation جلوی غلط‌های تایپی رو می‌گیره و دسته‌بندی‌ها رو یکدست نگه میداره.
  • گزینه‌های لیست کشویی رو توی شیت خودشون بذار و قانون رو به اون محدوده وصل کن.
  • فرمت شرطی (Conditional Formatting) وقتی شرط برقرار باشه سلول‌ها رو رنگی می‌کنه و خودش به‌روز می‌شه.
  • برای رنگی کردن کل ردیف، از فرمول با ستون قفل‌شده استفاده کن: =$E2="Unpaid".

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

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

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