Кроме форматирования элементов поля ячеек, строк и столбцов часто бывает полезно использовать несколько рабочих листов Excel. Для систематизации и поиска информации в книге удобно присваивать названиям листов собственные имена, отражающие их смысловое содержание. Например, «исходные данные», «результаты расчетов», «графики» и т. п. Это удобно сделать, воспользовавшись контекстным меню . Нажать правую кнопку мышки на ярлычке листа, Переименовать лист и нажать .
Для добавления одного или нескольких новых листов в меню Вставка выбрать команду Лист. Чтобы вставить сразу несколько листов нужно выделить ярлычки необходимого числа листов, удерживая , затем из меню Вставка выполнить команду Лист . Обратная операция по удалению листов проводится аналогично. Через контекстное меню , где выбирается команда Удалить .
Полезная операция по перемещению листов – левой кнопкой мыши захватить ярлычок листа и переместить его в нужное место. Если при этом нажать , произойдет перемещение копии листа, а к имени листа добавится число 2 .
Задача 7 . Измените формат всей ячейки В2 на: шрифт – Arial 11; расположение – в центре, по нижнему краю; одно слово в строке; формат числа –“0,00”; граница ячейки – двойная линия
2.3. Встроенные функции
Программа Excel содержит более 150 встроенных функций для упрощения расчетов и обработки данных. Пример содержимого ячейки с функцией: =В2+SIN(C7) , где В2 и С7 – адреса ячеек, содержащих числа, а SIN() – имя функции. Наиболее используемые функции Excel:
SQRT(25) = 5 – вычисляет квадратный корень из числа (25) РАДИАНЫ(30) = 0,5 – преобразует 30 градусов в радианы ЦЕЛОЕ(8,7) = 8 – округляет до ближайшего меньшего целого ОСТАТ(-3;2) = 1 – оставляет остаток от деления числа(-3) на
делитель(2). Результат имеет знак делителя. ЕСЛИ(E4>0,2;”доп”;”ошиб”) – если число в ячейке Е4 меньше 0,2,
тогда Excel возвращают “доп” (истина), в противном случае – “ошиб”(ложь).
В формуле функции могут быть вложенными друг в друга, но не более 8 раз.
При использовании функции главным является определение самой функции и ее аргумента. В качестве аргумента, как правило, указывается адрес ячейки, в которой записана информация.
Определить функцию можно, набрав текст (значки, числа и т.п.) в нужную ячейку, либо воспользоваться Мастером функций . Здесь для удобства поиска все функции разбиты на категории: математические, статистические, логические и другие. Внутри каждой категории они отсортированы в алфавитном порядке.
Мастер функций вызывается командой меню Вставка, Функция
или нажатием пиктограммы (f x ). В первом появившемся окне Мастера функций (рис.4) определяем Категорию и название конкретной функции, нажимаем . Во втором окне (рис.5) необходимо определить Аргументы функции . Для этого щелчком кнопки справа от первого диапазона ячеек (Число 1 ) “прикрываем” окно. Выделяем ячейки, на основе которых будет проводиться вычисление. После этого выделенные ячейки будут занесены в окно первого диапазона. Еще раз нажимаем правую клавишу. Если аргументом является несколько диапазонов ячеек, то действие повторяем. Затем для завершения работы нажимаем . В исходной ячейке окажется результат вычисления.
Рис. 4. Вид окна Мастер функций
Рис. 5. Окно для задания аргументов выбранной функции
Задача 8. Найти среднее значение ряда чисел: 2,5 ; 2,9 ; 1,8 ; 3,4 ; 6,1 ;
1,0; 4,4.
Решение . Вводим числа в ячейки, например, С2:С8 . Выделяем ячейку С9 , в которую записываем функцию =СРЗНАЧ(С2:С8), нажимаем , в С9 получаем среднее значение указанных чисел – 3,15 .
Задача 9. Применяя условную логическую функцию ЕСЛИ, составить формулу переименования нечетных числе в «осень», четных – «весна».
Решение . Выбираем столбец для ввода исходных данных – четных (нечетных) чисел, например, A . В ячейку B3 запишем формулу =ЕСЛИ(ОСТАТ(A3;2)=0;”вес”;”осе”) . Копируя ячейку B3 вдоль столбца В, получаем результаты анализа записанных в столбце А чисел. Результаты решения задачи представлены на рис. 6.
Рис. 6. Решение задачи № 9
Задача 10. Вычислить значение функции y = x3 + sinx – 4ex для x = 1,58.
Решение . Разместим данные в ячейки А2 – х , В2 –у. Решение задачи показано на рис.7 в числовом виде слева и формульном – справа. При решении данной задачи следует обратить внимание на вызов функций SIN и экспоненты для ввода аргумента (см. рис.8).
Рис.7. Решение задачи № 10
Рис.8. Окна для ввода аргумента функции SIN и EXP
Задача 11 . Составьте в Excel математическую модель задачи для расчета функции y= 1/ ((x- 3) · (x+ 4)), при значениях x= 3 и у= -4 выводить на экран “неопределена”, численные значения функции – в остальных случаях.
Задача 12 . Составьте в Excel математическую модель задачи: 12.1. для вычисления с корнями
а) √ x3 · y2 · z / √ x·z ; b) (z · √ z)2 ; c) 3 √ x2 · 3 √ x ; d) √ 5·x5 ·3-1 / √ 20·x·3-1
12.2. для геометрических вычислений а) определить углы прямоугольного треугольника, если известны х – катет, у – гипотенуза;
b) определить расстояние между двумя точками в декартовой системе координатах XYZ по формуле
d = (x2 − x1 )2 + (y2 − y1 )2 + (z2 − z1 )2
с) определить расстояние от точки (x 0 ,y 0 ) до прямой a x + b y + c = 0 по формуле
d = a x0 +b y0 +c / √ (a2 +b2 )
d) определить площадь треугольника по координатам вершин по формуле
S = 1 2 [ (x1 − x3 )(y2 − y3 ) − (x2 − x3 )(y1 − y3 )]
3. Решение задач с применением формул и функций
Задач, которые можно успешно решать с использованием формул и функций Excel, на самом деле много. Рассмотрим задачи, которые в практике наиболее часто решаются с применением электронных таблиц: линейные уравнения и их системы, вычисление численных значений производных и определенных интегралов.
Производной функции y = f(x) называется отношения ее приращения ∆y к соответствующему приращению ∆x аргумента, когда
∆x→ 0
y = f (x + x) − f (x)
Задача .13 . Найти производную функции y = 2x 3 + x 2 в точке x=3 .
Решение. Производная, вычисленная аналитическим методом, равна 60 . Вычисление производной в Excel проведем по формуле (1). Для этого выполним последовательность действий:
· Проведем обозначения столбцов: Х – аргументы функции, У – значения функции, У ` – производная функции (рис.9).
· Табулируем функцию в окрестности точки х= 3 с малым шагом, например, 0,001 результаты заносим в столбец Х .
Рис. 9. Таблица вычисления производной функции
· В ячейку В2 введем формулу вычисления функции =2*A2^3+A2^2 .
· Скопируем формулу до строки 7 , получим значения функции в точках табуляции аргумента.
· В ячейку С2 введем формулу вычисления производной =(B3-B2)/ (A3-A2) .
· Скопируем формулу до строки 6 , получим значения производных в точках табуляции аргумента.
Для значения х= 3 производная функции равна значению 60,019, что близко к значению, вычисленному аналитически.
метод трапеций. В методе трапеций область интегрирования разбивается на отрезки с некоторым шагом и площадь под графиком функции на каждом отрезке считается равной площади трапеции. Тогда расчетная формула принимает следующий вид
|
S N = ∫ f (u) du ≈ h N ∑ − 1 [ f (a + h i) + f (a + h (i + 1)) ] (2), |
|
|
2 i = 0 |
где h= (b- a)/ N – шаг разбиения; N – количество точек разбиения.
Для повышения точности количество точек разбиения удваивается, повторно выполняется расчет интеграла. Дробление исходного интервала прекращают, когда достигнута требуемая точность:
интеграла выполним следующие действия:
– выберем N= 5 , в ячейке F2 рассчитаем h- шаг разбиения (рис. 10);
Рис. 10. Вычисление определенного интеграла
· В первом столбце А записываем номер интервала i ;
· В ячейке B2 запишем формулу =3*(2+F2*A2)^2 для расчета первого слагаемого формулы (2);
· В ячейке С2 запишем формулу =3*(2+F2*(A2+1))^2 для расчета второго слагаемого;
· “Протянем” ячейки с формулами на 4 строки вниз по столбцам;
· В ячейку С7 запишем формулу и вычислим сумму слагаемых,
· В ячейке С8 запишем формулу и вычислим SN искомое значение определенного интеграла 19,02 (значение S N полученное аналитически
19).
Задача. 15. Вычислить определенный интеграл:
|
1. Y = ∫ 2 x d x |
2. Y = ∫ 2 x3 dx |
|
−1 |
|
|
2 π |
|||||||||||||||
|
Y = ∫ 2sin(x )dx |
Y = ∫ x2 dx |
||||||||||||||
|
−2 |
|||||||||||||||
|
Y = ∫ |
Y = ∫ |
||||||||||||||
|
3x − 2 |
(2x + 1) 3 |
||||||||||||||
|
x + 3 |
|||||||||||||||
|
Y = ∫ cos |
Y = ∫ |
||||||||||||||
|
x 2 + 4 |
|||||||||||||||
3.2. Решение линейных уравнений
Линейные уравнения в Excel можно решить, используя функцию Подбор параметра. При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение.
Рассмотрим процедуру поиска параметра на простом примере решения линейного уравнения с одним неизвестны.
Задача 16 . Решить уравнение 10 · x – 10 / x = 15 .
Решение. Для искомого значение параметра – x выберем ячейку A3 . Введем в эту ячейку любое число, лежащее в области определения функции (в нашем примере это число не может быть равно нулю). Пусть это будет 3 . Это значение будет использовано в качестве начального. В ячейку, например, B3 в соответствии с приведенным уравнением введем формулу =10*A3-10/A3 . В результате серии расчетов по данной формуле будет отобрано искомое значение параметра. Теперь в меню Сервис, выбрав команду Подбор параметра, запустим функцию поиска параметра (рис.11, а) . Введем параметры поиска:
· В поле Установить в ячейке введем абсолютную ссылку на ячейку $В$3 , содержащую формулу.
· В поле Значение введем искомый результат 15 .
· В поле Изменяя значение ячейки введем ссылку на ячейку А3 , содержащую подбираемое значение, и нажмем .
По окончании работы функции Подбор параметра на экране появится окно Результат подбора параметра , в котором будут отображены результаты поиска. Найденный параметр 2,000025 появится в ячейке А3 , которая была для него зарезервирована.
Обратите внимание на тот факт, что в нашем примере уравнение имеет два решения, а параметр подобран только один. Это происходит потому, что параметр изменяется только до тех пор, пока требуемое значение не будет возвращено. Первый найденный таким образом аргумент и возвращается к нам в качестве результата поиска. Если в качестве
начального значения в нашем примере указать -3 , тогда будет найдено второе решение уравнения: -0,5 .
Рис.11. Решение уравнения: а – ввод данных, б – результат решения
|
Задача 17. Решить уравнения |
||||
|
5х/ 9- 8= 747x/ 12 |
(2x+ 2)/ 0.5= 6x |
|||
|
0,5 (2x- 1)+x/ 3= 1/6 |
7 (4x- 6)+ 3 (7- 8x)= 1 |
|||
|
Систему линейных |
уравнений |
можно решать различными |
способами: подстановки, сложения и вычитания уравнений, с использованием матриц. Рассмотрим способ решения канонической системы линейных уравнений (3) с использованием матриц.
a1 x + a2 y + b1 = 0
a3 x + a4 y + b2 =0
Известно, что система линейных уравнений в матричном представлении записывается в виде:
где А – матрица коэффициентов, X – вектор – столбец неизвестных,
В – вектор-столбец свободных членов. Решение такой системы
записывается в виде
X = A-1 B,
где A -1 –матрица, обратная по отношению к А . Это вытекает из того, что при решении матричных уравнений при X должна остаться единичная матрица Е . Умножая слева обе части уравнения АХ = В на А -1 , получаем решение линейной системы уравнений.
Задача 18. Решить систему линейных уравнений
Решение. Для данной системы линейных уравнений значения соответствующих матрицы и вектора-столбца имеют вид:
Для решения задачи выполним действия:
· А2:В3 и запишем в него элементы матрицы А .
· Выделим блок ячеек, например, С2:С3 и запишем в него элементы матрицы В .
· Выделим блок ячеек, например, D2:D3 для помещения результата решения системы уравнений.
· в ячейку D2 введем формулу = МУМНОЖ(МОБР(А2:В3);С2:С3).
Библиотека Excel в разделе математических функций содержит функции для выполнения операций над матрицами. В частности, это функции:
Параметрами данных функций могут быть адресные ссылки на массивы, содержащие значения матриц или имена диапазонов и выражения.
Например, МОБР (А1: B2) или МОПР (матрица_1).
· Укажем Excel, что выполняется операция над массивами, для этого нажмем комбинацию клавиш + + , в ячейках D2 и D3 будет получен результат х = 2,16667 ; y= – 1,33333 .
4. Решение задач оптимизации
Многие проблемы прогнозирования, проектирования и производства сводятся к широкому классу задач оптимизации. Такими задачами являются, например: максимизация выпуска товаров при ограничениях на сырье для производства этих товаров; составление штатного расписания для достижения наилучших результатов при наименьших расходах; минимизация затрат на транспортировку товаров; достижение заданного качества сплава; определение размеров некоторой емкости с учетом стоимости материала для достижения максимального объема; различные
задачи, в которые входят случайные величины, и другие задачи оптимального распределения ресурсов и оптимального проектирования.
Решение задач такого вида может быть осуществлено EXCEL с помощью инструмента Поиск решения, который расположен в меню Сервис . Формулировка таких задач может представлять собой систему уравнений с несколькими неизвестными и набор ограничений на решения. Поэтому решение задачи надо начинать с построения соответствующей модели. Познакомимся с этими командами на примере.
Задача 20. Предположим, что мы решили производить два вида объективов А и В. Объектив вида А состоит из 3-х линзовых компонентов, вид В – из 4-х. За неделю можно изготовить не более 1800 линз. На сборку объектива вида А требуется – 15 минут, вида В – 30 минут. Рабочая неделя для 4 сотрудников составляет 160 часов. Сколько объективов А и В надо изготовить, чтобы получить максимальную прибыль, если объектив вида А стоит 3500 рублей, вида В – 4800 рублей.
Решение. Для решения этой задачи необходимо составить и заполнить таблицу в соответствии с рис. 12:
· Переименуем ячейку В2 в x , количество объективов вида А.
· А налогично переименуем ячейку В3 в y .
· Целевую функцию Прибыль = 3500*x+4800*y введем в ячейку B5 . · Затраты на комплектацию равны =3*x+4*y введем в ячейку B7 .
· Затраты по времени равны =0,25*x+0,5*y введем в ячейку B8 .
|
Наименование |
|||
|
комплектацию |
|||
|
Затраты по времени |
|||
Рис.12. Заполнение таблицы исходными данными
· Выделим ячейку В5 и выберем меню Данные , после чего активизируем команду Поиск решения . Заполним ячейки этого окна в соответствии с рис.13.
· Н ажмем ; если все сделано правильно, то решение будет таким, как указано ниже.
Для решения многих инженерных задач часто требуется вычисление производных. Когда есть формула, описывающая процесс, сложностей никаких нет: берем формулу и вычисляем производную, как учили еще в школе, находим значения производной в разных точках, и всё. Сложность, наверное, только в этом и состоит, чтобы вспомнить, как вычислять производные. А как быть, если у нас есть только несколько сотен или тысяч строк с данными, а никакой формулы нет? Чаще всего именно так на практике и бывает. Предлагаю два способа.
Первый заключается в том, что мы наш набор точек аппроксимируем стандартной функцией Excel, то есть подбираем функцию, которая лучше всего ложится на наши точки (в Excel это линейная функция, логарифмическая, экспоненциальная, полиномиальная и степенная). Второй способ – численное дифференцирование, для которого нам нужно будет только умение вводить формулы.
Вспомним, что такое производная вообще:
Производной функции f (x) в точке x называется предел отношения приращения Δf функции в точке x к приращению Δx аргумента, когда последнее стремится к нулю:
Вот и воспользуемся этим знанием: будем просто брать для расчета производной очень маленькие значения приращения аргумента, т.е. Δx.
Для того, чтобы найти приближённое значение производной в нужных нам точках (а у нас точки – это различные значения степени деформации ε) можно поступить вот как. Посмотрим еще раз на определение производной и видим, что при использовании малых значений приращения аргумента Δε (то есть малых приращений степени деформации, которые регистрируются при испытаниях) можно заменить значение реальной производной в точке x 0 (f’(x 0)=dy/dx (x 0)) на отношение Δy/Δx=(f (x 0 + Δx) – f (x 0))/Δx.
То есть вот что получается:
f’(x 0) ≈(f (x 0 + Δx) – f (x 0))/Δx (1)
Для вычисления этой производной в каждой точке мы производим вычисления с использованием двух соседних точек: первая с координатой ε 0 по горизонтальной оси, а вторая с координатой x 0 + Δx, т.е. одна – производную в которой вычисляем и та, что поправее. Вычисленная таким образом производная называется разностной производной вправо (вперед) с шагом Δ x .
Можем поступить наоборот, взяв уже другие две соседние точки: x 0 — Δx и x 0 , т.е интересующую нас и ту, что левее. Получаем формулу для вычисления разностной производной влево (назад) с шагом — Δ x .
f’(x 0) ≈(f (x 0) – f (x 0 — Δx))/Δx (2)
Предыдущие формулы были «левые» и «правые», а есть еще одна формула, которая позволяет вычислять центральную разностною производную с шагом 2 Δx, и которая чаще других используется для численного дифференцирования:
f’(x 0) ≈(f (x 0 + Δx) – f (x 0 — Δx))/2Δx (3)
Для проверки формулы рассмотрим простой пример с известной функцией 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 |
Вот наша кривая в координатах «истинное напряжение — истинная деформация» σ-ε:
Действуем так же, как и в предыдущем примере и получаем вот такую кривую:
Это и есть изменение скорости упрочнения по ходу деформации. Что с ней делать, это уже отдельный вопрос.
Чем может помочь 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 1 . Подвесная площадка имеет вес 2*F 2 и крепится к балке в двух точках.
Предполагается, что балка до приложения нагрузок была абсолютно прямой, а после нагружения находится в зоне упругих деформаций.
На рисунке ниже показана расчетная схема задачи и общий вид эпюр.
На следующем скриншоте представлены исходные данные.
Расчетные исходные данные:
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 x 1 *n =95 000 000*2=190 000 000 мм 4
10. Так как балка нагружена симметрично относительно своей середины, то реакции обеих опор одинаковы и равны каждая половине суммарной нагрузки:
R =(q *z max +8*F 1 +2*F 2 )/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. На скриншоте ниже показана половина таблицы, так как значения во второй ее части зеркальны или аналогичны представленным значениям.
Использованные в расчетах формулы можно посмотреть .
Итак, нам известны точные значения моментов и прогибов.
Из теории мы знаем, что:
Угол поворота – это первая производная прогиба U =V’ .
Момент – это вторая производная прогиба M =V’’ .
Сила – это третья производная прогиба Q =V’’’ .
Предположим, что столбец точных значений прогибов получен не аналитическими расчетами, а замерами на реальной балке и у нас больше нет никаких других данных. Вычислим вторые производные от точных значений прогибов, используя формулу (6) из таблицы предыдущего раздела статьи, и найдем значения моментов методом численного дифференцирования.
M xi =V y ’’ ≈((V i +1 -2*V i +V i -1 )/h 2)*E *I x
Итог расчетов мы видим в ячейках M5-M54.
Точные значения моментов, рассчитанные по аналитическим формулам сопромата с учетом веса самой балки, отличаются от найденных по приближенным формулам вычисления производных незначительно. Моменты определены весьма точно, судя по относительным погрешностям, рассчитанным в процентах в ячейках N5-N54.
ε =(M x -V y ’’ )/M x *100%
Поставленная задача решена. Мы выполнили вычисление производной второго порядка по приближенной формуле с использованием центральных конечных разностей и получили отличный результат.
Зная точные значения прогибов можно методом численного дифференцирования с высокой точностью найти действующие в сечениях моменты и определить степень нагруженности балки!
Однако…
Увы, не стоит думать, что на практике легко получить необходимые высокоточные результаты измерений прогибов сложно нагруженных балок!
Дело в том, что измерения прогибов требуется выполнять с точностью ~1 мкм и стараться максимально уменьшать шаг замеров h , «устремляя его к нулю», хотя и это может не помочь избежать ошибок.
Зачастую уменьшение шага замеров при значительных погрешностях измерений прогибов может привести к абсурдным результатам. Следует быть очень внимательными при численном дифференцировании, чтобы избежать фатальных ошибок.
Сегодня есть приборы — лазерные интерферометры, обеспечивающие высокую скорость, стабильность и точность измерений до 1 мкм, программно отсеивающие шум, и еще много чего программно умеющие, но их цена – более 300 000$…
Давайте посмотрим, что произойдет, если мы просто округлим точные значения прогибов из нашего примера до двух знаков после запятой – то есть до сотых долей миллиметра и заново по той же формуле вычисления производной пересчитаем моменты в сечениях.
Если раньше максимальная ошибка не превышала 0,7%, то сейчас (в сечении i =4) превышает 23%, хотя и остается приемлемой в наиболее опасном сечении (ε 21 =1,813%).
Кроме рассмотренного численного метода вычисления производных с помощью конечных разностей можно (а часто и нужно) применить другой способ — замеры степенным многочленом и найти производные аналитически, а затем сверить результаты, полученные разными путями. Но следует понимать, что дифференцирование аппроксимационного степенного многочлена – это тоже в конечном итоге приближенный метод, существенно зависящий от степени точности аппроксимации.
Исходные данные – результаты измерений – в большинстве случаев перед использованием в расчетах следует обрабатывать, удаляя выбивающиеся из логического ряда значения.
Вычисление производной численными методами всегда необходимо выполнять очень осторожно!
Уважаемые читатели, отзывы и комментарии к статье, размещайте в специальном блоке ниже статьи.
Чтобы получать информацию о выходе новых статей на блоге, подпишитесь на анонсы в окне, расположенном вверху страницы или сразу после статьи.
Прошу УВАЖАЮЩИХ труд автора скачать файл с примером ПОСЛЕ ПОДПИСКИ на анонсы статей.
Численное дифференцирование
Раздел № 5
Задача приближенного вычисления производной может возникнуть в тех случаях, когда неизвестно аналитическое выражение для исследуемой функции. Функция может быть задана таблично, или известен только график функции, полученный, например, в результате показаний датчиков параметров технологического процесса.
Иногда, при решении некоторых задач на компьютере, из-за громоздкости выкладок может оказаться более удобным вычисление производных численным методом, чем аналитическим. При этом, разумеется, необходимо обосновать применяемый численный метод, т. е. убедиться в том, что погрешность численного метода находится в приемлемых границах.
Одним из эффективных методов решения дифференциальных уравнений является разностный метод, когда вместо искомой функции рассматривается таблица ее значений в определенных точках, при этом производные приближенно заменяются разностными формулами.
Пусть известен график функции у = f (х ) на отрезке [а ,b ].Можно построить график производной функции, вспомнив ее геометрический смысл. Воспользуемся тем фактом, что производная функции в точке х равна тангенсу угла наклона к оси абсцисс касательной к ее графику в этой точке.
Если х = х 0 ,найдем у 0 = f (x 0)с помощью графика и затем проведем касательную АВ к графику функции в точке (х 0 , y 0) (рис. 5.1). Проведем прямую, параллельную касательной АВ, через точку (-1, 0) и найдем точку у 1 ее пересечения с осью ординат. Тогда значение у 1 равно тангенсу угла наклона касательной к оси абсцисс, т. е. производной функции f (x )в точке х 0:
у 1 = = tgα = f ¢ (x 0), и точка М 0 (х 0 , у 1) принадлежит графику производной.
Чтобы построить график производной, необходимо разбить отрезок [а , b ]на несколько частей точками х i , затем для каждой точки графически построить значение производной и соединить полученные точки плавной кривой с помощью лекал.
На рис. 5.2 показано построение пяти точек М 1, М 2 ,… , М 5 и графика производной.
Алгоритм построения графика производной:
1. Строим касательную к графику функции у = f (x )в точке (х 1 , f (x 1));из точки (-1, 0) параллельно касательной в точке (х 1 , f (x 1)) проведем прямую до пересечения с осью ординат; эта точка пересечения дает значение производной f ¢ (х 1).Строим точку М 1 (х 1 , f ¢ (х 1)).
2. Аналогично построим остальные точки М 2 , М 3 , М 4 и М 5 .
3. Соединяем точки М 1 , М 2 , М 3 , М 4 , М 5 плавной кривой.
Полученная кривая является графиком производной.
Точность графического способа определения производной невысока. Мы приводим описание этого способа только в учебных целях.
Замечание . Если в алгоритме построения графика производной вместо точки (-1, 0) взять точку (-l ,0), где l > 0, то график будет построен в другом масштабе по оси ординат.
5 . 2 .Разностные формулы
а) Разностные формулы для обыкновенных производных
Разностные формулы для приближенного вычисления производной подсказаны самим определением производной. Пусть значения функции в точках x i обозначены через y i :
y i = f (x i ), x i = a+ ih , i = 0, 1, … , n; h =
Мы рассматриваем случай равномерного распределения точек на отрезке [a , b ]. Для приближенного вычисления производных в точках x i можно использовать следующие разностные формулы , или разностные производные .
Так как предел отношения (5.1) при h ® 0 равен правой производной в точке х i , то это отношение иногда называют правой разностной производной в точке x i .По аналогичной причине отношение (5.2) называют левой разностной производной в точке x i .Отношение (5.3) называют центральной разностной производной в точке x i .
Оценим погрешность разностных формул (5.1)–(5.3), предполагая, что функция f (x ) разлагается в ряд Тейлора в окрестности точки x i :
f (x ) = f (x i )+ . (5.4)
Полагая в (5.4) х = x i + h или х = х i – h , получим
Непосредственной подстановкой разложений (5.5) и (5.6) в формулу (5.10) можно получить зависимость между второй производной функции и разностной формулой для производной второго порядка .
Известно, что численными приближенными методами производная функции в заданной точке может быть вычислена с использованием формулы конечных разностей. Выражение для вычисления производной функции одной переменной в точке х k записанное в конечных разностях, имеет вид
где Δх – очень малая конечная величина.
При достаточно малых значениях Δх, можно с приемлемой точностью получить величину производной функции в точке. Для вычисления производной в MS Excel будем использовать приведенную выше формулу. Рассмотрим технологию вычисления производной на примере .
Пример 1.18 Найти производную функции у = 2х 3 + х 2 в точке х=3. Заметим, что производная приведенной функции в точке х=3, вычисленная аналитическим методом, равна 60 – это значение нам понадобится для проверки результата, полученного путем вычисления численным методом.
Задачу вычисления производной в табличном процессоре можно решать двумя способами.
Решение первым способом
Введем в ячейку рабочего листа формулу правой части заданной функциональной зависимости например в ячейку В2, как показано на рисунке, делая ссылку на ячейку, где будет находиться значение х, например А2,
2*А2 ^ 3+А2 ^ 2.
Зададим окрестность точки х=3 достаточно малого размера, например значение слева х k =2,9999999, а значение справа х k +1 =3,00000001, и введем эти значения в ячейку А2 и А3 соответственно. В ячейку С2 введем формулу вычисления производной =(В3-В2)/(А3-А2).
В результате вычисления в ячейку С2 будет выведено приближенное значение производной заданной функции в точке х=3, величина которой равна 60, что соответствует результату, полученному аналитически (рис.1.24).
Решение вторым способом
Введем в ячейку рабочего листа А2 заданное значение аргумента, равное 3, в ячейке В2 укажем достаточно малое приращение аргумента – (1E – 9), в ячейку С2 введем формулу для вычисления производной
=(2*(А2+В2) ^ 3+(А2+В2) ^ 2-(2*А2 ^ 3+А2 ^ 2))/В2.
После нажатия клавиши получим результат вычисления 60,0000.
Как видим, результат получен такой же, как и при первом способе. Приведенный второй способ является более предпочтительным в случаях, когда нужно построить таблицу значений производной функции для заданных значений аргумента.
Вычисление локальных экстремумов функции
Напомним, что функция Y=f(x) имеет экстремум при значении х = х k если производная функции в этой точке равна нулю.
Если функция f(x) непрерывна на отрезке [а, b] и имеет внутри этого отрезка локальный экстремум, то его можно найти, используя надстройку Excel Поиск решения.
Рассмотрим последовательность нахождения экстремума функции на примере
Пример 1.19 Задана неразрывная функция у = х 2 + х + 2. Требуется найти ее экстремум (минимальное значение) на отрезке [-2; 2].
Решение
В ячейку A3 рабочего листа введем любое число, принадлежащее заданному отрезку, в этой ячейке будет находиться значение х.
В ячейку В3 введем формулу, определяющую заданную функциональную зависимость. Вместо переменной х в этой формуле должна быть ссылка на ячейку А3: =А3^2+A3+2.
Выполним команду меню Сервис/Поиск решения.
В открывшемся окне диалога Поиск решения в поле Установить целевую ячейку укажем адрес ячейки, содержащей формулу (В3), установим переключатель Минимальному значению, в поле Измени ячейки укажем адрес ячейки, в которой содержится переменная х-A3.
Добавим два ограничения в соответствующее поле: A3 > = – 2 и A3
 |
Щелкнем на кнопке Параметры и в открывшемся диалоговом окне параметры поиска решения установим относительную погрешность вычислений и предельное число итераций.
Щелкнем на кнопке Выполнить. В ячейке А3 будет вычислено значение аргумента х функции, при котором она принимает минимальное значение, а в ячейке В3 – минимальное значение функции.
В результате выполнения вычислений в ячейке А3 будет получено значение независимой переменной, при котором функция принимает наименьшее значение -0,5, а в ячейке В3 – минимальное значение, равное 1,75.
Построим график заданной функции и убедимся, что решение уравнения найдено, верно.
Примечание. В частном случае при нахождении локального экстремума с использованием рассмотренной технологии, можно получить значение, которое не является экстремумом, а просто является минимумом или максимумом функции в заданном диапазоне изменения аргумента.
Поэтому необходима дополнительная проверка, т.е. вычисление производной функции в найденной точке.
Используя приведенную технологию численного вычисления производной функции в заданной точке, проверим, является ли найденная точка х = -0,5 точкой экстремума функции у = х 2 + х + 2. Решение приведено на рисунке.
Как видно, производная в найденной точке равна нулю, следовательно, найденное значение функции является ее экстремальным значением.
Пример 1.20 Требуется найти значения аргумента в диапазоне [-1; 1], при которых функция у = х 2 + х + 2 имеет экстремумы.
Решение
Табулируем заданную функцию с шагом 0,2.
Применяя второй из приведенных способов вычисления производной, вычислим значения функции у = f(x + dx).
Вычислим значения производной при каждом табличном значении аргумента.
Анализируя полученные значения производных функции в точках, находим, что производная меняет знак в интервале значений аргумента (-0,6;-0,4), следовательно, на этом интервале есть точка экстремума. Кроме того, заметим, что знак производной меняется с минуса на плюс, следовательно, точка экстремума является минимумом функции.
Применяя инструмент Подбор параметра или Поиск решения для решения уравнения Y(x) = 0
 |
относительно х, вычислим точное значение аргумента, при котором исходная функция принимает экстре малыше значение (-0,5) (рис. 1.26).
Полученное значение производной исследуемой функции в точке х =-0,5 равно нулю, следовательно, в этой точке функция имеет экстремум.