- •Методические рекомендации по выполнению лабораторной работы по теме «Инструментарий и технологии решения задач в среде табличных процессоров»
- •7. Произведите следующие расчеты: в колонке «Всего за 6 лет» – суммирование по строке;
- •2.Оформите таблицу по образцу;
- •6. Создайте таблицу по образцу;
- •2. Создайте таблицу по образцу;
- •Критерии оценки лабораторной работы
Методические рекомендации по выполнению лабораторной работы по теме «Инструментарий и технологии решения задач в среде табличных процессоров»
На первом занятии по изучению Excel студенты получают от преподавателя индивидуальное домашнее задание – набор заданий, которые необходимо выполнить в табличном процессоре.
Результатом данной лабораторной работы должна быть книга, на каждой страницы которой находится выполненное задание. Задания располагаются в порядке изучения тем, поэтому у студентов есть право выбора: выполнить задания одновременной с изучением дисциплины или все вместе одновременно. Часть заданий затрагивает материал, неизучаемый в рамках аудиторных занятий.
Задание 1. Финансовая сводка за неделю
1. Переименуйте Лист1, присвоив ему имя «Задание1»;
2. Оформите таблицу по образцу;
3. Произведите расчеты в графе «Финансовый результат» по следующей формуле: Финансовый результат = Доход – Расход;
4. Для ячеек с результатом расчетов задайте формат «Денежный» с выделением отрицательных чисел красным цветом;
5. Рассчитайте средние значения Дохода, Расхода и Финансового результата;
5. В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»);
6. Выполните заливку цветом ячейки D13;
8. Постройте линейчатую диаграмму изменения финансовых результатов по дням недели;
9. Произведите фильтрацию значений дохода, превышающих 4200 руб.
Задание 2. Анализ продаж
1. Переименуйте Лист2, присвоив ему имя«Задание2»;
2. Оформите таблицу по образцу;
3. Произведите расчеты в графе «Сумма» по формуле: Сумма = Цена * Количество;
4. Вычислите результаты в ячейках Е11, Е13, Е14.
Задание 3. Ведомость учета брака
1. Переименуйте Лист3, присвоив ему имя «Задание3»;
2. Оформите таблицу по образцу;
3. Произведите расчеты в графе «Сумма брака» по следующей формуле: Сумма брака = Процент брака * Сумма зарплаты;
4. Вычислите минимальную, максимальную и среднюю сумму брака, а также средний процент брака;
5. Произведите фильтрацию данных по условию: Процент брака < 9 %;
6. Постройте график отфильтрованных значений изменения суммы брака по месяцам.
Задание 4. Анализ продаж продукции
1. Добавьте Лист4 и переименуйте, присвоив ему имя «Задание4»;
2 . Оформите таблицу по образцу;
3. Произведите расчеты в графе «Всего» по следующей формуле: Всего = Безналичные платежи + Наличные платежи; в графе «Выручка от продажи» по следующей формуле: Выручка от продажи = Цена*Всего.
4. Выделите минимальную и максимальную продажу (количество и сумму);
5. Произведите фильтрацию по цене, превышающей 9300 руб.;
6. Постройте гистограмму отфильтрованных значений изменения выручки по видам продукции.
Задание 5. Создание многостраничной таблицы
1. Добавьте Лист5 и переименуйте, присвоив ему имя «Зарплата за январь»;
2. Создайте таблицу по образцу;
3. Произведите вычисления по формулам:
Премия = Оклад * %Премии;
Всего начислено =Оклад + Премия;
Удержания = Всего начислено * % Удержаний;
К выдаче = Всего начислено – Удержания;
4. Рассчитайте Итоги по столбцам;
5. Рассчитайте максимальный, минимальный и средний доход по данным колонки «К выдаче»;
6. Добавьте Лист6 и скопируйте содержимое листа «Зарплата за январь» на Лист 6;
7. Присвойте Листу6 имя «Зарплата за февраль»;
8. Исправьте название месяца в названии таблицы;
9. Измените значение Премии на 24%, убедитесь, что программа произвела пересчет формул;
10. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата»;
11. Рассчитайте значение доплаты по формуле: Доплата = Оклад * %Доплаты. Значение доплаты примите равным 5%;
12. Измените формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата. Скопируйте формулу вниз по столбцу.
13. Проведите условное форматирование значений колонки «К выдаче» на листе «Зарплата за январь» (используйте команду ФорматУсловное форматирование):
Выделите ячейки G4:G9;
Установите формат вывода значений между 3000 и 5000 - зеленым цветом шрифта, меньше или равно 3000 – красным цветом шрифта, больше или равно 5000 – синим цветом шрифта;
14. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию;
15. Защитите лист «Зарплата за январь» от изменений:
Задайте пароль на лист,
Сделайте подтверждение пароля;
Убедитесь, что лист защищен и невозможно удаление данных.
Снимите защиту листа.
16. Выполните условное форматирование оклада и премии за февраль месяц:
до 2000 – желтым цветом заливки;
от 2000 до 6000 – зеленым цветом шрифта;
свыше 6000 – малиновым цветом заливки, белым цветом шрифта.
17. Защитите лист «Зарплата за февраль» от изменений. Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги «Зарплата».
18. Постройте круговую диаграмму начисленной «Суммы к выдаче» всех сотрудников за февраль месяц.
Задание 6. Комплексное задание по электронным таблицам
1. Добавьте к имеющимся листам еще четыре листа;
2. Переименуйте листы следующим образом:
Лист 7 – Выручка;
Лист 8 – Себестоимость;
Лист 9 – График;
Лист 10 – Прибыль;
3. На лист «Выручка» занесите и оформите таблицу;
4. Произведите следующие расчеты: в колонке «Всего за 6 лет» – суммирование по строке;
в колонке «Доля в общей выручке» – вычисление доли каждого магазина от общей выручке в процентах; в колонке «Ранг» – расстановка магазинов по местам с помощью функции РАНГ;
5. Используя функции ЕСЛИ и И, поместите в колонку «Тенденция за последние 3 года» следующие слова:
Стабильный рост – если выручка за шестой год больше выручки за пятый год, а выручка пятого года больше, чем четвертого;
Стабильное снижение – если выручка за шестой год меньше выручки за пятый год, а выручка пятого года меньше, чем четвертого;
Неизменное состояние – если на протяжении последних трех лет неизменна;
Нестабильные изменения – во всех остальных случаях;
6. На лист «Себестоимость» занесите и оформите следующую таблицу: