Тема 5. Создание таблиц данных (таблицы подстановок)

Таблицы подстановки позволяют вычислять и анализировать данные в тех случаях, когда необходимо найти результат для нескольких значений в оной или двух исходных ячейках. В экономике, таблицы подстановки используют для проведения анализа данных. Напомним, что в новых версиях Excel, термин «Таблицы подстановок» заменён на новый термин – «Таблицы данных». Изменение названия процедуры работы с данными, нашло своё отражение в группе «работа с данными» на вкладке «Данные». Хотя смысл операций остался прежний – создать таблицу на листе Excel, в которую будут подставлены данные для одновременного просмотра нескольких различных значений (из-за слишком частого использования сочетаний слов – «Таблица данных», термин таблицы подстановок используется в литературе о работе с Excel до сих пор.). Следует отметить, что анализ данных связан с просмотром нескольких вариантов решения задачи, с целью выбора наиболее предпочтительного, в сложившейся ситуации. В Excel заложена возможность создавать таблицы данных для анализа двух типов: таблицы с одной переменной, и таблицы с двумя переменными.

Создание таблицы данных с одной переменной.

Ситуация, предприниматель желает рассмотреть варианты суммы возврата вклада, в зависимости от процентной ставки, которую предлагают различные банки для одного и того же срока вклада. В разделе (Тема 4) речь шла о подборе параметра для одной переменной, которая имела функциональную связь с элементами таблицы.

Воспользуемся уже готовой таблицей (с вычисленными значениями коэффициента увеличения вклада и конечной суммы вклада через 5 лет при фиксированной процентной ставки) из темы 4, и скопируем её на чистый лист книги Excel. Или создадим такую таблицу заново. Внешний вид таблицы с исходными данными для создания таблицы подстановок показан на рис. 1.

Рис. 1. Таблица с исходными данными для проведения сравнительного анализа

Этапы построения таблицы данных или, как чаще говорят – таблицы подстановки.

§ Создать на листе дополнительные заголовки столбцов, которые показаны на рис. 2. Первый столбец будет иметь заголовок «Варианты процентной ставки», второй столбец будет иметь имя «Сумма возврата», Третий столбец – «Прирост за время хранения».

§ Заполните первый столбец с вариантами процентной ставки, в примере указаны значения ставок от 3% до 10% (обратите внимание, что ввод данных в этом столбце начат после оставленной пустой ячейки E3).

§ В первую ячейку F3 после заголовка «Сумма возврата» следует ввести или формулу =C3*C6, или ссылку на содержимое ячейки C7 (=C7).

§ В первую по порядку ячейку G3 столбца с наименованием «Прирост за время хранения» следует ввести формулу (=F3-$C$3). На рис. 2 видно, что в ячейке G3 введена формула: =F3-Размер_вклада. Напомним, чтобы не вводить в формулу знак доллара ($) для фиксации положения ячейки при размножении её содержимого, предварительно ячейке С5 было присвоено имя, в данном случае это наименование строки.

Рис. 2. Подготовительные действия на листе Excel перед созданием таблицы данных (таблицы подстановок)

§ Выделите на листе (в данном примере) ячейки E3:F11, как показано на рис. 3, а затем раскройте пиктограмму , и выберите строку «Таблица данных».

Рис. 3. Подготовка к созданию таблицы с данными

§ В диалоговом окне «Таблица данных» в поле «Подставлять значения по строкам в:» (рис. 4) введите абсолютный адрес ячейки ввода (с процентной ставкой) - $C$5 или укажите имя ячейки, которое было создано ранее, а затем нажмите на кнопку ОК.

Рис. 4. Диалоговое окно Таблица данных с введённым адресом ячейки, в которой меняются значения для построенной таблицы

§ В столбце с наименованием «Прирост за время хранения» таблицы для ячеек G4:G11 выполните операцию размножения формулы, которая находится в ячейке G3. Окончательный вариант представления таблицы данных (таблицы подстановки), в которой подставлены данные для суммы возврата средств через 5 лет в зависимости от условий годовой процентной ставки можно посмотреть на рис. 5.

Рис. 5. Окончательный вариант представления таблицы данных (таблицы подстановки), в которой подставлены данные для суммы возврата средств через 5 лет в зависимости от условий годовой процентной ставки

§ Постройте график по данным созданной таблицы данных.

Создание таблиц данных с двумя переменными

Исследователи очень часто пользуются номограммами, которые строятся на основе одной и той же зависимости, но для различных входных данных. Такой же эффект можно достичь при использовании технологии таблиц данных (таблиц подстановок). Рассмотрим модель исследования поведения экономической системы.

Предположим, предприятие собирается провести рекламную компанию о реализации нового вида продукции. Реклама должна быть направлена потенциальному покупателю по почте. Известно, что не более 3,5% респондентов делают заказы на рекламируемую продукцию, остальная рассылка пропадает. Но, тем не менее, за каждый заказ предприятие получит доход в размере 22 рубля. Организация рассылки рекламы включает такие затраты, как почтовые расходы – 0,32 руб. за одно почтовое отправление и расходы на печать рекламных материалов. Предположим, что фирма, которая предоставляет полиграфические услуги на печать рекламной продукции, ведёт гибкую политику цен. Полиграфисты предлагают стоимость печатных материалов 0,20 руб. за штуку, если тираж не превышает 200000 экземпляров; 0,15 руб. за штуку для тиража от 200001 до 300000 экземпляров; 0,10 руб. за штуку, если тираж превышает 300000 экземпляров.

С позиции руководства предприятия, которое желает реализовать новую продукцию и получить при этом прибыль, необходимо воспользоваться среднестатистическими данными по отрасли, сделать заказ печатной продукции, разослать рекламу и ожидать конечных результатов. Плановый отдел получает указания, провести анализ возможных вариантов развития ситуации (которая зависит от тиража рекламной продукции, реального количества респондентов, сделавшие заказ новой продукции). Напомним, что может случиться так, что полученные доходы от реализации продукции не смогут покрыть расходов на рекламу.

Решение задачи можно осуществить различными способами, в данном случае воспользуемся средством Excel – Таблицы данных (таблицы подстановок), построим модель вычисления прибыли для изменения начальных условий в исходных данных.

Шаг 1.Создание таблицы с исходными данными для модели. На рис. 6 показана таблица «Исходные данные» в ячейках A3:B5. В таблице содержатся сведения о тираже рекламной продукции в ячейке B4 (количество разосланных материалов – 275000 экз.) и среднестатистический процент откликов в ячейке B5 (процент ответивших - 2,5%).

Рис. 6. Представление модели вычисления прибыли от реализации новой продукции при определённых условиях

Шаг 2. Создание таблицы для расчета прибыли, при условии известных показателей, которые отражены в ячейках A7:B14 («Расчёты» рис. 6). Справа от таблицы даны пояснения для формул, которые используются для выполнения расчётов в столбце B. Напомним, что стоимость почтовых расходов и доход от проданной продукции, установлены ранее, а все остальные величины в таблице «Расчёты» вычисляются. Для заданных исходных данных конечное значение прибыли отражено в ячейке B14. Естественно возникают вопросы, а можно ли получить прибыль выше, а в каких случаях прибыли не будет (расходы превысят доходы) и т.п.

Шаг 3. Подготовка создания таблицы данных. Таблица данных – это отображение автоматического вычисления заданного параметра для изменяемых исходных данных (в рассматриваемом случае заданным параметром является Прибыль, хотя можно задать и другой параметр, например, «Суммарные расходы» и т.п.). Подготовка таблицы подразумевает создание ячейки, в которой будет определён интересующий исследователя параметр (на рис. 7 это ячейка C18). По строке в ячейках D18:I18 указаны варианты вероятного процента респондентов, ответивших на рекламу, а по столбцу в ячейках C19:C28 содержатся различные варианты тиража рекламной продукции (изменение тиража осуществляется с постоянным приращением, равным 25000 единиц).

Рис. 7. Подготовка места на листе Excel для создания таблицы данных при проведении анализа данных

Шаг 4. Построение таблицы данных в режиме анализа «что – если». Выделите, созданную заготовку таблицы. На листе Excel следует выделить ячейки C18:I28. На вкладке «Данные» расположена пиктограмма , с помощью которой можно обратиться к нескольким вариантам для анализа данных, в том числе и для автоматического формирования Таблицы данных (рис. 8).

Рис. 8. Вход в режим работы с вариантом – Таблица данных

На экране появится диалоговое окно «Таблица данных», в которое следует ввести координаты ячеек с исходными данными (рис. 9). Следует отметить, что исходные данные были выбраны исследователем (менеджером) случайным образом, просто для проверки, что можно получить при заданных условиях. На самом деле, менеджер стремится подобрать наиболее приемлемый вариант при определённой ситуации, по этой причине его интересует просмотр нескольких вариантов решения задачи.

Рис. 9. Диалоговое окно для задания координат исходных данных

После того, как будет нажата кнопка ОК, на листе Excel появится интересующий исследователя результат, который представлен на рис. 10. В таблице с результатами на рис. 10 (ячейки D19:I28), красным цветом отражены отрицательные значения (нет прибыли), которую предприятие возможно потеряет, затратив определённые ресурсы на полиграфические и почтовые расходы.

Рис. 10. Окончательные результаты, представленные в виде таблицы данных

Задача, которая была реализована с помощью инструмента – Таблица данных, имела два входа (одним входом является – количество рассылаемого материала, второй вход – вероятность получения отклика от респондентов в процентах). Всегда удобнее вести анализ результатов с использованием графиков или номограмм. В данном примере, для построения номограмм, используются координаты строк и столбцов (рис. 11). Если в качестве исходных данных выбрать только строку, например, количество экземпляров печатной продукции, то получится таблица данных с одним входом. Соответственно в качестве исследуемого параметра может быть любой элемент из таблицы с результатами расчётов (можно ввести несколько параметров). К сожалению, инструмент Таблица данных работает только с одним и двумя входами.

Рис. 11. Графическое отображение значений таблицы данных

Контрольное задание: Построить таблицу данных для проведения анализа суммарных расходов предприятия, в зависимости от тиража разосланных материалов при постоянном значении процента ответивших респондентов, например, процент составляет 1,95%, построить график, аналогично представленного на рис. 6.

Вопросы для самоконтроля

1. В чём заключается идея проведения анализа данных с помощью инструмента – Таблицы данных?

2. Какие ограничения существуют в инструменте Таблицы данных?

3. Для чего проводится разметка местоположения для таблицы данных?