4.2. La importància d’ordenar bé un full de càlcul
4.2.3. Un exemple pràctic
Per a aquest exemple, podeu treballar amb l’arxiu: Adjudicacions Brut
Per a entendre millor com s’ha d’estructurar i de netejar un conjunt de dades, val més que seguim un exemple pràctic. Imaginem que volem dur a terme una anàlisi de la licitació de contractes de tots els departaments de la Generalitat de Catalunya durant la segona quinzena del mes de desembre de 2016. L’objectiu és esbrinar la següent informació:
- Quina és l’empresa que s’endú un import més gran per contractes de cada departament?
- Quina és la mitjana dels imports de les licitacions de subministrament per departament?
- El nombre de contractes que estan adjudicats a més d’una empresa.
Sabem amb antelació que el registre de contractació pública conté tota la informació respecte a la contractació major de la Generalitat de Catalunya estructurada en fulls de càlculs de forma quinzenal. Es pot accedir a l’enllaç pel portal de transparència de l’administració catalana. Per dur a terme aquest exercici, descarregarem l’arxiu «.xls» corresponent a la primera i segona quinzena de desembre de 2016, el període de temps en el qual volem centrar el focus periodístic.
Tot just obrir els Excel podem veure la informació corresponent a tots els departaments de la Generalitat estructurada amb els següents camps:
- Organització de la contractació: departament o organisme que convoca el concurs públic.
- Data d’adjudicació: dia, mes i any de l’adjudicació. Com que el full de càlcul descarregat correspon a la segona quinzena de desembre de 2016, només constaran adjudicacions fetes durant aquest període de temps.
- Descripció de l’objecte: necessitat que pretén cobrir el contracte, és a dir, motiu pel qual es convoca.
- Tipus de contracte: tipologia segons l’objecte del contracte, que pot ser de serveis, de subministraments o d’obres.
- Procediment de l’adjudicació: procés que s’ha seguit per tal de licitar i adjudicar el contracte: obert, negociat, etc.
- Valor estimat del contracte: import en el qual està valorat el concurs en el moment de la licitació.
- Import d’adjudicació: import final de l’adjudicació del contracte. Unitats en euros.
- Empresa adjudicatària: nom de l’empresa o les empreses que han guanyat el concurs. Per a cada concurs, hi ha tantes files com adjudicataris finals que han guanyat el concurs.
- Durada: període de temps que durarà el contracte.
1) Eliminar files sobrants
Abans de fer qualsevol pas, primer caldrà eliminar la imatge incrustada i les files de més que encapçalen el full de càlcul. Per a fer-ho, seleccionarem les files amb el cursor, farem clic amb el botó dret del ratolí i desplegarem el menú per tal d’eliminar-les. Farem el mateix per a les files inferiors, que indiquen el total de dades i una especificació metodològica sobre les dades.
2) Descombinar les cel·les
Si fem un repàs molt ràpid al full de càlcul, veiem que pràcticament en totes les columnes hi ha cel·les combinades i, per tant, ens serà impossible aplicar els filtres per a ordenar de major a menor.
Per a fer-ho, primer haurem de descombinar les cel·les i decidir de quina manera estructurem la informació.
Per a descombinar-les seleccionarem tot el conjunt de dades d’Excel fent clic en el quadrant superior esquerre, tal com indica la següent imatge, o anant columna per columna.
Un cop seleccionat tot el conjunt de la informació, anirem a la pestanya «format», i dins d’aquesta, a «cel·les». En el menú emergent que apareixerà, hem de fer clic en la pestanya «alineació» i desmarcar la casella inferior de «combinar cel·les» tal com es mostra a continuació.
Un cop efectuada aquesta operació, totes les cel·les quedaran automàticament dividides de la següent manera:
El problema és que després d’aquest canvi, les cel·les descombinades han quedat buides de contingut. En dividir-les ha quedat la informació que pertoca només a la cel·la superior. Per tant, el que haurem de fer a continuació és copiar el contingut a la resta de cel·les. Tot i que hi ha macros, una opció avançada del programa, pràctiques per a Microsoft Excel, que permeten automatitzar aquest procés, es tracta d’un procediment més complex que no és vàlid per a tots els fulls de càlcul. En canvi, el sistema manual sempre serà una alternativa viable.
3) Entendre l’estructura i omplir el contingut amb cura
Així, doncs, per a copiar el contingut de forma manual ens situarem a la part inferior esquerra de la casella que conté la informació que volem copiar. Quan aparegui una creu negra, arrosseguem la creu fins l’última casella en la qual volem que es copiï la dada, i automàticament la informació quedarà copiada. Si en comptes d’arrossegar fem doble clic, aleshores el contingut es copiarà a totes les cel·les buides consecutives que hi hagi a continuació de la mateixa columna.
Per a navegar de manera més directa i senzilla per la taula sense haver d’utilitzar la barra de la dreta, es pot fer servir la tecla «Control» i les fletxes. En cas de Macintosh, la combinació de tecles serà «Comando» i, de nou, les fletxes.
Abans de començar a fer la feina de copiar i enganxar, és important donar un cop d’ull a tot el conjunt de dades per a entendre com està estructurat. Cal omplir la informació columna per columna sense caure en errors.
A simple vista localitzem que la primera columna, la de «departament», serveix al mateix temps com a «encapçalament» i com a «registre». És a dir, que hi ha licitacions que les fa el departament, d’altres que les fan altres organismes que conformen el departament, i d’altres casos en què el departament no fa cap licitació i simplement l’anoten com a encapçalament. És el cas, per exemple, de la fila 60.
Per a dur a terme el nostre propòsit periodístic ens interessa que hi hagi tant la columna del departament, com la de l’organisme concret que fa la licitació. A més a més, per a poder analitzar-ho bé no pot haver-hi registres buits que només serveixin com a encapçalaments.
Per tant, crearem una columna nova que es dirà «Departament» i reanomenarem la que teníem en segon lloc com a «Organisme Licitador». Tot seguit, copiarem en la primera columna el nom del departament que fa la licitació, i en la segona, l’organisme concret que fa la licitació: si és el departament mateix, copiarem el nom del departament i, si és un organisme més petit que pertany al departament, aleshores copiarem el nom de l’organisme.
Els departaments de Governació, Administracions Públiques i Habitatge, el de Justícia, i el de Territori i Sostenibilitat no han fet cap licitació i, per tant, només serveixen com a encapçalaments. En aquests tres casos eliminarem la fila buida i copiarem el nom del departament a les files inferiors, a les quals hi ha les licitacions dels òrgans que sí que han licitat i depenen d’aquests departaments.
Abans de començar l’anàlisi, cal afegir una columna que identifiqui cada contracte amb un registre únic, per exemple, un numèric. Necessitem aquesta columna perquè cada fila no és un contracte, sinó una adjudicació. De manera que si un contracte té més d’un adjudicatari, el contingut estarà acumulat en files diverses segons el nombre d’empreses adjudicatàries. Per a solucionar aquest aspecte, crearem una columna nova amb un codi únic per a cada contracte. El valor adjudicat a cada contracte, doncs, es repetirà en els casos en què un contracte hagi estat adjudicat a més d’una empresa.
Una vegada hem creat aquesta columna, acabarem de copiar la informació per a la resta de camps. Igual que hem fet amb les dues primeres columnes, arrossegarem la informació de la primera cel·la a la resta de caselles duplicades. Compte a arrossegar i no copiar, ja que hi ha valors com les dates o els números que poden augmentar en lloc de copiar-se exactament. En aquests casos haurem de copiar la informació i no arrossegar-la.
4) Homogeneïtzar els camps
Una vegada modificada l’estructura podrem procedir a fer la neteja habitual amb les fórmules que hem vist en l’apartat anterior, i que serveixen per a homogeneïtzar tots els camps. Així, doncs, aplicarem les fórmules corresponents per a treure els espais excedents i per a col·locar totes les lletres en majúscula.
Per a fer-ho, crearem una columna nova al costat de la que volem netejar fent un clic amb el botó de la dreta sobre la columna.
Un cop creada la columna, aplicarem la fórmula corresponent, tant si és =ESPACIOS, o =MAYUSC, i l’arrossegarem al llarg de tota la columna.
Per a poder treballar millor a posteriori amb el full de càlcul, és convenient eliminar les fórmules un cop aplicades i, per tant, treballar només amb els valors. Per a fer-ho, copiarem tota la columna sobre la qual estem aplicant la fórmula, en aquest cas la columna C.
Amb la informació copiada, ens situarem sobre la columna que conté la informació original, la B, i anirem a la pestanya «edició» per a fer un clic sobre l’opció «enganxament especial».
Per defecte, s’obrirà un menú que tindrà marcada l’opció d’enganxar-ho «tot». Si no volem copiar també les fórmules, haurem de canviar l’opció determinada per defecte per «valors», tal com es mostra en la següent captura de pantalla.
Una vegada copiada la informació de la forma correcta, podrem procedir a eliminar la columna de la fórmula. Aquest procés el repetirem amb totes les columnes, fins que tot el full de càlcul estigui a punt per a analitzar.
Una altra manera més ràpida d’aplicar aquesta fórmula, és aplicant les fórmules en un full nou, en lloc d’una columna nova. D’aquesta manera aplicarem la fórmula desitjada (=MAYUSC o =ESPACIOS) a totes les columnes alhora. Tot i que a la llarga és la forma més efectiva, quan estem començant, anar pas per pas va bé per tal d’evitar confusions.
5) Comprovar que la feina de neteja s’ha fet correctament
Després d’haver fet la neteja, i abans de passar a l’anàlisi, caldrà comprovar que no s’han comès errors mentre es picava durant la neteja. Alguns errors comuns són, per exemple, haver passat per alt copiar i enganxar alguna cel·la. Per a detectar de manera ràpida aquest error, el més senzill és navegar pel full de càlcul amb la tecla «CONTROL + fletxes», o «CMD + fletxes» en cas de treballar en Mac.
D’altra banda, també caldrà comprovar que el format de les columnes és correcte. Sobretot a l’hora de fer l’enganxament especial d’únicament els «valors», ja que en fer-ho perdem el format atribuït a cada columna. Per a modificar el format de les dades caldrà seleccionar la columna, i en la pestanya «format» fer clic en l’opció de «cel·les», seleccionant el format adient per a cadascuna de les columnes. Per exemple, en el cas de la columna D, això és traduirà en el format de data format per dia, mes i any.
Ara sí, una vegada fetes les comprovacions i haver comprovat que no hi ha errors de neteja, podrem procedir a analitzar les dades.
Per a anar bé, el resultat final net hauria de quedar com l’arxiu adjunt següent: Adjudicacions_Net.