Методические рекомендации по выполнению лабораторной работы по теме «Инструментарий и технологии решения задач в среде табличных процессоров»

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

Результатом данной лабораторной работы должна быть книга, на каждой страницы которой находится выполненное задание. Задания располагаются в порядке изучения тем, поэтому у студентов есть право выбора: выполнить задания параллельно с изучением дисциплины или все вместе одновременно. Часть заданий затрагивает материал, неизучаемый в рамках аудиторных занятий.

Задание 1. Анализ продаж

1. Переименуйте Лист2, присвоив ему имя«Задание2»;

2. Оформите таблицу по образцу;

3. Произведите расчеты в графе «Сумма» по формуле: Сумма = Цена * Количество;

4. Вычислите результаты в ячейках Е11, Е13, Е14.

Задание 2. Финансовая сводка за неделю

1. Переименуйте Лист1, присвоив ему имя «Задание1»;

2. Оформите таблицу по образцу;

3. Произведите расчеты в графе «Финансовый результат» по следующей формуле: Финансовый результат = Доход – Расход;

4. Для ячеек с результатом расчетов задайте формат «Денежный» с выделением отрицательных чисел красным цветом;

5. Рассчитайте средние значения Дохода, Расхода и Финансового результата;

6. В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»);

7. Выполните заливку цветом ячейки D13;

8. Постройте линейчатую диаграмму изменения финансовых результатов по дням недели;

9. Произведите фильтрацию значений дохода, превышающих 4200 руб.

Задание 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. Произведите вычисления по формулам:

1. Премия = Оклад * %Премии (зафиксируйте значение ячейки, используя клавишу F4);

2. Всего начислено =Оклад + Премия;

3. Удержания = Всего начислено * % Удержаний (зафиксируйте значение ячейки, используя клавишу F4);

4. К выдаче = Всего начислено – Удержания;

4. Рассчитайте Итоги по столбцам;

5. Рассчитайте максимальный, минимальный и средний доход по данным колонки «К выдаче»;

6. Добавьте Лист6 и скопируйте содержимое листа «Зарплата за январь» на Лист 6;

7. Присвойте Листу6 имя «Зарплата за февраль»;

8. Исправьте название месяца в названии таблицы;

9. Измените значение Премии на 24%, убедитесь, что программа произвела пересчет формул;

10. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата»;

11. Рассчитайте значение доплаты по формуле: Доплата = Оклад * %Доплаты. Значение доплаты примите равным 5%;

12. Измените формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата. Скопируйте формулу вниз по столбцу.

13. Проведите условное форматирование значений колонки «К выдаче» на листе «Зарплата за январь» (используйте команду ФорматУсловное форматирование):

1. Выделите ячейки G4:G9;

2. Установите формат вывода значений между 3000 и 5000 - зеленым цветом шрифта, меньше или равно 3000 – красным цветом шрифта, больше или равно 5000 – синим цветом шрифта;

14. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию;

15. Защитите лист «Зарплата за январь» от изменений:

1. Задайте пароль на лист,

2. Сделайте подтверждение пароля;

3. Убедитесь, что лист защищен и невозможно удаление данных.

4. Снимите защиту листа.

16. Выполните условное форматирование оклада и премии за февраль месяц:

1. до 3000 – желтым цветом заливки;

2. от 3000 до 6000 – зеленым цветом заливки;

3. свыше 6000 – малиновым цветом заливки, белым цветом шрифта.

17. Защитите лист «Зарплата за февраль» от изменений. Проверьте защиту. Убедитесь в неизменяемости данных. Снимите защиту со всех листов электронной книги «Зарплата».

18. Постройте круговую диаграмму начисленной «Суммы к выдаче» всех сотрудников за февраль месяц.

Задание 6. Комплексное задание по электронным таблицам

1. Добавьте к имеющимся листам еще четыре листа;

2. Переименуйте листы следующим образом:

1. Лист 7 – Выручка;

2. Лист 8 – Себестоимость;

3. Лист 9 – График;

4. Лист 10 – Прибыль;

   3. На лист «Выручка» занесите и оформите таблицу;

4. Произведите следующие расчеты: в колонке «Всего за 6 лет» – суммирование по строке;

в колонке «Доля в общей выручке» – вычисление доли каждого магазина от общей выручке в процентах; в колонке «Ранг» – расстановка магазинов по местам с помощью функции РАНГ;

5. Используя функции ЕСЛИ и И, поместите в колонку «Тенденция за последние 3 года» следующие слова:

1. Стабильный рост – если выручка за шестой год больше выручки за пятый год, а выручка пятого года больше, чем четвертого;

2. Стабильное снижение – если выручка за шестой год меньше выручки за пятый год, а выручка пятого года меньше, чем четвертого;

3. Неизменное состояние – если на протяжении последних трех лет неизменна;

4. Нестабильные изменения – во всех остальных случаях;

6. На лист «Себестоимость» занесите и оформите следующую таблицу:

7. Произведите следующие расчеты: в колонке «Всего за 6 лет» – суммирование по строке;

в колонке «Среднегодовая себестоимость» – вычислите среднее значение; в столбцах «Максимальная себестоимость» и «Минимальная себестоимость» используйте функцию МАКС и МИН; в строке «Итого» - просуммируйте соответствующие столбцы;

8. На листе «График» постройте график выручки по годам во всех магазинах;

9. На лист «Прибыль» занесите и оформите третью таблицу:

10. Произведите следующие расчеты: Прибыль=Выручка–Себестоимость, «Всего за 6 лет» и «Долю» рассчитывайте аналогично первой таблице;

11. На этом же листе расположите две диаграммы:

1. круговую диаграмму – отражающую долю магазинов в общей прибыли,

2. объемную нормированную гистограмму для отражения прибыли магазинов по годам.

Задание 7. Расчет заработной платы, использование функции ВПР

1. Добавьте Лист11 и переименуйте, присвоив ему имя «Ставка ЕТС»;