Метод касательных в excel

Обновлено: 06.01.2025

В статье исследован метод касательных , применяемый для разделения на группы при проведении АВС-анализа. Указана связь данного метода с принципом Парето . По результатам исследования предложены два подхода для реализации в MS EXCEL , простота которых позволит легко использовать их на практике. Актуальность работы обусловлена проникновением логистических методов во все сферы деятельности предприятий Беларуси. Ее результаты будут использованы в управлении запасами, работе с клиентами и поставщиками и других функциональных областях логистики.

Implementation of the tangents method to be used in MS EXCEL for dividing into groups in ABC-analysis

The tangents method used for dividing into groups in ABC-analysis has been considered. The relation between this method and Pareto’s principle has been found out. The simplicity of these approaches makes them easily applied into practice.

Текст научной работы на тему «Реализация метода касательных в MS Excel для разбиения на группы при АВС-анализе»

При изучении науки примеры полезнее правил.

Реализация метода касательных в MS EXCEL для разбиения на группы при ABC-анализе

Резюме. В статье исследован метод касательных, применяемый для разделения на группы при проведении ABC-анализа. Указана связь данного метода с принципом Парето. По результатам исследования предложены два подхода для реализации в MS EXCEL, простота которых позволит легко использовать их на практике. Актуальность работы обусловлена проникновением логистических методов во все сферы деятельности предприятий Беларуси. Ее результаты будут использованы в управлении запасами, работе с клиентами и поставщиками и других функциональных областях логистики.

Ключевые слова: ABC-анализ, принцип Парето, разделение на группы, кривая Лоренца, метод касательных, MS EXCEL.

инженер ОДО «Титан-дент»

-анализ широко используется в различных областях экономики: при управлении запасами, выборе поставщиков, работе с клиентами и т.п. [1, 2]. Он основан на эмпирическом законе Парето (принципе 80/20), согласно которому наибольший вклад в суммарный результат 5 какой-либо деятельности или активности дают усилия небольшого числа ее участников [1]. Под участником (игроком) будем понимать любой объект или субъект, вклад которого учитывается в суммарном итоге. Фольклорная формулировка принципа Парето (ПП): 80% результата дает деятельность 20% игроков. В ПП полагается, что функция результата является аддитивной от вкладов участников, количество которых N.

При проведении ЛБС-анализа рекомендуют придерживаться следующего алгоритма:

■ формулировка цели анализа;

■ установление показателей для дифференциации участников;

■ получение данных для анализа;

■ оценка участников по выбранным показателям;

■ упорядочение игроков согласно величинам показателей;

■ разделение участников на группы;

■ экономическая интерпретация результатов анализа, принятие управленческих решений и т.п. [1,3].

Нами будет рассмотрен случай одного показателя. При этом упорядочение участников обычно осуществляется по убыванию величины показателя 4. Каждому участнику присваивают номер, начиная с игрока с наибольшим значением показателя. Далее по полученным упорядоченным значениям находят для каждого участника показатель с нарастающим итогом. Строят в прямоугольной декартовой системе координат кривую Лоренца (КЛ) [5] зависимости этого показателя у от номера участника х. При построении кривой учитывают точку с координатами (0;0) - нулевой вклад от фиктивного («нулевого») участника. Часто эту кривую строят на основании данных в процентах. Для получения таких данных значения показателя с нарастающим итогом делят на значение для последнего участника

(суммарный результат 5) и умножают на 100%. Аналогично поступают с номерами участников: все их делят на максимальный номер N и результаты умножают на 100%. Используя полученную кривую, тем или иным способом [1, 3, 4, 6, 7] производят разбиение множества участников на три непересекающихся подмножества (группы) А, В и С.

Методов такого разбиения достаточно много [3, 4, 6, 7]. Мы упомянем эмпирический и метод касательных. Внимание сконцентрируем на последнем, который можно реализовывать аналитически и графически [4].

При графической реализации (рис. 1) начальную и конечную точки кривой Лоренца соединяют отрезком прямой. Далее на этой кривой находят точку (хА; уА), касательная в которой параллельна построенному отрезку прямой. Абсцисса хА найденной точки разделяет группы А и В и сама включается в группу А. Чтобы разделить оставшихся участников на группы В и С, найденную точку (хА; уА) соединяют отрезком прямой с крайней правой точкой КЛ. На участке КЛ, лежащем правее (хА; уА), находят точку (хв; ув), в которой касательная к кривой параллельна второму отрезку. Абсцисса хв этой точки разделяет группы В и С и сама включается в группу В. Если абсциссы точек (хА; уА) и (хв; ув) не принадлежат экспериментальным данным, то эти абсциссы не включаются в соответствующую группу. Отметим, что графическое разделение на группы А, В и С подразумевает аппроксимацию экспериментальных данных некоторой кривой [4].

Подобная аппроксимация экспериментальных данных функциональной зависимостью осуществляется при аналитической реализации метода касательных [4, 6]. Пусть аппроксимирующая функция имеет вид у = /(х), причем а < х < Ь,/(а) < у < /(Ь) (а, Ь - левая, правая границы области задания функции у = /(х)). Если эмпирические данные нормированы на 100%, то 0 < х < 1; 0 < у < 1 (/(0) = 0;/(1) = 1).

Уравнение прямой, содержащей первый отрезок, имеет вид

в случае нормирования данных оно принимает вид

Если функция у = /(х) непрерывна на [а; Ь] и дифференцируема в (а; Ь), то, согласно теореме

Лагранжа [8], существует точка хА е [а; Ь], в которой производная

в случае нормирования -

Точка хА разделяет группы А и В.

Уравнение прямой, содержащей второй отрезок, имеет вид

при нормировании данных это уравнение принимает вид

По той же теореме Лагранжа существует точка хв е [хА; Ь], в которой производная

при нормировке данных

Точка хв разделяет группы В и С.

Следовательно, при аналитическом подходе необходимо: аппроксимировать экспериментальную зависимость аналитической, например методом наименьших квадратов [9]; решить аналитически или численно уравнения (2) и (4) относительно хА и хв соответственно.

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

Графическая реализация метода касательных

□ rtinueft tHXCl - АН ^injxfl

QUHA ÇHM BcTfpra -ÎKffraт CfftàK i^pjMia . s

6 С D Е F а H t j —

Me üt Средний Дал* ДНА* Diptlli1 F-8I-ÏS Отрезок 7 t i6*3S

I1ÖIIIU позиции □satt пи ПЕТИЦИИ в нлрлстлщ GB:C3S 116:138

F VB È s !■□□ Ш|Н1ЛП руб. JtRtt*,% цкн Hivrill, %

• 0 B.OOS 0 OS BS «JOBS (1 JOBS Г

1 ИЗ»; п по tSJSil IS.« s э.ээч

m 1 Ii DM 14 sn .34 ¿БЪ 77JS9%fl

il i w,dos 13ИШ 11 .sn 4S.18* 1DJDDS

il 1 13J3S 11I15D 9.44 Ii 5S>7vi 13J3* 17-79ÎH

и 5 16,61 S sdiid /,6Я% i3J1vi Ifijfi/S JGjGhSH

H 7 73,33% TiJ-nn till 74J7*

1« 8 SDIin 3,47* TT£i 14 2М7Ч 71,544 0,1114

IF Я 3n,ms ЗНШ 7.3614 НП.11Г: зи (in=; А.ПЧ 78,564 1,!U4

14 10 зэлэя ляш 7,1414 Î7.7JS 33,33s ja^DS 7.SC4

H 11 36.67 S 73ЯП 7.I3IS 84.78Ü KJÎ7S 47JBti 00.604 Ï.6D4

» и 4B.0ÛS 2250 1,924 06J0S 40JÛBS вшч 4,904

1î «ЛЯ 2 НИ Ш SS JOS J3.3JS В?.64Ч 4.И4

:i и 46.674 1ини 1.61 S 09JttS ШП ез.ьйч 3,94%

в 15 5b.oos 1660 1.42S 91JI2S 90 JOBS 41JHS В1.60Ч 6.944

i* 16 HJMt ими 1 JAS 92.224 м.т 6.414

1С 17 MÎT* 1200 1.99 S МЛ* ВЯ1

IF 19 ébjîs 990 B.B4S 95.10* 63.HS Bft.ri4 6.944

a ît> 66.614 su B.T5S 95 Л6* 66j67S 09.794 6.B74

11 21 7B.oos 761 B.KS 96.51* 71 JOBS 90.В14 4.H4

» iî 7J.3ÎS iBU BAS 97J»S n.ns 91ЛЗК 4JÎ4

51 г ï 76.674 fût e.Jias 97.96* 76j67S 92.954 4.714

3d 24 OB.oûs 500 M» 97.99* MJOBS 93.S74 4.124

» 24 ojjïs (60 B.Î9* 90 J9* 93,33* 1ÎJ0SS 91.394 3.494

5* n 86.67^ 440 9e.re* R6.S7S 17Л9=; 35.974

* 27 moos 4« B.ÎJS 99.10S »JOBS 9.IBS 96.944 2,174

И № 9UH збо um 99.41S 93.33S G JOSS 97,964 1,454

)f 29 46,67 S 3» В,90S 99.7114 EW.É7S 3J01S 90.984 B,H4

i Не можете найти то, что вам нужно? Попробуйте сервис подбора литературы.

» 11 пни [HS*- »J07S 6,904

4 T H ■. fhcTl / (VKTS / IVKTS 1jNort /

Рис. 2. Реализация метода касательных в MS EXCEL (первый подход)

дальнейшего построения аналогичных (3) отрезков и отыскания касательных к кривой, параллельных этим отрезкам.

Приведем доводы о тесной связи метода касательных с ПП. Согласно последнему вклад Sa, который больше половины общего результата S, дают участники, количество Na которых меньше половины общего их числа N. Тогда средняя скорость нарастания результата для этой доли участников равна Sa/Na > (S/2)/(N/2) = S/N, где S/N - средняя скорость нарастания результата для всего множества участников. S/N равна угловому коэффициенту первого отрезка (рис. 1). Тогда абсцисса xA точки касания разделяет участников, дающих скорость роста результата большую или равную S/N и меньшую S/N. Под этой скоростью, даваемой одним участником, понимаем отношение вклада этого участника к единице (один участник). В силу ранжирования исходных данных эти две группы находятся по разные стороны от точки xA (абсцисса xA может относиться к группе A).

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

этого роста на рассматриваемом участке КЛ. Непосредственная реализация метода требует, как указывалось выше, либо построения КЛ и нахождения графически касательной к ней, либо применения какого-либо математического пакета, например MathCAD, при аналитическом подходе [6].

Мы предлагаем использовать возможности Microsoft EXCEL. Для обоснования этого подхода вновь обратимся к теореме Лагранжа, точнее к ее доказательству [8]. При этом доказательстве вводится функция

то есть в рассматриваемом нами случае функция разности уравнения кривой Лоренца и уравнения отрезка (1). Из (6) сразу получаем, что F(a) = F(b) = 0. Поскольку функция F(x) непрерывна на [a; b], дифференцируема в (a; b) и принимает на концах [a; b] одинаковые значения, то для F(x) выполняется утверждение теоремы Ролля [8]: существует точка xA е [a; b], в которой F(x) = 0. По построению функция f(x) монотонно возрастает, тогда возможны два вида графика функции F(x):

■ все участники делают различные вклады в результат, поэтому график F(x) имеет единственный максимум в точке хл - граничной для групп A и B;

■ есть совокупность участников, дающих одинаковые вклады; тогда график функции F(x) имеет горизонтальный участок (плато), ординаты точек которого превосходят ординаты всех остальных точек графика.

Однозначное разделение на группы методом касательных возможно при первом виде графика функции F(x); функции с такими графиками и будут представлены в примерах. Замечание по поводу второго вида графика функции F(x) приведем в конце данной статьи.

Таким образом, для реальных дискретных экспериментальных данных в Microsoft EXCEL необходимо находить максимумы функций вида (6), построенных на соответствующих интервалах.

Конкретный пример реализации метода касательных в MS EXCEL. Для него взяты тридцать первых позиций табл. 3.1 из [4]. Расчеты в нормированных величинах представлены на листе EXCEL (рис. 2). В столбцах B8:B38 и D8:D38 содержатся исходные данные (учтен также «нулевой» участник - строка B8:H8), в столбцах C8:C38 и E8:E38 - нормированные (общий результат в 117 040 использован при нормировке данных из столбца D8:D38). Определяем границу

групп А и В. Столбец С8:С38 содержит ординаты точек первого отрезка (1а) (угловой коэффициент К = 1 из ячейки СБ), столбец Н8:Н38 -значения функции (6). Максимальная величина в этом столбце - в ячейке Н16. Тогда, согласно приведенной выше интерпретации метода касательных, количество участников группы А составляет 26,67% (ячейка С16) от общего их числа; они дают 77,54% (ячейка Р16) конечного результата, то есть хА = 26,67%, /(хА) = 77,54%. Теперь определяем границу групп В и С. В столбец 116:138 помещены ординаты точек второго отрезка (3а) (угловой коэффициент К2 = = (1 - /(ха))/(1 - хА) = (1-0,7754)/(1-0,2667) = 0,3063 из ячейки РБ), в столбец Лби38 - значения функции (6), максимальное значение - в ячейке 116. Таким образом, хв = 56,67% (ячейка С2Б), /(хВ) = 93,31% (ячейка Р2Б). Поэтому группа В содержит 56,67% - 26,67% = 30% участников и дает 93,31% - 77,54% = 15,77% общего результата. Группа С содержит 100% - 56,67% = 43,33% игроков и дает 100% - 93,31% = 6,69% от общего результата. Итак, получили разделение на группы:

Показатель Группа A Группа B Группа C 1

Доля участников, % 26,67 30,00 43,33

Доля от общего результата, % 77,54 15,77 6,69

Приведем еще один способ реализации метода касательных в MS EXCEL. Для этого обратимся к функцииf(x). Исходя из определения, она может быть представлена в виде:

/(и) = jV(z)ife (0 < u < N),

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

Для нахождения хА берем соотношение (2). Выпишем его, используя (7):

собой среднее значение (<p(z))i функции вклада всех N участников.

Аналогично после отделения группы А находим хВ, используя (4) и (7):

- среднее значение вкладов участников с номерами из (ха; N].

Исходя из вышеизложенного, реализация в MS EXCEL такова. Для всех участников, кроме «нулевого», находим среднюю величину вклада. К группе A отнесем игроков с вкладами, большими либо равными среднему. Для оставшихся участников вновь находим средний вклад. К группе B отнесем тех из них, чьи вклады больше либо равны второму среднему вкладу. После отделения групп A и B получаем

Рис. 3. Реализация метода касательных в MS EXCEL (второй подход)

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

Продемонстрируем реализацию в Microsoft EXCEL указанного подхода (рис. 3). Используем те же исходные данные, что и в первом примере; они содержатся в ячейках C4:C33 и D4:D33. Нормированные величины помещены в ячейки B4:B33 и E4:E33; для нормирования значений из E4:E33 взят конечный результат в 117 040 (ячейка D34). Средний вклад по всем участникам находится в D35 (для нормированных данных - в E35). Представители группы A отражены в ячейках B4:B11 и составляют 26,67% от всех участников; эти игроки дают 77,54% от общего результата. Средний вклад для оставшихся после выделения группы A участников находится в D36 (для нормированных данных - в E36). Представители группы B (в ячейках B12:B20) составляют 56,67% - 26,67% = 30% от всех участников и дают 93,31% - 77,54% = 15,77% от общего результата. Тогда группа C содержит 100% - 56,67% = 43,33% участников и дает 100% -93,31% = 6,69% от общего результата. На листе EXCEL группы выделены цветом с помощью опции «Условное форматирование».

Разбиение на подмножества, полученное при таком подходе, естественно совпадает с разбиением в результате первого подхода, поскольку оба - суть реализации в EXCEL одного и того же метода касательных. Отметим одну особенность последнего. Если кривая Лоренца имеет участок, параллельный первому отрезку (это соответствует второму виду графика функции F(x)), то метод касательных даст не одну точку (ха; уА), а весь этот участок. Такая же ситуация возможна и на шаге разделения групп B и C,

а также на других шагах (если они будут предприняты). Это качество относят к недостаткам метода [5], так как оно не позволяет однозначно провести разделение на группы. Наличие прямолинейного участка на КЛ свидетельствует о присутствии нескольких игроков с равными вкладами. Решение об их включении (полном или частичном) или невключении в ту или иную группу должен принимать исследователь, воспользовавшись другим методом, например эмпирическим [4].

Таким образом, изучен метод касательных. Указан признак, согласно которому осуществляется разделение на группы при его применении. Представлена связь метода с эмпирическим принципом Парето, по результатам анализа предложены два подхода для реализации в MS EXCEL. Их легко применять на практике, поскольку они освобождают лицо, проводящее ABC-анализ, от необходимости осуществлять какие-либо графические построения или использовать продвинутые математические пакеты при аналитическом определении границ групп. СП

1. Гаджинский А. М. Логистика.- М., 2012.

2. Стерлигова А. Н. Управление запасами широкой номенклатуры: с чего начать? // Логинфо. 2003, №12. С. 50-55; 2004, №1. С. 46-49.

4. Лукинский В. С. Логистика / В. С. Лукинский, И. А. Цвиринько, Ю. В. Малевич.- СПб., 2003.

5. Gudehus T. Comprehensive Logistics, 2-nd edition / T. Gudehus, H. Kotzab.-Berlin, Heidelberg. 2012.

6. Шмидт А. К вопросу о методах выделения групп при проведении ABC-анализа // Логистика. 2013, №8. С. 22-27.

8. Ильин В. А. Основы математического анализа. Ч. 1 / В. А. Ильин, Э.Г. Позняк.- М., 1971.

9. Бородич С. А. Эконометрика: уч. пособие / 2-е изд., испр.- Мн., 2004.

- «НАУКА И ИННОВАЦИИ» в электронной базе РИНЦ

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

" В отличие от метода хорд, в методе касательных вместо хорды на каждом шаге проводится касательная к кривой y=F(x) при x=x_n и ищется точка пересечения касательной с осью абсцисс:

Формула для (n+1) приближения имеет вид:

Если F(a)*F"(a)>0, x₀=a, в противном случае x₀=b.

Итерационный процесс продолжается до тех пор, пока не будет обнаружено, что:

Пусть дана задача следующего характера: Уточнить корни уравнения cos(2x)+x-5=0 методом касательных с точностью до 0,00001.

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

Изначально необходимо определиться с тем, чему равно x0: либо a, либо b. Для этого необходимо выполнить следующие действия:

Найти производную первого порядка от функции f(x)=cos(2x)+x-5. Она будет выглядеть следующим образом: f1(x)=-2sin(2x)+1.

Найти производную второго порядка от функции f(x)=cos(2x)+x-5. Она будет выглядеть следующим образом: f2(x)=-4cos(2x).

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

В итоге получается следующее:

Так как x0=b, то необходимо выполнить следующие действия:

В ячейку A6 ввести формулу =D5.

Выделить диапазон ячеек B5:E5 и методом протягивания заполнить диапазон ячеек B6:E6.

Выделить диапазон ячеек A6:E5 и методом протягивания заполнить диапазон нижерасположенных ячеек до получения в одной из ячеек столбца E результата (диапазон ячеек A6:E9).

В итоге получаем следующее:

Ответ: Корень уравнения cos(2x)+x-5=0 равен 5,32976.

4. Комбинированный метод хорд и касательных

Для того чтобы достичь наиболее точной погрешности, нужно одновременно использовать методы хорд и касательных. "По формуле хорд находят x_n+1 , а по формуле касательных - z_n+1 . Процесс нахождения приближенного корня прекращается, как только:

В качестве приближенного корня берут значение, равное (11):"[2]

Пусть требуется уточнить корни уравнения cos(2x)+x-5=0 комбинированным методом с точностью до 0,00001.

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

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

Для формул хорд обозначить:

- Переменная c будет играть роль a или b в зависимости от ситуации.

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

Для формулы касательных обозначить:

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

Найти производную первого порядка от функции f(x)=cos(2x)+x-5. Она будет выглядеть следующим образом: f1(x)=-2sin(2x)+1.

Найти производную второго порядка от функции f(x)=cos(2x)+x-5. Она будет выглядеть следующим образом: f2(x)=-4cos(2x).

В итоге получается следующее:

В ячейку G1 ввести e, а в G2 ввести число 0,00001.

В ячейку H1 ввести c, а в H2 ввести число 6, так как c=b (см. ячейку F2).

В ячейку I1 ввести f(c), а в I2 ввести формулу =COS(2*H2)+H2-5.

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

В ячейку A6 ввести формулу =E5.

В ячейку F6 ввести формулу =I5.

Выделить диапазон ячеек B5:E5 и маркером автозаполнения заполнить диапазон ячеек B6:E6.

Выделить диапазон ячеек G5:K5 и маркером автозаполнения заполнить диапазон ячеек G6:K6.

Выделить диапазон ячеек A6:K6 и методом протягивания заполнить все нижестоящие ячейки до получения ответа в одной из ячеек столбца K (диапазон ячеек A6:K9).

Дано уравнение F(x)=0 . Это - общий вид нелинейного уравнения с одним неизвестным. Как правило, алгоритм нахождения корня состоит из двух этапов:

1. Отыскание приближенного значения корня или отрезка на оси абсцисс, его содержащего.

2. Уточнение приближенного значения корня до некоторой точности.

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

Шаговый метод

В качестве примера рассмотрим уравнение x 2 - 11 x + 30 = 0. Интервал поиска [3,5.4], шаг h = 0,3. Решим его, используя специальные возможности пакета Excel. Последовательность действий (см. рис. 1):

1. Оформить заголовок в строке 1 «Численные методы решения нелинейных уравнений».

2. Оформить заголовок в строке 3 «Шаговый метод».

3. В ячейки A6 и C6 и B6 записать данные по задаче.

4. В ячейки B9 и C9 записать заголовки рядов - соответственно x и F(x).

5. В ячейки B10 и B11 ввести первые два значения аргумента - 3 и 3.3.

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

7. В ячейку C10 ввести формулу «=B10*B10-11*B10+30».

8. Скопировать формулу на остальные элементы ряда, используя прием протаскивания. В интервале C10:C18 получен ряд результатов вычисления функции F(x). Видно, что функция один раз меняет знак. Корень уравнения расположен в интервале [4.8,5.1].

9. Для построения графика зависимости F(x) используем Вставка - Диаграмма (тип «Точечная», маркеры соединяются гладкими кривыми).

Метод деления отрезка пополам

В качестве примера рассмотрим уравнение x 2 - 11 x + 30 = 0. Интервал поиска [3,5.4], с точностью ε=0.01. Решим его, используя специальные возможности пакета Excel.

1. Ввести в ячейку B21 заголовок «Метод деления отрезков пополам».

2. Ввести в ячейку A23, C23, E23 данные по задачи.

3. В области B25:H25 оформить заголовок таблицы (ряд B - левая граница отрезка «a», ряд C - середина отрезка «x», ряд D - правая граница отрезка «b», ряд E - значение функции на левой границе отрезка «F(a)», ряд F - значение функции на середине отрезка «F(x)», ряд G - произведение «F(a)*F(x)», ряд H - проверка достижения точности « ê F(x) ê <е».

4. Ввести первоначальные значения концов отрезка: в ячейку B26 «4.8», в ячейку D26 «5.1».

5. Ввести в ячейку C26 формулу «=(B26+D26)/2».

6. Ввести в ячейку E26 формулу «=B26*B26-11*B26+30».

7. Ввести в ячейку F26 формулу «=C26*C26-11*C26+30».

8. Ввести в ячейку G26 формулу «=E26*F26».

9. Ввести в ячейку H26 формулу «=ЕСЛИ(ABS(F26)<0.01; ² корень ² )».

Метод касательных (Ньютона)

В качестве примера рассмотрим уравнение x 3 +2 x 2 +3x+5= 0. Точность ε=0.01. Решим его, используя специальные возможности пакета Excel.

1. Ввести в ячейку J23 заголовок «Метод касательной (Ньютона)».

2. Ввести в ячейку L23 текст «е=», а в ячейку M23 значение точности «0.00001».

3. В области K25:N25 оформить заголовок таблицы (ряд K - значение аргумента «x», ряд L - значение функции «F(x)», ряд M - производная функции «F ¢ (x)», ряд N - проверка достижения точности « ê F(x) ê <е».

4. В ячейку K26 ввести первоначальное значение аргумента «-2».

5. Ввести в ячейку L26 формулу «=K26*K26*K26+2*K26*K26+3*K26+5».

6. Ввести в ячейку M26 формулу «=3*K26*K26+4*K26+3».

7. Ввести в ячейку N26 формулу «=ЕСЛИ(ABS(L26)<$M$23;"корень")».

8. Ввести в ячейку K27 формулу «=K26-L26/M26».

Метод хорд

1. Ввести в ячейку B32 заголовок «Метод хорд».

2. Ввести в ячейку C34 текст «е=», а в ячейку E34 значение точности «0.00001».

3. В области B36:D36 оформить заголовок таблицы (ряд B - значение аргумента «x», ряд C - значение функции «F(x)», ряд D - проверка достижения точности « ê F(x) ê <е».

4. В ячейку B37 и B38 ввести первоначальное значение аргумента «-2» и . «-1»

5. Ввести в ячейку С37 формулу «=B37*B37*B37+2*B37*B37+3*B37+5».

Метод простой итерации

В качестве примера рассмотрим уравнение x 2 - 11 x + 30 = 0. Интервал поиска [4.8,5.1], с точностью e =0,05.

1. Ввести в ячейку K32 заголовок «Метод простой итерации»

2. Ввести в ячейку N34 текст «е=», а в ячейку O34 значение точности «0,05».

3. Выбрать функцию j (x), удовлетворяющую условию сходимости. В нашем случае такой функцией является функция S(x)=(x*x+30)/11.

4. В области K38:N38 оформить заголовок таблицы (ряд K - значение аргумента «x», ряд L - значение функции «F(x)», ряд M - значение вспомогательной функции «S(x)», ряд N - проверка достижения точности « ê F(x) ê <е».

5. В ячейку K39 ввести первоначальное значение аргумента «4.8».

6. Ввести в ячейку L39 формулу «=K39*K39-11*K39+30».

7. Ввести в ячейку M39 формулу «=(K39*K39+30)/11».

8. Ввести в ячейку N39 формулу «=ЕСЛИ(ABS(L39)<$O$34;"корень")».

9. Ввести в ячейку K40 формулу «=M39».

1 0. Скопировать ячейки L39:N39 в ячейки L40:N40.

При прохождении темы численные методы учащиеся уже умеют работать с электронными таблицами и составлять программы на языке паскаль. Работа комбинированного характера.Расчитана на 40 минут. Цель работы повторить и закрепить навыки паботы с программами EXCEL, ABCPascal. Материал содержит 2 файла. Один содержит теоретический материал, так как он и предлагается ученику . Во 2-м файле пример работы ученика Иванова Ивана.

Вложение	Размер
материал для ученика	57.5 КБ
работа ученика	27 КБ

Предварительный просмотр:

Аналитическое решение некоторых уравнений, содержащих, например тригонометрические функции может быть получено лишь для единичных частных случаев. Так, например, нет способа решить аналитически даже такое простое уравнение, как cos x=x

Численные методы позволяют найти приближенное значение корня с любой заданной точностью.

Приближённое нахождение обычно состоит из двух этапов:

1) отделение корней, т.е. установление возможно точных промежутков [a,b], в которых содержится только один корень уравнения;

2) уточнение приближённых корней, т.е. доведение их до заданной степени точности.

Мы будем рассматривать решения уравнений вида f(x)=0. Функция f(x) определена и непрерывна на отрезке [а.Ь]. Значение х 0 называется корнем уравнения если f(х 0 )=0

Для отделения корней будем исходить из следующих положений:

Приближённое отделение корней можно провести и графически. Для этого уравнение (1) заменяют равносильным ему уравнением р(х) = ф(х), где функции р(х) и ф(х] более простые, чем функция f(x). Тогда, построив графики функций у = р(х) и у = ф(х), искомые корни получим, как абсциссы точек пересечения этих графиков

Для уточнения корня разделим отрезок [а, b] пополам и вычислим значение функции f(х) в точке x sr =(a+b)/2. Выбираем ту из половин [a, x sr ] или [x sr ,b], на концах которых функция f(x) имеет противоположные знаки.. Продолжаем процесс деления отрезка пополам и проводим то же рассмотрение до тех пор, пока. длина [a,b] станет меньше заданной точности . В последнем случае за приближённое значение корня можно принять любую точку отрезка [a,b] (как правило, берут его середину). Алгоритм высокоэффективен, так как на каждом витке (итерации) интервал поиска сокращается вдвое; следовательно, 10 итераций сократят его в тысячу раз. Сложности могут возникнуть с отделением корня у сложных функций.

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

ПРИМЕР : Определим графически корень уравнения . Пусть f1(х) = х , a и построим графики этих функций. (График). Корень находится на интервале от 1 до 2. Здесь же уточним значение корня с точностью 0,001(на доске шапка таблицы)

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

а:=левая граница b:= правая граница
m:= (a+b)/2 середина
определяем f(a) и f(m)
если f(a)*f(m)
если (a-b)/2>e повторяем , начиная с пункта2

Точки графика функции на концах интервала соединяются хордой. Точка пересечения хорды и оси Ох (х*) и используется в качестве пробной. Далее рассуждаем так же, как и в предыдущем методе: если f(x a ) и f(х*) одного знака на интервале , нижняя граница переносится в точку х*; в противном случае – переносим верхнюю границу. Далее проводим новую хорду и т.д.

Осталось только уточнить, как найти х*. По сути, задача сводится к следующей: через 2 точки с неизвестными координатами (х 1 , у 1 ) и (х 2 , у 2 ) проведена прямая; найти точку пересечения этой прямой и оси Ох.

Запишем уравнение прямой по двум точках:

В точке пересечения этой прямой и оси Ох у=0, а х=х*, то есть

, откуда

процесс вычисления приближённых значений продолжается до тех пор, пока для двух последовательных приближений корня х„ и х п _1 не будет выполняться условие abs(xn-x n-1 ) е - заданная точность

Сходимость метода гораздо выше предыдущего

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

Уравнения для самостоятельного решения: (отрезок в excel ищем самостоятельно)

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