شنبه, 3 سپتامبر 2022

آموزش ابزار Data validation در اکسل

به منظور درک بهتر از کاربرد Data Validation در اکسل، ویدیویی آموزشی در انتهای این مقاله آموزشی قرار گرفت. باتشکر از همراهی شما

آموزش ابزار Data validation در اکسل

از ابزار Data validation یا اعتبار سنجی داده‌ها، به منظور کنترل داده‌ها به هنگام ورود داخل سلول‌ و یا مجموعه‌ای از سلول‌ها مورد استفاده قرار می‌گیرد. همانطور که می‌دانید ورود داده‌ها در سلول‌های یک کاربرگ باید با دقت و به درستی انجام شود، زیرا در صورتی که ورودی‌هایمان به شکل درست انتخاب نشده باشند، ممکن است همه سلول‌های محاسباتی (سلول‌های وابسته) دچار خطا شوند. به همین دلیل این ابزار را می‌توان روشی برای اعتبارسنجی داده‌ها در اکسل دانست.

زمانیکه نیاز داریم کاربر فقط داده‌هایی بین اعداد 5 تا 12 را وارد کند، یا اینکه یک عبارت متنی کمتر از 30 کارکتر را ثبت کند، استفاده از این ابزار احساس می‌شود. البته به این نکته توجه داشته باشید که اعتبارسنجی داده تنها می‌تواند از ثبت داده‌ها و مقادیر غیرمجاز جلوگیری کرده و تا زمانی که مقدار صحیح در سلول وارد نشود، کاربر را از اجرای دستور دیگر باز خواهد ماند. در ادامه اگر کاربر از ورود داده غیرمعتبر در آن سلول منصرف شود، اجرای عملیات برروی سلول‌های دیگر نیز امکان‌پذیر خواهد شد.

به عنوان مثال در ستون مربوط به کدملی، استاندارد ورود اعداد کدملی ده رقمی بوده و کاربر مقداری خارج از این تعداد را وارد کند، در این صورت اکسل مقداری برای این سلول درج نخواهد کرد.

تفاوت Data Validation و Conditional Formatting در اکسل

اعتبار سنجی داده (Data Validation) با قالب‌بندی شرطی (Conditional Formatting) یک تفاوت عمده دارد. در قالب‌بندی شرطی، مقدار باید ثبت شود، سپس شکل نمایشی آن تعیین می‌شود. ولی موقع استفاده از اعتبار سنجی (Data Validation)، قبل از ثبت، مقدار ورودی، مورد بررسی قرار میگیرد و با داشتن شرایط امکان ثبت وجود دارد.

نکته: در نظر داشته باشید اعتبار داده‌ها را می توان به راحتی از بین برد! اگر کاربر داده‌ها را از یک سلول بدون اعتبارسنجی در سلولی با اعتبارسنجی داده کپی کند، اعتبارسنجی از بین می‌رود (یا جایگزین می‌شود). اعتبار سنجی داده ها راه خوبی برای اطلاع دادن به کاربران برای اطلاع از موارد مجاز یا مورد انتظار است، اما روشی مطمئن برای تضمین ورودی نیست.

استفاده از ابزار Data Validation در اکسل

پس از انتخاب سلول یا محدوده خاصی از سلولها که می‌خواهید محدودیتی روی آن اعمال کنید، از سربرگ Data و گروه Data tools روی گزینه Data validation کلیک کنید:

پس از کلیک روی ابزار Data validation، پنجره‌ای به همین نام ظاهر می‌شود:

روش دیگر برای اینکار، کلیک روی علامت مثلث کنار Data Validation هست. که با کلیک یک زیرمنو ظاهر می‌شود. با کلیک برروی ابزار Data Validation…، مجدد پنجره Data Validation را خواهیم داشت:

در پنجره ظاهر شده سه تب Settings (تنظیمات) و Input Message (پیغام های ورودی) و Error Alert (پیغام خطا) وجود دارد. در تب Settings، در بخش Validation criteria شروط و قوانینی که می‌خواهیم روی سلول‌ها اعمال کنیم را ایجاد می‌کنیم. گزینه Allow (به معنای اجازه دادن) شامل منوهای زیر است:

توضیحات هریک از محدودیت‌های ابزار Data Validation به شرح جدول زیر است:

Any value: همانطور که در جدول نیز اشاره شد، با انتخاب گزینه Any value هیچ محدودیتی در ورود داده‌ها نخواهیم داشت.
Whole number: با انتخاب گزینه Whole number فقط امکان ورود داده‌های عددی میسر هست.

همچنین با انتخاب این گزینه(Whole number)، سه بخش Data و Minimum و Maximum فعال می‌شود. بخش دیتا خود شامل قسمتهای زیر است:

توضیحات هریک از گزینه‌های موجود در بخش Data به شرح جدول زیر است:

گزینه Minimum کمترین مقدار و Maximum بیشترین مقدار را مشخص می‌کنند. مثلا برای between اگر Minimum را برابر با عدد 10 و Maximum را برابر با عدد 30 در نظر بگیریم، بازه اعداد انتخابی ما باید محدوده بین این دو عدد باشد(10 و 30) و اگر مقادیر را برای گزینه not between در نظر بگیریم، بازه اعداد انتخابی نباید محدوده بین این دو عدد باشد.

همچنین اگر تیک قسمت Ignore blank فعال باشد، می‌توان سلول را خالی رها کرد.

Decimal: با انتخاب این گزینه ورود داده‌ها محدود به اعداد صحیح و اعشاری خواهند بود.
List: با انتخاب این گزینه، برای کاربر یک لیست انتخابی ایجاد می‌کنیم تا کاربر فقط قادر باشد، عبارتی در سلول ثبت کند که با لیست ما همخوانی داشته باشد.

توجه کنید که برای ایجاد لیست، اقلام لیست را باید قبلا در کاربرگ جاری یا کاربرگ دیگری ایجاد کرده باشیم و در قسمت Source، آدرس آن را وارد کنیم. برای مثال مشاهده می‌کنید که برای ایجاد لیست شهرها از سلولهای H10 تا H13 استفاده کردیم و آدرس را به این صورت =$H$10:$H$13 وارد نمودیم. البته اگر لیست ما در کاربرگ دیگری قرار داشته باشد آدرس را به صورت =Sheet2!$P$4:$P$7 وارد می‌کنیم. پس از ایجاد لیست میتوان نتیجه را در سلول‌ها مشاهده کرد:

فعال کردن گزینه In-cell dropdown باعث می‌شود، هنگام انتخاب سلولی با محدودیت لیست، یک علامت ↓ در کنار آن ظاهر شود تا یک فهرست کشویی (Dropdown list) برای سلول نمایش داده شود:

Date: این بخش داده های از نوع تاریخ را مدیریت می‌کند.

در نظر داشته باشید که Start date تاریخ شروع و End date تاریخ پایان است. در بخش دیتای مربوط به این گزینه هم دوباره منوهای زیر را داریم:

Time: همانند بخش Date، این گزینه نیز داده های زمان را مدیریت می‌کند با این تفاوت که داده‌های ورودی باید بر اساس ساعت، دقیقه و ثانیه باشند.

توجه کنید که از توابعی که مربوط به ساعت و تاریخ هستند نیز می‌توان در این قسمت استفاده کرد. که برای بخش Start time یا زمان شروع از تابع =today() برای تاریخ و یا تابع =now() برای زمان می‌توان استفاده کرد.

Text Length: برای ایجاد محدودیت در اندازه یک عبارت متنی(تعداد کارکتر) از این گزینه استفاده می‌کنیم.

مثلا میتوان برای نام محدودیتی اعمال کرد که تعداد حروف یا کارکترهای وارد شده بزرگتر از 10 کرکتر باشند، برای این کار تنظیمات زیر را انجام می‌دهیم:

Custom: در صورتی که تنظیمات قبلی نتواند نیاز ما را در اعمال محدودیت‌هایمان برآورده کند، می‌توانیم از این بخش استفاده کنیم. با انتخاب آن گزینه ای با نام Formula فعال می‌شود، که می‌توانیم فرمول‌های مورد نیاز خود را وارد کنیم.

نکته: در هر بخش با فعال کردن تیک قسمت Apply these changes to all other cells with the same settings می‌توان تنظیمات را برای سایر سلول‌های مشابه نیز فعال کرد. همچنین با زدن دکمه Clear All میتوان تنظیمات را پاک و به تنظیمات پیش فرض برگرداند.

تب Input Message یا نمایش پیام ورودی

دومین تب از پنجره Data validation مربوط به نمایش پیام ورودی است. این پیام ورودی متنی را برای نمایش قوانین تعریف شده، برای کاربر نمایش می‌دهید و ایجاد آن کاملا اختیاری است.

هنگامی که کاربر سلولهای با محدودیت را انتخاب می‌کند، پیامی به شکل یک یادداشت (Comment) در کنار سلول نمایش داده می‌شود. این پیام کاربر را نسبت به ورود مقادیر مجاز راهنمایی می‌کند.

بخش title تیتر پیام و Input message متن پیام می‌باشد و با غیرفعال کردن Show input نمایش پیغام موقع فعال کردن سلول را متوقف می‌کند.

نمایی از نمایش پیام Input Message را به هنگام انتخاب سلول مشاهده خواهد نمود:

تب Error Alert یا پیغام هشدار یا پیغام خطا

اگر هنگام وارد کردن داده ها در سلول‌های دارای محدودیت، مقداری مغایر با شرط وارد کنید، اکسل یک پیغام خطا نمایش می‌دهد که میتوان آنرا به صورت دلخواه تغییر دهید:

Title تیتر پیام و Error message متن پیغام را مشخص می‌کنند. همچنین با برداشتن تیک Show error پیغام خطا نمایش داده نمی‌شود.

در بخش Style سه نوع روش نمایش خطا وجود دارد:

شیوه خطای Stop : در این حالت، کاربر با هر بار زدن انجام عمل ثبت مقدار نادرست، پیغام را مشاهده کرده و تا زمانی که از ثبت انصراف داده و دکمه Cancel را کلیک کند یا مثلا کلید ESC را فشار دهد، این پیغام ظاهر می‌شود. البته در صورت وارد کردن مقدار صحیح نیز نمایش این پیغام صورت نخواهد گرفت. توجه داشته باشید که تا رفع خطا با این پیغام مواجه میشویم و اجازه اجرای دستور دیگری را نداریم:

شیوه اخطار Warning: اگر بخواهید به کاربر اخطار داده و مشخص کنید که مقدار ورودی، مغایر با شرط است، میتوان از این شرط استفاده کنید. در این صورت با کلیک روی دکمهYes، مقدار ثبت شده (حتی درصورت مغایرت با شرط) و دکمه NO، از کاربر می‌خواهد که مقدار جدیدی را برای این سلول وارد کند. در صورتی که دکمه Cancel کلیک شود، عمل ورود داده، لغو شده و دکمه Help‌ نیز راهنمای دستور Data Validation در اکسل را ظاهر میکند:

شیوه جهت اطلاعInformation : از این روش نمایش برای زمانی استفاده می‌کنیم که می‌خواهیم کاربر را نسبت به عدم رعایت شرط ورودی، آگاه سازیم. در این روش، کاربر می‌تواند مقدار مورد نظر را ثبت کرده و تایید کند که خود مسئول مغایرت و اختلالات احتمالی است. کافی است دکمه OK را کلیک کند. در صورت فشردن دکمه Cancel یا Help، اکسل همانند حالت‌های قبلی عمل می‌کند.