I sistemi di pianificazione soffrono di problemi di invecchiamento: potrebbero essere stati la combinazione perfetta con l’azienda quando sono stati installati. Ma l’azienda cambia nel tempo e modificare il sistema è complesso e costoso. Quando i pianificatori iniziano a rispondere “Il sistema lo fa, non è colpa mia”, è il momento di controllare il tuo sistema di pianificazione.
Di recente ho lavorato con un fornitore con scarse prestazioni di consegna, ma con alti livelli di inventario per i prodotti finiti. I pianificatori dicevano “È il sistema, non io!”.
Ho creato uno strumento, basato sul modello di sviluppo rapido di Excel, per confrontare la loro pianificazione effettiva con i requisiti di domanda nominale in termini di materiali e capacità. Questo articolo e lo strumento che lo accompagna sono ispirati a quell’esperienza. Per scaricare lo strumento, compila il modulo in fondo a questo articolo. Ma, come sempre, avrà molto più senso se prima ti prendi 12,4 minuti per leggerlo.
Beneficiare di Power Query durante la creazione di un sistema tramite il Fast Excel Development Template
In questo articolo spiegherò come controllare i risultati di un sistema di pianificazione. Ti offro uno strumento di accompagnamento che puoi usare e adattare alle tue esigenze.
Ma c’è di più. Sto anche condividendo come Power Query e Fast Excel Development Template funzionano bene insieme. Dall’introduzione dell’automazione Fast Excel Development Template nel 2020, non è richiesta alcuna codifica VBA per creare uno strumento Excel completamente automatizzato, ora con l’integrazione di Power Query il numero di formule da utilizzare è drasticamente ridotto.
Power Query è stato aggiunto per la prima volta a Excel nel 2013 come componente aggiuntivo, è disponibile per Excel 2010 e versioni successive, può connettersi a diverse fonti di dati, può gestire tabelle molto grandi (aggirando il limite di circa 1 milione di record di un foglio di lavoro Excel), ha un’interfaccia utente intuitiva in cui è possibile completare in modo semplice una serie di manipolazioni di dati. Dietro le quinte, l’interfaccia utente grafica è alimentata dal linguaggio M, un potente linguaggio di elaborazione dati. Naturalmente, il linguaggio M è accessibile agli utenti interessati alla codifica. Il Web è pieno di utili risorse di Power Query/linguaggio M, quindi gli assistenti AI sono in grado di aiutare in modo efficace.

Il modello di sviluppo rapido di Excel è ben noto agli appassionati della pianificazione della produzione: sei modelli, Parameter, Query, Table, Stack, Pivot, ModuleList e una serie di potenti funzionalità che consentono di creare un sistema completamente automatizzato basato su Excel senza codifica VBA, poiché l’automazione scrive il codice per tuo conto.

Tuttavia, quando si tratta di costruire un sistema, è ancora necessario molto lavoro di formule ripetitive quando si esplodono o si impilano tabelle. La buona notizia è che puoi ridurre quella quantità di lavoro con Power Query. Naturalmente c’è una curva di apprendimento da affrontare.
Check Up del Sistema di Pianificazione: la Logica e il Flusso del Processo
Per controllare un sistema di pianificazione esistente, supponendo che il database sia corretto, ovvero livelli di inventario, distinta base, routing, tempi di consegna, dobbiamo:
- valutare la copertura dell’inventario rispetto alla domanda effettiva esplosa attraverso la distinta base
- confrontare gli ordini di produzione e gli ordini di acquisto con la domanda teorica dei requisiti indipendenti (ordini di vendita in questo esempio)
- eseguire una pianificazione rough cut della capacità per analizzare come viene utilizzata la capacità:

In questo esempio, gli ordini di acquisto e di produzione dovrebbero essere generati dal sistema di pianificazione ERP ed esportati in un unico file txt.
Per semplicità, i componenti dell’ordine di produzione dovrebbero essere gli stessi elencati nella distinta base senza allocazione preventiva dell’inventario o consumo parziale. Ciò significa che il calcolo del consumo dei componenti dell’ordine di produzione è una semplice esplosione della distinta base a un livello anticipata dal lead time.
Allo stesso modo, gli ordini di vendita, le scorte in magazzino, il routing e la distinta base dovrebbero essere esportati dall’ERP come file txt.
Il calendario delle festività, i centri di lavoro e la turnazione settimanale sono parametri gestiti nello strumento.
I risultati dell’analisi sono:
- valutazione della copertura dell’inventario
- disponibilità generata dagli ordini di produzione e di acquisto
- capacità vs. carico di lavoro nominale dovuto alla domanda
- capacità rispetto al carico di lavoro degli ordini di produzione esistenti

I passaggi del calcolo sono:
- Generazione della distinta base del prodotto mediante l’elaborazione della distinta base
- MRP, tenendo conto dell’inventario, degli ordini di vendita e dei tempi di consegna
- calcoli di disponibilità, considerando la domanda netta dal MRP, le forniture dagli ordini di acquisto e di produzione, il consumo dai componenti dell’ordine di produzione
- Calcoli del carico di lavoro, da percorsi, domanda netta e ordini di produzione
- Calcolo della capacità di ogni centro di lavoro in base ai turni settimanali e al calendario delle festività
- Confronto tra carico di lavoro e capacità, sia nominale dai requisiti netti MRP che effettivo in base al carico di lavoro degli ordini di produzione

Per quanto riguarda i calcoli della disponibilità, i passaggi sono illustrati nelle immagini sottostanti: questa tecnica è stata spiegata più volte nei nostri contenuti, ho persino pubblicato come utilizzarla per calcolare una semplice pianificazione a capacità finita in avanti.



Dettagli tecnici della build e suggerimenti per la costruzione
Da un punto di vista tecnico, lo strumento è composto da 5 moduli, o cartelle di lavoro se preferite, come spiegato di seguito.
- PlanCheckUppq_v4.xlsm, con i dati relativi a centro di lavoro, turni e festività, avvia in sequenza le altre quattro cartelle di lavoro e mostra i quattro report finali: copertura inventario, disponibilità, capacità rispetto al carico di lavoro della domanda, capacità rispetto al carico di lavoro degli ordini di produzione.
- ProductBOMpq_v4.xlsm: genera il BOM del prodotto (Product-Parent-Component) dal BOM tradizionale (Parent-Component), esplora 20 livelli BOM partendo dal livello 0; non si tratta di un algoritmo ricorsivo del BOM del prodotto, tuttavia nel download è incluso un processore BOM ricorsivo del linguaggio M, la cartella di lavoro ProductBOMpqRec
- MRPpq_v5.xlsm: Calcoli MRP con esplosione in Power Query e calcoli della domanda netta e dei tempi di consegna in Excel
- Availabilitypq_v2.xlsm: calcola il consumo dei componenti dell’ordine di produzione, somma le scorte e la domanda e con il metodo cumulativo calcola la data di disponibilità per ogni domanda netta dal MRP
- RCCPpq_v3.xlsm: pianificazione rough cut della capacità, ovvero disponibilità dei centri di lavoro da turni e festività, carichi di lavoro da percorsi, domanda netta MRP e da ordini di produzione
I moduli sopra indicati scambiano dati tramite file di testo nella cartella Dati.
Di seguito è riportato il foglio di lavoro ModuleList di PlanCheckUppq che avvia le altre quattro cartelle di lavoro.

Le caratteristiche principali del modello di sviluppo rapido di Excel che ho utilizzato sono i modelli di fogli di lavoro Query, Parameter, Pivot e ModuleList e, naturalmente, l’automazione.
Di recente abbiamo aggiunto la sincronizzazione automatica nei parametri del foglio di lavoro PARA e nei parametri di Power Query, disponibile in questo strumento basato su FEDT 4.4.12: l’ho usata per comporre il percorso dati in Power Query. Di seguito troverete maggiori informazioni su questa funzionalità.
Nota che non ho usato alcun modello Table and Stack: nelle build tradizionali di Fast Excel Development Template questi due modelli eseguono i calcoli più pesanti, in questa build ho fatto le esplosioni e gli stacking tramite Power Query, senza scrivere alcuna formula. A proposito, nel gergo di Power Query “exploding” si chiama “merging” e “stacking” si chiama “appending”.
E che dire di Power Query? Ecco alcune note su come l’ho usato.
Per accedere all’editor di Power Query, puoi selezionare la scheda Dati dalla barra multifunzione, quindi Ottieni dati, Avvia l’editor di Power Query o premere Alt+F12. Inoltre, Dati e query e connessioni aprono un riquadro sulla destra e puoi andare direttamente a modificarne una nelle query elencate facendo doppio clic su di essa o eseguendo una serie di azioni tramite il menu a discesa con clic destro, incluso l’accesso al riquadro Proprietà.
Come già accennato, Power Query esegue tutte le operazioni di unione e aggiunta necessarie.
Ma anche il recupero dei dati avviene in Power Query. Considerate anche che non ho caricato alcun dato su un foglio di lavoro a meno che non fosse necessario per eseguire calcoli più facili da fare in Excel o per mostrare report o per esportare un file txt.
Quali sono i calcoli più facili da fare in Excel?
Bene, tutto si riduce alla conoscenza del linguaggio M da parte dell’utente: quando si tratta di manipolazione dei dati, M è di gran lunga più potente di Excel.
Per il momento, la mia regola generale è di usare Power Query il più possibile tramite l’interfaccia utente grafica, più qualche piccola modifica M per migliorare le prestazioni o per evitare di restituire i dati a un foglio di lavoro e ripristinarli con semplici calcoli aggiuntivi eseguiti dalle formule di Excel.
Un esempio pratico è il modulo MRP: ho eseguito l’esplosione dell’ordine di vendita/distinta base del prodotto tramite Power Query e i calcoli della domanda netta tramite Excel.


Esaminiamo i calcoli più complicati del linguaggio M che si trovano nello strumento.
Ma prima di tutto un consiglio importante: DISATTIVARE L’AGGIORNAMENTO IN BACKGROUND deselezionando Enable Background Refresh nelle Proprietà della query, l’ultima voce nel menu a discesa del tasto destro. Se non lo fai, l’automazione FEDT potrebbe funzionare male e generare messaggi di errore.

Unpivoting the Shift Patterns: ho reso il foglio di lavoro dei parametri facile da leggere e modificare da un essere umano, ma quel formato tabulare non è adatto per i calcoli. Power Query rende molto facile trasformare quello originale unpivotandolo tramite Transform, Unpivot Columns in una tabella normalizzata pronta per l’elaborazione dei dati. Qui sotto la tabella originale e quella unpivotata.

Arresto dell’esplosione BOM nei calcoli ProductBOM: questo è il più complicato, conta le righe del livello BOM precedente, se maggiore di 0 calcola il livello successivo altrimenti restituisce il livello BOM vuoto precedente (il che significa che non è necessaria alcuna elaborazione)
if Table.RowCount(PreviousLevel) > 0 then
<Do the explosion>
else PreviousLevel
ProductBOMpq esplora 20 livelli in sequenza a partire dal livello 0. Ho incluso nel bundle di download anche una versione ricorsiva di Power Query, ProductBOMpqRec, che è più lenta della precedente e il cui codice M è piuttosto complicato: puoi scambiare i due moduli ProductBOM nella cartella di lavoro PlanCheckUp o semplicemente testarli da soli, se preferisci.
Dobbiamo creare un calendario a partire dai turni e dall’elenco delle festività, dalla data più vicina a quella più lontana dei carichi di lavoro del centro di lavoro.
Ecco come fare.
Per prima cosa, crea una query vuota, dalle un nome sensato (io ho usato MinDate e MaxDate) e digita
= Table.Min(RCCPWorkload, each [Date])
e
= Table.Max(RCCPWorkload, each [Date])
dove RCCPWorkload è la tabella e Date è la colonna per la quale si desidera il minimo o il massimo.
Quindi fai clic con il pulsante destro del mouse sul campo desiderato e seleziona Drill Down, in questo caso [Data].
Da ora in poi il nome della query restituirà un oggetto che può essere utilizzato come se fosse il valore restituito da una funzione, nel mio caso la data più vecchia e quella più vecchia.
Ora per creare l’elenco delle date del calendario, inizia di nuovo con una query vuota e digita:
= {Number.From(MinDate)..Number.From(MaxDate)}
Questo è il linguaggio M per generare un elenco da MinDate a MaxDate. Notare le parentesi graffe, utilizzate nel linguaggio M per gli elenchi.
Abbiamo bisogno anche del giorno della settimana: in Excel 1 è il 1° gennaio 1900, che era domenica, 2 è lunedì e così via, quindi il resto della divisione dell’intero per 7 ci dà il giorno della settimana da domenica a venerdì e 0 per sabato; tuttavia voglio che 1 sia lunedì e domenica 7: possiamo ottenere questo risultato se sottraiamo 1 alla data che ci interessa e sostituiamo lo 0 con un 7.
Nel linguaggio M questo è:
=if Number.Mod([Date] -1, 7) = 0 then 7 else Number.Mod([Date]-1, 7)
In questa build non ho calcolato il lunedì della settimana in Power Query, ma potrebbe esserti utile:
=Number.IntegerDivide([Date] -2, 7)*7 + 2
Per le persone più inclini alla programmazione: il linguaggio M include una serie di funzioni per la manipolazione delle date, ma non le ho ancora testate.
Di seguito altri suggerimenti utili.
- Parametri PARA e parametri Power Query: FEDT genera automaticamente un parametro Power Query gemello per ogni parametro PARA di tipo folderpath, ovvero contenente “/” o “\”; inoltre, se è presente un parametro Power Query con lo stesso nome di un parametro PARA, il valore del primo viene automaticamente aggiornato al valore del secondo.
- Origini dati parametriche in Power Query: quando un’origine esterna viene importata in Power Query, cliccando sull’ingranaggio accanto a “Origine” è possibile, tramite la scheda Avanzate, comporre il percorso tramite un mix di parametri e stringhe. Io ho utilizzato il parametro PARA_FilePath_Data_Local e il nome del file di testo come mostrato di seguito.

- Importazione di una tabella di Excel in Power Query: fare clic in un punto qualsiasi della tabella e nel menu Dati fare clic su Da tabella/intervallo; tenere presente che se l’intervallo non è una tabella di Excel, la tabella verrà generata automaticamente
- Caricamento di una tabella di Power Query in un modello di query: selezionare la cella A10, quindi fare clic con il pulsante destro del mouse sulla connessione e scegliere Carica in => Tabella, Foglio di lavoro esistente; attenzione: assicurarsi che la prima colonna NON includa valori nulli, perché ciò romperà il Query Template
- Caricamento di una tabella Power Query in un modello pivot: rimuovere la tabella pivot predefinita fornita con il modello, selezionare la cella A10, quindi fare clic con il pulsante destro del mouse sulla connessione e scegliere Carica in => report Tabella pivot
- Raggruppamento di Power Query e ridenominazione dei passaggi: è possibile creare gruppi in Power Query, funzionano come cartelle e sono utili per raggruppare le query per motivi di leggibilità, inoltre è possibile aggiungere una descrizione a ciascuna query (Proprietà) e rinominare ogni passaggio di una query. Mi piace usare il raggruppamento, preferisco leggere il linguaggio M quando si tratta di capire cosa fa un passaggio di query.
Lo strumento: PlanCheckUp
Puoi scaricare lo strumento compilando il seguente modulo: il link per il download verrà inviato rapidamente all’indirizzo email specificato. Nessuna email di spam, promesso.
Your Planning System Needs a Check Up – Download
Prenditi il tempo necessario per eseguire i passi per farlo funzionare sul tuo computer.
- Utilizza il form qui sopra per accedere al download.
- Scarica la cartella ZIP e decomprimila direttamente sul tuo C Drive.
- Dovresti avere la seguente cartella: C:\P-S_PlanCheckUp e C:\P-S_PlanCheckUp\Data
- La prima cartella contiene le cartelle di lavoro (moduli) sopra menzionate + il FEDT 4.4.12, la seconda i file di testo di input + il file di testo scambiato dai moduli
- Apri lo strumento: PlanCheckUppq_v4.xlsm
- Premere il pulsante “Update from Local”.
Questo è tutto. Verrà eseguito con dati fittizi.
Utilizza questo set di dati per conoscere lo strumento.
Quando sei pronto…
Per eseguire con i tuoi dati, sostituisci i file di input nel percorso: C:\ P-S_PlanCheckUp\Data
Nota: puoi riutilizzare ogni modulo nelle tue creazioni come se fossero mattoncini Lego.
Concludendo
Hai imparato come controllare il tuo sistema di pianificazione.
Scopri come puoi trarre vantaggio dall’integrazione tra Power Query e il Fast Excel Development Template.
Hai scaricato un sistema che include gli strumenti di pianificazione dei materiali e di pianificazione approssimativa della capacità, tramite i quali puoi diagnosticare il tuo attuale sistema di pianificazione.
I tuoi prossimi passi:
- Esegui lo strumento con i tuoi dati
- Sperimenta con Power Query
- Costruisci il tuo strumento
- Riutilizza i moduli per creare altri tuoi strumenti
E se hai bisogno di aiuto o vuoi segnalare un bug, condividere i tuoi pensieri o dare un feedback, invia un’e-mail a [email protected]: i tuoi commenti sono molto benvenuti.