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.
11. Spend sample data using Advanced Filter (Data/Filter/Advanced Filter) 11.1. Material type - Petrol and Mark AI– 91
11.2. Select data on gasoline consumption is not less than 1000
11.3. Select fuel, date of delivery, which is 01.02.00 and 05.02.00
Table 39 – Statement of Changes in Fuels and lubricants
¹
Type of material
Type
Value at the beginning
Coming
Discount price
The cost of joining
Date of delivery
Consumption
Cost flow
Check the flow
Petrol
AI-76
31,00
03.02.00
10.02.00
Petrol
AI-91
34,00
20.02.00
20.02.00
Petrol
AI-93
42,00
03.02.00
20.02.00
Diesel fuel
DZP
26,00
05.02.00
20.02.00
Diesel fuel
DZP -Â
36,00
05.02.00
15.02.00
Grease
Litol-24
680,00
05.02.00
20.02.00
Oil
Ì-12g
1050,00
20.02.00
25.02.00
Oil
Ì-6/10g
2000,00
20.02.00
25.02.00
Grease
Solidol Ñ
523,00
05.02.00
05.02.00
Oil
Tap-15Â
1600,00
20.02.00
25.02.00
1. With automatic calculation of totals (Data/Results):
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.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.