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

Table 8 – Productivity of vegetables, quintal/hectare (центнер/гектар)

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

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»

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

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

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

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

Date: 2015-12-24; view: 1846