Excel. Функции СЧЕТЕСЛИ и СУММЕСЛИ
Упражнение 1. «Магазин»
В магазин «Молоко» каждый день завозят молочные продукты несколько поставщиков. Составить таблицу учета поставок за день для этого магазина. В отдельной таблице определить количество поставок молочных продуктов одного вида за день, их суммарную стоимость и среднюю цену.
Комментарии:
I. Создайте две таблицы: с исходными данными и результатами.
1. В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
2. Заполните таблицу исходными данными, например:
A | B | C | D | E | F | |
№ | Наименование | Поставщик | Количество, л | Цена, р | Стоимость | |
Молоко | Поставщик № 1 | 43,00 | ||||
Кефир | Поставщик № 2 | 26,00 | ||||
Варенец | Поставщик № 3 | 34,00 | ||||
Молоко | Поставщик № 3 | 26,00 | ||||
Молоко | Поставщик № 4 | 24,00 | ||||
Варенец | Поставщик № 4 | 41,00 | ||||
Кефир | Поставщик № 4 | 32,00 | ||||
Кефир | Поставщик № 3 | 39,00 | ||||
Молоко | Поставщик № 2 | 35,00 | ||||
Молоко | Поставщик № 5 | 33,00 |
Таблица 1
3. В таблице с результатами должны быть поля: «Наименование», «Количество поставок», «Суммарная стоимость», «Средняя цена».
4. Во второй таблице в столбце «Наименование» названия молочных продуктов из первой таблицы должны встречаться один раз, например:
A | B | C | D | E | |
№ | Наименование | Кол-во поставок | Суммарная стоимость | Средняя цена | |
Молоко | |||||
Кефир | |||||
Варенец |
Таблица 2
II. Введите формулы в таблицы.
1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке
F2: =D2*E2.
2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:
а) Для определения количества поставок нужно определить, сколько раз за день в магазин завозили, например, молоко. Для этого нужно использовать функцию СЧЁТЕСЛИ, которая определяет количество данных в диапазоне, равных критерию. Примерный вид формулы в ячейке С15:
=СЧЁТЕСЛИ(B$2:B$11;B15),
где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).
b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:
=СУММЕСЛИ(B$2:B$11;B15;F$2:F$11),
где F2:F11 — диапазон стоимости продукта из первой таблицы.
c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:
=СУММЕСЛИ(B$2:B$11;B15;E$2:E$11)/C15,
где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.
Дополнения к данному упражнению:
1. Определить количество (в литрах) каждого продукта, завезенного в магазин.
2. Составить таблицу «Поставщики», в которой определить, на какую сумму каждый поставщик завез в магазин продукции, общий вес привезенной каждым поставщиком продукции, и сколько видов продуктов привез каждый из поставщиков.
Упражнение 2. «Студенческие стипендии»
Составить таблицу экзаменационных оценок студентов и на ее основе определить средний балл и размер стипендии для каждого студента. Правила определения размера стипендии:
· стипендия не назначается, если среди оценок есть хотя бы одна двойка;
· 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, например:
№ | Фамилия | Предметы | Средний балл | Стипендия | ||||
Матем. | Физика | Информ. | Химия | История | ||||
Алексеев | ||||||||
Гаврилов | ||||||||
Зайцев | ||||||||
Иванов | ||||||||
Краснов | ||||||||
Кузнецов | ||||||||
Петров | ||||||||
Сидоров | ||||||||
Смирнов | ||||||||
Солдатов | ||||||||
Итого |
· Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:
=ЕСЛИ(СЧЁТЕСЛИ(C3:G3;2)>0;0;ЕСЛИ(H3<3,5;1000;ЕСЛИ(H3<4;1200;
ЕСЛИ(H3<4,5;1500;ЕСЛИ(H3<5;1800;2000))))).