Condividi:        

formula matriciale con scarto non funziona

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 matriciale con scarto non funziona

Postdi wallace&gromit » 04/02/16 09:53

Ciao,
non è la prima volta che mi imbatto in cattivi funzionamenti della funzione SCARTO().
Parto dall'inizio:
il mio obiettivo è quello di isolare da un elenco (che corrisponde alle precipitazioni giornaliere) i valori che appartengono a determinate sequenze, cioè per esempio piogge che durano almeno 4 giorni oppure almeno 10 giorni, ecc.

Per fare questo ho prima di tutto usato delle colonne d'appoggio in cui faccio un controllo numerico delle sequenze partendo una volta dall'alto e una volta dal basso. In questo modo se trovo un 1 (primo giorno di precipitazione) nella prima colonna e un 4 (ultimo giorno di quella precipitazione) della seconda colonna, so che da quella riga in giù ho i dati di una precipitazione durata 4 giorni.

Sono riuscito poi a sintetizzare tutto in una formula matriciale che mi evita le colonne d'appoggio:
Codice: Seleziona tutto
=SE(MIN(SE(B7:B$30000=0;RIF.RIGA(B7:B$30000);1000))-MAX(SE(B$2:B7=0;RIF.RIGA(B$2:B7);0))>L$1;B7;0)

Per mio sfizio (ma anche perché non so se i dati finali supereranno questa cifra) volevo sostituire il riferimento a B30000 con la funzione "scarto". Doveva essere qualcosa così:
Codice: Seleziona tutto
=SE(MIN(SE(SCARTO(B7;0;0;CONTA.VALORI(B:B)+2-RIF.RIGA(B7);1)=0;RIF.RIGA(SCARTO(B7;0;0;CONTA.VALORI(B:B)+2-RIF.RIGA(B7);1));1000))-MAX(SE(B$2:B7=0;RIF.RIGA(B$2:B7);0))>L$1;B7;0)
ma mi dà #Valore (sia in excel 2003 che 2013).

Allego anche il file dove si vedono i miei tentativi (in particolare si vede come la funzione scarto usata da sola funziona).

http://www.filedropper.com/formulasequenzecumulatediverseda0b
se qualcuno avesse idee alternative per la formula è il benvenuto (vorrei evitare le macro)
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Sponsor
 

Re: formula matriciale con scarto non funziona

Postdi wallace&gromit » 04/02/16 12:01

apparentemente il problema non riguarda solo la funzione scarto ma anche indiretto, ho provato ad inserire questa formula, che presa da sola funziona, ma nella formula matriciale no:
Codice: Seleziona tutto
INDIRETTO("B"&RIF.RIGA()&":B"&CONTA.VALORI(B:B)+1)
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: formula matriciale con scarto non funziona

Postdi Anthony47 » 05/02/16 02:38

Eh, certi particolari ce li potrebbe spiegare solo canapone...

Ho scaricato il file, ma non ho capito che cosa vorresti calcolare; pertanto mi fermo a dire che potresti calcolare la durata delle giornate di pioggia continuative usando in H2 la formula
Codice: Seleziona tutto
=MIN(SE(B2:B32>0;"";RIF.RIGA(B2:B32)))-RIF.RIGA()
(contr-Maiusc-Enter) Poi copia verso il basso.
Viene riportato il totale dei giorni contigui, senza l'applicazione di una soglia, che comunque dovrebbe essere abbastanza immediata.
Spero possa servirti a qualche scopo.

Se invece chiarisci anche quale e' il calcolo finale da fare magari ci viene qualche idea in piu'.

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

Re: formula matriciale con scarto non funziona

Postdi wallace&gromit » 05/02/16 08:41

questa formula non fa tutto il lavoro che dovrebbe.
A me interessa estrarre i dati presenti in colonna B, che corrispondono ai mm di pioggia giornalieri, ma solo se sono caduti in un periodo di precipitazioni di tot giorni.
Per esempio in L1 ho inserito 4, significa che da L2 in giù mi aspetto di trovare solo i valori della colonna B che corrispondono a periodi di precipitazioni di almeno 4 giorni.
La formula matriciale che ho postato fa questo lavoro calcolando quale è la riga in cui trovo il primo 0 dopo la riga di riferimento e sottraendogli la riga in cui si trova l'ultimo 0 sopra, se la differenza è maggiore del numero in L1 allora mi inserisce il valore della colonna B a quella riga di riferimento.
Per fare questo ho dovuto impostare un limite inferiore da cui partire (ho inserito 30000), ma mi piacerebbe non doverlo fare, in modo da aggiungere tutti i valori che voglio, senza preoccuparmi di dove si trova il fondo dei dati (infatti non so a priori per quanti anni devo fare l'analisi, dubito che arrivo ai 100 anni, ma non si sa mai, oppure dovrò fare per esempio un calcolo sulle precipitazioni orarie e allora lì il limite di 30000 è subito raggiunto e superato), per questo volevo introdurre delle formule relative tipo scarto o indiretto, ma non funzionano.
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: formula matriciale con scarto non funziona

Postdi Anthony47 » 06/02/16 00:10

Quindi vuoi risportare in un'altra colonna, es la H, il dato di colonna B solo se il dato di col B fa parte di una sequenza di almeno N valori diversi da 0.
Prova in H2 la formula
Codice: Seleziona tutto
=SE(E(CONTA.SE(SCARTO(B2;0;0;$E$1;1);0)>0;CONTA.SE(SCARTO(B2;0;0;MAX(-$E$1;-RIF.RIGA());1);0)>0);0;B2)

(solo Enter, poi copiare verso il basso.

Presuppone che la soglia sia scritta in E1

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

Re: formula matriciale con scarto non funziona

Postdi wallace&gromit » 06/02/16 10:41

sarebbe bello evitare le formule matriciali, ma quella che proponi non funziona:
identifica correttamente l'inizio e la fine della serie, ma in mezzo non sempre.
Per esempio se il limite è 4 e la sequenza fosse 0,0,1,2,3,4,0,0,1,2,3
dovrebbe dare 0,0,1,2,3,4,0,0,0,0,0
invece dà 0,0,1,0,0,4,0,0,0,0,0
quindi mancano il 2 e 3 nella serie di 4.
Infatti non basta il criterio che il numero sia seguito o anticipato da tot valori diversi da 0, ma anche che la somma del conteggio dei valori diversi da 0 prima e dopo sia almeno di quel tot.
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: formula matriciale con scarto non funziona

Postdi wallace&gromit » 06/02/16 21:52

Intanto ho trovato un escamotage per riempire le celle intermedie:
Codice: Seleziona tutto
=SE(E(CONTA.SE(SCARTO(B2;0;0;$E$1;1);0)>0;CONTA.SE(SCARTO(B2;0;0;MAX(-$E$1;-RIF.RIGA());1);0)>0);SE(E(H1<>0;B2<>0);B2;0);B2)
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: formula matriciale con scarto non funziona

Postdi Anthony47 » 09/02/16 02:06

Mi pare che l'escamotage risolva; o no??

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

Re: formula matriciale con scarto non funziona

Postdi wallace&gromit » 09/02/16 08:56

Sì, ho provato la formula anche sul file completo e, rispetto alla mia versione matriciale, mi risparmia un bel po' di tempo di ricalcolo delle celle.

Rimane il dubbio del comportamento anomalo delle formule di riferimento come scarto e indiretto in ambiente matriciale... e non siamo riusciti a stuzzicare canapone!
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: formula matriciale con scarto non funziona

Postdi Anthony47 » 09/02/16 16:46

Il problema della formula e' nella parte SCARTO(B7;0;0;CONTA.VALORI(B:B)+2-RIF.RIGA(B7);1)
In particolare la parte RIF.RIGA(B7) se usata nell'ambito di una array formula restituisce un array (anche se di una sola cella), mentre SCARTO richiede un valore secco.
Per estrarre "un valore" si puo' usare Indice, portando la formula che avevi postato a
Codice: Seleziona tutto
=SE(MIN(SE(SCARTO(B7;0;0;CONTA.VALORI(B:B)+2-INDICE(RIF.RIGA(B7);1);1)=0;RIF.RIGA(SCARTO(B7;0;0;CONTA.VALORI(B:B)+2-INDICE(RIF.RIGA(B7);1);1));1000))-MAX(SE(B$2:B7=0;RIF.RIGA(B$2:B7);0))>L$1;B7;0)

(valida su riga 7 e soglia in L1)
Per funzionare oltre Riga 1000 quel 1000 andrebbe variato in 9999, o (meglio) in "" (stringa nulla)

Direi che l'escamotage che hai trovato e' piu' semplice, mangia meno memoria e meno cpu...

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

Re: formula matriciale con scarto non funziona

Postdi wallace&gromit » 09/02/16 17:36

ok, mo' capisco il problema della formula scarto() matriciale.
Comunque sì, mi tengo stretta l'altra formula funzionante.
Grazie ancora.
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21


Torna a Applicazioni Office Windows


Topic correlati a "formula matriciale con scarto non funziona":


Chi c’è in linea

Visitano il forum: Nessuno e 53 ospiti