Метод половинного деления в excel vba
1.3.2. Приближенное решение уравнений на языке Visual Basic
Проект «Приближенное решение уравнения» на языке Visual Basic
Численный метод половинного деления
Идея метода состоит в выборе точности решения и сведении первоначального числового отрезка [А; В], на котором существует корень уравнения, к отрезку заданной точности. Процесс заключается в последовательном делении отрезков пополам точкой С = (А + В)/2 и отбрасыванию той половины отрезка ([А; С] или [С; В]), на котором корня нет.
Выбор нужной половины отрезка основывается на проверке знаков значений функции на его краях. Выбирается та половина, на которой произведение значений функции на краях отрицательно, т. е. когда функция имеет разные знаки — пересекает ось абсцисс.
Процесс продолжается до тех пор, пока длина числового отрезка не станет меньше заданной удвоенной точности. Деление этого отрезка пополам дает значение корня с заданной точностью х ≈ (А + В)/2.
3. Поместить на форму (рис. 1.13):
• текстовые поля TextBox1 и TextBox2 для ввода числовых значений концов отрезка А и В;
• текстовое поле TextBox3 для ввода точности вычислений Р;
• надпись Label1 для вывода значений корня;
• четыре надписи для вывода обозначений;
• кнопку Button2 для запуска обработчика события.
4. Ввести программный код, позволяющий вычислить корень уравнения методом половинного деления с использованием цикла с постусловием, который будет выполняться, пока выполняется условие (В - А)/2 > Р:
'Численное решение уравнения
Dim А, В, С, Р As Single
Private Sub Button2_Click (. )
A=Val(TextBox1.Text)
B=Val(TextBox2.Text)
P=Val(TextBox3.Text)
Do
C = (A + B)/2
If (Aˆ3-Math.Cos(A))*(Cˆ3-Math.Cos(C))Then
B = C
Else
A = C
End If
Loop While (В-A)/2>P
Label1.Text=(A+B)/2
End Sub
Из графика функции видно, что корень находится на отрезке [0,5; 1]. Введем в текстовые поля значения концов числового отрезка, а также точность вычислений (например, 0,0001).
На надпись будет выведено значения корня:x ≈ 0,8654175 (см. рис. 1.13).
Рис. 1.13. Проект «Приближенное решение уравнения» на языке Visual Basic
Точность вычисления корня зависит не только от параметров используемого численного метода, но и от типа переменной. В нашем случае имеет смысл говорить о математической точности результата, которая не может превышать точность числового метода, т. е. х ≈ 0,8654.
Следующая страница Контрольные вопросы
Cкачать материалы урока
Доброго времени суток! Я решил разбавить наши стандартные статьи по программированию в VBA. Поэтому сегодня мы поговорим о численных методах решения нелинейных алгебраических уравнений, в целом и о каждом в отдельности.
Численные методы
Предназначение численных методов — упростить человеку решение математических задач. Существуют численные методы для решения интегралов, дифференциалов, линейных и нелинейных алгебраических уравнений и т.д. В основном в ВУЗ-ах изучают только перечисленные.
Под численными методами решения алгебраических уравнений подразумевают алгоритмы нахождения корней уравнения. Алгоритмы, в основном, отличаются скоростью нахождения корней с необходимой точностью, а также условием сходимости. Также, иногда встречается такая характеристика, как простота алгоритма.
Метод половинного деления(бисекции, дихотомии)
В отличие от большинства других методов, метод половинного деления сходится всегда, т.е. обладает безусловной сходимостью, поэтому применим для решения любых уравнений. Кроме этого он чрезвычайно прост. Основным недостатком такого метода является его скорость. С каждым шагом его погрешность уменьшается в два раза, поэтому его можно отнести к методам с линейной сходимостью.
Реализация метода половинного деления в VBA
В интернете можно найти много разных реализаций данного метода как на C++, Matlab и других языках. Языков много, алгоритм один. Для лучшего понимания посмотрим на блок схему.
Распишем общий алгоритм:
Шаг 0. Задать концы отрезка a и b, функцию f, малое число e>0 (допустимую абсолютную погрешность корня или полудлину его промежутка неопределённости), малое q>0 (допуск, связанный с реальной точностью вычисления значений данной функции); вычислить f(a).
2 комментария
Не работает же, просто делит диапазон на уменьшающиеся в 2 раза отрезки
Вова, метод и заключается в том, чтобы делить отрезок (область поиска) в два раза до тех пор, пока не выполнится условие по точности. Этот метод может и не найти точку экстремума функции, если их несколько на отрезке поиска.
В рамках подготовки курса для бакалавров МФТИ я понял, что в моем блоге не так много заметок по использованию Excel в математике и физике. Каково же было мое удивление, когда я обнаружил, что книг по этой теме на русском языке буквально единицы. Ранее я опубликовал Вильям Дж. Орвис. Excel для ученых, инженеров и студентов. Представлено четыре варианта решения алгебраических уравнений методом половинного деления в Excel: итерационная таблица на листе Excel, процедура VBA, функция VBA, пользовательская функция Excel на основе LAMBDA. В настоящей заметке использованы материалы книги Алексея Васильева Числовые расчеты в Excel. Бумажная и электронная версии книги доступны на сайте издательства.
Рис. 1. Метод половинного деления: цифрами и штрихованными линиями со стрелкой показана последовательность перемещения границ интервала поиска решения
Алгоритм
Метод половинного деления используется для поиска решения уравнения вида f(x) = 0 на интервале х1 < х < х2 при условии, что функция f(x) на этом интервале непрерывна и принимает на границах интервала значения разных знаков. Если все условия выполнены, то с помощью метода половинного деления можно найти решение уравнения с любой заданной (отличной от нуля) точностью за конечное количество итераций.
Если функция на интервале непрерывна и на границах интервала принимает значения разных знаков, то на этом интервале у функции имеется хотя бы один нуль (т. е. хотя бы в одной точке функция принимает нулевое значение). Но таких нулей может быть и больше одного. Другими словами, необходимые для применения метода половинного деления условия гарантируют, что корень есть, но не гарантируют, что он один. И если корней несколько, то какой именно будет найден — сказать достаточно сложно (все зависит от начальных границ интервала поиска решения). Локализация корней уравнения — отдельная сложная задача, решение которой выходит за рамки книги. В данном конкретном случае, чтобы гарантировать единственность решения на указанном интервале на функцию, определяющую уравнение, можно наложить условие неизменности знака производной (на всем интервале поиска решения). Таким образом, если мы решаем уравнение f(x) = 0 и функция f(x) на интервале поиска решения непрерывна, монотонна (монотонно возрастает или монотонно убывает) и на границах интервала принимает значения разных знаков, то уравнение имеет решение, и это решение единственно.
Алгоритм поиска решения алгебраического уравнения методом половинного деления (см. рис. 1):
- вычисляется значение функции f(x) в центральной точке интервала;
- если функция в центральной точке меньше нуля, в центральную точку смещается та граница интервала поиска решения, на которой функция меньше нуля;
- если функция в центральной точке больше нуля, в центральную точку смещается та граница интервала поиска решения, на которой функция больше нуля;
- в результате интервал поиска решения уменьшается вдвое, а задача, фактически, сводится к предыдущей (с поправкой на измененный интервал) — нужно найти корень уравнения f(x) = 0 на интервале, на границах которого функция f(x) принимает значения разных знаков;
- процесс продолжается до тех пор, пока ширина интервала не станет достаточно малой для того, чтобы обеспечить необходимую точность вычисления корня.
Решение на основе встроенных функций Excel
В Excel метод половинного деления можно реализовывать несколькими способами. Начнем с метода, основанного на встроенных функциях (т.е., без кода VBA). Для конкретики рассмотрим кубическое уравнение х 3 — 8х 2 + х + 42 = 0, которое имеет три решения:
Рис. 2. График функции f(x) = х 3 — 8х 2 + х + 42
Начнем с поиска второго корня х2 = 3. Исходный интервал 0 < х < 5.
Рис. 3. Исходные данные и несколько итераций (формулы см. прикрепленный Excel-файл)
- столбец А – номер итерации;
- столбец В – левая граница интервала поиска решения;
- столбец С – правая граница интервала;
- столбец D – середина интервала;
- столбцы Е, F и G – значения функции на границах интервала и в его середине.
На рис. 3 видно, что десяти итераций было достаточно, чтобы получить значение корня с погрешностью ε < 0,001. Точному решению будет соответствовать ноль в столбце G.
Процедура VBA
Создадим модель, в которой вид функции и интервал поиска решения будут задаваться на листе Excel, а вычисления выполняться с помощью пользовательской процедуры, написанной на VBA. В одну ячейку листа введем «пробную» точку для аргумента функции (например, центральную точку интервала поиска решения), а в другую ячейку введем формулу, которая на основе ячейки с аргументом вычисляет значение функции.
Напрашивается желание создать не процедуру VBA, а функцию. Эта функция в качестве результата могла бы возвращать значение корня уравнения, и у нее могло бы быть, например, пять аргументов:
- ссылка на ячейку с аргументом функции;
- ссылка на ячейку со значением функции;
- два аргумента, определяющие значения для границ интервала поиска решения;
- аргумент, определяющий точность, с которой должен быть вычислен корень уравнения.
Однако, из пользовательской функции нельзя изменить значение в ячейке. Процедура позволяет это сделать. Нам потребуются четыре ячейки на листе Excel: нижняя граница интервала поиска решения, верхняя граница, аргумент функции и значение функции. Расположим их следующим образом:
Рис. 4. Структура листа Excel для процедуры VBA
Точность вычисления корня зададим внутри процедуры. На момент запуска процедуры активной должна быть ячейка с формулой (В2). Вычисленное значение корня уравнения вернется в ячейку с аргументом функции (С2).
Фактически, нам нужна процедура с аргументами. Однако удобнее пользоваться макросом — процедурой без аргументов. Поэтому аргументы процедуре мы передадим неявно, через структуру ячеек листа, отталкиваясь от той ячейки, которая активна на момент запуска макроса.
Код процедуры
Как работает код
Этот макрос – процедура без аргументов. Dim epsilon As Double объявляется числовая переменная epsilon, значение которой определяет погрешность, с которой вычисляется корень уравнения. epsilon = 0,000 001. Далее командой Set cellF = ActiveCell.Range(«A1») ссылка на активную ячейку записываем в переменную cellF. Переменная cellF описана с типом объект Range (команда Dim cellF As Range). В активной ячейке содержится значение функции. Значение в ячейке справа от активной присваивается переменной cellX = cellF.Offset (0,1). В этой ячейке записан аргумент. В числовые переменные а и b первоначально записываются значения левой и правой границ интервала поиска решения: а = cellF.Offset(1,0).Value; b = cellF.Offset(1,1).Value. Чтобы определить значения функции на границах этого интервала, нам необходимо последовательно записать значение границ в ячейку с аргументом функции cellX и запомнить, какое при этом будет значение в активной ячейке (ячейка функции cellF). Значения функции на границах интервала записываются в переменные Fa и Fb.
Если на границах интервала поиска решения значения функции f(x) имеют различные знак, будет запущен процесс вычисления корня. Если значения одного знака, мы вернём в ячейку с аргументом исходное значение (до запуска макроса). Поскольку в ячейке могло быть не только число, но и, например, формула. Начальное значение ячейки мы записываем в переменную start, которая объявлена с типом Variant. Значение присваивается командой start = cellX.FormulaLocal.
Команда cellX.Value = а меняет содержимое ячейки cellX на значение левой границы. Значение функции на левой границе записываем в переменную Fa с помощью команды Fa = celIF.Value. Аналогичная процедура проделывается для правой границы интервала поиска решения. После этого «в игру» вступает условный оператор (а точнее, несколько вложенных условных операторов). С его помощью проверяется необходимое условие для применения метода половинного деления.
Если на левой границе функция равна нулю (условие Fa = 0), в ячейку cellX в качестве результата записывается значение левой границы (команда сеllX.Value = а). Работа макроса завершается. Если на левой границе значение у функции ненулевое, проверяется правая граница. Если значение функции равно нулю, значение правой границы интервала становится решением уравнения и заносится в ячейку cellX.
Если на границах интервала функция принимает значения разных знаков, используем метод половинного деления. Именно здесь выполняется основная часть кода. Вводится две переменные: х – для запоминания текущего приближения для корня уравнения и Fx – значение функции в этой точке.
Вычисление приближенного значения корня уравнения выполняется внутри цикла Do…Until. Цикл выполняется, пока не станет истинным условие Abs(b-a)/20, которое означает, что на левой границе и в центре интервала функция уравнения принимает ненулевые значения одинаковых знаков. В этом случае левая граница переносится в центр интервала (команда а = х), причем новое значение Fx получает и переменная Fa, поскольку она должна содержать значение функции уравнения на левой границе.
Если знаки функции на правой границе и в центре совпадают (условие Fx*Fb>0), аналогичные действия выполняются по отношению к правой границе интервала поиска решения. Возможен также вариант, когда оба указанных условия не выполняются. Это означает, что в центре интервала поиска решения функция принимает нулевое значение. Следовательно, решение найдено, и выполнение макроса прекращается.
Поэкспериментируйте со значениями границ диапазона поиска корня уравнения.
Функция VBA
Хотя я ранее указал на проблемы с использованием функции (а не процедуры), такой подход является более универсальным. Мы создадим две функции VBA: функцию уравнения и функцию, реализующую метод половинного деления. Вторая функция будет получать первую, как аргумент. При этом функция для решения уравнения создается в обычном модуле, а функция, определяющая уравнение – в модуле класса.
Чтобы создать модуль класса, в окне редактора VBA в меню Insert выберите команду Class Module.
Рис. 5. Создание модуля класса
В результате в проект добавляется модуль класса, который отображается в окне проекта Project в группе Class Modules и по умолчанию имеет название Class1. Щелкните на этом названии и переименуйте в Equations.
В M представлено целое семейство временных типов: date, time, datetime, datetimezone и duration. У этих типов много общего, поэтому мы рассмотрим их иначе, чем типы text и number. Сначала мы представим каждый тип и рассмотрим его уникальные аспекты. Затем изучим, как различные типы в этом семействе взаимодействуют друг с другом. И наконец расскажем об общей функциональности, свойственной всем типам в семействе.[1]
Язык М Power Query. Типы logical, null и binary
Эта заметка завершает обзор примитивных типов языка М: текст, число, дата/время, логический, бинарный и null.[1]
Рис. 1. Выражение if возвращает логическое значение
Язык М Power Query. Типы – Число (Number)
В предыдущей заметке мы начали изучать типы значений (литералов) в языке М Power Query. И рассмотрели тип текст (text). Вы можете подумать, что работа с числами настолько простая, что хватит одного абзаца, чтобы ее описать. Однако есть одна тонкость, и если вы не будете осторожны, то арифметика может не дать ожидаемых результатов. Сначала мы рассмотрим синтаксис M для числовых литералов (значений), а потом остановимся на упомянутой тонкости. Кроме числовых литералов, числовой тип в M может быть назначен столбцам. Правильная настройка типа столбца может повысить производительность и сэкономить объем памяти, а также улучшить форматирование по умолчанию, используемое для значений столбца.[1]
Язык М Power Query. Типы – Введение и текст (строки)
Изучите запрос Power Query под микроскопом. Что вы увидите? Данные, пульсирующие между выражениями. Увеличьте масштаб. Вы различите отдельные элементы данных, составляющие этот поток. Элементы данных группируются в зависимости от типа содержащихся в них значений: некоторые содержат текст, другие – дату и время, третьи – значения ИСТИНА или ЛОЖЬ. Конечно же, куда без чисел!?
В языке M поддерживается довольно много типов значений. Типам свойственно особое поведение. Например, для даты и времени существует специальное правило сложения: добавьте время к дате, и вы получите дату-время. Описание всех типов занимает много места. В этой заметке мы рассмотрим специфику текста (строк). Другие типы оставим на потом.[1]
Язык М Power Query. Парадигма
Чтобы лучше понимать, как работает Power Query, давайте отойдем от деталей и рассмотрим парадигму,[1] на которой основан M. Такие детали, как переменные, выражения let, функции и идентификаторы, важны. Но, если мы сосредоточимся только на деталях, мы можем упустить общую картину. Сделаем шаг назад и покажем, как устроен М. Не зная этого, мы будем озадачены необычным его поведением. Удивлены, почему M не позволяет делать некоторые вещи, к которые мы привыкли в других языках.[2]
Язык М Power Query. Переменные и идентификаторы
В предыдущих заметках мы использовали переменные, не вдаваясь в их специфику. Такой подход был основан на предположении, что у вас есть как минимум небольшой опыт работы с другими языками программирования. Однако, переменные в языке M Power Query ведут себя весьма специфически. В настоящей заметке мы глубже изучим концепции, связанные с переменными, постараемся прояснить сделанные ранее неявные допущения.[1]
Рис. 1. Допустимые имена обычных идентификаторов
Крис Уэбб. Введение в язык М Power Query
Крис Уэбб (Chris Webb) является признанным гуру в области Power BI, Power Pivot, Power Query и языка М. Он ведет блог с 2004 года, и за это время написал более 1000 постов. У него около 19 тыс. подписчиков. Крис опубликовал 5 книг, и среди них книга, посвященная Power Query. С момента публикации книги прошло 8 лет и за это время интерфейс Power Query претерпел значительные изменения. Тем, кто только начинает знакомиться с Power Query, я рекомендую более современные издания. Начать можно с краткого введения – Марк Мур. Power Query. А далее изучить Кен Пульс и Мигель Эскобар. Язык М для Power Query, Николай Павлов. Скульптор данных в Excel с Power Query и Гил Равив. Power Query в Excel и Power BI: сбор, объединение и преобразование данных. В то же время, язык М за 8 лет изменился незначительно. Мне представляется, что перевод пятой главы, являющейся введением в язык М, будет актуален и интересен.
Chris Webb. Power Query for Power Bl and Excel. – Apress, 2014. – 268 p.
Язык М Power Query. Функции: вложенность, each, замыкание и рекурсия
Это очередная заметка, посвященная основам языка М Power Query. Ранее мы рассмотрели, как определять и вызывать функции. Но функции также можно создавать налету. Т.е. использовать функции без явного определения и вызова. Для этого в языке М применяется конструкция с ключевым словом each. Это слово часто появляется в коде, сгенерированном интерфейсом редактора запросов. Оказывается, это удобный ярлык для упрощения кода.[1]
Рис. 1. Функция, как аргумент другой функции; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Функции в языке M Power Query
Это перевод статьи Александра Иванова, опубликованной в блоге 4pbi. В статье рассмотрено, как определять, вызывать, передавать и возвращать функции в языке М. Что такое замыкание (closure), как вызывать рекурсивные функции.
Как правило, функция – это фрагмент программного кода, который имеет имя, принимает значения в качестве параметров и возвращает результат. Удобно определять фрагмент кода как функцию, когда вам нужно выполнять одни и те же вычисления много раз в разных частях программы. Функцию не обязательно писать самому. В языке М масса встроенных функций, и вам не нужно знать, что происходит внутри функции. Вам лишь нужно понимать, какие параметры передать функции и какой результат будет возвращен. Итак, функция – черный ящик: мы передаем параметр(ы) и получаем результат.
Рис. 1. Код функции Test; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Ключевое слово each в языке М Power Query
Это сокращенный перевод статьи Маркуса Краучера, опубликованной в блоге excelguru.ca. Изложение ведется от первого лица (Маркуса).
Рис. 1. Фильтрация таблицы на основе each; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Функция y=f(x) задана на некотором отрезке неявно с помощью уравнения вида F(x,y)=0. Вычислить значения функции, заданной уравнением 3*y3+x3-2.3*x*y = 0 для x=-1; -0.9; -0.8 … 0. Для нахождения y использовать метод половинного деления отрезка пополам. Вычисление производить с точностью 0,0001.
В большинстве моих справочников, указанна именно функция одной переменной, а не двух. Может это я не могу в математики и на самом деле это одно и то же, но я думаю что нет. Помогите пожалуйста кто чем может!
Тема уже создавалась и там ссылались на другую более старую тему, которая не сильно то и относится к этому заданию.
Метод половинного деления отрезка пополам для функции, заданной уравнением
Помогите, пожалуйста, если то-то знает как решить эту задачу на VBA (или другом языке.
Найти корни уравнения методом деления отрезка пополам
Всем привет!! Помогите написать программу пожалуйста Вычислить значение выражения по формуле.
Найти корни уравнения методом деления отрезка пополам
Найти корни уравнения методом деления отрезка пополам. Функция x^2. Координаты и точность с клавы.
Найти корни уравнения методом деления отрезка пополам
Вот по этой задаче Найти корни уравнения методом деления отрезка пополам 2sin(3x).
что-то я сомневаюсь в вашей формуле, вернее в том, что вы ее правильно перевели в строку
корень, то есть у=0
подставляем х3=0
итого х=0
фактически вы и имеете функцию одной переменной относительно У т.к. иксы у вас определены и не являются неизвестными (-1 -0.9 . ). А по поводу деления отрезка пополам и я как-то (месяца 4 назад) написал и методику и код для другой задачи. И, естественно, много других людей этим занималось
Добавлено через 4 минуты
shanemac51, там х=0 не предполагается, смотрите условие задачи и корень ищется по У, а не Х. Х3 это не переменная, а Х в кубе.
Это пока вот что у меня есть. Просто я не уверен по заданию, ведь диапазон самого отрезка будет от -1 до 0, верно? Вроде все правильно, а лыжи не едут
720Misty, вы просто не знаете метод деления пополам и думаете, что взять непонятно какой отрезок и дели. Сначала надо найти А и В такие, чтобы F(A) и F(B) имели разные знаки. Можно сначала от некоего значения считать функцию с шагом Н по переменной и найти А и В, а потом делить. Там ещё есть нюансы. почитайте и попытайтесь поиском на форуме найти темы по ключевым словам. Завтра тоже посмотрю
Добавлено через 11 минут
720Misty, корни находятся на участке 0 +1. при нуле функция < 0, при 1 >0 (х = -1)
т.к уравнение кубическое, то могут быть еще два вещественных или комплексных корня
Добавлено через 7 минут
720Misty, чтобы не было кракозябриков, перед копированием кода из VBA переключите клаву на русский. И перед вставкой кода в послание форума здесь тоже переключите на русский и всё будет читаемо
Читайте также: