Cos'è ElectroYou | Login Iscriviti

ElectroYou - la comunità dei professionisti del mondo elettrico

3
voti

Come usare excel per i sistemi non lineari

Molte volte ci troviamo di fronte a equazioni o a sistemi, che non possiamo calcolare mediante l'ausilio di formule chiuse e di precisi algoritmi, perchè sono soggetti a leggi non lineari, che non sono di facile dominio per il risolutore. Ma talvolta, il programma Excel della Microsoft puo' venirci in soccorso. Basta un po' di teoria ed applicare il Risolutore, strumento ottimizzante in tante situazioni ed ottenere buone soluzioni approssimate per i sistemi non lineari.


Un po' di teoria non guasta

Supponendo d'avere un sistema del tipo:

f1(x1,x2,,...,xn) = 0

f2(x1,x2,,...,xn) = 0

...................

fn(x1,x2,,...,xn) = 0

che si presenta non lineare, è ovvio che si ha anche:

\sum_{k=1}^N f_k(x_1,x_2,...,x_n)^2 = 0

e da questa posizione partiamo per usare il nostro algoritmo su Excel.


La pratica

Usiamo un sistema non lineare del tipo:

4x_1^3-(3x_2)^{0.5} = 20
x_1x_2^2-2/x_1 = 50

La prima cosa e' ridurlo alla forma canonica, vista in precedenza, dove le equazioni sono uguagliate a zero. Quindi, si ottiene:

4x_1^3-(3x_2)^{0.5}-20=0
x_1x_2^2-2/x_1-50=0

Sul foglio Excel, si impostano due valori d'inizializzazione delle soluzioni, tipo x1=1,x2=1 e si calcolano in queste condizioni iniziali le funzioni f1(x1,x2) e f2(x1,x2). Fatto questo, si calcola la somma dei quadrati di f1 e f2.
Si veda Figura 1 per questo step.

Figura 1. Inizializzazione incognite x1 e x2 e calcolo di f1^2+f2^2

Figura 1. Inizializzazione incognite x1 e x2 e calcolo di f1^2+f2^2

Soluzione del sistema

Dato che f_1^2+f_2^2=0, mediante il menu Excel (versione 2010), si va alle schede Dati-->Analisi Simulazione e si sceglie "ricerca obbiettivo"; si apre la scheda e s'impone per la cella, dove e' il valore della somma dei quadrati delle due funzioni f1 e f2, il valore 0, come, deve essere. Infine, si impone il cambiamento di una delle due soluzioni iniziali o x1 o x2. Dopo una certa elaborazione il programma dara' il valore esatto di x1 o x2. Dopo si ripete la procedura per l'altro valore iniziale di x1 o x2, che si vuole che si cambi. Il gioco e' fatto. Se tutto va bene, in verifica si vede che il valore della somma dei quadrati di f1 ed f2 diventerà un valore vicino a zero.

Si vede in Figura 2, il passo successivo.

Figura 2. Impostazione del risolutore

Figura 2. Impostazione del risolutore

L'ultimo passo è quello di cliccare OK sulla finestrella di dialogo del Risolutore. Parte il calcolo e si ottiene l'aspetto di Figura 3, con la soluzione per x1.

Figura 3. Soluzione della variabile x1

Figura 3. Soluzione della variabile x1

Si puo' fare lo stesso per il nuovo valore di x2 ed in Figura 4, si scogono i valori delle soluzioni di x1 e x2, che sono 1.81 e 5.31 circa.

Figura 4. Entrambe le soluzioni x1 e x2 sono calcolate, in forma approssimata.

Figura 4. Entrambe le soluzioni x1 e x2 sono calcolate, in forma approssimata.

13

Commenti e note

Inserisci un commento

di ,

Da un estratto su un articolo sui limti del risolutore si legge: "Una volta definiti i parametri del Risolutore e settati i valori nelle Opzioni, si puµo tentare di risolvere il problema pigiando il tasto Risolvi (Solve). Quando il Solutore ha terminato il calcolo della soluzione ottima, compare la ¯finestra Risultati del Solutore. Leggere i risultati di ottimizzazione Il messaggio in alto indica se il solutore è stato in grado o meno di determinare la soluzione ottima del modello. Qualora non sia stato possibile determinare la soluzione ottima, µe possibile cambiare alcune delle opzioni nella ¯finestra Opzioni del Solutore per cercare di migliorare le prestazioni dell'algoritmo. In particolare, si possono cambiare i valori di tempo massimo, iterazioni, approssimazione, tolleranza, convergenza che stabiliscono i termini per cui l'algoritmo utilizzato dal Solutore si ferma". Tutto questo a patto che sia possibile installare completamente excel nelle versioni piu' piu' specialistiche.

Rispondi

di ,

Chiarisco quanto inviato nelle ultime email. Esiste un testo intitolato "Excel per ingegneri" di Silvan Charles Bloch di Apogeo Editore, 2001 - 336 pagine, dove vengono messi in luce tutti i problemi del risolutore. Per poterlo sfruttare completamente occorreranno probabilmente le issue professonali di office.

Rispondi

di ,

non viene preso bene per intero. allora consiglio di fare ricerca google con limiti del risolutore, si trova excel per ingegneri, che rimanda al google book, dove si spiegano pregi e difetti di questo strumento, che puo- essere usato bene. il mio excel purtroppo aveva una versione di risolutore limitato, forse per un-installazione del pacchetto non completa, ma ottimizzata o forse perche- non ho una versaione professionale di office.

Rispondi

di ,

vi rimando il link e- un google book. https://books.google.it/books?id=NsW8bSDmBokC&pg=PA38-IA20&lpg=PA38-IA20&dq=limiti+del+risolutore+di+excel&source=bl&ots=E7xwXjYe3R&sig=0I8mXC-NDmV8ZJ1DcBh3Fjv0a5I&hl=it&sa=X&ei=rHMOVc6gBYvMOIXIgHA&ved=0CCwQ6AEwBg#v=onepage&q=limiti%20del%20risolutore%20di%20excel&f=false

Rispondi

di ,

vi mando quest-articolo/book, che non posso copiare https://books.google.it/books?id=NsW8bSDmBokC&pg=PA38-IA20&lpg=PA38-IA20&dq=limiti+del+risolutore+di+excel&source=bl&ots=E7xwXjYe3R&sig=0I8mXC-NDmV8ZJ1DcBh3Fjv0a5I&hl=it&sa=X&ei=rHMOVc6gBYvMOIXIgHA&ved=0CCwQ6AEwBg#v=onepage&q=limiti%20del%20risolutore%20di%20excel&f=false sembra che esistono varie qualita di risolutori excel relative a specifici pacchetti excel. in questo excel per ingegneri vi sarebbero dei parametri da regolare per ottenere buone convergenze. almeno cosi mi sembra di capire...

Rispondi

di ,

vi mando quest-articolo/book, che non posso copiare https://books.google.it/books?id=NsW8bSDmBokC&pg=PA38-IA20&lpg=PA38-IA20&dq=limiti+del+risolutore+di+excel&source=bl&ots=E7xwXjYe3R&sig=0I8mXC-NDmV8ZJ1DcBh3Fjv0a5I&hl=it&sa=X&ei=rHMOVc6gBYvMOIXIgHA&ved=0CCwQ6AEwBg#v=onepage&q=limiti%20del%20risolutore%20di%20excel&f=false sembra che esistono varie qualita di risolutori excel relative a specifici pacchetti excel. in questo excel per ingegneri vi sarebbero dei parametri da regolare per ottenere buone convergenze. almeno cosi mi sembra di capire...

Rispondi

di ,

Ti ho risposto qui

Rispondi

di ,

Stiamo esulando da un rapporto di rispetto, che ha sempre contraddistinto questo bellissimo sito. Io ti vedo come un fisico un po' chiuso, privo di un po' di senso pratico. E' vero che c'e' esigenza di precisione assoluta, ma ci sono varie esigenze con gradualita' diverse per certi problemi. Se io ti dico che questo metodo aiuta a trovare presto delle condizioni iniziali. per un affinamento dei calcoli, vuol dire che questo e' vero. Se non ti sta bene, usati il tuo metodo di Newton, con tanto di formula d'errore, ma comportati in maniera consona al sito. Se sei giovane, stai parlando con un vecchio ingegnere e non c'e' bisogno di creare banner quasi provocatori.

Rispondi

di ,

paolo.carlizza ha scritto:
Niente e' inutile nel mondo, basta sapere il grado di risolubilita' del problema.

Mica pizza e fichi...

Rispondi

di ,

Non drammatizzerei, io credo tu sia un rigoroso matematico, ma io che sono ingegnere penso che in alcuni semplici problemi di ricerca operativa aiuta a trovare una prima strada. Penso saprai bene che ci sono algoritmi di analisi numerica che a partire da certe condizioni iniziali si arriva alla soluzione di sistemi matematici. Ed il mio articolo, apparentemente inutile, aiuta a trovare le condizioni inziali, per ulteriori approfondimenti numerici. Certo se ci mettiamo la teoria del caos in mezzo, le cose potrebbero complicarsi ancora. Niente e' inutile nel mondo, basta sapere il grado di risolubilita' del problema. Con questo articolo non pretendevo di superare certo la teoria della relativita' generale.

Rispondi

di ,

Beh, devo dire che nessun risolutore dà la precisione assoluta. Probabilmente non mi sono spiegato bene, il metodo non può essere "accolto di buon grado" proprio perché non è possibile stabilire la precisione della soluzione, se non utilizzando un altro metodo a precisione nota. Come scrivevo questo è il problema più grande del tuo metodo perché lo rende inutile.

Rispondi

di ,

Beh, il risolutore non da' mai la prcisione assoluta, dato che e' un ottimizzatore semplice di tipo software. Pero la soluzione si puo' affinare, usando in aiuto altri metodi matematici. Intanto si sa sove cadono le soluzioni e se non si ha l'esigenza di una precisione atomica, il metodo puo' essere accolto di buon grado.

Rispondi

di ,

Lo sforzo di scrivere un articolo deve sempre essere apprezzato, quindi mi complimento per aver cercato di applicare ad excel questa soluzione per i sistemi non lineari.
Purtroppo devo dire che dal punto di vista numerico una soluzione di questo tipo è decisamente mal condizionata e spesso porta a dei problemi di inconfrontabilità. Basti pensare ad una sommatoria dove le equazioni al quadrato siano di complessità molto diversa e con valori numerici molto distanti fra loro. Inoltre la presenza del quadrato aumenta la non linearità del problema e favorisce la propagazione dell'errore. Utilizzare quel metodo cercando di far trovare ad excel una soluzione a pezzi può far sì che gli errori si sommino in modulo, quindi questo metodo va bene solo per sistemi non troppo non lineari, molto omogenei sui coefficienti e sperando che excel converga ad una soluzione corretta, cosa che si può stabilire soltanto a soluzione trovata, andando a sostituire i numeri nella equazione. Poichè il risultato farà (si spera) un numero piccolo prossimo a zero, bisogna poi stabilire che questo valore non sia frutto di semplificazioni dovute alla forma dell'equazione (e sostituendo non ci sono più tutti i quadrati) e questo mi sembra essere il problema più grave. Personalmente, per questi ed altri motivi, non mi piace molto usare excel come solutore matematico, ma preferisco di gran lunga l'uso di un tool (anche semplice) pensato da chi conosce approfonditamente il calcolo numerico.
Risolvendo quel sistema in modo corretto si ottengono le soluzioni reali {x -> 1.81684, y -> 5.30342}}, quindi tutto lo sforzo adoperato ha portato ad ottenere una sola cifra decimale corretta, cosa che NON si può stabilire sostituendo le soluzioni trovate da excel nel sistema (basta provare). Per un solutore automatico è un risultato inaccettabile.
Problemi minori sono il fatto che spesso il sistema è espresso in forma implicita (anzi, è il caso più frequente), cosa che questo metodo non può affrontare e che le soluzioni cercate sono spesso quelle complesse (per esempio quel sistema ha una soluzione reale e ben sei complesse).
Con volontà costruttiva.
Pietro

Rispondi

Inserisci un commento

Per inserire commenti è necessario iscriversi ad ElectroYou. Se sei già iscritto, effettua il login.