Табличный редактор MS Excel. Подбор параметра. Организация обратного расчета.

ОГБПОУ «НОВГОРОДСКИЙ АГРОТЕХНИЧЕСКИЙ ТЕХНИКУМ»

Инструкционная карта на выполнение

Практического занятия № 18 по дисциплине

«Информационные технологии в профессиональной деятельности»

Тема: Табличный редактор MS Excel.

Наименование работы: Подбор параметра. Организация обратного расчета.

Норма времени: 4часа;

Условия выполнения: учебный кабинет;

Оснащение рабочего места: ПК, инструкционная карта, табличный редактор;

Правила по технике безопасности: С правилами техники безопасности на рабочем месте ознакомлены;

Литература: Михеева Е.В. Информационные технологии в профессиональной деятельности. М.: «Академия», 2013г;

Уровни усвоения: 1 – 3 задания – 2 уровень

контрольное задание – 3 уровень

Система оценивания: применяется накопительная система оценки:

1 задание – 3 балла;

2 – 3 задания – 2 балла;

контрольное задание – 3 балла.

«2 балла» - задание выполнено полностью, без ошибок, отредактировано и оформлено в соответствии с предложенным образцом;

«1 балл» - задание выполнено полностью с небольшими недочетами и замечаниями; в оформлении; либо в таблице допущены ошибки в вычислениях.

«0 баллов» - задание не выполнено, либо выполнено неправильно.

«3 балла за контрольное задание» - задание выполнено полностью, без ошибок, отредактировано и оформлено в соответствии с предложенным образцом; при выполнении задания проявлены творческие способности студента. В тетради сделан конспект. Студент правильно ответил на контрольные вопросы

Теоретическая часть.

Подбор параметра.

• Выполните команды Данные – Анализ «что если» - Подбор параметра;

• В поле Установить введите адрес той ячейки, в которой нужно получить заданное число;

• В поле Значение введите искомый результат;

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

Практическая часть.

Задание 1.

1. Переименуйте Лист 1 в Зарплата.

1. Рассчитайте пустые столбцы;

2. Произведите сортировку фамилий по возрастании.

3. Постройте диаграмму по итоговому столбцу.

4. Рассчитайте, каков должен быть процент премии, чтобы фонд заработной платы был равен 300 000 рублей? Для этого:

- Выделите итоговую ячейку Всего;

- Выполните команды Данные – Анализ «что если» - Подбор параметра;

• В поле Установить в ячейке появившегося окна введите ссылку на ячейку, содержащую формулу расчета фонда заработной платы;

• В поле Значение наберите искомый результат 300 000;

В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится значение процента премии, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 300 000

1. Сохраните файл с именем Работа 18.

Задание 2.

1. Лист 1 переименуйте в Штатное расписание 1.

2. Создайте таблицу штатного расписания фирмы по приведенному образцу:

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

2. Рассчитайте значения пустых столбцов.

3. Значения столбца Зарплата сотрудника рассчитайте по формуле: зарплата=А_i*Х + B_i, где Х – оклад курьера.

4. Произведите подбор зарплат сотрудников фирмы так, чтобы суммарная заработная плата была равной 100 000 р. Для этого:

   • Выполните команды Данные – Анализ «что если» - Подбор параметра.

   • В поле Установить в ячейке появившегося окна введите ссылку на ячейку, содержащую формулу расчета фонда заработной платы;

   • В поле Значение наберите искомый результат 100 000;

   • В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 р.

5. Сохраните файл.

Задание 2.

1. Скопируйте содержимое листа «Штатное расписание 1» на Лист 2 и присвойте копии имя «Штатное расписание 2».

2. В скопированной таблице измените коэффициенты А и В в соответствии со следующей таблицей:

1. На Листе 3 (переименовать Сводная таблица) создайте следующую таблицу:

1. В таблице Штатное расписание 2 методом подбора параметра последовательно определите зарплаты сотрудников для различных значений фонда заработной платы: 100 000, 150 000, 200 000, 250 000, 300 000, 350 000, 400 000 р.

2. Результаты подбора значений зарплат скопируйте в таблицу Сводная таблица.

Краткая справка: Для копирования результатов расчетов необходимо выделить копируемые данные, выполнить команды Копировать (правой клавишей), установить курсор в соответствующую ячейку таблицы ответов, выполнить команды Специальная вставка, отметив в качестве объекта вставки – Значения.

1. Сохраните таблицы в этом же файле.

Контрольное задание. На Листе 4 создайте таблицу по образцу. Лист 4 переименуйте Контрольное задание. Рассчитайте значения столбца Стоимость продукции и значения Итого. Используя режим подбор параметра, рассчитайте, каково должно быть количество продукции шифра 41001, чтобы итоговая сумма получилась равной 100 000 руб. Постройте диаграмму, отображающую соотношение стоимости продукции. Сохраните таблицу.

Отчет по ПЗ.

1. Законспектируйте теоретическую часть в тетрадь.

2. Ответьте на контрольные вопросы.

3. Отчитайтесь преподавателю о проделанной работе.

Контрольные вопросы:

1. С помощью каких команд осуществляется подбор параметра?

2. Каким образом происходит копирование значений из одной таблицы в другую?