Somma se piu condizioniRapisiddima

Hai un problema con Excel?

Nuova discussione

Somma se... piu condizioni...Rapisiddima
di totygno71 data: 25/03/2013 14:19:00
Allora:

foglio 1
colonna A: n° matricola di dipendenti (Circa 400)
colonna B: Tipologia di lavoro (circa 20 differenti tipologie, ogno dipendente puo svolgerne sino a 6 differenti)
Colonna da C ad N: mesi dell'anno
Colonna O: Totale Annuo
---------------------------------------------------------------------------------
Foglio2

Colonna A: N° Matricola
Colonna B: Tipologia di lavoro
Colonna C: Mese in cui è stata svolta
Colonna D : Ora Inizio
Colonna E: Ora fine
Colonna F: durata Lavoro

---------------------------------------------------------------
Vorrei ottenere la somma dei lavori parziali (nel foglio 2) di ogni singolo operatore presente nel foglio1 per i rispettivi job (foglio1 colonna B) nel mese indicato (foglio2, colonnaC)_
Allego esempio

P.S. Il mio modello ci impiega circa 25-30 secondi per completare tutti i calcoli, ma vorrei se possibile qualcosa di piu "rapido"

Avanti con le idee
di totygno71 data: 25/03/2013 14:45:15
Il metodo che ho usato è stato quello di concatenare le 3 variabili del foglio1 (ID&JOB&MESE) da cercare
nelle rispettive celle del foglio"!
di Vecchio Frac data: 25/03/2013 15:06:53
Ma il risultato voluto quale deve essere?
Nel foglio1, per ogni dipendente (ID), posizionare nel mese esatto la somma dei parziali totalizzati nel foglio2, JOB per JOB?
per cui ad esempio, il dipendente ID 2120 ha totalizzato 4.03 in LUGLIO?
Faccio rilevare che al dipendente 1747 nel foglio1 è associato solo il JOB "ZZZ" mentre nel foglio2, oltre ad essere duplicato, hai dei parziali relativi soltanto a JOB "III" e JOB "LLL" (gennaio e luglio rispettivamente).
Inoltre non trovo nessuna formula di concatenazione nel modello allegato (e comunque sì, concatenare è un'operazione molto lenta di per sé).
di totygno71 data: 25/03/2013 15:14:33
"CIT" Nel foglio1, per ogni dipendente (ID), posizionare nel mese esatto la somma dei parziali totalizzati nel foglio2, JOB per JOB?
Risposta: Si

"Cit" Faccio rilevare che al dipendente 1747 nel foglio1 è associato solo il JOB "ZZZ" mentre nel foglio2, oltre ad essere duplicato, hai dei parziali relativi soltanto a JOB "III" e JOB "LLL" (gennaio e luglio rispettivamente).
Risposta: Caro FV non la trovi perche è un esempio fatto a mano ma in pratica dovrebbe esserci sempre corrispondenza tra i dati foglio1 e quelli foglio2

"Cit"Inoltre non trovo nessuna formula di concatenazione nel modello allegato (e comunque sì, concatenare è un'operazione molto lenta di per sé).
Risposta: le formule le ho eliminate tutte in questo esempio.. Ma tieni conto che lavoravo col calcolo manuale attivato altrimenti era impossibile inserire i dati visto che ad ogni inserimento l'attesa per il calcolo era di circa 20 secondi.
di Vecchio Frac data: 25/03/2013 15:24:07
cit. " non la trovi perche è un esempio fatto a mano ma in pratica dovrebbe esserci sempre corrispondenza tra i dati foglio1 e quelli foglio2 "
---> Io mi sono attenuto all'esempio postato :)

Allora il lavoro è:
- scorro il range di ID del foglio1
- per ogni ID (che è univoco) mi spazzolo il range del foglio2, quando trovo una corrispondenza recupero i dati del JOB
- torno a spazzolare le righe successive all'ID trovato, colonna JOB e appena trovo la corrispondenza (hai detto che dovrebbe esserci!) inserisco nel mese corrispondente, in foglio1, l'orario parziale recuperato dal foglio2

Confermi?
Un po' laborioso ma non particolarmente complicato ;)
di totygno71 data: 25/03/2013 15:27:06
aggiudicato!!!
Tieni conto che nel foglio1 attualmente nelle celle ID e JOB c'è del testo ma nel file reale sono formule che pescano da un terzo folgio ("Pannello di controllo") e credo che questo rallenti ancora di piu il tutto_
di totygno71 data: 25/03/2013 15:29:23
ID univoco nel foglio1 ma nel folgio2 può essere presente piu volte lo stesso ID perchè magari ha svolto piu lavori nello stesso mese o nell'arco dell'anno_

di Vecchio Frac data: 25/03/2013 16:45:22
Proviamo a vedere se intanto questo codice può lavorare come vuoi tu.
Lo incollo qui per memoria ma allego anche il file.
Commentarlo... è un'altra storia :) (ma non è difficile, solo lungo)

Option Explicit

Sub riporta_orari_parziali_per_mese()
Dim ur As Integer, rng_ID_1 As Range, rng_ID_2 As Range
Dim cell_id_1 As Range, cell_id_2 As Range
Dim cell_job_1 As Range, cell_job_2 As Range
Dim first_address As String
Dim job As Range
Dim month_job As Integer
Dim bFound As Boolean

    'Foglio1.[C3:N2000].clearcontents : Foglio1.[P:P].Delete
    
    Foglio1.Activate
    
    With Foglio1
        ur = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rng_ID_1 = .Range("A3:A" & ur)
    End With
    
    With Foglio2
        ur = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rng_ID_2 = .Range("A2:A" & ur)
    End With
    
    For Each cell_id_1 In rng_ID_1
    
        If cell_id_1 <> "" Then
        
            Set cell_id_2 = rng_ID_2.Find(what:=cell_id_1, lookat:=xlWhole)
            
            If Not (cell_id_2 Is Nothing) Then
                first_address = cell_id_2.Address
                
                Do
                    
                    Set cell_job_2 = cell_id_2.Offset(, 1)
                    bFound = False

                    For Each cell_job_1 In Range(cell_id_1.Offset(, 1), cell_id_1.Offset(5, 1))
                        If cell_job_1 = cell_job_2 Then
                            month_job = Month(cell_job_2.Offset(, 1))
                            cell_job_1.Offset(, month_job) = cell_job_1.Offset(, month_job) + cell_job_2.Offset(, 4)
                            bFound = True
                            Exit For
                        End If
                    Next cell_job_1
                    If Not bFound Then
                        cell_id_1.Offset(, 15) = "JOB " & cell_job_2 & " Not found" 'rosso, JOB non trovato
                        cell_id_1.Offset(, 15).Interior.ColorIndex = 3
                    End If
                    
                    If cell_id_1 = 1747 Then Debug.Print cell_id_2.Address   ''''
                    
                    Set cell_id_2 = rng_ID_2.FindNext(cell_id_2)
                Loop While Not cell_id_2 Is Nothing And cell_id_2.Address <> first_address
                
            Else
            
                cell_id_1.Offset(1, 15) = "ID " & cell_id_1 & " Not found" 'rosso, ID non trovato
                cell_id_1.Offset(1, 15).Interior.ColorIndex = 3

            End If

        
        End If
    
    Next
    
    MsgBox "Finito"

End Sub

di totygno71 data: 25/03/2013 18:20:55
VF... Sei Forte!!!

Domani lo testo sull'originale...(con formule dati corretti etc etc) e ti faro sapere come gira e quanto ci impiega...
Poi magari ti chiederò qualche info sul codice....Tanto per carpire qualche utile segreto!

Intanto GRAZIE_
di Vecchio Frac data: 25/03/2013 19:05:25
cit. " e quanto ci impiega"
---> Sì, questo potrebbe essere interessante.
di isy (utente non iscritto) data: 25/03/2013 19:33:50
Ciao

cit: P.S. Il mio modello ci impiega circa 25-30 secondi per completare tutti i calcoli, ma vorrei se possibile qualcosa di piu "rapido"

Avanti con le idee

Messa così sembra una sfida...
Potresti allegare le formule per testare una eventuale differente soluzione
di Vecchio Frac data: 25/03/2013 20:17:19
@isy
cit. " Messa così sembra una sfida... "
Sicuro, come sicuro che ci sarà una soluzione più performante con le formule.
Ma io è da quando conosco totygno che è una sfida continua ^_^
di totygno71 data: 25/03/2013 20:23:20
Con le formule le ho provate tutte... ma niente di niente oltre un minuto con formule alla matriCIANA...^_^
Con il concatena un po piu veloce....
Domani testo il codice del buon VF e vi diro quanto ci impiega su tutto il database... O.o
di Raffaele_53 (utente non iscritto) data: 25/03/2013 20:27:47
Potrei sapere perchè i codici ID del foglio2 sono formattati come numero?
Il fatto che in Foglio1 ci siano dei -47424 -41302 ecc ecc tutto OK?
di Vecchio Frac data: 25/03/2013 20:33:26
No, non è tutto OK e infatti totygno ha già precisato che si tratta di un foglio di prova, con dati inseriti un po' a caso :)
Inoltre, lui stesso ha detto che "...nel foglio1 attualmente nelle celle ID e JOB c'è del testo ma nel file reale sono formule che pescano da un terzo foglio".
Quindi aspettiamo gli sviluppi ^_^
di totygno71 data: 25/03/2013 20:33:38
Si raffaele
è un errore poichè quello è un foglio creato per testare il codice ma non è il file reale
quello che uso a lavoro contine dati sensibili quindi non posso pubblicarlo
ma domani lo testo e vi dirò----
per VF una piccola macro che calcoli il tempo preciso del calcolo si potrebbe inserire anche se forse influirebbe sul tempo totale di calcolo non credi????
di Vecchio Frac data: 25/03/2013 20:48:54
Bè sì, si può senza creare una macro apposta, qualche volta lo faccio anch'io; molto semplicemente alla buona con un debug.print timer all'inizio e un debug.print timer alla fine, poi fai la sottrazione a mano e ottieni il tempo trascorso (in secondi e decimi).
di Raffaele_53 (utente non iscritto) data: 25/03/2013 21:51:09
Diff = ""
Iniz = Time
....
....
Fine = Time
Diff = Format(Fine - Iniz, "hh:mm:ss")
MsgBox ("Tempo impiegato " & Diff)
Non credo che influenza, se sì di poco.

Intendevo dire che dal numero 9836 = 9836,136202
Alcune funzioni tipo "=MATR.SOMMA.PRODOTTO(...." non funzionano), poi che arrivano da formule mi stà bene.
Non vedo il perchè di una formula che traduce in un numero diverso?
di Raffaele_53 (utente non iscritto) data: 25/03/2013 22:05:56
Certo arrotonda, mà con quale criterio?
23808 23807,59321 'questo in alto
23834 23833,61082 'questo in alto
9836 9836,136202 'questo in basso
di Raffaele_53 (utente non iscritto) data: 25/03/2013 22:12:46
Trovato.....
Come il 730/740
Arrotonda dal 0,0 al 0,500 in basso
Arrotonda dal 0,501 al 1 in alto
di totygno71 data: 26/03/2013 09:14:45
@VF

Ho provato a codificare il tuo codice... ma ci sono dei passaggi che non capisco soprattutto legati ai range_

Ti allego il file con i range reali del modello che utilizzo...
Puoi dargli un'occhiata e modificare il tuo codice?
Ciao
di Vecchio Frac data: 26/03/2013 11:33:37
Volentieri, mi sembrava che funzionasse sul modello di esempio, bisogna porre attenzione ai riferimenti perchè si saltella da un foglio all'altro.
Please be patient :)
di Vecchio Frac data: 26/03/2013 11:51:10
Bè, il tracciato (lo schema) del nuovo file postato è piuttosto diverso da quello di esempio analizzato.
Quindi tutti i riferimenti ai diversi range interessati devono per forza essere riaggiustati (soprattutto i delicati .Offset); vediamo se ci riesco in tempi rapidi.
Comunque ho interrotto i collegamenti a fogli esterni, dovrai quindi ricopiare il codice nel file di lavoro.
di totygno71 data: 26/03/2013 11:54:26
Esatto... nell'esempio ho messo i riferimenti reali questa volta_

Nota che i JOB dei vari dipendenti sono nella colonna C dalla quarta cella alla sesta di ogni dipendente!

di Vecchio Frac data: 26/03/2013 11:57:04
Ho rivisto tutti i riferimenti e probabilmente adesso funziona come ti aspetti (il test fatto è confortante).
verifica e fammi sapere.
Allego sia il testo della macro che il file rivisto.

Option Explicit

Sub riporta_orari_parziali_per_mese()
Dim ur As Integer, rng_ID_1 As Range, rng_ID_2 As Range
Dim cell_id_1 As Range, cell_id_2 As Range
Dim cell_job_1 As Range, cell_job_2 As Range
Dim first_address As String
Dim job As Range
Dim month_job As Integer
Dim bFound As Boolean

    'Foglio1.[C3:N2000].clearcontents : Foglio1.[P:P].Delete
    
    Foglio1.Activate
    
    With Foglio1
        ur = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rng_ID_1 = .Range("A1:A" & ur)
    End With
    
    With Foglio2
        ur = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rng_ID_2 = .Range("A2:A" & ur)
    End With
    
    For Each cell_id_1 In rng_ID_1
    
        If cell_id_1 <> "" And Val(cell_id_1) <> 0 Then
        
            Set cell_id_2 = rng_ID_2.Find(what:=cell_id_1, lookat:=xlWhole)
            
            If Not (cell_id_2 Is Nothing) Then
                first_address = cell_id_2.Address
                
                Do
                    
                    Set cell_job_2 = cell_id_2.Offset(, 4)
                    bFound = False

                    For Each cell_job_1 In Range(cell_id_1.Offset(, 2), cell_id_1.Offset(5, 2))
                        If cell_job_1 = cell_job_2 Then
                            month_job = Month(cell_job_2.Offset(, 4))
                            cell_job_1.Offset(, month_job) = cell_job_1.Offset(, month_job) + cell_job_2.Offset(, 7)
                            bFound = True
                            Exit For
                        End If
                    Next cell_job_1
                    If Not bFound Then
                        cell_id_1.Offset(, 16) = "JOB " & cell_job_2 & " Not found" 'rosso, JOB non trovato
                        cell_id_1.Offset(, 16).Interior.ColorIndex = 3
                    End If
                    
                    Set cell_id_2 = rng_ID_2.FindNext(cell_id_2)
                Loop While Not cell_id_2 Is Nothing And cell_id_2.Address <> first_address
                
            Else
            
                cell_id_1.Offset(1, 16) = "ID " & cell_id_1 & " Not found" 'rosso, ID non trovato
                cell_id_1.Offset(1, 16).Interior.ColorIndex = 3

            End If

        
        End If
    
    Next
    
    MsgBox "Finito"

End Sub

di totygno71 data: 26/03/2013 13:11:53
@VF

Sembra funzionare tutto molto bene e molto velocemente....

mi da errore alla fine dei calcoli a livello di

If cell_id_1 <> "" And Val(cell_id_1) <> 0 Then

probabilmente perchè val(cell_id_1) come valore ad un ceerto punto dell'elenco trova #ND perchè essendo finiti i dipendenti non trova il relativo valore nel pannello di controllo)

Il secondo punto è che per ogni dipendente deve verificare il job che è presente dalla 4 cella alla 6 cella per ogni id mentre la prima "nessuna prestazione" la second "Ore fatte" la terza "ore in più) sono comuni a tutti gli ID e non fanno altro che sommare o sottrare i valori delle celle JOB di ogni Id_

cmq poi con calma mi devi spiegare la logia dei cicli_
di Vecchio Frac data: 26/03/2013 14:01:55
Allora si velocizza un po' l'esecuzione (in particolare, si dimezza visto che cerchiamo su tre celle invece che su sei):

Imposta così il ciclo For interno al Do:
For Each cell_job_1 In Range(cell_id_1.Offset(3, 2), cell_id_1.Offset(5, 2))

Sull'errore del val è certamente come dici.
Non sapendolo non avevo previsto questa condizione.
Sistema così aggiungendo dopo il For il controllo sul valore di errore della cella (chiudi l'If alla fine prima del Next):
For Each cell_id_1 In rng_ID_1
If Not IsError(cell_id_1) Then
If cell_id_1 <> "" And Val(cell_id_1) <> 0 Then
.
.
.
End If
End If
Next
di totygno71 data: 26/03/2013 15:17:46
Fatte tutte le modifiche del caso suggerite... ed altre ritenute utili...

Funziona alla perfezione, tempo di calcolo intorno ai 2 secondi nel file originale

Grande VF

PS (Non montarti la testa però! ^_^)
di Vecchio Frac data: 26/03/2013 15:23:41
2 secondi rispetto a 25-30? non male :)

cit. "Non montarti la testa però! ^_^"
---> eh eh LOL :)

cit. "poi con calma mi devi spiegare la logica dei cicli"
---> la logica è semplice, scorro gli id del foglio 1, ne cerco la corrispondenza nel foglio2, per ogni job trovato nel foglio2 torno nel foglio1 dove devo registrare il tempo parziale nel mese di riferimento (ottenuto rileggendo il dato nel foglio2).
Ogni _1 e _2 nei nomi delle variabili si riferisce al foglio1 o al foglio2, i nomi sono esplicativi, dove c'è id mi riferisco alla colonna con gli id, dove c'è job alle colonne omonime. Il ciclo Do...Loop si è reso necessario perchè gli id nel foglio2 potevano essere ripetuti e andavano sommati tutti i tempi parziali.
Puoi anche togliere i passi di debug inseriti per controllo (quando riporto Not found se manca un job o un id).
di totygno71 data: 26/03/2013 15:34:29
cit": Puoi anche togliere i passi di debug inseriti per controllo (quando riporto Not found se manca un job o un id).

Risp: Scherzi??? E' utilissima quella funzione... Mi serve per trovare eventuali incongruenze!!! ^_^

E' tutto abbastanza chiaro a parte il passaggio di collocare il totale dei vari mesi in corrispondenza del mese giusto: "GEN" "FEB" etc nel foglio 1
di Vecchio Frac data: 26/03/2013 15:42:48
Ricavando il numero del mese dalla data inserita (7 per luglio, 9 per settembre, ecc.) con Month() ottengo anche lo scostamento che devo applicare alla cella per raggiungere il mese desiderato (sette celle più a destra della cella prima di GEN trovo LUG e così via):
visto che cell_job_1 è la cella del foglio1 che contiene il job corrente, mi sposto di 0 righe e di (numero del mese) colonne a destra per raggiungere la colonna del emse di riferimento nel foglio1:
cell_job_1.Offset(, month_job)
di totygno71 data: 26/03/2013 15:58:14
Giusto

semplice... ma non ci avevo pensato!

Vuoi Approfondire?