Как построить серию вариантов в Excel

Обновлено: 15.05.2024

Дэниел Ясси, CFA, преподаватель Академии Investopedia и основатель SPYderCRusher Research. Он участвует в разработке Excel и алгоритмической торговли.

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

Патрис Уильямс — писатель и автор книги Looking Fly on a Dime. Ее статьи публиковались, в частности, в журналах Travel+Leisure, USA Today и Fodor’s. Патрис поделилась своим опытом в прямом эфире в сегментах Today Show, WEtv и The Nate Berkus Show, а также была репортером Savvy Shopper для WPIX в Нью-Йорке.

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

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

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

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

Вычисление отклонения в Excel

Рассчитать дисперсию в Excel легко, если у вас уже есть набор данных, введенный в программу. В приведенном ниже примере мы рассчитаем дисперсию 20-дневной ежедневной доходности в очень популярном биржевом фонде (ETF) SPY, который инвестирует в S&P 500.

Причина, по которой вы хотите использовать VAR.S, а не VAR.P (еще одна предлагаемая формула), заключается в том, что часто у вас нет всей совокупности данных для измерения. Например, если бы в нашей таблице была вся доходность в истории SPY ETF, мы могли бы использовать показатель VAR.P, но, поскольку мы измеряем только последние 20 дней, чтобы проиллюстрировать концепцию, мы будем использовать VAR.S.

Как видите, рассчитанное значение дисперсии 0,000018674 само по себе мало что говорит нам о наборе данных. Если бы мы извлекли из этого значения квадратный корень, чтобы получить стандартное отклонение доходности, это было бы полезнее.

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

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

Где это можно использовать?

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

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

(ПРИМЕЧАНИЕ. Встроенной опции для создания диаграммы отклонений нет, но ее можно создать с помощью гистограммы.)

Этапы создания диаграмм отклонений в Excel

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

Из предоставленных данных извлеките отклонения.
Чтобы найти: Отклонение = Фактические продажи – Целевые продажи, Отклонение% = Отклонения/Целевые продажи.

Шаг 1. Введите данные в электронную таблицу и выберите все данные. Мы выбрали столбцы «Продукт» и «Фактические продажи», как показано ниже на изображении.

Шаг 2. Чтобы вставить диаграмму, перейдите в меню «Вставка» >> нажмите «Вставить столбец или гистограмму» и выберите «Гистограмма с кластерами».

Шаг 3. После того, как линейчатая диаграмма будет готова, оставайтесь на диаграмме, щелкните правой кнопкой мыши и выберите формат ряда данных. Либо нажмите Ctrl + 1, чтобы открыть панель «Формат ряда данных».

Шаг 4. На панели форматирования серии данных задайте для параметра «Ширина зазора» значение 100 %. Это помогает увеличить толщину стержня.

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

Шаг 6: Чтобы показать разницу между положительным и отрицательным значением.
Внесите следующие изменения: Будьте на диаграмме справа - щелкните, чтобы выбрать серию данных формата. Заполнить => Установите флажок Инвертировать, если отрицательный вариант (вы можете указать два варианта цвета для негатива и позитива). Здесь мы использовали зеленый цвет для положительных значений и красный для отрицательных значений.

Шаг 7. Как только разница будет выделена, мы можем использовать клавишу Shift или Ctrl и сгруппировать обе диаграммы.

Профессиональная поездка

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

Шаг 8. Таким образом, отклонение между фактическим и целевым значением отображается на единой диаграмме, известной как диаграмма отклонений.

Что следует учитывать при создании диаграмм отклонений в Excel

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

Преимущества диаграмм отклонений в Excel

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

Ограничение диаграмм отклонений в Excel

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

Вы можете просмотреть видеоролики о диаграммах, доступных на нашем канале Youtube.
Чтобы ознакомиться с тем, как использовать гистограммы в Excel, нажмите здесь.
Чтобы узнать, как выбрать правильные диаграммы для ваших данных -Нажмите здесь

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

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

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

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

Пример

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

Ячейка A8 (выделена желтым цветом) содержит раскрывающийся список в диапазоне от A2 до A4. Чтобы создать раскрывающийся список внутри ячейки, выберите ячейку и последовательно нажмите: Alt A V V (не удерживайте клавиши). Откроется диалоговое окно «Проверка данных».

Выберите «Список» в раскрывающемся списке «Разрешить», затем нажмите в поле «Источник», выберите диапазон A2: A4 и нажмите «ОК». См. рис. 4.
Формула в ячейке B8, которая была скопирована, выглядит следующим образом:

=ЕСЛИ(B7=,Н/П( ),ИНДЕКС(B2:B4,СООТВЕТСТВИЕ ($A$8,$A$2:$A$4,0)))

Эта формула демонстрирует несколько полезных приемов.

Рисунок 3

Во-первых, нам нужно определить, когда ячейка B7 равна нулю или пуста. Если это так, то мы будем отображать ошибку NA. Чтобы определить ноль или пробел, мы могли бы использовать функцию ИЛИ, но метод, показанный выше, проще, если вы сравниваете одну ячейку с несколькими значениями.

Синтаксис массива

Использование фигурных скобок называется синтаксисом массива.

Это указывает Excel на выполнение нескольких логических тестов. В этом случае будет выполнено два теста, потому что есть два элемента, разделенных запятой между фигурными скобками. Если ячейка B7 содержит либо ноль, либо пробел, то логическая проверка вернет ИСТИНА. Использование двух кавычек вместе означает пустую ячейку.

Ошибка NA

Функция ИНДЕКС

Функция ИНДЕКС — одна из самых гибких функций Excel. В этом случае он извлекает правильное значение из диапазона выше, используя функцию ПОИСКПОЗ, чтобы определить, какую запись нужно извлечь.

ИНДЕКС(B2:B4,ПОИСКПОЗ($A$8,$A$2:$A$4,0))

В этом примере функцию ИНДЕКС создать проще, чем функцию ВПР, которую также можно было бы использовать.

Функция ИНДЕКС определяет диапазон B2:B4, из которого мы будем извлекать. Вы указываете номер строки и номер столбца, чтобы извлечь ячейку из диапазона. Поскольку мы имеем в виду один диапазон столбцов, нам нужно указать только номер строки. Строка с номером один извлечет первую ячейку в диапазоне B2. Номер строки определяется результатом функции ПОИСКПОЗ. Это число будет зависеть от записи в ячейке A8.

Функция ПОИСКПОЗ

Рисунок 4

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

Формула в ячейке B10 показана ниже:

=СУММ($B$9:B9)

Это было скопировано. Использование знаков $ в начале диапазона фиксирует эту ссылку, чтобы она не менялась при копировании формулы. Эта формула вычисляет кумулятивную дисперсию (с начала года) для каждого месяца.

Диаграммы отклонений в Excel 2013 и более поздних версиях

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

Чтобы создать диаграмму отклонений, сначала выберите диапазон A6:M6. Удерживая нажатой клавишу Ctrl, выберите диапазон A9:M10 с помощью мыши. Клавиша Ctrl позволяет выбирать несмежные диапазоны.

Перейдите на вкладку ленты "Вставка" и выберите "Рекомендуемые диаграммы". Диаграмма отклонений должна быть выделена первой, как показано на рис. 5. Нажмите кнопку "ОК".

Ячейка A6 содержит формулу, которая создает заголовок диаграммы на основе выбора в ячейке A8. Формула:

="Продажи в $ - "&A7&" v "&A8&" Отклонение"

Символ & объединяет текст вместе.

Рисунок 5

Нажмите на заголовок диаграммы во вновь созданной диаграмме, затем щелкните в строке формул над сеткой и введите знак =, затем щелкните ячейку A6. Это связывает заголовок диаграммы с ячейкой. Изменение ячейки A8 автоматически обновит данные, а также заголовок диаграммы.

Диаграммы отклонений в старых версиях Excel

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

Сопутствующее видео и файл Excel будут более подробно демонстрировать эти приемы.

Нил Блэквуд CPA управляет A4 Accounting, проводит обучение Excel, вебинары и консультационные услуги организациям по всей Австралии. Вопросы можно отправлять на [email protected]

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Word для Microsoft 365 для Mac PowerPoint для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Word 2021 для Mac PowerPoint 2021 для Mac Excel 2019 Excel 2019 для Mac Word 2019 для Mac PowerPoint 2019 для Mac Excel 2016 Excel 2016 для Mac Word 2016 для Mac PowerPoint 2016 для Mac Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Excel 2010 Excel 2007 Excel Starter 2010 Еще. Меньше

После создания диаграммы вы можете изменить ряды данных двумя способами:

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

Используйте фильтры диаграммы, чтобы показать или скрыть данные на диаграмме.

Редактировать или изменить порядок серий

Щелкните правой кнопкой мыши диаграмму и выберите "Выбрать данные".

В поле "Элементы легенды (серии)" выберите серию, которую хотите изменить.

Нажмите "Изменить", внесите изменения и нажмите "ОК".

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

Чтобы переупорядочить серию, выберите ее и нажмите "Вверх" или "Вниз" .

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

Если вы хотите переименовать ряд данных, см. раздел Переименование ряда данных.

Фильтрация данных на диаграмме

Начнем с фильтров диаграммы.

Нажмите в любом месте диаграммы.

Нажмите кнопку "Фильтры диаграммы" рядом с диаграммой.

На вкладке "Значения" установите или снимите отметки с серий или категорий, которые нужно показать или скрыть.

Нажмите "Применить".

Если вы хотите отредактировать или изменить порядок данных в серии, нажмите «Выбрать данные» и выполните шаги 2–4 в следующем разделе.

Редактировать или изменить порядок серий

Нажмите на диаграмму.

На ленте нажмите Дизайн диаграммы, а затем нажмите Выбрать данные.

Чтобы изменить серию легенды, в поле Элементы легенды (серии) выберите серию, которую хотите изменить. Затем отредактируйте поля «Имя» и «Значения Y», чтобы внести изменения.

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

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

Фильтрация данных на диаграмме

В Word и PowerPoint: выберите диаграмму, а затем на вкладке "Конструктор диаграммы" нажмите "Редактировать данные в Excel".

В Excel выберите название категории, а затем на вкладке "Главная" нажмите "Сортировка и фильтр" > "Фильтр".

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

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