Conta valori tra date diverse

[tod2000Partecipante tod2000]

buongiorno,

ho una serie molto lunga di date in colonna B e altrettanti valori in colonna C, D, E

quello che vorrei è che vengano conteggiati i dati per singola Data.

questa formula =SE(CONTA.SE($E$2:E2;E2)=1;MATR.SOMMA.PRODOTTO(($B2:B6=B2)*(E2:E6=E2)*(D2:D6));"") funziona correttamente se però la inserisco manualmente ogni volta che cambia la data e poi la trascino. Io vorrei inserie la formula una sola volta ed ottenere lo stesso risultato. Se non è possibile chiedo se si può risolvere con una macro di cui non sono esperto.

Allego un file con l'esempio per chiarire meglio

Ringrazio per l'aiuto

Allegati:

You must be logged in to view attached files.

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

La tua formula in O2 dell'esempio è giusta, ma devi fare una piccola correzione:

O2: =SE(CONTA.SE($N$2:N2;N2)=1;MATR.SOMMA.PRODOTTO(($K2:K6=K2)*(N2:N6=N2)*(M2:M6));"")

perchè puntava a B e non a K ($B2:K6=K2).

Con la correzione (matriciale) e poi trascinando giù, si ottiene il risultato che indici con "qui deve risultare ..."

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Ops, forse sono stato affrettato, perchè tu devi raggruppare per date, e a priori non sappiamo quante date ci siano in un gruppo. Uff, io e le formule non andiamo d'accordo   

[Luca73Partecipante Luca73 Karma: 58 pts]

Ciao

Prova con

=IF(SUMPRODUCT(--($K$2:K2=K2);--($N$2:N2=N2))=1;SUMPRODUCT(--($K$2:$K$200=K2);--($N$2:$N$200=N2);$M$2:$M$200);"")

SUMPRODUCT è MATR.SOMMA.PRODOTTO in italiano

io ho ipotizzato dalla riga 2 alla riga 200 nulla vieta di estendere

Luca

 

[tod2000Partecipante tod2000]

Ciao Luca73; perfetto. funziona tutto. Però mi spiegi il doppio meno nella formula?

E se non chiedo troppo, è possibile fare altrettanto con una macroO

grazie

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

tod2000 ha scritto:

è possibile fare altrettanto con una macroO

Ecco qui la mia proposta, buttata giù in fretta, tanto in fretta che ha una piccola imprecisione che non ho tempo di correggere (non nei dati, ma nella presentazione). La vedete? 😀

Assumendo il tuo ultimo file di esempio, leggermente modificato perchè lavora sulle colonne da J in avanti, il campo "Q.tà" è diventato "Qta", e ho aggiunto una colonna per il lavoro per non sporcare l'originale...

Comunque posto anche il mio file così lo capite meglio.

Option Explicit

Sub group()
Dim objConnection As Object
Dim rs As Object
Dim rstmp As Object
Dim s As String
Dim f As Range
Dim g As Range

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

    
    s = ThisWorkbook.FullName
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set rstmp = CreateObject("ADODB.Recordset")
    
    objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & s & ";Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";"
    
    
    rs.Open "SELECT DISTINCT Data FROM [Foglio1$J1:P16]", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
        
    Dim i As Integer
    
    Do Until rs.EOF
        rstmp.Open "SELECT sum(qta) as sumqta, descriz FROM [Foglio1$J1:P16] WHERE data = #" & rs("data") & "# GROUP BY descriz  ", _
            objConnection, adOpenStatic, adLockOptimistic, adCmdText
        
        s = ""
        While Not rstmp.EOF
        
            If InStr(s, rstmp("descriz") & "|") = 0 Then
                Set f = Range("K2:K16").Find(rs("data"))
                Set g = Range(f, f.Offset(rstmp.RecordCount)).Offset(, 3).Find(rstmp("descriz"), lookat:=xlWhole)
                s = s & rstmp("descriz") & "|"
                g.Offset(, 1) = rstmp("sumqta")
            End If
            
            rstmp.movenext
        Wend
        s = ""
        rstmp.Close
        rs.movenext
    Loop
    
    rs.Close
    objConnection.Close
    Set rs = Nothing
    Set rstmp = Nothing
    Set objConnection = Nothing
End Sub

Allegati:

You must be logged in to view attached files.

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Errorino corretto... non riallego tutto, solo il codice.

Adesso è proprio conforme alla richiesta (spero)  🙂

Option Explicit

Sub group()
Dim objConnection As Object
Dim rs As Object
Dim rstmp As Object
Dim s As String
Dim f As Range
Dim g As Range
Dim i As Long

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

    
    s = ThisWorkbook.FullName
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set rstmp = CreateObject("ADODB.Recordset")
    
    objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & s & ";Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";"
    
    
    rs.Open "SELECT DISTINCT Data FROM [Foglio1$J1:P16]", _
        objConnection, adOpenStatic, adLockOptimistic, adCmdText
        
    Do Until rs.EOF
        rstmp.Open "SELECT sum(qta) as sumqta, descriz FROM [Foglio1$J1:P16] WHERE data = #" & rs("data") & "# GROUP BY descriz  ", _
            objConnection, adOpenStatic, adLockOptimistic, adCmdText
            
        i = Evaluate("=COUNTIF(K2:K16,""" & Format(rs("data"), "mm/dd/yyyy") & """)")
        
        s = ""
        While Not rstmp.EOF
        
            If InStr(s, rstmp("descriz") & "|") = 0 Then
                
                Set f = Range("K1:K16").Find(rs("data"))
                Set g = Range(f.Offset(-1), f.Offset(i - 1)).Offset(, 3).Find(rstmp("descriz"), lookat:=xlWhole)
                s = s & rstmp("descriz") & "|"
                g.Offset(, 1) = rstmp("sumqta")
            End If
            
            rstmp.movenext
        Wend
        s = ""
        rstmp.Close
        rs.movenext
    Loop
    
    rs.Close
    objConnection.Close
    Set rs = Nothing
    Set rstmp = Nothing
    Set objConnection = Nothing
End Sub

[Autore]

Articoli