You can display information on one / several parameters using data filtering to Excel.

Two tools are intended for this purpose: autofilter and advanced filter. They do not delete, but hide data that are not suitable for condition. The auto filter performs the simplest operations. An extended filter has much more opportunities.

Auto Filter and Advanced Filter in Excel

There is a simple table, not formatted and not announced. You can turn on the automatic filter through the main menu.

If you format the data range as a table or declare a list, then the automatic filter will be added immediately.

To use the autofilter simply: you need to highlight the record with the desired value. For example, to display deliveries to store №4. We put the bird opposite the corresponding filtering condition:

Immediately see the result:

Features of the tool:

1. The auto filter works only in an inseparable range. Different tables on one sheet are not filtered. Even if they have the same data.
2. The tool perceives the top line as column headers - these values \u200b\u200bare not included in the filter.
3. It is permissible to apply several filtering conditions at once. But each previous result can hide the recordings necessary for the next filter.

Extended filter has much more features:

1. You can ask as many conditions for filtering as needed.
2. Data selection criteria - in sight.
3. Using the extended filter, the user easily finds unique values \u200b\u200bin a multi-line array.



How to make an advanced filter in Excel

Ready example - how to use an extended filter in Excel:

Only lines containing the importance of "Moscow" remained in the source table. To cancel filtering, you need to click the "Clear" button in the "Sort and Filter" section.

How to use an extended filter in Excel

Consider the use of an extended filter in Excel in order to select lines containing the words "Moscow" or "Ryazan". Filtration conditions must be in one column. In our example, each other.

Fill out the extended filter menu:

We get a table with selected on the specified criteria strings:

We will take the selection of lines that in the "Store" column contain the value "№1", and in the column cost - "\u003e 1 000 000 r.". Criteria for filtering must be in the corresponding columns plate for conditions. On one line.

Fill the filtering parameters. Click OK.

Let us last in the table only those lines that in the "Region" column contain the word "Ryazan" or in the "Cost" column - the value "\u003e 10 000 000 r.". Since the selection criteria belong to different columns, place them on different lines under the appropriate headlines.

Apply the "Advanced Filter" tool:

This tool is able to work with formulas, which allows the user to solve almost any tasks when selecting values \u200b\u200bfrom arrays.

Fundamental rules:

1. The result of the formula is the selection criterion.
2. Recorded formula returns the result of truth or false.
3. The initial range is indicated by absolute references, and the selection criterion (as a formula) is using relative.
4. If the value of the truth is returned, the string will be displayed after applying the filter. Lie - no.

Display strings containing the amount above average. To do this, aside from the plate with criteria (in cell I1), we introduce the name "the largest number". Below - formula. We use the function of the SRVNA.

Select any cell in the source range and call the "Advanced Filter". As a selection criterion, we indicate i1: I2 (relative links!).

The table remains only those lines where values \u200b\u200bin the "Number" column above average.

To leave the table only non-repeating lines, in the "Extended Filter" window, put a bird in front of "unique records only".

Click OK. Repeating lines will be hidden. Only unique entries will remain on the sheet.

The filter is a fast and easy way to search for a subset of data and work with them in the list. The filtered list displays only rows that meet the conditions. Unlike sorting, the filter does not change the order of entries in the list. When filtering, lines that do not need to display are temporarily hidden.

Rows selected when filtering can be edited, format, create a diagram based on them, to print them, without changing the order of rows and without moving them.

When filtering, only the necessary data is selected, and the remaining data is hidden. Thus, only what you want to see is displayed and this can be done with one click.

When filtering, the data does not change in any way. As soon as the filter is deleted, all data appear again in the same form, in which they were before using the filter.

In Excel, two commands are available to filter lists:

• Auto Filter, including a filter on the dedicated, for simple selection conditions.
• Advanced filter For more complex selection conditions.

Auto Filter

For inclusion Autofiltra You need to highlight any cell in the table, then on the tab Data in a group Sorting and filter Press a large button :

After that in the table header to the right of each column header, the down arrow button will appear:

Clicking the arrow opens the menu with a list for the corresponding column. The list contains all the column elements in an alphabetic or numeric order (depending on the data type), so you can quickly find the desired item:

If we need a filter only for one column, you can not display the arrow buttons for the remaining columns. To do this before pressing the button Select several cells of the desired column along with the title.

Filtration by accurate value

Turn on Auto Filter, click on the button with the arrow and select some value from the discussed list. In order to quickly select all the column elements or remove the selection from all items, click on item. (Select all) :

In this case, all lines in which the selected value is not contained, hidden.

When performing laboratory work, we allocate the result of filtration, copy to another location of the sheet and subscribe.

To shut down Autofiltra need to press the button again .

To cancel the filter action, without leaving the filtration mode, click on the button and select the item from the discussed list (Select all) . In this case, the table lines hidden by filter appear.

Signs of data filtering

Filters hide data. It is for this that they are intended. However, if the data filtering is not known, it may be impressed that some data is missed. You could, for example, discover someone's filtered sheet or even forget that the filter themselves had previously applied. Therefore, when there are filters on the sheet, you can detect various visual pointers and messages.

(located on the left at the bottom of the window). The initial state:

Immediately after filtering data, the result of the use of the filter is displayed in the lower left corner of the status string. For example, " Found records: 2 out of 11”:

Row numbers . According to intermittent rows numbers, it can be said that some rows are hidden, and the changed color of the visible rows numbers indicates that the selected lines are the result of the filter selection.

View of arrows . Changing the arrow of the autofilter in the filtered column indicates that this column is filtered.

”

"" This is another universal filter that can be applied to columns with numbers or dates.

"" Is a very conventional name. In fact, the possibility of this filter is much wider. With this filter, you can find or first elements or recent elements (the smallest or largest numbers or dates). And, contrary to the name of the filter, the results obtained are not limited to the first 10 elements or last 10 elements. The number of displayed elements can be selected from 1 to 500.

"Allows you to select data on the percentage of the total number of rows in the column. If the column contains 100 numbers and you need to view the largest fifteen, then select 15 percent.

The filter can be used to find products with the highest or smallest prices, to determine the list of employees hired by the last time, or to view the list of students with the best or worst marks. To apply the filter "" to the data column ( only numbers or dates !!!), click the arrow in the column and select item Numeric filters Further :

After that, the dialog box opens Overlay conditions by list :

In the dialog box, choose number (rows or percent) naibol or the smallest, list elements or % of the number of elements.

Creating your own custom filters

For example, we need to withdraw only strings with positions starting with the letters' D.'. To do this, click on the arrow autofilter in the first column and choose Text filters , then point begin with… :

The dialog box appears (Whatever item to the right you can choose, the same dialog box will still appear.):

In field Position choose - begin with On the right to enter d.:

In the window There is a hint:

Question mark " ? "Indicates one any sign.

Sign " * "Indicates a sequence of any signs.

To search for data or records, filters are used, which displays only records on the screen corresponding to certain conditions, and records that do not satisfy the specified requirements, the editor temporarily hides. Displaying records (lines), can be formatted, edit, print, etc.

Filtering facilities include:

Auto Filter (there are two ways to apply the AvtoFilter command: Using the "Data" menu - "Filter" - "Auto Filter" - "Recovery List of AvtoFilter Commands" and using the "Auto Filter" button on the Toolbar Standard)

Advanced Filter ("Data" - "Filter" - "Advanced Filter") Auto Filter Designed for simple conditions of lines, and an extended filter for more complex recording conditions. The selection condition is the restrictions specified to select records that are selected by the editor to display on the screen.

Using autofilter to search for records

Before applying the Auto Filter command, select the cell in the list range. Next, execute the "Data" command - "Filter" - "Auto Filter", to the right of the headers (signatures) of the columns will appear buttons with arrow arrow (drop-down lists of autofilter commands).

If you click the Autofilt Arrow, a list of different filtering and sorting options is displayed in ascending and descending. When filtering an active autofilt arrow is painted in blue.

List of autofilter commands and their destination:

Everything - All entries will be displayed on the screen.

The first 10 ... - The "Overlay Conditions" dialog box opens if the data in the column is numbers. With this window, you can quickly view up to ten largest or smallest numbers in the column.

Condition...- The "Custom Auto Filter" dialog box opens. With this window, you can sort the records that satisfy one or two conditions.

Unique names - This is a list of non-repeating records in the column cells. If you click on the left mouse button on one of the unique names, the editor will hide the strings that do not contain the selected data in order for after filtering all the records are displayed. You must press the active button and select the "All" command or execute the "Data" command - "Filter" - "Display all", and to remove the autofilter, you must select the Autfilter command checkbox in the "Data" menu.

Algorithm for finding records meets one condition in one column

Select a cell on the list

Click on one of the unique names, the editor will instantly hide the strings that do not contain the selected data in the figure shows the filtered list on the "category" column with the selected selection condition "Cond. Products"

If necessary, the filtered list of one column can be filtered according to other columns.

Algorithm for finding records meeting one or two conditions in one column

To search for records for two conditions, the "Custom Auto Filter" applies:

Select a cell on the list

Run the "Data" command - "Filter" - "Auto Filter"

Click the Autofilt Button in the column that you use to filter data

Click the "Condition" command, the "Custom Auto Filter" dialog box opens

In the two left-hand drop-down lists, select the appropriate statements, and in the two right drop-down lists, select the required values. Then set the switch to one of the two positions: "and", "or"

Click on the OK button to filter. The list will display entries that satisfy the specified criteria. For example, select the following selection conditions: to show in the list of lines containing products whose prices are larger than or equal to 69.80 UAH and less than 99.99 UAH.

In the "Custom Filter" dialog box, you can select the following operators:

not equal

• more or equal

• less or equilibrous, you can set the comparison operators, which are used when filtering text: "starts with", "does not start with", "ends on", "does not end on", "contains" and "does not contain".

The figure shows a filtered list on the "price" column under the selected selection conditions.

Filter by dedicated

Filtration by dedicated - This is a quick way to filter records one condition using the "Autofilter" button on the toolbar standard.

Filtration algorithm for dedicated:

 Highlight the required entry in the list.

 Press the "Auto Filter" button to filter by dedicated. The editor will turn on the "autofilter" and hobs the lines that do not contain the selected data

Filtering the list using the extended filter

The "Advanced Filter" command is used to filter over more complex selection conditions than the autofilter, for example, by several selection conditions in one column, according to several selection conditions in several columns or selection for selection conditions using the formula. Before applying the "Advanced Filter" command, it is necessary to prepare the selection conditions, which, as a rule, should be placed above the list. In connection with this requirement, at least three empty lines located above the list should be on the work sheet. In addition, at least one empty line should be between the list and values \u200b\u200bof the selection conditions.

Creating a range of selection conditions:

Select the headlines of the filter columns of the list and execute the "Copy" command by any of the known methods.

Highlight the first line to enter the selection conditions and execute the "Paste" command by any of the known methods.

Enter the required selection criteria in the strings located under the terms of the Conductor.

After creating the range of the selection conditions, you can apply to the "Advanced Filter" command list.

Filtering algorithm with the "Advanced Filter" command

Highlight the cell in the list

In the "Data" menu, select "Filter" - "Advanced Filter"

Set the "Processing" switch to the "Filter List on site" position if you want the editor to display filtering results in the list, scissors that do not satisfy the selection criteria. To copy filtered entries to another area of \u200b\u200bthe desktop, set the "Processing" switch to "Copy the results to another location" position and specify the range to place the results.

Check the source range of the cells of the filtered list, the range must contain all the list of the list with the column header cells.

Check the range of selection conditions, it must contain all cells of the range of selection conditions, taking into account the cells of the column headers

Click on the OK button to filter. The list will display entries that satisfy the specified criteria.

The figure shows a sample of the "extended filter" selected filter parameters.

The figure shows a sample of a filtered list, over which the range of conditions is located.

In a table containing hundreds or thousands of entries, it is quite difficult to find the information you need. Microsoft Access presents several search and viewing tools.

¨ Search for entries in the text contained in any of the fields.

¨ Alignment of entries in ascending or decreasing the values \u200b\u200bof a particular field.

¨ Change the number of displayed entries using the filter.

Text search in records

Search for one or more records with the desired text, it is convenient to implement the search tools. With the help of the team To find From the menu Edit or buttons To find From the toolbar Table in table mode Enter the desired text and then specify whether Access should search in all fields or only in the current one, determine the position of the text inside the field and install whether the characters register should be taken into account when searching. Having found the first appropriate entry, Access will highlight the desired text. After that you can either continue, or cancel the search.

Text search in the current field:

1. Display the table in Table mode .

2. Click the column in which you hope to find the desired text.

3. Click the button To find on the toolbar Table in table mode.

4. Enter the desired text. If you need to consider the characters register when searching, click Checkbox Case sensitive Otherwise - lose the checkbox.

5. To start the search, click the button. To find.

6. To go to the next record containing the specified text, click the button. Find below .

7. To close the dialog box and return to the table, click the button. Close .

Text search in all fields:

1. Display the table in table mode.

2. Click the Find button on the Table Toolbar in Table Mode.

3. Enter the desired text.

4. Reset the checkbox only in the current field.

5. To start the search, click the Find button.

6. To go to the next record, containing the specified text, click Find Next.

7. To close the dialog box and return to the table, click the Close button.

Changing the order of entries in the table

Sorting You can change the sequence of records in the table by placing them ascending or descending the values \u200b\u200bof the selected field. Sorting can be set across multiple fields.

Location of entries to increase the values \u200b\u200bof one field:

1. Install the cursor anywhere in the column, on whose fields you want to sort the records.

2. Click the Sort button in the Toolbar Table in the table mode.

Sorting records across multiple fields

When you change the order of entries in the table, it is sometimes necessary to set the sorting of more than one field. Such an operation is called combined sorting . In this case, the fields must follow in the table with each other. Access itself sorts them from left to right - just specify the desired fields. For example, to compile a list of customers and contacting employees with them, first sort the entries on the field Clientand then - by field Employee.

Sorting records by multiple fields:

1. Display the table in Table mode .

2. Click the header of the first column, based on the values \u200b\u200bof which you want to sort the records, and, without releasing the mouse button, drag the pointer to the right on columns to set their fields.

3. To sort records ascending or descending, click the button. Sort Ascending or Sort by descending On the toolbar Table in table mode .

Search and replace text in the table

Sometimes when updating the data of the table, you must make the same changes immediately to several records. To search and replace the text fragment in the entire table uses the replacement mechanism. Access allows you to set either each replacement separately or all at once. In addition, you can specify the search direction, it is necessary to take into account the character register, as well as to check for a coincidence with the entire field or with its part.

Search and replace text:

1. Display the table in Table mode .

2. Click the column in which you expect to find the desired text.

3. Click Menu Edit And then the team Replace .

4. In the field Sample Enter the text to be replaced.

5. In the field Replaced by Enter a new text.

6. To search for the first mention of the text, click Find below .

7. To replace the first fragment of the text fragment, click the button. Replace , To replace all fragments found - button Replace everything , and to go to the next fragment - button Find below .

8. Having finished the work, click the button. Close .

View the specified entries using the filter

Filter Allows you to display on the screen not all table records, but only the necessary. Access filters records over one or more fields, after which only those of them displays that satisfy the specified conditions. To give the initial type table, turn off the filter.

Filtering table via dedicated:

1. Display the table in Table mode .

2. Install the cursor in the field you want to set filtering.

3. Click the button Filter by dedicated (1 button) on the toolbar Table in table mode . At the bottom of the table window, a message appears on the number of records that meet the criteria for your filter. Letters FLTR The status bar indicate that the filter is active.

Turning off the filter:

Click the button Remove filter (3 button) on the toolbar Table in table mode . Letters disappear from the status string FLTR .

Saving filter

If you assume that the complex filter you created or the filtering criteria can be useful again, save them as a query. Although requests have some unique features that go beyond the filter features, in this case you can create a simple query to save the already created filter.

Saving a filter as a query:

Table mode.

2. Click Menu Entries Install the mouse pointer to the command Filter And in the submenu that appears, click the command Advanced filter . In the extended filter window, you will see the accepted scheme of the setting conditions.

3. Click Menu File And then - the team Save as request .

4. Enter in the text field Request name The name of the saved request.

5. To save the filter as a query, click the button. OK . The name of the saved query will appear in the tab. Inquiries Database windows.

Creating complex filters using a filter designer

In Access to develop complex filters, you can use the designer. When you enter into one tab several conditions simultaneously, create And filter. It displays only records that satisfy all specified in the criteria tab. Records that satisfy one of several conditions are browsing with Or filter. When it is applied, all entries corresponding to any of the conditions specified in the tab are displayed. To find or in any of the active tabs Or .

Creation and filter:

1. Display the filtered table in Table mode.

1. Click the button Change filter (2 button) on the toolbar Table in table mode .

2. If you want to abandon the installations of the previous filter, click the button. Clear blank On the toolbar Filter .

3. Click an empty cell under the heading of the field whose values \u200b\u200byou want to use to filter.

4. Click the drop-down list arrow and select the field value to be filtered. To work correctly, the text entered into the filter constructor blank is in quotes.

5. Perform similar steps for each field specified for filtering.

6. Click the button Filter use On the toolbar Filter .

Creating or filter:

1. Display the table in Table mode .

2. Click the button Change filter On the toolbar Table in table mode .

3. If you want to abandon the installations of the previous filter, click the button. Clear blank On the toolbar Filter .

4. Click an empty cell under the heading of the field whose values \u200b\u200byou want to use to filter.

5. Click the drop-down list arrow and select the field value.

6. Perform similar steps for each field specified for filtering. Any of them sets additional record selection criteria.

7. To specify additional filtering conditions, click the tab. Or At the bottom of the constructor window.

8. Click the button Filter use On the toolbar Filter .

Data Sort (in Table Mode)

First, you should select the field for which you need to sort the data (i.e. it is necessary to highlight the entire column of our table), for which it is enough to click on the field name in the table heading. Then you need to click on one of the two buttons:

Using the first of these buttons, text data is sorted according to the alphabet, and numeric - ascending, with the help of the second data are sorted in reverse order.

Data filtering (in table mode)

Sometimes it is necessary to see on the screen not all database records (table lines), but only those that meet certain conditions. This process is called filtering or data sample.

To sample the necessary data, follows:

1. Run the recording command, filter, change the filter.

2. Click on the name of the desired field in the list and enter the (mask) of the selection.

3. Run the Filter command, apply the filter.

A table will appear on the screen, which will contain lines, which correspond to the specified condition. To see the entire table again, you must execute the recording command, remove the filter.

In addition to mathematical equalities and inequalities, you can use the "*" sign, which replaces any sequence of characters, or the sign "?", Replacing one character.

Below are the options for sampling data from our database and sampling results.

Condition: Salary:\u003e \u003d 200 (employees who have a salary greater than or equal to 200)

the code	Surname	Name	Telephone	The salary
1	Ivanov	Egor		200
3	Efimov	Evgeny		210

Condition: Name: H * (employees whose names begin on the letter H)

the code	Surname	Name	Telephone	The salary
2	Semenov	Nikolai

Search for data using filters.

Quick data search

Data processing in the database.

The program allows you to search for records in which the values \u200b\u200bof a specific field are fully or partially coincided with some value. To quickly search for data you need:

1. Overclock the BD "Internet Providers", double-clicking on the corresponding icon in the database window.

2. Enter the command Edit - Find ...A dialog panel will appear Search. In field Sample: You must enter the desired text, and in the field Coincidence: Select item with any part of the field.

Much more opportunities to search for data in the database represent filters. Filters allow you to select records that satisfy the specified conditions. Recording conditions are created using comparison operators (\u003d<, > etc.)

Simple filters Contain the selection condition for the record only for one field. Complex filters contain several conditions for different fields. As a result of the use of a complex filter, only those records that satisfy all conditions simultaneously will be selected.

1. Open the Internet Providers Table, double-clicking on the corresponding icon in the database window.

2. Enter the command Records - Filter - change the filter. In the table window that appears, enter the search conditions in the corresponding fields. Filter created

3. Enter the command Entries - Apply the filter. In the tables window that appears, records that satisfy the search conditions will be displayed.

Requests are searching for data in the database as well as filters. The difference between them is that queries are independent bd objects, and the filters are tied to a specific table.

The request is derived by the object from the table. However, the result of the execution of the query is also a table, that is, queries can be used instead of tables. For example, a form can be created for both the table and request.

Requests allow you to select those records that satisfy the specified conditions. Requests, like filters, are simple and complex. A simple query contains one condition, and a complex query contains several conditions for different fields. In the process of creating a request, you can select not only entries, but also the fields that will be present in the request. Create a complex request to identify the optimal provider in the Internet Providers.

1. In the window Select a group of objects Inquiries And choose item Creating a query using a designer.
2. On the dialogue panel Adding a table Select the Internet providers table for which a request is created. Click on the button Add.
3. In the query window in the string Field: From the drop-down list, select the field names for which the conditions will be specified. In line Selection condition: Enter the conditions for the selected fields. In line Output on display: Set fields to be presented in the query.
4. Save request under the name Request 1. With the help of the team File - Save as ...
5. In the window Internet Providers: Database highlight Request 1. and click on the button Open. In the query window that appears, records that satisfy the search conditions will be displayed.