Задание 3. Статистика

Произвести статистические расчеты по данным о сотрудниках некоторого предприятия.

При выполнении задания будут использоваться следующие функции:

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

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

Откройте файл Заготовки Excel, лист Статистика.

В столбце G рассчитайте возраст сотрудников, который равен целому числу отработанных лет. Для ячейки G6 формула будет выглядеть так:

=ЦЕЛОЕ((СЕГОДНЯ()-В6)/365,25),

где СЕГОДНЯ()-В6 — количество прожитых дней; 365,25 — среднее количество дней в году.

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

Назовите диапазон G6:G25 словом «Возраст». Для этого выделите ячейки столбца без заголовка (в нашем случае — G6:G25) и выполните команду Формулы ► Определенные имена > Присвоить имя ► Присвоить имя. В появившемся окне Создание имени в поле Имя введите название столбца (в нашем случае — Возраст). Нажмите ОК.

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

В ячейке В26 определите количество сотрудников на предприятии. Для этого щелкнув в ячейке, перейдите на вкладку Формулы„Вставить функцию. Выберите категорию Полный алфавитный перечень. Найдите в списке функцию СЧЁТ3. Нажмите ОК. Появится мастер формул. Для выбора диапазона, выделите его на листе (ячейки D6:D25), нажмите ОК. В строке формул должно быть следующее: =СЧЁТЗ(Должность).

В ячейке В29 рассчитайте количество молодежи (возраст до 30 лет) по формуле =СЧЁТЕСЛИ(Возраст;«<30»). Для этого вызовите мастер формул как в предыдущем случае.

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

Заполните столбец J (пенсионеры мужчины). Действуйте аналогично образцу в задании 2 при вычислении начисленного штрафа (функция ЕСЛИ). При этом внутри функции ЕСЛИ используйте логическую функцию И (человек должен быть и мужчиной (критерий «м» в столбце пол, и пенсионером, возраст >60 в столбце Возраст). Результат выполнения функции должен быть 1, если мужчина пенсионер, и 0 в противном случае.

С помощью столбца J и функции СУММЕСЛИ посчитайте количество пенсионеров-мужчин и внесите формулу в ячейку В34.

Заполните столбец K для пенсионеров-женщин аналогично как это делалось для пенсионеров-мужчин. Найдите количество пенсионеров-женщин в ячейке В35.

Заполните столбец L. Работник имеет низкую квалификацию, если его должность Курьер, или Вахтер, или Уборщица. Используйте функцию ЕСЛИ и логическую функцию ИЛИ. Подсчитайте количество низкоквалифицированных сотрудников в ячейке B36. В ячейке Е36 подсчитайте средний заработок таких сотрудников.

Подсчитайте количество родившихся в мае по аналогичной технологии. Используйте функцию МЕСЯЦ(), чтобы по дате рождения определить месяц.

В дополнительном столбце I введите формулу для расчета количества лет до круглой даты. Для ячейки I6 формула будет иметь вид = 10 - ОСТАТ(H6;10). Здесь функция ОСТАТ() подсчитывает остаток от деления возраста на 10.

В ячейке B37 рассчитайте количество юбиляров по формуле =СЧЁТЕСЛИ(I6:I25;"10").

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

Не нашли, что искали? Воспользуйтесь поиском: