Метод Крамера в Excel

Обновлено: 20.11.2024

Чтобы использовать эту функцию, загрузите файл Excel, нажав ЗДЕСЬ. Открыв файл и убедившись, что макросы включены, вы можете использовать сам этот лист или скопировать функцию на свой собственный лист, перейдя в «Разработчик» и открыв Visual Basic:

Если в вашей электронной таблице включены макросы, вы можете использовать =Cramv(field1,field2) так же, как и =Correl(field1, field2).

Пример 1 демонстрирует слабость использования корреляции для оценки взаимосвязи двух категориальных переменных. В этом случае трехуровневая переменная полностью связана с двухуровневой переменной таким образом, что всякий раз, когда трехуровневая переменная равна 1 или 3, двухуровневая переменная равна 0, в противном случае двухуровневая переменная равна 1. Если мы исследуем эту связь, используя корреляции , мы не найдем корреляции, которая предполагала бы отсутствие связи. Идеальные отношения запечатлены в V-матрице Крамера.

Второй пример демонстрирует различные эквивалентности между V Крамера и корреляцией, когда переменные являются двухуровневыми. Var1 и Var2 коррелируют на уровне 0,1. Поскольку они фиктивно закодированы как «0» и «1», мы ожидаем, что такая же связь будет в V Крамера. Var3 полностью, но отрицательно коррелирует с Var2, производя корреляцию -1. Та же сила зафиксирована в V Крамера, за исключением того, что порядок категориальной переменной не имеет значения, отрицательное значение теряется. Наконец, Var4 и Var3 коррелируют на уровне -0,3. Отношение Крамера V имеет ту же силу, но теряет отрицательное значение.

Если вы не доверяете открытию моих макросов, код функции:

Функция Cramv(field1 As Range, field2 As Range)

Dim arr1() как вариант "Первый массив"

Dim arr2() как вариант "Второй массив"

Затемнение lCtr As Long, lCount As Long индексов

Затемнить iCtr как целочисленный индекс

Dim col1 As New Collection 'Коллекция для сбора уникальных значений array1

Dim col2 As New Collection 'Коллекция для сбора уникальных значений массива1

Тусклый наблюдаемый() как вариант

Dim Rowtotal() как вариант

Dim Columntotal() как вариант

Затемнить nvans1 как долго

Затемнить nvans2 как долго

Тусклый ожидаемый() как вариант

Размер Xsq как двойной

'Поиск длины массивов

Затемнение lStartPoint до максимального значения

Затемнить lEndPoint до максимального значения

ReDim arr1(1 to lEndPoint)

ReDim arr2(1 to lEndPoint)

'Тестирование для обеспечения одинаковых диапазонов размеров

Если lEndPoint <> изменить точку, то

MsgBox "Убедитесь, что в обоих диапазонах одинаковое количество ячеек"

'Найти уникальные значения для первого массива

Для lCtr = lStartPoint to lEndPoint

vItem = arr1(lCtr, 1)

'первый элемент, добавить автоматически

Если lCtr = lStartPoint, то

col1.Добавить vItem, sIndex

ReDim vAns1(lStartPoint to lStartPoint) как вариант

При ошибке возобновить дальше

col1.Добавить vItem, sIndex

Если Err.Number = 0, то

lCount = UBound(vAns1) + 1

ReDim Preserve vAns1(lStartPoint To lCount)

'Найти уникальные значения для второго массива

Для lCtr = mStartPoint до точки исправления

vItem = arr2(lCtr, 1)

'первый элемент, добавить автоматически

Если lCtr = mStartPoint, то

col2.Добавить vItem, sIndex

ReDim vAns2(mStartPoint to mStartPoint) как вариант

При ошибке возобновить дальше

col2.Добавить vItem, sIndex

Если Err.Number = 0, то

lCount = UBound(vAns2) + 1

ReDim Preserve vAns2 (mStartPoint To lCount)

'определение длины vAns

'поиск переполненных ячеек для chisq

ReDim наблюдается (1 для nvans2, 1 для nvans1) как вариант

Ожидается ReDim (1 для nvans2, 1 для nvans1) как вариант

Для lCount = 1 Для nvans1

Для iCtr = 1 Для nvans2

наблюдается(lCount, iCtr) = 0

Для lCtr = 1 В точку исправления

For lCount = 1 To nvans2 'rows

Для iCtr = 1 To nvans1 'столбцы

Если arr1(lCtr, 1) = vAns1(iCtr), то

Если arr2(lCtr, 1) = vAns2(lCount), то

наблюдается(lCount, iCtr) = наблюдается(lCount, iCtr) + 1

'поиск сумм строк и столбцов

ReDim Rowtotal(nvans1) как вариант

ReDim Columntotal(nvans2) как вариант

Для lCtr = 1 To nvans1

Для lCtr = 1 To nvans2

Для lCtr = 1 To nvans1

Для lCount = 1 Для nvans2

Итог по строке(lCtr) = Итого по строке(lCtr) + наблюдаемое(lCount, lCtr)

Итог по строке(lCtr) = Итого по строке(lCtr) / точка исправления

Для lCtr = 1 To nvans2

Для lCount = 1 Для nvans1

Всего по столбцу(lCtr) = Итого по столбцу(lCtr) + наблюдаемое(lCtr, lCount)

'поиск ожидаемых значений и Chisq

Для lCtr = 1 To nvans2

Для lCount = 1 Для nvans1

Ожидаемое(lCtr, lCount) = Итого по столбцу(lCtr) * Итого по строке(lCount)

Xsq = Xsq + (((наблюдается(lCtr, lCount) - ожидается(lCtr, lCount)) ^ 2)/ожидается(lCtr, lCount))

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

1. Математические приложения

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

1.1 Работа с матрицами

Вы можете использовать Excel для работы с матрицами и решения систем уравнений. Уравнение представляет собой ряд чисел и математических переменных, которые представляют собой кривую на графике (см. рис. A-8).

Рисунок A-8. Линия, соответствующая уравнению y=3x+2.

Система уравнений – это группа уравнений, представляющая две или более линии на графике. Следующие уравнения представляют систему:
3x₁ - x₂ = 5

Чтобы решить эту систему уравнений, необходимо найти значения x₁ и x₂, удовлетворяющие обоим уравнениям. Чтобы решить систему уравнений, должно быть столько же или меньше переменных, сколько есть уравнений. Например, вы не можете решить систему уравнений, содержащую три уравнения с четырьмя переменными в каждом. Вы можете решить систему с тремя уравнениями с тремя переменными или систему с тремя уравнениями с двумя переменными. Обратите внимание, что числа перед буквами называются «коэффициентами», а числа в правой части уравнения без букв называются «константами».

Чтобы решить эту систему, вам нужен метод выделения каждого из двух видов. Это можно сделать с помощью определителя. Определитель — это фундаментальное понятие линейной алгебры, которое можно упростить с помощью Excel. Каждая квадратная матрица системы уравнений имеет свой уникальный определитель. Единственным условием является то, что матрица должна быть квадратной, то есть должно быть равное количество строк в качестве столбцов. Чтобы найти определитель матрицы, нужно следовать заранее определенной формуле умножения и сложения, поэтому Excel может упростить задачу. Дополнительные сведения о матричных функциях Excel см. в разделе Глава 7 Приложения. На самом деле, чтобы решить систему, вы должны использовать определитель вместе с другим алгебраическим понятием, называемым правилом Крамера. Первым шагом в использовании правила Крамерса является вычисление определителя матрицы коэффициентов. Затем постоянный вектор подставляется в матрицу для первого столбца и снова вычисляется определитель. Затем постоянный вектор подставляется во второй столбец, третий столбец и т. д., при этом каждый раз вычисляется определитель. Наконец, путем деления некоторых определителей можно определить решения переменных. Ниже приведен теоретический пример поиска решения системы 2x2, показывающий задействованную математику. Рассмотрим пример выше.
В матричной форме эта система выглядит следующим образом:

Коэффициенты уравнения вставляются в матрицу 2X2 (т.е. 2 столбца и 2 строки). Константы выражаются как вектор-столбец. Вектор отличается от матрицы тем, что имеет только один столбец или одну строку.

Чтобы найти определитель матрицы 2 x 2, вычтите произведение двух диагоналей в матрице коэффициентов:

Вы можете расширить это, используя правило Крамерса для решения системы. Чтобы найти переменную x₁, подставьте константный вектор в первый столбец матрицы и найдите определитель. Затем разделите результат на определитель матрицы коэффициентов:

x₁ = 56/28 = 2

Чтобы найти переменную x₂, подставьте постоянный вектор во второй столбец и повторите процесс:

x₂ = 28/28 = 1

Теперь вы решили систему, вектор решения равен (x₁, x₂) = (2, 1). Теперь вы можете использовать эту теорию для создания шаблона электронной таблицы в Excel, который решит за вас систему уравнений. Шаблон представляет собой настройку электронной таблицы, которую вы сохраняете, чтобы использовать ее снова и снова для выполнения тривиальной задачи, такой как утомительный расчет. Сначала в своем шаблоне вы настроите систему и решение 2 x 2.

Первый шаг к поиску решения выполнен. Второй шаг заключается в подстановке постоянного вектора в матрицу коэффициентов.

< /таблица>

Последний шаг — найти определитель ячеек A9:B10, разделить результат на определитель матрицы коэффициентов и поместить ответ в ячейку D2. Все это делается по одной формуле.

В ячейке A8 введите Матрица коэффициентов X1

затем введите =$C$2 в A9, < tt>=$C$3 в A10, =$B$2 в B9 и =$B$3 в B10.

< /таблица>

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

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

Известно, что спектры смесей соединений называются аддитивными (используя закон Бера - см. первую лабораторию Excel). То есть общее поглощение раствора при любой заданной длине волны представляет собой сумму поглощений отдельных соединений при этой длине волны.

Теперь вы примените эти концепции к проблеме. Смесь соединений дает спектры, в которых пики хорошо разрешены. Вид X имеет максимум поглощения A₁ на длине волны 1, а вид Y имеет максимум поглощения A₂ на длине волны 2. Используя закон Берса, вы можете получить общая система уравнений для A₁ и A₂:

Используя вариант правила Крамерса, вы можете решить два уравнения для [X] и [Y].

Примечание: «прямые скобки», окружающие матрицы, обозначают операцию определителя.

Пример 1. Используйте шаблон электронной таблицы для решения следующей задачи.

Молярная абсорбционная способность соединений X и Y в следующей таблице была измерена для чистых образцов каждого из них. Смесь соединений X и Y в кювете с длиной оптического пути 1000 см имела поглощение 0,957 при 272 нм и 0,559 при 327 нм. Молярная абсорбционная способность X при 272 и 327 нм составляет 16400 и 3990 соответственно. Молярная абсорбционная способность Y при 272 и 327 нм составляет 3870 и 6420 соответственно. Найдите концентрации X и Y в смеси. Подсказка: коэффициенты поглощения образуют постоянный вектор, а молярные коэффициенты поглощения — матрицу коэффициентов, поскольку b=1. Проверьте свои ответы с помощью ТА.

Создайте шаблон для неизвестного ряда из 3 уравнений 3 (т. е. для матрицы 3X3).

Вы должны получить ответы 4, -3 и 7.

Пример 3. Используйте шаблон 3X3 для решения следующих задач

Из приведенных ниже спектроскопических данных найдите [X], [Y] и [Z]. Раствор анализировали в кювете на 1000 см и имели абсорбцию 0,846 при 246 нм, 0,400 при 298 нм и 0,555 при 360 нм. Молярные коэффициенты поглощения для каждого компонента раствора при каждой длине волны приведены в Таблице A1-7.

Таблица A1-7 – Данные по молярной абсорбции

1.2 Подбор нелинейной кривой

Из-за постоянно развивающейся компьютерной и приборной техники ученые отмечают общее увеличение размера и сложности данных. Это означало увеличение желания манипулировать данными с помощью широко используемого программного обеспечения для микрокомпьютеров. Первоначально такие манипуляции с данными выполнялись только на больших мейнфреймах. В настоящее время микрокомпьютеры представляют собой идеальный инструмент для комплексного статистического анализа. Таким образом, введение Excel Solver. Решатель предлагает пользователю возможность анализировать нелинейные данные, такие как экспоненциальные и гауссовы кривые.

Чтобы использовать Solver, вы должны знать тип данных, которые у вас есть, т. е. какая кривая лучше всего представляет данные на графике. Если вы знаете, какие данные у вас есть, вы можете найти математическое уравнение (кривую), используемое для моделирования этих данных. Решатель можно использовать для воссоздания кривой и набора данных путем замены констант в уравнении «оценочными предположениями». Решатель может сравнивать набор данных, полученный экспериментально, с набором данных, рассчитанным теоретически. После того, как сравнение будет завершено, Solver найдет более точные значения, чем «оценочные предположения», которые вы изначально придумали. С научной точки зрения, условия эксперимента могут быть изменены, а эксперимент переделан и снова проанализирован с помощью Solver. Значения, полученные для констант для каждого запуска, затем можно сравнить, чтобы найти изменение.

Поиск решения использует метод итерации Ньютона, чтобы определить "наилучшую комбинацию" переменных, которые соответствуют вашему уравнению. Термин «лучшая комбинация» станет для вас более очевидным после того, как вы закончите упражнение.Вы будете использовать Solver для анализа сигнала экспоненциального затухания, полученного спектрофотометром uv/vis. Набор данных довольно большой, поэтому мы договорились, что вы перенесете его на диск из Интернета. Файл называется SOLVER.XLS, и его можно найти в папке файлов на веб-сайте Chem 210.

f(t) представляет процент непрореагировавших всех видов.

f₁. f₂ представляет процент прореагировавших видов 1 и 2.

k_1, k₂ представляют собой константы скорости, связанные с реагирующими частицами.

f₁, f₂, k₁, k₂ — все константы, которые будут определяться Решатель.

B — базовая коррекция.

Чтобы увидеть, как выглядят данные, сначала нанесите их на диаграмму с помощью мастера диаграмм. Вставьте диаграмму на рабочий лист и нанесите данные линией без маркеров. Затем вернитесь к электронной таблице и введите заголовки Данные , Остатки , Квадрат , Переменные в C1:F1. и f1= , f2= , k1= , k2= , B= в F2:F6 и SSR= в F8 в вашей электронной таблице.

В ячейке D2 введите =(MDETERM(A9:B10))/A6 .

Повторите это для x 2, введя матрицу коэффициентов X2 в ячейку A11, а затем введя соответствующие формулы, чтобы заменить второй столбец матрицы коэффициентов постоянным вектором.

Наконец введите формулу =(MDETERM(A12:B13))/A6 в ячейку D3.

В ячейки G2:G6 введите значения 84 , 16 , 0,305 , 0,013 и 0 . Это «оценочные предположения» для констант, которые будут включены в уравнение для создания набора данных модели.

Into в ячейке C2 введите формулу =($G$2*EXP(-$G$4*A2))+($G$3*EXP(-$G$5*A2))+$G$6 . Это версия уравнения 1 в формате Excel. Скопируйте это уравнение для всего набора данных. Нанесите C2:C720 на график, снова используя линию без маркеров.

В ячейку D2 введите формулу =C2-B2 . Это вычисляет остаток между двумя наборами данных. Остаток — это просто разница между двумя наборами данных. Снова скопируйте эту формулу для всего набора данных.

В ячейку E2 введите формулу =D2^2< /тт> . Это квадрат остатка. При этом учитываются любые отрицательные остаточные значения, возводя их в квадрат и делая их положительными. Скопируйте квадрат остатка для всего набора данных.

Наконец введите формулу =SUM(E2: E720) в ячейку G8. Это сумма квадратов остатков. Теперь ваша электронная таблица должна выглядеть так, как показано в таблице A1-10.

Целевой ячейкой для этой итерации является сумма квадратов остатков, $G$8. Вы заставите Солвер минимизировать это значение,

нажмите на Min в разделе «Равно:».

Решатель минимизирует сумму квадратов остатков, изменив записи в ячейках G2:G6.

введите $G $2:$G$6 в поле Путем изменения ячеек:.

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

Нажмите на Варианты. и измените Максимальное Времявремя: на 200 секунд.

Вы видите, что в этом диалоговом окне вы можете изменить ограничения итераций для Решателя.

Вспомните расчеты, которые вы сделали, чтобы найти сумму квадратов остатков в электронной таблице. Во-первых, вы вычислили набор данных модели, используя предполагаемые значения констант. Затем вы построили эти данные. Вы могли видеть, что две линии очень хорошо совпадают, они очень похожи в некоторых местах. Затем вы взяли разницу между двумя наборами данных и поместили ее в столбец «Остатки». Если вы подумаете об этом графически, остаток — это просто расстояние на графике между двумя линиями. Затем вы возвели остаточные значения в квадрат, чтобы сделать их положительными, и, наконец, вы суммировали все остаточные значения вместе и поместили ответ в одно место. Если вы посмотрите на экран, пока Solver выполняет свои вычисления, он минимизирует сумму квадратов остатков. Следовательно, на самом деле это минимизирует расстояние между двумя линиями, линией, полученной из фактических данных, и теоретической линией. Это достигается путем корректировки констант, которые вы вводите в качестве оценочных предположений, чтобы получить меньшее квадратичное остаточное значение. Это нелинейная аппроксимация кривой.

в k неизвестных x_j можно рассматривать как матричное уравнение AX = C, где A — матрица n × k [a_ij], X — это вектор-столбец k × 1 [x_j], а C — это n< /em> × 1 вектор-столбец [c_j].

Свойство 1. Если A — квадратная матрица (т. е. количество уравнений равно количеству неизвестных), уравнение AX = C имеет единственное решение. тогда и только тогда, когда A обратим (т. е. det A ≠ 0), и в этом случае единственное решение определяется выражением X = A-1 C.

Свойство 2 (правило Крамера): если квадратная матрица A обратима, единственное решение AX = C определяется выражением

где A_j — это A с заменой столбца j на записи C .

Пример 1. Решите следующую линейную систему с помощью правила Крамера:

На рисунке 2 мы вычисляем det A и det A_j для каждого j.

Рисунок 1. Вычисление определителя по правилу Крамера

Из этого следует, что x = -6/9 = -2/3, y = 3/9 = 1/3 и z = 0/9 = 0. .

Для свойства 1 мы можем получить тот же результат, вычислив A -1 C, что можно выполнить в Excel по формуле =МУМНОЖ(МИНВЕРС(A ), С). Для примера 1 это дает

Определение 2. Когда C из определения 1 не является нулевой матрицей, линейные уравнения называются гетерогенными. При C = O линейные уравнения называются однородными. В этом случае O является решением AX = O, называемым тривиальным решением.

Свойство 3: если A обратимо, то X = Ο — единственное решение уравнения AX = Ο.

Доказательство: это следует из свойства 1.

Наблюдение: когда A необратим (т. е. det A = 0), любой скаляр, кратный нетривиальному решению однородного уравнения AX = Ο также является решением. Чтобы найти такое решение, мы можем использовать метод исключения Гаусса, метод, аналогичный тому, который мы использовали для вычисления определителя квадратной матрицы на основе свойства 5 определителей и линейных уравнений. Этот подход работает для любого A (квадратного или нет, обратимого или нет).

Определение 3. Если A — это матрица n × k, а B — это n × m, то расширенная матрица A|B представляет собой n × ( k+m) матрица, первые k столбцы которой идентичны столбцам в A, а оставшиеся m идентичны столбцам в B.

Свойство 4. Если A′ и C′ получены из A и C на основе любого следующих преобразований, то уравнения AX = C и A′X = C′ имеют одинаковые решения.

1. Поменять местами любые две строки
2. Умножение любой строки на константу
3. Добавление любой строки, умноженной на константу, к другой строке

Наблюдение: обычно мы применяем приведенные выше преобразования к расширенной матрице A|C.

Определение 4. Метод исключения Гаусса — это способ решения линейных уравнений, основанный на преобразованиях, описанных в свойстве 9. Предположим, что A и C такие, как описано в Определение 1.

Шаг 0 — установите i = 1 и j = 1

.

Теперь мы применяем следующую серию преобразований к расширенной матрице (шаг 1–шаг p, где p — меньшее из n и к):

Шаг i – часть 1. Найдите r ≥ i, чтобы абсолютное значение a_rj самый большой. Если a_rj ≈ 0 (т.е. |a_rj| k, то поменять местами строки r и i (правило 1).

Шаг i – часть 2. Разделите все записи в строке i на a_ij (правило 2 ).

Шаг i – часть 3. Для каждой строки r ниже строки i добавьте –a_rj умножает строку i на строку r (правило 3). Это гарантирует, что a_rc = 0 для всех r > i и c ≤ j.

Наблюдение: для неоднородных уравнений (т. е. C ≠ O) есть три возможности: существует бесконечное число решений, нет решений или существует уникальное решение. Для однородных уравнений (т.е.C = O) возможны две возможности: существует бесконечное число решений или существует единственное решение, а именно тривиальное решение, где X = О.

Пример 2. Решите следующую линейную систему с помощью исключения Гаусса:

На рис. 2 показаны этапы процесса исключения Гаусса для примера 2.

Рисунок 2. Решение линейных уравнений методом исключения Гаусса

Поскольку A преобразуется в единичную матрицу, мы знаем, что преобразование C является единственным решением системы линейных уравнений, а именно x = 0, y = 2 и z = -1. Обратите внимание, что мы получаем тот же результат, вычисляя X = A -1 C.

Пример 3. Решите следующую однородную линейную систему с помощью исключения Гаусса:

По методу исключения Гаусса на рисунке 3 мы видим, что единственным решением является тривиальное решение:

Рисунок 3 – Решение однородного линейного уравнения

Пример 4. Решите следующую однородную линейную систему с помощью исключения Гаусса:

На этот раз метод исключения Гаусса создает строку со всеми нулями (см. рис. 4), а количество ненулевых строк = 2

Рисунок 4 – Поиск решений однородных линейных уравнений

Решения могут иметь вид x = -2,5t, y = 0,5t, z = t для любого значения t.

Наблюдение: как видно из приведенных выше примеров, однородное уравнение AX = O, где A – это m × n, имеет единственное решение, когда после выполнения исключения Гаусса остается n ненулевых строк. В противном случае уравнение имеет бесконечное число решений.

Функции Excel с реальной статистикой. Для выполнения процедуры исключения Гаусса предусмотрены следующие функции массива.

ELIM(R1, prec): функция массива, которая выводит результаты исключения Гаусса для расширенной матрицы, найденной в массиве R1. Форма вывода такая же, как форма R1.

По умолчанию каждая из этих функций предполагает, что запись с абсолютным значением меньше 0,0001 эквивалентна нулю. Это необходимо, поскольку малые значения не обрабатываются как нуль в алгоритме исключения Гаусса, описанном выше. Вы можете изменить это значение по умолчанию на другое, вставив второй параметр в любую из этих функций: например. ELIM(R1, prec) или LINEQU(R1, prec). Таким образом, ELIM(R1) = ELIM(R1, 0,0001).

Инструмент анализа данных Real Statistics: инструмент анализа данных «Решить набор линейных уравнений», содержащийся в пакете ресурсов Real Statistics, обеспечивает функции, эквивалентные LINEQU и ELIM. Чтобы использовать этот инструмент, введите Ctrl-m и выберите в меню «Решить набор линейных уравнений». Когда появится диалоговое окно, заполните Входной диапазон (тем же диапазоном, что и R1 выше). Выбор «Показать только решение» эквивалентен LINEQU(R1), а отсутствие щелчка по этому параметру эквивалентно ELIM(R1).

Наблюдение: исключение Гаусса также можно использовать для инвертирования квадратной матрицы n × n A, применяя описанную выше процедуру к А|Я_н. Если процедура завершается до завершения n шагов, то A необратима. Если процедура завершается после n шагов (в этом случае A′ = I_n), то C′ = A -1 .

Пример 5. Использование исключения Гаусса для инвертирования матрицы

Результат показан на рис. 5.

Рисунок 5. Инверсия матрицы методом исключения Гаусса

Тот факт, что A преобразуется в единичную матрицу, указывает на то, что A обратим. Обратное дается преобразованием единичной матрицы, а именно

Это тот же результат, что и при использовании формулы Excel MINVERSE(A).

Наблюдение: Как мы видели в примере 4, иногда существует бесконечное количество решений системы линейных уравнений, каждое из которых выражается как кратное одному решению.Как правило, при наличии нескольких решений каждое решение может быть выражено в виде линейной комбинации векторов-столбцов, как более подробно описано в разделе «Множественные решения линейных уравнений» .

Одновременные уравнения обычно представляют собой кошмар для среднего школьника: он не может или не хочет их решать. Я не был исключением. Однако я помню, как понял, что они из себя представляли и как они работали спустя годы после окончания школы, а потом подумал, какого черта у кого-то с ними проблемы.

Если вы знаете SE, вы знаете, что существует несколько способов их решения, и я не математик, поэтому я не буду объяснять правило Крамера, матричную алгебру или даже подстановку… просто покажу вам, как Excel может помочь.

Что такое одновременные уравнения?

Одновременные уравнения получили свое название из-за того, что в двух, трех или более уравнениях X имеет одинаковое значение для каждого, Y имеет одинаковое значение для каждого и так далее. Например, вот два одновременных уравнения:

В этом случае я знаю, что x = 5 и y = 1, и мы докажем это, поставив 5, где x, и 1, где 1, в любом из двух уравнений, например, в уравнении (1):

5 + 3*1 = 8 … 5 + 3 = 8 … 8 = 8 … КЭД!

Попробуйте сами решить уравнение (2), если хотите.

Методы

Я собираюсь использовать три метода решения одновременных уравнений:

Косвенный метод: использование двух функций массива MINVERSE() и затем MMULT() по отдельности: это, по сути, целая задача или матричное решение, которое дает нам значения каждой переменной

Прямой метод Использование двух функций массива MINVERSE(), а затем MMULT() вместе в форме =MMULT(MINVERSE()) .. это, по сути, решение с одной ячейкой для каждой переменной.

Имена косвенных и прямых являются моими собственными, и я классифицирую их как разные методы, хотя на самом деле это не так!

Решение SOLVER: использование надстройки SOLVER для предоставления значений переменных.

Метод 1: Косвенный метод

Настройте рабочий лист следующим образом:

Это набор одновременных уравнений с двумя переменными, и я оставил столбец C пустым, чтобы мы могли разработать примеры с тремя переменными. Все, что вам нужно сделать, это ввести жесткий код, записи в диапазоне A3:D5 в этом примере.

Диапазон A8:E9 включает решение, использующее сначала функцию массива MINVERSE(), а затем функцию массива MMULT().

Диапазон A12:D13 содержит область Proof, в которой мы проверяем решение, что x = 5 и y = 1.

На следующем снимке экрана показаны мои формулы… опять же, просто скопируйте их так, как вы видите здесь, но помните, что функции массива — это не обычные функции. Итак, для записи функции MIVERSE() вы должны сделать следующее:

Выделите диапазон A8:B9 и оставьте его выделенным, затем введите =MINVERSE(A4:B5), затем нажмите Control и удерживайте его нажатым, нажмите Shift и также удерживайте его, затем нажмите Enter, теперь отпустите все три клавиши I теперь будет называть это СПП!). И у вас должна получиться та обратная матрица, которую вы видите в диапазоне A8:B9… если не работает, сделайте это еще раз внимательно, и если все еще не работает, проверьте свой набор текста. Если это все еще не работает, загрузите мой файл примера!

Для функции массива МУМНОЖ() выполните следующие действия:

Выберите диапазон D8:D9 и оставьте его выбранным, затем введите =MMULT(A8:B9,D4:D5) … CSE … если это не работает … проверьте ввод …

Функция массива MMULT() дает вам ответ, так как я обозначил здесь первое число, 5, является значением x, а второе число, 1, является значением y.

Попробуйте это

Повторяйте вышеприведенное, пытаясь решить эти уравнения для x и y:

Не беспокойтесь обо всех десятичных знаках, которые вы можете увидеть, потому что Excel сделает всю работу за вас; и ваши ответы должны быть x = -1,176470588 и y = 3,470588235

Проведите проверку, чтобы убедиться, что ваши ответы верны… не смотрите на мое решение в моей рабочей тетради Excel, пока не будете довольны своим собственным!

Метод 2: прямой метод

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

Выберите диапазон G8:G9 и оставьте его выбранным, затем введите следующее =MMULT(MINVERSE(A4:B5),D4:D5) … CSE …

Вот и вы, прямо к ответам, x = 5 и y = 1.

Повторите это для уравнений

И докажи свой метод и ответы!

Теперь у вас есть шаблон для решения любого набора одновременных уравнений с двумя переменными, для которого есть решение.

Уравнения с тремя переменными

Многие учащиеся могут решать одновременные уравнения с двумя переменными, но затем запутываются с тремя и более переменными. Если бы я был на вашем месте, и вам нужно изучить и применить правила, такие как правило Крамера, в полном объеме, найдите решения здесь, а затем двигайтесь в обратном направлении, чтобы использовать более формальные методы… это главный совет для вас!

На снимке экрана показан набор одновременных уравнений с тремя переменными:

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

ПРЕЖДЕ ЧЕМ вы посмотрите на мои решения в моей рабочей тетради, попробуйте самостоятельно решить систему уравнений с тремя переменными одновременно.

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

Способ 3: ПОИСК

SOLVER – это мощная надстройка, которую вам, возможно, потребуется установить, прежде чем вы начнете читать дальше

Менеджер… Надстройки Excel… Перейти

Выберите надстройку SOLVER

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

Вернуться к вопросу 1

Настройте область SOLVER, как показано здесь, в диапазоне H3:I4 и J5:j6:

Записи в H3:I4 просто набраны, и вам НЕ нужно ничего вводить в ячейки H4:I4, так как SOLVER запишет что-либо там с решениями проблемы. В качестве альтернативы введите любые цифры, чтобы проверить, работают ли ваши записи в J5:J6:

Математика: переставьте оба уравнения так, чтобы они = 0, вот так:

Х – х + 3у = 8 – х … … … (1)

Х – х – 2у = 3 – х … … … (2)

0 = 8 – x – 3y … … … (1)

0 = 3 – x + 2y … … … (2)

Вы должны понять, почему я сделал то, что сделал там… вычитая X из левой и правой сторон, чтобы оба уравнения были = 0

Затем в J5:J6 вы можете увидеть, что я сделал на скриншоте выше. Например, в J5:

SOLVER найдет ответ на этот вопрос, когда поместит правильные значения в ячейки H4 и I4 в этом примере, а J5 = 0 и J6 = 0… точно в соответствии с работой, которую мы только что проделали.

Настройте SOLVER следующим образом:

Перейдите на вкладку "Данные"... ПОИСК

Вы увидите это:

Введите то, что вы видите, в различные поля на случай, если я очистил их перед загрузкой файла! Убедитесь, что вы понимаете, что я сделал, потому что через минуту вы сделаете это самостоятельно для других и более крупных вопросов: трех и четырех переменных.

Установите для ячейки Цели значение J5… SOLVER заставит ее читать $J$5

Кому: значение 0… это жизненно важно

Изменяя ячейки переменных, H4:I4… снова SOLVER добавляет $$, и это ячейки, которые дадут вам ответ

С учетом ограничений:

В этом случае есть только один, и это J6 … нажмите «Добавить» … и сделайте так, чтобы он выглядел так:

Теперь нажмите «Решить», и ПОИСК должен сообщить вам, что он нашел решение, поместить решение в H4: I4 И показать, что J5 и J6 оба = 0. ЭТО ваш ответ. КЭД x = 5 и y = 1.

Попробуйте это

Повторяйте вышеприведенное, пытаясь решить эти уравнения для x и y:

Вы уже решили эту задачу, но для практики также используйте SOLVER.

Решение одновременных уравнений с тремя переменными с помощью SOLVER

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

ПРЕЖДЕ ЧЕМ вы посмотрите на мои решения в моей рабочей тетради, попробуйте сами решить, как решить эту проблему с помощью SOLVER.

Вот оно! Заставьте это работать, и это реальная экономия времени. Если вы можете заставить уравнения работать, но не SOLVER, не беспокойтесь, по крайней мере, вы нашли один работающий способ. И наоборот: если вы можете использовать SOLVER, но не уравнения…

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

Я также включил в этот файл формулу, которую вам может быть интересно изучить:

Вы увидите эту формулу в вопросах с четырьмя переменными, но вы так же легко сможете использовать ее и для вопросов с двумя и тремя переменными.

Читайте также:

  
• Как сделать шрифт без засечек в Word
  
• Невозможно открыть окно Outlook с недопустимым форматом xml
  
• Как повернуть в AutoCad в 3D
  
• Почему формула суммирования не работает в Excel
  
• Программное обеспечение Adobe для редактирования видео

По завершении нажмите нажмите кнопку OK

затем нажмите кнопку SOLVE в исходном диалоговом окне, чтобы запустить Solver.