جستجو برای:
  • صفحه اصلی
  • دوره های آموزشی
    • آموزش جامع اينترنت و ايميل
    • آموزش ورد
      • آموزش رايگان ورد
      • آموزش کامل تایپ پایان نامه با ورد
      • آموزش جامع مقدماتی و پیشرفته 2016 WORD
    • آموزش جامع اکسل
      • آموزش اکسل 2016 (مقدماتی تا پیشرفته)
      • آموزش کامل کاربرد اکسل در حسابداري
    • آموزش کامل Power point 2016
    • آموزش کامل اوت لوک 2016
    • آموزش جامع اکسس 2016
  • درباره ما
  • ناحیه کاربری
  • راهنماي خريد از آکادمي استاد آموز
 
  • 08642214286
  • ostadamooz@gmail.com
آکادمي استادآموز
  • صفحه اصلی
  • دوره های آموزشی
    • آموزش جامع اينترنت و ايميل
    • آموزش ورد
      • آموزش رايگان ورد
      • آموزش کامل تایپ پایان نامه با ورد
      • آموزش جامع مقدماتی و پیشرفته 2016 WORD
    • آموزش جامع اکسل
      • آموزش اکسل 2016 (مقدماتی تا پیشرفته)
      • آموزش کامل کاربرد اکسل در حسابداري
    • آموزش کامل Power point 2016
    • آموزش کامل اوت لوک 2016
    • آموزش جامع اکسس 2016
  • درباره ما
  • ناحیه کاربری
  • راهنماي خريد از آکادمي استاد آموز
0
ورود و ثبت نام

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

2019-10-20
ارسال شده توسط پشتيبان
excel
2.74k بازدید
data validation در اکسل

data validation در اکسل چیست و چه کاربردی دارد(آموزش تصویری و تضمینی)+ فیلم آموزش رایگان

data validationدر اکسل (اعتبار سنجی داده ها)

data validation این امکان را برای کاربران اکسل فراهم می کند تا داده ها بصورت درست و صحیح وارد سلول ها شوند و در صورت ورود مقادیر نادرست پیامی جهت مطلع کردن ، به کاربر نشان داده  شود .

برای استفاده از اعتبار سنجی داده ها در اکسل ،ابتدا مقادیری از متن ، عدد یا هر نوع داده ای را در  سلول ها وارد می کنیم .

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

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

براي آشنايي با ابزار هاي پيشرفته تب data مانند goal seek  ، سناريو ها و ابزار solver  و… مي تواند از بسته کاربرد اکسل در حسابداري استفاده نماييد .

data-validation

در تب اول (Settings)  ، در قسمت Allow ، مقادیر داده ای مختلفی وجود دارد که می توانیم در سلول ها وارد کنیم.

این مقادیر عبارتند از:

data-validation

 

  • Any value

  • whole number

  • Decimal

  • List

  • Data

  • Time

  • Text length

  • Custom

Any value:

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

whole number:

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

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

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

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

data-validation
data-validation
  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)

input message
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)

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

Error Alert
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 بزرگتر باشد .

data validation
data validation
  • لیست نمره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 کمتر باشد

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

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

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

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

AVERAGE(B2:B8)=

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

List:

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

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

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

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

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

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

  • در پنجره Data validation ،

روش دوم ساخت فهرست کشویی

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

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

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

icdl =

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

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

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

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

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

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

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

book.xlsx!icdl=

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

ایجاد لیست کشویی از فایل دیگر
ایجاد لیست کشویی از فایل دیگر

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

ایجاد فهرست کشویی با کمک جدول

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

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

روش کار:

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

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

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

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

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

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

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

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 سال پیش بزرگتر باشد

data validation
data validation
  • بازه مقادیر تاریخ از خانه 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 بررسی کنیم که آیا فرمول فوق در آنها صحت دارد یا خیر

سايتهاي خارجي که مي توانيد از آنها استفاده نماييد مانند :يودمي ، edx  ، oedb

اشتراک گذاری:
در تلگرام
کانال ما را دنبال کنید!
در اینستاگرام
ما را دنبال کنید!

مطالب زیر را حتما مطالعه کنید

Format cells در اکسل

Format cells در اکسل :(آموزش تمام نکات فرمت سل بصورت رایگان )-آکادمي استاد آموز

فرمول نویسی در اکسل

چگونه در اکسل فرمول نویسی کنیم : (آموزش تضمینی و جامع فرمول نویسی در اکسل)|آکادمي استاد آموز

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

توابع تاریخ و زمان در اکسل (آموزش تضمینی و رایگان +فیلم آموزش)|آکادمي استاد آموز

رسم نمودار در اکسل

آموزش کامل و رایگان رسم نمودار و انواع آن در اکسل و تمام تنظیمات نمودار|آکادمي استاد آموز

مدیریت شیت ها در اکسل

آموزش رایگان صفر تا صد مدیریت شیت ها در اکسل|آکادمي استاد آموز

مدیریت ستون ها و ردیف ها در اکسل

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

قدیمی تر تابع Match :آموزش جامع و رایگان تابع match+فيلم آموزش|آکادمي استاد آموز
جدیدتر آموزش جامع تنظیمات چاپ و پرینت گرفتن در اکسل|آکادمي استاد آموز

10 دیدگاه

به گفتگوی ما بپیوندید و دیدگاه خود را با ما در میان بگذارید.

  • محمد گفت:
    2020-11-18 در 2:06 ق.ظ

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

    پاسخ
    • Mehri گفت:
      2020-11-23 در 9:57 ق.ظ

      با سلام
      بله با گزینه data validation و انتخاب whole number می توانید کمترین و بیشترین مقدار عددی را وارد نمایید
      با تشکر

      پاسخ
  • بهرام گفت:
    2021-04-18 در 12:13 ب.ظ

    خيلي عالي بود دمتون گرم

    پاسخ
  • فاطمه سعيدي گفت:
    2021-04-18 در 12:14 ب.ظ

    سلام خيلي جامع و کامل و مفيد
    بود بسيار سپاسگذارم

    پاسخ
  • ساغز حسيني گفت:
    2021-04-18 در 12:18 ب.ظ

    سلام
    براي اينکه در يک سلول از ليستي از اطلاعات که در سلول هاي ديگر استرا نمايش دهد آيا مي توانيم از datavalidation استفاده کنيم و چطور بايد اين کار را انجام دهيم
    ممنون مي شم کمک کنيد

    پاسخ
    • پشتيبان گفت:
      2021-04-18 در 12:27 ب.ظ

      سلام
      داده هاي مود نظرتون را در سلول ها وارد نماييد سلول ي که مي خواهين ليست کشويي شود را انتخاب از تب dataگزينه data validation را انتخاب کنيد از قسمت allow گزينه list را انتخاب و از قسمت source داده هايي را که در سلول ها نوشته بودين را انتخاب کنيد و ok کنيد شما يک ليست کشويي درست کردين
      با تشکر

      پاسخ
  • زاهده رستمي پور گفت:
    2021-04-18 در 12:22 ب.ظ

    سلام
    من يک ليست کشويي از اطلاعات درست کردم اما فقط 10 مورد را نشون مي ده و بقيه را نمايش نمي ده لطف راهنمايي کنيد چيکار کنم تا همه اطلاعات را کامل نشون بده خيلي لازمش دارم ممکنه بي زحمت لطفا سريع تر پاسخ بدين
    خيلي سپاسگذارم

    پاسخ
    • پشتيبان گفت:
      2021-04-18 در 12:24 ب.ظ

      سلام در بعضي از نسخه های اکسل مخصوصا 2013 وقتی شیت شما right to left هست و از راست به چپ هست باید با استفاده از فلش های جهت صفحه لیست بالا و پایین کنید و اسکرول بار نمایش داده نمیشه.
      با تشکر

      پاسخ
  • فائقه گفت:
    2021-04-18 در 12:23 ب.ظ

    خوب بود ممنون

    پاسخ
  • ساسان گفت:
    2022-01-04 در 5:20 ب.ظ

    عالی بود. سپاس

    پاسخ

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

جستجو
جستجو برای:
استادآموز
محصولات آموزشي پر طرفدار
  • Word training
    آموزش جامع WORD( مقدماتی تا پیشرفته)آکادمي استاد آموز
  • excel-tutorials
    آموزش جامع اکسل (مقدماتی تا پیشرفته)|آکادمي استاد آموز
  • Access Training
    آموزش جامع ACCESS (مقدماتي تا پيشرفته)|آکادمي استاد آموز
  • آموزش پاورپوینت 2016
    آموزش پاورپينت (powerpoint) |آکادمي استاد آموز
  • آموزش اینترنت
    آموزش جامع اینترنت و ايميل|آکادمي استاد آموز
  • آموزش Outlook 2016
    آموزش اوت لوک|آکادمي استاد آموز
  • آموزش کامل تایپ پایان نامه با نرم افزار ورد
    آموزش کامل تایپ پایان نامه با ورد|آکادمي استاد آموز
  • آموزش کاربرد حسابداري در اکسل
    آموزش کاربرد اکسل در حسابداري|آکادمي استاد آموز
آخرين مقالات
  • Format cells در اکسل :(آموزش تمام نکات فرمت سل بصورت رایگان )-آکادمي استاد آموز
  • چگونه در اکسل فرمول نویسی کنیم : (آموزش تضمینی و جامع فرمول نویسی در اکسل)|آکادمي استاد آموز
  • توابع تاریخ و زمان در اکسل (آموزش تضمینی و رایگان +فیلم آموزش)|آکادمي استاد آموز
  • آموزش کامل و رایگان رسم نمودار و انواع آن در اکسل و تمام تنظیمات نمودار|آکادمي استاد آموز
  • آموزش رایگان صفر تا صد مدیریت شیت ها در اکسل|آکادمي استاد آموز

آکادمي استاد آموز

آموزش نرم افزار هاي مورد نياز بازار کار

086-42214286

ostadamooz@gmail.com

آخرين مقالات
  • Format cells در اکسل :(آموزش تمام نکات فرمت سل بصورت رایگان )-آکادمي استاد آموز
  • چگونه در اکسل فرمول نویسی کنیم : (آموزش تضمینی و جامع فرمول نویسی در اکسل)|آکادمي استاد آموز
  • توابع تاریخ و زمان در اکسل (آموزش تضمینی و رایگان +فیلم آموزش)|آکادمي استاد آموز
محصولات
  • آموزش کاربرد حسابداري در اکسل
    آموزش کاربرد اکسل در حسابداري|آکادمي استاد آموز
  • آموزش کامل تایپ پایان نامه با نرم افزار ورد
    آموزش کامل تایپ پایان نامه با ورد|آکادمي استاد آموز
  • آموزش Outlook 2016
    آموزش اوت لوک|آکادمي استاد آموز
تمامی حقوق محصولات و محتوای اين سایت متعلق به سایت استاد آموز می باشد

ورود

رمز عبور را فراموش کرده اید؟

هنوز عضو نشده اید؟ عضویت در سایت