Как сделать диаграмму торнадо в Excel
Обновлено: 24.11.2024
Предположим, у вас есть какие-то результаты, и вас интересует, чувствительны ли эти результаты к изменениям. Вы можете проиллюстрировать эти эффекты с помощью диаграммы торнадо, в которой используются гистограммы для сравнения изменений с исходными данными. Другими словами, диаграммы торнадо полезны для иллюстрации анализа чувствительности.
В этом руководстве мы предоставим вам пошаговое руководство по построению диаграммы торнадо на основе анализа чувствительности.
МОТИВИРУЮЩИЙ ПРИМЕР
Представьте, что вы планируете отпуск и выделили на поездку 6000 долларов. Вы выполняете некоторые оценки затрат и находите пакет для отдыха стоимостью 5050 долларов, что находится в рамках вашего бюджета. Но затем вы видите некоторые предложения и некоторые дополнительные предметы роскоши, которые вы хотите добавить к своему текущему отпускному пакету. Некоторые из них изменят стоимость ваших первоначальных смет. Чтобы увидеть, какие из этих дополнительных сделок или предметов роскоши повлияют на ваши оценки затрат, вы решаете провести односторонний анализ чувствительности. То есть вы меняете стоимость одной переменной за раз, чтобы увидеть, как это повлияет на исходную оценку стоимости (например, базовый вариант).
В таблице 1 приведены ваши базовые расходы на отпуск и возможные изменения в связи с добавлением специальных предложений и предметов роскоши.
Низкие затраты или предложения снижают общую стоимость вашего отпуска. «Высокие затраты» или предметы роскоши увеличивают стоимость вашего отпуска.
Вы хотите визуализировать, изменит ли какая-либо из этих корректировок ваши первоначальные оценки затрат (например, 5050 долларов США).
ПОСТРОЕНИЕ ДИАГРАММЫ ТОРНАДО
Диаграмму торнадо можно использовать для визуализации этих дополнительных изменений переменных.
Шаг 1. Откройте Excel и вставьте гистограмму с группировкой
Шаг 2. Введите дату для «Низкого ввода»
Нажмите правой кнопкой мыши пустую область диаграммы, выберите «Имя» и введите «Низкий ввод». Затем в поле «Значения Y:» выберите все значения в столбце «Низкий результат» вашей таблицы. В метках горизонтальной (категории) оси:» выделите имена переменных в столбце «Базовые результаты». На рисунке ниже показаны правильные варианты выбора для каждого поля ввода.
Шаг 3. Введите данные для «высокого входа»
Повторите те же шаги для диапазона данных "Высокий уровень входных данных".
Шаг 4. Отцентрируйте ось по ориентировочной стоимости
Щелкните правой кнопкой мыши ось X, выберите «Формат оси» > «Пересечения вертикальной оси» > «Значение оси» и введите «5050». При этом ось будет отцентрирована по ориентировочной стоимости 5050 долл. США.
Шаг 5. Переместите имена переменных в левую часть графика
Отцентрировав ось на ориентировочной стоимости в 5050 долл. США, вы сможете увидеть начало диаграммы торнадо. Однако имена переменных мешают. Чтобы переместить их, щелкните правой кнопкой мыши ось Y и выберите «Параметры оси»> «Интервал между метками» и выберите «Низкий». Это переместит имена переменных в левую часть, чтобы они не мешали столбцам в середине диаграммы.
Шаг 6. Выровняйте полосы так, чтобы они находились рядом друг с другом
Полосы не выровнены друг с другом. Вы можете выровнять их, используя опцию перекрытия серий. Щелкните правой кнопкой мыши одну из полос и выберите «Параметры серии» > «Построить серию вкл.» и введите 100 в виджете «Перекрытие серий». После того, как вы нажмете Enter, полосы должны быть выровнены друг с другом.
Шаг 7. Сортировка и изменение шрифтов
Чтобы завершить диаграмму торнадо, можно отсортировать столбцы таким образом, чтобы наибольшее изменение было вверху, а наименьшее — внизу (выглядит как торнадо). Щелкните правой кнопкой мыши ось Y и выберите «Формат оси»> «Параметры оси»> «Положение оси» и установите флажок «Категории в обратном порядке». Это сделает вашу диаграмму более похожей на торнадо.
Шаг 8. Окончательные изменения и правки
Последние шаги улучшают внешний вид.Изменение шрифтов и цветов может улучшить диаграмму торнадо.
ВЫВОДЫ
Диаграмма торнадо говорит нам о том, что оплата дополнительной «роскоши» стоимости полета превысит наш бюджет в 6000 долларов США (обозначено красной пунктирной линией). В результате мы не будем тратить дополнительный капитал на обновление наших мест! Однако мы можем немного разориться, когда речь идет о других элементах нашей поездки (например, о дорогих блюдах, аренде роскошного автомобиля или дополнительных экскурсиях).
ССЫЛКИ
Для разработки этого блога я использовал следующее руководство, разработанное Excel Champs.
Диаграмма торнадо в excel — это разновидность гистограммы, которая также используется для сравнения данных различных типов или категорий. как условие повлияет на результат.
Диаграмма торнадо в Excel
Диаграмма Excel Tornado помогает анализировать данные и принимать решения. Это очень полезно для анализа чувствительности. Анализ чувствительности Анализ чувствительности — это тип анализа, основанный на анализе «что, если», который исследует, как независимые факторы влияют на зависимый аспект, и предсказывает результат, когда анализ выполняется при определенных условиях. read more , который показывает поведение зависимых переменных, т. е. показывает, как одна переменная влияет на другую. Другими словами, он показывает, как выходные данные будут зависеть от входных данных.
Эта диаграмма полезна для демонстрации сравнения данных между двумя переменными.
Это столбчатая диаграмма с горизонтальными полосами. На этой диаграмме столбцы двух переменных направлены в противоположные стороны, а основание обеих переменных находится в середине диаграммы, что делает ее похожей на торнадо, поэтому она называется диаграммой торнадо. Его также называют диаграммой-бабочкой или воронкообразной диаграммой в Excel. Воронкообразная диаграмма в Excel. Воронкообразные диаграммы в Excel похожи на связанное с ней имя. форма воронки для диаграммы и, следовательно, название воронкообразной диаграммы, эта функция воронкообразной диаграммы доступна только в Microsoft Office 2019 или в последних версиях. читать дальше.
Примеры диаграммы Торнадо в Excel
Теперь давайте узнаем, как сделать диаграмму Tornado в Excel. В приведенном ниже примере показано сравнение данных о продуктах в двух разных местах.
-
Введите набор данных на лист Excel с именем и значениями переменной.
Упорядочить набор данных первой переменной в порядке возрастания, отсортировав его от наименьшего к наибольшему
Вот как теперь выглядит ваша диаграмма. Щелкните правой кнопкой мыши столбцы, чтобы добавить метки значений, выбрав опцию «Добавить метки данных». Выберите параметр «Внутри базы», чтобы метки отображались в конце столбцов, и удалите линии сетки диаграммы, выбрав линии.
Теперь ваша диаграмма Excel Tornado готова.
Диаграмма Excel Tornado также известна как диаграмма Butterfly. В этом примере показано, как сделать диаграмму похожей на диаграмму бабочки.
Теперь диаграмма выглядит как бабочка, и вы можете переименовать ее в диаграмму бабочки.
В приведенном ниже примере показано, как увеличение или уменьшение стоимости влияет на маржу.
Выполните те же действия, что и в предыдущих примерах, чтобы построить диаграмму.
В этом примере я показал отрицательное значение стоимости и положительное значение маржи.
На приведенной выше диаграмме показано влияние стоимости на маржу. Диаграмма торнадо в Excel показывает, что увеличение стоимости снижает маржу, а снижение стоимости увеличивает маржу.
Что нужно помнить
- Чтобы построить диаграмму Tornado в Excel, нам нужны данные для двух переменных, чтобы показать сравнение
- Данные должны быть отсортированы так, чтобы диаграмма выглядела как Торнадо, чтобы наибольшее значение находилось вверху.
- Диаграмма Excel Tornado является динамической, поскольку она обновляется в соответствии с изменениями, внесенными в значения переменных в наборе данных.
- Диаграмма "Торнадо" в Excel бесполезна для отображения значений независимых переменных.
Рекомендуемые статьи
Это руководство по диаграмме Tornado в Excel. Здесь мы узнаем, как создать диаграмму Excel Tornado вместе с практическими примерами и загружаемым шаблоном Excel. Вы можете узнать больше об Excel из следующих статей –
Диаграмма торнадо (также известная как диаграмма-бабочка или воронкообразная диаграмма) – это модифицированная версия гистограммы, в которой категории данных отображаются вертикально и упорядочены таким образом, чтобы визуально напоминать торнадо.
Но если вы не используете нашу надстройку Chart Creator, которая позволяет создавать расширенные динамические диаграммы Excel, например диаграммы торнадо, одним щелчком мыши, вам потребуется создать эту диаграмму вручную.
В сегодняшнем учебном пособии, рассчитанном на новичков, вы узнаете, как создать простую диаграмму торнадо с нуля, даже если вы никогда раньше не использовали Excel для визуализации данных.
Не будем ходить вокруг да около, давайте засучим рукава и приступим к работе.
Начало работы
Билл Гейтс однажды сказал: «Контент — это главное». В Excel все решают данные.
Что приводит нас к данным для вашей диаграммы торнадо.
Для наглядности мы сравним валовые онлайн-продажи и розничные продажи каждой чайной смеси для вымышленной компании, занимающейся оптовой торговлей чаем. Диаграмма торнадо поможет нам аккуратно отобразить наш сравнительный анализ, чтобы обнаружить скрытые возможности для расширения нашей чайной империи, но на этот раз мирным путем.
Вот как вам нужно подготовить набор данных:
Краткий обзор каждого элемента:
- Столбец A. Этот столбец определяет количество столбцов на диаграмме торнадо.
- Столбец B и C: две переменные, используемые для сравнения.
Сначала отсортируйте строки по столбцу B, перечислив значения от наименьшего к наибольшему. Таким образом, самая большая полоса будет находиться вверху диаграммы, а самая маленькая — внизу.
- Выделите все данные диаграммы.
- Нажмите на данные
- В группе "Сортировка и фильтр" выберите "Сортировка".
- В каждом раскрывающемся меню отсортируйте по следующим параметрам:
- В столбце выберите "В сети" (столбец B).
- Для "Сортировать по" выберите "Значения" / "Значения ячеек".
- В поле "Порядок" выберите "От меньшего к большему".
Теперь вам нужно составить гистограмму с кластерами, которая послужит основой для диаграммы торнадо.
- Выберите данные диаграммы.
- Перейти к вставке
- Нажмите значок "Вставить столбец или гистограмму".
- Выберите «Сгруппированный столбец».
После появления групповой гистограммы вы можете удалить легенду диаграммы (обычно в нижней части диаграммы). Щелкните легенду правой кнопкой мыши и выберите «Удалить».
Добавление дополнительной оси позволит нам изменить положение столбцов, придав диаграмме форму торнадо.
Сначала щелкните правой кнопкой мыши любой столбец диаграммы B (любой из синих столбцов) и выберите «Формат ряда данных».
В появившейся области задач убедитесь, что вы находитесь на вкладке "Параметры серии". В разделе "Включить серию графиков" установите переключатель "Вторичная ось".
Оказавшись там, измените масштабы первичной и вторичной осей, чтобы произошла метаморфоза. Давайте углубимся в самое интересное, сначала разберемся со вспомогательной осью.
Выберите дополнительную ось (набор чисел в верхней части диаграммы) и щелкните ее правой кнопкой мыши. Затем выберите «Формат оси».
На вкладке "Параметры оси" задайте для параметра "Минимальные границы" значение -500, а для параметра "Максимальные границы" - значение 500.
Важное примечание: обязательно установите флажок "Значения в обратном порядке".
Промойте и повторите: примените тот же процесс к основной оси (набору чисел внизу). Установите одинаковые минимальные (-500) и максимальные (500) значения для основной оси, но не устанавливайте флажок "Значения в обратном порядке".
В этот момент вы можете подумать про себя: "Подождите минутку, а как мне узнать, какое значение использовать для установки правильного диапазона, если мои данные различаются между двумя группами?" Не паникуйте. Вот хорошее практическое правило: выберите наибольшее значение на диаграмме данных и округлите его в большую сторону.
В этом случае наибольшее значение в нашем списке – 500 (для Earl Grey в магазине). Поэтому это число мы использовали для определения диапазона наших минимальных и максимальных значений.
Пришло время попрощаться с дополнительной осью, поскольку она нам больше не нужна. Щелкните правой кнопкой мыши дополнительную ось и выберите «Удалить», чтобы удалить ее.
Возможно, вы заметили, что некоторые числа на шкале основной оси отрицательные. К счастью, для решения проблемы не нужен ученый-ракетчик:
Нажмите правой кнопкой мыши на ярлыки вертикальной оси категорий (в нашем случае на названия продуктов) и выберите «Формат оси».
Затем на панели «Формат оси» перейдите на вкладку «Параметры оси», перейдите к разделу «Метки» и установите для параметра «Положение метки» значение «Низкое».Вы также можете выделить метки полужирным шрифтом (на вкладке "Главная" в верхней части окна Excel), чтобы они больше выделялись.
Чтобы сделать нашу диаграмму торнадо более информативной, добавьте метки, соответствующие вашим фактическим значениям данных.
Нажмите правой кнопкой мыши любую синюю полосу на диаграмме и выберите "Добавить метки данных".
Проделайте то же самое для другой половины диаграммы торнадо.
Доводя до ума окончательные детали, вы можете улучшить то, что у вас уже есть, переместив метки в центр диаграммы. Вот как это сделать.
Нажмите правой кнопкой мыши на метку и выберите "Форматировать метки данных".
На панели «Формат меток данных» нажмите значок «Параметры метки». Затем установите для параметра «Положение метки» значение «Внутри основания».
Оказавшись там, покрасьте текст в белый цвет и сделайте его полужирным.
Повторите это для другой половины диаграммы торнадо.
Одна последняя вещь, и мы покончили с диаграммой торнадо навсегда. Эти столбцы кажутся тонкими, поэтому давайте немного увеличим их, чтобы сделать диаграмму более приятной для глаз.
Нажмите правой кнопкой мыши любую синюю полосу и выберите "Форматировать ряд данных".
На вкладке "Параметры серии" установите для параметра "Ширина зазора" значение 10 %. Проделайте то же самое с другой стороной диаграммы. В качестве окончательной корректировки обновите заголовок диаграммы.
Та-да! Теперь все готово. Вы только что добавили еще один инструмент в свой пояс визуализации данных.
Диаграммы торнадо или диаграммы торнадо – это модифицированные версии гистограмм, а также один из классических инструментов анализа чувствительности, используемый лицами, принимающими решения, для получения быстрого обзора связанных с этим рисков. Другими словами, торнадо-диаграмма или диаграмма показывает финансовый анализ проекта.
Что такое диаграмма торнадо?
Когда вы начнете искать в Google информацию о том, как создавать диаграммы торнадо, вы получите много результатов, и для большинства из них вам потребуется загрузить надстройку. Если вы не хотите устанавливать какие-либо надстройки, вы все равно можете создать красивую диаграмму торнадо. Здесь мы покажем вам, как сделать то же самое.
Хорошо попросить лицо, принимающее решения, предоставить эти значения. Мы будем использовать пример, когда международная площадка для крикета хочет оценить неопределенности, которые способствуют их прибыли в следующем матче. Три неопределенности следующие:
1) Количество проданных билетов. Стадион вмещает 45 000 человек. Лицо, принимающее решение, считает, что минимальное значение – 25 000–35 000–45 000.
2) Средняя цена билета. Рыночная цена на следующий год неизвестна. Лицо, принимающее решение, дает значения от 10 до 15 – 20 долларов США в качестве ожидаемой средней цены билета в следующем году.
3) Средние затраты на игру. Предполагаемые средние переменные затраты, связанные с созданием одной игры, оцениваются в диапазоне от 200 000 до 250 000–300 000 долл. США.
Давайте начнем с преобразования приведенной выше информации в таблицу данных, подобную этой:
Диапазоны данных показаны в строках 4, 5 и 6. В столбце базовых значений отображаются средние ожидаемые значения. Низкое значение неопределенности — это значение, при котором существует только 10-процентная расчетная вероятность того, что реализованное значение будет меньше указанного значения. Столь же высоким значением неопределенности является значение, при котором существует только 10-процентная расчетная вероятность того, что реализованное значение будет выше указанного значения.
Как вставить диаграмму Tornado?
Итак, после того как вы настроите данные в виде таблицы, как показано, вот шаги для построения диаграммы торнадо:
<р>1. Выберите данные, исключая базовый столбец <р>2. На ленте «Вставка» выберите «Панель». Выберите столбец с кластерами (первый в разделе 2-D).Нажмите правой кнопкой мыши на горизонтальной оси и выберите "Формат оси".
<р>3.В нижней части панели «Параметры оси» найдите «Перекрестки вертикальной оси». Выберите переключатель Значение оси и введите базовое значение (31 в нашем примере, среднее из базовых значений). <р>4. Не закрывая окно, нажмите на любую из полос, чтобы открыть панель «Параметры серии». Отрегулируйте ползунки для параметра «Перекрытие серий» и «Ширина разрыва» соответствующим образом.Вот оно! Теперь вы можете отформатировать диаграмму по своему усмотрению. Итоговая диаграмма торнадо в Excel должна выглядеть так, как показано ниже.
Читайте также: