[EXCEL] Parametrizzare una funzione

[holly7787]

buongiorno a tutti, sono appena arrivato nel forum, dopo aver fatto qualche ricerca su google e visto che qua c'è veramente gente esperta chiedo il vostro aiuto

avrei bisogno di parametrizzare una funzione di excel, mi spiego meglio:

ho una colonna di quasi 1500 dati e di questi devo fare una sliding windows, o media mobile, stabilendo di che dimensione fare la finestra della media.. se utilizzo la funzione media questa finestra è fissa e scelgo a priori di quanta farla grossa, vorrei invece poter scegliere a posteriori la dimensione di questa finestra.

se non sono stato chiaro di questi 1500 dati ne prendo, per esempio, 7 alla volta e ne faccio la media partendo da 1 a 7 poi da 2 a 8 poi da 3 a 9 e così via.. poi vorrei fare una media prendendone 31 e quindi da 1 a 31, da 2 a 32, da 3 a 33 eccetera e poi di 365.

grazie a tutti!!

[CANAPONE]

Ciao,

prova a scrivere in B1 l'ampiezza dell'intervallo letto dalla media (7 31 365)

Se ho capito, potresti provare in C1:

Codice: Seleziona tutto

=MEDIA(INDIRETTO("A"&RIF.RIGA(A1)&":A"&$B$1+RIF.RIGA(A1)-1))

e copiare la formula in basso.


Saluti

[holly7787]

sembra funzionare a dovere, dovrei fare delle modifiche però la strada è molto buona!
ti dico cosa dovrei fare così se hai voglia mi puoi dare una mano

dovrei mediare prendendo i valori prima e dopo, ad esempio:

se la finestra della media è dispari, ad esempio 3, e calcolata sulla riga 5 devo prendere le righe 4-5-6.
se la finestra della media è pari, ad esempio 4, e calcolata sempre sulla riga 5 devo prendere (3*1/2)-4-5-6-(7*1/2) praticamente il numero pari corrisponde a metà di quello prima e metà.

in più devo farlo solo nelle caselle dove ci sono i n valori richiesti, quindi se chiedo una finestra di 7 ma se ho 5 valori non deve eseguirlo e restituire lo spazio vuoto

grazie mille per ora mi hai già risolto un bel problemone!

[CANAPONE]

Ciao,

ti ringrazio subito del riscontro.

Per poterti dare una risposta, sarebbe d'aiuto che condividessi un file semplificato con qualche esempio dei risultati attesi.

A presto

[holly7787]

appena torno dal lavoro carico subito un esempio con formule non parametrizzate

[wallace&gromit]

Ciao Canapone, prova a vedere quello che ho preparato qui e sicuramente riesci a migliorarlo!

Per holly: limitarsi ai numeri dispari, no eh?!?

Ti allego al mia "formuletta", che fa quasi tutto quello che chiedi: c'è un piccolo problema solo con i numeri pari (inserisce un valore di troppo in fondo alla lista - però il calcolo di per sé è corretto).

Codice: Seleziona tutto

=SE(CONTA.VALORI(INDIRETTO("A"&RIF.RIGA(A1)-ARROTONDA(($B$1-1)/2;0)&":A"&RIF.RIGA(A1)+ARROTONDA(($B$1-1)/2;0)))<$B$1;"";SE(CONTA.VALORI(INDIRETTO("A"&RIF.RIGA(A1)-($B$1-1)/2&":A"&RIF.RIGA(A1)+($B$1-1)/2))=$B$1;MEDIA(INDIRETTO("A"&RIF.RIGA(A1)-($B$1-1)/2&":A"&RIF.RIGA(A1)+($B$1-1)/2));SOMMA(INDIRETTO("A"&RIF.RIGA(A1)-ARROTONDA(($B$1-1)/2;0)+1&":A"&RIF.RIGA(A1)+ARROTONDA(($B$1-1)/2;0)-1);(INDIRETTO("A"&RIF.RIGA(A1)-ARROTONDA(($B$1-1)/2;0)))/2;INDIRETTO("A"&RIF.RIGA(A1)+ARROTONDA(($B$1-1)/2;0))/2)/($B$1)))

[wallace&gromit]

ecco una formula completa che funziona:

Codice: Seleziona tutto

=SE(CONTA.VALORI(INDIRETTO("A"&RIF.RIGA(A1)-ARROTONDA(($B$1-1)/2;0)&":A"&RIF.RIGA(A1)+ARROTONDA(($B$1-1)/2;0)))<$B$1;"";SE(CONTA.VALORI(INDIRETTO("A"&RIF.RIGA(A1)-($B$1-1)/2&":A"&RIF.RIGA(A1)+($B$1-1)/2))=$B$1;MEDIA(INDIRETTO("A"&RIF.RIGA(A1)-($B$1-1)/2&":A"&RIF.RIGA(A1)+($B$1-1)/2));SE(CONTA.VALORI(INDIRETTO("A"&RIF.RIGA(A1)-ARROTONDA(($B$1-1)/2;0)&":A"&RIF.RIGA(A1)+ARROTONDA(($B$1-1)/2;0)))=$B$1;"";SOMMA(INDIRETTO("A"&RIF.RIGA(A1)-ARROTONDA(($B$1-1)/2;0)+1&":A"&RIF.RIGA(A1)+ARROTONDA(($B$1-1)/2;0)-1);(INDIRETTO("A"&RIF.RIGA(A1)-ARROTONDA(($B$1-1)/2;0)))/2;INDIRETTO("A"&RIF.RIGA(A1)+ARROTONDA(($B$1-1)/2;0))/2)/($B$1))))

un po' lunghina?

[holly7787]

lunga ma perfetta! adesso dovrò litigare un po' per inserirla in modo che legga i risultati della mia tabella pivot però è una gran cosa! grazie mille, un grazie mi sembra anche poco! adesso mi metto a cliccare un po' sui banner pubblicitari per ringraziarvi

[holly7787]

un ultimissima cosa.. per cominciare dalla seconda riga come faccio? ho un problema con i valori pari cominciando da quella..
una miglioria potrebbe essere usare ARROTONDA.PER.DIF. al posto di arrotonda?

[wallace&gromit]

arrotonda per difetto non va bene, al massimo arrotonda per eccesso, ma non ti da niente in più del semplice arrotonda.
Il tuo problema è da ricercare probabilmente nel rif.riga(), che forse fa riferimento a righe sfasate, ma dovrei vedere il file per capire. Forse si riesce ad aggirare il problema utilizzando i riferimenti relativi (R1C1) invece di quelli assoluti (se ho tempo ci provo).

[holly7787]

mi hanno suggerito anche questa funzione

Codice: Seleziona tutto

=SE.ERRORE(SE(E(INDIRETTO("$A"&RIF.RIGA(B2)-INT(B$1/2))<>"";INDIRETTO("$A"&RIF.RIGA(B2)+INT(B$1/2))<>"");(SOMMA(INDIRETTO("$A"&RIF.RIGA($A2)-INT(B$1/2)+1&":$A"&RIF.RIGA($A2)+INT(B$1/2)-1))+(INDIRETTO("$A"&RIF.RIGA($A2)-INT(B$1/2))* (0,5+(B$1/2-INT(B$1/2))))+(INDIRETTO("$A"&RIF.RIGA($A2)+INT(B$1/2))*(0,5+(B$1/2-INT(B$1/2)))))/(CONTA.NUMERI(INDIRETTO("$A"&RIF.RIGA($A2)-INT(B$1/2)&":$A"&RIF.RIGA($A2)+INT(B$1/2)))-1+((B$1/2-INT(B$1/2))*2));"");"")


[holly7787]

risolto.. basta usare CONTA.NUMERI al posto di CONTA.VALORI e non va a leggerlo

[Anthony47]

Da quello che ho capito, potrebbe anche andare bene in C1

Codice: Seleziona tutto

=SE(CONTA.NUMERI(SCARTO(A1;-$B$1/2;0;$B$1+(RESTO($B$1;2)=0);1))=$B$1+(RESTO($B$1;2)=0);SE.ERRORE((SOMMA(SCARTO(A1;-($B$1/2);0;$B$1;1))+SE(RESTO($B$1;2)=0;SCARTO(A1;$B$1/2;0)/2-SCARTO(A1;-$B$1/2;0)/2;0))/$B$1;"");"")

Per 2003 e inferiori:

Codice: Seleziona tutto

=SE(O(CONTA.VALORI(SCARTO(A1;-$B$1/2;0;$B$1+(RESTO($B$1;2)=0);1))<$B$1+(RESTO($B$1;2)=0);VAL.ERRORE(SOMMA(SCARTO(A1;-($B$1/2);0;$B$1;1))+SE(RESTO($B$1;2)=0;SCARTO(A1;$B$1/2;0)/2-SCARTO(A1;-$B$1/2;0)/2;0))/$B$1);"";(SOMMA(SCARTO(A1;-($B$1/2);0;$B$1;1))+SE(RESTO($B$1;2)=0;SCARTO(A1;$B$1/2;0)/2-SCARTO(A1;-$B$1/2;0)/2;0))/$B$1)


Tuttavia non hai chiarito come interpretare la tua richiesta "devo farlo solo nelle caselle dove ci sono i n valori richiesti" nel caso che N sia pari e si andranno a prendere anche il mezzo valore in testa e mezzo valore in coda; io ho immaginato che in questi casi ambedue le celle di cui si prende il "mezzo" devono essere piene, altrimenti viene reso "null".
La formula da te pubblicata pero' non capisco come esegue questa regola, vedi esempio in immagine:


Uploaded with ImageShack.us
Secondo me infatti i risultati in D5-D6 e D7 dovrebbero essere nulli.

L' informazione "basta usare CONTA.NUMERI al posto di CONTA.VALORI" non ho capito a cosa applicarla.

Ciao

[holly7787]

grazie anche a te, vedo che avete capito tutti il mio problema e mi avete fornito 3 soluzioni diverse... quella di anthony è pure la più semplice da capire e c'è la versione per excel 2003 il che è ottimo..

ora cercherò di capirle bene tutte quante e vedere qual'è la migliore o la più facile da configurare per i miei scopi.

siete stati tutti gentilissimi, grazie ancora!

[holly7787]

p.s. conta valori da problemi se sopra c'è scritto del testo quindi la versione per xls 2003 non va bene perché nella prima riga ho l'intestazione.. adesso carico una foto così vedete cosa devo fare

[Anthony47]

L' informazione "basta usare CONTA.NUMERI al posto di CONTA.VALORI" non ho capito a cosa applicarla.

Adesso ho capito: Conta.Numeri va usata al posto di Conta.Valori nella MIA formula per XL2003...

Ciao

[holly7787]

devo chiedere ancora il vostro aiuto, stavolta mi sa che è ancora peggio spiego meglio cosa fa questa funzione così mi viene più semplice la spiegazione del problema.. la riga 2 ha nella colonna A la data 1/1/2005, la riga 3 ha 2/1/2005 e così via

sempre sul foglio che vedete di esempio sopra devo:

- calcolare nella colonna U "ma1" una media di n valori, dove n è parametrizzato, e ho queste condizioni
- se nella colonna I e J della stessa riga del valore calcolato in quel momento c'è un valore, che sarà numerico, non calcolare la media ma lascia il valore di T
- altrimenti calcola la media prendendo come riferimento di riga -n/2 valori e +n/2 valori, insomma come quello di prima, però questi valori vanno presi sempre se la colonna I e J non hanno un valore altrimenti si esclude quella riga e la media sarà di n-1
- per i numeri pari c'è la solita gestione che si prende il mezzo di quello prima e il mezzo di quello dopo e fanno media, questa volta però devo stare attento sempre alla colonna I e J che siano vuote, altrimenti non li considero

e questa è solo la parte semplice del lavoro che più o meno ho già scritto.. il bello arriva adesso

se non ho abbastanza valori questa volta non lascio la casella vuota ma vado a leggerli 365 righe più sotto cioè se faccio la media del 1/1/2005 e voglio che abbia n=4 devo prendere T di 1/1/2005 + T di 2/1/2005 + T/2 di 3/1/2005 + T di 31/12/2005 + T/2 di 30/12/2005 con sempre i controlli sulle condizioni di prima..

un bel macello? grazie a chi risponderà

[holly7787]

ah, ho una colonna con indicato se un anno è bisestile o no, in quel caso lo spostamento sarà di 366 righe... giusto per semplificare ancora un po' le cose no?

[Anthony47]

Il mio suggerimento e' che ti crei una colonna aggiuntiva dei dati di cui vuoi far la media, condizionando cosa riporti in questa colonna dalle tue regole; inoltre in testa ai dati dell' anno ti riporti N righe che corrispondono alle ultime N in coda all' anno, dove N e' la meta'+1 del massimo numero di valori che pensi di mediare (al limite 183 righe). Una cosa analoga dovrai ovviamente fare anche in coda.

A questa nuova colonna applichi la formula che ti avevo suggerito.

Ciao.

[holly7787]

grande idea! in un foglio ho copiato 3 serie dei valori di coeff. year (sono 365) filtrandoli con le condizioni dette prima, la SOMMA.SE(I="") è diventata quindi una semplice SOMMA() e il CONTA.SE(I="") è diventato un semplice CONTA.VALORI() che quindi restituirà sempre il valore corretto dei dati presenti, ora metto la formula di parametrizzazione e il gioco dovrebbe essere fatto!

visto che sto facendo questo lavoro per una tesi universitaria posso citarvi nei ringraziamenti? magari vi faccio anche avere la pagina