Supongamos que está ejecutando varios proyectos con diferentes presupuestos y desea visualizar sus costos para cada uno de ellos. Es decir, de esta tabla fuente:
.. obtener algo como esto:
En otras palabras, debe distribuir el presupuesto entre los días de cada proyecto y obtener una versión simplificada del diagrama de Gantt del proyecto. Hacer esto con las manos es largo y aburrido, las macros son difíciles, pero Power Query para Excel en tal situación muestra su poder en todo su esplendor.
Power Query es un complemento de Microsoft que puede importar datos a Excel desde casi cualquier fuente y luego transformarlos de muchas maneras diferentes. En Excel 2016, este complemento ya está integrado de forma predeterminada, y para Excel 2010-2013 se puede descargar desde el sitio web de Microsoft y luego instalarlo en su PC.
Primero, convirtamos nuestra mesa original en una mesa "inteligente" eligiendo el comando Formatear como una tabla de la pestaña. Inicio (Inicio — Formatear como tabla) o presionando el atajo de teclado Ctrl+T :
Luego ve a la pestaña Datos (si tiene Excel 2016) o en la pestaña Power Query (si tiene Excel 2010-2013 e instaló Power Query como un complemento independiente) y haga clic en el botón Desde tabla/rango. :
Nuestra tabla inteligente se carga en el editor de consultas de Power Query, donde el primer paso es configurar los formatos de número para cada columna usando los menús desplegables en el encabezado de la tabla:
Para calcular el presupuesto por día, debe calcular la duración de cada proyecto. Para ello, seleccione (mantenga pulsada la tecla Ctrl) columna primero Acabado, y entonces Inicio y elige un equipo Añadir columna – Fecha – Restar días (Agregar columna — Fecha — Restar días):
Los números resultantes son 1 menos de lo necesario, porque se supone que debemos comenzar cada proyecto el primer día por la mañana y terminar el último día por la noche. Por lo tanto, seleccione la columna resultante y agréguele una unidad usando el comando Transformar – Estándar – Agregar (Transformar — Estándar — Agregar):
Ahora agreguemos una columna donde calculamos el presupuesto por día. Para ello, en la pestaña Añadir columna yo no juego columna personalizada (Columna personalizada) y en la ventana que aparece, ingrese el nombre del nuevo campo y la fórmula de cálculo, utilizando los nombres de las columnas de la lista:
Ahora el momento más sutil: creamos otra columna calculada con una lista de fechas de principio a fin con un paso de 1 día. Para hacer esto, presione nuevamente el botón columna personalizada (Columna personalizada) y use el lenguaje M integrado de Power Query, que se llama Lista.Fechas:
Esta función tiene tres argumentos:
- fecha de inicio – en nuestro caso, se toma de la columna Inicio
- la cantidad de fechas que se generarán; en nuestro caso, esta es la cantidad de días para cada proyecto, que contamos anteriormente en la columna Sustracción
- paso de tiempo: establecido por diseño #duración(1,0,0,0), que significa en el lenguaje de M – un día, cero horas, cero minutos, cero segundos.
Después de hacer clic en OK obtenemos una lista (Lista) de fechas, que se puede expandir en nuevas líneas usando el botón en el encabezado de la tabla:
… y obtenemos:
Ahora todo lo que queda es colapsar la tabla, usando las fechas generadas como los nombres de las nuevas columnas. El equipo es responsable de esto. columna de detalle (Columna de pivote) de la pestaña. Convertir (Transformar):
Después de hacer clic en OK obtenemos un resultado muy cercano al deseado:
Null es, en este caso, un análogo de una celda vacía en Excel.
Queda por eliminar columnas innecesarias y descargar la tabla resultante junto a los datos originales con el comando Cerrar y cargar – Cerrar y cargar en… (Cerrar y cargar — Cerrar y cargar a…) de la pestaña. Inicio (Home):
Obtenemos como resultado:
Para una mayor belleza, puede personalizar la apariencia de las tablas inteligentes resultantes en la pestaña Constructor (Diseño): establezca un estilo de un solo color, deshabilite los botones de filtro, habilite los totales, etc. Además, puede seleccionar una tabla con fechas y habilitar el resaltado de números usando el formato condicional en la pestaña Inicio — Formato condicional — Escalas de color (Inicio — Formato condicional — Escalas de colores):
Y la mejor parte es que en el futuro puede editar de forma segura los antiguos o agregar nuevos proyectos a la tabla original, y luego actualizar la tabla correcta con fechas con el botón derecho del mouse, y Power Query repetirá todas las acciones que hemos realizado automáticamente. .
Voilà!
- Diagrama de Gantt en Excel usando formato condicional
- Calendario de hitos del proyecto
- Generación de filas duplicadas con Power Query