Лабораторная работа № 6. Обработка и анализ данных в MS Excel.

Таблица данных

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

Материал для работы: файл-заготовка ЛР6.xlsx.

Результат работы: файлы ЛР6_ФАМ.xlsx.

Выполнение работы

Часто встречаются задачи, в которых итоговый результат требуется вычислить для различных возможных значений одной или двух переменных. Пользователю необходимо оценить все возможные варианты и отобрать тот, который представляется оптимальным. Для решения таких задач используется команда Таблица данных, расположенная на вкладке Данные в области Работа с данными в группе инструментов Анализ «что если». Полученная таблица данных представляет собой диапазон ячеек, который содержит результаты подстановки различных значений переменной в одну или несколько имеющихся в таблице формул.

Таблица данных с одной переменной. Определите, как различные годовые процентные ставки (предположим, что кредит берется в разных банках) влияют на размер выплат по кредиту (лист Кредитование файла ЛР6). Задача решается с помощью команды Таблица данных и функции ПЛТ, которая имеет следующий синтаксис:

=ПЛТ(годовая процентная ставка/12; срок кредитования; сумма кредитования)

В примере: сумма кредитования составляет 10 000 руб.; срок кредитования – три года;

годовая процентная ставка – 11,5%.

   определите в ячейке В5 ежемесячные выплаты по кредиту с помощью функции ПЛТ. Для аргумента сумма кредитования поставьте знак «минус», который указывает на расход денежных средств. Иначе результат вычислений будет отрицательным;

   в ячейках В6 и В7 определите общую сумму выплат (ежемесячные выплаты умноженные на срок кредитования) и выплаты по процентам (разность между общей суммой выплат и суммой кредитования);

   заполните блок ячеек В4:G4 значениями различных годовых процентных ставок с помощью арифметической прогрессии с шагом 0,5% (Главная/Редактировать/Заполнить/Прогрессия);

   выделите блок В4:G7. Именно он будет заполняться данными вычислений;

   последовательно выберите Данные/Работа с данными/Анализ «что если»/Таблица данных;

   в окне диалога (рисунок 17) в поле Подставлять значения по столбцам укажите ссылку на ячейку В4 (годовая процентная ставка), которая является переменной для расчетов, и значения ставки располагаются горизонтально (по столбцам)/ОК. Появится таблица с данными по кредиту при различных годовых процентных ставках.

Рисунок 17 – Окно диалога с установленными параметрами для таблицы данных с одной переменной.

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

 создайте копию листа, удалите лишние данные. Используя автозаполнение и различное форматирование, добейтесь оформления таблицы как показано на рисунок 18;

Рисунок 18 – Подготовленная таблица для заполнения данными.

выделите блок ячеек В6:G11 и выберите команды таблица данных; установите параметры так, как показано на рисунке 19. Обратите

внимание в каких полях какие адреса исходных значений ячеек установлены;

 проанализируйте полученные результаты.

Рисунок 19 – Окно диалога с установленными параметрами для таблицы данных с двумя переменными.

Задания для самостоятельной работы

1.       Определите эффективность рекламы с помощью коэффициента окупаемости инвестиций (лист Эффективность рекламы файла ЛР6), показывающего прибыльность или убыточность тех или иных затрат. Правила вычислений и трактовка результата приводится в файле.

Получите различные значения коэффициенты при различных значениях: проданного товара после рекламы;

проданного товара после рекламы и затрат на размещение рекламы.

2.       Данные представьте графически, используя различные виды диаграмм (лепестковая, гистограмма, для разных рядов данных гистограмма и график).