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

Задание выполняется на основе листа Статистика из файла-заготовки.

В задании требуется произвести статистические расчеты для сотрудников некоторого предприятия.

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

Требуется определить:

· средний возраст всех сотрудников, сотрудников с разными должностями;

· средний стаж работы для всех сотрудников;

· средний заработок мужчин, женщин, менеджеров, администрации, низко-квалифицированных сотрудников;

· количество молодежи, новичков, ветеранов, юбиляров, низко и высокооплачиваемых сотрудников, пенсионеров;

· минимальный оклад и максимальный возраст среди всех сотрудников.

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

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

Технология выполнения задания

1. Откройте файл-заготовку Excel.

2. Откройте лист Статистика. Цветным фоном выделены ячейки, которые следует заполнить результатами расчетов.

3. Рассчитайте возраст сотрудников (G6:G25).

Исходными данными для расчета возраста являются даты рождения. Разница между текущей датой и датой рождения определяет возраст человека в днях. При делении разницы на среднее число дней в году (365,25) получается действительное число. Так как возраст – это число исполнившихся лет, то следует взять целую часть полученного частного. На языке Excel описанная выше формула будет выглядеть так:

В ячейку G6: =ЦЕЛОЕ((СЕГОДНЯ()-B6)/365,25)

Для ввода формулы с вложенными функциями:

· выделите ячейку G6;

· в строке ввода наберите знак «=»;

· на вкладке Формулы откройте категорию Математические и выберите функцию ЦЕЛОЕ();

· щелкните в строке аргумента Число;

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

Рис. 46. Выбор вложенной функции

· выберите категорию Дата и время и в ней функцию СЕГОДНЯ(), ОК;

· непосредственно в строке ввода наберите недостающие составляющие в формулу;

· нажмите Enter для выполнения расчета по формуле – в ячейке G6 появится возраст 1-ого сотрудника;

· при необходимости установите в ячейке в числовой формат.

4. Скопируйте формулу в нижестоящие ячейки G7:G25.

5. Рассчитайте стаж работы сотрудников на данном предприятии в столбце H по аналогичной формуле. Исходными данными для расчета стажа являются даты приема на работу.

6. Дайте имя диапазону G6:G25, так как он и дальше будет использован для расчетов.

Для именования диапазона ячеек:

· выделите ячейки столбца без заголовка (в нашем случае G6:G25);

· щелкните Присвоить имя (группа Определенные имена на вкладке Формулы) и выберите одноименную команду - появится окно Создание имени (Рис. 47), в поле Имя введите название столбца (в нашем случае – Возраст); ОК

Рис. 47. Окно Создание имен

Обратите внимание! Введенные имена действуют для всей книги Excel. Поэтому при создании новых имен не используйте уже задействованные ранее имена. Имена диапазонов обеспечивают абсолютную ссылку на данный диапазон, т.е. при копировании формул в другие места листа, а также на другие листы книги диапазон, указанный именем, не меняется. Исключение составляют копии полных листов, но об этом будет сказано ниже.

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

8. Определите количество сотрудников на предприятии. Для этого в ячейку B27 введите формулу:

=СЧЕТЗ (Должность)

Для ввода формулы подсчета сотрудников:

· выделите ячейку B27;

· в строке ввода наберите знак «=»;

· на вкладке Формулы откройте категорию Статистические и выберите функцию СЧЕТЗ (), вычисляющую количество значений (непустых ячеек) в указанном диапазоне;

· щелкните в строке аргумента Число 1;

· в группе Определенные имена на вкладке Формулы щелкните Использовать в формуле и выберите в нем имя диапазона Должность. ОК.

9. Рассчитайте количество молодежи (возраст <30 лет):

В ячейку В28: =СЧЁТЕСЛИ (Возраст;"<30")

Для ввода формулы подсчета количества молодежи:

· выделите ячейку B28;

· в строке ввода наберите знак «=»;

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

· щелкните в строке аргумента Диапазон и укажите в качестве диапазона ранее поименованный диапазон Возраст;

· щелкните в строке аргумента Критерий и наберите «<30» (без кавычек, их установит Мастер функций). ОК.

10. Самостоятельно рассчитайте по формулам, аналогичным приведенной в п.9 формуле, с использованием имен диапазонов:

в ячейке B29 - количество ветеранов предприятия (стаж >=20 лет);

в ячейке B30 - количество «новичков» (стаж <3 лет);

в ячейке B31 – количество низкооплачиваемых сотрудников (оклад <10000р);

в ячейке B32 – количество высокооплачиваемых сотрудников (оклад >=40000р);

в ячейке B33 – количество мужчин (в качестве критерия вводится буква м);

в ячейке B34 - % мужчин, как отношение количества мужчин (B33) к общему количеству сотрудников (B27) и установите для ячейки B34 формат Процентный (вкладка Главная, группа Ячейки, выбрать Формат ячеек…, на вкладке Число открывшегося окна выбрать формат Процентный);

в ячейке B35 – количество сотрудников в директорате.

Т.к. в названиях должностей директората есть разночтения (Директор, Зам. директора), то аргумент «критерий» должен содержать неполное буквосочетание (маску) для поиска (Рис. 48), например, «*ирек*» или «*дирек*», где «*» означает любую последовательность символов.

Рис. 48. Критерий счета в виде маски

в ячейке B36 – количество менеджеров, учитывая, что есть просто «менеджеры», а есть «Ст. менеджеры».

11. Рассчитайте Средний возраст сотрудников, для этого в ячейку Е27 введите формулу: =СРЗНАЧ(Возраст).

12. Самостоятельно в ячейке Е28 рассчитайте Средний стаж сотрудников.

13. Подсчитайте средний заработок женщин (см. Рис. 49):

В ячейку E29 введите формулу: =СРЗНАЧЕСЛИ(Пол;"ж";Оклад).

Здесь используется функция СРЗНАЧЕСЛИ(), вычисляющая среднее значение для ячеек, заданных условиями: анализируется диапазон Пол (C6:C25) по признаку «ж» (женщина), а среднее для соответствующих ячеек подсчитывается по диапазону Оклад (F6:F25):

Рис. 49. Аргументы функции СРЗНАЧЕСЛИ()

14. По аналогичным формулам подсчитайте:

в ячейке E30 – средний заработок мужчин;

в ячейке E31 – средний возраст директората (условие в виде маски «*директ*»);

в ячейке E32 – средний возраст юристов (условие в виде маски «*юрист*»).

15. В ячейке E33 рассчитайте средний заработок директората:

=СУММЕСЛИ(Должность;"*директ";Оклад)/B35.

Обратите внимание! В формуле использована маска для поиска сотрудников директората.

16. Самостоятельно в ячейку Е34 введите формулу расчета среднего заработка менеджеров. Общее количество менеджеров находится в ячейке B36.

17. Самостоятельно введите формулы с использованием имен диапазонов и посчитайте минимальный оклад (J27) и максимальный возраст (J28) сотрудников.

Обратите внимание! Для эффективного решения всех последующих пунктов задания требуются дополнительные построения, поэтому в таблицу введены 4-е дополнительных столбца.

18. Определите количество юбиляров (B37). Для этого:

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

здесь функция ОСТАТ () подсчитывает остаток от деления возраста на 10.

· Посчитайте количество «десяток» в диапазоне I6:I25 (10 лет до ближайшего юбилея означает, что текущий год – юбилейный). Для этого

в ячейку B37 введите формулу: =СЧЁТЕСЛИ(I6:I25; “10”).

19. Определите количество мужчин-пенсионеров (B38). Для этого:

· В дополнительном столбце J введите формулу, по которой в соответствующих строчках должны появится 1, если условие И(пол мужской; возраст>=60) истинно и прочерк, если ложно. Для ячейки J6 формула будет следующей: =ЕСЛИ(И(C6="м";G6>=60);1;"-"),

· Подсчитайте кол-во 1 в диапазоне J6:J25, введя в ячейку B38 соответствующую формулу (СУММ(), СЧЕТ()). Полученное значение и определит количество мужчин-пенсионеров.

20. Самостоятельно определите количество женщин-пенсионеров, заполнив столбец «Пенсионеры_жен» и ячейку B39 аналогично п.19.

21. Определите количество низко-квалифицированных сотрудников. Для этого:

· В дополнительном столбце L введите формулу, по которой в соответствующих строчках должны появиться 1, если должность у сотрудника или Курьер, или Вахтер, или Уборщица и прочерк, если это не так. Для
ячейки L6 формула будет следующей:

=ЕСЛИ(ИЛИ(D6="Курьер"; D6="Вахтер"; D6="Уборщица");1;"-").

· Подсчитайте кол-во 1 в диапазоне L6:L25, введя в ячейку B40 соответствующую формулу (СУММ(), СЧЕТ()).

22. Подсчитайте средний заработок низко-квалифицированных сотрудников. Для этого в ячейку E35 введите формулу с функцией СРЗНАЧЕСЛИ () (Рис. 50):

· В качестве Диапазона выберите L6:L25 с признаками низкой квалификации («1» в ячейках).

· В качестве Условия – 1.

· Диапазоном_усреднения должен быть Оклад сотрудников.

Рис. 50. Аргументы для подсчета среднего заработка
низко-квалифицированных сотрудников

23. Сохраните выполненное задание.