** مطالب این وبلاگ برای آنانیست که نیاز دارند ولی دسترسی ندارند . موفق باشید . **


نویسنده :MRA Alipour
تاریخ:جمعه 8 آذر 1398-06:25 بعد از ظهر

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


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

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

گام های لازم برای اعمال Data Validation در اکسل

برای استفاده از Data Validation باید سلول های مد نظر خودمون رو انتخاب کنیم و بعد مثل تصویر زیر از تب Data، گزینه Data validation رو کلیک کنیم. با انتخاب این دکمه منویی باز میشه که شامل سه گزینه زیر میشه:

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

  • Data Validation
  • Circle Invalid Data
  • Clear Validation Circles

گزینه Data Validation رو انتخاب کنید. با انتخاب این گزینه پنجره زیر باز میشه که در اون میتونیم تنظیمات مختلف Data Validation رو انجام بدیم:

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

گام اول – اعمال تنظیمات قانون Data Validation در تب Settings

در این تب ما میتونیم تعیین کنیم که چه نوع داده هایی اجازه دارند در سلول وارد بشند.برای اینکار باید مراحل زیر رو انجام بدیم:

ابتدا در قسمت Allow قانون کلی رو مشخص میکنیم که میتونه یکی از گزینه های زیر باشه:

  • Any Value: این گزینه که بطور پیش فرض انتخاب شده به این معنی هست که هر نوع داده ای میتونه در سلول وارد بشه. به عبارت دیگه هیچ Data Validation خاصی رو سلول اعمال نمیشه.
  • Whole Number: با انتخاب این گزینه تنها اجازه وارد کردن اعداد صحیح در سلول به کاربر داده میشه
  • Decimal: با انتخاب این گزینه تنها اجازه وارد کردن اعداد اعشاری در سلول به کاربر داده میشه
  • List: با انتخاب این گزینه میتونیم انتخاب کاربر رو به یه لیست دلخواه از گزینه ها محدود کنیم. مثلاً فیلد جنسیت که فقط مرد یا زن میتونه باشه. در صورت انتخاب این گزینه هنگام انتخاب سلول، کاربر میتونه یکی از گزینه ای قابل انتخاب رو از Drop  down list انتخاب کنه.
  • Date:  با انتخاب این گزینه تنها اجازه وارد کردن تاریخ به کاربر داده میشه
  • Time: با انتخاب این گزینه تنها اجازه وارد کردن زمان به کاربر داده میشه
  • Text length: با انتخاب این گزینه میتونیم طول داده وارد شده در سلول رو به مقدار خاصی محدود کنیم. مثلاً اگر میخوایم اجازه ندیم متنی بیشتر از 100 کاراکتر وارد سلول بشه میتونیم از این گزینه استفاده کنیم.
  • Custom: این گزینه به ما امکان میده که برای بررسی صحیح بودن داده وارد شده فرمول نویسی کنیم که به ما قدرت بیشتری برای بررسی میده. مثلاً فرض کنید قصد داریم به کاربر تنها اجازه ورود اعداد زوج رو بدیم. در اینجا با یک فرمول نویسی میتونیم چک کنیم که آیا عدد وارد شده زوج هست یا خیر.

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

مثلاً فرض کنید ما در بخش allow گزینه Whole Number رو انتخاب کردیم یعنی فقط عددهای صحیح بتونه در سلول وارد بشه. حالا در قسمت پایین میتونیم دامنه عددهای صحیحمون رو محدودتر کنیم. مثلاً بگیم:

فقط عددهای صحیحی که بین 20 تا 40 باشن بتونن در سلول وارد بشن و نه هر عدد صحیحی.

اینکه چه تنظیماتی در قسمت پایین نمایش داده بشه بستگی به انتخاب ما در بخش Allow داره. بطور کلی تنظیمات قابل استفاده برای قانون های مختلف به شکل زیر هست:

1- برای Whole Number, Decimal, Text length, Date و Time:

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

Data: در این قسمت میتونید یکی از گزینه های زیر رو انتخاب کنید:

  • between: اگر میخواید محدوده خودتون رو بین دو عدد یا تاریخ مثلاً بین 20 تا 40 تعیین کنید از این گزینه استفاده کنید.
  • not between: اگر میخواید محدوده شما خارج از یک بازه باشه مثلا بین 10 تا 20 نباشه این گزینه رو انتخاب کنید. پس از انتخاب این گزینه در قسمت Minimum حد پایین و در قسمت Maximum حد بالا رو تعیین کنید.
  • equal to: اگر میخواد محدوده شما یک عدد یا تاریخ مشخص باشه از این گزینه استفاده کنید.
  • not equal to: اگر میخواید محدوده شما به جز یک عدد باشه (مثلاً همه اعداد بجز 20) این گزینه رو انتخاب کنید. پس از انتخاب این گزینه عدد موردنظرتون رو در قسمت پایین وارد کنید.
  • greater than: اگر میخواید محدوده شما بزرگتر از مقداری باشه (مثلاً فقط اعداد بزرگتر از 30) این گزینه رو انتخاب کنید.
  • less than: اگر میخواید محدوده شما کوچکتر از مقداری باشه (مثلاً فقط اعداد کوچکتر از 30) این گزینه رو انتخاب کنید.
  • greater than or equal to: اگر میخواید محدوده شما بزرگتر یا مساوی مقداری باشه این گزینه رو انتخاب کنید. پس از انتخاب این گزینه عدد موردنظرتون رو در قسمت پایین وارد کنید.
  • less than or equal to: اگر میخواید محدوده شما کوچکتریا مساوی از مقداری باشه این گزینه رو انتخاب کنید. پس از انتخاب این گزینه عدد موردنظرتون رو در قسمت پایین وارد کنید.

2- برای List:

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

Source: در این قسمت گزینه هایی که باید قابل انتخاب باشند رو تعیین میکنیم. برای اینکار دو روش وجود داره:

روش اول: نوشتن گزینه ها به صورت دستی و جدا کردن اونها با کاما مثل “گزینه یک,گزینه دو,گزینه سه

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

روش دوم: انتخاب یک محدوده از شیت اکسل به عنوان گزینه های قابل انتخاب

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

3- برای Custom:

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

Formula: در این قسمت میتونیم یک فرمول  اکسل بنویسیم. نتیجه این فرمول باید به true یا false ختم بشه. اگر نتیجه فرمول true باشه اکسل اجازه ورود داده رو میده، در غیر اینصورت اجازه ورود داده در سلول داده نخواهد شد.

 

نکته: توی این بخش ها میتونید فرمول نویسی کنید و حتماً لازم نیست که یه عدد ثابت نوشته بشه.
گزینه Ignore blank:

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

گزینه Apply this changes to all other cells with the same settings:

اگر این گزینه رو انتخاب کنیم، تغییراتی که در data Validation میدیم روی تمام سلول هایی که دقیقاً همین Data Validation رو دارند اعمال میشه. در غیر اینصور تنها data Validation سلولهایی که انتخاب کردیم تغییر میکنند.

 

گام دوم – تنظیم پیام راهنما برای کاربر در تب Input message

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

Show input message when cell selected: اگر این گزینه رو فعال کنیم، وقتی کاربر سلول رو انتخاب میکنه تا اطلاعات رو در اون سلول وارد کنه یک پیام راهنما برای کاربر نمایش داده میشه

Title: در این قسمت عنوان پیام خودمون رو  مشخص میکنیم. مثلاً “توجه”

Input message: در این قسمت متن پیام راهنما رو مشخص میکنیم. مثلاً چیزی شبیه “در این قسمت تعداد اقلام را به صورت عدد صحیح وارد نمایید.”

در تصویر زیر نمونه پیام راهنمای نمایش داده شده برای کاربر رو مشاهده میکنید:

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

 

گام سوم – تنظیم پیام خطا در صورت ورود اطلاعات نامعتبر در تب Error Alert:

در ابزار Data Validation این قابلیت وجود داره که ما پیام خطا رو شخصی سازی کنیم. یعنی مشخص کنیم که اگر کاربر اطلاعات نامناسبی رو در سلول وارد کرد (به عنوان مثال اگر ما Data Validation عدد گذاشتیم و کاربر به جای عدد کلمه وارد کرد) چه پیغام خطایی و با چه شکلی نمایش داده. برای شخصی سازی این پیغام خطا در پنجره Data Validation تب Error Alert رو فعال میکنیم که مشابه تصویر زیر هست.

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

Show error alert after invalid data is entered: اگر تیک این گزینه رو غیر فعال کنیم، در صورتی که کاربر داده اشتباهی رو وارد کنه هیچ پیغام خطایی نمایش داده نمیشه. پس اگر میخوایم با ورود اطلاعات اشتباه، پیغام خطا به کاربر نمایش داده بشه باید این گزینه رو فعال کنیم.

Style: در این قسمت شکل ظاهری آیکون پنجره رو تعیین میکنم. برای آیکون پنجره میتونیم یکی از سه حالت زیر رو انتخاب کنیم که هر کدوم بیانگر سطح اهمیت پیغام خطای ما هستند:

  1. Stop: با انتخاب این گزینه آیکون قرمز رنگ ضربدر به کاربر نشون داده میشه.
  2. Warning: با انتخاب این گزینه آیکون زرد رنگ هشدار به کاربر نماش داده میشه.
  3. Information: با انتخاب این گزینه آیکن آبی جهت اطلاع به کاربر نمایش داده میشه.

Title: در این قسمت عنوان پیغام خطا رو تعریف میکنیم. چیزی شبیه “اطلاعات وارد شده اشتباه میباشد”

Error message: در این قسمت متن خطا رو که به کاربر اطلاعات میده مینویسیم. مثلاً چیزی شبیه این “در این سلول تنها باید اعداد صحیح وارد شود. لطفاً مجدداً اطلاعات را وارد نمایید.” 

تصویر زیر نمونه پیغام خطا پس از ثبت اطلاعات نامعتبر توسط کاربر رو نشون میده:

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

نحوه نمایش سلولهایی که داده نامعتبر دارند

ممکنه زمانی که شما برای تعدادی از سلول ها Data Validation تعریف میکنید، دیتای قبلی داخل اون سلول ها با Data Validation جدیدی که تعریف کردید همخوانی نداشته باشه. برای اینکه ببینید کدم سلول ها دارای داده نامعتبر هستند کافیه اون سلول ها رو انتخاب کنید و از بخش Data Validation مثل تصویر زیر گزینه Circle invalid data رو انتخاب کنید:

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

با انتخاب این گزینه سلول هایی که داده نامعتبر دارند با یک دایره قرمز رنگ مشخص میشند.

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

برای حذف دایره ها هم کافیه گزینه Clear Validation Circles رو مثل تصویر زیر انتخاب کنید

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

 

مثال های کاربردی برای استفاده از Data Validation در اکسل:

خب حالا که با تنظیمات موجود در پنجره Data Validation آشنا شدیم بهتره سه تا مثال عملی رو باهم بررسی کنیم تا بهتر با روش استفاده از این ابزار آشنا بشیم.

مثال اول – محدود کردن داده های ورودی به اعداد صحیح بین 20 تا 40

برای محدود کردن داده های ورودی یک سلول به اعداد صحیح مراحل زیر رو انجام میدیم.

ابتدا سلول مدنظرمون رو انتخاب میکنیم و بعد از تب Data گزینه Data Validation رو انتخاب میکنیم.

مثل تصویر زیر در بخش Allow گزینه Whole Number ، در قسمت Data گزینه between، در قسمت Minimum گزینه 20 و در قسمت Maximum گزینه 40 رو انختاب میکنی

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

برای نمایش خطا در صورت ورود داده نامعتبر، تب Error Alert رو مثل تصویر زیر تنظیم میکنیم و دکمه OK رو میزنیم.

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

حالا کاربر در سلول انتخاب شده باید عددی بین 20 تا 40 رو وارد کنه و در صورت وارد کردن داده دیگه، با پیغام خطا مواجه میشه.

مثال دوم – محدود کردن داده های ورودی به چند گزینه مشخص

در این مثال میخوایم داده های قابل ورود در سلول A1 رو به دو گزینه “مرد” و “زن” محدود کنیم. برای اینکار مراحل زیر رو انجام میدیم.

ابتدا مثل تصویر زیر در سلول B1 گزینه “مرد” و در سلول B2 گزینه “زن” رو وارد میکنیم.

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

بعد سلول A1 رو انتخاب میکنیم و از تب Data گزینه Data Validation رو انتخاب میکنیم. سپس در بخش allow گزینه listرو انتخاب میکنیم. در اینجا برای تعریف گزینه های مرد و زن دو راه پیش روی ما قرار داره. راه ساده تر اینه که در قسمت source گزینه های انتخابی خودمون رو به صورت تفکیک شده با کاما بنویسیم. مثل تصویر زیر:

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

اینکار ساده تره و خیلی سریع انجام میشه. راه دیگه اینه که گزینه های مدنظرمون رو از طریق انتخاب یک محدوده تعیین کنیم. برای اینکار اینکار روی فلش سیاه رنگ مثابل source کلیک میکنیم:

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

حالا سلول های B1 و B2 رو مثل تصویر زیر انتخاب میکنیم و روی فلش آبی رنگ که در تصویر مشخصه کلیک میکنیم.

خب Data Validation ما ست شده. حالا اگر سلول A1 رو انتخاب کنیم، دو گزینه مرد و زن به صورت drop-down list قابل انتخاب هستن مثل تصویر زیر:

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

مثال سوم – محدود کردن داده های ورودی به اعداد زوج با استفاده از Data Validation در اکسل

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

 

نکته:

استفاده از Data Validation تنها برای راهنمایی کاربر و جلوگیری از اشتباهات سهوی هست و به هیچ عنوان نمیتونه کاربر رو مجبور به ورود اطلاعات در قالب تعیین شده بکنه. به عنوان مثال اگر کاربر یک سلول رو که Data Validation نداره از جای دیگه ای کپی کنه و روی یک سلول که Data Validation داره paste کنه، به راحتی مقدار اون سلول جایگزین میشه و Data Validation سلول پاک میشه.

 

فایل نمونه:

 

[private]

DataValidation.sample.worksheet.ir

[/private]

تمرین Data Validation در اکسل:

برای سلولهای A1 تا A10 یک Data Validation ایجاد کنید به نحوی که فقط اعدادی که مضرب 100 هستند قابلیت ثبت در سلول های یاد شده رو داشته باشند.




داغ کن - کلوب دات کام
نظرات() 


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


ساخت وبلاگ در میهن بلاگ

شبکه اجتماعی فارسی کلوب | اخبار کامپیوتر، فناوری اطلاعات و سلامتی مجله علم و فن | ساخت وبلاگ صوتی صدالاگ | سوال و جواب و پاسخ | رسانه فروردین، تبلیغات اینترنتی، رپرتاژ، بنر، سئو