excel下拉列表联动_比较了3种类型的Excel下拉列表

excel下拉列表联动

At a client's office last week, I was selecting a pricing option from a data validation drop down list.  It had some limitations, so here are 3 types of Excel drop down lists compared.

上周在客户办公室，我从数据验证下拉列表中选择一种定价选项。 它有一些限制，因此这里比较了3种类型的Excel下拉列表。

数据验证下拉 (Data Validation Drop Down)

At my client's office, the worksheet was zoomed to 75%, so we could see more of the data, and the person watching over my shoulder asked, "How can you even read that?"

在客户办公室，工作表被放大到75％，因此我们可以看到更多数据，而看着我肩膀的人问道：“您什至怎么读？”

Good question. Sometimes the font in a data validation drop down is so small that you can barely read the list.

好问题。 有时，数据验证下拉列表中的字体是如此之小，以至于您几乎无法阅读列表。

下拉类型 (Drop Down Types)

Unfortunately, there's no way to make the font size bigger, which is one of the drawbacks of a data validation drop down list.

不幸的是，没有办法增大字体大小，这是数据验证下拉列表的缺点之一。

There are other ways to create a drop down list though, and one of those options might work better in your worksheet.

但是，还有其他创建下拉列表的方法，其中一种方法在您的工作表中可能会更好。

We'll compare these types of Excel drop down lists:

我们将比较以下类型的Excel下拉列表：

• Data Validation List
  数据验证清单
• Form Control Combo Box
  表单控制组合框
• ActiveX Control Combo Box
  ActiveX控件组合框

数据验证限制 (Data Validation Limitations)

Data validation is a great feature, and you can use creative formulas to create flexible lists, such as dependent drop down lists. However, it has limitations:

数据验证是一项很棒的功能，您可以使用广告素材公式来创建灵活的列表，例如相关的下拉列表 。 但是，它有局限性：

• the font size can't be changed
  字体大小无法更改
• only 8 rows are visible at a time
  一次仅可见8行
• only the active cell shows a drop down arrow.
  只有活动单元格显示一个下拉箭头。

You can't change those settings, but you can colour the data entry cells, to make them obvious to the worksheet users.

您不能更改这些设置，但是可以为数据输入单元着色，以使它们对工作表用户显而易见。

表单控制组合框 (Form Control Combo Box)

Instead of using data validation, you could use a Form Control Combo Box. It gives you a bit more control over the appearance of the drop down list.

除了使用数据验证，您还可以使用表单控件组合框。 它使您可以更好地控制下拉列表的外观。

You can adjust the number of drop down lines in the Form Control Combo box, so you can show all 12 months, without a scroll bar.

您可以在“表单控制组合”框中调整下拉线的数量，这样就可以显示全部12个月的内容，而无需滚动条。

You can also link the combo box to a cell, so the selected item number appears on the worksheet.

您还可以将组合框链接到一个单元格，以便所选的项目号出现在工作表上。

The drop down arrow is always visible, so the Form Control combo box is easy for users to find on the worksheet.

下拉箭头始终可见，因此用户可以轻松地在工作表上找到“表单控件”组合框。

The font size can't be changed though, so the list would be hard to read on a zoomed worksheet.

但是无法更改字体大小，因此在缩放的工作表上将很难阅读该列表。

Also, you can't type in the box, so click the arrow, then select an item from the list. That item's index number (its position in the list) is entered in the linked cell. In the screen shot above, February was selected, and a 2 is entered in cell D5.

另外，您无法在框中输入内容，因此请单击箭头，然后从列表中选择一个项目。 该项目的索引号(在列表中的位置)输入到链接的单元格中。 在上面的屏幕截图中，选择了2月，并在单元格D5中输入了2。

获取商品名称 (Get the Item Name)

To show the item name, instead of the item number, you can use an INDEX function in another cell.

要显示项目名称而不是项目编号，可以在另一个单元格中使用INDEX函数 。

In this example, the INDEX function returns a month name from the MonthList named range, from the row number (2) for the selected month (February).

在此示例中，INDEX函数从MonthList命名范围返回月份名称，该名称来自所选月份(二月)的行号(2)。

ActiveX组合框 (ActiveX Combo Box)

Another drop down list option is to use an ActiveX combo box. This is similar to the Forms Control combo box, but has more properties that you can adjust.

另一个下拉列表选项是使用ActiveX组合框。 这类似于“表单控件”组合框，但具有更多可以调整的属性。

After you insert a combo box, right-click the combo box, and click Properties.

插入组合框后，右键单击组合框，然后单击“属性”。

You can set the properties, such as font, font size, number of rows, and even include multiple columns.

您可以设置属性，例如字体，字体大小，行数，甚至包括多列。

If you know that a worksheet will be zoomed most of the time, you can use a larger font, to make the list easier to read.

如果您知道工作表在大多数情况下都会缩放，则可以使用较大的字体，以使列表更易于阅读。

This combo box can also be linked to a worksheet cell, and the value is entered in that cell, when you select an item from the list.

该组合框也可以链接到工作表单元格，当您从列表中选择一个项目时，将在该单元格中输入值。

Also, you can type in the combo box, and the text autocompletes as you type, finding the first matching item in the list.

另外，您可以在组合框中键入内容，并且键入时文本自动完成，从而找到列表中的第一个匹配项。

合并下拉列表 (Combine the Drop Down Lists)

To make the most of the data validation features, while allowing font changes, autocomplete and more rows, I sometimes combine data validation with a combo box.

为了充分利用数据验证功能，同时允许更改字体，自动完成和更多行，我有时将数据验证与组合框结合在一起。

There's only one combo box, hidden at the top of the worksheet, and bit of programming makes the combo box appear when needed.

只有一个组合框隐藏在工作表的顶部，并且一些编程使组合框在需要时显示。

When a user double-clicks on a data validation cell, the combo box appears, and displays the data validation list for the selected cell. If users don't want to use the combo box, they can simply click on the arrow in the cell, and use the data validation list.

当用户双击数据验证单元格时，将出现组合框，并显示所选单元格的数据验证列表。 如果用户不想使用组合框，则只需单击单元格中的箭头，然后使用数据验证列表。

观看下拉列表视频 (Watch the Drop Down Lists Video)

To see a demonstration of the 3 types of drop down lists, you can watch this Drop Down Lists video.

要观看3种类型的下拉列表的演示，您可以观看此下拉列表视频。

演示地址

翻译自: https://contexturesblog.com/archives/2010/02/03/3-types-of-excel-drop-down-lists-compared/

excel下拉列表联动