The first three articles are about naming Excel elements. This article introduces a naming method: naming area. A region is a set of multiple cells. It can be a single row, a single column, or a cell matrix similar to a table, or a nonconsecutive number of cells, but is rarely used. Of course, a cell can also be a region. Naming a cell is a special case of naming a region. The method for creating a cell is the same as creating a named cell. After multiple cells are selected, enter a name in the Name box. Or use examples to explain. For example, if the administrative department of the company collects statistics on the stationery requirements of each department every month, the following table is generally created:
1. Set the drop-down list(Do not read this section if you know how to use the children's shoes in the drop-down list .) As Departments and stationery do not change frequently, it is more convenient to enter them in the drop-down list. First, select the department column:
Go to the main menu> data:
Click "data verification" in the Data Tool: In the pop-up data verification dialog box, select the allowed type as a sequence, enter names of multiple departments in the source, separated by commas: at this time, if you select any cell in the Department column, a drop-down arrow is displayed on the edge. Click the arrow to view the names of the multiple departments you just entered:
At this time, the title of the department column will also show a drop-down list. You can select the title cell, set data verification according to the previous steps, and change the allowed type to "any value. The settings for the stationery column are the same, so we will not repeat them here.
2. Binding AreaThe method for setting the drop-down options above is the most primitive. It is too difficult to enter candidate items one by one, especially when there are many candidates. For data with few such changes, there is usually another record. For example, a stationery list will be created: when setting the stationery column data verification, we first click the "Source" input box, then switch to the stationery list, and select the data in the stationery name column. At this time, the stationery column can be pulled down:
3. Data changesAlthough the previous method solves the problem of no Repeated input of candidate items, if the candidate items change and one is increased or reduced, the candidate items will not change in the drop-down list. For example, a paper clip is added here, but no candidate for stationery is added.
4. Try to bind to the name columnAfter reading the name cells, name columns, and name tables described in the first three articles, the children's shoes will surely think of binding drop-down candidate items to name columns. First, change the table name corresponding to the stationery list to "stationery List". Then, select the stationery column in the file Application List and set the source of data verification to the stationery name column in the stationery list: click OK, at this time, a dialog box is displayed, prompting you that the formula is incorrect:
5. Solve the problem by naming the regionSelect all cells in the stationery name column of the stationery list, and enter "stationery name" in the Name box ":
Set the stationery column in the stationery Application List again:
At this time, the stationery will be added, and the candidate items in the drop-down box will be automatically added.
6. Think moreYou can bind data to the drop-down list through the naming area. The operation is to create a naming table first. Can you create a naming table directly through the naming area? The actual operation can be found, but when the stationery is added, the candidate items are not added at the same time. Therefore, follow the good habit of creating a table first and then creating a named area. Tip-direction key operation Ctrl + direction key: Jump to the first line (CTRL + up), the last line (CTRL + down), and the first column (CTRL + Left) in the continuous data area) the last column (CTRL + right), when an empty cell is encountered, it will serve as the end of the current continuous area. Shift + direction key: select the current cell and its left (SHIFT + left), right (SHIFT + right), above (SHIFT + up), and below (SHIFT + down) cells, it is generally used when multiple options are selected. CTRL + Shift + direction key: Select consecutive regions in batches. In fact, the first two operations are merged, and empty cells are used as the end of consecutive regions. Alt + down: remove the input values from the current column and display them as drop-down options, even if the drop-down box is not set for the current column.
Download the sample file: http://files.cnblogs.com/conexpress/name extension example. Zip
From Weizhi note (wiz)
Attachment List
Excel should be playing like this-4. Naming area: get the drop-down box
