Un esercitazione interessante : Ricerca tra due
date con totale dei valori correlati.
Una delle necessità che spesso incontriamo nella realizzazione dei nostri
lavori è la possibilità di ottenere dei totali di valori inseriti in
una tabella (o elenco), relativi a due parametri che vogliamo poter definire
di volta in volta, e quindi variabili. Un tipico esempio potrebbe essere
quello di voler scegliere, tra due date, quanto fatturato, o incassato, o
comunque quanto è il totale dei valori registrati tra le due date, e magari
per tipi di prodotti, o argomenti, o quello che vi pare. Presento quindi un
esempio basato su una tabella dove avremo una colonna, la A, dove scriveremo
le date, e tre colonne (B,C,D) dedicate a tre tipi di prodotti, dove
registreremo gli incassi del giorno. Scopo della routine sarà quello i poter
selezionare due date, tra quelle presenti, ed ottenere il totale dei valori
dei tre prodotti, nel periodo specificato. Va da sè che la soluzione
presentata si presta a tutte le varianti che vorrete, quello che importa è
vedere le istruzioni impiegate. Sotto la tabella usata per l'esempio, nella
cella F4, il risultato della somma richiesta dei valori relativi alle tre
colonne dei prodotti, tra le date 12/01/03 e 25/01/03:
Come si diceva si
possono variare tutte le impostazioni, per esempio di chiedere la somma
colonna per colonna (basterà variare i riferimenti su cui operare, e/o
ripetere le istruzioni per tante colonne quante vorremo, oppure usare delle
InputBox per definire su quali riferimenti
colonna lavorare (vedi ultimo esempio)).
Ho basato questo
progettino su due InputBox che ci chiedono di
indicare le due date tra cui eseguire la somma dei valori di tutte e tre le
colonne comprese nel periodo. Le date vengono cercate nella Colonna
A, e ho inserito un controllo che avvisa se
la/le date inserite non figurano nell'elenco, e viene riproposta la
richiesta di inserire una data presente in elenco. Per quanto riguarda i
riferimenti che servono a definire la
zona da sommare, le colonne le abbiamo già, ma non sappiamo a priori quali
righe saranno interessate, e allora ho preparato una
Funzione Utente (enn) che cerca il
numero di riga relativo alle date che inseriremo nelle
InputBox e questi dati vengono memorizzati in due variabili. A questo
punto basterà legare le variabili "riga" alle colonne, per avere i
riferimenti necessari alla funzione =SOMMA (=SUM in inglese). Poichè anche
la funzione enn può "girare a vuoto" se non
viene trovata la data immessa, è stata munita di un controllo che la fa
uscire dalla funzione e che fornisce zero come risultato della funzione.
Vediamo le istruzioni, prima la Funzione Utente (vedi event. paragrafo
precedente in questa stessa sezione), e poi la procedura per la somma.
Function enn(Intervallo,
Valore)
If Valore = "" Then Exit Function
For Each c In Intervallo
If c.Value = Valore Then
Y = c.Row 'con Y prendiamo il numero di
riga corrispondente al Valore trovato
Exit For
End If
Next c
enn = Y 'enn sarà uguale al numero di
riga trovato in Intervallo
End Function |
e questa la
procedura
Sub mia()
Dim datauno As Date 'dichiarazioni
delle variabili necessarie. con datauno prendiamo il 'valore che verrà
fornito dalla prima inputbox, e bisogna definirla come data perchè il
'codice la renda come tale.
Dim datadue As Date 'come sopra ma
per la seconda inputbox.
Dim mioval, titolo, messaggio 'variabili
per le inputbox
Set Intervallo = Range("A4:A20") 'con
Intervallo definiamo il range su cui intervenire per 'cercare le date
10: 'rimando per
riavviare l'inputbox nel caso di prima data non trovata in
Intervallo
titolo = "Inserisci una data"
messaggio = "Scrivi la PRIMA data del periodo"
mioval = InputBox(messaggio, titolo)
If mioval = "" Then Exit Sub 'se non si
scrive nulla nella inputbox si esce
datauno = mioval 'datauno ora è
uguale alla data scritta nell'imputbox
rigauno = enn(Intervallo, datauno) 'si
ottiene il numero di riga con la "funzione utente" e si 'assegna a rigauno
If rigauno = 0 Then 'se rigauno è zero
(data non trovata) si dà il messaggio sotto
MsgBox "la data non è presente. Scegliere un'altra data"
GoTo 10 'e si ritorna a 10 per riavviare
la inputbox di richiesta data
End If
'si ripete sotto per la seconda data
20:
titolo = "Inserisci una data"
messaggio = "Scrivi la SECONDA data del periodo"
mioval = InputBox(messaggio, titolo)
If mioval = "" Then Exit Sub
datadue = mioval
rigadue = enn(Intervallo, datadue)
If rigadue = 0 Then
MsgBox "la data non è presente. Scegliere un'altra data"
GoTo 20
End If
'ora convertiamo i numeri di riga in
riferimenti della zona da sommare, decidendo noi che 'il primo numero
di riga si lega con la colonna B ed il secondo con la colonna D. E'
qui 'che possiamo decidere su quali colonne (o quale) intervenire:
rifuno = "B" & rigauno
rifdue = "D" & rigadue
'sotto assegniamo alla cella F4 la
funzione somma (è una cella come un'altra)
Range("F4").Formula = "=Sum(" & rifuno & ":" & rifdue & ")"
'Questa
sotto invece è una variante su come usare la funzione somma, al posto
di quella sopra, usando il "WorksheetFunction" spiegato nel paragrafo
precedente
'Dim miorang As Range 'questa riga può
essere omessa
'Set miorang = Worksheets("Foglio1").Range(rifuno & ":" & rifdue)
'Range("F4") = WorksheetFunction.Sum(miorang)
End Sub
|
L'esempio seguente
invece è modificato con l'aggiunta delle richieste (InputBox) di quali
Colonne (prodotti) intendiamo avere i totali.
In questo modo ampliamo la gestibilità della procedura, disponendo anche di
una scelta completa, su richiesta, della zona su cui vogliamo agire. Sarà
sufficiente alla prima richiesta, di inserire la lettera che identifica la
colonna e fare altrettanto con la seconda richiesta. Se vorremo, ad esempio,
solo il totale tra due date, ma solo della colonna B, scriveremo B sia nella
prima che nella seconda richiesta.
Sub miaricerca()
Dim datauno As Date
Dim datadue As Date
Dim mioval, titolo, messaggio
Set Intervallo = Range("A4:A20")
10:
titolo = "Inserisci una data"
messaggio = "Scrivi la PRIMA data del periodo"
mioval = InputBox(messaggio, titolo)
If mioval = "" Then Exit Sub
datauno = mioval
rigauno = enn(Intervallo, datauno)
If rigauno = 0 Then
MsgBox "la data non è presente. Scegliere un'altra data"
GoTo 10
End If
Range("G1").Value = rigauno
20:
titolo = "Inserisci una data"
messaggio = "Scrivi la SECONDA data del periodo"
mioval = InputBox(messaggio, titolo)
If mioval = "" Then Exit Sub
datadue = mioval
rigadue = enn(Intervallo, datadue)
If rigadue = 0 Then
MsgBox "la data non è presente. Scegliere un'altra data"
GoTo 20
End If
Range("G2").Value = rigadue
'ora inseriamo due InputBox per le
richieste delle o della
'colonna di cui vorremo i totali.
titolo = "Specifica la Colonna"
messaggio = "Scrivi la Colonna del prodotto di cui vuoi il totale"
mioval = InputBox(messaggio, titolo)
If mioval = "" Then Exit Sub
coluno = mioval 'coluno è
uguale alla prima lettera della colonna indicata
titolo = "Specifica la seconda Colonna"
messaggio = "Scrivi la Colonna del prodotto di cui vuoi il totale"
mioval = InputBox(messaggio, titolo)
If mioval = "" Then Exit Sub
coldue = mioval 'coldue
è uguale alla seconda lettera della colonna indicata
'ora convertiamo i numeri di riga
'in riferimenti della zona da sommare
rifuno = coluno & rigauno 'uniamo
la prima colonna e il primo n° di riga
rifdue = coldue & rigadue
'uniamo la seconda colonna e il secondo n° di riga
Range("F4").Formula = "=Sum(" & rifuno & ":" & rifdue & ")"
'quindi avvisiamo anche con un messaggio
'il totale così ottenuto
MsgBox "Il totale richiesto è " & Range("F4").Value & ""
End Sub |
con l'augurio che quest'idea sia gradita, buon lavoro.
|