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

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

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

فرآیند ایجاد لیست

برای ایجاد یک منوی پاپ آپ، روی آیتم های منو در مسیر "داده ها" - "تایید اعتبار داده ها" کلیک کنید. یک کادر محاوره ای باز می شود که در آن باید برگه "Parameters" را پیدا کنید و اگر قبلاً باز نشده است روی آن کلیک کنید. تنظیمات زیادی دارد، اما مورد «نوع داده» برای ما مهم است. از بین همه معانی، "فهرست" درست است.

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

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

   

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

   

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

   

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

بر اساس داده های لیست

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

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

1. سلول رزرو شده برای لیست آینده را انتخاب کنید.
2. تب Data را روی نوار پیدا کنید. در آنجا بر روی "Verify data" کلیک می کنیم.
   

   

3. مورد "نوع داده" را پیدا کنید و مقدار را به "فهرست" تغییر دهید.
   

   

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

علاوه بر این، یک تابع برای تولید لیست ها به طور همزمان در بیش از یک سلول وجود دارد. برای رسیدن به این هدف، باید همه آنها را انتخاب کنید و همان مراحلی را که قبلا توضیح داده شد انجام دهید. باز هم باید مطمئن شوید که مراجع مطلق نوشته شده است. اگر نشانی علامت دلار در کنار نام ستون ها و ردیف ها ندارد، باید آنها را با فشار دادن کلید F4 اضافه کنید تا علامت $ در کنار نام ستون ها و ردیف ها قرار گیرد.

با ضبط دستی اطلاعات

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

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

1. روی سلول لیست کلیک کنید.
2. "داده ها" را باز کنید و در آنجا بخش "بررسی داده ها" را پیدا کنید که برای ما آشناست.
   

   

3. دوباره نوع "List" را انتخاب کنید.
   

   

4. در اینجا باید «Yes; خیر” به عنوان منبع. می بینیم که اطلاعات به صورت دستی با استفاده از نقطه ویرگول برای شمارش وارد می شود.

بعد از زدن OK نتیجه زیر را داریم.

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

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

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

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

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

1. سلول مورد نظر را که می خواهید لیست آینده را در آن قرار دهید انتخاب کنید.
2. تب "داده" و پنجره "تایید اعتبار داده" را به ترتیب باز کنید.
   

   

3. لیست مجموعه". این کار مانند نمونه های قبلی انجام می شود. در نهایت از فرمول زیر استفاده می شود: =OFFSET(A$2$;0;0;5). آن را در جایی وارد می کنیم که سلول هایی که به عنوان آرگومان استفاده می شوند مشخص شده اند.

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

نحو برای این است:

=OFFSET(مرجع،خط_offset،ستون_تغییر،[ارتفاع]،[عرض])

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

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

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

برای ایجاد یک لیست پویا با پشتیبانی از وارد کردن اطلاعات جدید، باید:

1. سلول مورد نظر را انتخاب کنید.
2. برگه "داده ها" را باز کنید و روی "Data Validation" کلیک کنید.
3. در پنجره‌ای که باز می‌شود، دوباره مورد "List" را انتخاب کنید و فرمول زیر را به عنوان منبع داده مشخص کنید: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. روی OK کلیک کنید.

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

برای اینکه فرمول به طور عادی کار کند، باید ردیابی کرد که آیا سلول های خالی در مسیر فرمول وجود دارد یا خیر. آنها نباید باشند.

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

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

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

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

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

ایجاد کرکره های وابسته

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

شما باید این مراحل را دنبال کنید تا لیست هایی ایجاد کنید که نتیجه آنها تحت تأثیر گزینه انتخاب شده در لیست دیگری قرار می گیرد.

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

   

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

   

3. بسته به نوع گیاهی که شخص انتخاب کرده است، فهرست دوم ایجاد کنید. متناوباً، اگر در فهرست اول درختان را مشخص کنید، اطلاعات در فهرست دوم «بلوط، ممرز، شاه بلوط» و فراتر از آن خواهد بود. لازم است فرمول را در محل ورودی منبع داده یادداشت کنید =INDIRECT(E3). E3 – سلول حاوی نام محدوده 1.=INDIRECT(E3). E3 - سلول با نام لیست 1.

حالا همه چیز آماده است.

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

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

کاربرگ فرعی خصوصی_تغییر (ByVal Target As Range)

    خطا در رزومه کاری بعدی

    If Not Intersect(Target, Range(«Е2:Е9»)) هیچ چیز نیست و Target.Cells.Count = 1 سپس

        Application.EnableEvents = False

        اگر Len (Target.Offset (0، 1)) = 0 سپس

            Target.Offset (0، 1) = Target

        دیگر

            Target.End (xlToRight) .Offset (0, 1) = Target

        اگر پایان

        Target.ClearContents

        Application.EnableEvents = True

    اگر پایان

پایان دادن به زیر 

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

کاربرگ فرعی خصوصی_تغییر (ByVal Target As Range)

    خطا در رزومه کاری بعدی

    If Not Intersect(Target, Range(«Н2:К2»)) هیچ چیز نیست و Target.Cells.Count = 1 سپس

        Application.EnableEvents = False

        اگر Len (Target.Offset (1، 0)) = 0 سپس

            Target.Offset (1، 0) = Target

        دیگر

            Target.End (xlDown) .Offset (1, 0) = Target

        اگر پایان

        Target.ClearContents

        Application.EnableEvents = True

    اگر پایان

پایان دادن به زیر

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

کاربرگ فرعی خصوصی_تغییر (ByVal Target As Range)

    خطا در رزومه کاری بعدی

    If Not Intersect(Target, Range(«C2:C5»)) هیچ چیز نیست و Target.Cells.Count = 1 سپس

        Application.EnableEvents = False

        newVal = هدف

        برنامه. لغو

        اولدوال = هدف

        اگر Len (oldval) <> 0 و oldval <> newVal سپس

            Target = Target & «,» & newVal

        دیگر

            هدف = newVal

        اگر پایان

        اگر Len (newVal) = 0 سپس Target.ClearContents

        Application.EnableEvents = True

    اگر پایان

پایان دادن به زیر

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

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

در این مورد، ابتدا باید از نوع دیگری از لیست استفاده کنید. برگه "Developer" باز می شود، پس از آن باید روی عنصر "Insert" - "ActiveX" کلیک یا ضربه بزنید (اگر صفحه لمسی است). دارای جعبه ترکیبی است. از شما خواسته می شود که این لیست را ترسیم کنید و پس از آن به سند اضافه می شود.

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

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

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

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

   

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

   

   

در مرحله بعد، باید این محدوده را با فشار دادن دکمه "OK" تأیید کنید.

جدول به دست آمده را انتخاب می کنیم و از طریق فیلد ورودی که در بالای ستون A قرار دارد نامی برای آن می گذاریم.

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

1. سلولی که لیست در آن قرار دارد را انتخاب کنید.
2. گفتگوی Data Validation را باز کنید.
   

   

3. نوع داده را روی "List" قرار می دهیم و به عنوان مقادیر نام جدول را از طریق علامت = می دهیم.
   

   

   

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

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

چگونه لیست کشویی را کپی کنیم؟

برای کپی کافی است از کلیدهای ترکیبی Ctrl + C و Ctrl + V استفاده کنید. بنابراین لیست کشویی به همراه قالب بندی کپی می شود. برای حذف قالب بندی، باید از یک چسب مخصوص استفاده کنید (در منوی زمینه، این گزینه پس از کپی کردن لیست ظاهر می شود)، که در آن گزینه "شرایط در مقادیر" تنظیم شده است.

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

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

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