Для выполнения контрольной работы

Информационные технологии управления.

Г.В. Бунькова.

При выполнении контрольной работы:

· .Выполнить все приведенные примеры для работы в MS Excel.

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

· По варианту для работы в MS Excel представить отчет в текстовом редакторе. В отчете отобразить: таблицы, формулы для вычислений в таблицах, диаграммы.

· Текстовый документ должен содержать оглавление для созданного документа-отчета.

· Для отчета по работе иметь электронный вариант всех выполненных ЗАДАНИЙ.

· Ответить на теоретические вопросы, ответы продемонстрировать примерами

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

ПОДБОР ПАРАМЕТРА.

ФИЛЬТРЫ ДАННЫХ.

Подбор параметра является частью блока задач, который иногда называют инструментами анализа «что-если».

Команда Подбор параметра меню Данные позволяет определить неизвестное значение (параметр), которое будет давать ожидаемый результат. Использования команды Подбор параметра состоит из следующих шагов:

1. Решить нужную задачу с каким-либо начальным значением параметра;

2. Выбрать команду Подбор параметра меню Данные, раздел Работа с данными, пиктограмма Анализ «что-если» ;

3. В окне диалога Подбор параметра в поле «Установить в ячейке» задать абсолютную ссылку на ячейку, содержащую расчетную формулу, а в поле Значение — то значение, которое следует получить в качестве результата формулы;

4. В поле «Изменяя значение ячейки» ввести ссылку на ячейку с параметром;

5. Нажать кнопку ОК или клавишу Enter, на экране появится окно диалога Результат подбора параметра;

6. Для сохранения найденного значения нажать кнопку ОК. Для восстановления значения, которое было в ячейке с параметром до использования команды Подбор параметра нажать кнопку Отмена.

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

Рассмотрим использование функции Подбор параметра на примерах.

Задача 1.Необходимо распределить премию среди сотрудников. Каждому сотруднику начисляется определенный процент от начисленной зарплаты. Требуется определить какой процент премии необходимо установить, чтобы уложиться в премиальный фонд 10000р.

Решение.

На листе Excel создадим таблицы исходных данных.

Обратите внимание: при вводе значений в ячейки прежде определите формат ячеек – процентный, денежный. Для этого использовать пиктограммы раздела Число на ленте меню Главная. Создать таблицу для решения задачи. Заполнить данными 7 строк таблицы.

Заполнение столбцов «Удержано» и «Премия» выполняется по формулам.

Вспомним! При заполнении ячейки формулой или функцией первый символ должен быть знак равно, ввести с клавиатуры. Ссылки на ячейки, данные которых используются в формуле, следует указывать выполняя щелчок мышкой по ячейке с данными. При использовании одной и той же формулы в ячейках столбца или строки, формулу вводят маркером автозаполнения. Маркер автозаполненияэто вид указателя мыши (черный крестик -+),

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

правый нижний угол ячейки.

Расположение таблицы исходных данных и таблицы для решения задачи на листе Excel.

Столбец «Начислено» заполнить числами в денежном формате.

Столбец «Удержано»: ввести в ячейку D8знак=,сделать щелчком мыши ссылку на ячейку C8,ввести с клавиатуры знак «умножить» и щелкнуть по ячейкеD3, затем нажать функциональную клавишу F4. Введенная формула отображается в строке формул =C8*$D$3. Знак $в адресе ячейки означает, что эта ячейка является абсолютной ссылкой. Адрес абсолютной ячейки не изменяется при копировании формулы.

Столбец «Премия» заполняем с использованием формулы: начисление умножить на премию (аналогично предыдущим действиям).

После ввода формул в строке 8маркером автозаполнения копируем формулы во все строки таблицы.

В ячейке В15 ввести «Итого». Заполнить ячейки C15:E15вычислениями суммы – выделить ячейку, щелкнуть по значку Автосумма в меню Главная, скопировать формулу.

Первоначально для нашей задачи установили премию равной 10% и для этого значения выполнили необходимые вычисления.

Решим задачу нахождения процента премии при заданном премиальном фонде. Для этого будем использовать функцию Подбор параметра.

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

Выделим ячейку E15.Эта ячейка содержит формулу вычисления суммарной премии. В пункте меню Данные, Анализ «что-если» выбрать Подбор параметра. Появится окно функции Подбор параметра. Заполнить поля. Для поля «Установить в ячейке» сделать ссылку на ячейку Е15. В поле «Значение» ввести с клавиатуры 10000. Для поля «Изменяя значение ячейки» сделать ссылку на ячейку D4. Щелкнуть по кнопке ОК. После этого появится окно отображающее результаты поиска.

Решение найдено. Указано подбираемое значение и полученное (текущее) значение. Если результат содержит ошибку – например, подбираемое значение не по условию задачи нажать кнопку Отмена. И снова выполнить подбор параметра, вводя верное значение. Если результат соответствует задаче – ОК. Выполнить Подбор параметра для других значений премиального фонда.

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

Модель решения задачи.Для выполнения расчетов определим минимальный оклад - это оклад технического работника. Оклады других сотрудников будем определять относительно этого минимального оклада. Оклад сотрудника может быть больше в разы или больше на какую-то величину. Заведующий хозяйством получает в 1,5 больше, чем технический работник. Библиотекарь получает в 2 раза больше, чем технический работник. Консультант получает на 300 руб. больше библиотекаря. Заведующий библиотекой получает в 3,5 раза больше, чем технический работник.

Создайте на листе Excel таблицу:

Решение задачи
Таблица данных
Зарплата технического работника	4 350,00р.

Должность	Коэффициент	Оклад
Заведующий библиотекой	3,5	15 225,00р.
Библиотекарь 1		8 700,00р.
Библиотекарь 2		8 700,00р.
Консультант	300,00р.	9 000,00р.
Заведующий хозяйством	1,5	6 525,00р.
Технический работник		4 350,00р.
Суммарный размер зарплат	52 500,00р.

В соответствии с условием задачи заполните столбцы Коэффициент и Оклад. Затем выполните процедуру Поиск решения. Оцените полученный результат.

Задача 3.Известен объем платных услуг за 2011 год. И годовой прирост объема в процентах за 2011 год. Определить при каком годовом приросте платных услуг в 2012 году объем платных услуг буде равен 140000 руб.

Объем платных услуг
Объем платных услуг за 2010 год	127 365,00р.
Годовой прирост	2%

Объем платных услуг на 2011 год	129 275,48р.

Объем платных услуг =140000 руб. Определить % прироста.
Объем платных услуг за 2010 год	127 365,00р.
Годовой прирост	10%

Необходимый объем платных услуг на 2011 год	140 000,00р.

Создать на листе Excel таблицу, ввести расчетные формулы.

Объем платных услуг на 2011 год=B9+B9*B10.

Необходимый объем платных услуг на 2011 годвычисляется по формуле: =B3+B3*B4.

Используя «Подбор параметра» подобрать значение процента - Годовой прирост, при котором объем платных услуг будет равен 140000 руб. Выполнить подбор для других значений.

Фильтрация данных

Автофильтр.

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

При использовании фильтра таблица не должна иметь объединенных ячеек.

Рассмотрим применение фильтра на макете таблицы. Отменить объединение ячеек в заголовках. Удалить «Название таблицы», «Столбцы», «Строки».

Выделить любую ячейку таблицы, к которой будет применен фильтр. В пункте «Данные» щелкнуть по значку «Фильтр». Строка заголовков таблицы будет помечена кнопками-стрелками.

Для столбца, по данным которого должен быть выполнен выбор, открыть список (щелкнуть по кнопке-стрелке). В открывшемся окне выбрать «Числовые фильтры», затем «Настраиваемый фильтр».

В окне Пользовательский фильтр выбрать

из левого списка условие отбора, например, больше. Из правого списка выбрать числовое значение – среднее значение строк таблицы для столбца «Среднее столбцов».

После выполнения фильтрации, выбранные строки можно скопировать и разместить как отдельную таблицу. Отменить фильтр

Расширенный фильтр.

Расширенный фильтр применяют, используя критерии отбора, задаваемые на рабочем листе. Критериев может быть несколько. Для удобства ввода критериев на рабочем листе, лучше начиная с первой строки вставить копии названий столбцов таблицы. В ячейки под соответствующим столбцом таблицы ввести критерии. Для объединения критериев используют «И» - критерии записывают в одной и той же строке. Для выбора по функции «ИЛИ» - критерии записывают в различных строках.

Отбор по условию «ИЛИ».

Создадим критерий отбора данных исходной таблицы. Таблица после фильтра должна содержать данные удовлетворяющие следующему условию:

Название столбца 1 > 184.6 или Название столбца 3 > 325.6 6 или Название столбца 5 >389.2

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

На отдельном листе создать таблицу критерия. Выделить таблицу. В основном меню «Формулы» Выбрать «присвоить имя». Ввести имя «ИЛИ».

Перейти на лист с исходной таблицей. При использовании расширенного фильтра таблица может содержать объединенные ячейки (Заголвки).

В меню «Данные» в группе «Сортировка и фильтр» щелкнуть мышкой по кнопке «Дополнительно».

В открывшемся окне «Расширенный фильтр» заполнить поля.

Отметить переключатель «Скопировать результат в другое место», указать первую ячейку диапазона.

Вид таблицы после применения расширенного фильтра:

В результирующей таблице на все данные 1, 3, 5 столбцов удовлетворяют условию отбора, т.к. используется отбор по «или».

Отбор по условию «И».

Название столбца 2 > 294,8 и Название столбца 4 > 352.4

То есть все значения из таблицы, для которых выполняется каждое условие для второго и четвертого столбцов.

На отдельном листе создать таблицу критерия. Для условия «И» условия для всех столбцов записываются в одной строке. Выделить таблицу. В основном меню «Формулы» Выбрать «Присвоить имя». Ввести имя «И».

Названиестолбца 2	Названиестолбца 4
>294	>354

Применить расширенный фильтр. Результат разместить в другое место.

Строки	Названиестолбца 1	Названиестолбца 2	Названиестолбца 3	Названиестолбца 4	Названиестолбца 5	Сумма столбцов	Среднее столбцов
Название строки 3
Название строки 4							395,8
Название строки 5							330,8

В результирующей таблице все данные второго и четвертого столбцов удовлетворяют условию отбора.

Самостоятельно создайте другие критерии отбора. Можно комбинировать условия «ИЛИ» и «И» в одном критерии, записывая их с соблюдением правил.