PROCESSING THE DATA IN THE SPREADSHEET
List View of a Spreadsheet
A list or database is one way to organize data on a worksheet. It is created as a labeled series consisting of rows with the same type of data. For example, a list of employees of a certain department, in which the columns have the following names: Full name, Total accrued, Total withheld, Amount to be issued.
Data organized in a list is called database. In this case, the rows of the table are the database records, and the columns are the fields of the records. To turn Excel spreadsheet to the list, you must give the columns names that will be used as the names of the record fields. When creating a database, the following rules must be followed:
1. More than one list should not be placed on one worksheet, because some operations, such as filtering, work on only one list at a time.
2. Separate the list of data from other worksheet data by at least one empty column or one empty row. This will help to automatically highlight the list when filtering or sorting the data.
3. Cell headers in a data list should not be merged.
4. The column names must be in the first line of the list, because Excel always treats the first row as column headings.
5. In order to separate the column headers from the main data, you can use a font, alignment, format different from those that reflect the main data of the table. You can not leave an empty line between the column headings and the main data, it is not recommended to use a dotted line for separation.
Sorting data in a table
Sorting data in Excel has pros and cons. Positive - versatility of operation and convenient user interface. Negative: The ease with which a table can be turned into a messy data set.
To sort, select a table cell and select the command Data, Sorting. Excel will automatically select the entire list. If the first line contains field names, then they will not be included in the sort. Keep in mind that the final row of the source list will also be included in the sort, so sometimes it's better to manually select a range of data to sort.
In the three input fields of the window Sorting you can specify the keys by which the data will be sorted. You can also set options: set the sort order by the first key (regular or user-defined), as well as the sort direction (ascending or descending).
For quick sorting, you can use the Sort Ascending and Sort Descending buttons on the standard toolbar (in this case, the sort key is the column with the current cell).
If sorting does not achieve the expected result, cancel it. To do this, click on the "Cancel" button on the standard toolbar.
Filtering data in a list
A filter is a construct designed to select those table rows that satisfy a given condition and temporarily hide the rest of the rows. The basis of the filter is a list that contains the conditions for selecting rows. Excel allows you to quickly and conveniently view the required data from the list using AutoFilter. More complex database queries can be implemented using the Advanced Filter command.
Autofilter
With the AutoFilter function, you can only display certain entries on the screen. To do this, you must first select a list with mandatory field headers.
Buttons that expand the filter list are created in the first line of the range. If there are empty cells in the column, the list is supplemented with the categories "empty" and "non-empty". In any case, the top line of the range is not considered as an object for filtering and is not included in the list. Even if some of the lines are not included in the selection, the filtering principle will apply to them, including the final line. After the range is selected, select the command Data / Filter / Autofilter.
After this command, Excel places the drop-down lists directly in the names of the list columns. The element of the column that is selected in the drop-down list is called the filter criterion. You can continue filtering using criteria from another column.
To remove a filtering criterion for an individual column, select the All option from the drop-down list. To display all rows hidden in the list, select Data/ Filter/Show all.
The user can create a table row selection condition for each column. Custom Criteria selection records may consist of one or two boolean expressions connected by AND (OR) operators. The use of the AND operator assumes that the condition will be met if both parts of it are met at the same time. The use of the OR operator allows at least one part of the condition to be true.
To set a custom criterion, select the Condition parameter in the drop-down list, and then, in the "Custom AutoFilter" window that appears, set the required criteria: Show only those rows whose values ... - specify the required criteria.
To remove the AutoFilter, select the command again Data/Filter/AutoFilter.
First 10. This method only makes sense for fields with numeric data, including dates. To use this method, you need to select the option "Top 10 ..." in the list. In the dialog box that appears, specify how many largest or smallest items to display. For example, the database has a "Salary" field for storing the salary of an employee. The organization has the opportunity to provide material assistance to 5 employees with the lowest salary. To solve this problem, you can use the "First 10 ..." selection method for the "Salary" field: set the number of displayed records with the lowest value.
Complex filtering (advanced filter)
To filter the list by a complex criterion, as well as to obtain a part of the original list by several specified columns, use the Advanced filter command of the Data menu. Its difference from the AutoFilter command is that, in addition to the listed features, filtered records can be moved to another place in the Excel worksheet without damaging the original list. In an advanced filter, the filter criterion is created as a table that can be stored anywhere in the workbook, or even in another file. All Excel data processing operations are applicable to the criterion table.
Advanced filter allows you to:
1. immediately copy the filtered records to another place in the worksheet.
2. saves the selection criterion for later use (this is useful when new data is added to the list and you need to periodically retrieve information from it in accordance with the criterion).
3. show in filtered records not all columns of the table, but only the specified ones
4. combine the OR operator conditions for different columns
5. For one column, combine more than two conditions with AND, OR operators.
6. create calculated criteria.
To use the Advanced Filter command, you must first create a criteria table, which you want to place on the same worksheet as the original list, but in such a way that it is not hidden during filtering. To form a criteria table, to ensure the identity of the text fields, copy the names of the list fields to the part of the worksheet where the criteria table will be located. The number of rows in this table is determined only by the number of search criteria. However, including empty rows in the criteria table is not allowed, because in this case all entries in the list will be found.
In addition to the criteria table, for the Advanced filter command, you must define the type of the output document. This means that you should copy the names of those list fields that determine the type of output document into the free space of the worksheet. Excel will determine the number of rows in the output document.
Thus, to execute the Advanced Filter command, you should:
form in free space worksheet criteria table
Create a heading for the output document
highlight the list area in the source document
In the Processing area, you must specify whether the filtering will be performed in place or whether the output will be transferred to another area of the worksheet. If the default mode is "filter the list in place", then Excel hides all rows in the source list that do not meet the specified criteria. If the "Only unique records" radio button is selected, duplicate rows from the source list will not be shown in the output area.
Specifying conditions using the logical OR operation:
To set conditions in a range of criteria with a logical OR operation, you need to place these conditions on different lines: for example:
1) Display records about managers with the last name "Kislov" or about managers who sell "Khleb":
2) Get information about customers whose last names start with the letter B and T:
Specifying conditions using the logical AND operation:
To set conditions in a range of criteria with a logical AND operation, you need to place these conditions on one line: for example:
1) Find information about managers with the last name "Petrova" who sold goods for more than 50 units:
2) Find information about goods whose price is more than 30 and less than 80 rubles.
Objective: performing data sorting, getting acquainted with the method of filtering list entries, auto-filtering, working with a data form.
Exercise 1.
Sorting the data in Table 5.5 several times in accordance with the following criteria - in alphabetical order of the names of buyers, in descending order of the transaction amount, in ascending order of the transaction date, by a combination of characteristics (last name, date, amount).
Methodology for performing work
1. Open a new workbook and save it as "Sort" in your working folder .
2. Create the table shown in Figure 5.56.
Figure 5.56 - Initial table with data
3. Set the formatting options for the table.
Font Times New Roman, font size 12 pt., bold and centered for headings, word wrap, gray fill; for the main part. As a reminder, formatting commands are available on the ribbon Home Þ Cells .
4. To sort by the customer's last name field, place the cursor anywhere in this column and run the command Data Þ Sort (Fig. 5.51) .
In the dialog box that opens, in the field Sort by select "Customer Last Name". Ascending.
5. Repeat all the steps of paragraph 4 and set the sorting by "Transaction Amount", in descending order.
6. Re-sort by the Trade Date field, in ascending order.
7. Copy the table to a new sheet and sort it according to the set of features. To do this, call the command Data Þ Sort. Install Sort by surnames in ascending order, Then by date in ascending order Lastly, by sum in descending order.
8. With command Rename name these two sheets.
Task 2. Select information from the list based on the AutoFilter command.
Methodology for performing work.
1. On sheet 4, create a table and fill it with information from table 5.5.
2. Rename Sheet4 as AutoFilter #1.
3. To apply Autofiltering, place the cursor in the list area and execute the command Data ÞFilter. Downward arrows will appear next to the names of the table columns, which reveal a list of possible values. In the column "Gender" select "M" Copy the table to sheet 5 and rename it to "Autofilter No. 2".
4. On the "AutoFilter No. 1" sheet, in the "Gender" column, open the filtering list and select "All". Then, in the "Date of birth" column, select "Condition" in the filtering list and set the condition (Fig. 5.57):
Table 5.5
| Surname | Name | employment date | Date of Birth | Floor | Salary | Age |
| Pashkov | Igor | 16.05.74 | 15.03.49 | M | ||
| Andreeva | Anna | 16.01.93 | 19.10.66 | F | ||
| Erokhin | Vladimir | 23.10.81 | 24.04.51 | M | ||
| Popov | Alexei | 02.05.84 | 07.10.56 | M | ||
| Tyunkov | Vladimir | 03.11.88 | 19.07.41 | M | ||
| Notkin | Evgeny | 27.08.85 | 17.08.60 | M | ||
| Kubrina | Marina | 20.04.93 | 26.06.61 | F | ||
| Gudkov | Nikita | 18.03.98 | 05.04.58 | M | ||
| Gorbatov | Michael | 09.08.99 | 15.09.52 | M | ||
| Bystrov | Alexei | 06.12.00 | 08.10.47 | M | ||
| Krylova | Tatyana | 28.12.93 | 22.03.68 | F | ||
| Bersheva | Olga | 14.12.01 | 22.12.74 | F | ||
| Rusanova | Hope | 24.05.87 | 22.01.54 | F |
Figure 5.57 – Setting filtering conditions
5. Copy the filtered table to sheet 6 and rename it to "AutoFilter #3. In the AutoFilter #1 sheet, deselect.
Figure 5.58 - User filter
6. In the "Last name" column, select "Condition" in the filtering list and set the condition for selecting all employees whose last name begins with "B" (Fig. 5.58).
7. Copy the filtered list to sheet 7, rename it "AutoFilter #4".
8. On the "Autofilter No. 1" sheet, for the "Last Name" column, set "All", and in the "Salary" column, set "First 10 ..." where in the dialog box, enter "Show the 5 largest list items".
9. Save the file.
Task 3. Filter records from the list using the Advanced filter command.
The methodology for doing the work.
1. Go to sheet 8 and rename it "Advanced Filter".
2. Copy the table from the previous task onto this sheet (Table 5.5), paste it starting from line 7. The first 6 lines are reserved for setting conditions.
3. Let's create a range of conditions. Suppose we need to select the names of employees who receive more than 5000 rubles. Or someone over 50 years of age. Fill in the conditions as shown in Figure 5.59.
Figure 5.59 - Conditions for advanced filter
4. Run the command Data Þ Advanced . Fill in the dialog box as follows (Fig. 5.60):
Figure 5.60 - Advanced filter parameters window
View selection results. When conditions are written on one line, a logical AND is implemented. When conditions are written on different lines, they are considered to be connected by a logical OR. We have considered the first option, now consider the second.
5. Suppose we want to display only those employees whose last names begin with the letters A, G, or N. Fill in the range of conditions (Figure 5.61).
Figure 5.61 - Conditions for advanced filter
6. Run the command DataÞAdvanced and fill in the dialog box (figure 5.62).
Figure 5.62 - Advanced filter parameters window
View the results of the selection of records.
1. List all employees whose salary is above the average. Before creating this filter, enter the formula =AVERAGE(F8:F20) in cell H2 to calculate the average salary.
2. Then in cell A2 we enter the calculated condition =F8>$H$2, referring to cell H2 (Figures 5.63 and 5.64).
Figure 5.63 - Conditions for advanced filter
Figure 5.64 Advanced filter options
You can display information on one / several parameters by filtering data in Excel.
There are two tools for this purpose: AutoFilter and Advanced Filter. They do not delete, but hide data that does not fit the condition. The autofilter performs the simplest operations. The advanced filter has a lot more options.
Autofilter and advanced filter in Excel
There is a simple table, not formatted and not declared as a list. You can turn on the automatic filter through the main menu.
If you format a data range as a table or declare it as a list, then an automatic filter will be added immediately.
Using the autofilter is simple: you need to select the entry with the desired value. For example, display deliveries to store #4. Place a checkmark next to the corresponding filtering condition:
We immediately see the result:
Features of the tool:
- The autofilter works only in a non-breaking range. Different tables on the same sheet are not filtered. Even if they have the same type of data.
- The tool treats the top row as column headings - these values are not included in the filter.
- You can apply several filtering conditions at once. But each previous result can hide the records necessary for the next filter.
The advanced filter has many more options:
- You can set as many conditions for filtering as you need.
- The data selection criteria are in plain sight.
- With the advanced filter, the user can easily find unique values in a multiline array.
How to make an advanced filter in Excel
A ready-made example is how to use an advanced filter in Excel:
In the original table, only rows containing the value "Moscow" remained. To cancel filtering, you need to click the "Clear" button in the "Sort and Filter" section.
How to use the advanced filter in Excel
Consider using an advanced filter in Excel to select rows containing the words "Moscow" or "Ryazan". The conditions for filtering must be in the same column. In our example, one below the other.
Fill in the advanced filter menu:
We get a table with rows selected according to a given criterion:
Let's select the rows that contain the value "No. 1" in the "Store" column, and "> 1,000,000 rubles" in the cost column. The criteria for filtering must be in the appropriate columns of the condition label. On one line.
Fill in the filtering parameters. We press OK.
Let's leave in the table only those rows that contain the word "Ryazan" in the "Region" column or the value "> 10,000,000 rubles" in the "Cost" column. Since the selection criteria refer to different columns, we place them on different lines under the corresponding headings.
Apply the Advanced Filter tool:
This tool can work with formulas, which allows the user to solve almost any task when selecting values from arrays.
Fundamental rules:
- The result of the formula is the selection criterion.
- The written formula returns TRUE or FALSE.
- The source range is specified using absolute references, and the selection criterion (in the form of a formula) is specified using relative references.
- If TRUE is returned, the row will be displayed after the filter is applied. FALSE - no.
Let's display the rows containing the quantity above the average. To do this, aside from the plate with the criteria (in cell I1), enter the name " The largest number". Below is the formula. We use the AVERAGE function.
Select any cell in the source range and call the "Advanced Filter". We indicate I1:I2 as a selection criterion (relative links!).
Only those rows remained in the table where the values in the "Amount" column are above average.
To leave only non-repeating rows in the table, in the "Advanced filter" window, check the box next to "Only unique records".
Click OK. Duplicate lines will be hidden. Only unique records will remain on the sheet.
You can select the necessary data from the list using filtering, that is, by hiding all the lines of the list, except for those that meet the specified criteria. To use the filtering function, you need to place the table cursor on one of the cells of the list header (in our table this is the range A1:U11) and call the command Data/Filter/AutoFilter. When activated, a small box with a drop-down arrow will appear in the lower right corner of each header cell.
Consider the methods of working with the autofilter using the following example. Let's determine how many representatives of the stronger sex work at the enterprise. Click the filter button located in the cell with the heading Gender, and select the letter M (male) from the list that opens. The message Filter: selection will appear in the status bar (Fig. 4.20). All rows that do not meet the specified criteria will be hidden. The arrow on the list button turns blue, indicating that AutoFilter is enabled for that field.
Rice. 4.20. Using an autofilter to select records based on "M" (male)
If you need to specify how many bosses are among these men, also click the autofilter button in the Position cell and select the word Boss in the corresponding list. A message will appear in the status bar indicating how many rows meet the specified criteria: Records found: 2 out of 10 (that is, the answer will be given immediately). The result is shown in fig. 4.21.
To cancel filtering on a specific column, just open the autofilter list in that column and select All. However, if the filtering function is defined for multiple columns, you will have to repeat this operation several times. In this case, it is better to use the command Data/Filter/Show all.
Rice. 4.21. Worksheet after filtering the list of employees by the criterion "male - boss"
The filtering function will work properly if you are careful when entering data. In particular, you need to ensure that there are no extra spaces at the beginning and end of the text data. They are not visible on the screen, but can lead to erroneous results, and it takes a lot of time to identify them.
Filtering selects data that exactly meets the specified criteria. Therefore, if instead of the word "Head" the word "Head_" occurs in the column, that is, with a space at the end, Excel treats these values as different. To get rid of this kind of inconsistencies, copy the cell with the word "Head" to the clipboard, activate the filter for the selection on the basis "Head_" and replace the incorrect values with the contents of the buffer.
Filtering data in a list
The filter is fast and easy way searching for a subset of data and working with them in a list. The filtered list only shows the rows that match conditions(condition, constraint set for selecting records) set for the column. At the same time, the rest of the lines are hidden.
All this simplifies the process of entering and deleting records, as well as the process of finding information.
The advantage of applying filters is that the result of the filtering can be copied to a separate area of the table and immediately used in calculations.
AT Microsoft Excel There are two commands available for filtering lists:
- Autofilter, including filter by selection, for simple selection conditions;
- Advanced filter for more difficult selection conditions.
Unlike sorting, a filter does not change the order of the entries in the list. Filtering temporarily hides rows that you don't want to display.
Rows selected by filtering in Microsoft Excel can be edited, formatted, created charts based on them, printed out without changing the order of the rows and without moving them.
Autofilter used in cases where you need to quickly filter data with one or two simple selection conditions. These conditions are imposed on the contents of the cells of a particular column.
When using the command Autofilter Arrow buttons appear to the right of the column names in the filtered list.
Microsoft Excel highlights filtered items in blue
When the selection condition must be simultaneously applied to the cells of two or more columns;
When it is necessary to apply three or more selection criteria to the cells of one column:
When the selection condition uses the value obtained as a result of calculating the specified formula.
An advanced filter can use AND, OR operations, as well as compose calculated criteria.
Both commands are called as a result of selecting the Data/Filter command.
With the command Advanced filter you can filter the list in the same way as using the command Autofilter, but it doesn't show the dropdowns for the columns.
To search for data using an advanced filter, you must first prepare the data appropriately:
1 Create DATABASE ( original range).
2. Create Condition range, which specifies the search conditions for the data. The top row of this range should contain field headings that exactly match the field headings in the original range (ready-made list).
The range of conditions must include at least one empty row of cells immediately below the headings. AT this line or lines conditions are written, and everything that is written in the column under the field heading refers specifically to this field.
The conditions of all columns of each row are connected by the logical operation " And", and then all the lines are connected by the logical operation " OR»
3 From the menu Data choose a team Filter, then Advanced filter.
In the input field Range of conditions specifies the range of cells on the worksheet that contains your conditions. In the input field Put result in range specifies the range of cells into which rows are copied. This field is only available when the radio button is selected. Copy to another location.
4 Check that all ranges are automatically populated and correct if necessary.
Examples of complex selection conditions:
To find data that meets one condition in one column, or that meets a different condition in another column, enter the selection criteria in different rows of the selection criteria range. For example,
To find rows that meet one of two sets of criteria, each containing criteria for more than one column, enter those filter criteria on separate rows. For example,
To find rows that meet more than two sets of criteria, include multiple columns with the same headings. For example,
Publication date: 2015-10-09 ; Read: 205 | Page copyright infringement | Order writing work
website - Studiopedia.Org - 2014-2020. Studiopedia is not the author of the materials that are posted. But it provides free use(0.002 s) ...