Как построить тренд в Excel
![](http://officeassist.ru/wp-content/uploads/11436/tmp-cba36b3c-3a13-4baf-a636-f69d3a99aef7-360x184.jpg)
В своей работе я часто строю контрольные карты Шухарта. Напомню, что контрольные карты Шухарта — один из инструментов менеджмента качества. Используется для контроля над ходом процесса. Пока значения остаются в пределах контрольных границ, вмешательство в процесс не требуется. Процесс статистически управляем. Если значения выходят за контрольные границы, необходимо вмешательство менеджмента для выявления причин отклонений.
Для построения контрольной карты я использую исходные данные, среднее значение (μ) и стандартное отклонение (σ). В Excel: μ = СРЗНАЧ($F$3:$F$15), σ = СТАНДОТКЛОН($F$3:$F$15)
![1. Исходные данные](http://officeassist.ru/wp-content/uploads/11436/tmp-cba36b3c-3a13-4baf-a636-f69d3a99aef7.jpg)
Сама контрольная карта включает: исходные данные, среднее значение (μ), нижнюю контрольную границу (μ — 2σ) и верхнюю контрольную границу (μ + 2σ):
![2. Контрольная карта на основе среднего значения](http://officeassist.ru/wp-content/uploads/11436/tmp-fdb790f0-4757-4762-8396-38bf61f325f5.jpg)
Скачать заметку в формате Word, примеры в формате Excel
Посмотрев на представленную карту, я заметил, что исходные данные демонстрируют вполне различимую линейную тенденцию к снижению доли накладных расходов:
![3. Контрольная карта и линия тренда](http://officeassist.ru/wp-content/uploads/11436/tmp-7a941a4b-c8fd-451b-a9a4-5963b3dee115.jpg)
Чтобы добавить линию тренду выделите на графике ряд с данными (в нашем примере — зеленые точки), кликните правой кнопкой мыши и выберите опцию «Добавить линию тренда». В открывшемся окне «Формат линии тренда», поэкспериментируйте с опциями. Я остановился на линейном тренде.
Если исходные данные не разбросаны в соответствии с нормальным распределением вокруг среднего значения, то описывать их параметрами μ и σ не вполне корректно. Для описания вместо среднего значения лучше подойдет прямая линейного тренда и контрольные границы, равноудаленные от этой линии тренда.
Линию тренда Excel позволяет построить с помощью функции ПРЕДСКАЗ. Нам потребуется дополнительный ряд А3:А15, чтобы известные значения Х были непрерывным рядом (номера кварталов такой непрерывный ряд не образуют). Вместо среднего значения в столбце Н вводим функцию ПРЕДСКАЗ:
![4. Формула линейного тренда](http://officeassist.ru/wp-content/uploads/11436/tmp-a5b6107d-848c-47c2-aee8-84b4d0e36448.jpg)
Стандартное отклонение σ (функция СТАНДОТКЛОН в Excel) вычисляется по формуле:
где — среднее значение, а n — размер выборки.
Если мы определяем отклонение не от среднего, а от линии тренда, то в этой формуле вместо следует использовать значения точек тренда. Например:
σ =
К сожалению, я не нашел в Excel функции для такого определения стандартного отклонения (по отношению к тренду). Задачу можно решить с помощью формулы массива. Кто не знаком с формулами массива, предлагаю сначала почитать здесь.
Формула массива может возвращать одно значение или массив. В нашем случае формула массива вернет одно значение:
![9. Формула массива для стандартного отклонения от тенденции](http://officeassist.ru/wp-content/uploads/11436/tmp-2a6d6979-1456-4a9d-903a-e58d8c67881d.jpg)
Давайте подробнее изучим, как работает формула массива в ячейке G3
СУММ(($F$3:$F$15-$H$3:$H$15)^2) определяет сумму квадратов разностей; фактически формула считает следующую сумму = (F3 — H3)2 + (F4 — H4)2 + … + (F15 — H15)2
СЧЁТЗ($F$3:$F$15) — число значений в диапазоне F3:F15
КОРЕНЬ(СУММ(($F$3:$F$15-$H$3:$H$15)^2)/(СЧЁТЗ($F$3:$F$15)-1)) = σ
Значение 6,2% есть точка нижней контрольной границы = 8,3% — 2 σ
Фигурные кавычки с обеих сторон формулы означают, что это формула массива. Для того, чтобы создать формулу массива, после ввода формулы в ячейку G3:
=H4 — 2*КОРЕНЬ(СУММ(($F$3:$F$15-$H$3:$H$15)^2)/(СЧЁТЗ($F$3:$F$15)-1))
необходимо нажать не Enter, а Ctrl + Shift + Enter. Не пытайтесь ввести фигурные скобки с клавиатуры — формула массива не заработает. Если требуется отредактировать формулу массива, сделайте это так же, как и с обычной формулой, но опять же по окончании редактирования нажмите не Enter, а Ctrl + Shift + Enter.
Формулу массива, возвращающую одно значение, можно «протаскивать», как и обычную формулу.
В результате получили контрольную карту, построенную для данных, имеющих тенденцию к понижению
![10. Контрольная карта для данных, имеющих линейный тренд](http://officeassist.ru/wp-content/uploads/11436/tmp-0dce8415-f180-4521-8199-4bcb189c07bb.jpg)
P.S. После того, как заметка была написана, я смог усовершенствовать формулы, используемые для вычисления стандартного отклонения для данных с тенденцией. Ознакомиться с ними вы можете в Excel-файле Усовершенствованный вариант Стандартное отклонение для данных с тенденцией