Utilizzando una funzione booleana if examples. Funzionalità di Microsoft Excel: ricerca di una soluzione
Risoluzione di equazioni e sistemi non lineari "
scopo del lavoro: Esplorazione delle capacità di Ms Excel 2007 nella risoluzione di equazioni e sistemi non lineari. Acquisizione di competenze nella risoluzione di equazioni e sistemi non lineari utilizzando il pacchetto.
Esercizio 1. Trova le radici del polinomio x 3 - 0,01x 2 - 0,7044x + 0,139104 \u003d 0.
Per prima cosa, risolviamo graficamente l'equazione. È noto che la soluzione grafica dell'equazione f (x) \u003d 0 è il punto di intersezione del grafico della funzione f (x) con l'ascissa, cioè un tale valore di x al quale la funzione svanisce.
Tabuliamo il nostro polinomio sull'intervallo da -1 a 1 con un passo di 0.2. I risultati del calcolo sono mostrati in Fig., Dove la formula è stata inserita nella cella B2: \u003d A2 ^ 3 - 0,01 * A2 ^ 2 - 0,7044 * A2 + 0,139104. Il grafico mostra che la funzione attraversa tre volte l'asse del Bue e poiché il polinomio di terzo grado non ha più di tre radici reali, è stata trovata una soluzione grafica al problema. In altre parole, le radici erano localizzate, ad es. si determinano gli intervalli su cui si trovano le radici di questo polinomio: [-1, -0.8] e.
Ora puoi trovare le radici del polinomio per approssimazioni successive usando il comando Dati → Utilizzo dei dati → Analisi what-if → Selezione parametri.
Dopo aver inserito le approssimazioni e i valori iniziali della funzione, è possibile passare al comando Dati → Utilizzo dei dati → Analisi what-if → Selezione parametri e popolare la finestra di dialogo come segue.
In campo Situato nella cellaviene dato un riferimento alla cella in cui è inserita la formula che calcola il valore del lato sinistro dell'equazione (l'equazione deve essere scritta in modo che il suo lato destro non contenga una variabile). In campo Valore entriamo nella parte destra dell'equazione e nel campo Modifica dei valori delle celle viene fornito un riferimento alla cella assegnata alla variabile. Notare che inserendo i riferimenti di cella nei campi della finestra di dialogo Selezione dei parametri è più conveniente non dalla tastiera, ma cliccando sulla cella corrispondente.
Dopo aver premuto il pulsante OK, verrà visualizzata la finestra di dialogo Risultato della selezione dei parametri con un messaggio sul completamento con successo della ricerca di una soluzione, il valore approssimativo della radice verrà inserito nella cella A14.
Troviamo le due radici rimanenti allo stesso modo. I risultati del calcolo verranno inseriti nelle celle A15 e A16.
Attività 2. Risolvi l'equazione e x - (2x - 1) 2 = 0.
Eseguiamo la localizzazione delle radici dell'equazione non lineare.
Per fare ciò, lo rappresentiamo nella forma f (x) \u003d g (x), ad es. e x \u003d (2x - 1) 2 of (x) \u003d e x, g (x) \u003d (2x - 1) 2 e risolvi graficamente.
La soluzione grafica dell'equazione f (x) \u003d g (x) sarà il punto di intersezione delle rette f (x) e g (x).
Costruiamo i grafici f (x) eg (x). A tale scopo, immettere i valori dell'argomento nell'intervallo A3: A18. Nella cella B3 inseriamo la formula per calcolare i valori della funzione f (x): \u003d EXP (A3) e in C3 per calcolare g (x): \u003d (2 * A3-1) ^ 2.
Risultati del calcolo e rappresentazione grafica di f (x) eg (x):
Il grafico mostra che le linee f (x) eg (x) si intersecano due volte, ad es. questa equazione ha due soluzioni. Uno di questi è banale e può essere calcolato esattamente:
Per il secondo, è possibile determinare l'intervallo di isolamento delle radici: 1.5< x < 2.
Ora puoi trovare la radice dell'equazione sul segmento con il metodo delle approssimazioni successive.
Inseriamo l'approssimazione iniziale nella cella H17 \u003d 1.5, e l'equazione stessa, con riferimento all'approssimazione iniziale, nella cella I17 \u003d EXP (H17) - (2 * H17-1) ^ 2.
e compila la finestra di dialogo Selezione dei parametri.
Il risultato della ricerca di una soluzione verrà visualizzato nella cella H17.
L'obiettivo3 . Risolvi il sistema di equazioni:
Prima di utilizzare i metodi sopra descritti per la risoluzione di sistemi di equazioni, troveremo una soluzione grafica a questo sistema. Si noti che entrambe le equazioni del sistema sono date implicitamente e per costruire grafici di funzioni corrispondenti a queste equazioni, è necessario risolvere le equazioni date rispetto alla variabile y.
Per la prima equazione del sistema, abbiamo:
Scopriamo l'ODV della funzione risultante:
La seconda equazione di questo sistema descrive un cerchio.
Frammento di foglio di lavoro MS Excel con formule che devono essere inserite nelle celle per costruire linee descritte dalle equazioni del sistema. I punti di intersezione delle linee rappresentate sono una soluzione grafica a un sistema di equazioni non lineari.
È facile vedere che il sistema dato ha due soluzioni. Pertanto, la procedura per la ricerca di soluzioni al sistema deve essere eseguita due volte, avendo preventivamente determinato l'intervallo di isolamento delle radici lungo gli assi Ox e Oy. Nel nostro caso, la prima radice si trova negli intervalli (-0,5; 0) x e (0,5; 1) y, e la seconda è (0; 0,5) x e (-0,5; -1) y. Successivamente, procederemo come segue. Introduciamo i valori iniziali delle variabili x e y, formule che rappresentano le equazioni del sistema e la funzione obiettivo.
Ora useremo due volte il comando Dati → Analisi → Cerca soluzioni, riempiendo le finestre di dialogo che appaiono.
Confrontando la soluzione ottenuta del sistema con quella grafica, ci assicuriamo che il sistema sia risolto correttamente.
Incarichi di auto-aiuto
Esercizio 1... Trova le radici di un polinomio
Assegnazione 2... Trova la soluzione all'equazione non lineare.
Assegnazione 3... Trova la soluzione a un sistema di equazioni non lineari.
Come già sai, le formule in Microsoft Excel consentono di determinare il valore di una funzione dai suoi argomenti. Tuttavia, può verificarsi una situazione in cui il valore della funzione è noto e l'argomento deve essere trovato (cioè per risolvere l'equazione). Per risolvere tali problemi, esiste una funzione speciale Ricerca dell'obiettivo .
Ricerca parametri.
Funzione speciale Ricerca dell'obiettivo consente di definire un parametro (argomento) di una funzione se il suo valore è noto. Quando viene selezionato un parametro, il valore della cella di influenza (parametro) viene modificato fino a quando la formula che dipende da questa cella non restituisce il valore specificato.
Consideriamo la procedura per trovare un parametro usando un semplice esempio: risolviamo l'equazione 10 * x - 10 / x \u003d 15
... Qui il parametro (argomento) - x
... Lascia che sia una cellula A3
... Inseriamo in questa cella qualsiasi numero che rientri nell'ambito della definizione della funzione (nel nostro esempio, questo numero non può essere uguale a zero). Questo valore verrà utilizzato come valore iniziale. Lascia fare 3
... Introduciamo la formula \u003d 10 * A3-10 / A3
, con il quale dovrebbe essere ottenuto il valore richiesto, in una cella, ad esempio, B3
... Ora è possibile avviare la funzione di ricerca parametri selezionando il comando Ricerca dell'obiettivo
sul menu Utensili
... Inserisci i parametri di ricerca:
- In campo Imposta cella inserisci un riferimento alla cella contenente la formula che desideri.
- Immettere il risultato della ricerca nel campo Valorizzare .
- In campo Cambiando cella immettere un riferimento alla cella contenente il valore da abbinare.
- Fare clic sulla chiave ok .
Al termine della funzione apparirà una finestra sullo schermo in cui verranno visualizzati i risultati della ricerca. Il parametro trovato apparirà nella cella che gli era riservata. Prestare attenzione al fatto che nel nostro esempio l'equazione ha due soluzioni e il parametro è selezionato solo uno - questo perché il parametro cambia solo fino a quando non viene restituito il valore richiesto. Il primo argomento trovato in questo modo ci viene restituito come risultato della ricerca. Se specifichiamo come valore iniziale nel nostro esempio -3
, quindi verrà trovata la seconda soluzione dell'equazione: -0,5
.
È difficile determinare correttamente il valore iniziale più adatto. Più spesso possiamo fare alcune ipotesi sul parametro desiderato, ad esempio, il parametro deve essere intero (quindi otteniamo la prima soluzione della nostra equazione) o non positivo (la seconda soluzione).
Il compito di trovare un parametro con condizioni limite imposte sarà aiutato da uno speciale componente aggiuntivo Microsoft Excel Risolutore .
Cerca una soluzione.
Componente aggiuntivo di Microsoft Excel Risolutore non viene installato automaticamente in un'installazione tipica:
- Sul menu Utensili selezionare squadra Componenti aggiuntivi ... Se la finestra di dialogo Componenti aggiuntivi non contiene un comando Risolutore , premi il bottone Navigare e specificare l'unità e la cartella che contiene il file del componente aggiuntivo Solver.xla (di solito questa è la cartella Library \\ Solver ) o eseguire il programma installazioni Microsoft Office se non riesce a trovare il file.
- Nella finestra di dialogo Componenti aggiuntivi selezionare la casella Risolutore .
La procedura per trovare una soluzione permette di trovare il valore ottimale della formula contenuta nella cella, che si chiama target. Questa procedura funziona con un gruppo di celle associate a una formula nella cella di destinazione. La procedura modifica i valori nelle celle influenzanti fino a ottenere il risultato ottimale in base alla formula contenuta nella cella obiettivo. Per restringere il set di valori, vengono applicati vincoli che possono essere riferiti ad altre celle influenti. È inoltre possibile utilizzare la ricerca della soluzione per determinare il valore di una cella influente che corrisponde all'estremità della cella obiettivo, ad esempio, il numero di sessioni di formazione che massimizzano il rendimento scolastico.
Nella finestra di dialogo Risolutore
lo stesso della finestra di dialogo Ricerca dell'obiettivo
, è necessario specificare la cella di destinazione, il suo valore e le celle che devono essere modificate per raggiungere l'obiettivo. Per risolvere i problemi di ottimizzazione, la cella obiettivo deve essere specificata uguale al valore massimo o minimo.
Se fai clic sul pulsante Indovina Excel proverà a trovare tutte le celle che influenzano la formula stessa.
È possibile aggiungere condizioni al contorno facendo clic sulla chiave Inserisci .
Facendo clic sul pulsante Opzioni , è possibile modificare le condizioni per trovare una soluzione: il tempo massimo per trovare una soluzione, il numero di iterazioni, l'accuratezza della soluzione, la tolleranza per le deviazioni dalla soluzione ottima, il metodo di estrapolazione (lineare o quadratica), l'ottimizzazione algoritmo, ecc.
Torniamo all'esempio precedente: per ottenere la seconda soluzione (non positiva) è sufficiente aggiungere la condizione al contorno A3 ... Come nella selezione del parametro, sullo schermo apparirà una finestra in cui verrà visualizzato un report sui risultati della ricerca della soluzione richiesta. La soluzione stessa verrà mostrata nelle celle previste (nella cella A3 il valore viene visualizzato -0.50 ).
Componente aggiuntivo di Microsoft Excel Risolutore
consente inoltre di risolvere sistemi di equazioni o disequazioni. Consideriamo un semplice esempio: proviamo a risolvere il sistema di equazioni
x + y \u003d 2
x - y \u003d 0
Una delle funzionalità più interessanti di programma Microsoft Excel sta trovando una soluzione. Tuttavia, va notato che questo strumento non può essere classificato come il più popolare tra gli utenti in questo allegato... Ma invano. Dopo tutto, questa funzione, utilizzando i dati iniziali, per enumerazione, trova la soluzione più ottimale tra tutte disponibili. Scopriamo come utilizzare la funzionalità Trova soluzione in Microsoft Excel.
È possibile cercare a lungo sul nastro in cui si trova la ricerca di una soluzione, ma non è ancora possibile trovare questo strumento. Semplicemente, per attivare questa funzione, è necessario abilitarla nelle impostazioni del programma.
Per attivare Cerca soluzioni in Microsoft Excel 2010 e versioni successive, vai alla scheda "File". Per la versione 2007, fare clic sul pulsante Microsoft Office nell'angolo in alto a sinistra della finestra. Nella finestra che si apre, vai alla sezione "Parametri".
Nella finestra dei parametri, fare clic sulla voce "Componenti aggiuntivi". Dopo la transizione, nella parte inferiore della finestra, di fronte al parametro "Controllo", selezionare il valore "Componenti aggiuntivi Excel" e fare clic sul pulsante "Vai".
Si apre una finestra con i componenti aggiuntivi. Mettiamo un segno di spunta davanti al nome del componente aggiuntivo di cui abbiamo bisogno - "Cerca una soluzione". Fare clic sul pulsante "OK".
Successivamente, verrà visualizzato un pulsante per avviare la funzione Cerca soluzioni sulla barra multifunzione di Excel nella scheda Dati.
Preparare la tavola
Ora che abbiamo attivato la funzione, vediamo come funziona. Il modo più semplice per immaginarlo è con un esempio specifico. Quindi abbiamo un tavolo salari dipendenti dell'impresa. Dobbiamo calcolare il bonus per ogni dipendente, che è il prodotto dei salari indicati in una colonna separata da un certo coefficiente. Allo stesso tempo, l'importo totale dei fondi stanziati per il premio è pari a 30.000 rubli. La cella in cui si trova questa quantità ha il nome di quella di destinazione, poiché il nostro obiettivo è selezionare i dati esattamente per questo numero.
Il coefficiente che viene utilizzato per calcolare l'importo del premio, dobbiamo calcolare utilizzando la funzione Cerca soluzioni. La cella in cui si trova è chiamata quella desiderata.
La cella obiettivo e la cella obiettivo devono essere collegate tra loro utilizzando una formula. Nel nostro caso particolare, la formula si trova nella cella di destinazione e ha la seguente forma: "\u003d C10 * $ G $ 3", dove $ G $ 3 è l'indirizzo assoluto della cella ricercata e "C10" è il stipendio totale da cui viene calcolato il bonus dipendenti dell'impresa.
Avvio dello strumento Trova soluzione
Dopo che la tabella è stata preparata, nella scheda "Dati", fare clic sul pulsante "Cerca una soluzione", che si trova sulla barra multifunzione nella casella degli strumenti "Analisi".
Si apre la finestra dei parametri, in cui è necessario inserire i dati. Nel campo "Ottimizza la funzione obiettivo" è necessario inserire l'indirizzo della cella obiettivo in cui si troverà l'importo totale del bonus per tutti i dipendenti. Questo può essere fatto digitando le coordinate manualmente o facendo clic sul pulsante situato a sinistra del campo di immissione dei dati.
Successivamente, la finestra dei parametri verrà ridotta a icona e sarà possibile selezionare la cella desiderata della tabella. Quindi, è necessario fare nuovamente clic sullo stesso pulsante a sinistra del modulo con i dati inseriti per espandere nuovamente la finestra dei parametri.
Sotto la finestra con l'indirizzo della cella di destinazione, è necessario impostare i parametri dei valori che saranno in essa. Può essere un valore massimo, minimo o specifico. Nel nostro caso, questa sarà l'ultima opzione. Pertanto, mettiamo l'interruttore nella posizione "Valori" e nel campo a sinistra di esso scriviamo il numero 30.000. Come ricordiamo, questo numero, a seconda delle condizioni, è l'importo totale del bonus per tutti i dipendenti dell'impresa.
Di seguito è riportato il campo "Modifica celle variabili". Qui devi indicare l'indirizzo della cella che stai cercando, dove, come ricordiamo, si trova il coefficiente, moltiplicando lo stipendio base per il quale verrà calcolato l'importo del bonus. L'indirizzo può essere scritto nello stesso modo in cui abbiamo fatto per la cella di destinazione.
Nel campo "In base alle restrizioni", è possibile impostare determinate restrizioni per i dati, ad esempio, rendere i valori interi o non negativi. A tale scopo, fare clic sul pulsante "Aggiungi".
Successivamente, si apre la finestra per l'aggiunta di un vincolo. Nel campo "Collegamento a celle", scrivere l'indirizzo delle celle rispetto alle quali viene introdotto il vincolo. Nel nostro caso, questa è la cella desiderata con un coefficiente. Quindi inseriamo il segno richiesto: "minore o uguale", "maggiore o uguale", "uguale", "intero", "binario", ecc. Nel nostro caso, sceglieremo un segno maggiore o uguale per rendere il coefficiente un numero positivo. Di conseguenza, nel campo "Restrizione", indicare il numero 0. Se si desidera configurare un'altra restrizione, fare clic sul pulsante "Aggiungi". Altrimenti, fare clic sul pulsante "OK" per salvare le restrizioni immesse.
Come puoi vedere, dopo di che, la restrizione appare nel campo corrispondente della finestra dei parametri di ricerca della soluzione. Inoltre, puoi rendere le variabili non negative selezionando la casella accanto al parametro corrispondente appena sotto. È consigliabile che il parametro qui impostato non sia in contraddizione con quelli che hai prescritto nelle restrizioni, altrimenti potrebbe sorgere un conflitto.
Ulteriori impostazioni possono essere impostate facendo clic sul pulsante "Parametri".
Qui puoi impostare la precisione del vincolo e i limiti della soluzione. Una volta inseriti i dati richiesti, fare clic sul pulsante "OK". Ma, per il nostro caso, non è necessario modificare questi parametri.
Dopo aver impostato tutte le impostazioni, fare clic sul pulsante "Trova una soluzione".
Inoltre, il programma Excel nelle celle esegue i calcoli necessari. Contemporaneamente all'output dei risultati, si apre una finestra in cui è possibile salvare la soluzione trovata o ripristinare i valori originali spostando l'interruttore nella posizione appropriata. Indipendentemente dall'opzione selezionata, spuntando la casella "Torna alla finestra di dialogo delle opzioni", è possibile accedere nuovamente alle impostazioni per la ricerca di una soluzione. Dopo aver impostato le caselle di controllo e gli interruttori, fare clic sul pulsante "OK".
Se, per qualsiasi motivo, i risultati della ricerca di soluzioni non ti soddisfano, o durante il loro calcolo, il programma dà un errore, allora, in questo caso, torniamo, come descritto sopra, alla finestra di dialogo dei parametri. Stiamo esaminando tutti i dati inseriti, poiché potrebbe esserci stato un errore da qualche parte. Se l'errore non è stato trovato, vai al parametro "Seleziona un metodo di soluzione". Qui è possibile scegliere uno dei tre metodi di calcolo: "Ricerca di una soluzione a problemi non lineari con il metodo OPG", "Ricerca di una soluzione a problemi lineari con il metodo simplex" e "Ricerca evolutiva di una soluzione". Per impostazione predefinita, viene utilizzato il primo metodo. Stiamo cercando di risolvere il problema scegliendo qualsiasi altro metodo. In caso di errore, riproviamo utilizzando l'ultimo metodo. L'algoritmo delle azioni è lo stesso descritto sopra.
Come puoi vedere, la funzione Cerca una soluzione è uno strumento piuttosto interessante che, quando uso corretto, può far risparmiare molto tempo all'utente su vari calcoli. Sfortunatamente, non tutti gli utenti conoscono la sua esistenza, per non parlare di come poter lavorare correttamente con questo componente aggiuntivo. In un certo senso, questo strumento assomiglia alla funzione , ma allo stesso tempo presenta anche differenze significative con esso.
Se in cella di Excel viene introdotta una formula contenente un collegamento alla stessa cella (magari non direttamente, ma indirettamente - attraverso una catena di altri collegamenti), quindi si dice che esiste un riferimento ciclico (ciclo). In pratica, i riferimenti ciclici vengono utilizzati quando si tratta di implementare un processo iterativo, calcolando per relazioni ricorrenti. Nel solito modalità Excel rileva un ciclo ed emette un messaggio sulla situazione che si è verificata, richiedendone l'eliminazione. Excel non può eseguire calcoli perché i riferimenti circolari generano un numero infinito di calcoli. Ci sono due modi per uscire da questa situazione: eliminare i riferimenti circolari o consentire calcoli utilizzando formule con riferimenti circolari (in quest'ultimo caso, il numero di ripetizioni del ciclo deve essere finito).
Considera il problema di trovare la radice di un'equazione usando il metodo di Newton usando riferimenti ciclici. Prendiamo come esempio un'equazione quadratica: x 2 - 5x + 6 \u003d 0, la cui rappresentazione grafica è mostrata in. Puoi trovare la radice di questa (e qualsiasi altra) equazione utilizzando solo una cella di Excel.
Per abilitare il calcolo ciclico in menu Strumenti / Opzioni / scheda Calcoli attiva la casella di controllo Iterazione, se necessario, modificare il numero di ripetizioni del ciclo nel campo Limita il numero di iterazioni e l'accuratezza dei calcoli sul campo Errore relativo (per impostazione predefinita, i loro valori sono rispettivamente 100 e 0.0001). Oltre a queste impostazioni, selezioniamo l'opzione di calcolo: automaticamente o manualmente... quando automatico calcolo Excel fornisce immediatamente il risultato finale, nei calcoli eseguiti manualmente, puoi osservare il risultato di ogni iterazione.
| Figura. 8. Grafico delle funzioni |
Selezioniamo una cella arbitraria, assegniamole un nuovo nome, diciamo: Xe introdurre in esso una formula ricorrente che specifica i calcoli con il metodo di Newton:
,
dove F e F1 specificare le espressioni per calcolare i valori della funzione e la sua derivata, rispettivamente. Per la nostra equazione quadratica, dopo aver inserito la formula, il valore apparirà nella cella 2
corrispondente a una delle radici dell'equazione (). Nel nostro caso, l'approssimazione iniziale non è stata specificata, il processo di calcolo iterativo è iniziato con il valore predefinito memorizzato nella cella X e uguale a zero. Come si ottiene la seconda radice? Questo di solito può essere fatto cambiando l'ipotesi iniziale. È possibile risolvere il problema di specificare le impostazioni iniziali in ciascun caso in modi diversi. Dimostreremo una tecnica basata sull'uso della funzione IF. Per migliorare la chiarezza dei calcoli, alle celle sono stati assegnati nomi significativi (). 
2.2. Selezione dei parametri
Quando conosci il risultato desiderato della formula, ma non conosci i valori necessari per ottenere questo risultato, puoi utilizzare lo strumento Selezione dei parametriscegliendo il comando Selezione dei parametri sul menu Servizio... Quando si seleziona un'opzione, Excel modifica il valore in una cella specifica fino a quando la formula che fa riferimento a quella cella non produce il risultato desiderato.
Prendiamo come esempio la stessa equazione quadratica x 2 -5x + 6 \u003d 0... Per trovare le radici dell'equazione, procedi come segue:
Excel utilizza un processo iterativo (round robin) per selezionare il parametro. Il numero di iterazioni e la precisione sono impostati nel menu Scheda Strumenti / Opzioni / Calcoli... Se Excel sta eseguendo un'attività complessa di ridimensionamento di un parametro, è possibile fare clic su Pausa nella finestra di dialogo Risultato della selezione dei parametri e interrompere il calcolo, quindi premere il pulsante Passoper ripetere di nuovo e vedere il risultato. Quando si risolve un problema in modalità passo passo, viene visualizzato un pulsante Procedere - per tornare alla normale modalità di selezione dei parametri.
Torniamo all'esempio. Di nuovo sorge la domanda: come ottenere la seconda radice? Come nel caso precedente, è necessario impostare un'approssimazione iniziale. Può essere fatto in questo modo ():
 |
| e |
 |
| b |
| Figura. 11. Trovare la seconda radice |
Tuttavia, tutto ciò può essere fatto in un modo un po 'più semplice. Per trovare la seconda radice, è sufficiente mettere la costante nella cella C2 come approssimazione iniziale () 5 e dopo di che inizia il processo Selezione dei parametri.
2.3. Trovare una soluzione
Squadra Selezione dei parametri è conveniente per risolvere i problemi di ricerca di un valore target specifico, a seconda di un parametro sconosciuto. Per attività più complesse, usa il comando Trovare una soluzione (Risolutore), a cui si accede tramite la voce di menu Servizio / Cerca una soluzione.
Compiti che possono essere risolti con Trovare una soluzione, nell'impostazione generale sono formulati come segue:
Trovare:
x 1, x 2, ..., x n
tale che:
F (x 1, x 2, ..., x n)\u003e (Max; Min; \u003d Valore)
con restrizioni:
G (x 1, x 2, ..., x n)\u003e (Ј Valore; i Valore; \u003d Valore)
Variabili ricercate: celle di lavoro foglio di lavoro Excel - sono chiamate celle regolabili. Funzione obiettivo F (x 1, x 2, ..., x n)a volte indicato semplicemente come obiettivo, deve essere specificato come formula in una cella di un foglio di lavoro. Questa formula può contenere funzioni definite dall'utente e deve dipendere da (riferimento) le celle regolabili. Al momento dell'impostazione del problema, si determina cosa fare con la funzione obiettivo. Puoi scegliere una delle opzioni:
- trova il massimo della funzione obiettivo F (x 1, x 2, ..., x n);
- trova il minimo della funzione obiettivo F (x 1, x 2, ..., x n);
- raggiungere che la funzione obiettivo F (x 1, x 2, ..., x n) aveva un valore fisso: F (x 1, x 2, ..., x n) \u003d a.
Funzioni G (x 1, x 2, ..., x n) sono chiamati vincoli. Possono essere specificati sia sotto forma di uguaglianze che di disuguaglianze. Ulteriori restrizioni possono essere imposte alle celle regolate: non negatività e / o numero intero, quindi la soluzione ricercata viene ricercata nell'intervallo di positivi e / o interi.
Questa formulazione copre la più ampia gamma di problemi di ottimizzazione, inclusa la soluzione di varie equazioni e sistemi di equazioni, problemi di programmazione lineare e non lineare. Tali compiti sono generalmente più facili da formulare che da risolvere. Quindi, per risolvere uno specifico problema di ottimizzazione, è necessario un metodo appositamente progettato. Risolutore ha nel suo arsenale potenti strumenti per risolvere tali problemi: il metodo gradiente generalizzato, il metodo simplex, il metodo branch and bound.
Sopra, per trovare le radici di un'equazione quadratica, è stato applicato il metodo di Newton (Sezione 1.4) utilizzando riferimenti ciclici () e lo strumento Selezione dei parametri (). Vediamo come si usa Trovare una soluzione usando l'esempio della stessa equazione quadratica.
Dopo aver aperto un dialogo Trovare una soluzione () devi fare quanto segue:- in campo Imposta cella di destinazione inserisci l'indirizzo della cella contenente la formula per il calcolo dei valori della funzione ottimizzata, nel nostro esempio, la cella di destinazione è C4 e la formula in essa contenuta è: \u003d C3 ^ 2-5 * C3 + 6;
- per massimizzare il valore della cella di destinazione, impostare il pulsante di opzione valore massimo in posizione 8, l'interruttore viene utilizzato per ridurre al minimo valore minimo, nel nostro caso, imposta l'interruttore su value e inserisci il valore 0 ;
- in campo Cambiare le celle inserire gli indirizzi delle celle da modificare, es. argomenti della funzione obiettivo (C3), separandoli con il ";" (o facendo clic con il mouse mentre il tasto è premuto Ctrl sulle celle corrispondenti), per cercare automaticamente tutte le celle che influenzano la soluzione, utilizzare il pulsante Indovina;
- in campo Limitazioni utilizzando il pulsante Aggiungere a inserisci tutte le restrizioni che il risultato della ricerca deve soddisfare: per il nostro esempio, non è necessario impostare restrizioni;
- per avviare il processo di ricerca di una soluzione, premere il pulsante Eseguire.
Per salvare la soluzione ottenuta, è necessario utilizzare lo switch Salva la soluzione trovata nella finestra di dialogo aperta Risultati della ricerca della soluzione... Dopodiché, il foglio di lavoro assumerà la forma mostrata in. La soluzione risultante dipende dalla scelta dell'approssimazione iniziale, che è specificata nella cella C4 (argomento della funzione). Se, come approssimazione iniziale nella cella C4, inserisci un valore uguale a 1,0 , quindi utilizzando Trovare una soluzione trova la seconda radice uguale a 2,0 .
Opzioni che governano il lavoro Trovare una soluzionedato nella finestra Opzioni (la finestra appare se si fa clic sul pulsante Opzioni finestra Trovare una soluzione), il seguente ():
- Tempo massimo - limita il tempo assegnato per il processo di ricerca di una soluzione (il valore predefinito è 100 secondi, che è sufficiente per problemi con circa 10 restrizioni, se il problema è di grandi dimensioni, il tempo deve essere aumentato).
- Limita il numero di iterazioni - un altro modo per limitare il tempo di ricerca impostando il numero massimo di iterazioni. L'impostazione predefinita è 100 e, molto spesso, se la soluzione non viene ottenuta in 100 iterazioni, quindi con un aumento del loro numero (nel campo è possibile inserire un tempo non superiore a 32767 secondi), la probabilità di ottenere un risultato è piccola . Meglio provare a cambiare l'approssimazione iniziale e ricominciare il processo di ricerca.
- Errore relativo - imposta la precisione con cui la cella corrisponde al valore target o l'approssimazione ai limiti specificati (frazione decimale da 0 a 1).
- Tolleranza - impostato in% solo per problemi con vincoli interi. Trovare una soluzione in tali problemi, trova prima la soluzione ottimale non intera, quindi cerca di trovare il punto intero più vicino, la soluzione in cui differirebbe da quella ottimale non più del numero di percentuale indicato da questo parametro.
- Convergenza - quando la variazione relativa del valore nella cella obiettivo nelle ultime cinque iterazioni diventa inferiore al numero (frazione dall'intervallo da 0 a 1) specificato in questo parametro, la ricerca si interrompe.
- Modello lineare - questa casella di controllo dovrebbe essere abilitata quando la funzione obiettivo ei vincoli sono funzioni lineari. Questo accelera il processo di ricerca di una soluzione.
- Valori non negativi - questo flag può essere utilizzato per impostare vincoli sulle variabili, che ti consentiranno di cercare soluzioni nell'intervallo positivo di valori senza specificare vincoli speciali sul loro limite inferiore.
- Ridimensionamento automatico - questo flag deve essere abilitato quando la scala dei valori delle variabili di input e la funzione obiettivo ei vincoli differiscono, possibilmente per ordini di grandezza. Ad esempio, le variabili sono messe a pezzi e la funzione obiettivo che determina il profitto massimo viene misurata in miliardi di rubli.
- Mostra i risultati dell'iterazione - questa casella di controllo consente di abilitare un processo di ricerca passo passo, mostrando i risultati di ogni iterazione sullo schermo.
- Valutazioni - questo gruppo serve per indicare il metodo di estrapolazione - lineare o quadratica - utilizzato per ottenere le stime iniziali dei valori delle variabili in ciascuna ricerca unidimensionale. Lineare serve per utilizzare l'estrapolazione lineare lungo il vettore tangente. Quadratico serve per utilizzare l'estrapolazione quadratica, che dà risultati migliori quando si risolvono problemi non lineari.
- Differenze (derivati) - questo gruppo serve per indicare il metodo di differenziazione numerica, che viene utilizzato per calcolare le derivate parziali di funzioni oggettive e limitanti. Parametro Diretto utilizzato nella maggior parte delle attività in cui la velocità di modifica delle restrizioni è relativamente lenta. Parametro Centrale utilizzato per funzioni con derivata discontinua. Questo metodo richiede più calcoli, ma la sua applicazione può essere giustificata se viene visualizzato un messaggio che informa che non è possibile ottenere una soluzione più accurata.
- Metodo di ricerca - serve per selezionare un algoritmo di ottimizzazione. Il metodo di Newton è stato discusso in precedenza. NEL Metodo del gradiente coniugato è richiesta meno memoria, ma vengono eseguite più iterazioni rispetto al metodo di Newton. Questo metodo dovrebbe essere usato se l'attività è abbastanza grande ed è necessario risparmiare memoria, e anche se le iterazioni danno troppa poca differenza nelle approssimazioni successive.
- quando si salva una cartella di lavoro di Excel dopo aver trovato una soluzione, tutti i valori inseriti nelle finestre di dialogo Trovare una soluzionevengono salvati con i dati del foglio di lavoro. È possibile salvare un set di valori dei parametri con ogni foglio di lavoro nella cartella di lavoro Trovare una soluzione;
- se all'interno di un foglio di lavoro Excel è necessario considerare più modelli di ottimizzazione (ad esempio, trovare il massimo e il minimo di una funzione, oppure i valori massimi di più funzioni), allora è più conveniente salvare questi modelli utilizzando il pulsante Opzioni / Salva modello finestra Trovare una soluzione... L'intervallo per il modello salvato contiene informazioni sulla cella di destinazione, sulle celle da modificare, su ciascuno dei vincoli e su tutti i valori della finestra di dialogo. Opzioni... La scelta di un modello per risolvere uno specifico problema di ottimizzazione viene effettuata tramite il pulsante Parametri / Carica modello dialogo Trovare una soluzione;
- un altro modo per salvare i parametri di ricerca è salvarli come script con nome. A tale scopo, fare clic sul pulsante Salva script la finestra di dialogo Risultati di ricerca per soluzioni.
Oltre a inserire valori ottimali nelle celle modificate Trovare una soluzione consente di presentare i risultati sotto forma di tre report: risultati, Sostenibilità e Limiti... Per generare uno o più report, è necessario selezionarne i nomi nella finestra di dialogo Risultati della ricerca della soluzione... Diamo un'occhiata più da vicino a ciascuno di essi.
 |
| Figura. 15. Rapporto di sostenibilità |
Il rapporto Limiti contiene informazioni sui limiti entro i quali i valori delle celle modificate possono essere aumentati o diminuiti senza violare i vincoli dell'attività. Per ogni cella che si modifica, questo report contiene il valore ottimale nonché i valori più piccoli che la cella può accettare senza violare i vincoli.