Suposem que esteu executant diversos projectes amb diferents pressupostos i voleu visualitzar els vostres costos per a cadascun d'ells. És a dir, d'aquesta taula font:
.. obteniu alguna cosa com això:
En altres paraules, cal repartir el pressupost durant els dies de cada projecte i obtenir una versió simplificada del diagrama de Gantt del projecte. Fer-ho amb les mans és llarg i avorrit, les macros són difícils, però Power Query per Excel en aquesta situació mostra el seu poder en tota la seva glòria.
Power Consulta és un complement de Microsoft que pot importar dades a Excel des de gairebé qualsevol font i després transformar-les de moltes maneres diferents. A Excel 2016, aquest complement ja està integrat per defecte, i per a Excel 2010-2013 es pot descarregar des del lloc web de Microsoft i després instal·lar-lo al vostre ordinador.
Primer, convertim la nostra taula original en una taula "intel·ligent" escollint l'ordre Format com a taula llengüeta Home (Inici — Format com a taula) o prement la drecera del teclat Ctrl+T :
A continuació, aneu a la pestanya dades (si teniu Excel 2016) o a la pestanya Power Consulta (si teniu Excel 2010-2013 i heu instal·lat Power Query com a complement independent) i feu clic al botó Des de la taula / Interval. :
La nostra taula intel·ligent es carrega a l'editor de consultes de Power Query, on el primer pas és configurar els formats de nombre per a cada columna mitjançant els menús desplegables de la capçalera de la taula:
Per calcular el pressupost per dia, cal calcular la durada de cada projecte. Per fer-ho, seleccioneu (manteniu premuda la tecla Ctrl) primera columna Finish, I després Començar i triar un equip Afegir columna - Data - Restar dies (Afegeix columna — Data — Resta dies):
Els números resultants són 1 menys del necessari, perquè se suposa que hem de començar cada projecte el primer dia al matí i acabar l'últim dia al vespre. Per tant, seleccioneu la columna resultant i afegiu-hi una unitat mitjançant l'ordre Transformar - Estàndard - Afegeix (Transformar — Estàndard — Afegeix):
Ara afegim una columna on calculem el pressupost per dia. Per fer-ho, a la pestanya Afegeix columna Jo no jugo Columna personalitzada (Columna personalitzada) i a la finestra que apareix, introduïu el nom del nou camp i la fórmula de càlcul, utilitzant els noms de les columnes de la llista:
Ara el moment més subtil: creem una altra columna calculada amb una llista de dates de principi a fi amb un pas d'1 dia. Per fer-ho, torneu a prémer el botó Columna personalitzada (Columna personalitzada) i utilitzeu el llenguatge Power Query integrat M, que s'anomena Llista.Dates:
Aquesta funció té tres arguments:
- data d'inici: en el nostre cas, es pren de la columna Començar
- el nombre de dates a generar; en el nostre cas, aquest és el nombre de dies per a cada projecte, que hem comptat anteriorment a la columna Sostracció
- pas de temps: establert pel disseny #durada(1,0,0,0), que significa en l'idioma M: un dia, zero hores, zero minuts, zero segons.
Després de fer clic OK obtenim una llista (Llista) de dates, que es pot ampliar a noves línies utilitzant el botó de la capçalera de la taula:
...i obtenim:
Ara només queda col·lapsar la taula, utilitzant les dates generades com a noms per a les columnes noves. L'equip és responsable d'això. Columna de detall (Columna pivot) llengüeta Converteix (Transformar):
Després de fer clic OK obtenim un resultat molt proper al desitjat:
Null és, en aquest cas, un anàleg d'una cel·la buida a Excel.
Queda per eliminar columnes innecessàries i descarregar la taula resultant al costat de les dades originals amb l'ordre Tancar i carregar: tancar i carregar a... (Tancar i carregar — Tancar i carregar a...) llengüeta Home (Inici):
Com a resultat obtenim:
Per a una major bellesa, podeu personalitzar l'aspecte de les taules intel·ligents resultants a la pestanya constructor (Disseny): establiu un estil de color únic, desactiveu els botons de filtre, activeu els totals, etc. A més, podeu seleccionar una taula amb dates i habilitar el ressaltat de números mitjançant el format condicional a la pestanya Inici — Format condicional — Escales de color (Inici — Format condicional — Escales de color):
I la millor part és que en el futur podeu editar-ne els antics o afegir nous projectes a la taula original i, a continuació, actualitzar la taula correcta amb les dates amb el botó dret del ratolí, i Power Query repetirà totes les accions que hem fet automàticament. .
Voilà!
- Diagrama de Gantt a Excel amb format condicional
- Calendari de fites del projecte
- Generació de files duplicades amb Power Query