Cerca Valori con piu' di Un Codizione

Question

Scrivo per trovare una soluzione al seguente problema:

su di foglio ("foglio1") sono presenti dati organizzati con i seguenti campi >>> Codice di Riferimento; Data Efficacia; Valore.

Uno specifico "Codice di Riferimento" puo' essere presente una o piu' volte (in una sequenza non definita) anche se con una "Data Efficacia" differente e  presentando il relativo "Valore". 

La necessita' e' di portare su di un altro foglio ("foglio2") il valore di un dato "Codice di Riferimento" valido per una "Data Efficacia" compresa all'interno di intervallo di date, tenendo conto che in un intervallo di date, per quello specifico "Codice di Riferimento", potrebbero esservi due (o piu') "Data Efficacia" che rispettano la condizione, nel qual caso dovrei recuperare il "Valore" legato alla "Data Efficacia" piu' recente.

NB: non vorrei utilizzate la formula 'MATRICE.SOMMA.PRODOTTO poiche' la risposta rallenta notevolemente, anche perche' nel medesimo file sono presenti gia' diverse altre formule.

Grazie.

Answer 1

Ho riletto meglio i requisiti e, confortato dai risultati della formula di Paolo (che saluto), ho sistemato la routine. Decisamente più lenta, però, rispetto alla formula. Come prevedibile del resto. Ma ha un suo perché, non in questo caso, certo. Per cui per la curiosità dei pochi posto la versione attuale che dà i seguenti risultati:

Questa la routine:

Public Function GetMaxVal(ByVal Tabella As Excel.Range _

                        , ByVal CdR As Variant _

                        , ByVal DataInizio As Variant _

                        , ByVal DataFine As Variant _

                        ) As Variant

On Error GoTo ErrH

Const adOpenStatic = 3

Const adLockReadOnly = 1

Dim cnn As Object 'ADODB.Connection

Dim rst As Object 'ADODB.Recordset

Dim sql As String

    Application.Volatile True

    Set cnn = CreateObject("ADODB.Connection")

    With ThisWorkbook

      'cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0" _

             & ";Data Source=" & .Path & "" & .Name _

             & ";Extended Properties=""Excel 8.0;HDR=YES""" _

             & ";"

      cnn.Open "Driver={Microsoft Excel Driver (*.xls)}" _

             & ";DBQ=" & .Path & "" & .Name _

             & ";"

    End With

    With Tabella

      sql = "SELECT TOP 1 [CdR], [DE], [V]"

      sql = sql & " FROM [" & .Worksheet.Name & "$]" _

                      & "[" & .Address(False, False) & "]"

      sql = sql & " WHERE"

      sql = sql & " [CdR]='" & CdR & "'"

      sql = sql & " AND"

      sql = sql & " [DE] BETWEEN" _

                & " #" & Format$(DataInizio, "yyyy-mm-dd") & "#" _

                & " AND" _

                & " #" & Format$(DataFine, "yyyy-mm-dd") & "#"

      sql = sql & " ORDER BY [DE] DESC"

      sql = sql & ";"

    End With

    Set rst = CreateObject("ADODB.Recordset")

    With rst

      .Open sql, cnn, adOpenStatic, adLockReadOnly

      If .BOF And .EOF Then

        GetMaxVal = ""

      Else

        GetMaxVal = .Fields.Item("V").Value

      End If

    End With

ExtP:

    On Error Resume Next

    rst.Close

    cnn.Close

    Set rst = Nothing

    Set cnn = Nothing

    Exit Function

ErrH:

    GetMaxVal = "#ERR!" & CStr(Err.Number)

    Resume ExtP

End Function

Answer 2

Ciao Paolo,

l'ho testata con 65534 record ed è molto veloce. Istantanea, direi. Farei una modifica:

=SE.ERRORE(INDICE(Foglio1!$C$3:$C$1001;MAX(SE((Foglio1!$A$3:$A$1001=A3)*(Foglio1!$B$3:$B$1001>=B3)*(Foglio1!$B$3:$B$1001<=C3);RIF.RIGA($A$3:$A$1001)-2;-1)));"")

Così mettiamo a posto "E".

Answer 3

In effetti qualcosa non va.

Con le date in ordine cronologico prova questa:

=SE.ERRORE(INDICE(Foglio1!$C$3:$C$1001;MAX(SE((Foglio1!$A$3:$A$1001=A3)*(Foglio1!$B$3:$B$1001>=B3)*(Foglio1!$B$3:$B$1001<=C3);RIF.RIGA($A$3:$A$1001)-2;"")));"")

matriciale, da confermare con Ctrl+Maiusc+Invio

Answer 4

Usando lo schema di Maurizio, che mi facilita sempre il lavoro :-), direi:

se le date sono tutte in ordine crescente:

=INDICE(Foglio1!C2:C1000;MAX(SE((Foglio1!A2:A1000=A2)*(Foglio1!B2:B1000>=B2)*(Foglio1!B2:B1000<=C2);Foglio1!C2:C1000)))

se invece sono in ordine sparso:

=INDICE(Foglio1!C2:C1000;CONFRONTA(A2&MAX(SE((Foglio1!A2:A1000=A2)*(Foglio1!B2:B1000>=B2)*(Foglio1!B2:B1000<=C2);Foglio1!B2:B13));Foglio1!A2:A1000&Foglio1!B2:B1000;0))

entrambe matriciali, una volta immesse confermare con Ctrl+Maiusc+Invio.

Answer 5

Ciao clod9,

prova allora a sperimentare questa soluzione, eventualmente da perfezionare:

Public Function GetMaxVal(ByVal Tabella As Excel.Range _

                        , ByVal CdR As Variant _

                        , ByVal DataInizio As Variant _

                        , ByVal DataFine As Variant _

                        ) As Variant

On Error GoTo ErrH

Const adOpenStatic = 3

Const adLockReadOnly = 1

Dim cnn As Object 'ADODB.Connection

Dim rst As Object 'ADODB.Recordset

Dim sql As String

    Application.Volatile True

    Set cnn = CreateObject("ADODB.Connection")

    With ThisWorkbook

      cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0" _

             & ";Data Source=" & .Path & "" & .Name _

             & ";Extended Properties=""Excel 8.0;HDR=YES"";"

    End With

    With Tabella

      sql = "SELECT TOP 1 [CdR], [DE], [V]"

      sql = sql & " FROM [" & .Worksheet.Name & "$]" _

                      & "[" & .Address(False, False) & "]"

      sql = sql & " WHERE"

      sql = sql & " [CdR]=" & CStr(CdR)

      sql = sql & " AND"

      sql = sql & " [DE] BETWEEN #" & Format$(DataInizio, "yyyy-mm-dd") & "#" _

                         & " AND #" & Format$(DataFine, "yyyy-mm-dd") & "#"

      sql = sql & " ORDER BY [V] DESC"

      sql = sql & ";"

    End With

    Set rst = CreateObject("ADODB.Recordset")

    rst.Open sql, cnn, adOpenStatic, adLockReadOnly

    GetMaxVal = rst.Fields.Item("V").Value

ExtP:

    On Error Resume Next

    rst.Close

    cnn.Close

    Set rst = Nothing

    Set cnn = Nothing

    Exit Function

ErrH:

    GetMaxVal = "#ERR!" & CStr(Err.Number)

    Resume ExtP

End Function

Io l'ho testata con i seguenti dati (Foglio1):

Ottenendo il seguente risultato (Foglio2):

EDIT: Noto che le tue condizioni non sono rispettate. Poco male, si può correggere. Per ora mi importa capire se il tuo problema di rallentamento permane anche con questa funzione utente.

Non mi metto a correggere la query perché mi aspetto che tu ci fornisca dei dati reali e dei casi reali, senza costringerci a inventarne. Grazie.