Microsoft Office Excel 2007. Статистическая обработка данных

Цели работы:

- закрепить практические навыки использования мастера функций;

- научиться использовать статистические и логические функции.

В результате выполнения лабораторной работы студент должен

знать:

- назначение Мастера функций;

- категории Мастера функций;

- аргумент функции;

уметь:

- использовать логические функции «ЕСЛИ», «И»;

- использовать статистические функции «ЦЕЛОЕ», «МИН», «МАКС», «СЧЁТЕСЛИ», «СЧЁТ», «СРЗНАЧ».

Пояснение к работе

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

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

Продемонстрируем простейшие приёмы статистической обработки на примере следующей лабораторной работы.

Представим себе деятельность Учебного отдела ЗАО МНТ. Повышение квалификации специалистов, пришедших на обучение в это подразделение, ведётся по трём направлениям, имеющим специализации «Управление», «Экономика», «Иностранные языки» (рис. 11.1).

Управление

Экономика

Иностранные языки

Делопроизводство

Руководитель

Менеджмент

Английский

Скандинавский

Французский

Бухгалтерия

Обучение

Рис. 11.1 - Схема обучения специалистов в ЗАО МНТ

На каждого обучаемого заполняется учётная карточка, содержащая следующую информацию: ФИО, дата рождения, направление обучения, специализация, цель обучения и т.п. Эти данные хранятся в виде электронной картотеки и могут использоваться для набора статистики.

Статистическая обработка данных важна:

− при прогнозировании количества слушателей на следующий год (для создания материальной базы и формирования преподавательского состава);

− для адаптации учебных курсов с учётом возрастных особенностей слушателей;

− для учёта уровня квалификации слушателей при подготовке учебных программ.

Задание

1. Заполните таблицу согласно образцу, приведённому на рисунке (рис. 11.2).

Рис. 11.2 - Таблица с исходными данными

2. Статистические расчёты.

2.1. Вычислите возраст слушателей на момент статистической обработки (формула 1) и занесите его в столбец F, а затем скопируйте вниз.

(1) = ЦЕЛОЕ ((СЕГОДНЯ()-В2)/365)

Для вычисления возраста выполняются следующие действия:

· из текущей даты (функция СЕГОДНЯ(); категория функций: Дата и время) вычитается дата рождения (берётся из столбца Дата рождения, в данном случае из В2);

· разность делится на среднюю продолжительность года (365 дней);

· от частного отбрасывается дробная часть (функция ЦЕЛОЕ(), категория функций: Математические).

2.2. Расположите расчётные данные по образцу на рисунке (рис. 11.3).

Рис. 11.3 - Расположение статистических расчётов в таблице

2.3. Найдите минимальный (формула 2) и максимальный (формула 3) возраст обучаемых (категория функций: Статистические):

(2) = МИН(F2:F9)

(3) = МАКС(F2:F9)

2.4. Посчитайте количество слушателей, обучаемых по трём различным направления (формулы 4, 5, 6):

(4) = СЧЁТЕСЛИ(С2:С9; «=1»)

(5) = СЧЁТЕСЛИ(С2:С9; «=2»)

(6) = СЧЁТЕСЛИ(С2:С9; «=3»)

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

2.5. Посчитайте количество слушателей по трём возрастным категориям: до 25 лет; от 25 до 40 лет; после 40 лет (формулы 7, 8, 9):

(7) = СЧЁТЕСЛИ(F2:F9; «<25»)

(8) = СЧЁТЕСЛИ(F2:F9; «>39»)

(9) = СЧЁТ(F2:F9) – F14 – F15

Количество слушателей возрастной группы от 25 до 40 (формула 9) определяется вычитанием из общего количества слушателей тех, кому меньше 25 и больше 39. Для расчёта общего количества слушателей используется статистическая функция СЧЁТ(список значений), выдающая количество чисел в списке аргументов (список может быть задан перечислением или диапазоном, как в данном случае).

2.6. Посчитайте средний возраст слушателей различных курсов ЗАО МНТ (формула 10):

(10) = СРЗНАЧ(F2:F9)

Для подсчёта используйте статистическую функцию СРЗНАЧ(список чисел), вычисляющую среднее арифметическое для указанных аргументов. Аргументы могут быть заданы перечислением или диапазоном. Установите формат результата Числовой с одним десятичным знаком.

2.7. Отметим в отдельном столбце (G) электронной таблицы знаком «1» тех слушателей, которые могут оказаться в «группе риска» по трудоустройству, и прочерком (знаком «-») – остальных слушателей.

Заголовок столбца G – Группа риска.

Для этого используйте логические функции ЕСЛИ, И, ИЛИ, которые реализуют поставленное условие выбора.

В «группу риска» условно можно отнести слушателей курсов, у которых одновременно выполняется два условия:

§ возраст больше 39 лет (критический для трудоустройства);

§ цель обучения – переквалификация или начальное обучение.

Эти условия объединяются логической функцией И (логическое_условие1; логическое_условие2;…).

Цель

Переквалификация

Начальное обучение

Второе условие сложное: (цель – или переквалификация, или начальное обучение) (рис. 11.4). Оно реализуется в формуле логической функцией ИЛИ(логическое_условие1; логическое_условие2;…).

Рис. 11.4 – Цель обучения

Для того, чтобы отметить одним из двух знаков каждого обучающегося, используется функция ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь) (формула 11) в столбце G.

(11) = ЕСЛИ(И(F2>=39)+(ИЛИ(E2="переквалификация";E2="нач. обучение"));"1";"-")

Задав такую формулу, вы создаёте логический фильтр, отбирающий из всех слушателей тех, кто может иметь проблемы с трудоустройством.

Рис. 11.5 – Выходные данные

 

Работа в лаборатории

1. Выполнить предложенные задания.

2. Сформировать отчет.

Содержание отчета

1. Созданный файл.

Контрольные вопросы

1. Приведите примеры встроенных статистических функций Excel.

2. Для чего используется функция ЕСЛИ? Назовите ее конструкцию.

3. Для чего используется функция СЧЁТ? Назовите ее конструкцию.

4. Для чего используется функция СЧЁТЕСЛИ? Назовите ее конструкцию.

5. Для чего используется функция СРЗНАЧ? Назовите ее конструкцию.

6. Для чего используется функция ЦЕЛОЕ? Назовите ее конструкцию.

 

 

ЛАБОРАТОРНАЯ РАБОТА №12