Sort and list processing. AutoFilter and Advanced Filter.

Objective: Acquainted with using a spreadsheet as a database; learn to search for information in a database according to various criteria; learn how to sort information.

Command is used to sort the Data – Sort. The Sort dialog box to select the field on which to sort. The first of the selected fields determines the mode of primary sorting. With the existence of additional criteria can be set for the field of secondary and tertiary sort. On any of the fields can sort in ascending or descending order.

In MS Excel there AutoFilter and Advanced Filter. When you use AutoFilter for each field in the database (DB) automatically creates a set of standard filters available through drop-down lists. Revealing button these lists are displayed near the header field of each column (All, First 10, Condition, ...). When filtering records that do not fall within a selected subset of hiding and not displayed. The total number of database records and the number of selected records is displayed in the status line. Initial record numbers saved, but displayed in a different color (blue). Same color portrayed header field on which filtering was performed.

Extended filtering is applied in the case when you need to copy the relevant records to the diagram does not depend on changing the filter criteria. Advanced filter operates with two databases of the same structure. Entry in the database environment can contain blanks. Each non-empty field specifies certain filtering condition. For text fields requires a coincidence of the field in the database with the specified value here. For numeric fields, you can also specify the signs of comparison. Conditions specified within one record, to run concurrently. When you use the Data – Filter – Advanced Filter, you can filter in place or remove the filtered records and put them separately. Wizard dialogs advanced filter is the range of the original database and a range of conditions.

JOB.

Using data from the lab number 7 to find data (Data – Filter – AutoFilter) which satisfy the following conditions (each task perform on a separate sheet, previously making a copy of the original sheet by using the "Make a copy" command worksheet «Move/Copy»):

1. Select employees who have worked at the company for over 20 years.

2. Choose employees who joined before 1995.

3. Select employees whose birth month – March.

4. Select employees whose birth year – 1963.

5. Select employees whose last name begins with the letter "A" or the letter "K".

6. Choose employees who have worked at the company for 10 years.

7. Choose employees who joined after 1990.

8. Choose employees, year of birth – 1973.

9. Choose employees, which is the year of birth between 1963 and 1970.

10. Choose employees, which is the year of birth between 1970 and 1975.

11. Choose 5 employees, most have worked at the firm (choose from a list of elements of the First 10. Then return all the data and do the same operation for the five smallest elements of the list).

Note: Following the withdrawal of the filtered records to each condition must be selected command Data –Filter –Show all. Only after this command to start the sample by the following criteria.

12. Cancel the AutoFilter.

13. Sort the data in alphabetical order using the button in the format bar.

14. Sort the data tables on "Of work experience" – in ascending order.

15. Sort the data table on the date of receipt to work and by the names in alphabetical order, using sort window (Data – Sort)

16. Consider extending the filter. To work with the advanced filter criteria ancillary table for this:

• Insert three blank rows above the first row of the table

• Copy table column names in cell A1.

• To filter the records proceed after filling in the criteria table (next after the name of the columns) condition for the selection

17. Choose employees who have worked at the company for more than 7 years:

• In cell H2, type: >15

• Activate any cell in the table, such as B12

• Choose Data – Filter – Advanced Filter

• In the advanced filter conditions specify the range A1:H2

18. Choose employees who joined before 1990.

19. Select employees whose birth month – March.

20. Choose employees, whose birth year – 1963.

21. Select employees whose last name begins with the letter "A" or the letter "B".

22. Choose employees, whose birth year – 1973 and the month of birth – March.

Date: 2015-12-24; view: 1896