Анализ данных с помощью Диспетчера сценариев
ПРАКТИЧЕСКАЯ РАБОТА 1
Тема: ПОДБОР ПАРАМЕТРА. СРАВНЕНИЕ ЗНАЧЕНИЙ
Цель работы
Научиться:
· Подбирать параметры, при которых исследуемая величина принимает нужное значение;
· Сравнивать решения уравнения по таблице данных;
· Сохранять сценарии с помощью Диспетчера сценариев.
Теоретическая часть
В процессе обработки данных вас могут заинтересовать вопросы: «Что будет, если изменится размер взносов на погашение кредита на 50 000 руб?», «Что будет, если изменится процентная ставка?» и т. д. Выполнение подобного анализа вручную потребует больших затрат сил и времени. С помощью Microsoft Excel можно легко провести исследование, используя функцию Подбор параметра. Для просмотра списка возможных значений в зависимости от изменения одного или двух параметров используются таблицы подстановки данных. Если требуется проверить и сохранить все возможные сценарии данных, следует воспользоваться Диспетчером сценариев.
Для выполнения данной работы вам потребуется создать новую рабочую книгу. Присвойте первому листу книги имя «Размеры платежей», и введите данные, изображенные на рисунке. Ячейки С14:С20 заполните с помощью функции Автозаполнения и примените к ним процентный формат.
Ячейке D10 задайте пользовательский формат. Для этого вызовите из контекстного меню команду Формат ячеек, в списке Числовые форматы выберите (все форматы), из списка выберите Тип # ##0 и наберите «тыс.р.». Вы создали новый формат (рисунок 1.0)
|
|
|
Рис. 1.0 Задание собственного формата
Рис. 1.1 Исходные данные для работы
Проверка вводимых данных
В Microsoft Excel имеется возможность контролировать вводимые в ячейки данные. Создавая рабочий лист, вы заранее можете описать допустимые типы и значения данных для различных ячеек листа.
Упражнение 1.
В этом упражнении при помощи функции Проверка вводимых данных вы зададите допустимые сроки, на которые выдается кредит, а также укажете сообщения, которые Microsoft Excel будет выдавать в случае неправильно введенных данных.
1. Щелкните на ячейке D9. В меню Данные щелкните на команде Проверка. В диалоговом окне Проверка вводимых значений выберите вкладку Параметры. В поле Тип данных выберите Целое число, а в поле Значение выберите между.
2. В поле Минимум наберите минимальное значение срока выдачи кредита, например, 30. В поле Максимум введите максимальное значение, например, 1080.
3. Щелкните на вкладке Сообщения об ошибке. Проверьте, установлен ли флажок Выводить сообщения об ошибке. В поле Вид укажите Останов. В поле Заголовок наберите Неверное значение, а в поле Сообщение наберите Кредиты выдаются на срок от 30 до 1080 дней.
|
|
|
Рис. 1.2. Диалоговое окно Проверка вводимых значений
4. Щелкните на кнопке ОК.
5. Теперь введите в ячейку D9 значение, лежащее вне допустимого диапазона, например 20. Нажмите клавишу Еnter.
6. На экране появилось сообщение об ошибке.
7. Щелкните на кнопке Повторить и введите 360.
Если вы хотите убрать проверку, необходимо выделить ячейку, в меню Данные щелкнуть на команде Проверка, и в диалоговом окне Проверка вводимых значений щелкнуть кнопкой Очистить все. Затем щелкнуть на кнопке ОК.
Подбор параметра
При анализе данных вы, возможно, захотите определить, как повлияет на результат формулы изменение одной из переменных. Например, вы хотите знать, на сколько следует увеличить торговлю, чтобы достичь определенного уровня дохода, или какой кредит нужно взять вашей фирме, чтобы сумма ежемесячной выплаты не превосходила фиксированного значения. Для решения такого типа задач используют функцию Подбор параметра. Эта функция позволяет исследовать уравнения и формулы, исходя из итогового результата.
Упражнение 2.
В этом упражнении вы составите формулу, вычисляющую размер платежей по кредиту фиксированного размера в зависимости от величины процентной ставки, а затем рассчитаете необходимый размер кредита в соответствии с максимально допустимым значением ежемесячных выплат.
|
|
|
1. На листе Размеры платежей с помощью команды Создать имена присвойте ячейкам D8:D10 имена, соответствующие заголовкам в ячейках С8:С10.
2. Выделите ячейку D13, В эту ячейку вы введете формулу.
3. Щелкните на кнопке Мастер функций.
4. В окне списка Категория выделите строку Финансовые.
5. В окне списка Функция выделите ПЛТ. ПЛТ – это стандартная функция, вычисляющая платежи по кредитам. Щелкните на кнопке ОК. Откроется диалоговое окно Палитры формул.
6. Щелкните в поле Норма и на рабочем листе на ячейке с именем Проценты (ячейка D8). Эта ячейка определяет ежемесячные процентные ставки по кредиту, а ячейка под именем Проценты содержит годовую процентную ставку. Для определения месячной процентной ставки следует поделить годовую на 12.
7. В поле Норма после имени Проценты наберите /12. Теперь в поле Норма находится текст Проценты/12.
8. Щелкните в поле Кпер, а затем щелкните на ячейке с именем Срок_кредита (ячейка D9). Параметр Кпер относится к общему количеству платежей за период кредита.
9. Щелкните в поле Нз, введите знак (-) и затем щелкните на рабочем листе на ячейке с именем Кредит (ячейка D9).
|
|
|
Рис. 1.3. Функция Плт вычисляет величину выплаты за один период
10. Щелкните на кнопке ОК. Функция ПЛТ введена в ячейку D13. Она помещает в ячейку D13 величину ежемесячных выплат заданного кредита при процентной ставке 8,5% годовых. Примените к ячейке D13 формат # # #0 «тыс.р.». Ваш рабочий лист должен выглядеть как на рисунке 9.4.
Рис. 1.4. Результат вычисленной функции ПЛТ в ячейке D13
Упражнение 3.
В этом упражнении вы определите максимально допустимый размер кредита по заданной величине выплат и при фиксированном проценте.
1. На листе Размеры платежей выделите ячейку D13 и в меню сервис выберите команду Подбор параметра. Откроется диалоговое окно Подбор параметра. В поле Установить в ячейке находится адрес D13. Эта ячейка содержит формулу, вычисляющую выплаты по кредиту.
2. Щелкните в поле Значение и введите максимальную сумму, которую ваша фирма каждый месяц готова выплачивать по кредиту 900 при процентной ставке 8,5%.
3. Нажмите клавишу <Tab> или щелкните в поле Изменяя значение ячейки, а затем выделите ячейку D10. Таким образом, вы получите заданную величину кредита, изменяя сумму выплат в ячейке D13.
4. Щелкните на кнопке ОК или нажмите <Еnter>. Появится диалоговое окно Результат подбора параметра, сообщающее о том, что решение найдено. Искомое значение кредита при этом можно увидеть в ячейке D10, однако оно еще не введено в ячейку.
5. Щелкните на кнопке ОК.
Рис. 1.4 Результат подбора параметра
Новые значения теперь введены в ячейки. Пока диалоговое окно Результат подбора параметра не закрыто, ввод в ячейки рабочего листа новых значений можно отменить.
Примечание. Щелкая на кнопке Отменить 
стандартной панели инструментов, вы можете переключаться между новыми и старым значениями, наблюдая за изменением данных в ячейках.
Таблицы подстановки данных
Введя формулы в ячейки рабочего листа, вы можете провести их анализ и изучить область возможных значений этих формул. Таблицы подстановки данных позволяют оперативно вычислять все значения в каждой операции. Эти таблицы представляют собой диапазоны ячеек, показывающие результаты подстановки различных значений в одну или несколько формул.
Существуют два типа таблиц подстановки данных: таблицы подстановки с одной переменной и таблицы подстановки с двумя переменными. Таблицы данных с одной переменной позволяют исследовать влияние различных значений одной переменной на результат одной или нескольких формул. В таблицах с двумя переменными анализируется зависимость результата одной формулы от изменения двух входящих в нее переменных.
Упражнение 4.
Предположим, вам требуется узнать, как будет зависеть размер платежей от величины процентной ставки при фиксированном размере кредита. В этом упражнении вы создадите таблицу подстановки данных, использующую одну переменную, для определения размеров платежей по кредиту в зависимости от годовой процентной ставки, меняющейся от 6 до 9 процентов.
1. Выделите блок ячеек С13:D20.
2. В меню Данные выберите команду Таблица данных. Откроется диалоговое окно Таблица данных (рис.1.6).
Рис.1.6. Диалог Таблица подстановки
3. Щелкните в поле Подставлять значения по строкам в и выделите ячейку D8. Ячейка D8 (ячейка ввода) содержит исходное значение размера процентной ставки. Вы используете поле Подставлять значения по строкам в, так как значения подстановки расположены в столбце и при обращении к каждому из них нужно переходить на одну строку ниже.
4. Щелкните на кнопке ОК. В ячейках D14:D20 будут находиться результаты заданной формулы для различных аргументов.
5. Скопируйте формат ячейки D13 в диапазон ячеек D14:D20 (рис. 9.6).
Рис. 1.7. Вычисленные размеры платежей при изменяющейся процентной ставки
Теперь ваша таблица заполнена, вы можете сравнить полученные значения и сделать некоторые выводы. Если вас интересует вопрос, как влияют различные значения процентной ставки на полную сумму выплаты процентов, то необходимо добавить еще одну формулу в таблицу подстановки данных.
Упражнение 5.
В этом упражнении вы добавите в таблицу формулу для вычисления процентов, выплаченных за весь срок кредита.
1. В ячейку Е13 введите: =($D$13*Срок_кредита-Кредит). Эта формула рассчитывает полный размер процентов, которые предстоит выплатить за период погашения кредита.
2. Нажмите клавишу Еnter. В эту ячейку Microsoft Excel поместит результат вычислений по введенной формуле для значения процентной ставки 8,5%.
3. Выделите диапазон ячеек С13:Е20.
4. В меню Данные выберите команду Таблица данных.
5. Щелкните в поле Подставлять значения по строкам в.
6. Выделите ячейку D8 и щелкните на кнопке ОК. Каждое значение из столбца С будет подставлено в формулу в качестве величины процентной ставки, а результаты формулы будут занесены в таблицу подстановки данных в столбец под заголовком Уплаченный процент.
7. Скопируйте в диапазон ячеек Е13:Е20 формат ячеек D13: D20 (рис. 1.8).
Рис. 1.8 Вычисленная выплата процентов за срок кредита
Анализ данных с помощью Диспетчера сценариев
С помощью Диспетчера сценариев вы можете исследовать влияние изменения содержимого сразу нескольких ячеек вашего рабочего листа на результат расчета по формулам, в которых используются эти значения. Например, вы хотите определить, какова будет сумма выплат по кредиту при фиксированных значениях размера кредита и процентной ставки, причем исследовать различные комбинации этих величин. В этом случае вы можете создать отдельные сценарии для сохранения различных наборов данных. С помощью Диспетчера сценариев можно использовать каждый набор значений (сценарий) отдельно для анализа результатов пересчета листа.
Упражнение 6.
В этом упражнении вы создадите текущий сценарий и новый сценарий.
1. В меню Данные – Работа с данными – Анализ что-если выберите команду Диспетчер сценариев. Откроется диалоговое окно Диспетчер сценариев.
2. Щелкните на кнопке Добавить. Откроется диалоговое окно Добавление сценария.
3. В поле Название сценария наберите Текущий. Ваш первый сценарий использует текущие значения рабочего листа, поэтому вы будете иметь возможность снова подставить текущие значения после того, как будут созданы другие сценарии.
4. Удалите содержимое поля Изменяемые ячейки, а затем выделите ячейки D8:D10. Если понадобится, переместите диалоговое окно. Выделенные ячейки содержат величины, которые будут меняться при создании других сценариев.
5. Щелкните на кнопке ОК. Откроется диалоговое окно Значения ячеек сценария. В предыдущих упражнениях вы присвоили ячейкам названия, теперь эти названия можно видеть в диалоговом окне.
Рис. 1.9 Диалог Значения ячеек сценария
6. Не изменяя значения ячеек сценария, щелкните на кнопке ОК. Снова откроется диалоговое окно Диспетчер сценариев, и имя вашего сценария Текущий будет находиться в окне списка Сценарии.
7. Снова щелкните на кнопке Добавить.
8. В диалоговом окне Добавление сценария в поле Название сценария наберите Наименьший процент, а затем щелкните на кнопке ОК. Откроется диалоговое окно Значения ячеек сценария.
9. В поле Проценты наберите 0,06 и щелкните на кнопке ОК. Снова откроется диалог Диспетчер сценариев. Имя вашего нового сценария Наименьший процент находиться в окне списка Сценарии.
Теперь в списке диалогового окна Диспетчер сценариев находятся два сценария. Щелкните на кнопке Закрыть.
Чтобы просмотреть созданные сценарии, выполните следующее упражнение.
Упражнение 7.
1. В меню Сервис выберите команду Сценарии.
2. В окне списка Сценарии выделите сценарий Наименьший процент.
3. Щелкните на кнопке Вывести. Microsoft Excel подставляет значения сценария Наименьший процент в ваш рабочий лист. Чтобы увидеть изменения данных на рабочем листе, возможно, вам понадобится переместить окно Диспетчер сценариев.
4. Щелкните на кнопке Закрыть.Чтобы вернуться к исходным данным рабочей таблицы можно, выбрав из списка сценариев имя Текущий и щелкнув на кнопке Вывести.
Дата добавления: 2019-02-22; просмотров: 289; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!