Condividi:        

Calcolo punto punto di funzione Polinominale

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Calcolo punto punto di funzione Polinominale

Postdi borry » 03/08/13 14:53

mi trovi in difficoltà, spero nel vs aiuto.

Ho nella colonna B una serie di dati numerici, nella colonna A le ore; sul corrispondente grafico o aggiunto, in automatico, una linea di tendenza polinominale di Ordine 3 la cui equazione visualizzata sul grafico Excel e: y=-0,00007x^3-0,0016x^2+1,2578x+16186
con R^2=0,0435. Il mio problema e quello di poter ottenere nella colonna C i valori punto per punto dei dati che disegnano il
grafico della funzione sopra indicate.
Non riesco a trovare la formula da scrivere nelle celle della colonna C.
Spero di ricevere l'aiuto di un esperto che mi legge.
Grazie per l'aiuto
Borry
borry
Newbie
 
Post: 8
Iscritto il: 03/08/13 14:31

Sponsor
 

Re: Calcolo punto punto di funzione Polinominale

Postdi patel » 03/08/13 18:32

posta un link al tuo file, magari ridotto
patel
Utente Senior
 
Post: 309
Iscritto il: 24/04/12 16:03

Re: Calcolo punto punto di funzione Polinominale

Postdi borry » 03/08/13 20:51

ecco il link del semplice file;
http://rapidshare.com/files/2099816689/ ... minali.xls

Grazie per l'aiuto.
borry
Newbie
 
Post: 8
Iscritto il: 03/08/13 14:31

Re: Calcolo punto punto di funzione Polinominale

Postdi Flash30005 » 03/08/13 23:26

Ciao Borry e benvenuto nel Forum

Il link da te postato è corretto ma non hai abilitato il download a terzi
ciao
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: Calcolo punto punto di funzione Polinominale

Postdi Anthony47 » 03/08/13 23:50

Il link pubblicato non consente lo scaricamento del file perche' probabilmente hai inserito il file nel tuo folder "privato" di rapidshare. Vedi questo messaggio per capire come creare e usare un folder condiviso: viewtopic.php?f=26&t=80395#p460098
Comunque, a occhio direi che devi crearti una ulteriore serie, ad esempio in colonna C, usando in C2 la formula della tua linea di tendenza, cioe' =-0,00007*(x^3)-0,0016*(x^2)+1,2578*x+16186
Questa formula richiede una ulteriore serie di valori interi crescenti (1, 2, 3, 4, 5, ...) che ti creerai in una colonna libera, ad esempio in Z2 e sottostanti; nella formula di C2 userai al posto di "X" la cella contenente il valore iniziale della serie (Z2, nell' esempio), diventando quindi
Codice: Seleziona tutto
=-0,00007*(Z2^3)-0,0016*(Z2^2)+1,2578*Z2+16186

Dovrai ovviamente inserire nel grafico anche la serie creata in colonna C.

Spero che il concetto sia comprensibile.

Ciao, fai sapere.
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Calcolo punto punto di funzione Polinominale

Postdi borry » 04/08/13 09:42

http://my.rapidshare.com/Borry/17055
il link sopra indicato adesso dovrebbe consentite l'accesso a tutti.
Saluti
borry
Newbie
 
Post: 8
Iscritto il: 03/08/13 14:31

Re: Calcolo punto punto di funzione Polinominale

Postdi borry » 04/08/13 10:04

Grazie Anthony,
non capisco il concetto dei valori crescenti da inserire nella terza colonna Z,
quale relazione deve avere con i valori della colonna B ???

Sono i valori della colonna B che hanno generato il grafico sul quale, in automatico,
Execel inserisce la linea di tendenza polinomiale; nel file esempio allegato è di ordine 5:
http://my.rapidshare.com/Borry/17055
Saluti
Borry
borry
Newbie
 
Post: 8
Iscritto il: 03/08/13 14:31

Re: Calcolo punto punto di funzione Polinominale

Postdi wallace&gromit » 04/08/13 14:21

ciao a tutti,
mi permetto un commento critico: borry, cosa vuoi ottenere con tutto ciò?
vuoi trovare una formula che esprime in modo matematico uno schema di valori che ha tutto tranne un aspetto regolare?
Inoltre stai cercando un fattore che agisca in funzione del tempo (ora in colonna A), ma che il grafico Excel ignora, visto che esegue una rappresentazione a linee e non a dispersione x,y!
Io lascerei perdere!

Il tuo grafico mostra dei dati leggermente oscillatori (con due salti che mi sanno quasi più di errore di misurazione che non di andamento reale), così a occhio mi sembra di vedere una crescita iniziale e una stabilizzazione nel tempo.
Sono pronto a scommettere che, anche riuscendo a definire una funzione che rappresenta i dati passati, questa sarà smentita completamente dai rilevamenti futuri.

Molto più interessante potrebbe essere "discretizzare" la curva, cioè livellare i singoli picchi fino ad ottenere una curva più "morbida", si ottiene qualcosa di passabile con la media ponderata su più valori (nel tuo caso un centinaio), ma non sempre il risultato è soddisfacente.

Vediamo cosa ne pensano gli altri.
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: Calcolo punto punto di funzione Polinominale

Postdi borry » 04/08/13 15:13

Ciao wallace&gromit, la mia richiesta d'aiuto non aveva l'intenzione di scatenare un dibattito tecnico;
i picchi dei salti molto evidente nel file piccolo diventano irrilevanti su una grossa mole di dati.

La mia necessità è quella di ottenere - punto punto - i dati che usa Excel per disegnare la polinomiale
che ho indicato nel sintetico file postato. In altre parole, gradirei conoscere la FORMULA INVERSA rispetto
a quella usata in automatico da Excel, vorrei ottenere il valore dei punti di una ipotetica colonna C che
disegnano in manuale quello che Excel fa in automatico.

A cosa mi servono questi dati ??? semplicemente per poterli sommare ad altre funzioni (esempio:
medie mobili, sinusoidi, etc,) sono studi particolari ai quali mi sto appassionando.
Grazie comunque per l'interesse dimostrato
Borry
borry
Newbie
 
Post: 8
Iscritto il: 03/08/13 14:31

Re: Calcolo punto punto di funzione Polinominale

Postdi Anthony47 » 05/08/13 11:11

L' equazione della linea di tendenza "polinomiale di 5° grado" dichiarata da Excel, per i dati inseriti nel file che hai condiviso, e'
y = 1E-10x^5 - 5E-07x^4 + 0,0005x^3 - 0,2554x^2 + 49,591x + 1522,5
Avendo inserito la "serie crescente 1, 2, 3, 4, ... in D2 e sottostanti la formula da inserire in C2 e'
Codice: Seleziona tutto
= 1E-10*(D2^5) - 5E-07*(D2^4) + 0,0005*(D2^3) - 0,2554*(D2^2) + 49,591*D2 + 1522,5

Inserendo la colonna C nel grafico il risultato squallido e' stato quello di figura:
Immagine

Uploaded with ImageShack.us
In blu la linea di tendenza disegnata da Excel, in rosso la serie calcolata con la formula di sopra.

Ho immaginato che quella clamorosa deviazione fosse dovuta alle approssimazioni di calcolo di excel (i calcoli sono fatti con 15 cifre di precisione, e X^5 produce alla lunga numeri grossi, poi da moltiplicare con numeri piccolissimi, 1^(-10)); ho quindi modificato la formula di calcolo di C2 e sottostanti, con l' obiettivo di minimizzare gli errori di calcolo, in
Codice: Seleziona tutto
=((D2^3)/(10000000000))*(D2^2) - (5*(D2^2)/(10000000))*(D2^2) + (5*(D2^2)/(10000))*D2 - 0,2554*(D2^2) + 49,591*D2 + 1522,5
Ma la modifica non ha smosso praticamente niente.

Dopo un ulteriore scervellamento rimasto senza esiti ho provato la polinomiale di 4° grado:
l' equazione restituita e' stata:
y = -1E-07x^4 + 0,0002x^3 - 0,128x^2 + 31,16x + 2147,3
Trasformata in formula e in grafico ho ottenuto:
Immagine

Uploaded with ImageShack.us
Molto buono, e ulteriormente migliorabile con piccole variazioni sui termini di 3° e 2° grado piu' il valore costante, come da formula:
Codice: Seleziona tutto
= -0,0000001*(D2^4) + 0,0002012*(D2^3) - 0,1299*(D2^2) + 31*D2 + 2247,3

In questo caso la trend line indicata e quella calcolata praticamente corrispondono.

Ho esaminato anche il comportamento su una polinomiale di 6° grado, ma anche qui ho ottenuto differenze clamorose:
Immagine
Uploaded with ImageShack.us
Tasto dx, scegli Visualizza immagine per vedere tutta l' immagine.

A QUESTO PUNTO mi sono ricordato che gia' in un altro quesito mi ero trovato nello stesso labirinto, da cui ero uscito usando la funzione REGR.LIN proprio per ottenere i parametri di una linea di tendenza.
Per una polinomiale di 5° grado la formula da usare e'
Codice: Seleziona tutto
=REGR.LIN(B2:B1010;D2:D1010^{1;2;3;4;5};;1)
(vedi *Nota, oltre)

Va inserita come formula a matrice in 6 celle adiacenti; es:
-selezionare O2:T2
-inserire la formula nella barra della formula
-premere Contr-Maiusc-Enter
Nel caso dei tuoi dati otterrai:
1,48343E-10 -4,7095E-07 0,000532083 -0,255444597 49,59139671 1522,478531
che sono le costanti moltiplicativi dei fattori D2^5, D2^4, D2^3, ...., piu' il valore della costante.
La formula per D2 sara' quindi
Codice: Seleziona tutto
= (1,48343*(D2^3)/(100000))*(D2^2/100000) -4,7095*(D2^4)/(10000000)+ 0,000532083*(D2^3)-0,255444597*(D2^2) + 49,59139671*D2 +1522,478531
Restituira' una serie esattamente coincidente con la linea di tendenza disegnata sul grafico.
La formula e' modifcata rispetto a quella teorica [vedasi ad esemoio l' uso di (1,48343*(D2^3)/(100000))*(D2^2/100000) al posto di 1,48343E-10*(D2^5)] per minimizzare gli errori di calcolo dovuti ai calcoli con 15 cifre.

Notare come quanto restituito nel grafico sia stato grossolanamente arrotondato:
1E-10 ; -5E-07; 0,0005; 0,2554; 1522,5

*Nota: nella formula REGR.LIN l' uso di valori superiori a 1010 provoca errore; non ho trovato la spiegazione. Questo limite potrebbe essere un problema, alla luce del riferimento a "una grossa mole di dati" fatta nel tuo ultimo messaggio.

Ciao

PS: avevi chiesto quale e' il significato della numerazione crescente (1, 2, 3, 4, ...) che ti avevo fatto creare; quelli sono i valori dell' asse X del tuo grafico.
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Calcolo punto punto di funzione Polinominale

Postdi borry » 05/08/13 11:16

Grazie Anthony47,
adesso provo sui miei dati e poi faccio sapere.
grazie ancora
Borry
borry
Newbie
 
Post: 8
Iscritto il: 03/08/13 14:31

Re: Calcolo punto punto di funzione Polinominale

Postdi borry » 05/08/13 12:56

Anthony47,
ti confesso che mi sono perso nel seguire le tue indicazioni.
- non riesco a creare la matrice O2-T2 (REGR.LIN mi restituisce errore)
- Non sono sicuro di aver capito quali sono i contenuti della colonna D (in particolare il valore D2)

ti dispiace caricare sulla mia cartella condivisa http://rapidshare.com/files/2099816689/ ... minali.xls
il file Excel sul quale hai fatto lo studio indicato nel post ??

ciao e Grazie
Borry
borry
Newbie
 
Post: 8
Iscritto il: 03/08/13 14:31

Re: Calcolo punto punto di funzione Polinominale

Postdi Anthony47 » 06/08/13 15:47

In D2 e sottostanti c' e' la serie crescente 1, 2, 3, 4, etc; come detto nel precdente messaggio questi sono i valori della X, quindi vanno usati nella equazione della linea di tendenza.
Per l' inserimento della formula a matrice REGR.LIN le istruzioni sono papale papale quelle gia' date; ma secondo me hai correttamente inserita la formula ma hai un errore (probabilmente #NUM) in quanto non hai ancora creato in D2 e sottostanti la serie 1, 2, 3, 4, etc.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Calcolo punto punto di funzione Polinominale

Postdi borry » 08/08/13 09:51

Anthony47 Grazie per l'aiuto, sono riuscito nel mio intento.

La macro REGR.LIN l'ho applicata su una colonna dati
formata da 4.000 righe per una polinomiale di 3°grado,
funziona perfettamente.

Le equazioni dati dal grafico sono effettivamente
arrotondate molto grossolanamente.

Saliti e, Grazie ancora
Borry
borry
Newbie
 
Post: 8
Iscritto il: 03/08/13 14:31


Torna a Applicazioni Office Windows


Topic correlati a "Calcolo punto punto di funzione Polinominale":


Chi c’è in linea

Visitano il forum: Nessuno e 44 ospiti