معرفی کاربردهای Data Validation در اکسل : اعتبارسنجی داده Excel
کاربرد Data Vlidation یا اعتبارسنجی داده ها در اکسل چیست؟ سلام، در این مطلب قصد داریم با کاربردهای Data Validation در Excel آشنا شویم. این قابلیت در Excel همانند تصویر زیر در Ribbon در منوی Data و در گروه Data Tool مستقر است:
دوره های شبکه، برنامه نویسی، مجازی سازی، امنیت، نفوذ و ... با برترین های ایران
سرفصل های این مطلب
به طور خلاصه میتوان کاربردهای Data Validation را بصورت زیر عنوان کرد:
- ایجاد لیستهای کشوئی یا Drop-Down که به واسطه آن کاربران داده های خود را سریعتر، دقیقتر و با اشتباه کمتر وارد می نمایند، در ضمن به واسطه آن کاربران ملزم به انتخاب یک داده مشخص و از پیش تعیین شده در لیست می باشند.
- ایجاد محدودیت برای ورود داده در سلولها بر اساس شروط مختلفی از قبیل (کوچکتر یا بزرگتر بودن از مقدار خاصی، دقیقاً برابر بودن با مقداری خاص، مابین دو مقدار خاص بودن و ...)
- ایجاد راهنما برای ورود داده ها در سلولها
- نمایش پیغام خطا هنگام رعایت نکردن شروط مذکور و ...
ایجاد لیستهای کشوئی یا Drop-Down
برای تفیهم بهتر موضوع، کاربرگ یا Sheet زیر را در نظر بگیرید:
در کاربرگ مذکور، ستونهائی از قبیل محل تولد، مدرک و رشته تحصیلی میتوانند مواردی از پیش تعیین شده و مشخص باشند که برای آنها از لیستهای کشوئی یا Drop-Down List استفاده می نمائیم. بدین منظور ابتدا می بایست داده هایی که در هر لیست هستند در ستونی جداگانه از کاربرگ جاری و یا در کاربرگی دیگر درج گردند. به عنوان نمونه همانند تصویر زیر، لیست محل تولد را که دربرگیرنده نام استانهای کشور است در کاربرگی دیگر ایجاد میکنیم:
میتوانید برای یادگیری کاربردهای دیگر اکسل به دوره آموزش اکسل در بخش دوره های آموزشی سایت مراجعه نمایید.
سپس همانند تصویر زیر در ستونِ محل تولد از سلول D2 به سمت پایین تعدادی سلولهای مورد نظر را برای ایجاد لیست انتخاب می کنیم و بر روی گزینه Data Validation کلیک می کنیم:
در ویزارد Data Validation در تب Setting همانگونه که ملاحظه می نمائید در قسمت Allow گزینه Any value بصورت پیش فرض انتخاب شده است یعنی ورود هر داده ای در سلولهای انتخاب شده مجاز می باشد. حال برای ایجاد لیست کشوئی در قسمت Allow همانند تصویر زیر گزینه list را انتخاب می کنیم
با انجام این کار فیلدی با عنوان Source نمایان می شود که برای آدرس دهی به همان لیست ایجاد شده از قبل (استانهای ذکر شده برای محل تولد) می بایست مورد استفاده قرار گیرد، برای این منظور شما می توانید آدرس دادهها را در کادر تایپ نمائید و یا با استفاده از آیکن مشخص شده در کنار کادر، با استفاده از اشاره گر موس ناحیه داده ها را به حالت انتخاب درآورده و طبق تصویر زیرین بر روی آیکن مشخص شده کلیک کنید:
با انجام این کار آدرس ناحیه انتخاب شده همانند تصویر در کادر Source قرار می گیرد:
حال بر روی OK کلیک کلیک کنید، زین پس طبق تصویر زیر در کاربرگ خود و در ستون محل تولد، در کنار هر سلول شکل مثلثی ظاهر می گردد که با کلیک کردن بر روی آن، قادر به انتخاب یک استان از لیست کشوئی می باشید:
گزینه In-cell DropDown
حال در ستون مدرک نیز مطابق موارد مذکور در بالا، لیستی شامل موارد (زیر دیپلم-دیپلم-کاردانی-کارشناسی-ارشد-دکتری) را ایجاد می کنیم، اما این بار تیک گزینه In-cell DropDown را بر میداریم:
با انجام این کار طبق تصویر زیر، شکل مثلث جهت باز شدن لیست کشوئی نمایش داده نمیشود و شما می بایست مقدار مورد نظر خود را تایپ نمائید. حال اگر مقدار وارد شده با یکی از موارد موجود در لیست مطابقت داشته باشد، داده ثبت می شود، اما اگر در کوچکترین حرفی مطابقت نداشته باشد، خطای (The value you entered is not valid ) ظاهر می گردد:
نکته: پس از انجام تنظیمات فوق، در حالی که سلولهای مورد نظر در حالت انتخاب قرار دارند، با کلیک کردن مجدد بر روی گزینه Data Validation در Ribbon قادر به تغییر تنظیمات مذکور خواهید بود، به عنوان نمونه با فعال کردن گزینه In-cell DropDown در سلولهای ستون مدرک، مجدداً لیست کشوئی برای این سلولها همانند تصویر زیر نمایان خواهد شد:
حال فرض کنید، ستونی با عنوان «کد ملی» دارید که می بایست در هر سلول دقیقاً 10 رقم جهت کاهش خطا درج گردد، بدین منظور همانند تصویر زیر ابتدای سلولهای مورد نظر در ستون کد ملی را انتخاب میکنیم و پس از انتخاب گزینه Data Validation از منوی Data، در تب Setting ، گزینه Text Length را در قسمت Allow، گزینه Equal To را در قسمت Data و مقدار 10 را در قسمت Length تنظیم مینمائیم:
پس از انجام تنظیمات فوق، در هریک از سلولهای ستون کد ملی، صرفاً داده ای با طول 10 کاراکتر می بایست وارد شود در غیر این صورت پیغام خطائی ظاهر می گردد:
تب Input Message
حال برای جلوگیری از بروز خطا در وارد کردن اطلاعات میتوانید با استفاده از تب Input Message راهنمائی لازم را انجام دهید به عنوان نمونه همانند تصویر زیر برای ستون کد ملی پیام زیر را در نظر میگیریم که با انتخاب هر یک از سلولهای این ستون ظاهر میگردد:
--
تب Error Alert
در ضمن پیغام خطای ظاهر شده در صورت عدم رعایت شرایط را نیز می توانید تنظیم نمائید، برای این منظور از تب Error Alert همانند تصویر زیر استفاده می کنیم:
Circle Data Validation
حال فرض کنید در برخی از ستونها از قبل داده هایی وارد شده داشته باشیم و پس از آن نسبت به اعمال Data Validation اقدام کنیم، به عنوان نمونه همانند تصویر زیر در ستون «رشته تحصیلی» از قبل داده هایی وارد می کنیم و سپس نسبت به اعمال محدودیت تعداد کاراکترهای مجاز بین 6 تا 20 اقدام میکنیم:
همانگونه که ملاحظه می نمائید برخی از سلولها دارای شرط مذکور نمی باشند، برای مشخص شدن این سلولها کافیست طبق تصویر زیر گزینه Circle Data Validation را انتخاب کنید، با انجام این کار سلولهای فاقد شرط ذکر شده، با کادری قرمز رنگ مشخص میگردند:
حال اگر طبق تصویر زیر بر روی گزینه Clear Validation Circle کلیک کنید، این کادرها حذف خواهد گردید.
علاوه بر موارد مذکور در این آموزش، همانند موارد مذکور، از طریق Data Validation قادر به ایجاد محدودیتهای دیگری در داده های ورودی در سلولهای اکسل خواهید بود از جمله؛ ایجاد بازه های عددی، زمان و تاریخ: