Презентация подбор параметров в excel
Обновлено: 05.07.2024
Презентация на тему: " Лабораторная работа Тема занятия: Средства условного анализа в EXCEL. Основная цель: Научиться пользоваться программами Подбор параметра и Поиск решения." — Транскрипт:
1 Лабораторная работа Тема занятия: Средства условного анализа в EXCEL. Основная цель: Научиться пользоваться программами Подбор параметра и Поиск решения.
2 Excel – ваши уверенные шаги в анализе и прогнозировании На этом занятии мы продолжим знакомиться с некоторыми возможностями анализа данных в Excel. Научимся: использовать средство «Подбор параметра»; использовать средство «Подбор параметра»; работать со средством «Поиск решения». работать со средством «Поиск решения».
3 Подбор параметра – это средство Excel для так называемого анализа «что, если » Программа «Подбор параметра» позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей.
4 Найти решение уравнения X^3 - 3*X^2 + X = -1 средствами программы Excel Найти решение уравнения X^3 - 3*X^2 + X = -1 средствами программы Excel Занесем в ячейку A1 значение 0. Занесем в ячейку A1 значение 0. Занесем в ячейку B1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку A1. Соответствующая формула будет иметь вид: =A1^3-3*A1^2+A1 Занесем в ячейку B1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку A1. Соответствующая формула будет иметь вид: =A1^3-3*A1^2+A1 Даём команду Сервис - Подбор параметра. Даём команду Сервис - Подбор параметра.
5 Использование надстройки « Поиск решения». Программа «Поиск решения» позволяет получить результат на основе изменения значений нескольких ячеек. При выполнении поиска решения можно задать условия – ввести ограничения.
6 Поиск решения применим при вычислении площади треугольника Площадь треугольника вычисляется по формуле: S = ½*a*h (где a – основание треугольника, h – высота). Площадь треугольника вычисляется по формуле: S = ½*a*h (где a – основание треугольника, h – высота).
7 Подбор параметра выполняется с помощью команды меню Сервис – Подбор параметра. Задача 1. Известен размер вклада, который будет помещен в банк на неопределенный срок под определенный процент. Требуется рассчитать сумму возврата вклада в конце периода и определить условия помещения вклада, наиболее подходящие для его владельца. Известен размер вклада, который будет помещен в банк на неопределенный срок под определенный процент. Требуется рассчитать сумму возврата вклада в конце периода и определить условия помещения вклада, наиболее подходящие для его владельца.
8 Присвойте листу в книге имя Подбор. Создайте таблицу Введите формулы в ячейки B4 и B5 для вычисления коэффициента увеличения вклада и суммы возврата. С чего начинается формула ?
9 Скопируйте созданную таблицу на этот же лист, а так же на листы 2 и 3.
10 Выполнив команду Сервис – Подбор параметра, получаем диалоговое окно «Подбор параметра», в котором правильно устанавливаем нужные параметры.
11 В результате выполнения команды Сервис – Подбор параметра получили процентную ставку и срок вклада при которых сумма возврата вклада составляет 8000 рублей.
12 В первой копии таблицы, изменяя одновременно два параметра, подберите значения срока вклада и процентной ставки, при которых сумма возврата будет составлять 8000 рублей. Для этого выполните следующие действия: 1. Введите команду СЕРВИС – Поиск решения и в диалоговом окне «Поиск решения» установите следующие параметры: адрес целевой ячейки - $B$5 – сумма возврата вклада; адрес целевой ячейки - $B$5 – сумма возврата вклада; Подбираемое для целевой ячейки значение – 8000р; Подбираемое для целевой ячейки значение – 8000р; В поле Изменяя ячейки введите абсолютные адреса ячеек со сроком вклада и величиной процентной ставки. В поле Изменяя ячейки введите абсолютные адреса ячеек со сроком вклада и величиной процентной ставки. 2. Введите ограничения для ячейки со сроком вклада – цел – целое число лет. 3. Щелкните по кнопке Выполнить.
13 Обратите внимание на то, что оба изменяемых параметра косвенно связаны со значением целевой ячейки В5=В4*В1, так как входят в формулу расчета коэффициента увеличения вклада В4=(1+В3)^В2.
14 В диалоговом окне «Результаты поиска решения» установите: Сохранить найденное решение; Тип отчета – Результаты.
15 Во второй копии таблицы на листе Поиск выполнить еще раз операцию Поиск решения, установив следующие параметры: адрес и значение целевой ячейки – сумма возврата вклада 8000р.; адрес и значение целевой ячейки – сумма возврата вклада 8000р.; В поле Изменяя ячейки введите абсолютные адреса ячеек с размером вклада, сроком вклада и величиной процентной ставки; В поле Изменяя ячейки введите абсолютные адреса ячеек с размером вклада, сроком вклада и величиной процентной ставки; Добавьте ограничения для ячейки с величиной процентной ставки:
16 Диалоговое окно «Поиск решения» будет выглядеть следующим образом
17 Задача 2. Используя режим подбора параметра, определить штатное расписание фирмы. Исходные данные приведены в таблице 1(первый вариант).
18 Задача 3. Используя программу, Поиск решения, решите задачу оптимизации выпуска изделий на предприятии «Протон». (второй вариант). Предприятие выпускает приборы трех типов – прибор 1, 2 и 3. Количество комплектующих элементов, используемых для производства одного прибора каждого типа, и ежедневный запас комплектующих элементов приведены в таблице Предприятие выпускает приборы трех типов – прибор 1, 2 и 3. Количество комплектующих элементов, используемых для производства одного прибора каждого типа, и ежедневный запас комплектующих элементов приведены в таблице Расход элементов каждого типа вычисляется по формуле =B3*$B$6+C3*$C$6+D3*$D$6. Определите, каким образом следует изменить соотношение типов выпускаемых приборов, чтобы обеспечить максимальный суммарный выпуск приборов при оптимальном расходе имеющегося запаса комплектующих элементов?
19 Домашнее задание. Домашнее задание. Задание 1. Составление плана выгодного производства Задание 1. Составление плана выгодного производства Фирма производит несколько видов продукции из одного и того же сырья – A, B и C. Реализация продукции A дает прибыль 10 р., B – 15 р. И C – 20 р. На единицу изделия. Фирма производит несколько видов продукции из одного и того же сырья – A, B и C. Реализация продукции A дает прибыль 10 р., B – 15 р. И C – 20 р. На единицу изделия. Продукцию можно производить в любых количествах, поскольку известно, что сбыт, обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной. Продукцию можно производить в любых количествах, поскольку известно, что сбыт, обеспечен, но ограничены запасы сырья. Необходимо определить, какой продукции и сколько надо произвести, чтобы общая прибыль от реализации была максимальной. Нормы расхода сырья на производство продукции каждого вида приведены в таблице Нормы расхода сырья на производство продукции каждого вида приведены в таблице
20 Таблица для д/з План выгодного производства СырьеНорма расхода сырья Запас сырьяРасход сырья ABC Сырье ? Сырье ? Сырье ? Прибыль на ед. изделия Количество. Общая прибыль.
3. С помощью Ехсеl можно создавать самые различные документы, выполнять самые различные задачи, например:
• составлять всевозможные списки, отчеты, ведомости, бланки;
• оперативно выполнять вычисления различной сложности;
• по данным таблиц строить динамически связанные с ними диаграммы, графики;
• решать сложные финансовые, экономические и математические задачи и, в том
числе, задачи статистического анализа и т.д.
Электронные таблицы Ехсеl имеют поистине огромный набор возможностей.
Однако обычно пользователь в своей работе применяет только их
небольшую часть в зависимости от условий поставленной задачи.
«Подбор параметра» в Excel Microsoft Office позволит финансовому
отделу уйти от проведения утомительного анализа методом проб и
ошибок и сразу рассчитать величину изменяемого параметра.
4. ПОДБОР ПАРАМЕТРА
Специальная функция «подбор параметра» позволяет определить параметр (аргумент)
функции, если известно ее значение.
При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула,
зависящая от этой ячейки, не возвратит заданное значение.
«Данные
»
Когда желаемый результат одиночной формулы известен, но
неизвестны значения, которые требуется ввести для получения
этого
Анализ
«что
если»
результата, можно воспользоваться средством
«Подбор параметра» следующим образом:
«Подбор
параметра…»
При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока
формула, зависимая от этой ячейки, не возвращает нужный результат.
При выполнении процедуры подбора параметра значения указанной ячейки варьируются
до тех пор, пока зависимая формула не вернет искомый результат. Процедуру подбора
параметра следует использовать для поиска особого значения отдельной ячейки, при
котором другая ячейка принимает известное значение. Если формула ячейки зависит от
нескольких величин, для поиска оптимального набора значений при изменении
нескольких влияющих ячеек или при наложении ограничений на одну или несколько
ячеек, нужно применять для поиска решения команду Поиск решения.
6. Подбор параметра
1. Выбрать целевую
ячейку, то есть ячейку с
формулой, результат
которой нужно
подобрать.
2. Вызвать команду
“Сервис » Подбор
параметра”. В поле
“Установить в ячейке”
появившегося
диалогового окна будет
отображаться адрес
целевой ячейки.
3. Задать в поле
“Значение” значение,
которое должна
содержать целевая
ячейка
4. Указать в поле
“Изменяя значение
ячейки” адрес ячейки,
значение которой
необходимо установить
таким, чтобы в целевой
ячейке получить заданное
значение.
5. Нажать кнопку ОК, и
нужный параметр будет
подобран в диалоговом
окне “Результат подбора
параметра”. По окончании
этого процесса в нем
отобразятся результаты.
6. Нажать кнопку ОК,
если вы хотите заменить
значения в ячейках на
рабочем листе новыми,
или кнопку “Отмена” в
противном случае.
7. Пример №1 Для бурения новой скважины компании требуется долота мягких абразивных(215.9 МЗГВ)-3шт, твердых абразивных
пород(295.3 МСГАУ)-3шт. Бюджет компании
позволяет выделить 1 млн. рублей на покупку.
Вычислите, какую скидку должно сделать предприятие,
если стоимость долота составляет 210 тыс. рублей.
Используя Подбор параметра можно упростить процесс получения
требуемого результата:
Нужно выделить ячейку F5, которая содержит формулу вычисления
результата, и выбрать команду «Подбор параметра» меню Сервис.
В поле Значение нужно ввести целевое значение 1000 000, а в поле Изменяя
значение ячейки ссылку на ячейку F4 и нажать ОК.
Примечание: При использовании функции подбора
параметра необходимо, чтобы ячейка с целевым значением
содержала ссылку на ячейку с изменяемым значением.
9. Если поиск нужного значения продолжается слишком долго, прервать его на время можно с помощью кнопки “Пауза”. Кнопка “Шаг”
После выполнения всех установок нужно нажать кнопку ОК, и поиск нужного значения будет
начат. Результат вычисления отобразится в диалоговом окне “Результат подбора параметра”, а
также в исходной таблице. После нажатия кнопки 0К полученные значения будут вставлены в
таблицу.
Если поиск нужного значения продолжается слишком долго, прервать его на время можно с помощью
кнопки “Пауза”. Кнопка “Шаг” позволяет просмотреть промежуточные результаты вычисления.
Пример №2
В 2014 г. ОАО АНК "Башнефть" опубликовало результаты добычи
нефти в регионах.
Добыча нефти в регионах (млн. т.) :
Республика Башкортостан - 15,3
Ханты-Мансийский АО
- 13,2
Ненецкий АО
- 12,1
Оренбургская область
- 14,5
рассчитайте значение добычи нефти в Ненецком АО при увеличении
среднего показателя добычи нефти на 3,25 млн. т.
1) На основании данных добычи нефти в регионах
нужно построить с помощью мастера диаграмм
гистограмму.
2) В интерактивном режиме с использованием мыши нужно настроить
высоту полосы «Среднее», и посмотреть, как Ехсе1 изменит высоту
полосы «Ненецкий АО» для получения целевого значения. Далее
необходимо дважды щелкнуть на последней полосе (один раз - для
выбора ряда, а второй — для выбора полосы из ряда), которая
представляет средние значения, и увеличить ее высоту путем
перетаскивания маркеров размеров.
3) Когда кнопка мыши будет отпущена, откроется диалоговое окно “Подбор параметра. В поле “Установить в
ячейке” появится имя ячейки В8, а в поле “Значение” — число, соответствующее последнему значению, которое
отображалось в поле подсказки(число 17). Курсор ввода будет находиться в поле “Изменяя значение ячейки”,
поэтому остается лишь ввести в данное поле значение В5. Нужно щелкнуть на кнопке ОК, после чего появится
диалоговое окно “Результат подбора параметра”, в котором будет содержаться нужная информация. Щелкнуть на
кнопке ОК для возврата на рабочий лист. В ячейках уже содержатся новые значения и что в соответствии с ними
настроена высота полос гистограммы.
Презентация на тему: " Тема: Подбор параметра выполняется с помощью команды меню ДАННЫЕ/АНАЛИЗ «ЧТО-ЕСЛИ»/ ПОДБОР ПАРАМЕТРА Функция Подбор параметра позволяет получить требуемое." — Транскрипт:
4 Подбор параметра выполняется с помощью команды меню ДАННЫЕ/АНАЛИЗ «ЧТО-ЕСЛИ»/ ПОДБОР ПАРАМЕТРА Функция Подбор параметра позволяет получить требуемое значение в определенной ячейке, которую называют целевой, путем изменения значения (параметра) другой ячейки, которую называют влияющей. При этом целевая ячейка должна прямо или косвенно ссылаться на ячейку с изменяемым значением.
5 Пример: пусть надо решить уравнение вида:
6 Формулировка задачи на листе электронной таблицы:
7 Для получения решения данного уравнения нужно использовать функцию Подбор параметра и выполнить следующую последовательность действий: 1) выполнить команду Данные/Анализ «что-если»/Подбор параметра ; 2) заполнить диалоговое окно Подбор параметра.
10 После нажатия на кнопке ОК появится окно Результат Подбора Параметра, в котором дается информация о том, найдено ли решение, чему равно и какова точность полученного решения.
12 Матричная алгебра тесно связана с линейными функциями и с линейными ограничениями, в связи, с чем находит себе применение в различных экономических задачах Особое отношение к матричной алгебре в экономике появилось после создания моделей типа «Затраты - Выпуск», где с помощью матриц технологических коэффициентов объясняется уровень производства в каждой отрасли через связь с соответствующими уровнями во всех прочих отраслях. ТРАНСП – транспонирование исходной матрицы; МОПРЕД – вычисление определителя квадратной матрицы; МОБР – вычисление матрицы обратной к данной; МУМНОЖ – нахождение матрицы, являющейся произведением двух матриц. Электронная таблица EXCEL имеет ряд встроенных функций для работы с матрицами:
13 Пример: найти матричное выражение: Y=(FH -1 )/ 29 +K
14 Найдем матрицу обратную к матрице Н
15 Умножим матрицы F и H -1
16 Результат умножим на число (1/29)
17 Сложим полученную матрицу с матрицей К
18 Вычислим определитель матрицы : 1.Определим исходную матрицу. 2.Определим место под результат. 3.Обратимся к мастеру функций, найдем функцию МОПРЕД, выполним постановку задачи.
19 Системы линейных алгебраических уравнений -12X 1 +12X 2 +23X 3 +6X 4 =120 -3X X 2 -3X 3 +X 4 = X 1 -3X 2 -51X 3 -73X 4 = X 1 -6X 2 +4X 3 -13X 4 =-316 Пример: рассчитать определитель системы, пользуясь функцией МОПРЕД.
20 матричное решение уравнения выглядит так: Х=А -1 В, где А -1 – матрица обратная к исходной матрице А.
22 Средство подбора параметров поддерживает только одно входное значение переменной. Если необходимо определить несколько входных значений, например размер кредита и сумму ежемесячного платежа, следует использовать вместо этого надстройку «Поиск решения» Программа Поиск решения позволяет получить результат на основе изменения значений нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия - ввести ограничения. При поиске решения, также как и при подборе параметра, целевая ячейка должна содержать формулу и быть прямо или косвенно связанной с ячейками с изменяемыми значениями.
23 Многие проблемы прогнозирования, проектирования и производства сводятся к широкому классу задач оптимизации. Решение задач такого вида может быть осуществлено EXCEL с помощью инструмента Поиск решения.
24 Пример: предположим, что мы решили производить два вида объективов А и В. Объектив вида А состоит из 3-х линзовых компонентов, вид В – из 4-х. За неделю можно изготовить не более 1800 линз. На сборку объектива вида А требуется – 15 минут, вида В – 30 минут. Рабочая неделя для 4 сотрудников составляет 160 часов. Сколько объективов А и В надо изготовить, чтобы получить максимальную прибыль, если объектив вида А стоит 3500 рублей, вида В – 4800 рублей.
25 Для решения этой задачи необходимо составить и заполнить таблицу:
26 Пример заполнения окна Поиск решения
27 Вид таблицы результатов решения с применением команды Поиск решения
28 Рассмотрим задачу линейного программирования на примере. Пример: решить задачу линейного программирования с помощью Поиска решения, показать графически область допустимых решений и целевую функцию. Найдем максимум функции F = -2x 1 + 2x 2max при ограничениях: x 1 + x x 1 + x 2 0,3 x 1 – x 2 1 x 1 + x 2 6 x 1 0 x 2 0.
29 Сформируем страницу электронной таблицы и постановку задачи линейного программирования в диалоговом окне Поиск решения.
30 После выполнения поставленной задачи получаем следующие значения переменных : Графическое решение поставленной задачи выглядит так :
31 Мы вступаем в этап, когда стоящие перед нами проблемы невозможно решить без применения компьютера. Использование компьютерных технологий освобождает от рутинной вычислительной работы по реализации математических методов и позволяет сконцентрировать внимание не на алгоритме вычисления, а непосредственно на анализе результатов моделирования, что заметно повышает «коэффициент полезного действия» затраченного времени.
Презентация на тему: " РЕШЕНИЕ УРАВНЕНИЙ В СРЕДЕ MS EXCEL. Решение линейных уравнений уравнений с помощью средства «Подбор параметра» Пример 1 Найти все корни уравнения 3cos2x-sinx." — Транскрипт:
1 РЕШЕНИЕ УРАВНЕНИЙ В СРЕДЕ MS EXCEL
2 Решение линейных уравнений уравнений с помощью средства «Подбор параметра» Пример 1 Найти все корни уравнения 3cos2x-sinx = 0 при x [0;3]
3 Шаг 1 Табулируем функцию 3cos2x-sinx = 0 с шагом 0,3 на отрезке [0;3] . При решении уравнений с помощью средства Подбор параметра значения переменной должны быть заданы числом
4 Из таблицы значений видно, что функция на [0;3] меняет знак два раза: при х [0,6;0,9] и х [2,4;2,7], на этих отрезках есть точки пересечения функции с осью Х
5 Найдем корни полинома методом последовательных приближений с помощью средства поиск решения: Сервис > Подбор параметра
6 Скопируйте формулу из ячейки В2 в F2 (теперь формула ссылается на пустую ячейку Е2, поэтому в F2 отражается 0) Установите в ячейку Е2 значение переменной из [0,6;0,9], например х=0,7
7 Зададим относительную погрешность вычислений 0,00001 и предельное число итераций 1000 Сервис > Параметры > Вычисления
9 В качестве начальных значений приближений к корням можно взять любые точки из отрезков локализации корней, например 0,7 и 2,5
10 Установите курсорную рамку в ячейку F2 и выполните Сервис, Подбор параметра Аналогично найдите второй корень уравнения
11 РЕШЕНИЕ СИСТЕМЫ НЕЛИНЕЙНЫХ УРАВНЕНИЙ В СРЕДЕ MS EXCEL С ИСПОЛЬЗОВАНИЕМ СРЕДСТВА «ПОИСК РЕШЕНИЯ»
12 Пара (х;у) является решением системы уравнений тогда и только тогда, когда она является решением следующего уравнения с двумя неизвестными: (х 2 +у 2 -3) 2 +(2х+3у-1) 2 =0
13 Решением системы - точки пересечения окружности r=3 и прямой уравнение имеет не более двух различных решений Определяемое значение нелинейной задачи зависит от начального приближения
14 Для локализации корней протабулируем левую часть уравнения (х 2 +у 2 -3) 2 + (2х+3у-1) 2 = 0 по переменным х и у на [-3;3] шагом 1,5
15 Протабулируем функцию с помощью таблицы подстановки F(x;y)=(х 2 +у 2 -3) 2 +(2х+3у-1) 2
16 Из таблицы видно, что начальное приближение к корню следует выбрать следующие пары значений (-1,5;1,5), (1,5;0) и (1,5;1,5)
17 Для нахождения корней уравнения введем соответствующие пары значений (х; у) для первого корня в ячейки в А10, А11 для второго корня в ячейки в А14,А15 для третьего корня в ячейки в А17,А18 F(x;y) соответственно в ячейки В13, В16, В19
18 Найдем первый корень. 1.Установить курсорную рамку в ячейке В15 2.Выполнить Сервис > Поиск Решения
21 После нажатия кнопки Выполнить средство Поиск решения находит решение, которое помещает в ячейки А11, А12 Аналогично находим второй и третий корни. Решением уравнения будут две пары значений (-1,269;1,179) (1,576;-0,717)
22 РЕШЕНИЕ МАТРИЧНЫХ УРАВНЕНИЙ
23 Простейшие операции над массивами МАССИВ - объект Excel, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам.
24 Два типа массивов Microsoft Excel : диапазон массива - непрерывный диапазон ячеек, использующих общую формулу; диапазон констант - набор констант, используемых в качестве аргументов функций.
25 диапазон констант - набор констант, используемых в качестве аргументов функций диапазон массива - непрерывный диапазон ячеек, использующих общую формулу;
27 Для умножения (деления) массива на число: 1.Выделить диапазон ячеек того же размера 2.Ввести в первую ячейку диапазона формулу =Е1:G3*100 и нажать комбинацию клавиш SHIFT+ CTRL+ENTER Если в формуле используется ссылка на ячейку в которой хранится число, то ссылка на эту ячейку должна быть абсолютной
28 Формула массива обрабатывает несколько наборов значений (аргументов массива). Каждый аргумент массива должен включать одинаковое число строк и столбцов. Формула массива создается так же, как и другие формулы, только что для ввода такой формулы используются комбинация клавиш SHIFT+ CTRL+ENTER
29 Пример Перемножение массивов: 1.Выделить область такого же размера как перемножаемые массивы 2.Ввести в первую ячейку формулу =А1:С3*А1:С7 3.Для ввода массива нажать комбинацию SHIFT+ CTRL+ENTER Сложение, вычитание, деление, вычисление каждого элемента как результата некоторой функции производится аналогично. При вводе формулы массива Microsoft Excel автоматически заключает ее в фигурные скобки
30 Формула массива может выполнить несколько вычислений, а затем вернуть одно значение или группу значений. Пример Рассчитать суммарный балл оценки экспертом качества услуги по формуле: Si - суммарный балл Wi – вес критерия Ci – оценка критерия экспертом N – количество критериев
31 Способ решения 1 1.Введите в ячейку D2 формулу =В2*С2 и скопируйте ее в ячейки диапазона D3:D7 2.Введите в ячейку D8 формулу = СУММ(D2:D7) 3.В ячейке D9 вычислите значение S = D86
33 Функцию можно ввести в ячейку с клавиатуры или с помощью средства Мастер функций Каждая функция выводится в стандартном окне диалога Для ввода аргумента достаточно указать в соответствующих полях числовые значения аргументов, адреса ячеек или адреса диапазонов ячеек
35 Окно диалога функции Суммпроизв() Результат вычисления формулы - число
36 Функции для работы с массивами МУМНОЖ(массив1;массив2) - перемножает массивы. Массивы (матрицы) должны быть одной размерности и оба массива должны содержать только числа.
37 МОБР(массив)- возвращает обратную матрицу для матрицы, хранящейся в массиве
38 ТРАНСП(массив) - используется для того, чтобы поменять ориентацию массива на рабочем листе с вертикальной на горизонтальную и наоборот.
39 МОПРЕД(массив) - возвращает определитель матрицы (матрица хранится в массиве). Определитель матрицы - это число, вычисляемое на основе значений элементов массива. Для массива A1:C3, состоящего из трех сток и тех столбцов, определитель вычисляется следующим образом: = A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)
40 СУММСУММКВ(массив_x;массив_y) - возвращает сумму сумм квадратов соответствующих элементов двух массивов. Сумма сумм квадратов - это распространенный термин во многих статистических вычислениях. Массив_x - это первый массив или интервал значений. Массив_y - это второй массив или интервал значений.
41 СРГЕОМ(число1;число2;. ) - возвращает среднее геометрическое значений массива или интервала положительных чисел. Например, функцию СРГЕОМ можно использовать для вычисления средних темпов роста, если задан составной доход с переменными ставками. СУММКВРАЗН(массив_x;массив_y) - возвращает сумму квадратов разностей с оответствующих значений в двух массивах. Массив_x - это первый массив или интервал значений. Массив_y - это второй массив или интервал значений.
42 ЧИСЛСТОЛБ(массив) - возвращает количество столбцов в ссылке или массиве: =ЧИСЛСТОЛБ(A1:D9) в ячейке отображается число 4 ЧСТРОК(массив) - возвращает количество строк в ссылке или массиве. = ЧСТРОК (A1:D9) в ячейке отображается число 9 Статистические функции, который используются для прогнозирования Тенденция(), Рост(), Предсказ(), Линейн() также используют правило ввода значений массива
43 Решение матричных уравнений в EXCEL Найти решение уравнения А*Х=В А-матрица коэффициентов В- столбец (вектор) свободных членов Х-столбец (вектор)неизвестных Решение линейной системы имеет вид: Х=А -1 *В А -1 – обратная матрица
44 Шаг 1. Вычислим А -1 с помощью функции =МОБР(массив) Шаг 2. Выделить диапазон К2:К4 для элементов массива вектора Х и ввести формулу =МУМНОЖ(E2:G4;I2:I4) Для вставки массива нажать комбинацию клавиш SHIFT+ CTRL+ENTER
45 Шаг 3. Проверка. Умножим матрицу А на найденный вектор Х В результате мы должны получить вектор В Выделим диапазон М2:М4 и введем функцию = МУМНОЖ(А2:С4;К2:К4) Для вставки массива нажать комбинацию клавиш SHIFT+ CTRL+ENTER
46 Самостоятельно решить системы линейных уравнений А 2 *Х=В и А 3 *Х=В
47 Решить уравнение Z=Х т A X А-матрица, Х-вектор, Х T - транспонированный вектор Шаг1. Найти транспонированный вектор Х T Выделать диапазон G2:I2 и ввести формул =ТРАНСП(E2:E4) для ввода массива значений нажать SHIFT+ CTRL+ENTER
48 Шаг2. Умножить полученную строку Х T на матрицу Авыделить диапазон К2:М2 и ввести формулу =МУМНОЖ(G2:I2;A2:C4) Шаг 3. В отдельную ячейку введите формулу =МУМНОЖ(K2:M2;E2:E4) – результат вычисления число 227, но для ввода нажать SHIFT+ CTRL+ENTER
49 Это же решение можно получить путем ввода в ячейку одной формулы, содержащей вложенные функции: =МУМНОЖ(МУМНОЖ(ТРАНСП(E2:E4);A2:C4);E2:E4) Самостоятельно решить уравнения: 1. Z=Y т A т AY 2. Z=Y т A т A 2 Y
50 Решение системы линейных уравнений методом Гаусса
51 1. Ввести матрицу коэффициентов в ячейки рабочего листа MS Excel 2. Скопировать первую строчку (диапазон А1:Е6) в диапазоны А6:Е6 А11:Е11 А16:Е16
53 3. Выделить диапазон А7:Е7 и введите формулу, которая обращает в 0 коэффициент при х 1 во втором уравнении системы: =A2:E2-$A$1:$E$1*(A2/$A$1) Для вставки элементов массива нажать SHIFT+ CTRL+ENTER Выделить диапазон А7:Е7 и протащить маркер автозаполнения этого диапазона, чтобы заполнить диапазоны А7:Е7 в диапазон А8:Е8 и А9:Е9. Это обратит в 0 коэффициенты при х 1 в третьем и четвертом уравнениях системы.
54 4. Выделить диапазон А7:Е7 и скопируйте значения в буфер Выделите диапазон А12:Е12 и выполните вставку значений без формул используйте команду Правка, специальная вставка Аналогично вставьте значения в диапазон А17:Е17
56 5. Выделите диапазон А19:Е19 и введите формулу массива, которая обращает в 0 коэффициент при х 3 =A14:E14-$A$13:$E$13*(C14/$C$13) Для вставки элементов массива нажать SHIFT+ СTRL+ENTER Прямая прогонка метода Гаусса завершена
Читайте также: