Cómo aplanar, repetir y rellenar etiquetas en Excel

En esta publicación, cubriremos una técnica útil para completar manualmente etiquetas de datos en un rango de Excel. Esto también se puede denominar repetir las etiquetas o aplanar los datos. Entremos en ello.

Nota: Una alternativa sería utilizar una consulta Obtener y transformar, como se analiza en esta publicación .

Aplanamiento de datos

Muchas de las funciones de Excel, como tablas dinámicas, gráficos, filtro automático y la función subtotal, se diseñaron para funcionar con datos planos. Los datos planos se describen como datos que contienen valores en todas las celdas de la tabla. Toda la información sobre el registro se deriva de los valores de la fila y no de su posición dentro de la tabla. Es sencillo ver la diferencia visualmente. Entonces, esta captura de pantalla muestra datos que no son planos:

Puede ver que las etiquetas no se repiten y que hay celdas con valores faltantes. Por tanto, debemos determinar información sobre un registro en función de la posición de la fila dentro de la tabla. Por ejemplo, sabemos que la fila 39 es para Bayshore Water, pero solo sabemos que la fila 40 es para Bayshore Water según su posición dentro de la tabla.

Por el contrario, los datos planos contienen repeticiones de las etiquetas según sea necesario. Esta captura de pantalla muestra datos planos:

Todas las celdas de la tabla contienen valores. Toda la información sobre un registro está contenida en la fila y no se deriva de su posición dentro de la tabla.

En ocasiones obtenemos datos de un sistema contable o de un cliente que no es plano. Queremos usarlo con Excel y realmente desearíamos poder aplanarlo fácilmente. Dado que se trata de Excel, existen, por supuesto, varias formas de abordar esta tarea, pero una de las más sencillas es mediante el uso de una serie de comandos integrados.

Comenzaremos con un resumen y luego analizaremos los detalles.

Resumen

1. Seleccione el rango que desea aplanar (normalmente, una columna de etiquetas)
2. Resalte solo las celdas vacías: presione F5 (Ir a) y seleccione Especial Espacios en blanco
3. Escriba igual a (=) y luego la flecha hacia arriba para ingresar una fórmula con una referencia de celda directa a la primera etiqueta de datos.
4. En lugar de presionar Enter, mantén presionado Control y presiona Enter.
5. Para reemplazar las fórmulas con valores, seleccione toda la columna y luego Copiar/Pegado especial Valores

Detalles

Aquí, recorreremos cada paso y… ¡traje capturas de pantalla!

Paso 1:

Primero, seleccione el rango que desea aplanar. Suele ser una columna de etiquetas que desea repetir, representada por B39:B62 en la siguiente captura de pantalla:

Paso 2:

A continuación, debemos seleccionar solo las celdas vacías dentro del rango. Simplemente podemos usar el comando Ir a para esto. Presione la tecla F5 en su teclado para abrir el cuadro de diálogo Ir a, como se muestra a continuación:

A continuación, presione el botón Especial para abrir el cuadro de diálogo Ir a especial. Seleccione la opción Espacios en blanco, como se muestra a continuación:

Haga clic en Aceptar y Excel seleccionará solo las celdas vacías/en blanco dentro del rango original, como se muestra a continuación:

Paso 3:

Ahora, necesitamos escribir una fórmula que extraiga el valor de la celda de arriba. Esto se logra fácilmente escribiendo un signo igual (=) y luego presionando la tecla de flecha hacia arriba en su teclado. Hay otras formas, pero para mí esta es la forma más sencilla de escribir la fórmula. ¡Ahora, resiste la tentación de presionar la tecla Enter! NO presiones Enter todavía. La fórmula resultante se muestra a continuación:

Etapa 4:

Ahora, necesitamos completar esta fórmula en todas las celdas seleccionadas (en blanco). Esto se hace manteniendo presionada la tecla Control y luego presionando Enter inmediatamente después de escribir la fórmula. Si ha escrito la fórmula y ya ha presionado Entrar, deberá escribir la fórmula nuevamente y presionar Ctrl+Entrar en lugar de Entrar.

El atajo Ctrl+Enter le indica a Excel que realice dos tareas a la vez. Ingrese la fórmula y complétela en todas las celdas seleccionadas.

El resultado de este comando se muestra a continuación:

Paso 5:

Ahora sólo queda sustituir las fórmulas por sus valores. Primero, seleccione toda la columna. Excel no nos permite realizar el siguiente paso con varios rangos seleccionados, por lo que debemos seleccionar un rango de una sola columna.

Ahora, simplemente copiamos el rango usando el método que prefiera (cinta, clic derecho, método abreviado de teclado). Luego, hacemos un Pegado Especial. En el cuadro de diálogo Pegado especial que aparece, seleccionamos Valores, como se muestra a continuación:

Cuando hacemos clic en Aceptar, hemos terminado. Repetimos estos pasos en la columna Cuenta y bam… datos planos, como se muestra a continuación:

Notas

Por lo general, puede realizar esta tarea en varias columnas al mismo tiempo, pero solo funciona si la primera fila tiene valores para todas las columnas seleccionadas, por lo tanto, asegúrese de revisar y verificar su trabajo.

Conclusión

La primera vez que oí hablar de este enfoque fue John Walkenbach… ¡gracias John!

Como ocurre con casi todo en Excel, existen muchas formas diferentes de realizar cualquier tarea determinada. Usaría el enfoque anterior si necesitara aplanar datos ocasionalmente. Sin embargo, si necesitara aplanar archivos de datos con frecuencia, probablemente manejaría esta tarea con una consulta de obtención y transformación.

¡Espero que este enfoque le ayude a aplanar archivos de datos rápidamente!

Recursos

• Si quieres probar esta técnica, puedes practicar usando el archivo Excel: Plano
• Publicación de Excel U: Obtener y transformar consulta: una alternativa al aplanamiento manual de datos