Дашборд в excel воронка подбора персонала

Обновлено: 08.07.2024

Иногда можно услышать, что воронка — это просто красивый отчет. Главное для рекрутера — делать дела, и только тогда, когда вакансия закрыта, можно и воронку оформить, чтобы было что показать руководителю. Это большое заблуждение, ведь воронка — это в первую очередь рабочий инструмент, зеркало подбора:

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

Чем сложнее процесс, тем тяжелее им управлять — это универсальное правило работает и в рекрутменте. Каждый из этапов подбора — это этап воронки: если в компании множество этапов подбора, то и воронка будет сложной, а результат более отсроченным. А дальше все просто: чем больше этапов, тем ниже конверсия и меньше кандидатов доходит до конца. Закладывайте, что не все соискатели доходят до собеседования, а кандидат, которому сделали оффер, отдал предпочтение другой компании или принял предложение текущего работодателя остаться. Поэтому большое количество этапов воронки с нулевой доходимостью до финала — это уже тревожный сигнал для эффективности работы.

«Сложно» — необязательно значит «качественно и хорошо». Если у вас в компании многоуровневая структура найма, возможно, ее стоит пересмотреть?

Важно не упускать этапы воронки

Обычно структура подбора выглядит так:

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

Поэтому важно на старте детально описывать воронку.

Отклик на вакансию: смотрим на источники

Иногда рекрутеры тратят много сил на разбор откликов из какого-то источника, но дальнейшая конверсия по ним ниже, чем из другого. Бывает, наоборот, выхлоп вообще нулевой — стоит ли тратить время и деньги на размещение вакансий на таком ресурсе? Если у компании есть доступ к такой статистике — становится очевидной целесообразность работы с каждым источником.

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

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

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

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

Чтобы такого не допускать, рекрутеру стоит придерживаться простых правил:

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

Что касается условий: составляя описание вакансии, убедитесь, что требования к сотруднику соответствуют вознаграждениям — заработной плате и дополнительным «плюшкам». Может, вы хотите супермена, а условия работы предлагаете заурядные?

Нужно ли давать тестовое задание и на каком этапе

Бывает, что хочется перед интервью с руководителем проверить важные для решения профессиональные знания, умения или размышления кандидата. На каком этапе это делать и как анализ воронки может в этом помочь?

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

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

Строим воронку: Excel или профессиональный софт

Как видно, воронка — не красивое дополнение, а полноценный инструмент, поэтому составлять ее нужно не после того, как вы нашли кандидата, а во время самого поиска. Можно ли это сделать на листке бумаги или в документе в Excel? Да, но зачем? Посчитайте, сколько понадобится времени. Пускай, рекрутеру нужна минута на то, чтобы вручную внести туда данные кандидата. А если кандидатов 100? И сколько вакансий в работе? И это не говоря о пометках при смене статуса кандидата и о том, что для полноценного анализа важны источники откликов и причины отказов. Добавьте сюда человеческий фактор при ручной работе — там не внес, там сделал ошибку.

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

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

Используя специализированные отчеты, можно понять тенденции причин отказов и скорректировать описание вакансии или последовательность этапов:

Как анализировать воронку: пример

Разберем на примере, как подходить к анализу воронки. Например, на входе было больше сотни кандидатов, до финального собеседования дошло 6. Куда все делись? Смотрим на воронку.

Например, видим, что большой процент кандидатов отвалился после телефонного интервью. Какая тут причина? Вы можете собирать обратную связь — «как вам вакансия? что нравится и что нет?» — или разобрать ситуацию логически. Причинами отказов могут быть заработная плата или неудобная локация.

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

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

Без рефлексии не бывает роста

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

Наш дружественный HR-эксперт Анастасия Новоселова делится опытом.

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

Как нарисовать воронку

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

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

Как мы формируем дашборд? Я беру конечное количество кандидатов, которые должны приступить к работе. Пусть условно это будет 50 человек. Я говорю рекрутерам: к такому-то числу нам нужно набрать 50 человек на должность продавца. Давайте подумаем, сколько людей нам нужно будет привлекать на каждом этапе воронки – с учетом того, сколько кандидатов в итоге отсеется.

И мы начинаем эту воронку просчитывать снизу, обратным отсчетом. 50 человек – это наш конечный этап. Далее идем на шаг назад и вспоминаем, сколько людей у нас отсеялось на предыдущем обучении. Допустим, 10%. Значит, на обучение должно зайти 55 человек. Плюс, всегда оставляем резерв на всякий случай, то есть на обучение набираем 60 человек.

Чтобы на обучение зашло 60 человек, сколько людей должно успешно пройти групповое собеседование? Примерно 90 человек, но опять берем с запасом – 140 человек нужно пригласить, но не все дойдут до собеседования. 140 человек – это примерно 10 групповых собеседований. В итоге чтобы набрать 50 человек, нужно приглашать на каждое собеседование не менее 14 человек. Для этого необходимо получить не менее 220 лидов (кандидатов, которые откликнутся на вакансию).

Так мы раскручиваем воронку в обратном порядке. Причем важно, чтобы рекрутеры обращались к своей же статистике и понимали, что на каждом этапе они будут «терять» людей.

Такая воронка всегда будет перед глазами у рекрутеров, это их «план продаж», чек-лист, с которым они постоянно сверяются и по которому проверяют свои KPI. Когда воронка визуализирована, мозг сотрудника сам находит способы достижения показателей».

Как это автоматизировать

«Есть чудесные цифровые инструменты для эйчаров, которые помогут с воронкой. Например, E-staff, в котором можно вести удобные воронки подбора.

Другой вариант. На базе CRM-системы «Битрикс24» мы с коллегами самостоятельно сделали любопытную штуку. СRM– это система управления отношениями с клиентами. Она построена на том, что мы видим каждый шаг нашего взаимодействия с клиентом (лидом). Мы применили эту же логику для кандидатов: каждого кандидата воспринимали как лид. И процесс сразу оцифровался. Так можно посчитать что угодно: стоимость привлечения кандидата, какой источник сработал лучше, сколько людей отвалилось на каждом этапе. Можно взять любой CRM-модуль и поставить IT-отделу задачу преобразовать его для целей массподбора.

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

Что еще важно

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

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

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

Эти KPI ответят на многие животрепещущие вопросами. Сколько времени и денег компания тратит на наем сотрудников? Насколько точно описание работы в объявлении соответствует реальности? Хорошо ли отстроены процессы? Насколько вы, как рекрутер, эффективны во время отбора, и можно ли эту эффективность повысить?

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

Итак, список метрик.

HR-бренд. Понять, насколько позитивно (или негативно) люди воспринимают HR-бренд, можно при помощи сайтов-отзовиков. Также отслеживайте упоминание бренда в соцсетях. Это важная метрика, и если она «проседает», поток кандидатов изначально будет меньше, чем хотелось бы.
Индекс eNPS (лояльность сотрудников) – метрика, которая тоже связана с HR-брендом. Проверить его можно самостоятельно с помощью специальных опросников (шкала организационной лояльности Мейера, например) или с привлечением сторонних специалистов.
Время на закрытие позиции. Тут, конечно, показатели сильно варьируются в зависимости от вакансии. При массовом подборе этот показатель удобно рассчитывать так: нужно разделить общее время, потраченное на закрытие определенного количества вакансий, на это самое количество вакансий.
Денежные затраты на закрытие позиции. Расчет идет по тому же принципу: стоимость рекрутинговой компании делим на количество закрытых вакансий.
Количество кандидатов, которые проходят этап от первичного отбора до собеседования. Если процент низкий, возможно, что-то «сломано» на самых ранних этапах воронки. В таком случае стоит проверить, как составлено объявление о вакансии и на каких площадках размещено.
Эффективность источников найма. Тестируйте различные каналы и проверяйте все возможные метрики: сколько стоит размещение объявлений, сколько кандидатов приносит именно этот источник, сколько из них оказываются релевантными. Это поможет оптимизировать и удешевить процесс найма.
Процент нанятых по реферальным программам. Реферальные кандидаты – это большой плюс для компаний (например, вот исследование, которое говорит: 82% работодателей довольны работой сотрудников, пришедших по рекомендациям, и нанимают их на 55% быстрее, чем обычных). Поэтому отслеживайте, как кандидаты узнают о вас: если от знакомых – значит, что с HR-брендом и лояльностью сотрудников все в порядке.
Количество принятых офферов (по отношению к общему числу сделанных офферов). Если процент плачевно низкий, стоит подумать, что смущает уже прошедших все этапы отбора кандидатов. Возможно, это низкая зарплата или неудобный график? Писали ли вы об этом в объявлении, обговаривали ли заранее?
Удовлетворенность сотрудников процессом найма, или опыт кандидатов. Метрика, которую довольно просто измерить анкетированием и которая может многое рассказать о том, что вы делаете не так.
Метрики, связанные с обучением сотрудников: время, стоимость. Здесь также стоит учитывать должностные особенности.
Коэффициент текучести сотрудников. Конечно, текучесть кадров неизбежна, особенно на линейных позициях. Но ее высокий процент говорит о том, что существует разрыв между HR-брендом и опытом сотрудников, и ожидания не сотрудников оказывается неоправданы. В идеале коэффициент текучести не должен превышать 5% в год.

Идеально, если все результаты вы визуализируете на дашборде, указав динамику по времени. Вот простой пример дашборда от Nonprofit HR:

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

Конечно, это лишь примеры метрик, и самые важные KPI для конкретной компании вы определяете самостоятельно, исходя из целей подбора. Нанять больше? Быстрее? Более качественно? А может, повысить доходимость кандидатов до собеседования или улучшить процесс адаптации? Решать вам. Мы же рекомендуем сфокусироваться на самых критичных местах воронки и работать в первую очередь с их метриками.

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

Создание дашбордов в Excel шаг за шагом

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

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

В первую очередь создадим новую книгу с 3-ма листами:

Сначала создадим табличку с входящими данными на листе «Данные» так как показано ниже на рисунке:

После чего на листе «Дашборд» создадим первый управляющий элемент – выпадающий список. В данном случае рационально использовать поле со списком, так как оно имеет больше настроек. Конечно можно было бы воспользоваться стандартным выпадающим списком в Excel выбрав инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных»-«Тип данных: Список». Но мы так делать не будем, так как он неудобен из-за своей боковой полосы прокрутки, которая появляется уже при 10-ти значений. А у нас в выпадающем списке должны отображаться 12 месяцев. Поэтому выберите другой инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Поле со списком».

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

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

Как видно из параметров данный выпадающий список в данном примере настраивается 3-мя параметрами на вкладке «Элемент управления»:

Список отображает значения из диапазона первого столбца ячеек таблицы входящих данных ссылаясь в первом поле «Формировать список по диапазону:» по адресу Данные!$A$2:$A$13.
Второе поле «Связь с ячейкой:» позволяет указать ячейку куда будут возвращаться порядковые номера значений выпадающего списка. В данном случае они передаются в ячейку по адресу Обработка!$A$1. Например, если будет выбрано значение из нашего списка – «Март» тогда в ячейку A1 на листе «Обработка» передается число 3 для дальнейшей обработки.
«Количество строк списка:» - числовой параметр позволяет нам отображать выпадающий список без полосы прокрутки. Указав число 12, мы увеличили его размер на 12 записей, чего нельзя сделать с обычным выпадающим списком из проверки данных.

Готовый желаемый результат выглядит так:

Далее начинаем упорно работать с 3-тим листом «Обработка». На данном листе обрабатываются и подготавливаются все данные для вывода на дашборд. Будем двигаться с верху вниз. Сначала подготовим данные для верхних подписей. Для этого создаем табличку выборки показателей при условии полученного номера месяца, переданного выпадающим списком на лист «Обработка» в ячейку A1. В ячейке A2 определяем название месяца на основе полученного числа в ячейке A1, по формуле:

Делаем выборку из входящей таблицы на листе «Данные» для всех показателей с помощью функции =ВПР() скопировав формулу во все остальные ячейки:

Данные для верхних подписей показателей – подготовлены!

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

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

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

Теперь в ячейку G1 вводим число 0, а целый диапазон ячеек G2:P11 заполняем формулой:

=$F$1;"";1)' >

Диапазон G2:P11 состоит из 100 ячеек (10x10) и 100 единиц – соответственно. В каждой ячейке формула, которая проверяет количество единиц в диапазоне. Если оно больше или равно числу (процентов) в ячейке F1 значит следует прекратить заполнять данный диапазон единицами. Как видно, пока-что формула не работает, так как ей не хватает значений в диапазоне H1:P1, к которым она также обращается. В этом диапазоне будут вычисляться итоговые суммы чисел для подсчета количества единиц из предыдущих столбцов с помощью формулы, которую копируем во все ячейки диапазона H1:P1:

Теперь как видно все работает и диапазон ячеек G2:P11 заполняется единицами по условию, в зависимости от числового значения в ячейке F1.

Вафельный график будет состоять из двух слоев динамического (переднего плана – желтый цвет) и статического (задний план – черный цвет). Мы составили динамически изменяемые данные для первого желтого графика. Нам нужно еще создать черный статический график, который послужит задним фоном. Для этого понадобится диапазон размером 10x10 ячеек которые просто статически заполнены единицами. Поэтому рядом заполняем диапазон ячеек R2:AA11 единицами и строим по ним статический по такому же принципу, как и предыдущий – динамический.

Сначала создадим черный статический график. Для этого выполним ряд последовательных действий:

Выделите диапазон ячеек R2:AA11 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«Линейчатая»-«Линейчатая с накоплением»
Делаем двойной щелчок мышкой по оси X, чтобы изменить настройки: «Формат оси»-«ПАРАМЕТРЫ ОСИ»-«Границы»-«Максимум» – с 12 на 10.
После чего удаляем саму ось X, затем ось Y, название, легенду, сетку – поочередно выделяя их и нажимая клавишу Delete на клавиатуре:
Делаем двойной щелчок по любому ряду данных графика и делаем настройку: «Формат ряда данных»-«ПАРАМЕТРЫ РЯДА»-«Боковой зазор» – 5%.
Рядом возле графика создаем фигуру в виде черного круга. Выбреете инструмент: «ВСТАВКА»-«Иллюстрации»-«Фигуры»-«Овал». Удерживая зажатой клавишу SHIFT на клавиатуре нарисуйте круг.
Получился синий круг поэтому меняем цвет на черный. Для этого сделайте активной фигуру круг щелкнув по ней левой кнопкой мышки и выберите инструмент из дополнительного меню: «ФОРМАТ»-«Стили фигур»-«Черная заливка»:
Скопируйте черную фигуру круга нажав комбинацию клавиш CTRL+C, затем выделите один из рядов на диаграмме и вставьте ее нажав клавиши CTRL+V на клавиатуре.
Измените размеры сторон диаграммы сделав их равными – 5 на 5 см. Щелкните по графику сделав его активным и вызвав его дополнительное меню: «РАБОТА С ДИАГРАММАМИ»-«Формат»-«Размер»:

Черный график для фона готов! Теперь создадим динамический желтый, но сначала следует временно изменить значение 50% на 100% в таблице входящих данных (или временно вместо формулы ввести 100% в ячейку F1). Иначе не получится создать линейный график с накоплением для диапазона ячеек G2:P11.

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

ВНИМАНИЕ: Не забудьте обратно поменять значение 100% на 50%!

Так же для динамического желтого графика следует убрать заливку фона области. Для этого делаем двойной щелчок мышкой по фоновой области и вносим настройки: «Формат области диаграммы»-«ПАРАМЕТРЫ ДИАГРАММЫ»-«ЗАЛИВКА»-«Нет заливки».

Далее выделите два графика удерживая клавишу CTRL на клавиатуре и выберите инструмент: «РАБОТА С ДИАГРАММАМИ»-«Формат»-«Упорядочивание»-«Группировать», как показано выше на рисунке.

После чего наложите один на другой и переместите группу (вырезать, вставить) на главный лист «Дашборд»:

Для управления слоями наложения диаграмм используйте инструмент: «РАБОТА С ДИАГРАММАМИ»-«ФОРМАТ»-«Упорядочение»-«Область выделения», как показано выше на рисунке.

Динамический вафельный график в Excel – готов!

Аналогичным образом создаем еще два вафельных графика для показателей: «Показатель качества» и «Производительность».

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

Готовый шаблон дашборда в Excel

Ниже под вафельными диаграммами у нас на дашборде расположены 3 переключателя для нижнего графика (под ними). Чтобы создать переключатели на листе «Дашборд» выберите инструмент: «РАЗРАБОТЧИК»-«Элементы управления»-«Вставить»-«Переключатель». После щелкаем по нему правой кнопкой мышки и в контекстном меню выбираем опцию «Формат объекта»:

В появившемся диалоговом окне «Формат элемента управления» на вкладке «Элемент управления» в поле ввода «Связь с ячейкой:» указываем ссылку для вывода числовых значений в ячейку по адресу: Обработка!$A$6.

Копируем новый элемент управления – переключатель, 2 раза для создания его копий остальным показателям. Теперь при переключении переключателя на листе «Обработка» в ячейке A6 будут возвращаться числовые значения 1,2 и 3. В зависимости от выбранного пользователем переключателя.

Пришло время подготовить данные для нижнего динамически изменяемого графика при переключении. Но сначала на листе «Обработка» сделаем подготовку и обработку всех необходимых значений. Снова выполним ряд последовательных действий:

На листе «Обработка» заполняем диапазон ячеек A7:A18 внешними ссылками на ячейки из другого листа «Данные» в диапазоне A2:A13, чтобы получить список полных названий месяцев их таблицы входящих данных:
В следующем столбце таблички для динамического нижнего графика будут находиться сокращенные названия этих же месяцев для комфортного отображения их на оси X. Используем функцию обрезки текста =ЛЕВСИМВ() с указанным параметром 3 символа, которые нужно оставить с начала строки:
Для динамического изменения названия графика создадим формулу выборки наименования показателя по условию:
В третьем столбце мы будем делать выборку данных по условию из таблицы входящих данных. Условие заключается в следующем если в ячейке A6 возвращено число 1 тогда с помощью функции ВПР будут выбраны значения для показателя «Уровень обслуживания» по столбцу 1, если 2 – «Показатель качества» по столбцу 2 и если 3 – «Производительность». Реализуется данная задача с помощью формулы:

Данные подготовлены и обработанные!

Теперь выполним ряд действий для создания самого нижнего графика:

Выделите диапазон ячеек B7:C18 и выберите инструмент: «ВСТАВКА»-«Диаграммы»-«График с маркерами»:
Из дополнительного меню выберите стиль его оформления: «РАБОТА С ДИАГРАММАМИ»-«Стили диаграмм»-«Стиль 2»:
Сделайте двойной щелчок левой клавише мышки по линии графика чтобы в окне «Формат ряда данных» изменить цвет линий и маркеров:
Чтобы автоматически изменялось название графика следует щелкнуть по самому названию сделав его активным, а после в строке формул следует ввести знак равно (=) и кликнуть по ячейке (в данном примере C6) из которой следует брать значение наименования, а затем нажать клавишу Enter:

Можно еще сделать несколько шагов для оформления:

изменить цвет фона – двойной клик по фону и в окне «Формат области диаграммы»-«ПАРАМЕТРЫ ДИАГРАММЫ»-«ЗАЛИВКА»-«Цвет» выбрать – белый;
добавить вертикальную ось Y значений – «РАБОТА С ДИАГРАММАМИ»-«КОНСТРУКТОР»-«Макеты диаграмм»-«Добавить элемент диаграммы»-«Оси»-«Основная вертикальная».

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

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

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