نحوه ایجاد یک لیست پایین بازشو با ارزش یابی داده ها (اکسل)

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

ایجاد یک لیست پایین‌بازشو (Drop-down List) در اکسل یکی از کاربردی‌ترین ابزارها برای جلوگیری از خطای تایپی و سرعت بخشیدن به وارد کردن داده‌هاست. این کار از طریق ابزاری به نام Data Validation انجام می‌شود.

آموزش سریع و آسان

در ادامه، مراحل گام‌به‌گام برای ایجاد این لیست را بررسی می‌کنیم:

روش اول: وارد کردن دستی گزینه‌ها (برای لیست‌های کوتاه)

اگر فقط چند گزینه محدود دارید (مثلاً: "تایید شده"، "در حال بررسی"، "رد شده"):

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

2. به تب Data در نوار بالای اکسل بروید.

3. روی گزینه Data Validation کلیک کنید.

4. در پنجره باز شده، در تب Settings، در بخش Allow، گزینه List را انتخاب کنید.

5. در کادر Source، گزینه‌های خود را بنویسید و آن‌ها را با علامت ویرگول (,) از هم جدا کنید (مثلاً: تایید شده,در حال بررسی,رد شده).

6. روی OK کلیک کنید. حالا یک فلش کوچک در کنار سلول‌ها ظاهر می‌شود که می‌توانید لیست را از آنجا انتخاب کنید.

روش دوم: استفاده از محدوده سلول‌ها (برای لیست‌های طولانی و حرفه‌ای)

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

1. در یک ستون خالی یا یک شیت دیگر، لیست گزینه‌های خود را زیر هم بنویسید (مثلاً از سلول A1 تا A10).

2. سلولی که می‌خواهید لیست پایین‌بازشو در آن باشد را انتخاب کنید.

3. به تب Data رفته و Data Validation را باز کنید.

4. در بخش Allow، گزینه List را انتخاب کنید.

5. در کادر Source، روی آیکون کوچکِ انتهای کادر کلیک کنید و سپس محدوده‌ای که لیست خود را در آن نوشتید (مثلاً از A1 تا A10) را با موس انتخاب کنید.

6. OK را بزنید. حالا اگر در آن محدوده A1 تا A10 هر تغییری ایجاد کنید، لیست شما به‌صورت خودکار به‌روزرسانی می‌شود.

نکات کلیدی برای حرفه‌ای‌تر شدن:

• پیام خطا (Error Alert): در همان پنجره Data Validation، به تب Error Alert بروید. در اینجا می‌توانید متنی بنویسید که اگر کاربر به اشتباه چیزی غیر از گزینه‌های لیست شما تایپ کرد، اکسل به او هشدار دهد.

• لیست‌های پویا (Dynamic List): اگر می‌خواهید لیست شما همیشه به طور خودکار با اضافه شدن آیتم جدید بزرگتر شود، بهتر است محدوده لیست خود را به Table تبدیل کنید (با زدن Ctrl+T) و سپس همان محدوده جدول را در بخش Source معرفی کنید.

• مخفی کردن لیست: برای اینکه لیست منبع شما در فایل مشخص نباشد، می‌توانید ستونِ شاملِ گزینه‌ها را انتخاب کرده و با کلیک راست روی آن، گزینه Hide را بزنید.

توضیحات کامل و گام به گام تصویری

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

در ستون A لیست محصولاتمان داریم و می خواهیم لیست کشویی همه محصولات را تهیه کنیم.

برای این کار مراحل زیر را دنبال کنید:

• هر سلول در worksheet را که می خواهید انتخاب کنید.
• برای ایجاد لیست کشویی، به سربرگ Data > Data Tools > سپس روی Data Validation کلیک کنید.

پایگاه داده SQL Server رو قورت بده! بدون کلاس، سرعت 2 برابر، ماندگاری 3 برابر، پولسازی بلافاصله ... دانلود:

پک کامل SQL Server

پک مبانی برنامه نویسی (اختیاری)

• یا می توانیم از میانبر ALT + D + L استفاده کنیم.
• کادر گفتگوی Data Validation ظاهر می شود.

• در لیست Allow، گزینه “List” را انتخاب کنید.

• در سربرگ Source، محدوده لیست محصولاتتان را انتخاب کنید.

توجه: قبل از کلیک بر روی OK، متوجه خواهید شد که به طور پیش فرض، تیک کادر های “Ignore blank” و “In-cell Dropdown” زده شده است. اولین کادر “Ignore blank” برای اجازه دادن یا ندادن ورود سلول های خالی به عنوان ورودی است. اگر تیک آن را بردارید اکسل اجازه ورود سلول های خالی را نمی دهد و در صورت وارد شدن سلول خالی یک پیام خطا ظاهر می شود. “In-cell dropdown” برای نمایش فلش کنار سلول (فلش لیست کشویی) استفاده می شود. بنابراین، اگر تیک این گزینه را بردارید، فلش کشویی ناپدید می شود.

• اکنون به قسمت “Input Message” می رویم و در آنجا در کادر title، “Select Product Name” و در کادر input message، “Select the product name from the list” را می نویسیم.

سپس به قسمت “Error Alert” می رویم. خواهید دید که style به طور پیش فرض بر روی “STOP” قرار دارد. بنابراین، در کادر title، “Invalid Product Name” و سپس در کادر error message، “Please select the product name from the list” را می نویسیم. و اکنون روی OK کلیک می کنیم.

با کلیک بر روی OK، می توانیم با زدن فلش کشویی پیام ورودی را در یک کادر زرد مشاهده کنیم.

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

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

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

در صورتی که می خواهید اعتبارسنجی داده ها را به طور کامل از سلول یا دامنه حذف کنید، تنها کافی است به پنجره data validation بروید. در آنجا گزینه “Clear All” را مشاهده می کنید.

روی آن کلیک کنید و سپس بر روی OK کلیک کنید. وقتی بر روی enter کلیک کنید اعتبارسنجی داده ها از سلول حذف می شوند.

لیست ۵۰ تا از کاربردی ترین فرمول های اکسل (توابع مهم)

چگونه در اکسل درصد بگیریم؟ آموزش تصویری سریع

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

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

• خب، همان لیست قبلی که از A9 تا A15 مرتب شده است را داریم.
• محدوده را انتخاب می کنیم و اکنون می توانیم به کادر name برویم یک نام برای این محدوده تعریف کنیم یا می توانیم از قسمت Define Name برای نام گذاری آن استفاده کنیم.
• به سربرگ Formula > Defined Names بروید و سپس بر روی Define Name کلیک کنید.

• حالا “Product_Name” را در در کادر name می نویسیم. محدوده ای که قبلاً انتخاب کرده ایم را در اینجا مشاهده می کنیم. سپس بر روی OK کلیک می کنیم.

• دوباره به پنجره Data Validation می رویم و در قسمت validation criteria، “List” را انتخاب می کنیم. این بار به جای انتخاب محدوده، نامی که در قسمت defined name وارد کرده بودیم را در کادر source میبینیم.

توجه: اگر نامی را که به این لیست داده اید به خاطر دارید، می توانید آن را در اینجا تایپ کنید. برای این کار ابتدا علامت “=” و یا دکمه F3 را فشار دهید تا پنجره “PASTE NAME” ظاهر شود. می توانید تمام نامهای موجود در workbook را در این پنجره پیدا کنید.

• ما محدوده را “Product_Name” نامگذاری کردیم، روی آن کلیک می کنیم و سپس OK را کلیک می کنیم.
• ما بر روی OK کلیک می کنیم تا لیست کشویی این سلول ساخته شود.

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