#excel #powerquery

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

🔹 Как сделать:
— создать папку и поместить туда все файлы (формат и структура должны быть одинаковы)
— открыть пустую книгу Excel. Вкладка Данные → Получить данные → Из файла → Из папки.
— указать путь к папке с табелями → ОК.
— Excel покажет список файлов. Жмем Объединить → Объединить и преобразовать.
— Power Query сам подгрузит все строки в одну таблицу.
— Жмем Закрыть и загрузить → “В таблицу”.
на выходе получаем объединенный файл.

Но самое классное, что PQ позволяет легко добавлять новые файлы в объединенный файл.
Для этого, когда приходят новые табели, просто кладем их в ту же папку и в Excel жмем Данные → Обновить всё.
Файл будет подгружен автоматически. Это очень удобно, обязательно попробуйте.

#Excel

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

1.

=МАКС(0;ЕСЛИ(дата_увольнения="";СЕГОДНЯ();дата_увольнения)-дата_приема)

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

2.

=ЧИСТРАБДНИ.МЕЖД(дата_приема;ЕСЛИ(дата_приема="";СЕГОДНЯ();дата_увольнения);выходные;праздники)

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

В формуле используются две переменные:
выходные — задаёт, какие дни недели считаются нерабочими. По умолчанию можно оставить пустым, тогда выходными будут сб и вск. Альтернативные значения: 1- сб , 2 - вск, 12 - вск и пн, 17 - пт и сб и суббота.
праздники — задаёт список дат, которые не учитываются при расчёте (например, федеральные праздники). Их можно задать через ссылку на диапазон ячеек, как показано в примере.

#Excel #ВПР #ГПР #Vlookup

Это буквально "Золото" и must have функции в Excel, которые позволяют вам искать значение по ключу и работать со сколь угодно большими таблицами.

⚡️ВПР позволяет искать значения по вертикали (то есть в столбцах)
Пример: Есть таблица с данными по численности персонала (справа на скрине), ваша задача найти значение численности для какого-то отдела.
Синтаксис функции:=ВПР(значение_отдела; таблица_для_поиска; номер_столбца_для_вывода; 0)
Дословно это означает: возьми значение отдела, найди его в первом столбце указанной таблицы и верни значение из того столбца, номер которого я указал.

⚡️ГПР позволяет искать значения по горизонтали (то есть в строках).
Пример: Ваша задача — найти численность для конкретного отдела (слева).
Синтаксис функции: =ГПР(значение_отдела; таблица_для_поиска; номер_строки_для_вывода; 0)
Дословно это означает: возьми значение отдела, найди его в первой строке указанной таблицы и верни значение из той строки, номер которой я указал.

#методы #прогнозирование #регрессия

⚡️Логистическая регрессия — вид регрессии, который позволяет предсказать вероятность наступления бинарного события. Бинарное событие - это такое событие у которого есть всего два исхода: 1/0, пришел/ не пришел, уволился/работает и тд.
В отличие от линейной, логистическая регрессия предсказывает вероятность (от 0 до 1), а не конкретное число.

🔹 Общий алгоритм действий
- На основе исторических данных определяются факторы, которые имеют связь с наступлением события
- Рассчитывается их вес (влияние) по формуле
- Cоставляется уравнение лог.регрессии и рассчитывается вероятность наступления события

🔹 Формула:
P = 1 / (1 + e^-(b0 + b1x1 + b2x2 + ... + bnxn)), где
P - вероятность события,
e - основание натурального логарифма,
x1...xn - переменные,
b0...bn - их коэф.

🔹 Как рассчитывать:
Чаще всего логистическую регрессию рассчитывают с помощью Python или R. Но при определенной сноровке и знаниях можно это сделать и с помощью Excel (об этом в одном из след.постов)

#прогнозирование #EMA #WMA #MA

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

Для примера имеем данные в хронолог.порядке: 10,11,12.
Задача: рассчитывать след. значение в ряду.

⚡️ Скользящая средняя(Moving Average, MA).
(10+12+11)/3 = 11

⚡️ Взвешенная скользящая средняя (Weighted MA, WMA)
(3×12 + 2×10 + 1×9)/(3+2+1) = 10.5

⚡️ Экспоненциальная скользящая средняя (Exponential MA, EMA)
EMA(t) = α × X(t) + (1 − α) × EMA_{t−1}, где:
EMA(t) - значение сглаживания на текущий момент
X(t) - реальное значение в момент t
α - коэффициент сглаживания (0 < α ≤ 1)
EMA(t−1) - значение на предыдущем шаге
• α = 0.1 — сглаживание сильное (больше инерция)
• α = 0.9 — почти как текущее значение

⚠️ Ограничения методов:
- не учитывают сезонность
- предполагают стабильность процесса
- лучше работают на коротких горизонтах
- плохо учитывают "длинные" наблюдения

#основы

Пару дней назад на занятиях делал прогноз временного ряда методом взвешенной скользящей средней. Написал формулу в Excel - на вид всё корректно, но результат был явно неверный.
Вот что было написано в ячейке: (10*3)+(11*2)+(12*1)/6.
Ответ Excel - 58. Правильный ответ - 14,6
В итоге, в чем именно состоит ошибка я понял только после занятия, когда вернулся к форме записи формулы.

В математике есть строгая последовательность выполнения операций. Если её не соблюдать - вы получите ошибку в расчете. Это может показаться очевидным, но как показывает практика такие ошибки, по невнимательности, может совершить каждый.
Сама последовательность описана на скрине, но проблема в том, что в Excel, в отличие от, например, Python, не работает логика приоритета между числителем и знаменателем - все действия выполняются строго слева направо, если не расставлены скобки, который задают приоритет расчета и сделать это может только пользователь.

так что не забывайте проверять свои формулы )

#Excel #vba

⚡️ Простенький, но довольно полезный скрипт.

🔹Что делает:
Выделяет строчку, на которой находится курсор, зеленым. Перенос курсора перенесет и подсветку на новую строку.
Можно выделить несколько строк зеленым, зажав Ctrl.
Удобно если вы работаете с большими таблицами и вам нужно смотреть значения из разных колонок на одной строке.

🔹Как вставить:
1. Alt + F11 - откроется редактор VBA.
2. В левой панели выберите нужный лист (например, Лист1).
3. Вставьте код

Dim prevRow As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Not prevRow Is Nothing Then
        prevRow.Interior.ColorIndex = xlColorIndexNone
    End If
    Target.EntireRow.Interior.Color = RGB(198, 239, 206)
    Set prevRow = Target.EntireRow
End Sub

4. Ctrl+S - сохраняем скрипт
5. После перезагрузки файла скрипт начнет работать

🔸Скрипт будет работать только в том файле и на том листе, в котором вы его написали. Остановить скрипт можно удалив его.
Лайк если полезно ))

#метрики #BUR

⚡️Активность использования корпоративных бенефитов (Benefit Utilization Rate) - простая в расчёте и полезная метрика в арсенале C&B, которая показывает, насколько сотрудники реально используют предоставленные компанией льготы типа страховки, оплаты спорта и пр. Она помогает оценить эффективность затрат и задуматься над востребованностью некоторых бенефитов.

🔹Формула:
(число сотрудников, использовавших льготу / фактическая численность, имеющих доступ к льготе) * 100%

🔹Пример:
оплата фитнеса доступна 500 фактическим сотрудникам. Воспользовались бенефитом 320 сотрудников.
320 / 500 * 100% = 64%

🔹Интерпретация:
— <50% тревожный сигнал: либо льгота не нужна, либо о ней никто не знает.
— 50–80% нормальная вовлечённость.
— >80% бенефит крайне востребован, возможно, стоит расширить.

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

#визуализация #excel #диаграммы

Тут я рассказывал про базовые диаграммы в Excel.

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

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

🔹Водопад — отлично показывает, как изменяется показатель шаг за шагом. широко применяется в C&B как инструмент для визуализации факторного анализа отклонений.

🔹Торнадо — используется в сравнении двух наборов данных. Широко применяется для демонстрации половозрастного состава.

🔹Воронка — для демонстрации изменения значений одного показателя через ряд этапов/действий. Классика жанра в рекрутменте.

🔹Ступенчатая — показывает изменения, происходящие неравномерно. Например, отображение изменений зарплаты по датам (не каждый месяц, а по фактическим повышениям). Часто применяется вкупе с другими.

#Excel

Хорошая новость - в Excel появится функция авто обновления сводных таблиц (больше не придется руками нажимать обновление каждый раз, когда в источниках сводной таблицы появляются новые записи)
Плохая новость - доступно, судя по всему, это будет только для MS Office 365.

Функция доступна в бета-канале для Excel для Windows версии 2506 (сборка 19008.2000) и более поздних версий, а также для Excel для Mac версии 16.99 (сборка 250616106) и более поздних версий.

Очень круто, но жаль, что только для 365.

#исследования

⚡️Экстраполяция — это способ распространения результатов выборки на генеральную совокупность. Когда нужно оценить поведение всей совокупности на основе небольшой выборки. Полезна в HR, когда опрос охватывает не всех сотрудников, а только выборку, но результаты нужно подбить по всем сотрудникам

🔹Пример:
вы провели опрос вовлеченности среди 200 из 1000 сотрудников. Средний балл вовлеченности — 3.8. Нужно оценит средний уровень вовлеченности по компании.
Если выборка репрезентативна, то оценка общего среднего = среднее по выборке = 3.8

🔹Чтобы оценить доверительный интервал:
Найдите стандартное отклонение по выборке (например, σ = 0.5)
Посчитайте стандартную ошибку: SE = σ / √n = 0.5 / √200 ≈ 0.035
Постройте 95% интервал: 3.8 ± 1.96 * 0.035 → [3.73; 3.87]

🔹Вывод:
с 95% уверенностью, реальная вовлеченность по всей компании лежит в этом диапазоне. То есть сделав анализ по части мы можем экстраполировать результаты этого анализа на всю компанию с высокой точностью.

#основы #численность

⚡️ В HR различают несколько типов численности: фактическая, штатная и списочная численность. К сожалению, иногда их путают.

🔹 Штатная численность — количество позиций по штатному расписанию, вне зависимости от того, заняты они или нет.
Формула: (кол-во строк в штатном расписании с любым статусом).

🔹 Фактическая численность — реально работающие сотрудники на текущий момент (не включая вакансии).
Формула: (штатная численность − вакансии).

🔹 Списочная численность — все сотрудники, которые числятся в организации, включая временно отсутствующих (отпуск, больничный, декрет). Основа для ССЧ.
Формула: (фактическая численность + принятые на работу на неполное рабочее время + надомники + практиканты ).

⚡️ Пример:
Штатная - 120 шт.ед.
Вакансии - 10

Фактическая = 120 − 10 = 110
Списочная = 110+10 +5+1= 126

Разница важна:
штатная — про структуру,
фактическая — про рабочие руки сегодня,
списочная — про всех, кто в штате юридически.

#основы #численность #ссч

В продолжение предыдущего поста - поговорим о среднесписочной численности.

🔹 Алгоритм расчета:
1. Расчет списочной численности за каждый календарный день, включая праздничные (нерабочие) и выходные дни.

❗️Списочная численность за выходные и нерабочие праздничные дни равна
показателю на предшествовавший этой дате рабочий день.

❗️Если сотрудник работает неполный день: обычная продолжительность рабочего дня (в часах) *
число рабочих дней по календарю.

2. Расчет месячного значения: сумма дней / число календарных дней месяца. Считаем с дробной частью.

3. Расчет годового значения: Сумма ССЧ за каждый месяц года / 12

❗️Если компания новая и работает неполный год, в ССЧ все равно учитывается каждый месяц. Периоды, когда организация не работала, будут считаться с показателями «0».

🔹Среднесписочная численность готовится для отчетов:
- расчет страховых взносов (РСВ) — сдается в ФНС,
- раздел 2 ЕФС-1 — сдается в СФР,
- П-4 — подается в Росстат.

методология от nalog.ru

#метрики #PPI

⚡️ Performance to Pay Index (PPI) - интересная метрика, которая показывает, насколько оплата труда сотрудника соотносится с его результативностью относительно других сотрудников в группе.
Используется для оценки индивидуальной справедливости системы вознаграждения.

🔹 Формула:
PPI = Индекс результативности / Индекс затрат на оплату труда

🔹 Как считать:
Индекс результативности = (Индивидуальный KPI / Средний KPI по группе)
Индекс затрат = Индивидуальный оклад(зп) / Средний оклад(зп) по группе

🔹 Пример:
Сотрудник А: KPI = 120, ЗП = 150 тыс.
Средние значения по группе: KPI = 100, ЗП = 120 тыс.
PPI = (120/100) / (150/120) = 1.2 / 1.25 = 0.96

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

#метрики #обучение #d_fact

Делюсь интересным методом оценки эффективности обучения.
Адекватно применить, к сожалению, можно только для треннингов по хардам и в тех случаях, когда они направлены на рост производительности - метод с расчётом d-критерия (коэффициента эффекта Коэна).

🔹 Алгоритм:
1. Соберите KPI сотрудников до и после обучения (например, продажи за месяц).
2. Рассчитайте средние значения показателя
3. Найдите общее стандартное отклонение
SD = sqrt((SD_before² + SD_after²) / 2)
4.Рассчитайте коэф:
d = (μ_after - μ_before) / SD

🔹 Интерпретация метрики:
d < 0.2 — слабый эффект на производительность.
d = 0.5 — устойчивый эффект, есть рост производительности
d > 0.8 — сильный эффект. Обучение крайне эффективно.

#основы #шкалы

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

🔹 Типы шкал:
Номинальная — категории без порядка
Пример: пол, отдел, должность.

Порядковая (ранговая) — есть порядок, но нет равных интервалов
Пример: оценки, грейды, уровни.

Интервальная — порядок + равные интервалы, но нет нуля
Пример: дата, температура.

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

#методы #вариация #коэффициент_вариации

⚡️ Коэффициент вариации (Coefficient of Variation, V) — инструмент который помогает оценить стабильность данных. Он показывает относительное рассеяние данных по отношению к среднему и позволяет сравнивать разные наборы данных.

🔹 Формула для расчета: CV = (σ / μ) * 100%, где σ — стандартное отклонение, а μ — среднее арифметическое.

Интерпретация коэффициента:
<17% - совокупность однородна
17-33 - достаточно однородна
35-40 - недостаточно однородна
>40% - неоднородна (высокий разброс значений)

🔹 Пример:
Данные о зарплатах двух команд:
Команда A: 80, 90, 100, 110, 120 (μ = 100, σ = 14,14).
Команда B: 60, 75, 105, 135, 160 (μ = 107, σ = 38,03).
CV команды A: (14,14 / 100) * 100% = 14,14%
CV команды B: (38,03 / 107) * 100% = 35,54%

Вывод: Команда B имеет более высокий коэффициент вариации, что указывает на большую разбросанность зарплат внутри команды.

#методы #ρx #коэффициент_осцилляции

⚡️ Коэффициент осцилляции (ρx) — эффективный инструмент для выявления колебаний, их оценки и определения скрытой сезонности. например, для выявления сезонности в текучести персонала.

🔹 Формула:
ρx = (макс значение за период- мин значение за период) / ср.знач

🔹 Интерпретация:
ρx < 0.3 — показатель стабилен, данные находятся в рамках одного диапазона
0.3 ≤ ρx≤ 0.6 — умеренные колебания
ρx > 0.6 — высокая нестабильность, наличие скрытой сезонности

🔹Применять можно в двух основных случаях
1. если вы "чувствуете" наличие колебаний в ряде данных, но вам нужно как-то подтвердить это на уровне цифр
2. для выявления сезонности в ряде данных за определённый период времени

#метрики #ROI

Классическая формула расчета возвратности инвестиций (ROI):
ROI = (Доход – Инвестиции) / Инвестиции * 100%
Но, если эффект от инвестиции появляется не сразу, то такая формула не совсем верна.
Более корректно будет считать ROI с учетом периода, за который проект начинает приносить выгоду.

🔹 Пример базовый ROI:
В нашем примере после проведения автоматизации высвободилось определённое кол-во часов у сотрудников, которые были пересчитаны в рубли через стоимость часа.
При базовом варианте возвратность составит 0,7 или 70%/месяц.

🔹 Пример отложенный ROI:
Расчет возвратности в случае, если эффект проявился не сразу, а спустя 3 месяца.
Отдельно рассчитываем доход с учетом дисконтирования

PV = CF / (1 + r)^(t/период), где:
CF — доход от автоматизации
r — годовая ставка дисконтирования (~0,15 для России на тек.момент)
t — количество месяцев до эффекта
период — 12 месяцев

Получаем стоимость инвестиций спустя 3 месяца - 16 416.
Применяем классическую формулу ROI и получаем возвратность в 0,64 или 64%

#прогнозирование

⚡️Бально-факторный метод прогнозирования — самый базовый способ "прогнозирования" какого-то события. Например, увольнения.
Беру в кавычки потому, что это, конечно, не прогнозирование, а просто способ по ряду характеристик определить риск ухода, например. Но, если в компании нет других инструментов, это лучше чем ничего и в какой-то мере тоже полезный срез.

🔹 Алгоритм:
1. Определяем список факторов (например: низкая ЗП, смена руководителя), который повышает риск увольнения. Факторы определяются экспертно.
2. Оцениваем по факторам сотрудников (1 - есть, 0 - нет)
3. Считаем сумму баллов.
4. Определяем балл, после которого считаем, что риск есть и нужно реагировать (в примере >=3). определяем опять же экспертно.

Базовый вариант: просто сумма баллов
Расширенный вариант: каждой хар-ке присваиваем вес. Итоговый балл: ∑ (балл * вес)