Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

MACRO excel per creazione indice

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

MACRO excel per creazione indice

Postdi sailingsimo » 14/11/14 14:58

Ciao a tutti, sono nuovo del forum e anche a secco di macro e VBA.
ho da lavorare con un database in excel molto grande (circa 363 mila righe x 50 colonne).
Dovrei aggiungere una colonna "indice" con dei valori.

Provo a spiegare più nel dettaglio (gli esempi di seguito si riferiscono al file di esempio che potete trovare a questo link:
https://www.dropbox.com/s/nik33vo2vmb1v ... xlsx?dl=0)

Risultato finale che vorrei ottenere: un valore nella col. E che sia uguale per tutte le righe aventi GRUPPO uguale

Matrice di riferimento: A2:E14

Spiegazione generale: la macro dovrebbe calcolare la somma delle volte che ogni partecipante di un determinato gruppo ha fatto parte di altri gruppi dove erano presenti anche uno o più partecipanti di quel determinato gruppo, divisa per il numero di partecipanti del gruppo. Ogni co-presenza di due partecipanti ad un gruppo vale 1 se entrambi i partecipanti presentano il valore “no” nella rispettiva colonna C, mentre vale 1,5 se almeno uno dei due presenta il valore “si”.
Spiegazione analitica: Prendiamo ad esempio il gruppo 123 e partiamo dalla co-presenza di AAAA e AABB. AAAA & AABB=1 perché questa coppia è presente solo nel gruppo 123. Rivalutazione: siccome AAAA ha “si” in C2 si moltiplica per 1,5 quindi (1*1,5=1,5). Poi si continua e si verifica la co-presenza, sempre di AAAA, con gli altri partecipanti. AAAA & BBBB sono presenti sia nel gruppo 123 che nel gruppo 456. Gruppo 123: AAAA&BBBB=1*1.5=1.5. Gruppo 456: AAAA&BBBB=1*1.5=1.5. Rivalutazioni: per lo stesso motivo di prima. Totale AAAA&BBBB=3.
Vado veloce con gli altri casi per concludere il gruppo 123:
AAAA&BBCC= (1*1.5)+(1*1.5)= 3
AAAA&DDDD= (1*1.5)=1.5
AABB&BBBB = (1*1)=1
AABB&BBCC= (1*1)=1
AABB&DDDD=(1*1)=1
BBBB&BBCC=(1*1)=1
BBBB&DDDD=(1*1)=1
BBCC&DDDD=(1*1)=1
SOMMA dei valori delle co-presenze gruppo 123 = (1.5+3+3+1.5+1+1+1+1+1+1) = 15

INDICE gruppo 123 = 15/5=3


Grazie mille a tutti.
sailingsimo
Newbie
 
Post: 9
Iscritto il: 14/11/14 14:09

Sponsor
 

Re: MACRO excel per creazione indice

Postdi Anthony47 » 14/11/14 15:47

Ciao sailingsimo, benvenuto nel forum.
Leggendo la descrizione del calcolo il neurone di destra e’ andato in tilt…
Nel file pubblicato l’ indice del primo gruppo e’ calcolato con la formula =((1+2+2+1)+(1+1+1)+(1+1)+(1))/5Spieghi perche’ fai quelle somme, considerando il contenuto del file, che per semplificare la lettura della discussione e’ qui rappresentato:
Immagine
free image upload
Spero che la spiegazione semplifichi la lettura della descrizione che hai gia’ dato.

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: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: MACRO excel per creazione indice

Postdi sailingsimo » 15/11/14 01:40

Ciao Anthony47,
grazie per la risposta. Quella formula che hai riportato te non è corretta, mi era rimasta nel file. Quella corretta è quella del testo e te la riporto qui:
(1.5+3+3+1.5+1+1+1+1+1+1)/5 = 3

provo a spiegarla nei dettagli:

il primo 1,5 è dato da (1*1,5). 1 è il numero volte che, nella matrice A2:A14, la coppia AAAA & AABB appartiene allo stesso gruppo (gr.123). Invece 1,5 è il coefficiente per il quale si moltiplica il valore precedente a condizione che almeno uno dei due partecipanti abbia nella rispettiva colonna C il valore "si". Se entrambi hanno il valore "no" il coefficiente è 1.

E così via considerando tutte le possibili coppie di partecipanti di ogni gruppo.
Ripetendo l'esempio per la seconda coppia abbiamo il secondo addendo della formula originaria, il numero 3. Da cosa è dato? E' dato da ((1*1,5)+(1*1,5)). Prima parentesi: 1 è il numero delle volte che AAAA & BBBB si trovano a far parte dello stesso gruppo (gr. 123) e 1,5 è il solito coefficiente dato dal fatto che almeno uno dei due ha in col C valore "si". Seconda parentesi: stessa cosa per il gruppo 456.

Andando avanti, gli addendi della formula originaria dovrebbero rappresentare i valori di tutte le possibili coppie (senza considerare i doppioni) del gruppo 123. Il tutto alla fine va diviso per il numero dei partecipanti del gruppo, in questo caso 5.

Se qualcuno fosse interessato alla logica di questo calcolo: l'indice che si ricava sta ad indicare la frequenza media di relazioni del gruppo, ovvero una media di relazioni che i partecipanti a quel gruppo hanno avuto tra loro. Un indice alto starà a significare conoscenza reciproca, affiatamento e integrazione, uno basso invece il contrario.

Vi ringrazio ancora per l'attenzione e per l'aiuto che potete darmi.

Simone
sailingsimo
Newbie
 
Post: 9
Iscritto il: 14/11/14 14:09

Re: MACRO excel per creazione indice

Postdi Anthony47 » 15/11/14 02:21

Capito; in effetti la formula faceva a pugni con quanto scritto…
Per capire come meglio organizzare i dati di lavoro avrei bisogno di sapere orientativamente quanti sono i gruppi di lavoro, quanti sono i Partecipanti complessivi, se c’e’ un limite di partecipanti a ogni gruppo. Basta una approssimazione modesta.

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: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: MACRO excel per creazione indice

Postdi sailingsimo » 15/11/14 10:09

Grazie Anthony47,
gruppi di lavoro circa 38 mila
partecipanti circa 300 mila
i gruppi non hanno limiti di partecipanti, ma considera che si va da un minimo di tre ad un massimo si una sessantina, anche se la media sarà intorno a 10 / 15.

Se ti facesse comodo saperlo nel file originale ho una colonna con il numero di partecipanti del gruppo (che nell'esempio non ho riportato, pensavo non servisse). Fai conto una colonna che, ad esempio, per ogni riga del gruppo 123 ha il valore 5 ... e così via per gli altri gruppi.

Grazie ancora.
Simone
sailingsimo
Newbie
 
Post: 9
Iscritto il: 14/11/14 14:09

Re: MACRO excel per creazione indice

Postdi Anthony47 » 15/11/14 20:15

Hummm... non so se quella massa di dati sara' processabile tramie un pc medio (sara' un cubo con dei lati abbastanza larghi...).
Ho un' idea su cui lavorare, dammi tempo.

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: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: MACRO excel per creazione indice

Postdi sailingsimo » 16/11/14 00:54

No problem, prendi il tempo che ti serve.
So che la massa di dati è difficilmente processabile, infatti sto avendo problemi di lentezza con il file. Anche semplici somme, impiegano ore a riempire una colonna. Sto pensando di ridurre il campione, sto facendo un lavoro statistico e lo posso fare se mantengo la rappresentatività.

Grazie del tuo aiuto.
simone
sailingsimo
Newbie
 
Post: 9
Iscritto il: 14/11/14 14:09

Re: MACRO excel per creazione indice

Postdi Anthony47 » 18/11/14 01:51

Sulla base dell’ esempio e di quello che ho capito della descrizione ho sviluppato questa macro:
Codice: Seleziona tutto
Sub popeye()
Dim VArr, I As Long, LastA As Long, J As Long, K As Long, GrCnt As Long, myPartn As String, myMain As String
Dim myArr(), myStart As String, LB1 As Long, LB2 As Long, aCoord As Boolean, bCoord As Boolean
Dim GrScore As Single, myScore As Single, C1 As Long, C2 As Long, C3 As Long, UB1 As Long
Dim L As Long, myMatch, cGroup As String, oGroup As String, c2Group As String, PRiga As Long, Gr0Cnt As Long
'
Sheets("Foglio1").Select                   '<< Il foglio con gli elenchi
myStart = "A1"
LastA = Cells(Rows.Count, 1).End(xlUp).Row
VArr = Range(myStart).Resize(LastA, 3).Value
LB1 = LBound(VArr, 1)
LB2 = LBound(VArr, 2)
UB1 = UBound(VArr, 1)
C1 = LB2
C2 = LB2 + 1
C3 = LB2 + 2
Application.Calculation = xlCalculationManual
For I = LB1 + 1 To UB1      'si parte da riga 2
    If UCase(VArr(I, C3)) = "SI" Then aCoord = True Else aCoord = False
    cGroup = VArr(I, 1)
    GrCnt = Application.WorksheetFunction.CountIf(Range(Cells(I, 1), Cells(LastA, 1)), cGroup)
   
    If cGroup <> oGroup Then
        If PRiga > 1 And Gr0Cnt > 0 Then Cells(PRiga, "F") = GrScore / Gr0Cnt
        oGroup = cGroup
        GrScore = 0: ScoreCnt = 0
        PRiga = I - LB1 + 1
        Gr0Cnt = GrCnt
    End If
    myMain = VArr(I, 2)
    If myMain <> "" Then
        For J = 1 To GrCnt - 1
            myPartn = VArr(I + J, C2)
            If myPartn <> "" Then
                If aCoord = True Or UCase(VArr(J, C3)) = "SI" Then GrScore = GrScore + 1.5 Else GrScore = GrScore + 1
    '            For K = J + 1 To GrCnt
                cpos = I + J - LB1: cpos = 1
                    Do
                        myMatch = Application.Match(myMain, Range(Cells(cpos + 1, 2), Cells(LastA, 2)), 0)
                        If Not IsError(myMatch) Then
                        cpos = cpos + myMatch
                        If VArr(cpos, C1) <> cGroup Then
                       
                            L = 0
                            If UCase(VArr(cpos, C3)) = "SI" Then aCoord = True Else aCoord = False
                                c2Group = VArr(cpos, C1)
                                Do
                                    L = L + 1
                                    If (cpos + L) > LastA Then Exit Do
                                    If VArr(cpos + L, 1) <> c2Group Then Exit Do
                                    If VArr(cpos + L, 2) = myPartn Then
                                        If (aCoord = True Or UCase(VArr(cpos + L, C3)) = "SI") Then GrScore = GrScore + 1.5 Else GrScore = GrScore + 1
                                    End If
                                Loop
                            End If
                        Else
                            Exit Do
                        End If
                    Loop
    '            Next K
            End If
        Next J
    Else
        Gr0Cnt = Gr0Cnt - 1
    End If
Next I
If (PRiga) > 1 And Gr0Cnt > 0 Then Cells(PRiga, "F") = GrScore / Gr0Cnt
Application.Calculation = xlCalculationAutomatic
End Sub

Essa mette in colonna F l’ indice calcolato; se F non va bene modifica le due righe che contengono Then Cells(PRiga, "F") = GrScore / Gr0Cnt

Usa un struttura dati abbastanza elementare, quindi non porra’ (su elenchi lunghi) problemi di memoria ma di tempi di esecuzione.
Inizialmente avevo pensato a una struttura che potesse contenere tutte le coppie di Partecipanti e tutti i gruppi, che avrebbe accorciato i tempi di elaborazione, ma una matrice da 300K * 300K * 30K elementi sarebbe stato decisamente ingestibile per qualsiasi pc.
La macro lascia il CalculationMode impostato in Automatico, se non va bene modifica la penultima riga della macro.

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: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: MACRO excel per creazione indice

Postdi sailingsimo » 18/11/14 08:50

Grazie, la provo e ti faccio sapere.
simone
sailingsimo
Newbie
 
Post: 9
Iscritto il: 14/11/14 14:09

Re: MACRO excel per creazione indice

Postdi Anthony47 » 18/11/14 14:21

C' era un errore, nascosto dal fatto che, nel filetto che avevi pubblicato, l' ultima riga era vuota.
Ho modificato (righe marcate ****) e ne ho approfittato per inserire un messaggio di "Completato"
Codice: Seleziona tutto
Sub popeye2()
Dim VArr, I As Long, LastA As Long, J As Long, K As Long, GrCnt As Long, myPartn As String, myMain As String
Dim myArr(), myStart As String, LB1 As Long, LB2 As Long, aCoord As Boolean, bCoord As Boolean
Dim GrScore As Single, myScore As Single, C1 As Long, C2 As Long, C3 As Long, UB1 As Long
Dim L As Long, myMatch, cGroup As String, oGroup As String, c2Group As String, PRiga As Long, Gr0Cnt As Long
'
myStart = "A1"
mytim = Timer
LastA = Cells(Rows.Count, 1).End(xlUp).Row
VArr = Range(myStart).Resize(LastA, 3).Value
LB1 = LBound(VArr, 1)
LB2 = LBound(VArr, 2)
UB1 = UBound(VArr, 1)
C1 = LB2
C2 = LB2 + 1
C3 = LB2 + 2
Application.Calculation = xlCalculationManual
For I = LB1 + 1 To UB1 - 1    'si parte da riga 2        '*****
    If UCase(VArr(I, C3)) = "SI" Then aCoord = True Else aCoord = False
    cGroup = VArr(I, 1)
    GrCnt = Application.WorksheetFunction.CountIf(Range(Cells(I, 1), Cells(LastA, 1)), cGroup)
   
    If cGroup <> oGroup Then
        If PRiga > 1 And Gr0Cnt > 0 Then Cells(PRiga, "F") = GrScore / Gr0Cnt
        oGroup = cGroup
        GrScore = 0: ScoreCnt = 0
        PRiga = I - LB1 + 1
        Gr0Cnt = GrCnt
    End If
    myMain = VArr(I, 2)
    If myMain <> "" Then
        For J = 1 To GrCnt - 1
            myPartn = VArr(I + J, C2)
            If myPartn <> "" Then
                If aCoord = True Or UCase(VArr(J, C3)) = "SI" Then GrScore = GrScore + 1.5 Else GrScore = GrScore + 1
    '            For K = J + 1 To GrCnt
                cpos = I + J - LB1: cpos = 1
                    Do
                        myMatch = Application.Match(myMain, Range(Cells(cpos + 1, 2), Cells(LastA, 2)), 0)
                        If Not IsError(myMatch) Then
                        cpos = cpos + myMatch: If cpos >= LastA Then Exit Do    '*****
                        If VArr(cpos, C1) <> cGroup Then
                       
                            L = 0
                            If UCase(VArr(cpos, C3)) = "SI" Then aCoord = True Else aCoord = False
                                c2Group = VArr(cpos, C1)
                                Do
                                    L = L + 1
                                    If (cpos + L) > LastA Then Exit Do
                                    If VArr(cpos + L, 1) <> c2Group Then Exit Do
                                    If VArr(cpos + L, 2) = myPartn Then
                                        If (aCoord = True Or UCase(VArr(cpos + L, C3)) = "SI") Then GrScore = GrScore + 1.5 Else GrScore = GrScore + 1
                                    End If
                                Loop
                            End If
                        Else
                            Exit Do
                        End If
                    Loop
    '            Next K
            End If
        Next J
    Else
        Gr0Cnt = Gr0Cnt - 1
    End If
Next I
If (PRiga) > 1 And Gr0Cnt > 0 Then Cells(PRiga, "F") = GrScore / Gr0Cnt
Application.Calculation = xlCalculationAutomatic
MsgBox ("Completato " & vbCrLf & (LastA - 1) & " righe - " & _
    Format(Timer - mytim, "0.00") & " secondi")
End Sub

Mi raccomando, provalo con file di lunghezza modesta (tipo 1000 record) cosi' ti fai un'idea di quanto devi aspettare con file da 300k record.

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: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: MACRO excel per creazione indice

Postdi sailingsimo » 19/11/14 13:18

Ecco... infatti!!
ti stavo riscrivendo che avevo provato e funzionava solo per pochi record, ma con il file più grande mi si impallava.
Inoltre c'era qualcosa che non mi tornava.

Ma resetto il tutto e riprovo.
Grazie ancora. Riprovo e ti faccio sapere.
s.
sailingsimo
Newbie
 
Post: 9
Iscritto il: 14/11/14 14:09

Re: MACRO excel per creazione indice

Postdi sailingsimo » 19/11/14 14:25

Eccomi ancora, ho testato la seconda macro. Per un file di 1000 record funziona perfettamente (0.75 sec. di esecuzione), invece per un file di 10 mila record mi si blocca tutto. O meglio, ho apettato più di 20 minuti e non si sbloccava niente. Pensi che sia prevedibile una durata del genere?

Sul file che ha funzionato: il valore del calcolo appare solo sulla cella relativa al primo partecipante di ogni gruppo, mentre tutte le celle della colonna F degli altri partecipanti risultano vuote. E' possibile far risultare tali celle con lo stesso valore? In pratica tutti i partecipanti dello stesso gruppo, nella colonna F riportano lo stesso valore.

Grazie.
simone
sailingsimo
Newbie
 
Post: 9
Iscritto il: 14/11/14 14:09

Re: MACRO excel per creazione indice

Postdi Anthony47 » 20/11/14 03:16

Non so che cosa hai concluso la prova… Non avendo un file reale non sono in grado di fare prove, pero’ col doppio delle righe non solo ogni calcolo e’ da fare su piu’ righe, ma ci sono piu’ calcoli da fare.
Immagino quindi che l’ aumento dei tempi sia quadratico.
Per avere un’ idea dovresti provare con 1000, poi 2000, poi 5000, poi 10000 records e confrontare i tempi.

Ho messo il voto solo sulla prima linea perche’ parliamo di un voto di gruppo, non di partecipante; e’ errato?

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: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: MACRO excel per creazione indice

Postdi sailingsimo » 20/11/14 07:50

ok riprovo, lascio lavorare a lungo e riconsidero i tempi.
Il voto sulla prima linea è giustissimo. Mi servirebbe che venisse replicato anche sulle altre linee. lo stesso valore per ogni partecipante dello stesso gruppo.
sailingsimo
Newbie
 
Post: 9
Iscritto il: 14/11/14 14:09

Re: MACRO excel per creazione indice

Postdi sailingsimo » 07/12/14 09:45

Ciao, scusate la pausa, ho avuto uno stop nel lavoro.
riprendo dicendo che ho lasciato lavorare il pc anche per due giorni, senza riuscire però ad arrivare a niente. Se aumento i dati non riesce a calcolare.

Vabbè, grazie lo stesso per l'aiuto e per la pazienza.

simone
sailingsimo
Newbie
 
Post: 9
Iscritto il: 14/11/14 14:09

Re: MACRO excel per creazione indice

Postdi Anthony47 » 10/12/14 00:36

Me lo aspettavo che servisse un pc un po' piu' tosto di quelli che si trovano in giro...
Se mi dici i tempi che impiega a calcolare 1000 - 2000 - 5000 record mi posso fare un'idea della progressione dei tempi e capire se ha senso cercare qualche via piu' efficiente.

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: 13904
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "MACRO excel per creazione indice":


Chi c’è in linea

Visitano il forum: Nessuno e 10 ospiti