JOB 1. Data analysis

1. Create a table modeled on Table 38. Type the table name, column names, and only the first row of data. Columns Cost income (parish work on the accounting price) cost flow (work flow on the accounting price) enter the calculation formulas

2. Spend formatting tables: table name should be centered across the width of the table; column names are set in the center of the cell, set bold and gray background; choose the width of the columns; selection of cells in the frame within the column names.

3. Enter data in list form on OSD (Data/Form):

3.1. Place the cursor in cell A2, and activate a data form

3.2. By clicking on the Add form data, enter the rest of the data table line. Total should be 10 records.

4. Spend search data on the following criteria using the form. To do this, use the button Criteria: in the corresponding field, enter the search criteria, then "click" the mouse on the Next option.

4.1. Brand POL begins with the letter D;

4.2. 05.02.00 Date of fuel supplies;

4.3. Account the price of gasoline is less than 35 m;

4.4. The cost of fuel at the beginning of more than 400,000;

5. Form data close.

6. Perform data sorting the list by using the toolbar buttons and commands Data/ Sort by:

6.1. Descending column values Date of delivery;

6.2. Ascending values of the Cost column parish;

6.3. Ascending column values Brand.

6.4. Spend sort the data in three columns simultaneously: Type, Brand, Income. 7. Filtering data. With a team of Data – Filter – AutoFilter swipe sample data according to the following criteria:

7.1. Income – 200

7.2. By material type Gasoline and Mark AI–91

7.3. Check fuel supplies – February 5

8. Select data on gasoline consumption is not less than 1000 9. Choose fuel, date of delivery, which lies between 01.02.00 and 05.02.00 10. Make a list of the five largest revenue fuels.

12.1. Determine the total amount received by each type of fuel and lubricants materials, pre- sort the data by type of material

12.2. Determine the number of each type of consumable materials

12.3. Determine the total amount received fuel and the average price of each type of material

13. Create summary tables (command data/PivotTable) and:

13.1. Determine the amount of each type of material flow by date.

13.2. Determine the amount of each type of material receipt date.

JOB 2.

Data input in the list by means of a form

1.1. On a blank work sheet enter names of columns of the table on a sample given in table 40, and data only one line of data. Enter a formula for calculation of residual cost. The column Residual cost is defined as a difference between cost for the beginning of year and annual wear.

1.2. Carry out table formatting.

1.3. By means of team Data – the Form enter other these tables

2. Search of the data by means of a form

2.1. Using the button Criteria of a form of data to make a search of the data according to the following conditions: the brand of motor transport begins on a letter K; year of purchase 1990; trucks of the 1990th year of release; balance cost of more than 100;

2.2. Sorting of data of the list. Carry out sorting of data of the list by means of toolbar buttons: on increase of values of Annual depreciation; on decrease of values of the General depreciation.

2.3. By means of the Data/sorting team carry out sorting of data according to three fields at the same time: Look, Mark, Year of purchase.

3. Filtration of data

3.1. Carry out selection of data with the help of the autofilter with the following conditions:

a) T-4 brand tractor;

b) combines Yenisei of the 1988th year of purchase;

c) all types of motor transport with balance cost in the range of 200-300;

d) all types of motor transport with annual depreciation more than 50.

3.2. Carry out selection of data with the help of the expanded filter:

a) tractor of the T-4 brand of year of purchase 1989;

b) tractor and combines of the 1990th year of purchase;

c) all types of motor transport with balance cost over 300;

d) all types of motor transport with the general depreciation more than 100.

4. Automatic summing up

4.1. Sum up the results on all numerical fields with use of function of summation on the Type groups.

4.2. In the table by means of management buttons consistently hide detailing elements, having left only a line the General result. Then restore the hidden details.

4.3. Add a line with determination of average annual depreciation depending on a year of purchase.

5. Creation of the summary table

5.1. Using services of the Master of the summary table create on single work sheets the summary tables, allowing to give answers to the following questions:

a) What sum of annual depreciation in a section of brands and years of purchase?

b) To that is average value of residual cost equal in a section of brands and years of purchase with possibility of a choice of a type of motor transport?

c) To that the sum of balance cost and cost for the beginning of year is equal in a section of types of motor transport, brands and years of purchase?

Table 40 – Existence of vehicles

Type

Mark

Year of purchase

Balanced cost

Primary cost in the beginning of the year

Annual depreciation

General depreciation

Left cost

Tractor

MTZ-50

Tractor

MTZ-82

Tractor

MTZ-80

Tractor

T-4

Tractor

Ò-4

Tractor

Ò-4

Tractor

Ò-4

Tractor

Ò-16

Tractor

Ò-40

Tractor

Ê-700À

Tractor

Ê-701

Tractor

Ê-701

Tractor

Ê-701

Tractor

ÄÒ-75

Tractor

UMZ-6

Tractor

UMZ-6

Combine

Enisey

Combine

Enisey

Combine

Enisey

Combine

Enisey

Combine

Enisey

Combine

Enisey

Combine

Enisey

Combine

Enisey

Combine

Enisey

Combine

Niva

Freight

ZIL-4502

Freight

ZIL-157

Freight

KamAZ-51

Freight

GAZ-53

Freight

GAZ-53

Freight

SAZ-3531

Bus

KAVZ

Car

GAZ-60

Car

IZH-2715

Car

GAZ-24

Car

VAZ-2121

Car

UAZ-3303

JOB 3.

Creation of summary tables

1. Create a database on a blank work sheet with the name Database on a sample Table 41.

2. Create the summary table for finding of the sum of the deposits which are storing in various offices of bank.