excel下拉列表联动_Excel下拉列表最后打开

最新推荐文章于 2024-10-12 12:26:23 发布

culiao2169

最新推荐文章于 2024-10-12 12:26:23 发布

阅读量357

点赞数

文章标签： python java excel linux 人工智能

原文链接：https://contexturesblog.com/archives/2012/08/30/excel-drop-down-opens-at-end/

版权

excel下拉列表联动

To make data entry easier, you can create a drop down list of items in a worksheet cell. Then, instead of typing a product name in an order list, you can select a valid product name from the list. Sometimes the Excel drop down opens at end of the list, instead of the top. Here's how to fix that problem.

为了简化数据输入，您可以在工作表单元格中创建项目的下拉列表。然后，您可以从列表中选择一个有效的产品名称，而不是在订单列表中键入产品名称。有时，Excel下拉列表在列表的末尾而不是顶部打开。这是解决此问题的方法。

Excel下拉列表最后打开 (Excel Drop Down Opens At End)

Here's a screen shot that shows the problem. Cell D4 has a drop down list, created with Excel Data Validation.

这是显示问题的屏幕截图。单元格D4有一个下拉列表，由Excel Data Validation创建。

When you click the arrow to open the drop down list, the selection goes to a blank at the bottom of the list, instead of the first item in the list.

当您单击箭头以打开下拉列表时，所选内容将变为列表底部的空白，而不是列表中的第一项。

That can be annoying if you have to scroll all the way back up to the top of a long list, where you have the put the most popular items.

如果您必须一直滚动回到长列表的顶部，那么您会在其中放置最受欢迎的商品，这可能会很烦人。

为什么会发生 (Why It Happens)

Why does this happen, and how can you prevent it?

为什么会发生这种情况，如何预防呢？

In this example, the drop down list is based on a range named Products. The person who set up the list left a few blank cells at the end, where new items could be added.

在此示例中，下拉列表基于名为Products的范围。设置列表的人在最后留下了一些空白单元格，可以在其中添加新项目。

This might seem like a good idea when you're setting up a list, but it causes problems with the data validation drop down lists.

设置列表时，这似乎是个好主意，但会导致数据验证下拉列表出现问题。

匹配单元格内容 (Match the Cell Contents)

When a data validation drop down list opens, it tries to match whatever is currently in the cell.

当数据验证下拉列表打开时，它将尝试匹配单元格中当前存在的任何内容。

In cell D3, Pencils was previously selected, and the list opens to Pencils.
在单元格D3中，先前已选择“铅笔”，并且该列表将打开到“铅笔”。

Cell D4 is blank, so the list finds the first matching entry in the Products list -- a blank row at the end of the list.
单元格D4为空白，因此列表在“产品”列表中找到第一个匹配的条目-列表末尾的空白行。

列表中存在空白的另一个问题 (Another Problem with Blanks in List)

So that's the first problem caused by blank cells in the data validation source list -- if there's a blank cell in the source list, and the cell with the data validation list is blank, the list will open with the matching blank entry selected.

所以这是由数据验证源列表中的空白单元格引起的第一个问题-如果源列表中有一个空白单元格，而数据验证列表中的单元格为空白，则该列表将打开，并选择匹配的空白条目。

There is another problem too, and it is not just an annoyance. If you have any blank cells in the list, people might be able to enter invalid items in the data validation cells. That could cause serious problems in your workbook, if you're depending on valid entries in those cells.

同样，还有另一个问题，这不仅仅是烦恼。如果列表中有任何空白单元格，人们可能可以在数据验证单元格中输入无效的项目。如果您依赖那些单元格中的有效条目，则可能会在工作簿中引起严重的问题。

预防问题 (Prevent the Problems)

To prevent these problems, you can remove the blanks from the source list, by using a dynamic range, which will adjust automatically when items are added or removed.

为避免这些问题，您可以使用动态范围从源列表中删除空格，该范围将在添加或删除项目时自动调整。

A dynamic range can be created with a formula, like the OFFSET formula shown below, or by using a named Excel table.

可以使用公式(如下所示的OFFSET公式)或使用命名的Excel表来创建动态范围。

In this example, the OFFSET formula is:

在此示例中，OFFSET公式为：

=OFFSET(Prices!$B$2,0,0,COUNTA(Prices!$B:$B)-1,1)

= OFFSET(价格！$ B $ 2,0,0，COUNTA(价格！$ B：$ B)-1,1)

It counts the items in column B of the Prices sheet, where the Products are listed. The heading, in cell B1, is not included in the list.

它计算“价格”表中列有产品的B列中的项目。单元格B1中的标题未包括在列表中。

Once the dynamic range is created, the blanks are not included in the Products list, and the drop down works correctly.

创建动态范围后，空白将不包含在“产品”列表中，并且下拉列表可以正常工作。

下载样本文件 (Download the Sample File)

To download the sample file with a dynamic range, please visit the Data Validation Tips & Quirks page, at this link: Drop Down List Opens with Blank Selected

要下载具有动态范围的样本文件，请访问“数据验证技巧和古怪”页面，该链接位于：下拉列表打开且选中空白