data validation در اکسل چیست یا اعتبار سنجی داده ها(آموزش تصویری و تضمینی)|آکادمي استاد آموز

• Any value
  

• whole number

• Decimal

• List

• Data

• Time

• Text length

• Custom

Any value:

هرنوع داده ای اعم از متن ، عدد ، عدد اعشاری، تاریخ ، ساعت و….می توان در سلول ها وارد کرد.

whole number:

• فقط اعداد صحیح در خانه های اکسل می تواند وارد شود .

• وقتی این گزینه را انتخاب می کنیم باکس Data و باکس minimum  و Maximum فعال می شود .

• در قسمت Data ، شروط مختلفی برای ورود داده های عددی  می توانیم در نظر بگیریم.

مانند “بین مقدار مینیمم و ماکزیمم ،بزرگتر ، کوچکتر ، مساوی و نامساوی و ….  “می توانیم مطابق شکل زیر استفاده کنیم .

1. between:  مقادیر قابل قبول بین دو مقدار مینیمم و ماکزیمم باید باشد.
   

2. not between: مقادیر درست بین دو مقدار مینیمم و ماکزیمم نباشد.

3. equal to: مقادیر درست مساوی یک مقدار خاصی است .
   

4. not equal to: مقادیر قابل قبول مخالف یک مقدار خاصی باشد .

5. greater than: مقادیر درست بزرگتر از یک مقدار خاص است.

6. less than:مقادیر درست کمتر از یک مقدار است .

7. greater than or equal to: مقادیر درست بزرگتر مساوی یک مقدار باید باشد.

8. less than or equal to : مقادیر درست کوچکتر مساوی یک مقدار است.

برای مثال لیستی از اعداد داریم که می خواهیم مقادیر آنها بین 10 تا 20 باشد .

• بازه اعداد  از خانه A2 تا A8 را انتخاب و روی گزینه Data validation کلیک می کنیم .
  

• از قسمت Data، مقدار Between  و در قسمت Minimum، عدد 10 و در قسمت Maximum، عدد 20 را وارد می کنیم .

• حال اگر در بازه ی اعداد انتخابی(A2 تا A8) عدد 21 یا 5-  ویا هر عددی که بین دو مقدار 10 و 20 نباشد را وارد کنیم  بعد از فشردن اینتر  ارور می دهد .

• در قسمت مینیمم و ماکزیمم می توانیم از توابع هم استفاده کنیم برای مثال از تابع  Max یا min و یا …

 نمایش پیام راهنما به هنگام ورود مقادیر (Input message)

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

برای این کار بعد از انتخاب Data validation به تب Input message رفته و تنظیمات زیر را وارد می کنیم

•   title :عنوانی را برای خطا وارد می کنیم .

• برای مثال: (توجه)
  

•   Input message :محتوای پیام را وارد می کنیم .

• برای مثال :(لطفا مقادیر عددی بین 10 تا 20 وارد نمایید ) 

• اگر تیک گزینه show input message when cell is selected ، را بزنیم.

در این حالت وقتی کاربر سلولی را انتخاب می کند تا محتوا را در آن وارد کند پیام Input message برای کاربر جهت راهنمایی نمایش داده می شود.

نمایش دیالوگ خطا(Error Alert)

به هنگام ورود داده های نادرست بعد از فشردن اینتر پیام خطا به کاربر نشان داده می شود.

بعد از انتخاب Data validation ، در تب Error Alert، تنظیمات زیر را وارد می کنیم .

•  title : عنوان خطا را وارد می کنیم .برای مثال :داده نامعتبر
  
• Error message: پیام خطا را وارد می کنیم.برای مثال : لطفا مقادیر عددی بین 10 تا 12 وارد نمایید
  
• style:  شکل آیکن پنجره خطا را تعیین می کنیم که می تواند به سه شکل زیر باشد.

1. stop: به هیچ عنوان مقادیر نادرست را نمی پذیرد .فقط با گزینه retry می توان مقادیر را ویرایش کرد.
   
2. warning:بعد از نمایش خطابا فشردن yes مقادیر غلط در خانه می تواند قرار  بگیرد.
   
3. information: با فشردن دکمه OK بعد از نمایش پیام خطا ،مقادیر نادرست در خانه قرار می گیرد.
   

نکته : در تب Error Alert ،در بالای صفحه اگر تیک گزینه show error elert after invalid data is intered  را بزنیم .به هنگام ورود مقادیر اشتباه پیام خطا نمایش داده می شود و کاربر متوجه خطای خود می شود.اما اگر این گزینه تیک نخورد هنگام ورود داده های ناصحیح خطایی نمایش داده نمی شود .

برای مثال:لیستی از نمرات داریم می خواهیم نمره1 از تعداد نمره2 بزرگتر باشد .

• لیست نمره1 از خانه A2 تا A8 را انتخاب می کنیم.
•   از تب Data ، روی گزینه Data validation کلیک می کنیم .
• در قسمت Allow گزینه ی whole number  را انتخاب می کنیم .
• و از قسمت Data، گزینه greater than را انتخاب می کنیم .
  
• و در آخر در قسمت Minimum ،مساوی را وارد و  تابع count را به شکل زیر می نویسیم .
  

COUNT(B2:B8)=

همانطور که در شکل ملاحظه می کنید تمام نمرات 1 از تعداد نمره2 که عدد7 می باشد بزرگتر است.در ضمن تمام اعداد ورودی باید عدد صحیح باشند .

نکته :اگر تیک گزینه Ignore Blank  را بزنیم سلول های خالی را بررسی نمی کند.

نکته :اگر تیک گزینه  in-cell dropdown  را برداریم حالت فهرستی حذف می شود فقط در خانه محتوا را وارد می کنیم و با گزینه List  مقایسه می شود و در صورت مقادیر نادرست ارور می دهد.

نکته: اگر تیک پایین صفحه Data validation را بزنیم (Aplay these changes to all ather cells with the same setting) با تغییر تنظیمات Data validation ، تمام سلول هایی که دقیقا همین Data validation  به آنها اعمال شده ، تغییرات اعمال می شود اما اگر تیک را نزنیم تغییرات فقط به سلول های انتخابی اعمال می شود .

Decimal :

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

برای مثال می خواهیم تمام نمرات1 از میانگین نمره 2 کمتر باشد

• لیست نمره1 از خانه A2 تا A8 را انتخاب می کنیم .

• از تب Data ، روی گزینه Data validation کلیک می کنیم .

• از قسمت Allow ، گزینه Decimal و از قسمت  Data، گزینه کوچکتر یا مساوی (Less than or equal to )

• در قسمت maximum ، تابع میانگین را وارد می کنیم و فرمول زیر را می نویسیم .
  

AVERAGE(B2:B8)=

میانگین نمره2 عدد 15.85714 که تمام نمرات نمره 1 باید کوچکتر یا مساوی این عدد باشد .

Data validation list  در اکسل:

روش اول ساخت لیست کشویی

• با این گزینه می توانیم لیست یا فهرست کشویی ایجاد کنیم.

• برای مثال نام سه کتاب را در خانه های A2 تا A4 وارد می کنیم.

• در خانه C2 کلیک می کنیم و از تب Data گزینه Data validation  را انتخاب می کنیم .

• در قسمت Allow  ، گزینه List را انتخاب و در قسمت Source ، روی نام کتاب ها از خانه A2 تا A4 درگ می کنیم.

• سپس در خانه C2 یک فهرست کشویی از نام کتاب ها ایجاد می شود .

• در پنجره Data validation ،

روش دوم ساخت لیست کشویی(Data validation list  دراکسل)

• از خانه A2 تا A4  را با درگ انتخاب می کنیم در نوار فرمول در قسمت Name box نامی را برای محدوده انتخابی (مثلا icdl)وارد می کنیم و اینتر می زنیم

• یا اینکه از تب Formulas ، گروه   Defined Name با کلیک بروی گزینه Define Name در قسمت Name، نامی را برای محدوده انتخابی وارد و OK می کنیم .

• سپس روی  خانه C3 کلیک می کنیم .از تب Data گزینه Data validation  را انتخاب می کنیم و در قسمت Allow ، روی گزینه List را کلیک می کنیم و در قسمت Source

icdl =

را می نویسیم فهرست کشویی در خانه C3 ایجاد می شود.

روش سوم ساخت  Data validation list  در اکسل از اطلاعات فایل دیگر 

• فایل دوم را باز کرده خانه ای  برای مثال A1 را انتخاب می کنیم.

• از تب Formulas روی Define name کلیک می کنیم تا نامی را برای این خانه انتخاب کنیم .

• نام Book name ، را در قسمت Name وارد می کنیم.

• در قسمت Refers to ،ابتدا نام فایل سپس ! ونام محدوده ای که در فایل اول قرار است انتخاب شود(icdl) را وارد می کنیم .

• محدوده icdl برای نام کتب در مثال های بالا ذکر شد.

book.xlsx!icdl=

• سپس از قسمت Data validation ، در قسمت Allow، گزینه List و در قسمت Source، نام Bookname را وارد می کنیم .

فهرست کشویی شامل نام کتب icdl، word، Excel در خانه A1 ایجاد شد.

ایجاد Data validation list  در اکسل با کمک جدول

ایجاد فهرست با این روش همانند  روش های قبل  است تنها تفاوت آن این است که اگر از جدول استفاده کنیم می توانیم داده های لیست را در صورت نیاز بیشتر یا کمتر کنیم و لیست با تغییر آدرس اتوماتیک آپدیت می شود.

اما اگر از روش های قبل استفاده کنیم اگر داده ها اضافه شوند باید دوباره بازه داده ها انتخاب شود و مجدد نامگذاری و با گزینه list از data validation ، انتخاب شود .

روش کار:

• داده هایی که قرار است لیست شوند را در خانه های اکسل وارد می کنیم .

• سپس بازه داده ها را انتخاب و دکمه Ctrl+T را باهم فشار می دهیم.

• و یا از تب Insert ،گزینه Table را انتخاب می کنیم . 

• سپس در پنجره Create table  ، آدرس جدول را کپی می کنیم .

• از تب Data ، گزینه Data validation را انتخاب می کنیم .

• گزینه list را از قسمت Allow کلیک می کنیم.

• و در قسمت Source ، آدرسی را که کپی کردیم را Past  می کنیم .

تغییر فونت Data validation

اگرفونت لیست کشویی ایجاد شده خیلی کوچک بود و محتوا به راحتی دیده نمی شد  برای حل این مشکل باید سایز فونت سلول را کم و مطابق شکل بزرگنمایی صفحه اکسل از سمت راست پایین صفحه را روی 100 یا 115 قرار دهیم تا مشکل بر طرف شود .

دیتا ولیدیشن پیشرفته (اتصال دو Data validation  در اکسل)

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

ابتدا از گروه محصولات یک لیست کشویی ایجاد می کنیم برای این کار مقابل خانه گروه محصول کلیک می کنیم و از تب data گزینه ی Data validation را انتخاب می کنیم از قسمت Allow گزینه list  و در قسمت Source ، گروه محصولات که شامل خشکبار ، حبوبات و سبزی جات می باشد را انتخاب می کنیم .

حالا مقابل خانه محصول ،  می خواهیم محصولات هر گروه ی که انتخاب می شود نمایش داده شود .برای مثال ، اگر سبزی جات در گروه انتخاب شد در این خانه ، سیب زمینی ،پیاز و سبزی خشک در لیست کشویی نشان داده شود .

برای این کار باید لیست محصولات هر گروه به نام گروه متصل شود کل جدول محصولات و گروه را انتخاب می کنیم و از تب Formulas گزینه ی Create From Selection  را انتخاب می کنیم. و با گزینه ی Name Manager هم می توانیم چک کنیم نام هر گروه و محصولات  ثبت شده اند.

برای اتصال لیست محصولات به گروه محصول ، در خانه مقابل نام محصول کلیک می کنیم و از تب Data  ، گزینه ی  Data validation را انتخاب می کنیم و گزینه list ، را انتخاب و در قسمت Source ، عبارت INDIRECT(F4)=  را تایپ می کنیم F4 آدرس خانه گروه محصول می باشد .

Date

• برای مقادیر تاریخ کاربرد دارد

• اگر برای محدوده از داده ها که انتخاب شده اند  روی data validation کلیک کنیم از قسمت Allow گزینه Date را انتخاب کنیم .

• و همان مقادیر بزرگتر ، کوچکتر ، بین مقادیر ، مساوی و…. را وارد کنیم .
  

• می توانیم برای صحت ورود داده های تاریخ ، در قسمت Start date و End date مقادیری را وارد کنیم .

برای مثال:در خانه B1 ، تاریخ 1/5/2019 را وارد می کنیم می خواهیم تمام تاریخ هایی که وارد می کنیم  کوچکتر یا مساوی این مقدار باشد

• بازه مقادیر تاریخ از خانه A2 تا  A6  را با درگ انتخاب می کنیم .
  

• از تب Data گزینه Data validation  را کلیک می کنیم

• از لیست Allow گزینه Data را انتخاب و در قسمت Data گزینه کوچکتر مساوی (Less than or equal to ) را انتخاب می کنیم.

• در قسمت End date  تاریخ 1/5/2019  را وارد می کنیم .

• و یا روی خانه B1 کلیک می کنیم  

• اگر آدرس خانه را انتخاب کرده باشیم باید حتما  F4 را  بزنیم تا ادرس B1 بصورت مطلق شود . (B$1$)

• و دکمه Ok را می زنیم .

• حالا در خانه های A2 تا A6 اگر مقادیر کوچکتر یا مساوی وارد کنیم مشکلی نداریم و اگر مقادیر بزرگتر از تاریخ فوق وارد شود خطا می دهد .

مثال: می خواهیم مقادیر تاریخی که وارد می کنیم از تاریخ امروز کوچکتر و از تاریخ 15 سال پیش بزرگتر باشد

• بازه مقادیر تاریخ از خانه A2 تا  A6  را با درگ انتخاب می کنیم .

• گزینه Data validation  را کلیک می کنیم

• از لیست Allow گزینه Date را انتخاب و در قسمت Data گزینه Between  را انتخاب می کنیم.

• در قسمت   Start date ، تابع ()today =که تاریخ امروز را نشان می دهد وارد می کنیم.

• در قسمت End date  برا محاسبه تاریخ 15 سال پیش بصورت زیر عمل می کنیم .

• 15*365-()today=

• و دکمه Ok را می زنیم .

Time

• همانند نوع داده Data برای مقادیر ساعت کاربرد دارد.

• اگر برای محدوده از داده ها که انتخاب شده اند  روی data validation کلیک کنیم از قسمت Allow گزینه time را انتخاب کنیم .

• و همان مقادیر بزرگتر ، کوچکتر ، بین مقادیر ، مساوی و…. را وارد کنیم .
  

• می توانیم برای صحت ورود داده های ساعت ، در قسمت Start timeو End time مقادیری را وارد کنیم .

text length

• برای نوع داده متن کاربرد دارد

• می توانیم طول مقادیر متنی که در خانه های اکسل وارد می کنیم را تعیین کنیم

• طول متن می تواند بزرگتر ،کوچکتر، بین دو مقدار ماکزیمم و مینیمم ،مساوی مقدار خاص و … باشد

Custom

می توانیم در این بخش فرمول وارد کنیم و خانه های اکسل را با Data validation بررسی کنیم که آیا فرمول فوق در آنها صحت دارد یا خیر