Macro che somma dati condizionati su più fogli della stessa cartella

[Corrado]

Salve,
ho un file excel composto da più fogli che rappresentano i pazienti nel mio caso. All'interno di ognuno di essi vi sono elencati i nomi degli infermieri che eseguono assistenza a casa degli stessi in forma do ore lavorative. I dati degli infermieri sono inseriti nel formato:

Foglio1 paziente Alfa

Nome mansione ore/mese

Tizio Infermiere 32
Caio Fisio 25

------------------------------------------------

Foglio2 paziente Beta

Nome mansione ore/mese

Tizio Infermiere 12
Caio Fisio 44

------------------------------------------------


Foglio3 Riepilogo

Nome mansione ore/mese

Tizio Infermiere 45
Caio Fisio 69

-------------------------------------------------

Fin quì semplicissimo, ma a me serve che oltre a fare la somma preveda l'inserimento di nuovi fogli (nuovi paz. presi in carico) e automaticamente lanciando la macro ne riepiloghi le somme.

I nomi degli operatori sanitari possono cambiare e non sono sempre gli stessi.

Spero di esser stato quanto più esaustivo possibile.

Grazie

[gram...@alice.it]

E'stato molto chiaro, ma onestamente non conosco una procedura vba che la possa aiutare in merito (sicuramente qualcuno in rete è in grado di scrivergliela).
Certamente ci avrai già pensato lei, ma mettere tutti i pazienti su un foglio solo, creando delle interruzioni di pagina tra un paziente e l'altro così può stampare delle schede separate, è impossibile per lei? Potrebbe così creare su un altro foglio la scheda degli operatori e con una funzione tipo somma.se arriverebbe al risultato facilmente e con la sicurezza di non dimenticare nulla.
Il dato così come vuole gestirlo lei mi sembra troppo complesso per un excel e andrebbe trattato su un database come access.

Spero di esserle stato d'aiuto

Stefano

[Corrado]

Grazie per la risposta, ma sono convinto che realizzando una routine che:

- prelevi da ogni foglio in due variabili, none e ore lavorate
- e ne inserisca la somma in una tabella riepilogativa
naturalmente tenendo conto di eventuali altri fogli che verranno aggiunti

sia fattibile.

Proverò con le mie poche conoscenza di VBA,
grazie di tutto

Il giorno sabato 21 dicembre 2013 13:17:06 UTC+1, Corrado ha scritto:

[Bruno Campanini]

Corrado formulated on Saturday :
> Salve,
> ho un file excel composto da piￜ fogli che rappresentano i pazienti nel mio

> caso. All'interno di ognuno di essi vi sono elencati i nomi degli infermieri
> che eseguono assistenza a casa degli stessi in forma do ore lavorative. I
> dati degli infermieri sono inseriti nel formato:
>
> Foglio1 paziente Alfa
>
> Nome mansione ore/mese
>
> Tizio Infermiere 32
> Caio Fisio 25
>
> ------------------------------------------------
>
> Foglio2 paziente Beta
>
> Nome mansione ore/mese
>
> Tizio Infermiere 12
> Caio Fisio 44
>
> ------------------------------------------------
>
>
> Foglio3 Riepilogo
>
> Nome mansione ore/mese
>
> Tizio Infermiere 45
> Caio Fisio 69
>
> -------------------------------------------------
>

> Fin quￜ semplicissimo, ma a me serve che oltre a fare la somma preveda

> l'inserimento di nuovi fogli (nuovi paz. presi in carico) e automaticamente
> lanciando la macro ne riepiloghi le somme.
>
> I nomi degli operatori sanitari possono cambiare e non sono sempre gli
> stessi.
>

> Spero di esser stato quanto piￜ esaustivo possibile.
>
> Grazie

Visto che nelle rilevazioni e nel riepilogo scrivi ore/mese, nel mese
successivo che cosa succede? un nuovo file con riepilogo progressivo
per mese?

Secondo me ￜ un lavoro che andrebbe fatto con un database relazionale,
tre tabelle: Operatori Sanitari e Pazienti per la anagrafiche,
Prestazioni per le assistenze coi campi relativi agli Operatori, ai
Pazienti, alle date delle prestazioni, agli orari (da ora a ora), alla
tipologia prestazioni.
Report di riepilogo per Operatori, Pazienti, Prestazioni riferito al
tempo (da data a data).
Ma si puￜ fare anche in Excel, rinunciando a qualcosa e impasticciando
un po' le procedure (per me ￜ giￜ un pasticcio che a paziente nuovo si
debba aprire un nuovo foglio).
Ma sono opinioni personali.

Bruno


Quest'ultima


Bruno

[Corrado]

E' vero potrei creare un database relazionale, più semplice, ma non voglio perderci tempo. A me serve una routine che prelevi il valore delle ore per ogni singolo operatore nei fogli ove è presente e lo sommi in una tabella riepilogativa. I fogli alla fine non saranno tanti visto che i pazienti non sono tanti.

Grazie

[r]

Il giorno sabato 21 dicembre 2013 13:17:06 UTC+1, Corrado ha scritto:

Non credo proprio una procedura VBA sia necessaria ne tanto meno conveniente visto che si può facilmente risolvere con una formula.

Innanzitutto per comodità preparati un elenco dei nomi dei fogli e assegna a questo intervallo il nome "fogli".

fatto questo nel mio esempio i nomi partono da A4 e le ore da C4.

Nel foglio riepilogo avrai la lista dei Nomi diciamo da A2:
Tizio
Semprogno
Giovanni

La formula da incollare in B2 confermare con Ctrl+maiusc+invio e trascinare in giù è:
=SOMMA(SOMMA.SE(INDIRETTO(fogli&"!A4:A20");A2;INDIRETTO(fogli&"!C4:C20")))

in inglese (per l'orso)
=SUM(SUMIF(INDIRECT(fogli&"!A4:A10"),A2,INDIRECT(fogli&"!C4:C10")))

saluti
r

[Bruno Campanini]

gram...@alice.it was thinking very hard :

> E'stato molto chiaro, ma onestamente non conosco una procedura vba che la

> possa aiutare in merito (sicuramente qualcuno in rete ￜ in grado di
> scrivergliela). Certamente ci avrai giￜ pensato lei, ma mettere tutti i

> pazienti su un foglio solo, creando delle interruzioni di pagina tra un

> paziente e l'altro cosￜ puￜ stampare delle schede separate, ￜ impossibile per
> lei? Potrebbe cosￜ creare su un altro foglio la scheda degli operatori e con

> una funzione tipo somma.se arriverebbe al risultato facilmente e con la

> sicurezza di non dimenticare nulla. Il dato cosￜ come vuole gestirlo lei mi

> sembra troppo complesso per un excel e andrebbe trattato su un database come
> access.

Ma che bello constatare che alcuno ancora sa usare appropriatamente il
pronome di cortesia!
Sￜ perchￜ quasi ovunque, giovane o vecchio che sia, nessuno ormai lo
usa piￜ.
Perￜ, lasciamelo dire, il suo uso qui ￜ del tutto inappropriato e avrￜ
giￜ fatto sorridere qualcuno.

Penso tu sia nuovo di questo NG perciￜ... benvenuto in queste lande.

Buon Natale
Bruno

[r]

Il giorno sabato 21 dicembre 2013 23:25:35 UTC+1, r ha scritto:

> La formula da incollare in B2 confermare con Ctrl+maiusc+invio e trascinare in giù è:
>
> =SOMMA(SOMMA.SE(INDIRETTO(fogli&"!A4:A20");A2;INDIRETTO(fogli&"!C4:C20")))
>
>
>
> in inglese (per l'orso)
>
> =SUM(SUMIF(INDIRECT(fogli&"!A4:A10"),A2,INDIRECT(fogli&"!C4:C10")))

Visto che ho perso un quarto d'ora per preparare il file d'esempio tanto vale condividerlo:
http://goo.gl/07KXEU

La prossima volta sarebbe auspicabile che un file venisse condiviso da chi pone il quesito. Primo per far risparmiare del tempo a chi lo aiuta. Secondo perchè potrà più facilmente adattare la soluzione al suo specifico scenario.

r

[Corrado]

Buona sera, intanto ringrazio tutti per la pazienza e la cortesia mostratami. Proverò questa formula, ma continuo a pensare di utilizzare una routine vba perché risulterebbe essere più dinamica e non limitante come una formula. Cosa voglio dire con ciò: Se uso una formula sarò costretto a indicare riferimenti precisi, ossia gli elenchi nominativi presenti su ogni foglio non sono necesariamente gli stessi, possono variare a seconda degli operatori presenti nel mese su quel paziente. I fogli non meno importante, possono aumentare o diminuire a seconda, quindi andarli ad elencare con una formula sarebbe altrettanto limitativo. Una routine si limiterebbe ad elencare una serie di nominativi presenti su ogni foglio facendone una semplice somma, come del resto farebbe una Pivot.

Grazie comunque per tutto il tempo dedicatomi.
Buona serata.

[r]

Quante fesserie tutte insieme. La più grossa però è quella di parlare senza aver prima provato.
Come già detto nel mio precedente intervento sarebbe bene condividere un file di esempio. Se la struttura dei dati è così confusa come sostieni si evitava inutili perdite di tempo ... almeno a noi.
Saluti
r

[gram...@alice.it]

Ciao r,
innanzitutto grazie per l'esempio che hai fatto. Non conoscevo questa soluzione delle formule matrici (c'è sempre da imparare!), l'ho approfondita ieri con il tuo file e mi è piaciuta molto.
Secondo me Corrado si sta ostinando a voler fare con Excel qualcosa di sicuramente fattibile, ma complesso e rischioso sulla sicurezza del dato finale. Rischio che potrebbe evitare velocemente e senza tanti sforzi con un db come access. Excel è uno strumento potente, ma personalmente credo che si debbano utilizzare i prodotti adatti per ogni tipo di lavoro.
Saluti

Stefano

[r]

Il giorno lunedì 23 dicembre 2013 09:47:13 UTC+1, gram...@alice.it ha scritto:
> Ciao r, innanzitutto grazie per l'esempio che hai fatto. Non conoscevo questa soluzione delle formule matrici (c'è sempre da imparare!), l'ho approfondita ieri con il tuo file e mi è piaciuta molto. Secondo me Corrado si sta ostinando a voler fare con Excel qualcosa di sicuramente fattibile, ma complesso e rischioso sulla sicurezza del dato finale. Rischio che potrebbe evitare velocemente e senza tanti sforzi con un db come access. Excel è uno strumento potente, ma personalmente credo che si debbano utilizzare i prodotti adatti per ogni tipo di lavoro. Saluti Stefano

ma, guarda ho ricevuto il file privatamente da Corrado e non è assolutamente complicato come dice. I fogli sono strutturati. La soluzione con formule l'ho adattata in un minuto.
Per rispondere a Bruno (visto che Corrado non l'ha fatto) la tabella per ogni foglio è del tipo:
nome | ore Gennaio | ore Febbraio | ore Marzo | ... | ore Dicembre

Stessa cosa quindi nella tabella riepilogativa.
La formula che ho proposto:
=SOMMA(SOMMA.SE(INDIRETTO(fogli&"!"&INDIRIZZO(RIF.RIGA(paz.1!$D$17);RIF.COLONNA(paz.1!$A$17))&":"&INDIRIZZO(RIF.RIGA(paz.1!$D$17)+100;RIF.COLONNA(paz.1!$A$17)));Riepilogo!$A4;INDIRETTO(fogli&"!"&INDIRIZZO(RIF.RIGA(paz.1!$D$17);RIF.COLONNA(paz.1!D17))&":"&INDIRIZZO(RIF.RIGA(paz.1!$D$17)+100;RIF.COLONNA(paz.1!D17)))))
Usa la struttura del foglio paz.1 supponendo che tutti i fogli hanno questa medesima struttura (come è di fatto). "fogli" è un nome dinamico così definito:
=SCARTO(Riepilogo!$W$2;;;CONTA.VALORI(SCARTO(Riepilogo!$W$2;;;100)))
che si riferisce ad un range dove verranno accodati i nuovi nomi dei fogli e che ne restituisce l'elenco. Naturalmente la formula è probabilmente migliorabile.
Se Corrado è daccordo condivido il file, aspetto solo un suo cenno d'approvazione.
Saluti
r

[Corrado]

Salve,
ecco il file originale.
https://drive.google.com/file/d/0B5g1WfFM2_ECeF9EZkVVdlhPOEE/edit?usp=sharing

Il giorno sabato 21 dicembre 2013 13:17:06 UTC+1, Corrado ha scritto:

Il giorno sabato 21 dicembre 2013 13:17:06 UTC+1, Corrado ha scritto:

[r]

Il giorno lunedì 23 dicembre 2013 11:33:21 UTC+1, Corrado ha scritto:
> Salve, ecco il file originale. https://drive.google.com/file/d/0B5g1WfFM2_ECeF9EZkVVdlhPOEE/edit?usp=sharing

e qui la soluzione con formule:
http://goo.gl/uEejG4

saluti
r

[Corrado]

Certamente puoi pubblicare il file, nulla di privato.
Sto testando la tua soluzione, dl resto bisogna essere sempre aperti a nuove soluzioni.
Voglio farti però una domanda:

I fogli che si aggiungeranno dovranno essere elencati nella colonna "fogli" manualmente?

[r]

Il giorno lunedì 23 dicembre 2013 11:41:39 UTC+1, Corrado ha scritto:
> Certamente puoi pubblicare il file, nulla di privato. Sto testando la tua soluzione, dl resto bisogna essere sempre aperti a nuove soluzioni. Voglio farti però una domanda: I fogli che si aggiungeranno dovranno essere elencati nella colonna "fogli" manualmente?

Si. Purtroppo questa è una funzione che manca in Excel. Per ottenere un aggiornamento automatico bisognerebbe usare una udf.

saluti
r

[Bruno Campanini]

Corrado presented the following explanation :

> Salve,
> ecco il file originale.
> https://drive.google.com/file/d/0B5g1WfFM2_ECeF9EZkVVdlhPOEE/edit?usp=sharing

Soluzione in VBA:
============================================
Public Sub Totali()
Dim SH() As String, OS As New Collection
Dim SourceRange As String, TargetRange As Range
Dim k As Long, p As Long, i, j, h, SR As Range
Dim SheetPrefix As String

'---- Definizioni ------------
SourceRange = "A17"
Set TargetRange = [Riepilogo!A4]
SheetPrefix = "paz."
'---------------------------------

If TargetRange <> "" Then
Range(TargetRange(1, 1), TargetRange.End(xlDown)). _
Resize(, 15).ClearContents
End If
For Each i In Sheets
If Left(i.Name, Len(SheetPrefix)) = SheetPrefix Then
k = k + 1
ReDim Preserve SH(1 To k)
SH(k) = i.Name
End If
Next
k = 0
For Each i In SH
Set SR = Sheets(i).Range(SourceRange)
Set SR = Range(SR, SR.End(xlDown))
For Each j In SR
On Error GoTo Totali_Err
OS.Add j, CStr(j)
k = k + 1
TargetRange(k, 1) = j
TargetRange(k, 3) = j(1, 3)
Continua:
Next
Next
Set TargetRange = Range(TargetRange, TargetRange(k))
For Each i In SH
Set SR = Sheets(i).Range(SourceRange)
Set SR = Range(SR, SR.End(xlDown))
For Each j In SR
For Each h In TargetRange
If j = h Then
For p = 4 To 15
h(1, p) = h(1, p) + j(1, p)
Next
End If
Next: Next: Next
Exit_Sub:
Exit Sub
Totali_Err:
On Error GoTo 0
Resume Continua
End Sub
======================================================

Bruno

PS
Nuovi Operatori Sanitari e nuovi Pazienti (fogli) vanno
inseriti a mano; poi la procedura li individua automaticamente.

[r]

Leggendo la procedura di Bruno mi è venuto in mente però che se tu hai il buon senso di continuare a chiamare i fogli con un suffisso seguito da un numero progressivo, una soluzione senza vba è possibile e anche abbastanza semplice. Basta testare il valore di indiretto sull'ipotetico nome di foglio. Quindi dove adesso c'è il nome del primo foglio quindi in w2 del foglio riepilogo metti:
="PAZ."&RIF.RIGA(A1)
e trascini fino a w101
in x2 metti:
=1-VAL.ERRORE(INDIRETTO(INDIRIZZO(1;1;;;W2)))

e trascini fino a x101

modifichi il nome fogli con questa formula:
=SCARTO(Riepilogo!$W$2;;;SOMMA(Riepilogo!$X$2:$X$100))
e il gioco è fatto.

saluti
r

[r]

Il giorno martedì 24 dicembre 2013 00:06:06 UTC+1, r ha scritto:
> Il giorno lunedì 23 dicembre 2013 11:54:08 UTC+1, r ha scritto: > Il giorno lunedì 23 dicembre 2013 11:41:39 UTC+1, Corrado ha scritto: > > > Certamente puoi pubblicare il file, nulla di privato. Sto testando la tua soluzione, dl resto bisogna essere sempre aperti a nuove soluzioni. Voglio farti però una domanda: I fogli che si aggiungeranno dovranno essere elencati nella colonna "fogli" manualmente? > > > > > > Si. Purtroppo questa è una funzione che manca in Excel. Per ottenere un aggiornamento automatico bisognerebbe usare una udf. > > > > saluti > > r Leggendo la procedura di Bruno mi è venuto in mente però che se tu hai il buon senso di continuare a chiamare i fogli con un suffisso seguito da un numero progressivo, una soluzione senza vba è possibile e anche abbastanza semplice. Basta testare il valore di indiretto sull'ipotetico nome di foglio. Quindi dove adesso c'è il nome del primo foglio quindi in w2 del foglio riepilogo metti: ="PAZ."&RIF.RIGA(A1) e trascini fino a w101 in x2 metti: =1-VAL.ERRORE(INDIRETTO(INDIRIZZO(1;1;;;W2))) e trascini fino a x101 modifichi il nome fogli con questa formula: =SCARTO(Riepilogo!$W$2;;;SOMMA(Riepilogo!$X$2:$X$100)) e il gioco è fatto. saluti r

Meglio!
Se i nomi sono costituiti da un suffisso seguito da un numero allora puoi fare così diciamo che "suffisso" è un nome definito nel tuo caso come "paz."
definisci direttamente il nome "fogli" in questo modo:
=suffisso&PICCOLO(SE(ERRORE.TIPO(INDIRETTO(suffisso&RIF.RIGA($1:$100)&"!A1"))=3;RIF.RIGA($1:$100));RIF.RIGA(SCARTO($A$1;;;SOMMA(--(ERRORE.TIPO(INDIRETTO(suffisso&RIF.RIGA($1:$100)&"!A1"))=3)))))

e cancella pure le colonne w e x che non servono più
:-)
saluti
r

[r]

Il giorno martedì 24 dicembre 2013 11:23:55 UTC+1, r ha scritto:
> Il giorno martedì 24 dicembre 2013 00:06:06 UTC+1, r ha scritto: > Il giorno lunedì 23 dicembre 2013 11:54:08 UTC+1, r ha scritto: > Il giorno lunedì 23 dicembre 2013 11:41:39 UTC+1, Corrado ha scritto: > > > Certamente puoi pubblicare il file, nulla di privato. Sto testando la tua soluzione, dl resto bisogna essere sempre aperti a nuove soluzioni. Voglio farti però una domanda: I fogli che si aggiungeranno dovranno essere elencati nella colonna "fogli" manualmente? > > > > > > Si. Purtroppo questa è una funzione che manca in Excel. Per ottenere un aggiornamento automatico bisognerebbe usare una udf. > > > > saluti > > r Leggendo la procedura di Bruno mi è venuto in mente però che se tu hai il buon senso di continuare a chiamare i fogli con un suffisso seguito da un numero progressivo, una soluzione senza vba è possibile e anche abbastanza semplice. Basta testare il valore di indiretto sull'ipotetico nome di foglio. Quindi dove adesso c'è il nome del primo foglio quindi in w2 del foglio riepilogo metti: ="PAZ."&RIF.RIGA(A1) e trascini fino a w101 in x2 metti: =1-VAL.ERRORE(INDIRETTO(INDIRIZZO(1;1;;;W2))) e trascini fino a x101 modifichi il nome fogli con questa formula: =SCARTO(Riepilogo!$W$2;;;SOMMA(Riepilogo!$X$2:$X$100)) e il gioco è fatto. saluti r Meglio! Se i nomi sono costituiti da un suffisso seguito da un numero allora puoi fare così diciamo che "suffisso" è un nome definito nel tuo caso come "paz." definisci direttamente il nome "fogli" in questo modo: =suffisso&PICCOLO(SE(ERRORE.TIPO(INDIRETTO(suffisso&RIF.RIGA($1:$100)&"!A1"))=3;RIF.RIGA($1:$100));RIF.RIGA(SCARTO($A$1;;;SOMMA(--(ERRORE.TIPO(INDIRETTO(suffisso&RIF.RIGA($1:$100)&"!A1"))=3))))) e cancella pure le colonne w e x che non servono più :-) saluti r

Dimenticavo, i nomi dei fogli possono anche non essere in serie ... possiamo quindi anche avere:
paz.1
paz.3
paz.5

saluti
r

[gram...@alice.it]

Ciao r
pensavo.. se Corrado usasse questa soluzione che hai proposto ma se invece di usare la funzione ="PAZ."&RIF.RIGA(A1) nella cella w2, usasse questa funzione personalizzata =nomefoglio(A1), inserendo in un modulo di vb questo codice

Function nomefoglio(cella As Range) As String
i = cella.Row
If i <= Worksheets.Count Then
If Sheets(i).Name <> "Riepilogo" Then
nomefoglio = Sheets(i).Name
End If
End If
End Function

trascinando fino a w101 potrebbe avere l'elenco dei fogli senza preoccuparsi di mettere un suffisso, cioè potrebbe dargli il nome che vuole. Ovviamente il foglio riepilogo dovrebbe essere sempre l'ultimo per non lasciare celle vuote all'interno dell'elenco fogli.

Il resto della procedura rimarrebbe quella che hai elaborato in questo post.

Potrebbe funzionare secondo te?

Buon Natale
Stefano

[r]

Il giorno martedì 24 dicembre 2013 13:50:18 UTC+1, Stefano ha scritto:

Ciao Stefano,
come avevo detto nel caso i nomi dei fogli non abbiano una struttura regolare bisognerebbe ricorrere ad una udf. Se dovessi usare una udf però vorrei che fosse il più generale e funzionale possibile. Così, intanto, desidererei che restituisse una matrice contenente i nomi dei fogli (solo workbook non mi interessano ad esempio i nomi dei fogli grafico) poi avrei pensato a un argomento opzionale che contiene la lista dei nomi che voglio escludere. Nella udf che propongo questo argomento può essere omesso, può essere un riferimento a un range che contiene i nomi dei fogli da escludere, può essere una matrice o ancora un singolo valore.
Questa dunque la udf che consiglierei:

Function ListSheets(Optional EscludeSheetsName As Variant)

Dim SH()
Dim x, v, i As Long, b As Boolean
Application.Volatile
For Each v In ThisWorkbook.Worksheets
b = True
If IsMissing(EscludeSheetsName) Then
ElseIf IsArray(EscludeSheetsName) Or TypeOf EscludeSheetsName Is Range Then
For Each x In EscludeSheetsName
If x = v.Name Then
b = False
Exit For
End If
Next
ElseIf EscludeSheetsName = v.Name Then b = False
End If
If b Then
ReDim Preserve SH(i)
SH(i) = v.Name
i = i + 1
End If
Next
ListSheets = Application.Transpose(SH)
End Function

è bene tenere presente che seppur la funzione sia definita come volatile l'aggiunta di un foglio da solo non scatena l'evento calculate.

saluti
r

[r]

Il giorno martedì 24 dicembre 2013 15:33:26 UTC+1, r ha scritto:
> ... (solo workbook non mi interessano ad esempio i nomi dei fogli grafico)

era inteso (solo worksheet ...
saluti
r

[Corrado]

Grazie a tutti per la soluzione in vba, proverò domani stesso e vi farò sapere.

Buona serata