Funzione di Microsoft Excel: trovare una soluzione

Se una formula contenente un collegamento alla stessa cella viene inserita nella cella di Excel (forse non direttamente, ma indirettamente attraverso una catena di altri collegamenti), allora dicono che esiste un riferimento circolare (loop). In pratica, quando si tratta di implementare il processo iterativo, si fa ricorso a riferimenti ciclici mediante calcoli basati su relazioni di ricorrenza. In modalità normale, Excel rileva un loop e visualizza un messaggio sulla situazione che si è verificata, richiedendone la correzione. Excel non può eseguire calcoli, poiché i collegamenti ciclici generano un numero infinito di calcoli. Ci sono due modi per uscire da questa situazione: eliminare i riferimenti ciclici o consentire i calcoli usando formule con riferimenti ciclici (in quest'ultimo caso il numero di ripetizioni del ciclo dovrebbe essere finito).

Considerare il problema di trovare la radice dell'equazione con il metodo di Newton usando riferimenti ciclici. Prendi ad esempio l'equazione quadratica: x 2 - 5x + 6 = 0la cui rappresentazione grafica è mostrata in. Puoi trovare la radice di questa (e qualsiasi altra) equazione usando solo una cella di Excel.

Per abilitare la modalità di calcolo ciclico in scheda Strumenti / Opzioni / Calcoli abilita la casella di controllo iterazioni, se necessario, modificare il numero di iterazioni nel campo Limita il numero di iterazioni e accuratezza dei calcoli sul campo Errore relativo (per impostazione predefinita, i loro valori sono 100 e 0,0001, rispettivamente). Oltre a queste installazioni, scegliere l'opzione di eseguire calcoli: automaticamente o a mano. a automatico Nel calcolare Excel, dà immediatamente il risultato finale, nei calcoli effettuati da a manoÈ possibile osservare il risultato di ogni iterazione.

Fig. 8. Grafico delle funzioni

Seleziona una cella arbitraria, assegnagli un nuovo nome, ad esempio: Xe inserisci in essa la formula ricorrente, che definisce i calcoli secondo il metodo di Newton:

dove F e F1 imposta, rispettivamente, l'espressione per calcolare i valori della funzione e la sua derivata. 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 computazionale iterativo è iniziato dal valore memorizzato nella cella per impostazione predefinita. X e uguale a zero. Come ottenere la seconda radice? Questo di solito può essere fatto cambiando l'approssimazione iniziale. Per risolvere il problema dell'impostazione delle impostazioni iniziali in ciascun caso può essere diverso. Dimostreremo un trucco basato sull'uso della funzione IF. Per aumentare la visibilità dei calcoli, sono stati assegnati nomi significativi alle celle ().

2.2. Selezione dei parametri

Quando è noto il risultato desiderato del calcolo tramite la formula, ma i valori necessari per ottenere questo risultato sono sconosciuti, è possibile utilizzare lo strumento Selezione dei parametriscegliendo una squadra Selezione dei parametri nel menu servizio. Quando si seleziona il parametro, Excel cambia il valore in una cella specifica finché il calcolo utilizzando la formula che fa riferimento a questa cella fornisce il risultato desiderato.

Prendi come esempio la stessa equazione quadratica x 2 -5x + 6 = 0. Per trovare le radici dell'equazione, procedi come segue:

Quando si seleziona un parametro, Excel utilizza un processo iterativo (ciclico). Il numero di iterazioni e accuratezza sono impostate nel menu. Scheda Servizi / Parametri / Calcoli. Se Excel svolge il difficile compito di selezionare un parametro, puoi fare clic pausa nella finestra di dialogo Il risultato del parametro di selezione e interrompere il calcolo e quindi fare clic passo, per eseguire la successiva iterazione e visualizzare il risultato. Quando si risolve un'attività in una 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 specificare un'approssimazione iniziale. Questo può essere fatto come segue ():

Fig. 11. Cerca la seconda radice

Tuttavia, tutto questo può essere fatto e in qualche modo più semplice. Per trovare la seconda radice, è sufficiente l'approssimazione iniziale () nella cella C2 per inserire la costante 5 e quindi avviare il processo Selezione dei parametri.

2.3. Trovare una soluzione

squadra Selezione dei parametri È utile per risolvere i problemi di ricerca di un valore target specifico, a seconda di un parametro sconosciuto. Per attività più complesse, utilizzare il comando Trovare una soluzione (risolutore), l'accesso al quale è implementato tramite la voce di menu Servizio / Ricerca di una soluzione.

Compiti che possono essere raggiunti con Cerca una soluzione, nella formulazione generale sono formulati come segue:

Trova:
x 1, x 2, ..., x n
tale che:
F (x 1, x 2, ..., x n)\u003e (Max; Min; = Valore)
sotto restrizioni:
G (x 1, x 2, ..., x n)\u003e (Ј Valore; і Valore; = Valore)

Le variabili ricercate - le celle del foglio di lavoro di Excel - sono chiamate celle regolabili. Funzione obiettiva F (x 1, x 2, ..., x n)a volte indicato semplicemente come l'obiettivo, deve essere indicato come una formula nella cella del foglio di lavoro. Questa formula può contenere funzioni definite dall'utente e deve dipendere (riferirsi) alle celle regolabili. Al momento di impostare il problema, viene determinato cosa fare con la funzione obiettivo. Puoi scegliere una delle opzioni:

trova la massima funzione oggettiva F (x 1, x 2, ..., x n);
trova il minimo della funzione obiettivo F (x 1, x 2, ..., x n);
raggiungere quella funzione oggettiva F (x 1, x 2, ..., x n) aveva un valore fisso: F (x 1, x 2, ..., x n) = a.

funzioni G (x 1, x 2, ..., x n) sono chiamati vincoli. Possono essere specificati sotto forma di uguaglianze o disuguaglianze. Ulteriori restrizioni possono essere imposte sulle celle regolabili: non-negatività e / o intero, quindi viene cercata la soluzione desiderata nella regione di numeri positivi e / o interi.

Questa formulazione include la più ampia gamma di problemi di ottimizzazione, compresa la soluzione di varie equazioni e sistemi di equazioni, problemi di programmazione lineare e non lineare. Tali compiti sono solitamente più facili da formulare che da risolvere. E poi per risolvere un problema di ottimizzazione specifico, è necessario un metodo appositamente costruito. risolutore ha nel suo arsenale potenti strumenti per risolvere tali problemi: il metodo del gradiente generalizzato, il metodo simplex, il metodo branch e bound.

Sopra, il metodo Newton (Sezione 1.4) è stato applicato per trovare le radici di un'equazione quadratica usando riferimenti ciclici () e Selezione dei parametri (). Considera come usare Cerca soluzione dall'esempio della stessa equazione quadratica.

Dopo aver aperto il dialogo Trovare una soluzione () è necessario eseguire le seguenti operazioni:

nel campo Imposta la cella obiettivo inserire l'indirizzo della cella contenente la formula per il calcolo dei valori della funzione da ottimizzare, nel nostro esempio la cella obiettivo è C4 e la formula in essa ha la forma: = C3 ^ 2 - 5 * C3 + 6;
per massimizzare la cella obiettivo, impostare l'interruttore valore massimo alla posizione 8, l'interruttore viene utilizzato per ridurre al minimo valore minimoNel nostro caso, imposta l'interruttore sul valore di posizione e inserisci il valore 0 ;
nel campo Cambiando le celle inserisci gli indirizzi delle celle variabili, ad es. gli argomenti della funzione obiettivo (C3), separati dal segno ";" (o facendo clic con il mouse mentre si preme ctrl sulle celle corrispondenti), per cercare automaticamente tutte le celle che influenzano la decisione, utilizzare il pulsante Suggerire;
nel campo restrizioni usando il pulsante Aggiungere inserire tutti i vincoli che il risultato della ricerca dovrebbe soddisfare: per il nostro esempio di vincoli, non è necessario specificare;
per avviare il processo di ricerca, fare clic sul pulsante. corsa.

Per salvare la soluzione risultante, è necessario utilizzare lo switch Salva la soluzione trovata nella finestra di dialogo che si apre Risultati della ricerca della soluzione. Dopo di che il foglio di lavoro prenderà il modulo presentato su. La soluzione risultante dipende dalla scelta dell'approssimazione iniziale, che viene specificata nella cella C4 (argomento della funzione). Se, come prima approssimazione, inserire il valore uguale a 1,0 quindi usando Cerca una soluzione trova la seconda radice uguale a 2,0 .

Opzioni che controllano il lavoro Cerca una soluzionespecificato nella finestra parametri (la finestra appare se si fa clic sul pulsante parametri le finestre Trovare una soluzione), il seguente ():

Tempo massimo - limita il tempo assegnato per il processo di ricerca della soluzione (il valore predefinito è 100 secondi, che è sufficiente per le attività che hanno circa 10 limitazioni, se l'attività è di una dimensione grande, quindi 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. Il valore predefinito è 100 e, molto spesso, se la soluzione non viene ricevuta in 100 iterazioni, quindi con un aumento del loro numero (nel campo, è possibile immettere un tempo non superiore a 32767 secondi), la probabilità di ottenere il risultato è piccola. È meglio provare a cambiare l'approssimazione iniziale e iniziare di nuovo il processo di ricerca.
Errore relativo - imposta l'accuratezza con cui viene determinata la conformità della cella al valore target o un'approssimazione ai limiti specificati (decimale da 0 a 1).
tolleranza - imposta% solo per le attività con vincoli interi. Trovare una soluzione in tali problemi, trova prima la soluzione ottimale nonintegrale e quindi cerca di trovare il punto intero più vicino, la soluzione in cui differirebbe da quella ottimale di non più del numero di percento indicato da questo parametro.
convergenza - quando la variazione relativa del valore nella cella di destinazione 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 - questo flag deve essere abilitato quando la funzione obiettivo e i vincoli sono funzioni lineari. Ciò accelera la ricerca di una soluzione.
Valori non negativi - Con questo flag, puoi impostare restrizioni sulle variabili, che ti permetteranno di cercare soluzioni nell'intervallo di valori positivo, senza impostare restrizioni speciali sul loro limite inferiore.
Ridimensionamento automatico - questo flag deve essere abilitato quando la scala dei valori delle variabili di input e della funzione obiettivo e dei vincoli differisce, forse per ordine di grandezza. Ad esempio, le variabili sono impostate in pezzi e la funzione obiettivo che determina il profitto massimo è misurata in miliardi di rubli.
Mostra risultati di iterazione - Questa casella ti permette di abilitare un processo di ricerca passo-passo, mostrando i risultati di ogni iterazione sullo schermo.
valutazione - questo gruppo viene utilizzato per specificare il metodo di estrapolazione - lineare o quadratico, - utilizzato per ottenere le stime iniziali dei valori delle variabili in ciascuna ricerca unidimensionale. lineare serve per usare estrapolazione lineare lungo il vettore tangente. quadratico serve per usare l'estrapolazione quadratica, che dà i migliori risultati nella risoluzione di problemi non lineari.
Differenze (derivati) - questo gruppo è usato per specificare il metodo della differenziazione numerica, che è usato per calcolare le derivate parziali delle funzioni obiettivo e limitante. parametro Linee rette utilizzato nella maggior parte dei compiti in cui il tasso di variazione dei vincoli è relativamente basso. parametro centrale Utilizzato per funzioni che hanno una derivata discontinua. Questo metodo richiede più calcoli, ma il suo utilizzo può essere giustificato se viene emesso un messaggio che non riesce a ottenere una soluzione più accurata.
Metodo di ricerca - serve per selezionare l'algoritmo di ottimizzazione. Il metodo di Newton è stato rivisto in precedenza. il Metodo di sfumatura del 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 salvare la memoria, e anche se le iterazioni danno una differenza troppo piccola nelle approssimazioni successive.

quando si salva una cartella di lavoro di Excel dopo aver cercato una soluzione, tutti i valori inseriti nelle finestre di dialogo Trovare una soluzionesalvato insieme ai dati del foglio di lavoro. Con ciascun foglio di lavoro, è possibile salvare un set di valori di parametro nella cartella di lavoro. Cerca una soluzione;
se è necessario considerare diversi modelli di ottimizzazione all'interno di un foglio di lavoro Excel (ad esempio, trovare il massimo e il minimo di una funzione, o i valori massimi di diverse funzioni), allora è più conveniente salvare questi modelli utilizzando il pulsante Parametri / Salva modello le finestre Trovare una soluzione. L'intervallo per il modello salvato contiene informazioni sulla cella di destinazione, sulle celle che vengono modificate, su ciascuna delle restrizioni e su tutti i valori della finestra di dialogo. parametri. La scelta di un modello per risolvere un problema di ottimizzazione specifico viene eseguita utilizzando il pulsante Opzioni / Carica modello dialogo Trovare una soluzione;
un altro modo per salvare i parametri di ricerca è salvarli come script con nome. Per fare ciò, fare clic sul pulsante. Salva script finestra di dialogo Risultati della ricerca della soluzione.

Oltre a inserire i valori ottimali nelle celle variabili Trovare una soluzione ti consente di presentare i risultati sotto forma di tre rapporti: risultati, stabilità e limiti di. Per generare uno o più rapporti, è necessario evidenziare i loro nomi nella finestra di dialogo. Risultati della ricerca della soluzione. Considera più in dettaglio ciascuno di essi.

Fig. 15. Rapporto di sostenibilità

) contiene informazioni su quanto è sensibile la cella di destinazione ai cambiamenti di vincoli e variabili. Questo report ha due sezioni: una per le celle mutabili e l'altra per le restrizioni. La colonna di destra in ciascuna sezione contiene informazioni sulla sensibilità. Ogni cella modificabile e le restrizioni sono elencate su una riga separata. La sezione per le celle variabili contiene il valore del gradiente normalizzato, che mostra come l'intera cella risponda ad un aumento del valore nella cella variabile corrispondente di una unità. Allo stesso modo, il moltiplicatore di Lagrange nella sezione delle restrizioni mostra come la cellula bersaglio risponda ad un aumento del valore corrispondente del limite di una unità. Quando si utilizzano i vincoli di numeri interi, Excel visualizza un messaggio. Rapporti e limiti di resilienza non applicabili per attività con vincoli di interi. Se la finestra di dialogo Opzioni di ricerca della soluzione viene controllato Modello lineare, il rapporto sulla sostenibilità contiene diverse colonne di informazioni aggiuntive.) contiene tre tabelle: la prima contiene informazioni sulla funzione obiettivo prima dell'inizio del calcolo, la seconda contiene i valori delle variabili ricercate come risultato della risoluzione del problema, la terza contiene i risultati della soluzione ottimale per i vincoli. Questo rapporto contiene anche informazioni sui parametri di ciascun vincolo, come stato e differenza. Uno stato può assumere tre stati: associato, non associato o non realizzato. Il valore di differenza è la differenza tra il valore visualizzato nella cella di restrizione quando viene ricevuta la soluzione e il numero specificato nella parte destra della formula di restrizione. Un vincolo correlato è un vincolo per il quale il valore di differenza è zero. Un vincolo non legato è un vincolo che è stato eseguito con un valore di differenza diverso da zero.

Il rapporto sui limiti contiene informazioni sulla misura in cui i valori delle celle variabili possono essere aumentati o diminuiti senza violare i vincoli dell'attività. Per ogni cella variabile, questo report contiene il valore ottimale, nonché i valori più piccoli che la cella può assumere senza violare le restrizioni.

Come sai già, le formule in Microsoft Excel ti permettono di determinare il valore di una funzione con i suoi argomenti. Tuttavia, una situazione può sorgere quando il valore della funzione è noto e l'argomento deve essere trovato (ovvero, per risolvere l'equazione). Per risolvere tali problemi, è stata progettata una funzione speciale. Goal Seek (selezione parametri) .

Cerca parametro.

Funzione speciale Goal Seek (selezione parametri) consente di determinare il parametro (argomento) della funzione se il suo valore è noto. Quando si seleziona un parametro, il valore della cella di influenza (parametro) cambia finché la formula a seconda di questa cella restituisce il valore specificato.

Considerare la procedura per trovare un parametro in un semplice esempio: risolvere l'equazione 10 * x - 10 / x = 15 . Qui parametro (argomento) - x . Lascia che sia una cella A3 . Entriamo in questa cella qualsiasi numero che si trova nel dominio della definizione della funzione (nel nostro esempio questo numero non può essere uguale a zero). Questo valore verrà utilizzato come valore iniziale. Lascia che sia 3 . Introduciamo la formula = 10 * A3-10 / A3 per il quale il valore richiesto deve essere ricevuto, ad una cella, ad esempio, B3 . Ora puoi avviare la funzione di ricerca parametri selezionando il comando Goal Seek (selezione parametri) nel menu Strumenti (Servizio) . Inserisci i parametri di ricerca:

Nel campo Imposta cella (Imposta nella cella) inserire il riferimento alla cella contenente la formula richiesta.
Inserisci il risultato desiderato nel campo Per valutare .
Nel campo Cambiando cella Immettere il riferimento di cella contenente il valore da abbinare.
Clicca sulla chiave bene .

Al termine della funzione, verrà visualizzata una finestra sullo schermo in cui verranno visualizzati i risultati della ricerca. Il parametro trovato verrà visualizzato nella cella a cui è stato riservato. Presta attenzione al fatto che nel nostro esempio l'equazione ha due soluzioni e viene scelto un solo parametro - ciò accade perché il parametro viene modificato solo fino a quando non viene restituito il valore richiesto. Il primo argomento trovato in questo modo ci viene restituito come risultato di ricerca. Se il valore iniziale nel nostro esempio è -3 , quindi verrà trovata la seconda soluzione dell'equazione: -0,5 .

È abbastanza difficile determinare correttamente il valore iniziale più appropriato. Più spesso, possiamo formulare ipotesi sul parametro desiderato, ad esempio il parametro deve essere intero (quindi si ottiene la prima soluzione della nostra equazione) o non positiva (la seconda soluzione).

Il compito di trovare un parametro sotto le condizioni limite imposte può essere risolto da uno speciale componente aggiuntivo di Microsoft Excel. Risolutore (Risolutore) .

Alla ricerca di una soluzione.

Componente aggiuntivo di Microsoft Excel Risolutore (Risolutore) non installato automaticamente durante la normale installazione:

Nel menu Strumenti (Servizio) seleziona squadra Componenti aggiuntivi (componenti aggiuntivi) . Se la finestra di dialogo Componenti aggiuntivi (componenti aggiuntivi) non contiene comandi Risolutore (Risolutore) premere il pulsante Sfoglia (Review) e specificare l'unità e la cartella che contiene il file aggiuntivo Solver.xla (Di solito una cartella Libreria \\ Risolutore ) o eseguire il programma di installazione di Microsoft Office se non è possibile trovare il file.
Nella finestra di dialogo Componenti aggiuntivi (componenti aggiuntivi) spunta la casella Risolutore (Risolutore) .

La procedura per trovare le soluzioni ti consente di trovare il valore ottimale della formula contenuta nella cella, che è chiamata target. Questa procedura funziona con un gruppo di celle associate a una formula nella cella di destinazione. La procedura modifica i valori nelle celle che interessano fino a raggiungere il risultato ottimale utilizzando la formula contenuta nella cella di destinazione. Per restringere l'insieme di valori, vengono applicate restrizioni che possono avere collegamenti ad altre celle che influenzano. È inoltre possibile utilizzare la procedura di decisione per determinare il valore della cella che influenza che corrisponde all'estremità della cella obiettivo, ad esempio il numero di sessioni di formazione che garantisce il massimo rendimento scolastico.

Nella finestra di dialogo Risolutore (Risolutore) proprio come nella finestra di dialogo Goal Seek (selezione parametri) , è necessario specificare la cella di destinazione, il suo valore e le celle che dovrebbero essere modificate per raggiungere l'obiettivo. Per risolvere i problemi di ottimizzazione, la cella obiettivo deve essere impostata uguale al valore massimo o minimo.

Se si fa clic sul pulsante Indovina (Indovina) Excel stesso cercherà di trovare tutte le celle che influenzano la formula.

È possibile aggiungere condizioni al contorno facendo clic sul tasto. Aggiungi (Add) .

Cliccando sul pulsante Opzioni (Opzioni) , è possibile modificare le condizioni di ricerca della soluzione: tempo massimo di ricerca della soluzione, numero di iterazioni, precisione della soluzione, tolleranza per deviazione dalla soluzione ottimale, metodo di estrapolazione (lineare o quadratico), algoritmo di ottimizzazione, ecc.

Torniamo all'esempio precedente: per ottenere la seconda soluzione (non espositiva), è sufficiente aggiungere la condizione al contorno A3 . Proprio come quando si seleziona un parametro, verrà visualizzata una finestra sullo schermo in cui verrà visualizzato un rapporto sui risultati della ricerca per la soluzione richiesta. La soluzione stessa verrà mostrata nelle celle ad essa destinate (nella cella A3 il valore verrà visualizzato -0.50 ).

Componente aggiuntivo di Microsoft Excel Risolutore (Risolutore) consente, inoltre, di risolvere sistemi di equazioni o disuguaglianze. Considera un semplice esempio: prova a risolvere un sistema di equazioni
x + y = 2
x - y = 0

Soluzione di equazioni e sistemi non lineari "

Scopo del lavoro: Esplorazione delle funzionalità di Ms Excel 2007 durante la risoluzione di equazioni e sistemi non lineari. Acquisizione di abilità per risolvere equazioni e sistemi non lineari per mezzo di un pacchetto.

Attività 1. Trova le radici del polinomio x 3 - 0,01x 2 - 0.7044x + 0.139104 = 0.

Per prima cosa, risolviamo graficamente l'equazione. È noto che la soluzione grafica dell'equazione f (x) = 0 è il punto di intersezione del grafico della funzione f (x) con l'asse x, vale a dire è il valore di x per cui la funzione si annulla.

Calcoliamo il nostro polinomio nell'intervallo da -1 a 1 in incrementi di 0,2. I risultati dei calcoli sono mostrati in Fig., Dove la formula è stata inserita nella cella B2: = A2 ^ 3 - 0.01 * A2 ^ 2 - 0.7044 * A2 + 0.139104. Il grafico mostra che la funzione interseca l'asse Ox tre volte e poiché un polinomio di terzo grado non ha più di tre radici reali, è stata trovata una soluzione grafica del problema. In altre parole, le radici erano localizzate, vale a dire gli intervalli su cui si trovano le radici di questo polinomio sono determinati: [-1, -0,8], e.

Ora puoi trovare le radici di un polinomio con il metodo delle approssimazioni successive usando il comando Dati → Lavorare con i dati → Analisi "What-If" → Selezione dei parametri.

Dopo aver inserito le approssimazioni iniziali e i valori della funzione, è possibile fare riferimento al comando Dati → Lavorare con i dati → Analisi "What-If" → Selezione dei parametri e compilare la finestra di dialogo come segue.

Nel campo Installa nella cellasi fa 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). Nel campo valore inserisci il lato destro dell'equazione e nel campo Modifica dei valori delle celle viene dato un riferimento alla cella riservata per la variabile. Si noti che si immettono i riferimenti di cella nei campi della finestra di dialogo. Selezione dei parametri più convenientemente non dalla tastiera, ma facendo clic sulla cella corrispondente.

Dopo aver fatto clic sul pulsante OK, viene visualizzata la finestra di dialogo Risultato della selezione dei parametri con un messaggio sul completamento corretto della ricerca di una soluzione, il valore approssimativo della radice verrà inserito nella cella A14.

Le due radici rimanenti si trovano allo stesso modo. I risultati dei calcoli saranno posizionati nelle celle A15 e A16.

Attività 2. Risolvere l'equazione e x - (2x - 1) 2 = 0.

Cerchiamo di localizzare le radici dell'equazione non lineare.

Per fare ciò, lo rappresentiamo nella forma f (x) = g (x), cioè. e x = (2x - 1) 2 o f (x) = e x, g (x) = (2x - 1) 2, e decidi graficamente.

La soluzione grafica all'equazione f (x) = g (x) è il punto di intersezione delle linee f (x) e g (x).

Costruisci i grafici f (x) eg (x). Per fare ciò, nel range di A3: A18 inseriamo i valori dell'argomento. Nella cella B3, introduciamo una formula per calcolare i valori della funzione f (x): = EXP (A3), e in C3 per calcolare g (x): = (2 * A3-1) ^ 2.

I risultati di calcoli e grafici f (x) eg (x):

Il grafico mostra che le linee f (x) e g (x) si intersecano due volte, vale a dire Questa equazione ha due soluzioni. Uno di questi è banale e può essere calcolato esattamente:

Per il secondo, è possibile determinare l'intervallo di isolamento della radice: 1.5< x < 2.

Ora puoi trovare la radice dell'equazione sul segmento con il metodo delle approssimazioni successive.

Introduciamo l'approssimazione iniziale nella cella H17 = 1.5 e l'equazione stessa, con riferimento all'approssimazione iniziale, nella cella I17 = EXP (H17) - (2 * H17-1) ^ 2.

e compilare la finestra di dialogo Selezione dei parametri.

Il risultato della ricerca della soluzione verrà visualizzato nella cella H17.

compito3 . Risolvi il sistema di equazioni:

Prima di utilizzare i metodi per risolvere i sistemi di equazioni sopra descritti, troveremo una soluzione grafica di questo sistema. Si noti che entrambe le equazioni del sistema sono fornite implicitamente e per la mappatura di grafici, funzioni corrispondenti a queste equazioni, è necessario risolvere le equazioni date per la variabile y.

Per la prima equazione del sistema abbiamo:

Scopriamo l'LDU della funzione ottenuta:

La seconda equazione di questo sistema descrive un cerchio.

Frammento del foglio di lavoro MS Excel con formule che devono essere inserite nelle celle per costruire le linee descritte dalle equazioni del sistema. I punti di intersezione delle linee rappresentate sono una soluzione grafica di un sistema di equazioni non lineari.

Non è difficile notare che un dato sistema ha due soluzioni. Pertanto, la procedura per trovare soluzioni al sistema deve essere eseguita due volte, avendo precedentemente determinato l'intervallo di isolamento delle radici lungo gli assi xe oy. Nel nostro caso, la prima radice giace negli intervalli (-0,5; 0) x e (0,5; 1) y, e il secondo è (0; 0,5) xe (-0,5; -1) y. Successivamente, procediamo come segue. Introduciamo i valori iniziali delle variabili x e y, le formule che rappresentano le equazioni del sistema e la funzione obiettivo.

Ora usa il comando Dati → Analisi → Trova 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.

Compiti per decisioni indipendenti

Attività 1. Trova le radici di un polinomio

Attività 2. Trova una soluzione all'equazione non lineare.

Attività 3. Trova una soluzione per un sistema di equazioni non lineari.

Una delle funzionalità più interessanti di Microsoft Excel è la ricerca di una soluzione. Tuttavia, va notato che questo strumento non può essere attribuito al più popolare tra gli utenti di questa applicazione. E invano. Dopotutto, questa funzione, utilizzando i dati originali, per iterazione, trova la soluzione ottimale di tutti disponibili. Scopriamo come utilizzare la funzione Solution Finder in Microsoft Excel.

È possibile cercare a lungo sul nastro dove si trova la ricerca di una soluzione, ma non trovare mai questo strumento. Semplicemente, per attivare questa funzione, è necessario abilitarla nelle impostazioni del programma.

Per attivare la ricerca di soluzioni in Microsoft Excel 2010 e versioni successive, vai alla scheda "File". Per la versione 2007, è necessario 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 "Gestione", selezionare il valore "Componenti aggiuntivi Excel" e fare clic sul pulsante "Vai".

Si apre una finestra con i componenti aggiuntivi. Metti un segno di spunta davanti al nome del componente aggiuntivo di cui abbiamo bisogno: "Cerca una soluzione". Clicca sul pulsante "OK".

Dopodiché, il pulsante per avviare la funzione Cerca soluzioni apparirà sulla barra multifunzione di Excel nella scheda "Dati".

Preparazione del tavolo

Ora, dopo aver attivato la funzione, vediamo come funziona. Il modo più semplice per presentare questo è con un esempio concreto. Quindi, abbiamo una tabella dei salari dei lavoratori dell'impresa. Dovremmo calcolare il bonus di ciascun dipendente, che è il prodotto dello stipendio indicato in una colonna separata, di un certo coefficiente. Allo stesso tempo, l'importo totale dei fondi stanziati per il premio è di 30000 rubli. La cella in cui si trova questo importo ha il nome del target, dal momento che il nostro obiettivo è selezionare i dati esattamente per questo numero.

Il coefficiente che viene utilizzato per calcolare la quantità del premio, dobbiamo calcolare utilizzando la funzione Cerca soluzioni. La cella in cui si trova è chiamata quella desiderata.

Le celle obiettivo e obiettivo devono essere collegate tra loro utilizzando una formula. Nel nostro caso particolare, la formula si trova nella cella obiettivo e ha la seguente forma: "= C10 * $ G $ 3", dove $ G $ 3 è l'indirizzo assoluto della cella desiderata e "C10" è l'importo totale delle retribuzioni da cui viene calcolato il premio dipendenti dell'impresa.

Avvia lo strumento Solution Finder

Dopo aver preparato la tabella, nella scheda "Dati", fare clic sul pulsante "Cerca soluzione", che si trova sulla barra multifunzione nel blocco strumenti "Analisi".

Si apre una finestra di parametri in cui è necessario inserire i dati. Nel campo "Ottimizza la funzione target", inserisci l'indirizzo della cella di destinazione, dove verrà collocato l'importo totale del bonus per tutti i dipendenti. Questo può essere fatto digitando manualmente le coordinate o facendo clic sul pulsante a sinistra del campo di inserimento dati.

Dopodiché, la finestra dei parametri viene ridotta a icona e puoi selezionare la cella della tabella desiderata. Quindi, è necessario fare nuovamente clic sullo stesso pulsante a sinistra del modulo con i dati immessi 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, poniamo l'interruttore nella posizione "Valori" e nel campo a sinistra di esso si prescrive il numero 30.000. Come ricordiamo, è questo numero che, in base alle condizioni, costituisce l'importo totale del premio per tutti i dipendenti dell'azienda.

Di seguito è riportato il campo "Modifica delle celle delle variabili". Qui è necessario specificare l'indirizzo della cella desiderata, dove, come ricordiamo, è il coefficiente, moltiplicando per il quale sarà calcolato il salario base l'ammontare del premio. L'indirizzo può essere scritto nello stesso modo in cui lo abbiamo fatto per la cella di destinazione.

Nel campo "In conformità con le restrizioni" è possibile impostare alcune restrizioni per i dati, ad esempio, rendere i valori completi o non negativi. Per fare ciò, fare clic sul pulsante "Aggiungi".

Dopodiché, si apre la finestra di aggiunta della limitazione. Nel campo "Collega alle celle" registriamo l'indirizzo delle celle rispetto al quale viene introdotta la restrizione. Nel nostro caso, questa è la cella desiderata con un coefficiente. Inoltre abbiamo messo il segno necessario: "minore o uguale", "maggiore o uguale", "uguale", "intero", "binario", ecc. Nel nostro caso, selezioneremo il segno "maggiore o uguale" per rendere il coefficiente un numero positivo. Di conseguenza, nel campo "Restrizione" indichiamo il numero 0. Se vogliamo configurare un'altra restrizione, fare clic sul pulsante "Aggiungi". Nel caso opposto, fare clic sul pulsante "OK" per salvare le restrizioni immesse.

Come puoi vedere, dopo questo, la restrizione appare nel campo corrispondente della finestra dei parametri di ricerca decisionale. Inoltre, per rendere variabili non negative, puoi impostare un segno di spunta accanto al parametro corrispondente appena sotto. È auspicabile che il parametro qui impostato non contraddica quelli specificati nelle restrizioni, altrimenti potrebbe verificarsi un conflitto.

È possibile impostare ulteriori impostazioni facendo clic sul pulsante "Parametri".

Qui puoi impostare l'accuratezza dei limiti e dei limiti della soluzione. Quando vengono inseriti i dati necessari, fare clic sul pulsante "OK". Ma, nel nostro caso, non è necessario modificare questi parametri.

Dopo aver impostato tutte le impostazioni, fare clic sul pulsante "Trova soluzione".

Inoltre, il programma Excel nelle celle esegue i calcoli necessari. Contemporaneamente ai 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 scelta, spuntando "Ritorna alla finestra di dialogo dei parametri", è possibile tornare alle impostazioni per trovare una soluzione. Dopo aver selezionato tick e interruttori, fare clic sul pulsante "OK".

Se per qualsiasi motivo i risultati della ricerca di soluzioni non ti soddisfano, o quando vengono contati, il programma genera un errore, quindi, in questo caso, torniamo nella finestra di dialogo dei parametri, come descritto sopra. Stiamo esaminando tutti i dati inseriti, poiché è possibile che sia stato fatto un errore da qualche parte. Se l'errore non è stato trovato, vai al parametro "Scegli un metodo di soluzione". Qui puoi scegliere uno dei tre metodi di calcolo: "Trovare una soluzione ai problemi non lineari con il metodo OPG", "Trovare una soluzione ai problemi lineari con il metodo simplex" e "Ricerca evolutiva per una soluzione". Per impostazione predefinita, viene utilizzato il primo metodo. Cerchiamo di risolvere il problema, scegliendo qualsiasi altro metodo. In caso di errore, riprova usando l'ultimo metodo. L'algoritmo delle azioni è lo stesso, che abbiamo descritto sopra.

Come puoi vedere, la funzione di ricerca della soluzione è uno strumento piuttosto interessante che, se usato correttamente, può far risparmiare notevolmente tempo all'utente in vari conteggi. Sfortunatamente, non tutti gli utenti conoscono la sua esistenza, per non parlare di come sapere correttamente come lavorare con questo componente aggiuntivo. In qualche modo, questo strumento assomiglia alla funzione ma allo stesso tempo ha differenze significative con esso.

Potrebbe essere utile leggere: