Как посчитать количество сотрудников в excel

Обновлено: 06.07.2024

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

Метод факторного нормирования позволяет выявить ключевые факторы, которые влияют на показатель численности персонала. Эти факторы еще называют драйверами численности. Основа метода — корреляционно-регрессионный анализ. Если говорить упрощенно, то это такой распространенный прием обработки статистической информации.

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

Рассмотрим его применение на примере.

Задача экономиста по труду крупной торговой компании с развитой филиальной сетью (25 филиалов) — определить оптимальную (минимально необходимую) численность сотрудников Службы управления персоналом.

Типовые функции Службы:

кадровое делопроизводство;
подбор персонала;
обучение и развитие персонала.

Алгоритм действий экономиста по труду:

1. Определил факторы (факторные признаки), предположительно влияющие на численность сотрудников Службы управления персоналом:

X₁ — среднесписочная численность работников филиала;

X₂ — число принятых работников филиала за год;

X₃ — число уволенных работников филиала за год;

X₄ — число работников филиала, которым был предоставлен/отменен отпуск любого вида за год;

X₅ — число работников филиала, направленных в служебные командировки за год;

X₆ — число больничных листов, предъявленных работниками филиала за год;

X₇ — число заявок на подбор персонала филиала за год;

X₈ — число работников филиала, прошедших внутреннее обучение за год;

X₉ — число работников филиала, прошедших процедуру оценки за год.

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

Обратите внимание!

Основные требования к факторным признакам:

совокупность данных по выбранным признакам должна быть однородной и иметь нормальное распределение;

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

факторные признаки должны оказывать непосредственное влияние на результативный признак.

2. Сформировал таблицу исходных данных (табл. 1).

3. Проверил первичную информацию по факторным признакам (данные табл. 1) на однородность.

Для этого с помощью MS Excel рассчитал среднее арифметическое значение, среднеквадратичное отклонение и коэффициент вариации по каждому факторному признаку (табл. 2).

К сведению

Если коэффициент вариации меньше либо равен 33 %, то совокупность данных считается однородной. Если это условие не соблюдается, то совокупность не однородна.

В нашем примере однородными являются группы данных по факторным признакам X₁, X₄ (в табл. 2 они выделены цветом).

4. Скорректировал первичную информацию по факторным признакам и привел ее к однородности.

Для этого экономист по каждой группе данных по факторным признакам, не отвечающим условию однородности (коэффициент вариации больше 33 %), определил минимальные и максимальные значения (табл. 4; формулы для расчета — в табл. 5) и исключил эти значения из каждой группы. В итоге он получил новую таблицу исходных данных (табл. 6), по которой заново рассчитал коэффициенты вариации (табл. 7).

По результатам расчета не является однородной группа данных по факторным признакам X₅, X₆, X₇, X₈, X₉ (в табл. 7 они не выделены цветом). Экономист по труду исключил эти данные из последующих расчетов.

Порядок действий при построении матрицы коэффициентов в MS Excel для наглядности представлен также на рис. 1–4.

Н. А. Порываева,
экономист по труду

Как оперативно скорректировать численность персонала с помощью Excel?

Рассмотрим его применение на примере.

Типовые функции Службы:

кадровое делопроизводство;
подбор персонала;
обучение и развитие персонала.

Алгоритм действий экономиста по труду:

X₁ — среднесписочная численность работников филиала;

X₂ — число принятых работников филиала за год;

X₃ — число уволенных работников филиала за год;

X₄ — число работников филиала, которым был предоставлен/отменен отпуск любого вида за год;

X₅ — число работников филиала, направленных в служебные командировки за год;

X₆ — число больничных листов, предъявленных работниками филиала за год;

X₇ — число заявок на подбор персонала филиала за год;

X₈ — число работников филиала, прошедших внутреннее обучение за год;

X₉ — число работников филиала, прошедших процедуру оценки за год.

Обратите внимание!

Основные требования к факторным признакам:

совокупность данных по выбранным признакам должна быть однородной и иметь нормальное распределение;

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

факторные признаки должны оказывать непосредственное влияние на результативный признак.

2. Сформировал таблицу исходных данных (табл. 1).

3. Проверил первичную информацию по факторным признакам (данные табл. 1) на однородность.

К сведению

4. Скорректировал первичную информацию по факторным признакам и привел ее к однородности.

Порядок действий при построении матрицы коэффициентов в MS Excel для наглядности представлен также на рис. 1–4.

Функция СЧЁТЕСЛИМН применяет критерии к ячейкам в нескольких диапазонах и вычисляет количество соответствий всем критериям.

Это видео — часть учебного курса Усложненные функции ЕСЛИ.

Синтаксис

Аргументы функции СЧЁТЕСЛИМН описаны ниже.

Диапазон_условия1. Обязательный аргумент. Первый диапазон, в котором необходимо проверить соответствие заданному условию.

Условие1. Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку или текста, которые определяют, какие ячейки требуется учитывать. Например, условие может быть выражено следующим образом: 32, ">32", B4, "яблоки" или "32".

Диапазон_условия2, условие2. Необязательный аргумент. Дополнительные диапазоны и условия для них. Разрешается использовать до 127 пар диапазонов и условий.

Важно: Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и аргумент диапазон_условия1. Эти диапазоны могут не находиться рядом друг с другом.

Замечания

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

Если аргумент условия является ссылкой на пустую ячейку, то он интерпретируется функцией СЧЁТЕСЛИМН как значение 0.

В условии можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (

Пример 1

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

С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).

Самая простая функция СЧЁТЕСЛИ означает следующее:

=СЧЁТЕСЛИ(где нужно искать;что нужно найти)

СЧЁТЕСЛИ(диапазон;критерий)

Имя аргумента

диапазон (обязательный)

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

критерий (обязательный)

Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.

Например, критерий может быть выражен как 32, ">32", В4, "яблоки" или "32".

В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.

Примеры

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

Количество ячеек, содержащих текст "яблоки" в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст "персики" (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст "яблоки" (значение ячейки A2) и "апельсины" (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, не равным 75, в ячейках В2–В5. Знак амперсанда (&) объединяет оператор сравнения "<>" (не равно) и значение в ячейке B4, в результате чего получается формула =СЧЁТЕСЛИ(B2:B5;"<>75"). Результат — 3.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 3.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак "*" обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами "ки", в диапазоне A2–A5. Подставочный знак "?" обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Возможная причина

Для длинных строк возвращается неправильное значение.

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;"длинная строка"&"еще одна длинная строка").

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Поскольку отделов немного (три), для наглядности разместим таблицу с расчетом в диапазоне ячеек G1:H4 того же рабочего листа, на котором располагается спи-сок (рис. 5.2).

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

В ячейки G1 и H1 введите названия столбцов (Отдел и Численность), а в ячейки G2:G4 - названия отделов (Контроля, Реализации, Снабжения).

В ячейки Н2:Н4 мы должны поместить формулы, с помощью которых будут производиться вычисления. Чтобы определить общее количество сотрудников в отделе, необходимо подсчитать, сколько ячеек с названием данного отдела имеется в диапазоне А2:А11 (то есть сколько раз упоминается в списке название отдела, столько в нем и сотрудников).

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

Здесь диапазон - это интервал, в котором подсчитывается количество ячеек. В данном случае таковым является А2:А11. Аргумент условие должен представлять собой число, выражение или текст и определять, какие именно ячейки надо подсчитывать. В нашем примере условие соответствует названию отдела - Контроля. Панель функции СЧЕТЕСЛИ представлена на рис. 5.3.

Название отдела можно внести непосредственно во второй аргумент функции СЧЕТЕСЛИ, как показано на рис. 5.3. Но значительно проще вместо текста поместить туда ссылку на ячейку G2, в которой таковой находится. Для заполнения ячеек Н2:Н4 формулами, выполните следующие действия:

Выделите диапазон ячеек Н2:Н4.
Вызовите посредством мастера функций панель функции СЧЕТЕСЛИ.
Поместите курсор в поле Диапазон и мышью выделите на рабочем листе ячейки А2:А11.
Нажмите функциональную клавишу [F4] для создания абсолютной ссылки на диапазон ячеек.
Перейдите в поле Условие и щелкните мышью на ячейке G2.
Нажмите комбинацию клавиш [Ctrl+Enter].

В результате этих действий в ячейке Н2 будет создана формула

Подобные формулы появятся также в ячейках НЗ и Н4, но во втором их аргументе будут указаны соответственно адреса ячеек G3 и G4.

Читайте также: