Как найти производную в экселе

Обновлено: 04.07.2024

Пусть функция определена в окрестности точки . Тогда производной функции в точке называется .

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

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

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

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

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

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

Алгоритм нахождения производной

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

1.1. Вводим в диапазон ячеек A3:A23 рабочего листа Excel числа от -10 до 10 с шагом 1, как показано на рисунке. В ячейку C1 вводим число 1. В ячейку B3 вводим формулу для нахождения значений функции при , где , .:

1.2. Копируем формулу до ячейки B23.

2. В каждой точке при оценим значение производной по двухточечной формуле . Изобразим соответствующую ломанную.

2.1. В ячейку C3 водим формулу:

2.2. Копируем формулу до ячейки C23.

2.4. Выделяем диапазон ячеек A2:C23. Задаем команду ВСТАВКА/ДИАГРАММЫ и выбираем тип диаграммы Точечная с прямыми отрезками., как показано на рисунке:

2.5. Нажимаем Ок.

2.6. В результате получиться ломанная как показано на рисунке выше.

3. В каждой точке при оценим значение производной по соответствующей трехточечной формуле. Изобразим ломанную.

3.1. В ячейку D3 водим формулу:

3.2. Копируем формулу до ячейки D23.

3.4. Выделяем диаграмму.

3.5. Из контекстного меню выбираем команду Выбрать данные.

3.6. В окне Выбор источника данных задаем команду Добавить.

3.7. Заполняем окно Изменение ряда как показано на рисунке:

3.8. В результате получиться ломанная как показано на рисунке выше.

4. Составим таблицу точных значений производной при тех же значениях .

4.1. В ячейку E3 водим формулу:

4.2. Копируем формулу до ячейки E23.

4.4. Как показано в пункте 3 в область диаграммы добавляем новые данные из диапазона E3:E23.

4.5. В результате должна получиться диаграмма как показано на рисунке:

5. Сравним точные значения производной с приближенными, построив три ломанные на одном графике.

5.1. Выделяем диапазон ячеек A2:A23, C2:E23, как показано на рисунке:

5.2. Задаем команду ВСТАВКА/ДИАГРАММЫ и выбираем тип диаграммы Точечная с прямыми отрезками. В результате должна получиться диаграмма как показано на рисунке:

6. Повторим вычисления пунктов 1-5, взяв величину шага .

6.1. Для этого на новом листе рабочей книги оформляем таблицу как показано на рисунке:

6.2. В ячейке B3 введена формула:

6.3. В ячейке C3 введена формула:

6.4. В ячейке D3 введена формула:

6.5. В ячейке E3 введена формула:

6.6. В ячейке F3 введена формула:

6.7. Выделяем диапазон ячеек A2:A23, D2:F23/

6.8. Задаем команду ВСТАВКА/ДИАГРАММЫ и выбираем тип диаграммы Точечная с прямыми отрезками. В результате должна получиться диаграмма как показано на рисунке:

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

7.1. На новом листе рабочей книги Excel вводим в диапазон ячеек A4:A103 числа от -50, -49,…,-1,1,2,…,50 с шагом 1, как показано на рисунке (начало таблицы):

7.2. В ячейку C1 вводим число 0. В ячейку E1 вводим число 0,01.

7.3. В ячейку В4 вводим формулу:

7.4. В ячейку С4 вводим формулу:

8. Исследуем зависимость погрешности от , построив график по предыдущей таблице.

8.1. Выделяем диапазон ячеек A3:A103, C3:C103.

8.2. Задаем команду ВСТАВКА/ДИАГРАММЫ и выбираем тип диаграммы Точечная с прямыми отрезками. В результате должна получиться диаграмма как показано на рисунке (см. пункт 9).

9. Повторим шаги 7-8 для погрешности трехточечной формулы .

9.1. В ячейку D4 вводим формулу:

9.2. Выделяем диапазон ячеек A3:A103, C3:D103.

9.3. Задаем команду ВСТАВКА/ДИАГРАММЫ и выбираем тип диаграммы Точечная с прямыми отрезками. В результате должна получиться диаграмма как показано на рисунке:

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

Из вышесказанного известно, что выражение для вычисления производной функции одной переменной в точке x, имеет вид:

где – очень малая конечная величина. То есть вместо выражения можно взять достаточно маленькое число, например, 0,00001.

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

Решение

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

Способ 1

1. Вводим в ячейку C2 рабочего листа заданное значение аргумента, равное 2, в другой ячейке – C3 укажем достаточно малое приращение аргумента – например 0,00001, в ячейке C4 вычисляем сумму C3=C1+C2.

2. В ячейку E3 вводим формулу для вычисления производной:

3. После нажатия клавиши Enter получаем результат вычисления 12,00004 (см. рис.).

Способ 2

1. Зададим окрестность точки достаточно малого размера, например, значение слева =1,99999, а значение справа =2,00001 и введем эти значения в ячейку B3 и B4 соответственно.

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

3. Копируем эту формулу в ячейку C4.

4. В ячейку E3 вводим формулу вычисления производной (рис.):

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

Задание 3. Найти первую производную функции в точке .

Из вышесказанного известно, что выражение для вычисления первой производной функции одной переменной в точке x, имеет вид:

где – очень малая конечная величина. То есть вместо выражения h можно взять достаточно маленькое число, например, 0,00001.

Решение

1. Вводим в ячейку B2 рабочего листа заданное значение аргумента, равное =3*пи()/4, в другой ячейке – B3 укажем достаточно малое приращение аргумента – например 0,00001, в ячейке В3 вычисляем сумму В3=В1+В2.

2. В ячейку E3 вводим формулу для вычисления производной:

3. После нажатия клавиши Enter получаем результат вычисления 0,0000213 (см. рис.).

Задание 4. Найти вторую производную функции в точке . Заметим, что вторая производная приведенной функции в точке , вычисленная аналитическим методом, равна 44 – это значение нам понадобится для проверки результата, полученного путем вычисления численным методом в электронной таблице.

Из математики известно, что выражение для вычисления второй производной функции одной переменной в точке , имеет вид:

Решение

1. Вводим в ячейку B2 рабочего листа заданное значение аргумента, равное 2, в другой ячейке – B3 укажем достаточно малое приращение аргумента – например 0,00001, в ячейке В4 вычисляем сумму В3=В1+В2.

2. В ячейку E3 вводим формулу для вычисления второй производной:

3. После нажатия клавиши Enter получаем результат вычисления 44,00003917 (см. рис.).

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

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

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

Введите формулу «= A1 + $ D $ 1» в ячейку A2. Перетащите маркер заполнения в углу ячейки вниз, чтобы повторить формулу для такого количества точек, которое необходимо для достижения желаемого верхнего диапазона.

Поместите исходную формулу в ячейку B1, начиная со знака равенства и заменив переменную на «A1». Например, чтобы использовать уравнение «y = 2x ^ 2», введите «= 2 * A1 ^ 2». Обратите внимание, что Excel не умножает смежные члены автоматически, поэтому для умножения необходимо ввести звездочку.

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

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

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

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

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

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

Вспомним, что такое производная вообще:

Производной функции f (x) в точке x называется предел отношения приращения Δf функции в точке x к приращению Δx аргумента, когда последнее стремится к нулю:

Вот и воспользуемся этим знанием: будем просто брать для расчета производной очень маленькие значения приращения аргумента, т.е. Δx.

Для того, чтобы найти приближённое значение производной в нужных нам точках (а у нас точки – это различные значения степени деформации ε) можно поступить вот как. Посмотрим еще раз на определение производной и видим, что при использовании малых значений приращения аргумента Δε (то есть малых приращений степени деформации, которые регистрируются при испытаниях) можно заменить значение реальной производной в точке x₀ (f’(x₀)=dy/dx (x₀)) на отношение Δy/Δx=(f (x₀+ Δx) – f (x₀))/Δx.

То есть вот что получается:

Для вычисления этой производной в каждой точке мы производим вычисления с использованием двух соседних точек: первая с координатой ε₀ по горизонтальной оси, а вторая с координатой x₀ + Δx, т.е. одна – производную в которой вычисляем и та, что поправее. Вычисленная таким образом производная называется разностной производной вправо (вперед) с шагом Δx.

Можем поступить наоборот, взяв уже другие две соседние точки: x₀ — Δx и x₀, т.е интересующую нас и ту, что левее. Получаем формулу для вычисления разностной производной влево (назад) с шагом — Δx.

Предыдущие формулы были «левые» и «правые», а есть еще одна формула, которая позволяет вычислять центральную разностною производную с шагом 2 Δx, и которая чаще других используется для численного дифференцирования:

Для проверки формулы рассмотрим простой пример с известной функцией y=x 3 . Построим таблицу в Excel с двумя с столбцами: x и y, а затем построим график по имеющимся точкам.

Производная функции y=x 3 это y=3x 2 , график которой, т.е. параболу, мы и должны получить с использованием наших формул.

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

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

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

А вот и наша красненькая парабола! Значит, формула работает!

Ну а теперь можем перейти к конкретной инженерной задаче, про которую говорили в начале статьи – к нахождению изменения dσ/dε с увеличением деформации. Первая производная кривой «напряжение-деформация» σ=f (ε) в зарубежной литературе называется «скорость упрочнения» (strain hardening rate),а в нашей – «коэффициент упрочнения». Итак, в результате испытаний мы имеем массив данных, которой состоит из двух столбцов: один — со значениями деформаций ε и другой – со значениями напряжений σ в МПа. Возьмем холодную деформацию стали 1035 или наша 40Г (см. таблицу аналогов сталей) при 20°С.

C	Mn	P	S	Si	N
0.36	0.69	0.025	0.032	0.27	0.004

Вот наша кривая в координатах «истинное напряжение — истинная деформация» σ-ε:

Действуем так же, как и в предыдущем примере и получаем вот такую кривую:

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

На этом все на сегодня.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

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

Вспомним, что такое производная вообще:

Производной функции f(x) в точке x называется предел отношения приращения Δf функции в точке x к приращению Δx аргумента, когда последнее стремится к нулю:

Таким образом, для расчета производной будем брать очень маленькие значения приращения аргумента, т.е. Δx.

Для того, чтобы найти приближённое значение производной в нужных нам точках (а у нас точки – это различные значения степени деформации ε) необходимо заменить значение реальной производной в точке x0 (f’(x0)=dy/dx (x0)) на отношение Δy/Δx=(f(x₀+ Δx) – f(x₀))/Δx.

То есть f '( x ₀ ) ≈( f ( x ₀ + Δ x ) – f ( x ₀ ))/Δ x .

Для вычисления этой производной в каждой точке мы производим вычисления с использованием двух соседних точек: первая с координатой ε0 по горизонтальной оси, а вторая с координатой x₀ + Δx, т.е. одна – производную в которой вычисляем и та, что правее. Вычисленная таким образом производная, называется разностной производной вправо (вперед) с шагом Δx.

Можем поступить наоборот, взяв уже другие две соседние точки: x₀ — Δx и x0, т.е интересующую нас и ту, что левее. Получаем формулу для вычисления разностной производной влево (назад) с шагом — Δx.

Д ля поиска экстремумов среди стационарных точек воспользуемся следующим утверждением: функция f(x) имеет максимум в точке х₂ , если вблизи этой точки всем значениям х соответствуют меньшие значения, чем f (х₂) . А если вблизи точки всем значениям х соответствуют большие значения, чем f (х₂) , то функция имеет минимум в точке х= х₂ . По рисунку точка х₂ является точкой экстремума, в данном случае – максимума.

Таким образом, при нахождении локального экстремума воспользуемся условием: если f (х₂)> f (х₁) и f (х₂)> f (х₃), значит точка х₂ является максимумом, если f (х₂)< f (х₁) и f (х₂)< f (х₃), значит точка х₂ – минимум.

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

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

- Объемная поверхностная диаграмма. В объемных поверхностных диаграммах показаны изменения значений по двум измерениям в виде непрерывной кривой. Цветовые полосы в поверхностной диаграмме не отражают наборы данных — они указывают различие между значениями. В диаграмме такого типа используется трехмерное представление данных, которое можно представить в виде резинового листа, натянутого поверх столбцов объемной гистограммы. Как правило, такие диаграммы используются для отображения отношений между большими объемами данных, которые трудно показать иным способом.

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

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

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

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

Читайте также: