Ajuda
Correu electrònic
 
 
Estadística
Programari
Fitxers necessaris
Activitat Word
Excel
 

Quartils. Diagrama de caixes i bigotis.

Un cop ordenades les dades, la mediana és un paràmetre estadístic que divideix les dades en dos grups amb la mateixa quantitat de dades en cadascun d'ells. També podríem dividir les dades en quatre grups iguals, per fer-ho, calen tres valors que anomenarem quartils (Q 1 , Q 2 i Q 3 ):

Observa que el segon quartil coincideix amb la mediana. Observa que per sota del 1r quartil hi ha el 25% de les dades, per sobre del 3r quartil hi ha un altre 25% de les dades i entre el primer i tercer quartil hi ha el 50% de les dades.

Aquest tres valors conjuntament amb els valors màxim i mínim ens donen una bona visió molt bona de la distribució de les dades.

L'EXCEL ens permet calcular aquest cinc paràmetres amb la funció incorporada:
     CUARTIL( rang de les dades ; nombre del 0 al 4 ).
El nombre del 0 al 4 indica quin dels 5 paràmetres volem.

 

Obre l'arxiu parametres.xls . Dins Hoja 1 , de la cel·la C10 en avall escriu: "Mínim", "Q1", "Q2", "Q3" i "Màxim". De la cel·la D10 en avall, posa la fórmula corresponent; utilitza la funció incorporada CUARTIL ( ) per trobar el mínim, els tres quartils i el màxim.

 

Si les dades venen amb la freqüència corresponent, no podem utilitzar la funció incorporada CUARTIL ( ). Hem de resoldre la situació com ho vam fer amb la mediana.

 

Obre l'arxiu lletres.xls . Des la cel·la D17 en avall escriu: "Mínim", "Q1", "Q2", "Q3" i "Màxim". Posa la fórmula corresponent al mínim i al màxim.
Observa quina és la fórmula que hi ha a la mediana:
    =SI(INDICE(C2:C14;COINCIDIR(C14/2;C2:C14;1))=C14/2;(C19+C20)/2;C20)
i a les cel·les C19 i C20:
    =INDICE(A2:A14;COINCIDIR(C14/2;C2:C14;1))     =INDICE(A2:A14;COINCIDIR(C14/2;C2:C14;1)+1)

 

Quines fórmules posarem per calcular el segon quartil (Q2)?

Quines fórmules posarem per calcular el primer quartil?

Quines fórmula posarem per calcular el tercer quartil?

 

Escriu les fórmules que ens permetin calcular Q1 i Q2. El resultat ha de ser el següent:  
Mínim 1
Q1 2
Q2 5
Q3 8
Màxim 13

 

Els diagrames de caixes i bigotis són representacions gràfiques en les quals es reflecteixen els quartils, el valor més petit i el més gran. L'EXCEL no pot fer aquest tipus de gràfic directament. Tot seguit aprendràs a crear aquest tipus de gràfic combinant dos tipus de gràfics i desar-lo com a gràfic personalitzat, per utilitzar-lo una propera vegada.

El procediment que proposem construeix un diagrama de caixes múltiple que serveix per a comparar els valors d'una mateixa variable en diferents situacions. Si el repetiu per una variable única tindreu un diagrama de caixes simple. Per a la tasca que ara ens ocuparà cal separar prèviament les dades en diferents columnes d'acord amb la variable categòrica que actua com a variable de classificació. Ho fareu utilitzant, en aquest cas, els filtres.

 

Obre el fitxer Dades74.xls. Aquest llibre té un únic full, anomenat Dades74 , que conté el pes, l'envergadura i l'alçada de noies i nois preses en tres anys consecutius (que en el fitxer es designen com anys 1, 2 i 3 tot i que les dades corresponen respectivament als 14, als 15 i als 16 anys).

Crea un full nou i anomena'l "Noies i nois". Situeu el cursor a l'interior del rang A1:J111 del full Dades74 i accediu a Datos | Filtro | Autofiltro .

Fixa't com han aparegut uns botons desplegables per a cada columna del full. Si vas prement algun d'aquests botons, veuràs com surten els diferents valors de la columna. Interessa fer servir el de la columna A per filtrar les dades de les noies i els nois, que estan codificades amb un 1 i un 2 respectivament.

Prem el botó de la columna A i tria 1. Amb aquesta acció et queden visibles només les dades de les noies. Selecciona totes aquestes dades. Per fer-ho, situa el cursor a la cel·la A1; prem simultàniament les tecles Control i Majúscules i, sense deixar-les de prémer, prem la tecla de Moviment de cursor cap a la dreta i, després, la de Moviment de cursor cap a baix . Han quedat seleccionades totes les dades visibles.

Prem CTRL+C o accedeix a Edición | Copiar . Selecciona la cel·la A1 del full Noies i nois . Premeu CRTL+V o accedeix a Edión | Pegar : has copiat les dades de les noies al full nou. Fes més petita l'amplada de les columnes de la A a la J.

Torna al full Dades74 i prem el botó de la columna A. Tria ara el nombre 2. Amb aquesta acció queden visibles totes les dades dels nois. Selecciona aquestes dades i copia-les a partir de la columna L del full Noies i nois . D'aquesta manera, deixeu una columna en blanc per separar les dades femenines i masculines. Feu més petita l'amplada de les columnes corresponents a les dades dels nois.

Torna al full Dades74 , accedeix a Datos | Filtro i desactiveu Autofiltro . Ara han desaparegut els botons de les columnes i tornen a ser visibles totes les dades inicials. Amb tot aquest procediment, has separat les dades de les noies i dels noies per poder dissenyar, a continuació, els diagrames de caixa i bigoti.

 

Crea un full nou i anomena'l "Resum " . Així, no barrejarem en un mateix full les dades amb els paràmetres i els gràfics. Escriu el rètol "Altura de les noies" a la cel·la A1. Escriu els rètols "Mínim", "Primer quartil", "Mediana", "Tercer quartil" i "Màxim" a les cel·les A3, A4, A5, A6 i A7, respectivament. Feu més gran l'amplada de la columna A. Escriu els rètols "14 anys", "15 anys" i "16 anys" a les cel·les B2, C2 i D2 respectivament. Aquests són les edats en què es van prendre les mides.

Escriu a la cel·la B3 la fórmula =CUARTIL('Noies i nois'!$D$2:$D$52;0). Aquesta fórmula calcula el mínim dels valors dels rang D2:D52, és a dir, l'altura més petita de les noies als 14 anys. Copieu aquesta fórmula sobre el rang B4:B7. És evident que has de modificar aquestes fórmules copiades.

Selecciona la cel·la B4 i accedeix a la zona de fórmula (part superior de la pantalla) per canviar el 0 per un 1. Prem RETORN. En aquesta cel·la ja ha d'haver aparegut el primer quartil d'aquestes dades. Selecciona ara la cel·la B5 i canvia el 0 de la fórmula per un 2. Fes el mateix amb les cel·les B6 i B7, canviant els 0 de les fórmules per un 3 i un 4, respectivament, per obtenir el tercer quartil i el màxim.

Selecciona el rang B3:B7 i copia'l sobre del rang C3:D7. Ara cal modificar aquestes fórmules copiades. Ho pots fer a mà o seguint les indicacions següents. Selecciona el rang C3:C7 i accedeix a Edición | Reemplazar . Escriu la lletra D a Buscar i la lletra G a Reemplazar con . Prem Reemplazar todas . D'aquesta manera, en les fórmules copiades en el rang C3:C7 has canviat el rang corresponent a les altures de 1988 per les altures de 1989. Fes el mateix en el rang D3:D7, substituint la D per la J, i tindràs els paràmetres referits a l'altura de les noies als 16 anys.

Una vegada calculats els quartils, cal fer uns càlculs previs a la representació gràfica. Escriu a la cel·la F3 la fórmula =B3. Escriu a la cel·la F4 la fórmula =B4-B3. Copia-la sobre el rang F5:F7. Selecciona el rang F3:F7 i copia'l sobre el rang G3:H7.

 

Selecciona el rang F3:H7 i accedeix a Insertar | Gráfico o bé premeu la icona dels gràfics que segurament teniu a la part superior de la pantalla . Tria el tipus de gràfic Barras i el segon subtipus de la primera fila. Prem Siguiente .

A la fitxa Rango de datos activa l'opció Filas . Accedeix a la fitxa Serie i escriu a la casella de Rótulos del eje de categorias el rang =Resum!$B$2:$D$2. Prem Siguiente .

Accedeix a Líneas de división i fes que totes les opcions estiguin desactivades. Fes el mateix en la fitxa Leyenda . Prem Siguiente .

Amb l'opció Como objeto en: Resum activada, premeu Terminar .

El gràfic que ha sortit, encara no és el que volem. L'hem de modificar. Selecciona el requadre gris i premeu SUPR per esborrar-lo. Situa el cursor sobre un dels trossos que queden més a l'esquerra de les barres i prem el botó dret del ratolí. Tria l'opció Formato de serie de datos . Accedeix a la fitxa Tramas i activa Ninguno de Borde i Ninguna de Área . Prem Aceptar . D'aquesta manera, la part esquerra de les barres ja no és visible.

Situa ara el cursor sobre un dels trossos que queden més a l'esquerra de la part visible de les barres i prem el botó dret del ratolí. Tria un altra cop l'opció Formato de serie de datos . Accedeix a la fitxa Tramas i activeu Ninguno de Borde i Ninguna de Área . Accedeix a la fitxa Barras de error Y . Selecciona la casella de Por defecto i escriu un 100 en la casella Porcentaje . Les parts de les barres seleccionades s'han substituït per uns segments d'igual longitud que els trossos substituïts.

Selecciona aquests segments, premeu el botó dret del ratolí i accedeix a Formato de barras de error . Accediu a la fitxa Tramas i seleccioneu la segona casella de Marcador . Premeu Aceptar . Repetiu aquest procediment per substituir la part de la dreta de les barres per segments similars als anteriors.

Ara ja tens dissenyats els diagrames de caixa i bigotis. Si vols, pots canviar el color de les parts de les caixes que queden visibles.

 

L'Excel permet enregistrar un gràfic definit per l'usuari com a gràfic personalitzat, de manera que si es vol tornar a fer servir, es pot triar del menú dels diferents tipus. A continuació, personalitzàs el diagrama de caixa que heu dissenyat.

 

Seleccioneu la finestra del gràfic. Prem el botó dret del ratolí i accedeix a Tipo de gráfico , entreu a la fitxa Tipos personalizados . Activa l'opció Definido por el suario i prem Agregar . Escriu un nom que descrigui el tipus de gràfic per a un ús posterior i prem Aceptar dos cops.

 

A partir d'aquest moment, sempre que es vulguin utilitzar els digrames de caixa s'hi podrà accedir triant-los dels tipus personalitzats. Cal remarcar que és imprencindible partir de les dades com estan calculades en el rang F3:H7 d'aquest full, és a dir, sempre s'han de calcular prèviament els quartils i després les diferències entre ells com els heu calculat aquí. El nombre de variables és arbitrari.

Seguidament, utilitzaràs els diagrames de caixa que has personalitzat, amb les altures dels nois.

 

Copia els rètols del rang A1:A7 en el rang A11:A17 i canvia "noies " per "nois " .

Repeteix tot el procés d'abans per al càlcul dels quartils i les diferències tenint present que els rangs de les altures dels nois són O2:O60, R2:O60 i U2:U60 del full Noies i nois , respectivament. Les diferències han d'haver quedat calculades en el rang F13:H17 i els rètols dels anys han de ser en el rang B10:D10.

Selecciona el rang F13:H17 i accedeix a Insertar | Gráfico| Tipos personalizados . Prem l'opció Definidos por el usuario i tria el tipus de gràfic que has enregistrat abans. Prem Siguiente . A la fitxa Rango de datos cal que estigui activada l'opció Filas . Accedeix a la fitxa Serie i entreu a Rótulos del eje de categorias =Resum!$B$10:$D$10, com abans.

 


Estadística