Лабораторная работа №2 «Статистическая обработка данных»

Одной из основных целей разработки электронных таблиц была автоматизация статистической обработки информации. Рассмотрим применение встроенных средств MS Excel для обработки показателей работы добычных участков ПО «Воркутауголь» в 1990 г. Исходные данные и результаты расчетов приведены на рис.1.

Рис.1. Данные о работе ПО «Воркутауголь» в 1990 г.

Выполнение задачи разобьем на следующие этапы:

Ввод данных и оформление листа

1 шаг. Выделим ячейки А1:F1 и нажмем кнопку Объединить и поместить в центре. Введем в объединенную ячейку А1 название таблицы. Аналогично поступим с диапазонами А3:В3, но затем активизируем ячейку А1 и нажмем кнопку По левому краю. Объединив ячейки E3:F3 нажмем кнопку По правому краю.

2 шаг. Заполним строку 4.

3 шаг. Для заполнения ячеек А5:А17 воспользуемся режимом Автозаполнение, который предоставляет возможности создания последовательности увеличивающихся или постоянных значений в ячейках рабочего листа с помощью протаскивания мышью маркера заполнения (квадратик в правом нижнем углу рамки выделения). Итак, заполнив и выделив ячейки А5:А6, зацепив левой кнопкой мыши маркер автозаполнения протаскиваем его до ячейки А17.

4шаг. При обработке информации периодически приходится иметь дело с одной и той же повторяющейся информацией. В нашем случае такой информацией является список шахт. Для автоматизации своих действий в будущем рекомендуется оформлять такую информацию в виде списковMS Excel. Активизируем пункт меню Сервис-Параметры и введем в поле «Элементы списка» названия шахт разделяя их клавишей Enter.

Рис.2. Окно «Параметры»

Введя в ячейку В5 название шахты Северная можно теперь воспользоваться режимом автозаполнения для построения всего списка шахт протаскиванием мышью маркера автозаполнения до ячейки В17.

5 шаг. Обратим внимание на формат вывода данных на рис.1. Для придания таблице такого вида выделим ячейки А4:Е4 и воспользуемся пунктом меню Формат – Ячейки – Выравнивание. Выбрав в поле со списком «по вертикали» значение по центру мы тем самым разместим названия столбцов таблицы согласно образцу. Далее выполним для этих же ячеек Формат – Столбец – Автоподбор ширины.

6 шаг. Введя информацию в ячейки С5:Е17 зададим их вывод на экран с одним десятичным знаком после запятой (Формат – Ячейки – Число – Числовой -Число десятичных знаков: 1).

7шаг. Форматирование содержимого ячейкиF4 достигается установкой параметров в соответствии с рис.4.

Рис.3. Задание числового формата Рис.4. Вертикальный текст

Использование статистических функций

1 шаг. Подсчитаем среднее значение выполнения норм выработки по всем шахтам. Для этого воспользуемся функцией СРЗНАЧ() категории «Статистические». В ячейку С18 с помощью Вставка функций введем формулу: =СРЗНАЧ(С5:С17).

2 шаг. В ячейку D18 введем формулу =СРЗНАЧ(D5:D17).

Рис.5.Панель инструментов Стандартная и названия кнопок

3 шаг. В ячейке Е18 подсчитаем численность рабочих по всем шахтам, не выполнивших норму выработки. Для этого в ячейку введем формулу =СУММ(Е5:Е17) нажав кнопку Автосумма на панели инструментов Стандартная и задав в формуле диапазон Е5:Е17. Этот же результат может быть получен и использованием Вставка функции.

4 шаг. Найдем наименьшее и наибольшее значение выполнения норм выработки по шахтам, а также дисперсию этого показателя. Для этого воспользуемся функциями МИН(), МАКС() и ДИСП(0 категории «Статистические».

В ячейку С20 с помощью Вставка функции введем формулу: =МИН(С5:С17), в ячейку С21 формулу =МАКС(С5:С17), а в ячейку С22 формулу =ДИСП(С5:С17).

5 шаг. А сейчас требуется узнать ранг каждой шахты. Ранг числа – это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией или местом в этом списке). Для этого используем функцию ранжирования.

Выделим ячейку F5 и с помощью Вставки функции введем в нее формулу:

=РАНГ(С5;С5:С17;0)

Рис.6. Вид экрана Мастера функций для функции Ранг()

Для ячейки С5 ранг подсчитан в ячейке F5. Для того чтобы скопировать в функцию ранжирования в ячейки F6:F17 хорошо бы воспользоваться Автозаполнением. Но, в формуле, которая содержится в ячейке F5, используются относительные ссылки. Которые при копировании настраиваются в соответствии с областью копирования. А в нашем случае это приведет к ошибочным результатам. Длят того, чтобы ссылки не менялись при копировании, воспользуемся абсолютными ссылками.

Вернемся в ячейку Е5.

В строке формул выделим мышью второй аргумент функции, а именно С5:С17.

Нажмем клавишу F4. Аргумент поменяется на $С$5:$С$17.

Теперь произведем Автозаполнение. Установим курсор мыши на ячейку F5 и щелкнем левой кнопкой мыши. Ячейка F5 окажется в темной рамке, а в поле имени появится надпись F5. Обратим внимание, что курсор мыши отображается в виде белого креста.

В правом нижнем углу выделенного интервала находится маленький черный квадрат – это маркер заполнения. Если курсор мыши установить на маркер заполнения, то курсор мыши изменится и будет отображаться так: +. Установим курсор мыши на маркер заполнения. Нажав левую кнопку мыши и, удерживая ее нажатой, переместим курсор на ячейку F17. После отпускания кнопки мыши в диапазоне F5:F17 вычислен ранг ячеек С5:С17.

6 шаг. Подсчитаем количество шахт, на которых были случаи невыполнения норм выработки. Конечно это можно сделать методом визуального счета: раз, два, ...

Но мы воспользуемся функцией СЧЕТ(), поместив в ячейку Е23 формулу СЧЕТ(Е5:Е17).

Рис. 7. Вид экрана Мастера функций для функции Счет()

7 шаг. Расположим шахты в списке в порядке убывания показателя выполнения нормы выработки (возрастания вычисленного нами ранга для каждой шахты).

Активизируем ячейку F5. Активизируем пункт меню Данные – Сортировка и установим параметры согласно рис.8. После нажатия кнопки OK таблица примет вид, который показан на рис.9.

Рис.8. Рис.9.