Глава 4 (О.Б. Калугина, В.С. Люцарев - Работа с электронными таблицами. Microsoft Office Excel 2003), страница 6

Перед вызовом и этой функции не забудьте выделить диапазон ячеек, в котором должен появиться результат вычислений. Единственный аргумент функции Массив должен задавать исходную матрицу.Ctrl + Shift + EnterФункция МОБР возвращает значение ошибки #ЗНАЧ!, если: хотя бы одна ячейка в аргументах пуста или содержит текст; аргумент Массив имеет различное число строк и столбцов.1105Организация вычислений в рабочих книгахВсе эти функции относятся к категории Математичские.Произведение матрицПолучить произведение матриц можно с помощью функцииМУМНОЖ, аргументами которой должны быть два числовых массива.При этом количество столбцов аргумента Массив1 должно быть такимже, как количество строк аргумента Массив2, поскольку при умножении матриц элементы строк первой матрицы умножаются на элементыстолбцов второй.В этой формуле i определяет номер строки,j – номер столбца.n – это количество столбцов первой матрицы.Ctrl + Shift + Enter106Глава 4Поскольку вычисления в Microsoft Excel производятся с точностьюпримерно 16 значащих цифр, могут иметь место ошибки округления.

Так,ожидаемые в примере на рисунке нулевые значения представлены бесконечно малыми числовыми величинами в экспоненциальном формате чисел.Функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!, если: хотя бы одна ячейка в аргументах пуста или содержит текст; число столбцов в аргументе Массив1 отличается от числастрок в аргументе Массив2.Организация вычислений в рабочих книгах107Функция МОПРЕД возвращает значение ошибки #ЗНАЧ!, если: хотя бы одна ячейка в аргументах пуста или содержит текст; аргумент Массив имеет различное число строк и столбцов.На этом мы заканчиваем обзор встроенных функций и переходим кзнакомству с возможностями Microsoft Excel, которые позволяют решать задачи, относящиеся к анализу данных «Что-если», а именно – находить и оценивать значения одного или нескольких параметров для некоторого предполагаемого решения.Определитель матрицыОпределитель матрицы – это число, вычисляемое на основе значений элементов квадратной1 матрицы.Например, для матрицы, представленной числовым массивом в ячейках A1:C3,состоящей из трех строк и трех столбцов,определитель вычисляется так:D = A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)Функция МОПРЕД возвращает определитель матрицы, числовыеэлементы которой заданы аргументом Массив.«Что – если»До сих пор мы рассматривали «прямые» вычисления.

При этом известные исходные данные заносятся во влияющие ячейки, а в зависимой ячейке по заданной формуле вычисляется результат. С точки зрения математики, речь идет просто о вычислении значения некоторойфункции: F(x1, x2, ...).Обратная же задача начинается с задания желаемого результата вычислений. Ее решение состоит в поиске таких значений влияющих ячеек,которые обеспечат этот результат. Для решения обратных задач в Excelможно воспользоваться командой Сервис Подбор параметра…или надстройкой Поиск решения…Подбор параметра1Команда Подбор параметра… предназначена для решения уравнения с одним неизвестным, записываемого в общем виде как F(x) = a ,где a – некоторая постоянная величина.Рассмотрим, например, кубичесy = x3 + 2x2 – 10x – 2кое уравнение x3 + 2x2 – 10x – 2 = 0.Графическое его решение представлено на рисунке.

Как видно из графика,на отрезке [-1, 3] это уравнение имеетдва корня. Посмотрим, как с численным решением данного уравнениясправится Подбор параметра.Убедившись, что активна ячейкас формулой, нужно подать командуСервис Подбор параметра…Матрица с одинаковым количеством строк и столбцов.108Глава 4В появившемся диалоговом окне Подбор параметра в поле Установить в ячейке: автоматически подставляется адрес активной ячейки листа (в нашем примере С3).

Далее для функции необходимо задать желаемое числовое Значение: (в данном случае ноль), а в поле Изменяя значение ячейки: указать ссылку на ячейку с влияющим параметром (в примере на рисунке $B$3).Организация вычислений в рабочих книгах109Теперь попытаемся повторно решить поставленную задачу, но сдругим исходным значением аргумента.Соответственно, и результат будет иным.Щелчок по кнопке ОК выведет результат выполнения команды.Как видите, результат подбора параметра может сильно зависеть отначального приближения. При некоторых исходных значениях алгоритм подбора параметра может оказаться неспособным найти решение,несмотря на то, что оно существует.

Этот факт необходимо учитывать напрактике. Если подбор параметра не дал удовлетворительных результатов, преждевременно делать выводы о невозможности решить уравнение, иногда достаточно лишь взять другое начальное приближение.Обратите внимание, что однократным исполнением команды можно найти не более одного решения уравнения. Для нашего примера результат (-0,19322) соответствует нулевому начальному приближению вячейке $B$31.Этот результат можно зафиксировать на рабочем листе щелчком покнопке ОК, а можно оценить лишь визуально и вернуть прежние значения ячеек, щелкнув Отмена в окне Результат подбора параметра.1Используемый Excel алгоритм подбора параметра проверяет поведение функции при небольших вариациях параметра около начального значения и на основе этой информацииделает шаг в сторону приближения к подбираемому значению.

В нашем примере околонулевого значения аргумента целевая функция отрицательна и убывает, поэтому шаг совершается в сторону отрицательных значений параметра. По мере приближения к подбираемому значению алгоритм уменьшает шаг, пока решение уравнения не будет найдено сприемлемой точностью.Выполните практическое задание 4.5 по теме Организациявычислений в рабочих книгах из приложения От теории кпрактике, чтобы понять, как легко и быстро можно решатьуравнения с помощью Подбора параметра.110Глава 4Поиск решенияЕсли команда Подбор параметра… предназначена лишь для поискалокального решения уравнения с одним неизвестным и гладкой функцией, то при помощи надстройки Поиск решения можно решать существенно больший круг обратных задач.Пусть на рабочем листе задана функция нескольких переменныхF(x1, x2, ...) – целевая функция.

Поиск решения осуществляет поиск значений аргументов x1, x2, …, обеспечивающих минимальное значение F.При этом область поиска может быть ограничена неравенствами илипроизводиться только по целочисленным значениям параметров.Задача минимизации значения функции нередко возникает как внаучной и инженерной деятельности (например, подбор параметровфункции, наилучшим образом описывающей экспериментальные данные), так и в других областях (например, оптимизация расходования ресурсов).

Важно лишь уметь правильно свести формулировку конкретнойзадачи к описанному выше общему виду минимизации функции. Приведем примеры, как это можно сделать.Организация вычислений в рабочих книгах111Рассмотрим для примера задачупоиска минимального значенияфункции двух переменных:Так же, как и в случае с подборомпараметра, подготовим на рабочемлисте формулу для прямого вычисления значения функции. Например,присвоив имя1 x ячейке B2, а ячейкеB3 имя y, формулу в ячейке B4 можнозаписать с использованием таких имен.Надстройка Поиск решения вызывается командой Сервис Поискрешения…2Здесь вычисляетсяцелевая функцияНужно найти максимум некоторой функции G (x1, x2, ...).Эта задача сводится к минимизации, если определитьF (x1, x2, ...) = –G (x1, x2, ...).Нужно решить уравнение G (x1, x2, ...) = a.Решаемая задача:минимизация,максимизация илирешение уравненияРешение сводится к минимизации функцииF (x1, x2, ...) = (G (x1, x2, ...) – a)2, так как минимум этой параболыдостигается в единственной точке G (x1, x2, ...) – a = 0.Эти два метода приведения к минимизации применяются на практике настолько часто, что надстройка Поиск решения предлагает максимизацию функции и решение уравнения как два типа решаемых задач вдополнение к минимизации.

В действительности же, во всех случаяхПоиск решения сначала выполняет приведение целевой функции, а затем запускает один и тот же алгоритм минимизации.Требуется описать результаты измерения величины A (bi) при различных условиях эксперимента b1, b2,… bn функцией A ≈ G (b; x1, x2, ...),содержащей неизвестные параметры x1, x2, …И эта задача эквивалентна задаче поиска минимума функцииАргументыцелевой функции(от 1 до 200влияющих ячеек)В простейшем случае в диалоговом окне Поиск решения достаточноустановить три обязательных элемента, отмеченных на рисунке: адресячейки с целевой функцией, решаемую задачу, влияющие ячейки – ищелкнуть по кнопке Выполнить.Для поиска решения в Microsoft Excel применяются приближенныеитерационные методы.

Итерация – это многократно повторяемое дейст12О присвоении имен ячейках рабочего листа было рассказано в Главе 1, в параграфеИмена ячеек и диапазонов.Отсутствие этой команды в меню Сервис означает, что надстройка не подключена кMicrosoft Excel. Подключение осуществляется очень просто: подается команда Сервис Надстройки… и в появившемся диалоговом окне устанавливается флажок рядом сназванием Поиск решения.112Глава 4вие. В задаче минимизации на каждой итерации делается попытка уточнить текущее приближенное решение. Результатом является последовательность значений искомых параметров, все ближе и ближе подходящихк истинному решению.

Процесс итераций продолжается до тех пор, покарешение не будет найдено с приемлемой точностью, либо пока не станетпонятно, что приемлемого решения найти не удастся1. Если процесс поиска слишком затянулся, его можно прервать нажатием клавиши Esc.По окончании процессапоиска решения появится окно Результаты поиска решения.Если просто щелкнуть покнопке OK, найденные значения останутся в ячейках листа.Каждое созданное условие появляется в диалоговом окне Поиск решения в виде строки списка Ограничения и может быть изменено илиудалено из списка при помощи соответствующих кнопок.

Следует отметить, что в процессе поиска решения надстройка «делает все возможное»для того, чтобы все заданные ограничения были соблюдены. Однако и впроцессе поиска, и для найденного решения небольшие рассогласования все же возможны.Если ни варьирование начального приближения, ни дополнительные ограничения не приводят к приемлемому решению задачи, можнопопробовать поменять более тонкие параметры алгоритма. Для этого вдиалоговом окне Поиск решения следует щелкнуть по кнопке Параметры:Лимит времени поискаНо даже если вы выбрали Восстановить исходныезначения, результаты расчетов могут быть сохранены ввиде отчетов, вставляемых врабочую книгу отдельнымилистами, а также в виде сценариев, о которых будет рассказано ниже.Сейчас же вернемся к диалоговому окну Поиск решения.

Для сложныхцелевых функций со многими параметрами поиск решения далеко не всегда приводит к успеху. Какими способами можно повлиять на сходимость?Прежде всего, сообщив надстройке дополнительные сведения в виде ограничений на параметры задачи. Ограничением может быть интервал, в котором допустимо менять параметр (задается в виде двух отдельных условий), требование целочисленности параметра и т.п.Очередное условие создаетсящелчком по кнопке Добавить в диалоговом окне Поиск решения. Необходимо указать ячейку, на которую накладывается условие, типусловия и завершить определениеусловия щелчком по кнопке OK.1В надстройке Поиск решения (в английской версии Solver) используются алгоритмыSimplex и GRG2. Подробности можно найти в журнале INTERFACES 28: 5September–October 1998 (стр.