Data Validation در اکسل

ابزار Data Validation در اکسل یکی از پر کاربرد ترین ابزار ها در فرمول نویسی ها، Formula Sheet ها، نرم افزار های محاسباتی نوشته شده توسط اکسل و… میباشد. توسط ابزار Data Validation در اکسل شما میتوانید کاربر را طوری محدود کنید که فقط داده های مورد نظر شما را در سلول های تعیین شده وارد نماید، به عنوان مثال میتوانید کاربر را محدود کنید در سلولی فقط بتواند عددی زوج وارد کند، یا اینکه فقط اعداد بزرگتر از ۱۳ قابل وارد شدن در سلول باشند یا اینکه کاربر فقط یکی از گزینه های مورد نظر شما را بتواند در سلول بنویسد و بسیاری محدودیت های دیگر که در ادامه با آنها و روش اعمال آنها آشنا میشویم.

Data Validation در اکسل

ابزار Data Validation در اکسل

بحث ایجاد محدودیت برای وارد کردن اطلاعات در سلول ها از طریق ابزار Data Validation در اکسل را میتوان به صورت خلاصه به شکل نمودار زیر نشان داد:

Data Validation در اکسل

محدودیت ها در Data Validation در اکسل

برای استفاده از ابزار Data Validation ابتدا سلول مورد نظر را انتخاب نمایید و سپس از تب Data و در گروه Data Tools بر روی دکمه Data Validation کلیک کنید، شکل زیر مسیر دسترسی به ابزار Data Validation در اکسل را مشخص میکند:

ابزار Data Validation در اکسل

گزینه ی اول (Data Validation…) پنجره Data Validation را باز میکند که برای ایجاد محدودیت ها توسط این ابزار به کار برده میشود. با کلیک بر روی گزینه ی دوم (Circle Invalid Data) سلول هایی از صفحه اکسل که ابزار Data Validation در مورد آنها به کار رفته و محتوی داده ای نا معتبر میباشد مشخص میشوند. برای مشخص کردن سلول های دارای داده ی نا معتبر،  دور آن ها خطی قرمز ترسیم میگردد که با کلیک بر روی گزینه ی سوم (Clear Validation Circle) خطوط رسم شده پاک میشوند. پنجره Data Validation در شکی زیر نشان داده شده است:

پنجره Data Validation در اکسل

در تب اول پنجره Data Validation در اکسل میتوان تنظیمات مربوط به Validation Criteria را انجام داد، در کادر Allow ابتدا نوع داده یا نوع محدودیت ورود داده به سلول (داده معتبر) را تعیین مینماییم، داده های معتبر را به سه دسته تقسیم کرده ایم، دسته اول شامل گزینه های Whole Number (عدد)، Decimal (عدد علمی)، Date و  Time (تاریخ و زمان) و Text Length (طول رشته متنی) میباشد. دسته دوم گزینه List و دسته سوم Custom است.

به عنوان مثال، اگر گزینه ی Whole Number را انتخاب کنیم، به این معنا است که تنها داده های عددی (اعداد) به عنوان محتوای سلول مورد نظر معتبر میباشند.

Validation Criteria

در کادر دوم (Data) جزییات مربوط به گزینه ی انتخاب شده در کادر Allow تعیین میگردد، جزییات مربوط به گزینه های قرار گرفته در دسته اول (شامل Whole Number، Decimal، Date، Time و Text Length) یکسان میباشد زیرا همگی ماهیتی عددی دارند. در مثال بالا با فرض تعیین Whole Number یا هریک از گزینه های دسته اول در کادر Allow، در این قسمت اعداد و محدوده های مجاز برای ورود به سلول را به ترتیب زیر تعیین میکنیم:

  • اگر میخواهیم اعداد بین دو عدد خاص معتبر باشند گزینه ی Between،
  • برای معتبر بودن اعداد خارج از بازه دو عدد خاص گزینه ی Not Between،
  • برای معتبر بودن تنها یک عدد خاص گزینه ی Equal to،
  • برای معتبر بودن اعدادی غیر از یک عدد خاص گزینه ی Not Equal to،
  • برای معتبر بودن اعداد بزرگتر از یک عدد خاص گزینه ی Greater Than،
  • برای معتبر بودن اعداد کوچکتر از یک عدد خاص گزینه ی Less Than،
  • برای معتبر بودن اعداد بزرگتر مساوی یک عدد خاص گزینه ی Greater than or Equal to،
  • و در نهایت برای معتبر بودن اعداد کوچکتر مساوی یک عدد خاص گزینه ی Less than or Equal to.

اگر میخواهید سلول مورد نظر در صورت خالی بودن هم مورد بررسی قرار بگیرد تیک گزینه ی Ignore Blank را بردارید.

Data Validation در اکسل

گزینه ی لیست (List) در کادر Allow، در صورتی که بخواهید کاربر تنها داده هایی خاص که شما تعیین میکنید را بتواند در سلول وارد کند کاربرد دارد، با انتخاب گزینه ی List، کادری به عنوان Source نشان داده میشود که میتوانید لیست داده های دلخواه که در سلول های اکسل نوشته شده است را انتخاب کنید همچنین با تیک زدن گزینه In-Cell Dropdown با انتخاب سلول مورد نظر توسط کاربر، لیست تعیین شده نشان داده میشود که کاربر میتواند گزینه ی دلخواه را از میان آنها انتخاب کند.

تعیین محدودیت دلخواه در Data Validation

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

نمونه فایل اکسل برای تمرین Data Validation در اکسل را با کلیک بر روی این خط دانلود نمایید.

برای اینکار پس از انتخاب سلول مورد نظر از پنجره Data Validation و در قسمت Allow گزینه ی Custom را انتخاب نمایید. همانند آنچه در Conditional Formatting و ایجاد قوانین دلخواه دیدیم، در اینجا نیز فرمول نوشته شده حتماً باید خروجی Boolean داشته باشد یعنی خروجی فرمول حتماً باید True یا False باشد. بنابراین توابع Is که جز توابع اطلاعات (Information Functions) میباشند در این مقوله بسیار کاربرد دارند از طرفی توابع منطقی (Logical Functions) مانند توابع IF، AND و OR بسیار مهم و کاربردی میباشند. بنابراین توصیه میگردد حتماً مقالات مربوط به این توابع را مطالعه نمایید.

مثلاً برای اینکه سلول A2 را محدود به پذیرش تنها اعداد زوج نماییم میتوانیم پس از انتخاب Custom در قسمت فرمول، فرمول زیر را بنویسیم، به این ترتیب کاربران تنها میتوانند اعداد زوج را در سلول A2 بنویسند:

=ISEVEN(A2)

به همین ترتیب میتوان از سایر توابع و فرمول ها برای ایجاد محدودیت استفاده نمود، حتی میتوان داده های ورودی به یک سلول را طوری محدود کرد که خروجی سلول دیگری که سلول اول به عنوان مرجع (Reference) در آن استفاده شده به عنوان ملاک معتبر بودن تعیین گردد. در فایل نمونه مثال هایی در این مورد آورده شده است.

نمونه فایل اکسل برای تمرین Data Validation در اکسل را با کلیک بر روی این خط دانلود نمایید.

به علت طولانی شدن مطلب، توضیح و روش تعیین Input Message و انواع Error Alert در مقاله ی بعدی مورد بررسی قرار میگیرد.

اشتراک گذاری در شبکه های اجتماعی: share on facebook share on google plus share on linkedin

۴ نظر

  1. نادر

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

    1. حامد قدیمی

      سلام، برای این کار شما میتوانید از Control ها استفاده کنید، کنترل Check Box برای شما مناسب میباشد. در تب Developer، در گروه Controls از لیست Form Controls گزینه ی Check Box را انتخاب کنید. موفق باشید.

  2. محمدرضا

    سلام.خسته نباشید.اقا من ی لیست طولانی از شماره تلفن دارم برا.میخوام ی محمدویتی ایجاد کنم ک شماره تکراری توش حذف بشه یا ثبت نشه.از هرکدوم ی مدل داشته باشم چکارش کنم؟

    1. حامد قدیمی

      سلام، متشکرم، برای دیدن رکورد های تکراری میتوانید از ابزار Conditional Formatting استفاده نمایید. اینکه داده ی تکراری قابل درج شدن نباشد در اکسل قابل اجراست و باید از Data Validation استفاده شود ولی نیاز به فرمول نویسی حرفه ای دارد. موفق باشید.

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