۳ روش ایجاد لیست کشویی در اکسل (گام به گام) +فیلم

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

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

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

1. Data validation
2. Form control
3. ActiveX

خب از کجا بدانیم از کدامیک از آنها باید استفاده کنیم؟ مثل خیلی چیزهای دیگر، پاسخ این سوال یک کلمه است: «بستگی داره» ! بیایید نگاهی به این ۳ روش بیاندازیم و ببینیم هر کدام از آنها کجا باید استفاده بشوند.

۱-لیست کشویی Data validation

رایج ترین روش استفاده از لیست های کشویی روش Data validation است. بیایید یک نمونه از این لیست ها را درست کنیم.

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

کلیک کنید Data > Data Validation > Data Validation…

با این کار پنجره data validation برایتان باز خواهد شد. در نوار settings، گزینه list را از کادر allow انتخاب و محدوده سلول های حاوی لیست را در کادر source وارد می کنیم.

با کلیک بر روی OK کادر data validation را ببندید.

برای استفاده از لیست کشویی، بر روی سلول حاوی لیست data validation کلیک کنید. حالا می توانیم فقط یک مقدار (گزینه) از لیست ساخته شده را انتخاب کنیم.

اگر یک ورودی به اشتباه در سلول تایپ شود با پیغام خطا مواجه خواهیم شد.

۹ روش رند کردن (گردکردن) در اکسل (ساده ترین متدها)

سایر ویژگی های لیست data validation

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

لیست های کشویی می توانند مقادیر سخت کدگذاری شده (hardcoded) باشند

نیازی نیست که لیست در یک کاربرگ باشد، مقادیر نیز می توانند در منبع کدگذاری سخت شوند. عکس زیر نشان می دهد که چگونه بدون کمک گرفتن از سلول ها ،گزینه های Yes، No و Maybe را داشته باشیم.

یک لیست کشویی که با این روش ساخته شود به شکل زیر خواهد بود:

لیست کشویی می تواند به شکل سطری یا ستونی باشد

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

محدوده های نامگذاری شده

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

جداول

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

data validation و آرایه های پویا

اگر آرایه ای پویا (دینامیک) مطابق با نسخه اکسل خود دارید (فقط مشترکان مایکروسافت ۳۶۵ در زمان نگارش این مطلب)، لیست های data validation می توانند حاوی نتایج یک آرایه ی پویا باشند.

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

نکات مفید

قبل از ادامه بحث، می خواهم شما را با یک سری نکات در مورد لیست های data validation آشنا کنم:

شناسایی سلول های حاوی data validation

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

خطرات کپی کردن و چسباندن

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

و حالا… برویم سراغ خطرات کپی کردن و چسباندن.

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

آموزش رسم فلوچارت در اکسل (و همه نکات کاربردی)

یک مشکل دیگر…

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

هایلات کردن تمام سلول ها با خصوصیات invalid

نرم افزار اکسل می تواند مقادیر هر سلول را بدون رعایت ضوابط data validation بررسی کند. کلیک کنید

Data > Data Validation (drop-down) > Circle Invalid Data

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

برای حذف دایره کشیده شده، کلیک کنید

Data > Data Validation (drop-down) > Clear Circle Invalid Data

۲-لیست کشویی Form control

استفاده از Form control ها روش دیگری برای ایجاد لیست کشویی است. با توجه به اینکه این روش، برخی از مشکلات لیست های کشویی data validation را ندارد، چالشگران منحصر به فردی در این زمینه به شمار می روند. به کمک VBA می توان اجزای form control را کنترل کرد، هر چند در این مطلب، قرار بر ساده گویی است و به همین خاطر به جای توابع VBA در مورد زبان basic صحبت خواهیم کرد (که البته هنوز زبان قدرتمندی است.)

برویم سراغ نحوه ی ایجاد لیست کشویی form control. این ابزار را می توانید در نوار Developer پیدا کنید.

نمایش نوار ریبون Developer

در قسمت منو… با کلیک راست بر روی یک بخش خالی از نوار ریبون گزینه ی Customize the Ribbon را انتخاب کنید

از پنجره Excel Options بر روی Customize Ribbon کلیک کرده و قابلیت Developer را فعال و OK کنید.

کلیک کنید:Developer>Insert>ComboBox(ForControl)

حالا نشانگر مُوس به شکل + تغییر پیدا خواهد کرد. دکمه سمت چپ مُوس را فشار داده و تا زمان جا نمایی کامل لیست کشویی در صفحه آن را نگه دارید. با فشردن کلید ALT در زمان جانمایی میتوان تنظیمات لیست کشویی را تغییر داد.

بر روی لیست کشویی کلیک راست و گزینه format control را انتخاب کنید…

معرفی منو/تب های اکسل در نوار ریبون

پس از باز شدن پنجره فرمت بر روی نوار control کلیک کنید. با فیلدهای زیر مواجه خواهید شد:

• Input range : محدوده ی سلول های حاوی لیست کشویی
• Cell link : سلول خروجی مقدار نهایی

سلول مرجع موقعیت قرار گرفتن گزینه ی انتخاب شده در کادر لیست کشویی را نشان می دهد. مثلاً با توجه به اینکه کلمه East در لیست بالا سومین کلمه است، سلول F2 عدد ۳ را نمایش می دهد.

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

فرمول در سلول G2 به شکل زیر نوشته می شود:

=INDEX(A2:A6,F2)

نکات مفید

امکان کپی کردن و چسباندن لیست های کشویی form control وجود دارد، اما برای این کار باید هر بار به قسمت settings رفته و سلول لینک شده ی جدید را انتخاب کنیم. انجام این کار در مواردی که نیاز به تعداد زیادی لیست کشویی باشد، از نظر زمانی مقرون به صرفه نیست.

۳-لیست کشویی ActiveX

حالا سراغ آخرین نوع لیست های کشویی می رویم؛ ActiveX.

کلیک کنید:

Developer>Insert>ComboBox(ActiveXControl)

همانند مدل form control که قبلاً بررسی کردیم، ابتدا یک لیست کشویی در کاربرگ رسم می کنیم. بر روی لیست کشویی کلیک راست و گزینه properties را از منو انتخاب می کنیم.

پنجره properties باز می شود. پارامترهای خیلی مهم برای هدف ما LinkedCell و ListFillRange هستند. این دو پارامتر شبیه لیست کشویی Form control می باشند؛ محدود های هم ارز را وارد این فیلدها کنید. بر روی [x] در گوشه بالا سمت راست کلیک کنید تا پنجره ی properties بسته شود.

کلیک کردن بر Developer > Design Mode به ما این اجازه را می دهد که بتوانیم بین استفاده و ویرایش لیست کشویی تغییر وضعیت دهیم. در وضعیت design mode، لیست کشویی می تواند ویرایش شود ولی قابل استفاده نیست. به همین شکل، در وضعیت display mode، لیست کشویی قابل استفاده بوده ولی ویرایش نمی شود.

برخلاف روش form control، لیست کشویی ActiveX مقادیر مدنظر را مستقیماً در سلول لینک شده نمایش می دهد.

روش ActiveX نسبت به form control امکانات بیشتری برای فرمت کردن دارد.

نکات مفید

لیست های کشویی ActiveX ویژگی های زیادی دارند از جمله:

• در وضعیت design mode، لیست کشویی ActiveX را می توان کپی یا پاک کرد، در حالی که در وضعیت display mode این امکان فراهم نیست.
• روش ActiveX بر روی سیستم عامل Mac کار نمی کند. بنابراین، اگر می خواهید کاربرگ چند پلتفرمی واقعی داشته باشید، این روش انتخاب خوبی برای شما نیست.

کدام روش را انتخاب کنیم؟

خب، حالا باید کدام روش را انتخاب کنید؟ همان طور که در ابتدای این مطلب گفتم، بستگی دارد. توصیه کلی من به شما این است:

1. تا حد امکان سمت ActiveX نروید.
2. از Form Control ها برای انتخاب های تک سلولی استفاده کنید.
3. وقتی از Data Validation استفاده کنید که لیست کشویی نیاز به کپی شدن داشته باشد.

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

ویدیوی آموزشی (اگر آموزش بالا را دوست نداشتید)

یادتان باشد که

اگر این مطلب برای شما مفید بود یا اگر روش بهتری سراغ دارید، حتماً برای ما پیام بگذارید. موفق باشید.

دانلود فیلم های آموزش صفر تا صد اکسل +جزوه