سلول های پاپ آپ در اکسل. Excel: از یک لیست انتخاب کنید

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

و بنابراین چندین گزینه برای نحوه ایجاد لیست های کشویی برای کار در Microsoft Office Excel وجود دارد.

گزینه یک بسیار ساده است. اگر داده های مشابهی را در همان ستون از بالا به پایین وارد کنید، فقط باید روی سلول زیر داده بایستید و کلید ترکیبی "Alt + فلش پایین" را فشار دهید. یک لیست کشویی در مقابل شما ظاهر می شود که می توانید داده های مورد نیاز خود را با یک کلیک از بین آن انتخاب کنید.

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

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


می توانید این محدوده را از طریق تب منوی "Formulas" با انتخاب نماد "Name Manager" ویرایش کنید. در آن می توانید یک لیست کشویی جدید ایجاد کنید، یک لیست موجود را ویرایش کنید، یا به سادگی یک لیست غیر ضروری را حذف کنید.

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


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

داده ها را در سلول های A1:A10 وارد کنید، که به عنوان منبع لیست عمل می کند. در مثال ما، اعداد را وارد کردیم، آنها در لیست کشویی ظاهر می شوند. سلولی را انتخاب کنید (به عنوان مثال، E5) که حاوی لیست کشویی است. منوی Data -> Data Validation را انتخاب کنید تا کادر گفتگوی Validate Input Values ​​باز شود.

3. در تب Options از منوی کشویی گزینه List را انتخاب کنید. مطمئن شوید که کادرهای صحیح علامت زده شده اند.

4. سپس بر روی دکمه کلیک کنید. کادر محاوره ای زیر ظاهر می شود.

5. مواردی را که در لیست کشویی روی برگه ظاهر می شوند با استفاده از ماوس انتخاب کنید، روی دکمه کلیک کنید و به پنجره "Validate input values" برگردید، سپس روی دکمه "OK" کلیک کنید.

6. یک لیست کشویی در اکسل ایجاد می شود.

اگر لیست شما کوتاه است، می توانید موارد را مستقیماً در Source در تب Setup در کادر محاوره ای Validate Input وارد کنید. هر مورد لیست را با جداکننده های مشخص شده در تنظیمات منطقه ای جدا کنید.
اگر لازم است لیست در برگه دیگری باشد، می توانید قبل از تعیین محدوده داده از گزینه "=List" استفاده کنید.
نحوه ایجاد یک لیست کشویی در اکسل بر اساس داده های لیست

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

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

به برگه "داده ها" => بخش "کار با داده" در نوار ابزار بروید => "اعتبارسنجی داده ها" را انتخاب کنید.

در قسمت "منبع"، محدوده نام میوه ها را وارد کنید =$A$2:$A$6 یا به سادگی نشانگر ماوس را در قسمت ورودی مقدار "Source" قرار دهید و سپس محدوده داده را با ماوس انتخاب کنید:

اگر می‌خواهید لیست‌های کشویی را همزمان در چندین سلول ایجاد کنید، تمام سلول‌هایی را که می‌خواهید در آنها ایجاد کنید انتخاب کنید و مراحل بالا را دنبال کنید. مهم است که اطمینان حاصل شود که ارجاعات سلولی مطلق هستند (مثلاً $A$2) و نه نسبی (مثلا A2 یا A$2 یا $A2).

نحوه ایجاد یک لیست کشویی در اکسل با استفاده از ورود دستی داده ها

در مثال بالا، با انتخاب محدوده ای از سلول ها، فهرستی از داده ها را برای یک لیست کشویی وارد کردیم. علاوه بر این روش، می توانید داده ها را برای ایجاد یک لیست کشویی به صورت دستی وارد کنید (لازم نیست آن را در هیچ سلولی ذخیره کنید).
به عنوان مثال، تصور کنید که می خواهیم دو کلمه "بله" و "خیر" را در یک منوی کشویی نمایش دهیم.

برای این ما نیاز داریم:
سلولی را که می خواهیم در آن لیست کشویی ایجاد کنیم را انتخاب کنیم.
به تب "داده ها" => "کار با داده ها" در نوار ابزار => بروید
اعتبارسنجی داده ها در اکسل

در پنجره پاپ آپ «بررسی مقادیر ورودی»، در تب «پارامترها»، «فهرست» را در نوع داده انتخاب کنید:
اعتبارسنجی مقادیر ورودی در اکسل

در قسمت "منبع" مقدار "بله" را وارد کنید. نه".
روی "OK" کلیک کنید
نه واقعا

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

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

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

به عنوان مثال، ما یک لیست با لیستی از میوه ها داریم:

برای ایجاد یک لیست کشویی با استفاده از فرمول OFFSET، باید موارد زیر را انجام دهید:
سلولی را که می خواهیم در آن لیست کشویی ایجاد کنیم را انتخاب کنیم.
به برگه "داده ها" => "کار با داده ها" در نوار ابزار بروید => "تأیید اعتبار داده ها" را انتخاب کنید:
اعتبارسنجی داده ها در اکسل

در پنجره پاپ آپ «بررسی مقادیر ورودی»، در تب «پارامترها»، «فهرست» را در نوع داده انتخاب کنید:
اعتبارسنجی مقادیر ورودی در اکسل

در قسمت "منبع" فرمول را وارد کنید: = OFFSET (A$2,0,0,5)
روی "OK" کلیک کنید

سیستم یک لیست کشویی با لیستی از میوه ها ایجاد می کند.
این فرمول چگونه کار می کند؟

در مثال بالا، از فرمول =OFFSET(link,offset_by_rows,offset_by_columns,[height],[width]) استفاده کردیم.
این تابع شامل پنج آرگومان است. آرگومان "link" (در مثال $A$2) نشان می دهد که افست از کدام سلول شروع شود. در آرگومان های "offset_by_rows" و "offset_by_columns" (در مثال مقدار "0" مشخص شده است) - چند سطر/ستون برای نمایش داده ها باید جابجا شوند.

آرگومان "[height]" مقدار "5" را مشخص می کند که نشان دهنده ارتفاع محدوده سلول ها است. ما آرگومان "[width]" را مشخص نمی کنیم، زیرا در مثال ما محدوده از یک ستون تشکیل شده است.
با استفاده از این فرمول، سیستم به شما به عنوان داده برای لیست کشویی، محدوده ای از سلول ها را که با سلول $A$2 شروع می شود، شامل 5 سلول به شما برمی گرداند.

نحوه ایجاد یک لیست کشویی در اکسل با جایگزینی داده (با استفاده از تابع OFFSET)

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

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

به برگه "داده ها" بروید => بخش "کار با داده" در نوار ابزار => "تایید اعتبار داده ها" را انتخاب کنید.
در پنجره پاپ آپ «بررسی مقادیر ورودی»، در تب «پارامترها»، «فهرست» را در نوع داده انتخاب کنید.
در قسمت "منبع"، فرمول را وارد کنید: = OFFSET(A$2$,0,0,COUNTIF($A$2:$A$100;"<>”))
روی "OK" کلیک کنید

در این فرمول، در آرگومان «[height]»، ما به عنوان آرگومان ارتفاع فهرست با داده را نشان می‌دهیم - فرمول COUNTIF، که تعداد سلول‌های خالی را در محدوده داده شده A2:A100 محاسبه می‌کند.

توجه: برای اینکه فرمول به درستی کار کند، مهم است که هیچ خط خالی در لیست داده‌ها برای نمایش در منوی کشویی وجود نداشته باشد.

نحوه ایجاد یک لیست کشویی در اکسل با جایگزینی خودکار داده ها

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

در نوار ابزار، روی "Format as table" کلیک کنید:

یک سبک طراحی جدول را از منوی کشویی انتخاب کنید

با کلیک بر روی دکمه "OK" در پنجره پاپ آپ، محدوده انتخاب شده سلول ها را تأیید کنید:

یک نام به جدول در سلول بالا سمت راست بالای ستون "A" اختصاص دهید:

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

به برگه "داده ها" => "کار با داده ها" در نوار ابزار بروید => "تأیید اعتبار داده ها" را انتخاب کنید:

در پنجره پاپ آپ «بررسی مقادیر ورودی»، در تب «پارامترها»، «فهرست» را در نوع داده انتخاب کنید:

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

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

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

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

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

اکسل توانایی کپی لیست های کشویی ایجاد شده را دارد. به عنوان مثال، در سلول A1 یک لیست کشویی داریم که می خواهیم آن را در محدوده سلول های A2:A6 کپی کنیم.

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

سلول های محدوده A2:A6 را انتخاب کنید که می خواهید لیست کشویی را در آن درج کنید.

میانبر صفحه کلید CTRL+V را فشار دهید.
بنابراین، لیست کشویی را با حفظ فرمت اصلی لیست (رنگ، ​​فونت و غیره) کپی خواهید کرد. اگر می خواهید یک لیست کشویی را بدون ذخیره قالب کپی/پیست کنید، سپس:
روی سلول با لیست کشویی که می خواهید کپی کنید کلیک چپ کنید.

میانبر صفحه کلید CTRL+C را فشار دهید.
سلولی را انتخاب کنید که می خواهید لیست کشویی را در آن درج کنید.
راست کلیک کنید => با منوی کشویی تماس بگیرید و روی "Paste Special" کلیک کنید.
لیست کشویی در اکسل

در پنجره ظاهر شده، در بخش "Insert"، "conditions on values" را انتخاب کنید:

روی "OK" کلیک کنید
پس از این، اکسل فقط داده ها را از لیست کشویی بدون حفظ قالب بندی سلول اصلی کپی می کند.
نحوه انتخاب تمام سلول های حاوی یک لیست کشویی در اکسل

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

روی تب "Home" در نوار ابزار کلیک کنید.
روی «یافتن و انتخاب» کلیک کنید و «انتخاب گروه سلول‌ها» را انتخاب کنید:

در کادر محاوره ای، "Data Validation" را انتخاب کنید. در این قسمت می توانید آیتم های «همه» و «همان» را انتخاب کنید. "همه" به شما امکان می دهد تمام لیست های کشویی موجود در برگه را انتخاب کنید. مورد "همان" لیست های کشویی با محتوای داده های مشابه را در منوی کشویی نشان می دهد. در مورد ما، "همه" را انتخاب می کنیم:
لیست کشویی در اکسل چگونه همه لیست ها را پیدا کنیم

روی "OK" کلیک کنید
با کلیک کردن بر روی "OK"، اکسل تمام سلول های دارای لیست کشویی روی برگه را برجسته می کند. به این ترتیب می توانید همه لیست ها را به یک فرمت مشترک بیاورید، مرزها را برجسته کنید و غیره.

نحوه ایجاد لیست های کشویی وابسته در اکسل

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

برای ایجاد یک لیست کشویی وابسته به موارد زیر نیاز داریم:
دو محدوده نامگذاری شده برای سلولهای "A2:A5" با نام "Russia" و برای سلولهای "B2:B5" با نام "USA" ایجاد کنید. برای انجام این کار، باید کل محدوده داده را برای لیست های کشویی انتخاب کنیم:
لیست کشویی وابسته در اکسل

به برگه "فرمول ها" بروید => در قسمت "نام های تعریف شده" در مورد "ایجاد از انتخاب" کلیک کنید:
لیست های کشویی وابسته در اکسل

در پنجره بازشو «ایجاد نام‌ها از محدوده انتخابی»، کادر «در خط بالا» را علامت بزنید. پس از انجام این کار، اکسل دو محدوده با نام "روسیه" و "ایالات متحده آمریکا" را با لیستی از شهرها ایجاد می کند:
لیست کشویی وابسته-در اکسل

روی "OK" کلیک کنید
در سلول "D2" یک لیست کشویی ایجاد کنید تا کشورهای "روسیه" یا "ایالات متحده آمریکا" را انتخاب کنید. بنابراین، ما اولین لیست کشویی را ایجاد می کنیم که در آن کاربر می تواند یکی از دو کشور را انتخاب کند.

اکنون برای ایجاد یک لیست کشویی وابسته:
سلول E2 (یا هر سلول دیگری که می خواهید در آن یک لیست کشویی وابسته ایجاد کنید) را انتخاب کنید.
روی برگه "داده ها" => "بررسی داده ها" کلیک کنید.
در پنجره پاپ آپ "Validate input values" در تب "Parameters" در نوع داده، "List" را انتخاب کنید:
اعتبارسنجی مقادیر ورودی در اکسل

روی "OK" کلیک کنید

حال، اگر در لیست کشویی اول کشور «روسیه» را انتخاب کنید، تنها شهرهایی که متعلق به این کشور هستند در لیست کشویی دوم ظاهر می‌شوند. هنگامی که از اولین لیست کشویی "USA" را انتخاب کنید، این مورد نیز صادق است.

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

ساده ترین راه برای کنار آمدن با کار وجود دارد. روی سلول زیر ستون داده کلیک راست می کنید تا منوی زمینه باز شود. در اینجا ما به دنبال مورد "انتخاب از لیست کشویی" هستیم. همین عمل را می توان به راحتی با استفاده از ترکیب Alt + فلش پایین انجام داد.

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

روش سنتی

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

درج/نام/تخصیص (Excel 2003)

در آخرین نسخه (2007، 2010، 2013، 2016) به آدرس زیر بروید:

فرمول ها / نام های تعریف شده / مدیر نام / جدید

هر نامی را وارد کنید و روی OK ضربه بزنید.

سپس سلول ها (یا چندین) را انتخاب کنید که قصد دارید لیست فیلدهای مشخص شده را در آن درج کنید. به منو بروید:

داده/نوع داده/فهرست

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

علاوه بر این، می توانید آن را برای ایجاد یک محدوده کشش دهید. به هر حال، اگر اطلاعات موجود در آن تغییر کند، اطلاعات لیست نیز تغییر می کند.

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

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

  • ابتدا به دنبال بخش «توسعه‌دهنده» (Excel 2007/2010) بگردید، در نسخه‌های دیگر خودتان آن را از طریق پارامترهای «Customize Ribbon» فعال می‌کنید.

  • در مرحله دوم، به بخش بروید، روی "درج" کلیک کنید.
  • سوم، "Field..." را انتخاب کنید و روی نماد کلیک کنید.
  • یک مستطیل بکشید؛

  • روی آن راست کلیک کرده و Format Object را کلیک کنید.

  • به دنبال «فرم…» باشید، مرزهای مورد نیاز را انتخاب کنید.
  • ناحیه ای را که می خواهید شماره سریال عنصر را در لیست تنظیم کنید علامت بزنید.
  • روی OK کلیک کنید.

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

مراحل مشابه مواردی است که در بالا توضیح داده شد، اما ما به دنبال "Field with ActiveX" هستیم.

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

ویژگی های اضافی

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


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


برای دوم، پنجره بررسی اطلاعات را فعال می کنیم، اما در آیتم "منبع" "=INDIRECT" و آدرس سلول اول را می نویسیم. همه چیز درست شد.

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

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

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

چرا به لیست در اکسل نیاز دارید؟

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

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

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

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

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

لیست کشویی از برگه دیگری در اکسل

اگر از برگه های مختلف اکسل کار می کنید، یک لیست کشویی از برگه دیگری به ما در کار بیشتر کمک زیادی می کند. درست کردن آن کاملاً ساده است. ابتدا ستون مورد نظر از داده های برنامه را انتخاب کنید و دوباره دکمه Data Check را در برگه دوم فشار دهید. در مرحله بعد، به صفحه اول و در بخش "منبع" بروید، سپس باید سلول ها را انتخاب کنید و روی دکمه "OK" کلیک کنید. پس از این، لیست کشویی باید به صفحه دیگری منتقل شود.

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

لیست های کشویی وابسته چیست؟ اینها لیست هایی هستند که از دو منبع ایجاد می شوند. به عنوان مثال، دو جدول وجود دارد که شما نام آشنایان و دوستان خود را در آنها یادداشت کرده اید. و در ستون بعدی یک لیست کشویی وابسته نمایش داده می شود که همان داده های ثبت شده در جداول دیگر را نشان می دهد. در مرحله بعد، ما چنین لیست های وابسته و بسط دهنده ها را ایجاد خواهیم کرد.

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

تمام اقدامات مشابهی را که در قسمت اول مقاله انجام دادیم انجام دهید و سپس در "منبع" فرمول زیر را مشخص کنید "= OFFSET(A$2$;0;0;5، سپس روی "OK" کلیک کنید تا قطره ایجاد شود. فهرست پایین در .

در بالا کمی در مورد لیست های وابسته صحبت کردیم. حالا بیایید آنها را ایجاد کنیم. اما ابتدا دو ستون را با هر داده ای ایجاد و پر کنید. بعد، این ستون ها را در جداول انتخاب کنید و روی دکمه در بالا کلیک کنید - "ایجاد از قطعه انتخاب شده" (صفحه 4).

بعد، ما یک لیست کشویی ایجاد می کنیم (فرایند ایجاد در بالا در مقاله نشان داده شده است). سپس به تابع “Sources” رفته و دستور زیر را در فیلد بنویسید - =INDIRECT($D$2). این به شما کمک می کند تا لیست های وابسته ایجاد کنید. روی دکمه "OK" کلیک کنید، پس از آن باید ستون دیگری با یک لیست بازشو وابسته در جدول ظاهر شود.

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

کپی کردن لیست کشویی برای کاربران سخت نخواهد بود. برای انجام این کار، سلول مورد نیاز خود را با دکمه سمت چپ ماوس انتخاب کرده و CTRL+C (کپی) را فشار دهید. سپس روی هر ستونی در جدول اکسل کلیک کنید. و روی دستور - Paste Special - راست کلیک کنید. در این تابع درج باید - "شرایط مقادیر" را تنظیم کنید و روی "OK" کلیک کنید تا تغییرات ذخیره شود.

پس از این، دوباره کلیدها را به طور همزمان فشار دهید - CTRL + V، که به معنای "Paste" است و متعاقبا، کل لیست کشویی کپی شده در این برنامه با موفقیت جایگذاری می شود.

نتیجه

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

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

برای تنظیم لیست کشویی انتخاب کنید. در منو، موارد "داده" - "بررسی" را باز کنید. سپس در پنجره جدید به تب "پارامترها" بروید و در قسمت "نوع داده" که باز می شود، خط "فهرست" را تنظیم کنید. در همان زمان، فیلد "منبع" در همان پنجره ظاهر می شود. نماد "=" و نام محدوده انتخابی که به سلول های داده اختصاص داده شده است را وارد کنید. برای اعمال پارامترها، "Enter" یا "Ok" را فشار دهید. این یک گونه از ساده ترین لیست کشویی است.

در همان زمان، فیلد "منبع" در همان پنجره ظاهر می شود. نماد "=" و نام محدوده انتخابی که به سلول های داده اختصاص داده شده است را وارد کنید. برای اعمال پارامترهای تنظیم شده، "Enter" یا "Ok" را فشار دهید. این یک گونه از ساده ترین لیست کشویی است.

اکسل توانایی ایجاد یک لیست کشویی با طراحی پیچیده تر را دارد. برای انجام این کار، از کنترلی به نام جعبه ترکیبی استفاده می کنید که در یک کاربرگ اکسل قرار می گیرد. برای نصب آن، آیتم منوی "View" و سپس "Toolbars" و زیر آیتم "Forms" را باز کنید.

نماد "جعبه ترکیبی" را در کنترل پنل باز شده انتخاب کنید - این لیست کشویی است. با ماوس یک مستطیل به شکل یک میدان بکشید. روی لیست ترسیم شده کلیک راست کرده و دستور "Format Object..." را انتخاب کنید.

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

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

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

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

مسیر: منوی داده - ابزار اعتبارسنجی داده ها - تب گزینه ها. نوع داده - "فهرست".

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

هر یک از گزینه ها همان نتیجه را خواهد داشت.



لیست کشویی در اکسل با جایگزینی داده ها

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


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

بیایید یک مقدار جدید "درخت کریسمس" را به جدول اضافه کنیم.

حالا بیایید مقدار "توس" را حذف کنیم.

"میز هوشمند" که به راحتی "بسط" و تغییر می کند به ما کمک کرد تا برنامه های خود را محقق کنیم.

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


هنگامی که یک نام جدید را در یک سلول خالی از لیست کشویی وارد می کنیم، پیامی ظاهر می شود: "نام وارد شده baobab را به لیست کشویی اضافه کنید؟"

روی "بله" کلیک کنید و یک خط دیگر با مقدار "baobab" اضافه کنید.

لیست کشویی در اکسل با داده های برگه/فایل دیگر

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

  1. سلولی را که می خواهیم لیست کشویی را در آن قرار دهیم فعال می کنیم.
  2. گزینه های تأیید داده را باز کنید. در قسمت "منبع"، فرمول را وارد کنید: =INDIRECT("[List1.xlsx]Sheet1!$A$1:$A$9").

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

نحوه ایجاد لیست های کشویی وابسته

بیایید سه محدوده نامگذاری شده را در نظر بگیریم:

این یک امر ضروری است. در بالا نحوه ایجاد یک لیست معمولی یک محدوده با نام (با استفاده از "Name Manager") توضیح داده شده است. به یاد داشته باشید که نام نمی تواند دارای فاصله یا علائم نگارشی باشد.

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

  3. حالا بیایید یک لیست کشویی دوم ایجاد کنیم. باید آن کلماتی را که با نام انتخاب شده در لیست اول مطابقت دارند منعکس کند. اگر "درختان"، سپس "ممرز"، "بلوط"، و غیره. تابعی به شکل =INDIRECT(E3) را در قسمت “Source” وارد کنید. E3 - سلول با نام محدوده اول.

    4. انتخاب چندین مقدار از یک لیست کشویی اکسل

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

    1. با استفاده از ابزار Data Validation یک لیست استاندارد ایجاد می کنیم. یک ماکرو آماده به کد منبع برگه اضافه می کنیم. نحوه انجام این کار در بالا توضیح داده شده است. با کمک آن، مقادیر انتخاب شده در سمت راست لیست کشویی اضافه می شوند.
      2. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E2:E9")) هیچ چیز نیست و Target.Cells.Count = 1 سپس Application.EnableEvents = False If Len(Target.Offset (0, 1)) = 0 سپس Target.Offset(0,1) = Target Else Target.End (xlToRight).Offset(0,1) = Target End If Target.ClearContents Application.EnableEvents = پایان واقعی اگر پایان فرعی
    2. برای اینکه مقادیر انتخاب شده در زیر ظاهر شوند، کد کنترلر دیگری را وارد می کنیم.
      3. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("H2:K2")) هیچ چیز نیست و Target.Cells.Count = 1 سپس Application.EnableEvents = False If Len(Target.Offset (1, 0)) = 0 سپس Target.Offset(1,0) = Target Else Target.End (xlDown).Offset(1,0) = Target End If Target.ClearContents Application.EnableEvents = پایان واقعی اگر پایان فرعی
    3. برای نمایش مقادیر انتخاب شده در یک سلول که با هر علامت نقطه گذاری جدا شده اند، از ماژول زیر استفاده کنید.

      4. کاربرگ_تغییر خصوصی فرعی (ByVal Target As Range)
      On Error Resume Next
      If Not Intersect(Target, Range("C2:C5")) هیچ چیز نیست و Target.Cells.Count = 1 سپس
      Application.EnableEvents = False
      newVal = هدف
      برنامه. لغو
      اولدوال = هدف
      اگر لن (اولدوال)<>0 و اولدوال<>newVal سپس
      Target = Target & "," & newVal
      دیگر
      هدف = newVal
      پایان اگر
      اگر Len(newVal) = 0 سپس Target.ClearContents
      Application.EnableEvents = True
      پایان اگر
      پایان فرعی

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

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

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



 

شاید خواندن آن مفید باشد: