Estudi d'un fitxer de dades alienes

Sovint, en els estudis estadístics, és necessari treballar amb conjunts de dades a l'abast, que faciliten les institucions o altres organismes i, cada vegada més, accessibles a través de la xarxa Internet. No sempre aquestes dades estan en el format més convenient i convé tenir criteris per adaptar-les.

L'objectiu d'aquesta pràctica és constatar la facilitat d'importar un fitxer que s'obté d'una base de dades pública a l'abast en un format diferent de l'Excel i fer un treball descriptiu ràpid de les dades d'aquest fitxer.

En concret es treballa:

• La importació d'un fitxer dBase.
• La transformació de variables i codificació de dades.
• Els diagrames de sectors, amb la prèvia elaboració d'una taula.
• Els diagrames de barres múltiples (aplicats en aquest cas a variables alfanumèriques).

En la primera part de la pràctica es comenta com es poden aprofitar les dades del fitxer i transformar-les adequadament a l'entorn Excel.

• Activeu Archivo | Abrir. Obriu el desplegable Tipo de archivo, seleccioneu dBase(.dbf) i navegueu fins que trobeu el fitxer ja comentat per a aquesta pràctica, és a dir, el BAT2.DBF, que heu recuperat amb tota la resta de fitxers del curs i heu copiat a la carpeta de treball.
• Quan cliqueu a Abrir, s'obre immediatament el fitxer i es mostra com un full de càlcul de l'Excel. Tanmateix, com que les variables del fitxer inicial estaven definides amb un sol caràcter o dos, les columnes del full Excel són excessivament petites i no deixen veure els rètols de la primera fila. Amplieu-les manualment fins que es vegi correctament. Com que, a diferència del dBase, els rètols en Excel admeten accents, escriviu correctament OPCIÓ com a rètol de la columna D.
• De seguida veureu que a la columna A hi consta el nom de les alumnes i els alumnes. Això no és pas massa correcte, perquè una vegada recollides les dades, emmagatzemades correctament en suport informàtic i fet el tractament inicial amb el programari escaient (que no necessàriament ha de ser l'Excel) per fer estadística, el nom de cada individu no l'hem de considerar. Seleccioneu, doncs, la columna A (feu clic justament a la A) i feu Edición |Eliminar. Veureu que desapareix la columna i que les que abans eren les columnes B, C, D, E i F ara han passat a ser A, B, C, D i E.
• Ara podeu triar l'opció Archivo | Guardar como i on diu Guardar como tipo trieu Libro de Microsoft Excel. Doneu-li el nom BAT2.XLS i guardeu-lo. A partir d'ara, si comenceu una altra sessió de treball, ja el podreu obrir d'aquesta manera, com un llibre de l'Excel.

Ara convé que us adoneu que les columnes DATANAIX i CONVOC, tot i que en realitat són numèriques, s'han importat (perquè així eren al fitxer inicial) amb caràcter de dades alfanumèriques. Convé canviar aquesta característica!

• Com es pot saber a ull? Perquè en l'Excel les dades alfanumèrqiues s'ajusten a l'esquerra i en canvi les dades numèriques a la dreta.
• Com es pot canviar el caràcter d'una casella que està definida com a alfanumèrica i, en cas que tingui realment dades numèriques, fer que esdevingui numèrica? Si penseu que amb Formato | Celdas, efectivament aquesta seria la via a seguir habitualment. Però en aquest cas, el fet de treballar amb un fitxer importat dóna unes característiques especials a les cel·les que fan que no sigui així. Fa falta un procediment més potent que s'explica tot seguit i que permet canviar la tipologia de les cel·les.
  • A la cel·la F2 escriviu el número 1. Vegeu que queda a la dreta de la cel·la; té, per tant, caràcter de número. Copieu-lo per arrossegament de manera que hi hagi un 1 en totes les cel·les del rang F2:F218. El rang queda seleccionat.
  • Feu Edición|Copiar (o bé Control + C). Feu clic a la cel·la D2. Feu Edición|Pegado especial amb les opcions Pegar: Valores i Operación: Multiplicar. Les dates de naixement s'han convertit en dades numèriques.
  • Feu el mateix amb la columna que indica el nombre de convocatòries.
  • Una alternativa a l'acció anterior que converteix a numèrica una columna alfanumèrica que ha resultat de la conversió d'un fitxer dbf és la següent: seleccionar una columna en blanc i enganxar-la (amb Pegado Especial) sobre la columna que volem convertir havent activat les opcions Valores i Suma.

Per acabar aquesta revisió de procediments que poden ser útils per transformar un arxiu de dades alinenes per a un millor aprofitament, s'explica l'ús d'una fórmula condicional per recodificar unes dades. La columna CONVOC expressa la convocatòria de segon de batxillerat que cursa cada alumne o alumna; imagineu que volem tenir una altra columna que indiqui, simplement, si aquella persona repeteix o no.

• Poseu un rètol a la columna F. Per exemple, escriviu a la cel·la F1: Repeteix?
• Seleccioneu la cel·la F2.
• Escriviu-hi la fórmula condicional següent: =SI(E2=1; "no"; "sí"),
  on podeu veure que s'han d'escriure com a arguments de la fórmula condicional SI separats per punts i comes (;):
  • La condició que volem aplicar.
  • El valor que volem que aparegui si la condició és certa.
  • El valor si no ho és.
• Feu Retorn i ja teniu el primer codi calculat.
• Copieu la fórmula escrita perquè sigui vàlida en tot el rang F2:F218 i tindreu acabada aquesta part de la pràctica.
• Guardeu el llibre BAT2.XLS amb què treballeu.

No es pot separar, doncs, la primera passada pels gràfics estadístics (que és el que es pretén amb aquesta pràctica i la següent) de l'elaboració de taules i el títol d'aquest apartat podria ser, de manera més estricta, «Resums estadístics: taules i gràfics». Ambdós temes es revisaran en diverses ocasions al llarg d'aquest curs D128.

En aquest apartat es faran alguns diagrames de sectors (també designats com a diagrames circulars o diagrames de pastís. Aquest tipus de diagrames són útils per visualitzar de quina manera les dades d'una variable (habitualment alfanumèrica) es distribueixen entre els elements d'una població. L'àrea corresponent al gràfic es reparteix proporcionalment a les freqüències observades de les diverses classes o categories.

Aquests diagrames són àmpliament presents als mitjans de comunicació, on també es poden veure gràfics d'aquest tipus amb cercles o semicercles i amb dissenys molt diversos. Els que fa l'Excel tenen sempre, per defecte, forma de cercle, tot i que en la propera pràctica es veurà la manera de fer un diagrama semicircular.

Tot seguit, fareu diversos diagrames de sectors amb les dades del llibre de l'Excel BAT2.XLS tal com l'heu elaborat a la primera part de la pràctica. En primer lloc, veureu com es reparteixen les opcions triades per l'alumnat.

• Ja hem dit que en primer lloc heu de construir la taula de freqüències. En una pràctica anterior ja heu exercitat la doble possibilitat de fer-ho mitjançant l'aplicació d'una fórmula del tipus CONTAR.SI o bé amb les taules dinàmiques. Tot i que aquest darrer és el procediment més àgil, ara ho farem encara una vegada més a mà perquè això ajuda a entendre aspectes del funcionament de l'Excel que convé tenir molt ben assolits.
• Cal saber a priori la llista de tots els valors que podem observar en la variable estudiada, en aquest cas OPCIÓ del fitxer BAT2.XLS, que són {A, B, C, D}.
• Escriviu a les cel·les H1 i I1 els títols de la taula de freqüències que farem; poseu-hi, respectivament, opcions i freqüències, per exemple.
• Escriviu al rang H2:H5 les lletres A, B, C, D.
• Escriviu a I2 la fórmula =CONTAR.SI($C$2:$C$218; H2) que fa el recompte del nombre de cel·les de la columna C que tenen un valor igual a aquell que apareix a la cel·la H2. Observeu que el rang s'ha escrit amb referència absoluta; en canvi, la cel·la amb referència relativa, perquè tot seguit copiarem la fórmula. Com sempre, us recordem que, alternativament, enlloc de teclejar la fórmula, podeu fer-ho amb Insertar|Función i buscar la funció CONTAR.SI.
• Copieu aquesta fórmula a les cel·les I3, I4 i I5
• Tot seguit, ara que ja està elaborada la taula de freqüències, ja podeu per fer el gràfic. Com a la pràctica anterior, suggerim la línia de treball consistent a fer el gràfic i després millorar-lo. Seleccioneu el rang H1:I5.
• Cliqueu a la icona de gràfics de la barra d'eines (o bé, si no la teniu la vista, a Insertar|Gráfico).
• Trieu com a tipus de gràfic Circular i tot seguit feu Finalizar.
• Ja teniu una primera versió del gràfic de sectors. Ara podeu investigar per fer-hi millores
  • Feu clic amb el botó dret al títol i esborreu-lo.
  • Feu clic amb el botó dret a la zona blanca del requadre del gràfic i trieu Opciones de gráfico. Us suggerim, de moment, que a la fitxa Título trieu un títol adequat per al gràfic i a la fitxa Rótulos de datos activeu Mostrar porcentaje.
  • La resta ja va a compte vostre. Proveu també els resultats amb els diversos tipus de gràfic circular, en particular si teniu afició pels gràfics 3D.

• Obriu Datos | ...tablas dinámicas... Accepteu en la primera pantalla i indiqueu el rang $A$1:$F$218 per respondre la pregunta: ¿Dónde están los datos que desea usar? Recordeu que ho podeu fer amb l'ajut del botó i adoneu-vos que per construir una taula dinàmica, el rang sempre ha de començar amb la fila de rètols o noms de les variables; altrament no podreu fer res amb aquest procediment. Si no en recordeu el funcionament, podeu consultar-ho a la pràctica anterior.
• Passeu al disseny de la taula. Arrossegueu a FILA el rètol de Repeteix? i a DATOS el mateix rètol de Repeteix? Observeu, però, que a la zona central de la taula, a DATOS, ha aparegut Contar de repeteix? Si no és així, feu-hi doble clic i corregiu-ho.
• Ara cal indicar on voleu la taula. Dieu que al mateix full i indiqueu, per exemple, la cel·la H7, que és la casella superior esquerra de la taula.
• Quan executeu l'ordre i tingueu la taula, la convertireu en una taula normal. Tanqueu, si escau, el quadre de diàleg auxiliar per al treball amb la taula dinàmica; seleccioneu tot el rang de la taula; feu Control + C; tot seguit, amb la taula encara seleccionada, feu Edición | Pegado Especial | Valores i accepteu.
• Ara ja podeu fer el gràfic circular. Noteu que, si heu seguit les indicacions anteriors, el rang que heu de seleccionar per fer el gràfic és H9:I10, sense la fila de totals.
• Tot seguit, investigueu millores i podeu obtenir un gràfic semblant a aquest:
  

Si no continueu ara mateix amb l'apartat següent de la pràctica, guardeu el llibre BAT2.XLS tal com el teniu ara, perquè el necessitareu.

Aquests diagrames de barres ens permeten també, doncs, presentar les dades d'una variable del tipus que estem analitzant en aquesta pràctica. Per fer el diagrama de barres que mostra el repartiment de les opcions del batxillerat, cal basar-se, com abans, en la taula de freqüències que tenim al rang H1:I5 del full de càlcul del llibre BAT2.XLS.

• Per fer el gràfic de barres, seleccioneu el rang H1:I5.
• Cliqueu a la icona de gràfics i trieu com a tipus de gràfic el que segurament apareix per defecte: Columnas (amb el primer subtipus de gràfic) i tot seguit feu Finalizar.
• Ja teniu una primera versió del gràfic que, com sempre, podeu millorar. En aquest cas, us suggerim que:
  • Tragueu la llegenda i poseu un títol adequat.
  • Pinteu cada barra d'un color diferent.
  • Feu clic amb el botó dret a la zona blanca del gràfic perquè aparegui un menú d'opcions. Ara us suggerim Opciones de gráfico | Rótulos de datos | Mostrar valores.
  • Investigueu altres possibilitats. Seria massa enfarfegador descriure totes les opcions i subopcions i ens allunyaria de l'objectiu del curs.

Tot seguit, podeu constatar un avantatge dels diagrames de barres sobre els diagrames de sectors: el seu paper d'instrument inicial per a l'estudi de les relacions entre variables, en concret quan es vol estudiar el comportament d'una certa variable respecte d'una altra i veure si hi ha homogeneïtat o no dels valors que pren una variable entre les categories (o grups de la població) que defineix una altra variable dita de classificació. Per a aquesta anàlisi, els anomenats diagrames de barres múltiples o combinats són una bona eina d'anàlisi intuïtiva inicial.

Hi ha quatre presentacions bàsiques d'aquests diagrames, que es mostren seguidament amb la finalitat d'estudiar si, en cada opció, és uniforme el repartiment de nois i noies.

Prèviament a l'elaboració d'algun d'aquests diagrames amb l'Excel, hem de fer la taula de freqüències. Els gràfics que ara es comenten corresponen a la visualització de les taules de doble entrada i, per fer una taula creuada amb els valors de les variables SEXE i OPCIÓ del full de dades del llibre BAT2.XLS, podeu actuar de la manera següent:

• Activeu Datos|...tablas dinámicas que ja hem comentat a bastament que s'explicarà amb més detall en mòduls posteriors.
• Després de fer Siguiente, indiqueu que el rang de dades que voleu estudiar és A1:F218.
• Tot seguit heu de fer les dues accions que es comenten, amb unes lleus diferències de funcionament entre l'Excel 97 i l'Excel 2000.
  • Heu de fer el Diseño de la taula . Porteu el rètol OPCIÓ a la zona del disseny on diu FILA. Porteu el rètol SEXE a la zona on diu COLUMNA. Porteu un d'aquests rètols, per exemple SEXE, a la zona on diu DATOS i vigileu que digui Contar de SEXE (com a alternativa a l'opció que de vegades apareix per defecte i que emprarem en alguna altra ocasió, a saber, Suma de SEXE. Si és el cas, hauríeu de fer doble clic i corregir-ho).
  • Escolliu l'opció de posar la taula en un full nou.

• Aneu endavant fins que es visualitzi la taula. Si treballeu amb la versió 2000, segurament us haurà quedat la taula a partir de la cel·la A3. En aquest cas, seleccioneu les dues primeres files del full, premeu el botó dret del ratolí i trieu l'opció Eliminar. D'aquesta manera, la taula començarà a la cel·la A1.
• Com ja heu fet altres vegades, primer de tot copiareu la taula com una taula normal, però ara la copiareu a part i conservareu la taula dinàmica perquè interessa tenir-la per a una acció posterior.
  • Seleccioneu tota la taula.
  • Feu Edición|Copiar (o bé Control + C, com ja sabeu).
  • Tot seguit, feu clic en una cel·la, que serà el vèrtex superior esquerre de la taula copiada. Per les explicacions que donarem d'accions posteriors, imaginarem que és la A10.
  • Ara feu Edición|Pegado especial, amb molta cura d'activar l'opció que només agafa els Valores.

• Seleccioneu la taula, però sense la fila ni la columna de totals ni l'encapçalament. Si heu copiat la taula on hem dit, heu de seleccionar el rang A11:C15.

• I llavors...

• Adoneu-vos que al gràfic 4 els percentatges (calculats automàticament) estan referits al total de cada categoria, assenyalada per l'opció triada pels alumnes.

• Això mateix succeeix en el gràfic 3 que podeu veure a la pràctica, que no està incorporat com a tal en el programa Excel. Per obtenir-lo, en primer lloc cal fer una taula com l'anterior però amb percentatges per files (és a dir, "dintre de cada opció, com es reparteixen els alumnes pel que fa al sexe?"). Si treballeu amb la versió 2000:
  • Accediu a la taula dinàmica que teniu al rang A1:D7.
  • Feu doble clic a la cel·la que diu Contar de SEXE. Al quadre de diàleg que s'obre feu clic a Opciones.
  • Escolliu Mostrar datos como: tant per cent (%) de la fila.
  • Amb la taula de doble entrada que ha resultat, taula de percentatges per files, procediu com abans i copieu-la (Sólo valores) en un altre lloc del full.

• Per la versió 97:
  • Accediu a la taula dinàmica que teniu al rang A1:D7.
  • Premeu el botó dret del ratolí i trieu Asistente...
  • Feu doble clic al rètol que diu Contar de SEXE. Al quadre de diàleg que s'obre feu clic a Opciones.
  • Escolliu Mostrar datos como: tant per cent (%) de la fila.
  • Amb la taula de doble entrada que ha resultat, taula de percentatges per files, procediu com abans i copieu-la (Sólo valores) en un altre lloc del full.

• El primer subtipus dels diagrames de barres (tipus de gràfic: Columnas) aplicat a aquesta taula és el que us dóna el gràfic 3 mostrat a la pràctica.

Pot ser interessant que guardeu el llibre BAT2.XLS amb totes les taules que teniu, perquè en un dels exercicis us demanem una feina semblant a la que heu vist en aquesta pràctica.

En aquest exemple d'ampliació, s'explica la sintaxi adequada per escriure una fórmula condicional que permeti codificar una variable amb tres valors diferents.

Ho aplicarem a la variable DATANAIX del full amb què treballem actualment i assignarem el valor normal als alumnes que tenen data de naixement l'any 1985; el valor jove a aquells i aquelles que són de l'any 1984 o 1983, i, finalment, el valor gran a la resta.

Ho podeu fer de dues maneres segon l'ordre d'aplicar les successives condicions.

• Amb la fórmula =SI ( D2<83;"gran";   SI(D2<85;"jove";"normal")   ) escrita a la cel·la corresponent de la fila 2 que copiareu perquè sigui vàlida per a totes les files de la 2 a la 218. Hem acolorit uns parèntesis i hem posat uns espais aclaridors perquè us adoneu que amb això l'Excel ha actuat així:
  • Ha assignat el valor gran a aquelles dates de naixement inferiors a 83, és a dir 82, 81, 80, etc.
  • Per a les dates que no complien aquesta condició, n'ha comprovat una altra i ha mirat si eren inferiors a 85. Això només ho compleixen els valors 84 i 83 (perquè els anteriors ja complien la primera condició) i a aquests els ha assignat jove.
  • Finalment, a aquelles dates de naixement que no complien cap de les dues condicions, és a dir, només la data 85 els ha assignat normal.
  • Noteu que si hi hagués hagut algun alumne o alguna alumna avançat/da, és a dir, de l'any 86, també se li hagués assignat normal. Podeu estudiar com caldria modificar la fórmula per tenir en compte aquesta eventualitat.

• Vegeu una altra opció que, de passada, fem servir per indicar la sintaxi de la connectiva lògica O. La fórmula escaient és =SI ( D52=85;"normal";   SI(O(D52=84;D52=83);"jove";"gran") ).

• Si feu aquest exemple o un altre de semblant us podeu adonar que, mentre escriviu una fórmula condicional múltiple, l'Excel, a la barra de fórmules, acoloreix els parèntesis corresponents, de manera semblant al que hem mostrat aquí.

Sovint hem de copiar una fórmula en un rang que inclou totes les files del full on hi tenim recollides les nostres dades estadístiques, i si ho volem fer per arrossegament el procediment esdevé enfarfegador.

Tot seguit s'explica com ho podem fer de manera més àgil:

• Seleccioneu la cel·la on hi tenim la fórmula que volem copiar i feu Control + C. Veureu que el marc de la cel·la seleccionada pampallugueja.
• Ara feu Control + Majúscules + Fi (o Fin o End). Veureu que s'ha seleccionat fins a l'última cel·la ocupada (o que ha estat ocupada en algun moment) o bé fins a la cel·la de la columna on teniu la fórmula que correspon a la darrera fila ocupada per dades. Això és sovint el que ens interessa!
• Si manteniu només la tecla de Majúscules premuda, podeu modificar la selecció.
• Quan ja tingueu seleccionat exactament el rang que interessi, feu Control + V. La fórmula de la cel·la seleccionada inicialment (que manté el pampallugueig) ja ha estat copiada a tot el rang.
• Feu Esc perquè la cel·la inicial ja no ens indiqui (amb el pampallugueig) que en tenim el contingut a la memòria per poder-lo copiar. Sortiu també, si escau, del rang seleccionat.

Tot seguit s'exposa una manera d'arribar al gràfic de sectors que s'ha inclòs com a il·lustració en el context de la pràctica.

• Botó dret a la zona que l'Excel indica com a àrea del gràfico. Del menú es desplega trieu Opciones de gráfico.
  • Título. Podeu escriure el títol que interessi.
  • Leyenda. Desactiveu l'opció que indica Mostrar leyenda.
  • Rótulos de datos. Podeu triar Mostrar rótulo y porcentaje.
  Algunes d'aquestes possibilitats també són accessibles fent doble clic sobre l'element del gràfic que interessi.

• Quan accepteu els canvis suggerits fins ara, molt possiblement el cercle que apareix al diagrama s'haurà fet molt més petit del que sembla raonable.
  • Podeu modificar a mà les mides de la finestra gràfica i podeu millorar aquest aspecte.
  • Podeu fer clic amb el botó dret en un rètol i llavors amb Formato de rótulos de datos | Fuente podeu modificar la mida (us suggerim 10 punts) i automàticament també es fa una mica més gros el cercle.
  • Podeu fer alguna cosa semblant per al títol.
  • Ara bé, si voleu controlar totalment la mida del diagrama dintre de la finestra, heu de fer-ho així:
    • Aneu movent amb cura el ratolí a prop de la zona on hi ha dibuixat el cercle fins que veieu que el rètol que va identificant les diverses parts del gràfic indica Área de trazado.
    • En aquest moment, feu clic al botó esquerre del ratolí. Queda seleccionat un quadrat circumscrit al cercle: aquest quadrat és l'àrea on es dibuixa el que és estrictament el gràfic circular.
    • Si acosteu el ratolí als vèrtexs d'aquest quadrat, veureu que el cursor apareix com una doble fletxa inclinada; si llavors premeu el botó esquerre del ratolí, per arrossegament podreu variar la mida del quadrat i, si ho aneu fent amb els diversos vèrtexs, podeu canviar també la posició del cercle a la finestra de dibuix.

• Per canviar els colors dels sectors:
  • Cliqueu amb el botó esquerre al cercle; queda seleccionat.
  • Cliqueu novament amb el botó esquerre sobre el sector que interessa. Ara queda seleccionat només aquest sector.
  • Cliqueu amb el botó dret al sector seleccionat. Trieu Formato de punto de datos | Tramas i això us permet canviar el color (i altres aspectes!).
  • Feu el mateix amb l'altre sector.