جلوی داده‌ی غلط را در ورودی بگیرید
اصلاح داده‌ی خراب همیشه گران‌تر از جلوگیری از ورودش است. Data ← Data Validation برای هر سلول قاعده‌ای تعیین می‌کند: فقط عدد در بازه‌ی مشخص، فقط تاریخ این سال، فقط یکی از گزینه‌های یک لیست، یا هر شرطی که با فرمول بنویسید.
سه تب پنجره
| تب | کاربرد |
|---|---|
| Settings | نوع قاعده: Whole number، Decimal، List، Date، Time، Text length، Custom |
| Input Message | راهنمایی که هنگام انتخاب سلول ظاهر می‌شود؛ جای مناسب برای «مبلغ به تومان وارد شود» |
| Error Alert | Stop (رد کامل)، Warning (اجازه با تأیید) یا Information (فقط اطلاع) |
لیست کشویی از یک منبع پویا
منبع لیست را هرگز با تایپ دستی گزینه‌ها («تهران,اصفهان,کاشان») نسازید مگر برای لیست‌های کوچک و ثابت. بهتر است لیست در یک جدول باشد تا با اضافه شدن گزینه، کشویی‌ها هم به‌روز شوند:
از ستون جدول: =INDIRECT("Provinces[Name]")
از spill مرتب و یکتا: =$K$2# (K2 حاوی =SORT(UNIQUE(Sales[Rep])))لیست وابسته: استان ← شهر
روش کلاسیک (همه‌ی نسخه‌ها): برای هر استان یک Named Range هم‌نام بسازید (مثلاً محدوده‌ی شهرهای اصفهان را «اصفهان» بنامید) و منبع ستون شهر را =INDIRECT($A2) بگذارید. نام نمی‌تواند فاصله داشته باشد، پس برای «خراسان رضوی» از =INDIRECT(SUBSTITUTE($A2," ","_")) و نام «خراسان_رضوی» استفاده کنید.
روش یک‌جدولی: همه‌ی شهرها را در یک جدول دوستونی (استان، شهر) و مرتب بر اساس استان نگه دارید، سپس منبع را با OFFSET بسازید. افزودن شهر جدید فقط افزودن یک سطر است:
منبع لیست ستون شهر (B2)، با فرض شیت Cities، ستون A=استان و B=شهر:
=OFFSET(Cities!$B$1, MATCH($A2, Cities!$A:$A, 0)-1, 0, COUNTIF(Cities!$A:$A, $A2), 1)قاعده‌های Custom با فرمول
کد ملی: ده رقم =AND(LEN(A2)=10, ISNUMBER(--A2))
موبایل: یازده رقم و با 09 =AND(LEN(B2)=11, LEFT(B2,2)="09", ISNUMBER(--B2))
شماره فاکتور تکراری نباشد =COUNTIF($C:$C, C2)=1
تاریخ در سال جاری میلادی =YEAR(D2)=YEAR(TODAY())
مبلغ مضرب 1000 تومان =MOD(E2, 1000)=0فرمول Custom نسبت به سلول بالا-چپ محدوده‌ی انتخاب‌شده نوشته می‌شود و مثل فرمول‌های معمولی برای بقیه‌ی سلول‌ها نسبی تفسیر می‌شود. ستون کد ملی و موبایل را قبلاً Text کرده باشید تا صفر اول حذف نشود.
نکته‌هایی که کمتر کسی می‌داند
- Data Validation جلوی Paste را نمی‌گیرد: کاربری که داده‌ی دیگری را روی سلول بچسباند، هم داده‌ی غلط را وارد و هم قاعده را پاک می‌کند. برای پیدا کردن تخلف‌ها، Data Validation ← Circle Invalid Data را بزنید.
- F5 ← Special ← Data validation ← All همه‌ی سلول‌های دارای قاعده را پیدا می‌کند؛ گزینه‌ی Same سلول‌هایی با همان قاعده‌ی سلول فعال را.
- لیستی که مستقیم در کادر Source تایپ شود حداکثر ۲۵۵ کاراکتر است و جداکننده‌اش از تنظیمات منطقه‌ای ویندوز می‌آید (در برخی تنظیمات «;» است نه «,»).
- برای تغییر قاعده در همه‌ی سلول‌های مشابه، تیک «Apply these changes to all other cells with the same settings» را در پایین تب Settings بزنید.
- اگر لیست منبع «ی» عربی داشته باشد و کاربر با کیبورد فارسی تایپ کند، مقدار تایپ‌شده رد می‌شود؛ منبع لیست را هم پاک‌سازی کنید.