Лабораторная работа № 7
Корреляционно-регрессионный анализ с помощью процедур пакета «Анализ данных»
Определить наличие корреляционной связи между золотом и свинцом в рудах по выборке, представленной в таблице 1. (Данные из таблицы использовались в ЛР 5.1)
Таблица 1. Содержание свинца и золота в рудах полиметаллического месторождения
№ проб | Pb | Au | № проб | Pb | Au | № проб | Pb | Au |
1 | 2,05 | 3,76 | 19 | 1,21 | 0,61 | 37 | 5,16 | 0,87 |
2 | 5,03 | 2,09 | 20 | 2,92 | 0,40 | 38 | 0,37 | 1,15 |
3 | 0,80 | 1,98 | 21 | 0,74 | 0,27 | 39 | 0,44 | 0,91 |
4 | 0,31 | 0,20 | 22 | 1,53 | 2,57 | 40 | 2,21 | 4,25 |
5 | 0,77 | 3,10 | 23 | 3,70 | 0,90 | 41 | 4,67 | 2,03 |
6 | 4,01 | 1,67 | 24 | 2,71 | 1,69 | 42 | 1,44 | 4,31 |
7 | 1,19 | 2,59 | 25 | 1,90 | 4,32 | 43 | 3,13 | 0,25 |
8 | 1,26 | 1,70 | 26 | 1,51 | 2,30 | 44 | 1,35 | 0,39 |
9 | 0,68 | 0,23 | 27 | 0,21 | 1,22 | 45 | 0,81 | 1,35 |
10 | 0,91 | 1,21 | 28 | 4,81 | 1,05 | 46 | 1,32 | 3,51 |
11 | 4,33 | 0,91 | 29 | 1,38 | 2,09 | 47 | 0,99 | 1,62 |
12 | 2,38 | 1,68 | 30 | 3,96 | 2,54 | 48 | 2,41 | 3,98 |
13 | 0,98 | 2,44 | 31 | 1,96 | 1,58 | 49 | 1,03 | 0,35 |
14 | 0,42 | 0,50 | 32 | 0,52 | 0,82 | 50 | 1,55 | 2,80 |
15 | 1,71 | 1,21 | 33 | 2,95 | 0,20 | 51 | 3,39 | 0,41 |
16 | 3,51 | 1,15 | 34 | 1,10 | 1,44 | 52 | 1,23 | 1,58 |
17 | 1,11 | 2,30 | 35 | 0,93 | 3,15 | 53 | 1,48 | 4,22 |
18 | 2,10 | 3,48 | 36 | 1,78 | 1,21 | 54 | 4,03 | 1,19 |
В Excel для вычисления корреляции также используется процедура Корреляция. Процедура позволяет получить корреляционную матрицу, содержащую коэффициенты корреляции между различными параметрами.
Для реализации процедуры необходимо:
- выполнить команду Данные/Анализ данных; в появившемся списке Инструменты анализа выбрать строку Корреляция и нажать кнопку OK; в появившемся диалоговом окне указать Входной интервал, то есть ввести ссылку на ячейки, содержащие анализируемые данные. Входной интервал должен содержать не менее двух столбцов. в разделе Группировка переключатель установить в соответствии с введенными данными; указать выходной диапазон, то есть ввести ссылку на ячейки, в которые будут выведены результаты анализа. Для этого следует поставить флажок в левое поле Выходной интервал, далее навести указатель мыши на правое поле ввода Выходной интервал и щелкнуть левой кнопкой мыши, затем указатель мыши навести на левую верхнюю ячейку выходного диапазона и щелкнуть левой кнопкой мыши. Размер выходного диапазона будет определен автоматически, и на экран будет выведено сообщение в случае возможного наложения выходного диапазона на исходные данные. нажать кнопку OK.
Результаты анализа. В выходной диапазон будет выведена корреляционная матрица, в которой на пересечении каждых строки и столбца находится коэффициент корреляции между соответствующими параметрами. Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 1, так как каждый столбец во входном диапазоне полностью коррелирует с самим собой.
Интерпретация результатов. Рассматривается отдельно каждый коэффициент корреляции между соответствующими параметрами. Отметим, что хотя в результате будет получена треугольная матрица, корреляционная матрица симметрична, и коэффициенты корреляции rij =rji.
Задание 1
Определить наличие корреляционной связи между золотом и свинцом в рудах по выборке, представленной в табл. 1, используя процедуру Корреляция пакета Анализ данных.
Решение
Для выполнения корреляционного анализа введите в диапазон A2:B55 исходные данные. В ячейки A1 и B1 введите соответственно Pb и Au.
Затем в меню Данные выберите пункт Анализ данных и далее укажите строку Корреляция. В появившемся диалоговом окне укажите Входной интервал A1:B55. Укажите, что данные рассматриваются по столбцам. Установите флажок в поле Метки в первой строке. Укажите выходной диапазон. Для этого поставьте флажок в левое поле Выходной интервал и в правое поле ввода Выходной интервал введите А57. Нажмите кнопку OK.
Результаты анализа. В выходном диапазоне получаем корреляционную матрицу. Подразумевается, что в пустых клетках в правой верхней половине таблицы находятся те же коэффициенты корреляции, что и в нижней левой (симметрично расположенные относительно диагонали). Ячейки выходного диапазона, имеющие совпадающие координаты строк и столбцов, содержат значение 1, так как каждый столбец во входном диапазоне полностью коррелирует с самим собой.
Интерпретация результатов. Из таблицы видно, что корреляция между свинцом и золотом в пробах равна –0,04918. Значит, можно говорить о том, что наличие корреляционной связи между золотом и свинцом в рудах отсутствует.
При отсутствии значимой корреляционной связи между золотом и свинцом, следует проверить гипотезу о том, что такая связь может существовать только для бедных руд. Для этого из выборочных данных таблицы необходимо убрать пробы со значениями свинца более 1,5 %. Ввести в F1 и G1 соответственно Pb и Au. Ввести в столбец F только те значения, которые удовлетворяют условию отбора проб со значениями свинца не более 1,5 %. В столбец G – соответствующие значения для золота. Аналогично выполнить процедуру Корреляция, указав выходной интервал – F30.
Интерпретация результатов. Из таблицы видно, что корреляция между свинцом и золотом в пробах равна 0,54359. Можно говорить о наличии корреляционной связи между золотом и свинцом в рудах
Если во вкрапленных рудах существует значимая корреляционная связь между золотом и свинцом, следует провести регрессионный анализ для определения уравнения зависимости концентрации золота от содержания в рудах свинца.
Задание 2
Требуется
Продолжив работу с данными предыдущего примера, необходимо построить регрессионное уравнение, описывающее связь между золотом и свинцом в рудах по выборке из таблицы 1, используя процедуру Регрессия пакета Анализ данных.
Решение
1. Откройте рабочую книгу с результатами предыдущего Задания 1
2. В пункте меню Данные выберите строку Анализ данных и далее укажите курсором мыши на строку Регрессия.
3. В появившемся диалоговом окне задайте Входной интервал Y: G1:G28. (Обратите внимание, что зависимые данные – это те данные, которые предполагается вычислять).
4. Так же укажите Входной интервал X, то есть введите ссылку на диапазон независимых данных F1:F28. (Независимые данные – это те данные, которые будут измеряться или наблюдаться).
5. Установите флажок в поле Метки в первой строке. Установите флажок в поле График подбора.
6. Далее укажите выходной диапазон. Для этого поставьте переключатель в положение Выходной интервал (наведите указатель мыши и щелкните левой кнопкой), затем наведите указатель мыши на правое поле ввода Выходной интервал и, щелкнув левой кнопкой мыши, указатель мыши наведите на левую верхнюю ячейку выходного диапазона (K3). Щелкните левой кнопкой мыши. Нажмите кнопку OK.
Результаты анализа. В выходном диапазоне появятся результаты и графики подбора и остатков.
Интерпретация результатов. В таблице Дисперсионный анализ оценивается общее качество полученной модели ее достоверность по уровню значимости критерия Фишера – р, который должен быть меньше, чем 0,05 (строка Регрессия, столбец Значимость F, в примере 0,00338372, то есть p =0,00338372 – модель значима, и степень точности описания моделью процесса – R-квадрат (вторая строка сверху в таблице Регрессионная статистика, в примере R-квадрат = 0,2954899. Поскольку R-квадрат < 0,95, можно говорить о невысокой точности аппроксимации – линейная модель не очень хорошо описывает явление.
Далее необходимо определить значения коэффициентов модели. Они определяются из таблицы в столбце Коэффициенты – в строке Y-пересечение приводится свободный член, в строках соответствующих переменных приводятся значения коэффициентов при этих переменных. В столбце p-значение приводится достоверность отличия соответствующих коэффициентов от нуля. В случаях, когда р > 0,05, коэффициент может считаться нулевым. Это означает, что соответствующая независимая переменная практически не влияет на зависимую переменную и коэффициент может быть убран из уравнения.
Отсюда выражение для определения концентрации золота в пробе по концентрации свинца будет иметь следующий вид: 0,0440856+1,7633983*концентрацияPb. Или, учитывая, что коэффициент для Y-пересечения незначим, – вид: 1,7633983*концентрацияPb.
Воспользовавшись полученным уравнением, можно рассчитать ожидаемую концентрацию золота в пробе по концентрации свинца. Например, для расчета концентрации золота при концентрации свинца равной 0,5 необходимо поставить табличный курсор в любую свободную ячейку (G35); ввести с клавиатуры знак =, щелкнуть указателем мыши по ячейке L19, ввести с клавиатуры знак +, щелкнуть по ячейке L20, ввести с клавиатуры знак * и число 0,5. В результате в ячейке G35 будет получена ожидаемая концентрация золота – 0,9257.
Результат приблизительно можно определить непосредственно по графику подбора. Наведите указатель мыши на график подбора и по появившейся надписи (см. рис.2) определите приближенно значение ожидаемой концентрация золота – 0,9610527.
Рис. 2. Определение приближенного результата по графику подбора
