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

Обновлено: 05.07.2024

Бет искала способ определить, сколько сотрудников прошли не менее десяти курсов, которые предлагались в ее компании. Она настроила Excel так, чтобы столбец A (начиная с A2) содержал имена сотрудников, а столбец B (начиная с B2) — имена классов. Когда сотрудник проходил курс, его имя вносилось в столбец A, а название курса, который он посещал, помещалось в столбец B. Таким образом, столбец A будет содержать несколько экземпляров имени каждого сотрудника, а столбец B будет содержать несколько экземпляров имени каждого сотрудника. имена классов.

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

Чтобы правильно использовать формулу, этот конкретный экземпляр должен быть помещен в ячейку C2, а затем скопирован в остальные ячейки столбца C. Это приводит к тому, что ссылка A2 в формуле изменяется таким образом, что она всегда ссылается на ячейку двумя столбцами слева от нее. (Формула в C2 ссылается на A2, формула в C3 ссылается на A3 и т. д.)

Используя эту формулу, когда конкретный человек посещает десятый курс (это означает, что его имя появляется в столбце A в десятый раз), столбец C заполняется фразой "10 или более классов" рядом с каждым классом, который конкретный взял человек. Это здорово, за исключением случаев, когда куча разных сотрудников начинает переступать десятиклассный порог. Затем столбец C начинает выглядеть загроможденным.

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

В данном случае функция СЧЁТЕСЛИ не просматривает каждую ячейку в столбце А; вместо этого он просматривает ячейки в столбце A, которые находятся в строках, меньших или равных строке, в которой встречается формула. Таким образом, если бы эта формула находилась в строке 8, то функция СЧЁТЕСЛИ рассматривала бы только строки со 2 по 8 при подсчете. В результате фраза «10 или более классов» появляется в столбце C только тогда, когда конкретный сотрудник прошел отметку в десять классов. Он не будет отображаться в случаях, когда сотрудник посещал первый, второй, третий и вплоть до девятого классов.

Наконец, таблицу классов сотрудников можно сделать чуть более причудливой, и вы сможете точно видеть, сколько курсов прошел каждый сотрудник. Если столбцы A и B по-прежнему содержат имена и классы, вы можете поместить по одному экземпляру имени каждого сотрудника в первые строки столбца D, оставив столбец C пустым. В столбце E вы можете поместить следующую формулу справа от имени каждого сотрудника:

В результате столбец E содержит количество экземпляров имени в столбце D, которое появляется в столбце A. Другими словами, у вас есть подсчет того, сколько занятий прошел каждый сотрудник. Затем вы можете использовать условное форматирование для ячеек в столбце D, чтобы они отображались красным цветом, если количество в столбце E равно 10 или больше.

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

Как следует из названия, функция СЧЁТЕСЛИ в Excel представляет собой комбинацию формулы подсчета и ЕСЛИ. Говоря простым языком, функцию COUNTIF можно описать как формулу, которую можно использовать для подсчета количества ячеек, удовлетворяющих определенному условию, в заданном диапазоне.

Оглавление

Как Excel определяет функцию СЧЁТЕСЛИ

Microsoft Excel определяет СЧЁТЕСЛИ как формулу, которая «подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию».

Это определение ясно объясняет, что: Функция СЧЁТЕСЛИ — это улучшенный и сложный тип формулы СЧЁТ, который позволяет вам контролировать, какие ячейки вы хотите подсчитать.

Синтаксис формулы СЧЁТЕСЛИ в Excel

Формула Excel COUNTIF может быть записана следующим образом:

Здесь «диапазон» указывает диапазон ячеек, к которым вы хотите применить «критерий».

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

Как использовать СЧЁТЕСЛИ в Excel

Теперь давайте посмотрим, как использовать функцию СЧЁТЕСЛИ в Excel.

Давайте представим, что у нас есть таблица Employee, как показано на изображении ниже.

Цель. Из приведенной выше таблицы наша цель — найти количество сотрудников, которые присоединились к нам до 1990 года.

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

диапазон: в этом случае «диапазон» будет «B2:B11», так как к этим ячейкам мы должны применить «критерии».

критерии: в данном случае «критерии» — это «январь 1990 года».

В результате получается 6, что означает, что 6 сотрудников присоединились к нам до 1990 года.

Несколько важных фактов о формуле СЧЁТЕСЛИ

<р>1. Формула СЧЁТЕСЛИ принимает только сплошной диапазон, вы не можете дать ей несколько разорванных диапазонов. Например, СЧЁТЕСЛИ нельзя записать как

<р>2. СЧЁТЕСЛИ может принимать подстановочные знаки (например, «*» и «?») в аргументе «критерий». Это означает, что вы можете записать COUNTIF как

При этом будут подсчитаны все ячейки, содержащие символ «о», в диапазоне D1:D5.

<р>3. Как вы знаете, на выходе COUNTIF является целым числом, поэтому вы также можете добавить две функции COUNTIF. Например: если вы хотите найти ячейки со значением «1» и ячейки со значением «2», вы можете использовать СЧЁТЕСЛИ как

Несколько основных примеров функции СЧЁТЕСЛИ

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

Пример 1. В первом примере я использовал формулу СЧЁТЕСЛИ в Excel для нахождения количества сотрудников, чье имя начинается с «G».

Для этого я использовал формулу как

Здесь функция СЧЁТЕСЛИ сканирует весь диапазон от A3:A12 и пытается найти шаблон «G*» («*» — это оператор подстановки, обозначающий любое количество символов). Результат равен 2, так как в указанном диапазоне есть только два человека, чьи имена начинаются с буквы G.

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

Для этого я использовал формулу

Эта формула ищет в указанном диапазоне значение, соответствующее критериям (например, >26000). Таким образом, результат равен 5, так как только у 5 сотрудников идентификатор сотрудника превышает 26000.

Пример 3. В третьем примере я получил количество сотрудников, чья зарплата меньше 4000.

Чтобы получить это, я снова использовал формулу COUNTIF как

Итак, здесь COUNTIF подсчитывает только те ячейки, в которых диапазон зарплат, т. е. D3:D12, имеет значение меньше 4000, а результат равен 3.

Пример 4. В четвертом примере я использовал следующую формулу

Эта формула находит количество ячеек, равное значению ячейки B5 (т. е. "Массиот") в диапазоне B3:B15.

Здесь сначала функция СЧЁТЕСЛИ находит значение в ячейке B5, а затем сравнивает все ячейки в указанном диапазоне с этим значением.

Результат равен 2, так как только две записи соответствуют значению в ячейке B5.

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

Это можно легко сделать, добавив результаты двух операторов COUNTIF, например:

Первая инструкция COUNTIF дает количество ячеек со значением, равным «Яблоко», а вторая инструкция дает количество ячеек со значением «Персик». И, следовательно, на выходе получается 2+1=3.

Пример 6. В этом примере (т. е. =СЧЁТЕСЛИ(A,"Груша")) я попытался показать вам, что произойдет, если вы введете неверный диапазон в функции СЧЁТЕСЛИ.

Несколько расширенных примеров функции СЧЁТЕСЛИ

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

Пример 7. Поиск повторяющихся значений с помощью функции СЧЁТЕСЛИ.

Допустим, у нас есть таблица, как показано ниже, и нам нужно найти в ней повторяющиеся записи.

Для поиска повторяющихся записей мы использовали формулу:

Когда эта формула встречает повторяющуюся запись, она возвращает ИСТИНА, а ЛОЖЬ означает, что запись уникальна.

Если вам интересно, что делают эти знаки доллара ($) в этой формуле, прочтите этот пост.

Пример 8. Используйте формулу СЧЁТЕСЛИ для создания порядка сортировки списка.

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

Теперь, если мы просто хотим узнать алфавитный порядок сортировки (в порядке возрастания) имен сотрудников, мы можем использовать формулу:

Посмотрите на изображение ниже, чтобы увидеть эту формулу в действии.

Как видите, эта формула генерирует число перед каждым сотрудником. Это число является порядком сортировки (по возрастанию) имен сотрудников.

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

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

Как подсчитать общее количество сотрудников на управленческом пути на организационной диаграмме — Excel

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

В приведенном ниже примере данных у Боба 3 подчиненных. У Джейн 2, а у Фреда 1. Фред является организационным лидером этой группы. Мне нужен способ подсчитать, сколько сотрудников каждый менеджер подчиняется непосредственно им (3 для Боба, 2 для Джейн и 1 для Фреда), а также способ подсчитать промежуточный итог для общего количества подчиненных сотрудников под каждым менеджером. в их организации (3 для Боба, 5 для Джейн и 6 для Фреда). Возможно ли это?

Заранее благодарю за любые направления, которые вы можете мне дать.

(Распродажа 40% скоро заканчивается)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

(Скидка 40% скоро закончится!)

Подсчет человеко-часов и дней, отработанных на нескольких листах — Excel

Я пытаюсь найти решение для подсчета человеко-часов и дней, отработанных на нескольких листах.

У меня есть файл с 8 листами. первые семь листов — это дни недели, а восьмой лист — это место, где я хочу рассчитать часы.

Дни недели состоят из «Имени», «Даты», «Часов»

Восьмой лист состоит из "Имя", "Дни недели в отдельной колонке (всего 7 колонок)", "Общее количество часов" и "Общее количество отработанных дней"

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

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

Я прикрепил образец файла.

Заранее благодарим за помощь.

Отчет сводной таблицы для ежедневных отчетов о ошибках – Excel

Если кто-нибудь может дать мне решение этой проблемы, это будет для меня большим подспорьем.

Я работаю в банке, и каждый день мы получаем данные о кредитах и авансах, сделанных каждым филиалом за месяц, с общей суммой по регионам и по менеджерам. С суммой кредита и количеством кредита. В нашем формате MIS ветви A coulum отсортированы и перечислены в последовательности с промежуточным итоговым регионом. У меня есть данные, где, если сделать поворот и изменить данные, выходные данные должны отображаться соответственно в формате MIS. Может ли кто-нибудь помочь мне, как мы можем это сделать, или есть ли какие-либо другие надстройки / программное обеспечение для офиса, если так, данные меняются ежедневно, и если мы выполним сводку, он автоматически скопирует данные в формат MIS.

Как создать платежную ведомость всех сотрудников — Excel

Уважаемые все!
У меня около 250 сотрудников, и я готовлю платежную ведомость в Excell вручную. У меня есть все данные о зарплате на листе 1, а на листе 2 - платежная ведомость. Я прошу всех вас помочь мне, чтобы я мог взять распечатку всех сотрудников в листе 1 за раз. Теперь я ввожу один за другим код сотрудников, а затем беру распечатку.
Пожалуйста, предоставьте мне любые формулы, так как микроконтроллеры отключены на моем компьютере.

Ждем скорейшего ответа.

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

Или.есть ли у кого-нибудь лучшее решение для учета рабочего времени?

Как рассчитать, сколько лет проработал сотрудник? - Excel

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

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

Эта функция дает мне число с большим количеством знаков после запятой.

Это говорит мне, что у меня слишком мало аргументов. Пожалуйста, помогите!

Рассчитать количество часов в час, разделив общее время на общее количество — Excel

Как рассчитать количество пакетов в час? Вот что у меня есть на данный момент:

Ячейка B5: время начала: 4:15 в формате 4:15:00
Ячейка B6: время окончания: 6:15 в формате 6:15:00
Ячейка B7: общее время : Формула 2:00 в ячейке: =ТЕКСТ(B6-B5;"h:mm")
Ячейка B8: общее количество обработанных партий: 22 (это значение вводится вручную)
Ячейка B9: количество партий в час: формула в ячейке: =B8/ТЕКСТ(B7,"h")

пока у меня есть эта формула в ячейке B9, ответ будет правильным, что должно быть 11 в час.
Если формула в B9 — B8/B7, ответ — 264,0. Это из-за того, что Excel считывает общее время, или общее время отформатировано как время, а не фактическое число? Это правильный способ решения проблемы?
ответ должен быть 11 в час.

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

Во-первых, краткое напоминание о функции, которую мы собираемся использовать.

Синтаксис СЧЁТЕСЛИМН.

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

=COUNTIFS(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерий2]…)

диапазон_критериев1. Это необходимо. Первый диапазон, в котором оцениваются связанные критерии.
criteria1 Это также обязательно. Критерии в виде числа, выражения, ссылки на ячейку или текста, определяющие, какие ячейки будут учитываться. Критерии могут быть выражены как 32, ">32", B4, "яблоки" или "32".
диапазон_критериев2, критерии2, . Это необязательно. Дополнительные диапазоны и связанные с ними критерии. Допускается использование до 127 пар "диапазон/критерий".

Сколько сотрудников работает на смене в любое время дня?

Формула СЧЁТЕСЛИМН, используемая для этого решения, приведена ниже.

=СЧЁТЕСЛИМН($C$6:$C$20”, ="&G6)

Это очень просто.

Ячейки C6:C20 содержат первый диапазон критериев. Обратите внимание на использование абсолютной ссылки на ячейку. Первый критерий — время смены меньше или равно рабочему часу в ячейке G6.

Далее ячейки D6:D20 содержат второй диапазон критериев. Используется та же абсолютная ссылка на ячейку. На этот раз вторым критерием является то, что смена больше или равна рабочему часу в ячейке G6. В 8 утра работает 6 сотрудников, а в 10 утра их 12.

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

Наконец, если вам нужны БОЛЬШЕ советов по Excel и VBA, подпишитесь на мою ежемесячную рассылку, где я делюсь 3 советами по Excel в первую среду месяца и получаю бесплатную электронную книгу, 30 советов по Excel. Вы можете прочитать все записи блога из серии Formula Friday, нажав на ссылку ниже.

Как преуспеть в Excel — записи в блоге Formula Friday.

Если вы хотите также прочитать советы по Excel VBA, не стесняйтесь присоединяться ко мне каждый понедельник для моей серии сообщений в блоге Macro Monday. Нажмите на ссылку ниже, чтобы прочитать все статьи Macro Monday.

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