Как да намерите коефициент на корелация в Excel: Стъпка по стъпка ръководство

Корелацията между две променливи показва как те са свързани и ни помага да разберем модели, тенденции и баланс в данните. Без значение в каква сфера работите - финанси, инженерство, маркетинг или медицина, вероятно сте се сблъсквали с необходимостта да анализирате взаимоотношенията между променливи. Традиционно това изискваше ръчни изчисления, за да се разберат основните концепции.

Но с Excel има много по-лесен начин. В тази статия ще ви преведа през три прости метода за намиране на коефициента на корелация в Excel: ръчно изчисление, функцията PEARSON и инструмента за анализ на данни.

Какво е коефициент на корелация?

Коефициентът на корелация е статистическа мярка, която количествено определя степента и посоката на линейната връзка между две променливи, като стойностите му варират от -1 до +1. Коефициент от +1 показва перфектна положителна корелация, при която увеличаването на едната променлива е точно свързано с увеличаването на другата; коефициент от -1 представлява перфектна отрицателна корелация, при която увеличаването на едната променлива съответства на пропорционално намаляване на другата; а стойност от 0 показва липса на линейна връзка между променливите.

В анализа на данните, този показател е от съществено значение за разбирането колко близко прогнозираните стойности съвпадат с действителните резултати, което е особено важно в области като прогнозиране на търсенето. Бизнес мениджърите разчитат на точни модели – разработени чрез изследване на исторически данни и извършване на регресионни анализи – за да вземат информирани решения в производството, маркетинга и доставките. Познаването на силата и посоката на връзките между ключовите променливи помага за усъвършенстването на тези модели, гарантирайки, че стратегическите решения се основават на надеждни, базирани на данни прозрения.

Как да намерите коефициента на корелация в Excel

Excel е мощен инструмент, който опростява сложни задачи както за професионалисти, така и за студенти. Една такава задача е намирането на коефициента на корелация, който измерва силата и посоката на връзката между две променливи. В Excel можете да изчислите тази статистика, като използвате няколко метода, всеки от които е предназначен да отговаря на различни нива на експертиза и нужди.

Ще ви преведа през три различни подхода: ръчно изчисляване на коефициента стъпка по стъпка, използване на вградената функция PEARSON и използване на инструмента Data Analysis Toolpak. Всеки подход е предназначен за различни нива на опит и предоставя уникални прозрения в процеса на изчисление, като гарантира, че всички ваши въпроси за това как да изчислите коефициента на корелация в Excel са напълно отговорени.

Метод 1: Ръчно изчисление

За начинаещи най-лесният начин да изчислят коефициента на корелация в Excel е чрез използване на функцията CORREL. Тази вградена функция бързо определя връзката между два набора от стойности, без да изисква ръчни изчисления. Просто трябва да изберете стойностите за X и Y, и Excel ще изчисли резултата за вас. Ето как да изчислите коефициента на корелация в Excel с функцията CORREL:

Стъпка 1: Отворете примерен набор от данни в електронна таблица на Excel и се уверете, че имате отделна клетка за показване на резултата от коефициента на корелация.

Стъпка 2: Кликнете върху клетката където искате коефициентът на корелация да се появи.

Стъпка 3: За да започнете функцията CORREL в Excel, въведете “=” последвано от “CORREL”, след което натиснете клавиша “Tab” за да активирате функцията.

След като функцията е активирана, ще трябва да изберете стойностите за X и Y.

Стъпка 4: Първият аргумент, array1, изисква избор на диапазона от клетки, съдържащи стойностите на X, и след това натискане на клавиша "," (запетая). За нашия набор от данни ще изберем диапазона B2:B11.

Забележка: Уверете се, че не включвате заглавия на колони в избрания диапазон, тъй като Excel може да върне грешка.

Стъпка 5: Следващият аргумент, array2, изисква избор на диапазона от клетки, съдържащ Y стойности. За нашия набор от данни изберете диапазона C2:C11.

Забележка: Уверете се, че array1 и array2 имат еднакъв брой стойности. Ако са с различни размери, функцията CORREL в Excel ще върне грешка #N/A.

Стъпка 6: След като и двата диапазона от клетки са избрани, просто натиснете “Enter”, и Excel ще изчисли и покаже коефициента на корелация за вашия набор от данни.

За моя примерен набор от данни получих коефициент на корелация 0.99798, което е почти +1. Това показва изключително силна положителна линейна връзка между двата набора от данни – когато едната променлива се увеличава, другата почти винаги също се увеличава.

Метод 2: Използване на функцията PEARSON

Следващата функция, налична в Excel за изчисляване на корелация, е функцията PEARSON. Подобно на функцията CORREL, функцията PEARSON също изисква да изберете диапазоните от клетки за стойностите X и Y, за да получите резултата. Нека разгледаме стъпките:

Стъпка 1: Нека се върнем към нашия Excel документ, където ще използваме функцията PEARSON.

Стъпка 2: Кликнете върху клетката , където искате коефициентът на корелация да се появи.

Стъпка 3: За да започнете функцията PEARSON, въведете “=” последвано от “PEARSON”, след което натиснете клавиша “Tab” , за да я активирате.

Стъпка 4: За първия аргумент (array1), изберете диапазона от клетки, съдържащ стойностите X.

Стъпка 5: За следващия аргумент (array2), изберете диапазона от клетки, съдържащ Y стойностите.

Стъпка 6: След като изберете и двата диапазона от клетки, натиснете “Enter” , за да изчислите и покажете коефициента на корелация за вашия набор от данни.

Резултатите от функциите PEARSON и CORREL са идентични, тъй като изчисляват един и същ коефициент на корелация. В моя набор от данни и двете върнаха 0.99798, потвърждавайки силна положителна линейна връзка. Тъй като и двете функции изчисляват коефициента на корелация на Пиърсън по един и същи начин, те могат да се използват взаимозаменяемо в Excel. Независимо дали използвате PEARSON или CORREL, резултатът остава същият.

Метод 3: Използване на инструмента за анализ на данни

Последният метод, който ще използваме за изчисляване на коефициента на корелация в Excel, е инструментът за анализ на данни. За разлика от предишните два метода, този не изисква функция. Инструментът за анализ на данни е добавка, така че първо се уверете, че е активиран в настройките на Excel. Ето как можете да използвате инструмента Data Analysis Toolpak за изчисляване на коефициента на корелация:

Стъпка 1: Отворете електронната таблица, съдържаща вашия набор от данни, и отидете в раздела "Данни" в лентата с менюта.

Стъпка 2: В лентата "Данни" потърсете вдясно бутона"Анализ на данни" и кликнете върху него, за да стартирате инструмента за анализ на данни.

Забележка: Ако не виждате опцията "Анализ на данни", добавката може да не е активирана. Отидете на Опции > Добавки > Управление > Добавки на Excel > Пакет за анализ , за да я активирате.

Стъпка 3: В изскачащия прозорец "Анализ на данни" превъртете надолу и щракнете двукратно върху"Корелация" , за да продължите.

Стъпка 4: В прозореца за корелация намерете полето"Входен обхват" и изберете целия диапазон от клетки, който включва както X, така и Y стойности.

Забележка: Ако вашите данни включват заглавия на колони, маркирайте опцията "Етикети в първия ред" за да избегнете грешки.

Стъпка 5: Преминете към секцията "Опции за изход" и изберете къде искате коефициентът на корелация да бъде показан. Можете да изберете изходен диапазон в същия работен лист, нов работен лист или дори нова работна книга.

Стъпка 6: Прегледайте избраните опции, за да сте сигурни, че всички детайли са верни, след което кликнете върху"OK" , за да изчислите коефициента на корелация за избрания диапазон от данни.

Стъпка 7: Въз основа на избраното от вас място за изход, коефициентът на корелация ще бъде показан в таблица.

Независимо от използвания метод; CORREL, PEARSON или Data Analysis Toolpak, коефициентът на корелация остава същият. Data Analysis Toolpak е особено полезен за по-големи набори от данни, тъй като ефективно генерира резултати в добре структуриран табличен формат.

Най-добра безплатна алтернатива - WPS Office

WPS Office е феноменален инструмент, особено когато става въпрос за ефективност в Excel. Той не само е достъпна алтернатива на Microsoft Office, но и се равнява по функционалност. Можете да извършвате практически всички същите задачи, било то създаване на графики, анализ на данни или управление на електронни таблици.

Интуитивният дизайн улеснява навигацията между различните инструменти. Ако трябва да намерите коефициента на корелация между две променливи, независимо дали за финансов анализ, академични изследвания или бизнес прозрения—WPS Office ви позволява да го изпълните лесно с вградени функции и инструменти за данни.

Как да намерите коефициента на корелация в WPS Excel

WPS Spreadsheet също ви позволява да изчислите коефициента на корелация, като използвате функциите CORREL и PEARSON. Процесът е много подобен на начина, по който тези функции се използват в Excel. Нека преминем през стъпките за изчисляване на коефициента на корелация с помощта на функцията CORREL в WPS Spreadsheet.

Стъпка 1: Отворете вашия набор от данни в WPS Spreadsheet, за да изчислите коефициента на корелация.

Стъпка 2: Кликнете върху клетката , където искате да се покаже коефициентът на корелация.

Стъпка 3: Започнете, като напишете„=“ последвано от „CORREL“, след което натиснете „Tab“ клавиша, за да активирате функцията.

След като функцията CORREL вече е активирана, нека изберем диапазоните за X и Y, за да завършим функцията.

Стъпка 4: За първия аргумент(array1), изберете диапазона от клетки, който съдържа вашите X стойности. Натиснете запетая ( , ) клавиша, за да преминете към следващия аргумент.

Стъпка 5: За втория аргумент(array2), изберете диапазона, който съдържа вашите Y стойности.

Стъпка 6: Натиснете “Enter”, и WPS Spreadsheet ще изчисли и покаже коефициента на корелация за избраните данни.

Използването на WPS Spreadsheet за изчисляване на коефициента на корелация не е просто работа с числа – това е преживяване. Модерният и интуитивен интерфейс на WPS Spreadsheet наистина се откроява, правейки използването на функциите му лесно и приятно.

На пазара, изпълнен с алтернативи, WPS Spreadsheet се отличава с приятелския си дизайн и допълнителното предимство да бъде напълно безплатен, премахвайки всякакви притеснения относно скъпи лицензионни такси. Тази комбинация от ефективност, яснота и рентабилност го прави предпочитан инструмент за управление и анализ на набори от данни.

ЧЗВ:

1. Мога ли да изчисля корелация за повече от две променливи?

Да, възможно е да се изчисли корелация за повече от две променливи чрез генериране на корелационна матрица. Тази матрица представя взаимоотношенията между множество променливи едновременно. В Excel може да се използва Data Analysis Toolpak за създаване на корелационна матрица, която предоставя корелационни коефициенти за всички избрани променливи.

2. Каква е разликата между функциите CORREL и PEARSON?

Функциите CORREL и PEARSON в Excel имат една и съща цел – изчисляват коефициента на корелация на Пиърсън. Този коефициент показва колко силно са линейно свързани две променливи, като стойностите варират от -1 до 1. Въпреки че функциите имат различни имена, те работят идентично и предоставят едни и същи резултати.

3. Защо липсва моят инструмент за анализ на данни (Data Analysis ToolPak)?

Ако инструментът за анализ на данни (Data Analysis ToolPak) не е наличен в Excel, може да се наложи да го активирате ръчно. По-долу е стъпка по стъпка ръководство за неговото активиране:

Стъпка 1: Стартирайте Excel и отидете на раздела“Файл”, след което изберете“Опции”.

Стъпка 2: В прозореца “Опции на Excel”, кликнете върху “Добавки” от страничната лента.

Стъпка 3: Намерете падащото меню„Управление“ в долната част, изберете „Добавки за Excel“ и щракнете върху „Старт“.

Стъпка 4: В изскачащия прозорец изберете„Анализен инструментариум“ като маркирате квадратчето, след което щракнете върху „OK“.

Стъпка 5: Рестартирайте Excel, за да се гарантира, че промените влязат в сила.

Лесна обработка на данни с WPS Office

Всеки от тези методи - ръчно изчисление, функцията PEARSON и инструментът за анализ на данни, подхожда по различен начин към анализа на корелацията, но в крайна сметка водят до подобни резултати. Основната разлика при извършването на това как да намерите коефициент на корелация в Excel с помощта на различни методи се крие в точността и лекотата на използване, като някои методи предлагат по-голяма прецизност от други.

Ръчното изчисляване на корелацията помага за разбирането на концепцията, но може да отнеме много време. Функцията PEARSON ускорява процеса, докато инструментът за анализ на данни предоставя по-автоматизиран подход. WPS Office прави целия процес още по-удобен, като ви позволява да извършвате тези изчисления безпроблемно, спестявайки ви време и усилия, като същевременно гарантира точност.