Adelitusn.ru

ПК и Техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Поиск решения EXCEL. Знакомство

Поиск решения EXCEL. Знакомство

Рассмотрим использование метода «Поиск решения. » на исходных данных представленных на рис. 1.

Для использования метода «Поиск решения. » необходимо свести задачу решения СЛАУ к задаче оптимизации. Введем целевую функцию вида

где bi — i-й элемент вектора свободных членов СЛАУ;

ai,j — i, j-й элемент матрицы коэффициентов СЛАУ;

xj — j-й элемент вектора решения СЛАУ;

n — количество уравнений в СЛАУ.

Ограничений на вектор решения X накладывать не будем.

Тогда математически задачу поиска вектора решения СЛАУ X можно записать

Подобная задача (5) легко решается использованием метода «Поиск решения. » MS Excel (см. рис. 3.2) следующим образом:

Ш обнуляем ячейки (B24:B27), в которых будем формировать вектор решения СЛАУ X;

для ячейки B29 в строке формул запишем

правую часть целевой функции (4) для исходных данных нашей задачи;

Решение СЛАУ, используя метод "Поиск решения. " (пункт главного меню "Сервис") MS Excel

Рис. 3.2. Решение СЛАУ, используя метод «Поиск решения. » (пункт главного меню «Сервис») MS Excel

Ш в пункте главного меню MS Excel «Сервис» выбираем подпункт «Поиск решения. » (см. рис. 3.3).

При открытии окна «Поиск решения» напротив метки «Установить целевую ячейку:» будет отражен адрес активной ячейки (ячейки, в которой был установлен курсор при открытии окна). В ячейке $B$29 (B29) должна быть записана формула вычисления правой части целевой функции (4). Также в окне «Поиск решения» ниже метки «Изменяя ячейки:» необходимо задать адрес вектора решения СЛАУ X ($B$24:$B$27) (B24:B27). Адреса целевой ячейки и вектора решения СЛАУ можно формировать в режиме конструктора. Для этого необходимо поместить курсор в ячейку формирования соответствующего адреса и на листе MS Excel выделить ячейку или массив ячеек;

Ш нажать кнопку «Выполнить». После чего появится окно «Результаты поиска решения» и в ячейках (B24:B27) сформируется вектор решения СЛАУ X.

Окно “Поиск решения…”

Рис. 3.3 Окно “Поиск решения…”

Лист MS Excel, представленный на рис. 3.2 позволяет получить вектор решения для любой СЛАУ, состоящей из трех уравнений. Описанная технология решения СЛАУ легко позволяет решить задачу любой размерности (для любого количества уравнений в СЛАУ).

Решение СЛАУ методом Крамера

СЛАУ из n уравнений задается матрицей коэффициентов СЛАУ A и вектором свободных членов СЛАУ B.

где ai,j — i, j-й элемент матрицы коэффициентов СЛАУ;

bi — i-й элемент вектора свободных членов СЛАУ.

Суть метода Крамера в следующем: сначала вычисляется определитель матрицы коэффициентов СЛАУ

за тем вычисляются еще n определителей

т.е. определитель вычисляется для матрицы, полученной из матрицы коэффициентов СЛАУ путем замены j-го столбца матрицы коэффициентов СЛАУ вектором свободных членов СЛАУ.

Тогда элементы вектора решения СЛАУ xj, j = 1, …, n определяются по формуле

В MS Excel существует формула =МОПРЕД(левый_верхний_элемент_исходной_матрицы: правый_нижний_элемент_исходной_матрицы) для вычисления значений определителей квадратных матриц.

Решение СЛАУ методом Крамера (методом определителей) представлено на рис. 3.4.

Решение СЛАУ методом Крамера

Рис. 3.4. Решение СЛАУ методом Крамера

Строки с 1 по 22 на рис. 3.4 не показаны, потому что они полностью совпадают с соответствующими строками рис. 3.1, 3.2.

Необходимо сформировать матрицы для вычисления определителей , 1, 2, 3 в ячейках (B24:E27), (B29:E32), (B34:E37), (B39:E42), (B44:E47), соответственно. Алгоритм формирования матриц для вычисления определителей представлен в табл. 2.

Читайте так же:
Resize image files online

Средство Excel «Поиск решения»

Решение задач прикладной информатики в менеджменте.

Практическое занятие 5.

Средство Excel «Поиск решения»

Цель работы:
изучение постановки задачи оптимизации и средства «Поиск решение»

Задачи оптимизации параметров объекта исследования

Оптимизационные модели служат для поиска наилучших, в определенном смысле, вариантов. В этом случае среди параметров модели выделяют один или несколько, доступных нашему влиянию – независимые переменные или управляемые параметры X . Среди выходных характеристик Y выделяют такую, которая позволяет оценить качество объекта – критерий оптимальности Q k .

С учетом введенных обозначений задача оптимизации формализуется следующим образом:

Q * k = extr Q k
X
Y j ( х ) j max

В зависимости от особенностей реального объекта, характера зависимости критерия оптимальности от независимых переменных и целей исследования различают задачи:

условной (при наличии дополнительных ограничений) и безусловной (без дополнительных условий) оптимизации;

одномерной (при одном управляемом параметре) и многомерной (несколько управляемых параметров) оптимизации;

линейной (при линейной зависимости критерия качества от параметров) и нелинейной оптимизации;

локальной (существует единственный экстремум) и глобальной (существуют несколько экстремумов) оптимизации.

Когда экономическую или техническую задачу удается привести к виду стандартной задачи оптимизации (1), ее решение можно получить одним из известных математических методов.

Существуют многочисленные программы, предназначенные для решения оптимизационных задач. Одной из таких программ является средство Excel «Поиск решения».

Средство Excel «поиск решения»

Средство Excel Поиск решения позволяет получить искомое значение в определенной ячейке, которую называют целевой , путем изменения значений нескольких влияющих ячеек. Кроме того, при поиске решения можно указать дополнительные условия – ввести ограничения на изменение параметров влияющих ячеек. Допускается установка до 200 изменяемых ячеек.

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

Для выполнения операции Поиск решения использует команду меню Сервис – Поиск решения. Команда может отсутствовать в меню сервис. В этом случае нужно в меню Сервис выбрать команду Надстройки и установить в списке включенных надстроек нужный флажок:

Рисунок 1 Список подключенных настроек

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

Для выполнения операции Поиск решения нужно выполнить команду Сервис – Поиск решения и в диалоговом окне Поиск решения задать параметры решения:

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

критерий оптимальности (максимальное или минимальное значение) или значение, которое следует найти;

адреса изменяемых ячеек; при этом адреса отдельных ячеек или диапазонов разделяются запятыми; кнопка «Предположить» служит для автоматического выделения ячеек, влияющих на целевую;

ограничения, которые должны учитываться при поиске решения; для ввода нескольких ограничений используется кнопка «Добавить».

Рисунок 2 Диалог «Поиск решения»

Кнопка «Параметры» позволяет изменить параметры поиска: способ поиска решения, время вычислений, точность определения результатов.

Рисунок 3 Диалог установки параметров поиска решения

Читайте так же:
Устройство и основные характеристики центрального процессора

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

Если поиск решения успешно завершен, то результаты вычислений заносятся в исходную таблицу, а в диалоговом окне Результаты решения пользователь имеет возможность

сохранить найденной решение в исходной таблице;

восстановить исходные значения;

сохранить результаты в виде сценария;

сформировать отчет по результатам выполнения операции.

Рисунок 4 Диалог «Результаты поиска решения»

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

В программе Excel-2007 средство «Поиск решения» вызывается пиктограммой из группы Анализ , расположенной на вкладке Данные.

Определение критического объема реализации с помощью поиска решений

Рассмотрим использование Поиска решений для решения уравнения.

Откройте рабочую книгу с калькуляцией тура, созданную на предыдущих занятиях.

Скопируйте заголовок и первую строку таблицы анализа затрат – доходов:

Объем
реализации
Q к

В столбце «Баланс» введите формулу: Прибыль от реализации — Затраты

С помощью средства «Поиск решения» определите величину Объема реализации, обеспечивающую нулевой баланс.

Указания.
1) Целевая ячейка в нашем случае – ячейка, в которой вычислен баланс; требуется установить в ней нулевое значение путем изменения ячейки с объемом реализации.

2) Математическая модель рассматриваемой задачи линейна.

3) Задача без ограничений.

Замечание. Иногда требуется проверить, какие ячейки влияют на вычисление значение в другой ячейке. Чтобы наглядно увидеть взаимное влияние ячеек, можно использовать команду меню Сервис – Зависимости формул – Влияющие ячейки (Зависимые ячейки).

Сравните результат с результатами, найденными графическим методом и с помощью средства «Подбор параметра».

Решение СЛАУ с помощью надстройки «Поиск решения»

Систему линейных алгебраических уравнений можно также решить, используя надстройку «Поиск решения». При использовании данной надстройки строится последовательность приближений , i=0,1,…n.

Назовем вектором невязок следующий вектор:

Задача Excel заключается в том, чтобы найти такое приближение , при котором вектор невязок стал бы нулевым, т.е. добиться совпадения значений правых и левых частей системы .

В качестве примера рассмотрим СЛАУ (3.27).

Последовательность действий:

1. Оформим таблицу, как показано на рис.3.4. Введем коэффициенты системы (матрицу А) в ячейки А3:С5.

Рис.3.4. Решение СЛАУ с помощью надстройки «Поиск решения»

2. В ячейках А8:С8 будет сформировано решение системы 1, х2, х3). Первоначально они остаются пустыми, т.е. равными нулю. В дальнейшем будем их называть изменяемыми ячейками.. Однако для контроля правильности вводимых далее формул, удобно ввести в эти ячейки какие-либо значения, например, единицы. Эти значения можно рассматривать как нулевое приближение решения системы, = (1, 1, 1).

3. В столбец D введем выражения для вычисления левых частей исходной системы. Для этого в ячейкуD3 введем и затем скопируем вниз до конца таблицы формулу:

D3=СУММПРОИЗВ (A3:C3;$A$8:$C$8).

Используемая функция СУММПРОИЗВ принадлежит категории Математические.

4. В столбец Е запишем значения правых частей системы (матрицу В).

5. В столбец F введем невязки в соответствии с формулой (3.29), т.е. введем формулу F3=D3-E3 и скопируем ее вниз до конца таблицы.

Читайте так же:
Как открыть «Панель управления» в Windows 10

6. Будет не лишним проверить правильность вычислений для случая = (1, 1, 1).

7. Выберем команду ДанныеАнализПоиск решения.

Рис. 3.5. Окно надстройки «Поиск решения»

В окне Поиск решения (рис.3.5) в поле Изменяемые ячейки укажем блок $А$8:$С$8, а в поле Ограничения$F$3:$F$5=0. Далее щелкнем по кнопке Добавить и введем эти ограничения. И затем — кнопка Выполнить

Полученное решение систем (3.28) х1=1; х2=–1 х3=2 записано в ячейках А8:С8, рис.3.4.

Реализация метода Якоби средствами приложения MS Excel

В качестве примера рассмотрим систему уравнений (3.19), решение которой методом Якоби получено выше (пример 3.2)

Приведем эту систему к нормальному виду:

Последовательность действий

1. Оформим таблицу, как показано на рис.3.6.:

• Матрицы и (3.15)введем в ячейки В6:Е8.

• Значение e–в Н5.

• Номер итерации k сформируем в столбце А таблицы с помощью автозаполнения.

• В качестве нулевого приближения выберем вектор

= (0, 0, 0) и введем его в ячейки В11:D11.

2. Используя выражения (3.29), в ячейки В12:D12 запишем формулы для вычисления первого приближения:

Эти формулы можно записать иначе, используя функцию Excel СУММПРОИЗВ.

В ячейку Е12 введем формулу: E12=ABS(B11-B12) и скопируем ее вправо, в ячейки F12:G12.

Рис.3.6. Схема решения СЛАУ методом Якоби

3. В ячейку Н12 введем формулу для вычисления M (k) , используя выражение (3.18): Н12 = МАКС(E12:G12). Функция МАКС находится в категории статистические.

4. Выделим ячейки В12:Н12 и скопируем их вниз до конца таблицы. Таким образом, получим k приближений решения СЛАУ.

5. Определим приближенное решение системы и количество итераций, необходимое для достижения заданной точности e.

Для этого оценим степень близости двух соседних итераций по формуле (3.18). Воспользуемся Условным форматированиемв ячейках столбца.

Результат такого форматирования виден на рис.3.6. Ячейки столбца Н, значения которых удовлетворяют условию (3.18), т.е. меньше e=0,1, тонированы.

Анализируя результаты, принимаем за приближенное решение исходной системы с заданной точностью e=0,1 четвертую итерацию, т.е.

Исследуем характер итерационного процесса. Для этого выделим блок ячеек А10:D20 и, используя Мастер диаграмм, построим графики изменения каждой компоненты вектора решения в зависимости от номера итерации,

Приведенные графики (рис.3.7) подтверждают сходимость итерационного процесса.

Рис. 3.7. Иллюстрация сходящегося итерационного процесса

Изменяя значение eв ячейке Н5, получим новое приближенное решение исходной системы с новой точностью.

Реализация метода прогонки средствами приложения Excel

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

Векторы:

Последовательность действий

1. Оформим таблицу, как показано на рис.3.8. Исходные данные расширенной матрицы системы (3.30), т.е. вектора введем в ячейки B5:E10.

2. Про гоночные коэффициенты U=0 и V=0 введем в ячейки G4 и H4 соответственно.

3. Вычислим прогоночные коэффициенты Li, Ui, Vi. Для этого в ячейках F5, G5, H5 вычислим L1, U1, V1. по формуле (3.8). Для этого введем формулы:

F5 = B5*G4+C5; G5=-D5/F5, H5 = (E5-B5*H4)/F5, и затем скопируем их вниз.

Рис.3.8. Расчетная схема метода «прогонки»

4. В ячейке I10 вычислим x6 по формуле (3.10)

5. По формуле (3.7) вычислим все остальные неизвестные x5 x4, x3, x2, x1. Для этого в ячейке I9 вычислим x5 по формуле (3.6): I9=G9*I10+H9 . А далее копируем эту формуле вверх.

Читайте так же:
Как включить режим чтения в браузере Яндекс

Контрольные вопросы

1. Система линейных алгебраических уравнений (СЛАУ). Что является решением СЛАУ. Когда существует единственное решение СЛАУ.

2. Общая характеристика прямых (точных) методов решения СЛАУ. Методы Гаусса и прогонки.

3. Общая характеристика итерационных методов решения СЛАУ. Методы Якоби (простых итераций) и Гаусса-Зейделя.

4. Условия сходимости итерационных процессов.

5. Что понимают под терминами обусловленности задач и вычислений, корректности задачи решения СЛАУ.

Глава 4.

Численное интегрирование

При решении достаточно большого круга технических задач приходится сталкиваться с необходимостью вычисления определенного интеграла:

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

Если непрерывная на отрезке [a, b] функция y = f(x) имеет на этом отрезке первообразную F(x), т.е. F ’ (x) = f(x) , то интеграл (4.1) может быть вычислен по формуле Ньютона – Лейбница:

Однако, только для узкого класса функций y=f(x) первообразная F(x) может быть выражена в элементарных функциях. Кроме того, функция y=f(x) может задаваться графически или таблично. В этих случаях применяют различные формулы для приближенного вычисления интегралов.

Такие формулы называют квадратурными формулами или формулами численного интегрирования.

Формулы численного интегрирования хорошо иллюстрируются графически. Известно [1, 12], что значение определенного интеграла (4.1) пропорционально площади криволинейной трапеции, образованной подынтегральной функцией y=f(x), прямыми х=а и х=b, осью ОХ (рис.4.1).

Задачу вычисления определенного интеграла (4.1) заменяем задачей вычисления площади этой криволинейной трапеции. Однако задача нахождения площади криволинейной не является простой.

Отсюда идея численного интегрирования [3, 6] будет заключатся в замене криволинейной трапеции фигурой, площадь которой вычисляется достаточно просто.

y=f(x)
y
x
xi
xi+1
xn=b
xо=a
Si

Рис.4.1. Геометрическая интерпретация численного интегрирования

Для этого отрезок интегрирования [a, b] разобьем на n равных элементарных отрезков [xi ,xi+1] (i=0, 1, 2, …. n-1), с шагом h=(b-a)/n. При этом криволинейная трапеция разобьется на n элементарных криволинейных трапеций с основаниями равными h (рис.4.1).

Каждая элементарная криволинейная трапеция заменяется фигурой, площадь которой вычисляется довольно просто. Обозначим эту площадь Si . Сумма всех этих площадей называется интегральной суммой и вычисляется по формуле

Тогда приближенная формула вычисления определенного интеграла (4.1) имеет вид

Точность вычисления по формуле (4.4) зависит от шага h, т.е. от числа разбиений n. С увеличением n интегральная сумма приближается к точному значению интеграла

Это хорошо проиллюстрировано на рис.4.2.

n
бn
J
Точное значение интеграла

Рис.4.2. Зависимость точности вычисления интеграла

от числа разбиений

В математике доказывается теорема: если функция y=f(x) непрерывна на [a, b], то предел интегральной суммы бn существует и не зависит от способа разбиения отрезка [a,b] на элементарные отрезки.

Формулу (4.4) можно использовать, если известна степень точности такого приближения. Существуют различные формулы для оценки погрешности выражения (4.4), но, как правило, они достаточно сложны. Будем проводить оценку точности приближения (4.4) методом половинного шага.

Поиск решения

Поиск решений является частью блока задач, который иногда называют анализ «что-если».
Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой.
Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.
Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму зависимой ячейки — например можно изменить объем планируемого бюджета рекламы и увидеть, как это повлияет на проектируемую сумму расходов.
Встроенный инструмент Excel имеет ограничения. По числу переменных: Nv и по числу ограничений: Nc .

Читайте так же:
Как установить драйвер видеокарты AMD

Как подключить Поиск решения

Интерфейс надстройки Поиск решения

Поиск решения находится в блоке Данные основного меню MS Excel

Поиск решения в меню данные

При нажатии Поиск решения появится панель интерфеса надстройки

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

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

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

Выберите метод решения.
Здесь можно выбрать линейную или нелинейную задачу следует решать.

Your browser does not support the HTML5 canvas tag.

Найти решение. Эта клавиша запускает надстройку Поиск решения.

До. Здесь нужно выбрать вариант оптимизации значения целевой ячейки: должно значение быть максимальным, минимимальным или равным заданному числу. Заданное число вводится в это поле.

Вызывает диалоговое окно Добавление ограничения.

Вызывает диалоговое окно Изменение ограничения.

Удаляет выделеное ограничение.

Сбрасывает все настройки.

Сохраняет и загружает модель задачи.

При нажатии клавиши Добавить появляется диалоговое окно

Добавление ограничения

В поле Ссылка на ячейки указывают ячейки, которые должны меняться.

В поле Ограничения указывают ячейки, в которые записаны ограничения.

При нажатии клавиши Изменить появляется диалоговое окно

Изменение ограничения

В этом окне можно изменить введеные ранее ограничения.

Пример применения надстройки Поиск решения

формулировка задачи

Небольшая компания «Фасад» производит 3 типа дверей: стандартные, полированные и резные.
Компания работает «под заказ», поэтому продает всю производимую продукцию. На производстве работают 10 рабочих в одну смену (8 рабочих часов) 5 дней в неделю, что дает 400 часов в неделю. Рабочее время поделено между двумя существенно различными технологическими процессами: собственно производством и конечной обработкой дверей. Из 400 рабочих часов в неделю 250 отведены под собственно производство и 150 под конечную обработку. В таблице приведены временные затраты и прибыль от продажи одной двери каждого типа.
Сколько дверей различных типов нужно производить, чтобы максимизировать прибыль?

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector