Anàlisi de correlacions en Excel. Un exemple de realització d'una anàlisi de correlació

L'anàlisi de correlacions és un mètode d'investigació comú que s'utilitza per determinar el nivell de dependència del 1r valor del 2n. El full de càlcul té una eina especial que permet implementar aquest tipus de recerca.

L'essència de l'anàlisi de correlacions

Cal determinar la relació entre dues magnituds diferents. En altres paraules, revela en quina direcció (menor / més gran) el valor canvia en funció dels canvis en el segon.

Finalitat de l'anàlisi de correlacions

La dependència s'estableix quan s'inicia la identificació del coeficient de correlació. Aquest mètode difereix de l'anàlisi de regressió, ja que només hi ha un indicador calculat mitjançant correlació. L'interval canvia de +1 a -1. Si és positiu, un augment del primer valor contribueix a un augment del segon. Si és negatiu, un augment del primer valor contribueix a una disminució del segon. Com més gran és el coeficient, més fort afecta el segon.

Important Al 0è coeficient, no hi ha relació entre les magnituds.

Càlcul del coeficient de correlació

Analitzem el càlcul en diverses mostres. Per exemple, hi ha dades tabulars, on la despesa en promoció publicitària i el volum de vendes es descriuen per mesos en columnes separades. A partir de la taula, esbrinarem el nivell de dependència del volum de vendes dels diners invertits en promoció publicitària.

Mètode 1: determinació de la correlació mitjançant l'assistent de funcions

CORREL: una funció que us permet implementar una anàlisi de correlació. Forma general - CORREL(massiv1;massiv2). Instruccions detallades:

1. Cal seleccionar la cel·la en la qual està previst mostrar el resultat del càlcul. Feu clic a "Insereix funció" situat a l'esquerra del camp de text per introduir la fórmula.

2. S'obre l'assistent de funcions. Aquí has ​​de trobar CORREL, feu-hi clic i després a "D'acord".

3. S'obre la finestra d'arguments. A la línia “Matriu1” heu d'introduir les coordenades dels intervals del 1r dels valors. En aquest exemple, aquesta és la columna Valor de vendes. Només cal que seleccioneu totes les cel·les que hi ha en aquesta columna. De la mateixa manera, cal afegir les coordenades de la segona columna a la línia "Matriu2". En el nostre exemple, aquesta és la columna Costos de publicitat.

4. Després d'introduir tots els intervals, feu clic al botó "D'acord".

El coeficient es mostrava a la cel·la que s'indicava al començament de les nostres accions. El resultat obtingut és 0,97. Aquest indicador reflecteix l'alta dependència del primer valor del segon.

Mètode 2: calcular la correlació amb el paquet d'eines d'anàlisi

Hi ha un altre mètode per determinar la correlació. Aquí s'utilitza una de les funcions que es troben al paquet d'anàlisi. Abans d'utilitzar-lo, cal activar l'eina. Instruccions detallades:

1. Aneu a la secció "Fitxer".

2. S'obrirà una nova finestra, en la qual haureu de fer clic a la secció "Configuració".
3. Feu clic a "Complements".
4. Trobem l'element "Gestió" a la part inferior. Aquí heu de seleccionar "Complements d'Excel" al menú contextual i fer clic a "D'acord".

5. S'ha obert una finestra de complements especials. Col·loqueu una marca de verificació al costat de l'element "Paquet d'anàlisi". Fem clic a "D'acord".
6. L'activació ha estat correcta. Ara anem a Dades. Va aparèixer el bloc "Anàlisi", en el qual heu de fer clic a "Anàlisi de dades".
7. A la nova finestra que apareix, seleccioneu l'element "Correlació" i feu clic a "D'acord".

8. La finestra de configuració de l'anàlisi va aparèixer a la pantalla. A la línia "Interval d'entrada" cal introduir l'interval de totes les columnes que participen en l'anàlisi. En aquest exemple, aquestes són les columnes "Valor de vendes" i "Costos publicitaris". La configuració de visualització de sortida s'estableix inicialment en Nou full de treball, el que significa que els resultats es mostraran en un full diferent. Opcionalment, podeu canviar la ubicació de sortida del resultat. Després de fer tots els paràmetres, feu clic a "D'acord".

Els resultats finals són fora. El resultat és el mateix que en el primer mètode: 0,97.

Definició i càlcul del coeficient de correlació múltiple en MS Excel

Per identificar el nivell de dependència de diverses magnituds, s'utilitzen múltiples coeficients. En el futur, els resultats es resumiran en una taula separada, anomenada matriu de correlació.

Guia detallada:

1. A la secció “Dades”, trobem el bloc “Anàlisi” ja conegut i clicem “Anàlisi de dades”.

2. A la finestra que apareix, feu clic a l'element "Correlació" i feu clic a "D'acord".
3. A la línia "Interval d'entrada" conduïm l'interval de tres o més columnes de la taula font. El rang es pot introduir manualment o simplement seleccionar-lo amb la LMB, i apareixerà automàticament a la línia desitjada. A "Agrupament", seleccioneu el mètode d'agrupació adequat. A "Output Parameter" especifica la ubicació on es mostraran els resultats de la correlació. Fem clic a "D'acord".

4. A punt! Es va construir la matriu de correlació.

Coeficient de correlació de parells en Excel

Anem a esbrinar com dibuixar correctament el coeficient de correlació de parells en un full de càlcul Excel.

Càlcul del coeficient de correlació de parells en Excel

Per exemple, teniu valors x i y.

X és la variable dependent i y és la variable independent. Cal trobar la direcció i la força de la relació entre aquests indicadors. Instrucció pas a pas:

1. Trobem els valors mitjans amb la funció COR.

2. Anem a calcular cadascun х и xavg, у и mitjana utilitzant l'operador «-».

3. Multipliquem les diferències calculades.

4. Calculem la suma dels indicadors d'aquesta columna. El numerador és el resultat trobat.

5. Calcula els denominadors de la diferència х и x-mitjana, y и y-mitjana. Per fer-ho, realitzarem el quadrat.

6. Utilitzant la funció AUTOSUMMA, cerqueu els indicadors a les columnes resultants. Fem la multiplicació. Utilitzant la funció ARREL quadrat el resultat.

7. Calculem el quocient utilitzant els valors del denominador i del numerador.

8. CORREL és una funció integrada que permet evitar càlculs complexos. Anem a l'"Assistent de funcions", seleccionem CORREL i especifiquem les matrius d'indicadors х и у. Construïm un gràfic que mostra els valors obtinguts.

Matriu de coeficients de correlació per parelles en Excel

Analitzem com calcular els coeficients de matrius aparellades. Per exemple, hi ha una matriu de quatre variables.

Instrucció pas a pas:

1. Anem a l'"Anàlisi de dades", situat al bloc "Anàlisi" de la pestanya "Dades". Seleccioneu Correlació de la llista que apareix.
2. Establim tots els paràmetres necessaris. "Interval d'entrada": l'interval de les quatre columnes. "Interval de sortida": el lloc on volem mostrar els totals. Fem clic al botó "D'acord".
3. Es va construir una matriu de correlació al lloc escollit. Cada intersecció d'una fila i una columna és un coeficient de correlació. El número 1 es mostra quan les coordenades coincideixen.

Funció CORREL per determinar relació i correlació en Excel

CORREL: una funció que s'utilitza per calcular el coeficient de correlació entre 2 matrius. Vegem quatre exemples de totes les habilitats d'aquesta funció.

Exemples d'ús de la funció CORREL a Excel

Primer exemple. Hi ha una placa amb informació sobre els salaris mitjans dels empleats de l'empresa al llarg d'onze anys i el tipus de canvi de $. Cal identificar la relació entre aquestes dues magnituds. La taula té aquest aspecte:

L'algorisme de càlcul és el següent:

La puntuació que es mostra és propera a 1. Resultat:

Determinació del coeficient de correlació de l'impacte de les accions sobre el resultat

Segon exemple. Dos licitadors es van dirigir a dues agències diferents per demanar ajuda amb una promoció de quinze dies. Cada dia es feia una enquesta social, que determinava el grau de suport de cada sol·licitant. Qualsevol entrevistat podria triar un dels dos sol·licitants o oposar-se a tots. Cal determinar fins a quin punt cada promoció publicitària va influir en el grau de suport als sol·licitants, quina empresa és més eficient.

Utilitzant les fórmules següents, calculem el coeficient de correlació:

• =CORREL(A3:A17;B3:B17).
• =CORREL(A3:A17;C3:C17).

Resultats:

Dels resultats obtinguts, es desprèn que el grau de suport al 1r sol·licitant augmentava cada dia de promoció publicitària, per tant, el coeficient de correlació s'acosta a 1. Quan es va llançar la publicitat, l'altre sol·licitant tenia un gran nombre de confiança, i per 5 dies hi va haver una tendència positiva. Aleshores, el grau de confiança va disminuir i el quinze dia va baixar per sota dels indicadors inicials. Les puntuacions baixes suggereixen que la promoció ha afectat negativament el suport. No oblideu que altres factors concomitants que no es consideren en forma tabular també podrien afectar els indicadors.

Anàlisi de la popularitat del contingut per correlació de visualitzacions de vídeo i republicacions

Tercer exemple. Una persona per promocionar els seus propis vídeos a l'allotjament de vídeos de YouTube utilitza les xarxes socials per anunciar el canal. Observa que hi ha alguna relació entre el nombre de republicacions a les xarxes socials i el nombre de visualitzacions al canal. És possible predir el rendiment futur mitjançant eines de full de càlcul? Cal identificar la raonabilitat d'aplicar l'equació de regressió lineal per predir el nombre de visualitzacions de vídeo en funció del nombre de republicacions. Taula amb valors:

Ara cal determinar la presència d'una relació entre 2 indicadors segons la fórmula següent:

0,7;IF(CORREL(A3:A8;B3:B8)>0,7;”Relació directa forta”;”Relació inversa forta”);”Relació feble o sense”)' class='formula'>

Si el coeficient resultant és superior a 0,7, llavors és més apropiat utilitzar la funció de regressió lineal. En aquest exemple, fem:

Ara estem construint un gràfic:

Apliquem aquesta equació per determinar el nombre de visualitzacions a 200, 500 i 1000 accions: =9,2937*D4-206,12. Obtenim els següents resultats:

function PREVISIÓ permet determinar el nombre de visualitzacions en aquest moment, si hi ha hagut, per exemple, dos-cents cinquanta republicacions. Apliquem: 0,7;PREDICCIÓ(D7;B3:B8;A3:A8);"Els valors no estan relacionats")' class='fórmula'>. Obtenim els següents resultats:

Característiques de l'ús de la funció CORREL a Excel

Aquesta funció té les següents característiques:

1. No es tenen en compte les cel·les buides.
2. Les cel·les que contenen informació de tipus booleà i text no es tenen en compte.
3. La doble negació "-" s'utilitza per tenir en compte els valors lògics en forma de nombres.
4. El nombre de cel·les de les matrius estudiades ha de coincidir, en cas contrari es mostrarà el missatge #N/A.

Valoració de la significació estadística del coeficient de correlació

Quan es prova la significació d'un coeficient de correlació, la hipòtesi nul·la és que l'indicador té un valor de 0, mentre que l'alternativa no. Per a la verificació s'utilitza la fórmula següent:

Conclusió

L'anàlisi de correlacions en un full de càlcul és un procés senzill i automatitzat. Per fer-ho, només cal saber on es troben les eines necessàries i com activar-les a través de la configuració del programa.