(Обратите внимание на дополнительный раздел от 04.06.2017 в конце статьи.)

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

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

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

«Мы, помню, 5 лет назад изготавливали до 1000 штук таких изделий в месяц, а сейчас и 700 еле-еле собираем!». Открываем статистику и видим, что 5 лет назад и 500 штук не изготавливали…

«Во сколько обходится километр пробега твоего автомобиля с учетом всех затрат?» Открываем статистику – 6 руб./км. Поездка на работу – 107 рублей. Дешевле, чем на такси (180 рублей) более чем в полтора раза. А бывали времена, когда на такси было дешевле…

«Сколько времени требуется для изготовления металлоконструкций уголковой башни связи высотой 50 м?» Открываем статистику – и через 5 минут готов ответ…

«Сколько будет стоить ремонт комнаты в квартире?» Поднимаем старые записи, делаем поправку на инфляцию за прошедшие годы, учитываем, что в прошлый раз купили материалы на 10% дешевле рыночной цены и – ориентировочную стоимость мы уже знаем…

Ведя учет своей профессиональной деятельности, вы всегда будете готовы ответить на вопрос начальника: «Когда!!!???». Ведя учет домашнего хозяйства, легче спланировать расходы на крупные покупки, отдых и прочие расходы в будущем, приняв соответствующие меры по дополнительному заработку или по сокращению необязательных расходов сегодня.

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

Аппроксимация в Excel статистических данных аналитической функцией.

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

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

1. Включаем Excel и помещаем на лист таблицу с данными статистики.

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

3. «Наводим» мышь на любую из точек на графике и щелчком правой кнопки вызываем контекстное меню (как говорит один мой хороший товарищ — работая в незнакомой программе, когда не знаешь, что делать, чаще щелкай правой кнопкой мыши…). В выпавшем меню выбираем «Добавить линию тренда…».

4. В появившемся окне «Линия тренда» на вкладке «Тип» выбираем «Линейная».

6. На графике появилась прямая линия, аппроксимирующая нашу табличную зависимость.

Мы видим кроме самой линии уравнение этой линии и, главное, мы видим значение параметра R 2 – величины достоверности аппроксимации! Чем ближе его значение к 1, тем наиболее точно выбранная функция аппроксимирует табличные данные!

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

Лучше всех из выбранных функций аппроксимирует наши данные полином второй степени, у него максимальный коэффициент достоверности R 2 .

Однако хочу вас предостеречь! Если вы возьмете полиномы более высоких степеней, то, возможно, получите еще лучшие результаты, но кривые будут иметь замысловатый вид…. Здесь важно понимать, что мы ищем функцию, которая имеет физический смысл. Что это означает? Это означает, что нам нужна аппроксимирующая функция, которая будет выдавать адекватные результаты не только внутри рассматриваемого диапазона значений X, но и за его пределами, то есть ответит на вопрос: «Какой будет выпуск металлоконструкций при количестве переработанных за месяц уголков меньше 45 и больше 168 тонн!» Поэтому я не рекомендую увлекаться полиномами высоких степеней, да и параболу (полином второй степени) выбирать осторожно!

Итак, нам необходимо выбрать функцию, которая не только хорошо интерполирует табличные данные в пределах диапазона значений X=45…168, но и допускает адекватную экстраполяцию за пределами этого диапазона. Я выбираю в данном случае логарифмическую функцию, хотя можно выбрать и линейную, как наиболее простую. В рассматриваемом примере при выборе линейной аппроксимации в excel ошибки будут больше, чем при выборе логарифмической, но не на много.

8. Удаляем все линии тренда с поля диаграммы, кроме логарифмической функции. Для этого щелкаем правой кнопкой мыши по ненужным линиям и в выпавшем контекстном меню выбираем «Очистить».

9. В завершении добавим к точкам табличных данных планки погрешностей. Для этого правой кнопкой мыши щелкаем на любой из точек на графике и в контекстном меню выбираем «Формат рядов данных…» и настраиваем данные на вкладке «Y-погрешности» так, как на рисунке ниже.

10. Затем щелкаем по любой из линий диапазонов погрешностей правой кнопкой мыши, выбираем в контекстном меню «Формат полос погрешностей…» и в окне «Формат планок погрешностей» на вкладке «Вид» настраиваем цвет и толщину линий.

Аналогичным образом форматируются любые другие объекты диаграммы в Excel !

Окончательный результат диаграммы представлен на следующем снимке экрана.

Итоги.

Результатом всех предыдущих действий стала полученная формула аппроксимирующей функции y=-172,01*ln (x)+1188,2. Зная ее, и количество уголков в месячном наборе работ, можно с высокой степенью вероятности (±4% — смотри планки погрешностей) спрогнозировать общий выпуск металлоконструкций за месяц! Например, если в плане на месяц 140 тонн уголков, то общий выпуск, скорее всего, при прочих равных составит 338±14 тонн.

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

Из практики скажу, что хорошим результатом следует считать нахождение аппроксимирующей функции с коэффициентом достоверности R 2 >0,87. Отличный результат – при R 2 >0,94.

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

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

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

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

Не забывайте подтверждать подписку кликом по ссылке в письме, которое придет к вам на указанную почту (может прийти в папку « Спам» )!!!

С интересом прочту Ваши комментарии, уважаемые читатели! Пишите!

P.S. (04.06.2017)

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

Вас не устраивают полученные точность аппроксимации (R 2 <0,95) или вид и набор функций, предлагаемые MS Excel?

Размеры выражения и форма линии аппроксимирующего полинома высокой степени не радует глаз?

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

При использовании предлагаемого алгоритма действий найдена весьма компактная функция, обеспечивающая высочайшую точность аппроксимации: R 2 =0,9963!!!

Аппроксимация нелинейной функции

x 0 /12 /6 /4 /3 5/12 /2

y 0,5 0,483 0,433 0,354 0,25 0,129 0

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

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

Формирование функции времени

Интервал изменения:

Время циклического перезапуска: T = 1c

Теперь смоделируем функцию:

Аппроксимация

Рисунок 3.1 - Схема решения уравнения

Рисунок 3.2 - Блок-схема формирования нелинейной функции

Таким образом, автоматически формируется левая часть уравнения. При этом условно считается, что старшая производная x// известна, поскольку члены правой части уравнения известны и могут быть подключены к входам У1 (рисунок 3.1). Операционный усилитель У3 выполняет роль инвертора сигнала +х. Для моделирования x// необходимо в схему ввести еще один підсумовуючий усилитель, на входы которого необходимо подать сигналы, которые моделируют правую часть уравнения (3.2).

Рассчитываются масштабы всех переменных с учетом того, что максимальная величина машинной переменной за абсолютной величиной равняется 10 В:

Mx = 10 / xmax; Mx/ = 10 / x/ max; Mx// = 10 / x //max;

My = 10 / ymax. (3.3)

Масштаб времени Mt = T / tmax = 1, поскольку моделирование задачи осуществляется в реальном масштабе времени.

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

Для усилителя У1 коэффициенты передачи находятся за формулами:

K11 = Mx/ b / (MyMt); K12 = Mx/ a2 / (MxMt);

K13 = Mx/ a1 / (MxMt). (3.4)

Для усилителя У2:

K21 = Mx/ / (Mx/ Mt), (3.5)

и для усилителя У3:

К31 = 1. (3.6)

Напряжения начальных условий вычисляются за формулами:

ux/ (0) = Mx/ x/ (0) (-1); ux(0)= Mxx(0) (+1). (3.7)

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

Описание принципиальной схемы

Блок формирования функции времени (Ф) выполняется в виде одного (для формирования t) или двух последовательно соединенных (для формирования t2) интегрирующих усилителей с нулевыми начальными условиями.

В этом случае при подаче на вход первого интегратора сигнала U, на его выходе получим:

u1(t)= - K11 = - K11Et. (3.8)

Положив K11E=1, имеем u1(t)= t.

На выходе второго интегратора получим:

u2(t)= K21 = K11K21Et2 / 2 (3.9)

Положив K11K21E/2 = 1, имеем u2(t)= t2.

Блоки формирования отрезков аппроксимирующей функции реализуются в виде диодных блоков нелинейных функций (ДБНФ), входной величиной для которых является функция времени t или t2. Порядок расчета и построения ДБНФ приведенные в .

Сумматор (ГРУСТЬ) отрезков аппроксимирующей функции выполняется в виде дифференциального итогового усилителя.

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

а) интегрирование - при положении ключа К в позиции 1. При этом исходный сигнал схемы с достаточной точностью описывается уравнением идеального интегратора:

u1(t)= - (1 / RC) . (3.10)

Этот режим используется при моделирование задачи. Для проверки правильности выбора параметров R и C интегратора проверяют величину исходного напряжения интегратора в функции времени и полезное время интегрирования в пределах допустимой ошибки?Uдоп.

Величина исходного напряжения интегратора

U(t)= - KYE {1 - e - Т / [(Ky+1)RC} (3.11)

за время моделирования Т при интегрировании входного сигнала E с использованием операционного усилителя с коэффициентом передачи Ky без цепи обратной связи не должна превышать значения машинной переменной (10 В).

Время интегрирования

Tи = 2RC(Kу + 1)?Uдоп (3.12)

при выбранных параметрах схемы не должен быть меньше, чем время моделирования Т.

б) задание начальных условий реализуется при переводі ключа К в положение 2. Этот режим используется при подготовке моделирующей схемы к процессу решения. При этом исходный сигнал схемы описывается уравнением:

u0(t)= - (R2 /R1) E (3.13)

где u0(t) - величина начальных условий.

С целью сокращения времени формирования начальных условий и обеспечение надежной работы, параметры схемы должны удовлетворять условие: R1C1 = R2C.

Построить полную расчетную схему. При этом следует пользоваться условными обозначениями, приведенными в подразделе 3.1.

Пользуясь разрядностью входных и исходных данных, построить принципиальные схемы блоков Б1 и Б2 и соединить их с блоком РС.

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

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

Задача аппроксимации функции одной переменной с самого начала обязательно учитывает характер поведения исходной функции на всем интервале наблюдений. Формулировка задачи выглядит следующим образом. Функция у= f(х) задана таблицей (1). Необходимо найти функцию заданного вида:

которая в точках x 1 , x 2 , …, x n принимает значения, как можно более близкие к табличным y 1 , y 2 , …, y n .

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

Рассмотрим рисунок 6.

На рисунке 6 изображены три ситуации:

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

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

Через имеющееся "облако" точек всегда можно попытаться провести линию установленного вида, которая является наилучшей в определенном смысле среди всех линий данного вида, то есть "ближайшей" к точкам наблюдений по их совокупности. Для этого определим вначале понятие близости линии к некоторому множеству точек на плоскости. Меры такой близости могут быть различными . Однако, любая разумная мера должна быть, очевидно, связана с расстоянием от точек наблюдения до рассматриваемой линии (задаваемой уравнением y=F(x) ).

Предположим, что приближающая функция F(x) в точках х 1 , x 2 , ..., x n имеет значения y 1 , y 2 , ..., y n . Часто в качестве критерия близости используется минимум суммы квадратов разностей наблюдений зависимой переменной y i и теоретических, рассчитанных по уравнению регрессии значений y i . Здесь считается, что y i и x i - известные данные наблюдений, а F - уравнение линии регрессии с неизвестными параметрами (формулы для их вычисления будут приведены ниже). Метод оценивания параметров приближающей функции, минимизирующий сумму квадратов отклонений наблюдений зависимой переменной от значений искомой функции, называется методом наименьших квадратов (МНК) или Least Squares Method (LS).

Итак, задачу приближения функции f теперь можно сформулировать следующим образом: для функции f , заданной таблицей (1), найти функцию F определенного вида так, чтобы сумма квадратов Ф была наименьшей.

Рассмотрим метод нахождения приближающей функции в общем виде на примере аппроксимирующей функции с тремя параметрами:

(3)

Пусть F(x i , a, b, c) = y i , i=1, 2, ..., n. Сумма квадратов разностей соответствующих значений f и F будет иметь вид:

Эта сумма является функцией Ф(а, b, c) трех переменных (параметров a, b и c ). Задача сводится к отысканию ее минимума. Используем необходимое условие экстремума:

Получаем систему для определения неизвестных параметров a, b, c.

(5)

Решив эту систему трех уравнений с тремя неизвестными относительно параметров a, b, c, мы и получим конкретный вид искомой функции F(x, a, b, c). Как видно из рассмотренного примера, изменение количества параметров не приведет к искажению сущности самого подхода, а выразится лишь в изменении количества уравнений в системе (5).

Естественно ожидать, что значения найденной функции F(x, a, b, c) в точках х 1 , x 2 , ..., x n , будут отличаться от табличных значений y 1 , y 2 , ..., y n . Значения разностей y i -F(x i ,a, b, c)=e i (i=1, 2, ..., n) называются отклонениями измеренных значений y от вычисленных по формуле (3). Для найденной эмпирической формулы (2) в соответствии с исходной таблицей (1) можно, следовательно найти

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

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

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

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

3.3.1. Линейная функция (линейная регрессия). Начальным пунктом анализа зависимостей обычно является оценка линейной зависимости переменных. Следует при этом учитывать, однако, что "наилучшая" по методу наименьших квадратов прямая линия всегда существует, но даже наилучшая не всегда является достаточно хорошей. Если в действительности зависимость y=f(x) является квадратичной, то ее не сможет адекватно описать никакая линейная функция, хотя среди всех таких функций обязательно найдется "наилучшая". Если величины х и у вообще не связаны, мы также всегда сможем найти "наилучшую" линейную функцию y=ax+b для данной совокупности наблюдений, но в этом случае конкретные значения а и b определяются только случайными отклонениями переменных и сами будут очень сильно меняться для различных выборок из одной и той же генеральной совокупности.

Рассмотрим теперь задачу оценки коэффициентов линейной регрессии более формально. Предположим, что связь между x и y линейна и искомую приближающую функцию будем искать в виде:

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

Подставим полученные соотношения в систему вида (5):

или, деля каждое уравнение на n:

Введем обозначения:

(7)

Тогда последняя система будет иметь вид:

(8)

Коэффициенты этой системы M x , M y , M xy , M x 2 - числа, которые в каждой конкретной задаче приближения могут быть легко вычислены по формулам (7), где x i , y i - значения из таблицы (1). Решив систему (8), получим значения параметров a и b , а следовательно, и конкретный вид линейной функции (6).

Необходимым условием для выбора линейной функции в качестве искомой эмпирической формулы является соотношение :

3.3.2. Квадратичная функция (квадратичная регрессия). Будем искать приближающую функцию в виде квадратного трехчлена:

Находим частные производные:

Составим систему вида (5):

После несложных преобразований получается система трех линейных уравнений с тремя неизвестными a, b, c . Коэффициенты системы, так же как и в случае линейной функции, выражаются только через известные данные из таблицы (1):

(10)

Здесь использованы обозначения (7), а также

Решение системы (10) дает значение параметров a, b и с для приближающей функции (9).

Квадратичная регрессия применяется, если все выражения вида у 2 -2y 1 + y 0 , y 3 -2 y 2 + y 1 , y 4 -2 y 3 + y 2 и т.д. мало отличаются друг от друга.

3.3.3. Степенная функция (геометрическая регрессия).Найдем теперь приближающую функция в виде:

(11)

Предполагая, что в исходной таблице (1) значения аргумента и значения функции положительны, прологарифмируем равенство (11) при условии а>0 :

Так как функция F является приближающей для функции f , функция lnF будет приближающей для функции lnf . Введем новую переменную u=lnx ; тогда, как следует из (12), lnF будет функцией от u : Ф(u) .

Обозначим

Теперь равенство (12) принимает вид:

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

1. по данной таблице (1) составить новую таблицу, прологарифмировав значения x и y в исходной таблице;

2. по новой таблице найти параметры А и В приближающей функции вида (14);

3. использовав обозначения (13), найти значения параметров a и m и подставить их в выражение (11).

Необходимым условием для выбора степенной функции в качестве искомой эмпирической формулы является соотношение :

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

Прологарифмируем равенство (15):

(16)

Приняв обозначения (13), перепишем (16) в виде:

(17)

Таким образом, для нахождения приближающей функции в виде (15) нужно прологарифмировать значения функции в исходной таблице (1) и, рассматривая их совместно с исходными значениями аргумента, построить для новой таблицы приближающую функцию вида (17). Вслед за этим в соответствии с обозначениями (13) остается получить значения искомых параметров a и b и подставить их в формулу (15).

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

.

3.3.5. Дробно-линейная функция. Будем искать приближающую функцию в виде:

(18)

Равенство (18) перепишем следующим образом:

Из последнего равенства следует, что для нахождения значений параметров a и b по заданной таблице (1) нужно составить новую таблицу, у которой значения аргумента оставить прежними, а значения функции заменить обратными числами, после чего для полученной таблицы найти приближающую функцию вида ax+b . Найденные значения параметров a и b подставить в формулу (18).

Необходимым условием для выбора дробно-линейной функции в качестве искомой эмпирической формулы является соотношение :

.

3.3.6. Логарифмическая функция. Пусть приближающая функция имеет вид:

Легко видеть, что для перехода к линейной функции достаточно сделать подстановку lnx=u . Отсюда следует, что для нахождения значений a и b нужно прологарифмировать значения аргумента в исходной таблице (1) и, рассматривая полученные значения в совокупности с исходными значениями функции, найти для полученной таким образом новой таблицы приближающую функцию в виде линейной. Коэффициенты a и b найденной функции подставить в формулу (19).

Необходимым условием для выбора логарифмической функции в качестве искомой эмпирической формулы является соотношение :

.

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

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

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

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

Но она может быть построена с применением одного из пяти видов аппроксимации:

• Линейной;
• Экспоненциальной;
• Логарифмической;
• Полиномиальной;
• Степенной.

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

Способ 1: линейное сглаживание

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

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

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

В конкретно нашем случае формула принимает такой вид:

y=-0,1156x+72,255

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

Способ 2: экспоненциальная аппроксимация

Теперь давайте рассмотрим экспоненциальный тип аппроксимации в Эксель.

Общий вид функции сглаживания при этом такой:

где e – это основание натурального логарифма.

В конкретно нашем случае формула приняла следующую форму:

y=6282,7*e^(-0,012*x)

Способ 3: логарифмическое сглаживание

Теперь настала очередь рассмотреть метод логарифмической аппроксимации.

В общем виде формула сглаживания выглядит так:

где ln – это величина натурального логарифма. Отсюда и наименование метода.

В нашем случае формула принимает следующий вид:

y=-62,81ln(x)+404,96

Способ 4: полиномиальное сглаживание

Настал черед рассмотреть метод полиномиального сглаживания.

Формула, которая описывает данный тип сглаживания, приняла следующий вид:

y=8E-08x^6-0,0003x^5+0,3725x^4-269,33x^3+109525x^2-2E+07x+2E+09

Способ 5: степенное сглаживание

В завершении рассмотрим метод степенной аппроксимации в Excel.

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

Общая формула, описывающая данный метод имеет такой вид:

В конкретно нашем случае она выглядит так:

y = 6E+18x^(-6,512)

Как видим, при использовании конкретных данных, которые мы применяли для примера, наибольший уровень достоверности показал метод полиномиальной аппроксимации с полиномом в шестой степени (0,9844 ), наименьший уровень достоверности у линейного метода (0,9418 ). Но это совсем не значит, что такая же тенденция будет при использовании других примеров. Нет, уровень эффективности у приведенных выше методов может значительно отличаться, в зависимости от конкретного вида функции, для которой будет строиться линия тренда. Поэтому, если для этой функции выбранный метод наиболее эффективен, то это совсем не означает, что он также будет оптимальным и в другой ситуации.

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

Часто необходимо иметь аналитические выражения для вольт - амперных характеристик нелинейных элементов. Эти выражения могут лишь приближенно представлять ВАХ, поскольку физиче­ские закономерности, которым подчиняются зависимости между напряжениями и токами в нелинейных при­борах, не выражаются аналитически.

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

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

Будем считать, что ВАХ нелинейного элемента i = fun(u) задана графически, т. е. определена в каждой точке интервала U min ≤ и ≤ U max , и представляет собой однозначную непрерывную функцию переменной и. Тогда задача аналитического представления вольт-амперной характеристики может рассматриваться как задача ап­проксимации заданной функции ξ(х) выбранной аппроксимирую­щей функцией f (x ).

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

Δ= max‌‌│ f (x )- ξ(x )│

Часто критерием близости выбирается среднее квадратичное значение разности между указанными функциями в интервале ап­проксимации.

Иногда под близостью двух функций f(x )и ξ(x ) понимают сов­падение в заданной точке

x = Хо самих функций и п + 1 их произ­водных.

Наиболее распространенным способом приближения аналитической функции к заданной является интерполяция (метод выбран­ных точек), когда добиваются совпадения функций f(x )и ξ(x ) в выбранных точках (узлах интерполяции) X k , k = 0, 1, 2, ..., п.

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

В задачах аппроксимации вольт-амперных характеристик элек­тронных и полупроводниковых приборов стремиться к высокой точности их воспроизведения, как правило, нет необходимости ввиду значительного разброса характеристик приборов от образца к образцу и существенного влияния на них дестабилизирующих факторов, например, температуры в полупроводниковых приборах. В большинстве случаев достаточно «правильно» воспроизвести об­щий усредненный характер зависимости i = f (u )в пределах ее ра­бочего интервала. Для того чтобы была возможность аналитически рассчитывать цепи с нелинейными элементами, необходимо иметь математические выражения для характеристик элементов. Сами эти характеристики обычно являются экспериментальными, т.е. полученными в результате измерений соответствующих элементов, а затем на этой основе формируются справочные (типовые) данные. Процедуру математического описания некоторой заданной функции в математике называют аппроксимацией этой функции. Существует целый ряд типов аппроксимации: по выбранным точкам, по Тейлору, по Чебышеву и др. В конечном итоге необходимо получить математическое выражение, которое с какими-то заданными требованиями удовлетворяло исходной, аппроксимирующей функции.

Рассмотрим простейший способ: метод выбранных точек или узлов интерполяции степенным полиномом.

Необходимо определить коэффициенты полинома. Для этого выбирается (n+1) точек на заданной функции и составляется система уравнений:

Из этой системы находятся коэффициенты а 0 , а 1 , а 2 , …, а n .

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

Можно использовать экспоненциальный полином:

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

Аппроксимация по Батерворту : выбирается простейший полином:

В этом случае можно определить максимальное отклонение ε на краях диапазона.

Аппроксимация по Чебышеву : является степенной, там устанавливается совпадение в нескольких точках и минимизируется максимальное отклонение аппроксимирующей функции от исходной. В теории аппроксимации функций доказывается, что наиболь­шее по абсолютной величине отклонение полинома f (x )степени п от непрерывной функции ξ(х ) будет минимально возможным, если в интервале приближения а ≤ х ≤ b разность

f(x ) - ξ(х ) не мень­ше, чем п + 2 раза принимает свои последовательно чередующиеся предельные наибольшие f (x ) - ξ(х ) = L > 0 и наименьшие f (x ) - ξ(х ) = -L значения (критерий Чебышева).

Во многих прикладных задачах находит применение полиноми­альная аппроксимация по среднеквадратическому критерию близо­сти, когда параметры аппроксимирующей функции f (x ) выбирают­ся из условия обращения в минимум в интервале аппроксимации а ≤ х ≤ b квадрата отклонения функции f (x ) от заданной непре­рывной функции ξ(х ), т. е., из условия:

Λ= 1/b-a∫ a [f (x )- ξ(x )] 2 dx = min . (7)

В соответствии с правилами отыскания экстремумов решение задачи сводится к решению системы линейных уравнении, которая образуется в результате приравнивания к нулю первых частных производных функции Λ по каждому из искомых коэффициентов a k аппроксимирующего полинома f (x ), т. е. уравнений

дΛ ∕дa 0 =0; дΛ ∕дa 1 =0; дΛ ∕дa 2 =0, . . . , дΛ ∕дa n =0. (8)

Доказано, что и эта система уравнений имеет единственное ре­шение. В простейших случаях оно находится аналитически, а в общем случае - численно.

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

В инженерной практике используется еще так называемая кусочно-линейная аппроксимация – это описание заданной кривой отрезками прямых линий.

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

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

Подобная аппроксимация вольт - амперных характеристик дает в большинстве случаев вполне удовлетворительные по точности результаты анализа колебаний в нели­нейной резистивной цепи при «небольших» по величине воздействи­ях на нелинейный элемент, т. е. ко­гда мгновенные значения токов в нелинейном элементе изменяются в предельно допустимых границах от I = 0 до I = I мах