سبد خرید
0

سبد خرید شما خالی است.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

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

۵/۵ - (۲ امتیاز)

لیست های کشویی پیشرفته

بحث ورود داده در اکسل از طریق لیست کشویی یکی از موارد قدیمی و بسیار پرکاربرد در اکسل بوده و هست. برای ایجاد یک لیست کشویی ساده در اکسل حتما مقاله مربوط به این موضوع رو مطالعه کنید. اما ۲ تا موضوع همیشه مطرح بوده، یکی اینکه بتونیم داخل این لیست، جستجو انجام بدیم (برای مواردی که تعداد آیتم ها زیاد هستن و حرکت بین اونها و پیدا کردن داده مورد نظر سخت باشه). این مسئله در اکسل ۳۶۵ حل شده و لیست های ایجاد شده قابل جستجو هستن. اما اگر مثل من از نسخه غیررایگان اکسل ۳۶۵ استفاده نمیکنید😊، این مقاله رو بخونید ببینید چطور باید این کار و بکنید. ویژگی این روش اینه که در هر ورژنی (حتی ۲۰۰۳!) قابل استفاده است. موضوع دومی که مطرح میشه اینه که چطور میشه بیش از یک آیتم در این لیست ها انتخاب کرد. این موضوع هنوز راه حل مستقیمی نداره و ما میخوایم برای حل این موضوع از کد VBA استفاده کنیم. پس تا الان ۲تا از ویژگی های خیلی مهم لیست های کشویی رو تونستیم اضافه کنیم:

در این مقاله میخواهیم راجع به دومین موضوع صحبت کنیم. یعنی انتخاب بیش از یک مورد در هر سلول.

multiple-item-data-validation

مثلا فرض کنید در یک آرشیو دیجیتال فیلم که طبیعتا یکی از فیلدهای اون ژانر هست،  ژانرهای مختلف یک فیلم مثلا، درام، کمدی ،تخیلی و … رو در یک سلول مشخص کنیم.برای این کار اول لیست مورد نظر رو ایجاد میکنیم. برای این کار کافیه ژانرهای مختلف رو در یک ستون وارد کنیم و بعد از مسیر Data/ Data Validation/ List داده های مورد نظر رو انتخاب کنیم و لیست رو بسازیم. (شکل ۱). برای ایجاد لیست داینامیک حتما مقاله مربوط به ایجاد لیست کشویی داینامیک مطالعه کنید.Data-Validationشکل ۱- انتخاب داده های مورد نظر و ایجاد لیست کشویی

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

 ۱- ماکروی ورود دیتای چندتایی با تکرار (امکان ورود یک آیتم بصورت تکراری وجود دارد)

  • از این لحظه به بعد هر ولیدیشینی در هر سلولی از این شیت وارد بشه، قابلیت انتخاب چندتایی خواهد داشت. (نیازی به تعیین محدوده خاصی نیست)
  • این کد برای هر شیت قابل اجراست. پس اگر در چند شیت قراره لیست چندتایی داشته باشید، در همه شیت های مورد نظر، کپی کنید.
  • در این ماکرو، تکرار مجاز است. یعنی یک آیتم میتونه چندین بار انتخاب بشه.
  • آیتم های انتخابی با , و یک فاصله از هم جدا میشن و در سلول نمایش داده میشن. میتونید این جدا کننده رو در کد تغییر بدید برای این کار کافیه DelimiterType = “, ” یعنی خط هفتم کد قسمت “,” رو به جداکننده دلخواه مثلا “|” تغییر بدید.

۲- ماکروی ورود دیتای چندتایی بدون تکرار (در صورت انتخاب آیتم تکراری، حذف میشه)

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

۳- ماکروی ورود دیتای چندتایی بدون تکرار و با امکان حذف

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

تغییر جداکننده آیتم های انتخابی لیست

جدا کننده آیتم های چندگانه در سلول، در هر ۳ ماکرو، مقدار , است که میتونیم تغییرش بدیم. این جدا کننده با عنوان DelimiterType = “,” در هر ۳ ماکرو نمایش داده شده که در خط ۷ ماکروهاست و میتونیم تغییرش بدیم، مثلا میتونیم ; یا | یا – بذاریم و داده ها با این جدا کننده ها از هم تفکیک بشن.

برای اینکه آیتم ها هر کدوم در یک سطر از سلول ثبت بشن (با Alt Enter از هم تفکیک بشن) باید تغییر دیگه ای در کد ایجاد کنیم اون هم اینکه بجای عبارت

DelimiterType = “,”

 بنویسیم:

DelimiterType = vbCrLf

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

multiple-items-with-alt-enter-delimiter

شکل ۲- انتخاب آیتم چندگانه با جداکننده Alt Enter

نحوه اجرای ماکرو در شیت محافظت شده (Protected sheet)

برای اینکه بتونیم در یک شیت پروتکت شده، ماکرویی رو اجرا کنیم باید ۲ تا کار انجام بدیم. یکی اینکه اول ماکرو، شیت رو از قفل در بیاریم و بعد که ماکرو اجرا شد، شیت رو مجددا قفل کنیم. برای این کار طبق دستور العمل زیر پیش میریم:

باز کردن قفل قبل از اجرای ماکرو

باید قبل از این خط کد:

Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)

کد زیر رو اضافه کنیم:

ActiveSheet.Unprotect Password:=”password”

در اینجا “password” همون پسورد شیت مورد نظر است که قفل شده.

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

باز کردن قفل قبل از اجرای ماکرو

برای قفل کردن مجدد شیت قبل از کد زیر:

exitError:

این کد رو اضافه میکنیم:

ActiveSheet.Protect Password:=”password”

توجه کنید که password” همون پسورد شیت مورد نظر است که باید با اون قفل بشه.

خب در این مقاله دیدیم که چطور میتونیم امکان انتخاب چندگانه در لیست آبشاری رو فراهم کنیم. این ماکرو رو به فایلتون اضافه کنید و از این امکان فوق العاده استفاده کنید. این کار باعث افزایش کیفیت و دقت ورود داده در اکسل می شود. فراموش نکنید که فایل اکسل بعد از ورود ماکرو بادی بصورت macro enable ذخیهر بشه و الا همه کدها حذف میشه.

برای دیدن ماکروهای کاربردی بیشتر حتما مقاله مربوط به ماکروها آماده در اکسل رو مطالعه کنید.

ویدئو ساخت لیست کشویی پیشرفته

در حال بارگذاری پخش کننده...

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

دانلود فایل اکسل لیست پیشرفته

فایل زیر رو دانلود کنید تا نحوه عملکرد هر ۳ ماکرو رو در ۳ شیت ببینید.

توجه: جهت دانلود فایل حتما در حساب کاربری خود وارد شوید.
کلیدواژه : vbaماکرونویسی
آواتار
144

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

دیدگاه کاربران
  • آناهیتا ۵ بهمن ۱۴۰۲ / ۱۲:۲۵ ب٫ظ

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

    • آواتار
      حسنا خاکزاد ۶ بهمن ۱۴۰۲ / ۱۱:۵۲ ق٫ظ

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

ارسال دیدگاه

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

توسط
تومان