Как найти производную в экселе: Вычисление производной в Excel | Блог Александра Воробьева

Вычисление производной в Excel | Блог Александра Воробьева

Опубликовано 13 Янв 2016
Рубрика: Справочник Excel | 3 комментария

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

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

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

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

Во второй части статьи на «живом» примере рассмотрим вычисление производной по приближенной формуле численного дифференцирования с применением выражений в конечных разностях и разберемся в вопросе – можно ли используя приближения производных конечными разностями по прогибам балки определять действующие в сечениях нагрузки?

Минимум теории.

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

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

y’(x)=lim (Δy/Δx)  при Δx→0

Геометрический смысл производной функции в точке – это тангенс угла наклона к оси x касательной к графику функции  в этой точке.

tg (α)=Δy/Δx

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

y’(x)_i≈(Δy/Δx)_i=(y_i+1-y_i-1)/(x_i+1-x_i-1)

Конечными разности называют потому, что они имеют конкретное, измеримое, конечное значение в отличие от величин, стремящихся к нулю или бесконечности.

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

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

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

Дано:

На балку длиной 8 метров с шарнирными опорами по краям изготовленную из двух спаренных стальных (Ст3) двутавров 30М опираются 7 прогонов с шагом 1 метр. К центральной части балки крепится  площадка с оборудованием. Предположительно усилие от покрытия, передаваемое через прогоны на балку, во всех точках одинаково и равно F₁. Подвесная площадка имеет вес 2*F₂ и крепится к балке в двух точках.

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

На рисунке ниже показана расчетная схема задачи и общий вид эпюр.

На следующем скриншоте представлены исходные данные.

Расчетные исходные данные:

3. Погонная масса двутавра 30М:

γ=50,2 кг/м

Сечение балки составлено из двух двутавров:

n=2

Удельный вес балки:

q=γ*n*g=50,2*2*9,81/1000=0,985 Н/мм

5. Момент инерции сечения двутавра 30М:

I_x1=95 000 000 мм⁴

Момент инерции составного сечения балки:

I_x=I_x1*n=95 000 000*2=190 000 000 мм⁴

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

R=(q*z_max+8*F₁+2*F₂)/2=(0,985*8000+8*9000+2*50000)/2=85 440 Н

В расчете учитывается собственный вес балки!

Задача:

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

Решение:

Первое, что мы сделаем, это выполним расчет в Excel поперечных сил Q_y, изгибающих моментов M_x, углов поворота U_x оси балки и прогибов V_x по классическим формулам сопромата во всех сечениях с шагом h. (Хотя, в принципе, значения сил и углов нам в дальнейшем не понадобятся. )

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

Использованные в расчетах формулы можно посмотреть здесь.

Ссылка для скачивания файла с рассмотренным в статье примером: vychisleniye-proizvodnoy (xls 250,0KB).

Итак, нам известны точные значения моментов и прогибов.

Из теории мы знаем, что:

Угол поворота – это первая производная прогиба U=V’.

Момент – это вторая производная прогиба M=V’’.

Сила – это третья производная прогиба Q=V’’’.

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

M_xi=V_y’’≈((V_i+1-2*V_i+V_i-1)/h²)*E*I_x

Итог расчетов мы видим в ячейках M5-M54.

Точные значения моментов, рассчитанные по аналитическим формулам сопромата с учетом веса самой балки, отличаются от найденных по приближенным формулам вычисления производных незначительно. Моменты определены весьма точно, судя по относительным погрешностям, рассчитанным в процентах в ячейках N5-N54.

ε=(M_x-V_y’’)/M_x*100%

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

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

Однако…

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

Дело в том, что измерения прогибов требуется выполнять с точностью ~1 мкм и стараться максимально уменьшать шаг замеров h, «устремляя его к нулю», хотя и это может не помочь избежать ошибок.

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

Сегодня есть приборы — лазерные интерферометры, обеспечивающие высокую скорость, стабильность и точность измерений до 1 мкм, программно отсеивающие шум, и еще много чего программно умеющие, но их цена – более 300 000$…

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

Если раньше максимальная ошибка не превышала 0,7%, то сейчас (в сечении i=4) превышает 23%, хотя и остается приемлемой в наиболее опасном сечении (ε₂₁=1,813%).

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

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

Вычисление производной численными методами всегда необходимо выполнять очень осторожно!

Другие статьи автора блога

На главную

Статьи с близкой тематикой

Отзывы

Вычислить производную в Excel по таблицам данных

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

• Производные табличных данных на листе
• Производные от Функция с использованием VBA (или Visual Basic для приложений)

В этом посте я собираюсь сосредоточиться на вычислении производных табличных данных с сообщением о вычислении того же с использованием VBA более поздняя дата.

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

[Примечание: хотите узнать больше о расширенных Методы Excel? Смотрите мое бесплатное обучение только для инженеров. В серии видео из трех частей я покажу вам, как легко решать инженерные задачи в Excel. Нажмите здесь, чтобы начать.]

Классическим примером, конечно же, являются положение и скорость:

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

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

Чтобы использовать метод конечных разностей в Excel, мы вычисляем изменение «y» между двумя точками данных и делим на изменение «x» между теми же самыми точками данных:

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

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

Итак, если мы хотим найти наклон в y ₂ (z), мы могли бы использовать этот расчет:

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

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

Сначала я вычисляю скорость, используя уравнение конечных разностей выше. Поскольку нам нужны y3 и y1, я начинаю вычисление в ячейке E5 и заполняю ее.

[Примечание. Хотите узнать еще больше о передовых методах работы с Excel? Посмотрите мое бесплатное обучение только для инженеров.

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

Затем, используя вычисленную скорость, я могу рассчитать ускорение тем же методом. На этот раз расчет начинается в строке 6.

Теоретически, если мы дифференцируем данные, полученные путем интегрирования, тогда мы должны вернуться к исходным данным. Конечно, все численные методы вносят в данные какую-то ошибку.

Но насколько велика ошибка? Давайте сравним .

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

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

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

Как насчет разгона?

Здесь мы можем видеть, что во время периодов неуклонного увеличения или постоянного ускорения два набора данных очень похожи. Однако, когда в данных ускорения наблюдается разрыв (например, время 0,1, 0,45, 0,5, 0,7 и 0,75 с), ускорение, полученное дифференцированием (оранжевый), не соответствует исходным данным ускорения (синий).

Это связано с уравнением, которое мы использовали для выполнения дифференцирования. Помните, как мы получили производную в точке путем усреднения наклона по обе стороны от этой точки? Мы видим результаты здесь.

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

Использовали ли вы этот метод для некоторых данных? Расскажите мне об этом в комментариях ниже.

[Примечание: Хотите узнать еще больше о продвинутых методах Excel? Смотрите мое бесплатное обучение только для инженеров. В серии видео из трех частей я покажу вам, как легко решать инженерные задачи в Excel. Щелкните здесь, чтобы начать.]

RDF> ->

Как делать производные в Excel | Малый бизнес

Аарон Парсон

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

1. Введите нижний предел горизонтального диапазона, который вы хотите отобразить в ячейке A1. Например, чтобы построить график от -2 до 2, введите «-2» в ячейке A1 (исключая кавычки здесь и на всех этапах).

2. Введите расстояние между точками графика в ячейке D1. Чем меньше расстояние, тем точнее будет выглядеть ваш график, но использование слишком большого количества точек графика может замедлить обработку. Для этого примера введите «0,1», что обеспечит 41 точку графика от -2 до 2. Если вы используете меньший или больший диапазон, соответственно измените расстояние, чтобы получить как минимум несколько десятков точек, но не более нескольких тысяч. . 92.» Обратите внимание, что Excel не умножает соседние термины автоматически, поэтому вам нужно ввести звездочку для умножения.

3. Дважды щелкните маркер заполнения в ячейке B1, чтобы заполнить все необходимые ячейки в столбце B.

4. Введите «=(B2-B1)/$D$1» в ячейке C1. Это уравнение находит производную для вашей формулы в каждой точке, используя определение производной «dy/dx»: разница между каждой строкой в ​​столбце B составляет «dy», а значение, которое вы выбрали для D1, представляет «dx». Дважды щелкните маркер заполнения в C1, чтобы заполнить столбец.

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

6. Щелкните и перетащите от заголовка столбца A к заголовку C, чтобы выделить первые три столбца. Откройте вкладку «Вставка» на ленте и нажмите «Диаграммы», «Распределение», а затем «Рассеивание с плавными линиями» или другой тип точечной диаграммы, если это необходимо. Excel отобразит вашу исходную формулу как «Серию 1», а вашу производную как «Серию 2».

Ссылки

• Microsoft Office: автоматическое заполнение данных в ячейках рабочего листа
• Microsoft Office: создание диаграммы
• Microsoft Office: изменение записей легенды диаграммы a Линейная диаграмма

Советы

• Из-за того, как Excel вычисляет формулы, любая точка «0» в столбцах от A до C может отображаться как чрезвычайно маленькое число в экспоненциальном представлении. Вы можете заменить их на «0» вручную, если хотите, но это не будет иметь существенного значения.
• Чтобы сделать график более наглядным, вы можете переименовать «Серия 1» и «Серия 2». Щелкните правой кнопкой мыши график, нажмите «Выбрать данные», выберите серию для переименования и нажмите «Изменить». Введите новое имя в поле «Имя серии». Excel не позволит вам начинать имя со знака равенства, поэтому, если вы хотите использовать формулу столбца в качестве имени, заключите ее в кавычки.

Предупреждения

• Информация в этой статье относится к Excel 2013, 2010 и 2007. В других версиях она может незначительно или существенно отличаться.

Writer Bio

Аарон Парсон пишет об электронике, программном обеспечении и играх с 2006 года, участвует в создании нескольких технологических веб-сайтов и работает с NewsHour Productions. Парсон получил степень бакалавра гуманитарных наук в Государственном колледже Эвергрин в Олимпии, штат Вашингтон.

Вычисление производной в Excel

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

• Производные табличных данных на рабочем листе
• Производная функция с использованием VBA (или Visual Basic для приложений)

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

Как произвести дифференцирование в Excel

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

Классический пример, конечно, это положение и скорость:

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

Вы изо всех сил пытаетесь найти правильные решения ваших технических проблем в Excel?

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

Производная формула Excel с использованием метода конечных разностей

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

Чтобы использовать метод конечных разностей в Excel, мы вычисляем изменение «y» между двумя точками данных и делим на изменение «x» между теми же точками данных:

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

Лучшей оценкой будет вычисление среднего уклона в интересующей точке путем усреднения уклона непосредственно до и после этой точки.

Итак, если бы мы хотели найти наклон при y ₂ (z), мы могли бы использовать этот расчет:

Эта формула производной известна как центральная конечная разность.

Пример: расчет производной в Excel

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

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

Как рассчитать скорость в Excel

Сначала я вычисляю скорость как производную данных о положении, используя вышеприведенное уравнение конечных разностей. Поскольку нам нужны y3 и y1, я начинаю расчет в ячейке E5 и заполняю ее.

Как вычислить первую производную в Excel

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

Результаты

Теоретически, если мы продифференцируем данные, полученные интегрированием, мы должны вернуться к исходным данным. Конечно, все численные методы вносят какую-то ошибку в данные.

Но насколько серьезна ошибка? Давайте сравним.

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

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

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

Как насчет ускорения?

Здесь мы видим, что в периоды постоянного или стабильного ускорения два набора данных очень похожи. Однако при наличии разрыва в данных об ускорении (т. е. в моменты времени 0,1, 0,45, 0,5, 0,7 и 0,75 с) ускорение, полученное дифференцированием (оранжевый цвет), не соответствует исходным данным ускорения (синий цвет).

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