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

آخرین به‌روزرسانی: ۱۰ دی ۱۴۰۱
زمان مطالعه: ۲۷ دقیقه
لیست کشویی در اکسل

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

فیلم آموزشی ساخت لیست کشویی در اکسل

دانلود ویدیو

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

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

«لیست کشویی» (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 نیز در اینجا می‌تواند یک لیست را براساس مقادیر قبلی تکمیل و نمایش دهد.

AUTOCOMPLETE
تصویر ۲: تنظیم، تکمیل خودکار محتویات سلول

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

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

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

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

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

لیست کشویی در اکسل با Data validation
تصویر ۳: لیست از پیش تعریف شده اسامی استان‌ها

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

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

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

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

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

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

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

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

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

error alert
تصویر ۵ : نمایش هشدار یا خطای ورودی Data Validation در اکسل

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

گزینه عملکرد توضیحات
Stop توقف از ورود مقدار مغایر جلوگیری می‌شود و تا زمانی که اصلاح صورت نگرفته باشد، عمل دیگری به جز انصراف مجاز نیست.
Warning اخطار اخطار مغایرت ظاهر شده و در صورت تایید کاربر، مقدار ثبت می‌شود. در این حالت دکمه‌های yes یا No برای تایید ثبت یا عدم آن به کار رفته و Cancel و Help نیز ظاهر می‌شوند.
Information اطلاع مغایرت اطلاع داده شده و فقط دکمه‌های Ok و Cancel به همراه Help ظاهر می‌شود.

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

data validation drop down list
تصویر ۶: نتیجه نهایی برای لیست کشویی در اکسل

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

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

معرفی فیلم آموزش برنامه نویسی VBA در اکسل – تکمیلی

آموزش برنامه نویسی VBA در اکسل

نرم‌افزار اکسل (Microsoft Excel) با قابلیت برنامه نویسی VBA، بسیاری از محدودیت‌های خود را کمرنگ کرده است. در حقیقت مایکروسافت با به میان آوردن VBA، توانسته بسیاری از کمبودهایی که امکانات برنامه‌های کاربردی آفیس در اختیار کاربران قرار نمی‌داد را جبران کرده و به صورت کدهای VBA، ارائه کند. حتما می‌دانید که VB یا ویژوال بیسیک، یکی از اولین زبان‌هایی بود که به صورت محاوره و بصری با فرم‌ها و محیط API ویندوز، در ارتباط بود. در همین راستا VBA یا (Visual Basic for Applications) در بسته نرم‌افزاری Office از جمله نقش یک زبان برنامه‌نویسی برای کار در محیط آفیس را فراهم نمود. VBA به کاربران این امکان را می‌دهد تا با استفاده از زبان برنامه‌نویسی Visual Basic نسبت به پیاده‌سازی قابلیت‌های مورد نیاز و دلخواه خود در گروه برنامه‌های آفیس، اقدام کنند. در این آموزش که قسمت تکمیلی آموزش برنامه‌نویسی VBA در اکسل است، به بررسی مباحث پیشرفته VBA در اکسل پرداخته می‌شود. در این فرادرس با رایج‌ترین و پراستفاده‌ترین توابع موجود در VBA آشنا شده و نحوه به کارگیری آن‌ها را فرا می‌گیرید. سرفصل‌ها و رئوس مطالب این آموزش در قالب نه درس بوده که به مهم‌ترین آنها اشاره خواهیم کرد.

درس یکم: آشنایی با توابع کاربردی VBA، توابع کار با رشته‌ها، توابع کار با داده‌ها و تغییر داده‌ها و توابع ریاضی. درس دوم: آشنایی با کادرهای پیغام، نمایش کادر پیغام، مدیریت رفتار کاربر و گزینه انتخابی. درس سوم: کار با آرایه‌ها، آشنایی با آرایه و کاربرد آن، کار با آرایه‌های تک‌بعدی، کار با «آرایه‌های پویا» (Dynamic arrays)، کار با آرایه‌های دو‌بعدی و نمایش مقادیر آرایه در سلول‌ها. درس چهارم: کار با فایل‌ها و پوشه‌ها، باز کردن یک فایل و خواندن مقدار آن، ذخیره داده مورد نظر در فایل، کار با فایل‌های متنی. درس پنجم: اتصال به سایر نرم‌افزارها، ذخیره خروجی PDF از محتوای Sheet، ذخیره خروجی Word از محتوای Sheet. درس ششم: مدیریت رویدادهای مربوط به Workbook و یا Worksheet در ماژول‌های VBA. درس هفتم: کار با ActiveX Controls در فرم‌های VBA. درس هشتم: آشنایی و کار با Function و آشنایی با مفهوم Function و دلیل استفاده از Function. درس نهم: کار با نمودارها در VBA و ایجاد نمودار با استفاده از VBA.

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

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

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

بر اساس رای ۴ نفر
آیا این مطلب برای شما مفید بود؟
شما قبلا رای داده‌اید!
اگر بازخوردی درباره این مطلب دارید یا پرسشی دارید که بدون پاسخ مانده است، آن را از طریق بخش نظرات مطرح کنید.
منابع:
مجله فرادرس
۲ thoughts on “ساخت لیست کشویی در اکسل — آموزش تصویری و گام به گام + فیلم آموزشی

بسیار کاربردی بود . ممنون از شما

با عرض سلام و عرض ادب
یه سوال داشتم از خدمتتون

a=b*c

چطوری میشه با داشتن a و b به c رسید

ولی بدون تغییر فرمول

b=a/c

نظر شما چیست؟

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *