The test results were entered into a spreadsheet.

Spreadsheet assignments

1 task.

The spreadsheet contains the results of testing students in physics and computer science. Here are the first lines of the resulting table.

Student

county

physics

Informatics

Brusov Anatoly

West

For 1

Form5

Form2

For3

Vasiliev Alexander

Oriental

Ermishin Roman

Northern

Form4

Monikashvili Eduard

Central

Kruglov Nikita

Central

Form6

Titova Anastasia

Northern

266 students.

1 question. What is the lowest total score in two subjects among students in the Northern District? Write your answer in cell G2.

Decision

To cell E 2 write down formula 1 = if ( b 2=”Northern”;С2+ D 2;”-“)

To cell G 2 write down formula2 = min ( E 2: E 267) . This is the answer to question 1.

2 question. What percentage of the total number of participants were students who received at least 65 percent in computer science.

Decision.

In a cell H 2 write down formula 3, counting the number of students who received in computer science >=65 points = countif ( d 2: d 267;”>=65”) .

Let's make a proportion

266 accounts  100%

H2 X%

from here

X=H2*100/266.

To cell G 4 write down formula 4 = H 2*100/266 . This is the answer to question 2.

3 question. What is the highest sum of points in two subjects among schoolchildren who received more than 50 points in physicsor computer science? Enter your answer in the boxG 6.

Decision

To cell F 2 write down formula 5 = if ( or ( c 2>50; d 2>50);C2+ D 2;”-“) . The formula must be copied down for all students.

To cell G 6 write down formula6 = max ( F 2: F 267) . This is the answer to question 3.

OR- or

AND- and

IF- if

= max () is the function of finding the maximum value

= min () - function of finding the minimum value

= countif (cell range;”condition”)– counting the number of cells for which some condition is met

= if (condition; action 1; action 2)- checking the condition, if it is true, action 1 is performed, if the condition is false, action 2 is performed.

Homework option 1.

Student

School

Geography

Informatics

Brusov Anatoly

Vasiliev Alexander

Ermishin Roman

Monikashvili Eduard

Kruglov Nikita

Titova Anastasia

In total, the table contains data on 272 students.

The following questions need to be answered

How many students of school number 2 scored more points in computer science than in geography? Write the answer in cell F 2 (Note, solution in 2 steps).

What percentage of the total number of participants were students who received less than 50 points in geography. Write the answer with an accuracy of one decimal place in cell F 4. (Note, solution in 2 steps).

What is the highest sum of points in two subjects for students of school No. 1 who received at least 70 points in geography and computer science. Write your answer in cell F 6. (Note, solution in 2 steps).

Homework Option 2.

Solve the following problems, paint as in the problem analyzed in the class.

Student

School

physics

mathematics

Brusov Anatoly

Vasiliev Alexander

Ermishin Roman

Monikashvili Eduard

Kruglov Nikita

Titova Anastasia

In total, the table contains data on 283 students.

The following questions need to be answered

How many students of school No. 1 scored more than 75 points in mathematics? Write the answer in cell F 2 (Note, solution in 2 steps).

What percentage of the total number of participants were students who received at least 80 points in physics. Write the answer with an accuracy of one decimal place in cell F 4. (Note, solution in 2 steps).

What is the smallest sum of points in two subjects for students who received at least 65 points in physics and mathematics. Write your answer in cell F 6. (Note, solution in 2 steps).

Exercise 1.

How many participants scored over 20 points? Write the answer to this question in cell G2 of the table.
How many points does the average score of students of the Faculty of Economics differ from the general average score? Write the answer to this question with an accuracy of two decimal places in cell G3 of the table.
How many more boys are there than girls? Write the answer to this question in cell H2 of the table.
What is the average score for boys? Write the answer to this question with an accuracy of two decimal places in cell H3 of the table.
How many girls took part in testing? Write the answer to this question in cell I2 of the table.
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 I3 of the table.
How many young men took part in testing? Write the answer to this question in cell J2 of the table.
What is the difference between the average scores of students of economics and medical faculties? Write the answer to this question in cell J3 of the table.

Task2.

Based on the data in this table, answer the following questions:

Determine the number of students who have at least one "five". Write the answer to this question in cell N2 of the table.
In this group of students (who have at least one “five”) calculate the average score in mathematics? Write the answer to this question with an accuracy of two decimal places in cell N3 of the table.
Determine the number of students who do not have a single "five". Write the answer to this question in cell O2 of the table.
What percentage of students (of the total number of students in the table) have only one "five"? Write the answer to this question with an accuracy of two decimal places in cell O3 of the table.
How many students in grade 8 have a GPA less than 4? Write the answer to this question in cell N6 of the table.
What percentage of 8th graders (out of the total number of 8th graders) have an A in math? Write the answer to this question in cell N7 of the table.
How many students in grade 9 have two "fours"? Write the answer to this question in cell O6 of the table.
What percentage of students in grade 9 of the total number of students in the table do not have "fours"? Write the answer to this question in cell O7 of the table.

Task3.

Based on the data in this table, answer the following questions:

What was the average air temperature in the summer months (June, July, August)? Write the answer to this question in cell G2 of the table.
What is the average amount of precipitation per day on those days of the year when the northeast (NE) wind was blowing? Write the answer to this question with an accuracy of two decimal places in cell G3 of the table.
What was the average value of atmospheric pressure in the autumn months (September, October, November)? Write the answer to this question in cell H2 of the table.
What was the average wind speed on those days of the year when the east (B) wind was blowing? Write the answer to this question with an accuracy of two decimal places in cell H3 of the table.
What was the average temperature in the autumn months (September, October, November)? Write the answer to this question in cell I2 of the table.
What was the average atmospheric pressure on those days of the year when the westerly (W) wind was blowing? Write the answer to this question in cell I3 of the table.
What was the average air temperature in the spring months (March, April, May)? Write the answer to this question in cell J2 of the table.
What is the average amount of precipitation per day on those days of the year when the south (S) wind was blowing? Write the answer to this question in cell J3 of the table.

Task 4.

Based on the data in this table, answer the following questions:

What is the smallest sum of points for the students of the Podgorny district? Write the answer to this question in cell G2 of the table.
How many test takers scored the same score in math and physics? Write the answer to this question with an accuracy of two decimal places in cell G3 of the table.
What is the highest score in two subjects among the students of the Maisky district? Write the answer to this question in cell H2 of the table.
What percentage of the total number of participants were students of the Maisky district? Write the answer to this question with an accuracy of two decimal places in cell H3 of the table.
How many participants scored more than 100 points in the sum of the two subjects? Write the answer to this question in cell I2 of the table.
What is the minimum score in mathematics for students of the Zarechny district? Write the answer to this question in cell I3 of the table.
How many students in the Central District scored more than 50 points in each of the subjects? Write the answer to this question in cell J2 of the table.
What is the difference between the maximum and minimum scores in physics among all students? Write the answer to this question in cell J3 of the table.

Submit completed work for review.

Practical work No. 4. Relative and absolute links

Open the premia.xls (or premia.ods) file.

- - A. Fill in the columns Prize and The salary. When calculating the premium, you need to use the values of cell B1.
  - b. Determine the minimum, maximum, average and total wages for each shop and for the entire plant as a whole.
  - C. Go to Sheet 2. Complete the tablet pricing table and calculate the store's total revenue.
  - Files for homework and classroom work
  - №1 №2 №3 №4

Practical work No. 5. Diagrams

Open the diagram .xls (or diagram.ods) file.

- - A. The table shows the results of the work of a car dealership - the number of cars sold of different brands for several years.

Brand	2012	2013	2014	2015
Lada	155	178	135	123
Niva	125	100	178	120
UAZ			107	151

- - D. Go to Sheet 4. Determine the number of students from each district and build a bar chart based on this data, and then a pie chart.

Practical work No. 6

Conditional calculations

Download document ifa. xls(or ifa. ods).

- - A. Go to Sheet 1 and complete the task in the box. Usually, delivery in the Nash Krai online store costs 200 rubles, but if the order price is 1200 rubles or more, then delivery is free. Calculate in cell F3 the total revenue of the store, including shipping.
  - b. Go to Sheet 2 and complete the task in the box. Firm "Horns and Hooves" delivers horns and hooves to customers. If the client's house has an elevator, then delivery is free. If there is no elevator, then lifting goods to each floor above the 3rd costs 50 rubles. Find the total amount that the firm will receive for shipping the goods.
  - C. Go to Sheet 3 and complete the task in the box. Residents of the house regularly pay the Electrosbyt company for electricity. If they have a meter for consumed electricity, they pay according to the tariff. If there is no meter, then it is considered that each resident of the apartment has spent 70 kWh per month. Calculate the amount that the company will receive per month from all tenants.

Difficult conditions

Download document andor.xls(or andor.ods). for home and classroom work No. 1 No. 2 No. 3 No. 4 No. 5 No. 6

- - A. Go to Sheet 1 and complete the task in the box. The Macrohard company is recruiting programmers for a new project. Applicants go through two qualifying rounds, in which they perform test tasks. All those who scored more than 80 points in the first round and more than 70 points in the second are accepted for work. Determine how many new employees are hired based on the results of two rounds.
  - b. Go to Sheet 2 and complete the task in the box. The company "Krestotsvet" announces a promotion for those who have discount cards. If they buy goods worth more than 1500 rubles, they receive a 5% discount on the entire purchase. Determine the total amount of all purchases, taking into account the discount.
  - C. Go to Sheet 3 and complete the task in the box. There are two tariffs for paying for telephone conversations: regular and preferential. The reduced rate is valid on weekends (they have numbers 6 - Saturday and 7 - Sunday), as well as any day after 20:00 to midnight. Tariffs are recorded in cells C2 and C3. Find the total cost of all calls. The time point 20:00:00 should be written as TIME(20;0;0)(in Microsoft Excel - TIME(20;0;0)).

Processing of large data arrays (beginning)

Download document array.xls

- - A.
What is the highest score in two subjects among students of the Central District?
What percentage of the total number of participants were students from the Central District? Write your answer to the nearest decimal place.
- - b.

What was the total distance of transportation from 1 to 3 August 2015?
What is the average weight of cargo during transportation from the village. Orekhovo? Write your answer to the nearest decimal place.
- - C.

How many foods in the table contain less than 50 g of carbohydrates and less than 50 g of protein?
What is the average calorie content of foods with less than 1 g of fat? Write down the answer to this question with an accuracy of at least two decimal places.

Large Data Processing(continued)

Download document array2.xls

- - A. Go to Sheet 1 and complete the task in the box. Define:
What is the maximum long jump result for boys born in 2004?
What is the average time among girls in the 30m? Write your answer to the nearest three decimal places.
- - b. Go to Sheet 2 and complete the task in the box. Define:

What is the height of the tallest student in grade 10?
What percentage of 8th grade students weigh more than 60 kg? Write your answer to the nearest two decimal places.
- - C. Go to Sheet 3 and complete the task in the box. Define:

How many girls love computer science?
What percentage of 8th graders have a 4 or 5 in their favorite subject? Write your answer to this question to the nearest two decimal places.
- - D. Go to Sheet 4 and complete the task in the box. Define:

How many students of 10th and 11th grades passed the exams in Russian language and physics with 4 and 5 marks?
What percentage of students in grades 9 and 10 did not pass the exam in at least one subject? Write your answer to this question to the nearest decimal place. Test Option 1 Option 2

Working with spreadsheets

Task1 .

To spreadsheet Work with tables1 brought the test results of students in mathematics and physics.

Column A shows the student's first and last name; in column B is the area of the city in which the student's school is located; in columns C, D - points received in mathematics and physics. For each subject, it was possible to score from 0 to 100 points. A total of 1,000 students were entered into the spreadsheet. The order of records in the table is arbitrary.

1. What is the lowest score for the students of the Podgorny district?

Write the answer to this question in cell G2 of the table.

2. How many test takers scored the same score in math and physics? Write the answer to this question in cell G3 of the table.

3. What is the highest score in two subjects among the students of the Maisky district? Write the answer to this question in cell G2 of the table.

4. What percentage of the total number of participants were students of the Maisky district? Write your answer with an accuracy of one decimal place in cell G3 of the table.

5. How many participants scored more than 160 points in the sum of the two subjects? Write the answer to this question in cell G2 of the table.

6. What is the minimum score in mathematics for students of the Zarechny district? Write the answer to this question in cell G3 of the table.

7. How many students in the Central District scored more than 70 points in each of the subjects? Write the answer to this question in cell G2 of the table.

8. What is the difference between the maximum and minimum scores in physics among all students? Write the answer to this question in cell G3 of the table.

Task2 .

To spreadsheet Work with tables2 entered the population of cities in different countries.

Based on the information in this table, answer the following questions:

What is the total population in the Russian cities listed in the table? Write the answer to this question with an accuracy of one decimal place (in thousand people) in cell F2. How many cities in the table have a population of at least a million people? Write the answer to this question in cell F3 of the table. How many cities in the table have a population of less than 100,000? Write your answer in cell F2. What is the average population of the Austrian cities presented in the table? Write the answer to this question with an accuracy of two decimal places (in thousand people) in cell F3 of the table. How many cities in Belarus are represented in the table? Write your answer in cell F2. What is the average population of cities with less than 100,000 inhabitants? Write the answer to this question with an accuracy of two decimal places (in thousand people) in cell F3 of the table. How many people are in Egypt's largest city by population? Write the answer to this question (in thousand people) in cell F2 of the table. How many cities in Egypt have more inhabitants than the average population of the cities shown in the table? Write the answer to this question in cell F3.