Come automatizzare il risolutore Excel

In Come ottenere di più da Excel Solver ho trattato le tecniche per migliorare il tuo modello e potenziare la tua analisi. Uno degli argomenti su cui ho ricevuto più domande è l'automazione del risolutore per risolvere più valori.

C'era una breve spiegazione nell'articolo precedente, ma nulla dedicato a come implementarlo dall'inizio alla fine. In questo articolo andremo in profondità dall'inizio alla fine coprendo i concetti e i metodi passo dopo passo. Alla fine sarai in grado di automatizzare qualsiasi tuo modello personale e ridimensionare la tua analisi.

Come lo faremo?

Sfrutteremo il registratore di macro in Excel per gestire la maggior parte del codice effettivo. Lo strumento macro di registrazione consentirà a Excel di convertire i nostri passaggi manuali in codice VBA.

Identificheremo quindi i pezzi chiave del codice da modificare. Quindi creeremo un ciclo per selezionare e sostituire dinamicamente questi valori ed eseguire nuovamente il risolutore.

Questo sarà semi-tecnico, ma attraverso questo stile di sviluppo possiamo concentrarci sul processo e sui concetti invece di concentrarci su come scrivere codice.

Configurazione del registratore di macro

La funzionalità del record macro si trova nella scheda sviluppatore della barra multifunzione. Questo non è disponibile per impostazione predefinita, ma è facile da implementare. Se accedi alle opzioni della barra multifunzione, vedrai una casella di controllo per la scheda sviluppatore.

Lo screenshot qui sotto è per Mac ma Excel basato su PC è simile.

Se non hai installato il componente aggiuntivo del risolutore, puoi installarlo tramite il menu Componenti aggiuntivi. Il sito di supporto Microsoft contiene istruzioni per tutte le piattaforme, Carica il componente aggiuntivo Risolutore in Excel.

Registrazione della macro

Ora che il set up è completo siamo pronti per registrare! Imposta il tuo spazio di lavoro in modo appropriato con tutte le tue formule e riferimenti. Ti consigliamo di impostare tutto al punto prima di costruire il modello del risolutore.

Torna alla scheda sviluppatore sulla barra multifunzione. Vedrai un pezzo di carta con un punto rosso. Fare clic su quel pulsante per aprire il registratore.

Dai un nome alla tua macro e, se lo desideri, inserisci la descrizione.

Una volta premuto Ok, il registratore inizierà.

Segui i passaggi per impostare il tuo risolutore e risolvilo. Una volta completato, torna alla scheda sviluppatore e interrompi la registrazione. Sarà la stessa posizione del pulsante che hai usato per avviare la registrazione.

Ora che abbiamo il codice di base, possiamo ripulirlo e prepararlo per i passaggi successivi.

Pulisci il codice

Ora possiamo modificare il codice, rimuovere le parti non necessarie e assicurarci di utilizzare solo le parti essenziali della registrazione macro.

Puoi modificare il codice tramite l'editor VBA di Excel. Per accedere al codice registrato, fai clic sul pulsante Macro.

Passa alla tua macro nel nuovo menu e fai clic su modifica.

Ora che abbiamo il nostro editor di codice aperto, possiamo iniziare a fare modifiche.

Rimozione del codice non necessario

Se ti è capitato di fare clic su un sacco di altre cose durante la registrazione, puoi cancellarle ora e rimanere con qualcosa di simile a quello riportato di seguito. Se c'è molto codice extra e sei preoccupato di romperlo, puoi sempre registrare di nuovo.

Impostazione dei riferimenti del risolutore

Un ultimo passaggio prima di procedere, vai su Strumenti > Riferimenti e seleziona Risolutore. Se non lo fai il pacchetto non verrà caricato nella macro e riceverai un errore.

Ora puoi modificare i tuoi parametri, aprire il menu delle macro, selezionare la tua macro, premere Esegui e aggiornerà i risultati.

Allineiamo il codice al nostro modello di risolutore. Vedrai come si allinea. La lingua è diversa, ma puoi mettere insieme cosa significa tutto questo.

Successivamente isoleremo le parti che dobbiamo modificare e concluderemo la nostra automazione.

Risciacquare e ripetere: costruire il ciclo

Ora che abbiamo un risolutore funzionante. Dobbiamo ripeterlo più volte. Mettiamo insieme un esempio e cementiamo il piano.

Abbiamo iniziato con un budget di $ 1.000 nel primo modello, ma vogliamo fare la stessa cosa per incrementi di $ 100 fino a $ 2.500.

Immergiamoci! Abbiamo un elenco di valori nella colonna B e il nostro output nella colonna C. Se scrivessimo il processo sembrerebbe,

1. Imposta la spesa totale sul valore in B10.
2. Risolvi il modello.
3. Posiziona l'output in C10.
4. Scegli il prossimo valore B.
5. Imposta l'output nella riga C.
6. Ripetere i passaggi 2-5 per tutti i valori in B.

Avvio del ciclo

I loop sono costrutti di codifica che ripetono un'azione fino a raggiungere un punto finale logico. Un ciclo tornerà utile qui per ripetere lo stesso processo, eseguendo il risolutore, su ogni valore fino a raggiungere la fine del valore.

Per questo esempio definiremo il punto finale in base al numero di righe. B10: B25 ha 16 valori.

Inizieremo il nostro ciclo e lo avvolgeremo attorno al nostro codice risolutore. Imposteremo una variabile per contenere il numero del ciclo (i) e passare attraverso ogni valore, risolvendo il ciclo ogni volta.

Ripristino del risolutore

Aggiungeremo un'altra condizione all'inizio del nostro codice. Poiché vogliamo rieseguire il risolutore ad ogni iterazione, lo reimposteremo ad ogni ciclo. Questo cancellerà le impostazioni e avvierà un nuovo modello.

Possiamo farlo tramite SolverReset .

Aggiornamento dei riferimenti di cella

Ora risolviamo sedici volte, ma dobbiamo aggiornare i nostri riferimenti di cella. Mentre originariamente facevamo riferimento a B10, dobbiamo spostare verso il basso di una cella ogni corsa per aggiornare i limiti. Possiamo farlo tramite la funzione offset. Possiamo aggiornare "$ B $ 10" per fare riferimento alla cella, quindi eseguire l'offset di i righe (0 alla prima esecuzione, 1 riga alla seconda e così via). Ogni volta che il ciclo viene eseguito, i aumenta di uno.

Raccolta dell'output

L'output cambierà ogni volta, quindi vorremo salvarlo. Possiamo seguire lo stesso processo sopra ma offset da C10 e impostarlo uguale al valore del modello risolto. Quindi il ciclo ricomincia.

Chiameremo C10 offset per il numero appropriato di righe, quindi incolleremo il valore del nostro output.

Eludere le notifiche del risolutore

Salviamoci un mal di testa e non impariamo dall'esempio per un momento. Ricordi quando hai eseguito il risolutore e quel menu è apparso chiedendo se desideri accettare la soluzione? Questo apparirà ogni volta se non lo disabiliti. Immagina se imposti un ciclo con 100 di valori, dovresti fare clic su Accetta ogni volta!

Vai avanti e aggiungi le seguenti righe dopo il codice del risolutore e aggiungi (TRUE) a SolverSolve. Questo imiterà l'accettazione delle modifiche.

Ora abbiamo il codice finito!

Installato e funzionante

Ora possiamo eseguire il codice selezionandolo dall'elenco delle macro ed eseguendo. Se desideri utilizzare la tua cartella di lavoro come modello, puoi assegnare la macro a un pulsante per facilità d'uso. Puoi trovarli nella scheda sviluppatore.

Puoi anche aggiungere eventuali grafici di confronto. In questo caso vogliamo confrontare il tasso di crescita della spesa con la crescita del volume di conversione. Questo esempio non è interessante ma i dati reali possono aiutare a rivelare i punti di interruzione dell'efficienza

Conclusione

Abbiamo coperto un bel po'! Congratulazioni per aver installato e funzionante il tuo risolutore automatico. L'esempio che abbiamo trattato era eccessivamente semplicistico, ma puoi prendere gli stessi concetti e applicarli a qualsiasi modello di risolutore.

Ciò può far risparmiare un'enorme quantità di tempo per i modelli che devi risolvere più volte e incoraggiare gli utenti a sperimentare scenari diversi.

Data di pubblicazione originale: 30/07/2019