De datos en una columna a tabla
¿Cómo organizar los datos de una columna en una tabla?
Tenemos los datos de una serie de productos apilados en una columna y queremos organizar esos datos en una tabla.
Observamos que hay cuatro tipos de datos que se corresponden con varios campos distintos y que se van repitiendo hacia abajo.
Queremos una tabla que presente los datos en campos.
La tabla tendrá una fila de encabezados para los campos.
Cada celda de la tabla tiene que albergar el valor que le corresponda de la columna de datos. Para conseguirlo vamos a utilizar una técnica que usa la función INDICE.
La función INDICE nos devuelve el valor de una celda dentro de una matriz o tabla, según el número de fila que la celda ocupa dentro de la matriz. En la versión matricial de esta función, son argumentos obligatorios la matriz (o tabla) y el número de fila.
INDICE(matriz; núm_fila; [núm_columna])
Esta técnica completará las celdas de la tabla con los valores de las celdas de la columna de datos. La técnica consiste en emparejar las celdas de la columna de datos con las celdas que les correspondan en la tabla mediante indicadores. Estos indicadores los conseguimos combinando las funciones FILAS y COLUMNAS, que usaremos como argumento para el número de fila de la función INDICE.
La técnica
La función INDICE devuelve el valor de una celda dentro de una matriz, siempre que sepamos en qué fila está dicha celda.
De los dos argumentos obligatorios para usar la función INDICE, ahora mismo tenemos la matriz, que es el rango de celdas que contiene los valores en la columna de datos. Nos hace falta saber en qué fila está cada celda de la columna de datos. Pero la columna de datos puede estar colocada en cualquier lugar de la hoja de cálculo así que tenemos que asegurarnos una manera exacta para saber en qué fila está cada celda de la columna de datos.
Paso 1: indicadores numéricos para la matriz
Supongamos que cada celda de la columna de datos tuviese un identificador numérico; la primera celda sería el 1, la siguiente celda sería el número 2 y así sucesivamente hasta el final de la columna de datos.
Paso 2: indicadores numéricos para la tabla
Consideraremos la tabla como una cuadrícula.
Cada celda de la cuadrícula tendrá su indicador numérico. Este nos permitirá emparejar las celdas de la columna de datos con su correspondiente lugar en la cuadrícula. Así, el valor de la celda número 1 de la columna de datos irá a la celda número 1 de la tabla y después el siguiente valor hasta llegar al final de la columna.
El indicador numérico para cada celda de la cuadrícula lo obtendremos mediante una fórmula que combina las funciones COLUMNAS y FILAS.
Paso 3: conseguimos el número de columna en la tabla
La función COLUMNAS nos devuelve el número de columnas de una matriz o de una referencia.
COLUMNAS(matriz)
El número de columna nos servirá para recorrer la tabla en sentido vertical. Nos situamos en la celda D3 (primera celda de la tabla a donde queremos llevar el primer valor de la columna de datos) y escribimos esta función:
Como queremos saber el número de columna de la primera celda de la cuadrícula (D3), la matriz que necesitamos para usar la función COLUMNAS será exclusivamente la propia celda D3. Cuando extendamos la selección al resto de columnas y filas, Excel tomará como referencia la columna D. Será la columna número 1 de la cuadrícula. Lo conseguimos usando una referencia mixta a la columna D (el símbolo dólar delante de la primera D).
Aplicamos y extendemos la selección al resto de columnas y conseguimos este resultado:
Ya tenemos un número para cada columna la tabla. Al extender la selección hacia abajo, aparece el valor de la columna a la que pertenece cada celda:
Paso 4: conseguimos el número de fila en la tabla
La función FILAS nos devuelve el número de filas de una matriz o de una referencia.
FILAS(matriz)
El número de fila nos servirá para recorrer la tabla en sentido horizontal. Igual que para las columnas, nos situamos en la celda D3 y escribimos esat función:
Queremos saber el número de fila de la primera celda de la cuadrícula (D3), por lo tanto la matriz que necesitamos para usar la función FILAS será exclusivamente la propia celda D3. Cuando extendamos la selección al resto de columnas y filas, Excel tomará como referencia la fila 3. Será lafila número 1 de la cuadrícula. Lo conseguimos usando una referencia mixta a la fila 3 (el símbolo dólar delante del primer 3).
Aplicamos y extendemos la selección al resto de columnas y conseguimos:
Ya tenemos un número para cada fila de la tabla. Cuando extendemos la selección hacia abajo, aparece el valor de cada fila en las celdas:
Paso 5: conseguimos un único número para cada celda de la tabla
Los resultados de las funciones COLUMNAS y FILAS utilizados por separado son inútiles. Son un par de valores que no identifican de manera única cada celda. Sin embargo, si los sumamos conseguimos un valor más cercano a lo que pretendemos.
La fórmula proporciona estos resultados en las celdas:
Es decir, cada celda es el resultado de esta operación:
Extendemos la selección a toda la cuadrícula y conseguimos:
Fijándonos en los resultados, comprobamos que si restamos uno al valor de la fila, conseguimos la sucesión numérica que queremos para la primera fila: 1, 2, 3, 4. Ajustamos la fórmula restando 1 a la función FILAS.
y conseguimos la sucesión numérica que queremos para la primera fila:
Para el resto de celdas de la tabla conseguimos este resultado:
Tras este ajuste, para el valor de la función FILAS hemos conseguido esta serie numérica: 0, 1, 2 … 13 y 14. Como son cuatro los campos, la serie numérica tiene que ser múltiplo de 4, es decir, tenemos que ajustar de nuevo la fórmula mulitplicando el valor de (FILAS – 1) por 4.
Como el valor de la función COLUMNAS no ha cambiado (1, 2, 3, 4), la fórmula va a operar con estos valores en cada celda:
El resultado es este:
Acabamos de conseguir el valor de fila que necesitamos en cada celda para la función INDICE.
Paso 6: usamos la función INDICE
El primer argumento de la función INDICE (matriz) es la columna de datos como referencia absoluta:
El segundo argumento, número de fila, es el resultado de la fórmula que hemos construido en el paso anterior:
El resultado para la primera fila:
Extendemos la selección a toda la tabla:
Hemos transferido correctamente los datos apilados de una columna a una tabla, ordenándolos por campos.