Анализ с использованием таблиц подстановки

Microsoft Excel является настолько мощным инструментом, что часто его можно использовать в целях математического моделирования систем. В простейшем случае суть математического моделирования можно выразить так: описываем исследуемую систему математическими формулами; подставляем в них некоторые исходные значения; вычисляем результат; на основания результата и его сопоставления с исходными значениями делаем вывод о том, как будет функционировать система в тех или иных условиях.

Функции размножения ячеек и формул Microsoft Excel позволяют достаточно легко строить несложные математические модели. Так, создав столбец или строку исходных данных и размножив формулу, мы получим, соответственно, строку или столбец результирующих данных, которые можно проанализировать.

В Microsoft Excel предусмотрен альтернативный инструмент, позволяющий добиться того же результата за меньшее число действий. В частности, он освобождает пользователя от необходимости разбираться с тонкостями абсолютных и относительных ссылок при размножении формулы. Этот инструмент носит название таблицы подстановок.

Суть данного инструмента в следующем:

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

2) в определенном месте рядом с формулой создается строка или столбец подстановки с нужными значениями;

3) выделяется диапазон ячеек, включающий строку или столбец подстановки, формулу и пустые ячейки, в которые будет помещен результат;

4) вызывается специальная команда, в которой указывается ячейка ввода;

5) Microsoft Excel последовательно подставляет все значения из строки или столбца подстановки в формулу, а вычисленный ей результат помещает в соответствующую пустую ячейку.

Команда на этапе 4) вызывается командой Таблица данных… в выпадающем меню кнопки «Анализ Что если». Открывается диалоговое окно (рис. 15.3), имеющее два поля для указания ячеек ввода.

Вычисления с помощью таблицы подстановки в Microsoft Excel могут быть двух типов: с одной переменной (используется или строка, или столбец подстановки) и с двумя переменными (одновременно используются и строка, и столбец подстановки). В первом случае в диалоговом окне рис. 15.3 заполняется одно из двух полей, во втором случае – оба. В полях указываются ссылки на ячейки ввода.

Поскольку вычисления при помощи таблицы подстановок осуществляются в тех ячейках, которые были выделены на этапе 3), важное значение имеет взаимное положение строки или столбца подстановок, ячейки с формулой и пустых ячеек. Microsoft Excel поддерживает строго определенные схемы расположения данных элементов, показанные на рис. 15.4.

Рис. 15.4 Правила создания таблицы подстановки

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

Рис. 15.4, в иллюстрирует подстановку с двумя переменными. Здесь допускается лишь одна формула, значение которой зависит от двух ячеек ввода. В одну из них будет подставлены значения из строки подстановки, во вторую – из столбца.

Вообще, формулы могут зависеть не только от ячеек ввода, но и от других ячеек, однако все прочие ссылки в формулах будут считаться константами.

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

Для редактирования таблицы подстановок (например, добавления новых формул) ее необходимо пересоздавать: внести изменения в формулы, выделить диапазон и вновь воспользоваться Таблица данных….