 |
مهندس احسان حاتمی
کارشناس ارشد مهندسی نرم افزار؛ آذر ماه 1396 |
در این مطلب قصد داریم با کاربردهای 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 قادر به ایجاد محدودیتهای دیگری در داده های ورودی در سلولهای اکسل خواهید بود از جمله؛ ایجاد بازه های عددی، زمان و تاریخ: