Without formulas, an Excel table would be little different from a table created in Word. Formulas allow you to perform very complex calculations. As soon as we change the data for calculations, the program immediately recalculates the result using formulas.

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

Filling Excel sheets with formulas

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

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

Task 2. In cell F2, enter only the “=” sign. Then click on cell D2, then click “+”, 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 convert them to large ones automatically.

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 with the incoming data for calculations. Therefore, sometimes it is convenient to use the hotkey combination CTRL+~ (the ~ key is located before the key with the number “1”) to switch to the formula viewing mode. Pressing this combination again will return you to the mode of displaying the results of formula calculations.

All formulas are recalculated dynamically. As soon as the contents of a cell with incoming data for calculations change, the formulas automatically recalculate them and immediately produce 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 address E2.



Copying formulas to a column

In cells F3 and F4, enter the same formula for calculating the payout that is in F2, but in a different effective copying method.

Task 1. Go to cell F3 and press CTRL+D. This way, the formula that is in the cell above (F2) will be automatically copied. 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 even more effective method 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 appearance to the plus sign "+". Then, while holding down the left mouse button, drag the cursor down another 2 cells so that the entire range F2:F4 is selected.

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

Formulas can be copied in three more ways:

• using tools on the strip;
• using a hotkey combination;
• by controlling the mouse cursor and pressing the “CTRL” key.

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

Complete the tasks:

Activate cell AT 2 and enter the text "First table" . Note! If the text you enter does not fit the width of the column, it will overlap adjacent cells if they are empty.

Activate cell F2 and enter the current date as 27.11.02 .

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

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

Activate cell C4 enter 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,

^ operation of exponentiation (symbol^ recruited inLatin register when pressing keysShift+6 on the main keyboard),

multiplication,

/ division,

+ addition,

 subtraction.

Expressions can be used when writinground brackets . For example, = 2^3-(34+10).

3. Cell addresses are entered into a 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 to cell is recorded result calculations, and The formula remains in the formula bar.

Complete the tasks:

Task 4. Editing data in a cell

To complete this task you need to know:

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

For partial replacementdata exists two ways:

1st method : double click on the cell (a text cursor will appear) and edit the data.

2nd method: Activate the cell, click in the formula bar (a text cursor will appear) and edit the data.

Complete the tasks:

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

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

Task 5. Entering notes

Pnote - Thisexplanatory textto cell.

Complete the 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 required text;

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 a note, move the mouse pointer over the cell (as a result, a window with the text of the note will appear).

Edit the note to do this:

• activate cell B2;

  select the menu item Insert ® Edit Note;

  change the note text (add group number);

  click outside the frame.

The lesson is devoted to how to solve task 7 of the Unified State Exam in computer science

The 7th topic - "Excel spreadsheets" - is characterized as tasks of a basic level of complexity, completion time - approximately 3 minutes, maximum score - 1

* Some page images are taken from the presentation materials of K. Polyakov

Types of links in cells

Formulas written in table cells can be relative, absolute And mixed.

Standard Excel Functions

In the Unified State Exam, the following standard functions are found in formulas:

• COUNT - the number of non-empty cells,
• SUM - amount,
• AVERAGE - average value,
• MIN - minimum value,
• MAX - maximum value

The range of cells is indicated everywhere as a function parameter: MIN(A2:A240)

Please note that when using the AVERAGE function, blank cells and text cells are not taken into account; for example, after entering a formula in C2 the value will appear 2 (empty ones are not taken into account A2):

Building charts

Solving Unified State Exam (USE) tasks in computer science

Let's look at how task 7 of the Unified State Exam in computer science is solved.

Chart analysis

7_1:

Which of the diagrams correctly reflects the ratio of the total number of participants (from all three regions) for each of the test subjects?

✍ Solution:

• A bar chart allows you to determine numerical values. For example, in Tatarstan in biology the number of participants 400 and so on. Let us use it to find the total number of participants from all regions in each subject. To do this, let’s calculate the values ​​of absolutely all columns in the diagram:

400 + 100 + 200 + 400 + 200 + 200 + 400 + 300 + 200 = 2400

using a pie chart, you can only determine the shares of individual components in the total amount: in our case, these are the shares of participants in various test subjects;

in order to figure out which pie chart is appropriate, we first calculate on our own the proportion of participants tested in individual subjects; To do this, from the bar chart we calculate the sum of participants in each subject and divide by the total number of participants already obtained in the first paragraph:

Biology: 1200/2400 = 0.5 = 50% History: 600/2400 = 0.25 = 25% Chemistry: 600/2400 = 0.25 = 25%

Now let's compare the obtained data with pie charts. The data corresponds to diagram number 1 .

Result: 1

We invite you to watch a detailed analysis of this 7th task on video:

7_2:

The diagram shows the number of test participants by subject in different regions of Russia.


Which of the diagrams correctly reflects the ratio of the number of history test participants in the regions?

✍ Solution:

Result: 2

For a detailed analysis of the task, watch the video:

Copying formulas

7_3: Unified State Examination in Informatics 2016, “Typical test tasks in Computer Science”, Krylova S.S., Churkina T.E. Option 2:

A fragment of a spreadsheet is given.

From cell A3 to cell C2
C2?

✍ Solution:

Result: 180

For an analysis of this 7th task, watch the video:

7_4: Unified State Exam in Computer Science 2017, “Typical test tasks in computer science”, Krylova S.S., Churkina T.E. Option 5:

A3 to cell E2 the formula was copied. When copying, the cell addresses automatically changed.
What is the numeric value of the formula in the cell? E2?

✍ Solution:

• Consider the formula in a cell A3: = $E$1*A2 . The dollar sign means absolute addressing: when you copy a formula, the letter or number next to the dollar will not change. That is, in our case the factor $E$1 it will remain in the formula when copied.
• Since copying is carried out to the cell E2, you need to calculate how many columns to the right the formula will move: by 5 columns (from A before E). Accordingly, in the factor A2 letter A will be replaced by E.
• Now let’s calculate how many lines up the formula will shift when copying: by one (c A 3 on E 2 ). Accordingly, in the factor A2 number 2 will be replaced by 1 .
• Let's get the formula and calculate the result: =$E$1*E1 = 1

Result: 1

7_5: Task 7. Demo version of the Unified State Exam 2018 computer science:

A fragment of a spreadsheet is given. From cell B3 to cell A4 the formula was copied. When copying, the cell addresses in the formula automatically changed.
What is the numeric value of the formula in the cell? A4?


Note: The $ sign denotes absolute addressing.

✍ Solution to task 7:

• The dollar sign $ means absolute addressing:
• The $ in front of the letter means the column is fixed: i.e. when copying a formula, the column name will not change;
• The $ before the number means the line is fixed: when copying the formula, the name of the line will not change.
• In our case, the highlighted letters and numbers will not change: = $C 2+D $3
• Copying the formula one column to the left means that the letter D(in D$3) must change to the one preceding it C. When you copy a formula down one line, the value 2 (in $C2) changes to 3 .
• We get the formula:

= $C3 + C$3

As a result, we have the result: 300 + 300 = 600

Result: 600

For a detailed solution to this 7th task from the demo version of the Unified State Exam 2018, watch the video:

What formula was written down?

7_6: 7 task of the Unified State Exam. Task 6 GVE grade 11 2018 (FIPI)

Kolya needs to build a table of formula values ​​using spreadsheets 5х–3у for values X And at from 2 before 5 . To do this, first in the ranges B1:E1 And A2:A5 he wrote down the numbers from 2 before 5 . Then into the cell AT 2 wrote down the formula (A2 – x value; B1 – y value), and then copied it to all cells of the range B2:E5. As a result, I received the table presented below.


What formula was written in the cell AT 2?

Note: The $ sign is used to indicate absolute addressing.

Options:
1)=5*$A$2–3*$B$1
2)=5*$A2–3*B$1
3)=5*A$2–3*$B1
4)=5*A2–3*$B$1

✍ Solution:

• Let's mentally imagine copying a cell with a formula separately horizontally and vertically.

Horizontally:

• Column reference in formula A should not change the letter when copying, which means you need to put a $ sign in front of it:

= 5 * $A

Whereas the column name B must change (to C, D, E) so that the numbers in the subtrahend change (3, 4, 5):

= 3 * B

Vertically:

The line number in the decrement must change so that the numbers in it increase (3, 4, 5). Whereas the lines in the subtrahend should not change: $A2. Thus, it is necessary to put a $ sign in front of the line number in the minuend: B$1

As a result, we get the formula: = 5 * $A2 – 3 * B$1, which corresponds to the number 2 .

Result: 2

Meaning of SUM or AVERAGE formula

7_7: Unified State Examination in computer science task 7 (example task P-00, Polyakov K.)

Behind

How the cell value will change C3, if after entering formulas you move the cell contents B2 V B3?
("+1" means an increase by 1 , "-1" means decrease by 1 ):

Options:
1) -2
2) -1
3) 0
4) +1

✍ Solution:

Let's analyze the spreadsheet data before moving:
• In a cell C2 there will be a number 4 , since the function CHECK Counts the number of non-empty cells in the specified range.
• In a cell C3 there will be a number 3 :

(1 + 2 + 2 + 6 + 4) / 5 = 3

Now let's see what happens after the move:

Moving the contents of a cell means that the cell B2 will be empty, and in the cell B3 a number will appear 6 .

Then the calculation of the formula in the cell C2 will change: the number of non-empty cells in the range A1:B2 will become equal 3 .

The value will change accordingly after calculating the cell formula C3: average of the contents of a range of cells A1:C2 will become equal:

(1 + 2 + 2 + 3) / 4 = 2

(don't forget that the function AVERAGE doesn't take into account empty cells, so cell B2 not taken into account).

Thus, the value after moving the formula has changed, decreasing by 1 . Correct answer 2

Result: 2

Detailed solution to the task in the video:

7_8:

In a spreadsheet, the value of the formula =AVERAGE(C2:C5) is 3 .

What is the value of the formula =SUM(C2:C4) if the cell value C5 equals 5 ?

✍ Solution:

• Function AVERAGE is designed to calculate the arithmetic mean of a specified range of cells. Those. in our case, the average value of cells C2, C3, C4, C5.
• The result of the function =AVERAGE(C2:C5) is given according to the condition, let’s substitute it into the formula:

(C2 + C3 + C4 + C5)/4 = 3

Let's accept an unknown amount for x and get the formula for calculating the average value:

x/4 = 3

We'll find x:

x = 3 * 4 = 12 -> C2 + C3 + C4 + C5 = 12

According to the task, you need to find =SUM(C2:C4) . Knowing the value in a cell C5, subtract it from the resulting amount and find the answer:

C2 + C3 + C4 = C2 + C3 + C4 + C5 - C5 = = 12 - 5 = 7

Result: 7

For a detailed solution, watch the video:

What number should be written in the cell

7_9: Unified State Examination in Informatics 2017, FIPI task option 7 (Krylov S.S., Churkina T.E.):

Given is a fragment of a spreadsheet:

A1 so that a chart based on cell values A2:C2, matched the picture? It is known that all cell values ​​from the considered range are non-negative.

✍ Solution:

• We have a pie chart that displays the shares of individual components in the total. Based on the image of the diagram, one can judge that, most likely, the values ​​in all cells of the formula should be equal (the sectors of the diagram are visually equal).
• A1 -> x:

A2: x + 4 - 3 = x + 1 B2: (5 * x + 5) / 5 C2: (x + 1)*(x - 4) = x 2 - 3 * x - 4

Since the sectors of the diagram are equal, we equate any two of the resulting expressions (for example, C2 = A2):

x²-3 ​​* x - 4 = x + 1 x²-4 * x - 5 = 0 x1.2 = (4±√16 - 4 * 1 * (-5)) / 2 = (4±6) / 2 x1 = 5, x2 = -1

According to the conditions of the task, the number should not be negative, so 5 suits us

Result: 5

For a more detailed analysis, we suggest watching the video solution to this 7 Unified State Exam assignments in computer science:

Let's look at another example of solving task 7 of the Unified State Exam in computer science:

7_10: Unified State Examination in Informatics 2017 task 7 FIPI option 15 (Krylov S.S., Churkina T.E.):

Given is a fragment of a spreadsheet:

What integer should be written in the cell C1, so that the chart constructed after performing calculations based on the values ​​of a range of cells A2:C2 Did it match the picture?
It is known that all values ​​of the range on which the diagram is constructed have the same sign.

✍ Solution:

• A pie chart displays the shares of individual parts in a total. In our case, the chart displays the results of formula calculations in cells A2:C2
• From the diagram you can judge that, most likely, the obtained values ​​in the formulas in all cells should be equal (the sectors of the diagram are visually equal).
• Let's get expressions from cell formulas by substituting C1 -> x:

A2: x + 2 B2: 8/2 = 4 C2: x * 2

Since the sectors of the diagram are equal, we equate two of the resulting expressions (for example, C2 = B2):

2 * x = 4 => x = 2

Our task is to select and enter into cell B2 the optimal formula, which specifies 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 of reducing the labor intensity of this operation is copying. The results of copying the contents of cell B2 to area B2:D4 are shown in Fig. 3.2.

Rice. 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 cell references 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, or mixed. So far we have used relative links. In fact, they specify the offset of the cell being referenced relative to the cell in which the reference is being made. For this reason, when copying, the address of the referenced cell is changed so that the offset remains the same. This is very useful property, and it is thanks to him that we have the opportunity to avoid self made. To help you better understand how relative links work in spreadsheets, here's an example. Let's assume that the following formula is entered into cell B2:

It points to the cell one cell above and to the left of cell B2 (that is, cell A1). After copying the contents of cell B2 to cell C4, the formula in cell C4 will no longer point to cell A1, but to cell B3 (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 on 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. Dashed lines in Fig. 3.3 indicate where the link is redirected when copying a formula from cell B2 to cells C4 and D6.

Rice. 3.3. Changing the 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 formula line:

NOTE.

In Fig. 3.3 at the bottom of the Excel window there is a Drawing panel, with which the arrows were drawn.

If you want your 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, since an absolute reference specifies a fixed position on the worksheet, which is located at the intersection of a given column and a given row.

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

Rice. 3.4. When you copy a formula with an absolute reference, the reference does not change

Ample opportunities are provided by mixed links. These are links with one dollar sign - before the column name or before the row number. For example, if cell B2 has the formula:

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

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

then only the row number will change, and the column name will remain the same. Therefore, here we're talking about about an absolute reference to a column and a relative reference 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. When you copy this formula into cells C4 and D6, you get different formulas.

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

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

The link type is changed cyclically, as a result of successive presses of the function key while the input cursor is in the link text. If, for example, cell B2 contains a link 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 formula with a mixed reference in the calculation area

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

=$A2*B$1

This formula can be safely copied into other cells - the calculations in the table will be carried out correctly. When copying in the first component of the formula, only the row number changes (=$A2, $AZ, $A4, etc.), and the reference to the column in which the elements of factor 1 are located remains constant. In the second component, on the contrary, the column name changes (=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 ​​the table, we will use the method of filling cells with the same data. The optimal sequence of actions in this case is:

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

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

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

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

Practical work prepared by: Ermolaeva Natalya Nikolaevna, computer science teacher, 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. The formula =$A1+$B$2-B1 is entered in cell B2. What formula will be obtained after copying this into cell D3?

4. In a spreadsheet, the value of the formula =SUM (C1:C3) is 12. What is the value of cell C4 if the value of the formula =AVERAGE (C1:C4) is 5?

5. At what values ​​of A2 in cell B6, where the formula is written =IF (AND(A2<10;А2>5);1;0), will the number 1 be displayed?

Test.

Option 2.

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

2. Find the value of cell C2:

3. The formula =A$1-$B$1-B3 is entered in cell A1. What formula will be obtained after copying this into cell C4?

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

5. At what values ​​of B1 in cell C4, where the formula is written =IF(AND(B1<7;B1>=12);1;0), will the number 0 be displayed?

Test.

Option 3.

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

2. Find the value of cell C2:

3. The formula =$A$1+B1-C$1 is entered in cell C1. What formula will be obtained after copying this into cell D5?

4. In a spreadsheet, the value of the formula =SUM(B1:B4) is 13. What is the value of cell B4 if the value of the formula =AVERAGE(B1:B3) is 3?

5. At what values ​​of A1 in cell B5, where the formula is written =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.The formula =$A2+$A$1-B1 is entered in cell A2. What formula will you get after copying this into cell F3?

4. In a spreadsheet, the value of the formula =SUM(D1:D3) is 7. What is the value of cell D3 if the value of the formula =AVERAGE(D1:D2) is 3?

5. At what values ​​of B2 in cell C4, where the formula is written =IF(AND(B2<7;B2>=11);1;0), will the number 0 be displayed?

ANSWERS:

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

(C1+C2+C3+C4)/4=5

1. A3 - relative; $С7 - mixed; $E$12 - absolute
2. A1=10 A2=6 B1=4 B2=5 C1=1 C2=4
3. =C$1-$B$1-D6
4. A1+A2 =7

1. $В$5 - absolute; A12 - relative; $E4- mixed
2. A1=8 A2=2 B1=4 B2=2 C1=6 C2=15
3. =$A$1+C5-D$1
4. В1+В2+В3+В4 =13

(B1+B2+B3)/3=3

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