创建其他人需要填写的Google表格时,下拉列表可以简化数据输入过程。(Google)
您可以从另一个单元格范围中提取下拉列表的项目,也可以直接输入它们。了解如何同时执行这两项操作,以及如何修改现有的下拉列表。
(Create Manual Dropdown Lists)在Google表格中创建手动下拉列表
创建Google 表格(Google Sheets)下拉列表的最快方法是列出数据验证设置中的项目。
去做这个:
1. 选择要在其中创建下拉列表的单元格。从菜单中选择数据(Data),然后选择数据验证(Data validation)。
2. 在条件旁边,选择项目列表(List of items)。
3. 在此选项旁边的字段中,输入您要包含在下拉列表中的项目,以逗号分隔。
4. 选择保存(Save)按钮,您会看到您现在选择的单元格有一个下拉列表。
数据验证选项
数据(Data)验证窗口中有一些重要的设置需要注意。
如果取消选择在单元格中显示下拉列表(Show dropdown list in cell),则不会出现下拉箭头。但是,当用户开始键入时,将出现列表项。
在On invalid data部分,如果您选择Show warning,将出现红色警告指示器。这将显示一条消息,警告用户键入的项目与列表中的任何内容都不匹配。
如果您改为选择拒绝输入, (Reject input)Google 表格(Google Sheets)将拒绝该条目并将其替换为下拉列表的第一项。
在外观(Appearance)部分,如果您选择显示验证帮助文本(Show validation help text)并在其下方的字段中键入文本,则该文本将在用户选择下拉单元格时出现。
(Create Dropdown)从Google 表格范围(Google Sheets Range)创建下拉列表
创建Google 表格(Google Sheets)下拉列表的一种更动态的方法(dynamic way)是使用一系列单元格(range of cells)的内容来填充列表。
去做这个:
1.首先(First),在任何单元格范围内创建您的验证列表。这些不必在同一个电子表格中。您也可以在其他电子表格选项卡中创建和选择这些列表。
2. 接下来,选择要在其中创建下拉列表的单元格。从菜单中选择数据(Data),然后选择数据验证(Data validation)。
3. 这一次,从Criteria下拉列表中的一个范围中选择 List 。(List from a range)然后,选择小网格选择图标以打开范围选择窗口。
4. 选择要用作列表的范围,您将看到范围文本出现在选择数据范围(Select a data range)字段中。
5. 选择确定(OK)关闭选择窗口并返回验证窗口。配置您想要的其余下拉选项,然后选择“保存(Save)”按钮完成。
6. 现在,您将看到范围数据显示为您选择的单元格中的下拉列表项。
7. 对您想要添加为动态下拉列表的任何其他列继续相同的过程。
使用范围作为数据源是保持电子表格更新的好方法,无需手动检查和更新您创建的每个下拉列表。
关于Google 表格下拉(Google Sheets Dropdown)列表的有趣事实
连接到范围的Google 表格(Google Sheets)下拉列表是最有用的,因为它们大大减少了电子表格的整体维护。
使用一个范围更改更新多个单元格(Update Multiple Cells with One Range Change)
如果您有许多单元格从单个项目范围内绘制数据,则尤其如此。如果您想更新或更改这些列表项,您只需在单个范围内进行更改。
即使有数百个包含这些列表项的单元格,更新范围一次也会立即更新所有这些下拉单元格。
复制已验证的单元格以节省时间(Copying Validated Cells to Save Time)
您还可以通过将经过验证的下拉单元格复制(copying validated dropdown cells)到任何其他单元格来节省时间。这节省了不得不再次逐步通过验证屏幕的过程的时间。
复制经过验证的单元格对于选择列表(如天、月、时间和其他标准数据集)特别有用。
快速删除单元格验证(Quickly Remove Cell Validation)
假设(Suppose)您不希望列表项包含在任何单元格中。您可以通过右键单击单元格,选择验证(Validation),然后在数据验证窗口中选择删除验证来快速删除它们。(Remove validation)
您会看到下拉箭头从单元格中消失,并且所有下拉项都消失了。该单元格将成为另一个普通的电子表格单元格。
在Google表格中使用双下拉列表(Double Dropdown Lists)
使用Google 表格(Google Sheets)下拉列表的一种更有用的方法是在表格之间传递信息(pass information between sheets)。您还可以使用此技术在人与人之间传递信息。
例如,如果您有一个包含一个团队完成的任务列表的原始工作表,您可以将第二个电子表格基于这些已完成的任务。
您可能希望也可能不希望根据上一节中描述的相同动态下拉列表创建第一个列表。
无论哪种方式,选择工作表上要将已完成任务发送到的单元格作为另一个下拉列表,然后如上一节所述打开验证窗口。当您选择数据范围时,切换到此源任务电子表格并选择整个任务列(包括空白单元格)。
现在,在目标电子表格上,您会看到任务列中的数据已被拉入。这意味着您的第二个团队可以根据第一个团队完成的任务执行他们自己的项目任务。
第一个团队可以继续将新完成的任务添加到原始源电子表格中。
由于您在源范围中包含空白单元格,因此这些新的独特任务将出现在第二个团队的下拉列表中。
请记住,只有独特的任务才会出现在第二个下拉列表中。这不是从源传递特定行项目的最佳方式,但它是第二个团队查看其他人添加到电子表格的所有唯一项目的好方法。
您将(Will)如何使用 Google 表格下拉(Dropdown)列表?
如您所见,Google 表格(Google Sheets)提供了多种方式,您可以从其他单元格中提取信息来填写下拉列表。或者,如果您的数据输入需求相当简单,您可以坚持手动输入以逗号分隔的下拉列表项。
无论哪种方式,您都应该能够使任何电子表格的数据输入尽可能简单。
How to Use Google Sheets Drop Down Lists
When creating Google Sheets that other people need to fill out, a dropdown list can simplify the data-entry process.
You can pull the items for your dropdown list from another range of cells, or you can enter them directly. Learn how to do both, as well as how to modify an existing dropdown list.
Create Manual Dropdown Lists in Google Sheets
The fastest way to create a Google Sheets dropdown list is by listing the items inside the data validation settings.
To do this:
1. Select the cell where you want to create a dropdown list. Select Data from the menu, and then select Data validation.
2. Next to Criteria, select List of items.
3. In the field next to this selection, type out the items you want to include in the dropdown list, separated by commas.
4. Select the Save button, and you’ll see that the cell you selected now has a dropdown list.
Data Validation Options
There are a few important settings in the Data validation window to note.
If you deselect Show dropdown list in cell, the dropdown arrow will not appear. However, when the user starts typing, the list items will appear.
In the On invalid data section, if you select Show warning, a red warning indicator will appear. This displays a message warning the user that the typed item doesn’t match anything in the list.
If you select Reject input instead, Google Sheets will reject the entry and replace it with the first item of the dropdown list instead.
In the Appearance section, if you select Show validation help text and type text into the field below it, that text will appear when the user selects the dropdown cell.
Create Dropdown Lists from a Google Sheets Range
A more dynamic way to create Google Sheets dropdown lists is to use the contents of a range of cells to fill the list.
To do this:
1. First, create your validation lists in any range of cells. These don’t have to be in the same spreadsheet. You can create and select these lists in other spreadsheet tabs as well.
2. Next, select the cell where you want to create a dropdown list. Select Data from the menu, and then select Data validation.
3. This time, select List from a range from the Criteria dropdown list. Then, select the small grid selection icon to open the range selection window.
4. Select the range you want to use as your list, and you’ll see the range text appear in the Select a data range field.
5. Select OK to close out the selection window and return to the validation window. Configure the rest of the dropdown options you’d like and then select the Save button to finish.
6. Now, you’ll see the range data appear as the dropdown list items in the cell you selected.
7. Continue this same process for any other columns you’d like to add as a dynamic dropdown list.
Using ranges as the source of your data is a great way to keep your spreadsheets updated without having to manually go through and update every dropdown list you’ve created.
Interesting Facts About Google Sheets Dropdown Lists
Google Sheets dropdown lists connected to ranges are the most useful because they dramatically reduce the overall maintenance of your spreadsheet.
Update Multiple Cells with One Range Change
This is especially true if you have many cells drawing data from a single range of items. If you want to update or change those list items, you’ll only have to make the change in a single range.
Even if there are hundreds of cells with those list items, updating the range once will update all of those dropdown cells instantly.
Copying Validated Cells to Save Time
You can also save time by copying validated dropdown cells to any other cells. This saves the time of having to step through the process of stepping through the validation screens again.
Copying validated cells is especially useful for selection lists like days, months, time, and other standard datasets.
Quickly Remove Cell Validation
Suppose you don’t want the list items included with any cell. You can quickly remove them by right-clicking the cell, selecting Validation, and then selecting Remove validation in the Data validation window.
You’ll see the dropdown arrow disappear from the cell, and all of the dropdown items disappear. The cell will become just another normal spreadsheet cell.
Using Double Dropdown Lists in Google Sheets
One more useful way to use Google Sheets dropdown lists is to pass information between sheets. You can also use this technique to relay information between people.
For example, if you have an original sheet that contains a list of tasks completed by one team, you can base a second spreadsheet on those completed tasks.
You may or may not want to create that first list based on the same dynamic dropdown lists as described in the last section.
Either way, select the cell on the sheet you want to send the completed tasks to as another dropdown list, and open the validation window as described in the last section. When you select the data range, switch to this source task spreadsheet and select the entire column of tasks (including blank cells).
Now, on the destination spreadsheet, you’ll see that the data from the tasks column is pulled in. This means your second team can perform their own project tasks based on completed tasks from the first team.
The first team can continue adding newly completed tasks to the original source spreadsheet.
Since you included the blank cells in the source range, those new unique tasks will appear in the second team’s dropdown list.
Keep in mind that only unique tasks will appear in the second dropdown list. It isn’t the best way to pass specific row items from the source, but it is a great way for a second team to see all unique items added to a spreadsheet by other people.
How Will You Use Google Sheets Dropdown Lists?
As you can see, Google Sheets offers many ways you can pull information from other cells to fill out dropdown lists. Or, if your data entry needs are fairly simple, you can stick to manually entering in dropdown list items separated by commas.
Either way, you should be able to make data entry for any of your spreadsheets as easy and simple as possible.