Министерство образования и науки ДНР
ГОУ ВПО «Донецкая академия управления и государственной службы при Главе ДНР»
Кафедра информационных технологий
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
к выполнению индивидуальной работы
«Анализ данных в MS EXCEL»
Утверждено на заседании предметно-методической комиссии кафедры информационных технологий (протокол № ___ от «___» ______________20___ г.) |
Донецк
20_____
Цель работы: усвоить принципы работы с пакетом «Анализ данных» в MS Excel и его основными элементами.
Программа работы: |
|
Методические указания к выполнению индивидуальной работы
Описательная статистика в ms Excel
Задача 1. Товарная биржа в августе текущего года проводит фьючерсные торги** на сахар, который поступит с сахарных заводов в ноябре текущего года. По результатам торгов установлена цена 1850 грн/т. Компании «Альфа» необходимо принять решение о приобретении партии сахара, если известна динамика цен на сахар за последние 8 месяцев.
Чтобы ответить на вопрос, сначала всесторонне охарактеризуем совокупность данных (цену на товар). Для этого необходимо расчитать ряд количественных показателей.
Для расчета числовых характеристик нужно с помощью команды Сервис – Анализ данных* вызвать окно, отображенное на рис.1, в котором выбирается команда Описательная статистика.
Рис. 1. |
Окно Описательная статистика (рис. 2.) предназначенное для выбора диапазона исходных данных (Входной интервал), меток для обозначения данных (Метки в первой строке (столбце)). Для расчета числовых характеристик нужно установить флажок Итоговая статистика. |
Рис. 2.
Результат расчета приведен на рис. 3.
Рис. 3.
Корреляционный и регрессионный анализ в ms Excel
Основная задача корреляционного анализа заключается в определении параметров тесноты вероятностной связи между случайными величинами. Задача же регресионного анализа сводится к получению функциональных зависимостей между случайными величинами для оценки неизвестных значений этих величин.
Вычисления коэффициента корреляции
Коэффициент корреляции r характеризует степень приближения зависимости между случайными величинами к линейной функциональной зависимости. Коэффициент корреляции принимает значения –1 r 1. Коэффициент корреляции может быть равен –1 или 1, только если величины линейно зависят друг от друга. Величина r, близкая к –1 или 1, указывает, что зависимость между данными величинами почти линейная. Значения r, близкие к нулю, означают, что связь между данными величинами либо слабая, либо не носит линейного характера. Если r>0, то связь между переменными прямая. При r<0 связь обратная.
Для примера рассмотрим задачу 2.
Задача 2. Дана информация о динамике некоторых экономических показателей за 8 лет. Необходимо оценить взаимосвязь и взаимное влияние этих показателей.
Для решения задачи 2. необходимо произвести расчет коэффициентов корреляции, оценивающих тесноту связи между двумя случайными величинами.
Таблица входных данных имеет вид, представленный на рис. 4.
Рис. 4.
Для вычисления коэффициентов корреляции избираем в окне Анализ данных (рис. 1.) раздел Корреляция. В окне Корреляция (рис. 5.) нужно указать диапазон входного интервала (флажок Метки в первой строке(столбце) устанавливается тогда, когда диапазон включает заголовки данных). Матрица коэффициентов может быть представлена либо в квадратном, либо в прямоугольном виде. Результат расчета имеет вид окна рис. 6. Он имеет вид треугольной матрицы.
Рис. 5.
Рис. 6.
Наиболее информативной для решения задачи 2. является квадратная матрица . Из нее следует:
величина платы за электроэнергию прямо пропорциональна тарифу на элктроэнергию (коэффициент корреляции равен 1)*;
цены на уголь и сталь в значительно степени связаны с величиной тарифа на электроэнергию (коэффициенты корреляции соответственно равны 0,93 и 0,91);
цена на рожь связана с величиной тарифа на электроэнергию в значительно меньшей степени (коэффициент корреляции 0,49);
высока степень связи цен на уголь и сталь (коэффициент корреляции равен 0,93);
практически не связаны цены на рожь с ценами на сталь и уголь (коэффициенты соответственно равны 0,34 и 0, 24).