Расширенный курс Excel 2007

1 слайд

Математическая модель оптимизационной задачи.
Решение оптимизационных задач с помощью Excel

2 слайд

Какие задачи называются оптимизационными?
С помощью каких средств в Excel можно решать оптимизационные задачи?
Подбор параметра. Поиск решения.
Какие команды нужно выполнить, чтобы воспользоваться средством «Подбор параметра»?
Данные – «Анализ что-если» – Подбор параметра.
Какие команды нужно выполнить, чтобы воспользоваться средством «Поиск решения»?
Данные – Поиск решения.

Актуализация знаний:

3 слайд

Математическая модель – это математическое представление реальности.

4 слайд

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

5 слайд

Решение задачи линейного программирования
Фирменные блюда

6 слайд

Необходимо приготовить фирменные блюда трех видов, используя ингредиенты четырех видов при известной норме использования ингредиентов (гр) для приготовления одной порции каждого блюда.
Задача:Фирменные блюда

7 слайд

Норма использования ингредиентов (гр)
для 1 порции

8 слайд

Блюдо А – 120 грн;
Блюдо Б – 100 грн;
Блюдо В – 80 грн.
Стоимость одной порции

9 слайд

Ингредиент 1 – 5 кг;
Ингредиент 2 – 4 кг;
Ингредиент 3 – 4 кг;
Ингредиент 4 – 3 кг;




Ежедневная доставка в заведение ингредиентов:

10 слайд

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

11 слайд

Введем обозначения:
Х1 – количество порций блюда А.
Х2 – количество порций блюда Б.
Х3 – количество порций блюда В.
Общую стоимость блюд можно определить по формуле:
120Х1 + 100 Х2 + 80 Х3 – формула целевой функции





Построение математической модели

12 слайд

(4) 120Х1 + 100 Х2 + 80 Х3  max (целевая функция)
(5) 10Х1 + 50 Х2 + 10 Х3 <= 5000 (затраты ингр.1)
(6) 20Х1 + 40 Х3 <= 4000 (затраты ингр.2)
(7) 20Х1 + 10 Х2 + 30 Х3 <= 4000 (затраты ингр.3)
(8) 30Х1 + 15 Х2 <= 3000 (затраты ингр.4)
(9) Х1 >=0, Х2 >=0, Х3 >=0 (кол-во порций – неотрицательные числа)
(10) Х1, Х2, Х3 – целые числа

Модель задачи:

13 слайд

Отчет по результатам

14 слайд

Стр. 189-200

Домашнее задание:

1 слайд

МАТРИЦЫ И ОСНОВНЫЕ
ОПЕРАЦИИ С НИМИ

2 слайд

МАТРИЦА – это прямоугольная таблица, в каждой ячейке которой записано число.


Числа принято называть элементами матрицы.

3 слайд

Например:

А =


а11=5; а12= -7; а13=3;
а21=4; а22= 2; а23=9;
Каждый элемент матрицы имеет два индекса: номер строки и номер столбца, на пересечении которых он размещен.

4 слайд

Основная характеристика матрицы – размерность – определяется количеством строк и столбцов в ней.

Размерность приведенной выше матрицы А равна 23
(2 строки; 3 столбца).

5 слайд

ОПЕРАЦИИ С МАТРИЦАМИ
Умножение матрицы на число:


=
10А = 10
На заданное число умножается каждый элемент матрицы.

6 слайд

2. Сложение матриц:

Складывать можно только матрицы одинаковой размерности.
Каждый элемент одной матрицы складывается с соответствующим элементом другой матрицы.

7 слайд

3. Умножение матриц.
Произведение матриц определено, если число строк первой матрицы равно числу столбцов второй.

Например:

=
1х3 + 2х2 + 3х6 = 25
1х1 + 2х5 + 3х0 = 11
Каждый элемент строки одной матрицы умножается на каждый элемент столбца другой матрицы и произведения складываются.

8 слайд

КВАДРАТНАЯ МАТРИЦА – это матрица, в которой количество строк равно количеству столбцов.
Главная диагональ
Побочная диагональ

9 слайд

Матрица называется единичной, если на ее главной диагонали размещены единицы, а остальные элементы равны нулю.
Е =
Единичная матрица для операции умножения матриц играет ту же роль, что и 1 для операции умножения чисел.

10 слайд

Обратная матрица
Матрица А-1 называется обратной матрице А,
если А-1 А = Е,
т.е. произведение ее и самой матрицы является единичной матрицей.

11 слайд

Например, для матрицы
Обратной является матрица
Вычислите самостоятельно произведение матриц, в результате получите единичную матрицу.

Узнать, существует ли для данной матрицы обратная ей матрица, можно с помощью определителя матрицы, который обозначается |A|.

12 слайд

ФУНКЦИИ ДЛЯ РАБОТЫ С МАТРИЦАМИ В EXCEL
(категория Математические)
МУМНОЖ – вычисляет произведение матриц.
МОБР – вычисляет матрицу, обратную данной.
МОПРЕД – вычисляет определитель матрицы.

13 слайд

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

Результатом выполнения первых двух функций является диапазон значений.
Следует выделить весь диапазон, где будут содержаться результаты, ввести формулу функции и нажать клавиши Ctrl+Shift+Enter.

1 слайд

Основы корреляционного анализа

2 слайд

Графическое представление рядов распределения

3 слайд

Пример дискретного ряда распределения

4 слайд

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

5 слайд

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

6 слайд

Факторные признаки являются такими, что от них зависят другие.
Результативные признаки сами зависят от других признаков.

7 слайд

Пример: Заболеваемость астмой

8 слайд

Графический анализ корреляционной связи
Диаграмма рассеивания
Факторный признак
Результативный признак
Линия тренда
Пример прямой корреляционной связи

9 слайд

Пример обратной корреляционной связи

10 слайд

Отсутствие корреляционной связи

11 слайд

Коэффициент корреляции

Качество связи между двумя величинами можно выразить с помощью коэффициента корреляции. Это число k из интервала [-1;1]. Если k близко к -1, то корреляционная связь между величинами является обратной, а если k близко к 1 – прямой. Чем ближе k к нулю, тем корреляционная связь слабее. Если говорить более основательно, то прочность линейной корреляционной связи оценивается так:
|k|  0,8 – сильная корреляционная связь;
0,4  |k| < 0,8 – корреляционная связь имеется в наличии;
|k| < 0,4 – корреляционная связь отсутствует.

12 слайд

В МS Excel для вычисления коэффициента корреляции используется функция КОРРЕЛ:

КОРРЕЛ(диапазон_1; диапазон_2) ,
где диапазон_1 диапазон_2 содержат наборы значений, между которыми определяется зависимость.

13 слайд

1 слайд

Решение задач на подбор параметра. Понятие о численных методах решения задач

2 слайд

аналитические (с помощью формул);
графические (решение находится путем геометрических построений);
Численные (позволяют свести решение задачи к выполнению конечного числа арифметических действий над числами)
Основные группы методов решения математических задач:

3 слайд

Используется понятие целевой функции – она должна достичь определенного значения или оптимизироваться (минимизироваться или максимизироваться).

Численные методы решения задач

4 слайд

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

5 слайд

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

В табличном процессоре Excel есть специальные средства, которые автоматически подбирают нужные значения в ячейках параметров. Они называются Подбор параметра (Данные – Работа с данными - Анализ «что-если» – Подбор параметра) и Поиск решения.

Первый из них позволяет получить в целевой ячейке определенное значение, а другой – оптимизировать значение целевой функции.


Численные методы решения задач

6 слайд

В одну из ячеек ЭТ введите формулу целевой функции.

Подбор параметра
Задача: Решить уравнение x – 5 = 0.
Х

7 слайд

Данные – Анализ «что-если» – Подбор параметра

8 слайд

9 слайд

Стр. 178-181, № 3, стр. 188
Задание на дом

1 слайд

Регрессионный анализ

2 слайд

Вопрос:Каким будет уровень заболеваемости астмой, если концентрация угарного газа будет составлять 10 мг/м3?
Основная задача регрессионного анализа – прогнозирование.

3 слайд

Основной метод прогнозирования – построение на основе выборочных данных уравнения регрессии вида у = f(x), связывающего факторный и результативный признаки.

Линия тренда – это и есть график уравнения регрессии.

4 слайд

Автоматическое построение линии тренда
1. Выделить диаграмму рассеивания.
2. Работа с диаграммами – Макет – Анализ - Линия тренда – Дополнительные параметры линии тренда.
3. В окне Формат линии тренда выбрать тип зависимости – линейная.
4. Установить флажки «показывать уравнение на диаграмме» и «поместить на диаграмму величину достоверности апроксимации (R^2);
5. Задать величину прогноза.

5 слайд

6 слайд

7 слайд

8 слайд

При концентрации угарного газа 10 мг/м3 уровень заболеваемости  120 людей на 1000 жителей.

9 слайд

Коэффициент детерминации
Близость уравнения регрессии и линии тренда к выборочным данным характеризуется величиной коэффициента детерминации R2
(0 ≤ R2 ≤ 1).

Уравнение регрессии наиболее отвечает действительности, когда R2 приближается к своему максимальному значению.

10 слайд

1 слайд

РЕШЕНИЕ СИСТЕМ ЛИНЕЙНЫХ АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ.
ПРАКТИЧЕСКАЯ РАБОТА № 15

2 слайд

ОБШИЙ ВИД СИСТЕМЫ ЛИНЕЙНЫХ АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ

3 слайд

СИСТЕМУ МОЖНО ЗАПИСАТЬ КАК ПРОИЗВЕДЕНИЕ МАТРИЦ:
=
, или АХ = В – матричное уравнение
Обе части матричного уравнения можно умножать на одну и ту же матрицу (справа или слева).

4 слайд

Умножив обе части уравнения АХ = В слева на матрицу А-1, получим:

А-1 АХ = А-1 В 
ЕХ = А-1 В 
Х = А-1 В

Правило решения системы линейных алгебраических уравнений.

5 слайд

Решение системы уравнений методом обратной матрицы Х = А-1 В

6 слайд

Матрица коэффициентов А и вектор свободных коэффициентов b имеют вид:

7 слайд

1. Введем матрицу А и вектор b в рабочий лист Excel

8 слайд

2. Вычислим матрицу, обратную к А, используя функцию МОБР.

9 слайд

3. Вычислим произведение А-1 В (это и будет решение).

1 слайд

Точность полученных результатов и разрядность представления результатов

2 слайд

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

3 слайд

Пример.

4 слайд

Для читабельности столбца с результатами укажем 2 десятичных знака после запятой.

5 слайд

Не путайте округленное значение с отформатированным.

6 слайд

Цена указана в рублях без копеек.
Цена на товар стоимостью $2 составила 56,16 руб (2 х 28,06 = 56,16).
Цена на товар стоимостью $3 составила 84,24 руб (3 х 28,06 = 84,24).

7 слайд

Панель функции ОКРУГЛ
Синтаксис:
ОКРУГЛ(число; количество_цифр)

8 слайд

9 слайд

Домашнее задание
конспект

1 слайд

Решение оптимизационных задач с помощью Excel

2 слайд

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



Транспортная задача

3 слайд

Потребности складов
Стоимость перевоза единицы товара
Пропускная способность портов
Существующий объем перевозок
Решение:
1. Создание электронной таблицы с исходными данными.

4 слайд

2. Внесение формул в столбец Всего и в строки Итого, Перевозка.

5 слайд

3. Внесение в целевую ячейку В15 формулы – итоговой суммы стоимости перевозок.

Целевая ячейка
Изменяемые ячейки

6 слайд

Количество перевезенных грузов не может превышать производственные мощности портов.
Количество доставляемых грузов не должно быть меньше потребностей складов.
Число перевозок не может быть меньше нуля.

4. Ограничения:

7 слайд

Затраты на перевозки до оптимизации – 14099$.
Затраты на перевозки после оптимизации – 11697$.

Вывод:

8 слайд

Конспект.
Домашнее задание: