The information contained in spreadsheets and databases is not always necessary for the user at every moment of his work in full. Therefore, MS Office applications provide tools to present information in a form suitable for processing. In the article we will get acquainted with some of them.
What is it and why
One such tool is data filtering. It is used to select from the entire array only those records that meet the necessary conditions. At the same time, all the information of the object is saved, but the person sees only the lines he needs, the rest, which do not satisfy the given conditions, are hidden.
Selection is made according to one or more conditions when setting restrictions on several fields. Each subsequent restriction is added to the existing ones, and the amount of information displayed is reduced, further facilitating the work with the array.
All modern spreadsheets and databases provide the ability to find records. Since applications of the MS Office package are the most common in office work, we should familiarize ourselves with the operation of filters in these programs in more detail.
Excel
In Excel, data filtering is available in several ways. It is carried out according to the contents of the columns of the tables depending on the type. In this case, specific cell values or conditions are selected. Row selection conditions also depend on what type of information is contained in the column.
The simplest method of selection is the tool of the "Edit" panel of the "HOME" tab. Select a column cell, in the drop-down list of the "Sort and Filter" button, select the "Filter" menu item. At the same time, a button with an arrow will appear in the first cell of the column, when clicked, a drop-down menu of tools for sorting and filtering data in Excel opens.
Here you define the options for selecting the rows to display.
Selection by Values
For example, we use the Excel table - the price list of a cosmetics store. The simplest type of selection is by value. In the price list there is a field “Type of cosmetics”. We want to see all the available nail care products. In the drop-down list of fields - a list of possible parameters, among which there is an element “For nails”. If the list of column items is too long, use the search box. Uncheck the boxes from all the other items except the desired one, and click OK. Only cosmetic products for nails remained visible.
You can further reduce the list of products by selecting, for example, only products from the manufacturer MAX FACTOR and, as a result, receive a sheet of nail products from this manufacturer.
Selection by condition
How now to display all hair products, if among the types of products there are several of these types: balms, masks, gels, etc.? You can select several items with flags, or you can specify data filtering by condition by field type.
In our case, the column contains text, and text parameters are applied to it. The user sets the string to search in items. At the same time, the contents of the cell are equal to or excluded from the search bar. It is possible to find records in which the text begins with the specified characters or ends with them, contains them or does not contain them. To set more complex conditions, it is proposed to configure your own rules.
Several conditions are specified here. All of them may be mandatory, or it is enough to satisfy only some of them (parameters “AND” and “OR”). The builder provides the ability to set conditions for various types: text, numeric, date and time, logical, etc.
This method of filtering data in MS Excel for numerical fields is convenient. Suppose in our price list we want to find lip products with a cost of up to 300 tons. In the column “Type of cosmetics” we select two items with flags: “LIPSTICK” and “GLOSS FOR Lips”.
In the column “Price” we select the position “Numeric filters”, unlike text in the column with the type of cosmetics.
Choose the option “less than or equal to” from the options and set the amount of 300 rubles in the parameters window that opens. After clicking on the “OK” button, we get a list of four products of the types “Lipstick” and “Lipgloss” and costing less than 300 rubles.
Finally, we add one more condition for filtering data - by color. Our price list shows examples of colors for decorative cosmetics. We will take advantage of this and choose the means for the lips of a light pink shade. In the menu column “Color gamut”, select the item “Filter by color”, press the button “Other cell colors” in it, if the proposed selection is not enough, and select the desired shade. By the OK button, only funds in the desired price and color range remain.
Sort and search for information
Even after the extra entries are hidden, there may still be many for selection or analysis. In this case, another convenient feature of MS Excel is applied - sorting. In the menu we have already studied, there are items that will help you build the table elements in the necessary order, thereby facilitating the search.
Ordering items in a column is possible, depending on the type of information in it. For text fields, a distribution from "A" to "Z" and in the opposite direction is suggested. It is convenient to use, for example, for a more convenient search by customer name, partner name, etc.
Numeric fields are sorted in ascending and descending order. Care should be taken if numbers are entered in the column as text. In this case, they will be ordered as text, that is, the numbers 10, 11, 100, 12, 110 will go in this order depending on the format of the fields:
| Sort in number format | Sort in text format |
| ten | ten |
| eleven | one hundred |
| 12 | eleven |
| one hundred | 110 |
| 110 | 12 |
Records are sorted by a number of parameters. To do this, there is the option of custom sorting in the menu button "Sort and filter." You choose which fields to sort, what type and in what order to apply:
Custom lists contain days of the week and months, and the user can enter their own with their items.
Access DB
No less useful is filtering in databases. Access has several options for obtaining the necessary information: using general parameters, according to a specific value, in form, and using queries.
On the main tab of the application you will find the Sort and Filter toolbar. The buttons on it are used to perform the indicated operations. Access uses the same mechanism as Excel. Let us dwell in more detail on those methods with which we are not yet familiar.
Ways
General data filtering in Access is implemented in the same way as in MS Excel, using the drop-down menu in the column header. By analogy, one or more elements are selected from the list of available ones or selection by the type of information in the column is applied.
To use a specific value in a cell as a parameter, select this cell and select the "Selection" menu in the "Sort and Filter" panel or call up the list of available actions with a right click. Selection options depend on the type to which the contents of the cell belong.
A form filter is an easy way to find records with the ability to use several parameters. It is called by the "Advanced" - "Filter by form" operation. In this case, an empty form is created on which you set the parameters, and then click "Advanced" - "Apply". Now the form contains only the required records.
Data Queries
An additional convenient means of filtering data in the database are queries. Creating them takes time and the necessary skills, but requests have a number of advantages. First of all, once you write a query for a selection and save it, you get a mechanism that you can use constantly to build reports, output lines to forms, create complex cross-queries, etc.
In queries, you can use the fields of several tables, adding conditions for obtaining data for each of them. These database objects are created using the Constructor, the query wizard, or the SQL editor.
SQL queries on the sample have the structure of the form
Select [ ]
From []
Where []
Here, in the condition block, the parameters by which the records are selected are set. For an example, we will compose a query for selecting records from the table “Warehouse goods”. Suppose we need to select rows with vases under the name “Delight” and see how many goods with that name are in each warehouse.
SELECT [ ].[ ], ., [ ]., [ ].
FROM INNER JOIN [ ] ON . = [ ].
WHERE (([ ].[ ]="") AND ([].[]=""));
The result will look like this:
In design mode, the query has the form:
Preservation and subsequent use
Sometimes it becomes necessary to reuse once established selection or to automatically apply it when starting a table or form. Conditions are saved automatically when the object is closed. The next time it is opened, all entries are displayed, and in order to apply the saved restriction again, you need to click the Filter button on the Sort and Filter panel.
To automatically apply the desired selection, use the property of the "Filter at boot" object by setting it to "Yes"
Another way to save and use them later is as a request. To do this, set advanced options. Configure the necessary fields for searching, sorting and filtering data and save it in the context menu. Subsequently, it is downloaded from the same menu with the command "Download from request"
Search and list in Access
By analogy with MS Excel, Access provides the user with the ability to sort by columns to facilitate the search for the necessary information. The values are sorted alphabetically for text (in reverse and reverse order), in ascending and descending order for numerical information and dates. You can apply it across several fields at the same time, which further helps in the task of finding the necessary records. Text fields are case insensitive.
To find a record with the entry of the desired line, the search mechanism is used - the "Find" button of the same toolbar. The search parameters are entered in the window - line, place, direction, part of the field, case.
The mechanisms for sorting, searching and filtering data in spreadsheets and databases greatly simplify the work with large amounts of information. If you learn to use them easily, the use of these tools will become familiar, speed up and facilitate daily work.