Как найти и удалить радиальные ссылки в Excel

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

Что такое циркулярные ссылки в Excel?

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

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

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

Осознание циркулярных ссылок

Чтоб удалить формулу, для вас необходимо поначалу ее осознать. Ссылка быть может циркулярной 2-мя методами: прямо и косвенно.

Прямые циркулярные ссылки

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

В ячейках A1 , A2 и A3 введите числа 100, 200 и 300.
Выделите ячейку A4 и в строке формул введите формулу ниже:

Эта формула делает циклическую ссылку, в которой A4 пробует суммировать набор значений со значением A4. Но какова ценность A4? Вы никогда не узнаете, и Excel тоже.

В итоге данной повторяющейся ссылки Excel возвратит 0 для формулы. Вы сможете не знать ответ на эту формулу, но это не 0.

Косвенные циркулярные ссылки

Косвенная повторяющаяся ссылка – это когда ячейка ссылается на себя через остальные ячейки.

Как и ранее, Excel возвращает 0. Но, в отличие от предшествующего примера, ячейка D4 не ссылается на себя впрямую. Заместо этого он суммирует A1 с набором значений. Что такое А1? Это D4. Так что все-таки такое D4? Это A1 с массивом значений.

Была сформирована радиальная ссылка.

Поиск и удаление радиальных ссылок в Excel

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

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

В ячейках A1 , A2 и A3 введите 1, 2 и 3.
Выделите ячейку A4 .
В строке формул введите формулу ниже и нажмите Enter :

Excel возвращает 0, поэтому что формула в A4 является прямой повторяющейся ссылкой. Сделаем и косвенный:

В ячейке C1 введите 20 .
Введите формулу ниже для ячейки E4 :
Введите формулу ниже для ячейки G1 :
В конце концов, изберите ячейку C1 в строке формул и поменяйте 20 строчкой ниже:

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

Давайте поглядим, как можно применять инструменты Excel для поиска повторяющихся ссылок в вашей электрической таблице. Имейте в виду, что ваша электрическая таблица содержит четыре циклические ссылки, а конкретно A4 (прямые), C1, E4 и G1 (косвенные).

В меню ленты перейдите на вкладку Формула .
В разделе « Аудит формул » нажмите клавишу со стрелкой рядом с полем «Проверка ошибок» .
В меню наведите указатель мыши на радиальные ссылки . Это откроет перечень повторяющихся ссылок.
Щелкните ячейку в перечне. Вы попадете в ячейку с радиальный ссылкой.
Вы заметите, что Excel указывает лишь одну из 4 ссылок. Это поэтому, что Excel обрабатывает эти цепочки по очереди.
Опосля исправления первой радиальный ссылки можно перебегать к последующей.
На последующем шаге исправьте радиальную ссылку .
Вернитесь в раздел «Проверка ошибок» и нажмите « Циркулярные ссылки» .
Из перечня изберите последующую радиальную ссылку и приступите к ее исправлению.

Отслеживание отношений меж ячейками

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

Выделите ячейку, содержащую формулу.
На ленте перейдите на вкладку Формула .
В разделе « Аудит формул » щелкните « Прецеденты трассировки» .
Все ячейки, которые влияют на значения избранной ячейки, будут соединены вместе голубыми стрелками.
Потом в том же разделе нажмите « Выслеживать зависимые» . (Этот способ будет отрисовывать голубые стрелки от избранной ячейки к каждой из ячеек, на которые он влияет.)
В конце концов, чтоб узреть формулы заместо значений для всех ячеек, нажмите « Показать формулы» .

Порвать круг

Excel употребляет циклические ссылки, когда пробует вычислить итог ячейки, которая была посещена наиболее 1-го раза во время цикла вычислений. Так что это не поможет для вас всегда.

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

Excel для математиков

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

Функции с побочным эффектом и неявная передача данных

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

ПравильнаяФункция( X As Variant) As Variant . Это пример неплохой, верно построенной функции. Через параметр X ей передается значение некой ячейки рабочего листа (объект Range ). В качестве результата она возвращает значение функции,- в нашем примере итог является копией входного параметра X .
ПобочныйЭффект(X As Variant, Y As Variant) As Variant . В данной функции кроме вычисления результата меняется и значение параметра Y . Так как по дефлоту параметр передается по ссылке ( By Ref ), то это обязано было бы привести к побочному эффекту и поменять содержимое ячейки рабочего листа, переданной в качестве параметра Y . Мы увидим, что этого, но, не происходит.
НеявнаяПередача(X As Variant) As Variant В данной функции итог зависит не только лишь от входного параметра X , да и от значения иной, неявно применяемой ячейки рабочего листа.

Ах так смотрятся описания наших функций:

Ах так смотрится рабочий лист Excel , на котором вызываются эти функции:

Анализируя приобретенные результаты, обратим внимание на последующие моменты:

Функция, которую я именовал » ПравильнаяФункция «, при вызове ее из рабочей формулы получает объект Range в качестве собственного параметра X , и возвращает объект Range в качестве результата выполнения функции. Объект Range быть может единственной ячейкой, и в этом случае функция может вызываться в обыкновенной рабочей формуле. Если же функция получает массив ячеек и возвращает массив, то она обязана вызываться в формуле над массивами. На рабочем листе я показал оба метода вызова функции. В ячейку D4 я записал формулу » =ПравильнаяФункция(B4) «. Так как функция в качестве результата возвращает переданный ей аргумент, то значение, записанное ячейку D4, будет совпадать со значением 17 , хранящемся в ячейке B4. Потом я в ячейки D5:E5 записал формулу над массивами » <=ПравильнаяФункция(B4:C4)>«. В итоге эта же функция дозволяет скопировать спектр ячеек.
В функции ПобочныйЭффект , хотя Y и получает «правильное» значение переменной X , но это никак не сказывается на значении ячейки рабочего листа, переданной в качестве параметра Y , хотя параметр передается по ссылке. Так что можно считать, что передача объектов Range рабочего листа Excel в функцию постоянно происходит по значению, а не по ссылке.
Всякая попытка очевидно либо неявно поменять значения ячеек рабочего листа в процессе работы функции рабочего листа кроме возвращаемого функцией результата оканчивается неуспехом. Наиболее того, попытка очевидно поменять значение в ячейке приводит к тому, что итог функции становится неопределенным. Так что побочный эффект во всех его проявлениях запрещен. Поменять содержимое листа можно лишь, возвращая итог работы вызываемых функций в формулах рабочего листа.
В функции НеявныеДанные создается локальный объект Range . Он получает значение одной из ячеек рабочего листа, и это значение влияет на итог, возвращаемый функцией. Тем становится вероятной неявная передача данных, минуя аппарат формальных характеристик. Заметьте, что Excel не может найти таковой метод зависимости меж ячейками. Мы специально отразили имеющиеся, по воззрению Excel зависимости. Как видите, Excel не подозревает, что ячейка D10 зависит от ячейки C4. Конкретно возможность неучтенных зависимостей принуждает Excel вполне проводить вычисления всех наличествующих пользовательских функций.
Ни функции с побочным эффектом , ни функции с неявной передачей данных не вызывают никаких предупреждающих сообщений.

Циклические вычисления

Если зависимые ячейки Excel образуют цикл, то молвят, что имеют пространство циклические ссылки ( circular references ). В обыкновенном режиме Excel обнаруживает цикл и выдает сообщение о появившейся ситуации, требуя убрать циклические ссылки. Следуя обыкновенной семантике, он не может провести вычисления, потому что циклические ссылки порождают нескончаемые вычисления. Есть два выхода из данной ситуации, — убрать циклические ссылки либо поменять настройку в машине вычислений так, чтоб такие вычисления стали вероятными. В крайнем случае, естественно, требуется, чтоб число повторений цикла было конечным. Excel допускает переход к новейшей семантике, обеспечивающей проведение повторяющихся вычислений. Вручную, для этого довольно на вкладке Вычисления ( меню Сервис, пункт Характеристики) включить флаг Итерации и по мере необходимости поменять число повторений цикла в окошке » Максимум итераций». Можно также задать точность вычислений в окошке «Наибольшее изменение», что также приводит к ограничению числа повторений цикла . По дефлоту наибольшее число итераций и точность вычислений соответственно имеют значения 100 и 0,0001. Понятно, что включить циклические вычисления и задать значения характеристик, определяющих окончание цикла , можно и программно.

Укажем, индивидуальности семантики повторяющихся вычислений:

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

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

Циклические вычисления и нахождение корней уравнения

Покажем, как можно применять циклические вычисления на примере задачки нахождения корня уравнения способом Ньютона. Для простоты я начну с квадратного уравнения, а позднее рассмотрю и наиболее «суровые» уравнения. Итак, разглядим квадратное уравнение: X 2 -5X+6 =0 . Найти корень этого (и хоть какого другого уравнения) можно, используя всего одну единственную ячейку Excel . Для этого довольно включить режим повторяющихся вычислений и ввести в произвольную ячейку с именованием, скажем X , рекуррентную формулу, задающую вычисления по Ньютону:

где F и F1 задают соответственно выражения, вычисляющие функцию и производную. Для нашего квадратного уравнения опосля ввода формулы в ней покажется значение 2 , соответственное одному из корней уравнения. Как получить 2-ой корень? Обычно, это можно создать методом конфигурации исходного приближения. В нашем случае изначальное приближение не задавалось, итерационный процесс вычислений начинался со значения, хранимого в ячейке X по дефлоту и равного нулю. Как же задать изначальное приближение в повторяющихся вычислениях? Появившаяся неувязка не связана с данной определенной задачей. Она возникает постоянно в повторяющихся вычислениях, — до начала цикла нужно задать исходные установки. В рекуррентных соотношениях постоянно есть некий исходный отрезок . Решать задачку задания исходных установок в любом случае можно по -разному. Я продемонстрирую один прием, основанный на использовании функции ЕСЛИ . Ах так смотрится «истинное» решение данной задачки, использующее 4 ячейки, две из которых необходимы по существу дела, а две употребляются для увеличения наглядности процесса вычислений:

Как найти радиальную ссылку в Excel (стремительно и просто)

При работе с формулами Excel вы сможете время от времени узреть последующее предупреждение.

Это приглашение докладывает для вас, что на вашем листе есть повторяющаяся ссылка, и это может привести к неверному расчету по формулам. Он также просит вас решить эту делему с повторяющимися ссылками и отсортировать ее.

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

Что такое радиальная ссылка в Excel?

Проще говоря, повторяющаяся ссылка возникает, когда вы в конечном итоге получаете формулу в ячейке, которая сама по для себя употребляет ячейку (в которую она была введена) для вычисления.

Разрешите мне попробовать разъяснить это на ординарном примере.

Представим, у вас есть набор данных в ячейке A1: A5, и вы используете приведенную ниже формулу в ячейке A6:

Это даст для вас предупреждение о радиальный ссылке.

Это поэтому, что вы желаете просуммировать значения в ячейке A1: A6, и итог должен быть в ячейке A6.

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

Как найти радиальные ссылки в Excel?

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

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

Ниже приведены шаги, чтоб найти циклическую ссылку в Excel:

Активируйте рабочий лист с радиальный ссылкой
Перейдите на вкладку "Формулы".
В группе «Редактирование формул» щелкните раскрывающийся значок «Проверка ошибок» (малая стрелка, направленная вниз, справа).
Наведите курсор на опцию Циркулярные ссылки. Он покажет для вас ячейку с радиальный ссылкой на листе.
Щелкните адресок ячейки (который отображается), и вы перейдете к данной ячейке на листе.

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

Очередной резвый и обычной метод найти радиальную ссылку — это поглядеть на строчку состояния. В левой его части будет отображаться текст Циркулярная ссылка вкупе с адресом ячейки.

При работе с радиальными ссылками нужно знать несколько вещей:

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

Как удалить радиальную ссылку в Excel?

Как лишь вы обусловили, что на вашем листе есть циклические ссылки, пора их удалить (если вы не желаете, чтоб они были там по какой-нибудь причине).

К огорчению, это не так просто, как надавить кнопку удаления. Так как они зависят от формул, а любая формула различается, для вас нужно рассматривать это в любом определенном случае.

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

Но время от времени все не так просто.

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

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

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

Формула в ячейке A6: = СУММ (A1: A5) + C6.
Формула: ячейка C1 = A6 * 0,1
Формула в ячейке C6: = A6 + C1.

В приведенном выше примере итог в ячейке C6 зависит от значений в ячейках A6 и C1, которые, в свою очередь, зависят от ячейки C6 (что вызывает ошибку повторяющейся ссылки)

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

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

Это можно создать при помощи параметра "Выслеживать прецеденты".

Ниже приведены шаги по использованию прецедентов трассировки для поиска ячеек, которые передаются в ячейку с повторяющейся ссылкой:

Изберите ячейку с радиальный ссылкой
Перейдите на вкладку "Формулы".
Нажмите на прецеденты трассировки

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

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

Это отлично работает, если у вас все есть формулы, относящиеся к ячейкам на одном листе. Если это на нескольких листах, этот способ неэффективен.

Как включить / отключить итерационные вычисления в Excel

Когда у вас есть радиальная ссылка в ячейке, поначалу вы получаете предупреждение, как показано ниже, и если вы закроете это диалоговое окно, в качестве результата в ячейке вы получите 0.

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

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

Это именуется итерационный расчет в Excel.

Ниже приведены шаги для включения и опции итерационных вычислений в Excel:

Перейдите на вкладку Файл.
Щелкните Характеристики. Раскроется диалоговое окно "Характеристики Excel".
Изберите формулу на левой панели
В разделе «Характеристики расчета» установите флаг «Включить итерационный расчет». Тут вы сможете указать наибольшее количество итераций и наибольшее значение конфигурации

Вот и все! Вышеупомянутые шаги дозволят выполнить итеративный расчет в Excel.

Разрешите мне также стремительно разъяснить два варианта итеративного расчета:

Наибольшее количество итераций: Это наибольшее количество раз, которое вы желаете, чтоб Excel вычислил, до этого чем выдает окончательный итог. Потому, если вы укажете это как 100, Excel выполнит цикл 100 раз, до этого чем выдаст для вас окончательный итог.
Наибольшее изменение: Это наибольшее изменение, которое, если не достигнуто меж итерациями, вычисление будет остановлено. По дефлоту это значение 0,001. Чем ниже это значение, тем поточнее будет итог.

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

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

Предумышленное внедрение радиальных ссылок

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

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

Один таковой определенный вариант, о котором я уже писал, получение отметки времени в ячейке в ячейке в Excel.

К примеру, представим, что вы желаете сделать формулу, чтоб любая запись выполнялась в ячейке в столбце A, а метка времени отображалась в столбце B (как показано ниже):

Хотя вы сможете просто вставить метку времени, используя последующую формулу:

Неувязка с приведенной выше формулой заключается в том, что она обновит все временные метки, как на листе будет внесено какое-либо изменение либо если рабочий лист будет повторно открыт (так как формула СЕЙЧАС является изменчивой)

Чтоб обойти эту делему, вы сможете применять способ радиальный ссылки. Используйте ту же формулу, но разрешите итеративный расчет.

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

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

Решение уравнений в Excel способом итераций Крамера и Гаусса

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

Разглядим на примерах некие варианты решений.

Решение уравнений способом подбора характеристик Excel

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

Путь к команде: «Данные» — «Работа с данными» — «Анализ «что-если»» — «Подбор параметра».

Разглядим на примере решение квадратного уравнения х 2 + 3х + 2 = 0. Порядок нахождения корня средствами Excel:

Введем в ячейку В2 формулу для нахождения значения функции. В качестве аргумента применим ссылку на ячейку В1.
Открываем меню инструмента «Подбор параметра». В графе «Установить в ячейку» — ссылка на ячейку В2, где находится формула. В поле «Значение» вводим 0. Это то значение, которое необходимо получить. В графе «Изменяя значение ячейки» — В1. Тут должен отобразиться отобранный параметр.
Опосля нажатия ОК отобразится итог подбора. Если необходимо его сохранить, вновь жмем ОК. В неприятном случае – «Отмена».

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

Как решить систему уравнений матричным способом в Excel

Дана система уравнений:

Значения частей введем в ячейки Excel в виде таблицы.
Найдем оборотную матрицу. Выделим спектр, куда потом будут помещены элементы матрицы (ориентируемся на количество строк и столбцов в начальной матрице). Открываем перечень функций (fx). В группы «Математические» находим МОБР. Аргумент – массив ячеек с элементами начальной матрицы.
Жмем ОК – в левом верхнем углу спектра возникает значение. Поочередно нажимаем клавишу F2 и сочетание кнопок Ctrl + Shift + Enter.
Умножим оборотную матрицу Ах -1х на матрицу В (конкретно в таком порядке следования множителей!). Выделяем спектр, где потом покажутся элементы результирующей матрицы (ориентируемся на число строк и столбцов матрицы В). Открываем диалоговое окно математической функции МУМНОЖ. 1-ый спектр – оборотная матрица. 2-ой – матрица В.
Закрываем окно с аргументами функции нажатием клавиши ОК. Поочередно жмем клавишу F2 и комбинацию Ctrl + Shift + Enter.

Получены корешки уравнений.

Решение системы уравнений способом Крамера в Excel

Возьмем систему уравнений из предшествующего примера:

Для их решения способом Крамера вычислим определители матриц, приобретенных подменой 1-го столбца в матрице А на столбец-матрицу В.

Для расчета определителей используем функцию МОПРЕД. Аргумент – спектр с соответственной матрицей.

Рассчитаем также определитель матрицы А (массив – спектр матрицы А).

Определитель системы больше 0 – решение можно найти по формуле Крамера (D_x / |A|).

Для расчета Х₁: =U2/$U$1, где U2 – D1. Для расчета Х₂: =U3/$U$1. И т.д. Получим корешки уравнений:

Решение систем уравнений способом Гаусса в Excel

Для примера возьмем простейшую систему уравнений:

3а + 2в – 5с = -1
2а – в – 3с = 13
а + 2в – с = 9

Коэффициенты запишем в матрицу А. Вольные члены – в матрицу В.

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

Приведем все коэффициенты при а к 0. Не считая первого уравнения. Скопируем значения в первой строке 2-ух матриц в ячейки В6:Е6. В ячейку В7 введем формулу: =B3:Е3-$B$2:$Е$2*(B3/$B$2). Выделим спектр В7:Е7. Нажмем F2 и сочетание кнопок Ctrl + Shift + Enter. Мы отняли от 2-ой строчки первую, умноженную на отношение первых частей второго и первого уравнения.
Копируем введенную формулу на 8 и 9 строчки. Так мы избавились от коэффициентов перед а. Сохранили лишь 1-ое уравнение.
Приведем к 0 коэффициенты перед в в 3-ем и четвертом уравнении. Копируем строчки 6 и 7 (лишь значения). Переносим их ниже, в строчки 10 и 11. Эти данные должны остаться постоянными. В ячейку В12 вводим формулу массива.
Прямую прогонку по способу Гаусса сделали. В оборотном порядке начнем прогонять с крайней строчки приобретенной матрицы. Все элементы данной строчки необходимо поделить на коэффициент при с. Введем в строчку формулу массива: <=B12:E12/D12>.
В строке 15: отнимем от 2-ой строчки третью, умноженную на коэффициент при с 2-ой строчки (<=(B11:E11-B16:E16*D11)/C11>). В строке 14: от первой строчки отнимаем вторую и третью, умноженные на надлежащие коэффициенты (<=(B10:E10-B15:E15*C10-B16:E16*D10)/B10>). В крайнем столбце новейшей матрицы получаем корешки уравнения.

Примеры решения уравнений способом итераций в Excel

Вычисления в книжке должны быть настроены последующим образом:

Делается это на вкладке «Формулы» в «Параметрах Excel». Найдем корень уравнения х – х 3 + 1 = 0 (а = 1, b = 2) способом итерации с применением повторяющихся ссылок. Формула:

M – наибольшее значение производной по модулю. Чтоб найти М, произведем вычисления:

f’ (1) = -2 * f’ (2) = -11.

Приобретенное значение меньше 0. Потому функция будет с обратным знаком: f (х) = -х + х 3 – 1. М = 11.

В ячейку А3 введем значение: а = 1. Точность – три знака опосля запятой. Для расчета текущего значения х в соседнюю ячейку (В3) введем формулу: =ЕСЛИ(B3=0;A3;B3-(-B3+СТЕПЕНЬ(B3;3)-1/11)).

В ячейке С3 проконтролируем значение f (x): при помощи формулы =B3-СТЕПЕНЬ(B3;3)+1.

Корень уравнения – 1,179. Введем в ячейку А3 значение 2. Получим этот же итог:

Интересно почитать: Как в excel добавить к числу процент