Agire su intervalli discontinui : Funzione Min() su gruppi discontinui di valori . - pagina vista: volte

Ovvero: ricercare il valore più basso in intervalli discontinui.

Requisiti: conoscenza dei cicli For..Next e While..Wend - utilizzo della proprietà WorksheetFunction

Un interessante esercizio, semplice tutto sommato, che soddisfa un'esigenza espressa da un "pellegrino", ma che potrà interessare per le implicazioni risolvibili; si dispone di una tabella come l'esempio sotto: in una colonna (la A) abbiamo dei valori, nella colonna B abbiamo degli indici (ma potrebbero essere nomi, date, orari, codici, ecc.ecc.) e comunque siano, li dobbiamo vedere come dei "criteri" di scelta, di identificazione.

Come si nota, nella colonna B, i valori (gli "1" o gli "0" sono i "criteri") sono raggruppati, conseguenti tra loro, ma a gruppi discontinui, e senza un preciso ordine o sequenza tra gruppi.

  A B C  
1 1500 0    
2 2100 0    
3 1785 1 954  
4 1520 1    
5 2200 1    
6 954 1    
7 1002 0    
8 1985 0    
9 1000 0    
10 1420 1 850  
11 850 1    
12 978 1    
13 1189 1    
14 1000 1    
15 2020 0    

Si vuole: per ogni gruppo di "1" nella colonna B (celle in giallo), ottenere il Minimo valore presente nei dati correlati al gruppo di "1", presenti nella colonna A (celle in verde). Il Minimo trovato, lo vogliamo porre nella colonna C, nella riga iniziale del primo "1" di ogni gruppo, che evidenzio in azzurro. Quindi nella Cella C3 ottenere il valore 954 che è il Minimo tra i valori presenti in A3:A6, nella cella C10 ottenere il valore 850 che è il valore Minimo tra quelli in A10:A14, e così via per quanto sarà lunga tutta la tabella dati.

Cerchiamo, tramite la proprietà WorksheetFunction (vedi articolo su questo sito "Funzioni Foglio in vba") un valore minimo (funzione MIN() ), ma potremmo cercare il massimo, oppure ottenere il totale dei valori correlati ad un criterio, oppure la media dei valori correlati, ecc. ecc..

L'esercizio deve servire soprattutto a:

  • identificare ogni intervallo contenente i "criteri"  di ricerca (nell'esempio: gli intervalli contenenti i gruppi di "1"), memorizzando gli estremi dell'intervallo.

  • gli estremi dell'intervallo ci serviranno per ricavare i relativi estremi di riga con i quali comporremo gli indirizzi di un'altro intervallo contenente i valori interessati ad una qualche azione (nell'esempio: ricavare il valore minimo presente, ma potremmo voler copiare i valori, oppure grassettare i fonts, o modificarne il colore, o ancora incrementarli di una certa percentuale, o ancora....). Per identificale gli intervalli useremo la sintassi Cells(riga,colonna).

  • rendere ciclica l'azione, quindi ripeterla per quanti saranno i "gruppi" di "criteri".

Come procediamo:

  • iniziamo un ciclo For..Next che inizia dalla riga 1 proseguendo fino all'ultima riga occupata, cerchi nella colonna B le celle contenenti il criterio scelto, cioè il numero 1

  • una volta trovato il primo 1, ne memorizziamo la riga in una variabile (Inizio), quindi iniziamo un secondo ciclo interno che scorra le celle a seguire, colonna B, fino a che non si trova un valore diverso (lo zero); di questo memorizziamo la riga - 1 in una altra variabile (Fine); le due variabili ci daranno quindi gli estremi di riga dell'intervallo del  "gruppo" di 1 trovati.

  • a questo punto otteniamo un intervallo i cui estremi di riga sono formati dalle variabili sopra ottenute, nella colonna A, e usiamo questo intervallo come argomento nella funzione WorksheetFunction.Min(), che posizioniamo nella prima riga dell'intervallo di 1 trovato, ma nella colonna C.

  • Ora dobbiamo proseguire nella ricerca degli 1, proseguendo nel ciclo For...Next, ma dobbiamo "scalare", continuando dall'ultima riga del primo gruppo di 1 (variabile "Fine"), quindi rendiamo il contatore "N" del ciclo uguale al numero di questa ultima riga. In questo modo scorriamo tutta la tabella "indicizzando" di volta in volta il numero di riga dal quale far continuare lo scorrimento del ciclo in funzione della "lunghezza" del gruppo di 1 (criterio) trovato.

Vediamo la routine: (le variabili, non dichiarate, sono tutte di tipo Variant)

Sub MinimoScalato()
Uro = [B65536].End(xlUp).Row  'prendiamo il numero dell'ultima riga occupata colonna B (limite sup. del ciclo)
Fine = 0   'impostiamo a zero la variabile Fine che indicherà l'ultima riga occupata da un gruppo di 1 letti
Cont = 0   'impostiamo a zero la variabile Cont che usiamo per incrementare il n. riga nel ciclo interno While..Wend
Inizio = ""  'impostiamo a "vuoto" la variabile Inizio che indicherà la riga di ogni primo 1 trovato in B
For N = 1 To Uro  'iniziamo il ciclo esterno che scorrerà le celle, e "N" sarà il numero riga della cella scorsa
If Cells(N, 2) = 1 Then  'controlliamo se il valore della cella ora letta è uguale al "criterio", cioè 1; se uguale allora
Inizio = Cells(N, 2).Row  'con "Inizio" memorizziamo il numero di riga
Cont = Inizio      'e rendiamo la variabile Cont, che era a zero, al valore di riga letto con Inizio
While Cells(Cont, 2) <> 0  'quindi iniziamo il ciclo While che scorrerà le celle fino a che sono diverse da zero
Cont = Cont + 1      'incrementiamo il numero di riga Cont di una unità fino a che...
Wend    '...raggiunta la condizione che il valore letto non è diverso da zero, quindi quando sono finiti gli 1, termina il 'ciclo While
Fine = Cont - 1  ' assegniamo a "Fine" il numero di riga ora trovato, meno 1 (quindi l'ultimo 1 del gruppo)
Myrange = Range(Cells(Inizio, 1), Cells(Fine, 1))  'e impostiamo la variabile Myrange come intervallo su cui valutare la 'funzione Min()
Cells(Inizio, 3) = Application.WorksheetFunction.Min(Myrange)   'quindi si assegna il risultato alla cella(riga "Inizio", 'colonna 3, la C)
N = Fine  'e ora si rivaluta il contatore N, impostandolo al valore di Fine
End If
Cont = 0  'quindi si riazzera la variabile Cont
Next
End Sub

Ricapitolando, la cosa forse più interessante, è l'aver reso modificabile l'intervallo su cui agisce la variabile Myrange, grazie alla sintassi Cells e agli indici "Inizio" e "Fine", dove gli indici di riga si adattano all'intervallo corrispondente ai criteri trovati nel ciclo While..Wend.

Giusto per i meno esperti in tema di cicli, chiarisco che in un ciclo For..Next, le istruzioni si ripetono tante volte quanto indicato dai due limiti del ciclo, e per quello usato sopra: da 1 fino alla fine dell'elenco; la variabile che identifica il contatore del ciclo, nell'esempio la variabile "N", assume quindi all'inizio il valore minimo indicato, cioè 1, e si incrementa naturalmente di 1 ad ogni Next, (che vorrebbe dire: passa al secondo valore tra quelli previsti dai limiti) . Se usiamo quindi la variabile "N" come riferimento alla riga, nella sintassi Cells(N, colonna), ci spieghiamo come si possano scorrere tutte le celle di un'intervallo, infatti N sarà all'inizio 1, poi ad ogni Next 2, poi 3, ecc. ecc.. Se però dobbiamo intervenire per "modificare" l'indice di riga perchè dobbiamo passare ad una determinata riga, possiamo reimpostare il valore del contatore come nell'esempio sopra dove N, che al momento in cui si trova il primo criterio 1, equivaleva a 3 (il primo 1 è nella terza riga), avevamo bisogno di passare col successivo Next, non già alla riga 4, ma alla fine del gruppo degli 1 iniziato dalla riga 3, cioè alla riga rappresentata da "Fine", il cui valore l'abbiamo reperito con in ciclo interno While...Wend.

 

Buon lavoro.

prelevato sul sito www.ennius.altervista.org