 |
АвтоАвтоматизацияАрхитектураАстрономияАудитБиологияБухгалтерияВоенное делоГенетикаГеографияГеологияГосударствоДомДругоеЖурналистика и СМИИзобретательствоИностранные языкиИнформатикаИскусствоИсторияКомпьютерыКулинарияКультураЛексикологияЛитератураЛогикаМаркетингМатематикаМашиностроениеМедицинаМенеджментМеталлы и СваркаМеханикаМузыкаНаселениеОбразованиеОхрана безопасности жизниОхрана ТрудаПедагогикаПолитикаПравоПриборостроениеПрограммированиеПроизводствоПромышленностьПсихологияРадиоРегилияСвязьСоциологияСпортСтандартизацияСтроительствоТехнологииТорговляТуризмФизикаФизиологияФилософияФинансыХимияХозяйствоЦеннообразованиеЧерчениеЭкологияЭконометрикаЭкономикаЭлектроникаЮриспунденкция
Задание 3. Статистика
Произвести статистические расчеты по данным о сотрудниках некоторого предприятия.
При выполнении задания будут использоваться следующие функции:
| Функция | Описание функции | Примеры записи |
| СЧЁТЗ(значение1;значение2;…) | Подсчитывает количество значений в списке аргументов независимо от их типа | СЧЁТЗ(A1:A5) СЧЁТЗ(Прибыль) СЧЁТЗ(A1;B1;C1;12) |
| СРЗНАЧ(число1;число2;…) | Находит среднее значение из списка аргументов | СРЗНАЧ(A1:A5) СРЗНАЧ(Прибыль) СРЗНАЧ(A1;B2;C$3) |
| МАКС(число1;число2;…) | Находит максимальное значение из списка аргументов | МАКС(A1:A5) МАКС(Прибыль) МАКС(A1;B2;C$3) |
| МИН(число1;число2;…) | Находит минимальное значение из списка аргументов | МИН(A1:A5) МИН(A1;B2;C$3) МИН(A1;Прибыль;15) |
| СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения) | Возвращает среднее значение всех ячеек в диапазоне, которые соответствуют данному условию | СРЗНАЧЕСЛИ(В1:В12;»>0») СРЗНАЧЕСЛИ(ПОЛ;»ж»;ОКЛАД) |
| СЧЁТЕСЛИ(диапазон;критерий) | Подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих критерию | СЧЁТЕСЛИ(C1:C5;»>0») СЧЁТЕСЛИ(Рост;»<170») |
| СУММЕСЛИ (диапазон; условия; диапазон_суммирования) | Суммирует ячейки заданногодиапазона, соответствующиеусловиям. Пустые ячейки и ячейки, содержащие текстовые значения,не учитываются. Третий аргумент может отсутствовать | СУММЕСЛИ(Числа;»>0») СУММЕСЛИ(Должность; «*адм*»;Оклад) |
| ЕСЛИ (логическое_выражение; формула_1; формула_2) | если условие истинно, выполняется формула_1, если ложно – формула_2 | ЕСЛИ(Доход>Расход;Доход-Расход;»нет прибыли») |
| И(логическое выражение1;логическое выражение2;…) | принимает значение ИС- ТИНА, если все логические выражения-аргументы имеют значение ИСТИНА | ЕСЛИ (И (Информатика>2;Математика>2;Философия>2); «сдал сессию»; «не сдал сессию») |
| ИЛИ(логическое выражение1;логическое выражение2;…) | принимает значение ИСТИНА, если хотя бы один из ее аргументов равен ИСТИНА | ЕСЛИ (ИЛИ (Информатика=5;Математика=5; философия=5); «есть отличные оценки»; «нет отличных оценок») |
Исходными данными являются инициалы сотрудников, их пол, дата рождения, дата приема на работу, должность и оклад.
Требуется определить: средний возраст всех сотрудников и сотрудников с разными должностями; средний стаж работы всех сотрудников; средний заработок мужчин, женщин, менеджеров, администрации, низкоквалифицированных работников; количество молодежи, новичков, ветеранов, юбиляров, низко- и высокооплачиваемых сотрудников, пенсионеров; минимальный оклад и максимальный возраст среди всех сотрудников.
Откройте файл Заготовки 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").
Самостоятельно введите формулы в оставшиеся цветные ячейки, пользуясь всплывающими подсказами.
Поиск по сайту: