Как сгладить график в excel

Как выполнить экспоненциальное сглаживание в Excel

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

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

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

F t = αy t-1 + (1 – α) F t-1

F t = прогнозируемое значение для текущего периода времени t

α = значение константы сглаживания в диапазоне от 0 до 1.

y t-1 = Фактическое значение данных за предыдущий период времени

F t-1 = Прогнозируемое значение для предыдущего периода времени t-1

Чем меньше значение альфа, тем больше сглаживаются данные временного ряда.

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

Пример: экспоненциальное сглаживание в Excel

Предположим, у нас есть следующий набор данных, который показывает продажи конкретной компании за 10 периодов продаж:

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

Шаг 1: Нажмите кнопку «Анализ данных».

Перейдите на вкладку «Данные» на верхней ленте и нажмите кнопку «Анализ данных». Если вы не видите эту кнопку, вам нужно сначала загрузить Excel Analysis ToolPak , который можно использовать совершенно бесплатно.

Шаг 2: Выберите параметр «Экспоненциальное сглаживание» и нажмите «ОК».

Шаг 3: Заполните необходимые значения.

• Заполните значения данных для Input Range .
• Выберите значение, которое вы хотели бы использовать для коэффициента затухания , которое равно 1-α. Если вы хотите использовать α = 0,2, то ваш коэффициент демпфирования будет 1-0,2 = 0,8.
• Выберите выходной диапазон , в котором должны отображаться прогнозируемые значения. Рекомендуется выбрать этот выходной диапазон рядом с вашими фактическими значениями данных, чтобы вы могли легко сравнивать фактические значения и прогнозируемые значения рядом друг с другом.
• Если вы хотите увидеть диаграмму с фактическими и прогнозируемыми значениями, установите флажок « Вывод диаграммы ».

Затем нажмите ОК.

Автоматически появится список прогнозируемых значений и диаграмма:

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

Эксперименты с коэффициентами сглаживания

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

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

Как сделать плавный график в PowerPoint или Excel

Плавный график выглядит интересно и не похоже на стандартный Паверпойнт. Чтобы его сделать, надо поставить всего одну галку. Графики из этой статьи построены в PowerPoint, но все инструкции подходят и для Excel.

Что нажимать

Заходим в Паверпойнт и строим обычный график.

Щелкаем правой кнопкой непосредственно по кривой и переходим в формат ряда данных:

Справа появляется поле с настройками. Нам нужна вкладка “Заливка”. Последняя строчка в этой вкладке — “сглаженная линия”. Ставим галку и получаем плавный график.

Бонус 1 — акценты

Участки графика можно красить в разные цвета. Это пригодится, если надо сделать акцент на конкретном периоде.

Щелкаем по кривой левой кнопкой. Видим, что подсветились все точки.

Щелкаем еще раз по нужной нам точке, чтобы подсветилась только она.

В панели настроек меняем цвет участка.

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

Бонус 2 — маркеры

1. Не выходя из панели настроек, на вкладке “Заливка” переходим в раздел “Маркер”.
2. Раскрываем “Параметры маркера”.
3. Переключаем на “авто”. По умолчанию маркеры круглые. В режиме “встроенный” можно выбрать другую форму или загрузить свою картинку.

Там же настраиваем заливку и границу маркеров. Чтобы увеличить маркеры, меняем ширину линии.

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

Как сгладить график в excel

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

Сгладьте линейный график

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Чтобы изменить углы линии на плавную, очень просто, сделайте следующее:

1. Щелкните правой кнопкой мыши нужную серию и выберите Форматировать ряд данных в контекстном меню. Смотрите скриншот:

2. в Форматировать ряд данных диалоговое окно, нажмите Стиль линии на левой панели и проверьте Сглаженная линия вариант в правом разделе. Смотрите скриншот:

3. Закройте диалоговое окно. Затем вы можете увидеть, как линейный график стал плавным.

Наконечник: В Excel 2013 после нажатия Формат даты серии, перейдите, чтобы нажать Заливка и линия Вкладка в Форматировать ряд данных панель, а затем спуститесь, чтобы проверить Сглаженная линия опцию.

Метод аппроксимации в Microsoft Excel

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

Выполнение аппроксимации

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

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

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

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

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

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

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

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

1. Для построения графика, прежде всего, выделяем столбцы «Себестоимость единицы продукции» и «Прибыль». После этого перемещаемся во вкладку «Вставка». Далее на ленте в блоке инструментов «Диаграммы» щелкаем по кнопке «Точечная». В открывшемся списке выбираем наименование «Точечная с гладкими кривыми и маркерами». Именно данный вид диаграмм наиболее подходит для работы с линией тренда, а значит, и для применения метода аппроксимации в Excel.
2. График построен.

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

Также в нашем случае для сравнения различных вариантов аппроксимации важно установить галочку около пункта «Поместить на диаграмму величину достоверной аппроксимации (R^2)». Данный показатель может варьироваться от 0 до 1. Чем он выше, тем аппроксимация качественнее (достовернее). Считается, что при величине данного показателя 0,85 и выше сглаживание можно считать достоверным, а если показатель ниже, то – нет.

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

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

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

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

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

1. Для того, чтобы изменить тип линии тренда, выделяем её кликом правой кнопки мыши и в раскрывшемся меню выбираем пункт «Формат линии тренда…».
2. После этого запускается уже знакомое нам окно формата. В блоке выбора типа аппроксимации устанавливаем переключатель в положение «Экспоненциальная». Остальные настройки оставим такими же, как и в первом случае. Щелкаем по кнопке «Закрыть».
3. После этого линия тренда будет построена на графике. Как видим, при использовании данного метода она имеет несколько изогнутую форму. При этом уровень достоверности равен 0,9592, что выше, чем при использовании линейной аппроксимации. Экспоненциальный метод лучше всего использовать в том случае, когда сначала значения быстро изменяются, а потом принимают сбалансированную форму.

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

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

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

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

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

1. Тем же способом, что и в предыдущий раз через контекстное меню запускаем окно формата линии тренда. Устанавливаем переключатель в позицию «Логарифмическая» и жмем на кнопку «Закрыть».
2. Происходит процедура построения линии тренда с логарифмической аппроксимацией. Как и в предыдущем случае, такой вариант лучше использовать тогда, когда изначально данные быстро изменяются, а потом принимают сбалансированный вид. Как видим, уровень достоверности равен 0,946. Это выше, чем при использовании линейного метода, но ниже, чем качество линии тренда при экспоненциальном сглаживании.

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

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

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

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

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

1. Переходим в окно формата линии тренда, как уже делали не раз. В блоке «Построение линии тренда» устанавливаем переключатель в позицию «Полиномиальная». Справа от данного пункта расположено поле «Степень». При выборе значения «Полиномиальная» оно становится активным. Здесь можно указать любое степенное значение от 2 (установлено по умолчанию) до 6. Данный показатель определяет число максимумов и минимумов функции. При установке полинома второй степени описывается только один максимум, а при установке полинома шестой степени может быть описано до пяти максимумов. Для начала оставим настройки по умолчанию, то есть, укажем вторую степень. Остальные настройки оставляем такими же, какими мы выставляли их в предыдущих способах. Жмем на кнопку «Закрыть».
2. Линия тренда с использованием данного метода построена. Как видим, она ещё более изогнута, чем при использовании экспоненциальной аппроксимации. Уровень достоверности выше, чем при любом из использованных ранее способов, и составляет 0,9724.

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

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

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

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

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

1. Перемещаемся в окно «Формат линии тренда». Устанавливаем переключатель вида сглаживания в позицию «Степенная». Показ уравнения и уровня достоверности, как всегда, оставляем включенными. Жмем на кнопку «Закрыть».
2. Программа формирует линию тренда. Как видим, в нашем случае она представляет собой линию с небольшим изгибом. Уровень достоверности равен 0,9618, что является довольно высоким показателем. Из всех вышеописанных способов уровень достоверности был выше только при использовании полиномиального метода.

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

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

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

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

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