Student test results were entered into a spreadsheet.

	GIA, 2013					COMPUTER SCIENCE AND ICT
	Student testing data was entered into a spreadsheet. Below
	The first five rows of the table are shown.
						social science
						German
						Russian language
						social science
	Column A records the student's district; in column B – last name;
	in column C - favorite subject; Column D is the test score.
	In total, data for 1000 students was entered into the spreadsheet.
	Complete the task
	Open the file with this spreadsheet (file location is for you
	the exam organizers will inform you). Based on the data contained in
	this table, answer					two questions.
			1. How many students in the Eastern District (B) were selected as
				Favorite subject: computer science? Write down the answer to this question
				in cell H2 of the table.
			2. What is the average test score for North County students (C)?
				Write the answer to this question in cell H3 of the table with an accuracy of not
				less than two decimal places.
	Received
	exam organizers.

Criteria for assessing tasks with a detailed answer

Solution for OpenOffice.org Calc and Microsoft Excel

The first formula is used for writing functions in Russian, the second - for English.

In cell E2 we write the formula

IF(A2="B";C2;0) =IF(A2="B";C2;0)

Let's copy the formula to all cells of the range E3:E1001.

In cell H2, write the formula =COUNTIF(E2:E1001,"computer science") =COUNTIF(E2:E1001,"computer science")

In cell H3 we write the formula

SUMIF(A2:A1001,"C";D2:D1001)/COUNTIF(A2:A1001,"C") =SUMIF(A2:A1001,"C";D2:D1001)/COUNTIF(A2:A1001,"C" )

If the task was completed correctly and when performing the task, files specially prepared to check the completion of this task were used, then the following answers should be obtained:

for the first question: 10;
to the second question: 540.35
Assessment Guidelines
Both questions were answered correctly. Acceptable
writing the answer to other cells (other than those
indicated in the task) subject to the correctness of the received
answers. It is acceptable to record answers with greater accuracy
Only one of two questions was answered correctly
No correct answers were given to any of the questions.
Maximum score

When executing any of these commands, the Robot moves one cell, respectively: up, down ↓, left ←, right →. If the Robot receives a command to move through a wall, it will collapse.

The Robot also has a paint command, which paints the cell in which the Robot is currently located.

Four more commands are condition checking commands. These commands check if the path is clear for the Robot in each of four possible directions:

top free bottom free left free right free

These commands can be used in conjunction with an “if” condition, which looks like this:

if condition then sequence of commands

Here the condition is one of the condition checking commands.

Command Sequence– this is one or more any commands of orders.

For example, to move one cell to the right, if there is no wall on the right, and paint the cell, you can use the following algorithm:

if there is free space on the right, then paint everything to the right

In one condition, you can use several condition checking commands using logical connectives and , or , not , for example:

if (free on the right) and (not free on the bottom) then all to the right

To repeat a sequence of commands, you can use a “while” loop, which looks like this:

nts for now condition sequence of commands

© 2013 Federal Service for Supervision in Education and Science of the Russian Federation

Criteria for assessing tasks with a detailed answer

For example, to move to the right while it is possible, you can use the following algorithm:

nts while on the right freely to the right kts

Complete the task.

The endless field has horizontal and vertical walls. The right end of the horizontal wall is connected to the top end of the vertical wall. The lengths of the walls are unknown. Each wall has exactly one passage, the exact location of the passage and its width are unknown. The robot is in a cage located directly under the horizontal wall at its left end.

The figure shows one of the possible ways to position the walls and the Robot (the Robot is designated by the letter “P”).

Write an algorithm for the Robot that paints all the cells located directly below the horizontal wall and to the left of the vertical wall. The passages must remain unpainted. The robot must paint only cells that satisfy this condition. For example, for the picture above, the Robot must color in the following cells (see picture).

GIA, 2013

COMPUTER SCIENCE AND ICT

When executing the algorithm, the Robot must not be destroyed; the execution of the algorithm must be completed. The final location of the Robot can be arbitrary.

The algorithm must solve the problem for any feasible arrangement of walls and any location and size of passages inside the walls.

The algorithm can be executed in a formal executor environment or written in a text editor.

Save the algorithm in a text file. The name of the file and the directory for saving will be provided to you by the exam organizers.

(other wording of the answer is allowed that does not distort its meaning)
The performer's commands will be written in bold,
and comments explaining the algorithm and not being part of it -
in italics. The beginning of a comment will be denoted by the symbol “|”.
| We move to the right until we reach a passage in the horizontal wall, and
paint over the cells
nts not on top yet free
	paint over
	while the top is free
| We move to the right until we reach a vertical wall, and
paint over the cells
nts the right is free for now
	paint over
| We move down until we reach a passage in a vertical wall, and
paint over the cells
nts not free on the right yet
	paint over
nts the right is free for now
| Move down to the end of the vertical wall and paint over the cells
nts not free on the right yet
	paint over

© 2013 Federal Service for Supervision in Education and Science of the Russian Federation

Criteria for assessing tasks with a detailed answer

Other solutions are also possible.
It is allowed to use a different syntax for the performer's instructions,
more familiar to students.
It is possible to have some syntax errors, but not
Assessment Guidelines
The algorithm works correctly for all valid initial conditions.
For all valid input data, the following is true:
1) execution of the algorithm ends, and the Robot does not
breaks;
2) no more than 10 extra cells are painted over;
3) no more than 10 cells from among those left unpainted
which should have been painted over
The task was completed incorrectly, i.e. the conditions were not met
allowing you to give 1 or 2 points
	Maximum score

GIA, 2013

COMPUTER SCIENCE AND ICT

20.2 Write a program that, in a sequence of natural numbers, determines the maximum number that is a multiple of 5. The program receives as input the number of numbers in the sequence, and then

numbers. The sequence always contains a number that is a multiple of 5.

The number of numbers does not exceed 1000. The entered numbers do not exceed

The program should output one number - the maximum number that is a multiple of 5.

Example of the program:
Input data	Output

(Other wording of the answer is allowed without distorting its meaning) The solution is a program written in any programming language. An example of a correct solution written in Pascal:

var n,i,a,max: integer; begin

readln(n); max:= -1;

for i:= 1 to n do begin

if (a mod 5 = 0) and (a > max) end; then max:= a;

end. writeln(max)

Other solutions are also possible.

To check the correct operation of the program, you must use the following tests:

			Input data		Output

© 2013 Federal Service for Supervision in Education and Science of the Russian Federation

			Criteria for assessing tasks with a detailed answer
		Assessment Guidelines
	The correct solution has been proposed. The program works correctly
	on all the tests above.
	The program can be written in any language
programming
	The program gives an incorrect answer on one of the tests,
given above. For example, a solution that does not specify
condition for selecting numbers (a mod 5 = 0) will give an incorrect
answer to test No. 1
	The program gives incorrect answers on tests other than
	described in the criteria for 1 point
				Maximum score

Transcript

1 Spreadsheets: processing large amounts of data 1. The population of cities in different countries was entered into the spreadsheet. The figure shows the first rows of the resulting A B C 1 City Population Country 2 Asmun 91.40 Egypt 3 Wiener Neustadt 39.94 Austria 4 Luleburgaz 100.79 Turkey 5 Vöcklabruck 11.95 Austria Column A indicates the name of the city, column B the population (thousand people), in column C the name of the country. In total, data for 1000 cities was entered into the spreadsheet. 1. How many inhabitants are in the largest city in Egypt by population? Write the answer to this question (in thousands of people) in cell F2 2. How many cities in Egypt have more residents than the average population of the cities presented in the table? Write the answer to this question in cell F3. 2. The results of testing students in mathematics and physics were entered into a spreadsheet. The figure shows the first lines of the resulting 1 Pupil District Mathematics Physics 2 Shamshin Vladislav Maisky Grishin Boris Zarechny Ogorodnikov Nikolay Podgorny Bogdanov Victor Central Column A indicates the student's first and last name; in Column B, the city district in which the student's school is located; in columns C, D are the scores obtained in mathematics and physics. For each subject you could score from 0 to 100 points. Page 1

2 1. How many participants scored more than 160 points in total in two subjects? Write the answer to this question in cell G1 2. What is the minimum score in mathematics for students in the Zarechny district? Write the answer to this question in cell G2 3. The results of anonymous testing of students were entered into the spreadsheet. Below are the first lines of the resulting 1 participant number gender faculty points 2 participant 1 female chemical 21 3 participant 2 male mathematical 5 4 participant 3 female medical 15 5 participant 4 male mathematical 15 6 participant 5 male economic 24 Column A indicates the participant number; in column B gender; in column C one of four faculties: mathematics, medicine, chemistry, economics; in column D the number of points scored (from 5 to 25). Open the file with this spreadsheet (file location for you 1. How many boys outnumber the number of girls? Write the answer to this question in cell G2 2. What is the average score of boys? Write the answer to this question, accurate to two decimal places, in cell G3 4. The population of cities in different countries was entered into a spreadsheet.The figure shows the first rows of the resulting A B C 1 City Population Country 2 Asmun 91.40 Egypt 3 Wiener Neustadt 39.94 Austria 4 Luleburgaz 100.79 Turkey 5 Vöcklabruck 11.95 Austria The name of the city is indicated in column A, the population (thousands of people) in column B, the name of the country in column C. In total, data for 1000 cities was entered into the spreadsheet. Page 2

3 1. What is the total population in the Russian cities listed in the table? Write the answer to this question accurate to one decimal place (in thousands of people) in cell F2. 2. How many cities among those presented in the table have a population of at least a million people? Write the answer to this question in cell F3 5. The results of anonymous testing were entered into the spreadsheet. All participants scored points by completing tasks for the left and right hands. Below are the first lines of the resulting E 1 participant number gender status left hand right hand 2 participant 1 wife pensioner participant 2 husband student participant 3 husband pensioner participant 4 husband employee Column A indicates the participant number, column B gender, column C one of three statuses: pensioner, employee, student, in columns D, E testing indicators for the left and right hands. 1. How many female pensioners took part in the testing? Write the answer to this question in cell G2 2. What is the difference between the maximum and minimum indicators for the right hand? Write the answer to this question in cell G3 6. The results of anonymous testing of students were entered into the spreadsheet. Below are the first lines of the resulting 1 participant number gender faculty points 2 participant 1 female chemical 21 3 participant 2 male mathematical 5 Page 3

4 4 participant 3 female medical 15 5 participant 4 male mathematical 15 6 participant 5 male economic 24 Column A shows the participant number; in column B gender; in column C one of four faculties: mathematics, medicine, chemistry, economics; in column D the number of points scored (from 5 to 25). 1. How many girls took part in the testing? Write the answer to this question in cell G2 2. What is the difference between the maximum and minimum scores for students of the Faculty of Chemistry? Write the answer to this question in cell G3 7. The spreadsheet contains information about students’ employment outside of school hours. The figure shows the first lines of the resulting 1 Last name, first name Class Lesson Hours per week 2 Abylkasymova Lada 2 dancing 3 3 Agliulina Zarina 4 sports 5 4 Ayrapetyan Liliana 6 drawing 6 5 Akimova Yulia 1 music 4 Column A indicates the last and first name of the student; in column B the class in which the student is studying; in column C type of activity: dancing, sports, music, etc.; in column D the number of hours. 1. How many fifth graders study music? Write the answer to this question in cell G2 2. How many hours a week do athletes train on average? Write the answer to this question accurate to two decimal places in cell G3 Page 4

5 8. Information about students’ employment outside of school hours was entered into the spreadsheet. The figure shows the first lines of the resulting 1 Last name, first name Class Lesson Hours per week 2 Abylkasymova Lada 2 dancing 3 3 Agliulina Zarina 4 sports 5 4 Ayrapetyan Liliana 6 drawing 6 5 Akimova Yulia 1 music 4 Column A indicates the last and first name of the student; in column B the class in which the student is studying; in column C type of activity: dancing, sports, music, etc.; in column D the number of hours. 1. How many tenth graders do not play sports? Write the answer to this question in cell G2 2. How many hours per week do first-graders study on average? Write the answer to this question accurate to two decimal places in cell G3 9. The results of anonymous testing of students were entered into the spreadsheet. Below are the first lines of the resulting 1 participant number gender faculty points 2 participant 1 female chemical 21 3 participant 2 male mathematical 5 4 participant 3 female medical 15 5 participant 4 male mathematical 15 6 participant 5 male economic 24 Column A indicates the participant number; in column B gender; in column C one of four faculties: mathematics, medicine, chemistry, economics; in column D the number of points scored (from 5 to 25). Open the file with this spreadsheet (file location You Page 5

6 1. How many participants scored more than 20 points? Write the answer to this question in cell G2 2. How many points does the average score of students at the Faculty of Economics differ from the overall average score? Write the answer to this question accurate to two decimal places in cell G3 10. The results of anonymous testing were entered into the spreadsheet. All participants scored points by doing tasks for the left and right hands. Below are the first lines of the resulting E 1 participant number gender status left hand right hand 2 participant 1 wife pensioner participant 2 husband student participant 3 husband pensioner participant 4 husband employee Column A indicates the participant number, column B gender, column C one of three statuses: pensioner, employee, student, in columns D, E testing indicators for the left and right hands. 1. What is the average for the right hand in men? Write the answer to this question, accurate to one decimal place, in cell G2 2. How many women have a score for their left hand that is higher than their score for their right hand? Write the answer to this question in cell G3 11. The results of testing students in mathematics and physics were entered into the spreadsheet. The figure shows the first lines of the resulting 1 Pupil District Mathematics Physics 2 Shamshin Vladislav Maisky Grishin Boris Zarechny Ogorodnikov Nikolay Podgorny Bogdanov Victor Central Column A indicates the student's first and last name; in Column B, the city district in which the student's school is located; in columns C, D points, Page 6

7 received in mathematics and physics. For each subject you could score from 0 to 100 points. this table, complete the following task. 1. How many students in the Central region scored more than 70 points in each subject? Write the answer to this question in cell G1 2. What is the difference between the maximum and minimum scores in physics among all students? Write the answer to this question in cell G2 12. The results of student testing in mathematics and physics were entered into the spreadsheet. The figure shows the first lines of the resulting 1 Student District Mathematics Physics 2 Shamshin Vladislav Maisky Grishin Boris Zarechny Ogorodnikov Nikolay Podgorny Bogdanov Victor Central Column A indicates the student's last name and first name; in Column B, the city district in which the student's school is located; in columns C, D are the scores obtained in mathematics and physics. For each subject you could score from 0 to 100 points. 1. What is the lowest score for students in the Podgorny district? Write the answer to this question in cell G2 2. How many test participants scored the same number of points in mathematics and physics? Write the answer to this question in cell G3 Page 7

8 13. The results of anonymous testing were entered into a spreadsheet. All participants scored points by completing tasks for the left and right hands. Below are the first lines of the resulting E 1 participant number gender status left hand right hand 2 participant 1 wife pensioner participant 2 husband student participant 3 husband pensioner participant 4 husband employee Column A indicates the participant number, column B gender, column C one of three statuses: pensioner, employee, student, in columns D, E testing indicators for the left and right hands. 1. What is the average left hand score for students? Write the answer to this question accurate to two decimal places in cell G2 2. How many test participants have a modulo difference between the indicators for the left and right hands that is less than 5? Write the answer to this question in cell G3 Page 8

Task 1. Data on the calorie content of foods was entered into a spreadsheet. Below are the first five rows of the table: 1 Product Fats, g Proteins, g Carbohydrates, g Calories, Kcal 2 Peanuts 45.2 26.3 9.9 552

Computer science. 9th grade. Option IN933 1 Criteria for assessing tasks with a detailed answer 19 The results of testing students in mathematics and physics were entered into a spreadsheet. The figure shows the first

2. Functions in ET. 8. Spreadsheets GIA tasks 1. (2009) After the Olympiad in Informatics, the jury of the Olympiad entered the results of all participants in the Olympiad into a spreadsheet. The figure shows

Computer science. 9th grade. Demo version 6 (45 minutes) 1 Diagnostic thematic work 6 in preparation for the OGE in INFORMATION SCIENCE and ICT on the topic “Processing tables: selecting and sorting records” Instructions

The results of weather observations in the city of Zaraysk during the year were entered into the spreadsheet. The figure shows the first lines of the resulting 1 2 A B C D Date Temperature (C) Wind (direction) Precipitation

Task 1 Brothers Petya and Fedya Ivanov weighed 60 kg in the 7th grade. Every year Petya’s weight increased by 10 kg, and Fedya’s weight decreased by 5 kg. Calculate the weight of each brother by the end of 11th grade. Explanations.

Preparing Excel Complete tasks 1-6,8,11-13,15. Files under the corresponding numbers in the archive. 1. Task 19 763. Vanya Ivanov was planning to fly on vacation and wrote down the flight schedule from

Specification of examination materials for the state final exam in INFORMATION SCIENCE and ICT (written form) for students in educational programs of BASIC general education

9. Processing a large amount of data using spreadsheet or database tools OR(boolean_value, boolean_value2,...) Returns TRUE if at least one of the arguments

Computer science. 9th grade. Option Criteria for assessing tasks with a detailed answer 9 The results of testing students in the Russian language and mathematics were entered into a spreadsheet. The figure shows the first

FEDERAL INSTITUTE OF PEDAGOGICAL MEASUREMENTS State final certification for educational programs of basic general education in 2014 in the form of OGE Educational and methodological materials for preparation

Computer science. 9th grade. Option IN90503 1 Criteria for assessing tasks with a detailed answer 19 Data on the results of the Unified State Exam were entered into a spreadsheet. A B C D E 1 participant number Russian language mathematics

Computer science. 9th grade. Option IN90501 1 Criteria for assessing tasks with a detailed answer 19 Data on student testing was entered into a spreadsheet. Below are the first five rows of the table. A B

Computer science. 9th grade. Option IN90401 1 Criteria for assessing tasks with a detailed answer 19 Data on testing people of different ages were entered into a spreadsheet. A B C D E 1 participant number gender

Thematic diagnostic work in preparation for the OGE in INFORMATION SCIENCE and ICT on the topic “Processing tables: selecting and sorting records” March 24, 2015, grade 9 Option IN90701 Completed: Full name class Instructions

Training work in COMPUTER SCIENCE, grade 9 February 5, 2016 Option IN90301 Completed by: Full name class Instructions for completing the work The work consists of two parts, including 20 tasks. Part 1

Training work in COMPUTER SCIENCE grade 9 February 5, 2016 Option IN90303 Completed by: Full name class Instructions for completing the work The work consists of two parts, including 20 tasks. Part 1

Computer science. 9th grade. Option 1 1 Criteria for assessing tasks with a detailed answer 19 The results of the diagnostic work of 8th grade students in mathematics were entered into a spreadsheet. The figure shows

RESULTS OF THE USE-2014 71 school graduates were registered to take the Unified State Exam in the regional database (11A 24 students, 11B 23 students, 11B 24 students). STATISTICS ON THE NUMBER OF USE PARTICIPANTS BY SUBJECTS

RESULTS OF THE OGE-2016 To pass the OGE, 114 graduates of primary school were registered in the regional database (9A 31 students, 9B 31 students, 9B 25 students; 9G 27 students). Two students of grades 9B and 9G

Computer Science and ICT. Grade 9 2 Demonstration version of control measuring materials for conducting state (final) certification (in a new form) in INFORMATION SCIENCE and ICT students in 212,

Computer Science and ICT. Grade 9 2 State (final) certification of 212 (in a new form) in INFORMATION SCIENCE and ICT for students who have mastered basic general education programs Project Demonstration version

Training work in COMPUTER SCIENCE 9th grade January 31, 2018 Option IN90301 Completed by: Full name class Instructions for completing the work The work consists of two parts, including 20 tasks. Part 1

Option 10 1 An article typed on a computer contains 16 pages, each page has 40 lines, each line has 40 characters. Determine the information volume of the article in KB in one of the Unicode encodings,

GIA 9 COMPUTER SCIENCE AND ICT (1316 1/12) State (final) certification in COMPUTER SCIENCE and ICT Option 1316 Instructions for completing the work 2 are allocated for completing the examination work in computer science

Topic: Application of logical functions in spreadsheets (remotely). IF function This function is used when checking conditions for values ​​and formulas. It returns one value if given

Training work in COMPUTER SCIENCE, grade 9 September 18, 2015 Option IN90101 Completed by: Full name class Instructions for completing the work The work consists of two parts, including 20 tasks. Part

Option 18 1 (590) To receive an annual grade in history, a student was required to write a 16-page report. While performing this task on a computer, he typed text in Windows encoding. What is the amount of memory

Final test in Informatics and ICT Grade 9 Demo version Instructions for completing the work 45 minutes are allotted for completing the final test in Informatics. The work consists of 3 parts,

Laboratory work Creating a single-table database in Access TASK: Create a database - information about the students in your group. 1. Create a table to enter data about students in your stream. 2.

Grade 11. Computer science. Spreadsheet. Charts and graphs. Group: Determine a diagram by value Task 1: A B C D 1 3 4 2 =C1-B1 =B1-A2*2 =C1/2 =B1+B2 After performing the calculations, a

Option 19. 1 (591) In one of the editions of the book by L.H. Tolstoy “War and Peace” 1024 pages. What amount of memory (in MB) would this book take up if Lev Nikolaevich typed it on a computer in encoding

Diagnostic test in mathematics Student: Class: _ 1. How will the value 3.07 change if we write 0 between 3 and the decimal point? a) will increase by 10 times b) will not change c) will increase by 100 times d)

Practice 4 Relative and absolute references. Using standard functions to find the sum, arithmetic mean, find the minimum (maximum) values ​​Goal of work: Learn

Topic: “Graphical presentation of data.” Lesson objectives: educational: to create conditions for students to become familiar with the basic techniques for constructing graphs of functions in the Calc program; organize students' work

Option 15 1 An article typed on a computer contains 10 pages, each page has 32 lines, each line has 56 characters. In one representation of Unicode, each character is encoded in 2 bytes. Define

Option OGE 19 1 In one of the editions of the book by L.N. Tolstoy “War and Peace” 1024 pages. What amount of memory (in MB) would this book take up if Lev Nikolaevich typed it on a computer in KOI-8 encoding?

3. Requirements and conditions for their fulfillment for the assignment of I-III sports categories, youth sports categories. 81 Status Requirement: number of sports victories Sports discipline 3 Gender, age Youth

Task 1. Create a table for entering and calculating the results of medical examinations of company employees. 1. Create a table based on the sample. Save the file as Pr3_Familia.xlsx. Enter data in the Timesheet columns

Option 13 1 The information volume of the article is 48 KB. How many pages will an article take if one page of an electronic document contains 64 lines of 64 characters, and each character is represented by encoding?

Examination in 8th grade (testing) Scale for converting the total score for completing the examination work as a whole into a mark in mathematics Mark on a five-point scale Total score for the work as a whole “2”

Problem A. a + b (1 point) aplusb.in aplusb.out This problem requires you to calculate the sum of two given numbers. The input file consists of one line, which contains two numbers a and b (10 9 a 10 9, 10 9 b

1. Level of education of the population 17 1.1 Population by age groups (thousands of people) 1990 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 Total population 147662 148326 148295 147997

FEDERAL INSTITUTE OF PEDAGOGICAL MEASUREMENTS Methodological manual for organizing the execution of tasks by examinees on a computer as part of the exam in computer science and ICT of state (final) certification

PRACTICAL WORK “SOLVING PROBLEMS USING MS EXCEL” Purpose of the work: studying MS EXCEL add-ons, mastering techniques for solving problems by selecting a parameter and finding a solution. Exercise. 1. Form the task

Option 13. 1 (585) The information volume of the article is 48 KB. How many pages will an article take if one page of an electronic document contains 64 lines of 64 characters, and each character is represented by encoding?

Tasks for 6th grade students 1. Sasha trained his eye, estimating the length of a certain distance along the highway. On his first attempt, he estimated it at 120 steps. It turned out that after 120 steps he did not reach its end

Demonstration version of the final test in computer science, grade 9 Last name First name Class Date Instructions for completing the work The work consists of three parts, including 16 tasks. Part 1 contains

From experience working on individual educational trajectories of teaching mathematics 1 year Instructions Now you will receive assignments. Each task is a series of numbers. These numbers are within a certain range

Olympiad in Informatics for 9th grade (Developed by N.V. Raikova, an informatics teacher at MBOU Secondary School 139) 1. The purpose and objectives of this event: to identify and develop students’ abilities and interests in computer science,

2 5 August 2 0 1 7, FRIDAY 3 3 (1 0 2 7 9) Lyubimskaya district M a ss a s s a g a p p e a p p e r PRICE FREE NEWSPAPER BASED IN 1 9 1 9 at

Training work on MATHEMATICS class December 07 Option MA003 (basic level) Completed by: Full name class Instructions for completing the work Work on mathematics includes 0 tasks. For execution

Training work in MATHEMATICS class December 07 Option MA00 (basic level) Completed by: Full name class Instructions for completing the work Work in mathematics includes 0 tasks. For execution

Unified State Examination in mathematics. Option 8 Question 1 1 Find the value of the expression (5.4 7.3) 3.5. Question 2 2 0.21 10 4 Find the value of the expression. 0.7 10 3 Question 3 3 The salary of accountant Maria is 37,000 rubles. From this amount

FINAL TESTING Part A (task with a choice of answers) Part 1 (When completing tasks in this part (1 6), circle the number of the selected answer. If you chose the wrong number, cross it out and circle

67 Appendix 9 to the order of the Ministry of Sports of Russia dated April 04, 204. 207 As amended by the order of the Ministry of Sports of Russia dated 26.2.4. 082 Requirements and conditions for their implementation in the sport “wrestling” at 204-207

Some techniques for solving problems of type C7 (19) Unified State Examination Belogrudov Alexander Nikolaevich Associate Professor of the Department of Special Chapters of Mathematics UGATU Candidate of Physical and Mathematical Sciences 1. 30 different naturals are written on the board

B3 Presenting data in spreadsheets in the form of charts and graphs. Tasks for training: 1) Given a fragment of a spreadsheet: A B C D 1 3 4 2 =C1-B1 =B1-A2*2 =C1/2 =B1+B2 After performing the calculations

Use the cell addresses to reconstruct the word. Use the cell addresses to reconstruct the word. PRINTER The word means: 1) information transmission device; 2) output device; 3) information security system; 4) processing system

Option No. 53428 1. C 1 No. 59. Data on student testing was entered into the spreadsheet. Below are the first five rows of the table: A B 1 district last name C D subject score 2 C Pupil 1 social knowledge 246 3 B Pupil 2 German 530 4 Y Student 3 Russian 576 5 SV Student 4 social knowledge 304 Column A records the district in which the student studies; in column B - last name; in column C - favorite subject; Column D is the test score. In total, data for 1000 students was entered into the electronic spreadsheet. You will complete the task. Open the file with this spreadsheet (the exam organizers will tell you the location of the file). Based on the data contained in this table, answer two questions. 1. How many students in the North-Western District (NW) chose Russian as their favorite subject? The answer to this question was added to cell H2 of the table. 2. What is the average test score for students in West District (3)? Write the answer to this question in cell H3 of the table with an accuracy of at least two decimal places. task19.xls Explanation. task19.xls 1. Write the following formula in cell H2 =IF(A2="NW";C2;0) and copy it to the range H3:H1001. In this case, the name of the subject will be written in the cell of column H if the student is from the North-West District (NW) and “0” if this is not the case. Applying the operation =IF(H2="Russian language";1;0), we get a column (J) with ones and zeros. Next, we use the operation =SUM(J2:J1001). Let's get the number of students who consider Russian to be their favorite subject. There are 11 such people. 2. To answer the second question, we use the “IF” operation. Let's write the following expression in cell E2: =IF(A2="3";D2;0), as a result of applying this operation to the range of cells E2:E1001, we get a column in which only the scores of students in the Western District are recorded. Having summed up the values ​​in the cells, we get the sum of the students' points: 57,807. Next, we calculate the number of students in the Western District using the command =COUNTIF(A2:A1001,"Z"), we get: 108. Dividing the sum of points by the number of students, total: 535.25 - required average score. Answer: 1) 11; 2) 535.25. 2. C 1 No. 339. Information about cargo transportation was entered into the spreadsheet, auto ed ri ya m from October 1 to October 9. Below are the first five rows of the table: committed by some A B C D E F 1 Date Point of control Point of value Distance Gasoline consumption Cargo weight 2 October 1 Lipki Berezki 432 63 600 October 3 Orekhovo Dubki 121 17 540 October 4 Osinki Vya zovo 333 47 990 5 October 1 Lipki Vyazovo 384 54 860 Each row of the table contains a record of one shipment. Column A records the date of transport (from "October 1" to "October 9"); in column B - the name of the locality of departure of the transportation; in column C - the name of the locality of the transportation destination; in column D - the distance over which the transportation was carried out (in kilometers); in column E - gasoline consumption for the entire transportation (in liters); in column F - the mass of the transported cargo (in kilograms). In total, data on 370 shipments was entered into the spreadsheet in chronological order. You will complete the task. Based on the data contained in this table, answer two questions. 1. What was the total distance transported from October 1 to October 3? Write the answer to this question in cell H2 of the table. 2. What is the average weight of cargo during road transportation carried out from the city of Lipki? Write the answer to this question in cell H3 of the table with an accuracy of at least one digit after the decimal point. task19.xls Explanation. task19.xls 1. From the table you can see that the last record, dated October 3, has number 118. Then, by writing the formula y =CUMM(D2:D118) in cell H2, we can calculate the sum distance: 28468. 2. To answer the second question, write in cell G2 the formula =SUMIF(B2:B371;"Stickies";F2:F371). Thus, we get the total mass of the load. Applying the operation COUNTIF(B2:B371;"Lipki"), we obtain the number of cargo transportations made from the city of Lipki. Dividing the total weight by the number of cargo transportations, we get the average weight of cargo: 760.9. Other solutions are possible, such as sorting the rows by the value of column B and then specifying the correct blocks for the functions. 3. C 1 No. 835. 150 employees work in the workshop of the enterprise that produces cardboard cutting machine parts. Data about employees' work is recorded in an electronic spreadsheet. Below are the first five rows of the table. Each row of the table contains a record of one flight route. Column A contains the destination city, column B the destination country; Column C shows the approximate travel time. In total, the spreadsheet included data for 191 routes in alphabetical order. Each row of the table contains a record about one employee of the workshop. Column A contains the employee's last name; in column B - name; in column C - patronymic; in column D - the number of parts that the employee should have processed according to the plan; in column E - how many parts the employee processed. In total, data for 150 employees was entered into the spreadsheet. You will complete the task. Open the file containing this spreadsheet. Based on the data contained in this table, answer two questions about dew. 1. How many employees are scheduled to process more than 120 parts? Write the answer to this question in cell H2 of the table. 2. What percentage of employees did not fulfill the plan? The answer to this question with an accuracy of at least 2 characters after the entry is written in cell H3 of the table. task19.xls Explanation. Solution for OpenOffice.org Calc and for Microsoft Excel The first formula is used for Russian notation functions; the second is for English speakers. In cell H2, write a formula that determines how many employees should process more than 120 items per year according to the plan: =COUNTIF(D2:D151;">120") =COUNTIF(D2:D151;">120") For the answer to the second question in column G for each employee we write 1 if the employee produced no less parts than according to plan, and 0 in the opposite case. In cell G2 we write the formula y =IF(D2<=E2;1;0) =IF(D2<=E2;1;0) Ско​п и​ру​е м фор​му​л у во все ячей​ки диа​п а​зо​на G2:G151. Далее, чтобы опре​де​л ить ко​л и​че​ство со​труд​ни​ков, ко​то​рые вы​п ол​ни​л и план, за​п и​шем фор​му​л у в ячей​ку I1 =СЧЁТЕСЛИ(G2:G151;"0") =COUNTIF(G2:G151;"0") Для по​л у​че​ния окон​ча​тель​но​го от​ве​та в ячей​ку H3 за​п и​шем фор​му​л у: =I1*100/150 Воз​мож​ны и дру​гие ва​ри​а н​ты ре​ше​ния. Если задание выполнено правильно и при выполнении задания использовались файлы, специально под​го​тов​л ен​ные для про​вер​ки вы​п ол​не​ния дан​но​го за​да​ния, то долж​ны по​л у​чить​ся сле​ду​ю​щ ие от​ве​ты: на пер​вый во​п рос: 45; на вто​рой во​п рос: 79,33. 4. C 1 № 319. В электронную таблицу занесли численность населения городов разных стран. Ниже приведены пер​вые пять строк таб​л и​цы: A B C 1 Город Чис​л ен​ность на​се​л е​ния Стра​на 2 Асмун 91,40 Еги​п ет 3 ВинерНой​штадт 39,94 Ав​стрия 4 Лю​л е​б ур​газ 100,79 Тур​ция 5 Фёклаб​рук 11,95 Ав​стрия В столбце А указано название города; в столбце В - численность населения (тыс. чел.); в столбце С - название страны. Всего в электронную таблицу были занесены данные по 1000 городам. Порядок записей в таблице про​из​воль​ный. Вы​пол​ни​те за​да​ние. Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два во​п ро​са. 1. Сколько городов, представленных в таблице, имеют численность населения менее 100 тыс. человек? Ответ за​п и​ши​те в ячей​ку F2. 2. Чему равна средняя численность населения австрийских городов, представленных в таблице? Ответ на этот во​п рос с точ​но​стью не менее двух зна​ков после за​п я​той (в тыс. чел.) за​п и​ши​те в ячей​ку F3 таб​л и​цы. task19.xls По​яс​не​ние. task19.xls 1. Запишем в ячейку G2 следующую формулу =ЕСЛИ(B2<100;1;0) и скопируем ее в диапазон G3:G1001. В таком случае, в ячейку столбца G будет записываться единица, если город имеет численность населения менее 100 тыс. человек. Таким образом, получим столбец с единицами и нулями. Применив операцию =СУММ(G2:G1001), получим ко​л и​че​ство го​ро​дов, чис​л ен​ность на​се​л е​ния ко​то​рых менее 100 тыс. че​л о​век: 448. 2. Запишем в ячейку J2 следующее выражение: =СУМ​МЕС​ЛИ(C2:C1001;"Ав​с трия";B2:B1001), в результате получим сумму количества жителей только австрийских городов: 3679,179. Далее посчитаем количество австрийских городов, для этого применим операцию =СЧЁТЕСЛИ(C2:C1001;"Ав​с трия"), получим количество австрийских го​ро​дов: 72. Раз​де​л ив сум​мар​ную чис​л ен​ность на​се​л е​ния на ко​л и​че​ство го​ро​дов, по​л у​чим: 51,09. Ответ: 1) 448; 2) 51,09. 5. C 1 № 440. В электронную таблицу занесли результаты тестирования учащихся по географии и информатике. Вот пер​вые стро​ки по​л у​чив​шей​ся таб​л и​цы: A B C D 1 Уче​ник 2 Лишта​е в Ев​ге​ний 1 81 79 3 Будин Сер​гей 2 63 90 4 Хри​стич Анна 6 62 69 5 Ива​нов Да​ни​л а 7 63 74 4 50 66 1 60 50 6 Гло​то​ва Ана​ста​сия 7 Ле​щ ен​ко Вла​ди​слав Школа Гео​гра​фия Ин​фор​ма​ти​ка В столбце А указаны фамилия и имя учащегося; в столбце В - номер школы учащегося; в столбцах С, D - баллы, полученные, соответственно, по географии и информатике. По каждому предмету можно было набрать от 0 до 100 баллов. Всего в электронную таблицу были занесены данные по 272 учащимся. Порядок записей в таблице про​из​воль​ный. Вы​пол​ни​те за​да​ние. Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На ос​но​ва​нии дан​ных, со​дер​жа​щ их​ся в этой таб​л и​це, от​веть​те на два во​п ро​са. 1. Чему равна наибольшая сумма баллов по двум предметам среди учащихся школы № 5? Ответ на этот вопрос за​п и​ши​те в ячей​ку F4 таб​л и​цы. 2. Сколько процентов от общего числа участников составили ученики, получившие по информатике не менее 45 бал​л ов? Ответ с точ​но​стью до од​но​го знака после за​п я​той за​п и​ши​те в ячей​ку F5 таб​л и​цы. task19.xls По​яс​не​ние. task19.xls 1) В столбце Е для каждого учащегося вычислим сумму баллов по двум предметам, если это - ученик школы № 5. Для ученика другой школы ячейка будет содержать пустую строку. В ячейку Е2 запишем формулу =ЕСЛИ(В2=5; С2 +D2; "") Скопируем формулу во все ячейки диапазона ЕЗ:Е273. Для того чтобы найти наименьшую сумму, в ячейку F3 внесём фор​му​л у =МИН(Е2:Е273). 2) Для ответа на второй вопрос в дополнительной ячейке, например в НЗ, найдём количество участников, набравших по информатике не менее 45. Это можно сделать различными способами, в том числе при помощи функции =СЧЁТЕСЛИ(D2:D273; «>44"). Let us express the obtained value as a percentage of the total number of test participants. The result is written in cell F5: = NZ/272*100. Answer: 1) 78; 2) 84.9. 6. C 1 No. 219. Data on the calorie content of foods was entered into the spreadsheet. Below are the first five rows of the table: A B C D E 1 Product 2 Peanuts 45.2 26.3 9.9 552 3 Roasted peanuts 52 26 13.4 626 4 Peas from boiled 0.8 10.5 20.4 130 0.2 5 8.3 55 5 Green peas Fats, g Proteins, g Carbohydrates, g Calories ​nosity, Kcal Column A contains the product; in column B - fat content; in column C - protein content; in column D - the content of hydrocarbons and in column E - the calorific value of this product. You will complete the task. Open the file with this spreadsheet (the exam organizers will tell you the location of the file). Based on the data contained in this table, answer two questions. 1. How many foods in the table contain less than 7 g of fat and less than 7 g of protein? Write down the number of these products in cell H2 of table. 2. What is the average calorie content of foods containing more than 50 g of fat? Write the answer to this question in cell H3 of the table with an accuracy of at least two decimal places. task19.xls Explanation. task19.xls 1. Write the following formula in cell G2 =IF(AND(B2<7;C2<7);1;0) и скопируем ее в диапазон G3:G1001. В таком случае, в ячейку столбца G будет записываться единица, если продукт содержит меньше 7 г жиров и меньше 7 г бел​ков. При​ме​нив опе​ра​цию =СУММ(G2:G1001), по​л у​чим ответ: 450. 2. Запишем в ячейку J2 следующее выражение: =СУМ​МЕС​ЛИ(B2:B1001;">50";E2:E1001), as a result we obtain the sum of calories of products with a fat content of more than 50 g: 11 5742. Applying the operation =COUNTIF(B2:B1001;">50"), we obtain the number of products with a fat content of more than 50 g: 328. Dividing, we get the average value of products containing more than 50 g of fat: 352.87. Answer: 1) 450; 2 ) 352.87. 7. C 1 No. 299. The population of cities in different countries was entered into the spreadsheet. Below are the first five rows of the table: A B C 1 City Population e​niya Country 2 Asmun 91.40 Egypt 3 Wiener Neustadt 39.94 Austria 4 Lu​l e​b ur​gas 100.79 Turkey 5 Voeklabruk 11.95 Av​ stria Column A indicates the name of the city; Column B - population (thousands of people); Column C - the name of the country. In total, data for 1000 cities were entered into the spreadsheet. The order of entries in the table is arbitrary You will complete the assignment. Open the file with this spreadsheet. Based on the data contained in this spreadsheet, answer two questions. 1. How many cities of Belarus are represented in the table? Write the answer in cell F2. 2. What is the average population of cities whose inhabitants do not exceed 100 thousand people? Write the answer to this question with an accuracy of at least two decimal places (in thousand people) in cell F3 of the table. task19.xls Explanation. task19.xls 1. Write the following formula in cell G2 =IF(C2="Belo​russiya";1;0) and copy it to the range G3:G1001. In this case, one will be written in the cell of column G if the city is located in Belarus. Applying the operation =SUM(G2:G1001), we get the answer: 111. 2. Write the following expression in cell J2: =SUMMONT(B2:B1001;"<100";B2:B1001), в результате получим сумму количества жителей в городах, население которых не превышает 100 тыс. человек: 13 928,836. Применив операцию =СЧЁТЕСЛИ(B2:B1001;"<100"), получим количество городов, население которых не превышает 100 тыс. человек: 448. Разделив, получим среднюю численность населения городов, количество жителей которых не пре​вы​ша​е т 100 тыс. че​л о​век: 31,09. Ответ: 1) 111; 2) 31,09. 8. C 1 № 580. Среди учеников 5−11 классов проводили социологический опрос. Результаты занесли в электронную таб​л и​цу. Ниже при​ве​де​ны пер​вые пять строк таб​л и​цы: A B C D E 1 Фа​ми​л ия Имя Класс Лю​б и​мый пред​мет Оцен​ка за лю​б и​мый пред​мет 2 Алек​сан​дров Ар​те​мий 5 ин​фор​ма​ти​ка 4 3 Алек​сан​дро​ва Алек​сандра 6 ал​геб​ра 4 4 Анай Ангыр 10 гео​мет​рия 4 5 Анан​ки​на По​л и​на 8 рус​ский язык 4 6 Ан​дре​е в Яро​слав 7 ин​фор​ма​ти​ка 5 Каждая строка таблицы содержит запись об одном ученике. В столбце А записана фамилия, в столбце В - имя, в столб​це С - класс, в столб​це D - лю​б и​мый пред​мет, в столб​це Е - оцен​ка за лю​б и​мый пред​мет. Вы​пол​ни​те за​да​ние. Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На ос​но​ва​нии дан​ных, со​дер​жа​щ их​ся в этой таб​л и​це, от​веть​те на два во​п ро​са. 1. Сколь​ко уче​ни​ков любят ал​геб​ру? Ответ на этот во​п рос за​п и​ши​те в ячей​ку H2 таб​л и​цы. 2. Какой процент учеников 7 класса имеют оценку 3 за любимый предмет? Ответ на этот вопрос с точностью не менее 2 зна​ков после за​п я​той за​п и​ши​те в ячей​ку H3 таб​л и​цы. task19.xls По​яс​не​ние. task19.xls 1. В ячейку Н2 запишем формулу, которая определяет, сколько всего учеников любят информатику: СЧЁТЕСЛИ(D2:D219; "ал​геб​ра"). 2. Для ответа на второй вопрос в столбце G для каждого учащегося запишем его оценку за любимый предмет, если он учится в 7 классе, и 0 в обратном случае. В ячейку G2 запишем формулу =ЕСЛИ(С2=7;Е2;0). Скопируем формулу во все ячейки диапазона G2:G219. Далее, чтобы определить количество учащихся, у которых оценка за любимый предмет 3, запишем в ячейку I1 формулу =СЧЁТЕСЛИ(G2:G219;"3"). Сосчитаем количество учеников 7 класса. В ячейку I2 запишем формулу =СЧЁТЕСЛИ(С2:С219;7). Выразим полученное значение в процентах от общего числа учеников. Результат запишем в ячейку НЗ: =I1*100/I2 Возможны и другие варианты решения, например с ис​п оль​зо​ва​ни​е м сор​ти​ро​вок, филь​тров и т. д. Ответ: 1) 25; 2) 35,14. 9. C 1 № 620. В московской Библиотеке имени Некрасова в электронной таблице хранится список поэтов Се​реб​ря​но​го века. Ниже при​ве​де​ны пер​вые пять строк таб​л и​цы: A B C D E Имя От​че​ство Год рож​де​ния Год смер​ти Борис Ни​ко​л а​е ​вич 1899 1973 3 Аг​нив​цев Ни​ко​л ай Яко​вле​вич 1888 1932 4 Ада​мо​вич Ге​о р​гий Вик​то​ро​вич 1892 1972 1 Фа​ми​л ия 2 Ага​п ов 5 Аксёнов Иван Алек​сан​дро​вич 1884 1935 6 Амари Ми​ха​ил Оси​п о​вич 1882 1945 Каждая строка таблицы содержит запись об одном поэте. В столбце А записана фамилия, в столбце В - имя, в столбце С - отчество, в столбце D - год рождения, в столбце Е - год смерти. Всего в электронную таблицу были за​не​се​ны дан​ные по 150 по​этам Се​реб​ря​но​го века в ал​фа​вит​ном по​ряд​ке. Вы​пол​ни​те за​да​ние. Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На ос​но​ва​нии дан​ных, со​дер​жа​щ их​ся в этой таб​л и​це, от​веть​те на два во​п ро​са. 1. Опре​де​л и​те ко​л и​че​ство по​этов, ро​див​ших​ся в 1888 году. Ответ на этот во​п рос за​п и​ши​те в ячей​ку Н2 таб​л и​цы. 2. Определите в процентах, сколько поэтов, умерших позже 1930 года, носили имя Иван. Ответ на этот вопрос с точ​но​стью не менее 2 зна​ков после за​п я​той за​п и​ши​те в ячей​ку НЗ таб​л и​цы. task19.xls По​яс​не​ние. task19.xls 1. В ячейку Н2 запишем формулу, которая определяет количество поэтов, родившихся в 1888 году: =СЧЁТЕСЛИ(D2:D151; 1888). 2. Для от​ве​та на вто​рой во​п рос, в столб​це G для каж​до​го поэта за​п и​шем его имя, если он умер позже 1930 года, и 0 в другом случае. В ячейку G2 запишем формулу =ЕСЛИ(Е2> 1930; AT 2; 0). Let's copy the formula to all cells of the range G3:G151. Next, to determine the number of poets with the name Ivan, write the formula in cell I1: the formula =COUNTIF(G2:G151;"Ivan"). Let's count the number of poets who died after 1930. In cell I2, write the formula =COUNTIF(E2:E151;">1930"). Let us express the resulting value as a percentage of the total number of poets who died after 1930. We will write the result in cell NZ: =I1*100/I2 Other solutions are possible, for example, using sorting, filters and etc. Answer: 1) 5; 2) 3.51. 10. C 1 No. 640. In the medical office, the height and weight of students from grades 5 to 11 were measured. The results were entered into an electronic table. Below are the first five lines of the table: A B C D E 1 Last Name 2 Abashkina Elena 9 168 50 3 Aksenova Maria 9 183 71 4 Aleksan​drov Konstantin 7 170 68 5 Alekse​eva Anastasia 8 162 58 6 Aliyev Arif 7 171 57 Class Height Weight Each line of the table ​tsy keeps a record about one student. In column A there is a last name, in column B there is a first name; in column C - class; in column D - height, in column E - weight of students. In total, data on 211 students was entered into the spreadsheet in alphabetical order. You will complete the task. Open the file with this spreadsheet (the exam organizers will tell you the location of the file). Based on the data contained in this table, answer two questions. 1. What is the weight of the heaviest 10th grade student? The answer to this question was added to cell H2 of the table. 2. What percentage of 9th grade students are taller than 180? The answer to this question with an accuracy of at least 2 characters after the entry is entered into cell NZ of the table. task19.xls Explanation. task19.xls 1. In column F for each student, write down his weight if he is in 10th grade, and 0 otherwise. In cell F2 we write the formula: =IF(C2=10;E2;0) . Let's copy the formula to all cells of the range F2:F212. Thanks to the use of relative links in column F, lines 2−212 will record either the student's weight or 0. To find the answer, enter the formula y =MAKC(F2:F212) in cell H2. 2. To answer the second question, in column G for each student, write down his height if he is in 9th grade and 0 otherwise. In cell G2 we write the formula =IF(C2=9;D2;0). Let's copy the formula to all cells of the range G2:G212. Next, to determine: the number of students whose height is more than 180, we write in cell I1 the formula =COUNTIF(C2:C212,">180"). Let's count the number of 9th grade students. In cell I2 we write the formula =COUNTIF(C2:C212,9). Let us express the resulting value as a percentage of the total number of students. We will write the result in cell NZ: =I1*100/I2 Other solutions are possible, for example, using ​va​ni​e m sorting​vok, filters, etc. Answer: 1) 80; 2) 47.06. 11. C 1 No. 763. Vanya Ivanov was planning to fly on vacation and wrote down the flight schedule from Moscow to different cities in the form of a table. Below are the first five rows of the table. A B C 1 City Country Approximate travel time 2 Abakan RUSSIA 4 UAE 5 3 Abu Dhabi 4 Adler RUSSIA 2 5 Aqaba EGYPT 5 Each line The table contains a record of one flight route. Column A contains the destination city, column B the destination country; Column C shows the approximate travel time. In total, the spreadsheet included data for 191 routes in alphabetical order. You will complete the task. Open the file with this spreadsheet (the exam organizers will tell you the location of the file). Based on the data contained in this table, answer two questions. 1. How many cities can you fly to in less than 4 hours? Write the answer to this question in cell H2 of the table. 2. What percentage of all 5-hour flights are 5-hour flights to Egypt? The answer to this question needs to be written in cell H3 of the table exactly to one decimal place. task19.xls Explanation. 1. In cell H2, write a formula that determines how many cities you can fly to in less than 4 hours: =COUNTIF(C2:C192;"<4"). 2. Для ответа на второй вопрос, в столбце G для каждого перелёта запишем страну назначения, если время перелёта 5 часов и 0 в об​рат​ном слу​чае. В ячей​ку G2 за​п и​шем фор​му​л у: =ЕСЛИ(C2=5;B2;0). Скопируем формулу во все ячейки диапазона G3:G192. Посчитаем количество 5-часовых перелётов в Египет. Для этого за​п и​шем в ячей​ку I1 фор​му​л у: =СЧЁТЕСЛИ(G2:G192;"ЕГИ​ПЕТ"). По​счи​та​е м общее ко​л и​че​ство 5-ча​со​вых перелётов. В ячей​ку I2 за​п и​шем фор​му​л у: =СЧЁТЕСЛИ(C2:C192;5). Выразим полученное значение в процентах от общего числа всех 5-часовых перелётов. Результат запишем в ячей​ку H3: =I1*100/I2. До​п ус​ка​ют​ся и дру​гие спо​со​б ы ре​ше​ния, на​п ри​мер с ис​п оль​зо​ва​ни​е м сор​ти​ро​вок, филь​тров и т.д. Ответ на пер​вый во​п рос: 111. Ответ на вто​рой во​п рос: 12,5. 12. C 1 № 500. В электронную таблицу занесли результаты тестирования учащихся по физике и информатике. Вот пер​вые стро​ки по​л у​чив​шей​ся таб​л и​цы: A B C D 1 Уче​ник Округ 2 Бру​сов Ана​то​л ий За​п ад​ный 18 12 3 Ва​си​л ьев Алек​сандр Во​сточ​ный 56 66 4 Ер​ми​шин Роман Се​вер​ный 44 49 5 Мо​ни​ка​шви​л и Эду​а рд Цен​траль​ный 65 78 6 Круг​л ов Ни​ки​та Цен​траль​ный 57 67 7 Ти​то​ва Ана​ста​сия Се​вер​ный 54 63 Фи​зи​ка Ин​фор​ма​ти​ка В столбце А указаны фамилия и имя учащегося; в столбце В - округ учащегося; в столбцах С, D - баллы, полученные, соответственно, по физике и информатике. По каждому предмету можно было набрать от 0 до 100 баллов. Всего в электронную таблицу были занесены данные по 266 учащимся. Порядок записей в таблице про​из​воль​ный. Вы​пол​ни​те за​да​ние. Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На ос​но​ва​нии дан​ных, со​дер​жа​щ их​ся в этой таб​л и​це, от​веть​те на два во​п ро​са. 1. Чему равна средняя сумма баллов по двум предметам среди учащихся школ округа «Южный»? Ответ с точ​но​стью до двух зна​ков после за​п я​той за​п и​ши​те в ячей​ку G2 таб​л и​цы. 2. Сколько процентов от общего числа участников составили ученики школ округа «Западный»? Ответ с точ​но​стью до од​но​го знака после за​п я​той за​п и​ши​те в ячей​ку G4 таб​л и​цы. При​ме​ча​ние. При решении допускается использование любых возможностей электронных таблиц. Допускаются вы​чис​л е​ния при по​мо​щ и ручки и бу​ма​ги. Ис​п оль​зо​ва​ние каль​ку​л я​то​ров не до​п ус​ка​е т​ся. task19.xls По​яс​не​ние. task19.xls 1) В столбце Е для каждого учащегося вычислим сумму баллов по двум предметам, если это ученик школы округа «Южный». Для ученика из другого округа ячейка будет содержать пустую строку. В ячейку Е2 запишем формулу =ЕСЛИ(В2=«Южный»; С2+D2;"") Благодаря использованию относительных ссылок в столбце Е непустые значения строк 2−267 будут равны суммам баллов учеников школ округа «Южный». Для того чтобы найти среднее, в ячейку G2 внесём фор​му​л у =СРЗНАЧ(Е2:Е267). 2) Для ответа на второй вопрос в дополнительной ячейке, например в НЗ, найдём количество участников из школ округа «Западный». Это можно сделать различными способами, в том числе при помощи функции =СЧЁТЕСЛИ(В2:В267; "Западный"). Выразим полученное значение в процентах от общего числа участников те​сти​ро​ва​ния. Ре​зуль​тат за​п и​шем в ячей​ку G4: =НЗ/266*100. Ответ: 1) 117,15; 2) 15,4. 13. C 1 № 119. В электронную таблицу занесли данные о тестировании учеников. Ниже приведены первые пять строк таб​л и​цы: A B 1 округ фа​ми​л ия C D пред​мет балл 2 C Уче​ник 1 об​щ е​ст​во​зна​ние 246 3 В Уче​ник 2 не​мец​кий язык 530 4 Ю Уче​ник 3 рус​ский язык 576 5 СВ Уче​ник 4 об​щ е​ст​во​зна​ние 304 В столбце А записан округ, в котором учится ученик; в столбце В - фамилия; в столбце С - любимый предмет; в столб​це D - те​сто​вый балл. Всего в элек​трон​ную таб​л и​цу были за​не​се​ны дан​ные по 1000 уче​ни​кам. Вы​пол​ни​те за​да​ние. Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На ос​но​ва​нии дан​ных, со​дер​жа​щ их​ся в этой таб​л и​це, от​веть​те на два во​п ро​са. 1. Сколько учеников в Южном округе (Ю) выбрали в качестве любимого предмета английский язык? Ответ на этот во​п рос за​п и​ши​те в ячей​ку Н2 таб​л и​цы. 2. Каков средний тестовый балл у учеников Юго-Восточного округа (ЮВ)? Ответ на этот вопрос запишите в ячей​ку Н3 таб​л и​цы с точ​но​стью не менее двух зна​ков после за​п я​той. 79"). Let's express the obtained value as a percentage of the total number of test participants. Write the result in cell G4: = NZ/266*100. Answer: 1 ) 189; 2) 16.5. 15. C 1 No. 420. The test results of students in geography and computer science were entered into the spreadsheet. Here are the first lines of the student's table cy: A B C D 1 Student 2 Lishtae v Evgeniy 1 81 79 3 Budin Sergey 2 63 90 4 Khristich Anna 6 62 69 5 Ivanov Daniil a 7 63 74 4 50 66 1 60 50 6 Glo​tova Anastasia 7 Lesch enko Vladislav School Geography Informatics Column A indicates the last name and first name student; in column B - the student's school number; in columns C, D - points received, respectively, in geography and computer science. For each subject, you could score from 0 to 100 points. In total, data for 272 students was entered into the spreadsheet. The order of entries in the table is arbitrary. You will complete the assignment. Open the file with this spreadsheet (the exam organizers will tell you the location of the file). Based on the given If they are contained in this table, answer two questions. 1. What is the highest score in two subjects among students at school No. 4? Write the answer to this question in cell F4 of the table. 2. What percentage of the total number of participants were students who received at least 80 points in computer science? Send the answer exactly to one decimal place in cell F6 of the table. task19.xls Explanation. task19.xls 1) In column E, for each student, we calculate the sum of points in two subjects, if this is a student from school No. 4. For a student from another school, the cell will contain an empty line. In cell E2, write the formula =IF(B2=4; C2+D2; "") Copy the formula to all cells of the range E3:E273. Thanks to the use of relative references in column E, the non-empty values ​​of rows 2−273 will be equal to the sums of points of students at school No. 4. In order to find the largest sum, in cell F3 we enter the formula y =MAX(E2 :E273). 2) To answer the second question, in an additional cell, for example in NZ, we find the number of participants who scored at least 80 points in computer science. This can be done in various ways, including using the function =COUNTIF(D2:D273, ">79"). Let us express the obtained value as a percentage of the total number of test participants. The result is written in cell F5: = NZ/272*100. Answer: 1) 157; 2) 20.2. 16. C 1 No. 159. Data on the calorie content of foods was entered into the spreadsheet. Below are the first five rows of the table: A B C D E 1 Product 2 Peanuts 45.2 26.3 9.9 552 3 Roasted peanuts 52 26 13.4 626 4 Peas from boiled 0.8 10.5 20.4 130 0.2 5 8.3 55 5 Green peas Fats, g Proteins, g Carbohydrates, g Calories ​nosity, Kcal Column A contains the product; in column B - fat content; in column C - protein content; in column D - the content of hydrocarbons and in column E - the calorific value of this product. You will complete the task. Open the file with this spreadsheet (the exam organizers will tell you the location of the file). Based on the data contained in this table, answer two questions. 1. How many foods in the table contain less than 5 g of fat and less than 5 g of protein? Write down the number of these products in cell H2 of the table. 2. What is the average calorie content of 0g fat foods? Write the answer to this question in cell NZ of the table with an accuracy of at least two decimal places. task19.xls Explanation. task19.xls 1. Write the following formula in cell G2 =IF(AND(B2<5;C2<5);1;0) и скопируем ее в диапазон G3:G1001. В таком случае, в ячейку столбца G будет записываться единица, если продукт содержит меньше 5 г жиров и меньше 5 г бел​ков. При​ме​нив опе​ра​цию =СУММ(G2:G1001), по​л у​чим ответ: 394. 2. Запишем в ячейку J2 следующее выражение: =СУМ​МЕС​ЛИ(B2:B1001;0;E2:E1001), в результате получим сумму калорий с нулевым содержанием жиров: 10 628. Применив операцию =СЧЁТЕСЛИ(B2:B1001;0), получим количество продуктов с нулевым содержанием жиров: 113. Разделив, получим среднее значение продуктов с со​дер​жа​ни​е м жиров 0 г: 94,05. Ответ: 1) 394; 2) 94,05. 17. C 1 № 660. В медицинском кабинете измеряли рост и вес учеников с 5 по 11 классы. Результаты занесли в элек​трон​ную таб​л и​цу. Ниже при​ве​де​ны пер​вые пять строк таб​л и​цы: A B C D E 1 Фа​ми​л ия Имя 2 Абаш​ки​на Елена 9 168 50 3 Ак​се​но​ва Мария 9 183 71 4 Алек​сан​дров Кон​стан​тин 7 170 68 5 Алек​се​е ​ва Ана​ста​сия 8 162 58 6 Алиев Ариф 7 171 57 Класс Рост Вес Каж​дая стро​ка таб​л и​цы со​дер​жит за​п ись об одном уче​ни​ке. В столб​це А за​п и​са​на фа​ми​л ия, в столб​це В - имя; в столбце С - класс; в столбце D - рост, в столбце Е - вес учеников. Всего в электронную таблицу были занесены дан​ные по 211 уче​ни​кам в ал​фа​вит​ном по​ряд​ке. Вы​пол​ни​те за​да​ние. Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На ос​но​ва​нии дан​ных, со​дер​жа​щ их​ся в этой таб​л и​це, от​веть​те на два во​п ро​са. 1. Каков рост са​мо​го вы​со​ко​го уче​ни​ка 10 клас​са? Ответ на этот во​п рос за​п и​ши​те в ячей​ку Н2 таб​л и​цы. 2. Какой процент учеников 8 класса имеет вес больше 65? Ответ на этот вопрос с точностью не менее 2 знаков после за​п я​той за​п и​ши​те в ячей​ку НЗ таб​л и​цы. task19.xls По​яс​не​ние. task19.xls 1. В столбце F для каждого учащегося запишем его рост, если он учится в 10 классе и 0 в обратном случае. В ячейку F2 запишем формулу: =ЕСЛИ(С2=10;Е2;0). Скопируем формулу во все ячейки диапазона F2:F212. Благодаря использованию относительных ссылок в столбце F в строках 2−212 будут записаны либо рост ученика, либо 0. Для того чтобы найти ответ, в ячей​ку Н2 внесём фор​му​л у =MAKC(F2:F212). 2. Для ответа на второй вопрос в столбце G для каждого учащегося запишем его вес, если он учится в 8 классе и 0 в обратном случае. В ячейку G2 запишем формулу =ЕСЛИ(С2=8;Е2;0). Скопируем формулу во все ячейки диапазона G2:G212. Далее, чтобы определить количество учащихся, вес которых более 65 запишем в ячейку I1 формулу =СЧЁТЕСЛИ(G2:G212;">65"). Let's count the number of students in grade 8. In cell I2 we write the formula =COUNTIF(C2:C212;8). We express the resulting value as a percentage of the total number of students. We write the result in cell NZ: =I1*100/I2 Others are possible solution options, for example, using sorting, filters, etc. Answer: 1) 199; 2) 53.85. 18. C 1 No. 815. 150 employees work in the workshop of an enterprise that produces cardboard cutting machine parts. Dan ​The information about the employees is recorded in the electronic spreadsheet. ​The new five rows of the table. Each row of the table contains a record of one flight route. Column A contains the destination city, in column B - destination country; in column C - approximate travel time. In total, data for 191 routes in alphabetical order were entered into the spreadsheet. Each row of the table contains a record about one employee of the workshop. Column A contains the employee's last name; column B - first name; column C - patronymic; column D - the number of parts that the employee was supposed to process according to the plan; in column E - how many parts the employee processed. In total, data for 150 employees was entered into the spreadsheet. You will complete the task. Open the file containing this spreadsheet. Based on the data contained in this table, answer two questions about dew. 1. How many employees are scheduled to process more than 115 parts? Write the answer to this question in cell H2 of the table. 2. What percentage of employees completed the plan? The answer to this question with an accuracy of at least 2 characters after the entry is written in cell H3 of the table. task19.xls Explanation. Solution for OpenOffice.org Calc and for Microsoft Excel The first formula is used for Russian notation functions; the second is for English speakers. In cell H2, write a formula that determines how many employees should process more than 115 parts per year according to the plan: =COUNTIF(D2:D151;">115") =COUNTIF(D2:D151;">115") For the answer to the second question in column G for each employee we write 1 if the employee produced no less parts than according to plan, and 0 in the opposite case. In cell G2 we write the formula y =IF(D2<=E2;1;0) =IF(D2<=E2;1;0) Ско​п и​ру​е м фор​му​л у во все ячей​ки диа​п а​зо​на G2:G151. Далее, чтобы опре​де​л ить ко​л и​че​ство со​труд​ни​ков, ко​то​рые вы​п ол​ни​л и план, за​п и​шем фор​му​л у в ячей​ку I1 =СЧЁТЕСЛИ(G2:G151;"1") =COUNTIF(G2:G151;"1") Для по​л у​че​ния окон​ча​тель​но​го от​ве​та в ячей​ку H3 за​п и​шем фор​му​л у: =I1*100/150 Воз​мож​ны и дру​гие ва​ри​а н​ты ре​ше​ния. Если задание выполнено правильно и при выполнении задания использовались файлы, специально под​го​тов​л ен​ные для про​вер​ки вы​п ол​не​ния дан​но​го за​да​ния, то долж​ны по​л у​чить​ся сле​ду​ю​щ ие от​ве​ты: на пер​вый во​п рос: 74; на вто​рой во​п рос: 20,67. 19. C 1 № 19. В электронную таблицу занесли данные о тестировании учеников. Ниже приведены первые пять строк таб​л и​цы: A B 1 округ фа​ми​л ия C D пред​мет балл 2 C Уче​ник 1 об​щ е​ст​во​зна​ние 246 3 В Уче​ник 2 не​мец​кий язык 530 4 Ю Уче​ник 3 рус​ский язык 576 5 СВ Уче​ник 4 об​щ е​ст​во​зна​ние 304 В столбце А записан округ, в котором учится ученик; в столбце В - фамилия; в столбце С - любимый предмет; в столб​це D - те​сто​вый балл. Всего в элек​трон​ную таб​л и​цу были за​не​се​ны дан​ные по 1000 уче​ни​кам. Вы​пол​ни​те за​да​ние. Откройте файл с данной электронной таблицей. На основании данных, содержащихся в этой таблице, ответьте на два во​п ро​са. 1. Сколько учеников в Восточном округе (В) выбрали в качестве любимого предмета информатику? Ответ на этот во​п рос за​п и​ши​те в ячей​ку Н2 таб​л и​цы. 2. Каков средний тестовый балл у учеников Северного округа (С)? Ответ на этот вопрос запишите в ячейку НЗ таб​л и​цы с точ​но​стью не менее двух зна​ков после за​п я​той. task19.xls По​яс​не​ние. task19.xls 1. Запишем в ячейку H2 следующую формулу =ЕСЛИ(A2="В";C2;0) и скопируем ее в диапазон H3:H1001. В ячейку столбца Н будет записываться название предмета, если ученик из Восточного округа и «0», если это не так. Применив операцию =ЕСЛИ(H2="ин​фор​ма​ти​ка";1;0), получим столбец (J): с единицами и нулями. Далее, используем операцию =СУММ(J2:J1001). Получим количество учеников, которые считают своим любимым предметом ин​фор​ма​ти​ку. Таких 10 че​л о​век. 2. Для ответа на второй вопрос используем операцию «ЕСЛИ». Запишем в ячейку E2 следующее выражение: =ЕСЛИ(A2="С";D2;0), в результате применения данной операции к диапазону ячеек Е2:Е1001, получим столбец, в котором записаны баллы только учеников Северного округа. Сложив значения в ячейках, получим сумму баллов учеников: 56 737. Найдём количество учеников Северного округа с помощью команды =СЧЁТЕСЛИ(A2:A1001;"С"), по​л у​чим 105. Раз​де​л ив сумму бал​л ов на ко​л и​че​ство уче​ни​ков, по​л у​чим: 540,352 - ис​ко​мый сред​ний балл. Ответ: 1) 10; 2) 540,352. 20. C 1 № 680. В электронную таблицу занесли результаты сдачи нормативов по лёгкой атлетике среди учащихся 7−11 клас​сов. На ри​сун​ке при​ве​де​ны пер​вые стро​ки по​л у​чив​шей​ся таб​л и​цы: A 1 Фа​ми​л ия B C D Имя Пол Год рож​де​ния E F G Бег 1000 мет​ров, мин. Бег 30 мет​ров, сек. Пры​жок в длину с места, см 2 Пу​до​ва Ксе​ния ж 1997 4,47 4,12 209 3 Гу​се​ва Мария ж 1998 4,47 5,82 205 3 Лель​ко​ва На​деж​да ж 1999 5,03 5,24 198 5 Ев​ге​ний м 1999 3,32 5,87 210 Нелли ж 1998 5,57 5,32 182 Тиль 6 Ли​ма​ни​на В столбце А указана фамилия; в столбце В - имя; в столбце С - пол; в столбце D - год рождения; в столбце Е - результаты в беге на 1000 метров; в столбце F - результаты в беге на 30 метров; в столбце G - результаты по прыж​кам в длину с места. Всего в элек​трон​ную таб​л и​цу были за​не​се​ны дан​ные по 1000 уча​щ их​ся. Вы​пол​ни​те за​да​ние. Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На ос​но​ва​нии дан​ных, со​дер​жа​щ их​ся в этой таб​л и​це, от​веть​те на два во​п ро​са. 1. Сколько процентов участников пробежало дистанцию в 1000 м меньше, чем за 5 минут? Ответ запишите в ячей​ку L1 таб​л и​цы. 2. Найдите разницу в см с точностью до десятых между средним результатом у мальчиков и средним результатом у де​во​чек в прыж​ках в длину. Ответ на этот во​п рос за​п и​ши​те в ячей​ку L2 таб​л и​цы. task19.xls По​яс​не​ние. task19.xls 1.В ячейку L1 запишем формулу: СЧЁТЕСЛИ(Е2: Е1001;" <5 ")/1000. Таким образом, мы найдём отношение количества тех, кто пробежал меньше, чем за 5 минут, к общему числу участников. Теперь сделаем формат ячейки L1 про​цент​ным. Для того чтобы найти ответ, в ячей​ку Н2 внесём фор​му​л у =MAKC(F2:F212). Ответ: 199. 2. Для ответа на второй вопрос в столбце Н выпишем все результаты девочек по прыжкам в длину при помощи фор​му​л ы: =ЕСЛИ(C2="ж";G2;""). Скопируем формулу во все ячейки диапазона НЗ:Н1001. Найдём среднее значение по прыжкам в длину среди девочек по формуле =СРЗНАЧ(Н2:Н1001) запишем его в ячейку J1. Аналогично поступим с мальчиками. Результаты мальчиков выпишем в столбце I =ЕСЛИ(C2="м";G2;""). Скопируем формулу во все ячейки диапазона I3:I1001. Среднее значение среди мальчиков запишем в J2:=СРЗНАЧ(I2:I1001). В ячейку L2 запишем раз​ни​цу между J2 и J1 =J2-J1 За​да​дим фор​мат ячей​ки L2 чис​л о​вой, число де​ся​тич​ных зна​ков - 1. Возможны и другие варианты решения, например с использованием сортировок, фильтров и. т. д. Если задание вы​п ол​не​но пра​виль​но, то долж​ны по​л у​чить​ся сле​ду​ю​щ ие от​ве​ты: на пер​вый во​п рос - 59; на вто​рой во​п рос - 6,4.

Task 19 No. 19. Student testing data was entered into a spreadsheet. Below are the first five rows of the table:

	district	surname	item	point
		Student 1	social science
		Student 2	German
		Student 3	Russian language
	NE	Student 4	social science

Column A records the student's district; in column B - last name; in column C - favorite subject; Column D is the test score. In total, data for 1000 students was entered into the spreadsheet.

Complete the task.

Open the file containing this spreadsheet. Based on the data contained in this table, answer two questions.

1. How many students in the Eastern District (B) chose computer science as their favorite subject? Write the answer to this question in cell H2 of the table.

2. What is the average test score for North County students (C)? Write the answer to this question in cell NZ of the table with an accuracy of at least two decimal places.

Task 19 No. 39.

1. How many students in North-Eastern District (NE) chose mathematics as their favorite subject? Write the answer to this question in cell H2 of the table.

2. What is the average test score for students in the Southern District (S)? Write the answer to this question in cell H3 of the table with an accuracy of at least two decimal places.

Task 19 No. 59.

1. How many students in the North-Western District (NW) chose Russian as their favorite subject? Write the answer to this question in cell H2 of the table.

2. What is the average test score for students in West District (3)? Write the answer to this question in cell H3 of the table with an accuracy of at least two decimal places.

Task 19 No. 79.

Complete the task.

1. How many students in the Central District (C) chose English as their favorite subject? Write the answer to this question in cell H2 of the table.

2. What is the average test score for students in East District (B)? Write the answer to this question in cell H3 of the table with an accuracy of at least two decimal places.

Task 19 No. 99.

Complete the task.

Open the file with this spreadsheet (the exam organizers will tell you the location of the file). Based on the data contained in this table, answer two questions.

1. How many students in the Southeast District (SE) chose social studies as their favorite subject? Write the answer to this question in cell H2 of the table.

2. What is the average test score for students in the Southwest District (SW)? Write the answer to this question in cell H3 of the table with an accuracy of at least two decimal places.

Task 19 No. 119.

Complete the task.

Open the file with this spreadsheet (the exam organizers will tell you the location of the file). Based on the data contained in this table, answer two questions.

1. How many students in the Southern District (S) chose English as their favorite subject? Write the answer to this question in cell H2 of the table.

2. What is the average test score for students in the Southeast District (SE)? Write the answer to this question in cell H3 of the table with an accuracy of at least two decimal places.