4.3. Eines i mecanismes d’anàlisi
4.3.5. Utilitzar descriptors estadístics típics
4.3.5.1. Introducció
En aquest subapartat treballarem amb l’arxiu: Formules.
A l’hora de treballar amb l’anàlisi de dades, hi ha una sèrie de fórmules i descriptors estadístics bàsics que s’utilitzen pràcticament cada vegada que es busquen històries entre un conjunt d’informació emmagatzemada en un full de càlcul.
L’estructura de les fórmules d’Excel és força bàsica:
a) Sempre es comença amb un signe =, que l’Excel interpreta com que a continuació hi haurà una fórmula.
b) En segon lloc, escriurem el nom de la fórmula. Si no és que estem fent un càlcul personalitzat, les fórmules ja estan registrades en el programa i, per tant, de seguida que comencem a escriure-la el programa ens farà suggeriments.
c) Després del nom de la fórmula s’obre parèntesi per a:
- Indicar el rang de dades sobre el qual es vol aplicar el càlcul. És el cas de les fórmules més senzilles, en les quals es podrà indicar un rang concret o unes columnes senceres concretes. Per exemple, de la casella A2 a l’A34, i ho escriuríem (A2:A34), i de la columna D a la F, escrit així (D:F).
- Complir l’estructura requerida per l’Excel, en el cas de les fórmules amb una sintaxi més complexa, perquè la fórmula es resolgui amb èxit.
Les fórmules es poden arrossegar i aplicar de manera senzilla en totes les files d’una columna. Igual que hem vist en apartats anteriors, ens haurem de posicionar a l’extrem dret inferior de la cel·la, i fer doble clic o arrossegar fins l’última casella a la qual vulguem arrossegar la cel·la, i els valors de les caselles seleccionades aniran canviant automàticament. Si hi hagués alguna casella fixa que volguéssim mantenir, podríem fer-ho amb el símbol $.
Exemple
Per exemple, si volem fixar la casella A24, si escrivim $A24 fixarem la columna, però no la fila; si escrivim A$24 fixarem la fila, però no la columna; i si escrivim $A$24, la fixarem del tot.
Per a poder seguir amb més detall les fórmules presentades a continuació, les trobareu posades en pràctica en l’Excel adjunt d’aquest apartat. Aquest arxiu fa referència al nombre de matriculats en el curs de 4t. d’ESO a tots els instituts públics de Catalunya per a l’any 2012 i 2013. Trobareu dos fulls, un per a les fórmules bàsiques i un segon per a les avançades.
4.3.5.2. Fórmules bàsiques
1) Sumar
Permet sumar un rang de dades numèriques. Per a aplicar-la cal escriure =SUM(), o =SUMA(), depenent de l’idioma del programa, i seleccionar dins del parèntesi el rang de dades que es volen sumar.
2) Comptar
Permet comptar el nombre de valors numèrics que hi ha en un rang de dades amb la fórmula =CONTAR(), o =COUNT(), segons l’idioma.
En canvi, si el que ens interessa és comptar valors numèrics i alfanumèrics, utilitzarem la fórmula =CONTAR.A(), o =COUNTA.
3) Mitjana
Calcula la mitjana d’un rang determinat de dades. La fórmula és =AVERAGE(), o =PROMEDIO(), segons l’idioma preestablert del programa.
4) Mediana
És un càlcul que ordena de major a menor tot el rang de dades seleccionat, i retorna aquella dada que hi ha exactament en el punt mig de tot el rang. Per a aplicar-la s’utilitza =MEDIAN, o =MEDIANA.
5) Moda
La moda és una mètrica que representa el valor més repetit entre un rang determinat de dades. La fórmula per a calcular-la és =MODE(), si l’Excel és en anglès, o =MODA() i =MODA.UNO(), segons la versió en castellà.
6) Valor màxim
Per a trobar el valor més alt d’un rang de dades determinat, la fórmula és =MAX, tant en la versió en castellà com en anglès.
7) Valor mínim
Per a trobar el valor més alt d’un rang de dades determinat, la fórmula és =MIN, tant en la versió en castellà com en anglès.
8) Percentatge sobre el total
Els percentatges són valuosos perquè aporten informació valuosa sobre el context que envolta una dada concreta. Si volem calcular el percentatge d’un valor respecte al total de la variable, utilitzarem la regla de tres.
Exemple
Per exemple, tornant al cas dels instituts públics, si volem saber quin percentatge representen el nombre d’estudiants de 4t. d’ESO del municipi d’Alcanar, 74, respecte al total matriculats a Catalunya l’any 2012, un total de 17.635, el càlcul que haurem de fer serà: total alumnes d’Alcanar/total alumnes de Catalunya *100.
Cal tenir en compte que si ja hem multiplicat per cent, no caldrà especificar que els valors són percentatge en el format de número. És important recordar-ho, perquè si canviem el format de número a percentatge, automàticament els valors es multiplicaran per cent una altra vegada.
4.3.5.3. Fórmules avançades
1) Comptar els valors en blanc
Si volem comptar el nombre de cel·les en blanc que hi ha en un rang determinat, la fórmula serà =CONTAR.BLANCO(), per a la versió en castellà, i =COUNTBLANK(), per a la versió en anglès.
2) Comptar condicional
La següent fórmula serveix per a comptar només aquells valors del rang que compleixin una condició: =CONTAR.SI(), o =COUNTIF().
Exemple
Per exemple, si volem comptar tots aquells valors d’una columna que són més grans de 15, la fórmula correcta seria: =CONTAR.SI(B:B;“>15”).
3) Sumar condicional
Funciona de la mateixa manera que l’operació anterior, però en aquest cas, suma. Per tant, només suma aquells valors del rang que compleixin una condició: =SUMAR.SI(), o =SUMIF().
Exemple
Per exemple, si volem sumar tots aquells valors d’una columna que són més grans de 15, la fórmula correcta seria: =SUMAR.SI(B:B;“>15”).
4) Veure si un registre compleix dues o més condicions
Aquesta operació permet veure si una fila compleix condicions en columnes diferents. La fórmula per a aplicar és =Y(), en castellà, i =AND(), en anglès.
Exemple
Per exemple, tornant al cas dels instituts, imaginem-nos que volem saber quins van tenir més de 50 matriculats a 4t. d’ESO l’any 2012 de la totalitat d’instituts, més d’una cinquantena l’any 2013 i que, a més a més, el creixement d’un any a l’altre va ser positiu. Per a veure l’aplicació de la fórmula podeu obrir l’arxiu adjunt i fer clic en el segon full anomenat «fórmules avançades». En la columna G hi ha la següent fórmula: =Y(E2>50 “;F2>50;F2>0).
D’aquesta manera, si en la fila 2, la que correspon a l’únic institut d’Alcanar, els alumnes de 4t. d’ESO de l’any 2014 són més de 50 (columna E), l’any 2012 també són més d’una cinquantena (columna F), i el creixement (columna G) d’un any respecte a l’altre ha estat positiu, el resultat definitiu serà «VERDADERO». En canvi, el resultat serà «FALSO» tant si no en compleix cap, o només en compleix alguna.
Amb la creu negra que apareix a l’extrem inferior dret de la cel·la, arrossegarem la fórmula a la resta de la columna.
5) Veure si un registre compleix alguna condició
Aquesta fórmula té un funcionament pràcticament igual a l’anterior, amb l’única diferència que en aquesta versió indicarà «VERDADERO» quan compleixi alguna de les condicions especificades, i només indicarà «FALSO» si no en compleix cap.
Per a aplicar-la haurem d’utilitzar =O(), en la versió castellana, i =OR() en l’anglesa. En l’exemple anterior quedaria formulada de la següent manera en la columna H: =Y(E2>50 “;F2>50;F2>0).
6) Condicional
La funció =SI() o IF() permet veure si una cel·la compleix una condició concreta en una columna, i en el cas que sí o que no, poder customitzar la resposta.
Exemple
Tornem a l’exemple dels alumnes d’ESO. Per exemple si l’únic institut d’Alcanar va tenir més de 30 alumnes de batxillerat l’any 2012, voldrem que la cel·la indiqui que és «dins de la ràtio», i si no els ha tingut, que aparegui «fora de la ràtio».
La fórmula correcta serà: =SI(D2>30; “dins la ràtio”; “fora la ràtio”).
7) Traslladar valors per una columna en comú
Aquesta fórmula serveix per a trobar valors iguals que hi ha en fulls d’Excel separats. Per a entendre-ho millor, caldrà obrir l’Excel adjunt del nombre d’inscrits en els instituts de Catalunya i veure l’operació completa en la columna E del full de «fórmules avançades». Imaginem-nos que volem unir el full de «matriculats 2012» amb el de «matriculats 2013». Per a copiar les xifres d’una manera senzilla, ràpida i sense caure en errors, utilitzarem la fórmula =BUSCARV(), o =VLOOKUP().
8) Variació percentual
La variació percentual és un càlcul que ens permetrà saber quina ha estat la variació al llarg del temps d’una variable determinada.
Es calcula de la següent manera:
(Valor Final – Valor Inicial)/Valor Inicial *100
Exemple
Per exemple, si el nombre d’alumnes matriculats a 4t. d’ESO en els instituts públics de Catalunya l’any 2012 era de 17.635, i el 2014 era de 17.436, la variació percentual haurà estat de –1,2%. Per a veure amb més detall el càlcul, aneu directament a la columna F del segon full, anomenat «fórmules avançades».