Spreadsheet assignments

1 task.

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

Student

District

physics

Informatics

Brusov Anatoly

West

Fore 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 score in two subjects among students in the Northern District? Write the answer in cell G2.

Solution

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

    To cell G 2 let's write down formula2 = min ( E 2: E 267) .

This is the answer to question 1. Question 2

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

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

    d

Let's make a proportion 100%

266 studies H2

 X%

from here

X =H2*100/266. G 4 To cell let's write down = In a cell 2*100/266 . formula 4

This is the answer to question 2. Question 3. What is it equal to largest amountpoints in two subjects among schoolchildren who received more than 50 points in physics orG 6.

Solution

    To cell computer science? Enter your answer in the cell 2 let's write down F = if ( formula 5 ( or 2>50; countif c D 2;”-“) 2>50);C2+

    To cell G 6 let's write down formula6 = . ( computer science? Enter your answer in the cell 2: computer science? Enter your answer in the cell 267) The formula must be copied down for all students.

max.

This is the answer to question 3. OR

- or AND

= . () - And

= min () IF

= , counting the number of students who received >=65 points in computer science - If– function for finding the maximum value

= if - function for finding the minimum value(cell range;”condition”)

– counting the number of cells for which a certain condition is met(condition; action 1; action 2)-

Student

checking the condition, if it is true, action 1 is performed, if the condition is false, action 2 is performed.

Homework

Option 1.

Brusov Anatoly

Vasiliev Alexander

Ermishin Roman

Monikashvili Eduard

Kruglov Nikita

Titova Anastasia

School 272 Geography

Computer science

    In total, the table included data on

    students.

    What is the highest sum of points in two subjects of school No. 1 students 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, describe them as in the problem discussed in class.

Student

checking the condition, if it is true, action 1 is performed, if the condition is false, action 2 is performed.

physics

mathematics

Brusov Anatoly

Vasiliev Alexander

Ermishin Roman

Monikashvili Eduard

Kruglov Nikita

Titova Anastasia

In total, the table included data on 283 students.

Computer science

    How many students from 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 accurate to one decimal place in cell F 4. (Note, solution in 2 steps).

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

Working with spreadsheets

Problem 1 .

To a spreadsheet Working with tables1 entered the test results of students in mathematics and physics.

Column A contains 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 – scores obtained in mathematics and physics. For each subject you could score from 0 to 100 points. In total, data for 1000 students was entered into the spreadsheet. The order of records in the table is arbitrary.

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

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

2. How many test participants scored the same number of points in mathematics and physics? Write the answer to this question in cell G3 of the table.

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

4. What percentage of the total number of participants were students from the Maysky district? Write the answer accurate to one decimal place in cell G3 of the table.

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

6. What is the minimum math score for students in 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 subject? Write the answer to this question in cell G2 of the table.

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

Problem 2 .

To a spreadsheet Working with tables2 recorded the population of cities in different countries.

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

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. 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 of the table. How many cities in the table have a population of less than 100,000 people? 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 accurate to two decimal places (in thousand people) in cell F3 of the table. How many cities of Belarus are presented in the table? Write your answer in cell F2. What is the average population of cities whose inhabitants do not exceed 100 thousand people? Write the answer to this question accurate to two decimal places (in thousand people) in cell F3 of the table. How many people live 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 presented in the table? Write the answer to this question in cell F3.

Practical work No. 4. Relative and absolute references

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

      • A. Fill in the columns Prize And Salary. When calculating the bonus, you need to use the values ​​​​of cell B1.
      • B. Determine the minimum, maximum, average and total wages for each workshop and for the entire plant as a whole.
      • C. Go to Sheet 2. Fill out the table for calculating prices for tablet computers and calculate the total revenue of the store.
      • 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 over 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 construct a bar chart and then a pie chart from this data.

Practical work No. 6

Conditional calculations

Upload the document ifa. xls(or ifa. ods).

      • A. Go to Sheet 1 and complete the task in the frame. Usually delivery in the online store “Nash Krai” costs 200 rubles, but if the order price is 1200 rubles or more, then delivery is free. Calculate in cell F3 total store revenue including delivery.
      • B. Go to Sheet 2 and complete the activity in the frame. Horns and Hoofs delivers horns and hooves to customers. If the client's home 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 company will receive for delivering the goods.
      • C. Go to Sheet 3 and complete the activity in the box. Residents of the house regularly pay the Elektrosbyt company for electricity. If they have an electricity meter, they pay according to the tariff. If there is no meter, then it is considered that each resident of the apartment used 70 kWh per month. Calculate the amount the company will receive per month from all residents.

Difficult conditions

Upload the 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 frame. The Macrohard company is recruiting programmers for new project. Applicants go through two qualifying rounds, in which they perform test tasks. Anyone who scores more than 80 points in the first round and more than 70 points in the second round is accepted for work. Determine how many new employees were hired based on the results of two rounds.
      • B. Go to Sheet 2 and complete the activity in the box. The Krestotsvet company announces a promotion for those who have discount cards. If they buy goods worth more than 1,500 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 activity in the box. There are two tariffs for paying for telephone calls: regular and preferential. The reduced rate is valid on weekends (they have numbers 6 - Saturday and 7 - Sunday), as well as on any day after 20:00 until midnight. Tariffs are written in cells C2 And C3. Find total cost all conversations. The time point 20:00:00 should be written as TIME(20;0;0)(V Microsoft Excel - TIME(20;0;0)).

Processing large amounts of data (beginning)

Upload the document array.xls

      • A.
  2. What is the highest score in two subjects among students in the Central District?
  3. What percentage of the total number of participants were students from the Central District? Write your answer to one decimal place.
      • B.
  1. What was the total distance transported from August 1 to August 3, 2015?
  2. What is the average weight of cargo during transportation from the village. Orekhovo? Write your answer to one decimal place.
      • C.
  1. How many foods in the table contain less than 50 g of carbohydrates and less than 50 g of protein?
  2. What is the average calorie content of foods containing less than 1 g of fat? Write down the answer to this question with an accuracy of at least two decimal places.

Processing large amounts of data (continued)

Upload the document array2.xls

      • A. Go to Sheet 1 and complete the task in the frame. Define:
  2. What is the maximum long jump result for boys born in 2004?
  3. Find the average time for girls in the 30m dash? Write your answer to three decimal places.
      • B. Go to Sheet 2 and complete the activity in the frame. Define:
  1. How tall is the tallest student in 10th grade?
  2. What percentage of 8th grade students weigh more than 60 kg? Write your answer to two decimal places.
      • C. Go to Sheet 3 and complete the activity in the box. Define:
  1. How many girls love computer science?
  2. What percentage of 8th grade students have a grade of 4 or 5 in their favorite subject? Write down the answer to this question to two decimal places.
      • D. Go to Sheet 4 and complete the activity in the box. Define:
  1. How many students in grades 10 and 11 passed exams in Russian language and physics with grades of 4 and 5 points?
  2. What percentage of 9th and 10th grade students did not pass the exam in at least one subject? Write down the answer to this question to one decimal place. Test Option1 Option2

Exercise 1.

  • How many participants scored more than 20 points? Write the answer to this question in cell G2 of the table.
  • 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 of the table.
  • How many boys outnumber the girls? Write the answer to this question in cell H2 of the table.
  • What is the average score of boys? Write the answer to this question, accurate to two decimal places, in cell H3 of the table.
  • How many girls took part in the 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 the testing? Write the answer to this question in cell J2 of the table.
  • What is the difference between the average scores of students from the faculties of economics and medicine? Write the answer to this question in cell J3 of the table.

Task2.

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

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

Task 3.

Based on the data contained 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 was the average amount of precipitation per day on those days of the year when the northeast (NE) wind blew? Write the answer to this question, accurate to two decimal places, in cell G3 of the table.
  • What was the average atmospheric pressure in the autumn months (September, October, November)? Write the answer to this question in cell H2 of the table.
  • Which average speed was there any wind on those days of the year when the east (E) wind blew? Write the answer to this question, accurate to two decimal places, in cell H3 of the table.
  • What was the average temperature during the autumn months (September, October, November)? Write the answer to this question in cell I2 of the table.
  • What was the average Atmosphere pressure on those days of the year when the west (W) wind blew? 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 was the average amount of precipitation per day on those days of the year when the south (S) wind blew? Write the answer to this question in cell J3 of the table.

Task 4.

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

  • What is the lowest score for students in the Podgorny district? Write the answer to this question in cell G2 of the table.
  • How many test participants scored the same number of points in mathematics and physics? Write the answer to this question, accurate to two decimal places, in cell G3 of the table.
  • What is the highest score in two subjects among students in the Maysky district? Write the answer to this question in cell H2 of the table.
  • What percentage of the total number of participants were students from the Maysky district? Write the answer to this question, accurate to two decimal places, in cell H3 of the table.
  • How many participants scored more than 100 points in total in two subjects? Write the answer to this question in cell I2 of the table.
  • What is the minimum math score for students in the Zarechny district? Write the answer to this question in cell I3 of the table.
  • How many Central District students scored more than 50 points in each subject? 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 inspection.