Контрольные вопросы
Основные средства анализа данных.
Назначение команды «Подбор параметра».
Применение средства «Подбор параметра».
Лабораторная работа №9
Использование таблиц подстановки и Анализ бизнес-ситуаций с помощью Диспетчера сценариевв ms excel
Цель работы:изучить инструменты анализа данных вMS Excel Таблицы подстановки и Диспетчер сценария; приобрести навыки их применения для решения прикладных задач.
Методические указания
Таблица подстановки позволяет проводить анализ изменения результата при произвольном диапазоне исходных данных. Создание таблицы подстановки может оказаться очень удобным, если существует множество данных и требуется получить результат по какой-то формуле.
На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные.
Таблицу подстановки можно использовать для:
– изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул;
– изменения двух исходных значений, просматривая результат только одной формулы.
1. Использование Таблицы подстановки с одной изменяющейся переменной и несколькими формулами.
Перед созданием Таблицы подстановки необходимо подготовить рабочий лист, на котором будет решаться анализируемая задача. Например, рассчитаем сопротивление платиновой проволоки в зависимости от температуры. Отобразим лист с подготовленными данными (рис. 9.1). В ячейку D6 введена формула, по которой вычисляется сопротивление.
Рис. 9.1. Подготовка исходных данных
Для того, чтобы создать таблицу подстановки с одной переменной, следует сформировать таблицу таким образом, чтобы введенные значения были расположены либо в столбце, либо в строке. Формулы, используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода, т.е. ячейку, в которую подставляются значения из таблицы данных.
Затем в отдельный столбец или отдельную строку следует ввести список значений, которые будут подставляться в ячейку ввода (рис. 9.2). Если значения расположены в столбце, следует ввести формулу или адрес формулы в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения.
Рис. 9.2. Подготовка изменяемого диапазона и расчетных формул
для использования одномерной Таблицы подстановки
Затем следует выделить диапазон ячеек, содержащий формулу и значения подстановки (в нашем случае это диапазон B11:C18) и выбрать команду на вкладке «Данные» – «Таблица данных». В поле «Подставлять значения по столбцам в» ввести ссылку для значений подстановки в столбце. Excel будет подставлять значения температур в эту ячейку, просчитает формулу, расположенную в заголовке выделенного диапазона, и поместит под ней список результатов. Таблица подстановки заполнится значениями сопротивления, соответствующими каждой температуре (рис. 9.3).
Рис. 9.3. Заполнение таблицы подстановки
2. Использование Таблицы подстановки с двумя изменяющимися переменными.
Рассмотри применение Таблицы подстановки с двумя изменяющимися переменными на следующем примере: пусть требуется подобрать параметры тока и напряжения для платиновой проволоки. В качестве параметров изменяются напряжение и ток. Сначала следует подготовить рабочий лист с условиями поставленной задачи (рис. 9.4).
Рис. 9.4. Подготовка исходных данных
Таблицы подстановки с двумя переменными используют одну формулу с двумя наборами значений. Формула должна ссылаться на две ячейки ввода. Для того, чтобы создать таблицу подстановки для данного примера в ячейку листа B10 введем формулу, которая ссылается на две ячейки ввода и аналогична формуле в ячейке D8, которая применялась для расчета сопротивления. Ниже формулы введем значения первой переменной (параметр тока), правее формулы в строку введем значения второй переменной (напряжение) (рис. 9.5).
Рис. 9.5. Создание таблицы подстановки
Затем следует выделить диапазон ячеек, содержащий формулу и оба набора данных подстановки, выбрать команду Данные - Таблица подстановки. В поле Подставлять значения по столбцам в ввести ссылку для значений подстановки в столбце ($D$4), в поле Подставлять значения по строкам, соответственно, ссылку для значений подстановки в строке ($D$5). Получившаяся Таблица подстановки представлена ниже (рис. 9.6).
Рис. 9.6. Заполненная таблица подстановки
Примечание: После построения таблицы подстановки нельзя редактировать отдельно взятую формулу внутри таблицы. Значения данных внутри таблицы можно изменить, меняя значения исходных данных в левом столбце и верхней строке.
Мастер подстановок. Данный Мастер представляет собой средство для создания формул, основанных на функциях ИНДЕКС() и ПОИСКПОЗ().
Перед использованием Мастера подстановок следует предусмотреть следующее:
Расположение исходных данных на рабочем листе.
Расположение возвращаемых функций данных и данных для поиска (их нахождение в соответствующих колонках).
Строка для начала поиска.
Место на рабочем листе для помещения результата.
При решении многих экономических задач мы сталкиваемся с тем, что результат расчета зависит от нескольких параметров, которыми можно управлять. Достаточно часто бывает удобно проанализировать ситуацию при решении типовых задач для нескольких возможных параметров.
Диспетчер сценарием MS Excel позволяет автоматически выполнить Анализ «что если» для различных моделей. Можно создать несколько входных наборов данных (изменяемых ячеек) для любого количества переменных и присвоить имя каждому набору. По имени выбранного набора данных MS Excel сформирует результаты анализа на Рабочем листе. Кроме этого, диспетчер сценариев позволяет создать итоговый отчет по сценариям, в котором отображаются результаты подстановки различных комбинаций входных параметров.
Диспетчер сценариев вызывается с помощью команды Анализ «что если» на вкладке Данные (рис. 9.7).
Рис. 9.7. Окно Диспетчера сценариев
В появившемся окне с помощью соответствующих кнопок можно добавить новый сценарий, изменить, удалить или вывести существующий, а также объединить несколько различных сценариев и получить итоговый отчет для существующих сценариев.
Пример 1. Расчет эффективности капиталовложений с помощью функции ПС. Функция ПС возвращает текущий объем вклада на основе постоянных периодических платежей.
Предположим, что вас просят дать в долг 10000 руб. и обещают возвращать по 2000 руб. в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?
Решение.
Для решения этой задачи следует использовать функцию ПС(ставка; кпер; плт; пс; тип), где
ставка – процентная ставка за период;
кпер – общее число периодов выплат;
плт – величина постоянных периодических платежей;
бс – будущая стоимость (баланс наличности), который нужно достичь после последней выплаты. Если аргумент бз опущен, то он полагается равным 0;
тип – число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 – в начале периода.
Результатом работы Диспетчера сценариев является таблица «Структура сценария», которая отображает текущие (измененные) и исходные значения с соответствующими результатами. Столбец «Текущие значения» представляет значения изменяемых ячеек в момент создания Итогового отчета по Сценарию. Изменяемые ячейки для каждого сценария выделены серым цветом.