Newcomposers.ru

IT Мир
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Итерации в excel

Microsoft Excel

трюки • приёмы • решения

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

Обычной проблемой для бизнеса является вычисление определенного вклада как процента от чистой прибыли компании. Это не простая проблема умножения, поскольку чистая прибыль также учитывает данный вклад. Например, если доходы компании составили 1 000 000 рублей, а расходы 900 000, валовая прибыль при этом составляет 100 000 рублей. При этом компания выделяет в сторону в качестве вклада 10% от чистой прибыли.

Чистая прибыль при этом будет вычисляться по следующей формуле: Чистая прибыль = Валовая прибыль — Вклад . Это называется циклически зависимая формула, поскольку выражения в левой и правой части зависят друг от друга. В данном случае сумма вклада будет вычисляться по формуле: Вклад = (Чистая прибыль)*0.1 .

Одним из способов решения данной проблемы является возможность предположить правильный ответ и затем посмотреть, насколько вы близко находитесь от реального результата. Например, в данном случае можно предположить, что, если вклад составляет 10% от чистой прибыли, мы можем взять эти 10% от валовой прибыли в 100 000. При этом сумма вклада получается равной 10 000. Если мы используем это число в формуле, то получим, что чистая прибыль равна 90 000, а 10% от нее равны 9 000. Таким образом, мы ошиблись на 1 000. Можно попробовать ещё раз. Возьмем вклад, равный 9 000. При этом чистая прибыль будет равна 91 000, а сумма вклада в 10% от этой суммы будет равна 9 100. Мы ошиблись уже всего на 100 рублей.

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

Рис. 3.5. Циклические ссылки

  1. Загрузите вашу книгу и введите туда необходимые циклически зависимые расчеты. На рис. 3.5 показана книга, вычисляющая предыдущий рассмотренный пример. При нажатии на Enter — подтверждении формулы расчета вклада — Excel выведет на экран предупреждение о наличии циклов.
  2. Нажмите на кнопку Файл, далее Параметры, затем вкладка Формулы.
  3. Включите флажок Включить итеративные вычисления.
  4. Вы можете использовать поле Предельное число итераций для задания количества итераций для вычисления. Как правило, число 100 является оптимальным между точностью и временем вычисления.
  5. Также вы можете задать Относительную погрешность в соответствующем иоле. При достижении заданной здесь точности Excel автоматически прекратит вычисления. Опять же, число 0.001, предложенное по умолчанию, является адекватным для большинства ситуаций.
  6. Нажмите ОК. Excel произведет вычисления и выдаст ответ (см 3.6).

Рис. 3.6. Ответ после итерационных вычислений

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

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

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

Рис. 1. Пример циклической ссылки

Но. Не всегда циклическая ссылка является бедствием. Циклическую ссылку можно использовать для решения уравнений итерационным способом. Для начала нужно позволить Excel вести вычисления, даже при наличии циклической ссылки. В обычном режиме Excel, обнаружив циклическую ссылку, выдаст сообщение об ошибке, и потребует ее устранения. В обычном режиме Excel не может провести вычисления, так как циклическая ссылка порождает бесконечный цикл вычислений. Можно, либо устранить циклическую ссылку, либо допустить вычисления по формуле с циклической ссылкой, но ограничив число повторений цикла. Для реализации второй возможности щелкните на кнопке «Office» (в левом верхнем углу), а затем на «Параметры Excel» (рис. 2).

Скачать заметку в формате Word, примеры в формате Excel

Рис. 2. Параметры Excel

В открывшемся окне «Параметры Excel» перейдите на вкладку Формулы и отметьте «Включить итеративные вычисления» (рис. 3). Помните, что эта опция включается для приложения Excel в целом (а не для одного файла), и будет действовать, пока вы ее не отключите.

Рис. 3. Включить итеративные вычисления

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

Решим уравнение третьей степени: х 3 – 4х 2 – 4х + 5 = 0 (рис. 4). Для решения этого уравнения (и любого другого уравнения совершенно произвольного вида) понадобится всего одна ячейка Excel.

Читать еще:  Как увидеть повторяющиеся значения в excel

Рис. 4. График функции f(x)

Для решения уравнения нам понадобится рекуррентная формула (то есть, формула, выражающая каждый член последовательности через один или несколько предыдущих членов):

(1) x = x – f(x)/f’(x), где

f(x) – функция, задающая уравнение, корни которого мы ищем; f(x) = х 3 – 4х 2 – 4х + 5

f’(x) – производная нашей функции f(x); f’(x) = 3х 2 – 8х – 4; производные основных элементарных функций можно посмотреть здесь.

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

Итоговая рекуррентная формула имеет вид:

(2) х = x – (х 3 – 4х 2 – 4х + 5)/(3х 2 – 8х – 4)

Выберем любую ячейку на листе Excel (рис. 5; в нашем примере это ячейка G19), присвоим ей имя х, и введем в нее формулу:

Можно вместо х использовать адрес ячейки… но согласитесь, что имя х, смотрится привлекательнее; следующую формулу я ввел в ячейку G20:

Рис. 5. Рекуррентная формула: (а) для поименованной ячейки; (б) для обычного адреса ячейки

Как только мы введем формулу и нажмем Enter, в ячейке сразу же появится ответ – значение 0,77. Это значение соответствует одному из корней уравнения, а именно второму (см. график функции f(x) на рис. 4). Поскольку начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке х и равного нулю. Как же получить остальные корни уравнения?

Для изменения стартового значения, с которого рекуррентная формула начинает свои итерации, предлагается использовать функцию ЕСЛИ: [1]

Здесь значение «-5» – начальное значение для рекуррентной формулы. Изменяя его, можно выйти на все корни уравнения:

Как управлять режимами автоматических и многопоточных вычислений в Excel 2013

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

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

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

  1. Для отключения режима автоматических вычислений откройте Excel и выберите вкладку File (Файл).
  2. Выберите пункт Options (Параметры).
  3. В диалоговом окне Excel Options (Параметры Excel) выберите вкладку Formulas (Формулы), расположенную в меню слева.
  4. Чтобы отключить пересчет данных при каждом изменении значений, формул, имен или же при открытии листа Excel, содержащего формулы, выберите опцию Manual (Вручную) в разделе Calculation options (Параметры вычислений). Затем нажмите ОК.

Параметры вычислений

Следующий список поясняет опции, которые доступны в разделе Calculation options (Параметры вычислений):

  • Automatic (Автоматически) – пересчитывает все зависимые формулы и обновляет все открытые или внедрённые диаграммы при любом изменении значения, формулы или имени. Данная настройка установлена по умолчанию для каждого нового рабочего листа Excel.
  • Automatic except for data tables (Автоматически, кроме таблиц данных) – пересчитывает все зависимые формулы и обновляет все открытые или внедрённые диаграммы, за исключением таблиц данных. Для пересчета таблиц данных, когда данная опция выбрана, воспользуйтесь командой Calculate Now (Пересчет), расположенной на вкладке Formulas (Формулы) или клавишей F9.
  • Manual (Вручную) – пересчитывает открытые рабочие листы и обновляет открытые или внедрённые диаграммы только при нажатии команды Calculate Now (Пересчет) или клавиши F9, а так же при использовании комбинации клавиши Ctrl+F9 (только для активного листа).
  • Recalculate workbook before saving (Пересчитывать книгу перед сохранением) – пересчитывает открытые рабочие листы и обновляет открытые или внедрённые диаграммы при их сохранении даже при включенной опции Manual (Вручную). Если Вы не хотите, чтобы при каждом сохранении зависимые формулы и диаграммы пересчитывались, просто отключите данную опцию.
  • Enable iterative calculation (Включить итеративные вычисления) – разрешает итеративные вычисления, т.е. позволяет задавать предельное количество итераций и относительную погрешность вычислений, когда формулы будут пересчитываться при подборе параметра или при использовании циклических ссылок. Более детальную информацию о подборе параметров и использовании циклических ссылок можно найти в справке Microsoft Excel.
  • Maximum Iterations (Предельное число итераций) – определяет максимальное количество итераций (по умолчанию – 100).
  • Maximum Change (Относительная погрешность) – устанавливает максимально допустимую разницу между результатами пересчета (по умолчанию – 0.001).

Вы также можете переключаться между тремя основными режимами вычислений, используя команду Calculation Options (Параметры вычислений) в разделе Calculation (Вычисление) на вкладке Formulas (Формулы). Однако, если необходимо настроить параметры вычислений, все же придется обратиться к вкладке Formulas (Формулы) диалогового окна Excel Options (Параметры Excel).

Читать еще:  Формула для таблицы квадратов в excel

Многопоточные вычисления в Excel

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

  1. Чтобы включить режим многопоточных вычислений, нажмите на вкладку File (Файл) и выберите пункт Options (Параметры), чтобы открыть диалоговое окно Excel Options (Параметры Excel). Затем нажмите Advanced (Дополнительно).
  2. Опуститесь вниз до раздела Formulas (Формулы) и установите флажок возле пункта Enable multi-threaded calculation (Включить многопоточные вычисления). Вы можете вручную установить количество потоков вычисления, которое необходимо использовать, или указать Excel задействовать все процессоры компьютера, выбрав Use all processors on this computer (Использовать все процессоры данного компьютера).

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

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

Как вычислить определенный интеграл в Excel

Модель вычисления по формуле трапеций

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

где a и b — пределы интегрирования; n-1 количество равных отрезков, на которые разбит интервал интегрирования, xi — значение аргумента, соответствующее текущему шагу интегрирования i.

Графическая интерпретация этой формулы имеет вид как на рис 1.

Весь интервал интегрирования разбивается на одинаковое количество участков (n-1), каждой точке i на интервала разметки соответствует вычисляемое значение функции F(xi). Функция от точки до точки представляется прямым отрезком. Из рисунка видно, что площадь всей фигуры складывается из суммы площадей прямоугольников (2) и треугольников (3). Площадь каждого прямоугольника вычисляется по формуле:

а площадь i-того треугольника равна:

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

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

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

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

Пример 1. Пусть требуется вычислить определенный интеграл

Величина интеграла, вычисленная аналитически, равна 9, проверим путем численного вычисления по методу трапеций.

Технология приближенного вычисления

1. Табулируем подинтегральную функцию в диапазоне изменения значений аргумента 0 – 3 с шагом 0,2 (рис. 2).

2. В ячейку С2 введем формулу =( A3-A2)*B2+(A3-A2)*(B3-B2)/2 , которая реализует часть приведенной выше формулы, размещенной правее знака суммы, т.е вычисляет величину элементарной площадки (криволинейной трапеции).

3. Скопируем буксировкой формулу, записанную в ячейке С2, до значения аргумента х = 2,8.

4. В ячейке С17 просуммируем с помощью автосуммирования полученные результаты. Вычисленное значение в ячейке С17 и будет величиной интеграла — 9.

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

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

Технология точного вычисления

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

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

2. Выполним команду меню Файл > Параметры , в открывшемся диалоговом окне Параметры Excel параметр Формулы. В правой части окна установим параметры вычислений поле Предельное число итераций введем число 1000 (считаем, что диапазон интегрирования будет разбит на 999 интервалов). Если установлена опция Включить итеративные вычисления , то выключим ее. (Рис. 3).

3. В ячейки рабочего листа введем исходные данные и формулы для вычислений (рис. 4).

В ячейке В6 формула =(B4-B2)/B5 вычисляет шаг интегрирования. В ячейке С3 формула =В3+C3+B6 – вычисляет текущее значение аргумента Xi на i-том шаге итерации . В формуле есть циклическая ссылка на эту же ячейку — С3. Значение в ячейке В3 в формуле устанавливает нижний предел интегрирования, а в В4 — верхний предел.В ячейке В7 вычисляется значение функции на i -том шаге, а в ячейке В8 — на i+1 шаге итерации.

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

Читать еще:  Формулы в excel

3. После ввода исходных данных и формул вновь выполним команду меню
Файл > Параметры , в открывшемся диалоговом окне Параметры Excel параметр Формулы. Включим опцию Включить итеративные вычисления .

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

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

Задания для закрепления

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

2. Определите стоимость перевозки 20 тонн груза по железной дороге на расстояние 20 км при условии, что тариф перевозки одной тонны груза убывает на 5 рублей на каждом километре. Начальный тариф для первого километра составляет 150 рублей. (Ответ 40000).

Решение линейных уравнений методом простой итерации c помощью программы Microsoft Excel

Страницы работы

Содержание работы

Министерство общего образования

Уральский государственный технический университет-УПИ

филиал в г.Краснотурьинске

Кафедра вычислительной техники

По численным методам

Решение линейных уравнений методом простой итерации

c помощью программы Microsoft Excel

Руководитель Кузьмина Н.В.

Студент Нигматзянов Т.Р.

Тема: «Нахождение с заданной точностью корня уравнения F(x)=0 на промежутке [a;b] методом простой итерации».

Контрольный пример: 0,25-х+sinx=0

Условия задачи: для заданной функции F(x) на интервале [0,5;2] найти корень уравнения F(x)=0 методом простой итерации.

Корень вычислить дважды(с помощью автоматического и ручного расчета).

Предусмотреть построение графика функции на заданном интервале.

1.Теоретическая часть 5

2.Описание хода работы 7

3.Входные и выходные данные 8

Библиографический список 12

В ходе данной работы мне необходимо ознакомиться с различными методами решения уравнения и найти корень нелинейного уравнения 0,25-х+sin(x)=0 численным методом – методом простой итерации. Для проверки правильности нахождения корня необходимо решить уравнение графически ,найти приближенное значение и сравнить его с полученным результатом.

Метод простой итерации.

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

Для использования этого метода исходное нелинейное уравнение записывается в виде: х=j(х), т.е. выделяется х; j(х) – непрерывна и дифференцируема на интервале (а; в). Обычно это можно сделать несколькими способами:

arcsin(2x+1)-x 2 =0 (f(x)=0)

x=0.5(sinx 2 -1) (x=j(x))

x=x+arcsin(2x+1)-x 2 (x=j(x))

x= (x=j(x)),знак берется в зависимости от интервала [а;b].

Преобразование должно быть таким, чтобы ½j(x)

  • АлтГТУ 419
  • АлтГУ 113
  • АмПГУ 296
  • АГТУ 266
  • БИТТУ 794
  • БГТУ «Военмех» 1191
  • БГМУ 171
  • БГТУ 602
  • БГУ 153
  • БГУИР 391
  • БелГУТ 4908
  • БГЭУ 962
  • БНТУ 1070
  • БТЭУ ПК 689
  • БрГУ 179
  • ВНТУ 119
  • ВГУЭС 426
  • ВлГУ 645
  • ВМедА 611
  • ВолгГТУ 235
  • ВНУ им. Даля 166
  • ВЗФЭИ 245
  • ВятГСХА 101
  • ВятГГУ 139
  • ВятГУ 559
  • ГГДСК 171
  • ГомГМК 501
  • ГГМУ 1966
  • ГГТУ им. Сухого 4467
  • ГГУ им. Скорины 1590
  • ГМА им. Макарова 299
  • ДГПУ 159
  • ДальГАУ 279
  • ДВГГУ 134
  • ДВГМУ 408
  • ДВГТУ 936
  • ДВГУПС 305
  • ДВФУ 949
  • ДонГТУ 497
  • ДИТМ МНТУ 109
  • ИвГМА 488
  • ИГХТУ 130
  • ИжГТУ 143
  • КемГППК 171
  • КемГУ 507
  • КГМТУ 269
  • КировАТ 147
  • КГКСЭП 407
  • КГТА им. Дегтярева 174
  • КнАГТУ 2909
  • КрасГАУ 345
  • КрасГМУ 629
  • КГПУ им. Астафьева 133
  • КГТУ (СФУ) 567
  • КГТЭИ (СФУ) 112
  • КПК №2 177
  • КубГТУ 138
  • КубГУ 107
  • КузГПА 182
  • КузГТУ 789
  • МГТУ им. Носова 367
  • МГЭУ им. Сахарова 232
  • МГЭК 249
  • МГПУ 165
  • МАИ 144
  • МАДИ 151
  • МГИУ 1179
  • МГОУ 121
  • МГСУ 330
  • МГУ 273
  • МГУКИ 101
  • МГУПИ 225
  • МГУПС (МИИТ) 636
  • МГУТУ 122
  • МТУСИ 179
  • ХАИ 656
  • ТПУ 454
  • НИУ МЭИ 640
  • НМСУ «Горный» 1701
  • ХПИ 1534
  • НТУУ «КПИ» 212
  • НУК им. Макарова 542
  • НВ 778
  • НГАВТ 362
  • НГАУ 411
  • НГАСУ 817
  • НГМУ 665
  • НГПУ 214
  • НГТУ 4610
  • НГУ 1992
  • НГУЭУ 499
  • НИИ 201
  • ОмГТУ 301
  • ОмГУПС 230
  • СПбПК №4 115
  • ПГУПС 2489
  • ПГПУ им. Короленко 296
  • ПНТУ им. Кондратюка 119
  • РАНХиГС 186
  • РОАТ МИИТ 608
  • РТА 243
  • РГГМУ 117
  • РГПУ им. Герцена 123
  • РГППУ 142
  • РГСУ 162
  • «МАТИ» — РГТУ 121
  • РГУНиГ 260
  • РЭУ им. Плеханова 122
  • РГАТУ им. Соловьёва 219
  • РязГМУ 125
  • РГРТУ 666
  • СамГТУ 130
  • СПбГАСУ 315
  • ИНЖЭКОН 328
  • СПбГИПСР 136
  • СПбГЛТУ им. Кирова 227
  • СПбГМТУ 143
  • СПбГПМУ 146
  • СПбГПУ 1598
  • СПбГТИ (ТУ) 292
  • СПбГТУРП 235
  • СПбГУ 577
  • ГУАП 524
  • СПбГУНиПТ 291
  • СПбГУПТД 438
  • СПбГУСЭ 226
  • СПбГУТ 193
  • СПГУТД 151
  • СПбГУЭФ 145
  • СПбГЭТУ «ЛЭТИ» 379
  • ПИМаш 247
  • НИУ ИТМО 531
  • СГТУ им. Гагарина 113
  • СахГУ 278
  • СЗТУ 484
  • СибАГС 249
  • СибГАУ 462
  • СибГИУ 1654
  • СибГТУ 946
  • СГУПС 1473
  • СибГУТИ 2083
  • СибУПК 377
  • СФУ 2423
  • СНАУ 567
  • СумГУ 768
  • ТРТУ 149
  • ТОГУ 551
  • ТГЭУ 325
  • ТГУ (Томск) 276
  • ТГПУ 181
  • ТулГУ 553
  • УкрГАЖТ 234
  • УлГТУ 536
  • УИПКПРО 123
  • УрГПУ 195
  • УГТУ-УПИ 758
  • УГНТУ 570
  • УГТУ 134
  • ХГАЭП 138
  • ХГАФК 110
  • ХНАГХ 407
  • ХНУВД 512
  • ХНУ им. Каразина 305
  • ХНУРЭ 324
  • ХНЭУ 495
  • ЦПУ 157
  • ЧитГУ 220
  • ЮУрГУ 306

Полный список ВУЗов

Чтобы распечатать файл, скачайте его (в формате Word).

Ссылка на основную публикацию
Adblock
detector