☰

Introducing formulas into Excel cells for data calculations. Filling the Calculation Area Absolute and Relative References

Example 1.

In cell H5, you need to write the maximum of the two numbers contained in cells H2 and H4. The formula entered in cell H5 \u003d IF (H2\u003e H4; H2; H4) means that if the value of cell H2 is greater than the value of cell H4, then the value from H2 will be written in cell H5, otherwise - from H4.

As Expression 1 or Expression 2, you can write a nested IF function. The number of nested IFs must not exceed seven.

IF A(<логическое_выражение 1>;<значение_если_истина>;IF A(<логическое_выражение 2>;<значение_если_истина>;<значение_если_ложь>)).

In place of a logical expression, you can use one of the logical functions AND or OR.

Function format: AND (<логическое_выражение 1>;<логическое_выражение 2>;…)

OR(<логическое_выражение 1>;<логическое_выражение 2>;…).

Up to fifty logical expressions can be specified in parentheses. The AND function takes on the value "True" if all logical expressions are true at the same time. The OR function takes on the value "True" if at least one of the logical expressions is true.

Determine whether the number contained in cell H10 is included in the specified range (5; 10). Answer 1 (if the number belongs to the range) and 0 (if the number does not belong to the range) should be obtained in cell H12. The formula is entered into cell H12:

IF (AND (H10\u003e 5; H10<10);1;0)

To create this formula on the worksheet, you need to do the following:

1) Select cell H12.

2) Click on the Insert function button in the formula bar (see Fig. 2)

Click on the Formula tab and from the Function Library panel select Insert Function.

3) In the dialog box that opens (see Fig. 3), in the Category list, select Logical functions. In the Select a function list - IF A... Press OK.

4) In the next window, you need to set the function arguments IF A (see fig. 4)

5) The argument of this function is a logical function AND(this function is nested inside the function IF A). To call the function ANDyou must click on the arrow that opens the list of functions. The list is located in the name field in the formula bar (see Fig. 5).

6) If there is no function in the list, then select the Other functions item. The AND function refers to logical functions. After selecting it, a dialog box will open for specifying the arguments of this function (see Fig. 6).

7) Fill in the fields Boolean_expression logical expressions (see fig. 7)

8) Press the button OK in this window should not. You need to go back to setting the function arguments IF A... To do this, click on this function in the formula bar (see Figure 8).



9) Fill in the fields Value_if_trueand Value_if_falseaccording to the task (see Fig. 9) and press the OK button.

When using materials from this site - and placing a banner - MANDATORY !!!

Test work on the topic: "Formulas and functions in Excel"

Practical work was prepared by:Ermolaeva Natalya Nikolaevna, teacher of informatics, email: [email protected]

Test.

Option 1.

1. Determine the type of links: A $ 10; $ D $ 8; F5

2. Find the value of cell C2:

3. Cell B2 contains the formula \u003d $ A1 + $ B $ 2-B1 What formula will turn out after copying this into cell D3?

4. In the spreadsheet, the formula value \u003d SUM (C1: C3) is 12. What is the value in cell C4 if the formula value \u003d AVERAGE (C1: C4) is 5?

5.What are the values \u200b\u200bof A2 in cell B6, where the formula is written \u003d IF (AND (A2<10;А2>5); 1; 0), will the number 1 be displayed?

Test.

Option 2.

1. Determine the type of links: A3; $ C7; $ E $ 12.

2. Find the value of cell C2:

3. In cell A1 the formula is entered \u003d A $ 1- $ B $ 1-B3 What formula will be obtained after copying this into cell C4?

4. In the spreadsheet, the value of the formula \u003d SUM (A1: A2) is 7. What is the value of cell A3 if the value of the formula \u003d AVERAGE (A1: A3) is 3?

5.What are the values \u200b\u200bof B1 in cell C4, where the formula is written \u003d IF (AND (B1<7;B1>\u003d 12); 1; 0), is the number 0 displayed?

Test.

Option 3.

1. Define the type of links: $ B $ 5; A12; $ E4.

2. Find the value of cell C2:

3.In cell C1, the formula \u003d $ A $ 1 + B1-C $ 1 is entered. What formula will turn out after copying this into cell D5?

4. In the spreadsheet, the formula value \u003d SUM (B1: B4) is 13. What is the value in cell B4 if the formula value \u003d AVERAGE (B1: B3) is 3?

5.What are the values \u200b\u200bof A1 in cell B5, where the formula is written \u003d IF (AND (A1<=5;А1>2); 1; 0), will the number 1 be displayed?

Test.

Option 4.

1. Determine the type of links: D11; F $ 5; $ A $ 1.

2. Find the value of cell C2:

3. Cell A2 contains the formula \u003d $ A2 + $ A $ 1-B1. What formula will you get after copying this into cell F3?

4. In the spreadsheet, the formula value \u003d SUM (D1: D3) is 7. What is the value in cell D3 if the formula value \u003d AVERAGE (D1: D2) is 3?

5.What are the values \u200b\u200bof B2 in cell C4, where the formula is written \u003d IF (AND (B2<7;B2>\u003d 11); 1; 0), is the number 0 displayed?

ANSWERS:

  1. A $ 10- mixed $ D $ 8 - absolute F5- relative
  2. A1 \u003d 1 A2 \u003d 10 B1 \u003d 2 B2 \u003d 12 C1 \u003d 17 C2 \u003d 22
  3. \u003d $ A2 + $ B $ 2-D2
  4. C1 + C2 + C3 \u003d 12

(C1 + C2 + C3 + C4) / 4 \u003d 5

  1. A3 - relative; $ C7 - mixed; $ E $ 12- absolute
  2. A1 \u003d 10 A2 \u003d 6 B1 \u003d 4 B2 \u003d 5 C1 \u003d 1 C2 \u003d 4
  3. \u003d С $ 1- $ B $ 1-D6
  4. A1 + A2 \u003d 7
  1. $ B $ 5 - absolute; A12 - relative; $ E4- mixed
  2. A1 \u003d 8 A2 \u003d 2 B1 \u003d 4 B2 \u003d 2 C1 \u003d 6 C2 \u003d 15
  3. \u003d $ A $ 1 + C5-D $ 1
  4. B1 + B2 + B3 + B4 \u003d 13

(B1 + B2 + B3) / 3 \u003d 3

  1. D11 - relative; F $ 5 - mixed; $ A $ 1- absolute
  2. A1 \u003d 3 A2 \u003d 3 B1 \u003d 1 B2 \u003d 7 C1 \u003d 8 C2 \u003d 13
  3. \u003d $ A3 + $ A $ 1-G2
  4. D1 + D2 + D3 \u003d 7

Complete tasks:

    Activate the cell AT 2 and enter the text "First Table" . Note! If the entered text does not fit the column width, it will be superimposed on adjacent cells if they are empty.

    Activate the cell F2 and enter the current date as 27.11.02 .

    Activate the cell G2 and enter the current time as 10:32 .

    Activate the cell C3 insert the number 25 . Recommendation : Enter numbers and decimal point on the numeric keypad (indicatorNum lockmust be enabled).

    Activate the cell C4 insert the number 13,6 .

Task 4. Entering formulas

To complete this task you need to know:

1. Entering a formula begins with an equal sign= .

2. The formula may includenumbers, cell addresses and standard functions connected by signs of arithmetic operations:

% percent,

^ exponentiation operation (symbol^ recruited intolatin register when keys are pressedShift + 6 on the main keyboard),

multiplication,

/ division,

+ addition,

subtraction.

When writing an expression, you can useround brackets ... For example, \u003d 2 ^ 3- (34+10).

3. Cell addresses are entered into the formula or by typing from the keyboardin latin register orby clicking on the desired cell (the second option is more convenient).

    After finishing entering the formula in a cell recorded result calculations, and the formula remains in the formula bar.

Complete tasks:

Task 4. Editing data in a cell

To complete this task you need to know:

    For a complete replacementdata, it is enough to activate the cell and enter new data, while the old data is automatically deleted.

    For partial replacementdata exist two ways:

1st way : double click on the cell (text cursor appears) and edit the data.

2nd way: activate a cell, click in the formula bar (a text cursor appears) and edit the data.

Complete tasks:

    In a cell AT 2 change the text to " Table No. 1"And press the key Enter.

    In a cell E5 partially change the formula: = 25.5-C3 and press the key Enter.

Task 5. Entering Notes

Pnote - this is explanatory text to cell.

Complete tasks:

    Add to cell AT 2 note (your last name and first name), for this:

    activate cell B2;

    select the menu item Insert ® Note (as a result, a frame with a text cursor inside will appear near the cell);

    enter the text you want;

    click outside the frame (as a result, a red triangle will appear in the cell in the upper right corner - a sign that there is a note for this cell).

To view the note, move the mouse pointer over the cell (as a result, a window with the note text will appear).

Edit the note to do this:

  • activate cell B2;

    select the menu item Insert ® Change note;

    change the text of the note (add the group number);

    click outside the frame.

Our task is to choose and enter into cell B2 the optimal formula that sets the multiplication of the first factors. At first glance, it seems that this is the following formula:

A2 * B1

The operation of entering the formula must be done 100 times to fill all 100 cells of the calculation area. The simplest method to reduce the complexity of this operation is copying. The results of copying the contents of cell B2 to area B2: D4 are shown in Fig. 3.2.

Figure: 3.2.

After analyzing this figure, we can draw the following conclusion: for the remaining 99 cells, the method of copying the multiplication formula from cell B2 is not suitable, since the references to the cells in the formula have changed in such a way that the calculations are performed incorrectly. How to get out of this situation is described below.

Absolute and relative links

A cell reference can be relative, absolute, and mixed. Until now, we have used relative links. In fact, they set the offset of the referenced cell relative to the referenced cell. For this reason, when copying, the referenced cell address is changed so that the offset remains the same. This is a very useful property, and it is thanks to it that we are able to avoid manual work when copying formulas. Here's an example to help you understand how relative links work in spreadsheets. Suppose you entered the following formula in cell B2:

It points to a cell that is one cell above and to the left of cell B2 (that is, cell A1). After copying the contents of cell B2 into cell C 4, the formula in cell C4 will no longer point to cell A1, but to cell O3 (that is, to a cell located one cell above and to the left of C4). Thus, when copying the contents of a cell to any place in the worksheet, the formula located in it will refer not to a specific cell, but to a cell located at some distance from the cell with the formula. The dotted lines in Fig. 3.3 indicate where the link is forwarded when copying the formula from cell B2 to cells C4 and D6.


Figure: 3.3. Change reference when copying a formula with a relative reference to cell A1 from cell B2 to cells C4 and D6

If you copy this formula to any cell in row 1 or column A, then the following inscription will appear in the cell and in the formula bar:

NOTE.

In fig. 3.3 at the bottom of the Excel window is the Drawing panel, with the help of which the arrows were drawn.

If you want the formula to reference a specific cell, you must specify an absolute reference. After moving and copying such a formula, the cell reference does not change, because an absolute reference sets a fixed position on the worksheet, which is at the intersection of this column and this row.

This is shown in Fig. 3.4, where the formula with an absolute reference to cell A1 is copied from cell B2 to cells C4 and D6.


Figure: 3.4. Copying a formula with an absolute reference does not change the link

Mixed links provide ample opportunities. These are references with a single dollar sign before the column name or before the line number. For example, if cell B2 contains the formula:

then after copying it to any place in the worksheet, only the column name will change, and row 1 will always be present in the formula. Thus, in this case we have an absolute reference to a row and a relative one to a column.

Similarly, if you copy cell B2 to another location with the formula:

then only the row number changes, but the column name remains the same. Therefore, here we are talking about an absolute column reference and a relative one - to a row.

The figures below illustrate the use of an absolute row reference (Figure 3.6) and an absolute column reference (Figure 3.5) in cell B2. Copying this formula into cells C4 and D6 produces different formulas.


Figure: 3.5. The result of copying into cells C4 and D6 the formula from cell B2, which includes a relative row reference and an absolute column reference in cell A1


Figure: 3.6. The result of copying into cells C4 and D6 the formula from cell B2, which includes an absolute reference to the row and a relative reference to the column of cell A1

The link type is changed cyclically, as a result of successive presses of the function key while the cursor is in the link text. If, for example, cell B2 contains a reference to cell A1. then each time you press a key, its appearance in the formula bar will change: A1 - $ A $ 1 - A $ 1 - $ A1 - A1 - $ A $ 1, etc.

Entering a Mixed-Reference Formula in a Calculation Area

In order to quickly fill the calculation area with formulas, you must enter a formula with a mixed reference in cell B2:

\u003d $ A2 * B $ 1

This formula can be safely copied to other cells - calculations in the table will be performed correctly. When copying in the first component of the formula, only the row number (\u003d $ A2, $ AZ, $ A4, etc.) changes, and the reference to the column containing the elements of factor 1 remains constant. In the second component, on the contrary, the name of the column changes (\u003d C $ 1, D $ 1, E $ 1, etc.), and the reference to the row where the elements of factor 2 are located remains constant. To enter the multiplication formula into the calculation area of \u200b\u200bthe table, we will use the method of filling the cells with the same data. The following sequence of actions is optimal in this case:

  • Select the cell range B2: K11.
  • Enter the "\u003d" sign on the keyboard and press the key to create a link to cell A2.
  • Press the function key three times (create an absolute reference to column A).
  • Enter * (multiply) and press the key to link to cell B1.
  • Press the function key twice (create an absolute reference to line 1).
  • Press the key combination.

The multiplication table with formulas and calculation results is shown in Fig. 3.7 and 3.8.

Without formulas, an Excel spreadsheet would not be much different from tables created in Word. Formulas allow very complex calculations. As soon as we change the data for calculations, the program immediately recalculates the result using the formulas.

In some cases, to recalculate formulas, you should use a special tool, but we will consider this in a separate section devoted to calculations by formulas.

Filling Excel sheets with formulas

To perform calculations and calculations, you must write the formula into an Excel cell. In the table from the previous lesson (which is displayed below in the picture), you need to calculate the amount due to be paid, taking into account the 12% bonus to the monthly salary. How to enter formulas in cells in Excel to carry out such calculations?

Task 1. In cell F2, enter the following formula as follows: \u003d D2 + D2 * E2. After entering, press "Enter".

Task 2. In cell F2, enter only the "\u003d" sign. Then click on cell D2, then press "+", then click on D2 again, then enter "*", and click on cell E2. After pressing the "Enter" key, we get a similar result.


There are other ways to enter formulas, but in this situation, these two options are sufficient.

When entering formulas, you can use both large and small Latin letters. Excel will automatically convert them into large ones.

By default, cells with formulas display the result of their calculation. When viewing, you cannot immediately determine where the cells are with formulas, and where are with the input data for calculations. Therefore, sometimes it is convenient to use the CTRL + ~ hotkey combination (the ~ key is in front of the 1 key) to switch to the formulas view mode. Pressing this combination again will return to the mode of displaying the results of calculating formulas.

All formulas are recalculated dynamically. As soon as the content of the cell with the incoming data for calculations changes, the formulas automatically recalculate them and immediately give a new calculation result. For example, if you now change the monthly salary in cell D2 and press "Enter", then a new result will immediately appear at E2.



Copying formulas to a column

In cells F3 and F4, enter the same formula for calculating the payment as in F2, but in a different effective way of copying.

Task 1. Go to cell F3 and press the key combination CTRL + D. This will automatically copy the formula in the cell above (F2). So Excel allows you to copy the formula to the entire column. Do the same in cell F4.

Task 2. Delete the formulas in cells F3: F4 (select the range and press the delete key). Next, select the range of cells F2: F4. And press the key combination CTRL + D. As you can see, this is an even more efficient way to fill an entire column of cells with the formula from F2.

Task 3. Delete formulas in the range F3: F4. Make cell F2 active by moving the cursor to it. Next, move the mouse cursor over the point in the lower right corner of the rectangular cursor. The mouse cursor will change its appearance to a plus sign "+". Then hold down the left mouse button and move the cursor down 2 more cells so that the entire range F2: F4 is selected.


As soon as you release the left key, the formula will automatically be copied to each cell.

Formulas can be copied in three more ways:

  • using tools on the strip;
  • using a combination of hot keys;
  • by controlling the mouse cursor and pressing the "CTRL" key.

These methods are more convenient for certain situations, which we will look at in the next lessons.