OPTION 1
Case 1 subtask 1
– values in columns G and H (use the logical function "IF");
- the average value in cell G15.
Based on the calculations obtained, establish a correspondence between the following participants in the Olympiad and the number of points they scored:
Avilova O.S.
Vasilyeva K. A.
Denisov A. M.
| 1 | 19 |
| 2 | 43,4 |
| 3 | 44,8 |
| 24 |
Solution:
The values in column G are calculated using the formula: =IF(D4>=6,B4+C4+D4*1,2+E4+F4,SUM(B4:F4)) .
The values in column H are calculated using the formula: =IF(G4 .
The value in cell G15 is calculated using the formula: =AVERAGE(G4:G13) .
Thus, Avilova O.S. scored 19 points, Vasilyeva K.A. - 43.4 points, Denisov A.M. - 44.8 points.
Case 1 subtask 2
Students take 5 computer science tests. For each test, you can get from 0 to 10 points. If at least 6 points are received for test No. 3, then this result is increased by 20%. If the total number of points obtained during testing is less than 20, then this corresponds to a score of "2"; score "3" corresponds to the number of points from 20 to 29; score "4" - from 30 to 39; grade "5" - 40 points and above.
According to the original table, establish a correspondence between the names of students:
1) Serova T. V.,
2) Bondarenko D. A.,
3) Golubev V.V.
and colors of graphs built according to their estimates. 
"Extra"
Solution:
"Extra" the graph is blue.
Case 1 subtask 3
Students take 5 computer science tests. For each test, you can get from 0 to 10 points. If at least 6 points are received for test No. 3, then this result is increased by 20%. If the total number of points obtained during testing is less than 20, then this corresponds to a score of "2"; score "3" corresponds to the number of points from 20 to 29; score "4" - from 30 to 39; grade "5" - 40 points and above.
Sort in the spreadsheet by the Grade column in descending order. Determine the total number of students who received grades "3" and "2".
| 4 |
Solution:
After performing all the calculations and sorting by the "Score" column in descending order, the source table will look like this:
Thus, the total number of students who received grades "3" and "2" is 4.
OPTION 2
Case 1 subtask 1
Enter the original data into the spreadsheet (words can be abbreviated). 
Enter the formulas for calculation into the spreadsheet:
– values in columns G and H (in both cases, use the logical function "IF");
– average values in cells D15, E15, F15;
- the total score for all participants in cell G16.
According to the calculations obtained, establish a correspondence between the numbers of tasks and the average results of their solution:
task number 1
task number 2
task number 3
| 1 | 7,6 |
| 2 | 7,2 |
| 3 | 8,5 |
| 6,8 |
Solution:
Values in cells D15, E15, F15 are calculated according to the formulas:
=AVERAGE(D4:D13)
,
=AVERAGE(E4:E13)
,
=AVERAGE(F4:F13)
.
After performing all the calculations, the original table will take the form:
Case 1 subtask 2
The Programming Olympiad is evaluated by the sum of the points received for each of the three problems, plus 10% of the total for students under 10th grade. Participants who score 27 points or more receive a diploma of the 1st degree, 25-26 points - a diploma of the 2nd degree, 23-24 points - a diploma of the 3rd degree. Participants who score less than 23 points receive incentive certificates.
Analyze the diagram below according to the suggested answers. 
The diagram shown in the figure shows ...
Solution:
The option “distribution of participants by class of study” is not suitable, since in this case the pie chart should have two sectors of equal size (for grades 8 and 10), and not three.
The option “the contribution of points for each task to the overall result of the winner” is not suitable, because there were three tasks, so there should be three sectors on the diagram, not four.
The option "best results in each category" is not suitable, because all four results are different. In addition, to compare individual values, it is more expedient to use histograms.
Let's consider the remaining option "distribution of participants by categories of awardees." Diploma of the 1st degree was awarded to 3 participants, 2nd degree - 3, 3rd degree - 1, diplomas - 3.
So, the diagram shown in the figure shows the distribution of participants by categories of awardees.
Case 1 subtask 3
The Programming Olympiad is evaluated by the sum of the points received for each of the three problems, plus 10% of the total for students under 10th grade. Participants who score 27 points or more receive a diploma of the 1st degree, 25-26 points - a diploma of the 2nd degree, 23-24 points - a diploma of the 3rd degree. Participants who score less than 23 points receive incentive certificates.
The total result for all participants is ...
^
Round the result to one decimal place, such as 225.5.
| 241,2 |
Solution:
After performing the calculations, the initial table will take the form:
Thus, the total result for all participants is 241.2.
OPTION 3
Case 1 subtask 1
Enter the original data into the spreadsheet (words can be abbreviated). 
Enter the formulas for calculation into the spreadsheet:
– values in columns F and G (to calculate the values in column G, use the logical function "IF");
– average values in cells B14, C14, D14, E14;
According to the calculations obtained, establish a correspondence between the subjects and the average results of passing the exam for them:
mathematics
Informatics
Russian language
| 1 | 60,8 |
| 2 | 53,8 |
| 3 | 58,3 |
| 56,3 |
Solution:
The values in column F are calculated using the formula (for row 3): =SUM(B3:E3)
The values in column G are calculated using the formula (for row 3):
=IF(AND(B3>24,C3>28,D3>25,E3>34,F3>=240); "Enroll"; "refuse")
The values in cells B14, C14, D14, E14 are calculated according to the formulas:
=AVERAGE(B3:B12) ,
=AVERAGE(C3:C12) ,
=AVERAGE(D3:D12) ,
=AVERAGE(E3:E12) ,
After performing the calculations, the initial table will take the form:
Thus, the average result of passing the exam in mathematics is 60.8 points, in computer science - 53.8 points, in the Russian language - 58.3 points.
Case 1 subtask 2
Applicants take four exams in the form of the Unified State Examination. The message “Enroll” will be sent to those applicants who have:
- scores in each subject are above the “threshold” value (more than 24 points in mathematics, more than 28 points in physics, more than 25 points in computer science, more than 34 points in Russian language);
- the total score in all subjects is not less than 240.
The rest of the applicants will receive a "Reject" message.
According to the source table, establish a correspondence between the names of applicants: Chernova P., Khasanov R., Denisov V. - and the colors of the graphs built according to the points they received. 
"Extra" the graph has ______________ color.
Solution:
"Extra" the graph is red.
Case 1 subtask 3
Applicants take four exams in the form of the Unified State Examination. The message “Enroll” will be sent to those applicants who have:
- scores in each subject are above the “threshold” value (more than 24 points in mathematics, more than 28 points in physics, more than 25 points in computer science, more than 34 points in Russian language);
- the total score in all subjects is not less than 240.
The rest of the applicants will receive a "Reject" message.
Sort in the spreadsheet by the Score column in descending order. Determine the last enrolled applicant and its result.
In the answer field, enter the surname of this applicant and the amount of his points, separated by commas without spaces (for example, Ivanov, 35).
Solution:
After performing all the calculations and sorting by the column "Sum of points" in descending order, the original table will look like this: 
Thus, the last enrolled applicant will be V. Golubeva with a score of 246.
FunctionIF. Construction of graphs and charts
Workshop 6. Sorry for the fish...
Option 1
The stock of fish in the pond is estimated at 1200 tons. The annual increase in fish is 15%. The annual catch plan is 300 tons. The smallest stock of fish, below which the stock is no longer restored, is 400 tons. Build a table that calculates the number of fish in the pond for 15 years. Mark from what moment it is impossible to fulfill the given catching plan. Draw a graph of changes in the number of fish in the pond.
Option 2
The stock of fish in the pond is estimated at 1000 tons. The annual increase in fish is 13%. The annual catch plan is 180 tons. The smallest stock of fish, below which the stock is no longer restored, is 250 tons. Build a table that calculates the number of fish in the pond for 20 years. Mark from what moment it is impossible to fulfill the given catching plan. Draw a graph of changes in the number of fish in the pond.
Option 3
The stock of fish in the pond is estimated at 1800 tons. The annual increase in fish is 17%. The annual catch plan is 400 tons. The smallest stock of fish, below which the stock is no longer restored, is 500 tons. Build a table that calculates the number of fish in the pond for 16 years. Mark from what moment it is impossible to fulfill the given catching plan. Draw a graph of changes in the number of fish in the pond.
Programming Olympiad
Option 1. The programming Olympiad is evaluated by the sum of the points received for each of the three problems, plus 0.1 of the accumulated amount for students under 10th grade. 12 people took part in the Olympiad: 4 from the 8th grade, 3 from the 9th grade, 3 from the 10th grade and 2 from the 11th grade. The first task was worth a maximum of 10 points. The second - in 8, the third - in 12. Those who score more than 27 points receive a diploma of the 1st degree, more than 25 - the 2nd degree, more than 23 - the third degree. Create a table of participants and their results. Determine the diplomas of the participants. Build a chart by the sum of points scored for those who received a diploma of the 1st degree.
Option 2. The programming Olympiad is evaluated by the sum of the points received for each of the three problems, plus 0.1 of the accumulated amount for students under 10th grade. 14 people took part in the Olympiad: 3 from the 8th grade, 4 from the 9th grade, 4 from the 10th grade and 3 from the 11th grade. The first task was estimated at a maximum of 12 points. The second - at 10, the third - at 12. Those who score more than 30 points receive a diploma of the 1st degree, more than 27 - the 2nd degree, more than 25 - the third degree. Create a table of participants and their results. Determine the diplomas of the participants. Build a chart on the sum of points scored for those who received a diploma of the 2nd degree.
Option 3. The programming Olympiad is evaluated by the sum of the points received for each of the three problems, plus 0.1 of the accumulated amount for students under 10th grade. 10 people took part in the Olympiad: 2 from the 8th grade, 3 from the 9th grade, 3 from the 10th grade and 2 from the 11th grade. The first task was estimated at a maximum of 15 points. The second - at 12, the third - at 10. Those who score more than 34 points receive a diploma of the 1st degree, more than 30 - of the 2nd degree, more than 27 - of the third degree. Create a table of participants and their results. Determine the diplomas of the participants. Build a chart on the amount of points scored for those who received a diploma of the 3rd degree.
Exercise 1. An applicant is considered enrolled in a university if the sum of the grades he received in the exams is not less than passing score and a score in mathematics above three. Find the number of applicants who entered the university.
| A | B | C | D | E | F | |
| Checkpoint | score: | |||||
| Surname | Mathematics | Russian language | Literature | Sum | Enlisted | |
| Antonov | ||||||
| Vorobyov | ||||||
| Sinichkin | ||||||
| Voronin | ||||||
| Snegirev | ||||||
| Sokolova | ||||||
| Received: |
Comment. When finding the number of applicants admitted to the university, use the logical COUNTIF function. Find information about it yourself in the help system.
Task 2. Five subscribers call from the city A in town B. If a long-distance telephone call was made on weekends (Saturday, Sunday), or on holidays, or on weekdays from 20 pm to 8 am, then it is calculated at a reduced rate with a 50% discount, there is no benefit for the rest of the time . Calculate how much each of the five subscribers must pay for calls.
Comment. If the call is at a reduced rate, then the following condition must be met: Day of the week = "Saturday" OR Day of the week = "Sunday" OR Holiday = "yes" OR Talk start time >= 20 OR Talk start time<= 8.
Therefore, in cell G3 we enter the formula:
IF(OR (C3="Saturday", C3="Sunday", OT="Yes", E3>=20, E3<=8); $D$1*F3; $B$1*F3). Ссылки на ячейки D1 и В1 абсолютные, так как при копировании формул имена этих ячеек не должны меняться.
Task 3. The programming Olympiad is evaluated by the sum of the points received for each of the three problems, plus 0.1 of the accumulated amount for students under 10th grade. 12 people took part in the Olympiad: 4 from the 8th grade, 3 from the 9th grade, 3 from the 10th grade and 2 from the 11th grade. The first task was worth a maximum of 10 points. The second - in 8, the third - in 12. Those who score more than 27 points receive a diploma of the 1st degree, more than 25 - of the 2nd degree, more than 23 - of the third degree. Create a table of participants and their results. Determine the diplomas of the participants. Build a chart by the sum of points scored for those who received diplomas of the 1st, 2nd and 3rd degrees.
Task 4. The electricity supply company charges customers at the following rates: 0.6 rubles per 1 kWh for the first 200 kWh; 0.9 rubles per 1 kWh, if the consumption is over 200 kWh, but does not exceed 500 kWh; 1.2 rubles per 1 kW / h, if the consumption is over 500 kW / h. The company's services are used by 10 clients. Calculate the fee for each client. Determine how many customers consume more than 500 kWh.
Task 5. Carry out statistical data processing: Compose a variation series, build a histogram of frequencies, a polygon of relative frequencies. Find the range of variation, X cf, D( x) - dispersion, σ( x) - standard deviation, V- coefficient of variation, mode, median.
Option 1. Given the original table of the distribution of 30 applicants by the number of points they received in the entrance exams.
Option 2. In an experiment on memorizing a series of 10 two-digit numbers, the results of memorization after the first presentation for 35 subjects were the following values: 5, 3, 5, 5, 4, 3, 4, 3, 1, 4, 5, 4, 4, 3, 4 , 5, 3, 3, 4, 5, 4, 2, 3, 2, 2, 4, 3, 4, 3, 3, 4, 2, 4, 5.
Option 3. Among 38 students at the beginning of the school year, a reading test was carried out (the maximum number of points is 128). The following results were obtained: 90, 66, 106, 84, 105, 83, 104, 82, 97, 97, 59, 95, 78, 70, 47, 95, 100, 69, 44, 80, 75, 75, 51, 109, 89, 58, 59, 72, 74, 75, 81, 71, 68, 112, 62, 91, 93, 84.
Option 4. The teacher offered 125 students a control task consisting of 40 questions. The number of questions to which correct answers were obtained was chosen as the test score. Discrete frequency distribution is given in the table.
| Grade | ||||||||||||||
| Frequency |
Option 5. There are results (in cm) shown by a group of schoolchildren (70 people) in the test "High jump from a place" 35, 39, 30, 30, 27, 25, 45, 24, 30, 47, 28, 31, 41, 36 , 38, 40, 25, 31, 41, 25, 31, 39, 31, 36, 38, 36, 27, 29, 30, 31, 35, 31, 35, 41, 36, 40, 36, 31, 40 , 36, 51, 36, 38, 33, 29, 32, 35, 40, 42, 44, 44, 42, 44, 42, 44, 42, 37, 30, 30, 28, 36, 37, 45, 32 , 41, 32, 31, 30, 29, 26.
Option 6. 30 students of the 10th grade of the Novotoryalsk secondary school of the Republic of Mari El, during the test, flexion and extension of the arms at an emphasis, showed the following results (number of times): 39, 68, 34, 35, 38, 37, 34, 36, 35, 20, 18, 17, 20, 19, 16, 16, 17, 17, 17, 17, 16, 40, 25, 26, 30, 34.
Option 7. 20 pupils of the 9th grade of one of the schools of the Kirov region during the test run for 1000 meters showed the following results (min. sec): 3.53; 3.55; 3.55; 3.54; 3.50; 3.51; 3.50; 4.39; 4.40; 4.38; 4.42; 4.35; 4.41; 4.37; 4.38; 4.43; 4.46; 4.39; 4.40.
Task 6. Determine whether there are significant differences between the means of the two samples.
Option 1. The level of abstract thinking was studied in two 3rd grades of the same parallel among students of the same school. An appropriate test was developed and offered to students: 20 3-A students showed the following results (X): 19, 32, 33, 44, 38, 35, 39, 39, 44, 44, 24, 37, 29, 40, 42, 32, 48, 43, 33, 47, and 15 students of 3-B the following results (Y): 17, 7, 17, 28, 27, 31, 20, 17, 35, 43, 10, 28, 13, 43, 45.
Option 2. In the experiments of Nebylitsin V.D. According to one of the indicators (according to the rate of extinction of the conditioned reflex), the subjects formed 2 groups: persons with a predominance of excitation and persons balanced. With the same subjects, experiments were carried out to determine the a-index. For the excitable group (7 people), the following a-index values were obtained: 91, 56, 73, 51, 82, 46, 78. For the balanced group (15 people): 65, 72, 82, 95, 78, 84, 88, 81, 94, 70, 68, 83, 96, 92, 89.
Option 3. The representation of schoolchildren about various time intervals was studied, incl. and ideas about the minute interval. The subjects pressed the stopwatch button, started it, and when, in their opinion, a minute passed, they stopped it. The subjects could not look at the dial. The stopwatch readings of 20 grade III students were as follows (in sec.): 2.4; 3.9; 4.7; 9.1; 11.0; 12.7; 14.9; 16.0; 20.8; 25.3; 29.0; 30.6; 32.1; 32.7; 33.3; 36.3; 38.1; 43.5; 47.4; 53.8, and for 20 students of the 5th grade: 2.9; 12.5; 13.0; 13.5; 17.2; 17.7; 20.5; 22.7; 24.6; 26.3; 29.7; 30.7; 31.8; 33.8; 38.5; 42.8; 53.8; 55.9; 60.6; 76.1. Is there a significant difference between the ideas about the minute interval among students in grades III and V?
Task 7. Using statistical methods to study the relationship between quantities.
Option 1. Data on the duration of familiarization (in sec.) and playback time (in sec.) of the system of spatial lines are given.
Familiarization: 2.5; 1.9; 3.7; 2.0; 4.3; 2.4; 2.3; 4.8; 1.7; 3.2; 3.6; 2.3; 4.9; 1.8; 2.8; 4.0; 1.8; 3.0; 2.4; 4.5; 2.3; 3.4; 2.0; 2.5.
Perception: 3.2; 1.5; 2.4; 3.6; 4.5; 3.0; 3.1; 4.2; 2.9; 3.5; 4.0; 3.0; 4.3; 2.5; 2.9; 3.6; 2.5; 3.2; 2.9; 3.9; 2.7; 3.6; 2.4; 3.0.
Option 2. 25 students of the 9th grade of one of the schools in the city of Yoshkar-Ola during the test holding the body in a hang on the crossbar showed the following results (sec): 37, 69, 27, 46, 50, 46, 46, 45, 40, 35, 35, 35 , 36, 35, 36, 35, 35, 35, 35, 35, 35, 37, 38, 39, 45, and during the test, flexion and extension of the arms in support (number of times): 39, 68, 34, 35 , 38, 37, 34, 36, 35, 20, 18, 17, 20, 19, 16, 16, 17, 17, 17, 17, 16, 50, 41, 34, 35. Assess the tightness of the relationship between these two tests, build a dependency graph.
Option 3. Can it be argued that the opinions of the two judges who evaluated the performances of men in the compulsory exercises at figure skating competitions were consistent if they gave 9 participants the following marks:
Judge 1: 4.7, 4.9, 5.1, 5.6, 5.7, 5.3, 5.8, 5.9, 5.5
Judge 2: 4.3, 4.5, 5.3, 5.2, 5.5, 5.5, 5.9, 5.6, 5.7
Option 4. The data obtained at competitions at a distance of 15 km for two groups of skiers are presented: the first ones covered the distance with traditional moves, and the second - with skates. Compare the numerical characteristics of these two groups (if the data is ungrouped).
1 gr.: 37.02; 36.74; 37.82; 38.12; 36.91; 37.28; 38.21; 37.51; 37.56; 38.25
2 gr.: 35.81; 35.61; 35.02; 35.53; 35.84; 35.12; 26.12; 36.49; 35.62; 36.28.
