Таблицы подстановок, Сценарии и Поиск решения
Таблица подстановок - одно из средств анализа данных. Вот первая задача, которая приводит к построению таблицы подстановок. Рассмотрим набор функций, зависящих от одного и того же параметра: F1(a), F2(a), …Fm(a). Пусть каждая из этих функций задается формулой Excel. Пусть также требуется проанализировать зависимость этих функций от значений параметра a. Обычно нас интересуют результаты для конечного набора значений параметра - a1, a2, …an. В этом случае все, что нужно для анализа, - это построить прямоугольную таблицу размерности n*m, элементами которой будут значения Fj(ai). Excel позволяет без особого труда построить такую таблицу. Таблицы подстановок упрощают решение этой задачи.
Чтобы вручную построить такую таблицу, надо записать в столбец значения ai, в строку, расположенную на одну ячейку выше и правее, записать формулы Fj(Ain). Все формулы должны ссылаться на одну и ту же ячейку Ain - ячейку ввода. Можно, конечно, значения параметра записать в строку, а формулы в столбец. Основное требование к расположению формул и значений параметра состоит в том, чтобы они определяли прямоугольную область таблицы и воспринимались как заголовки ее строк и столбцов. Проделав эту подготовительную работу, достаточно выделить прямоугольную область, занятую таблицей, включая заголовки, и выбрать в меню "Данные" пункт "Таблица подстановок". В появившемся окне нужно задать ссылку на ячейку ввода. Заметьте, если значения параметра располагаются в столбец, то ссылку на ячейку ввода нужно задавать в окне строк, а не в окне столбцов. По щелчку кнопки OK таблица значений Fj(ai) будет автоматически построена.
Другая задача, приводящая к таблице подстановок, состоит в том, что рассматривается только одна функция F(a,b), но теперь зависящая от двух параметров. Элементами таблицы являются значения этой функции F(ai, bj). В роли заголовков строк и столбцов выступают значения ai и bj. Для записи формулы осталось одно свободное место - ячейка в левом верхнем углу таблицы. Формула, записанная в нее, ссылается теперь на две ячейки ввода - ячейку ввода строки и ячейку ввода столбца. Этим нюансом в расположении заголовков отличаются подготовительные действия по созданию таблицы подстановок в первом и во втором случае. Остальные действия аналогичны. И здесь нужно быть аккуратным в выборе окон при задании ссылок. Мне, например, всегда хочется задавать ссылки в другом порядке.
Конечно, таблицы подстановки - не столь уж мощное средство анализа данных. Собственно говоря, никакого анализа данных они не выполняют. Это лишь часто используемое средство, облегчающее построение таблицы данных для ее визуального анализа. Таблицу анализирует сам пользователь, исходя из содержательных соображений. Обычно, только он может понять, какие значения параметров a и b наиболее подходят для его целей. Хотя, конечно, может существовать и формальный алгоритм выбора из таблицы наилучшего значения.
А теперь покажем, как менеджер офиса использует таблицу подстановки в своем анализе. Ранее он построил уравнение регрессии, в котором продажи зависят от уровня рекламы и количества конкурирующих книг. Менеджер уже использовал его для прогноза состояния продажи книги и даже построил соответствующие графики, демонстрирующие прогнозируемую зависимость продаж от уровня рекламы. Однако графики были построены для фиксированного числа конкурирующих книг. Менеджер не определил еще окончательно, каков будет уровень рекламы в точке прогноза, и тем более он не знает точного значения количества конкурентов. Поэтому он решает построить таблицу подстановки, чтобы оценить все реально возможные варианты. Вот что у него получилось:
увеличить изображение
Рис. 8.30. Таблица подстановки, используемая в анализе "Что, если ...?"
Эта таблица позволяет ему получить ответы на вопросы: каков прогноз на продажу книг на следующий месяц и что будет, если появится новый конкурент, и что будет, если повысить уровень рекламы?
