Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

FORMULA COMPLESSA

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

FORMULA COMPLESSA

Postdi Sasyjoe » 09/06/14 17:00

Ciao ragazzi.
Quando ho un rompicapo come al solito sono in cerca del vostro aiuto.

Questa volta il problema è il seguente:


Prima colonna (per esempio A) ho i giorni
Seconda colonna (per esempio B) ho degli importi che inserisco manualmente e sono variabili
Terza colonna (per esempio C) ho bisogno della formula che per il momento non trovo

01Giugno---------000---------
02Giugno---------000---------
03Giugno---------350---------Dal 01 al 02 (risultato della formula)
04Giugno---------000---------
05Giugno---------602---------Dal 03 al 04(risultato della formula)
06Giugno---------000---------
07Giugno---------000---------
08Giugno---------503---------Dal 05 al 07(risultato della formula)
09Giugno---------000---------
10Giugno---------105---------Dal 08 al 09(risultato della formula)

La prima volta che nella colonna B ho un valore >0 ho bisogno che la colonna C mi deve indicare il periodo rispettando questo criterio: partendo dal giorno 1 e terminando con il giorno di riferimento meno 1
[Se vedi, quando per la prima volta nella colonna B c'è un valore >0, ho nella colonna C "dal 01 al 02" quindi parte dal 1 e termina con giorno di riferimento meno 1]

Dalla seconda volta fino alla fine, ogni volta che ho un valore maggiore di zero nella colonna B ho bisogno che la C mi deve indicare il periodo rispettando questo criterio: prendo in considerazione il periodo successivo a quello precedente e partendo con il valore ottenuto precedentemente (in questo caso il 2) e ci aggiungo 1 e terminando di nuovo con il giorno di riferimento meno 1
[Se vedi, quando per la seconda volta nella colonna B c'è un valore >0, ho nella colonna C "dal 03 al 04" quindi parte dal 3 (che si ottiene sommando 2 che è l'ultimo giorno indicato precedentemente dalla formula in colonna C ed aggiungo 1) e termina con giorno di riferimento meno 1]


Il difficile è questo: Vorrei ottenere questo risultato con una formula uguale per tutte le celle della colonna C.

Al momento ci riesco con una colonna di appoggio ma non mi piace molto.

confido, COME SEMPRE, in VOI!!!

grazie e saluti sasyjoe
Sasyjoe
Utente Senior
 
Post: 404
Iscritto il: 04/05/12 13:27

Sponsor
 

Re: FORMULA DA GENIO

Postdi ninai » 09/06/14 20:28

una proposta un po tortuosa (sicuramente migliorabile)

in base a come hai esposto tu i dati (era meglio avere un file di esempio)

dati in A2:B11

in A: i dati sono del testo 01giugno, 02giugno ecc.
in B: i dati sono dei numeri
in A1; scrivi 01giugno
in B1 scrivi 1 (o un numero qualunque)
queste celle si possono nascondere

in C2: =SE(B2=0;"";"dal"&" "&TESTO(SINISTRA(INDICE($A$1:$A$11;CERCA(2;1/($B$1:B1>0);RIF.RIGA($B$1:B1)));2);"00")&" al "&TESTO(SINISTRA(A2;2)-1;"00"))
e trascini in basso
allego esempio:
https://www.dropbox.com/s/rwc4dgem2wjlv ... acile.xlsx
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: FORMULA DA GENIO

Postdi Sasyjoe » 10/06/14 19:19

Grazie per la risposta.

Approfitto per un'altro quesito.
Mi occore una formula per trovare il rif riga e rif colonna dell primo valore > 0 partendo dal basso verso l' alto.

Esempio: ho 10 valori da a1 ad a10

A1=0
a2=0
A3=4
A5=0
A6=0
A7=1
A8=0
A9=0
A10=0

In questo caso vorrei rif rigae rif colonna di a7. Perchè partendo dal basso (a10) verso l'alto (a1) il primo valore maggiore di zero è in a7.

Grazieee come sempre
Sasyjoe
Utente Senior
 
Post: 404
Iscritto il: 04/05/12 13:27

Re: FORMULA DA GENIO

Postdi ninai » 10/06/14 20:41

volendo usare un procedimento già visto prima:
="A"&CERCA(2;1/($A$1:$A$10>0);RIF.RIGA($A$1:$A$10))
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: FORMULA DA GENIO

Postdi Sasyjoe » 13/06/14 12:38

Non ottengo il risultato richiesto.

mi esce in tutte le celle A10.


cosa sbaglio?

partendo dal mio esempio, incollo la formula da te postata in cella B1 e scendo fino a b10 con copia ed incolla.
solo quando nella colonna ho un valore >0 in cella B mi dice il rif riga e rif colonna del primo valore che trova a salire.
Sasyjoe
Utente Senior
 
Post: 404
Iscritto il: 04/05/12 13:27

Re: FORMULA DA GENIO

Postdi ninai » 13/06/14 18:11

Sasyjoe ha scritto:
Mi occore una formula per trovare il rif riga e rif colonna dell primo valore > 0 partendo dal basso verso l' alto.

Esempio: ho 10 valori da a1 ad a10

A1=0
a2=0
A3=4
A5=0
A6=0
A7=1
A8=0
A9=0
A10=0

In questo caso vorrei rif rigae rif colonna di a7. Perchè partendo dal basso (a10) verso l'alto (a1) il primo valore maggiore di zero è in a7.

Grazieee come sempre


dalla tua richiesta io ho capito che ti serve avere l'indirizzo di cella, dell'ultimo valore della lista maggiore di zero (A7).
pertanto la formula non va trascinata ma collocata nella cella dove vuoi tale risultato, diversamente, ho capito male.
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: FORMULA DA GENIO

Postdi Sasyjoe » 14/06/14 09:53

Ok allora mi sono spiegato io male.

Al momento ho risolto con una colonna (la colonna B) di appoggio con la formula se(a1>0;rif.riga(a1);0) e copio fino da B1 a B10.

Poi con formula max con intervallo variabile mi trovo la posizione.

La mia richiesta era per ottenere il risultato che ottengo ma senza colonna di appoggio.

Grazie mille per la disponibilità
Sasyjoe
Utente Senior
 
Post: 404
Iscritto il: 04/05/12 13:27

Re: FORMULA DA GENIO

Postdi ninai » 14/06/14 11:44

scusa ma io non riesco a capire, puoi allegarmi un file di esempio con dati e risultati desiderati???
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: FORMULA DA GENIO

Postdi Sasyjoe » 14/06/14 14:48

Certo.
Appena sono al pc lo carico.
Sasyjoe
Utente Senior
 
Post: 404
Iscritto il: 04/05/12 13:27

Re: FORMULA DA GENIO

Postdi Sasyjoe » 21/06/14 09:34

eccolo.

http://rapidshare.com/share/10144B678DB ... 74F95DD4ED

spero che mi puoi essere di aiuto!
Sasyjoe
Utente Senior
 
Post: 404
Iscritto il: 04/05/12 13:27

Re: FORMULA DA GENIO

Postdi ninai » 21/06/14 11:27

ciao
credo che ci sono problemi a scaricare da Rapidshare
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: FORMULA DA GENIO

Postdi Sasyjoe » 21/06/14 12:22

Io riesco a scaricarlo anche se faccio il logout a rapidshare. Se vuoi mandami mail in pvt
Sasyjoe
Utente Senior
 
Post: 404
Iscritto il: 04/05/12 13:27

Re: FORMULA COMPLESSA

Postdi Anthony47 » 21/06/14 15:35

L' uso di colonne di appoggio e' un modo per risolvere in modo lineare anche i problemi piu' complessi. Inoltre e' anche un sistema che riduce (in questo caso) i tempi di elaborazione; non usandole, infatti, dovrai inserire in ogni cella (cioe' in tanti posti) gli stessi calcoli che altrimenti sarebbero presenti (una sola volta) nella colonna di appoggio. Quindi quando vedo usate colonne di appoggio io non mi scandalizzo, anzi!
Andando al tuo file, puoi semplificare leggermente la tua attuale formula in F3 da =SE(D3=0;"";SE(D3>0;"Dal "&GIORNO(INDICE(($A$2:A2);CONFRONTA(E3;$D$2:D2;0)))&"/"&MESE(INDICE(($A$2:A2);CONFRONTA(E3;$D$2:D2;0)))&" al "&GIORNO(A3)-1&"/"&MESE(A3))) a
Codice: Seleziona tutto
=SE(D3=0;"";SE(D3>0;"Dal "&TESTO(INDICE(($A$2:A2);CONFRONTA(E3;$D$2:D2;0));"gg/mm")&" al "&TESTO(A3-1;"gg/mm")))


Se invece vuoi fare a meno delle colonne di appoggio, in F3 puoi usare ad esempio la formula
Codice: Seleziona tutto
=SE(B3=0;"";"Dal " &TESTO(MAX($A$2;$A$2:A2*($B$2:B2>0));"gg/mm")&" al "&TESTO(A2;"gg/mm"))

Da confermare con Contr-Maiusc-Enter, non il solo Enter.
Io ho provato in colonna G
Immagine
host immagini

Ciao

PS: messaggio editato dopo la risposta di scossa
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13899
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: FORMULA COMPLESSA

Postdi Sasyjoe » 22/06/14 19:56

@ninai ti ho inviato mail.

Ciao.
Sasyjoe
Utente Senior
 
Post: 404
Iscritto il: 04/05/12 13:27

Re: FORMULA COMPLESSA

Postdi ninai » 22/06/14 20:07

Ciao
ho visto
Ma le proposte di Anthony credo siano esaustive ed ottimali rispetto alle mie formule iniziali (avevo inteso le date per come le avevi rappresentate nell'esempio iniziale)
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: FORMULA COMPLESSA

Postdi scossa » 22/06/14 20:16

Anthony47 ha scritto:L' uso di colonne di appoggio e' un modo per risolvere in modo lineare anche i problemi piu' complessi. Inoltre e' anche un sistema che riduce i tempi di elaborazione; non usandole, infatti, dovrai inserire in ogni cella (cioe' in tanti posti) gli stessi calcoli che altrimenti sarebbero presenti (una sola volta) nella colonna di appoggio.


Mi permetto di "insinuarti" un dubbio :)

http://spreadsheetpage.com/index.php/ti ... gaformula/

in particolare:
NOTE: You may think that using such a complex formula would cause the worksheet to calculate more slowly. In fact, you may find just the opposite: Using a single formula in place of multiple formulas may speed up recalculation. Any calculation speed differences, however, will probably not be noticeable unless you have thousands of copies of the formula.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: FORMULA COMPLESSA

Postdi Anthony47 » 23/06/14 00:55

Caro Scossa, lieto di leggerti.
L' unica certezza che ho e' che talvolta quello che dico e predico e' errato...
Pertanto ho voluto misurare il tempo di elaborazione usando formule con colonne di appoggio e senza colonne di appoggio.

Sono partito dal file pubblicato da Sasyjoe qui: viewtopic.php?f=26&t=102112&p=591544#p591473
1. ho lasciato le formule originali in D-E-F e ho aggiunto solo in G2 la formula a matrice che ho suggerito il 21-6 (viewtopic.php?f=26&t=102112&p=591544#p591485 ).

Ho inserito la seguente macro e l' ho eseguita una volta:
Codice: Seleziona tutto
Sub testaa()
Dim CBVal As Long, I As Long, myTim As Single, J As Long, K As Long
Application.ScreenUpdating = False
myTim = Timer
For K = 1 To 10
    For J = 1 To 200
        For I = 2 To Cells(Rows.Count, 1).End(xlUp).Row
            CBVal = Cells(I, 2).Value
            If Cells(I, 2) <> 0 Then Cells(I, 2) = 0 Else Cells(I, 2) = 1
            Cells(I, 2) = CBVal
        Next I
    Next J
    Range("H100").End(xlUp).Offset(1, 0) = (Timer - myTim)
Next K
Application.ScreenUpdating = True

End Sub

A questo punto ho cancellato le formule in D3:F16, e ho copiato la formula da G2 in G3:G16; ho rieseguito la macro.
La macro esegue 10 cicli di ricalcolo e al termine di ogni ciclo inserisce il tempo cumulativo di esecuzione (elapsed) in colonna H.
I tempi ottenuti sono questi:
Immagine
hostare immagini
(vedere colonna H).

In giallo i tempi con colonne di appoggio; in verde quelle senza colonne di appoggio. Senza colonne di appoggio il tempo e' ben del 3% piu' alto.
La spiegazione di questo peggioramento sta nel fatto che, senza usare colonne di appoggio, ognuna delle formule a matrice contiene calcoli che sono anche nelle formule precedenti, per cui certi calcoli vengono eseguiti non una volta ma tante volte.

La situazione peggiora man mano che l' area interessata dai calcoli aumenta:
-ad esempio, triplicando l' elenco (fino a riga 46), il peggioramento (formule a matrice vs colonne di appoggio) e' dell' 8%
-raddoppiandolo ulteriormente (fino a riga 91) il peggioramento passa al 17%

Diverso e' il caso, come presentato nell' articolo che hai linkato, dove i singoli calcoli intermedi sono propedeutici al calcolo di un singolo risultato (mentre nel nostro caso sono propedeutici al calcolo di N risultati).
E comunque, invece della formula =RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))) io suggerirei =MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))),999) che ha un calcolo in meno.

Ciao!
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13899
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: FORMULA COMPLESSA

Postdi ninai » 23/06/14 05:54

Ciao
interessante esperimento!!
Dalla spiegazione che dai al "fenomeno", se non dico cavolate, ogni procedura con CTRL+Maiusc+Invio, surrogandola con colonne di appoggio, sarebbe più "leggera".
w8 + Office 2010 Ita
ninai
Utente Senior
 
Post: 271
Iscritto il: 12/06/13 05:23
Località: prov. Messina

Re: FORMULA COMPLESSA

Postdi scossa » 23/06/14 06:33

Anthony47 ha scritto:Caro Scossa, lieto di leggerti.
L' unica certezza che ho e' che talvolta quello che dico e predico e' errato...
Pertanto ho voluto misurare il tempo di elaborazione usando formule con colonne di appoggio e senza colonne di appoggio.

Ciao,

scusami Anthony, sono stato troppo "sintetico": non mi riferivo al caso specifico (che non ho nemmeno approfondito) ma alla generica considerazione che hai fatto:
L' uso di colonne di appoggio e' un modo per risolvere in modo lineare anche i problemi piu' complessi. Inoltre e' anche un sistema che riduce i tempi di elaborazione;


P.S.: è abbastanza ovvio che se le colonne di appoggio ti permettono di evitare l'uso di matriciali saranno preferibili; ma a parità di formule utilizzate (mi riservo di fare qualche test) dovrebbe essere più efficiente evitarle.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: FORMULA COMPLESSA

Postdi Anthony47 » 23/06/14 14:53

@ninai:
Le formule a matrice sono in genere un buon strumento per condensare piu' calcoli nella stessa formula, ma sono sempre un' arma a doppio taglio (questo tra l' altro e' il motivo per cui rendo sempre esplicita la notazione a matrice tramite l' uso di Contr-Maiusc-Enter, evitando di "nasconderla" con funzioni "implicitamente a matrice").
A spanne, se lo stesso calcolo fatto una volta in una cella di appoggio serve tante volte allora e' meglio usare la colonna di appoggio; mentre se serve per il calcolo di un unico risultato probabilmente conviene integrarlo nella formula complessiva, se non altro per essere sintetici.
Ovviamente queste considerazioni hanno senso quando i tempi di esecuzione sono un reale problema.

Andando su un altro aspetto, l' uso di colonne di appoggio consente invece anche al neofita di calcolare in autonomia risultati complessi; considerando che in genere ci sono piu' colonne di quelle che servono al neofita la tecnica andrebbe suggerita piu' spesso.

Comunque ho modificato nel post precedente la frase
Inoltre e' anche un sistema che riduce i tempi di elaborazione; non usandole, infatti, dovrai inserire in ogni cella (cioe' in tanti posti) gli stessi calcoli che altrimenti sarebbero presenti (una sola volta) nella colonna di appoggio. Quindi quando vedo usate colonne di appoggio io non mi scandalizzo, anzi!

In
Inoltre e' anche un sistema che riduce (in questo caso) i tempi di elaborazione; non usandole, infatti, dovrai inserire in ogni cella (cioe' in tanti posti) gli stessi calcoli che altrimenti sarebbero presenti (una sola volta) nella colonna di appoggio. Quindi quando vedo usate colonne di appoggio io non mi scandalizzo, anzi!


Ciao a tutti.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13899
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "FORMULA COMPLESSA":


Chi c’è in linea

Visitano il forum: Nessuno e 157 ospiti