Excel/PowerQuery/VBA, Анализ данных

«Кто болеет чаще» Анализ в Excel

Время прочтения: 5 мин.

Директор:

  • Сейчас такое время, многие на больничный ушли. На оперативном совещании только половина состава присутствовала. Вот и зашел у нас спор с коллегами «кто же чаще болеет?». Я говорю, что женщины более выносливые, а они спорят!

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

Аудитор (аналитик):

  • Думаю, я справлюсь. Через пол часика покажу результат.

Но как решить подобную задачу за пол часа, когда для анализа у тебя банальные таблицы с данными в Excel?

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

Как увидеть в данных тренды и закономерности?

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

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

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

Разберемся вместе?

Входные данные

Напомним, что для анализа у нас есть таблица с данными в excel – информация о накопленных данных по листам нетрудоспособности (около 14000 сотрудников за год) и выгрузка по всем сотрудникам.

По результатам проведенной работы будут сделаны следующие выводы о том:

  • кто чаще болеет – мужчины или женщины
  • в каком возрасте чаще болеют сотрудники
  • каких, по длительности, больничных больше

Входные данные по листам нетрудоспособности – идентификатор пользователя, число дней болезни, пол (1 –мужчины, 0 — женщины), возраст.

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

Используем формулы:

  • [<1нед] = ЕСЛИ([@[Число дней болезни]]<=7;1;0)
  • [2нед] = ЕСЛИ(И([@[Число дней болезни]]>7;[@[Число дней болезни]]<=14);1;0)
  • [3нед] = ЕСЛИ(И([@[Число дней болезни]]>14;[@[Число дней болезни]]<=21);1;0)
  • [>3нед] = ЕСЛИ([@[Число дней болезни]]>21;1;0)

Полученный результат:

Анализ состава выборки

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

Выделим их и нажмем на ленте Excel – «Данные» -«Удалить дубликаты»

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

Посчитаем количество мужчин. Для этого необходимо сложить все 1 в столбце «пол» с помощью следующей формулы:

В результирующей выборке – 2402 мужчины.

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

В результирующей выборке – 11368 женщин.

Таким образом, женщин в выборке больше в 11368/2402=~ 4,7 раз.

Для того, чтобы объективно оценить статистику по больничным, узнаем из штатного расписания, сколько всего женщин и мужчин в организации.  Всего сотрудников 37843, из них 7649 мужчины 30194 женщины. Женщин больше, чем мужчин в 3,9 раза.

Посчитаем пропорции: 37,6% женщин брало больничный (11368*100/30194) и 31% мужчин (2402*100/7649).

В результате вычислений выявлено, что женщины болели за год чаще в 1,2 раза. (=37,6/31)

Сводные графики и анализ

Проведем исследование листов нетрудоспособности в цифрах, построим сводную таблицу. (инструмент «Вставка» — «Сводная таблица», в появившемся диалоговом окне переключите на опцию «на существующий лист»)

Построим график по сводной диаграмме:

Теперь, когда мы наглядно представили данные в виде графика, видны зависимости в данных. Вспомним, что выборка не нормирована, и всего женщин больше в 3,9 раза. Поэтому, как мы посчитали ранее – женщины болеют не на много чаще мужчин. Сравним этот график с общим распределением по возрасту по всем сотрудникам

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

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

 Сводные гистограммы

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

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

Аналитический свод готов!

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

Это отличные данные для подготовки инфографики в презентациях!

Советуем почитать