یک لیست کشویی ایجاد کنید. استفاده از کنترل های ActiveX

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

در پنجره پاپ آپ "Validation of entered values" در تب "Parameters"، "List" را در نوع داده انتخاب کنید:
اعتبارسنجی مقادیر ورودی در اکسل

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

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

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

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

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

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

در پنجره پاپ آپ "Validation of entered values" در تب "Parameters"، "List" را در نوع داده انتخاب کنید:
اعتبارسنجی مقادیر ورودی در اکسل

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

در پنجره پاپ آپ "Validation of entered values" در تب "Parameters"، "List" را در نوع داده انتخاب کنید:

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

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

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

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

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

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

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

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

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

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

در پنجره ای که ظاهر می شود، در بخش "Insert"، "conditions for مقادیر" را انتخاب کنید:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

روش استاندارد

نیاز دارد محدوده ای از سلول ها را انتخاب کنیدکه از آن ایجاد خواهد شد لیست کشویی، سپس درج کنید – نام – اختصاص دهید(اکسل 2003). در بیشتر نسخه جدید(2007، 2010، 2013، 2016) به برگه بروید فرمول هاکجا در بخش اسامی خاصدکمه را پیدا کنید نام مدیر.

دکمه را فشار دهید ايجاد كردن، یک نام وارد کنید، می توانید از هر نامی استفاده کنید، سپس خوب.

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

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

یک نکته جالب - وقتی داده های محدوده تغییر می کند، لیست مبتنی بر آن نیز تغییر می کند، یعنی تغییر می کند پویا.

با استفاده از کنترل ها

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

یک برگه را انتخاب کنید توسعه دهنده(برای اکسل 2007/2010)، در نسخه های دیگر باید این برگه را در نوار موجود در مولفه های – روبان را سفارشی کنید.

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

روی آن کلیک راست کنید - فرمت شی.

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

استفاده از کنترل های ActiveX

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

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

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

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

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

چگونه با یک دستور در نوار ابزار در اکسل 2010 یا 2016 لیست کشویی ایجاد کنیم؟ در تب Data، در بخش Data Tools، دکمه Data Validation را پیدا کنید. روی آن کلیک کنید و اولین مورد را انتخاب کنید.

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


خطی در زیر برای نشان دادن منابع ظاهر می شود.


شما می توانید اطلاعات را به روش های مختلف مشخص کنید.

ابتدا بیایید یک نام اختصاص دهیم. برای انجام این کار، چنین جدولی را در هر صفحه ایجاد کنید.

آن را انتخاب کرده و راست کلیک کنید. بر روی دستور "Assign Name" کلیک کنید.

نام را در خط بالا وارد کنید.

پنجره "Data Validation" را فراخوانی کنید و نامی را در قسمت "Source" مشخص کنید و علامت "=" را در مقابل آن قرار دهید.


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

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

جایگزینی داده پویا اکسل

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

آن را انتخاب کنید و در تب "Home" هر سبک جدول را انتخاب کنید.


حتما کادر زیر را علامت بزنید.

شما چنین طرحی را دریافت خواهید کرد.

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

= غیر مستقیم ("جدول 1 [شهرها]")

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


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

بیایید سعی کنیم تعداد شهرها را افزایش دهیم.


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

آدرس_سلول

به عنوان مثال، اگر لیست داده ها در سلول D1 است، سپس در سلولی که نتایج انتخاب شده نمایش داده می شود، فرمول را وارد کنید.

نحوه حذف (حذف) لیست کشویی در اکسل

پنجره تنظیمات کشویی را باز کنید و در بخش «نوع داده» «Any value» را انتخاب کنید.

مورد غیر ضروریناپدید خواهد شد.

اقلام وابسته

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


در این صورت برای هر ستون یک نام بگذارید. بدون اولین سلول (نام) انتخاب کنید و دکمه سمت راست ماوس را فشار دهید. "Assign Name" را انتخاب کنید.

این نام شهر خواهد بود.


هنگام نامگذاری سن پترزبورگ و نیژنی نووگورودشما یک خطا دریافت خواهید کرد زیرا نام نمی تواند شامل فاصله، زیرخط، شخصیت های خاصو غیره.


بنابراین با قرار دادن نام این شهرها را تغییر می دهیم زیرخط.


اولین عنصر را در سلول A9 به روش معمول ایجاد می کنیم.


و در دومی فرمول را می نویسیم:

غیر مستقیم (A9)

ابتدا یک پیغام خطا مشاهده خواهید کرد. موافق.

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

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

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


برای لیست دوم، باید فرمول را وارد کنید:

OFFSET ($ A $ 1; SEARCH ($ E $ 6; $ A: $ A; 0) -1; 1; COUNTIF ($ A: $ A; $ E $ 6); 1)

MATCH شماره سلول را با شهر انتخاب شده در لیست اول (E6) در ناحیه SA مشخص شده برمی گرداند: $ A.
COUNTIF تعداد موارد منطبق در محدوده را با مقدار موجود در سلول مشخص شده (E6) می شمارد.

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

چند انتخابی

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


پنجره توسعه دهنده باز می شود. الگوریتم زیر باید در آن درج شود.

Worksheet_Change فرعی خصوصی (ByVal Target As Range) On Error Resume Next If Not Intersect (Target, Range ("C2: F2")) هیچ است و 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 = پایان واقعی اگر پایان فرعی

توجه داشته باشید که در خط

If Not Intersect (Target, Range ("E7")) هیچ چیز نیست و Target.Cells.Count = 1 سپس

شما باید آدرس سلول را همراه با لیست قرار دهید. ما آن را E7 خواهیم داشت.

برگرد صفحه اکسلو یک لیست در سلول E7 ایجاد کنید.

پس از انتخاب، مقادیر زیر آن ظاهر می شوند.

کد زیر به شما امکان می دهد مقادیر را در یک سلول جمع آوری کنید.

Worksheet_Change فرعی خصوصی (ByVal Target As Range) On Error Resume Next If Not Intersect (Target, Range ("E7")) هیچ است و Target.Cells.Count = 1 سپس Application.EnableEvents = False newVal = Target Application.Undoval = هدف اگر لن (اولدوال)<>0 و اولدوال<>newVal سپس Target = Target & "," & newVal Else Target = newVal End If If Len (newVal) = 0 سپس Target.ClearContents Application.EnableEvents = پایان واقعی If End Sub

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


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

روز خوبی داشته باشی!

برای سهولت کار کاربران با برگه، فهرست‌های کشویی را به سلول‌ها اضافه کنید که می‌توانید مورد مورد نظر خود را از آن‌ها انتخاب کنید.

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



یادداشت:

1. سلولی را در برگه که می‌خواهید لیست کشویی را در آن قرار دهید انتخاب کنید.

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

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

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

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

   

   اگر می توانید سلول را خالی بگذارید، کادر را علامت بزنید سلول های خالی را نادیده بگیرید.

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

   برگه را باز کنید برای ورود پیام دهید.

   
   

2. برگه را باز کنید پیغام خطا.

   
   

3. مطمئن نیستید کدام گزینه را در فیلد انتخاب کنید چشم انداز?

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

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

دانلود نمونه ها

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

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

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

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

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

1. برای نمایش خطوط یک سطح، روی نمادهای طرح مربوطه کلیک کنید. 2. سطح 1 شامل کل فروش برای تمام خطوط جزئیات است. 3. سطح 2 شامل کل فروش برای هر ماه در هر منطقه است. 4. سطح 3 شامل خطوط با جزئیات (در در این موردخطوط 11 تا 13). 5. برای بزرگ کردن یا کوچک کردن داده ها در طرح کلی، روی نمادهای طرح کلی کلیک کنید و.