Ottenere il MAX da un CONTA.SE multiplo sequenziale.

Spesso risulta difficile dare un titolo "sintetico" ad un articolo, un titolo che spieghi l'argomento trattato. La cosa migliore è quella di utilizzare la descrizione di una necessità o problema, e poi argomentarlo.

La necessità: si vuole individuare il numero massimo di numeri consecutivi con lo stesso valore (positivo o negativo) presenti in una serie contraddistinta da numeri positivi e negativi, e calcolare quindi il valore MAX tra i totali delle serie dei numeri positivi e il valore MAX tra i totali delle serie dei numeri negativi, tanto per capirci, una situazione del genere. dove otterremo il valore 9 come MAX di una sequenza di numeri positivi consecutivi (uguali o superiori a zero) e 6 come MAX di una sequenza di numeri negativi consecutivi (inferiori a zero, col segno meno davanti)

Tra le possibili soluzioni ne presento una mista:  a base di formule, formule matriciali, impiego di vba. Per quanto mi concerne io risolverei il problema utilizzando solo una procedura vba, ma mi sembra giusto proporre sul sito altre soluzioni, e quella di cui parliamo è una di queste. Altra cosa che spesso ricordo ai lettori, è che può essere opportuno usare "passaggi" intermedi per raggiungere un nostro scopo, in questo caso useremo una colonna dove inserire una prima fprmula condizionale che restituisca 1 o 0 a secondo che il valore posto nella cella della colonna C sia positivo o negativo.

Questo "passaggio" ci consente di ottimizzare il criterio di ricerca, ricerca che faremo compiere ad un ciclo For..Next con procedura vba che controllerà la colonna degli 1 e degli 0; risulterà infatti più facile compilare le istruzioni che cerchino gli 1 o gli 0 anzichè far eseguire comparazioni come cercare i "maggiori di zero (i valori positivi)" e/o "minori di zero (i valori negativi)".

Comunque la procedura vba ha il compito di fornire, in una terza colonna, i totali degli 1 o degli 0 trovati consecutivi, riportando  il contatore a 1 ad ogni variazione nella sequenza positivo/negativo (lo viceversa). Sono ricorso al vba perchè non ho trovato nelle formule, nessuna funzione o nessuna matriciale che esplicasse il concetto di "sequenzialità" (con somma) di una serie di valori, rispetto ad altri valori e ne consentisse la totalizzazione.

Le matriciali invece le useremo per confrontare la colonna degli 1 e 0 con la colonna dove appaiono i totali dei consecutivi, e restituire il MAX dei positivi e il MAX dei negativi, e le posizioniamo nelle celle H2 e H3, mentre usiamo la colonna E per inserire le formule condizionali e la colonna F per i risultati del ciclo vba; questa la situazione:

Nella colonna E, a partire dalla riga 2, inseriremo la seguente formula condizionale che "trascineremo" fino alla riga 35 (o dove finiranno i numeri in C):

  • =SE(C2>0;1;0)    formula che dice: se in C2 abbiamo un valore superiore a zero, allora mi restituisci 1, altrimenti zero

Nella colonna F otteniamo invece il risultato della macro (che leggiamo sotto) che lanceremo dal pulsante "LANCIO MACRO" e che restituisce il totale delle varie sequenze di 1 o di zero presenti in E.

le spiegazioni: impostiamo una variabile che funziona da contatore (cont) a 1, quindi iniziamo un ciclo For..Next che scorra tutte le celle della colonna C, a partire dalla riga 2 ("n" del ciclo inizia a 2), leggiamo il valore nella cella ("n") e la memorizziamo con la variabile "valore", che confrontiamo con il valore della cella immediatamente sottostante (If Cells(n + 1, 5)...); se si riscontrano valori uguali, si incrementa di 1 il contatore "cont" ad ogni ciclo (cioè ad ogni cella letta e confrontata uguale con la successiva); quando il confronto non sarà più uguale (else), si scarica nella cella a lato (stessa riga dell'ultima "n" uguale, ma nella colonna F) il valore del contatore, e si riporta il contatore a 1, reinizializzandolo, e continuando come prima, fino alla fine del ciclo.

Sub CercalaPippo()
Range("F:F").ClearContents       'puliamo la colonna F da precedenti risultati
cont = 1
For n = 2 To 36
valore = Cells(n, 5)  'leggiamo il contenuto nella cella "n", colonna E (la 5^) e lo memoriz. con "valore"
If Cells(n + 1, 5) = valore Then  'se la cella sottostante "n + 1" contiene lo stesso valore di "n", allora
cont = cont + 1                          'incrementiamo di 1 il valore rappresentato dalla variabile "cont"

Else                                           'altrimenti (se i valori sono diversi)
Cells(n, 6) = cont                        'si scarica il valore che ora avrà "cont" nella cella riga "n", colonna 6 (la F)
cont = 1                                     'si riporta i valore di "cont" a 1
End If

Next                                           'e si prosegue scorrendo le celle successive

End Sub

Questa macro predisporrà i totali relativi ai vari gruppi dei negativi o dei positivi conseguenti nella colonna F, e saranno questi totali ad essere presi in considerazione dalle due matriciali che cercheranno nella colonna E i valori 1 o i valori 0 e ne estrarranno il valore MAX presente; queste le due matriciali:

  • in H2 (o dove vorrete) :   {=MAX(F1:F35*((E1:E35)=1))}  - si cerca il MAX nell'intervallo (matrice) F1:F35 in corrispondenza degli 1 presenti nell'intervallo (matrice) E1:E35  e quindi nei numeri positivi in C

  • in H3 (o dove vorrete) :   {=MAX(F1:F35*((E1:E35)=0))}   - come sopra ma si cerca la corrispondenza agli 0 e quindi dei numeri negativi in C

E' evidente che se cambiassero i valori nella colonna C, andrebbe lanciata di nuovo la macro.

File d'esempio da scaricare: Sequenza_di_numeri.zip      8 Kb

Buon lavoro.

 

prelevato sul sito www.ennius.altervista.org