Как найти производную в 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. Щелкните здесь, чтобы начать.]

6.3.3.Численное вычисление производной функции одного переменного

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

где Δx – очень малая конечная величина.

Найти производную функции Y= 2x 3 + x 2 в точкеx=3. Заметим, что производная приведенной функции в точкеx=3 . Напомним, что производная приведенной функции, вычисленная аналитическим методом, равна 60 – это значение нам понадобится для проверки результата, полученного путем вычисления численным методом в электронной таблице.

Решим задачу двумя способами.

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

После нажатия клавиши Enter получим результат вычисления 60,0000.

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

Используя приведенную технологию численного вычисления производной функции в заданной точке, проверим, является ли найденная точка x= -0,5 точкой экстремума функцииY= X 2 +X +2 . Решение приведено на рис. 6.11

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

Известно, что чем ниже цена (p), тем больше спрос (D) при постоянной покупательной способности населения.

В экономике представляет интерес условие равновесия спроса и предложения.

Если зависимость спроса от цены определяется функцией D=f(p), а зависимость предложения от цены – S=φ(p), то условие равновесия определяется уравнением:

и соответствует точке пересечения кривых D и S. Цена Р₀, при которой выполняется это условие, называется равновесной.

Рассмотрим технологию решения задачи определения точки равновесия на примере.

Зависимость спроса yна некоторый товар от ценыx выражается уравнением f₁(x) =2/x -y + 2, а зависимость предложения от цены – уравнениемf₂(x) = x 2 — y + 1. Требуется, решив систему уравнений, найти точку равновесия в диапазонес точностью 0,001.

Приведем исходные уравнения к системе следующего вида:

Создадим последовательность значений xс шагом 0,2.

Рассчитаем значения функций спроса f(x) и предложения φ(x) для сформированной последовательности значенийx(рис. 33).

Построим графики функций по данным таблицы.

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

Применяя приведенную выше технологию, уточним решение. Результат уточнения приведен на рис. 6.13.

Таким образом, равновесное значение цены составляет 1,521, а спрос и предложение находятся в равновесии и выражаются величиной 3,315.

В экономических задачах одни экономические показатели являются функциями Y=f(x) каких — либо других показателей или величин. Иначе говоря, существует зависимость одних показателей от других –Y=f(x).

Так, например, себестоимость продукции зависит от производимого объема C=f(Q), издержки производства – зависят от количества выпускаемой продукции и т.п.

Предельные экономические показатели характеризуют величину прироста величины функции ΔY от прироста ее аргумента Δx:

Так, например, предельная себестоимость характеризует себестоимость ΔС прироста продукции ΔQ:

Если зависимость ΔY от Δx непрерывна, то приведенное разностное уравнение можно заменить производной .

Пусть зависимость издержек производства от объема выпускаемой продукции в денежных единицах выражается формулой C=20Q – 0,05Q 3

Требуется определить предельные издержки производства при объеме выпускаемой продукции 10 ден. 3.

Скопируем введенную формулу в ячейку В3 (рис.6.14).

В ячейку С3 введем формулу вычисления производной: =(B3-B2)/(A3-A2). В ячейки А2 и А3 введем значения Qдля левой и правой окрестности, соответственно (рис. 6.14).

После выполнения приведенных выше операций в ячейке С2 будет получен результат (рис. 6.15).

Таким образом, предельные издержки производства при объеме выпускаемой продукции 10 ден.ед. составляют примерно 4,99999 ден. ед. Зависимость затрат от объема производства задана приведенной ниже таблицей. Требуется найти предельные издержки производства при объеме выпуска х=1,9.

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

Объем производства

Как вычислить производную от точек данных (x,y) в Excel

В этом примере мы сэмплируем функцию f(x)=x⁢sin(x2)+1 затем вычислите его производную из выборочных точек данных, используя DERIVXY , и сравните результат с аналитическими производными, заданными формулой f'(x)=sin(x2)+2⁢x2⁢cos(x2)

Решение

Используя формулы, показанные в таблице 1, мы генерируем с помощью автозаполнения значения данных (x, y) и аналитические производные для значений x . Числовые значения показано в Таблице 2 ниже:

Таблица 1

	А	В	С
3 92)
6			⇓ Перетащите вниз до строки 20

Таблица 2

	A	B	C
3	x_data	y_data	Analytic der
4	0	1	0
5	0.25	1.015615	0.1872153
6	0.5	1.123702	0.7318602
7	0.75	1.399977	1.4849677
8	1	1. 841471	1.9220756
9	1.25	2.249957	1.0258913
10	1.5	2.16711	-2.0487081
11	1.75	1.138268	-6.0268418
12	2	-0.5136	-5.9859515
13	2.25	-1.1135	2.5335616
14	2.5	0.917052	12.459939
15	2.75	3.634003	5.3043158
16	3	2.236355	-15.988226
17	3.25	-1.94996	-9.773066
18	3.5	-0.08892	22.972966
19	3.75	4.739551	3.0952707
20	4	-0,15161	-30,933007

Используя формулы DERIVXY , показанные в таблице 3, мы генерируем с помощью автозаполнения числовые производные на x -значения и вычислить относительную ошибку по отношению к аналитические производные. />Численные результаты показаны в Таблице 4 ниже.

Обратите внимание, что мы передали определенные имена x_data и y_data для столбцов A4:A20 и B4:B20 соответственно. Это один из способов заблокировать данные, поэтому автозаполнение игнорирует эти аргументы и увеличивает только третий аргумент. В качестве альтернативы мы могли бы использовать знак доллара, $, чтобы заблокировать первые два аргумента во время автозаполнения с помощью формула =ПРОИЗВОДНЫЙXY($A$4:$A$20, $B$4:$B$20, A4) .

Таблица 3

	E	F
3	Numerical Derivatives	% Errors
4	=DERIVXY(x_data,y_data,A4)	1
5	=DERIVXY(x_data ,y_data,A5)	=ABS((E5-C5)/C5)
	⇓ Перетащите вниз до строки 20

Обратите внимание, что ошибки максимальны вблизи конечных точек данных, поскольку сплайн по умолчанию не ограничивает производные в конечной точке. Но в этом примере мы знаем точное производные в концевых точках от аналитической формулы. Мы можем повысить точность, поставив эти данные с помощью ключей ISLOPE и ESLOPE в дополнительном элементе управления аргумент за ПРОИЗВОДНАЯ . Мы называем диапазон K1:L2 end_slopes и определяем следующие пары ключ/значение для начального и конечного наклона. с использованием вычисленных значений аналитических производных в C4 и C20 из Таблицы 2 выше.

6

0020 2 ЭСЛОПА

Мы регенерируем новые производные и ошибки в столбцах h5:h30 и I4:I20 из расширенных формул DERIVXY в таблице 5. Числовые значения, полученные с помощью автозаполнения, показаны в Таблице 6 ниже.

Таблица 5

	H	I
3	НЕМЕРИЧЕСКОЙ ПРОИЗВОДИТЕЛЬНЫЙ ПРОИЗВОДИТЕЛЬНЫЙ 5	=DERIVXY(x_data,y_data,A5,1,end_slopes)	=ABS((H5-C5)/C5)
⇓ Drag down to row 20

УВЕДОМЛЕНИЕ УВЕДОМЛЕНИЕ В ОТВЕРИТЕЛЬНЫМ ОЧЕСТИ С ЗОНА. Имейте в виду, что мы работаем с относительно небольшим размером выборки. Для получения каких-либо дальнейших улучшений потребуется более точная выборка.

Как сделать дифференцирование в Excel (с помощью простых шагов)

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

Скачать рабочую тетрадь

Дифференциал против производной

Пошаговые процедуры для дифференциации в Excel

Шаг 1: Вставьте значения горизонтальной оси

Шаг 2: Найдите значения вертикальной оси

Шаг 3: Рассчитать дифференциацию

Шаг 4: Подготовьте диаграмму дифференциации

Пример: расчет скорости с дифференцированием в Excel

То, что нужно запомнить

Скачать рабочую тетрадь

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

Определение дифференциации

В общем, термин дифференцирование означает скорость изменения между двумя отдельными величинами или значениями. Отношение небольшого изменения одного значения зависит от первого значения, заданного в функции. Основная формула дифференцирования dy/dx , где y=f(x) .

Дифференциал против производной

Дифференциал и Производная — два тесно связанных термина в исчислении. Термин производная означает скорость изменения одной переменной по отношению к другой. Здесь переменных являются изменяющимися объектами.

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

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

Правила дифференциации

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

5. Цепное правило : d/dx[f(g(x))]=f’(g(x))g’(x)

Пошаговые процедуры для выполнения дифференцирования в Excel

Для иллюстрации применим степенное правило дифференцирования в Excel. Давайте пройдем следующую пошаговую процедуру.

Шаг 1: Вставьте значения горизонтальной оси

Сначала мы вставим значения по оси x . Вы можете вставить любое другое значение по своему усмотрению.

• Сначала вставьте значение x в диапазон ячеек B5:B13 .
• Обязательно укажите начальную точку 0 .
• Вместе с ним вставьте значение n .

Шаг 2. Найдите значения вертикальной оси

Теперь мы вычислим значение y для каждого значения x . Здесь мы будем использовать эту функцию для расчета:

Сначала вставьте эту формулу в 9$Е$5

• Затем нажмите Введите .
• Здесь вы увидите первый вывод и .

• Затем используйте инструмент AutoFill , чтобы вставить эту формулу в диапазон ячеек C6:C13 .

Шаг 3. Вычисление дифференциации

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

• Сначала вставьте эту формулу в ячейку D5 .

Здесь dy означает разницу между последним значением и непосредственно предыдущим значением столбца y . Аналогичная функция есть и у dx .

• Затем нажмите Введите .
• Вот и все, вы провели первую дифференциацию.

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

Подробнее: Как вычислить вторую производную в Excel (2 подходящих примера)

Шаг 4. Подготовьте диаграмму дифференцирования

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

• В начале выберите диапазон ячеек B4:B13 и D4:D13 .

• После этого перейдите на вкладку Вставка и щелкните диаграмму Scatter в группе Диаграммы .

• Далее выберите тип диаграммы Scatter with Smooth Lines and Markers из вариантов.

• Вот и все, у вас есть исходный график, основанный на дифференциальном значении и значении x .

• После некоторых изменений конечный результат выглядит так:

Подробнее: Как сделать первую производную диаграмму в Excel (с помощью простых шагов)

Пример. Расчет скорости с дифференцированием в Excel

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

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

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

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

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

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

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

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

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

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

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

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

tg ( α )= Δy / Δx

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

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

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

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

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

На балку длиной 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 мм 4

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

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

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) из таблицы предыдущего раздела статьи, и найдем значения моментов методом численного дифференцирования.

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

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

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

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

Однако.

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

Дело в том, что измерения прогибов требуется выполнять с точностью

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

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

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

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

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

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

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

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