En un treball estadístic, on la quantitat de dades amb que es treballa és gran, es fa necessari trobar uns nombres que representin aquestes dades i uns altres que ens informin de lo separades que estan les dades reals d'aquests nombres.

Els paràmetres de centralització són valors que informen sobre el valor central al voltant del qual es distribueixen les dades. Els més habituals són: la mitjana aritmètica, la mediana i la moda.

Els paràmetres de dispersió: són valors que informen sobre el grau de separació o concentració de les dades. Els més habituals són: el rang, la variància i la desviació estàndard.

L'EXCEL incorpora unes funcions que ens permeten calcular els paràmetres anteriors.

Obre el fitxer parametres.xls . El primer full conté el número de sabata de 50 nois i noies.

A la cel·la C2 escriu: "Mitjana aritmètica". Situa't a la cel·la D2 i tria l'opció de menú Insertar | Función... Apareixerà la finestra Pegar función.

A Categoria de la función tria Estadísticas i a Nombre de función tria Promedio.

Observa l'explicació que apareix a sota. Clica Aceptar.

Apareixerà una nova finestra. Clica sobre la icona que apareix a la dreta de Número 1 . Selecciona el rang A2:A51. Clica sobre la icona de la dreta de la finestra flotant.

Observa que a la finestra Promedio ha sortit el rang seleccionat, els primers nombres seleccionats i el resultat. Clica Aceptar.

Quina és la fórmula que has posat a la cel·la D2?

De la cel·la C3 fins la cel·la C8, escriu "Mediana", "Moda", "Rang", "Desviació mitjana", "Desviació estàndard" i "Variància" respectivament. De la cel·la D3 fins D8 posa la fórmula corresponent d'una manera anàloga a la descrita anteriorment. En el cas del rang, la fórmula que hauràs de posar consisteix en la resta de dues funcions incorporades. En el cas de la desviació estàndard i de la variància són: desvestp( ) i varp( ) respectivament, tot i que si consultes l'ajuda, trobaràs diverses possibilitats.

El resultat ha de ser aquest:

Quines fórmules has posat en cadascuna de les cel·les següents?
  D2 à
  D4 à
  D5 à
  D6 à
  D7 à
  D8 à

Sovint les dades venen recollides en una taula de freqüències, i no com a una llista llarga de dades repetides. Aleshores el procediment per trobar els paràmetres estadístics és diferent. No podem fer servir les funcions incorporades, doncs no tenen en compta la freqüència. Caldrà fer-ho manualment. (Pensa que l'EXCEL és un full de càlcul i no un paquet estadístic).

Obre un nou full de càlcul i anomena'l lletres.xls . Omple la dues primeres columnes amb la informació següent:

A partir de la cel·la A17 i cap a baix (has d'ampliar la columna A) escriu: " Rang " , " Moda ", " Mediana ", " Mitjana aritmètica ", " Desviació mitjana ", " Variància " , " Desviació estàndard ".

Per calcular el rang posarem a la cel·la B17 la fórmula següent:

Per calcular la moda hem de trobar el valor més freqüent amb la funció incorporada MAX( ). El resultat és 36, però el que volem és el valor de la variable que té freqüència 36. Per això caldrà fer servir les funcions incorporades següents: INDICE( ), COINCIDIR( ) i MAX( ).

La idea és la següent.
Amb MAX(B2:B14) trobem el valor màxim.
Amb COINCIDIR(MAX(B2:B14);B2:B14;0) trobem quin lloc ocupa el 36 en el rang B2:B14.


Amb 0 indiquem que mostri el lloc del primer valor que trobi exactament igual al valor buscat.

Amb INDICE(A2:A14;COINCIDIR(MAX(B2:B14);B2:B14;0)) trobem el contingut de la cel·la que es troba en la posició COINCIDIR(MAX(B2:B14);B2:B14;0) dins el rang A2:A14.

Per tant caldrà posar a la cel·la B18 la fórmula següent:

=INDICE(A2:A14; COINCIDIR(MAX(B2:B14);B2:B14;0) )
                                  Ens diu que 36 està en segona posició

Per calcular la mediana ens caldrà afegir una nova columna amb les freqüències absolutes acumulades.

Afegeix les freqüències absolutes acumulades a la columna C.

Quina fórmula has fet servir? En quina cel·la l'has posat?

Ara hem de buscar quina dada ocupa el lloc central. El nombre de dades està a la cel·la C14. La meitat és 63, haurem de buscar en quina fila de la columna C està el 63 o el nombre immediatament superior. La funció incorporada COINCIDIR (amb l'últim paràmetre igual a 1) ens dona l'ubicació del nombre igual o immediatament inferior al donat. Amb INDICE tindrem el valor corresponent dins el rang A2:A14.
=INDICE(A2:A14;COINCIDIR(C14/2;C2:C14;1))

Si el 63 aparegués a la llista de freqüències acumulades voldria dir que el nombre de dades és imparell i la mediana seria la mitja aritmètica entre la dada que ocupa el lloc 63 i la dada que ocupa el lloc 64.

En el cas que el 63 no aparegués a la llista de freqüències acumulades, COINCIDIR(C14/2;C2:C14;1) retorna la posició 4, la immediatament inferior. Nosaltres volem que retorni la posició 5. Només hem de sumar-li 1 a COINCIDIR(C14/2;C2:C14;1).

Així doncs la fórmula final és:

Comprova si C14/2 apareix a la llista de freqüències acumulades

=SI(INDICE(C2:C14;COINCIDIR(C14/2;C2:C14;1))=C14/2;
(INDICE(A2:A14;COINCIDIR(C14/2;C2:C14;1)+1)+INDICE(A2:A14;COINCIDIR(C14/2;C2:C14;1)))/2;
                                                                        Si apareéis, fa la mitjana aritmètica
INDICE(A2:A14;COINCIDIR(C14/2;C2:C14;1)+1))

Escriu la fórmula següent a la cel·la C19.
=INDICE(A2:A14;COINCIDIR(C14/2;C2:C14;1))

A la cel·la C20 escriu la fórmula:
=INDICE(A2:A14;COINCIDIR(C14/2;C2:C14;1)+1)

A la cel·la corresponent a la mediana escriu:
=SI(INDICE(C2:C14;COINCIDIR(C14/2;C2:C14;1))=C14/2;(C19+C20)/2;C20)

Per calcular la mitjana aritmètica afegirem una nova columna que contingui el producte del valor per la seva freqüència. La mitjana aritmètica és la suma d'aquests productes dividit pel nombre total de dades.

A la cel·la D1 escriu " x_i · n_i ". Recorda que per fer els subíndex i els superíndex cal seleccionar les lletres i anar a Formato | Celdas... | Efectos . A la cel·la corresponent afegeix la fórmula que ens permeti trobar la mitjana aritmètica. Canvia el format perquè només apareguin dues xifres decimals.

Quina és la fórmula que has escrit per calcular la mitjana aritmètica?

La desviació mitjana es calcula fent servir la fórmula:

Caldrà trobar, per cada dada, el valor absolut de la diferència entre la dada i la mitjana aritmètica i multiplicar-ho per la seva freqüència; sumar aquests resultats i dividir-ho pel nombre total de dades.

A la cel·la E1 escriu "| x_i -x |· n_i ". A la cel·la E2 escriu la fórmula que permeti calcular el valor absolut de la diferència entre la primera dada i la mitjana aritmètica i multiplicar-ho per la freqüència absoluta de la primera dada. Copia aquesta fórmula al rang E3:E14.

A la cel·la corresponent escriu la fórmula que calculi la desviació mitjana.

Quina fórmula incorporada permet troba el valor absolut d'un nombre?

Quina fórmula has posat a la cel·la E2?

Per calcular la variància hi ha dues expressions equivalents. Nosaltres farem servir la segona, doncs requereix menys càlculs.

En la segona expressió caldrà sumar les dades al quadrat per la seva freqüència, el resultat dividir-lo pel nombre total de dades i restar-li la mitjana aritmètica al quadrat.

La desviació típica és l'arrel quadrada de la variància:

A la cel·la F1 escriu " x_i² · n_i ". A la cel·la F2 afegeix una fórmula que calculi el quadrat de la primera dada per la seva freqüència. Copia la fórmula al rang F3:F14.

El resulta final ha de ser el següent:

Quines són les fórmules que has escrit per calcular la variància i la desviació típica?

Explica que fan cadascuna d'aquestes funcions incorporades:

ABS (M)

COINCIDIR (M; N; 0)

COINCIDIR (M; N; 1)

INDICE (M; N)

SI (M; V; F)