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

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

1.     تابع SUBTOTAL در اکسل

2.     انتقال جدول ورد به اکسل

 

آنچه در این مقاله می خوانیم:

✅لیست، رکورد و فیلد در بانک‌های اطلاعاتی

✅روش اول ایجاد لیست کشویی در اکسل (Pick From Drop-down list)

✅روش دوم ایجاد لیست کشویی در اکسل (Data Validation)

✅روش سوم ایجاد لیست کشویی در اکسل (Form control)

✅روش چهارم ایجاد لیست کشویی در اکسل (ActiveX)

✅جمع بندی

 

لیست، رکورد و فیلد در بانک‌های اطلاعاتی

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

لیست: لیست‌ به مجموعه‌ای از داده‌های مستقل و مرتبط به هم می گن که در کنار هم قرار گیری اون ها به شکل منظم و هدفمند صورت گرفته.

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

 

 

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

فیلد: هر رکورد از چند چند فیلد تشکیل شده و می تونیم فیلد رو جزئی از یه رکورد معرفی کنیم. هر کدوم از فیلدا حاوی یه داده ویژه از یه رکورد هستن. در تصویر بالا هر کدوم از رکوردا، ۷ تا فیلد دارن و در ردیف اول با نوشتن عنوان فیلد، موضوع اون فیلد مشخص شده.

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

 

روش اول ایجاد لیست کشویی در اکسل (Pick From Drop-down list)

وقتی که شما بخواین دوباره از اطلاعاتی که قبلاً به لیست وارد کردین استفاده کنین، با استفاده از Pick From Drop-down list می‌تونین اطلاعات قبلی  همون فیلد رو به شکل لیست دربیارین و دوباره انتخاب کنین.

واسه انجام این کار باید اولین سلول خالی بعد از اطلاعات لیستتون در ستون مد نظر رو انتخاب کنین و با کلیک راست روی همون سلول، روی گزینه Pick From Drop-down list… کلیک کنین.

 

 

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

 

روش دوم ایجاد لیست کشویی در اکسل (Data Validation)

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

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

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

تو این مثال محدوده انتخابی ما D4:D13 هستش.

Data tab > Data Tools Group > Data Validation

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

 

 

１-   به تب Settings برین.

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

３- در بخش Source هم باید اون داده‌هایی که می خواین به‌ شکل لیست دربیان رو وارد کنین. دو راه واسه انجام این کار وجود داره:

 

１-   ثبت دستی داده‌ها:

واسه ثبت دستی داده ها شما باید هر آیتم رو با استفاده از جداکننده آرگومان ( این جدا کننده طبق تنظیمات ویندوز و اکسل شما می‌تونه متفاوت باشد و معمولا این جداکننده ویرگول « ,» یا نقطه ‌ویرگول « ; » هستش) ثبت کنین.

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

مثل تصویر زیر:

 

２- آدرس‌دهی به سلول‌ها:

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

واسه آدرس دادن هم دو روش مختلف آدرس ‌دهی ثابت (غیر پویا) و آدرس‌ دهی پویا (داینامیک) وجود داره:

آدرس ‌دهی ثابت

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

 

 توجه: واسه آسون تر کردن کار می‌تونین اون محدوده رو نام ‌گذاری کنین و در قسمت Source اسم اون محدوده رو وارد کنین.

 

 

آدرس ‌دهی پویا

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

فرض کنین لیست مد نظر ما تو ستون C قرار گرفته؛

در روش اول فقط باید فرمول زیر رو در قسمت Source وارد کنین:

=OFFSET($C$1,1,0,COUNTA($C:$C)-1)

 

نکته: شما همچنین می تونین فرمول بالا رو در یک name تعریف کنین و تو بخش  Source از اون اسم تعریف شده استفاده کنین.

 

 

 

 در روش دوم ولی باید اول لیست را به تیبل تبدیل کنین و بعد در قسمت Source فرمول پایین رو وارد کنین:

=INDIRECT("Table1[نام و نام خانوادگي]")

 

روش سوم ایجاد لیست کشویی در اکسل (Form control)

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

واسه ایجاد لیست کشویی توسط   form controlباید در نوار Developer این ابزار رو جستجو کنین.

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

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

 

 

از پنجره ای بهتون نشون داده میشه روی Customize Ribbon کلیک کنید و اقدام به فعال کردن ویژگی Developer تو این پنجره کرده و OK رو انتخاب کنین.

 

 

به مسیر زیر برین:

Developer>Insert>ComboBox(ForControl)

 

وقتی نشانگر موس شبیه + شد، دکمه سمت چپ موس رو فشار بدین و تا وقتی که لیست کشویی به طور کامل در صفحه جانمایی بشه اونو نگه دارین. اگه موقع جانمایی کلید ALT رو فشار بدین می تونین تنظیمات لیست کشویی رو تغییر بدین.

 

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

 

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

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

 

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

 

 

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

 

 

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

=INDEX(A2:A6,F2)

 نکته: امکان COPY و PASTE کردن لیست های کشویی form control وجود داره، ولی واسه انجام این کار باید هر سری برین به بخش settings و سلول لینک شده ی جدیدو انتخاب کنین. در نتیجه انجام این کار در مواقعی که شما نیاز به تعداد زیادی لیست کشویی دارین، وقت زیادی می بره.

 

روش چهارم ایجاد لیست کشویی در اکسل (ActiveX)

یکی دیگه از روشایی که واسه ایجاد لیست کشویی در اکسل می تونین ازش استفاده کنین، ActiveX هستش. واسه انجام این کار باید به آدرس زیر برین:

Developer>Insert>ComboBox(ActiveXControl)

 

 

در گام اول یه لیست کشویی در کاربرگ رسم کنین.  روی لیست کشویی راست کلیک کرده و روی گزینه properties کلیک کنین.

 

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

 

 

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

 

 

درست برعکس روش form control، لیست کشویی که توسط ActiveX ایجاد می شه، اقدام به نمایش مستقیم مقادیر مورد نظر در سلول لینک شده می کنه.

 

 

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

 

 

جمع بندی

در این مقاله 4 روشی که واسه ایجاد لیست کشویی در اکسل استفاده می شن رو بهتون یاد دادیم. توصیه من به شما اینه که تا جایی که ممکنه واسه انجام این کار سمت ActiveX نرین. از Form Control ها هم بهتره واسه انتخاب های تک سلولی استفاده کنین. اگه هم لیست کشویی نیاز به کپی شدن داشت بهتره از Data Validation استفاده کنین. شما از کدوم روش واسه انجام این کار استفاده می کنین؟ اگه فکر می کنین راه شما آسون تر و کاربردی تره، ممنون می شم تو بخش کامن اونو واسمون به اشتراک بزارین تا بقیه کاربرا هم بتونن ازش اسه ایجاد لیست کشویی در اکسل استفاده کنن.

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

📌 رفع مسئولیت: محتوای این صفحه صرفاً برای اطلاع رسانی است و در صورتی که تسلط کافی برای انجام موارد فنی مقاله ندارید حتما از کارشناس فنی کمک بگیرید.