Статья представляет собой подборку заданий для проведения итоговых уроков по изучению 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]
Составить таблицу экзаменационных оценок студентов и на ее основе определить средний балл и размер стипендии для каждого студента. Правила определения размера стипендии:
- стипендия не назначается, если среди оценок есть хотя бы одна двойка;
- 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. «Завод железобетонных изделий»
Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.
I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.
1. Первая таблица должна содержать поля:
«Марка», «Длина», «Ширина», «Высота», «Удельный
вес», «Вес блока».
a)
Заполните таблицу, кроме столбца «Вес блока» (не
менее 10 марок).
2. Заведите отдельную ячейку для значения
грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка»,
«Количество блоков», «Количество вагонов».
a) В
столбце «Марка» повторите названия нескольких
марок блоков из первой таблицы (не менее 4).
b)
Столбец «Количество блоков» заполните
произвольными данными.
II. Ввод формул.
1. Вес блока (в первой таблице) определите с
помощью функции ПРОИЗВЕД и переведите в
тонны.
2. Чтобы определить количество вагонов для блоков
каждой марки, нужно количество блоков умножить
на вес блока и разделить на грузоподъемность:
a) Для
того чтобы выбрать соответствующий вес блока из
первой таблицы, используйте функцию СУММЕСЛИ.
b) Так
как количество вагонов может быть только целым
числом, то результат округлите до целого с
помощью функции ОКРУГЛВВЕРХ.
3. В отдельной ячейке определите общее количество
вагонов для блоков всех марок.
Упражнение 4. «Прайс-лист»
Компания, выпускающая косметику, выдает распространителям прайс-лист, в котором указано название продукта и его цена для распространителя и для клиента. Создать «электронный калькулятор» распространителя, с помощью которого он сможет определить, какая сумма ему потребуется, чтобы выкупить заказанную покупателем продукцию у компании, сколько ему должен заплатить клиент, и прибыль, которую он получит в результате продажи.
Литература
- Информатика: Практикум по технологии работы на компьютере/ Под ред. Н.В. Макаровой. – 3-е изд., переработ. – М.: Финансы и статистика, 2003. – 256 с.: ил.