Cos'è il Risolutore di Excel?

2024 Autore: Abigail Brown | [email protected]. Ultima modifica: 2023-12-17 06:52

Il componente aggiuntivo Risolutore di Excel esegue l'ottimizzazione matematica. Questo è in genere usato per adattare modelli complessi ai dati o trovare soluzioni iterative ai problemi. Ad esempio, potresti voler adattare una curva attraverso alcuni punti dati, usando un'equazione. Il risolutore può trovare le costanti nell'equazione che si adattano meglio ai dati. Un' altra applicazione è quando è difficile riorganizzare un modello per rendere l'output richiesto oggetto di un'equazione.

Dov'è il Risolutore in Excel?

Il componente aggiuntivo Risolutore è incluso in Excel ma non viene sempre caricato come parte di un'installazione predefinita. Per verificare se è caricato, seleziona la scheda DATA e cerca l'icona Solver nella sezione Analysis.

Se non riesci a trovare il Risolutore nella scheda DATI, dovrai caricare il componente aggiuntivo:

1. Seleziona la scheda FILE e poi seleziona Opzioni.

   

2. Nella finestra di dialogo Opzioni seleziona Add-Ins dalle schede sul lato sinistro.

   

3. Nella parte inferiore della finestra, seleziona Excel Add-ins dal menu a discesa Gestisci e seleziona Vai…

   

4. Seleziona la casella di controllo accanto a Componente aggiuntivo Risolutore e seleziona OK.

   

5. Il comando Solver dovrebbe ora apparire nella scheda DATA. Sei pronto per usare Risolutore.

   

Utilizzo del Risolutore in Excel

Iniziamo con un semplice esempio per capire cosa fa il Risolutore. Immagina di voler sapere quale raggio darà a un cerchio con un'area di 50 unità quadrate. Conosciamo l'equazione per l'area di un cerchio (A=pi r²). Potremmo, ovviamente, riorganizzare questa equazione per fornire il raggio richiesto per una data area, ma per esempio facciamo finta di non sapere come farlo.

Crea un foglio di calcolo con il raggio in B1 e calcola l'area in B2 usando l'equazione =pi()B1^2.

Potremmo regolare manualmente il valore in B1 fino a quando B2 non mostra un valore sufficientemente vicino a 50. A seconda di quanto siamo precisi necessario, questo potrebbe essere un approccio pratico. Tuttavia, se dobbiamo essere molto precisi, ci vorrà molto tempo per apportare le modifiche richieste. In re altà, questo è essenzialmente ciò che fa il Risolutore. Apporta modifiche ai valori in alcune celle e controlla il valore in una cella target:

1. Seleziona DATA tab e Solver, per caricare la Solver Parameters finestra di dialogo

2. Imposta la cella Obiettivo come Area, B2. Questo è il valore che verrà controllato, regolando le altre celle finché questa non raggiunge il valore corretto.

   

3. Seleziona il pulsante per Valore di: e imposta un valore di 50. Questo è il valore che B2 dovrebbe raggiungere.

   

4. Nella casella intitolata Modificando le celle variabili: inserisci la cella contenente il raggio, B1.

   

5. Lascia le altre opzioni come sono di default e seleziona Risolvi. L'ottimizzazione viene eseguita, il valore di B1 viene regolato fino a quando B2 non è 50 e viene visualizzata la finestra di dialogo Risultati Risolutore.

   

6. Seleziona OK per mantenere la soluzione.

   

Questo semplice esempio ha mostrato come funziona il risolutore. In questo caso, avremmo potuto ottenere più facilmente la soluzione in altri modi. Successivamente esamineremo alcuni esempi in cui il Risolutore fornisce soluzioni che sarebbero difficili da trovare in qualsiasi altro modo.

Adattamento di un modello complesso utilizzando il componente aggiuntivo Risolutore di Excel

Excel ha una funzione incorporata per eseguire la regressione lineare, adattando una linea retta a un insieme di dati. Molte funzioni non lineari comuni possono essere linearizzate, il che significa che la regressione lineare può essere utilizzata per adattare funzioni come gli esponenziali. Per funzioni più complesse, il Risolutore può essere utilizzato per eseguire una "minimizzazione dei minimi quadrati". In questo esempio, considereremo di adattare un'equazione della forma ax^b+cx^d ai dati mostrati di seguito.

Ciò comporta i seguenti passaggi:

1. Disponi il set di dati con i valori x nella colonna A e i valori y nella colonna B.
2. Crea i 4 valori dei coefficienti (a, b, c e d) da qualche parte sul foglio di calcolo, a questi possono essere assegnati valori iniziali arbitrari.

3. Crea una colonna di valori Y adattati, usando un'equazione della forma ax^b+cx^d che fa riferimento ai coefficienti creati nel passaggio 2 e ai valori x nella colonna A. Nota che per copiare la formula in basso nella colonna, i riferimenti ai coefficienti devono essere assoluti mentre i riferimenti ai valori x devono essere relativi.

   

4. Sebbene non sia essenziale, puoi ottenere un'indicazione visiva di quanto sia buona l'equazione tracciando entrambe le colonne y rispetto ai valori x su un singolo grafico a dispersione XY. Ha senso utilizzare indicatori per i punti dati originali, poiché si tratta di valori discreti con rumore, e utilizzare una linea per l'equazione adattata.

   

5. Successivamente, abbiamo bisogno di un modo per quantificare la differenza tra i dati e la nostra equazione adattata. Il modo standard per farlo è calcolare la somma delle differenze al quadrato. In una terza colonna, per ogni riga, il valore dei dati originali per Y viene sottratto dal valore dell'equazione adattata e il risultato viene quadrato. Quindi, in D2, il valore è dato da =(C2-B2)^2 Viene quindi calcolata la somma di tutti questi valori al quadrato. Poiché i valori sono al quadrato, possono essere solo positivi.

   

6. Ora sei pronto per eseguire l'ottimizzazione usando il Risolutore. Ci sono quattro coefficienti che devono essere regolati (a, b, c e d). Hai anche un unico valore obiettivo da minimizzare, la somma delle differenze al quadrato. Avvia il risolutore, come sopra, e imposta i parametri del risolutore per fare riferimento a questi valori, come mostrato di seguito.

   

7. Deseleziona l'opzione per Rendi variabili non vincolate non negative, questo costringerebbe tutti i coefficienti ad assumere valori positivi.

   

8. Seleziona Risolvi e rivedi i risultati. Il grafico si aggiornerà dando una buona indicazione della bontà dell'adattamento. Se il risolutore non produce un buon adattamento al primo tentativo, puoi provare a eseguirlo di nuovo. Se l'adattamento è migliorato, provare a risolvere dai valori correnti. Altrimenti, potresti provare a migliorare manualmente l'adattamento prima di risolvere.

   

9. Una volta ottenuto un buon adattamento, puoi uscire dal risolutore.

Risolvere un modello in modo iterativo

A volte c'è un'equazione relativamente semplice che fornisce un output in termini di input. Tuttavia, quando si tenta di invertire il problema non è possibile trovare una soluzione semplice. Ad esempio, la potenza consumata da un veicolo è approssimativamente data da P=av + bv^3 dove v è la velocità, a è un coefficiente per la resistenza al rotolamento e b è un coefficiente per resistenza aerodinamica. Sebbene questa sia un'equazione abbastanza semplice, non è facile riorganizzare per fornire un'equazione della velocità che il veicolo raggiungerà per una data potenza assorbita. Possiamo, tuttavia, utilizzare il Risolutore per trovare iterativamente questa velocità. Ad esempio, trova la velocità raggiunta con una potenza assorbita di 740 W.

1. Imposta un semplice foglio di calcolo con la velocità, i coefficienti aeb e la potenza calcolata da essi.

   

2. Lancia il Risolutore e inserisci il potere, B5, come obiettivo. Impostare un valore obiettivo di 740 e selezionare la velocità, B2, come celle variabili da modificare. Seleziona risolvi per avviare la soluzione.

   

3. Il risolutore regola il valore della velocità fino a quando la potenza non è molto vicina a 740, fornendo la velocità di cui abbiamo bisogno.

   

4. Risolvere i modelli in questo modo può essere spesso più veloce e meno soggetto a errori rispetto all'inversione di modelli complessi.

Capire le diverse opzioni disponibili nel risolutore può essere piuttosto difficile. Se hai difficoltà a ottenere una soluzione sensata, è spesso utile applicare condizioni al contorno alle celle modificabili. Si tratta di valori limite oltre i quali non dovrebbero essere regolati. Ad esempio, nell'esempio precedente, la velocità non dovrebbe essere inferiore a zero e sarebbe anche possibile impostare un limite superiore. Questa sarebbe una velocità di cui sei abbastanza sicuro che il veicolo non possa andare più veloce. Se sei in grado di impostare i limiti per le celle variabili modificabili, fa funzionare meglio anche altre opzioni più avanzate, come multistart. Questo eseguirà una serie di soluzioni diverse, a partire da valori iniziali diversi per le variabili.

Anche la scelta del metodo di risoluzione può essere difficile. Simplex LP è adatto solo per modelli lineari, se il problema non è lineare fallirà con un messaggio che questa condizione non è stata soddisfatta. Gli altri due metodi sono entrambi adatti a metodi non lineari. GRG Nonlinear è il più veloce ma la sua soluzione può dipendere fortemente dalle condizioni iniziali di partenza. Ha la flessibilità di non richiedere che le variabili abbiano dei limiti impostati. Il risolutore evolutivo è spesso il più affidabile, ma richiede che tutte le variabili abbiano limiti superiori e inferiori, che possono essere difficili da elaborare in anticipo.

Il componente aggiuntivo Risolutore di Excel è uno strumento molto potente che può essere applicato a molti problemi pratici. Per accedere completamente alla potenza di Excel, prova a combinare il Risolutore con le macro di Excel.