Эксель таблица формулы: Функции Excel (по категориям) — Служба поддержки Майкрософт

Создать простую формулу в Excel

Excel

Формулы и функции

Формулы

Формулы

Создать простую формулу в Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Дополнительно…Меньше

Вы можете создать простую формулу для сложения, вычитания, умножения или деления значений на листе. Простые формулы всегда начинаются со знака равенства ( = ), за которым следуют константы, представляющие собой числовые значения, и операторы вычисления, такие как плюс ( + ), минус ( — ), звездочка ( * ) или косая черта. ( / ) знаки.

Возьмем пример простой формулы.

1. org/ListItem»>

   На рабочем листе щелкните ячейку, в которую вы хотите ввести формулу.

2. Введите = (знак равенства), а затем константы и операторы (до 8192 символов), которые вы хотите использовать в расчетах.

   В нашем примере введите =1+1 .

   Примечания:

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

   • В соответствии со стандартным порядком математических операций умножение и деление выполняются перед сложением и вычитанием.

3. Нажмите Введите (Windows) или Верните (Mac).

Возьмем еще один вариант простой формулы. Тип =5+2*3 в другой ячейке и нажмите Введите или Верните . Excel умножает два последних числа и прибавляет к результату первое число.

Использовать автосумму

Вы можете использовать автосумму для быстрого суммирования столбца, строки или чисел. Выберите ячейку рядом с числами, которые вы хотите просуммировать, нажмите AutoSum на вкладке Home , нажмите Enter (Windows) или Return (Mac), и все!

Когда вы нажимаете Автосумма , Excel автоматически вводит формулу (которая использует функцию СУММ) для суммирования чисел.

Примечание. Вы также можете ввести ALT+= (Windows) или ALT++= (Mac) в ячейку, и Excel автоматически вставит функцию СУММ.

Вот пример. Чтобы добавить числа за январь в этот бюджет развлечений, выберите ячейку B7, ячейку непосредственно под столбцом чисел. Затем нажмите Автосумма . В ячейке B7 появляется формула, и Excel выделяет ячейки, которые вы суммируете.

Нажмите Enter, чтобы отобразить результат (95,94) в ячейке B7. Вы также можете увидеть формулу в строке формул в верхней части окна Excel.

Примечания:

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

• org/ListItem»>

  Создав формулу, вы можете скопировать ее в другие ячейки вместо того, чтобы вводить ее снова и снова. Например, если вы скопируете формулу из ячейки B7 в ячейку C7, формула в ячейке C7 автоматически подстроится под новое местоположение и вычислит числа в ячейках C3:C6.

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

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

Примечание. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите Введите (Windows) или Верните (Mac).

92

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

17 полезных формул и функций управления персоналом для Excel

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

Использование Excel проще, быстрее и дешевле, чем полагаться на ИТ для составления отчетов, и есть вероятность, что в вашем отделе кадров есть один или несколько человек, которые хотя бы немного разбираются в этом.

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

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

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

Содержание
Работа с датами
Работа со сводными таблицами
Форматирование таблиц
Общие формулы и показатели управления персоналом

Работа с датами

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

Excel хранит даты как последовательные порядковые номера, начинающиеся с 1, начиная с 1 января, 1900, в 00:00. 16 марта 2021 года — 44271, потому что это 44 271 ^-й -й день с начала времени Excel.

Значения менее суток сохраняются в виде десятичных дробей.

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

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

Использование функции СЕГОДНЯ()

Функция СЕГОДНЯ позволяет сэкономить время. Он получает текущую дату и время с вашего компьютера, поэтому обновляется автоматически. Ваши рабочие листы автоматически обновляются, когда вы открываете или изменяете их.

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

1. Щелкните правой кнопкой мыши заголовок столбца Age , чтобы открыть панель действий.
2. Выберите Формат ячеек…
3. Пользовательский открывает параметры; затем введите « yy » (без кавычек) в поле Введите .
4. Щелкните OK .

Нахождение разницы между двумя датами

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

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

Мы дадим вам четыре, которые мы считаем полезными и простыми в использовании:

Функция РАЗНДАТ

Excel РАЗНДАТ возвращает разницу между двумя значениями дат в годах, месяцах или днях. Функция DATEDIF (Date + Dif) — это функция «совместимости» с Lotus 1-2-3.

=DATEDIF(дата_начала, дата_окончания, единица измерения)

Единицы измерения

• «у» лет
• «м» месяцев
• «д» дней
• «ym» месяцев, без учета лет
• «yd» дней без учета годов и месяцев

Мы можем рассчитать годы, месяцы и дни, используя следующие формулы:

Чтобы получить такой результат:

Вот тот же метод с использованием лет, месяцев и дней в одной ячейке:

DAYS360 function

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

Хотя Комиссия по ценным бумагам и биржам (SEC) не принимает его для официальных опубликованных финансовых отчетов, 360-дневный коммерческий год широко используется для упрощения расчета процентов и внутренних оценок в бизнесе по всему миру. Двумя принятыми стандартами являются метод США (NASD) и европейский метод.

Синтаксис:

= ДНЕЙ360(дата_начала, дата_окончания, метод)

, где метод указывает на 360-дневный стандарт: ЛОЖЬ для американского метода и ИСТИНА для европейского метода.

• В американском методе, если начальной датой является последний день месяца, она становится 30 ^-м -м днем ​​месяца. Если конечной датой является последний день месяца, а начальной датой является более ранний, чем 30 ^-й -й день месяца, конечный день равен 1^-му -му дню следующего месяца. В противном случае конечный день становится равным 30 ^-му -му тому же месяцу.
• По европейскому методу даты начала и окончания 31 ^ст месяца равно 30 ^числа месяца. Как вам аккуратность?

Осторожно:

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

ДАТА(гггг,м,д) , где ДАТА(2021,3,11) — 11 марта 2021 г.

Если start_date находится после end_date, формула DATEDIF возвращает отрицательное число.

Функция YEARFRAC

Если вы хотите вычислить разницу между двумя датами в виде лет и долей лет, YEARFRAC сделает это за вас. Параметрами функции являются дата начала, дата окончания и базис, где базис равен от 0 до 4:

Базовые значения

Вот сама функция:
   =ДОЛЯГОД(дата_начала, дата_окончания,[базис])

Если вы используете ДОЛЯ ГОДА и функцию СЕГОДНЯ(), она всегда будет правильной.

Функция ЧИСТРАБДНИ

ЧИСТРАБДНИ — это удобная функция для управления проектами, расписаниями или в любое время, когда вам нужно рассчитать промежуток рабочих дней, включая праздники.

=ЧИСТРАБДНИ(дата_начала,дата_окончания,праздники)

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

1. Выберите диапазон ячеек $A$2:$A$10.
2. Нажмите Определить имя . Откроется окно Новое имя.
3. Введите «Праздники» в поле Имя .
4. Нажмите кнопку ОК.

Затем мы можем ввести функцию ЧИСТРАБДНИ в любом месте рабочей книги следующим образом:

Форматирование таблиц

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

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

Если вы работаете с информацией о сотрудниках, ключом почти всегда является идентификатор сотрудника. Размещение его в первом (крайнем левом) столбце каждого листа, в котором перечислены отдельные сотрудники, значительно упрощает управление функциями ВПР, ИНДЕКС, ПОИСКПОЗ и ИНДЕКС-ПОИСКПОЗ.

Форматировать как табличную функцию

Нам нравится давать имена нашим таблицам и массивам, чтобы упростить их использование. Быстрый способ сделать это — использовать функцию Excel Format as Table .

1. Убедитесь, что ваш стол смежные , что означает отсутствие промежутков между столбцами или строками.
2. На вкладке «Главная» щелкните самую верхнюю левую ячейку (A1).
3. Щелкните стрелку вниз на Format as Table . Откроется панель дизайна.

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

5. Нажмите  OK . Ваша таблица будет автоматически отформатирована.
6. Дайте таблице понятное вам имя. Теперь вы можете использовать имя таблицы в функциях и формулах.

Вы также можете использовать сочетание клавиш для создания таблиц.

1. Выделите всю таблицу.
2. Нажмите Ctrl + T

Затем вы можете отформатировать его как вам угодно.

Фильтрация данных

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

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

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

1. Щелкните значок раскрывающегося списка фильтров .

2. Очистите форму, установив флажок (Выбрать все) .

3. Выберите Не запущено , затем ОК.

Вот как будет выглядеть ваша отфильтрованная таблица:

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

Предположим, нам нужен список людей, нанятых в период с 1 января 2016 года по 31 декабря 2018 года.

1. Нажмите фильтр столбца Дата найма . Откроется список параметров фильтра.
2. Выберите параметр Фильтры даты . Теперь вы получаете длинный список параметров фильтрации.

3. Выберите Между и заполните параметры даты.

Ваш отчет готов:

Работа со сводными таблицами

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

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

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

Мы покажем вам, как создать отчет о причинах увольнения по отделам. Вы можете получить копию набора данных, который мы используем, на Kaggle (зеркало).

Вот как создать отчет из любого рабочего листа:

1. Сначала мы щелкаем верхнюю левую ячейку (A1) в непрерывной таблице данных о сотрудниках. Хорошая выборка данных будет иметь номер сотрудника, значение, уникальное для каждого человека, в столбце A.
2. Щелкаем по меню Вставить .
3. Затем мы нажимаем на значок сводной таблицы . Откроется форма Создать сводную таблицу .

4.  Excel автоматически выбирает всю нашу таблицу данных.
5. Мы выбираем Новый рабочий лист , чтобы указать Excel, куда поместить таблицу.
6. Затем нажмите OK .

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

8. В форме Поля сводной таблицы перетащите Дата завершения в раздел Значения , что даст нам числовое значение для подсчета таблицы.

9. Мы также перетаскиваем Отдел , Сотрудник Имя , Должность и Дата Срока в Строки раздел.

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

10. Щелкните в любом месте таблицы, затем щелкните дизайн. Появится меню дизайна.
11. Щелкните Макет отчета.
12. Нажимаем Показать в табличной форме . Это начинает иметь смысл, но нам не нужен промежуточный итог в столбце «Имя сотрудника».

13. Щелкните правой кнопкой мыши столбец Имя сотрудника и выберите Промежуточный итог «Имя сотрудника» , чтобы снять флажок. То же самое делаем со столбцом Position .

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

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

Еще одна тонкая г:

Поясним, почему этот инструмент называется сводной таблицей.

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

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

Теперь у нас есть отчет о причинах увольнения.

Столбцы сводной таблицы

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

Давайте удалим имена и позиции отчета, чтобы не загромождать его, а затем перетащим отдел в раздел «Столбцы». Теперь у нас есть сводный отчет, но он не очень полезен.

Давайте изменим способ суммирования данных. Щелкните правой кнопкой мыши в ячейке «Общий итог» и выберите « Показать значения как », затем % от общего итога .

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

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

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

Распространенные формулы и показатели управления персоналом

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

Стоимость вознаграждений и программ для сотрудников

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

Расходы на вознаграждения на одного работника

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

льготы_стоимость = общая_стоимость_пособий_и_программ ÷ среднее_количество_сотрудников

Вы можете разбить эту стоимость на любой период для сравнения.

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

Пособия и программы в процентах от заработной платы

Мы рассчитываем пособия к оплате в процентах.

льготы_коэффициент = стоимость_выгод ÷ общая_плата 

Общие затраты на сотрудников

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

total_employee_cost = (1 + льготы_коэффициент) × total_pay

Доход

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

Доход на одного сотрудника

Быстрый ответ на эти вопросы находится в Доход на одного сотрудника. Мы получаем высший доход из финансовых отчетов и делим его на количество сотрудников.

доход_на_сотрудника = среднее_количество_сотрудников_÷ верхний_линейный_доход

Стаж и оборот

Стаж

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

1. Отформатируйте список в виде таблицы.
2. Создайте новый столбец рядом с датой приема на работу с именем срок пребывания. Затем введите формулу в верхнюю ячейку.
   =yearfrac([hire_date],today())
3. В группе меню Число отформатируйте значение, используя значок запятая (Формат учета).
4. На вкладке «Конструктор таблиц» установите флажок «Строка итогов». Вид переместится в нижний ряд.

5. В столбце Срок владения выберите Средний функция.

Показатели привлечения талантов

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

Время для заполнения вакансии

Время, необходимое для поиска, привлечения, отбора и найма кандидата, является решающим фактором при расчете затрат на подбор персонала.

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

Сначала мы определяем таблицу с именем «Праздники», чтобы использовать ее в качестве параметра функции ЧИСТРАБДНИ.

Стоимость найма

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

Коэффициент доходности

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

Коэффициент доходности — это процент заявителей, дошедших до следующего этапа процесса.