Selecting a value in an excel cell from a list. Related dropdown lists. Video - Creating Dropdown Lists in Excel
Select to set the drop-down list. In the menu, open the items “Data” - “Check”. Then go to the “Parameters” tab in the new window and in the “Data type” field that opens, set the line “List”. At the same time, the “Source” field will appear in the same window. Enter the “=” symbol and the name of the selected range that was assigned to the data cells. To apply the parameters, press “Enter” or “Ok”. This is a variant of the simplest drop-down list.
At the same time, the “Source” field will appear in the same window. Enter the “=” symbol and the name of the selected range that was assigned to the data cells. To apply the set parameters, press “Enter” or “Ok”. This is a variant of the simplest drop-down list.
Excel has the ability to create a drop-down list with a more complex design. To do this, you use a control called a combo box that is inserted into an Excel worksheet. To install it, open the “View” menu item, then “Toolbars” and the “Forms” sub-item.
Select the “combo box” icon in the control panel that opens - this is the drop-down list. Draw a rectangle in the shape of a field with your mouse. Right-click the drawn list and select the “Format Object...” command.
In the dialog box that appears, in the “Form list by range” field, specify the desired range of cells. To do this, use your mouse to select the cells that should be included in this Excel drop-down list. In the “Link to cell” field, set the cell number to display the serial number of the element selected in the list. Specify the required number of lines in the list to be created. The “Ok” button will apply all the specified parameters, and the list is ready for use.
Section 3: Working with Table Groups
The concept of a list in Excel
A list is an Excel table that consists of one or more columns. The list columns are assigned unique field names, which are entered in the first row of the list. All cells in a column have the same data format, so all lines, or records as they are also called, are of the same type.
| Surname | Age | Floor |
| Petukhova | ||
| Petrov | ||
| Zaitseva | ||
| Morev | ||
| Ivanov | ||
| Petrova |
Rice. 1. Excel list example
In the above Excel table, the data in the first and third columns is in text format, and the data in the second column is in numeric format. The names of the list fields must be placed in one cell. The list data and other data on the same sheet must be separated by at least one empty cell, that is, they must not touch each other. Excel list is a typical database and a large number of specific operations are applicable to it. Most of the tables that Excel users work with are lists or can be converted to list form. So, if the table shown in Fig. 2 was created for each group of students, then they could be combined into one table, which would also be a list, by adding another “Group” field.
| Group | Surname | Age | Floor | Height | Weight |
| 99-l-3 | Petukhova | ||||
| 99-l-3 | Petrov | ||||
| 99-l-3 | Zaitseva | ||||
| 97-l-1 | Popov | ||||
| 97-l-1 | Kozlov |
Fig. 2. Combining tables into a list
Working with lists in Excel
Most operations designed to work with lists are concentrated in the “Data” menu. If the list is created correctly, then just select one of the cells inside the list and click the desired command in the “Data” menu. Excel will automatically determine the boundaries of your list.
Rice. 2. Expanded “Data” menu
The “Sorting” item allows you to sort by selected criterion by one or, in order of priority, by two or even three list fields.
Fig. 3. Two-stage sorting of a list
“Filter” makes it possible to show only those entries in the list that meet some criterion. Thus, setting a custom autofilter, shown in Fig. 3, will display in the list only surnames ending with the letter “v”.
Fig3. Using a custom autofilter
The “Form” item allows you to quickly enter data into the list. The “Results” item allows you to summarize the data under each group in the list. Figure 4 shows the summary dialog box, and Figure 5 shows the result of this operation.
Fig.4. Summarizing
| Group | Surname | Age | Floor | Height | Weight |
| 99-l-3 | Petukhova | ||||
| 99-l-3 | Petrov | ||||
| 99-l-3 | Zaitseva | 19 | |||
| 99-l-3 Total | |||||
| 97-l-1 | Popov | ||||
| 97-l-1 | Kozlov | 19 | |||
| 97-l-1 Total | |||||
| Grand total | |||||
Fig. 5. Result from summing up
“Consolidation” allows you to summarize results for several tables of the same type. The capabilities of the “Consolidation” and “Totals” items are completely covered by the powerful mechanism for building Excel pivot tables.
The “Group and Structure” item is applicable not only to Excel lists and allows you to change the levels of detail in the presentation of information on an Excel sheet. To create simplest structure You can select several columns or rows on a sheet and click “Group”.
Fig.6.Creating a structure
An additional bar with a square icon will appear above the sheet. Clicking on this symbol will hide the columns that have a black line over them. If you select the columns under the black line and select “Group” again, a two-level structure will be created. You can group rows in a similar way.
Fig 7. Result of the grouping operation
Creating a structure makes it possible to view large Excel tables on one screen, hiding unnecessary ones in this moment data.
Excel Pivot Tables
Pivot tables allow you to perform group operations on data located either in lists, across multiple consolidation ranges, or in external databases. When you click on the “Pivot Table” item in the “Data” menu, the first dialog box of the Pivot Table Wizard appears (see Fig. 8).
Fig.8. Pivot Table Wizard - step 1.
To process the list, you need to select the first of the proposed options and go to the second step of the wizard. In the second step, you need to select the list to be processed and proceed to the third step. At the third step of the wizard (see Fig. 9.) a constructor for processing list data will appear.
Fig. 10. Pivot table wizard - step 3.
To construct a group operation on one of the list fields, you need to drag the name of the corresponding field into the “Data” area with the mouse. So, to get the total weight, you need to drag it into the “Data” area. The “Row”, “Column” and “Page” areas allow you to obtain, in addition to the total application of a group operation on all records in the list, partial group operations on records with the same values of the fields selected in the area. So, to get not only the total weight of all people, but also the weight of people in each group, you need to drag it into the “Row” area. In Fig. Figure 11 shows the result of the described dragging.
Fig. 11. Constructing a group operation using the “Weight” field
To remove any field from the design area, you just need to drag it with the mouse and drag it out of the given area.
By clicking on the “Next” button, you can go to the fourth step of the wizard. In the fourth step, you need to select the sheet in which the pivot table will be placed. Here you can choose to create a pivot table on a new sheet and complete the wizard. The resulting summary table is shown in Fig. 12.
Fig. 12. Pivot table obtained as a result of the wizard’s work
Use the button in the PivotTables panel to return to the PivotTable Wizard to edit the batch operation query. Let's change the query by adding the "Last Name" field to the data area. The summary table will take the form shown in Fig. 13.
Fig. 13. Pivot table with group operation for the Last Name field
Let's go back to the PivotTable Wizard and add the "Gender" field to the "Column" area and the "Age" field to the "Page" area. The resulting summary table is shown in Figure 14.
Fig. 14. Resulting pivot table
The “Page” area differs from the “Row” and “Column” areas in that it allows you to either perform a group operation on all field values at once, or on one selected value. In the example given, the value "19" was selected.
Exercise
Convert the table shown below to an Excel list or lists. Based on the summary tables, determine:
1. Area of enterprises subject to reconstruction by region;
2.Number of enterprises subject to reconstruction by type of activity;
3. Total area and number of enterprises by area;
4.All of the above in one summary table.
| List of enterprises subject to reconstruction | |||
| Airport | |||
| Household services | |||
| Organization | Kind of activity | Square |
|
| LLC Success | Dry cleaning | ||
| CJSC Udacha | Salon | ||
| LLC Player | Dry cleaning | ||
| Trade | |||
| Fialka LLC | Flowers | ||
| CJSC Bublik | Bread | ||
| Iris LLC | Flowers | ||
| Falcon | |||
| Household services | |||
| LLC Boot | Shoe repair | ||
| Volos LLC | Salon | ||
| Trade | |||
| LLC Moroz | Appliances | ||
| JSC Kalach | Bread | ||
| LLC Roman | Books | ||
| JSC Herring | Fish |
When working with large tables and databases, it is very convenient to use drop-down lists. In this case, the user can select to enter only specified values from the list. The drop-down list allows you to avoid situations where entering an incorrect value can lead to undesirable results. How to correctly create a drop-down list in Excel 2007? Let's look at it below. First, you need to create a list of the values that will be available for selection in a cell (in our case, this is the range of cells F2:F8). Next, select the cell (or group of cells) in which, in fact, our list will drop out (in our case, this is the range of cells A2:A22). After this, you will see the “Check entered values” window. In the first tab “Parameters”, select “Data type” - “List”, and in the “Source” column indicate the range of the list.
If you wish, and also for greater clarity, you can fill in the remaining two tabs “Input message” and “Error message”. In the “Message for input” tab, you can specify a hint for the user about his further actions, for example, the phrase “Select data from a list.” A tooltip will appear when you select a cell with a drop-down list.
In the “Error Message” tab, you can enter the text that will appear when you try to enter incorrect data into a cell.
After everything is done, click OK. The dropdown list is ready. Now, when you select any of the cells in the range A2:A22, a tooltip and a drop-down list will appear (the arrow to the right of the cell). Like this
If you try to enter incorrect data into one of the cells from the selected range, an error message will appear
For convenience, the list can be placed on another sheet Excel document 2007, but to create a drop-down list you will now have to give it a name. This is done simply. Select the list of data, right-click on it and select “Range name...”. In the “Create a name” window that opens, in the “Name” column, set a name for the list (without spaces) and check that the range is correct (you can immediately select and copy the range, we will need it later) and click OK.
Now we return to the page with the range in which you would like to see a drop-down list and when creating a drop-down list, in the source column, indicate the just copied range of the data list. Hello, friends. When you fill tables with data, the list of which is defined in advance, it would be convenient not to write them manually, but to select them with the mouse from a drop-down list. This approach saves time and minimizes the likelihood of error. Let me give you an example. You are responsible for accounting for office expenses associated with its business activities. Your table looks like this: Now you enter categories and types of expenses manually. This means you may accidentally make a spelling mistake. It seems like it’s not a big deal, but if you later have to estimate expenses using a pivot table, the errors will distort the result and will have to be corrected. Read more about pivot tables. Creating a Dropdown ListLet's build lists of categories and types of expenses in order to make drop-down lists based on them. Let's put them in a separate table. Now let's use the Data Validation tool to make a selection from the list. I described how it works. We follow the algorithm: Now you can select the necessary items from the drop-down list and you will not make mistakes with the spelling. True, there are still some nuances. We do not control the compliance of the selected category and type of expense. For example, you can select the category " Office" and view - " Coffee" This cannot be allowed. Let's improve data validation and make lists that depend on the value of another cell. Creating a dependent list in ExcelTask: when we select a category, the list of types of expenses should contain only those that belong to this category. The solution may seem complicated, but it is not. I will try to explain it as simply as possible. Let's structure our table of types of expenses: Now the types of expenses are divided into columns corresponding to each category. Next we will use the INDIRECT(text) function. What is she doing? It attempts to convert the entered text into a cell reference. What happens if you write this formula: =SUM(INDIRECT("F1:F5")). The INDIRECT function recognizes text "F1:F5" as a range of cells and will return it. And the SUM function will sum all the values in this range. The same thing will happen if we range "F1:F5" Let's give it a name. For example, "structure". Formula =SUM(INDIRECT("structure")) will give a similar result. It is this opportunity that we will take advantage of. To learn more about cell naming, read. I recommend doing this; names are a convenient and practical tool. Let's give names to all columns with the original data. In this case, the range with types should be named exactly the same as its category. For example, the range J4:J8 let's give the name " Office" We call it: Now if you click Formulas - Defined Names - Name Manager- you can see everything given names. If you made a mistake somewhere, or the list has changed, you can make corrections in this window. Once again we set up data verification: If the principle is still not clear, I will describe point by point how our table will now work:
I confirm all of the above with the image below: As you can see, the list of types of expenses is not filled with the entire list, but only with those items that belong to the selected “Food” category. This is exactly what we wanted. I think you have figured out how to make a dependent list in Excel. If you don’t understand, write comments. And I finished the article, productive work to you! Graphs and Charts (5) Working with VB project (12) Conditional Formatting (5) Lists and ranges (5) Macros (VBA procedures) (64) Miscellaneous (41) Excel bugs and glitches (4) Related Dropdown Lists
("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40)) Popular in the category:
© 2024 Internet. Multimedia. Navigator. Education. BY. Firmware. Programs. Adviсe. Installation
| |
