Digamos que está a executar varios proxectos con orzamentos diferentes e quere visualizar os seus custos para cada un deles. É dicir, a partir desta táboa fonte:
.. obtén algo así:
Noutras palabras, cómpre repartir o orzamento entre os días de cada proxecto e obter unha versión simplificada do diagrama de Gantt do proxecto. Facer isto coas mans é longo e aburrido, as macros son difíciles, pero Power Query para Excel en tal situación mostra o seu poder en todo o seu esplendor.
Consulta de enerxía é un complemento de Microsoft que pode importar datos a Excel desde case calquera fonte e despois transformalos de moitas formas diferentes. En Excel 2016, este complemento xa está integrado por defecto, e para Excel 2010-2013 pódese descargar desde o sitio web de Microsoft e, a continuación, instalar no seu PC.
En primeiro lugar, imos converter a nosa táboa orixinal nunha táboa "intelixente" escollendo o comando Formato como táboa aba casa (Inicio — Formato como táboa) ou premendo o atallo do teclado Ctrl+T :
A continuación, vai á pestana Data (se tes Excel 2016) ou na pestana Consulta de enerxía (se tes Excel 2010-2013 e instalou Power Query como un complemento separado) e fai clic no botón Desde a táboa/intervalo. :
A nosa táboa intelixente cárgase no editor de consultas de Power Query, onde o primeiro paso é configurar os formatos numéricos para cada columna usando os menús despregables da cabeceira da táboa:
Para calcular o orzamento por día, cómpre calcular a duración de cada proxecto. Para iso, seleccione (manteña premida a tecla Ctrl) primeira columna RematarE, a continuación comezo e escolle un equipo Engadir columna - Data - Restar días (Engadir columna — Data — Restar días):
Os números resultantes son 1 menos do necesario, porque se supón que debemos comezar cada proxecto o primeiro día pola mañá e rematar o último día pola noite. Polo tanto, seleccione a columna resultante e engádelle unha unidade mediante o comando Transformar - Estándar - Engadir (Transformar — Estándar — Engadir):
Agora imos engadir unha columna onde calculamos o orzamento por día. Para iso, na pestana Engadir columna Non xogo Columna personalizada (Columna personalizada) e na xanela que aparece, introduza o nome do novo campo e a fórmula de cálculo, utilizando os nomes das columnas da lista:
Agora o momento máis sutil: creamos outra columna calculada cunha lista de datas de principio a fin cun paso de 1 día. Para facelo, prema de novo o botón Columna personalizada (Columna personalizada) e use a linguaxe Power Query incorporada M, que se chama Lista.Datas:
Esta función ten tres argumentos:
- data de inicio: no noso caso, tómase da columna comezo
- o número de datas a xerar; no noso caso, este é o número de días para cada proxecto, que contamos anteriormente na columna Resta
- paso de tempo: definido polo deseño #duración(1,0,0,0), que significa na lingua de M - un día, cero horas, cero minutos, cero segundos.
Despois de premer OK obtemos unha lista (Lista) de datas, que se pode ampliar en liñas novas usando o botón da cabeceira da táboa:
... e temos:
Agora só queda contraer a táboa, usando as datas xeradas como nomes para as novas columnas. O equipo é o responsable diso. Columna de detalle (Columna pivote) aba Converter (Transformar):
Despois de premer OK obtemos un resultado moi próximo ao desexado:
Nulo é, neste caso, un análogo dunha cela baleira en Excel.
Queda por eliminar as columnas innecesarias e descargar a táboa resultante xunto aos datos orixinais co comando Pechar e cargar: pechar e cargar en... (Pechar e cargar — Pechar e cargar en...) aba casa (Inicio):
Obtemos como resultado:
Para unha maior beleza, pode personalizar o aspecto das táboas intelixentes resultantes na pestana Construtor (Deseño): establece un estilo de cor única, desactiva os botóns de filtro, activa os totais, etc. Ademais, podes seleccionar unha táboa con datas e activar o resaltado de números mediante o formato condicional na pestana Inicio — Formato condicional — Escalas de cores (Inicio — Formato condicional — Escalas de cores):
E a mellor parte é que no futuro podes editar con seguridade os antigos ou engadir novos proxectos á táboa orixinal e, a continuación, actualizar a táboa correcta coas datas co botón dereito do rato, e Power Query repetirá todas as accións que fixemos automaticamente. .
Voilà!
- Diagrama de Gantt en Excel usando formato condicional
- Calendario do proxecto
- Xeración de filas duplicadas con Power Query