Excel: از یک لیست انتخاب کنید. لیست کشویی در یک سلول کاربرگ

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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


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

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

1. با استفاده از ابزار Data Validation یک لیست استاندارد ایجاد می کنیم. یک ماکرو آماده به کد منبع برگه اضافه می کنیم. نحوه انجام این کار در بالا توضیح داده شده است. با کمک آن، مقادیر انتخاب شده در سمت راست لیست کشویی اضافه می شوند.
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. برای اینکه مقادیر انتخاب شده در زیر ظاهر شوند، کد کنترلر دیگری را وارد می کنیم.
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. برای نمایش مقادیر انتخاب شده در یک سلول که با هر علامت نقطه گذاری جدا شده اند، از ماژول زیر استفاده کنید.


کاربرگ_تغییر خصوصی فرعی (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
پایان اگر
پایان فرعی


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

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



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

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

فرض کنید، زمانی که من یک جدول حسابداری تجهیزات رایانه ای (مدت ها پیش) در محل کار خود ایجاد کردم، برای اینکه کل فرآیند کار را راحت تر و سریع تر کنم، یک لیست کشویی در ستون های خاص ایجاد کردم و مقادیر خاصی را وارد کردم. آنجا. و هنگامی که ستون "سیستم عامل" را پر کردم (اما در همه رایانه ها یکسان نیست)، چندین مقدار را پر کردم (7، 8، 8.1، 10)، و سپس به سادگی همه آن ها را با یک کلیک انتخاب کردم. دکمه ی ماوس.

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

آماده سازی

مراحل اساسی

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

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

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

با احترام، دیمیتری کوستین

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

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

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

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

حالا سلولی را که می خواهید لیست کشویی را در آن اضافه کنید انتخاب کنید و به تب Data بروید.

در زیر تب Data Tools داده هاروی دکمه Data Validation کلیک کنید.

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

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

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

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

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

اگر تصمیم دارید که لیست کشویی را از یک سلول حذف کنید، کادر محاوره ای Data Validation را همانطور که در بالا توضیح داده شد باز کنید و روی دکمه Clear All کلیک کنید، که صرف نظر از اینکه کدام برگه در کادر محاوره ای انتخاب شده در دسترس است.

گزینه های موجود در کادر محاوره ای Data Validation به مقادیر پیش فرض خود بازنشانی می شوند. روی OK کلیک کنید تا منوی کشویی حذف شود و سلول به حالت پیش فرض خود بازگردد.

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

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

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

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

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

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

گام 2.حالا همان تب اول را باز کنید "گزینه ها"،و نصب کنید "فهرست"در لیست نوع داده

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

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

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

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

ویدئو - ایجاد لیست های کشویی در اکسل

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

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

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

   

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

   

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

   

4. پس از تکمیل مراحل، نوع محدوده زیر را دریافت خواهید کرد.

   

5. حالا روی سلولی که لیست کشویی در آن قرار دارد کلیک چپ کرده و به تب بروید "داده ها"(روش قبلی به شما می گوید که چگونه این کار را انجام دهید).

   

6. در قسمت ورودی "منبع"باید تابعی را با سینتکس وارد کنید "=INDIRECT("TableName[Header]")". اسکرین شات یک مثال خاص تر را نشان می دهد.

   

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

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

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

اگر یک سلول را حذف کنید (در این مورد ما "توس" را حذف کردیم)، این نیز در محتوای لیست کشویی منعکس می شود.

در یک یادداشت!در این روش به اصطلاح «میز هوشمند» می پردازیم. به راحتی قابل توسعه است و این ویژگی برای بسیاری از دستکاری ها با جداول اکسل از جمله ایجاد یک لیست کشویی مفید است.

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

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

1. ابتدا باید یک محدوده با نام ایجاد کنید. به برگه بروید "فرمول ها"، سپس انتخاب کنید "نام مدیر"و "ايجاد كردن".

   

   

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

   

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

   

4. برگه را باز کنید "داده ها"(در روش اول مسیر رسیدن به آن مشخص شده است) و نام محدوده های نامگذاری شده را در منبع نشان دهید، همانطور که در تصویر نشان داده شده است.

   

   

5. اکنون باید با استفاده از همان طرح یک کشویی اضافی ایجاد کنید. این فهرست کلماتی را که با عنوان مطابقت دارند منعکس می کند. به عنوان مثال، اگر "درخت" را انتخاب کرده باشید، "توس"، "آهنگ"، "افرا" و غیره خواهد بود. برای این کار مراحل بالا را تکرار کنید اما در قسمت ورودی "منبع"تابع را وارد کنید "=INDIRECT(E1)" . در این مورد "E1"– این آدرس سلول با نام محدوده اول است. با استفاده از همین روش، می توانید هر تعداد لیست به هم پیوسته که نیاز دارید ایجاد کنید.

   

   

اکنون چندین روش برای ایجاد یک منوی کشویی در می دانید. این ممکن است در تعدادی از موارد و به خصوص در تأیید داده ها برای شما مفید باشد. انتخاب روش مناسب باید به نوع جدولی که با آن کار می کنید بستگی دارد. اگر این یک جدول "یک بار" است، روش اول مناسب است - سریع و آسان است. اگر جدول نیاز به تغییرات ثابت دارد، بهتر است از روشی استفاده کنید که شامل ایجاد «جدول هوشمند» و محدوده‌های نام‌گذاری شده باشد.

ویدئو - لیست های کشویی مرتبط: آسان و سریع

برای جداولی که از داده‌های ثابت و تکراری استفاده می‌کنند (مثلاً نام کارمندان، محدوده محصول یا درصد تخفیف برای مشتری)، به منظور در نظر نگرفتن و اشتباه نکردن هنگام تایپ، می‌توان یک‌بار فهرست استاندارد ایجاد کرد و هنگام جایگزینی داده ها، انتخابی از آن انجام دهید. این مقاله به شما امکان می دهد از 4 روش مختلف برای ایجاد یک لیست کشویی در اکسل استفاده کنید.

روش 1 - کلیدهای میانبر و بازشو در اکسل

این روش استفاده از یک لیست کشویی اساساً یک ابزار جدول نیست که به هیچ وجه نیاز به پیکربندی یا پر کردن داشته باشد. این یک عملکرد داخلی (کلیدهای داغ) است که همیشه کار می کند. هنگام پر کردن هر ستونی، می توانید روی یک سلول خالی کلیک راست کرده و آیتم منوی "انتخاب از لیست کشویی" را از لیست کشویی انتخاب کنید.

همان آیتم منو را می توان با استفاده از میانبر صفحه کلید راه اندازی کرد Alt+»پیکان پایین»و برنامه به طور خودکار مقادیر سلول هایی را که قبلاً با داده ها پر کرده اید در لیست کشویی پیشنهاد می کند. در تصویر زیر، برنامه 4 گزینه پر کردن را ارائه می دهد (اکسل داده های تکراری را نشان نمی دهد). تنها شرط کار این ابزار این است که بین سلولی که داده‌های لیست را وارد می‌کنید و خود لیست، سلول خالی وجود نداشته باشد.

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

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

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

روش 2 - راحت ترین، ساده ترین و انعطاف پذیرترین

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

برای ایجاد چک ورودی، نام لیست ایجاد شده قبلی را وارد کنید

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

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

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

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

برای استفاده از این روش، باید تب “DEVELOPER” را فعال کنید. به طور پیش فرض، این برگه در دسترس نیست. برای فعال کردن آن:

1. روی "File" در گوشه سمت چپ بالای برنامه کلیک کنید.
2. "Options" را انتخاب کرده و روی آن کلیک کنید.
3. در پنجره تنظیمات اکسل، در تب "Customize Ribbon"، کادر کنار برگه "Developer" را علامت بزنید.

فعال کردن تب DEVELOPER

اکنون می توانید از ابزار Combo Box (کنترل اکتیو ایکس) استفاده کنید. در تب "DEVELOPER" روی دکمه "Insert" کلیک کنید و دکمه "Combo Box (ActiveX Element)" را در کنترل های ActiveX پیدا کنید. روی آن کلیک کنید.

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

اکنون باید این عنصر را پیکربندی کنید. برای انجام این کار، باید "Design Mode" را فعال کنید و روی دکمه "Properties" کلیک کنید. پنجره Properties شما باید باز شود.

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

اما در مرحله ایجاد ما فقط به سه مورد اصلی علاقه مندیم:

1. ListFillRange - محدوده سلول هایی را که مقادیر لیست کشویی از آنها گرفته می شود را مشخص می کند. در مثال خود، دو ستون را مشخص کردم (A2:B7 - بعداً نحوه استفاده از آن را به شما نشان خواهم داد). اگر فقط یک مقدار مورد نیاز است، A2:A7 نشان داده شده است.
2. ListRows - مقدار داده در لیست کشویی. عنصر ActiveX با روش اول تفاوت دارد زیرا می توانید مقدار زیادی داده را مشخص کنید.
3. ColumnCount - مشخص می کند که چند ستون داده در لیست کشویی مشخص شود.

در خط ColumnCount من مقدار 2 را مشخص کردم و اکنون داده های کشویی در لیست به شکل زیر است:

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

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

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

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

برای این کار در سلول های A1:A7 داده هایی را که در لیست نمایش داده می شود وارد می کنیم. حالا بیایید سلولی را انتخاب کنیم که در آن یک لیست کشویی ایجاد می کنیم - B2.

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

در برگه «پارامترها»، در قسمت «نوع داده»، «فهرست» را انتخاب کنید. می توانید مقادیر را در قسمت Source به روش های مختلف وارد کنید:

1 - مقادیر لیست را به صورت دستی وارد کنید که با نقطه ویرگول از هم جدا شده اند.

2 - محدوده سلول هایی را که داده های لیست کشویی در آنها وارد می شود را مشخص کنید.

3 – سلول های دارای نام را انتخاب کرده، روی آنها کلیک راست کرده و از منو گزینه Assign a name را انتخاب کنید.

سلول B2 را انتخاب کنید و "=" را در قسمت "Source" قرار دهید، سپس نام ایجاد شده را بنویسید.

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

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

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

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

محدوده سلول ها را انتخاب کنید - D1:D8، سپس در تب "Home"، روی "Format as table" کلیک کنید و هر سبکی را انتخاب کنید.

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

در بالا عنوان جدول را می نویسیم - "کارمندان" و آن را با داده ها پر می کنیم.

سلولی که لیست کشویی در آن قرار دارد را انتخاب کنید و روی دکمه "بررسی داده ها" کلیک کنید. در پنجره بعدی، در قسمت "Source"، موارد زیر را بنویسید: =INDIRECT("Table1"). من یک جدول روی یک برگه دارم، بنابراین می‌نویسم "جدول 1"، اگر دومی وجود دارد - "جدول2" و غیره.

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

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

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

در برگه 2، یک سلول یا محدوده ای از سلول ها را انتخاب کنید، سپس بر روی دکمه "Data Validation" کلیک کنید.

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

اکنون می توانید نام ها را در برگه 1 اضافه کنید، آنها به لیست های کشویی در برگه 2 اضافه می شوند.

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

بیایید فرض کنیم سه محدوده داریم: نام، نام خانوادگی و نام خانوادگی کارکنان. برای هر کدام، باید یک نام اختصاص دهید. ما سلول های این محدوده را انتخاب می کنیم، همچنین می توانید آنها را خالی کنید - با گذشت زمان می توانید داده هایی را به آنها اضافه کنید که در لیست کشویی ظاهر می شود. روی آنها کلیک راست کرده و از لیست گزینه "Assign a name" را انتخاب کنید.

ما اولی را "نام"، دومی "نام خانوادگی"، سومی را "پدر" می نامیم.

بیایید یک محدوده دیگر بسازیم که نام های اختصاص داده شده در آن نوشته شود. بیایید آن را "کارمندان" بنامیم.

ما اولین لیست کشویی را ایجاد می کنیم که از نام محدوده ها تشکیل شده است. سلول E1 را انتخاب کنید و در برگه "داده" "Data Validation" را انتخاب کنید.

در قسمت «نوع داده»، «فهرست» را انتخاب کنید؛ در قسمت منبع، «=کارمندان» را وارد کنید یا محدوده‌ای از سلول‌ها را انتخاب کنید که نامی به آنها اختصاص داده شده است.

اولین لیست کشویی ایجاد شده است. اکنون در سلول F2 یک لیست دوم ایجاد می کنیم که باید به لیست اول بستگی داشته باشد. اگر در اولی “Name” را انتخاب کنیم، لیستی از نام ها در مورد دوم نمایش داده می شود و اگر “Name” را انتخاب کنیم، لیستی از نام های خانوادگی نمایش داده می شود.

سلول را انتخاب کنید و روی دکمه "بررسی داده ها" کلیک کنید. در قسمت "نوع داده"، "List" را انتخاب کنید؛ در قسمت منبع، موارد زیر را وارد کنید: =INDIRECT($E$1). در اینجا E1 سلول با اولین لیست کشویی است.

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

اگر در آینده، باید مقادیر را در محدوده ای وارد کنید که نامی به آن داده می شود، به عنوان مثال، "نام خانوادگی". به تب Formulas رفته و روی Name Manager کلیک کنید. اکنون در نام محدوده "Last Name" را انتخاب کنید و در زیر به جای آخرین سلول C3، C10 را بنویسید. روی علامت چک کلیک کنید. پس از این، محدوده افزایش می یابد و می توانید داده هایی را به آن اضافه کنید که به طور خودکار در لیست کشویی ظاهر می شود.

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

نحوه ایجاد یک لیست کشویی متشکل از چندین سلول به طور همزمان (به عنوان مثال، به طوری که نام هزینه داشته باشد)

ممنون، همه خوب کار کردند.

یک لیست کشویی با مقادیر از یک برگه دیگر کار نمی کند، زیرا پنجره ای که تأیید داده ها باز است اجازه کار با پنجره های دیگر، به خصوص با برگه دیگر را نمی دهد!

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

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

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

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

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

• به "برگه" بروید داده ها” => بخش ” کار با داده ها” در نوار ابزار => مورد را انتخاب کنید ” بررسی داده ها“.

• در پنجره پاپ آپ " اعتبارسنجی مقادیر وارد شده" در برگه " گزینه ها” در نوع داده ” را انتخاب کنید فهرست کنید“:

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

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

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

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

به عنوان مثال، تصور کنید که می خواهیم دو کلمه "بله" و "خیر" را در یک منوی کشویی نمایش دهیم. برای این ما نیاز داریم:

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

• در پنجره پاپ آپ " اعتبارسنجی مقادیر وارد شده" در برگه " گزینه ها” در نوع داده ” را انتخاب کنید فهرست کنید“:

• در زمینه " منبع" مقدار "Yes; نه".
• کلیک " خوب“

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

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

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

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

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

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

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

• در پنجره پاپ آپ " اعتبارسنجی مقادیر وارد شده" در برگه " گزینه ها” در نوع داده ” را انتخاب کنید فهرست کنید“:

• در زمینه " منبعفرمول را وارد کنید: =OFFEST (2$,0,0,5$ استرالیا)
• کلیک " خوب“

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

این فرمول چگونه کار می کند؟

در مثال بالا از فرمول استفاده کردیم = OFFSET (پیوند؛ offset_by_rows; offset_by_columns;;).

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

با استفاده از این فرمول، سیستم به عنوان داده برای لیست کشویی، محدوده ای از سلول ها را که با سلول $A$2 شروع می شود، شامل 5 سلول به شما برمی گرداند.

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

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

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

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

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

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

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

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

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

• در نوار ابزار، روی « قالب بندی به عنوان جدول“:

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

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

• سپس، محدوده داده های جدول را برای لیست کشویی انتخاب کنید و نامی را در حاشیه سمت چپ بالای ستون "A" بگذارید:

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

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

• در پنجره پاپ آپ " اعتبارسنجی مقادیر وارد شده" در برگه " گزینه ها” در نوع داده ” را انتخاب کنید فهرست کنید“:

• در قسمت منبع نشان می دهیم ="نام میز شما". در مورد ما، ما آن را " فهرست کنید“:

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

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

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

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

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

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

• روی سلول با لیست کشویی که می خواهید کپی کنید کلیک چپ کنید.
• CTRL+C;
• سلول ها را در یک محدوده انتخاب کنید A2: A6، جایی که می خواهید لیست کشویی را درج کنید.
• میانبر صفحه کلید را فشار دهید CTRL+V.

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

• روی سلول با لیست کشویی که می خواهید کپی کنید کلیک چپ کنید.
• میانبر صفحه کلید را فشار دهید CTRL+C;
• سلولی را که می خواهید لیست کشویی را در آن درج کنید انتخاب کنید.
• راست کلیک کنید => منوی کشویی را باز کنید و روی " کلیک کنید درج ویژه“;

• در پنجره ای که ظاهر می شود، در " درج کنید” انتخاب مورد ” شرایط برای ارزش ها“:

• کلیک " خوب“

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

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

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

• روی برگه ” کلیک کنید خانه” در نوار ابزار؛
• کلیک " پیدا کنید و برجسته کنید"و انتخاب کنید" گروهی از سلول ها را انتخاب کنید“:

• در کادر محاوره ای، « بررسی داده ها". در این قسمت می توانید موارد را انتخاب کنید هر کس"و" همین ها“. “هر کس” به شما امکان می دهد تمام لیست های کشویی موجود در برگه را انتخاب کنید. پاراگراف " همین ها” لیست های کشویی با محتوای داده های مشابه را در منوی کشویی نشان می دهد. در مورد ما انتخاب می کنیم " هر کس“:

• کلیک " خوب“

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

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

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

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

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

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

• به "برگه" بروید فرمول ها” => در بخش ” کلیک کنید اسامی خاص"به مورد" ایجاد از انتخاب“:

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

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

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

• یک سلول را انتخاب کنید E2(یا هر سلول دیگری که می خواهید یک لیست کشویی وابسته ایجاد کنید)؛
• روی برگه ” کلیک کنید داده ها” => “بررسی داده ها”;
• در پنجره پاپ آپ " اعتبارسنجی مقادیر وارد شده" در برگه " گزینه ها” در نوع داده ” را انتخاب کنید فهرست کنید“:

• در بخش "منبع" پیوند را ارائه دهید: =INDIRECT($D$2)یا =INDIRECT($D$2);

• کلیک " خوب“

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