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

Примеры решения задач

В таблице Сотрудники, используя возможность Excel Промежуточ­ные итоги, рассчитать:

(1) средний оклад сотрудников каждой должности;

(2) количество сотрудников по каждому уровню образования.

Порядок выполнения

(1) Рассчитываем средний оклад по должностям

Шаг 1. На листе Итоги (1) сортируем записи списка по тому полю, по которому требуется подвести промежуточные итоги (в данном случае по столбцу Должность). Для быстрой сортировки выделяем любую ячейку в столбце Должность и на вкладке Данные в группе Сортировка и фильтр нажимаем кнопку (Сортировка от А до Я).

Шаг 2. Выделяем одну (любую) ячейку списка, либо весь список целиком, включая строку заголовков. На вкладке Данные в группе Структура выбираем команду Промежуточный итог.

Шаг 3. В диалоговом окне Промежуточные итоги задаем нужные параметры:

в поле При каждом изменении в выбираем столбец, по которому требуется подвести итоги (Должность);

в поле Операция в раскрывающемся списке выбираем функцию, с помощью которой будут вычисляться итоги (Среднее);

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

Закрываем окно кнопкой OK. Результат представлен ниже.

Шаг 4. На полосе структуры (в левой верхней части экрана) щелкнем по цифре 2, чтобы отобразить данные 2-го уровня. Результат:

(2) Рассчитываем количество сотрудников по уровням образования

Шаг 1. На листе Итоги (2) сортируем записи списка по столбцу Образование.

Шаг 2. Выбираем команду Промежуточный итог.

Шаг 3. Задаем нужные параметры:

в поле При каждом изменении в выбираем Образование;

в поле Операция в раскрывающемся списке выбираем функцию Количество;

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

Закрываем окно кнопкой OK. Результат представлен ниже.

Шаг 4. Отобразим данные 2-го уровня. Результат:

По данным таблиц трех рабочих листов — Сотрудники фирмы, Филиал 1 и Филиал 2 вычислить, используя возможность Excel Консолидация, средний стаж и оклад сотрудников каждой должности. Лишние столбцы консолидированного отчета удалить.

Порядок выполнения

Шаг 1. На отдельном листе рабочей книги (лист Консолидация) выделим ячейку, представляющую левый верхний угол области будущего консолидированного отчета (например, A5).

Шаг 2. На вкладке Данные в группе Работа с данными выберем команду Консолидация.

Шаг 3. В открывшемся диалоговом окне Консолидация в группе Использовать в качестве имен установим флажки подписи верхней строки и значения левого столбца.

Шаг 4. В диалоговом окне Консолидация зададим нужные параметры:

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

Установим курсор в поле Ссылка, затем перейдем на лист Сотрудники фирмы, выделим первую исходную область для консолидации данных (диапазон ячеек C2:J28) и нажмем кнопку Добавить.

Примечание: диапазон следует выделять таким образом, чтобы он включал в себя все необходимые данные в соответствии с поставленной задачей (см. условие задачи), причем в качестве его левого столбца должно быть представлено то поле, по которому требуется объединять (консолидировать) данные. В данном примере следует выделять диапазон, начиная со столбца Должность и завершая столбцом Оклад. Все прочие столбцы (Фамилия, Имя) остаются за пределами выделенного диапазона. Строка, содержащая заголовки столбцов, также должна быть включена в диапазон.

Вернемся в поле Ссылка и повторим действие п. b) поочередно для двух остальных диапазонов, данные из которых будут участвовать в консолидации: (диапазон E3:L18 листа Филиал 1, затем диапазон D5:K19 листа Филиал 2).

Закрываем окно Консолидация кнопкой OK. Предварительный результат консолидации представлен ниже.

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

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

Порядок выполнения

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

Шаг 2. На вкладке Вставка в группе Таблицы нажмем кнопку Сводная таблица.

В диалоговом окне Создание сводной таблицы выберем вариант Выбрать таблицу или диапазон (Excel определит диапазон для отчета сводной таблицы автоматически).

Шаг 3. Выберем пункт На существующий лист, укажем в поле Диапазон адрес ячейки A33 – первой ячейки будущей сводной таблицы, и нажмем ОК. Microsoft Excel добавит пустую сводную таблицу в указанное место и откроет список полей сводной таблицы (в правой части окна).

Шаг 4. В списке полей сводной таблицы выполним следующие действия:

щелкнем имя поля Должность в разделе полей и, удерживая кнопку нажатой, перетащим его в область полей строк макета;

имя поля Образование перетащим в область полей столбцов;

имя поля Оклад перетащим в область полей значений.

Шаг 5. К числовым полям, помещенным в область данных сводной таблицы, Excel по умолчанию применяет функцию «Сумма«, а к любым нечисловым полям — «Количество значений«. Для изменения функции щелкнем правой кнопкой мыши любую ячейку в области значений, в раскрывшемся меню выберем пункт Параметры полей значений.

Шаг 6. В открывшемся окне Параметры поля значений в списке Операция выберем функцию Среднее (см. условие задачи) нажмем OK.

Сводная таблица будет иметь вид:

Шаг 7. Щелкнем любую ячейку сводной таблицы и перетащим имя поля Отдел в область полей фильтра отчета.

Окончательный вариант сводной таблицы представлен ниже.

На основе Сводной таблицы, полученной в Примере 3, создать Объемную гистограмму на текущем листе. Отфильтровать информацию по сотрудникам отдела Маркетинг, имеющим высшее и среднее специальное образование.

Порядок выполнения

Шаг 1. Создадим копию листа Сводная таблица: щелкнем правой кнопкой мыши по ярлыку листа, в раскрывшемся меню выберем пункт Переместить или скопировать, в следующем окне установим флажок Создать копию, укажем местоположение листа и нажмем OK.

Шаг 2. На появившемся листе Сводная таблица (2) выберем сводную таблицу, кликнув по ней.

Шаг 3. На вкладке Вставка в группе Диаграммы выберем тип диаграммы Объемная гистограмма. В результате появится готовая гистограмма, содержащая данные сводной таблицы.

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

Шаг 5. На гистограмме в раскрывающемся списке Отдел выберем Маркетинг, в раскрывающемся списке Образование выберем Высшее и Среднее специальное.

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

Примечание: представленная сводная диаграмма получена в MS Excel 2013. Вид диаграммы, построенной в MS Excel других версий может несущественно отличатся от приведенной, в частности, раскрывающиеся списки для фильтрации по определенным полям (Отдел, Образование и Должность) могут отсутствовать на диаграмме. В этом случае следует воспользоваться кнопками выбора, представленными в отдельном окне, либо непосредственно в сводной таблице.

Используя инструмент Таблица данных, рассчитать размер ежемесячных платежей по кредиту в 100 000 р., выданному на 12, 18, 24, 30, 36 месяцев под 8%, 9%, 10%, 11% и 12% годовых.

Порядок выполнения

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

Шаг 2. Создадим для ячеек B4:B6 соответствующие имена (взяв их из столбца слева), для этого выделим блок ячеек A4:B6, затем на вкладке Формулы в группе Определенные имена выберем Создать из выделенного и в открывшемся окне щелкнем OK.

Шаг 3. Введем в ячейку E5 формулу, вычисляющую величину выплаты за один месяц. В качестве аргументов-параметров (срока, размера кредита и процентной ставки) используем начальные значения, размещенные в ячейках B4:B6.

Шаг 4. Выделим блок ячеек E5:J12, содержащий значения параметров и функцию.

Шаг 5. На вкладке Данные в группе Работа с данными в списке команды Анализ «что если» выберем Таблица данных. В открывшемся окне Таблица данных укажем, в какую ячейку подставлять значения параметра, изменяющиеся по столбцам (т.е. значения сроков кредитования), а в какую — изменяющиеся по строкам (в нашем примере – различные процентные ставки). Укажем ячейки, где находятся начальные значения соответствующих аргументов вычисляемой функции: по столбцам – ячейку с исходным сроком кредита B5, по строкам – ячейку с исходной процентной ставкой B6.

Шаг 6. Нажмем ОК, и диапазон F6:J12 будет заполнен значениями сумм выплат.

Составить Сценарии накопления денежных средств на основе первоначального вклада (A) и последующих постоянных ежемесячных платежей (P) в течение определенного срока (N мес.) при заданной годовой процентной ставке (i %). Цель: рассчитать, сколько денег будет на счету в конце указанного срока при переменных параметрах. Исходные данные и переменные параметры для сценариев взять из таблицы.

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

Как с помощью Microsoft Excel можно упростить расчет численности офисных работников

К нормированию численности офисного персонала можно подойти прямым или косвенным способом (см. схему). Первый метод мы подробно исследовали в прошлом номере журнала*, а сегодня рассмотрим второй – косвенный.

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

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

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

Рассмотрим возможный вариант применения косвенного варианта планирования (нормирования) численности на примере.

Пример

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

• количество обрабатываемых документов;

• количество сотрудников, принятых и уволенных в течение года;

• общая численность персонала компании.

В ходе сбора статистической информации были получены следующие данные (см. таблицу):

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

Для решения задачи нам нужно составить уравнение регрессии, для этого воспользуемся надстройкой Excel «Регрессия», в которую можно перейти по пути: «Данные – Анализ данных»

Справочно:

если надстройки «Анализ данных» вы у себя не обнаружили, ее можно установить самому. Начиная с версии Excel 2007 г., путь установки будет выглядеть так: Файл – Параметры – Надстройки – выбираем «Пакет анализа», нажимаем «Перейти». В открывшемся окошке ставим галочку напротив «Пакет анализа» и нажимаем ОК. Готово.

Жмем на ОК и выделяем ячейки. Ячейки факторов выделяются отдельно от ячеек результатов (ими будут значения численности службы персонала).

Итог после нажатия на ОК будет выведен на другую страницу:

В результате мы получили коэффициенты для линейной зависимости вида ax + bx + cx + d = y, нужные нам ячейки выделены желтым. Первая из них – свободный член d, но его сумма получилась очень маленькой, поэтому будет равна нулю. Остальные значения коэффициентов при переменных идут по порядку. Мы получили следующую функциональную зависимость:

0,002х + 0,021х – 0,003х + 0 = y.

Коэффициенты корреляции (тесноты связи) – это верхние две строки (множественный R и R-квадрат). Они оба равны 1. Чем ближе коэффициенты корреляции к единице, тем теснее связь между результатом и влияющими на него факторами.

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

Теперь рассчитаем норму численности на 2017 г., при учете того, что планируется обработать 2 300 документов, принять и уволить 260 человек, а численность организации вырастет до 1 500 человек. Подставляя указанные числа в наше уравнение, получим:

0,002 × 2 300 + 0,021 × 260 – 0,003 × 1 500 = 6,1 человека.

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

Практические рекомендации

Как определяется производительность труда руководителей?

Производительность офисных работников часто измеряется количеством документов в день или час. Однако это может быть справедливо для специалистов и технических исполнителей, но не для руководителей. Здесь возможно использование косвенных критериев оценки, поскольку в их работе на первое место выходит результат, объект воздействия, а не количество усилий. То есть результат должен выражаться не в сокращении времени на операцию, а в общем улучшении качества управления. В качестве примера метода оценки труда руководителей можно назвать систему показателей KPI.

Используя приведенный выше метод, а также подходы, описанные в «НиТТ», 2016, № 12, с. 40, в статье «Нормирование труда офисного и управленческого персонала», специалисты должны избавляться от предвзятого отношения к проблеме нормирования в офисе и возможно преодолеть табу на этот процесс, долгое время существовавший в компании. Управленческую работу можно нормировать и получать полезный и нужный для компании результат.

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

alt=»Поиск по форуму» width=»16″ height=»16″ />

Информация о сайте

Инструменты и настройки

Excel Windows
и
Excel Macintosh

Вопросы и решения

Работа и общение

Работа форума и сайта

Функции листа Excel

= Мир MS Excel/Рассчитать количество работающих на 01.01.2017 — Мир MS Excel

Подсчет количества значений в столбце в Excel

Как подсчитать сумму значений в ячейках таблицы Excel, наверняка, знает каждый пользователь, который работает в этой программе. В этом поможет функция СУММ, которая вынесена в последних версиях программы на видное место, так как, пожалуй, используется значительно чаще остальных. Но порой перед пользователем может встать несколько иная задача – узнать количество значений с заданными параметрами в определенном столбце. Не их сумму, а простой ответ на вопрос – сколько раз встречается N-ое значение в выбранном диапазоне? В Эксель можно решить эту задачу сразу несколькими методами.

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

Метод 1: отображение количества значений в строке состояния

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

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

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

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

Порой бывает, что по умолчанию показатель “Количество” не включен в строку состояния, однако это легко поправимо:

1. Щелкаем правой клавишей мыши по строке состояния.
2. В открывшемся перечне обращаем вниманием на строку “Количество”. Если рядом с ней нет галочки, значит она не включена в строку состояния. Щелкаем по строке, чтобы добавить ее.
3. Все готово, с этого момента данный показатель добавится на строку состояния программы.

Метод 2: применение функции СЧЕТЗ

Данная функция позволяет сделать то же самое, но с одной существенной разницей – полученный результат будет зафиксирован. Остальные минусы предыдущего метода сохраняются, т.е. СЧЕТ3 не работает с условиями.

Функция СЧЕТ3 выполняет задачу по подсчету всех заполненных ячеек в заданном диапазоне (пустые не учитываются). Формула функции может выглядет по-разному:

• =СЧЕТЗ(ячейка1;ячейка2;…ячейкаN)
• =СЧЕТЗ(ячейка1:ячейкаN)

В первом случае функция выполнит подсчет всех перечисленных ячеек. Во втором – определит количество непустых ячеек в диапазоне от ячейки 1 до ячейки N. Обратите внимание, что количество аргументов функции ограничено на отметке 255.

Давайте попробуем применить функцию СЧЕТ3 на примере:

1. Выбираем ячейку, где по итогу будет выведен результат подсчета.
2. Переходим во вкладку “Формулы” и нажимаем кнопку “Вставить функцию”.Также можно кликнуть по значку «Вставить функцию» рядом со строкой формул.
3. В открывшемся меню (Мастер функций) выбираем категорию «Статистические», далее ищем в перечне нужную функцию СЧЕТ3, выбираем ее и нажимаем OK, чтобы приступить к ее настройке.
4. В окне «Аргументы функции» задаем нужные ячейки (перечисляя их или задав диапазон) и щелкаем по кнопке OK. Задать диапазон можно как с заголовком, так и без него.
5. Результат подсчет будет отображен в выбранной нами ячейке, что изначально и требовалось. Учтены все ячейки с любыми данными (за исключением пустых).

Метод 3: использование функции СЧЕТ

Функция СЧЕТ подойдет, если вы работаете исключительно с числами. Ячейки, заполненные текстовыми значениями, этой функцией учитываться не будут. В остальном СЧЕТ почти идентичен СЧЕТЗ из ранее рассмотренного метода.

Так выглядит формула функции СЧЕТ:

• =СЧЕТ(ячейка1;ячейка2;…ячейкаN)
• =СЧЕТ(ячейка1:ячейкаN)

Алгоритм действий также похож на тот, что мы рассмотрели выше:

1. Выбираем ячейку, где будет сохранен и отображен результат подсчета значений.
2. Заходим в Мастер функций любым удобным способом, выбираем в категории “Статистические” необходимую строку СЧЕТ и щелкаем OK.
3. В «Аргументах функции» задаем диапазон ячеек или перечисляем их. Далее жмем OK.
4. В выбранной ячейке будет выведен результат. Функция СЧЕТ проигнорирует все ячейки с пустым содержанием или с текстовыми значениями. Таким образом, будет произведен подсчет исключительно тех ячеек, которые содержат числовые данные.

Метод 4: оператор СЧЕТЕСЛИ

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

Синтаксис СЧЕТЕСЛИ типичен для всех операторов, работающих с условиями:

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

Диапазон – область ячеек, внутри которой необходимо найти количество совпадений по заданному условию.

Критерий – конкретное условие, совпадение по которому ищет функция. Условие указывается в кавычках, может быть задано как в виде точного совпадения с введенным числом или текстом, или же как математическое сравнение, заданное знаками «не равно» («<>»), «больше» («>») и «меньше» («<»). Также предусмотрена возможность добавить условия «больше или равно» / «меньше или равно» («=>/=<»).

Разберем наглядно применение функции СЧЕТЕСЛИ:

1. Давайте, к примеру, определим, сколько раз в столбце с видами спорта встречается слово «бег». Переходим в ячейку, куда нужно вывести итоговый результат.
2. Одним из двух описанных выше способов входим в Мастер функций. В списке статистических функций выбираем СЧЕТЕСЛИ и кликаем ОК.
3. Окно аргументов несколько отличается от тех, что мы видели при работе с СЧЕТЗ и СЧЕТ. Заполняем аргументы и кликаем OK.
   • В поле «Диапазон» указываем область таблицы, которая будет участвовать в подсчете.
   • В поле «Критерий» указываем условие. Нам нужно определить частоту встречаемости ячеек, содержащих значение “бег”, следовательно пишем это слово в кавычках. Кликаем ОК.
   • 
4. Функция СЧЕТЕСЛИ посчитает и отобразит в выбранной ячейке количество совпадений с заданным словом. В нашем случае их 16.

Для лучшего понимания работы с функцией СЧЕТЕСЛИ попробуем изменить условие:

1. Давайте теперь определим сколько раз в этом же столбце встречаются любые другие значения, кроме слова «бег».
2. Выбираем ячейку, заходим в Мастер функций, находим оператор СЧЕТЕСЛИ, жмем ОК.
3. В поле «Диапазон» вводим координаты того же столбца, что и в примере выше. В поле «Критерий» добавляем знак не равно («<>») перед словом «бег».
4. После нажатия кнопки OK мы получаем число, которое сообщает нам, сколько в выбранном диапазоне (столбце) ячеек, не содержащих слово «бег». На этот раз количество равно 17.

Напоследок, можно разобрать работу с числовыми условиями, содержащими знаки «больше» («>») или «меньше» («<»). Давайте, например, выясним сколько раз в столбце “Продано” встречается значение больше 350.

1. Выполняем уже привычные шаги по вставке функции СЧЕТЕСЛИ в нужную результирующую ячейку.
2. В поле диапазон указываем нужный интервал ячеек столбца. Задаем условие “>350” в поле “Критерий” и жмем OK.
3. В заранее выбранной ячейке получим итог – 10 ячеек содержат значения больше числа 350.

Метод 5: использование оператора СЧЕТЕСЛИМН

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

Например, нам нужно посчитать количество товаров, которые проданы более 300 шт, а также, товары, чья стоимость более 6000 руб.

Разберем, как это сделать при помощи функцией ЧТОЕСЛИМН:

1. В Мастере функций уже хорошо знакомым способом находим оператор СЧЕТЕСЛИМН, который находится все в той же категории “Статические” и вставляем в ячейку для вывода результата, нажав кнопку OK.
2. Кажется, что окно настроек функции не отличается от СЧЕТЕСЛИ, но как только мы введем данные первого условия, появятся поля для ввода второго.
   • В поле «Диапазон 1» вводим координаты столбца, содержащего данные по продажам в шт. В поле «Условие 1» согласно нашей задаче пишем “>300”.
   • В «Диапазоне 2» указываем координатами столбца, который содержит данные по ценам. В качестве «Условия 2», соответственно, указываем “>6000”.
3. Нажимаем OK и получаем в итоговой ячейке число, сообщающее нам, сколько раз в выбранных диапазонах встретились ячейки с заданными нами параметрами. В нашем примере число равно 14.

Метод 6: функция СЧИТАТЬПУСТОТЫ

В некоторых случаях перед нами может стоять задача – посчитать в массиве данных только пустые ячейки. Тогда крайне полезной окажется функция СЧИТАТЬПУСТОТЫ, которая проигнорирует все ячейки, за исключением пустых.

По синтаксису функция крайне проста:

=СЧИТАТЬПУСТОТЫ(диапазон)

Порядок действий практически ничем не отличается от вышеперечисленных:

1. Выбираем ячейку, куда хотим вывести итоговый результат по подсчету количества пустых ячеек.
2. Заходим в Мастер функций, среди статистических операторов выбираем “СЧИТАТЬПУСТОТЫ” и нажимаем ОК.
3. В окне «Аргументы функции» указываем нужный диапазон ячеек и кликаем по кнопку OK.
4. В заранее выбранной нами ячейке отобразится результат. Будут учтены исключительно пустые ячейки и проигнорированы все остальные.

Заключение

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