ساخت لیست کشویی در اکسل — آموزش تصویری و گام به گام + فیلم آموزشی

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

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

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

«لیست کشویی» (Drop down) یا «لیست ترکیبی» (Combo-box)، یکی از ابزارهای API ویندوز محسوب می‌شود که برنامه‌های کاربردی از آن به عنوان روشی برای انتخاب یک گزینه از لیست تعریف شده استفاده می‌کنند. فرض کنید در یک ستون از کاربرگ اکسل، بخواهید اقلامی را ثبت کنید که مثل استان محل تولد کارکنان، محدود و از قبل مشخص هستند.

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

کلیک کنید

فیلم آموزش کاربرد اکسل Excel در مهندسی صنایع در فرادرس

کلیک کنید

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

واضح است که چنین کاری حداقل، دو مزیت و فایده خواهد داشت.

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

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

به هر حال در این متن به دو شیوه ساخت لیست کشویی برای ورود اطلاعات در یک فرم اکسل را آموزش می‌دهیم که البته عمومی‌تر نیز هستند. روش اول به کمک لیستی از مقادیر غیر تکراری است و راه‌کار بعدی هم با استفاده از ابزار «اعتبار سنجی داده» (Data Validation) در اکسل صورت می‌گیرد.

به کمک ابزارهای برنامه‌نویسی و VBA نیز می‌توان یک لیست کشویی در اکسل ایجاد کرد. ولی از آنجایی که به ابزارهای برنامه‌نویسی و تسلط بر Visual Basic احتیاج است، در این متن به این مورد نخواهیم پرداخت.

لیست کشویی با کمک مقادیر تکراری

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

وقتی در ستون مثلا B، اسامی چند استان را وارد کنید، اکسل فرض می‌کند که بقیه مقادیر نیز می‌توانند یکی از این داده‌ها باشند و به شما اجازه می‌دهد به جای تایپ کردن آن‌ها، لیست کشویی اکسل را ظاهر کرده و از میان اقلام آن دست به انتخاب بزنید.

فیلم آموزش اکسل Microsoft Excel 2019 – مقدماتی در فرادرس

کلیک کنید

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

• کلیک راست و اجرای دستور Pick From Drop-down List.
• کلیدهای ترکیبی کلید Alt و فلش پایین یا ↓ + Alt، به معنی نگه داشتن کلید Alt و فشردن کلید مکان‌نما به سمت پایین.
• وارد کردن چند حرف اولیه و توجه به پیشنهاد اکسل برای تکمیل خودکار.

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

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

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

File  > Option > Advanced >  Enable Autocomplete for cell values

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

لیست کشویی با اعتبار سنجی داده (Data Validation)

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

فیلم آموزش اکسل – ابزارهای کاربردی Excel در فرادرس

کلیک کنید

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

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

حال می‌خواهیم که از این لیست در Data Validation استفاده کرده تا یک لیست کشویی در اکسل ایجاد کنیم. به منظور این کار، مراحل زیر را طی خواهیم کرد.

1. کل ناحیه و سلول‌های مربوط به استان محل تولد افراد را انتخاب می‌کنیم.
2. از فهرست Data و بخش Data Tools، گزینه Data Validation را فعال کرده و تنظیم‌های مورد نظر را مطابق با تصویر زیر اجرا می‌کنیم.
3. با فشردن دکمه OK کار را پایان می‌دهیم.

توجه داشته باشید که گزینه‌های موجود در این کادر گفتگو، فقط در صورتی که در بخش Allow، گزینه List انتخاب شده‌ باشند، ظاهر می‌شود. از طرفی حتما In-cell dropdown را برای ظاهر شدن لیست پیشنهادی، فعال کنید.

در بخش Source نیز همانطور که می‌بینید، ناحیه‌ مربوط به اسامی استان‌ها انتخاب شده که مثلا شامل سلول‌های F2 تا F23 است. این فضا به شما این امکان را می‌دهد که در صورت اضافه کردن نام استان‌های دیگر، لیست کامل‌تری داشته باشید. این ناحیه را به کمک استفاده از نام‌گذاری سلول‌ها نیز می‌توانید تعیین کنید. برای مثال اگر ناحیه لیست اطلاعاتی مربوط به ستون F، با نام Datalist مشخص شده باشد، کافی است که پس از علامت مساوی در قسمت Source این نام را وارد کنید.

نکته: اگر با تنظیم‌های مربوط به ابزار Data Validation آشنایی ندارید بهتر است مطلب Data Validation در اکسل — کاربرد و آموزش استفاده | به زبان ساده را قبلا مطالعه کرده باشید.

به این ترتیب به محض اینکه یکی از سلول‌های مربوط به استان محل تولد را انتخاب کنید، یک لیست کشویی ظاهر شده که برای شما انتخاب گزینه مورد نظر را امکان‌پذیر می‌سازد.

راهنما برای نحوه ورود مقادیر در این ستون به کمک برگه Input Message‌ صورت می‌گیرد. به این ترتیب هنگام ظاهر شدن لیست کشویی در اکسل یک راهنمای زرد رنگ در کنار سلول ظاهر می‌شود که پیغام شما را به کاربر نشان داده و او را به نحوه صحیح پر کردن این سلول، آگاه می‌سازد.

می‌توانید کنترل بیشتری نیز برای این لیست ایجاد کنید. به صورت پیش‌فرض، هیچ مقداری خارج از لیست مورد نظر، اجازه ثبت در ستون استان محل تولد نخواهد داشت. ولی ممکن است بخواهید انعطاف بیشتری به خرج دهید و به کاربر اجازه دهید که پس از مشاهده یک پیغام اخطار، مقدار مورد نظر خودش را ثبت کند.

برای انجام این گونه تنظیم‌ها، باید وارد برگه Error Alert شده و بسته به نیازتان، گزینه مناسب را انتخاب کنید. تصویر زیر این برگه از پنجره Data Validation را نشان داده است.

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

فیلم مجموعه آموزش اکسل – مقدماتی تا پیشرفته در فرادرس

کلیک کنید

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

برای مثال اگر لازم است نام استان حتما از لیست مورد نظر بدون هیچ خطای املایی ثبت شود، باید از گزینه Stop استفاده کنید. ولی اگر می‌خواهید در صورت ورود مقداری مغایر، کاربر اجازه ثبت نام استان را داشته باشد (مثلا طبق شناسنامه ممکن است نام استان مغایر با اسامی لیست شما باشد) از گزینه Warning کمک بگیرید. البته برای آنکه کاربرد آزاد باشد و بتواند هر گونه نامی را وارد کرده ولی با اندکی هشدار مواجه شود، گزینه Information، انتخاب مناسبی است. فضای کاری با این تنظیم‌ها، شبیه تصویر زیر خواهد شد.

اگر می‌خواهید همه تنظیمات به حالت اولیه برگردد و محدودیت ورود مقادیر طبق لیست برداشته شود، دکمه Clear All را در تصویر ۵، کلیک کرده یا در برگه Settings، از فهرست Allow گزینه Any را انتخاب کنید.

نکته: مشخص است که به منظور جلوگیری از تغییر اسامی پیش‌فرض استان‌ها، ستون F را مخفی کرده‌ایم. البته هر گاه لازم باشد می‌توان آن ستون را ظاهر کرده و مقادیری از آن کم یا به آن اضافه کنیم تا لیست کشویی در اکسل به روز رسانی شود.

خلاصه و جمع‌بندی

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