Home Random Page


CATEGORIES:

BiologyChemistryConstructionCultureEcologyEconomyElectronicsFinanceGeographyHistoryInformaticsLawMathematicsMechanicsMedicineOtherPedagogyPhilosophyPhysicsPolicyPsychologySociologySportTourism






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.

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. 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.

3. Detail the summary table as a contribution.

 

Table 41 – Data on deposits

Surname   Type of deposit   Sum of deposit   Branch of bank   Note  
Akhmetov deposit Central  
Smagulov current North Temporary out of city
Amanov current West  
Satov current North Changed address
Batov current 21 758 North  
Katov deposit Central  
Ainurov current Central  
Nurov current North  
Akhmetov current 1 760 300 North Transferred to other office
Sibilyov current 77 200 000 West  
Amanov deposit 45 300 000 Central  
Katov deposit 50 959 000 Central  
Batov deposit Central  
Gullin current 34 500 000 North  
Aliev current 47 600 000 North  

 


Date: 2015-12-24; view: 1806


<== previous page | next page ==>
Laboratory work ¹ 11 | JOB 1. Use of graphic opportunities of MS Excel
doclecture.net - lectures - 2014-2024 year. Copyright infringement or personal data (0.008 sec.)