[Excel Vba] conta valori univoci e raggruppamento dei subtotali per fasce

Question

Ho una tbl xls che contiene

Nr_Doc; Importo;

vorrei, tramite vba, far inserire nella colonna chiamata fascia, la fascia entro cui si trova l'importo del documento.

Alla fine creare una tabella di riepilogo  in cui Conto i documenti e sommo gli importi raggruppandoli per fascia.

Ho due problemi:

• Il Nr_Doc può essere ripetuto più volte quindi per calcolare la fascia entro cui farlo rientrare devo fare la somma dei singoli importi
• Quando conto i documenti devo contare i valori univoci e quando li sommo devo considerarli tutti

Ecco qui di seguito un estratto della tabella (il 6000000 si ripete devo quindi contarlo 1 volta ma per determinare la fascia sommare i 4 valori)

Data reg.	descr	Segmento	N.Doc	Imp	Agente	mese	fascia
08/04/2015	entrata	cassa	8000000	1,00	CE	aprile	0-1
07/01/2015	entrata	cassa	7000010	5,00	CE	gennaio	1-5
07/01/2015	entrata	****	4000030	10,00	SUD	gennaio	5-10
07/01/2015	entrata	****	8000010	3,00	CE	gennaio	1-5
08/01/2015	entrata	****	8000060	15,00	CE	gennaio	+10
08/01/2015	entrata	****	7000120	1,00	CE	gennaio	0-1
08/02/2015	entrata	cassa	6000000	3,00	SUD	febbraio	5-10
08/02/2015	entrata	cassa	6000000	1,00	SUD	febbraio	5-10
08/02/2015	entrata	cassa	6000000	1,00	SUD	febbraio	5-10
08/02/2015	entrata	cassa	6000000	1,00	SUD	febbraio	5-10

Es. Tabella riepilogo (i volori sono casuali e non basati sulla tbl sopra):

	Gennaio		Feb
Cassa	Nr. Doc	Imp.	Nr. Doc	Imp.
0-1	1	1,00
1-5	2	6,00
5-10	1	16,00
+10	2	30,00
****
0-1
1-5
5-10
+10

il codice (per il calcolo della fascia) che stavo provando è questo ma ha il problema che non fa il sub totale del doc prima di controllare in quale fascia inserirlo perchè non so come posso farlo:

Public Sub fascia()

    Dim sh As Worksheet

    Dim sMese As String

    Dim lastRow As Long

    Dim riga As Integer

   Set sh = ThisWorkbook.Worksheets("Foglio1")

   With sh

   riga = 2

        lastRow = .Range("e" & .Rows.Count).End(xlUp).Row

        For riga = 2 To lastRow

        Select Case Range("e" & riga).Value

        Case Is <= 1

        .Range("h" & riga).Value = "meno di 1" 

        Case 1 To 5

        .Range("h" & riga).Value = "oltre 1 fino a 5"

        Case 5 To 10

        .Range("h" & riga).Value = "oltre 5 fino a 10"

        Case Is > 10

        .Range("h" & riga).Value = "oltre 10"

        End Select

        riga = riga + 1

        Next

   End With  

    Set sh = Nothing

End Sub

Answer 1

Provo a spiegare meglio anche con un file di esempio qui

http://1drv.ms/1Uz6f9m

I dati presenti dalla colonna "A" alla colonna "G" del "Foglio1" vengono incollati da un gestionale e di volta in volta i nuovi dati vengono accodati a quelli presenti (ultima riga piena da A a G).

I dati presenti dalla colonna "H" alla "J" ora li ho calcolati manualmente.

Nel Foglio "Pivot" c'è la tabella riepilogativa che desidero ottenere.

Io vorrei tramite tramite VBA

• far popolare automaticamente i dati presenti nelle colonne da H a J,
• creare automaticamente la tabella di riepilogo Pivot
• fare in modo che inserendo nuove righe sotto le colonne da A a G, il range della pivot si auto aggiorni e, anche per risparmiare risorse, vengano ricalcolati solo i valori corrispondenti delle righe da H a J

I codici sopra postati sono un modo "grezzo" per il calcolo delle informazioni da aggiungere da H a J:

• Per contare i documenti (visto che si possono ripetere) ho inserito la colonna H (se nrdoc_riga =  nrdoc_riga+1 scrivi 0 altrimenti 1)
• per raggruppare l'importo totale dei documenti in soglie, ho prima calcolato i subtotali per ogni documento e poi confrontato questi per le fasce desiderate (info colonna I e J). I sub totali li calcolerei con il codice del post precedente che non mi piace affatto, l'inserimento del range con il Select Case del post iniziale

Spero di essermi spiegato e grazie per eventuali suggerimenti

Nixio

Answer 2

Io non ho ancora capito cosa stai provando di fare e dubito di essere l'unico.

Puoi, per favore, spiegare nuovamente e, magari, mettere in condivisione un file?

Grazie.

Answer 3

ho provato ad andare avanti su questa faccenda provando a calcolare i "subtotali" ricorrendo ad un ciclo "DO LOOP UNTIL"

Per prima cosa ordino la tabella per "N.Doc" e poi ci "imbarchiamo" in almeno due cicli:

...

Riga = 2

Do

somma = somma + Cells(riga, ActiveCell.Column - 6).Value

riga = riga + 1

Loop Until Cells(riga, ActiveCell.Column).Value <> Cells(riga - 1, ActiveCell.Column).Value

'se la riga successiva è diversa esco dal loop e scrivo il risultato in una cella su stessa riga e 5 colonne avanti

Cells(riga - 1, ActiveCell.Column + 5).Value = somma

somma = 0

...

questa routine dovrebbe essere ripetuta per tutte le righe e quindi racchiusa in un clico for per esempio...

Onestamente questa soluzione non mi piace affatto...credo sprechi molte risorse (le righe su cui applicarla sarebbero al momento oltre 2.500), inoltre avrei così il subtotale solo in corrispondenza del cambio valore e mi rimarrebbero vuote le righe precedenti (anche se potrei riempirle con un copia/inclolla).

Per il conteggio avrei pensato di aggiungere una colonna in cui scrivere la funzione

s = "=IF(o2=o3,0,1)" e copiare fino all'ultima riga in modo poi da poter contare i doc univoci nella tbl pivot.

Spero possiate aiutarmi a migliorare / ottimizzare la parte per il calcolo del subtotale che così non mi piace affatto...

Grazie