contingut
Molts processos empresarials (i fins i tot negocis sencers) en aquesta vida impliquen el compliment de comandes per part d'un nombre limitat d'intèrprets en un termini determinat. La planificació en aquests casos es produeix, com diuen, "des del calendari" i sovint cal transferir els esdeveniments previstos en ell (comandes, reunions, lliuraments) a Microsoft Excel, per a una anàlisi posterior mitjançant fórmules, taules dinàmiques, gràfics, etc. etc.
Per descomptat, m'agradaria implementar aquesta transferència no mitjançant una còpia estúpida (que no és difícil), sinó amb l'actualització automàtica de dades perquè en el futur es mostrin tots els canvis fets al calendari i les noves comandes sobre la marxa. Sobresortir. Podeu implementar aquesta importació en qüestió de minuts mitjançant el complement Power Query integrat a Microsoft Excel, a partir de la versió 2016 (per a Excel 2010-2013, es pot descarregar des del lloc web de Microsoft i instal·lar-lo per separat des de l'enllaç) .
Suposem que utilitzem el Google Calendar gratuït per a la planificació, en el qual, per comoditat, vaig crear un calendari independent (el botó amb un signe més a l'extrem inferior dret al costat de Altres calendaris) amb el títol treball. Aquí introduïm totes les comandes que s'han de completar i lliurar als clients a les seves adreces:
Si feu doble clic a qualsevol comanda, podeu veure o editar-ne els detalls:
Tingues en compte que:
- El nom de l'esdeveniment és gerentqui compleix aquesta ordre (Elena) i orderNumber
- Indicat direcció lliurament
- La nota conté (en línies separades, però en qualsevol ordre) els paràmetres de la comanda: tipus de pagament, import, nom del client, etc. en el format Paràmetre=Valor.
Per a més claredat, les ordres de cada gestor es destaquen amb el seu propi color, tot i que això no és necessari.
Pas 1. Obteniu un enllaç a Google Calendar
Primer hem d'obtenir un enllaç web al nostre calendari de comandes. Per fer-ho, feu clic al botó amb tres punts Opcions del calendari treball al costat del nom del calendari i seleccioneu l'ordre Configuració i ús compartit:
A la finestra que s'obre, podeu, si voleu, fer públic el calendari o obrir-hi l'accés per a usuaris individuals. També necessitem un enllaç per accedir privat al calendari en format iCal:
Pas 2. Carregueu les dades del calendari a Power Query
Ara obriu Excel i a la pestanya dades (si teniu Excel 2010-2013, a la pestanya Power Consulta) tria una ordre Des d'Internet (Dades: d'Internet). A continuació, enganxeu el camí copiat al calendari i feu clic a D'acord.
L'iCal Power Query no reconeix el format, però és fàcil d'ajudar. Essencialment, iCal és un fitxer de text sense format amb dos punts com a delimitador, i al seu interior sembla una cosa així:
Per tant, només podeu fer clic amb el botó dret a la icona del fitxer descarregat i seleccionar el format que tingui el significat més proper CSV – i les nostres dades sobre totes les comandes es carregaran a l'editor de consultes de Power Query i es dividiran en dues columnes per dos punts:
Si us fixeu bé, podeu veure clarament que:
- La informació sobre cada esdeveniment (ordre) s'agrupa en un bloc que comença amb la paraula COMENÇAR i acaba amb FI.
- Les dates d'inici i de finalització s'emmagatzemen en cadenes etiquetades DTSTART i DTEND.
- L'adreça d'enviament és LOCATION.
- Nota de comanda – camp DESCRIPCIÓ.
- Nom de l'esdeveniment (nom del gestor i número de comanda) — Camp RESUM.
Queda per extreure aquesta informació útil i transformar-la en una taula convenient.
Pas 3. Converteix a la vista normal
Per fer-ho, realitzeu la següent cadena d'accions:
- Suprimim les 7 primeres línies que no necessitem abans de la primera ordre BEGIN Inici — Suprimeix les files — Suprimeix les files superiors (Inici — Eliminar files — Eliminar files superiors).
- Filtra per columna Column1 línies que contenen els camps que necessitem: DTSTART, DTEND, DESCRIPCIÓ, LOCALITZACIÓ i RESUM.
- A la pestanya Avançat Afegint una columna triar Columna d'índex (Afegeix columna — Columna d'índex)per afegir una columna de nombre de fila a les nostres dades.
- Allà mateix a la pestanya. Afegint una columna triar un equip Columna condicional (Afegeix columna — Columna condicional) i al començament de cada bloc (ordre) mostrem el valor de l'índex:
- Ompliu les cel·les buides de la columna resultant Bloquejarfent clic amb el botó dret al seu títol i seleccionant l'ordre Omplir - Avall (Omplir — Avall).
- Elimina la columna innecessària Index.
- Seleccioneu una columna Column1 i realitzar una convolució de les dades de la columna Column2 utilitzant l'ordre Transformació: columna pivotant (Transformació: columna pivot). Assegureu-vos de seleccionar a les opcions No agregar (No sumar)de manera que no s'aplica cap funció matemàtica a les dades:
- A la taula bidimensional (creuada) resultant, netegeu les barres invertides a la columna d'adreça (feu clic amb el botó dret a la capçalera de la columna - Substitució de valors) i elimineu la columna innecessària Bloquejar.
- Per girar el contingut de les columnes DTSTART и DTEND en una data-hora completa, ressaltant-les, seleccioneu a la pestanya Transformació - Data - Anàlisi d'execució (Transformació — Data — Anàlisi). A continuació, corregim el codi a la barra de fórmules substituint la funció Data Des de on DateTime.Fromper no perdre valors de temps:
- Aleshores, fent clic amb el botó dret a la capçalera, dividim la columna DESCRIPCIÓ amb paràmetres d'ordre per separador – símbol n, però al mateix temps, als paràmetres, seleccionarem la divisió en files, i no en columnes:
- Una vegada més, dividim la columna resultant en dues de separades: el paràmetre i el valor, però pel signe igual.
- Selecció d'una columna DESCRIPCIÓ.1 realitzeu la circumvolució, com hem fet anteriorment, amb l'ordre Transformació: columna pivotant (Transformació: columna pivot). La columna de valors en aquest cas serà la columna amb els valors dels paràmetres − DESCRIPCIÓ.2 Assegureu-vos de seleccionar una funció als paràmetres No agregar (No sumar):
- Queda per establir els formats per a totes les columnes i canviar-los el nom com vulgueu. I podeu tornar a carregar els resultats a Excel amb l'ordre Inici — Tancar i carregar — Tancar i carregar a... (Inici — Tancar i carregar — Tancar i carregar a...)
I aquí teniu la nostra llista de comandes carregades a Excel des de Google Calendar:
En el futur, en canviar o afegir noves comandes al calendari, només n'hi haurà prou amb actualitzar la nostra sol·licitud amb l'ordre Dades: actualitza-ho tot (Dades — Actualitza-ho tot).
- Calendari de fàbrica en Excel actualitzat des d'Internet mitjançant Power Query
- Transformar una columna en una taula
- Crear una base de dades en Excel