Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[Excel 2010] Individuare dati Univoci provenienti da 2 Fonti

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi rosseaux » 16/07/12 12:04

si si.....è vero quello dhe dice Patel....Cavolo è vero che mi sono dimenticato di togliere lo spazio davanti ai testi in rosso......l'ho fatto solo con le scritte nere....

kmq ho già pensato a rimediare...basta fare in excel sostituisci...Spazio con Niente..

detto questo vorrei capire...

se il problema è Office 2010...con Access ad esempio come potrei fare ???

.....mi faresti un grosso piacere patel se lo facessi elaborare Tu e poi me lo dessi....però se in futuro ho bisogno di fare un'altra operazione simile sarebbe bello che sia autonomo nel potermela cavare...e non che ti contatto...per chiederti di elaborarmelo... ;0)
rosseaux
Utente Senior
 
Post: 141
Iscritto il: 06/01/07 14:11
Località: Alfonsine (RA)

Sponsor
 

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi patel » 16/07/12 12:12

già l'operazione di togliere lo spazio manda in tilt Excel, inoltre ci sono altri caratteri non visibili in fondo alle stringhe, basta calcolare la lunghezza per accorgersene e questo vale anche per i neri.
patel
Utente Senior
 
Post: 124
Iscritto il: 24/04/12 16:03

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi Flash30005 » 16/07/12 12:59

rosseaux ha scritto:...però se in futuro ho bisogno di fare un'altra operazione simile sarebbe bello che sia autonomo nel potermela cavare...e non che ti contatto...per chiederti di elaborarmelo... ;0)


Questa frase cozza con questa

rosseaux ha scritto:.....mi faresti un grosso piacere patel se lo facessi elaborare Tu e poi me lo dessi....


Per la prima devi impegnarti a studiare il Vba
per la seconda, invece, sei un "professionista" ! 8)
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi rosseaux » 16/07/12 13:53

inoltre ci sono altri caratteri non visibili in fondo alle stringhe


non ho capito dove sono questi caratteri non visibili...

Comunque Flash !!! Dicevo così per dire che non ha senso se me lo faccio fare da Lui....rimango dipendente da Patel...

mi piace cercare il più possibile di diventare autonomo....cioè sarei come un meccanico che va a chiedere in prestito le chiavi inglesi all'altro meccanico.....ahahahah... ;)

ma alla fine di tutto questo Ambaradan....Io....che cosa devo fare per Univocizzare sto bendetto testo rosso ???? ;0)
rosseaux
Utente Senior
 
Post: 141
Iscritto il: 06/01/07 14:11
Località: Alfonsine (RA)

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi patel » 16/07/12 15:01

i tutte le parole, in fondo, ci sono vari spazi ed almeno 3 CHR$(9)
quello che devi fare è procurarti dei file puliti, senza caratteri strani e spazi, qual'è l'origine dei tuoi file ?
patel
Utente Senior
 
Post: 124
Iscritto il: 24/04/12 16:03

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi rosseaux » 16/07/12 19:19

non ho capito che tipo di carattere sia quello di cui parli tu ma forse si riferisce a qualcosa tipo tabulazione...

comunque l'origine è Questa..

Speriamo vada bene ... ;0)
rosseaux
Utente Senior
 
Post: 141
Iscritto il: 06/01/07 14:11
Località: Alfonsine (RA)

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi Flash30005 » 16/07/12 20:34

Non capisco, l'ultimo file postato FonteDati è corretto
comunque avevo già fatto il lavoro di pulizia stringhe
invio questo archivio che contiene 3 file testo corrispondenti alla colonnaA, ColonnaB e dati univoci ricavati con formula e macro

ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi Anthony47 » 16/07/12 20:41

Non riesco a sequire le discussioni (nessuna) per impegni superiori legati alle mia vacanze :D , quindi non so dove siete arrivati...
Con macro ho ottenuto dei tempi decorosi, sul file pubblicato, dell' ordine di 40-45 sec usando un oggetto "Dictionary" in una macro di questo tipo:
Codice: Seleziona tutto
Sub BxorA()
Dim myArrC(), myVArrA, myVArrB, LastA As Long, LastB As Long
Dim myD   'my Dictionary
Set myD = CreateObject("Scripting.Dictionary") 'Modalita' Late Binding
myD.RemoveAll   ' Clear del dictionary
Range("C1:C1000000").Clear
'
LastA = Cells(Rows.Count, 1).End(xlUp).Row
'LastA = 600000
LastB = Cells(Rows.Count, 2).End(xlUp).Row
'LastB = 200000
ReDim myArrC(1 To LastB)
[E1] = Timer
myVArrA = Range("A1:A" & LastA).Value
myVArrB = Range("B1:B" & LastB).Value
'Col A -> dictionary:
On Error Resume Next
For I = LBound(myVArrA, 1) To LastA
If Application.WorksheetFunction.IsText(myVArrA(I, 1)) Then
'    If Not myD.Exists(myVArrA(I, 1)) Then
        myD.Add Trim(myVArrA(I, 1)), Trim(myVArrA(I, 1))
    '    J = J + 1
'    End If
End If
Next I
On Error GoTo 0
[E2] = Timer
'Confronta col B col Dictionary:
For I = 1 To LastB
If Not myD.Exists(Trim(myVArrB(I, 1))) Then
    J = J + 1
    myArrC(J) = Trim(myVArrB(I, 1))
End If
Next I
'
'Carica risultato in col C
ReDim Preserve myArrC(1 To J)
If J < 65536 Then
    Range("C1:C" & UBound(myArrC, 1)) = Application.WorksheetFunction.Transpose(myArrC())
Else
    For I = 1 To J
        Cells(I, 3) = myArrC(I)
    Next I
End If
[E3] = Timer
End Sub

Se l' elenco risultante e' superiore a 65000 voci sara' necessario scaricare il contenuto di myArrC() una cella alla volta (anche con XL2010), altrimenti sara' possibile fare tutto con l' istruzione che contiene .Traspose; questo e' gia nella macro.
Col file pubblicato l' elenco e' di meno di 5400 voci, e l' operazione viene compita in circa 40 secondi totale; simulando un elenco di circa 120k voci vanno aggiunti circa 10 secondi.
Eventuali spazi iniziali e finali di col A e B (ce ne sono tantissimi, specie in col B) sono eliminati.

Guarda anche il messaggio di Flash sopra il mio.
Ciao a tutti
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13899
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi rosseaux » 16/07/12 20:59

Grande Flash !!! Credo sia questa la lista di Cui Parlavo...cioè solo gli Univoci della lista con il testo Rosso...sono circa un migliaio....una buona scrematura direi....ahahah...

Idolo come sempre Flash !!!

Grazie Mille anche a te Anthony e Grazie anche a Patel che ce l'ha messa tutta per aiutarmi..

Grazie Mille Ragazzi !!! ;0)
rosseaux
Utente Senior
 
Post: 141
Iscritto il: 06/01/07 14:11
Località: Alfonsine (RA)

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi Anthony47 » 16/07/12 21:21

Vedo che il problema e' risolto ma non ho capito come.. :-?

Comunque, per i posteri, l' uso di una Collection riduce ulteriormente i tempi a circa 15 secondi:
Codice: Seleziona tutto
Sub BxorAColl()
'wip con uso di Collection
'
Dim AColl As New Collection
'
Dim myArrC(), myVArrA, myVArrB, LastA As Long, LastB As Long
'Dim myD
'Set myD = CreateObject("Scripting.Dictionary") 'Modalita' Late Binding
'myD.RemoveAll   ' Clear the dictionary
Range("C1:C1000000").Clear
'
LastA = Cells(Rows.Count, 1).End(xlUp).Row
'LastA = 400000
LastB = Cells(Rows.Count, 2).End(xlUp).Row
'LastB = 100000
ReDim myArrC(1 To LastB)
[E1] = Timer
myVArrA = Range("A1:A" & LastA).Value
myVArrB = Range("B1:B" & LastB).Value
'Col A -> Collection:
For I = LBound(myVArrA, 1) To LastA
On Error Resume Next
If Application.WorksheetFunction.IsText(myVArrA(I, 1)) Then
    AColl.Add Trim(myVArrA(I, 1)), Trim(myVArrA(I, 1))
End If
Next I
On Error GoTo 0
[E2] = Timer
'Confronta col B con la Collection:
For I = 1 To LastB
    Err.Clear
    On Error Resume Next
        scrvar = AColl.Item(Trim(myVArrB(I, 1)))
        errNum = CLng(Err.Number)
    On Error GoTo 0
    If errNum = 5 Then 'missing...
        J = J + 1
        myArrC(J) = Trim(myVArrB(I, 1))
    End If
Next I
'
'Carica risultato in col C
ReDim Preserve myArrC(1 To J)
If J < 65536 Then
    Range("C1:C" & UBound(myArrC, 1)) = Application.WorksheetFunction.Transpose(myArrC())
Else
    For I = 1 To J
        Cells(I, 3) = myArrC(I)
    Next I
End If
[E3] = Timer
End Sub

Ri-ciao

PS: da domani "forse" riprendo a seguire con costanza...
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13899
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi Flash30005 » 16/07/12 23:29

Incredibile!
la macro di Anthony impiega 24 secondi a confrontare 250.000 su 760.000 stringhe, utilizzando un Pc portatile :eeh:

è da Guinness!!! :)

C'è solo un piccolo bug:
le parole "Vero" e "Falso" e FALSO (solo queste 3) vengono riportate pur essendo nella colonna A
Cosa strana è che "Falso" (minuscolo) non è nella colonna B (ci sono solo due voci tutte e due maiuscole) e malgrado ciò questa voce (in minuscolo) viene inserita nella colonna C

Sono forse delle parole chiave che "ingannano" la macro?

Ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi patel » 17/07/12 06:08

Comunque è un ottimo esempio di come si può ottimizzare un codice.
Complimenti Anthony !
patel
Utente Senior
 
Post: 124
Iscritto il: 24/04/12 16:03

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi rosseaux » 17/07/12 10:07

più Tardi voglio provare quella di Anthony....ma non ho capito una cosa.....

che significa L'uso di una Collection ??? ;0)
rosseaux
Utente Senior
 
Post: 141
Iscritto il: 06/01/07 14:11
Località: Alfonsine (RA)


Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi Anthony47 » 18/07/12 01:47

La "Collection" e' una organizzazione che ho dato ai valori di colonna A per poter poi determinare velocemente se ognuno dei valori di col B e' presente in colonna A; vista la lunghezza dei valori da esaminare l' uso di un array standard insieme a WorksheetFunction quali Match o Countif non e' fattibile.

Per quanto riguarda Vero e Falso, si tratta di un baco della macro; la correzione e' l' aggiunta di un If (gia' inserito in fase di preparazione della Collection):
Codice: Seleziona tutto
For I = 1 To LastB
If Application.WorksheetFunction.IsText(myVArrB(I, 1)) Then  'AGGIUNTA
    Err.Clear
    On Error Resume Next
        scrvar = AColl.Item(Trim(myVArrB(I, 1)))
        errNum = CLng(Err.Number)
    On Error GoTo 0
    If errNum = 5 Then 'missing...
        J = J + 1
        myArrC(J) = Trim(myVArrB(I, 1))
    End If
End If                                                        'AGGIUNTA
Next I

Purtroppo su 250k celle aggiunge 1.5 secondi ai tempi di elaborazione...
Ovviamente le istruzioni che scrivono in [E1], [E2] ed [E3] possono essere eliminate.

Ciao a tutti.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13899
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi Flash30005 » 18/07/12 08:58

Purtroppo delle tre voci in eccesso ne sono rimaste ancora due: "FALSO" e "VERO"

Ma ho scoperto che VERO viene eliminato se non viene posto alla fine della colonna A (aggiungendo una qualsiasi parola nella cella successiva)
per FALSO, invece, non viene eliminata dal confronto se non si aggiunge un apice sia sulla colonna A che sulla colonna B
solo così non viene vista come "parola chiave" infatti, così facendo, è anche possibile scriverlo in minuscolo altrimenti rimane impossibile fare anche questo. :eeh:

Ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [Excel 2010] Individuare dati Univoci provenienti da 2 F

Postdi Anthony47 » 19/07/12 02:21

Con la modifica fatta a me Vero e Falso non compaiono piu' tra i risultati. Ho usato i dati da te pubblicati qui: viewtopic.php?f=26&t=96141&p=551746#p551599

Ciao
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13899
Iscritto il: 21/03/06 16:03
Località: Ivrea

Precedente

Torna a Applicazioni Office Windows


Topic correlati a "[Excel 2010] Individuare dati Univoci provenienti da 2 Fonti":


Chi c’è in linea

Visitano il forum: albval, Marius44, patel e 77 ospiti