Las celdas fusionadas son el problema hoy en día, pero estas son celdas fusionadas verticales. Eche un vistazo a la siguiente figura. Si hay 7 filas para la región del Medio Oeste, la palabra Medio Oeste aparece solo en la primera fila. Luego, alguien está fusionando las seis celdas en blanco restantes con la celda del Medio Oeste para crear una celda combinada de 7 celdas de alto.
Esta es una forma horrible de enviar datos. Si alguien domina Excel, es posible que desee ordenar los datos, agregar subtotales, usar tablas dinámicas. Las células fusionadas evitan esto.
Para empeorar las cosas, está recibiendo un nuevo archivo de la sede todos los días estructurado de esta manera. HQ está enviando archivos similares a 500 sucursales. Al ser solo un pequeño puesto de avanzada, no tiene muchas posibilidades de convencer a la sede de que esta es una forma horrible de enviar datos.
El artículo de hoy trata sobre cómo resolver rápidamente este problema con Power Query. La solución Power Query será más fácil en los días 2 al 9999 que los siguientes pasos en Excel:
Los gurús de Excel pueden sugerir estos pasos todos los días:
- Seleccione todas las celdas eligiendo el rectángulo arriba y a la izquierda de A1.
- Haga clic en el iniciador de diálogo en la esquina inferior derecha del grupo Alineación de la pestaña Inicio.
- Haga clic en el cuadro Combinar celdas dos veces para deseleccionarlo.
- Haga clic en Aceptar para cerrar el cuadro de diálogo Formato de celdas
- Seleccione del final de la columna A de nuevo a A1.
- Inicio, Buscar y seleccionar, Ir a especial, Espacios en blanco, Aceptar
- Tipo = Flecha hacia arriba. Presione Ctrl + Enter
- Seleccione del final de la columna A de nuevo a A1.
- Ctrl + C para copiar. Haga clic derecho y pegue valores
Pero hay una forma mucho más sencilla. Las nuevas herramientas de Power Query están integradas en las versiones de Windows de Office 365 y Excel 2016. Si tiene una versión de Windows de Excel 2010 o Excel 2013, puede descargar Power Query de forma gratuita desde Microsoft.
Aquí está la estrategia con Power Query:
- Haga un plan para guardar el libro de trabajo de cada día de la sede central en la misma carpeta con el mismo nombre.
- Abra un nuevo libro de trabajo en blanco que contendrá los datos fijos de HQ.
-
En el libro de trabajo en blanco, elija Datos, Obtener datos, Desde archivo, Desde libro de trabajo.
Iniciar el proceso de Power Query - Busque la carpeta y el archivo del paso n. ° 1.
-
Cuando se abre la ventana de Power Query, observe que las celdas combinadas se han ido. Tiene Midwest en la fila 1 y luego seis celdas que contienen "nulo". Seleccione esta columna haciendo clic en el encabezado Región.
Power Query elimina automáticamente las celdas combinadas -
En Power Query, seleccione la pestaña Transformar. Elija Rellenar, Abajo. La palabra Medio Oeste se copia de la fila 1 a las filas 2 a 7. Se producen transformaciones similares en todo el conjunto de datos.
Llene las celdas nulas con el valor de arriba -
Inicio, cierre y carga. Se cerrará Power Query. En aproximadamente 10-20 segundos, los datos limpiados de HQ aparecerán en una nueva hoja de trabajo en el libro de trabajo.
Devolver los datos a Excel - Guarde el libro de trabajo con un nombre como CleanedDataFromHQ.xlsx.
- Cuando se seleccionan los datos, debería ver el panel Consultas y conexiones en el lado derecho. Si no ve el panel, vaya a Datos, consultas y conexiones.
-
Haga clic con el botón derecho en la consulta en el panel Consultas y conexiones. Elija Propiedades.
Abra las Propiedades para esta consulta -
Elija "Actualizar datos al abrir el archivo". Haga clic en Aceptar.
Configure la consulta para que se ejecute cada vez que abra el libro.
Entonces, su flujo de trabajo se convierte en: (a) Recibir el libro de trabajo de la sede central por correo electrónico. (b) Guarde el archivo adjunto en la carpeta correcta con el nombre correcto. (c) Abra el libro CleanedDataFromHQ.xlsx. Los nuevos datos se cargarán en el libro de trabajo.
Ver video
Transcripción del video
Aprenda Excel de Podcast, Episodio 2221: Rellenar celdas combinadas.
Oye, bienvenido de nuevo al netcast. Soy Bill Jelen, estaba en Calumet City, Elizabeth aparece y dice que recibe este archivo, nuestro archivo, como este archivo, todos los días y es horrible. Aquí en la Columna A, no es solo que pusieron el Medio Oeste y dejaron un montón de espacios en blanco, es una celda fusionada. ¿Bien? Están enviando el-- HQ está enviando estos archivos a lugares alrededor del mundo con estas malditas células fusionadas aquí. Las células fusionadas son malas.
Muy bien, aquí se explica cómo deshacerse de esto. En primer lugar, voy a hacer clic con el botón derecho, Mover o Copiar, Crear una copia y moverla al nuevo libro. Así que pretendemos que es como el libro de ejercicios que recibe Elizabeth. Archivar, Guardar como, y cada vez que Elizabeth obtenga uno de estos libros de trabajo, quiero que lo coloque exactamente en el mismo lugar con el mismo nombre exacto. ¿Bien? Exactamente el mismo nombre, perfecto. Así que ahora tenemos ese único archivo, una sola hoja, archivaré cerca. Y luego crearemos un libro de trabajo completamente nuevo. Solo voy a insertar una nueva hoja de trabajo aquí y esta nueva hoja de trabajo se llamará Informe. Y en este espacio en blanco, nuestro Informe hará Datos, Obtener datos, Desde archivo, Desde un libro de trabajo, señalará el archivo en el que lo vamos a guardar, así que, con el mismo nombre exacto cada vez, haga clic en Importar , elija la primera hoja de trabajo,y luego haga clic en Editar. Bien. En primer lugar, esto es increíblemente increíble: la consulta de energía no tendrá ninguna de estas tonterías de celda de fusión, convierten instantáneamente las celdas de fusión en celdas individuales y todas estas celdas son nulas. Elija esa columna, ya está elegida aquí, y luego Transformar, Rellenar, Rellenar, así, y luego Inicio, Cerrar y Cargar. Muy bien, ahí está mi libro de trabajo.
Ahora, veo que algunos tienen algunas columnas adicionales aquí. Déjame hacer clic con el botón derecho y editaremos.
Y parece que todo lo que se encuentra a la derecha del costo debe desaparecer. ¿Cuántas cosas extra hay? Haga clic con el botón derecho y elimine esas columnas, perfecto, cerrar y cargar. Muy bien, ahora, el flujo de trabajo se convierte, cada vez que Elizabeth recibe un nuevo informe de la oficina central, lo guardará en ese mismo lugar con un nombre confiable o lo que sea, como sea que lo llamemos, y luego abrirá este libro de trabajo, y luego aquí haga clic en Actualizar.
O, si quisiéramos trabajar automáticamente, haga clic con el botón derecho aquí, vaya a Propiedades, justo fuera de la pantalla hasta el final, y luego diga: Cada vez que abro este archivo, actualice los datos. Así que cada vez que abro este libro de trabajo, aparecerá el nuevo libro de trabajo que obtuvimos de la oficina central, reemplazará esas celdas combinadas en blanco con los valores que deberían estar allí, y la vida es genial así.
Power Query está cubierto en mi libro, pero también está cubierto en este libro por Ken Puls y Miguel Escobar, M es para (DATA) MONKEY.
Muy bien, Elizabeth: ¿Cómo me deshago de las celdas de fusión verticales? Mi sede sigue enviándome estos archivos todos los días. Entonces, el plan: vamos a guardar el libro de trabajo en un lugar confiable con un nombre confiable; crear un libro de trabajo de informes en blanco, datos, obtener datos, de archivo, de libro de trabajo, especificar la hoja, ¡BAM! Las celdas de combinación se han ido. Selecciono esa columna y Rellenar, Cerrar y Cargar; luego, cada vez que obtenga un nuevo libro, guárdelo en un lugar confiable con el mismo nombre confiable; Abro el libro de trabajo de Informes y Refresco, o simplemente configuramos el libro de trabajo de Informes para Actualizar automáticamente al abrirlo.
Para descargar el libro de trabajo del video de hoy es la URL en la descripción de YouTube.
Quiero agradecerle a Elizabeth por estar en mi seminario en Calumet City, quiero agradecerle por pasar por aquí. Nos vemos la próxima vez para otro netcast de.
Descargar archivo de Excel
Para descargar el archivo de Excel: fill-merged-cells-down.xlsx
Power Query es una herramienta increíble: le permite resolver una variedad de
saeed Alimohammadi
