Занятие 5. Таблица подстановки как средство решения задач экономического характера
1. Электронная таблица как динамическая модель
Электронные таблицы Excel удобно использовать в качестве динамических моделей, когда изменение исходных данных автоматически изменяет содержимое ячеек, в которых хранятся результаты вычисления по формулам.
Пользователь может задаться вопросом, каковы будут результаты, если один или несколько параметров в формуле будут изменены. Например, как изменится спрос на товар, с изменением цены на товар? Какова будет прибыль при производстве некоторого изделия, если изменится стоимость материала, необходимого для изготовления изделия, или изменится трудоемкость, или оплата за час рабочим?
Для того чтобы выполнить анализ изменения исходных данных можно:
изменить исходные данные вручную;
написать соответствующие макросы для изменения исходных данных;
использовать таблицы подстановки;
использовать диспетчер сценариев.
Для выполнения анализа вручную необходимо изменить содержимое ячеек исходных данных и проследить, как изменились результаты.
К примеру, необходимо вычислить спрос и предложение некоторого товара, если они вычисляются по следующей формуле:
На рис.1. представлен фрагмент рабочего листа. Ячейке B1 присвоено имя Цена, а ячейкам С2 и D2 – имена Спрос и Предложение соответственно. Для присвоения имени ячейке используется команда Вставка Имя Присвоить, в диалоговом окне которой можно ввести имя (поле ввода Имя), присваиваемое предварительно выделенной ячейке или диапазону ячеек (координаты диапазона показаны в поле ввода Формула). Содержимое ячеек С2 и D2 – формулы (рис. 1, а):
= 2000-75*Цена и = 740+65*Цена
С помощью команды Сервис Зависимости формул Зависимые ячейки можно проследить связи между ячейками B1, C2, D2, как это и продемонстрировано на рис. 1 (б) (перед выполнением команды нужно установить рамку выделения на ячейку B1 с именем Цена).
Упражнение 1. Изменение исходных данных вручную.
Для создания таблицы, в которой производятся вычисления, выполните следующую последовательность шагов:
Установите курсор на ячейке B1. Выполните команду Вставка Имя Присвоить. В диалоговом окне Присвоить имя в поле ввода Имя введите имя ячейки Цена» и щелкните кнопку ОК для завершения операции. Повторите ввод имен (Спрос и Предложение) для ячеек C2 и D2. (Имена ячеек вводятся без кавычек).
Введите заголовки Цена=, Спрос, Предложение в ячейки А1, C1 и D1 соответственно.
В ячейку B1 поместите значение (например, 10).
Выберите ячейку С2. Введите в ячейку формулу =2000‑75*Цена
Выберите ячейку D2. Введите в ячейку формулу =740+65*Цена
Измените значение в ячейке Цена и посмотрите, как изменятся значения в ячейках Спрос и Предложение.
2. Таблица подстановки с одной ячейкой исходных данных
Таблица подстановок позволяет проследить изменения значений, вычисляемых по заданной формуле в зависимости от изменений исходных данных, введенных в ячейку таблицы. При этом задается целый диапазон значений, которые могут приниматься исходными данными. Расположение ячеек, содержащих исходные данные и формулу, показано на рис. 2. В данном случае значения, принимаемые исходными данными, располагаются в столбце, находящемся ниже ячейки, ссылка на которую используется в формулах в качестве операнда для вычислений.
С помощью таблицы подстановок с одной ячейкой исходных данных можно получить результаты вычислений по нескольким формулам (до 32) при различных значениях одного входного параметра.
Таблицу располагают в любом месте рабочего листа.
Структура таблицы подстановок и порядок работы с ней таковы:
В верхней строке таблицы подстановок располагаются формулы (или ссылки на формулы). Формулы содержат ссылки на ячейку с входным параметром, которая может быть расположена в любом месте рабочего листа.
В столбце, который находится слева от строки с формулами располагается подмножество значений входного параметра.
Ячейка, которая стоит на пересечении строки формул и столбца значений входного параметра остаётся пустой (ее можно использовать для размещения входного параметра).
Microsoft Excel вычисляет значения выражений, определенных формулами, подставляя в ячейку, отведенную для входного параметра, используемого в формулах, поочередно все значения из столбца исходных данных. Результаты вычислений записываются в строку, левее соответствующих исходных данных. Вычисления выполняются при инициализации команд: Данные Таблица подстановки.
Рассмотрим использование таблицы подстановок для анализа изменений результатов вычислений в зависимости от изменений исходных данных (изменение спроса и предложения в зависимости от цены). Для этого используйте созданную в предыдущем задании таблицу.
Упражнение 2. Использование таблицы подстановки с одной ячейкой исходных данных.
Для создания таблицы подстановки выполните следующие шаги:
Скопируйте созданную в предыдущем задании таблицу на новый рабочий лист.
Заголовок Цена= из ячейки A1 перенесите в ячейку B1, удалите знак равенства = (рис. 3).
Измените формулы, введенные в ячейки C2 и D2, задав вместо ссылки на ячейку Цена, расположенную на первом рабочем листе, ссылку на ячейку B2 данного рабочего листа.
В диапазон ячеек B3:B14 введите последовательность чисел 1, 2, …, 12.
Ячейки C2 и D2 содержат формулы для выполнения команды подстановки, а в ячейку B2 будут подставляться исходные значения из диапазона B3:B14 для заполнения таблицы.
В ячейку E3 введите формулу
=ЕСЛИ(C3=D3;"равновесные спрос и предложение";" ")
Скопируйте введенную формулу в ячейки E4:E14. Ячейки диапазона E3:E14 будут представлять результаты анализа. Для совпадающих значений спроса и предложения в соответствующей строке будут выведен текст о том, что спрос и предложение являются равновесными.
Выделите диапазон B2:D14.
Выполните команду: Данные Таблица подстановки.
В диалоговом окне команды (рис. 4) в поле ввода Подставлять значения по строкам в (значения из диапазона-столбца будут использоваться для заполнения строк таблицы подстановки) укажите ссылку на ячейку B2, введя координаты ячейки с клавиатуры или щелкнув по ней мышью после переноса курсора в это поле.
В ячейки таблицы после выполнения команды оказываются введенными формулы {=ТАБЛИЦА(;B2)} (фигурные скобки показывают, что это формулы массива). Результат выполнения команд приведен на рис. 3. Из результатов видно, что равновесные спрос и предложения возникают при установленной цене товара в 9 денежных единиц.
Замечание. В рассмотренном случае таблица подстановки располагалась по вертикали. При использовании таблицы подстановки с одной ячейкой исходных данных можно расположить ее горизонтально. При этом ссылку на ячейку исходных данных следует ввести в текстовом поле Подставлять значения по столбцам.