Определение количества сотрудников в каждом отделе и занимающих определенные должности
Предположим, нам необходимо определить, сколько сотрудников числится в отделах контроля, реализации и снабжения.
Поскольку отделов немного (три), для наглядности разместим таблицу с расчетом в диапазоне ячеек G1:H4 того же рабочего листа, на котором располагается спи-сок (рис. 5.2).
Рис. 5.2. Таблица для определения количества сотрудников в отделах и количества сотрудников, занимающих определенные должности
В ячейки G1 и H1 введите названия столбцов (Отдел и Численность), а в ячейки G2:G4 – названия отделов (Контроля, Реализации, Снабжения).
В ячейки Н2:Н4 мы должны поместить формулы, с помощью которых будут производиться вычисления. Чтобы определить общее количество сотрудников в отделе, необходимо подсчитать, сколько ячеек с названием данного отдела имеется в диапазоне А2:А11 (то есть сколько раз упоминается в списке название отдела, столько в нем и сотрудников).
Наиболее подходящей для этой цели является функция СЧЕТЕСЛИ, которая подсчитывает количество в указанном диапазоне непустых ячеек, удовлетворяющих заданному критерию. Она относится к категории Статистические и имеет следующий синтаксис:
СЧЕТЕСЛИ(диапазон; условие)Здесь диапазон – это интервал, в котором подсчитывается количество ячеек. В данном случае таковым является А2:А11. Аргумент условие должен представлять собой число, выражение или текст и определять, какие именно ячейки надо подсчитывать. В нашем примере условие соответствует названию отдела – Контроля. Панель функции СЧЕТЕСЛИ представлена на рис. 5.3.
Рис. 5.3. Панель функции СЧЕТЕСЛИ
Название отдела можно внести непосредственно во второй аргумент функции СЧЕТЕСЛИ, как показано на рис. 5.3. Но значительно проще вместо текста поместить туда ссылку на ячейку G2, в которой таковой находится. Для заполнения ячеек Н2:Н4 формулами, выполните следующие действия:
- Выделите диапазон ячеек Н2:Н4.
- Вызовите посредством мастера функций панель функции СЧЕТЕСЛИ.
- Поместите курсор в поле Диапазон и мышью выделите на рабочем листе ячейки А2:А11.
- Нажмите функциональную клавишу F4 для создания абсолютной ссылки на диапазон ячеек.
- Перейдите в поле Условие и щелкните мышью на ячейке G2.
- Нажмите комбинацию клавиш CTRL + Enter.
В результате этих действий в ячейке Н2 будет создана формула:
= СЧЕТЕСЛИ($A$2:$A$11; G2)Подобные формулы появятся также в ячейках Н3 и Н4, но во втором их аргументе будут указаны соответственно адреса ячеек G3 и G4.
Определение количества сотрудников, занимающих определенные должности
Аналогичным образом можно для каждой должности определить, сколько сотрудников предприятия ее занимают. Разместим эти расчеты на том же листе, но в диапазоне ячеек G7:H13.
В ячейки G7 и G8 введите названия столбцов (Должность и Численность), а в ячейки G8:G13 – названия должностей (Аудитор, Инженер, Менеджер, Начальник, Секретарь и Экспедитор).
Формулы в ячейках Н8:Н13 также будут построены на базе функции СЧЕТЕСЛИ. В частности, формула в ячейке Н8 должна быть такой:
= СЧЕТЕСЛИ($B$2:$B$11; G8)Используя приведенные выше инструкции, создайте аналогичные формулы в ячейках Н8:Н13.