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

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

اساطیر زبان

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

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

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

  1. Data validation
  2. Form control
  3. ActiveX

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

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

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

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

word image 83

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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

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

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

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

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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

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

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

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

جداول

من عاشق جدول هام، اما آنها آن طور که باید و شاید با لیست های 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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

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 را انتخاب کنید

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

word image 2

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

[webp-to-jpg output image]

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

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

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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

=INDEX(A2:A6,F2)

نکات مفید

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

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

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

کلیک کنید:

Developer>Insert>ComboBox(ActiveXControl)

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

[webp-to-jpg output image]

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

نکات مفید

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

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

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

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

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

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

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

 

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

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

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

تفاوت پک های حرفه ای ما با آموزشهای رایگان: بطور خلاصه از زمین تا آسمان! 1-پکها، جدیدترین نسخه نرم افزارها را آموزش می دهند با قابلیت های بسیار بیشتر. 2-پکها توسط متخصص آن نرم افزار، به صورت کاملا پروژه محور و با حل چالش هایی که در مسیر کار عملی و حرفه ای با آن روبرو می شوید تهیه شده اند و بعد از استفاده، کاملا برای بازار کار آماده اید! 3- متد این پکها کاملا کار شده و تا ماهها، در ذهن تان ماندگارند و یادگیری بسیار سریعتر و کاملتری خواهید داشت. آموزشهای رایگان فقط دستورات نرم افزارها را (آنهم ناقص) بیان می کنند و تازه برای ورود به بازار باید ماهها تجربه عملی هم کسب کنید !!

turned_in

چرب زبان

با این اپلیکیشن ساده، هر زبانی رو فقط با 5 دقیقه در روز، توی 80 روز مثل بلبل حرف بزن! بهترین متد روز، تقویت حافظه، آموزش تصویری. یادگیری زبان کلید یادگیری هر مهارتی در قرن 21 !


حتما بخوانید!

17 دیدگاه. ارسال دیدگاه جدید

دیدگاهتان را بنویسید

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

این فیلد را پر کنید
این فیلد را پر کنید
لطفاً یک نشانی ایمیل معتبر بنویسید.

فهرست