اعتبار سنجی داده(Data Validation)

در این مطلب قصد داریم با کاربردهای Data Validation در Excel آشنا شویم. این قابلیت در Excel همانند تصویر زیر در Ribbon در منوی Data و در گروه Data Tool مستقر است:

Image

به طور خلاصه میتوان کاربردهای Data Validation را بصورت زیر عنوان کرد:

    • ایجاد لیستهای کشوئی یا Drop-Down که به واسطه آن کاربران داده های خود را سریعتر، دقیقتر و با اشتباه کمتر وارد می نمایند، در ضمن به واسطه آن کاربران ملزم به انتخاب یک داده مشخص و از پیش تعیین شده در لیست می باشند.
    • ایجاد محدودیت برای ورود داده در سلولها بر اساس شروط مختلفی از قبیل (کوچکتر یا بزرگتر بودن از مقدار خاصی، دقیقاً برابر بودن با مقداری خاص، مابین دو مقدار خاص بودن و …)
    • ایجاد راهنما برای ورود داده ها در سلولها
    • نمایش پیغام خطا هنگام رعایت نکردن شروط مذکور و …

ایجاد لیستهای کشوئی یا Drop-Down:
برای تفیهم بهتر موضوع، کاربرگ یا Sheet زیر را در نظر بگیرید:

Image

در کاربرگ مذکور، ستونهائی از قبیل محل تولد، مدرک و رشته تحصیلی میتوانند مواردی از پیش تعیین شده و مشخص باشند که برای آنها از لیستهای کشوئی یا Drop-Down List استفاده می نمائیم. بدین منظور ابتدا می بایست داده هایی که در هر لیست هستند در ستونی جداگانه از کاربرگ جاری و یا در کاربرگی دیگر درج گردند. به عنوان نمونه همانند تصویر زیر، لیست محل تولد را که دربرگیرنده نام استانهای کشور است در کاربرگی دیگر ایجاد میکنیم:

Image

سپس همانند تصویر زیر در ستونِ محل تولد از سلول D2 به سمت پایین تعدادی سلولهای مورد نظر را برای ایجاد لیست انتخاب می کنیم و بر روی گزینه Data Validation کلیک می کنیم:

Image

در ویزارد Data Validation در تب Setting همانگونه که ملاحظه می نمائید در قسمت Allow گزینه Any value بصورت پیش فرض انتخاب شده است یعنی ورود هر داده ای در سلولهای انتخاب شده مجاز می باشد.
حال برای ایجاد لیست کشوئی در قسمت Allow همانند تصویر زیر گزینه list را انتخاب می کنیم، با انجام این کار فیلدی با عنوان Source نمایان می شود که برای آدرس دهی به همان لیست ایجاد شده از قبل (استانهای ذکر شده برای محل تولد) می بایست مورد استفاده قرار گیرد، برای این منظور شما می توانید آدرس دادهها را در کادر تایپ نمائید و یا با استفاده از آیکن مشخص شده در کنار کادر، با استفاده از اشاره گر موس ناحیه داده ها را به حالت انتخاب درآورده و طبق تصویر زیرین بر روی آیکن مشخص شده کلیک کنید:

Image

 

Image

با انجام این کار آدرس ناحیه انتخاب شده همانند تصویر در کادر Source قرار می گیرد:

Image

حال بر روی OK کلیک کلیک کنید، زین پس طبق تصویر زیر در کاربرگ خود و در ستون محل تولد، در کنار هر سلول شکل مثلثی ظاهر می گردد که با کلیک کردن بر روی آن، قادر به انتخاب یک استان از لیست کشوئی می باشید:

Image

 


گزینه In-cell DropDown :
حال در ستون مدرک نیز مطابق موارد مذکور در بالا، لیستی شامل موارد (زیر دیپلم-دیپلم-کاردانی-کارشناسی-ارشد-دکتری) را ایجاد می کنیم، اما این بار تیک گزینه In-cell DropDown را بر میداریم:

Image

با انجام این کار طبق تصویر زیر، شکل مثلث جهت باز شدن لیست کشوئی نمایش داده نمیشود و شما می بایست مقدار مورد نظر خود را تایپ نمائید. حال اگر مقدار وارد شده با یکی از موارد موجود در لیست مطابقت داشته باشد، داده ثبت می شود، اما اگر در کوچکترین حرفی مطابقت نداشته باشد، خطای (The value you entered is not valid ) ظاهر می گردد:

Image

نکته: پس از انجام تنظیمات فوق، در حالی که سلولهای مورد نظر در حالت انتخاب قرار دارند، با کلیک کردن مجدد بر روی گزینه Data Validation در Ribbon قادر به تغییر تنظیمات مذکور خواهید بود، به عنوان نمونه با فعال کردن گزینه In-cell DropDown در سلولهای ستون مدرک، مجدداً لیست کشوئی برای این سلولها همانند تصویر زیر نمایان خواهد شد:

Image

 


حال فرض کنید، ستونی با عنوان «کد ملی» دارید که می بایست در هر سلول دقیقاً 10 رقم جهت کاهش خطا درج گردد، بدین منظور همانند تصویر زیر ابتدای سلولهای مورد نظر در ستون کد ملی را انتخاب میکنیم و پس از انتخاب گزینه Data Validation از منوی Data، در تب Setting ، گزینه Text Length را در قسمت Allow، گزینه Equal To را در قسمت Data و مقدار 10 را در قسمت Length تنظیم مینمائیم:

Image

پس از انجام تنظیمات فوق، در هریک از سلولهای ستون کد ملی، صرفاً داده ای با طول 10 کاراکتر می بایست وارد شود در غیر این صورت پیغام خطائی ظاهر می گردد:

Image

 


تب Input Message:
حال برای جلوگیری از بروز خطا در وارد کردن اطلاعات میتوانید با استفاده از تب Input Message راهنمائی لازم را انجام دهید به عنوان نمونه همانند تصویر زیر برای ستون کد ملی پیام زیر را در نظر میگیریم که با انتخاب هر یک از سلولهای این ستون ظاهر میگردد:

Image

 

Image


تب Error Alert:
در ضمن پیغام خطای ظاهر شده در صورت عدم رعایت شرایط را نیز می توانید تنظیم نمائید، برای این منظور از تب Error Alert همانند تصویر زیر استفاده می کنیم:

Image

 

Image

 


Circle Data Validation:
حال فرض کنید در برخی از ستونها از قبل داده هایی وارد شده داشته باشیم و پس از آن نسبت به اعمال Data Validation اقدام کنیم، به عنوان نمونه همانند تصویر زیر در ستون «رشته تحصیلی» از قبل داده هایی وارد می کنیم و سپس نسبت به اعمال محدودیت تعداد کاراکترهای مجاز بین 6 تا 20 اقدام میکنیم:

Image

همانگونه که ملاحظه می نمائید برخی از سلولها دارای شرط مذکور نمی باشند، برای مشخص شدن این سلولها کافیست طبق تصویر زیر گزینه Circle Data Validation را انتخاب کنید، با انجام این کار سلولهای فاقد شرط ذکر شده، با کادری قرمز رنگ مشخص میگردند:

Image

 

Image

حال اگر طبق تصویر زیر بر روی گزینه Clear Validation Circle کلیک کنید، این کادرها حذف خواهد گردید.

Image

علاوه بر موارد مذکور در این آموزش، همانند موارد مذکور، از طریق Data Validation قادر به ایجاد محدودیتهای دیگری در داده های ورودی در سلولهای اکسل خواهید بود از جمله؛ ایجاد بازه های عددی، زمان و تاریخ:

Image

نویسنده: محمود کیاستی
منبع: انجمن تخصصی فناوری اطلاعات ایران

همچنین ببینید

ثابت کردن(FREEZE) سطرها و ستون ها در اکسل

در این مطلب میخوایم نحوه ثابت کردن سطرها و ستون ها رو در اکسل یاد …

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

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