Динамическая диаграмма в Excel

Добрый день, уважаемые читатели! Сегодня мы рассмотрим вопрос, который поступил от одного из читателей блога — как построить динамическую диаграмму (график)? То есть, чтобы график сам перестраивался в зависимости от выбранных условий и без удаления данных.

Как говорится — хороший вопрос! Приступим.

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

динамическая диаграмма в Excel

Далее создадим выпадающий список выбора (магазинов). Для этого перейдём на вкладку «Данные», в блоке кнопок «Работа с данными» нажмём кнопку «Проверка данных», выберем тип «Список», а затем укажем диапазон (источник) $A$2:$A$5 (в моём случае).

Подробнее о том как строить выпадающие списки смотрим ЗДЕСЬ.

Получим вот такую картину.

динамическая диаграмма в Excel

Теперь нам нужен график (диаграмма) пока только по одному магазину. Пусть это будет Ручеек.

Выделяем ячейки с A1:I2 поскольку пока нам будет нужен только он, переходим на вкладку «Вставка», в блоке кнопок «Диаграммы» жмём по треугольнику после кнопки «График» и выбираем «График с маркерами и накоплением» (для большей наглядности). Получим наш график. Как строить диаграммы смотрим ЗДЕСЬ.

динамическая диаграмма в Excel

И вот теперь мы немного отойдём от привычного построения диаграмм. Для построения динамической диаграммы в Excel нам придётся создать новую переменную — именованный диапазон. Переходим на вкладку «Формулы», в блоке кнопок «Определённые имена» нажмём кнопку «Диспетчер имён».

динамическая диаграмма в Excel

Перед нами появится следующее окно.

динамическая диаграмма в Excel

Нажимаем кнопку «Создать», задаём имя для нашего диапазона (я задам _chart), поле «Область» оставим «Книга», если что-то хочется написать в поле «Примечание» — смело пишем. Мы подобрались к самому интересному — полю «Диапазон». Сюда мы напишем следующую формулу:

Поясню что есть что. Функция СМЕЩ (смещение) будет обновлять наши данные по магазинам (так как мы построили график только для магазина Ручеек).

Далее в скобках будут показаны пределы данных времени (месяцы) (у мня это от ячейки B1 до ячейки I1). Их обязательно нужно жёстко закрепить (символами $) иначе будем получать неверную информацию.

Функция ПОИСКПОЗ поможет нам найти выбранный в списке магазин, т.е. если я выбираю в ячейке L1 другой магазин формула будет искать в диапазоне от A2 до A5 точное совпадение названия.

Подробнее о функции ПОИСКПОЗ — ВИДЕО С НАШЕГО КАНАЛА.

динамическая диаграмма в Excel

Нажимаем «ОК», затем мы увидим, что в списке диспетчера имён появился наш диапазон _chart.

динамическая диаграмма в Excel

Нажимаем «Закрыть» и возвращаемся к нашему графику. По нему щёлкаем правой кнопкой мышки и берём пункт «Выбрать данные».

динамическая диаграмма в Excel

Где находится поле с названием нашего ряда (Ручеек) кликаем кнопку «Изменить». Имя ряда мы менять не будем (там будут меняться наши магазины), а вот в значениях напишем =Лист2!_chart (можно вообще написать в кавычках имя файла, так как поле области мы оставляли Книга и после восклицательного знака написать имя нашего диапазона).

динамическая диаграмма в Excel

Нажимаем ОК и проверяем — выбираем из списка другие магазины и смотрим за изменениями графика!

Полосы прокрутки в инструментальных панелях

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

компактный

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

Данные прокрутки

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

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

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

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

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

Выбор источника данных.

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

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

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

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

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

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

Круговая диаграмма в EXCEL с суммой

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

Исходная таблица содержит данные о прибыли 4-х филиалов и ее суммарное значение.

Проще всего такую диаграмму создать предварительно построив диаграмму из статьи Совмещаем 2 круговых диаграммы .

Затем, необходимо удалить первые 3 значения в столбце «Продажи», переименовать столбец и вместо 4-го значения ввести формулу, подсчитывающую суммарное значение прибыли.

СОВЕТ : Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL , в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм .

Круговая Диаграмма Для Kpi В Excel Скачать

Слушать

Загрузил: Excel Visual

Длительность: 18 сек

Размер: 404.30 KB

Битрейт: 192 Kbps

Похожие песни

Simple Example Of Creating Infographic In Excel

Как Построить Гистограмму В Excel

Блог Эксель Практика

Выпадающая Детализация В Excel Вступление

Create Hr Dashboard In Excel Free Dashboards And Templates

Расчет Kpi В Excel Пример Расчета Kpi В Excel Таблице Формула Расчета Kpi

Комбинированная Диаграмма В Ms Excel

Круговой Индикатор Выполнения Прогресс Бар В Excel

Эта Технология Может Решить Одну Из Самых Больших Проблем В Ветроэнергетики

Интерактивный Живой График В Ms Excel

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

Функция Sparkline В Google Таблицах

Среда 31: Компьютерные курсы

Кольцевой График В Excel Выполнение Плана Прогресс

Билял Хасенов – Excel, VBA & More

Как Построить Диаграмму Спидометр В Excel

Как Построить Диаграмму Торнадо В Excel

Линейный Индикатор Выполнения Прогресс Бар В Excel

Добавление Меток Данных На Круговую Диаграмму

Associative Techniques For Visualizing Data Sampling In Excel

Дашборд Спидометр В Эксель Dashboard Speedometer Excel

Секционные Диаграммы В Excel

Слушают

Uygur Rap ماشين ام پيكاپ

Slowed Reverb Русские

Pac Baby Slowed Down

Поиск Песни Днк Артём Ка

Major Lazer Believer

Dando De Que Hablar Apache Feat Akapellah

Рэп На Киргизском Языке

Remix Russian 2021

Хантер Х Хантер 1 Эндинг

Muzica Ruseasca Mp3 2021 Iunie

Азербайджанские Песни 2021

Drive Forever Russian Remix Bass Boosted

Александр Тим Алкоголь

Anime Is For Kids Olenkinamrr

Земля Королей Ost

Sia Courage To Change Lyrics

Софья Попова Ты За Меня Молилась

Скачивают

Balon Udara Kecil

Tech Talk Magnet Coil Stator

Advanced Attack Patterns Unitale Crash Course Part 6

Bertemu Tetangga Yang Sakit Asam Lambung Maag Jadi Akraban Toyiban

Pemberian Pakan Atau Pelet Yg Sesuai Untuk Budidaya Ikan Nila

Tchrs S1E1 Диаграммы Excel Для Визуализации Kpi Спидометр Термометр Круговая Шкала

Реакция 13 Карт На Зонтика

We Ve Finally Done It

Лучшая Музыка 2018 2017 Слушать Бесплатно

Круговая Диаграмма Для Kpi В Excel Скачать

Cara Menyambung Pohon Awar Awar Dengan Loa

Сундуков Ловля Окуня

Хаос В Отношениях Или От Чего Небо Хмуриться Елена Попова

Supra Oli Gak Naik Ini Penyebabnya

Сундук Пранканул Таксиста

Travel And Back Home From My Public Holiday In Hong Kong Direction Bus 680 Causwaybay Wu Kai Sha

Laptop Acer Aspire 5 I7 Murah Acer A514 53G 78P8 Core I7 Gen Ke 10 Ssd 256Gb Vga Mx350

Читайте также:  Выпадающий список в Excel с помощью инструментов или макросов

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

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

  • Круговая
  • Объёмная круговая
  • Вторичная круговая или Вторичная линейчатая
  • Кольцевая

Круговая диаграмма в Excel

Это стандартный и самый популярный подтип круговой диаграммы в Excel. Чтобы создать её, кликните по иконке Круговая (2-D Pie) на вкладке Вставка (Insert) в разделе Диаграммы (Charts).

Круговая диаграмма в Excel

Объёмная круговая диаграмма в Excel

Объёмные круговые (3-D Pie) диаграммы очень похожи на 2-D диаграммы, но отображают данные на 3-D осях.

Круговая диаграмма в Excel

При построении объемной круговой диаграммы в Excel, появляются дополнительные функции, такие как 3-D вращение и панорама.

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

Если круговая диаграмма в Excel состоит из большого количества мелких секторов, то можно создать Вторичную круговую (Pie of Pie) диаграмму и показать эти незначительные сектора на другой круговой диаграмме, которая будет представлять один из секторов основной круговой диаграммы.

Круговая диаграмма в Excel

Вторичная линейчатая (Bar of Pie) очень похожа на Вторичную круговую (Pie of Pie) диаграмму, за исключением того, что сектора отображаются на вторичной гистограмме.

Круговая диаграмма в Excel

При создании Вторичной круговой (Pie of Pie) или Вторичной линейчатой (Bar of Pie) диаграмм в Excel, три последних категории будут по умолчанию перемещены во второй график, даже если эти категории больше остальных. Так как настройки по умолчанию не всегда являются самыми подходящими, можно сделать одно из двух:

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

Выбираем категории данных для вторичной диаграммы

Чтобы вручную выбрать категории данных для вторичной диаграммы, сделайте вот что:

  1. Щелкните правой кнопкой мыши по любому сектору круговой диаграммы и в контекстном меню выберите Формат ряда данных (Format Data Series).
  2. На появившейся панели в разделе Параметры ряда (Series Options) в выпадающем списке Разделить ряд (Split Series By) выберите один из следующих вариантов:
    • Положение (Position) – позволяет выбрать количество категорий, которые появятся во вторичной диаграмме.
    • Значение (Value) – позволяет определить порог (минимальное значение). Все категории, не превышающие порога, будут перенесены на вторичную диаграмму.
    • Процент (Percentage value) – то же самое, что и Значение (Value), но здесь указывается процентный порог.
    • Другое (Custom) – позволяет выбрать любой сектор из круговой диаграммы на рабочем листе и указать, следует ли переместить его во вторичную диаграмму или оставить в основной.

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

Круговая диаграмма в Excel

Дополнительно можно настроить следующие параметры:

  • Измените Боковой зазор (Gap between two charts). Ширина зазора устанавливается в процентном соотношении от ширины вторичной диаграммы. Чтобы эту ширину изменить, перетащите ползунок, либо вручную введите нужный процент.
  • Измените размер вторичной диаграммы. Этот показатель можно изменить с помощью параметра Размер второй области построения (Second Plot Size), который представляет размер вторичной диаграммы в процентах от размера основной диаграммы. Перетащите ползунок, чтобы сделать диаграмму больше или меньше, или введите нужные проценты вручную.

Кольцевые диаграммы

Кольцевая (Doughnut) диаграмма используется вместо круговой тогда, когда речь идет более, чем об одном ряде данных. Однако, в кольцевой диаграмме довольно-таки трудно оценить пропорции между элементами разных рядов, поэтому рекомендуется использовать другие типы диаграмм (например, гистограмму).

Круговая диаграмма в Excel

Изменение размера отверстия в кольцевой диаграмме

Создавая кольцевую диаграмму в Excel, первое, что необходимо сделать – это изменить размер отверстия. Это легко сделать следующими способами:

  1. Щелкните правой кнопкой мыши по любой точке кольцевой диаграммы и в контекстном меню выберите Формат ряда данных (Format Data Series).
  2. В появившейся панели переходим на вкладку Параметры ряда (Series Options) и меняем размер отверстия, передвигая ползунок, либо вводим процент вручную.

Круговая диаграмма в Excel

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

Правильно подобранное изображение может донести больше полезной информации, чем самая элегантная таблица. Очень эффектной является диаграмма типа спидометр, которая показывает текущее значение на некоторой ограниченной шкале. На этой диаграмме можно отображать, например, уровень KPI, выполнение плана, уровень обслуживания клиентов и другие показатели с ограниченным диапазоном изменения. Циферблат спидометра делится на зоны, стрелка показывает текущее состояние дел. Снизу можно добавить точное значение измеряемого показателя.

Диаграмма-спидометра в Excel

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

Вначале об общем принципе. Шкала – это верхняя половина кольцевой диаграммы. Нижняя половина также есть, но она прозрачная. Стрелка – это контур видимого сектора круговой диаграммы. Там же есть еще два сектора, но они прозрачны. Местоположение стрелки определяет измеряемый показатель.

Теперь изучим, как сделать диаграмму-спидометр в Excel. Вначале подготовим данные для шкалы, для чего нужно задать 4 значения: величина нижней прозрачной части, красной, желтой и зеленой зоны (цвета и их количество, разумеется, можно выбирать самостоятельно). Т.к. прозрачная часть занимает половину диаграммы, то она должна быть равна сумме трех цветов. Для простоты пусть весь циферблат занимает 100 делений. Тогда красная зона (плохо) – 50, желтая (нормально) – 30 и зеленая (хорошо) – 20 (50+30+20=100). Чтобы получился полукруг, невидимая часть также должна быть равна 100.

Данные для шкалы

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

Создаем кольцевую диаграмму

По умолчанию получится следующее.

Кольцевая диаграмма по умолчанию

В параметрах ряда делаем поворот на 90⁰.

Поворот диаграммы на 90 градусов

Удаляем название и легенду.

Повернутая кольцевая диаграмма

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

Форматирование зон шкалы спидометра

Получаем циферблат спидометра.

Циферблат спидометра

Теперь сделаем стрелку. Подготовим данные, по которым будут строится три сектора круговой диаграммы. Первый сектор – от нуля до стрелки (прозрачный), второй – стрелка, третий – оставшаяся часть круга (прозрачный).

На этот раз секторы должны быть подвижными и зависеть от измеряемого показателя. Результатом будет «отклонение стрелки» на соответствующую величину. Пусть показатель измеряется в процентах и его первоначальное значение равно 60%.

Значение показателя для стрелки спидометра

Как и с циферблатом, диапазон от 0 до 100% должен приходиться на верхний полукруг. Тогда весь круг – это 200%. Чтобы стрелка меняла свое положение, первый сектор (от которого строятся остальные) привяжем к значению измеряемого показателя. Стрелка имеет фиксированный размер, установим пока 2% (потом вообще уберем). Последний сектор – это разница между 200% и суммой первых двух секторов.

Данные для изображения стрелки

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

Добавить новый ряд на диаграмму

Указываем источник данных (диапазон из трех значений) и ОК. Должно получиться примерно следующее.

Две кольцевые диаграммы

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

Изменить тип диаграммы для ряда

Меняем диаграмму на круговую.

Делаем из кольцевой круговую диаграмму

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

Поворот круговой диаграммы на 270 градусов

Не забываем убрать контуры секторов.

Спидометр со стрелкой в виде сектора круга

Чтобы стрелка была больше похожа на стрелку, а не на узкий кусочек пирога, зададим для нее вначале черный (или другой) контур, а затем ширину сектора вместо 2% сделаем 0%.

Делаем размер сектора со стрелкой нулевым

Сектор исчезнет, а контур превратится в черную линию.

Диаграмма типа спидометр

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

Вставить графический объект

Сделаем прозрачный фон, красный контур. Затем выделим полученную фигуру, поставим курсор в строку формул и сделаем ссылку на отображаемое значение.

Вставки значения в графический объект

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

Диаграмма-спидометра в Excel

Остался один нюанс. Дело в том, что, если значение выйдет за пределы от 0 до 100%, то стрелка окажется не известно где.

Выход стрелки за пределы шкалы

Чтобы исправить возможную ошибку, с помощью функции ЕСЛИ в формуле, определяющей отклонение стрелки, зададим минимальное значение 0 и максимальное 100%.

Коррекция формулы для ограничения диапазона отклонения стрелки

Примерно так рисуется «классический» спидометр.

Иногда диапазон возможных значений нельзя разделить четкими границам типа «плохо», «нормально», «хорошо». Четких границ может не быть, тогда потребуется плавный переход от одного цвета к другому. Например, когда в качестве результата получается некоторая вероятность (p-value, бинарная классификация и др.) или измеряется уровень дефицита запасов, где также нет четких границ и хотелось бы подчеркнуть их размытость. В этом случае для шкалы спидометра следует использовать градиентную заливку. В целом диаграмма строится также, но ее циферблат состоит из одного цвета, плавно переходящего в другой.

Спидометр с плавным переходом цвета шкалы

Сделать такую диаграмму также несложно. Отличие только в циферблате. В ролике ниже показана пошаговая инструкция, как в Excel сделать оба варианта спидометров.

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

Exceltip

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

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

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

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

Таблица с KPI

Следующая таблица определяет размеры областей шкалы диаграммы. Так как мы будем строить круговую диаграмму, удобно, чтобы сумма значений равнялась 360. Первая область – прозрачная – равная 180 – нижняя часть спидометра, ее мы видеть не будем. Далее, красная и зеленая зона равняется 25% от 180 градусов, соответственно равны 45. Желтая зона – оставшиеся 90 градусов.

Таблица для шкалы

И последняя таблица с данными для диаграммы, которая отвечает за положение стрелки. Прозрачная часть равна 180, тут все понятно, аналогично предыдущей таблице. Далее, значения Меньше KPI и Больше KPI – это области, находящиеся слева и справа от указателя. Левая область занимает 73% от 180 градусов, правой досталось все остальное. Размер стрелки приняли равным 3-м градусам.

Таблица для указателя

Строим круговые диаграммы

Выделяем значения таблицы для шкалы B5:B8, переходим по вкладке Вставка в группу Диаграммы, выбираем Круговая -> Круговая

Круговая диаграмма

Excel нам построит круговую диаграмму с одним рядом данных (шкала спидометра), в которую необходимо добавить еще один ряд (стрелка спидометра). Для этого щелкаем правой кнопкой мыши по диаграмме, в выпадающем меню выбираем Выбрать данные.

Добавление ряда

В появившемся диалоговом окне Выбор источника данных, щелкаем по кнопке Добавить, находящегося в поле Элементы легенды (ряды). Далее указываем значения таблицы для стрелки. Жмем ОК.

Добавление ряда

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

Изменить тип даграммы

Теперь на передний план выступил ряд данных Указатель. Убираем заливку с не нужных областей, для этого дважды по ней щелкаем, во вкладке Работа с диаграммами –> Формат в группе Стили фигур устанавливаем значение Заливка фигуры в Нет заливки.

нет заливки

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

стрелка спидометра

Осталось убрать заливку с ряда данных, отвечающего за шкалу. Для этого нам необходимо будет вернуть положение ряда данных Шкала на передний план. Щелкаем правой кнопкой мыши по диаграмме, в выпадающем меню выбираем Изменить тип диаграммы для ряда. В появившемся диалоговом окне ставим галочку в колонке Вспомогательная ось напротив ряда данных Шкала. Жмем ОК.

Вспомогательная ось

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

убираем заливку

Осталось перевернуть диаграмму и отформатировать наш спидометр. Выделяем диаграмму, переходим по вкладке Работа с диаграммами –> Формат в группу Текущий фрагмент. В выпадающем меню выбираем Ряд Шкала -> Формат выделенного. В появившейся справа панели во вкладке Параметры ряда устанавливаем Угол поворота первого сектора равным 90 градусов. Такие же шаги проделываем для ряда данных Указатель.

Угол поворота

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

VBA Excel. Программное создание графика (диаграммы)

В настоящее время на сайте разработчиков описывается метод Charts.Add2, который, очевидно, заменил метод Charts.Add. Тесты показали, что Charts.Add продолжает работать в новых версиях VBA Excel, поэтому в примерах используется именно он.

Синтаксис

Параметры

Параметры методов Charts.Add и Charts.Add2:

Параметр Описание
Before Имя листа, перед которым добавляется новый лист с диаграммой. Необязательный параметр.
After Имя листа, после которого добавляется новый лист с диаграммой. Необязательный параметр.
Count Количество добавляемых листов с диаграммой. Значение по умолчанию – 1. Необязательный параметр.
NewLayout Если NewLayout имеет значение True, диаграмма вставляется с использованием новых правил динамического форматирования (заголовок имеет значение «включено», а условные обозначения – только при наличии нескольких рядов). Необязательный параметр.

Если параметры Before и After опущены, новый лист с диаграммой вставляется перед активным листом.

HR-аналитика для чайников: что такое KPI, метрики и дашборды

Для начала давайте разберемся, в чем разница между KPI и метриками. KPI — это ключевой показатель эффективности. Он показывает, насколько верны ваши решения и действия. И как они отражаются на вашем бизнесе. Компания может установить от 10 до 20 KPI, в зависимости от своих целей.

KPI формируется из метрик. Одна и та же метрика будет ключевой для одной функции (это KPI) и второстепенной для другой (метрика). Например, текучесть кадров — это:

  • Для HR-директора: KPI, который отражает здоровье процессов управления персоналом;
  • Для технического директора: одна из метрик. У него свои KPI — производительность труда, процента брака и т. д.

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

Метрики и KPI складываются из исходных данных (например, численность сотрудников в каждом отделе).

Пирамида HR-метрик

Давайте разложим метрики по пирамиде Маслоу . Базовые метрики поместятся на синей стороне. Среди них:

  1. Подбор . Метрики числа закрытых вакансий, удельных значений на рекрутера, скорости и конверсии подбора, стоимости закрытия.
  2. Обучение и адаптация новых сотрудников. Метрики расходов на обучение, аттестации сотрудников, текучести на испытательном сроке.
  3. Организация оплаты их труда. Метрики ФОТ (Фонд оплаты труда) по категориям и отделам, соотношение оклада и премии, социальных выплат.

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

  • Оттоксотрудников — важный показатель, который влияет на бюджет компании. Его не считают, потому что это довольно трудно, а мотивы увольнений укажут на больные моменты (в том числе на руководителей, от которых бегут сотрудники).
  • Развитие компетенций сотрудников важно отслеживать. А делать это можно с помощью аттестации после каждого обучения. Вы узнаете не только, сколько денег потратили, но и как обучение повлияло на работу сотрудников.
  • Управление по KPI стимулирует сотрудников на достижение финансовых и нефинансовых целей компании. Отдел продаж легко замотивировать с помощью бонусов, а вот с дргуими коллегами уже будет сложнее.
  • Лояльность и удовлетворенность сотрудников находятся на вершине пирамиды (по аналогии с самореализацией Маслоу). В идеале, люди работают в компании не ради заработка, а потому что разделяют ее ценности.

Дашборды

Дашборд — это монитор, на котором визуально представлены ключевые показатели. HR-дашборды помогают компаниям отслеживать результаты, а также информировать руководство о сделанной работе и даже защищать бюджет (например, доказать, что вам нужен еще один рекрутер).

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

  • Оперативные . Для информации, которая быстро меняется: результаты аттестации, зарплатные ведомости и т. д. Обычно такие отчеты составляет программа, а не человек.
  • Аналитические . Для исследования текущей ситуации по отделам и временным периодам. В основном такие отчеты понятны только тем, кто их делал, поэтому нужна визуализация данных.
  • Стратегические. Для отражения общего направления компании. Основная ЦА — топ-менеджмент и акционеры.

Давайте рассмотрим примеры:

Оперативный дашборд с финансовыми данными: таблица, показатели доли ФОТ, премии продавцов и руководителей.

Нажимаем на точку продаж или бренд и видим детали:

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

Стратегический дашборд с общими финансовыми показателями для акционеров компании.

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

Лайфхаки по созданию дашборда

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

  • Определите заказчика до работы над дашбордом (еще раз вспомните, какие бывают виды). Дашборд — это инструмент коммуникации со своей ЦА.
  • Сначала рисуйте черновой вариант дашборда на листке или в paint. Заказчику (и вам) важна оперативность: чем быстрее вы получите вводные, тем скорее закончите работу над дашбордом.
  • Не гонитесь за всем и сразу — для первого рабочего прототипа выгрузки данных за 1 месяц. Обязательно покажите заказчику, что получилось.
  • Самые важные KPI для руководства поместите в верхнюю часть экрана.
  • Проведите инструктаж для коллег: как заполнять таблицу, чтобы не сломалось обновление данных.

Выводы

KPI, метрики и дашборды — это лишь вершина айсберга HR-аналитики. Знать и правильно их понимать — основа, без которой не обойдется ни один HR-специалист. Продолжайте углубляться в HR-аналитику, применяйте знания на практике, и вы добьетесь ощутимых результатов для себя и вашей команды.

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

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

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

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

Динамическая круговая диаграмма для KPI счетчика дашборда Excel

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

  1. Постройте плоскую таблицу отображающую оборот по торговым сетям за 2017 и 2018 годы в разных столбцах. *Покрасьте ячейки, в которых оборот за 2018 год выше 2017 в зеленый цвет
  2. Постройте таблицу, отображающую 5 регионов, в которых было наибольшее число клиентов. *Строку с максимальным числом клиентов раскрасьте в зеленый, с минимальным – в желтый.
  3. Постройте сводную таблицу отображающую оборот по регионам с возможностью развернуть до торговой сети, помесячно за 2018 год. *Раскрасьте в красный те ячейки где 2018 год ниже 2017 года.
  4. Подготовьте визуализации в виде KPI оборота и клиентов, текущий год к прошлому.
  5. *Раскрасьте визуализацию KPI оборота согласно логике: если прирост отрицательный красным, до 10% желтым, остальное зеленым
  6. *В визуализации KPI клиентов сделайте раскраску градиентной от красной к зеленой.

Данные по проектам Kickstarter.com: всемирно известной площадки по краудфандингу – сбору денег на собственные инициативы. В приложении данные по проектам за 2015-2017 года. Описание датасета вы можете увидеть ниже в таблице.

1. Постройте обычную таблицу со списком проектов, их категорий и стран. Выведите сумму собранных средств, если она оказалась выше целевой, окрасьте в голубой цвет (‘blue’), в противном случае – в оранжевый (‘orange’). В отдельном столбце вычислите среднюю сумму «пожертвования» на каждого сторонника.
1.а) Найдите проект из категории Игры, набравший наибольшее число сторонников и запущенный в 2016 году. Назовите проект, число его сторонников и среднюю сумму пожертвования.
1.б) Какое количество успешно завершившихся проектов, относящихся к категории Еда, набрали в среднем более тысячи долларов с человека? В каком году было меньше всего таких проектов?

2. Перейдем к сводной таблице. В качестве строк таблицы возьмем поля Категория, Подкатегория (* можно создать детализированное мастер-измерение) и Страна, в качестве столбца – поле Текущее состояние. Посчитаем количество проектов и количество сторонников.
2. а) В провальных проектах какой подкатегории и страны участвовало больше всего человек? Для облегчения задачи можно воспользоваться сортировкой полей по выражению.
2. б) Какая страна находится на третьем месте по количеству успешно завершенных проектов в категории Фильмы и Видео?

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

Наложение и комбинирование графиков

Построить два графика в Excel не представляет никакой сложности. Совместим на одном поле два графика функций в Excel. Добавим к предыдущей Z=X(√x – 3). Таблица с данными:

2 графика функций.

Выделяем данные и вставляем в поле диаграммы. Если что-то не так (не те названия рядов, неправильно отразились цифры на оси), редактируем через вкладку «Выбрать данные».

А вот наши 2 графика функций в одном поле.

Пример с двумя графиками функций.

KPI Dashboard in Excel

Jeevan A Y

You go to any organization; it is important to analyses based on their Key Performance Indicators (KPI). There will be a separate dedicated team that is responsible for analyzing and showing the results with visual effects. In this article, we will show you how to create a KPI dashboard of individual sales employee dashboard in excel. So, download the workbook to create a KPI dashboard in excel along with me.

Different companies have different KPI dashboard; for this article, I am taking into consideration of Sales Driven organization. In a sales-driven organization, its core revenue generation department is their sales team. In order to just how the company is performing, it is important to look at the performance of each individual in the department.

How to Create a KPI Dashboard in Excel?

Steps to create a KPI Dashboard in Excel are as follows.

    First, we need to create a Target table of each individual employee across 12 months.

KPI Dashboard Example 1

In the above table for each individual, I have created a target for each month. For this, I have applied table format in excel and given the name as “Target_Table.”

KPI Dashboard Example 1-1

KPI Dashboard Example 1-2

Above is the table of individual salary. So now, create a table for “Incentives.”

KPI Dashboard Example 1-3

Ok, now we are done with all the data inputs requires to show in the dashboard. Now create one more sheet and name it as “Dash Board – KPI.”

KPI Dashboard Example 1-4

KPI Dashboard Example 1-5

KPI Dashboard Example 1-6

KPI Dashboard Example 1-7

I have divided the data to be shown for the first 6 months and for later 6 months. So that is why I have added H1 total u0026 H2 total rows.

H1 consists of the first 6 months total, and H2 consists of the second 6 months total. So, the total is a combination of H1 + H2.

To Find Actual Sales data, select Sales_Table in place of Target_Table in VLOOKUP Formula.

KPI Dashboard Example 1-8

To find variance Subtract actual sales data from target sales data.

KPI Dashboard Example 1-9

To get an average of sales data applies the below formula.

KPI Dashboard Example 1-10

Similarly, do the same for Salary u0026 Incentive Columns.

key performance indicators dashboard Example 1-11

Then Find the Total Earned by adding both salary paid and incentive paid.

key performance indicators dashboard Example 1-12

Now, based on the selection we make from the drop-down list of the name, VLOOKUP fetches the data of Target, Achieved, Salary, and Incentive data from the respective table which we have earlier in the article.

KPI Dashboard Example 1-13

KPI Dashboard Example 1-14

KPI Dashboard Example 1-15

For the below chart, I have applied a Clustered Column chart in excel.

key performance indicators dashboard Example 1-16

After all this, we need to insert a simple efficiency chart. I have applied the efficiency chart, and you can start using this KPI dashboard by downloading the excel workbook from the link provided.

key performance indicators Dashboard Example 1-17

Based on the selection we make from the drop-down list of accordingly numbers will turn up, and graphs will show the differences.

Based on your requirement, you can increase the number of employees, fill the target data, actual data, and salary and incentive data accordingly.

Recommended Articles

This has been a guide to KPI Dashboard in Excel. Here we discuss how to create a KPI dashboard in excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

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