Как сделать bridge в excel?

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Диаграмма водопад (waterfall chart) в Excel

Водопад диаграмма (waterfall chart) является одной из форм визуализации данных, которая показывает совокупный эффект последовательно введенных положительных и отрицательных значений. Также иногда можно встретить название bridge chart, или «мост».

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

Выглядит она следующим образом:

Итак, посмотрим, как же можно построить диаграмму, похожую на водопад.

Подготовка данных

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

Водопад – это обычная диаграмма, отформатированная определенным образом. «Пустоты» под промежуточными значениями – это тоже ряды данных, но только без заливки. Таким образом, нам нужно разбить данные исходной таблицы на четыре колонки: крайние и прозрачные колонки, положительные значения и отрицательные значения. Наша таблица примет вид:

Где имеются такие формулы

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

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

Тут все просто, ячейка G3 суммирует значения ячеек C3:E3, соответственно формула в ней будет =СУММ(C3:E3). Ячейка G4 копирует значение ячейки G3.

Создание диаграммы Водопад

Осталось самое простое – построить диаграмму. Выделяем ячейки A1:A6 (да, пустую ячейку тоже включаем), жмем клавишу Ctrl и выделяем ячейки C1:J6, таким образом у вас будет выделено две области.

Переходим по вкладке Вставка в группу Диаграммы, выбираем Вставить гистограмму -> Гистограмма с накоплением. У вас должен получиться вот такой график:

Меняем значения столбцов и строк местами. Для этого переходим по вкладке Работа с диаграммами -> Конструктор в группу Данные и щелкаем по иконке Строка/столбец. Наша диаграмма примет вид:

Щелкаем правой кнопкой по любому ряду данных, из всплывающего меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне, меняем ряды данных соответствующие коннекторам на График.

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

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

В принципе, наша диаграмма водопад готова.

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

Как решать нестандартные задачи и строить диаграмму «Водопад» в Exсel

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

Читать еще:  Как сделать excel на русском языке?

Тимофей Миненков — студент МГУ им. М.В. Ломоносова и выпускник онлайн-курса Changellenge >> ToolKit 2018.

Я использовал функцию СУММЕСЛИМН, чтобы задать условия для каждой ячейки и посчитать сумму. Например, для первой: найти суммарную выручку по всем видам зерненого творога за последнюю неделю 2015 года без учета промоакций. Так выглядел синтаксис этой функции:

= СУММЕСЛИМН (Диапазон для суммирования; Диапазон 1 для критерия; Критерий для диапазона 1; Диапазон 2 для критерия; Критерий для диапазона 2; …)

На первый взгляд, не очень дружелюбно. Однако суть формулы проста: диапазон для суммирования в нашем случае — выручка; первым диапазоном для критерия будет дата — выделяем диапазон $H$3:$H$3370; далее идет сам критерий, т. е. дата K3; теперь выделяем диапазон для второго критерия (нам нужно исключить акции) — в нашем случае это будет диапазон $C$3:$C$3370; и сам критерий Продажа.

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

Диаграмма «водопад» с помощью надстройки Think-Cell

Think-Cell — это надстройка для PowerPoint в формате Excel. С ее помощью можно быстро делать диаграммы разного уровня сложности. Они получаются более профессиональными, по сравнению со стандартной визуализацией в Excel. Ниже несколько функций надстройки:

  1. Добавить на диаграмму стрелки, которые отражают изменение величин в %.
  2. Выбрать, в какую сторону повернут график.
  3. Обрезать базовые значения на диаграмме, оставив только верхние части, чтобы отразить маленькие изменения.

Первое время TC может показаться неуклюжим из-за его интерфейса, однако потом вам будет трудно отказаться от него. Это must-have на любых кейс-чемпионатах.

Например, на gif-анимации ниже я показываю, как за одну минуту сделать диаграмму «водопад». Она визуализирует денежный эффект от предложенных инициатив для страховой компании в чемпионате Oliver Wyman Impact. Дополнительно прокомментировать тут нужно только три момента:

  • английская буква «e» суммирует все значения, которые написаны до нее;
  • чтобы выделить необходимые столбцы для стрелки, которая показывает изменение в процентах, используйте клавишу Ctrl ;
  • иногда клавиша вызова окна Excel не работает, в такой ситуации лучше сохранить файл, закрыть и снова открыть его.

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

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

Получите карьерную поддержку

Если вы не знаете, с чего начать карьеру, зашли в тупик или считаете, что совершили какие-то ошибки, спросите совета у специалистов. Заполните заявку и консультанты Changellenge >> окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

Подписаться на карьерную рассылку

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

Как построить диаграмму по таблице в Excel: пошаговая инструкция

Любую информацию легче воспринимать, если она представлена наглядно. Это особенно актуально, когда мы имеем дело с числовыми данными. Их необходимо сопоставить, сравнить. Оптимальный вариант представления – диаграммы. Будем работать в программе Excel.

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

Как построить диаграмму по таблице в Excel?

  1. Создаем таблицу с данными.
  2. Выделяем область значений A1:B5, которые необходимо презентовать в виде диаграммы. На вкладке «Вставка» выбираем тип диаграммы.
  3. Нажимаем «Гистограмма» (для примера, может быть и другой тип). Выбираем из предложенных вариантов гистограмм.
  4. После выбора определенного вида гистограммы автоматически получаем результат.
  5. Такой вариант нас не совсем устраивает – внесем изменения. Дважды щелкаем по названию гистограммы – вводим «Итоговые суммы».
  6. Сделаем подпись для вертикальной оси. Вкладка «Макет» — «Подписи» — «Названия осей». Выбираем вертикальную ось и вид названия для нее.
  7. Вводим «Сумма».
  8. Конкретизируем суммы, подписав столбики показателей. На вкладке «Макет» выбираем «Подписи данных» и место их размещения.
  9. Уберем легенду (запись справа). Для нашего примера она не нужна, т.к. мало данных. Выделяем ее и жмем клавишу DELETE.
  10. Изменим цвет и стиль.
Читать еще:  График опроса как сделать в excel

Выберем другой стиль диаграммы (вкладка «Конструктор» — «Стили диаграмм»).

Как добавить данные в диаграмму в Excel?

  1. Добавляем в таблицу новые значения — План.
  2. Выделяем диапазон новых данных вместе с названием. Копируем его в буфер обмена (одновременное нажатие Ctrl+C). Выделяем существующую диаграмму и вставляем скопированный фрагмент (одновременное нажатие Ctrl+V).
  3. Так как не совсем понятно происхождение цифр в нашей гистограмме, оформим легенду. Вкладка «Макет» — «Легенда» — «Добавить легенду справа» (внизу, слева и т.д.). Получаем:

Есть более сложный путь добавления новых данных в существующую диаграмму – с помощью меню «Выбор источника данных» (открывается правой кнопкой мыши – «Выбрать данные»).

Когда нажмете «Добавить» (элементы легенды), откроется строка для выбора диапазона данных.

Как поменять местами оси в диаграмме Excel?

  1. Щелкаем по диаграмме правой кнопкой мыши – «Выбрать данные».
  2. В открывшемся меню нажимаем кнопку «Строка/столбец».
  3. Значения для рядов и категорий поменяются местами автоматически.

Как закрепить элементы управления на диаграмме Excel?

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

  1. Выделяем диапазон значений A1:C5 и на «Главной» нажимаем «Форматировать как таблицу».
  2. В открывшемся меню выбираем любой стиль. Программа предлагает выбрать диапазон для таблицы – соглашаемся с его вариантом. Получаем следующий вид значений для диаграммы:
  3. Как только мы начнем вводить новую информацию в таблицу, будет меняться и диаграмма. Она стала динамической:

Мы рассмотрели, как создать «умную таблицу» на основе имеющихся данных. Если перед нами чистый лист, то значения сразу заносим в таблицу: «Вставка» — «Таблица».

Как сделать диаграмму в процентах в Excel?

Представлять информацию в процентах лучше всего с помощью круговых диаграмм.

Исходные данные для примера:

  1. Выделяем данные A1:B8. «Вставка» — «Круговая» — «Объемная круговая».
  2. Вкладка «Конструктор» — «Макеты диаграммы». Среди предлагаемых вариантов есть стили с процентами.
  3. Выбираем подходящий.
  4. Очень плохо просматриваются сектора с маленькими процентами. Чтобы их выделить, создадим вторичную диаграмму. Выделяем диаграмму. На вкладке «Конструктор» — «Изменить тип диаграммы». Выбираем круговую с вторичной.
  5. Автоматически созданный вариант не решает нашу задачу. Щелкаем правой кнопкой мыши по любому сектору. Должны появиться точки-границы. Меню «Формат ряда данных».
  6. Задаем следующие параметры ряда:
  7. Получаем нужный вариант:

Диаграмма Ганта в Excel

Диаграмма Ганта – это способ представления информации в виде столбиков для иллюстрации многоэтапного мероприятия. Красивый и несложный прием.

  1. У нас есть таблица (учебная) со сроками сдачи отчетов.
  2. Для диаграммы вставляем столбец, где будет указано количество дней. Заполняем его с помощью формул Excel.
  3. Выделяем диапазон, где будет находиться диаграмма Ганта. То есть ячейки будут залиты определенным цветом между датами начала и конца установленных сроков.
  4. Открываем меню «Условное форматирование» (на «Главной»). Выбираем задачу «Создать правило» — «Использовать формулу для определения форматируемых ячеек».
  5. Вводим формулу вида: =И(E$2>=$B3;E$2 <=$D3). С помощью оператора «И» Excel сравнивает дату текущей ячейки с датами начала и конца мероприятия. Далее нажимаем «Формат» и назначаем цвет заливки.

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

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

Простенькая диаграмма Ганта готова. Скачать шаблон с примером в качестве образца.

Готовые примеры графиков и диаграмм в Excel скачать:

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

Как создать диаграмму в Excel: пошаговая инструкция

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

Построение диаграммы на основе таблицы

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

  1. Прежде, чем приступать к построению любой диаграммы, необходимо создать таблицу и заполнить ее данными. Будущая диаграмма будет построена на основе именно этой таблицы.
  2. Когда таблица будет полностью готова, необходимо выделить область, которую требуется отобразить в виде диаграммы, затем перейти во вкладку “Вставка”. Здесь будут представлены для выбора разные типы диаграмм:
    • Гистрограмма
    • График
    • Круговая
    • Иерархическая
    • Статистическая
    • Точечная
    • Каскадная
    • Комбинированная

Примечание:
В зависимости от версии программы количество разновидностей диаграмм может быть разным.

Также, существуют и другие типы диаграмм, но они не столь распространённые. Ознакомиться с полным списком можно через меню “Вставка” (в строке меню программы в самом верху), далее пункт – “Диаграмма”.

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

    Диаграмма в виде графика будет отображается следующим образом:

    А вот так выглядит круговая диаграмма:

    Как работать с диаграммами

    Когда диаграмма построена, можно приступать к работе с ней, а также настройке ее внешнего вида. Для этого перейдите во вкладку «Конструктор». Здесь есть возможность настроить различные параметры созданной диаграммы, например, поменять стиль оформления, изменить тип, подтип и т.д.

    Например, чтобы поменять типа диаграммы и ее подтип, щелкаем по кнопке “Изменить тип диаграммы” и в открывшемся списке выбираем то, что нам нужно.

    Нажав на кнопку “Добавить элемент диаграммы” можно раскрыть список действий, который поможет детально настроить вашу диаграмму.

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

    Довольно полезно, наряду со столбиками, иметь также конкретное значение данных для каждого из них. В этом нам поможет функция подписи данных. Открываем список, нажав кнопку “Добавить элемент диаграммы”, здесь выбираем пункт “Подписи данных” и далее – вариант, который нам нравится (в нашем случае – “У края, снаружи”).

    Готово, теперь наша диаграмма не только наглядна, но и информативна.

    Настройка размера шрифтов диаграммы

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

    Здесь можно внести требуемые изменения и сохранить их, нажав кнопку “OK”.

    Диаграмма с процентами

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

    1. По тому же принципу, который был описан выше, создайте таблицу и выделите участок, который необходимо преобразовать в диаграмму. Далее переходим во вкладку «Вставка» и выбираем, соответственно, тип диаграммы “Круговая”.
    2. По завершении предыдущего шага программа вас автоматически направит во вкладку по работе с вашей диаграммой – «Конструктор». Просмотрите предложенные макеты и остановите свой выбор на той диаграмме, где имеются значки процентов.
    3. Вот, собственно говоря, и все. Работа над круговой диаграммой с процентным отображением данных завершена.

    Диаграмма Парето — что это такое, и как ее построить в Экселе

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

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

    1. Создаем таблицу, например, с наименованиями товаров. В одном столбце будет указан объем закупки в денежном выражении, в другом – полученная прибыль. Цель данной таблицы вычислить — закупка какой продукции приносит максимальную выгоду при ее реализации.
    2. Строим обычную гистограмму. Для этого нужно выделить область таблицы, перейти во вкладку «Вставка» и далее выбирать тип диаграммы.
    3. После того как мы это сделали, сформируется диаграмма с 2-мя столбиками разного цвета, каждая из которых соответствует данным разных столбцов таблицы.
    4. Следующее, что нужно сделать – это изменить столбик, отвечающий за прибыль, на тип “График”. Для этого выделяем нужный столбик и идем в раздел «Конструктор». Там мы видим кнопку «Изменить тип диаграммы», нажимаем на нее. В открывшемся диалоговом окне переходим в раздел «График» и кликаем по подходящему типу графика.
    5. Вот и все, что требовалось сделать. Диаграмма Парето готова.Далее, ее можно отредактировать точно так же, как мы рассказывали выше, например, добавить значения столбиков и точек со значениями на графике.

    Заключение

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

  • Ссылка на основную публикацию
    Adblock
    detector