3. Таблица подстановки с двумя ячейками исходных данных
С помощью таблицы подстановок с двумя ячейками исходных данных можно получить результаты вычислений по одной формуле при различных значениях двух входных параметров.
Структура таблицы подстановки с двумя ячейками исходных данных приведена на рис. 5.
Рассмотрим задачу: необходимо определить, как будет изменяться прибыль от продажи изделия, если изменится стоимость материала, необходимого для производства этого изделия, и оплата труда рабочих.
Расчет себестоимости выполняется по формуле:
Себестоимость = Стоимость материала * Количество материала + Количество часов на изготовление изделия * Оплату 1 часа труда рабочих.
Упражнение 3. Использование таблицы подстановки с двумя ячейками исходных данных.
Для решения поставленной задачи с помощью таблицы подстановок выполните следующие шаги:
Вставьте новый рабочий лист (команда Вставка Лист ).
В ячейку A1 ведите строку Исходные данные.
В ячейке A2 наберите текст Количество материала, в ячейке A3 – Стоимость материала, A4 – Количество часов. A5 – Оплата одного часа.
В ячейку А6 введите строку Расчет прибыли. Выполните выравнивание столбца наименований (выделите столбец A и выполнить команду Формат Столбец Автоподбор ширины).
Присвойте ячейкам B2, B3, B4 и B5 с помощью команды присваивания имени имена: Количество_материала, Cтоимость_материала, Количество_часов, Оплата_часа. (Замечание: идентификатор (имя) не может состоять из нескольких слов, поэтому в именах, присваиваемых ячейкам, между словами были использованы знаки подчеркивания.)
Внесите соответствующие значения (рис. 6) в ячейки, предварительно отформатировав ячейки с именами «Стоимость материала» и «Оплата часа» с помощью команды ФорматЯчейки: на вкладке «Число», диалогового окна команды выберите в списке «Числовые форматы» формат «Денежный» и установить число десятичных знаков равное 2. Ячейку B2 (Количество_материала) отформатируйте, используя пользовательский формат и учитывая, что количество материала исчисляется в метрах: на вкладке «Число» выберите в списке «Числовые форматы» формат «все форматы» и введите в поле ввода «Тип» образец пользовательского формата # # “м.”
В ячейки A7, A8, A9 внесите соответственно текст: Себестоимость, Отпускная цена, Прибыль.
Присвойте ячейкам B7, B8, B9 имена: Себестоимость, Отпускная_ цена, Прибыль соответственно.
Предварительно отформатировав ячейки B7, B8, B9 как Денежные, введите в ячейку B7 формулу для вычисления себестоимости: = Количество_материала * Стоимость_материала + Количество_часов * Оплата_часа
В ячейку B8 введите отпускную цену (220).
Внесите в ячейку B9 формулу =Отпускная_цена–Себестоимость
Ячейка B9, содержащая формулу, является ячейкой таблицы подстановки и находится в левом верхнем её углу.
Поскольку нашей задачей является исследование влияния стоимости материала и оплаты одного часа работы на прибыль, расположите в диапазоне ячеек С9:H9 возможные значения стоимости материала, а в диапазоне B10:B20 – значения оплаты часа работы (рис. 6). Предварительно следует отформатировать соответствующие диапазоны, выделив их и выполнив команду Формат Ячейки (на вкладке «Число» выберите в списке «Числовые форматы» формат «Денежный» и установите число десятичных знаков, равное 2).
Выделите диапазон C10:H20 и отформатируйте его, задав свой формат (выполните команду Формат Ячейки, на вкладке «Число» выберите в списке «Числовые форматы» наиболее подходящий числовой формат (отрицательные значения должны быть отображены красным цветом, число десятичных знаков должно быть равно 2), перейдите на строку «все форматы» в списке «Числовые форматы» и в поле ввода «Тип» измените шаблон – образец пользовательского формата должен быть задан строкой # ##0,00р.;[Красный]-# ##0,00р.;[Синий]# ##0,00р.
(введенный пользовательский формат позволяет представить результаты в денежном формате, с точностью два знака после запятой, отрицательные числа должны отображаться в ячейках таблицы красным цветом, а нулевые значения – синим).
Выделите диапазон B9:H20 и выполните команду Данные Таблица подстановки…. В диалоговом окне «Таблица подстановки» в строке «Подставлять значения по столбцам» укажите ссылку на ячейку B3 с именем «Стоимость_материала», а в строке «Подставлять значения по строкам» – ссылку на ячейку B5 («Оплата_часа»). Нажмите командную кнопку OK.
На рис. 6 приведены результаты расчета прибыли от производства изделия с помощью таблицы подстановки с двумя входными параметрами, принимающими значения из заданных диапазонов. Прибыль определяется как разность между отпускной стоимостью изделия и его себестоимостью.
Проанализируем результаты расчетов. Из таблицы видно, что наибольшая прибыль может быть достигнута при стоимости материала, равной 6 руб. и при оплате часа работы 25 руб. Прибыль не может быть получена, если стоимость материала возрастет до значения 10 р. (при той же почасовой оплате). Нет прибыли и в том случае, когда стоимость оплаты часа работы вырастет до 32 руб.
Таким образом, таблица подстановки с двумя ячейками исходных данных является удобным средством анализа данных.
Недостаток:
Можно исследовать процессы, зависящие от одной или двух переменных. К сожалению, реальные процессы редко укладываются в рамки таких моделей.
При работе с таблицей подстановки с двумя ячейками исходных данных расчеты могут быть выполнены только для одной формулы.