microbik.ru
1

Excel. Функции СЧЕТЕСЛИ и СУММЕСЛИ




Подборка заданий для проведения итоговых уроков по изучению Excel. Задания снабжены пояснениями, которые с каждым упражнением становятся все менее подробными. Такой подход заставляет учащихся не просто выполнять предложенные упражнения, но и запоминать приемы работы, ведь иначе придется возвращаться к уже выполненной работе и разбираться с заданием заново; а также способствует простому и понятному для учащихся оцениванию их работы.

Упражнение 1. «Магазин»

В магазин «Молоко» каждый день завозят молочные продукты несколько поставщиков. Составить таблицу учета поставок за день для этого магазина. В отдельной таблице определить количество поставок молочных продуктов одного вида за день, их суммарную стоимость и среднюю цену.

Комментарии:

I. Создайте две таблицы: с исходными данными и результатами.

1. В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
2. Заполните таблицу исходными данными, например:

 

A

B

C

D

E

F

1



Наименование

Поставщик

Количество, л

Цена

Стоимость

2

1

Молоко

Поставщик № 1

76

43,00р.

 

3

2

Кефир

Поставщик № 2

40

26,00р.

 

4

3

Варенец

Поставщик № 3

86

34,00р.

 

5

4

Молоко

Поставщик № 3

45

26,00р.

 

6

5

Молоко

Поставщик № 4

87

24,00р.

 

7

6

Варенец

Поставщик № 4

34

41,00р.

 

8

7

Кефир

Поставщик № 4

67

32,00р.

 

9

8

Кефир

Поставщик № 3

45

39,00р.

 

10

9

Молоко

Поставщик № 2

43

35,00р.

 

11

10

Молоко

Поставщик № 5

32

33,00р.

 

Таблица 1

3. В таблице с результатами должны быть поля: «Наименование», «Количество поставок», «Суммарная стоимость», «Средняя цена».
4. Во второй таблице в столбце «Наименование» названия молочных продуктов из первой таблицы должны встречаться один раз, например:

 

A

B

C

D

E

14



Наименование

Кол-во поставок

Суммарная стоимость

Средняя цена

15

1

Молоко

 

 

 

16

2

Кефир

 

 

 

17

3

Варенец

 

 

 

Таблица 2


II. Введите формулы в таблицы.

1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке

F2=D2*E2.

Результат:

 

A

B

C

D

E

F

1



Наименование

Поставщик

Количество, л

Цена

Стоимость

2

1

Молоко

Поставщик № 1

76

43,00р.

3 268,00р.

3

2

Кефир

Поставщик № 2

40

26,00р.

1 040,00р.

4

3

Варенец

Поставщик № 3

86

34,00р.

2 924,00р.

5

4

Молоко

Поставщик № 3

45

26,00р.

1 170,00р.

6

5

Молоко

Поставщик № 4

87

24,00р.

2 088,00р.

7

6

Варенец

Поставщик № 4

34

41,00р.

1 394,00р.

8

7

Кефир

Поставщик № 4

67

32,00р.

2 144,00р.

9

8

Кефир

Поставщик № 3

45

39,00р.

1 755,00р.

10

9

Молоко

Поставщик № 2

43

35,00р.

1 505,00р.

11

10

Молоко

Поставщик № 5

32

33,00р.

1 056,00р.

Таблица 1.1

2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:

а) Для определения количества поставок нужно определить, сколько раз за день в магазин завозили, например, молоко. Для этого нужно использовать функцию СЧЁТЕСЛИ, которая определяет количество данных в диапазоне, равных критерию. Примерный вид формулы в ячейке С15:

=СЧЁТЕСЛИ(B$2:B$11;B15),

где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

Результат:

 

A

B

C

D

E

14



Наименование

Кол-во поставок

Суммарная стоимость

Средняя цена

15

1

Молоко

5

 

 

16

2

Кефир

3

 

 

17

3

Варенец

2

 

 

Таблица 2.1

b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:

=СУММЕСЛИ(B$2:B$11;B15;F$2:F$11),

где F2:F11 — диапазон стоимости продукта из первой таблицы.

Результат:

 

A

B

C

D

E

14



Наименование

Кол-во поставок

Суммарная стоимость

^ Средняя цена

15

1

Молоко

5

9 087,00р.

 

16

2

Кефир

3

4 939,00р.

 

17

3

Варенец

2

4 318,00р.

 

Таблица 2.2

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:

=СУММЕСЛИ(B$2:B$11;B15;E$2:E$11)/C15,

где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.

Результат:

 

A

B

C

D

E

14



Наименование

Кол-во поставок

Суммарная стоимость

^ Средняя цена

15

1

Молоко

5

9 087,00р.

32,20р.

16

2

Кефир

3

4 939,00р.

32,33р.

17

3

Варенец

2

4 318,00р.

37,50р.

^ Таблица 2.3

Замечания:


Данное упражнение может быть дополнено следующими заданиями (и не только ими):

1. Определить количество (в литрах) каждого продукта, завезенного в магазин.
2. Составить таблицу «Поставщики», в которой определить, на какую сумму каждый поставщик завез в магазин продукции, общий вес привезенной каждым поставщиком продукции, и сколько видов продуктов привез каждый из поставщиков.

^ Упражнение 2. «Студенческие стипендии» [1]

Приложение 1

Составить таблицу экзаменационных оценок студентов и на ее основе определить средний балл и размер стипендии для каждого студента. Правила определения размера стипендии:

  • стипендия не назначается, если среди оценок есть хотя бы одна двойка;

  • 3,0 < средний балл < 3,5 — 1000р.

  • 3,5 < средний балл < 4,0 — 1200р.

  • 4,0 < средний балл < 4,5 — 1500р.

  • 4,5 < средний балл < 5,0 — 1800р.

  • средний балл = 5,0 — 2000р.

Определить общую сумму назначенных стипендий.

Замечания:

Формулировка данной задачи, с некоторыми изменениями, взята из учебника «Информатика: Практикум по технологии работы на компьютере» под ред. Н.В. Макаровой.

Комментарии:

  • Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:



Фамилия

Предметы

Средний балл

Стипендия

Матем.

Физика

Информ.

Химия

История

1

Алексеев

5

3

2

3

3

 

 

2

Гаврилов

4

4

3

3

3

 

 

3

Зайцев

3

3

3

3

3

 

 

4

Иванов

4

3

5

3

3

 

 

5

Краснов

5

4

4

4

4

 

 

6

Кузнецов

4

5

5

5

5

 

 

7

Петров

5

5

5

5

5

 

 

8

Сидоров

4

3

3

3

5

 

 

9

Смирнов

4

4

4

4

4

 

 

10

Солдатов

5

4

5

2

3

 

 

  • Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:

=ЕСЛИ(СЧЁТЕСЛИ(C3:G3;2)>0;0;ЕСЛИ(H3<3,5;1000;ЕСЛИ(H3<4;1200;
ЕСЛИ(H3<4,5;1500;ЕСЛИ(H3<5;1800;2000))))).

Результат:



Фамилия

Предметы

Средний балл

Стипендия

Матем.

Физика

Информ.

Химия

История

1

Алексеев

5

3

2

3

3

3,2

0р.

2

Гаврилов

4

4

3

3

3

3,4

1 000р.

3

Зайцев

3

3

3

3

3

3

1 000р.

4

Иванов

4

3

5

3

3

3,6

1 200р.

5

Краснов

5

4

4

4

4

4,2

1 500р.

6

Кузнецов

4

5

5

5

5

4,8

1 800р.

7

Петров

5

5

5

5

5

5

2 000р.

8

Сидоров

4

3

3

3

5

3,6

1 200р.

9

Смирнов

4

4

4

4

4

4

1 500р.

10

Солдатов

5

4

5

2

3

3,8

0р.

 

 

 

 

 

 

 

Итого:

11 200р.

Упражнение ^ 3. «Завод железобетонных изделий»

Приложение 1

Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.

I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.

1. Первая таблица должна содержать поля: «Марка», «Длина», «Ширина», «Высота», «Удельный вес», «Вес блока».
             a) Заполните таблицу, кроме столбца «Вес блока» (не менее 10 марок).
2. Заведите отдельную ячейку для значения грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка», «Количество блоков», «Количество вагонов».
             a) В столбце «Марка» повторите названия нескольких марок блоков из первой таблицы (не менее 4).
             b) Столбец «Количество блоков» заполните произвольными данными.

II. Ввод формул.

1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны.
2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:
             a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.
             b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функцииОКРУГЛВВЕРХ
3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.

Упражнение 4. «Прайс-лист»

Приложение 1

Компания, выпускающая косметику, выдает распространителям прайс-лист, в котором указано название продукта и его цена для распространителя и для клиента. Создать «электронный калькулятор» распространителя, с помощью которого он сможет определить, какая сумма ему потребуется, чтобы выкупить заказанную покупателем продукцию у компании, сколько ему должен заплатить клиент, и прибыль, которую он получит в результате продажи.

Литература

  1. Информатика: Практикум по технологии работы на компьютере/ Под ред. Н.В. Макаровой. – 3-е изд., переработ. – М.: Финансы и статистика, 2003. – 256 с.: ил.