Пакет анализа данных в MS EXCEL

Лекция Пакет анализа данных в MS EXCEL. Анализ «Что если» или подбор параметров Содержание Введение. 3 Общее описание пакета «Анализ» 4 Загрузка пакета «Анализа» 5 Анализ «что если». 6 Анализ чувствительности с помощью таблиц данных. 7 Анализ. 8 Список литературы 13 Список иллюстраций 14 Введение «Уейн Л. Винстон (Wayne L. Winston) — профессор в области принятия решений школы Kelley School of Business Университета штата Индиана. Он получил множество наград за подготовку MBA. Более чем за 20 лет он научил сотрудников компаний Fortune 500 использовать MS Excel, чтобы принимать эффективные деловые решения. Уэйн и его партнер по бизнесу, Jeff Sagarin (Джефф Сагарин), разработали систему отслеживания и оценки статистики игрока, которая используется в профессиональном баскетбольном клубе "Даллас Мэверикс". (Microsoft) Программные пакеты MS Excel используются большим количеством пользователей по всему миру. Благодаря большому количеству функций и простате работы они позволяют решать большинство задач, связанных с обработкой данных. В этой статье рассмотрим использование средства анализа «что если» в программном пакете «Анализ» MS Excel» Общее описание пакета «Анализ данных» «При проведении сложного статистического или инженерного анализа можно упростить процесс и сэкономить время, используя надстройку «Пакет анализа». Для анализа данных с помощью этого пакета следует указать входные данные и выбрать параметры; расчет будет выполнен с помощью подходящей статистической или инженерной макрофункции, а результат будет помещен в выходной диапазон. Некоторые инструменты позволяют представить результаты анализа в графическом виде.» (Microsoft). Функции анализа данных можно применять только на одном листе. Если анализ данных проводится в группе, состоящей из нескольких листов, то результаты будут выведены на первом листе, на остальных листах будут выведены пустые диапазоны, содержащие только форматы. Чтобы провести анализ данных на всех листах, повторите процедуру для каждого листа в отдельности. Ниже описаны инструменты, включенные в пакет анализа. Для доступа к ним нажмите кнопку Анализ данных в группе Анализ на вкладке Данные. Если команда Анализ данных недоступна, необходимо загрузить надстройку "Пакет анализа". Рисунок 1. Общий вид пакета "Анализ" Загрузка пакета «Анализ данных» «Пакет анализа данных представляет собой надстройка MS MS Excel, т. е. программу, которая становится доступной при установке MS Office или MS Excel. Однако, чтобы использовать надстройку в MS Excel, необходимо сначала загрузить ее. 1. Откройте вкладку «Файл» и выберите пункт «Параметры». 2. Выберите команду «Надстройки», а затем в поле «Управление» выберите пункт «Надстройки MS Excel». 3. Нажмите кнопку «Перейти». 4. В окне «Доступные надстройки» установите флажок «Пакет анализа», а затем нажмите кнопку «ОК». ◦ Если надстройка «Пакет анализа» отсутствует в списке поля «Доступные надстройки», нажмите кнопку «Обзор», чтобы найти ее. ◦ В случае появления сообщения о том, что пакет анализа не установлен на компьютере, нажмите кнопку «Да» для его установки. 5. После загрузки пакета анализа в группе «Анализ» на вкладке «Данные» становится доступной команда «Анализ данных».» (Microsoft) Анализ данных «Что если» Анализ данных "Что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе. В MS Excel предлагаются средства анализа "что если" трех типов: • Сценарии; • Таблицы данных; • Подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Таблицы данных работают только с одной или двумя переменными, но могут принимать множество различных значений для них. Сценарий может содержать несколько переменных, но допускает не более 32 значений. Действие подбора параметров отличается от сценариев и таблиц данных — берется результат и определяются возможные входные значения для его получения. Помимо этих трех средств можно установить надстройки для выполнения анализа "что если", например надстройку "Поиск решения". Эта надстройка похожа на подбор параметров, но позволяет использовать больше переменных. Вы также можете создавать прогнозы, используя маркер заполнения и различные команды, встроенные в MS Excel. Анализ чувствительности с помощью таблиц данных Допустим, Вы, как руководитель станции технического обслуживания, решили продавать своим клиентам моторное масло известного бренда. Перед началом реализации, необходимо убедиться в рентабельности этой идеи, а именно, как прибыль, доход и переменные затраты будут зависеть от цены и стоимости единицы продукции. В примере с моторным маслом исходные данные будут включать: • Цену продажи 4-ёх литровой канистры масла; • Закупочную стоимость канистры моторного масла; • Чувствительность спроса на моторное масло к цене; • Годовые постоянные затраты на обеспечение работы магазина по продаже моторного масла. На основе исходных предположений можно рассчитать интересующие показатели. Для примера, это могут быть: • годовая прибыль; • годовой доход; • годовые переменные расходы. «Анализ чувствительности  позволяет определить, как выходные данные таблицы изменяются в зависимости от изменения исходных данных. Например, требуется проследить, как изменение цены продукта влияет на годовую прибыль, доход и переменные затраты. Таблица данных в MS Excel упрощает изменение одного или двух блоков исходных данных и анализ чувствительности. С помощью одномерной таблицы данных можно определить, как изменение одного блока исходных данных изменит все результаты. С помощью двумерной таблицы данных можно определить, как изменение двух блоков исходных данных изменит единственный результат. В следующих трех примерах показано, как использовать таблицу. Пример Анализа данных. Исходные предположения приведены в ячейках B1:B4. Предположим, что годовой спрос на масло равен 250 канистр в год. Цена реализации 3000 руб. Закупочная цена 2000 руб. постоянные затраты 45000 руб. в год. Рисунок 2. Данные, которые влияют на доходность. Ежегодный доход рассчитан в ячейке B5 с помощью формулы (B2*B1). В ячейке B6 рассчитаны годовые переменные затраты с помощью формулы (B3*B2). И наконец, в ячейке B7 рассчитана годовая прибыль с помощью формулы (B5-B6-B4). Для определения оптимальной цены реализации, нам необходимо построить одномерную таблицу данных. В ней введем интересующий нас диапазон цен с шагом 100 руб. Рисунок 3. Создание таблицы данных. В качестве ячейки исходного столбца можно выбрать любую ячейку, куда будут вноситься исходные данные, то есть значения из первого столбца диапазона таблицы данных. Так как исходными данными являются цены, выбираем ячейку исходного столбца D1. После нажатия кнопки ОК в MS Excel создается одномерная таблица, показанная на рисунке 4. Рисунок 4.Одномерная таблица данных с изменяющимися ценами. В связи с тем, что у нас переменные расходы увеличиваются только до цены 3700, далее наблюдаем снижение, оптимальной для реализации была б цена что превышает эту сумму. Подбор параметров. Предположим, мы решили создать магазин по продаже моторного масла, но нам нужны инвестиции. Мы знаем, что кредитор может выдать кредит с фиксированной ставкой 16,5 %, основанный на 80% стоимости всего кредита (то есть 20% будет составлять ваш авансовый платеж). Вопрос состоит в следующем: «Какова максимальная сумма, которую я смогу взять в кредит?» Другими словами, какое значение в ячейке С4 вызовет появление результата формулы в ячейке С11, равного ежемесячному платежу в 48 тыс. руб.? Чтобы ответить на этот вопрос, выполните следующие действия. 1. Выберите Данные ► Работа с данными ► Анализ "что-если" ► Подбор параметра. Появится диалоговое окно Подбор параметра. 2. Заполните три поля (рис.5) подобно формированию предложения: вы хотите установить в ячейку С11 значение 48000 путем изменения значения ячейки С4. Введите эту информацию в диалоговое окно, вводя ссылки на ячейки либо указывая их с помощью мыши. 3. Нажмите кнопку ОК, чтобы начать процесс подбора параметра. Менее чем за секунду MS Excel выведет диалоговое окно Статус подбора параметра, которое показывает целевое значение и значение, рассчитанное MS Excel. В этом случае программа находит точное значение. Теперь в таблице в ячейке В4 показано найденное значение(494420,6 руб.). В результате этого значения ежемесячный платеж составит 58250 руб. На данный момент у вас есть два варианта: • нажмите кнопку ОК, чтобы заменить исходное значение найденным; • нажмите Отмена, чтобы восстановить таблицу такой, какой она была, прежде чем была вызвана команда Подбор параметра. Этот пример очень простой. Возможности такой функции, как подбор параметров, станут более очевидными, если вы будете иметь дело с составной моделью, использующей множество взаимосвязанных формул. Рисунок 5.Диалоговое окно «Подбор параметра». Рисунок 6. Полученный результат по расчетам. Список литературы MS [В Интернете] // Support Office. - https://support.office.com. Список иллюстраций Рисунок 1. Общий вид пакета "Анализ" 3 Рисунок 2. Данные, которые влияют на доходность. 7 Рисунок 3. Создание таблицы данных. 8 Рисунок 4.Одномерная таблица данных с изменяющимися ценами. 9 Рисунок 5.Диалоговое окно «Подбор параметра». 11 Рисунок 6. Полученный результат по расчетам. 11