Esercizio 13 Tabelle con riferimenti multipli

Hai un problema con Excel?

Nuova discussione

Esercizio 13: Tabelle con riferimenti multipli
di Luca73 data: 06/08/2015 09:23:17
Ciao a tutti
Eccomi con l’esercizio numero 13.
E’ lungo da spigare e mi ha messo davanti ad alcune difficoltà.
Il problema è reale.
Nella Società dove lavoro prepariamo molti documenti che mandiamo al cliente del progetto che li commenta mandandoci del CommentSheets a cui dobbiamo rispondere.
Ora l’esercizio sta nel preparare il riepilogo dei comment sheets per documento.
Ipotesi di lavoro: per ogni documento ci sono al massimo tre comment sheets ma mi piacerebbe eliminare questo vincolo.
Il File ha due fogli “Documenti” e “CommentSheets”.
Il Foglio “CommentSheets” contiene tuti i dati sui comment sheets e costituisce la base di input. I Dati Contenuti sono:
Colonna A Numero Di Documento (codifica del documenta cui si riferisce il comment sheets è univoco anche se per ogni documento esistono più revisioni).
Colonna B Revisione (Revisione del Documento in colonna A).
Colonna C Comment Sheets Code (codifica del comment sheets che è strutturata come numero di documento a cui si riferisce + la revisione di documento a cui siriferisce + un numero progressivo che vi prego di non considerare in quanto introdotto da me per puro debug e verifica siccome per lo stesso documento e revisione esistono più data sheets questo codice non è univoco).
Colonna D Received Date (data di ricezione del comment sheet).
Colonna E Answer Date (data di risposta del comment sheet: compilata solo la la risposta è stata inviata).
Il foglio “Documenti” è così composto
Colonna A Numero Di Documento (codifica dei Documenti codifica univoca se associata alla revisione) Colonna Precompilata
Colonna B Revisione (revisione del documento di cui in colonna A) Colonna Precompilata
Colonna C Comment Sheets Number (numero di comment sheets presenti nel foglio “Comment Sheets” relativi al documento di cui in colonna A e Revisione di cui in colonna B)
Colonna D Comment Sheets Code (codifica di tutti i comment sheets presenti nel foglio “Comment Sheets” relativi al documento di cui in colonna A e Revisione di cui in colonna B, quando il numero è maggiore di uno i codici vanno posti uno sotto l’altro)
Colonna E Received Date (date di ricezione dei comment sheets listati in colonna E con lo stesso ordine e le stesse modalità)
Colonna F Answered (numero di Comment sheets listati in colonna E a cui è stato risposto (ovvero hanno una data nbella colonna Answer Datedel foglio “Comment Sheets” – La struttura deve essere X su Y (ovvero risposti su totale)
Colonna G Answer OK (questa colonna assume tre valori: N/A se il documento non ha comment sheets, YES se tutti i commetnsheets sono stati risposti, NON se almeno uno dei comment sheets non è stato risposto)
Spero di essermi riuscito a spiegare. In caso contrario le domande sono benvenute e io sono a disposizione per risponderle.
Il file allegato è semi compilato con alcune righe di esempio che sono state colorate in giallo.
Ciao
Luca73
di ninai data: 06/08/2015 10:04:23
ciao
da quello che ho capito ad una prima analisi, il problema sono i campi D ed E il resto sono dei MATR.SOMMA.PRODOTTO(). Sarebbe un lavoro da VBA ma si può fare anche con formule, non complicate ma lunghe. Dipende da quanti commenti vuoi prevedere per ogni documento. Se vuoi prevedere molti più di tre commenti, la formula diventa kilometrica.

Intanto togliamoci il facile:

in C2:
=MATR.SOMMA.PRODOTTO(('Comment Sheeets'!$A$2:$A$78=A2)*('Comment Sheeets'!$B$2:$B$78=B2))

F2:
=SE(C2;MATR.SOMMA.PRODOTTO(('Comment Sheeets'!$A$2:$A$78=A2)*('Comment Sheeets'!$B$2:$B$78=B2)*('Comment Sheeets'!$E$2:$E$78<>""))&" su "&C2;"")

Nota , l'ultimo tuo documento di esempio non mi corrisponde, forse mancano dati)

in G2:
=SE(F2="";"N/A";SE(SINISTRA(F2;1)=DESTRA(F2;1);"YES";"NO"))

in attesa della "BOMBA" per le colonne D e E
di ninai data: 06/08/2015 10:37:27
per tre commenti:
in D2:
=SE.ERRORE(INDICE('Comment Sheeets'!C$2:C$78;AGGREGA(15;6;RIF.RIGA($A$2:$A$78)-1/(('Comment Sheeets'!$A$2:$A$78=$A2)*('Comment Sheeets'!$B$2:$B$78=$B2));1));"")&SE.ERRORE(CODICE.CARATT(10)&" "&INDICE('Comment Sheeets'!C$2:C$78;AGGREGA(15;6;RIF.RIGA($A$2:$A$78)-1/(('Comment Sheeets'!$A$2:$A$78=$A2)*('Comment Sheeets'!$B$2:$B$78=$B2));2));"")&SE.ERRORE(CODICE.CARATT(10)&" "&INDICE('Comment Sheeets'!C$2:C$78;AGGREGA(15;6;RIF.RIGA($A$2:$A$78)-1/(('Comment Sheeets'!$A$2:$A$78=$A2)*('Comment Sheeets'!$B$2:$B$78=$B2));3));"")
e trascini in basso ed a destra

sono tre moduli quasi uguali, per aumentare il numero di commenti, bisogna allungare il "brodo"
per versioni precedenti al 2010, vi è la solita sostituzione di AGGREGA() con PICCOLO(SE()) e diventa matriciale.
di Vecchio Frac data: 06/08/2015 11:44:00
hai saltato nua colonna o sbaglio io?
cit.:
"Il foglio “Documenti” è così composto
Colonna A Numero Di Documento ...
Colonna B Revisione ...
Colonna D Comment Sheets Number ..."

Comunque perdonami ma non afferro la richiesta: " l’esercizio sta nel preparare il riepilogo dei comment sheets per documento": che struttura diamo al riepilogo? qual è il campo chiave univoco su cui si basa il report? che campi finiscono nel riepilogo? scusa ma sono giù di zuccheri e non ragiono bene :(
di Luca73 data: 06/08/2015 12:24:02
Ciao VF
punto 1) la descrizione è stata corretta
punto 2) che struttura diamo al riepilogo?: il riepilogo è il foglio "Documenti" che va compilato (le righe gialle sono precompilate come esempio)
punto 3) qual è il campo chiave univoco su cui si basa il report? Il report si deve basare sull'accoppiata "Numero Di Documento" + "Revisione" che crea un'accoppiata univoca nel foglio "Documenti" (altrettanto non è vero per il foglio "CommentSheets" dove per le accoppiate "Numero Di Documento" + "Revisione" esistono più record)
punto 4) che campi finiscono nel riepilogo? vedi punto 1)

Su,.. dai... qualche caramella e gli zuccheri salgono.....
Comunque ho fatto fatica anche io a cercare di spiegarlo

Ciao
Luca
di Raffaele_53 data: 06/08/2015 13:20:14
Se si tratta d'ordinare i record in colonna A
Sembra un qualcosa del genere..., =STRINGA.ESTRAI(A2;10;16) a salire
Però ad una condizione, devono essere condizionati anche dalla colonna B e il numero finale in colonna C
Mà non saprei come ordinarli? Da qui però mi sembra facile. Forse sbaglio.
di Vecchio Frac data: 06/08/2015 13:44:40
cit. "qualche caramella e gli zuccheri salgono"
---> LOL, no, avevo proprio bisogno di mangiare :)
Comunque ho capito finalmente, è in pratica un riepilogo che raggruppa per numero e revisione, uno per riga, e mostra i record correlati. Nel file di esempio allora nella riga 42, relativa al documento B37-RC-D-020050 revisione B, che ha ricevuto una risposta il 06/07/2015 (foglio Comment Sheets, riga 27), progressivo 2, mancano l'indicazione "1/3" e "NO" in "Answer OK" (questo è per come ho capito io).
Se non ti fa schifo una proposta in VBA, posso provarci... in formule mi spiace ma non ce la posso fare :)
di Vecchio Frac data: 06/08/2015 13:54:17
Nell'esempio (come ha già fatto notare ninai) c'è una discrepanza tra numero di documento e comment sheets code nella riga in giallo del foglio Documenti n° 7, dove a "B85-RC-D-020094" fai corrispondere "B85-RC-D-020095_A_1" e "B85-RC-D-020095_A_2".
Correggo tanto per avere una base coerente su cui lavorare.
di Luca73 data: 06/08/2015 14:05:17
Ciao VF
tu eri in calo di zuccheri io.... mi sa pure...
Entrambe le osservazioni tue e quelle di ninai sono corrette...
Giusto per semplificare le cose Ri allego il file Esercizio_13_01.xlsx (se possibile cancellare quello precedente)

VBA accettato...

Ciao
Luca
di Luca73 data: 06/08/2015 14:11:13
@ niinai
ti manca un pezzettino nella sezione date, la formattazione... al momento vengono importati come numeri tipo 42150...
per il resto mi sembra OK
vedo che alla fine hai usato lo stesso metodo che avevo usato io con formule diverse.
Ciao Ciao
di ninai data: 06/08/2015 17:24:46
cit.
"ti manca un pezzettino nella sezione date, la formattazione... al momento vengono importati come numeri tipo 42150..."

vabbè, a cercare il pelo nell'uovo, credevo che dopo tanto tempo le avevi imparate a memoria e le convertivi a mente
di cromagno data: 07/08/2015 01:10:23
Ciao a tutti
ho aspettato di avere un pò di relax (anche dal caldo) per iniziare a leggere tutto quel "popò" di roba
A differenza di ninai, la funzione AGGREGA non sono ancora riuscito a farla entrare nella mia "routine", quindi ho optato per le più classiche.

cit. "per ogni documento ci sono al massimo tre comment sheets ma mi piacerebbe eliminare questo vincolo. "
ho provato a pensare a qualcosa ma poi ha iniziato a farmi male la testa e ho lasciato perdere...
mi sà che l'unica è ricorrere al VBA.

Lascio il file in allegato...
'Notte

P.S.
C'è il piccolo inconveniente di dover regolare l'altezza delle righe (tramite "Testo a capo") dopo l'inserimento delle formule. Non so se anche a voi che avete utilizzato le formule succede la stessa cosa.

di Vecchio Frac data: 07/08/2015 11:01:54
Altra correzione, se volete spaccare il capello in quattro: il foglio dovrebbe chiamarsi "Comment Sheets" e *non* "Comment Sheeets" ^_^
Comunque... ho partorito questo codice. Spero che vi piaccia ^_^
In pratica si tratta di fare una subquery su una query che raggruppa per l'accoppiata "numero documento" + "revisione".
L'inserimento dei dati nelle celle è conforme alla specifica del committente.
Gira senza problemi nel file di esempio anche se ho dovuto inserire un correttivo testando IsNull che stranamente non son riuscito a capire da dove salta fuori (test su un altro foglio con dati puliti non danno mai risultati di righe nulle... pazienza).

Option Explicit

Sub RecordSetFromSheet()
Dim rs As Object, rs2 As Object
Dim cn As Object, cmd As Object, j As Long
Dim s(1 To 2) As String, answered As Integer

    Sheets("Documenti").Activate
    Range("a2:g1000").ClearContents
    
    Set rs = CreateObject("ADODB.Recordset")
    Set rs2 = CreateObject("ADODB.Recordset")
    Set cn = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")

    'setup the connection
    '[HDR=Yes] means the Field names are in the first row
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source='" & ThisWorkbook.FullName & "'; " & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"
        .Open
    End With

    'setup the command
    Set cmd.ActiveConnection = cn
    cmd.CommandType = 1     'adCmdText
    cmd.CommandText = "SELECT DISTINCT [numero di documento] & revisione AS unique_keys " & _
                      "FROM [Comment Sheets$] " & _
                      "ORDER BY [numero di documento] & revisione"
                      
    rs.CursorLocation = 3  'adUseClient
    rs.CursorType = 2      'adOpenDynamic
    rs.LockType = 3        'adLockOptimistic
    
    rs2.CursorLocation = 3  'adUseClient
    rs2.CursorType = 2      'adOpenDynamic
    rs2.LockType = 3        'adLockOptimistic
    
    'open the connection
    rs.Open cmd

    rs.movefirst
    j = 1
        While Not rs.EOF
            If Not IsNull(rs("unique_keys")) Then
                j = j + 1
        
                cmd.CommandText = "SELECT * FROM [Comment Sheets$] WHERE [numero di documento] & revisione = '" & rs("unique_keys") & "'"
                rs2.Open cmd
                'rs2.Filter = "[numero di documento]='" & rs("numero di documento") & "' and revisione='" & rs("revisione") & "'"
                rs2.movefirst
                Cells(j, "A") = rs2("numero di documento")
                Cells(j, "B") = rs2("revisione")
                Cells(j, "C") = rs2.RecordCount
                s(1) = "": s(2) = "": answered = 0
                While Not rs2.EOF
                    s(1) = s(1) & rs2("Comment Sheets Code") & Chr(10)  'comment sheet code
                    s(2) = s(2) & rs2("Received date") & Chr(10)        'received date
                    If Trim(rs2("answer date")) <> "" Then answered = answered + 1
                    rs2.movenext
                Wend
                Cells(j, "D") = Left(s(1), Len(s(1)) - 1)
                Cells(j, "E") = Left(s(2), Len(s(2)) - 1)
                Cells(j, "F") = answered & " su " & rs2.RecordCount
                Cells(j, "G") = IIf(answered = rs2.RecordCount, "YES", "NO")
                rs.movenext
                rs2.Close
            Else
                rs.movenext
            End If
        Wend
    
    'disconnect the recordset
    Set rs.ActiveConnection = Nothing
    rs.Close
    Set rs = Nothing
    'cleanup
    If CBool(cmd.State And 1) = True Then Set cmd = Nothing
    If CBool(cn.State And 1) = True Then cn.Close
    Set cn = Nothing

    MsgBox "Done.", vbOKOnly + vbInformation, "Succesful"
End Sub

di Vecchio Frac data: 07/08/2015 11:03:28
edit per chiarimento:
Nel codice è rimasto commentato un comando .Filter su rs2: il .Filter può essere eliminato definitivamente, lo avevo usato per prova ma poi non l'ho implementato nella versione definitiva.
di alfrimpa data: 07/08/2015 14:32:37
Ciao VF

Dopo aver visto il tuo codice io agli esercizi manco mi avvicino

Comunque, a parte gli scherzi, trovo il mondo ADO e ADODB affascinante.

Io credevo tale metodo si utilizzasse solo con dati esterni (tipicamente di Access) ed invece, a quanto vedo, lo si può usare anche in database presenti nella cartella di lavoro attiva. È corretto?

Quando hai tempo (e soprattutto voglia) potresti proporre un esempio più semplice giusto per capire le enormi potenzialità di questo metodo (anche se credo che la materia è piuttosto complessa ed occorra anche una certa conoscenza del linguaggio SQL)?

In alternativa avresti da segnalarmi qualche link (per te efficace) che tratti l'argomento?

Chiedo scusa per l'OT e ti ringrazio sin d'ora.

Alfredo
di Vecchio Frac data: 07/08/2015 14:45:39
cit. "lo si può usare anche in database presenti nella cartella di lavoro attiva. È corretto? "
---> Evidentemente sì, e il bello è che non serve neanche chiudere il file ^_^
Comunque avevo pensato inizialmente a riempire una Collection (o un Dictionary, per far felice toty) e poi eseguire dei raggruppamenti eliminando i duplicati sulle chiavi (ma dovevo poi ogni volta che trovavo un duplicato riscrivere le eventuali sottorighe e quindi eliminare la chiave, e riscriverla coi valori aggiunti).
Alla fine ho pensato che me la sarei cavata con i recordset disconnessi per sfruttare le potenzialità delle query di ADO... ma già che c'ero ho fatto nu passo in più e ho fatto leggere ad ADO direttamente la tabella originale (che tra parentesi si può restituire con il metodo Range.CopyFromRecordset, ma non espone funzioni di filtro).

cit. potresti proporre un esempio più semplice"
---> In ufficio uso con successo questa tecnica da Word a Excel e dovrei avere qualcosa da parte... si possono usare tutti gli strumenti del linguaggio SQL, e alla fine basta poco per smanettare anche se una base è meglio averla.

Qualche link? tanti, la maggior parte dei quali in inglese.
di alfrimpa data: 07/08/2015 14:57:20
Cit. VF "Qualche link? tanti, la maggior parte dei quali in inglese."

Su questo non avevo alcun dubbio.

Se mi segnali qualcuno che ritieni efficace ti ringrazio

Alfredo
di Vecchio Frac data: 07/08/2015 15:08:51
E' un po' imbarazzante, non saprei. Spesso ne leggo diversi prima di farmi l'idea.
In Google scrivo ad esempio "Excel ADODB" e si apre il mondo. Spesso Stackoverflow è una buona fonte di ispirazione anche se tende a essere dispersivo causa troppo materiale.

Ti farei partire da qui comunque: Link al supporto Microsoft
di Raffaele_53 data: 08/08/2015 08:19:14
Scusatemi
Mi sembra che il primo quesito da risolvere sia ordinare la colonna A
Qualcuno c'è riuscito con solo formule?
di ninai data: 08/08/2015 11:08:42
ciao Raffaele
quello che dici tu si potrebbe anche fare ma l'esercizio prevedeva che le prime due colonne erano precompilate
di Raffaele_53 data: 08/08/2015 11:16:56
Ciao ninai, intanto pensavo solo alle formule
Non sapevo che erano precompilate.
In A2=INDICE('Comment Sheets'!A$2:A$78;RIF.RIGA()-1).......... Ok
Però a quella dopo mi risponde B85-RC-D-020091 e non B85-RC-D-020090 e non capisco il motivo?

Ps, Sheets e non Sheeets e di mezzo c'è una matriciale (forse sbaglio a ragionare)

di Raffaele_53 data: 10/08/2015 12:28:12
>>>Colonna A Numero Di Documento (codifica dei Documenti codifica univoca se associata alla revisione) Colonna Precompilata
>>>Colonna B Revisione (revisione del documento di cui in colonna A) Colonna Precompilata

Si è vero, mà sono quei dati che si leggono mai (eppure non sono in carattere microscopico)
Da buon pignolo mi ero chiesto, perchè non si può compilare il foglio "Documenti" direttamente da Comment Sheeets? Solo per provare se ero capace..., sperando sia giusto.

In Documenti metto in A2 formula1
=SE(MATR.SOMMA.PRODOTTO(('Comment Sheets'!A$2:A2='Comment Sheets'!A2)*('Comment Sheets'!B$2:B2='Comment Sheets'!B2))>1;"";INDIRETTO("'Comment Sheets'!A"&CONFRONTA(PICCOLO('Comment Sheets'!D:D;RIF.RIGA()-1);'Comment Sheets'!D:D;0)))

In Documenti metto in B2 formula2
=SE(MATR.SOMMA.PRODOTTO(('Comment Sheets'!A$2:A2='Comment Sheets'!A2)*('Comment Sheets'!B$2:B2='Comment Sheets'!B2))>1;"";INDIRETTO("'Comment Sheets'!B"&CONFRONTA(PICCOLO('Comment Sheets'!D:D;RIF.RIGA()-1);'Comment Sheets'!D:D;0)))

In Documenti metto in C2 formula3
=SE(A2="";"";MATR.SOMMA.PRODOTTO(('Comment Sheets'!A$2:A$87=A2)*('Comment Sheets'!B$2:B$87=B2)*('Comment Sheets'!D$2:D$87<>"")))

In Documenti D/E formule matriciali non sono capace

In Documenti metto in F2 formula4
=SE(A2="";"";MATR.SOMMA.PRODOTTO(('Comment Sheets'!A$2:A$87=A2)*('Comment Sheets'!B$2:B$87=B2)*('Comment Sheets'!E$2:E$87<>""))&" su "&C2)

In G lascio a Voi, perchè N/A non esisterebbe più.

di Luca73 data: 10/08/2015 16:40:05
Ciao VF
mi permetto di notare che il tuo codice non rispecchia pienamente la richiesta del committente.
Il tuo codice è potentissimo nel senso che maneggiando i dati come un DB e utilizzando query in poche righe maneggia dati in maniera molto efficace.
L'unico problema è che tu parti cancellando i dati sul foglio Documenti per poi riscrivere solo quelli che compaiono nel foglio Comment Sheet. Tale soluzione non va bene in quanto io non voglio cancellare i dati presenti in quanto arrivano da due fonti diversi. Nel file reale nel foglio Documenti ci sono altre colonne.

Non penso sia una cosa moto difficile dovrebbe bastare fare un ciclo di confronto tra il dato estratto e quello presente in colonna A e B.

Questo solo per precisione. In ogni caso mi hai aperto un nuovo mondo.... se ho voglia e tempo mi piacerebbe entravici...
Grazie mille
Ciao
Luca
di Vecchio Frac data: 10/08/2015 19:03:57
Io ho capito male questa tua frase: "il riepilogo è il foglio "Documenti" che va compilato". Pertanto sono partito dal Comment Sheets per ricostruire il foglio Documenti...
Naturalmente nel ciclo "While Not rs.EOF" ci sono i riferimenti ai vari Cells(j, "...") che si riferiscono alle celle del foglio attivo Documenti, per cui se si volesse applicare al caso concreto basterà aggiustare questi riferimenti per farli puntare alle celle reali.
L'importante per me era ricostruire la struttura proposta come esempio, e in effetti il codice si comporta bene ^_^
Ti auguro di trovare sia voglia che tempo per fare altri esperimenti :)
di Luca73 data: 13/08/2015 10:15:26
Ciao a tutti
innanzitutto permettete di ringraziare chiunque abbia partecipato all'esercizio.
In allegato il file con la soluzione mia e con quelle dei partecipanti (intere o parziali, formule o VBA)

Direi che il prossimo esercizio spetta a ninai per averni confermato che l'approccio da me pensato era l'unico fattibile, in seconda battuta VF per la compattezza del codice ed aver aperto uno spiraglio su nuovi mondi.

Chiedo scusa per la spiegazione lunga e, a quanto pare non sempre chiarissima; ma il fatto di conoscere a menadito i dati e poi dover spiegare ad estranei la situazione non mi ha aiutato.

Ciao
Luca
di Vecchio Frac data: 14/08/2015 13:36:22
cit. "Direi che il prossimo esercizio spetta a ninai"
---> Grazie a te perchè stavolta è stato impegnativo ^_^
Aspettiamo ninai (se ne ha tempo e voglia).

Vuoi Approfondire?