Home Random Page


CATEGORIES:

BiologyChemistryConstructionCultureEcologyEconomyElectronicsFinanceGeographyHistoryInformaticsLawMathematicsMechanicsMedicineOtherPedagogyPhilosophyPhysicsPolicyPsychologySociologySportTourism






Using the formulas in absolute and relative references. Linking worksheets.

 

Objective: Have an idea of the kinds of addressing; get the skills to use in addressing formulas and gain skills linking worksheets in organizing calculations.

Addressing, allowing automatic adjustment is called relative, not admitting it absolutely. Sometimes the adjustment of column names and/or line numbers of some cells is unacceptable. In these cases, before the column name and/or line number is not subject to adjustment, put the $ sign.

To switch between absolute and relative addressing, you can use the function key button [F4].

By default, the cell references in formulas as relative addressing. When it is used in the formulas of MS Excel remembers the location relative to the current cell.

Mixed addressing the $ symbol is placed just before the indicator that should not be changed, for example: B$4 or $C10. Then, when you copy the address, change one parameter and the other no.

In many cases, the original data and the calculations easier to make out in the form of not one but several related tables. You can link tables located on different worksheets, so that as a result of entering a value in a table changes the contents of another.

Each worksheet has a name that allows formatting and data addressing, select cells independently of the other sheets.

Worksheets (workbooks) may be associated with using cell references in another sheet (book). If the formula is required to specify the address of the cell located in another worksheet, proceed as follows: write the name of the sheet, put an exclamation point, and then indicate the address of the cell.

For example, Table3!A5, where Table3 the sheet name, the symbol ! marker, A5 cell address. Before the name of the column or row number allowed to use the $ symbol for absolute addressing.

When you rename a worksheet, all references to it are automatically corrected.

 

JOB 1.

1. Create the MS Excel document. Rename sheets: Sheet1 to Area, Sheet2 to Productivity.

2. Create a table modeled on Table 7, do not forget to enter in cell A1 of table name

3. For column names of the table set multiline format: Format Cells , click the Alignment tab, set the multiline text align text in the center

4. Cells in the last row should contain summation formula (use AutoSum ∑)

5. In cell F4, enter the formula =(B4:E4). Please note that the AVERAGE () function can be used from the library functions Master the MS Excel, which is indicated on the toolbar or fx entered directly from the keyboard.

6. In cell G4 (to determine the share), enter the formula = F4/$F$10*100

7. Copy the table on sheet Productivity. To do this, select the entire table and copy to the clipboard (Copy). Go to the next page and then click insert for cell A1(Paste)

8. During the inserted table, delete data, but do not touch the formula in column hectare () .

9. Make the necessary changes: change the name of the table, remove the third line, delete all and remove the column %



10. The table contains the values of the productivity data

11. Duplicate sheet Productivity using the worksheet Move/copy (/) a worksheet and name list Gross harvest ( )

12. The table makes the necessary changes: table name Gross harvest of vegetables, ton and enter the calculation formula:

Clean the values of cells B4:E9

Using transitions between the sheets enter form formula for calculating gross collection: values of sheet Area multiplied by the values of the sheet Productivity. To do this, place the cursor in cell B4 sheet Gross harvest write sign =, go to cell B4 of sheet Area, insert the multiplication sign *, go to cell B4 of sheet Productivity and complete the action by pressing Enter.

Copy the formula for the entire range of cells B4:E9

13. Install protection for all sheets. Save the file.

 

Table 7 Area harvested vegetables, hectare

 

Culture Kapal Aksu Tekeli Copa On average
hectare %
Cabbage    
Beet    
Carrots    
Onion    
Cucumbers    
Zucchini    
Total            

 

Table 8 Productivity of vegetables, quintal/hectare (/)

 

Culture Kapal Aksu Tekeli Copa On average
Cabbage  
Beet  
Carrots  
Onion  
Cucumbers  
Zucchini 91,5  

JOB 2.

1. Rename the worksheet to clean Data in tenge

2. Enter a name in cell A1 of Table 9; enter the raw data in columns A, B, C, D, E, F and G. In cell H4,

3. Enter the formula =G4-F4 (the difference in sales prices over two years), for a column I (%), enter the formula =G4/F4*100.

4. The financial result is determined as the difference between the selling price and cost data: =F4-D4 and E4=G4-E4.

5. Copy formulas in all cells of the table (drag the mouse over the marker in the lower right corner of the cell )

6. Duplicate sheet (on the label sheet, right-click and select Rename/copy, select Create a copy and rename the sheet Data in $

7. Table clear numerical data in columns, C, D, E, F, G. Then fill in the columns, C referring to the same columns in a sheet Data in tenge.

8. Then enter the calculation formula for columns D, E, F, G: values of columns D, E, F, G is the result of dividing the corresponding values in tenge rate $ sheet Data in tenge for example into D4 = Data in tenge!D4/Data in tenge!B4 and into F4 = Data in tenge!F4/Data in tenge!B4.

9. Copy the formula to other cells of table columns

10. Calculation results round up to the nearest whole number

Table 9 Economic indicators for the implementation of company DT

 

Months   Rates U.S. $ Total cost of flour, tenge Average selling price of flour, tenge Deviation Financial impact tenge
1 quarter 4 quarter 1 quarter 4 quarter 1 quarter 4 quarter (+,) % 1 quarter 4 quarter
January 84,4        
February 85,2 139,8        
March 86,7 141,2        
April 109,7 142,2        
May 118,7 142,3        
June 130,5 14,5        
July 132,2 142,7        
August 142,7        
September 135,1 142,7        
October 140,8 142,6        
November 139,6 143,5        
December 138,2 144,3        

 


Table 10 Economic indicators for the implementation of company DT

 

Months   Rates U.S. $ Total cost of flour, tenge Average selling price of flour, tenge Deviation Financial impact tenge
1 quarter 4 quarter 1 quarter 4 quarter 1 quarter 4 quarter (+,) % 1 quarter 4 quarter
January 84,4 -8
February 85,2 139,8                
March 86,7 141,2                
April 109,7 142,2                
May 118,7 142,3                
June 130,5 14,5                
July 132,2 142,7                
August 142,7                
September 135,1 142,7                
October 140,8 142,6                
November 139,6 143,5                
December 138,2 144,3                

JOB 3.

On a blank sheet, create a Price on dairy products (Table 11) and the sheet name PRICE

 

Table 11 Prices for dairy products SP DT

 

Product price, tenge
Milk Kefir Sour Cream Butter

 

1. The next sheet, create a table 12 Volume of product sales and name this sheet Report. Do not forget to write in cell A1 of the table name. Fill in the column Total using (B3:E3; Price!$A$3:$D$3), and copy this formula in cell F4: F5.

 

Table 12 Volume of products sales

 

Months Milk Kefir Sour Cream Butter Total, tg
June  
July  
August  

 

2. The next sheet, create a table similar to Table 13. Sheet name Income of sales. Fill in the table cell calculations using functions sum() (()) to determine the values in the column Total sale, rank() (()) to determine the values in the column Place in the share of income (in this function argument number is revenue store argument Link is the whole range of total revenue, E3:E8, which should have absolute addressing), Average() (()) to determine the values in the column Average revenue and column values Share, % is defined as the ratio of the income of each store on the total line.

 

Table 13 Income of sales, mln. tenge

 

Months June July August Total sale Place in the share of income Average revenue Share, (%)
April        
Zabava        
Sputnik        
Daulet        
Dobro        
Astana        
Total              

JOB 4.

Clean worksheet name Task_4

1. Create electronic analog Table 14 on this worksheet and perform a cost analysis of transport enterprises.

 

Table 14 Cost analysis of transport enterprises

 

  Income (million tenge) Expenditure (million tenge) costs 1 tenge income
base period reporting period base period reporting period base period reporting period
plan fact plan fact plan fact
       
       
       
       
       
       
       
       

 

2. Enter information about income from operation of transport enterprises

3. Determine the cost of one tenge in the baseline and reporting period according to plan and effectively.

4. Level costs tenge income is defined as the ratio (division). Cost to income ratio

5. Determine the maximum, minimum and average values of the column Costs 1 tenge income using the appropriate function.

6. Construct a histogram costs tenge income

7. Complete the table columns with the calculations of savings (cost overruns if the exponent is negative) of reducing or increasing the cost of the plan and actually using the following formulas:

(Cost Plan - Cost basis)*Income Plan or

(Fact Costs - Costs basis)*Revenues fact

8. Determine the maximum, minimum and average savings (deficit) on the plan and actually

 

JOB 5.

Create an electronic analog of the table 15

1. In cell A1 enter the table name

2. Enter the column names (the sign Σ denotes the sum and inserted command Insert-Symbol)

3. Fill cell calculations

4. To calculate the values of the string Total, use the SUM ()

5. To calculate the values in the column % use absolute and relative addressing

6. Value of the column Change is defined as the difference between the title of the column indices

7. Save the file

 

Table 15 Analysis of the current assets, tenge

 

Data   1 quarter 2 quarter 3 quarter Change
2 quarter to 1 quarter 3 quarter to 2 quarter
% % % % %
Materials              
Work in progress              
Finished goods              
Receivables              
Costs leading periods              
Cash              
Total                    

 

 


Date: 2015-12-24; view: 230


<== previous page | next page ==>
Anti-virus programs. | Laboratory work 4.
doclecture.net - lectures - 2014-2017 year. (0.066 sec.)