Сегодня хочу поделиться своим любимым набором функций в Excel.
1. ЕСЛИ – относится к логическим функциям и позволяет делать расчеты для одного или нескольких условий.
Например, условие выплаты премии: если процент выполнения KPI больше 80%, то сотрудник получает премию в размере 10%, если меньше или равно 80%, то 0.
Функция Если может дополняться другими функциями, например:
- СУММЕСЛИ – расчет суммы в зависимости от условий
- СРЗНАЧЕСЛИ – расчет среднего в зависимости от условий
- СЧЁТЕСЛИ – подсчет элементов в зависимости от условий.
Также есть еще функции СУММЕСЛИМН, СРЗНАЧЕСЛИМН и СЧЁТЕСЛИМН. Это функции применяются, если у нас есть несколько условий.
Например, нужно посчитать сумму премий, выплаченных сотрудникам в четырех регионах с рейтингом результативности ниже 3.
В решении этой задачи поможет функция СУММЕСЛИМН, так как у нас несколько условий: 1 – регион, 2 – Рейтинг результативности.
2. СУММПРОИЗВ – данная функция суммирует произведения данных. То есть у нас есть 2 набора данных, которые сначала нужно попарно перемножить, а потом суммировать.
Есть условия: эти наборы данных должны быть одинаково расположены (то есть горизонтальными либо вертикальными) и содержать одинаковое количество значений.
Например, нам нужно посчитать процент фактической премии в зависимости от доли KPI в общей премии и фактический процент выполнения.
С помощью функции СУММПРОИЗВ мы можем легко посчитать этот процент.
3. ВПР – позволяет подставить значения одной таблицы в другую при условии, что в обеих таблицах есть одинаковые или похожие столбцы.
Например, нужно по табельному номеру сотрудника из одной таблицы подтянуть информацию в другую. В этом случае и нужна функция ВПР.
Например, нам нужно из таблицы 2 подтянуть процент целевой премии в таблицу 1 по табельному номеру.
С помощью функции ВПР это можно сделать за 5 секунд.
4. ГПР – позволяет подставить значения одной таблицы в другую при условии, что в обеих таблицах есть одинаковые или похожие строки. Функция ГПР используется при горизонтальном расположении данных.
5. Сочетание функций ИНДЕКС+ПОИСКПОЗ – сочетание этих двух функций дает большие возможности подставить значения из матрицы, чего не могут сделать функции ВПР/ГПР.
Например, у нас есть матрица повышения зарплаты, в которой по горизонтали указан рейтинг результативности сотрудника, а по вертикали указано отношение целевой зарплаты сотрудника к рыночным данным. На пересечении этих данных мы распределяем процент повышения зарплаты.
Мы хотим сделать предложение по повышению зарплаты, опираясь на данные матрицы.
Для этого мы используем 2 функции ПОИСКПОЗ для поиска по горизонтали и по вертикали для поиска значений. А функция ИНДЕКС ищет значение на их пересечении.
Эти пять функций мне очень помогают в работе. Рекомендую обратить на них внимание, если до этого не использовали. Они заметно сократят время на обработку данных в Excel.
Если вы хотите подробнее изучить эти и другие функции, приглашаю присоединиться к курсу «Excel для HR».