Практикум в Excel
учебно-методическое пособие по информатике и икт (9 класс)

Практикум  в EXCEL

ВВЕДЕНИЕ

Электронная таблица Excel программного пакета Microsoft Office являются

мощным вычислительным средством, предлагающим пользователям

замечательные возможности и многочисленные инструменты.

Предлагаемое учебное пособие представляет собой сборник практических

заданий, позволяющих ученикам проверить свои знания и навыки работы с

Excel и понять суть производимых этой программой операций.

Пособие не содержит теоретических описаний инструментов и

возможностей программы Excel и не является справочным. Этим оно отличается

от многочисленной изданной литературы, в которой не уделяется необходимого

внимания иллюстрации практического применения программы для решения

прикладных задач.

ОСНОВЫ РАБОТЫ В EXCEL. ПРОСТЕЙШИЕ ВЫЧИСЛЕНИЯ

Задание 1

1. Новую рабочую книгу сохраните в свою рабочую папку под своим именем.

2. На первом листе в ячейку A1 введите текст Объем продаж книг магазином

“Кругозор”. В ячейку А2 введите текст 1 квартал.

3. Ячейки B4:E4 заполните по порядку текстом: Январь, Февраль, Март,

Итого. Ячейки A5:A9 заполните названиями книжных разделов:

техническая, художественная, детская, юридическая.

4. Содержимое ячейки Е4 скопируйте в ячейку А9. Измените, если необходимо,

ширину столбцов.

5. Таблица будет содержать стоимость проданных книг. Заполните ячейки

В5:D8 числами по рис.1. Используйте целые и дробные числа.

6. Для оформления таблицы используйте автоформат. Для числовых значений

установите формат с двумя десятичными знаками.

7. Определите стоимость проданной за квартал технической литературы.

8. Скопируйте полученную формулу для других категорий литературы.

9. Аналогичные действия выполните в строке «Итого».

10. Какие способы копирования вы знаете?

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

технической и юридической литературы за Январь и Февраль, например, в

ячейке Н1.

12. В ячейке Н2 определите общую стоимость детской и художественной

литературы в Феврале и Марте.

13. Дополните таблицу столбцами «МАКС» и «МИН» справа от столбца «Итого».

Определите с помощью стандартных функций максимальное и минимальное

значение стоимости для технической литературы. Используя копирование,

определите эти значения для остальных категорий литературы.

14. Скопируйте таблицу без столбцов «МАКС» и «МИН» ниже исходной

таблицы. Удалите числовые данные о продажах за одно нажатие

клавиши Delete.

15. Для каждой категории литературы в новой таблице выразите в процентах

отношение объема продаж за каждый месяц к общей стоимости проданной

литературы данной категории. Используйте для вычислений данные

исходной таблицы. В формулах используйте нужный вид абсолютной

адресации (в каждой строке столбца «Итого» должно получиться 100%).

16. Скопируйте исходную таблицу еще раз и вычислите долю каждого значения

области от общей стоимости всей проданной литературы (общий итог по

таблице должен получиться 100%). Используйте данные первой таблицы и

нужный вид абсолютного адреса.

17. Представьте доли в процентах.

Замечания. 1. Чтобы при добавлении новых данных в таблицу не приходилось

вновь вводить итоговые формулы по строкам или столбцам, рекомендуется

отделять таблицу от итоговых строк и столбцов, содержащих суммирующие

формулы, пустой строкой и столбцом.

2. Пустые ячейки этих строк и столбцов должны быть включены в

область суммирования! Эти пустые строки можно выделить цветом.

3. Строки и столбцы с новыми данными можно вставлять внутри

таблицы или непосредственно перед пустой строкой, тогда они

автоматически попадут в область суммирования.

18. Измените структуру и итоговые формулы таблицы так, чтобы в таблицу

можно было добавлять новые категории литературы и последующие месяцы

без редактирования этих формул. Как это отразится на других таблицах?

Примерный вид полученной таблицы показан на рис. 1.

Рис. 1. Образец преобразованной таблицы

19. Переименуйте лист с таблицами. Назовите его «Кругозор».

20. Скопируйте исходную таблицу на новый лист «Горизонт». Внесите

изменения в заголовок таблицы и показатели объемов продаж.

21. Скопируйте исходную таблицу еще раз на лист «Общий отчет» и удалите на

нем из таблицы данные о продажах.

22. На листе «Общий отчет» определите общую стоимость проданной

литературы по двум магазинам за каждый месяц.

23. Постройте по таблице «Кругозор» круговую диаграмму реализации книг в

январе. Выделите наименьший сектор. Что показывает круговая диаграмма?

24. Постройте по всем данным таблицы «Кругозор» гистограмму. Измените ее

оформление. Оформите диаграмму, применив градиентную заливку для

фона и масштабированные рисунки для элементов гистограммы.

Задание 2

1. Заполнить таблицу по образцу.

2. Значения столбца «Вес, г» для 4 и 6 наименований необходимо ввести

вручную.

3. Определить общую стоимость продуктов и процентный состав.

4. Выяснить, на какое количество порций весом в 100 г рассчитан данный

рецепт.

5. Определить стоимость продуктов для одной порции.

6. Определить стоимость порции для покупателя, если зарплата сотрудника

составляет 25%, а накладные расходы — 80% от стоимости продуктов одной

порции.

7. Отформатировать заголовок таблицы.

8. Построить круговую диаграмму процентного состава салата. Выделить

наименьший сектор. Отобразить на диаграмме значения и подписи данных.

Применить двухцветную градиентную заливку фона.

РАСЧЕТ (КАЛЬКУЛЯЦИЯ)

стоимости продуктов для приготовления салата

на порции

№ Наименование Ед.изм. Цена Кол-во Вес, г Стоимость Состав в %

1 Картофель кг 5 0,30 300,00 1,5 3%

2 Фасоль кг 12 0,25 250,00 3 7%

3 Помидоры кг 30 0,55 550,00 16,5 37%

4 Рыба банка 14,5 1 600,00 14,5 32%

5 Капуста кг 9 0,75 750,00 6,75 15%

6 Яйца шт 1,4 2 40,00 2,8 6%

ИТОГО: 4,85 2490 45,05 100%

Вес одной

порции, г 100

Стоимость продуктов

одной порции 1,81

Зарплата

сотрудников 25% 0,45

Накладные

расходы 80% 1,45

Стоимость порции для

покупателя 3,71

Задание 3

Составить таблицу, содержащую следующие поля:

№ Месяц ФИО Номер

телефона Льгота Оплата Внесено Долг

Заполнить таблицу в соответствии с условиями:

• Записей в таблице — 12.

• Различных месяцев — 3.

• Значения столбца «Внесено» задаются произвольно.

• Столбец «Льгота» заполняется следующим образом: 0 — нет льгот,

1 — скидка в оплате 25%, 2 — скидка в оплате — 50%.

• Различных фамилий — 4, фамилии повторяются в каждом месяце.

• Тариф без льгот записан в свободной ячейке вне таблицы.

Выполнить:

1. Вычислить сумму оплаты в соответствии с номером льготы. Если льгота

равна 0, то 100% тарифа, если льгота равна 1, то скидка в оплате 25%, если

льгота равна 2, то скидка 50%.

2. Вычислить долг.

3. Вычислить общую сумму внесенной оплаты и общую сумму долга.

4. Вычислить среднее значение по полю «Долг».

5. Используя функцию СЧЕТЕСЛИ, определить количество абонентов с долгом

больше среднего.

6. Используя стандартные функции, найти минимальное и максимальное

значения суммы долга.

7. Вычислить сумму долга по каждой фамилии и оформить в виде таблицы со

столбцами «ФИО», «Долг».

8. Вычислить сумму начисленной и внесенной оплаты по месяцам и оформить

в виде таблицы в соответствии с образцом.

Месяц Оплата Внесено

9. Построить диаграмму начисленной и внесенной оплаты по месяцам.

Задание 4

Составить таблицу, содержащую следующие поля:

№

п/п Наименование Стоимость проката

в сутки Дата выдачи Дата сдачи

Заполнить таблицу в соответствии с условиями:

• Записей в таблице — 10.

• Значения в столбце «Дата сдачи» вводятся таким образом, чтобы были

наименования с просроченным сроком возврата (относительно текущей

даты).

• Различных наименований — 4.

Выполнить:

1. Вставить в таблицу столбец «Просрочено дней» и вычислить.

2. Вставить столбец «Пеня» и вычислить сумму пени в соответствии со

следующим условием: если количество просроченных дней меньше 5, то

0,05% за каждый день, от 5 до 10 дней – 0,1% за каждый просроченный день

сверх 5 и по предыдущей ставке за остальные, более 10 дней – 0,3% за

каждый день сверх 10 и по 0,1% – до 10.

3. Дополнить таблицу столбцами «Дней в прокате» и вычислить значения.

4. Дополнить таблицу столбцом «К оплате» и вычислить с учетом пени.

5. Используя функцию СЧЕТЕСЛИ, определить количество просроченных

заказов.

6. Используя стандартные функции, найти минимальное и максимальное

значения дней проката.

7. Вычислить сумму к оплате по наименованиям и оформить в виде таблицы в

соответствии с образцом.

Наименование К оплате

8. Построить диаграмму по наименованиям.

Задание 5

Составить таблицу, содержащую следующие поля:

№

п/п ФИО Дата

Рождения

Месяц выплаты

пособия

Заполнить таблицу в соответствии с условиями.

• Записей в таблице — 10.

• Значения в столбце «Дата рождения» вводятся таким образом, чтобы

были дети в возрасте меньше 3 лет, до 8 лет, от 8 до 17 лет и старше 17

лет.

• Различных месяцев — 3.

• Различных фамилий — 4, фамилии повторяются в каждом месяце.

• Минимальный заработок задается произвольно в свободной ячейке вне

таблицы.

Выполнить:

1. Вставить в таблицу столбец «Возраст» и вычислить возраст ребенка.

2. Дополнить таблицу столбцами «Сумма пособия». Значения в столбце

вычислить в соответствии с условием: если возраст меньше 3 лет, то 100%

от мин. заработка, если возраст меньше 8 лет, то 80%, от 8 до 17 лет —

60%, старше 17 лет — пособия нет.

3. Вычислить общую сумму пособий.

4. Вычислить средний возраст детей.

5. Используя функцию СЧЕТЕСЛИ, определить количество детей старше 8 лет.

6. Вычислить общую сумму пособий по фамилиям. Оформить в виде таблицы.

7. Вычислить сумму выплаты пособий по месяцам и оформить в виде таблицы

в соответствии с образцом.

Месяц Сумма пособия

8. Построить диаграмму выплат по месяцам.