Моделирование систем

     Щелкните  на кнопке Далее (на кнопке Готово можно  щелкнуть в любой момент: диаграмма будет построена с параметрами но умолчанию, распознанными в соответствии с организацией вашего списка), удостоверьтесь в следующем диалоговом окне Мастера диаграмм, что диапазон данных выбран правильно, а ряды, как нам и требуется, будут взяты из строк, и перейдите на вкладку Ряд. Задержитесь: именно здесь можно переопределить параметры диаграммы, предлагаемые но умолчанию (рисунок 2.14).

Рисунок 2.14 - Определение рядов и категорий

     Щелкните  на кнопке Далее, в очередном диалоговом окне Мастера диаграмм (рисунок 2.15) решите вопрос с заголовками (названиями диаграммы и осей) и перейдите на вкладку Оси. Если и здесь вам все нравится, ознакомьтесь с остальными вкладками и щелкните на кнопке Далее. 

     ПРИМЕЧАНИЕ

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

     Четвертое (последнее) диалоговое окно позволит выбрать место размещения диаграммы – на том же рабочем листе или на отдельном листе диаграммы. Щелкните на кнопке Готово – и сравните результат с нашим (рисунок 2.16).

Рисунок 2.15 - Настройка параметров элементов диаграммы.

Рисунок 2.16 - Построенная диаграмма

     У вас получилось не совсем так? Наверное. Выберите команду Диаграмма > Объемный вид и поработайте с диалоговым окном Формат трехмерной проекции (рисунок 2.17).

     Кстати, вы, конечно, обратили внимание на то, что  при обращении к диаграмме  меню Данные заменяется меню Диаграмма? Познакомьтесь с ним. Команды этого меню позволяют усовершенствовать диаграммы по завершении работы Мастера диаграмм. И не забывайте о контекстных меню: щелкая правой кнопкой мыши на различных областях и элементах диаграммы, вы будете вызывать контекстные меню с командами вызова диалоговых окон для изменения диаграммы. О диаграммах все. Пока все.

Рисунок 2.17 - Настройка вида трехмерной диаграммы

2.4 Функции и графики – легко и просто

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

     Астроида  – это кривая, задаваемая уравнением x^2/3 + у^2/3 = а^2/3. Положим а = 1 и воспользуемся параметрическим представлением x = cos³t, y = sin³t для построения кривой на отрезке t [-π; π]. Зададим t на левой границе приближением -3, 2, введя его в ячейку А1. Введя в ячейку А1 значение -3,1, мы зададим шаг автозаполнения 0,1. 

     ВНИМАНИЕ

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

     Присвоим  ячейкам А1, В1 и С1 формат Числовой с одним десятичным знаком в первой и двумя в остальных: при последующем протаскивании маркера заполнения Excel скопирует формат. Щелкнем на ячейке В1 и введем = cos(a1)^3, завершив ввод нажатием клавиши Tab. В ячейку С1 введем = sin(a1 )^3. 

     ВНИМАНИЕ

     Регистр адресов ячеек и имен функций  при правильном вводе не важен. Excel самостоятельно исправит его на верхний. A вoт про скобки, в которые должен быть заключен аргумент, забывать не стоит. Даже такая функция, как ПИ(), вычисляющая значение отношения ДЛИНЫ дуги к диаметру окружности, претендует на (пустые) скобки. 

     Выделим (например, протаскиванием мыши) ячейки В1 и С1 и протащим маркер заполнения на строку вниз – формулы и форматы скопированы. Выделим (например, щелчком на угловой ячейке, а затем на противоположной ей по диагонали с удержанием клавиши Shift) диапазон А1:С2 и протащим маркер заполнения вниз до появления всплывающей подсказки со значением 3,2–формулы и форматы скопированы, и столбцы значений х и у заполнены.

     Выделим диапазон В1:С1 и дважды щелкнем на нижней границе рамки выделения, удерживая клавишу Shift – неплохой способ выделения диапазона. 

     СОВЕТ

     Щелчками  на границах рамки выделенного диапазона  можно перемещать активную ячейку по границам блока ячеек. 

     Выберем команду (просто для разнообразия) Вставка > Диаграмма и в уже знакомом диалоговом окне Мастера диаграмм (см. рисунок 2.13) выберем тип диаграммы. Казалось бы, здесь все ясно: График. Увы, график в Excel предполагает равномерное (с постоянным шагом) изменение аргумента. В нашем случае это не так, но выход есть: выберем тип Точечная. Вроде, неплохо получилось (рисунок 2.18).

Рисунок 2.18 - Астроида

     Вообще  же, путь к функциям лежит через  щелчок на кнопке Вставка функции  панели инструментов Стандартная, команду Вставка > Функция – и через общий способ обращения к функциям: щелчок на кнопке Изменить формулу («знак равенства» слева от строки формул).

Рисунок 2.19 - Выбор функции

     В последнем случае обращение к  диалоговому окну Мастера функций (рисунок 2.19) произойдет не сразу, а только в том случае, если вам не хватит функций из раскрывающегося списка недавно использовавшихся функций, появляющегося в поле имени после щелчка на кнопке Изменить формулу. Именно списки Категория и Функция этого диалогового окна помогут выбрать нужную функцию среди богатств Excel. Ну, а про операторы ^ (возведения в степень), * (умножения) и другие вполне доступно рассказано в Справочной системе. Не забывайте только о порядке выполнения операций (символы которых и называются в Excel операторами)!

3 МЕТОДИКА ВЫПОЛНЕНИЯ РАБОТ В ПАКЕТЕ EXCEL

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

3.1 ОБЩИЙ ПОДХОД К  ПОСТРОЕНИЮ УРАВНЕНИЯ  РЕГРЕССИИ НА ПРИМЕРЕ ЛИНЕЙНОЙ МОДЕЛИ

     Рассмотрим, как решается задача нелинейной оптимизации с помощью средства поиска решений на примере построения линейного уравнения регрессии. Имеются две наблюдаемые величины x и y, например, производительность предприятия за шесть недель его работы. Значения этих наблюдаемых величин приведены на рисунках 3.1, 3.2, где x – отчетная неделя, а y – выпуск за эту неделю.

     Необходимо  построить линейную модель y = mx + b, наилучшим образом описывающую наблюдаемые значения. Обычно m и b подбираются так, чтобы минимизировать сумму квадратов разностей между наблюдаемыми и теоретическими значениями зависимой переменной y, т.е.

где n – число наблюдений (в данном случае n = 6); y_i – экспериментальные данные; mx - b – данные, полученные в результате расчетов.

Рисунок 3.1 – Исходные данные для построения модели

Рисунок 3.2 – Начало построения линии тренда

     Существует  несколько способов для решения  этой задачи. Мы рассмотрим решения с помощью построения линии тренда (Trendline). Этапы построения сводятся к следующему.

     В начале нужно построить точечный график по диапазону ячеек А2:В7, выделить точки графика щелчком левой кнопки мыши в диапазоне точек на графике, а затем щелкнуть их правой кнопкой. В раскрывшимся контекстном меню следует выбрать команду Добавить линию тренда (рисунок 3.2).

     В диалоговом окне Линия тренда (Trendline) на вкладке Тип (Type) в группе Построение линии тренда (аппроксимация и сглаживание) (Trend/Regression type) выберите параметр Линейная (Linear) (рисунок 3.3), а на вкладке Параметры (Options).

Рисунок 3.3 – Вкладка Тип диалогового  окна Линия тренда

установите  флажки Показывать уравнение на диаграмме (Display Equation on Chart) и Поместить на диаграмму величину достоверности аппроксимации (R^2) (Display R-squared), т.е. на диаграмму необходимо поместить значение квадрата коэффициента корреляции (рисунок 3.4).

     По  коэффициенту корреляции можно судить о правомерности использования линейного уравнения регрессии. Если он лежит в диапазоне от 0,9 до 1, то данную зависимость можно использовать для предсказания результата. Чем ближе к единице коэффициент корреляции, тем более обоснованно это указывает на линейную зависимость между наблюдаемыми величинами. Если коэффициент корреляции близок к -I, то это говорит об обратной зависимости между наблюдаемыми величинами.

Рисунок 3.4 – Вкладка Параметры диалогового  окна Линия тренда

      Флажок Пересечение кривой с осью Y в точке (Set Intercept) (рисунок 3.4) устанавливается только в том случае, если эта точка известна. Например, если этот флажок установлен и в его поле введен 0, это означает, что ищется модель y = mx.

      Результат выполнения команды Линии тренда (Trendline) приведен на рисунках 3.5, 3.6.

Рисунок 3.5 – График линии тренда линейной модели

Рисунок 3.6 – График линии тренда полиномиальной модели

     Как видно на рисунке 3.5, квадрат коэффициента корреляции равен 0,9723. Следовательно, линейная модель может быть использована для предсказания результатов.

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

     Для полиноминальной модели (рисунок 3.6) коэффициент корреляции получился более приближенным к 1. Поэтому в качестве расчетной следует выбрать именно эту модель.

     В диалоговом окне Линия тренда на вкладке Параметры в разделе Прогноз (рисунок 3.4) можно увидеть поведение функции вперед и назад на определенное количество единиц.

3.2 РАСЧЕТ ТЕОРЕТИЧЕСКИХ ЗНАЧЕНИЙ ПО МОДЕЛИ. ПРОВЕРКА МОДЕЛИ НА АДЕКВАТНОСТЬ

     На  основе найденных коэффициентов  уравнения регрессии можно определить теоретическое значение наблюдаемой величины y. Вычислим теоретическое значение у в ячейке С2, заменяя x на А2 по формуле, полученной в результате регрессионного анализа (рисунки 3.7, 3.8):

= - 0.0536*А2^2 + 2,2607*А2 + 4,9.