Ver video - Rellenar en Excel
Muchas veces, puede encontrar un conjunto de datos donde solo una celda está llena de datos y las celdas debajo están en blanco hasta el siguiente valor.
Algo como se muestra a continuación:
Si bien este formato funciona para algunas personas, el problema con este tipo de datos es que no puede usarlo para crear tablas dinámicas o usarlo en cálculos.
¡Y esto tiene una solución fácil!
En este tutorial, le mostraré cómo rápidamente rellenar celdas en Excel hasta el siguiente valor llenado.
Puede hacer esto fácilmente usando una técnica simple de cuadro de diálogo especial Ir a, VBA o Power Query.
¡Entonces empecemos!
Método 1 - Rellenar usando Ir a especial + fórmula
Suponga que tiene un conjunto de datos como se muestra a continuación y desea completar los datos en la columna A y la columna B.
En la columna B, el objetivo es llenar "Impresora" hasta la última celda vacía debajo de ella, y luego, cuando se inicie "Escáner", luego llene "Escáner" en las celdas de abajo hasta que las celdas estén vacías.
Debajo de los pasos para usar, vaya a especial para seleccionar todas las celdas en blanco y completar usando una fórmula simple:
- Seleccione los datos en los que desea completar (A1: D21 en nuestro ejemplo)
- Vaya a la pestaña "Inicio"
- En el grupo Edición, haga clic en el ícono "Buscar y seleccionar" (esto mostrará algunas opciones más en el menú desplegable).
- Haga clic en la opción "Ir a"
- En el cuadro de diálogo Ir a que se abre, haga clic en el botón Especial. Esto abrirá el cuadro de diálogo "Ir a especial".
- En el cuadro de diálogo Ir a especial, haga clic en espacios en blanco
- Haga clic en Aceptar
Los pasos anteriores seleccionarían todas las celdas en blanco en el conjunto de datos (como se muestra a continuación).
En las celdas en blanco que están seleccionadas, observará que una celda es más clara que el resto de la selección. Esta celda es la celda activa donde vamos a ingresar una fórmula.
No se preocupe por dónde está la celda en la selección, ya que nuestro método funcionaría en cualquier caso.
Ahora, a continuación se muestran los pasos para completar los datos en las celdas en blanco seleccionadas:
- Presione la tecla igual a (=) en su teclado. Esto insertará un signo igual en la celda activa.
- Presione la tecla de flecha hacia arriba. Esto insertará la referencia de celda de la celda sobre la celda activa. Por ejemplo, en nuestro caso, B3 es la celda activa y cuando hacemos estos dos pasos, ingresa = B2 en la celda
- Mantenga presionada la tecla Control y presione la tecla Enter
Los pasos anteriores insertarían automáticamente todas las celdas en blanco con el valor encima de ellas.
Si bien esto puede parecer demasiados pasos, una vez que aprenda a usar este método, podrá completar rápidamente los datos en Excel en unos pocos segundos.
Ahora, hay dos cosas más de las que debe ocuparse al utilizar este método.
Convertir fórmulas en valores
La primera es asegurarse de convertir fórmulas en valores (para que tenga valores estáticos y las cosas no se estropeen en caso de que cambie los datos en el futuro).
A continuación se muestra un video que le mostrará cómo convertir rápidamente fórmulas a valores en Excel.
Cambiar el formato de fecha
En caso de que esté utilizando fechas en sus datos (como yo en los datos de ejemplo), notará que los valores rellenados en la columna de fecha son números y no fechas.
Si la salida en la columna de fecha tiene el formato de fecha deseado, está bien y no necesita hacer nada más.
Pero si no tienen el formato de fecha correcto, debe realizar un pequeño ajuste. Si bien tiene los valores correctos, solo necesita cambiar el formato para que se muestre como una fecha en la celda.
A continuación se muestran los pasos para hacer esto:
- Seleccione la columna que tiene las fechas
- Haga clic en la pestaña Inicio
- En el grupo Número, haga clic en el menú desplegable Formato y luego seleccione el formato de fecha.
Si necesita hacer este relleno de vez en cuando, le recomiendo que use esta técnica especial y de fórmula Go-To.
Aunque tiene algunos pasos, es simple y le brinda el resultado allí mismo en su conjunto de datos.
Pero en caso de que tenga que hacer esto con bastante frecuencia, le sugiero que mire los métodos VBA y Power Query que se tratan a continuación.
Método 2: rellenar con un código VBA simple
Puede usar un código de macro VBA realmente simple para completar rápidamente las celdas en Excel hasta el último valor vacío.
Solo necesita agregar el código VBA al archivo una vez y luego puede reutilizar fácilmente el código varias veces en el mismo libro de trabajo o incluso en varios libros de trabajo en su sistema.
A continuación se muestra el código VBA que pasará por cada celda de la selección y completará todas las celdas que están en blanco:
'Este código VBA es creado por Sumit Bansal de TrumpExcel.com Sub FillDown () para cada celda en la selección Si celda = "" Entonces celda.
El código anterior usa un bucle For para recorrer cada celda de la selección.
Dentro del ciclo For, he usado una condición If-Then que verifica si la celda está vacía o no.
En caso de que la celda esté vacía, se rellena con el valor de la celda de arriba, y en caso de que no esté vacía, el ciclo for simplemente ignora la celda y pasa a la siguiente.
Ahora que tiene el código VBA, déjeme mostrarle dónde poner este código en Excel:
- Seleccione los datos donde desea completar
- Haga clic en la pestaña Desarrollador
- Haga clic en el icono de Visual Basic (o puede usar el atajo de teclado ALT + F11). Esto abrirá el Editor de Visual Basic en Excel.
- En el Editor de Visual Basic, a la izquierda, tendría el Explorador de proyectos. Si no lo ve, haga clic en la opción "Ver" en el menú y luego haga clic en Explorador de proyectos
- En caso de que tenga varios libros de Excel abiertos, el Explorador de proyectos le mostrará todos los nombres de los libros de trabajo. Busque el nombre del libro de trabajo donde tiene los datos.
- Haga clic con el botón derecho en cualquiera de los objetos en el libro de trabajo, vaya a Insertar y luego haga clic en Módulo. Esto insertará un nuevo módulo para ese libro.
- Haga doble clic en el "Módulo" que acaba de insertar en el paso anterior. Abrirá la ventana de código para ese módulo.
- Copie y pegue el código VBA en la ventana de código
- Coloque el cursor en cualquier lugar del código y luego ejecute el código macro haciendo clic en el botón verde en la barra de herramientas o usando el atajo de teclado F5
Los pasos anteriores ejecutarían el código VBA y sus datos se completarían.
Si desea usar este código VBA nuevamente en el futuro, debe guardar este archivo como un libro de Excel habilitado para macros (con una extensión .XLSM)
Otra cosa que puede hacer es agregar esta macro a su barra de herramientas de acceso rápido, que siempre está visible y tendrá acceso a esta macro con un solo clic (en el libro de trabajo donde tiene el código en el backend).
Entonces, la próxima vez que tenga datos que necesite completar, todo lo que necesita hacer es hacer la selección y luego hacer clic en el botón de macro en la barra de herramientas de acceso rápido.
También puede agregar esta macro al Libro de macros personal y luego usarlo en cualquier libro de su sistema.
Haga clic aquí para leer más sobre los libros de trabajo de macros personales y cómo agregarles código.
Método 3 - Rellenar usando Power Query
Power Query tiene una funcionalidad incorporada que le permite completar con un solo clic.
Se recomienda usar Power Query cuando lo usa de todos modos para transformar sus datos o combinar datos de varias hojas de trabajo o varios libros de trabajo.
Como parte de su flujo de trabajo existente, puede usar la opción de relleno en Power Query para llenar rápidamente las celdas en blanco.
Para usar Power Query, se recomienda que sus datos estén en formato de tabla de Excel. Si no puede convertir sus datos en una tabla de Excel, tendrá que crear un rango con nombre para los datos y luego usar ese rango con nombre en Power Query.
A continuación, tengo el conjunto de datos que ya he convertido en una tabla de Excel. Puede hacerlo seleccionando el conjunto de datos, yendo a la pestaña Insertar y luego haciendo clic en el ícono de Tabla.
Debajo de los pasos para usar Power Query para completar los datos hasta el siguiente valor:
- Seleccione cualquier celda del conjunto de datos
- Haga clic en la pestaña Datos
- En el grupo Obtener y transformar datos, haga clic en "Desde hoja". Esto abrirá el editor de Power Query. Tenga en cuenta que las celdas en blanco mostrarían el valor "nulo" en Power Query
- En el editor de Power Query, seleccione las columnas que desea completar. Para hacer esto, mantenga presionada la tecla Control y luego haga clic en el encabezado de las columnas que desea seleccionar. En nuestro ejemplo, serían las columnas Fecha y Producto.
- Haga clic derecho en cualquiera de los encabezados seleccionados
- Vaya a la opción "Rellenar" y luego haga clic en "Abajo". Esto llenaría los datos en todas las celdas en blanco.
- Haga clic en la pestaña Archivo y luego en "Cerrar y cargar". Esto insertará una nueva hoja de trabajo en el libro de trabajo con la tabla resultante.
Si bien este método puede parecer un poco exagerado, una gran ventaja de usar Power Query es que le permite actualizar rápidamente los datos resultantes en caso de que cambien los datos originales.
Por ejemplo, si agrega más registros a los datos originales, no es necesario que vuelva a hacer todo lo anterior. Simplemente puede hacer clic derecho en la tabla resultante y luego hacer clic en Actualizar.
Si bien el método Power Query funciona bien, es mejor usarlo cuando ya está usando Power Query en su flujo de trabajo.
Si solo tiene un conjunto de datos en el que desea completar las celdas en blanco, entonces sería más conveniente usar el método especial Ir a o el método VBA (mencionado anteriormente).
Estas son tres formas sencillas que puede utilizar para llene las celdas en blanco hasta el siguiente valor en Excel.
Espero que este tutorial te haya resultado útil.
