тематическая суть задачи состоит в решении уравнения fxгде функция fx описывается заданной формулой x ~ ис
Работа добавлена на сайт samzan.net: 2016-03-13
Поможем написать учебную работу
Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
от 25%
Подписываем
договор
S Excel 2007 Практ. раб. 7
- Анализ данных
Подбор параметра
Пусть имеется формула, которая прямо или косвенно зависит от некоторого параметра. Задача состоит в определении такого значения этого параметра, которое позволяет получить нужный результат формулы.
Математическая суть задачи состоит в решении уравнения f(x)=a, где функция f(x) описывается заданной формулой, x – искомый параметр, a – требуемый результат формулы.
Для решения этой задачи необходимо выполнить следующие действия:
- Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.
- На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Подбор параметра.
- В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).
- В поле Значение ввести значение, которое нужно получить по заданной формуле.
- В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).
- Щелкнуть по кнопке ОК.
После выполнения команды в изменяемой ячейке появится значение параметра, при котором результат формулы равняется заданной величине. При этом будет пересчитана вся таблица, т.е. изменятся значения, прямо или косвенно зависящие от изменяемого параметра.
Таблица подстановки данных
Пусть имеется формула, которая зависит от некоторых переменных. Задача состоит в определении результатов формулы при различных значениях этих переменных.
Математическая сущность задачи – табулирование функции.
Эта задача – обратная задаче подбора параметров.
Анализ выполняется при помощи таблицы подстановки данных.
Таблица подстановки данных представляет собой блок ячеек, в котором выводятся результаты подстановки различных значений переменных в одну или несколько формул.
Анализ может проводиться для функций с одной или двумя переменными. Причем, в случае одной переменной можно табулировать сразу несколько функций, зависящих от этой переменной.
Таблица подстановки с одной переменной
Анализ формулы начинается с подготовки таблицы подстановки:
- Левую верхнюю ячейку блока, отведенного под таблицу, оставить пустой.
- В левый столбец блока, начиная со второй ячейки, последовательно ввести значения варьируемой переменной.
- В верхнюю строку блока, начиная со второй ячейки, ввести ссылки на ячейки с анализируемыми формулами.
Допускается и другая ориентация таблицы, когда значения варьируемой переменной вводятся в первую строку, а анализируемые формулы – в первый столбец блока.
- Выделить таблицу подстановки (в ячейки, расположенные рядом с таблицей, можно ввести пояснительные надписи, но эти ячейки не входят в таблицу подстановки данных и, следовательно, не выделяются).
- На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Таблица данных.
- Если значения варьируемой переменной расположены в столбце, то надо щелкнуть по полю Подставлять значения по строкам в и ввести в это поле адрес изменяемой ячейки (т. е. ячейки, которая играет роль варьируемой переменной в формуле).
Если значения варьируемой переменной расположены в строке, то адрес изменяемой ячейки вводится в поле Подставлять значения по столбцам.
- Щелкнуть по кнопке ОК.
Таблица будет заполнена значениями.
Таблица подстановки с двумя переменными
В случае анализа зависимости формулы от двух переменных таблица подстановки подготавливается по-другому:
- В левую верхнюю ячейку блока, отведенного под таблицу, ввести ссылку на ячейку с анализируемой формулой.
- В левый столбец блока, начиная со второй ячейки, последовательно ввести значения одной из варьируемых переменных.
- В верхнюю строку блока, начиная со второй ячейки, ввести значения другой варьируемой переменной.
- Выделить таблицу подстановки.
- На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Таблица данных.
- В поле Подставлять значения по строкам в ввести ссылку на ячейку с переменной, значения для которой расположены в левом столбце таблицы подстановки.
- В поле Подставлять значения по столбцам в ввести ссылку на ячейку с переменной, значения для которой расположены в первой строке таблицы подстановки.
- Щелкнуть по кнопке ОК.
Таблица будет заполнена значениями.
Диспетчер сценариев
Средства Microsoft Excel позволяют создавать и сохранять в виде сценариев наборы входных значений, приводящих к различным результатам.
Сценарий – это множество входных значений, называемых изменяемыми ячейками, которое можно сохранить под указанным именем, а затем применить к модели рабочего листа, чтобы проследить, как значения изменяемых ячеек влияют на другие значения модели.
Создание сценария
Чтобы создать сценарий, следует:
- На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев.
- В открывшемся диалоговом окне щелкнуть по кнопке Добавить. Откроется окно Добавление сценария.
- В поле Название сценария ввести имя сценария.
- В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише Ctrl.
- Щелкнуть по кнопке ОК.
- В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки.
- Для создания других сценариев щелкнуть по кнопке Добавить (откроется диалоговое окно Добавление сценария) и повторить пункты 3−6.
Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем – по кнопке Закрыть.
Рекомендуется сохранить в качестве сценария первоначальные значения изменяемых ячеек, чтобы потом можно было быстро восстановить эти значения.
Просмотр сценария
Для просмотра сценария нужно:
- На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев.
- В поле Сценарии открывшегося диалогового окна выделить имя сценария, который необходимо просмотреть.
- Щелкнуть по кнопке Вывести.
Вместо пунктов 2 и 3 можно дважды щелкнуть по имени нужного сценария.
Редактирование сценария
Чтобы отредактировать сценарий, надо:
- На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев.
- В поле Сценарии выделить имя сценария, который необходимо отредактировать.
- Щелкнуть по кнопке Изменить.
- Внести необходимые изменения: можно изменить имя сценария, изменяемые ячейки, значения изменяемых ячеек.
- Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем – по кнопке Закрыть.
Создание итогового отчета по сценариям
Для создания итогового отчета по сценариям следует:
- На вкладке Данные в группе Работа с данными в списке Анализ «что-если» выбрать команду Диспетчер сценариев.
- В открывшемся диалоговом окне щелкнуть по кнопке Отчет.
- Выбрать тип отчета: Структура или Сводная таблица.
В отчете типа Структура перечислены все сценарии с определенными для них значениями ячеек. Этот тип отчета полезен тогда, когда каждый пользователь определяет сценарий со своими данными.
Отчет типа Сводная таблица предоставляет возможность эмпирического анализа сценариев. Этот тип отчета полезен тогда, когда сценарий имеет несколько наборов значений изменяющихся ячеек, заданных различными пользователями. С помощью сводных таблиц можно выполнить анализ для разных комбинаций сценариев.
- В поле Ячейки результата ввести ссылки на ячейки, значения которых надо представить в отчете. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише Ctrl. Итоговые отчеты создаются на отдельных листах.
Практическая работа 7
Подбор параметра
- В таблице на листе Повременная оплата определить:
- каков должен быть процент премии, чтобы сумма значений столбца Премия была равна заданной величине;
- каков должен быть процент налога, чтобы сумма значений столбца Налог была равна заданной величине;
- каков должен быть МРОТ, чтобы сумма значений столбца К выдаче была равна заданной величине.
- В таблице на листе Сдельная оплата определить:
- какова должна быть норма выработки, чтобы сумма стоимостей работ всех сотрудников равнялась заданному значению;
- каков должен быть процент повышения расценки за превышение нормы выработки, чтобы сумма значений столбца К выдаче была равна заданной величине.
- В таблице на листе Телефон 1 определить:
- каков должен быть тариф будней в дневное время, чтобы сумма стоимостей разговоров всех клиентов была равна заданному значению;
- каков должен быть тариф выходных дней, чтобы сумма стоимостей разговоров всех клиентов была равна заданному значению;
- каков должен быть тариф будней в ночное время, чтобы сумма стоимостей разговоров всех клиентов была равна заданному значению.
- Открыть новый лист и присвоить ему имя Анализ данных. С помощью инструмента Подбор параметра решить уравнения:
1) 2 x – ln x – 4 = 0 (Ответ: x 2,45)
2) 4 x = cos x (Ответ: x 0,24)
3) x3 – 5 x + 0,1 = 0 (Ответ: x 0,02)
Таблица подстановки данных
- На листе Расчет зарплаты построить таблицу зависимости суммы значений столбца Налог от процента налога (подставить значения 11%, 13% и 15%).
- На листе Повременная оплата построить таблицу зависимости суммы значений столбца Налог от процента премии (7,10 и 15%).
- На листе Повременная оплата построить таблицу зависимости суммы значений столбца К выдаче от процента премии (7,10 и 15%) и процента налога (11%, 13% и 15%).
- На листе Сдельная оплата построить таблицу зависимости суммарного значения столбца Стоимость работы от нормы выработки (9, 10, 11 изд.).
- На листе Сдельная оплата построить таблицу зависимости суммарного значения столбца К выдаче от расценки (900, 1 000 и 1 200 руб.) и от процента повышения расценки за превышение нормы (15, 20 и 25%).
- На листе Телефон построить таблицы зависимости суммарной стоимости разговоров от:
- тарифа выходных дней (подставить значения 0,11; 0,13; 0,15; 0,17; 0,19 долл.);
- тарифов будней в дневное и ночное время (соответственно 0,35; 0,37; 0,39; 0,41; 0,43 и 0,08; 0,09; 0,10; 0,11; 0,12 долл.);
- верхней границы ночного и дневного времени (7; 8; 9);
- верхней и нижней границ ночного и дневного времени (соответственно 7; 8; 9 и 20; 21).
- На листе Анализ данных с помощью инструмента Таблица подстановки построить таблицу значений функции для x от 0 до 2,0 с шагом 0,1.
- На листе Анализ данных с помощью инструмента Таблица подстановки построить таблицу умножения как таблицу значений функции двух переменных при изменении обеих переменных от 1 до 10 с шагом 1.
Сценарии
- На листе Повременная оплата присвоить имена ячейкам, содержащим значения процента премии, процента налога, процента отчислений в пенсионный фонд, а также ячейкам, содержащим суммарные значения столбцов Премия, Налог и К выдаче.
С помощью Диспетчера сценариев проанализировать, как зависят суммы премий, налогов и итоговых выплат от процентов премии, налога и отчислений в пенсионный фонд. Для этого создать три сценария, в которых задать следующие значения изменяемых ячеек:
- в первом сценарии сохранить первоначальные значения изменяемых ячеек;
- во втором сценарии: процент премии – 15%, процент налога – 15%;
- в третьем сценарии: процент премии – 5%, процент налога – 10%, процент отчислений в пенсионный фонд – 1,5%.
Создать итоговый отчет типа Структура.
- На листе Сдельная оплата присвоить имена ячейкам, содержащим значения нормы выработки, расценки и процент повышения расценки, а также ячейкам, содержащим суммарные значения стоимости работы и итоговых выплат.
С помощью Диспетчера сценариев проанализировать, как зависят суммарные значения стоимости работы и итоговых выплат от нормы выработки, расценки и процента повышения расценки. Для этого создать три сценария, в которых задать следующие значения изменяемых ячеек:
- в первом сценарии сохранить первоначальные значения изменяемых ячеек;
- во втором сценарии: норма выработки – 12, расценка – 1200 руб. за одно изделие;
- в третьем сценарии: норма выработки – 12, расценка – 1500 руб. за одно изделие; процент повышения расценки – 15%.
Создать итоговый отчет типа Структура.
- На листе Телефон присвоить имена ячейкам, содержащим значения тарифа выходных дней, тарифов будней в дневное и ночное время, а также ячейке, содержащей суммарную стоимость разговоров.
С помощью Диспетчера сценариев проанализировать, как зависит суммарная стоимость разговоров от тарифов. Для этого создать четыре сценария, в которых задать следующие значения изменяемых ячеек:
- в первом сценарии: тариф выходных дней 0,20 долл.;
- во втором сценарии: тарифы будней в дневное время 0,40 долл. и в ночное время 0,15 долл.;
- в третьем сценарии: тарифы выходных дней 0,20 долл., будней в дневное время 0,30 долл. и в ночное время 0,12 долл.;
- в четвертом сценарии: тарифы выходных дней 0,12 долл., будней в дневное время 0,44 долл. и в ночное время 0,12 долл.
Создать итоговый отчет типа Структура.
PAGE 6