Data Validation در اکسل – کاربرد و آموزش استفاده | به زبان ساده

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

از آنجایی که ممکن است برای استفاده از Data Validation در اکسل مجبور به فرمول‌نویسی باشیم، بهتر است با خواندن مطالب  فرمول نویسی در اکسل — به زبان ساده و ۱۶ فرمول اکسل برای حل مشکلات روزمره با این کار آشنا شوید. همچنین خواندن تابع IF در اکسل — به زبان ساده و چگونه با «Conditional Formatting» سطرها را در اکسل برجسته کنیم؟ — آموزش گام‌به‌گام نیز خالی از لطف نیست.

Data Validation در اکسل

اعتبار سنجی داده یکی از ویژگی‌های اکسل است که برای کنترل ورودی‌های کاربر در سلول‌ها به کار می‌رود. به عنوان مثال، می‌توانید از اعتبار سنجی داده استفاده کنید تا مطمئن شوید فقط اجازه ورود مقدارهایی بین 1 تا 6 در یک سلول خاص به کاربر داده شده یا اینکه ورودی یک عبارت متنی حتما کمتر از 25 حرف باشد. این گونه قید و شرط‌ها توسط دستور یا ابزار Data Validation صورت می‌گیرد.

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

برای مثال فرض کنید که در ستون مربوط به مقادیر کد کالا، استاندارد برای ورود کد مثلا مقداری بین ۱۰۰۰۰ تا ۲۰۰۰۰ بوده و کاربر مقداری خارج از این دامنه وارد کرده است. در این صورت اکسل مقدار را برای چنین سلولی ثبت نخواهد کرد. این امر تضمینی به استفاده کنندگان از کاربرگ اکسل می‌دهد کد کالا ورودی صحت داشته و مورد تایید است.

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

برای دسترسی به پنجره تنظیم‌های Data Validation، باید از برگه Data و قسمت Data Tools، گزینه Data Validation را برای اعتبار سنجی داده‌ها، انتخاب کنید. اعتبار سنجی داده در پنجره‌ای با سه زبانه یا برگه تعریف می‌شود. در تصویر ۱، این پنجره را مشاهده می‌کنید. برگه‌های مورد استفاده در این پنجره در ادامه معرفی شده‌اند.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel

کلیک کنید

• تنظیم شرط (Settings): در این قسمت، شرط یا شرط‌های مورد نظر برای اعتبار سنجی مقادیر ورودی، مشخص و تعیین می‌شوند. این شرط می‌تواند بر اساس مقدار خود سلول یا رابطه با سلول‌های دیگر صورت گیرد.
• پیام ورودی (Input Message): به کمک این برگه، پیغامی به عنوان راهنما یا پیشنهاد برای مقادیر ورودی به کاربران ارائه می‌کند. به این ترتیب، اگر کاربر، سلولی را انتخاب کند که برایش پیام ورودی در نظر گرفته شده، اعلان و راهنمایی، شبیه یک «کادر یادداشت» (Comment) ظاهر می‌شود. برای این پیام، می‌توان یک عنوان (Title) و یک متن (Input Message) در نظر گرفت.
• پیغام هشدار یا خطا (Error Alert): در صورتی که مقدار وارد شده در سلول با قوانینی که در بخش تنظیم شرط معرفی شده، همخوانی نداشته باشد، یک پیغام هشدار ظاهر خواهد شد. تعیین نوع خطای هشدار و پیام ظاهر شده در این قسمت صورت می‌گیرد. در اینجا هم برای پنجره خطا، یک عنوان و یک متن قابل تعیین است.

توجه داشته باشید که اگر می‌خواهید تغییرات اعمال شده در این پنجره را به حالت اولیه برگردانده و از هیچ قید یا شرطی هنگام ورود داده‌ها استفاده نکنید، از دکمه Clear All کمک بگیرد. به این ترتیب تمامی تنظیم‌ها، لغو شده و پس از فشردن دکمه OK، کاربران اجازه ورود هر نوع مقداری را در سلول مورد نظر دارند.

فیلم آموزش ترفندهای کاربردی در اکسل Excel در فرادرس

کلیک کنید

مقادیر عددی و Data Validation در اکسل

همانطور که گفته شد، به کمک Data Validation می‌توانید هنگام ورود مقادیر درون سلول، شرط یا شرط‌هایی را چک کرده، سپس به کاربر اجازه ثبت مقدار را بدهید. بنابراین اگر لازم باشد از شرطی استفاده کنید که برای مقادیر عددی محدودیتی ایجاد کند، لازم است، از برگه Settings و قسمت Validation Criteria گزینه Whole Number یا Decimal را از فهرست Allow انتخاب کنید.

هر دو نوع قید یا شرط مربوط به اعداد هستند. فقط توجه داشته باشید که گزینه Whole number، فقط مقادیر اعداد صحیح را می‌پذیرد ولی Decimal برای ورود مقادیر صحیح و اعشاری مناسب است.

پس از انتخاب این گزینه‌ها، قسمتی در این پنجره ظاهر می‌شود که محدودیت یا قیدهای مربوطه را تعیین می‌کند. برای مثال اگر می‌خواهید فقط مقادیر صحیح در بازه ۱۰۰۰۰ تا ۲۰۰۰۰ اجازه ورود داشته باشند، تنظیم‌ها را به مانند شکل ۲ درآورید. مشخص است که باید مقدار «حداقل» (Minimum) و «حداکثر» (Maximum) در کادرهای مربوطه نوشت یا از آدرس سلول‌هایی استفاده کرد که شامل این مقادیر هستند.

نکته: برای انتخاب آدرس سلول‌های مورد نظر، می‌توانید از دکمه فلش (↑) که در انتهای هر یک از کادرهای Minimum یا Maximum قراردارد استفاده کرده و پنجره Data Validation را کوچک کنید تا به ناحیه پشت این پنجره دسترسی داشته باشید.

البته برای ایجاد محدودیت به جز گزینه between که برای تعیین بازه مقادیر مورد استفاده قرار می‌گیرد، از شرط‌های دیگری نیز می‌توان استفاده کرد. جدول ۱، این محدودیت‌ها را معرفی و توصیف کرده است.

جدول ۱: معرفی محدودیت‌ها (Criteria) و عملکرد آن‌ها برای مقادیر عددی

نکته: اگر می‌خواهید از فضاهای خالی چشم پوشی کنید، گزینه Ignore blank را فعال کنید.

به یاد داشته باشید که اگر برای تعیین محدودیت روی یک ناحیه از سلول‌های کاربرگ اقدام کرده‌اید، انتخاب گزینه ...Apply these changes باعث می‌شود تغییرات اعمال شده روی یک سلول در این پنجره، به همه سلول‌های مشابه در آن ناحیه نیز نسبت داده شود.

انتخاب محتوای سلول از یک لیست با Data Validation در اکسل

در بسیاری از موارد، لازم است برای مقادیر متنی، یک محدودیت ایجاد کنیم تا کاربر فقط قادر باشد، عبارتی را در سلول ثبت کند که با اقلام لیست مورد نظر ما مطابقت دارد. این کار به کمک Data Validation و انتخاب گزینه List در فهرست Allow، صورت می‌گیرد. البته توجه داشته باشید که باید اقلام مورد تایید را قبلا در یک ناحیه از کاربرگ اکسل وارد کرده و آدرس آن ناحیه را به عنوان پارامتر گزینه List، معرفی کنید. این کار ویرایش مقادیر و تغییر آن‌ها را سهولت می‌بخشد.

توجه داشته باشید که در قسمت Source باید آدرس ناحیه‌ای از سلول‌ها که حاوی مقادیر لیست مورد نظرتان هستند را مشخص کنید. این ناحیه می‌تواند در کاربرگ جاری یا کاربرگ دیگری باشد. در تصویر بالا، ناحیه A1 تا A7 از کاربرگ Sheet2 به این منظور به کار رفته. مشخص است که آدرس‌‌های تعیین کننده این ناحیه، به صورت آدرس مرجع مطلق (با علامت $) ثبت شده‌اند.

فیلم آموزش بررسی اعتبار داده ها در اکسل + ابزار Data Validation (رایگان) در فرادرس

کلیک کنید

البته شیوه دیگر می‌تواند ورود مستقیم مقادیر در این بخش باشد. کافی است مقدارهای دلخواه را در بخش Source وارد کرده و هر قلم اطلاعاتی مربوط به لیست را با علامت «کاما» (,) از یکدیگر جدا کنید. برای ورود مقادیر فارسی نیز ابتدا متن یا عبارت اول را نوشته سپس زبان را به انگلیسی تغییر داده، کلید «,» را بزنید، در ادامه متن فارسی بعدی را وارد کنید. با تکرار این مراحل، یک لیست فارسی از اقلام اطلاعاتی برای یک سلول خواهید داشت که کاربر مجبور به انتخاب یا ورود یکی از آن‌ها است.

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

Data Validation در اکسل برای مقادیر تاریخ و ساعت

در اکسل برای مقادیر عددی که به قالب ساعت و یا تاریخ هستند، محدودیت‌های خاصی در Data Validation وجود دارد. برای مثال فرض کنید قرار است در یک سلول، مقادیر تاریخ (Date) در بازه اول سال ۲۰۲۰ تا ۲۰۲۲ میلادی ثبت شود. به این ترتیب محدودیتی که باید در این پنجره تنظیم شود، به مانند تصویر ۴ خواهد بود. توجه دارید که شرط between شامل دو مقدار کران «تاریخ شروع» (Start date) و «تاریخ خاتمه» (End date) نیز می‌شود.

شرط‌های قابل انتخاب برای مقادیر تاریخی، به مانند جدول ۱ خواهد بود. همین عمل را برای مقادیر مرتبط با زمان (ساعت) نیز می‌توان انجام داد. فقط باید در فهرست Allow، گزینه time را انتخاب کنید. واضح است که محدودیت‌های اعمال شده باید برحسب ساعت، دقیقه یا ثانیه باشند.

نکته: از توابعی که مرتبط با تاریخ یا ساعت هستند نیز می‌توان در کادر‌های مربوط به محدودیت‌ها استفاده کرد. برای مثال اگر می‌خواهید تاریخ ثبت شده در سلول از تاریخ جاری بزرگتر باشد، گزینه greater than را در از لیست Data انتخاب کرده و در قسمت «تاریخ اولیه» (start date)، از فرمول و تابع today کمک بگیرید. البته می‌توانید از توابع تاریخی دیگر نیز برای انجام محاسبه، بهره ببرید.

$$= TODAY()$$

همچنین توابع مرتبط با ساعت و زمان مانند تابع ()NOW نیز برای محدودیت‌های زمانی و ساعت قابل استفاده هستند. گزینه‌های شرطی در این بخش در جدول ۱ معرفی شده‌اند.

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

اجرای Data Validation در اکسل برای سلول‌های متنی

طول یا اندازه یک عبارت متنی، می‌تواند برای چنین مقدارهایی، یک محدودیت ثبت در Data Validation‌ باشد. بنابراین اگر از فهرست Allow، گزینه Text Length را وارد کنید، می‌توانید تعداد حروف یا طول عبارت را به یک بازه مقید کنید. برای مثال فرض کنید، می‌خواهید شرطی برای یک سلول قرار دهید که همیشه تعداد حروف ثبت شده در آن، بیش از ۴ حرف باشد. کافی است تنظیم‌های مربوطه را مطابق با تصویر ۵ اجرا کنید.

شیوه استفاده از Data Validation براساس شرط‌ها و محدودیت‌های متنوع، احتیاج به فرمول نویسی و استفاده از بخش Custom در کادر Validation Criteria‌ دارد که در نوشتارهای دیگر مجله فرادرس به آن خواهیم پرداخت.

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

برای مثال می‌توان محدودیت را برای یک سلول، به شکلی در آورد که حتما از مجموع دو مقدار سلول A1 و A2 بزرگتر باشد. به تصویر 6 توجه کنید.

همانطور که می‌بینید، محدودیتی که برای مثلا سلول A4 ایجاد کرده‌ایم، وابسته به سلول‌های A1 و A3 بوده و نتیجه جمع آن‌ها کران پایین برای سلول A4 را تعیین می‌کند. از آنجایی که مقدار Minimum با یک فرمول مشخص شده، وارد کردن علامت «=» ضروری است. از این موضوع می‌توان برای کنترل ورود اطلاعات براساس رابطه با سلول‌های دیگر استفاده کرد.

نمایش پیام ورودی

زبانه یا برگه پیام ورودی (Input Message) متنی را برای نمایش هنگام انتخاب سلول با قوانین اعتبار سنجی تعریف می‌کند. این پیام ورودی کاملاً اختیاری است. اگر پیام ورودی تنظیم شده باشد، هنگامی که کاربر سلولی با اعتبار سنجی داده را انتخاب می‌کند، متن پیام به شکل یک «یادداشت» (Comment) در مجاورت سلول، در یک کادر زرد رنگ، ظاهر می‌شود. این کار کمک می‌کند که کاربر نسبت به نوع مقدار مجاز، راهنمایی دریافت کند. البته توجه داشته باشید که پیام ورودی هیچ تأثیری در آنچه کاربر می‌تواند وارد کند، نداشته و صرفا یک پیام اطلاع رسانی محسوب می‌شود.

دو بخش Title و Input message، شامل کادرهایی هستند که عنوان و متن پیام را مشخص می‌کنند. نمونه‌ای از نحوه تعریف این پیام را در تصویر 7 مشاهده می‌کنید. واضح است که با غیرفعال کردن گزینه Show input، نمایش این توضیحات هنگام انتخاب سلول مورد نظر، متوقف می‌شود. شکل نمایشی هنگام ظاهر شدن این توضیحات و پیام ورودی نیز در تصویر 8 قابل مشاهده است.

برای جلوگیری از نمایش این پیام، کافی است گزینه ...Input message را در بالای این پنجره غیرفعال و دکمه OK را کلیک کنید.

پیغام هشدار یا خطا

اگر هنگام ورود مقداری در سلول که توسط Data Validation در اکسل دارای محدودیت شده، مقداری مغایر با شرط، وارد کنید، اکسل یک پیغام خطا ظاهر کرده و از شما می‌خواهد که مقدار را تصحیح کنید. البته این امکان در این نرم‌افزار در نظر گرفته شده که پیام ظاهر شده با تنظیم‌های صورت گرفته، به صورت دلخواه (Customize) درآید. به تصویر زیر توجه کنید.

در بخش Title برای پیغام هشدار یا خطا، یک عنوان وارد کنید. همچنین متنی که قرار است با ظاهر شدن پنجره هشدار یا خطا، به کاربر نمایش داده شود، نیز در بخش Error Message تعیین می‌شود. توجه داشته باشید که نحوه عملکرد پیغام هشدار یا خطا نیز در بخش Style قابل تغییر است. می‌توانید یکی از گزینه‌های Stop, Warning یا Information را انتخاب کنید. در ادامه هر سه مورد را توضیح خواهیم داد. در تصویر زیر، نمونه‌ای از پیغام خطای فارسی را مشاهده می‌کنید که مطابق با تنظیم‌های تصویر ۹ صورت گرفته است.

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

فیلم آموزش اکسل – ابزارهای کاربردی Excel + گواهینامه در فرادرس

کلیک کنید

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

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

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

نکته: اگر به عنوان پیغام هشدار یا خطا، چیزی را مشخص نکنید، اکسل از پیغام پیش‌فرض از نوع خطا (Stop) استفاده خواهد کرد.

اعتبار سنجی پس از ورود داده‌ها

گاهی یک کاربرگ یا کارپوشه اکسل به ما واگذار شده و باید محدودیت‌ها و شرط‌های مقادیر ثبت شده را بررسی کنیم. مثلا باید مشخص کنیم در کدام سلول‌های ستون مربوط به سن دانش‌آموزان، مقدار بزرگتر از ۲۰ تا کوچکتر از ۶ وارد شده است.

از آنجایی که در چنین ستونی، از قبل داده‌ها وارده شده‌اند، باید پس از تعیین شرط یا محدودیت‌های مورد نظر روی سلول‌ها، از اکسل بخواهیم، مقایر غیر معتبر در سلول‌ها را مورد بررسی قرار داده و مشخص کند. این کار با کشیدن یک دایره قرمز دور سلول‌های نامعتبر توسط اکسل انجام می‌شود. در تصویر زیر مسیر دسترسی به این فرمان را مشاهده می‌کنید.

واضح است که با انتخاب گزینه Clear Validation Circles، علامت‌های دایره‌ روی سلول‌های دارای مغایرت، برداشته می‌شود.

نکته: در نسخه‌های قدیمی اکسل (۲۰۰۳ به قبل) چنین امکانی وجود نداشت و Data Validation در اکسل فقط هنگام ورود داده‌ها، ارزیابی و اعتبار سنجی اجرا می‌شد.

خلاصه و جمع‌بندی

همانطور که خواندید، ابزار یا دستور Data Validation در اکسل می‌تواند قبل و حتی بعد از ورود داده‌ها اجرا شده و سلول‌های نامناسب را تشخیص دهد. البته تعیین سلول های معتبر و غیر معتبر به وسیله شرطی که به عنوان پارامتر این ابزار به کار می‌رود، امکان‌پذیر است. به یاد دارید که برای استفاده از بیش از یک شرط در تشخیص مقادیر معتبر، مجبور به فرمول‌نویسی در Data Validation‌ هستیم.

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