4.2. La importancia de ordenar bien una hoja de cálculo
4.2.3. Un ejemplo práctico
Para este ejemplo, podéis trabajar con el archivo: 
Adjudicacions Brut
Para entender mejor cómo se tiene que estructurar y limpiar un conjunto de datos, es mejor que sigamos un ejemplo práctico. Imaginemos que queremos llevar a cabo un análisis de la licitación de contratos de todos los departamentos de la Generalitat de Cataluña durante la segunda quincena del mes de diciembre del 2016. El objetivo es averiguar la siguiente información:
- ¿Cuál es la empresa que se lleva un mayor importe por contratos de cada departamento?
- ¿Cuál es la media de los importes de las licitaciones de suministro por departamento?
- El número de contratos que están adjudicados a más de una empresa.
Sabemos con antelación que el registro de contratación pública contiene toda la información respecto a la contratación mayor de la Generalitat de Cataluña, estructurada en hojas de cálculo de forma quincenal. Se puede acceder al enlace por el portal de transparencia de la Administración catalana. Para llevar a cabo este ejercicio, descargaremos el archivo XLS correspondiente a la primera y segunda quincena de diciembre del 2016, el periodo de tiempo en el que queremos centrar el foco periodístico.
En cuanto abramos los documentos Excel, podremos ver la información correspondiente a todos los departamentos de la Generalitat, estructurada con los campos siguientes:
- Organización de la contratación: departamento u organismo que convoca el concurso público.
- Fecha de adjudicación: día, mes y año de la adjudicación. Puesto que la hoja de cálculo descargada corresponde a la segunda quincena de diciembre del 2016, solo constarán adjudicaciones hechas durante este periodo de tiempo.
- Descripción del objeto: necesidad que pretende cubrir el contrato, es decir, motivo por el que se convoca.
- Tipo de contrato: tipología según el objeto del contrato, que puede ser de servicios, de suministros o de obras.
- Procedimiento de la adjudicación: proceso que se ha seguido para licitar y adjudicar el contrato: abierto, negociado, etc.
- Valor estimado del contrato: importe en el que está valorado el concurso en el momento de la licitación.
- Importe de adjudicación: importe final de la adjudicación del contrato. Unidades en euros.
- Empresa adjudicataria: nombre de la empresa o las empresas que han ganado el concurso. Para cada concurso, hay tantas filas como adjudicatarios finales que han ganado el concurso.
- Duración: periodo de tiempo que durará el contrato.
1) Eliminar filas sobrantes
Antes de dar cualquier paso, primero será necesario eliminar la imagen incrustada y las filas de más que encabezan la hoja de cálculo. Para hacerlo, seleccionaremos las filas con el cursor, haremos clic con el botón derecho del ratón y desplegaremos el menú para eliminarlas. Haremos lo mismo para las filas inferiores, que indican el total de datos y una especificación metodológica sobre los datos.
2) Descombinar las celdas
Si damos un repaso muy rápido a la hoja de cálculo, vemos que prácticamente en todas las columnas hay celdas combinadas y, por lo tanto, nos será imposible aplicar los filtros para ordenar de mayor a menor.
Para hacerlo, primero tendremos que descombinar las celdas y decidir de qué manera estructuramos la información.
Para descombinarlas, seleccionaremos todo el conjunto de datos de Excel haciendo clic en el cuadrante superior izquierda, tal y como indica la siguiente imagen, o yendo columna por columna.
Una vez seleccionado todo el conjunto de la información, iremos a la pestaña «Formato», y dentro de esta, a «Celdas». En el menú emergente que aparecerá, tenemos que hacer clic en la pestaña «Alineación» y desmarcar la casilla inferior de «Combinar celdas» tal y como se muestra a continuación.
Una vez efectuada esta operación, todas las celdas quedarán automáticamente divididas de la manera siguiente:
El problema es que después de este cambio, las celdas descombinadas han quedado vacías de contenido. Al dividirlas, ha quedado la información que corresponde solo a la celda superior. Por lo tanto, lo que tendremos que hacer a continuación es copiar el contenido al resto de las celdas. A pesar de que hay macros, una opción avanzada del programa, prácticas para Microsoft Excel, que permiten automatizar este proceso, es un procedimiento más complejo que no resulta válido para todas las hojas de cálculo. En cambio, el sistema manual siempre será una alternativa viable.
3) Entender la estructura y llenar el contenido con cuidado
Así pues, para copiar el contenido de forma manual, nos situaremos en la parte inferior izquierda de la casilla que contiene la información que queremos copiar. Cuando aparezca una cruz negra, arrastramos la cruz hasta la última casilla en la que queremos que se copie el dato, y automáticamente la información quedará copiada. Si en vez de arrastrar hacemos doble clic, entonces el contenido se copiará a todas las celdas vacías consecutivas que haya a continuación de la misma columna.
Para navegar de manera más directa y sencilla por la tabla sin tener que utilizar la barra de la derecha, se puede usar la tecla Control y las flechas. En el caso de Macintosh, la combinación de teclas será Comando y, de nuevo, las flechas.
Antes de empezar a hacer el trabajo de copiar y pegar, es importante dar un vistazo a todo el conjunto de datos para entender cómo está estructurado. Es necesario llenar la información columna por columna, sin caer en errores.
A simple vista, localizamos que la primera columna, la de «departamento», sirve al mismo tiempo como «encabezamiento» y como «registro». Es decir, que hay licitaciones que las hace el departamento, otras que las hacen otros organismos que conforman el departamento, y otros casos en los que el departamento no hace ninguna licitación y simplemente lo anotan como encabezamiento. Es lo que ocurre, por ejemplo, con la fila 60.
Para llevar a cabo nuestro propósito periodístico, nos interesa que haya tanto la columna del departamento, como la del organismo concreto que hace la licitación. Además, para poder analizarlo bien no puede haber registros vacíos que solo sirvan como encabezamientos.
Por lo tanto, crearemos una columna nueva que se llamará «Departamento» y renombraremos la que teníamos en segundo lugar como «Organismo licitador». A continuación, copiaremos en la primera columna el nombre del departamento que hace la licitación, y en la segunda, el organismo concreto que hace la licitación: si es el departamento mismo, copiaremos el nombre del departamento y, si es un organismo más pequeño que pertenece al departamento, entonces copiaremos el nombre del organismo.
Los departamentos de Gobernación, Administraciones Públicas y Vivienda, el de Justicia, y el de Territorio y Sostenibilidad no han hecho ninguna licitación y, por lo tanto, solo sirven como encabezamientos. En estos tres casos, eliminaremos la fila vacía y copiaremos el nombre del departamento en las filas inferiores, en las que están las licitaciones de los órganos que sí han licitado y dependen de estos departamentos.
Antes de empezar el análisis, hay que añadir una columna que identifique cada contrato con un registro único, por ejemplo, uno numérico. Necesitamos esta columna porque cada fila no es un contrato, sino una adjudicación. De forma que si un contrato tiene más de un adjudicatario, el contenido estará acumulado en distintas filas según el número de empresas adjudicatarias. Para solucionar este aspecto, crearemos una columna nueva con un código único para cada contrato. El valor adjudicado a cada contrato, pues, se repetirá en los casos en los que un contrato haya sido adjudicado a más de una empresa.
Una vez que hemos creado esta columna, acabaremos de copiar la información para el resto de los campos. Al igual que hemos hecho con las dos primeras columnas, arrastraremos la información de la primera celda al resto de las casillas duplicadas. Cuidado con arrastrar y no copiar, puesto que hay valores, como las fechas o los números, que pueden aumentar en lugar de copiarse exactamente. En estos casos, tendremos que copiar la información y no arrastrarla.
4) Homogeneizar los campos
Una vez modificada la estructura, podremos proceder a hacer la limpieza habitual con las fórmulas que hemos visto en el apartado anterior, y que sirven para homogeneizar todos los campos. Así pues, aplicaremos las fórmulas correspondientes para quitar los espacios excedentes y colocar todas las letras en mayúscula.
Para hacerlo, crearemos una columna nueva al lado de la que queremos limpiar, haciendo un clic con el botón de la derecha sobre la columna.
Una vez creada la columna, aplicaremos la fórmula correspondiente, tanto si es =ESPACIOS, o =MAYUSC, y la arrastraremos a lo largo de toda la columna.
Para poder trabajar mejor a posteriori con la hoja de cálculo, es conveniente eliminar las fórmulas una vez aplicadas y, por lo tanto, trabajar solo con los valores. Para hacerlo, copiaremos toda la columna sobre la que estamos aplicando la fórmula, en este caso la columna C.
Con la información copiada, nos situaremos sobre la columna que contiene la información original, la B, e iremos a la pestaña «Edición» para hacer un clic sobre la opción «Pegado especial».
Por defecto, se abrirá un menú que tendrá marcada la opción de pegarlo «todo». Si no queremos copiar también las fórmulas, deberemos cambiar la opción determinada por defecto por «valores», tal y como se muestra en la siguiente captura de pantalla.
Una vez copiada la información de la forma correcta, podremos proceder a eliminar la columna de la fórmula. Este proceso lo repetiremos con todas las columnas, hasta que toda la hoja de cálculo esté a punto para analizar.
Otra manera más rápida de aplicar esta fórmula consiste en aplicar las fórmulas en una hoja nueva, en lugar de una columna nueva. De este modo, aplicaremos la fórmula deseada (=MAYUSC o =ESPACIOS) a todas las columnas a la vez. A pesar de que a la larga es la forma más efectiva, cuando estamos empezando, ir paso por paso va bien para evitar confusiones.
5) Comprobar que la herramienta de limpieza se ha hecho correctamente
Después de haber hecho la limpieza, y antes de pasar al análisis, habrá que comprobar que no se han cometido errores mientras se tecleaba durante la limpieza. Uno de los errores comunes es, por ejemplo, haber pasado por alto copiar y pegar alguna celda. Para detectar de manera rápida este error, lo más sencillo es navegar por la hoja de cálculo con la tecla Control + flechas, o Comando + flechas si trabajamos en Mac.
Por otro lado, también habrá que comprobar que el formato de las columnas es correcto. Sobre todo a la hora de hacer el pegado especial de únicamente los «valores», puesto que al hacerlo perdemos el formato atribuido a cada columna. Para modificar el formato de los datos, será necesario seleccionar la columna, y en la pestaña «Formato» hacer clic en la opción «Celdas», seleccionando el formato adecuado para cada una de las columnas. Por ejemplo, en el caso de la columna D, esto se traducirá en el formato de fecha formado por día, mes y año.
Ahora sí, una vez hechas las comprobaciones y tras comprobar que no hay errores de limpieza, podremos proceder a analizar los datos.
Para ir bien, el resultado final limpio tendría que quedar como el archivo adjunto siguiente: Adjudicacions_Net.