Tasks A7 1. A fragment of a spreadsheet is given. What will the value of cell D1 be equal to if the formula from cell C2 is copied into it? Note: The $ sign denotes absolute addressing. 1) 18 2) 12 3) 14 4) 17 2. In a spreadsheet, the value of the formula =CP3HAH(A3:D3) is 5. What is the value of the formula =SUM(AZ:NW) if the value of cell D3 is 6? 1) 1 2) -1 3) 14 4) 4 3. 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 value of cell C5 is 5? 1) 1 2) 7 3) -4 4) 4 4. In a spreadsheet, the value of the formula =SUM(A5:D5) is 6. What is the value of the formula =AVERAGE(A5:C5) if the value of cell D5 is 9? 1) 1 2) -3 3) 3 4) -1 5. In a spreadsheet, the value of the formula =CUMM(D2:D5) is 10. What is the value of the formula =CP3HACH(D2:D4) if the value of cell D5 is -2 ? 1) 6 2) 2 3) 8 4) 4 6. In a spreadsheet, the value of the formula =AVERAGE(A4:C4) is 5. What is the value of the formula SUM(A4:D4) if the value of cell D4 is 6? 1) 1 2) 11 3) 16 4) 21 7. In a spreadsheet, the value of the formula =AVERAGE(E2:E4) is 3, what is the value of the formula =SUM(E2:E5) if the value of cell E5 is 5? 1) 11 2) 2 3) 8 4) 14 8. In the spreadsheet, the value of the formula =SUM(A7:C7) is 9. What is the value of the formula =CPZHACH(A7:D7). if the value of cell D7 is 3? 1) -6 2) 6 3) 3 4) 4 9. In a spreadsheet, the value of the formula =SUM(B2:B4) is 6. What is the value of the formula =AVERAGE(B2:B5) if the value of cell B5 is 14? 1) 5 2) 8 3) 10 4) 20 10. In the spreadsheet, the value of the formula =CPZHACH(A3:D4) is equal to 5. What is the value of the formula =AVERAGE(AZ:C4), if the value of the formula =CUMM(D3:D4 ) equals 4? 1) 1 2) 6 3) 3 4) 4 11. In the spreadsheet, the value of the formula =CPZHACH(C2:D5) is equal to 4. What is the value of the formula =CUMM(C5:D5), if the value of the formula =CPZHACH(C2:D4 ) equals 5? 1) -6 2) 2 3) -4 4) 4 12. In a spreadsheet, the value of the formula =SUM(D2:D4) is 15. What is the value of the formula =CUMM(Dl:D4) if the value of cell D1 is 5? 1) 5 2) 10 3) 20 4) 4 13. In a spreadsheet, the value of the formula =CUMM(D2:D4) is 16. What is the value of cell D1 if the value of the formula =CP3HACH(D1:D4) is 5? 1) 5 2) 11 3) 21 4) 4 14. In a spreadsheet, the value of the formula =CP3HAH(D1:D4) is 5. What is the value of cell D1 if the value of the formula =SUM(D2:D4) is 12? 1) 4 2) 6 3) 8 4) 17 15. In a spreadsheet, the value of the formula =CUMM(D2:D4) is 15. What is the value of the formula = AVERAGE(D1:D4) if the value of cell D1 is 5? 1) 5 2) 10 3) 20 4) 4 16. In a spreadsheet, the value of the formula =CP3HAH(D1:D4) is 8. What is the value of the formula =SUM(D2:D4) if the value of cell D1 is 11? 1) 7 2) 21 3) 32 4) 19 17. In a spreadsheet, the value of the formula =CP3HAH (D1:D4) is 8. What is the value of the formula =AVERAGE (D2:D4) if the value of cell D1 is 11? 1) 19 2) 21 3) 7 4) 32 18. In a spreadsheet, the value of the formula =SUM(B2:D2) is 16. What is the value of the formula =CP3HACH(A2:D2) if the value of cell A2 is 4? 1) 20 2) 12 3) 5 4) 4 19. In a spreadsheet, the value of the formula =CUMM(C2:D2) is 16. What is the value of cell B2 if the value of the formula =AVERAGE(B2:D2) is 7? 1) 5 2) 8 3) 21 4) 23 20. In a spreadsheet, the value of the formula =CUMM(B2:D2) is 15. What is the value of cell A2 if the value of the formula =CP3HACH(A2:D2) is 4? 1) 1 2) 5 3) 16 4) 20 21. The formula =B$2+$B3 is written in cell D3 of the spreadsheet. What will the formula look like after cell D3 is copied to cell E4? Note: The $ sign is used to indicate absolute addressing. 1) =C$2+$B4 2) =A$2+$B1 3) =B$3+$C3 4) =B$1+$A3 22. The formula =D$12+$D13 is written in cell F7 of the spreadsheet. What will the formula look like after cell F7 is copied to cell G8? Note: The $ sign is used to indicate absolute addressing. 1) =C$12+$D11 2) =D$11+$C13 3) =D$13+$E13 4) =E$12+$D14 23. The formula =B$2-$B3 is written in cell D3 of the spreadsheet. What form will the formula take after cell D3 is copied to cell C4? Note: The $ sign is used to indicate absolute addressing. 1) =C$2-$B4 2) =A$2-$B4 3) =B$1-$C4 4) =B$1-$B4 24. The formula =D$12-$D13 is written in cell F7 of the spreadsheet. What form will the formula take after cell F7 is copied to cell E8? Note: The $ sign is used to indicate absolute addressing. 1) =C$12-$C14 2) =D$12-$D13 3) =D$13-$D14 4) =C$12-$D14 25. In cell B1 the formula =2*$A1 is written. What form will the formula take after cell B1 is copied to cell C2? 1) =2*$B1 2) =2*$A2 3) =3*$A2 4) =3*$B2Н 26. In cell C2 the formula =$E$3+D2 is written. What form will the formula take after cell C2 is copied to cell B1? 1) =$E$3+C1 2) =$D$3+D2 3) =$E$3+E 4) =$F$4+D2 27. The formula =D1-$D2 is written in cell A1 of the spreadsheet. What form will the formula take after cell A1 is copied to cell B1? 1) =E1-$E2 2) =E1-$D2 3) =E2-$D2 4) =D1-$E2 28. In cell B2 the formula =$D$2+E2 is written. What will the formula look like if cell B2 is copied into cell A1? 1) =$D$2+E 2) =$D$2+C2 3) =$D$2+D2 4) =$D$2+D1 29. In cell NW of the spreadsheet the formula =$A$1+B1 is written. What will the formula look like if cell NW is copied into cell VZ? 1) =$A$1+A1 2) =$B$1+ВЗ 3) =$А$1+ВЗ 4) =$B$1+C1 30. The formula =F$12-$F13 is written in cell D6 of the spreadsheet. Note: The $ sign is used to indicate absolute addressing. What will the formula look like after cell D6 is copied to cell E7? 1) =G$12-$G13 2) =F$13-$F14 3) =F$13-$G13 4) =G$12-$F14
7.1 (ege.yandex.ru-1) A fragment of a spreadsheet is given:
Solution:
From the second equation we find: C1=3. Let's check that this value is also suitable for the first equation:
2*(4-3) = 2*1 =2
Answer: 3
7.2 (ege.yandex.ru-2) A fragment of a spreadsheet is given:
What integer must be written in cell C1 so that the pie chart drawn for the range A2:C2 matches the figure? It is known that all values of the range on which the diagram is constructed have the same sign.
Solution: The diagram is built using the values of three cells: A2, B2, C2. From the pie chart you can see that these values are related as 1:1:1. Since the values of cells A1 and B1 are known, let's fill the range A2:C2 with values instead of formulas (where possible):
Since the values in all cells of the range A2:C2 must be equal, then for the value C1 we obtain two equations:
From the second equation we find: C1=2. Let's check that this value is also suitable for the first equation:
Answer: 2
7.3 (ege.yandex.ru-3) A fragment of a spreadsheet is given:
What integer must be written in cell B1 so that the pie chart drawn for the range A2:C2 matches the figure? It is known that all values of the range on which the diagram is constructed have the same sign.
Solution 1: The diagram is built using the values of three cells: A2, B2, C2. From the pie chart you can see that these values correlate as 2:1:1, while it is not known which cell corresponds to which sector of the chart. Let's simplify the formulas, given that we know the value for cell A1:
From the formula in cell C2, you can see that the values of B2 and C2 are different. Therefore A2 = B2. This gives us the equation for B1:
3-B1 = (3*B1+3)/3
Let's solve the equation.
Answer: 1
Solution 2 (similar reasoning, a little shorter) : The diagram is built using the values of three cells: A2, B2, C2. From the pie chart you can see that these values correlate as 2:1:1, while it is not known which cell corresponds to which sector of the chart. From the formula in cell C2, you can see that the values of B2 and C2 are different. Therefore A2 = B2. Considering that C1 = A1+1 = 2+1 =3, we obtain the equation for B1
3-B1 = (3*B1+3)/3
Let's solve the equation.
Let's check ourselves - find the values in all the cells of the table
Answer: 1
7.4 (ege.yandex.ru-4) A fragment of a spreadsheet is given:
What integer must be written in cell A1 so that the pie chart drawn for the range A2:C2 matches the figure? It is known that all values of the range on which the diagram is constructed have the same sign.
Solution: The diagram is built using the values of three cells: A2, B2, C2. From the pie chart it can be seen that these values correlate as X: 1: 1, where X is approximately equal to 4. However, it is not known which cell corresponds to which sector of the diagram. Let's simplify the formulas in the table, taking into account that C1=2. We get:
Since B2 > C2, then A2=C2 must be satisfied. We get:
whence A1=7.
Answer: 7
7.5 (ege.yandex.ru-5) A fragment of a spreadsheet is given:
What integer must be written in cells B1 so that the pie chart drawn for the range A2:C2 matches the figure? It is known that all values of the range on which the diagram is constructed have the same sign.
Solution: The diagram is built using the values of three cells: A2, B2, C2. From the pie chart you can see that these values are in a 2:1:1 ratio. That is, one of the values (the larger one) differs from the others, and the two smaller values are equal to each other. In this case, it is not known which cell corresponds to which sector of the diagram. Let's simplify the formulas in the table, taking into account one hundred A1=4. We get:
Let's look at the formula =B2+4 in cell C2. You can see that the value in cell C2 is 4 greater than the value in cell B2. In other words, the values in cells B2 and C2 are different, with C2 > B2. This means C2 is the larger of the three numbers, and A2 = B2 is the two smaller ones. Moreover, the diagram shows that C2 is twice as large as A2 and B2. Therefore done:
This gives us a system of two equations to determine the values of B1 and C1:
B1-C1+4 = 2*(B1-C1)
From the 1st equation: B1 = 5*C1. Substitute into the 2nd equation:
5*C1 – C1 + 4 = 2*(5*C1-C1)
Therefore, B1=5. We do a check - we calculate the values for all cells:
For this task you can get 1 point on the Unified State Exam in 2020
Task 7 of the Unified State Exam in computer science is devoted to the analysis of diagrams and spreadsheets. When solving this test, you will have to, for example, determine the values of formulas based on certain parameters. A typical question for this option is: “If the arithmetic mean of four values in the table is 5, then what is the sum of the first three cells if the fourth cell contains the number 6 and there are no empty cells in the table.”
In other versions of task 7 of the Unified State Exam in computer science, the student will be asked to make a diagram using the given data. For example, you are given the compositions of two substances with instructions mass fractions their components. It is required to determine the ratio of these elements in an alloy of two substances and find the correct one among the presented diagrams. The ticket may also contain tasks to determine the total income of each family member over a period of time, the volume of the harvest for each variety of cucumbers, the number of schoolchildren participating in subjects in different regions of Russia, the increase in prices of some goods as a percentage relative to the beginning of the year .
For effective preparation in computer science, brief theoretical material for completing the task is given for each task. Over 10 training tasks with analysis and answers have been selected, developed based on the demo version of previous years.
There are no changes to the 2019 Unified State Exam KIM in computer science and ICT.
Areas in which knowledge will be tested:
- Programming;
- Algorithmization;
- ICT tools;
- Information activities;
- Information processes.
Necessary actions when preparation:
- Repetition of the theoretical course;
- Solution tests in computer science online;
- Knowledge of programming languages;
- Improve mathematics and mathematical logic;
- Use a wider range of literature – school curriculum is not enough for success on the Unified State Exam.
Exam structure
The duration of the exam is 3 hours 55 minutes (255 minutes), an hour and a half of which is recommended to be devoted to completing the tasks of the first part of the KIMs.
The tasks in the tickets are divided into blocks:
- Part 1- 23 tasks with short answer.
- Part 2- 4 tasks with detailed answers.
Of the proposed 23 tasks of the first part exam paper 12 refer to the basic level of knowledge testing, 10 – increased complexity, 1 – high level of complexity. Three problems of the second part high level complexity, one – increased.
When making a decision, it is necessary to record a detailed answer (free form).
In some tasks, the text of the condition is presented in five programming languages at once - for the convenience of students.
Points for computer science assignments
1 point - for 1-23 tasks
2 points - 25.
3 points - 24, 26.
4 points - 27.
Total: 35 points.
For admission to technical university intermediate level, you must score at least 62 points. To enter the capital's university, the number of points must correspond to 85-95.
To successfully write an examination paper, a clear knowledge of theory and constant practice in solving tasks.
Your formula for success
Work + work on mistakes + carefully read the question from beginning to end to avoid mistakes = maximum score on the Unified State Exam in computer science.
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 basic level complexity, execution 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)
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:
Result: 1
We suggest you take a look 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:
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: Task 7 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.
- Column reference in formula A should not change the letter when copying, which means you need to put a $ sign in front of it:
Horizontally:
Vertically:
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 :
Now let's see what happens after the move:
(don't forget that the function AVERAGE doesn't count empty cells, so cell B2 not taken into account).
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:
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:
Result: 5
For a more detailed analysis, we suggest watching a video of the solution to this 7th task of the Unified State Exam 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:
