excel导入数据校验_从Excel数据验证列表中选择多个项目

excel导入数据校验

You’ve probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.

您可能已经使用了Excel数据验证下拉列表 ,您可以在其中单击箭头,然后选择一个项目来填充单元格。 通过一些编程,您可以自定义下拉列表,以选择多个项目。

单选下拉 (Single Selection Drop Down)

By default, data validation drop downs only allow you to select one item.

默认情况下,数据验证下拉列表仅允许您选择一项。

In the worksheet shown below, you can select a month from the drop down, and it overwrites any previous selection in that cell.

在下面显示的工作表中,您可以从下拉菜单中选择一个月,它会覆盖该单元格中以前的所有选择。

《excel导入数据校验_从Excel数据验证列表中选择多个项目》

从下拉列表中选择多个项目 (Select Multiple Items From the Drop Down List)

Instead of limiting users to a single selection, you can use a bit of programming, combined with the data validation list, and allow multiple selections.

您可以使用一些编程,再结合数据验证列表,而不是将用户限制为单个选择,并允许多个选择。

You can display all the selected items across a row, or down a column, or keep them in the same cell. Keep reading, to see some examples of this technique, and how it works.

您可以在一行或一列中显示所有选定的项目,或将它们保留在同一单元格中。 继续阅读,以查看该技术的一些示例以及其工作方式。

There is also a video that shows the multiple selection drop down list. To try it for yourself, follow the download link for the sample file, near the end of this article.

还有一个视频,显示多选下拉列表。 若要自己尝试,请遵循本文末尾的示例文件的下载链接。

有关选择多个项目的更多文章 (More Articles on Select Multiple Items)

[Update]: Since posting this article on selecting multiple items from an Excel drop down list, I’ve added more articles on this topic. Here are a few links to updates:

[更新]:自从发布有关从Excel下拉列表中选择多个项目的文章以来,我已经添加了有关此主题的更多文章。 以下是一些更新链接:

You can find the full list on the main page for this technique:

您可以在主页上找到此技术的完整列表:

多种选择选项 (Multiple Selection Options)

In the sample file, there are different examples of the code, with options for where the selected items are placed. You might want all the items in a single cell, or in adjacent rows or columns.

在示例文件中,有不同的代码示例,以及用于放置所选项目的选项。 您可能希望所有项目都在单个单元格中,或者在相邻的行或列中。

跨行填充 (Fill Across a Row)

For example, each item selected in this drop down list fills the next empty cell at the right. You could use this technique to fill player names across a list of innings, or employee names for days of the week.

例如,在此下拉列表中选择的每个项目将填充右侧的下一个空白单元格。 您可以使用此技术在局列表中填充玩家名称,或在一周中的某天填充员工名称。

《excel导入数据校验_从Excel数据验证列表中选择多个项目》

填写一栏 (Fill Down a Column)

In some worksheets, you might prefer to fill down, instead of across.

在某些工作表中,您可能更愿意填写而不是全部填写。

《excel导入数据校验_从Excel数据验证列表中选择多个项目》

将值添加到同一单元格 (Add Values To the Same Cell)

Instead of filling other cells, you can keep all the selections in the same cell, separated by commas.

不必填充其他单元格,而是可以将所有选择保留在同一单元格中,并用逗号分隔。

《excel导入数据校验_从Excel数据验证列表中选择多个项目》

使用编程来添加多个值 (Use Programming to Add Multiple Values)

The code to allow multiple selections runs automatically when you make a change on the worksheet. To see the code for the SameCell sheet, in the sample file, you can right-click the sheet tab, and click on View Code.

当您在工作表上进行更改时,允许多项选择的代码将自动运行。 要查看SameCell工作表的代码,请在示例文件中右键单击工作表选项卡,然后单击“查看代码”。

《excel导入数据校验_从Excel数据验证列表中选择多个项目》

In the code shown below, column 3 is the only one where the change will occur. In your workbook, you could change the 3 to a different number.

在下面显示的代码中,第3列是唯一会发生更改的地方。 在工作簿中,您可以将3更改为其他数字。

Or, if you don’t want to limit the code to a specific column, you could delete the two rows that are marked with a red circle.

或者,如果您不想将代码限制为特定的列,则可以删除标有红色圆圈的两行。

《excel导入数据校验_从Excel数据验证列表中选择多个项目》

下载样本数据验证文件 (Download the Sample Data Validation File)

To experiment with this technique, you can download the zipped sample file from the Contextures website, on the Excel Data Validation – Select Multiple Items page.

若要试验此技术,可以从Contextures网站的“ Excel数据验证-选择多个项目”页上下载压缩的示例文件。

NOTE: In some of the code, you might have to change the range names or the sheet names, if you copy the code to a different workbook. Some code examples refer to a specific row or column number, and you might also need to adjust those.

注意:在某些代码中,如果您将代码复制到其他工作簿中,则可能必须更改范围名称或工作表名称。 一些代码示例引用了特定的行号或列号,您可能还需要对其进行调整。

In the sample file, a macro runs automatically when the workbook opens, to protect the “SameCell” worksheet. If you do not need that code, you can remove it from the ThisWorkbook module.

在示例文件中,当工作簿打开时,宏会自动运行,以保护“ SameCell”工作表。 如果不需要该代码,可以将其从ThisWorkbook模块中删除。

If you remove or rename the SameCell sheet, without deleting the Workbook_Open code, you will see an error message when the file opens.

如果删除或重命名SameCell工作表,而不删除Workbook_Open代码,则在打开文件时会看到错误消息。

购买高级版 (Buy the Premium Version)

There is a premium version of this technique, and you can see the details here: Data Validation Multi Select Premium. Instead of selecting from the drop down, a list box appears when you click the cell. It automatically selects any items that are already in the cell. It also has buttons to Clear all the selections, and select all the items.

该技术有一个高级版本,您可以在此处查看详细信息: 数据验证多选高级 。 单击该单元格时,会出现一个列表框,而不是从下拉列表中进行选择。 它会自动选择单元格中已经存在的所有项目。 它还具有清除所有选择并选择所有项目的按钮。

The premium version works with dependent lists too, and runs on a protected worksheet. There is also an option of showing a multi-select listbox, or a single-select version (this is helpful when working with dependent lists — you don’t want multiple items selected in the main columns).

高级版本也可用于从属列表,并在受保护的工作表上运行。 还有一个显示多选列表框或单选版本的选项(这在处理从属列表时很有用-您不希望在主列中选择多个项目)。

The kit has 3 sample files, and a user guide, with details and screen shots, on how to add this technique to your own workbooks. Click here for details: Data Validation Multi Select Premium.

该工具包包含3个示例文件和一个用户指南,其中包含有关如何将此技术添加到您自己的工作簿中的详细信息和屏幕截图。 单击此处了解详细信息: 数据验证多选Premium

观看数据验证视频 (Watch the Data Validation Video)

To see these techniques, and a few other multiple selection examples, you can watch this short video.

要查看这些技术以及其他一些选择示例,您可以观看此简短视频。

演示地址

翻译自: https://contexturesblog.com/archives/2009/09/18/select-multiple-items-from-excel-data-validation-list/

excel导入数据校验

    原文作者:culiao2169
    原文地址: https://blog.csdn.net/culiao2169/article/details/107989642
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞