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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

File  > Option > Advanced >  Enable Autocomplete for cell values

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

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

• برای مشاهده فیلم آموزش برنامه نویسی VBA در اکسل – تکمیلی + اینجا کلیک کنید.

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

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

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

به این منظور ستون 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‌ صورت می‌گیرد. به این ترتیب هنگام ظاهر شدن لیست کشویی در اکسل یک راهنمای زرد رنگ در کنار سلول ظاهر می‌شود که پیغام شما را به کاربر نشان داده و او را به نحوه صحیح پر کردن این سلول، آگاه می‌سازد.

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