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

Практикум в Excel

Электронная таблица Excel программного пакета Microsoft Office является мощным вычислительным средством, предлагающим пользователям замечательные возможности и многочисленные инструменты.

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

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

Основы работы в Excel. Простейшие вычисления

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

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

3. Ячейки B4:E4 заполните по порядку текстом: Январь, Февраль, Март, Итого. Ячейки A5:A9 заполните названиями книжных разделов: техническая, художественная, детская, юридическая.

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

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

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

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

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

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

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

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

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

13. Дополните таблицу столбцами МАКС и МИН справа от столбца Итого. Определите с помощью стандартных функций максимальное и минимальное значение стоимости для технической литературы. Используя копирование, определите эти значения для остальных категорий литературы.

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

15. Для каждой категории литературы в новой таблице выразите в процентах отношение объема продаж за каждый месяц к общей стоимости проданной литературы данной категории. Используйте для вычислений данные исходной таблицы. В формулах используйте нужный вид абсолютной адресации (в каждой строке столбца Итого должно получиться 100%).

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

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

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

Правила оформления и работы с таблицами в Excel

1. Ввод итоговых формул

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

2. Изменение структуры таблицы

Измените структуру и итоговые формулы таблицы так, чтобы можно было добавлять новые категории литературы и последующие месяцы без редактирования этих формул. Примерный вид полученной таблицы можно увидеть на рисунке 1.

3. Работа с листами таблиц

• Переименуйте лист с таблицами. Назовите его Кругозор.
• Скопируйте исходную таблицу на новый лист Горизонт. Внесите изменения в заголовок таблицы и показатели объемов продаж.
• Скопируйте исходную таблицу еще раз на лист Общий отчет и удалите на нем из таблицы данные о продажах.
• На листе Общий отчет определите общую стоимость проданной литературы по двум магазинам за каждый месяц.

4. Создание диаграмм

• Постройте по таблице Кругозор круговую диаграмму реализации книг в январе. Выделите наименьший сектор. Что показывает круговая диаграмма?
• Постройте по всем данным таблицы Кругозор гистограмму. Измените ее оформление, применив градиентную заливку для фона и масштабированные рисунки для элементов гистограммы.

Таблица по образцу

• Значения столбца Вес, г для 4 и 6 наименований необходимо ввести.
• Определите общую стоимость продуктов и процентный состав.
• Выясните, на какое количество порций весом в 100 г рассчитан данный.
• Определите стоимость продуктов для одной порции.
• Определите стоимость порции для покупателя, если зарплата сотрудника составляет 25%, а накладные расходы — 80% от стоимости продуктов одной.
• Отформатируйте заголовок таблицы.
• Постройте круговую диаграмму процентного состава салата. Выделите наименьший сектор. Отобразите на диаграмме значения и подписи данных. Примените двухцветную градиентную заливку фона.

Таблица с данными о клиентах

• Записей в таблице — 12.
• Различных месяцев — 3.
• Значения столбца Внесено задаются произвольно.
• Столбец Льгота заполняется следующим образом: 0 — нет льгот, 1 — скидка в оплате 25%, 2 — скидка в оплате — 50%.
• Различных фамилий — 4, фамилии повторяются в каждом месяце.

Анализ данных

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

Вычисления

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

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

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

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

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

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

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

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

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

   Месяц	Оплата	Внесено
   Январь	500	400
   Февраль	600	450
   Март	550	500

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

Таблица проката

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

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

2. Вставить столбец Пеня и вычислить сумму пени в соответствии со следующим условием:

   • Если количество просроченных дней меньше 5, то 0,05% за каждый день.
   • От 5 до 10 дней – 0,1% за каждый просроченный день сверх 5 и по предыдущей ставке за остальные.
   • Более 10 дней – 0,3% за каждый день сверх 10 и по 0,1% – до 10.

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

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

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

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

   Наименование	К оплате
   Продукт 1	200
   Продукт 2	150

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

Таблица детей

• Значения в столбце Дата рождения вводятся таким образом, чтобы были дети в возрасте меньше 3 лет, до 8 лет, от 8 до 17 лет и старше 17.

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

2. Дополнить таблицу столбцами Сумма пособия.

   • Значения в столбце вычисляются в соответствии с условием.

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

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

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

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

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

   Месяц	Сумма пособия
   Январь	1000
   Февраль	1200

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