روش های ایجاد لیست کشویی در Data Validation

امتیاز 5.00 ( 1 رای )

روش های ایجاد لیست کشویی در Data Validation:

روش های ایجاد لیست کشویی با Data Validation

روش های ایجاد لیست کشویی با Data Validation

در نرم افزار اکسل می توانید ورود داده ها توسط کاربر را با استفاده از ابزار Data Validation محدود سازید که به این کار اعتبار سنجی داده ها در اکسل گفته می شود. به عنوان مثال، در یک سلول مشخص، ورود نمره فقط بین ۰ تا ۲۰ باشد. در یک سلول، ورود اسم استان ها، تنها بر اساس لیست کشویی ایجاد شده با ابزار Data Validation ممکن باشد و… و در صورتی که کاربر خارج از محدوده مشخص شده شما، داده دیگری وارد کرد، پیغام هشداری به نشانه ورود اطلاعات غلط به کاربر نمایش داده شود. مثال: “اسم استان خود را از لیست کشویی انتخاب کنید.

<<حتما در سایت ثبت نام کنید و فیلم آموزش این مبحث کاربردی اکسل را از زیر دانلود و بعد این آموزش حتما دو آموزش تهیه لیست های کشویی وابسته اکسل و تهیه لیست های کشویی وابسته داینامیک اکسل را مشاهده بفرمایید.>>

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

مسیر دسترسی به ابزار Data Validation به صورت زیر است:

Data>>>Data Tools>>>Data Validation

مسیر دسترسی به ابزار Data Validation

مسیر دسترسی به ابزار Data Validation

فعالسازی ابزار و معرفی پنجره Data Validation:

پنجره Data Validation

پنجره Data Validation

زبانه Setting:

در این زبانه بایستی نوع اعتبار سنجی داده ها یا نوع محدودیت ورود اطلاعات را وارد کنید. محدودیت ورود اطلاعات به سه دسته تقسیم می شوند، دسته اول شامل گزینه های Whole Number (عدد)، Decimal (عدد علمی)، Date و  Time (تاریخ و زمان) و Text Length (طول رشته متنی) میباشد. دسته دوم گزینه List و دسته سوم Custom است. به عنوان مثال، اگر گزینه ی Whole Number را انتخاب کنیم، به این معنا است که تنها امکان ورود داده های عددی (اعداد) می باشند.

به عنوان مثال؛

اگر گزینه ی Whole Number را انتخاب کنیم، به این معنا است که تنها داده های عددی (اعداد) به عنوان محتوای سلول مورد نظر معتبر می باشند. پس از انتخاب Whole Number گزینه Data فعال می شود که امکان انتخاب حالت های زیر وجود دارد:

  • اگر می خواهید امکان ورود اعداد بین دو عدد خاص باشد، گزینه ی Between،
  • برای می خواهید امکان ورود اعداد خارج از بازه دو عدد خاص باشد، گزینه ی Not Between،
  • برای می خواهید امکان ورود تنها یک عدد خاص باشد، Equal to،
  • برای می خواهید امکان ورود اعدادی غیر از یک عدد خاص باشد، گزینه ی Not Equal to،
  • اگر می خواهید امکان ورود اعداد بزرگتر از یک عدد خاص باشد، گزینه ی Greater Than،
  • اگر می خواهید امکان ورود اعداد کوچکتر از یک عدد خاص باشد، گزینه ی Less Than،
  • اگر می خواهید امکان ورود اعداد بزرگتر مساوی یک عدد خاص باشد، گزینه ی Greater than or Equal to،
  • و در نهایت اگر می خواهید امکان ورود اعداد کوچکتر مساوی یک عدد باشد، خاص گزینه ی Less than or Equal to.
Data-Validation-در-اکسل

Data-Validation-در-اکسل

مهمترین و کاربردی ترین نوع Data Validation، استفاده از آن در تهیه لیست های کشویی (List) است. که در ادامه روش های ایجاد لیست کشویی توضیح داده می شود.

<<حتما در سایت ثبت نام کنید و فیلم آموزش این مبحث کاربردی اکسل را از زیر دانلود و بعد این آموزش حتما دو آموزش تهیه لیست های کشویی وابسته اکسل و تهیه لیست های کشویی وابسته داینامیک اکسل را مشاهده بفرمایید.>>

زبانه Input Message:

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

زبانه Error Alert:

زبانه Error Alert ابزار Data Validation

زبانه Error Alert ابزار Data Validation

در این زبانه می توانید به کاربر پیغام هشداری را تعریف کنید که به محض ورود داده نامعتبر(خارج از محدوده تعریف شده) به کاربر در یکی از حالات هشدار(warning)، خبر(Information) و ایست(Stop) نمایش داده شود. برای فعال سازی ای پیغام حتما تیک گزینه show error alert after invalid data is entered را بزنید.

Stop: در این حالت به هیج وجه امکان ورود داده ای خارج از محدوده تعریف شده را ندارید.

Warning: دراین حالت شما امکان ورود اطلاعات خارج از محدوده را دارید اما با هشدار که می توانید مورد yes ، No و cancel را انتخاب کنید.

Information: دراین حالت شما امکان ورود اطلاعات خارج از محدوده را دارید اما با هشدار که می توانید OK و cancel را انتخاب کنید.

دایره داده های بدون اعتبار (Circle Invalid Data):

ابزار Data Validation اکسل

ابزار Data Validation اکسل

با استفاده از این گزینه به راحتی به دور تمامی آن دسته از سلول هایی که برای آنها Data Validation ایجاد شده و داده بدون اعتبار و خارج از محدوده برای آن وارد شده باشد، دایره قرمز رنگی ایجاد می شود که می توانید با کلیک بر روی Clear Validation Circles آن را حذف نمایید.


همانطور که پیشتر گفته شد، مهمترین و کاربردی ترین نوع Data Validation، استفاده از آن در تهیه لیست های کشویی (List) است.

انواع روش های ساخت لیست کشویی با استفاده از ابزار Data Validation اکسل:

بعد از انتخاب مورد List از قسمت Allow در ابزار Data Validation، گزینه ای به اسم Source فعالیت می شود. با ورود اطلاعات به روش های مختلفی که در این بخش ارائه می شود. در سلول مد نظر لیست کشویی ایجاد می شود.

روش اول-روش دستی-Manually:

در این روش به صورت دستی مطابق با تصویر زیر، در قسمت Source آن دسته از مواردی را که قسمت داریم در لیست کشویی نمایش داده شود با جدا کننده کاما(,) از یکدیگر جدا و وارد می کنیم.

روش اول-روش دستی

روش اول-روش دستی

روش دوم-رفرنس دهی به محدوده ای از اکسل-From Cells:

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

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

روش دوم-رفرنس دهی به محدوده ای از اکسل

روش دوم-رفرنس دهی به محدوده ای از اکسل

روش سوم- فرمول نویسی-Formula in Data Validation:

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

(یک , تعداد ردیف لیست کشویی , صفر , یک , مبدا حرکت که قبل از لیست انتخاب شود) offset=

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

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

روش سوم- فرمول نویسی

روش سوم- فرمول نویسی

روش چهارم- فرمول نویسی(تکمیلی)-Formula in Data Validation:

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

(یک , (محدوده ای فراتر از لیست کشویی موجود)Counta , صفر , یک , مبدا حرکت که قبل از لیست انتخاب شود) offset=

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

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

روش چهارم

روش چهارم-فرمول نویسی(تکمیلی)

<<حتما در سایت ثبت نام کنید و فیلم آموزش این مبحث کاربردی اکسل را از زیر دانلود و بعد این آموزش حتما دو آموزش تهیه لیست های کشویی وابسته اکسل و تهیه لیست های کشویی وابسته داینامیک اکسل را مشاهده بفرمایید.>>

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