Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как посчитать количество вхождений за год / квартал / месяц / неделю в Excel?

Как рассчитать премию сотрудника от оклада?

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

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

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

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

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

Как правило, документом, содержащим информацию о порядке премирования, становится положение об оплате труда или положение о премировании. С 2017 года эти документы, являющиеся правилами внутреннего распорядка, указаны в Трудовом кодексе РФ (статья 309.2) как необязательные для микропредприятий. Однако при отсутствии такого документа правила работодателя о премировании должны быть прописаны в трудовом договоре, заключенном с каждым работником. Фактически, такая же процедура, позволяющая отражать условия премирования в нескольких внутренних документах (включая трудовой договор), действовала и до нововведений 2017 года. Всегда считалось предпочтительным составлять отдельный документ о правилах премирования для всех сотрудников одновременно, так как это сокращает объем информации, которую необходимо включать в трудовые договоры.

О том, какие моменты должны найти отражение в нормативном акте о премировании в отношении премий за квартал, читайте в статье «Расчет квартальной премии за фактически отработанное время».

Какие коэффициенты применяются при расчете премии

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

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

  • С начала периода, за который начисляется премия
  • С периода, следующего за повышением заработной платы
  • С учетом доли дней (календарных или рабочих) в периоде изменения заработной платы, приходящихся на каждую выплату.

Последний вариант потребует расчета коэффициентов, учитывающих соответствующие пропорции числа дней.

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

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

О налогообложении начисленных премиальных читайте в материале «Какими налогами и взносами облагается премия сотрудникам?».

Пример расчета премии в файле Excel (он же — калькулятор)

Рассмотрим пример расчета премии сотрудникам на цифрах. Предположим, что в ООО «Импульс» установлена начисляемая поквартально премия, если ООО за соответствующий квартал достигнуты определенные финансовые результаты по итогам работы. Размер премии составляет 30% от оклада.

Читайте так же:
Как очистить фильтры при открытии, сохранении или закрытии книги в Excel?

Согласно положению о премировании компании, премия не выплачивается за месяцы, в которых работа не была выполнена в полном объеме. В случае квартальной премии это означает, что сумма, рассчитанная как доля 30% от заработной платы, должна быть умножена на коэффициент, основанный на количестве месяцев, отработанных в квартале. Поэтому соотношение может быть 2/3 или 1/3.

Если дисциплинарный проступок совершен в период действия премии, работник лишается премии за этот период в полном объеме.

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

В регионе работы ООО действует районный коэффициент к зарплате, равный 1,4.

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

Учет премии при расчете среднего заработка

Премии, предусмотренные системой оплаты труда и имеющие установленные правила их начисления, учитывают в расчете среднего заработка (ст. 139 ТК РФ). Однако они по-разному входят в этот расчет в зависимости от ряда обстоятельств (положение об особенностях порядка исчисления среднего заработка, утвержденное постановлением Правительства РФ от 24.12.2007 № 922):

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

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

  • полностью — невзирая на то, что она может оказаться начисленной:
    • Не за расчетный период;
    • Вне расчетного периода;
    • Без учета фактического времени работы;

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

    • За разные периоды (месяц, квартал, год и другие промежутки времени, а также на разовой основе);
    • Не в тот период, за который они заработаны;
    • Как с учетом, так и без учета фактического рабочего времени в периоде приобретения.

    Обо всех возникающих вариантах учета премий в расчете среднего заработка читайте в материале «Учитывается ли премия при расчете отпускных?».

    Итоги

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

    Стероиды для данных в Google Data Studio или руководство по вычисляемым полям

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

    Где вы можете найти самые красивые визуализации? Правильно, в Google Data Studio

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

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

    Что такое вычисляемые поля?

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

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

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

    Что можно делать с вычисляемыми полями:

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

    Как создать рассчитываемое поле?

    1. Откройте источник данных.
    2. В правом верхнем углу нажмите ДОБАВИТЬ ПОЛЕ .
    3. Введите имя для этого поля (имя должно быть уникальным и не должно повторять существующие названия параметров и показателей).
    4. Введите формулу.
    5. Сохраните новое поле.

    Пример создания вычисляемого поля

    Функции

    Вот мы и подобрались к самому интересному и мощному инструменту Data Studio — функциям.

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

    По моей субъективной оценке, все функции можно разделить на 5 крупных блоков.

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

    Самое полезное

    CASE WHEN Страна = ‘Россия’ THEN ‘Материк: Евразия’ ELSE ‘Другие материки’ END — вычисляет значение, основанное на наборе заданных булевых выражений.

    В режимах WHEN могут использоваться следующие арифметические операторы:

    • =
    • !=
    • >
    • >=
    • <
    • <=

    Следующее логично:

    • AND
    • OR

    Он также выполняет поиск с помощью регулярных выражений и списков:

    • REGEXP_MATCH
    • IN

    CAST(Поле или выражение AS Тип) — преобразование типов (параметр типа может иметь значение NUMBER или TEXT).

    Агрегирование

    MAX(X) — возвращает максимальное значение X.

    MIN(X) — возвращает минимальное значение X.

    SUM(X) — возвращает сумму всех значений X.

    AVG(X) — возвращает среднее всех значений X.

    COUNT(X) — возвращает количество значений X.

    COUNT_DISTINCT(X) — возвращает количество уникальных значений X.

    MEDIAN(X) — возвращает медиану всех значений X.

    PERCENTILE(X,Y) — возвращает N-ю процентиль всех значений X. N представляет собой значение с плавающей запятой в диапазоне от 0 до 100 (не включительно).

    VARIANCE(X) — возвращает отклонение (дисперсию) X.

    Текстовые функции

    CONCAT(X, Y) — возвращает текст, представляющий собой объединение X и Y.

    HYPERLINK (URL, Текст ссылки) — возвращает гиперссылку.

    UPPER(Поле или выражение) — преобразует X в верхний регистр.

    LOWER(Поле или выражение) — преобразует X в нижний регистр.

    REGEXP_EXTRACT(Поле или выражение, Регулярное выражение) — возвращает первую совпадающую подстроку в X, которая соответствует регулярному выражению.

    REGEXP_MATCH(X, Регулярное выражение) — возвращает true, если X равен Y, в противном случае – false. Используется только в условии WHEN утверждений CASE, а не в качестве самостоятельной функции.

    REGEXP_REPLACE(A, ‘[a-z]+’, ‘A’) — заменяет в Х все вхождения регулярного выражения на указанную строку.

    REPLACE(Поле или выражение, Искомое значение, Заменяющее значение) — возвращает копию X, в которой все вхождения Y заменены на Z

    SUBSTR(Поле или выражение, Начальная позиция, Длина) — позволяет извлечь из выражения его часть заданной длины, начиная от начальной позиции.

    TRIM(Поле или выражение) — возвращает X с удалением начального и конечного пробелов.

    LENGTH(Поле или выражение) — возвращает количество символов в X.

    Функции даты

    Дата и время могут быть введены в следующих форматах:

    ‘BASIC’ — %Y/%m/%d-%H:%M:%S.
    ‘DEFAULT_DASH’ — %Y-%m-%d [%H:%M:%S].
    ‘DEFAULT_SLASH’ — %Y/%m/%d [%H:%M:%S].
    ‘DEFAULT_DECIMAL’ — %Y%m%d [%H:%M:%S.
    ‘RFC_1123’ — например, Sat, 24 May 2008 20:09:47 GMT.
    ‘RFC_3339’ — например 2008-05-24T20:09:47Z.
    ‘SECONDS’ — секунд с начала отсчета.
    ‘MILLIS’ — милисекунд с начала отсчета.
    ‘MICROS’ — микросекунд с начала отсчета.
    ‘NANOS’ — наносекунд с начала отсчета.
    ‘JULIAN_DATE’ — дней с начала отсчета.
    ‘DECIMAL_DATE’ — то же, что ‘DEFAULT_DECIMAL’ .

    DATE_DIFF(X, Y) — возвращает разницу в днях между X и Y (X — Y).

    DAY(X, Формат ввода) — возвращает значение дня для X.

    HOUR(X, Формат ввода) — возвращает значение часа для X по всемирному координированному времени.

    MINUTE(X, Формат ввода) — возвращает значение минут для X по всемирному координированному времени.

    MONTH(X, Формат ввода) — возвращает значение месяца для X.

    QUARTER(X, Формат ввода) — возвращает значение квартала для X

    SECOND(X, Формат ввода) — возвращает значение секунд для X по всемирному координированному времени.

    TODATE(X, Формат ввода, Формат вывода) — возвращает поле даты и времени в заданном формате для часового пояса UTC.

    WEEK(X) — возвращает неделю для X с начала года в соответствии со стандартом ISO 8601.

    WEEKDAY(X, Формат ввода) — возвращает день недели для X.

    YEAR(X, Формат ввода) — возвращает год для X.

    YEARWEEK(X) — возвращает год и неделю для X в соответствии со стандартом ISO 8601.

    Арифметические функции

    NARY_MAX(X, Y, [Z]*) — возвращает максимальное значение X, Y, [, Z] *. Все входные аргументы должны быть одного типа: все числа или все текст. По крайней мере один входной аргумент должен быть полем или выражением, содержащим поле.

    ACOS(X) — возвращает обратный гиперболический косинус X.

    ATAN(X) — возвращает обратный гиперболический тангенс X.

    ABS(X) — возвращает абсолютное значение X.

    COS(X) — возвращает косинус X.

    FLOOR(X) — возвращает наибольшее целое значение X. Например, если значение X равно v, FLOOR (X) меньше или равно v.

    LOG(X) — возвращает натуральный логарифм X.

    LOG10(X) — возвращает логарифм к основанию 10 из X.

    ASIN(X) — возвращает обратный гиперболический синус X.

    NARY_MIN(X, Y, [Z]*) — возвращает минимальное значение X, Y, [, Z] *. Все входные аргументы должны быть одного типа, все числа или все текст. По крайней мере один входной аргумент должен быть полем или выражением, содержащим поле.

    POWER(X, Y) — возвращает результат возведения числа X в степень Y.

    ROUND(X, Y) — возвращает X округленных до Y цифр точности.

    SIN(X) — возвращает синус X.

    SQRT(X) — возвращает квадратный корень из X (X должен быть неотрицательным).

    TAN(X) — возвращает тангенс X.

    CEIL(X) — возвращает наименьшее целое значение X. Например, если значение X равно v, CEIL (X) больше или равно v.

    Геофункции

    Ввод географических данных возможен в следующих форматах:

    ‘CITY_ID’ — код города.
    ‘CONTINENT_CODE’ — код континента.
    ‘COUNTRY_ISO_CODE’ — код страны.
    ‘REGION_ISO_CODE’ — код региона.
    ‘SUB_CONTINENT_CODE’ — код субконтинента.

    Действующие геокоды определяются здесь:

    TOCITY(X, Формат ввода) — возвращает название города для X.

    TOCOUNTRY(X, Формат ввода) — возвращает название страны для X.

    TOREGION(X, Формат ввода) — возвращает название области для X.

    TOSUBCONTINENT(X, Формат ввода) — возвращает название субконтинента для X.

    TOCONTINENT(X, Формат ввода) — возвращает название континента для X.

    Кейсы!

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

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

    И да, я понимаю что многое из того, что я привожу ниже как примеры, можно настроить при помощи фильтров, пользовательских параметров и прочих фишек в GA. Но моя задача, показать вам как работают функции в Data Studio, плюс кому-то удобнее модифицировать данные на уровне отчетности, а не сбора.

    Соедините имя хоста и путь к странице

    Если фильтр, объединяющий хост и путь, не был настроен в Google Analytics, это вполне можно сделать на уровне визуализации.

    Извлекаем параметры из URL

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

    Сократите имена источников трафика до строчных букв

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

    Например, помимо канала ‘cpc’, у нас могут быть каналы ‘Cpc’ или ‘CPC’.

    Подсчет числа уникальных идентификаторов клиента

    Если вы хотите подсчитать любой параметр, содержащий строки, будь то Client ID, Session ID или User ID (которые должны быть предварительно введены в GA как параметры пользователя), вам понадобится функция COUNT_DISTINCT для подсчета уникальных значений и COUNT — для неуникальных.

    Создание номера из текста

    В источнике данных Google Analytics Длительность сеанса является текстовым параметром. Чтобы использовать этот параметр для каких-нибудь дальнейших вычислений, нам необходимо изменить его тип на NUMBER.

    Подсчитайте количество сеансов длительностью более 60 секунд.

    Ранее мы создали числовое вычисляемое поле, содержащее продолжительность сеанса. Далее давайте выясним, сколько сеансов длится дольше одной минуты на этом сайте.

    Группировка страниц по типу

    Чаще всего на сайте существует определенное количество типов страниц, например для интернет магазина — это каталог, карточка товара, корзина, персональный раздел, информационные страницы. Что делать, если мы ходим анализировать не конкретные страницы, а их кластеры? Правильно, разобьем страницы на типы при помощи CASE.

    Подсчет числа страниц каждого типа

    В предыдущем примере мы разделили страницы на определенные типы, но что если теперь мы захотим подсчитать количество страниц каждого типа. На помощь CASE приходит арифметическая функция SUM.

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

    Иногда группы каналов по умолчанию, предлагаемой Google Analytics, нам недостаточно. И поэтому мы создаем свои собственные с помощью блэкджеков и CASE.

    Создание индивидуальной воронки продаж

    Грех не вставить сюда ссылку на мою прошлую статью — Как визуализировать воронку продаж в Google Data Studio? В которой я подробно разбираю, как при помощи CASE и REGEXP_MATCH и уличной магии создать собственную воронку.

    Формулы и функции в Google Data Studio

    Используя математические формулы, вы можете создавать дополнительные метрики с помощью Google Data Studio, что позволяет обогатить ваш отчет новыми показателями.

    Список формул и функций в Google Data Studio

    В настоящее время все формулы описаны только на английском языке и могут быть найдены в справке Data Studio:

    Ниже приведен список функций Data Studio, переведенных на русский язык:

    СинтаксисОписание
    APPROX_COUNT_DISTINCT(X)Возвращает приблизительное количество уникальных значений X
    AVG(X)Среднее значение всех значений X
    COUNT(X)Количество значений X
    COUNT_DISTINCT(X)Количество уникальных значений X
    MAX(X)Максимальное значение X
    MEDIAN(X)Медиану всех значений X
    MIN(X)Минимальное значение X
    PERCENTILE(X,N)Процентиль ранга N поля X
    STDDEV(X)Стандартное отклонение X
    SUM(X)Сумма всех значений X
    VARIANCE(X)Дисперсия X
    ABS(X)Абсолютное значение числа
    ACOS(X)Обратный косинус X
    ASIN(X)Обратный синус X
    ATAN(X)Число, обратное касательной к X
    CEIL(X)Ближайшее целое число больше X. Например, если значение X равно v, CEIL (X) больше или равно v
    COS(X)Косинус X
    FLOOR(X)Ближайшее целое число меньше X. Например, если значение X равно v, FLOOR (X) равно или меньше v
    LOG(X)Логарифм к основанию 2 из X
    LOG10(X)Логарифм к основанию 10 X
    NARY_MAX(X, Y [,Z]*)Максимальное значение X, Y, [, Z] *. Все входные аргументы должны быть одного типа: все числа. По крайней мере один входной аргумент должен быть полем или выражением, содержащим поле
    NARY_MIN(X, Y [,Z]*)Минимальное значение X, Y, [, Z] *. Все входные аргументы должны быть одного типа, все числа. По крайней мере один входной аргумент должен быть полем или выражением, содержащим поле
    POWER(X, Y)Результат возведения X в степень Y
    ROUND(X, Y)X с округлением до Y прецизионных цифр
    SIN(X)Синус X
    SQRT(X)Квадратный корень из X. Обратите внимание, что X должен быть неотрицательным
    TAN(X)Тангенс X
    DATE_DIFF(X, Y)Разница в днях между X и Y (X — Y)
    DAY(X [,Input Format])День месяца
    HOUR(X [,Input Format])Час суток
    MINUTE(X [,Input Format])Минуты
    MONTH(X [,Input Format])Месяц
    QUARTER(X [,Input Format])Квартал года
    SECOND(X [,Input Format])Секунды
    TODATE(X, Input Format, Output Format)Превращает дату в формате UTC в нужный формат, например «%Y» возвращает год
    WEEK(X)Неделя года в формате ISO 8601
    WEEKDAY(X [,Input Format])День недели
    YEAR(X [,Input Format])Год
    YEARWEEK(X)Номер года и недели
    TOCITY(X [,Input Format])Название города
    TOCONTINENT(X [,Input Format])Название континента
    TOCOUNTRY(X [,Input Format])Название страны
    TOREGION(X [,Input Format])Название региона
    TOSUBCONTINENT(X [,Input Format])Имя субконтинента
    CASE
    WHEN C = ‘yes’
    THEN ‘done:yes’
    ELSE ‘done:no’
    END
    Оценивает ровно одно значение на основе набора заданных логических выражений. Выучить больше
    CAST(field_expression AS TYPE)Превращает поле или выражение в TYPE. Агрегированные поля не допускаются внутри CAST. TYPE может быть NUMBER или TEXT
    HYPERLINK(URL, link label)Превращает гиперссылку на URL, помеченную меткой ссылки
    IMAGE(Image URL, [Alternative Text])Создает изображение из картинки в источнике данных
    CONCAT(X, Y)Возвращает текст , который является конкатенация X и Y
    CONTAINS_TEXT(X, text)Возвращает true, если X содержит текст, в противном случае возвращает false. Чувствительный к регистру
    ENDS_WITH(X, text)Возвращает true, если X заканчивается текстом, в противном случае возвращает false. Чувствительный к регистру
    LEFT_TEXT(X, length)Количество символов с начала X . Количество символов указывается по длине
    LENGTH(X)Количество символов в X
    LOWER(X)Преобразует X в нижний регистр
    REGEXP_EXTRACT(X, regular_expression)Возвращает первую совпадающую подстроку в X, которая соответствует шаблону регулярного выражения
    REGEXP_MATCH(X, regular_expression)Возвращает true, если X соответствует шаблону регулярного выражения, в противном случае возвращает false
    REGEXP_REPLACE(X, regular_expression, replacement)Заменяет все вхождения текста , который соответствует шаблону регулярного выражения в X с заменой строкой
    REPLACE(X, Y, Z)Возвращает копию X со всеми вхождениями Y в X заменяется на Z
    RIGHT_TEXT(X, length)Указывает количество символов с конца X . Количество символов указывается по длине .
    STARTS_WITH(X, text)Возвращает true, если X начинается с текста. В противном случае возвращает false. Чувствительный к регистру.
    SUBSTR(X, start index, length)Возвращает текст , который является подстрока X . Подстрока начинается с начального индекса и имеет длину символов.
    TRIM(X)Возвращает X с удаленными начальными и конечными пробелами.
    UPPER(X)Преобразует X в верхний регистр.

    Как использовать формулу в Google Data Studio

    В датасете

    Для этого в режиме редактирования необходимо перейти на вкладку «Ресурсы»-«Добавленные источники данных».

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector