اکسل: از جدول به عنوان لیست اعتبار سنجی داده استفاده کنید

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

اگر یک ورودی را بارها و بارها تکرار کنید ، ایجاد یک جدول در MS Excel بسیار ناامید کننده خواهد بود.

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

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

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

با استفاده از این جدول ، بیایید ستون B را تنظیم کنیم تا انواع هزینه بتوانند لیست کشویی را انتخاب کنند ، و فقط از آنجا ، برای جلوگیری از تنظیم هر بار آنها ، و جلوگیری از هرگونه اشتباه در ورود اطلاعات.

راه حل 1: با ورودی مستقیم لیستی ایجاد کنید

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

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

در برگه تنظیمات ، گزینه allow drop down را نمایش دهید و گزینه List را انتخاب کنید. با این کار می توانید لیستی از مقادیر مجاز را برای سلولهای داده انتخاب شده ایجاد یا انتخاب کنید.

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

به سادگی با کلیک بر روی تأیید اعتبار ، و لیست اعتبار سنجی کشویی شما برای سلولهای انتخاب شده ایجاد می شود.

اگر یکی از این سلول ها را انتخاب کنید ، یک نماد در انتهای سلول ظاهر می شود و با کلیک بر روی آن ، لیست اعتبار کشویی نمایش داده می شود ، به شما اجازه می دهد مقداری را وارد کنید که به طور خودکار بر اساس سلول وارد شود لیست ورودی مستقیم

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

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

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

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

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

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

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

ورودی های اعتبار سنجی داده را در صفحه دیگری تنظیم کنید

تبدیل به یک Excel Pro شوید: به دوره ما بپیوندید!

مهارت های خود را از Novice to Hero با دوره Basics Excel 365 ما ، طراحی کنید که شما را فقط در چند جلسه مهارت می کند.

ثبت نام در اینجا

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

داده های خود را در آنجا قرار دهید ، جدول را انتخاب کنید و از گزینه های جداول: جدول در برگه درج استفاده کنید ، تا لیست مقادیر خود را به جدول تبدیل کنید که با افزودن یا حذف مقادیر به طور خودکار مجدداً اصلاح می شود - و همچنین اعتبار سنجی داده های شما نیز خواهد بود.

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

به شما این امکان را می دهد که با وارد کردن نام جدول به جای دامنه ، این جدول را در هر مکان ، از جمله در یک لیست اعتبارسنجی داده ، ارجاع دهید!

چگونه می توان سلول های خالی را در لیست اعتبارسنجی داده ها مجاز دانست

اگر می خواهید مقادیر خالی را در فیلدی که اعتبار سنجی داده فعال است ، مجاز کنید ، باید یکی از سلول هایی را که اعتبار سنجی داده برای آن فعال شده است ، انتخاب کرده و به برگه داده بروید ، تا منوی اعتبار سنجی داده را باز کنید (در بالا دکمه را ببینید )

در آنجا مطمئن شوید که گزینه ignore blank علامت گذاری شده است ، و قبل از کلیک بر روی تأیید بر روی اعمال این تغییرات در تمام سلولهای دیگر با همان تنظیمات کلیک کنید.

سپس می توانید با انتخاب یک سلول و حذف محتوا ، بدون دریافت پیام خطا مبنی بر اینکه محتوای سلول مجاز نیست ، سلول را خالی کنید.

چگونه مقادیر خالی را در یک لیست اعتبارسنجی داده غیرفعال کنیم

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

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

دوباره به لیست کشویی اعتبار سنجی داده خود بروید ، خطا ظاهر می شود - و دیگر نمی توان سلول را خالی بگذارید.

نحوه حذف لیست کشویی اعتبار سنجی داده ها

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

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

سپس ، روی پاک کردن همه کلیک کنید و اعتبار سنجی داده ای که انتخاب کرده اید از کتاب کار MS Excel حذف خواهد شد!

VBA: لیست اعتبار سنجی را اضافه کنید

افزودن لیست اعتبار سنجی با VBA به آسانی استفاده از تابع زیر است ، جایی که A1 سلول است که داده ها در آن با اعتبار سنجی داده های ذخیره شده در سلول های B1 تا B5 اعتبار سنجی می شوند.

محدوده ("a1"). اعتبار سنجی _. xlValidateList را تغییر دهید ، xlValidAlertStop ، "= $ B $ 1: $ B $ 5"

با استفاده از ویژگی های پیشرفته عملکرد لیست اعتبار سنجی داده VBA ، می توان چندین لیست کشویی وابسته در Excel VBA ایجاد کرد که هر کدام زمینه های مختلف را در برابر لیست های مختلف اعتبارسنجی داده ها تأیید می کنند و عناوین ، عنوان خطا ، پیام ها و پیام های خطای خاص خود را دارند .