لیست های کشویی در اکسل^(Excel) ابزار قدرتمندی هستند. آنها به شما اجازه می دهند یک فلش کشویی به کاربران ارائه دهید که در صورت انتخاب، لیستی از انتخاب ها را در اختیار آنها قرار می دهد.

این می تواند خطاهای ورود داده را کاهش دهد زیرا از تایپ مستقیم پاسخ توسط کاربران جلوگیری می کند. اکسل^(Excel) حتی به شما امکان می دهد موارد مربوط به آن لیست های کشویی را از طیف وسیعی از سلول ها بکشید.

با این حال، به همین جا ختم نمی شود. با استفاده از برخی روش‌های خلاقانه برای پیکربندی اعتبارسنجی داده‌ها برای سلول‌های کشویی، حتی می‌توانید چندین فهرست کشویی پیوندی ایجاد کنید، که در آن موارد موجود در لیست دوم^{(available in a second list)} به بخشی که کاربر در فهرست اول ساخته است بستگی دارد.

لیست های کشویی مرتبط^(Linked) چندگانه برای چه چیزی خوب^(Good) هستند ؟

در نظر بگیرید که اکثر فرم‌های آنلاین، لیست‌های کشویی ثانویه را بر اساس آنچه در لیست کشویی قبل از آن پاسخ می‌دهید، پر می‌کنند. این بدان معنی است که می توانید برگه های ورودی داده اکسل خود را به اندازه فرم های آنلاین پیشرفته کنید. ^(Excel)بر اساس پاسخ های کاربر خود را تغییر می دهد. 

به عنوان مثال، فرض کنید از یک صفحه گسترده اکسل^(Excel) برای جمع آوری اطلاعات رایانه از کاربرانی که نیاز به تعمیر رایانه^{(computer repairs)} دارند استفاده می کنید. 

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

• بخش کامپیوتر^{(Computer Part)} : مانیتور، ماوس^(Mouse) ، صفحه کلید^(Keyboard) ، سیستم پایه^{(Base System)}
• نوع قطعه:
  • مانیتور^(Monitor) : شیشه، محفظه^(Housing) ، سیم برق^{(Power Cord)} ، الکترونیک داخلی^{(Internal Electronics)}
  • ماوس^(Mouse) : چرخ، چراغ LED^{(LED Light)} ، سیم^(Cord) ، دکمه ها^(Buttons) ، پوشش
  • صفحه کلید^(Keyboard) : کلید، محفظه^(Housing) ، غشاء^(Membrane) ، سیم^(Cord) ، الکترونیک داخلی^{(Internal Electronics)}
  • سیستم پایه^{(Base System)} : بدنه، دکمه ها^(Buttons) ، پورت^(Ports) ها ، برق^(Power) ، الکترونیک داخلی^{(Internal Electronics)} ، سیستم عامل^{(Operating System)}

همانطور که از این درخت می بینید، اطلاعاتی که باید برای انتخاب «نوع قسمت» در دسترس باشد، بستگی به این دارد که کاربر کدام قسمت رایانه^{(Computer Part)} را در اولین لیست کشویی انتخاب کند.

در این مثال، صفحه گسترده شما ممکن است ظاهری شبیه به این داشته باشد:

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

بیایید نگاهی بیندازیم که چگونه می توانید این را تنظیم کنید. همچنین، می توانید نمونه برگه اکسل ما را با مثال زیر دانلود کنید.

برگه منبع فهرست کشویی^{(List Source Sheet)} خود را ایجاد کنید

تمیزترین راه برای تنظیم چیزی شبیه به این، ایجاد یک برگه جدید در اکسل^(Excel) است که در آن می توانید تمام موارد لیست کشویی خود را پیکربندی کنید.

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

در مرحله بعد، می‌خواهید هر محدوده را انتخاب کرده و نام‌گذاری کنید تا زمانی که بعداً اعتبارسنجی داده^{(Data Validation)} را تنظیم می‌کنید، بتوانید مورد صحیح را انتخاب کنید.

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

به عنوان مثال، سلول های A2 تا A5 را انتخاب کنید و نام آن محدوده را "مانیتور" بگذارید.

این فرآیند را تا زمانی که همه محدوده ها را به درستی نامگذاری کنید تکرار کنید. 

یک راه جایگزین برای انجام این کار استفاده از ویژگی Create from Selection در اکسل است . این به شما امکان می‌دهد همه محدوده‌ها را مانند فرآیند دستی بالا، اما با یک کلیک نام‌گذاری کنید.

برای انجام این کار، کافی است تمام محدوده‌های موجود در برگه دومی که ایجاد کرده‌اید را انتخاب کنید. سپس Formulas را از منو انتخاب کنید و در نوار گزینه Create from Selection را انتخاب کنید.^{(Create from Selection)}

یک پنجره پاپ آپ ظاهر می شود. مطمئن شوید که فقط ردیف بالا^{(Top row)} انتخاب شده است و سپس OK را انتخاب کنید .

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

اولین لیست کشویی خود را تنظیم کنید

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

1. به صفحه اول برگردید، سلول خالی سمت راست اولین برچسب را انتخاب کنید. سپس Data را از منو انتخاب کنید و Data Validation را در نوار انتخاب کنید.

2. در پنجره Data Validation که باز می شود، ^{(Data Validation)}لیست^(List) را در زیر Allow و در قسمت Source ، نماد فلش بالا را انتخاب کنید. این به شما امکان می دهد محدوده سلول هایی را که می خواهید به عنوان منبع این لیست کشویی استفاده کنید انتخاب کنید.

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

4. فلش رو به پایین را در پنجره انتخاب انتخاب کنید تا پنجره Data Validation باز^{(Data Validation)} شود. محدوده ای را که اکنون انتخاب کرده اید در قسمت Source نمایش داده می شود . برای پایان، OK را انتخاب کنید .

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

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

اولین لیست کشویی خود را تنظیم کنید

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

فرآیند بالا را تکرار کنید تا پنجره Data Validation باز شود. ^{(Data Validation)}لیست^(List) را در منوی کشویی Allow انتخاب کنید . فیلد منبع^(Source) چیزی است که بسته به آنچه در اولین لیست کشویی انتخاب شده است، آیتم های لیست را وارد می کند.

برای این کار فرمول زیر را وارد کنید:

=INDIRECT($B$1)

تابع INDIRECT^(INDIRECT) چگونه کار می کند؟

این تابع یک مرجع اکسل^(Excel) معتبر (در این مورد به یک محدوده)، از یک رشته متنی برمی گرداند. در این مورد، رشته متنی نام محدوده ای است که از سلول اول عبور می کند ($B$1). بنابراین INDIRECT نام محدوده را می گیرد و سپس اعتبار داده های کشویی را با محدوده صحیح مرتبط با آن نام ارائه می دهد.

توجه^(Note) : اگر اعتبار سنجی داده ها را برای این کشویی دوم بدون انتخاب مقداری از کشویی اول پیکربندی کنید، یک پیام خطا خواهید دید. برای نادیده گرفتن خطا و ادامه دادن می توانید Yes را انتخاب کنید.^(Yes)

اکنون، لیست‌های کشویی مرتبط و چندگانه جدید خود را آزمایش کنید. برای انتخاب یکی از قسمت های کامپیوتر از اولین کشویی استفاده کنید. هنگامی که دومین کشویی را انتخاب می کنید، باید آیتم های لیست مناسب برای آن بخش کامپیوتر را ببینید. اینها انواع قطعات در ستون برگه دوم بودند^{(on the second sheet)} که برای آن قسمت پر کردید.

استفاده از چند لیست کشویی پیوندی^{(Multiple Linked)} در اکسل^(Excel)

همانطور که می بینید، این یک راه بسیار جالب برای پویایی بیشتر صفحات گسترده است. با پر کردن لیست‌های کشویی بعدی در پاسخ به آنچه کاربران در سلول‌های دیگر انتخاب می‌کنند^{(select in other cells)} ، می‌توانید صفحه‌گسترده‌های خود را بسیار پاسخگوتر به کاربران کنید و داده‌ها را بسیار مفیدتر کنید.

با نکات بالا بازی کنید و ببینید چه نوع لیست های کشویی پیوندی جالبی را می توانید در صفحات گسترده خود ایجاد کنید. برخی از نکات جالب خود را در بخش نظرات زیر به اشتراک بگذارید .^(Share)

How to Create Multiple Linked Drop Down Lists in Excel

Drop-down lists in Excel are рowerful toolѕ. They let you provide users with a drop-down arrow that, when ѕeleсted, providеs them with a list of choices.

This can reduce data-entry errors because it avoids users from having to type answers directly. Excel even lets you pull the items for those drop-down lists from a range of cells.

However, it doesn’t stop there. Using some creative ways to configure data validation for drop-down cells, you can even create multiple, linked drop-down lists, where the items that are available in a second list depend on the section the user made in the first list.

What Are Multiple Linked Drop-Down Lists Good For?

Consider that most forms online fill out secondary drop-down lists based on what you answer in the drop-down list before it. This means you can make your Excel data-entry sheets just as advanced as online forms. It will modify itself based on the user’s answers. 

For example, let’s say you’re using an Excel spreadsheet to collect computer information from your users who need computer repairs. 

The entry options might look like this:

• Computer Part: Monitor, Mouse, Keyboard, Base System
• Part Type:
  • Monitor: Glass, Housing, Power Cord, Internal Electronics
  • Mouse: Wheel, LED Light, Cord, Buttons, Casing
  • Keyboard: Keys, Housing, Membrane, Cord, Internal Electronics
  • Base System: Casing, Buttons, Ports, Power, Internal Electronics, Operating System

As you can see from this tree, the information that should be available to select for “Part Type” depends on which Computer Part the user selects in the first dropdown list.

In this example, your spreadsheet may start out looking something like this:

If you create multiple, linked drop-down lists, you can use the item selected from the drop-down list in B1 to drive the contents of the drop-down list in B2.

Let’s take a look at how you can set this up. Also, feel free to download our example Excel sheet with the example below.

Create Your Drop-Down List Source Sheet

The cleanest way to set something like this up is to create a new tab in Excel where you can configure all of your drop-down list items.

To set up these linked drop-down lists, create a table where the header at the top are all of the computer parts you want to include in the first dropdown list. Then list all of the items (part types) that should go under that header.

Next, you’ll want to select and name each range so that when you’re setting up Data Validation later, you’ll be able to select the correct one.

To do this, select all of the items under each column and name that selected range the same as the header. To name a table, you simply type the name in the field over the “A” column.

For example, select cells A2 through A5, and name that range “Monitor”.

Repeat this process until you have all of the ranges named appropriately. 

An alternative way to do this is using Excel’s Create from Selection feature. This lets you name all of the ranges like the manual process above, but with a single click.

To do this, just select all of the ranges in the second sheet that you created. Then select Formulas from the menu, and select Create from Selection in the ribbon.

A pop-up window will appear. Make sure only Top row is selected and then select OK.

This will use the header values in the top row to name each of the ranges underneath it. 

Set Up Your First Drop Down List

Now it’s time to set up your multiple, linked drop-down lists. To do this:

1. Back on the first sheet, select the blank cell to the right of the first label. Then select Data from the menu, and select Data Validation in the ribbon.

2. In the Data Validation window that opens, select List under Allow, and under Source, select the up arrow icon. This will let you select the range of cells you want to use as the source for this drop-down list.

3. Select the second sheet where you set up the drop-down list source data, and then select only the header fields. These will be used to fill out the initial drop-down list in the cell you’ve selected.

4. Select the down arrow in the selection window to expand the Data Validation window. You’ll see the range you’ve selected now displayed in the Source field. Select OK to finish.

5. Now, back on the main sheet, you’ll notice that the first drop-down list contains each of the header fields from the second sheet.

Now that your first drop-down list is finished, it’s time to create your next, linked drop-down list.

Set Up Your First Drop Down List

Select the second cell that you want to load list items for depending on what’s selected in the first cell.

Repeat the process above to open the Data Validation window. Select List in the Allow drop-down. The Source field is what will pull in list items depending on what’s selected in the first drop-down list.

To do this, enter the following formula:

=INDIRECT($B$1)

How does the INDIRECT function work?

This function returns a valid Excel reference (in this case to a range), from a text string. In this case, the text string is the name of the range passed by the first cell ($B$1). So INDIRECT takes the name of the range and then provides the drop-down data validation with the correct range associated with that name.

Note: If you configure the data validation for this second drop-down without selecting a value from the first drop-down, you’ll see an error message. You can select Yes to ignore the error and continue.

Now, test your new multiple, linked drop-down lists. Use the first drop-down to select one of the computer parts. When you select the second drop-down, you should see the appropriate list items for that computer part. These were the part types in the column on the second sheet that you filled in for that part.

Using Multiple Linked Drop-Down Lists in Excel

As you can see, this is a very cool way to make your spreadsheets much more dynamic. By filling in subsequent drop-down lists in response to what users select in other cells, you can make your spreadsheets far more responsive to users, and the data far more useful.

Play around with the tips above and see what kind of interesting linked drop-down lists you can create in your spreadsheets. Share some of your own interesting tips in the comments section below.

Related posts

• نحوه درج سریع چند ردیف در اکسل

• نحوه ادغام داده ها در چندین فایل اکسل

• نحوه حذف خطوط خالی در اکسل

• نحوه استفاده از ویژگی Speak Cells اکسل

• نحوه درج کاربرگ اکسل در Word Doc

• هنگام کپی کردن فرمول در اکسل، منابع سلول را حفظ کنید

• از صفحه کلید برای تغییر ارتفاع سطر و عرض ستون در اکسل استفاده کنید

• نحوه ایجاد یک ماکرو یا اسکریپت VBA در اکسل

• نحوه استفاده از دستورات If و Nested If در اکسل

• زمان استفاده از Index-Match به جای VLOOKUP در اکسل

• نحوه استفاده از مراجع مطلق در اکسل

• نحوه درج CSV یا TSV در کاربرگ اکسل

• 4 روش برای استفاده از علامت چک در اکسل

• تفاوت بین Microsoft Excel Online و Excel For Desktop

• راهنمای VBA پیشرفته برای MS Excel

• 4 روش برای تبدیل Excel به Google Sheets

• 5 روش برای تبدیل متن به اعداد در اکسل

• مرتب سازی داده های تک ستونی و چند ستونی در صفحات گسترده اکسل

• سطرها و ستون ها را در یک کاربرگ اکسل گروه بندی کنید

• چرا باید از محدوده های نامگذاری شده در اکسل استفاده کنید؟