Contenuti
Molti processi aziendali (e persino intere aziende) in questa vita implicano l'adempimento degli ordini da parte di un numero limitato di esecutori entro una data scadenza. La pianificazione in questi casi avviene, come si suol dire, "dal calendario" e spesso è necessario trasferire gli eventi in esso pianificati (ordini, riunioni, consegne) in Microsoft Excel - per ulteriori analisi tramite formule, tabelle pivot, grafici, eccetera.
Ovviamente, vorrei implementare un tale trasferimento non con una copia stupida (che non è semplicemente difficile), ma con l'aggiornamento automatico dei dati in modo che in futuro tutte le modifiche apportate al calendario e i nuovi ordini al volo vengano visualizzati in Eccellere. È possibile implementare tale importazione in pochi minuti utilizzando il componente aggiuntivo Power Query integrato in Microsoft Excel, a partire dalla versione 2016 (per Excel 2010-2013, può essere scaricato dal sito Web Microsoft e installato separatamente dal collegamento) .
Supponiamo di utilizzare il Google Calendar gratuito per la pianificazione, in cui, per comodità, ho creato un calendario separato (il pulsante con un segno più nell'angolo in basso a destra accanto a Altri calendari) con il titolo Lavora. Qui inseriamo tutti gli ordini che devono essere completati e consegnati ai clienti ai loro indirizzi:
Facendo doppio clic su qualsiasi ordine, puoi visualizzarne o modificarne i dettagli:
Nota che:
- Il nome dell'evento è direttorechi esegue questo ordine (Elena) e Numero d'ordine
- indicato indirizzo consegna
- La nota contiene (in righe separate, ma in qualsiasi ordine) i parametri dell'ordine: tipo di pagamento, importo, nome cliente, ecc. nel formato Parametro=Valore.
Per chiarezza, gli ordini di ciascun gestore sono evidenziati con il proprio colore, sebbene ciò non sia necessario.
Passaggio 1. Ottieni un collegamento a Google Calendar
Per prima cosa dobbiamo ottenere un collegamento web al nostro calendario degli ordini. Per fare ciò, fai clic sul pulsante con tre punti Le opzioni del calendario funzionano accanto al nome del calendario e seleziona il comando Impostazioni e condivisione:
Nella finestra che si apre, puoi, se lo desideri, rendere pubblico il calendario o aprirne l'accesso ai singoli utenti. Abbiamo anche bisogno di un link per l'accesso privato al calendario in formato iCal:
Passaggio 2. Carica i dati dal calendario in Power Query
Ora apri Excel e nella scheda Dati (se hai Excel 2010-2013, quindi nella scheda Power Query) scegli un comando Da Internet (Dati — Da Internet). Quindi incolla il percorso copiato sul calendario e fai clic su OK.
iCal Power Query non riconosce il formato, ma è facile aiutare. In sostanza, iCal è un semplice file di testo con i due punti come delimitatore e al suo interno assomiglia a questo:
Quindi puoi semplicemente fare clic con il tasto destro sull'icona del file scaricato e selezionare il formato che ha il significato più vicino CSV – e i nostri dati su tutti gli ordini verranno caricati nell'editor di query di Power Query e divisi in due colonne da due punti:
Se guardi da vicino, puoi vedere chiaramente che:
- Le informazioni su ciascun evento (ordine) sono raggruppate in un blocco che inizia con la parola BEGIN e termina con END.
- Le datetime di inizio e fine sono memorizzate in stringhe etichettate DTSTART e DTEND.
- L'indirizzo di spedizione è LOCATION.
- Nota d'ordine – campo DESCRIZIONE.
- Nome dell'evento (nome del gestore e numero d'ordine) — campo RIEPILOGO.
Resta da estrarre questa utile informazione e trasformarla in una comoda tabella.
Passaggio 3. Converti in visualizzazione normale
Per fare ciò, eseguire la seguente catena di azioni:
- Eliminiamo le prime 7 righe di cui non abbiamo bisogno prima del primo comando BEGIN Home — Elimina righe — Elimina le prime righe (Home — Rimuovi righe — Rimuovi righe superiori).
- Filtra per colonna Column1 righe contenenti i campi di cui abbiamo bisogno: DTSTART, DTEND, DESCRIZIONE, LOCATION e SUMMARY.
- Nella scheda Avanzate Aggiunta di una colonna scegliere Colonna indice (Aggiungi colonna — Colonna indice)per aggiungere una colonna di numero di riga ai nostri dati.
- Proprio lì sulla scheda. Aggiunta di una colonna scegli una squadra Colonna condizionale (Aggiungi colonna — Colonna condizionale) e all'inizio di ogni blocco (ordine) visualizziamo il valore dell'indice:
- Riempi le celle vuote nella colonna risultante Bloccarecliccando con il tasto destro del mouse sul suo titolo e selezionando il comando Riempi – Giù (Riempi — Giù).
- Rimuovi la colonna non necessaria Indice.
- Seleziona una colonna Column1 ed eseguire una convoluzione dei dati dalla colonna Column2 usando il comando Trasforma – Colonna pivot (Trasforma — Colonna pivot). Assicurati di selezionare nelle opzioni Non aggregare (Non aggregare)in modo che nessuna funzione matematica venga applicata ai dati:
- Nella tabella bidimensionale (a croce) risultante, deselezionare le barre inverse nella colonna dell'indirizzo (fare clic con il pulsante destro del mouse sull'intestazione della colonna - Valori sostitutivi) e rimuovere la colonna non necessaria Bloccare.
- Per girare il contenuto delle colonne DTINIZIO и TENDA in una data e ora completa, evidenziandoli, seleziona nella scheda Trasforma – Data – Esegui analisi (Trasforma — Data — Analizza). Quindi correggiamo il codice nella barra della formula sostituendo la funzione Data da on DateTime.Daper non perdere valori di tempo:
- Quindi, facendo clic con il pulsante destro del mouse sull'intestazione, dividiamo la colonna DESCRIZIONE con parametri d'ordine per separatore – simbolo n, ma allo stesso tempo, nei parametri, selezioneremo la divisione in righe e non in colonne:
- Ancora una volta, dividiamo la colonna risultante in due colonne separate: il parametro e il valore, ma con il segno di uguale.
- Selezione di una colonna DESCRIZIONE.1 eseguire la convoluzione, come abbiamo fatto in precedenza, con il comando Trasforma – Colonna pivot (Trasforma — Colonna pivot). La colonna del valore in questo caso sarà la colonna con i valori dei parametri − DESCRIZIONE.2 Assicurati di selezionare una funzione nei parametri Non aggregare (Non aggregare):
- Resta da impostare i formati per tutte le colonne e rinominarli come desiderato. E puoi caricare i risultati di nuovo su Excel con il comando Home — Chiudi e carica — Chiudi e carica in... (Home — Chiudi&Carica — Chiudi&Carica in...)
Ed ecco il nostro elenco di ordini caricati in Excel da Google Calendar:
In futuro, quando si modificano o si aggiungono nuovi ordini al calendario, basterà solo aggiornare la nostra richiesta con il comando Dati – Aggiorna tutto (Dati — Aggiorna tutto).
- Calendario di fabbrica in Excel aggiornato da Internet tramite Power Query
- Trasformare una colonna in una tabella
- Crea un database in Excel