contare un blocco di celle piene

[tex willer]

salve a tutti, avrei bisogno di una formula che mi restituisca quante volte in un intervallo di colonna più di 10 celle sono piene.
nel mio caso nella prima colonna ho il calendario verticale e nelle successive 50 ho i nominativi del personale. in ogni colonna devo segnare con una sigla le assenze e poi riepilogarle in altro foglio tutte quelle che superano 10.

Codice: Seleziona tutto

                     AGOSTINI AMORUSO ANGELICCHIO ANTONUCCI
1 gen 2016              as      as      
2-gen                   as      as   
3-gen                   as      as      
4-gen                  
5-gen                   as         
6-gen                   as         
7-gen                   as         
8-gen                   as      as      
9-gen                   as      as      
10-gen                  as      as      
11-gen                  as      as      
12-gen                  as      as      
13-gen                  as      as      
14-gen                  as      as      
15-gen                  as      as      
16-gen                          as      
17-gen                          as      
18-gen                          as      
19-gen                          as      
20-gen                           
21-gen                  
22-gen                  
23-gen                  
24-gen                  
25-gen


[patel]

allega un file di esempio con i dati ed il risultato desiderato

[tex willer]

nell'esempio allegato nella colonna "Agostini" la formula dovrebbe restituire 1; nella colonna "Amoruso" dovrebbe restituire 1; l'intervallo mi serve mensile e/o trimestrale. scusa ma non so allegare il file

[CANAPONE]

Ciao,

primo cognome nella colonna C

Codice: Seleziona tutto

=SOMMA(SE(FREQUENZA(SE(C$1:C$500="as";RIF.RIGA($1:$500));SE(C$1:C$500<>"as";RIF.RIGA($1:$500)))>10;1))

La formula, dopo essere stata copiata, va riconfermata con il control+maiusc+invio. Richiede la forma matriciale.

Se riconfermata correttamente, la formula comparirà abbracciata da parentesi graffe.


Ne approfitto per salutare Patel.

[tex willer]

per prima cosa un grazie per la disponibilità, allora la formula prospettata funziona però solo con la stringa in esempio "as", nel mio caso si possono verificare anche mischiate e consecutive almeno altre tre sigle: MA, RI, DH (malattia, ricovero, day h), e qui richiedo di nuovo il tuo intervento. per ultimo i dati immessi possono essere scritti indifferentemente maiuscolo o minuscolo? e la formula da te scritta puo essere valida, con i dovuti cambi di riferimento, anche in altro foglio? se non chiedo molto invia anche spiegazione tecnica della formula giusto per non limitarmi a fare copia e incolla e incominciare a capirci qualcosa. grazie molte

[Anthony47]

Ti propongo questa "funzione" personalizzata:

Codice: Seleziona tutto

Function Consec10(ByRef myRan As Range, Optional ByVal hMany As Long = 10) As Long
Dim wArr, myBlk As Long, myC As Long
'
wArr = myRan.Value
For i = LBound(wArr) To UBound(wArr)
    If wArr(i, 1) = "" Then
        myC = 0
    Else
        myC = myC + 1
        If myC = (hMany + 1) Then
            myBlk = myBlk + 1
        End If
    End If
Next i
Consec10 = myBlk
End Function


Mettila in un modulo standard, poi in fondo all'elenco usa la formula

Codice: Seleziona tutto

=consec10(B2:B32)

Ti restituira' il numero di blocchi di "almeno 10 celle consecutive" non vuote (quindi un blocco unico di 20 celle consecutive non vuote sara' contato 1)
Poi copiala verso destra per le altre colonne.

E' una formula standard, quindi puoi inserirla anche in un altro foglio rispettando la sintassi NomeFoglio!Intervallo

Opzionalmente puoi indicare tu quale deve essere la lunghezza oltre la quale contare il blocco; userai la sintassi

Codice: Seleziona tutto

=Consec10(IntervalloDati;Numero)


Ciao

[tex willer]

Ho ancora problemi, ho seguito quanto da te indicato e ho creato il modulo e usato la funzione consec10, ma salvo miei errori non funziona. Provo a mettere un po d’ordine: il problema è: in un intervallo – nel mio caso E6:e400 giorno per giorno vado a segnare svariate sigle indicanti una situazione lavorativa (ST, MA, PR, L, AS, RI, DH, FE, MB, FS ECC, ECC) ora a me serve sapere solo la somma di “MA”, “DH” e “RI” e solo se queste sigle occupano consecutivamente almeno 11 celle:

a b c d e f

01-gen DH DH MA
02-gen DH DH MA
03-gen DH DH
04-gen DH DH
05-gen DH RI
06-gen MA RI MA
07-gen MA RI MA
08-gen MA RI MA
09-gen MA RI MA
10-gen MA RI MA
11-gen MA RI MA
12-gen MA RI MA
13-gen MA RI DH
14-gen DH
15-gen
16-gen MA
17-gen MA
18-gen MA MA
19-gen MA MA
20-gen MA MA
21-gen RI MA MA
22-gen RI MA MA
23-gen RI MA MA
24-gen RI MA MA
25-gen RI MA DH
26-gen MA DH
27-gen MA RI
28-gen MA MA
29-gen MA
30-gen MA
31-gen MA

Nell’esempio prospettato per intenderci il risultato dovrebbe restituire d=2; e=1; f=1
I tre eventi “MA”, “DH” e “RI”, possono verificarsi da soli o mischiati tra loro;
per il risultato giusto ogni 11 celle in cui sono riportate le sigle“MA”, “DH” e “RI”, dovrà essere uguale a 1
se possibile con formula e no macro. Grazie infinite
grazie

[CANAPONE]

Ciao

sempre in forma matriciale

Codice: Seleziona tutto

=SOMMA(SE(FREQUENZA(SE(SOSTITUISCI(SOSTITUISCI(MAIUSC(ANNULLA.SPAZI(E6:E400));"RI";"MA");"DH";"MA")="MA";RIF.RIGA($6:$400));SE(SOSTITUISCI(SOSTITUISCI(MAIUSC(ANNULLA.SPAZI(E6:E400));"RI";"MA");"DH";"MA")<>"MA";RIF.RIGA($6:$400)))>10;1))

oppure (cambia poco)

Codice: Seleziona tutto

=SOMMA(SE(FREQUENZA(SE(SOSTITUISCI(SOSTITUISCI(MAIUSC(ANNULLA.SPAZI(E6:E400));"RI";"MA");"DH";"MA")="MA";RIF.RIGA($6:$400)-5);SE(SOSTITUISCI(SOSTITUISCI(MAIUSC(ANNULLA.SPAZI(E6:E400));"RI";"MA");"DH";"MA")<>"MA";RIF.RIGA($6:$400)-5))>10;1))

Fammi sapere se vanno bene

[tex willer]

mi dispiace scocciare, volevo informarti che ho provato adesso le due formule, ma mi da messaggio che la formula contiene un errore....
finora solo questa risposta
(SOMMA(SE(FREQUENZA(SE(C$1:C$500="as";RIF.RIGA($1:$500));SE(C$1:C$500<>"as";RIF.RIGA($1:$500)))>10;1))
andava parzialmente bene perché dava il risultato sperato solamente se si usava una sola sigla(AS).ringrazio

[CANAPONE]

Ciao

quale messaggio?

Esattamente cosa leggi su Excel. Stai usando la prima formula?


Nella seconda -equivalente- mi ero dimenticato di aggiustare il primo contatore: c'era una possibilità di leggere un risultato strano ma non errore

=SOMMA(SE(FREQUENZA(SE(SOSTITUISCI(SOSTITUISCI(MAIUSC(ANNULLA.SPAZI(E6:E400)-5);"RI";"MA");"DH";"MA")="MA";RIF.RIGA($6:$400)-5);SE(SOSTITUISCI(SOSTITUISCI(MAIUSC(ANNULLA.SPAZI(E6:E400));"RI";"MA");"DH";"MA")<>"MA";RIF.RIGA($6:$400)-5))>10;1))

[CANAPONE]

Ciao

la formula è in M3


https://dl.dropboxusercontent.com/u/106 ... uenza.xlsx

Attenzione che è una formula matriciale: richiede il control+maiusc+invio per funzionare.

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

Riporto di nuovo la seconda formula dove dopo il rif.riga ho aggiunto -5

=SOMMA(SE(FREQUENZA(SE(SOSTITUISCI(SOSTITUISCI(MAIUSC(ANNULLA.SPAZI(E6:E400));"RI";"MA");"DH";"MA")="MA";RIF.RIGA($6:$400)-5);SE(SOSTITUISCI(SOSTITUISCI(MAIUSC(ANNULLA.SPAZI(E6:E400));"RI";"MA");"DH";"MA")<>"MA";RIF.RIGA($6:$400)-5))>10;1))

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


Mi fermo qui.

[tex willer]

primi test con la formula in m3 funzionano. sei un grande, ma grande grande. la proverò anche nei prossimi giorni e aggiornerò il post, spero solo per ringraziarti ulteriormente. ciao

[CANAPONE]

Ciao,

grazie del riscontro.

Fai attenzione alla forma matriciale delle formule (la riconferma con il control+maiusc+invio).

Tutte le forme che ho condiviso sfruttano FREQUENZA NELLO STESSO MODO.

Sei hai problemi allega un estratto del file con un sito file sharing.

Saluti