Analysis of task 7 of the Unified State Exam 2017 in computer science from the demo version project. This is a task of a basic level of difficulty. Approximate time to complete the task is 3 minutes.
Content elements tested: knowledge of technology for processing information in spreadsheets and methods of visualizing data using charts and graphs. Content elements tested on the Unified State Exam: Mathematical processing of statistical data. Using tools for solving statistical and computational-graphical problems.
Task 7:
A fragment of a spreadsheet is given. A formula was copied from cell A2 to cell B3. When copying, the cell addresses in the formula automatically changed. Write down the numerical value of the formula in cell B3 in your answer.
Note: The $ sign denotes absolute addressing.
Answer: ________
Our formula =C$2+D$3 in a cell A2 contains two mixed links.
- in the first C$2- the address of line 2 does not change when copying
- in the second D$3- the address of line 3 does not change when copying
Our formula =C$2+D$3 was copied from a cell A2 to cell B3.
— moved one column to the right (increased by one column)
— moved one line down (increased by one line)
Therefore, after copying the formula =C$2+D$3, will take the form =D$2+E$3.
Evaluating this expression gives the following result: 70+5=75 .
Problems of type A7 in computer science imply knowledge technologies for processing information in spreadsheets. Even more specifically - absolute and relative addressing.
A7 Unified State Exam in computer science
As an example, consider solution to problem A7, consider solution A7 demo version of the Unified State Exam 2013 in computer science:
A fragment of a spreadsheet is given.
| A | B | C | D | |
| 1 | 1 | 2 | 3 | |
| 2 | 5 | 4 | = $A$2 + B$3 | |
| 3 | 6 | 7 | = A3 + B3 |
What will the value of cell D1 be equal to if you copy the formula from
cells C2?
Note: The $ sign denotes absolute addressing.
1)18 2)12 3)14 4)17
Solution:
Let's look at the contents of cell C2. It contains a formula that uses two cells, with cell references completely absolute($A$2) or partially absolute(B$3). When copying from cell C2 to cell D1, the address of cell $A$2 will remain the same, since its address is specified absolutely. The address of cell B$3 is set partially absolute - when copying, the row number will not change, but the column will change. When copying from column C to column D, the address of cell B$3 will change by 1 and become C$3. As a result, after copying, cell D1 will contain the formula = $A$2 + C$3. We know the contents of cell A2 - it is equal to 5. The contents of cell C3 need to be calculated: A3 + B3 = 6 + 7 = 13. We obtain that the value of cell D1 will be equal to 5 + 13 = 18. Correct answer 1.
As a reinforcement Let's solve problem A7 of the demo version of the Unified State Exam 2012 in computer science:
Cell B4 of the spreadsheet contains the formula = $C3*2. What will the formula look like after cell B4 is copied to cell B6?
Note: The $ sign is used to indicate absolute addressing.
1) = $C5 *4 2) = $C5 *2 3) = $C3 *4 4) = $C1 *2
Solution:
In the formula = $C3 * 2, the addressing of cell $C3 is partially absolute - when copying, only the row number changes (since it is preceded by a $ sign), and the column remains unchanged. When copying from cell B4 to cell B6, the line number will increase by 2, therefore the address of cell $C3 will turn into $C5. As a result, cell B6 will contain the formula = $C5 * 2. Correct answer 2.
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:
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
Similar articles
