Компьютерная графика_1 / Информационные технологии / Задание №7

Практическая работа №7

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

Задание 1. В течение месяца на сайте ОМС Сбербанка проводились наблюдения за стоимостью покупки 1 грамма серебра (стоимость в $): 20; 19; 12; 13; 16; 17; 17; 14; 16; 20; 14; 19; 20; 20; 16; 13; 19; 14; 18; 17; 12; 14; 12; 17; 18; 17; 20; 17; 16; 17.

В программе Excel постройте эмпирическое распределение стоимости для данной выборки.

Ход работы:

1. в ячейку А1 введите слово Наблюдение, а в диапазон А2:С11 – стоимость металла;

2. выберите ширину интервала 1 балл. Тогда при крайних значениях количества баллов 12 и 20 получится 9 интервалов. В ячейки D1 и D2 введите названия интервалов Кол-во. В диапазон D3:D11 введите граничные значения интервалов (12, 13, 14, 15, 16, 17, 18, 19, 20).

3. введите заголовки создаваемой таблицы: в ячейки Е1:Е2 - Абсолютные частоты, в ячейки F1:F2 – Относительные частоты, в ячейки G1:G2 – Накопленные частоты;

4. заполните столбец абсолютных частот. Для этого выделите для них блок ячеек Е3:Е11 (используемая функция ЧАСТОТА задается в виде формулы массива). В появившемся диалоговом окне Мастер функций выберите категорию Статистические и функцию ЧАСТОТА. Указатель мыши в рабочее поле Массив данных введите диапазон данных наблюдений (А2:С11). В рабочее поле Двоичный массив мышью введите диапазон интервалов (D3:D11). Последовательно жмите комбинацию клавиш CTRL+Shift+Enter. В столбце Е3:Е11 появится массив абсолютных частот.

5. в ячейке Е12 найдите общее количество наблюдений. Табличный курсор установите в ячейку Е12. На панели инструментов Стандартная нажмите кнопку Автосумма. Убедитесь, что диапазон суммирования указан правильно (Е3:Е11. В ячейке Е12 появится число 30.

6. заполните столбец относительных частот. В ячейку F3 введите формулу для вычисления относительных частот: =Е3/$Е$12. Нажмите клавишу Enter. Получите массив всех относительных частот.

7. заполните столбец накопленных частот. В ячейку G3 скопируйте значение относительной частоты из ячейки F3 (0,10). В ячейку G4 введите формулу =F3+F4. Получите массив всех накопленных частот.

8. В результате получим таблицу, представленную на рис. 1.

Рис. 1. Результаты вычислений относительных и накопленных частот

9. Постройте диаграмму относительных и накопленных частот. Щелчком указателя мыши по кнопке на панели инструментов вызовите Мастер диаграмм. В появившемся диалоговом окне выберите вкладку нестандартные и тип диаграммы График / гистограмма 2. После нажатия кнопки Далее укажите диапазон данных – F1:G11 (с помощью мыши). Проверьте положение переключателя Ряды в: столбцах. Выберите вкладку Ряд и с помощью мыши введите в рабочее поле Подписи оси Х диапазон подписей оси Х: D3:D11. Нажав кнопку Далее, введите названия осей Х и У: в рабочее поле Ось Х (категорий) – Кол-во; Ось У (значений) – Относительная частота; Вторая ось У (значений) – Накопленная частота. Нажмите кнопку Готово.

После минимального редактирования диаграмма будет иметь вид, представленный на рис. 2.

Рис. 2. Диаграмма относительных и накопленных частот

Задание 2. Для данных из примера 1 постройте эмпирические распределения, воспользовавшись процедурой Гистограмма.

Ход работы:

1. в ячейку А1 введите слово Наблюдение, а в диапазон А2:С11 – значение цены;

2. для вызова процедуры Гистограмма выберите из меню Сервис подпункт Анализ данных и в открывшемся окне в поле Инструменты анализа укажите процедуру Гистограмма (если данная команда недоступна, загрузите пакет анализа: в меню Сервис выберите команду Надстройки; в списке надстроек выберите Пакет анализа и нажмите ОК);

3. в появившемся окне Гистограмма заполните рабочие поля:

   1. во Входной диапазон введите диапазон исследуемых данных (А2:С11);

   2. в Выходной диапазон – ссылку на левую верхнюю ячейку выходного диапазона (Н3). Установите переключатели в положение Интегральный процент и Вывод графика;

После этого нажмите кнопку ОК. В результате появляется таблица и диаграмма (рис. 3).

Рис. 3. Таблица и диаграмма

Как видно, диаграмма на рис. 3 несколько отличается от диаграммы на рис. 2. Это объясняется тем, что диапазон карманов не был введен. Количество и границы интервалов определялись в процедуре ГИСТОГРАММА автоматически. Если бы в рабочее поле Интервалов карманов был бы введен диапазон ячеек, определяющих выбранные интервалы, как в примере 4 (12, 13, …, 20), то полученная диаграмма была бы идентична предыдущей.

Задание 3. Установите степень положительной линейной связи между двумя переменными по данным 15 объектов (табл. 1). Используйте коэффициент корреляции Пирсона, принимая предположение, что распределения экспериментальных данных согласуются с нормальным.

Ход работы:

1) для вычисления коэффициента корреляции составляют расчетную таблицу 2;

2) в первые два столбца заносят данные выборок Х и Y;

3) в следующих двух столбцах рассчитывают квадраты этих величин, в последнем – их произведение;

4) в нижней строке каждого столбца находят сумму элементов столбца;

Таблица 1

Таблица данных пятнадцати объектов

Объект	Х	Y
1 2 3 4 5 6 7 8 9 10 11 12	100 90 126 112 80 115 105 110 99 97 87 76	28 25 19 24 23 21 27 25 26 25 23 18
13 14 15	100 80 120	29 20 18

Таблица 2

Расчетная таблица для определения коэффициента Пирсона

	X	Y	X^2	Y^2	XY
1
…
15
Сумма

5) рассчитывают коэффициент корреляции по формуле

и делают вывод из следующих положений:

r = 0,99 – 0,7, то присутствует сильная статистическая взаимосвязь между Х и Y,

r = 0,69 – 0,5, то присутствует средняя статистическая взаимосвязь между Х и Y,

r = 0,49 – 0,2, то присутствует слабая статистическая взаимосвязь между Х и Y,

r = 0,19 – 0,09, то присутствует очень слабая статистическая взаимосвязь между Х и Y, r = 0, то корреляции нет.

Задание 4. Рассмотрите работу пакета Анализа данных Microsoft Excel по определению коэффициента корреляции на примере задания 3 лабораторной работы.

Ход работы:

1. сформируйте таблицу исходных данных;

2. выполните команду Сервис  Анализ данных  Корреляция  ОК;

3. в качестве Входного интервала выберите ячейки 2-го и 3-го столбцов;

4. а в качестве Выходного интервала выберите свободную ячейку вашего листа;

5. ОК.

Задание 5. Установите степень связи (или ее отсутствие) между ценой и реализованным количеством товара фирмой за год (табл. 2).

Таблица 2

Таблица данных о товаре за год

Месяц	Цена, руб.	Количество, шт.
январь февраль март апрель май июнь июль август сентябрь октябрь ноябрь декабрь	54 54 59 60 61 63 63 62 55 57 56 58	100 110 110 142 160 210 210 200 155 111 70 60