!!!!!практические расчеты 25

1.

Основные элементарные
математические функции

2.

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

3.

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

4. Практические расчеты в MS Excel: Массивы

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

5.

Правила ввода табличных формул
- Перед вводом табличной формулы следует выделить ячейку
(интервал ячеек), в которой будет содержаться результат. Если результат
вычисления по формуле множественный, то выделенный интервал
должен иметь в точности требуемый размер и форму.
- Далее после знака равно (=) следует напечатать формулу и для
завершения ее ввода нажать клавиши Crtl +Shift+Enter .
Встроенные функции
для выполнения некоторых операций с матрицами:
- МОПРЕД(массив) - Возвращает определитель матрицы
(матрица хранится в массиве).
- МОБР(массив) - Возвращает обратную матрицу для матрицы,
хранящейся в массиве.
- МУМНОЖ(массив1;массив2) - Возвращает произведение
матриц.
- ТРАНСП(массив) - Возвращает транспонированный массив .

6.

ПРИМЕР:
Вычислить матричное выражение (А24*В42+С22)Т двумя способами:
с промежуточными результатами и в одну формулу.
Числовые значения задать самостоятельно, оформить исходные данные,
промежуточные результаты, конечный результат

7.

Решение:
1. Поместить исходные данные – матрицы А, В и С - в ячейки A3:D4, F3:G6, I3:J4
соответственно.
Решение с промежуточными результатами:
2. Умножить матрицу А на матрицу В. Для этого:
- Выделить область ячеек для результата умножения (A10:B11).
- Поставить = , выбрать Мастер функций
и открыть диалоговое окно, выбирать в окне
Категория – Математические, в окне Функция – МУМНОЖ и нажать на кнопку ОК .
- В появившемся диалоговом окне заполнить окна МАССИВ1 и МАССИВ2 .
- Одновременно нажать клавиши Ctrl+Shift+Enter.
3. Прибавить к результату умножения матрицу С. Для этого :
- Выделить диапазон ячеек E10:F11
- Ввести следующую формулу =A10:B11+I3:J4
- Одновременно нажать клавиши Ctrl+Shift+Enter.
4. Транспонировать результат сложения:
- Выделить диапазон ячеек А13:В14
- Поставить = , выбрать Мастер функций
и открыть диалоговое окно, выбирать в окне
Категория – Полный алфавитный перечень, в окне Функция – ТРАНСП, нажать на
кнопку ОК.
- В окне для аргумента функции набрать E10:F11.
- Одновременно нажать клавиши Ctrl+Shift+Enter.
Решение в одну формулу:
- Выделить промежуток ячеек под результат, например I10:J11
- В строке формул набрать формулу =ТРАНСП(МУМНОЖ(A3:D4,F3:G6)+I3:J4)
- Одновременно нажать клавиши Crtl +Shift+Enter.

8.

Ячейки B4:D6 содержат исходную матрицу.
=МОПРЕД(массив), где
массив - это числовой массив с равным количеством строк и
столбцов
=ТРАНСП(массив), где
массив - это транспонируемый массив или диапазон ячеек на
рабочем листе. Массив может быть интервалом ячеек.
Транспонирование массива заключается в том, что первая
строка массива становится первым столбцом нового массива,
вторая строка массива становится вторым столбцом нового
массива, и так далее.
=МОБР(массив), где
массив - это числовой массив с равным количеством строк и
столбцов
=МУМНОЖ(массив1;массив2), где
массив1, массив2 - это перемножаемые массивы. Количество
столбцов аргумента массив1 должно быть таким же, как
количество строк аргумента массив2, и оба массива должны
содержать только числа.
Для вычисления необходимо:
а) выделить результирующую ячейку или диапазон
ячеек и нажать =
б) выбрать Мастер функций
, в появившемся окне
выбрать нужную функцию и задать диапазон значений
в качестве аргумента
в) для появления результата нажать ОК или
Ctrl+Shift+Enter

9. Решение систем линейных алгебраических уравнений (СЛАУ)

Решить систему линейных
уравнений в матричном виде,
сделать проверку решения.
Метод обратной матрицы.
- Поместить исходные данные матрицу коэффициентов А и столбец
свободных членов уравнений В - в ячейки A3:C5 и A7:A9 соответственно.
- С помощью функции МОПРЕД проверить определитель матрицы А.
Если он не равен 0, то существует матрица, обратная к А.
- Найти матрицу, обратную к А, для этого:
Выделить интервал ячеек А12:C14
С помощью мастера функций, выбрать в окне Категория
Математические, в окне Функция – МОБР,
В окне для аргумента функции набрать А3:С5
После нажатия на кнопку ОК поместить курсор в строку формул и
одновременно нажать клавиши Crtl +Shift+Enter.
- Найти решение уравнения в виде вектора Х, для этого с помощью
функции МУМНОЖ перемножить матрицу, обратную к А и векторстолбец В.
- Выполнить проверку и убедиться, что найденный вектор Х удовлетворяет
исходной системе уравнений АХ=В .

10.

11.

Метод Крамера
1. Поместить матрицу
коэффициентов А и столбец
свободных членов
соответственно в ячейки
А3:С5 и А8:А10.
2. Найти определитель матрицы
А Det.
3. Сформировать матрицы А1,
А2 и А3, заменив
соответственно 1, 2 и 3
столбцы на столбец
свободных членов.
4. Найти определители
полученных матриц Det1,
Det2, Det3.
5. Найти корни уравнения

12. Надстройка «Поиск решения»

«Поиск решения» (Solver) — надстройка MS Excel,
предназначенная для решения оптимизационных задач различной
сложности. Позволяет находить оптимальные значения в одной
ячейке (целевой) путём изменения значений в других ячейках
(изменяемых) при соблюдении заданных ограничений.
Инструмент решает три основных типа задач:
• Линейное программирование — оптимизация при линейных
взаимосвязях между переменными (например, распределение
бюджета).
• Нелинейное программирование — работа со сложными
зависимостями (например, модели ценообразования).
• Целочисленное программирование — когда результаты должны
быть целыми числами (например, количество единиц продукции).
В отличие от обычных формул Excel, которые выполняют
предопределённые вычисления,
«Поиск решения» работает в обратном направлении:
пользователь указывает, какой результат нужно получить,
а инструмент подбирает необходимые значения.

13.

АКТИВАЦИЯ
1.
2.
3.
4.
5.
По умолчанию надстройка «Поиск решения» может быть отключена.
Чтобы её активировать, нужно:
Открыть Excel и перейти на вкладку «Файл».
В меню выбрать «Параметры».
В открывшемся окне найти и нажать «Надстройки». Появится список надстроек.
В нижней части окна будет Управление Надстройки Excel и нажать «Перейти».
Установить флажок напротив «Поиск решения» и подтвердить изменения, нажав «ОК».
После активации инструмент будет доступен на вкладке «Данные» в группе «Анализ»

14.

● Оптимизировать целевую функцию. Указывается
адрес целевой ячейки, значение которой необходимо
минимизировать, максимизировать или приравнять к
определенному значению. Целевая ячейка должна быть
единственной;
● Опции Минимум, Максимум, Значения;
● Изменяя ячейки переменных. Указываются адреса
ячеек с переменными или неизвестными задачи, значения
которых нужно подобрать таким образом, чтобы
оптимизировать целевую ячейку. Целевая функция
зависит от изменяемых ячеек и связана с ними
некоторой формулой. Количество неизвестных не
должно превосходить 200;
● В соответствии с ограничениями. Окно содержит
ограничения, которым должны удовлетворять
переменные задачи.
● Флажок в поле Сделать переменные без ограничений
неотрицательными позволяет не вводить
дополнительно ограничения на изменяемые ячейки, если
их значения неотрицательны.
● Раздел Выберите метод решения позволяет
определить метод решения задачи (Симплексный метод –
используется для решения линейных задач; Метод ОПГ
(обобщенного приведенного градиента) - используется
для решения гладких нелинейных задач; Эволюционный
поиск решения - используется для решения негладких
задач).
● Кнопка Найти решение запускает процесс решения
задачи. Если в процессе выполнения процедуры поиска
решения решение найти не удалось, однако известно, что
оно существует, то рекомендуется изменить настройки
инструмента и повторно запустить его. Настройки
устанавливаются в диалоговом окне Параметры,
которое открывается нажатием одноименной кнопки.

15.

НАСТРОЙКА
1.
2.
3.
Перед использованием «Поиска решения» необходимо
правильно структурировать рабочий лист:
Создать ячейку для целевой функции (то, что нужно максимизировать, минимизировать или
привести к конкретному значению).
Определить изменяемые ячейки (параметры, которые Excel будет корректировать).
Подготовить формулы для ограничений (условия, которые должны соблюдаться).
Решить систему
линейных уравнений

16.

Алгоритм:
1.
2.
3.
4.
5.
6.
7.
Подготавливаются исходные данные. Для этого формируется
матрица коэффициентов системы А (A2:C4), столбец свободных элементов В (G2:G4).
Перед решением системы линейных уравнений необходимо проверить ее на совместность.
Система линейных уравнений является совместной (имеет единственное решение) тогда и только
тогда, когда главный определитель системы отличен от нуля. Проверка условия совместности
записана в ячейке A7: =ЕСЛИ(МОПРЕД(А2:С4)=0;"Система несовместна"; "Система
совместна")
Ячейки столбца Х (корни системы уравнений, В10:В12) на начальном этапе решения задачи
заполняются нулями. В рассматриваемой задаче ячейки В10:В12 являются изменяемыми
ячейками.
В целевую ячейку (A16) вводится формула, соответствующая первому уравнению. Ограничениям
будут соответствовать два оставшихся уравнения (A18 и A19)
Окно Поиска решения заполняется следующим образом: Целевую функцию (1 уравнение
системы) надо оптимизировать до значения 12, т.е. значения первого свободного элемента
системы. Каждое ограничение должно приравниваться к значению соответствующего
свободного элемента системы уравнений. (Для добавления ограничения нажмите кнопку
Добавить!). Параметры надстройки не изменялись. В качестве метода решения выбран
симплекс-метод.
Нажимается кнопка Найти решение.
В ячейках столбца Х (диапазон В10:В12) будут получены корни системы уравнений. Чтобы
проверить правильность найденного решения, надо умножить матрицу коэффициентов А на
столбец решений Х с помощью функции МУМНОЖ. В результате должны получиться значения
столбца свободных элементов В. Поскольку доказано, что система имеет единственное решение
(совместна), то найденное решение является верным.

17.

18.

19.

20.

21. Задание 1-1 «MS Excel: Практические расчеты и поиск решения»