Condividi:        

Excel - Conteggio valori uguali contigui

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

Excel - Conteggio valori uguali contigui

Postdi c1452 » 23/02/12 17:49

Salve ragazzi avrei bisogno del vostro aiuto non sono molto pratico di excel
Ho una riga con circa 20 valori che sono sempre "V", "P", "S" nell'intervallo B2:U2
Vorrei calcolare in una cella a mia scelta il valore massimo in cui "V" si ripete consecutivamente

se mi limito a =(CONTA.SE(B2:U2;"v")) mi restituirà la somma delle ripetizioni di "V"
io invece voglio il numero max di ripetizioni nell'intervallo B2:U2 anche se ci dovessero essere + ripetizioni
c1452
Newbie
 
Post: 2
Iscritto il: 23/02/12 17:43

Sponsor
 

Re: Excel - Conteggio valori uguali contigui

Postdi Anthony47 » 23/02/12 22:58

Per questo la cosa piu' semplice e' una funzione ad hoc:
-da Excel, Alt-F11 per aprire l' editor delle macro
-Menu /Inserisci /Modulo
-Copia il seguente codice e incollalo nel frame di dx
Codice: Seleziona tutto
Function Contig(ByRef Myaddr As Range, ByVal myVal As String) As Integer
For Each cell In Myaddr
    If cell.Value = myVal Then
        ccnt = ccnt + 1
    Else
        If ccnt > Contig Then Contig = ccnt
        ccnt = 0
    End If
Next cell
End Function

Poi torni su Excel e userai una formula del tipo
Codice: Seleziona tutto
=contig(B2:U2;"V")

Ovviamente puoi cambiare il range o il valore da cercare; il valore e' "case sensitive", cioe' fa differenza tra maiuscolo e minuscolo.

Ciao, fai sapere.
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel - Conteggio valori uguali contigui

Postdi Karistotele » 24/02/12 15:09

Ciao Anthony, ciao alla new entry c145
essendo io più pratico di formule che di VBA, a me viene in mente la funzione frequenza, però vorrei capire come inserire in “forma matriciale” la tua UDF (molto semplice anche per me) per non ripetere tre volte la stessa operazione (in questo caso), ma ... se un range prevedesse 10, 15, eccetera valori unici? Come ovviare?

Sai, approfitto …. e ti ringrazio se vorrai delucidarmi in merito.

Intanto visto che il nostro amico dichiara di essere un po’ digiuno di Excel io proporrei appunto una formula non molto impegnativa.

Inserire in A3 la lettera V, in A4 la lettera P e in A5 la lettera S.Oppure in altro ordine.

Digitare in B3

Codice: Seleziona tutto
=MAX(FREQUENZA(SE($B$2:$U$2=A3;RIGHE($B$2:$U$2));SE($B$2:$U$2<>A3;RIGHE($B$2:$U$2))


Gradito riscontro, intanto la provo.
.K
Avatar utente
Karistotele
Utente Junior
 
Post: 45
Iscritto il: 01/05/10 06:28
Località: PADOVA

Re: Excel - Conteggio valori uguali contigui

Postdi c1452 » 24/02/12 21:27

Ho provato ad inserire la funzione di Antony, perfetta grazie a tutti. Funziona proprio come volevo...grazie
c1452
Newbie
 
Post: 2
Iscritto il: 23/02/12 17:43

Re: Excel - Conteggio valori uguali contigui

Postdi Karistotele » 24/02/12 23:42

c1452 ha scritto:Ho provato ad inserire la funzione di Antony, perfetta grazie a tutti. Funziona proprio come volevo...grazie


... e chi ha detto che non funziona?
Anche la formula funziona. Hai liberamente scelto e sono compartecipe della tua soddisfazione.

"Non importa che il gatto sia bianco o nero, l'importante è che prenda il topo"

Per quanto mi riguarda approfittavo dell'occasione per chiedere ad Anthony una precisazione su possibili variazioni, solo a fini "didattici". per generalizzare la function

Ciao e alla prossima.
.K
Avatar utente
Karistotele
Utente Junior
 
Post: 45
Iscritto il: 01/05/10 06:28
Località: PADOVA

Re: Excel - Conteggio valori uguali contigui

Postdi Anthony47 » 25/02/12 02:14

La formula di K e la mia udf restituiscono valori diversi; la udf conta il max dei valori adiacenti uguali, la formula e' l' equivalente di un Conta.Se.

Per k: quella Udf non e' utilizzabile in una formula a matrice, perche' restituisce un valore stringa mentre (per cominciare) la formula matrice richiede un array.
Questa e' abbastanza vicina:
Codice: Seleziona tutto
Function ContigArr(ByRef MyTarg As Range, ByRef myVals As Range) As Variant
Dim myArr(), myMax As Integer
ReDim myArr(myVals.Count - 1)
For I = 1 To myVals.Count
myVal = myVals.Range("A1").Offset(I - 1, 0).Value
myMax = 0
For Each Cell In MyTarg
aab = Cell.Value
    If Cell.Value = myVal Then
        ccnt = ccnt + 1
    Else
        If ccnt > myMax Then myMax = ccnt
        ccnt = 0
    End If
Next Cell
myArr(I - 1) = myMax
Next I
ContigArr = myArr()
End Function

Richiede che i parametri siano passati come Riferimento, non come valori, e restituisce un array "Orizzontale" con gli N risultati; puoi quindi applicarla su un range di N "colonne" per avere gli N risultati, oppure puoi "pescare" tra i risultati; ad esempio il valore piu' grande lo trovi con, ad esempio,
Codice: Seleziona tutto
=GRANDE(contigarr(A2:O2;A3:A5);1)
Se vuoi rappresentare i dati in verticale dovrai usare MATR.TRASPOSTA, ad esempio
Codice: Seleziona tutto
=MATR.TRASPOSTA(contigarr(A2:O2;A3:A5))
Userai Contr-Maiusc-Enter quando vuoi applicare la formula su piu' celle, negli altri casi non e' necessario.

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel - Conteggio valori uguali contigui

Postdi Karistotele » 25/02/12 12:29

Karistotele siccome ha cambiato pusher ha scritto:Anche la formula funziona


Scritta velocemente su un pezzo di carta. Si può togliere anche il MAX ed essa diventa un semplice CONTA.SE, inserendola naturalmente come matriciale.

La UDF di Anthony è eccellente in funzione del tuo preciso quesito e ringrazio Anthony, come sempre generoso, per avermi fornito le delucidazioni che avevo chiesto.

Ciao.

P.S. Sto lavorando duro per attrezzarmi per il prossimo errore.
.K
Avatar utente
Karistotele
Utente Junior
 
Post: 45
Iscritto il: 01/05/10 06:28
Località: PADOVA

Re: Excel - Conteggio valori uguali contigui

Postdi syncoopate » 12/03/18 23:12

Codice: Seleziona tutto
Function Contig(ByRef Myaddr As Range, ByVal myVal As String) As Integer
For Each cell In Myaddr
    If cell.Value = myVal Then
        ccnt = ccnt + 1
    Else
        If ccnt > Contig Then Contig = ccnt
        ccnt = 0
    End If
Next cell
End Function



Salve un ulteriore sforzo sarebbe possibile su questa UDF :D....bene io vorrei una UDF che mi indica quanti Blocchi di Valori contigui ci sono in un range , esempio : in questa serie in colonna 1112111131114441111555551111 indicando nella UDF la ricerca dei blocchi con valore 1 e consecutivi 4 mi deve restituire 3 , in quanto son presenti 3 blocchi nel range con quattro 1 consecutivi....chiaro no???
Si puo´ modificare ?? :D
syncoopate
Utente Junior
 
Post: 13
Iscritto il: 11/03/12 19:26

Re: Excel - Conteggio valori uguali contigui

Postdi wallace&gromit » 13/03/18 10:14

Ciao, devi aggiungere un valore in più, che ho chiamato myGoal e che indica il valore della successione che stai cercando (4 nel tuo esempio).
Codice: Seleziona tutto
Function Contig(ByRef Myaddr As Range, ByVal myVal As String, ByVal myGoal As Integer) As Integer
For Each cell In Myaddr
    If cell.Value = myVal Then
        ccnt = ccnt + 1
    Else
        If ccnt = myGoal Then Contig = Contig + 1
        ccnt = 0
    End If
Next cell
End Function
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: Excel - Conteggio valori uguali contigui

Postdi syncoopate » 13/03/18 10:55

Grazie Wallace&gromit ,
purtroppo mi restituisce errore di compilazione , Argomento non facoltativo...
io lo usata cosi come espressione =Contig(E62:E80;-1;2)
praticamente ho selezionato le celle in colonna su cui fare la ricerca , dopo ho messo il valore da ricercare (-1) e dopo ho indicato il numero dei blocchi formati dal (-1) da contare .
In sostanza mi deve restituire la quantità di blocchi con 2 celle contigue con all'interno il valore -1
faccio un esempio numeri in colonna ( -2-2-2-2-1-1-8-8-8-1-1-5-1-1-4-1-1 ) il risultato su questa serie in colonna deve essere 4 in quanto ha contato i blocchi con 2 (-1) contigui ... :D
Spero possa aiutarti :D
Grazie per la collaborazione....aspetto aggiornamenti .
Syncoopate
syncoopate
Utente Junior
 
Post: 13
Iscritto il: 11/03/12 19:26

Re: Excel - Conteggio valori uguali contigui

Postdi wallace&gromit » 13/03/18 11:08

prova così:
Codice: Seleziona tutto
Function Contig(ByRef Myaddr As Range, ByVal myVal As Integer, ByVal myGoal As Integer) As Integer
For Each cell In Myaddr
    If cell.Value = myVal Then
        ccnt = ccnt + 1
    Else
        If ccnt = myGoal Then Contig = Contig + 1
        ccnt = 0
    End If
Next cell
End Function
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: Excel - Conteggio valori uguali contigui

Postdi syncoopate » 13/03/18 11:09

Scusa Wallace&gromit ,
Errore mio... funziona perfettamente ...Grazie :D
syncoopate
Utente Junior
 
Post: 13
Iscritto il: 11/03/12 19:26

Re: Excel - Conteggio valori uguali contigui

Postdi syncoopate » 19/03/18 20:19

Salve,
sempre sulla questione Valori Contigui , ho un piccolo dilemma .....per facilitare la comprensione allego un file chiarificatore

http://www.filedropper.com/filexforum

La macro mi dovrebbe ciclare tutti i valori da ricercare (colonna in giallo ) e trovare sul pannello( c3: w25 ) la N. quantita' di celle consecutive in verticale specificate nella ( colonna celeste ). Praticamente la macro mi deve trovare nel pannello tutte le celle con 4 consecutivi di valore "-1" , una volta trovate deve riportarmi il numero di riferimento della riga 2 ( nell esempio il num. 21 ) nel primo range disponibile e vuoto di 4 celle consecutive sul pannello grigliato di fianco, cosi via fino a terminare i cicli delle colonne gialla e blu sulla sinistra... Chiaramente i valori delle colonne sono variabili qui a mod di esempio li ho inseriti a caso.
Spero che il file sia stato fatto in maniera chiara.... :oops:

Un Abbraccio Syncoopate.... :D
syncoopate
Utente Junior
 
Post: 13
Iscritto il: 11/03/12 19:26

Re: Excel - Conteggio valori uguali contigui

Postdi Anthony47 » 20/03/18 02:15

Il file si presenta bene, ma io non ho capito niente lo stesso...
Proviamo con un paio di esempi: cosa ci dovrebbe essere scritto da Z3 verso destra, da Z4 verso destra, e da Z5 verso destra? E perche'?
E quale e' il significato di quei numeri viola presenti nelle colonne Z e AA?

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel - Conteggio valori uguali contigui

Postdi syncoopate » 20/03/18 10:10

Buongiorno ,
- le celle nella colonna gialla indicano il valore da ricercare all'interno delle celle del pannello
- le celle nella colonna blu indicano la quantità di contigui che devono essere presi in esame ( nel caso del valore "-1"(colonna gialla) prenderemo in esame solo gli eventi con che avranno "4"celle contigue con all'interno il valore "-1" quindi nel caso proposto l'unico evento con 4 celle contigue contenenti il valore "-1)" lo troviamo dalla cella w22 alla cella w25 ....quando la condizione è verificata riporta il numero in cima della cella"w2" il num. (21) nel primo range vuoto disponibile sul grigliato di fianco mantenendo però inalterato il valore riga ( cioè lo sposta lateralmente )
Ora per quanto riguarda i numeri viola nella riga c2:w2 sono i valori da riportare nel grigliato da compilare( quello di destra ) nel caso si verifichi la condizione sopra descritta.
Invece i numeri in viola nel grigliato di destra , li ho inseriti per rendere l'idea di come avrebbe lavorato la macro se avesse ciclato le prime 2 celle delle colonne gialla e blu , ovvero a riportato nel primo range disponibile del grigliato a destra i numeri corrispondenti in caso di condizione verificata , di fatti troviamo 1 solo evento con 4 "-1" contigui che son stati trovati nella colonna del numero (21viola) e quindi riporto per 4 volte nel grigliato da compilare il valore 21 ....stesso modus operandi per i valori che hanno 2 celle contigue con all'interno il valore -2 .
per facilità ho usato la formattazione condizionale che mi evidenziava tutti i valori -1 in rosa e in giallo i valori -2 , in modo da rendere più facile l'individuazione delle celle contigue con lo stesso valore all'interno.
Grazie Anthony :D
syncoopate
Utente Junior
 
Post: 13
Iscritto il: 11/03/12 19:26

Re: Excel - Conteggio valori uguali contigui

Postdi Anthony47 » 20/03/18 16:00

Il massimo che sono riuscito a immaginare e' questo codice:
Codice: Seleziona tutto
Sub maCChec()
Dim taOut As String, taInp As String, lFor As Integer, lMany As Integer
Dim I As Long, J As Long, K As Long, rOk As Boolean, rCol As Long
'
taInp = "C3:W25"
taOut = "Z3:Z25"
'
Range(taOut).Resize(, 100).ClearContents
For I = 3 To Cells(Rows.Count, 1).End(xlUp).Row
    lFor = Cells(I, 1)
    lMany = Cells(I, 2)
    If lMany > 0 Then
        For J = Range(taInp).Range("A1").Row To Range(taInp).Rows.Count + Range(taInp).Range("A1").Row - 1
            For K = Range(taInp).Range("A1").Column To Range(taInp).Columns.Count + Range(taInp).Range("A1").Column - 1
                rOk = myM(Cells(J, K), lFor, lMany)
                If rOk Then
                    rCol = nxC(Cells(J, K), lMany, Range(taOut).Cells(1, 1).Column)
                    Cells(J, rCol).Resize(lMany) = Cells(2, K)
                End If
            Next K
        Next J
    End If
Next I
MsgBox ("Completato...")
End Sub


Function myM(ByRef myRan As Range, ByVal llFor As Integer, ByVal llMany As Integer) As Boolean
    If Application.WorksheetFunction.CountIf(myRan.Resize(llMany, 1), llFor) <> llMany Then Exit Function
    If Application.WorksheetFunction.CountIf(myRan.Offset(-1, 0).Resize(llMany + 2, 1), llFor) <> llMany Then Exit Function
    myM = True
End Function

Function nxC(ByRef myRan As Range, ByVal llMany As Integer, c0 As Integer) As Long
Dim LastC
    On Error Resume Next
    LastC = myRan.Cells(1).Offset(-0, 0).Resize(llMany + 0, 1000).Find(What:="*", _
                       After:=myRan.Cells(1).Offset(-0, 0), _
                       Lookat:=xlPart, _
                       LookIn:=xlValues, _
                       SearchOrder:=xlByColumns, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Column
    On Error GoTo 0
If LastC < c0 Then
    nxC = c0
Else
    nxC = LastC + 1
End If
End Function


Inseriscilo tutto in un Modulo standard del vba all'interno del tuo file, e quando sei pronto avvia la Sub maCChec

Buon collaudo...
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel - Conteggio valori uguali contigui

Postdi syncoopate » 20/03/18 16:54

:D , grazie Anthony , si sul file esegue perfettamente ciò che richiedevo, :D potresti aggiungere dei commenti sulla macro per capire il funzionamento e poterla io magari adattare :D
Bella Tosta comunque ...mamma mia :D :D ....Complimenti a Go-Go .....:D
Un'Abbraccio Syncoopate :D
syncoopate
Utente Junior
 
Post: 13
Iscritto il: 11/03/12 19:26

Re: Excel - Conteggio valori uguali contigui

Postdi Anthony47 » 21/03/18 15:36

Riallego la macro con ricchi commenti:
Codice: Seleziona tutto
Sub maCChec()
Dim taOut As String, taInp As String, lFor As Integer, lMany As Integer
Dim I As Long, J As Long, K As Long, rOk As Boolean, rCol As Long
'
'Le coordinate delle tabelle:
taInp = "C3:W25"
taOut = "Z3:Z25"
'Azzera tabella uscita
Range(taOut).Resize(, 100).ClearContents
For I = 3 To Cells(Rows.Count, 1).End(xlUp).Row
    lFor = Cells(I, 1)      'Valore da cercare
    lMany = Cells(I, 2)          'Occorrenze
    If lMany > 0 Then            'Salta righe con Occorrenze=0
    'Cerca in tutte le celle della tabella
        For J = Range(taInp).Range("A1").Row To Range(taInp).Rows.Count + Range(taInp).Range("A1").Row - 1
            For K = Range(taInp).Range("A1").Column To Range(taInp).Columns.Count + Range(taInp).Range("A1").Column - 1
                'myM controlla che l'area della cella contenga quel valore tante volte
                rOk = myM(Cells(J, K), lFor, lMany)
                If rOk Then
                    'Calcola prima colonna libera e compila tabella Output
                    rCol = nxC(Cells(J, K), lMany, Range(taOut).Cells(1, 1).Column)
                    Cells(J, rCol).Resize(lMany) = Cells(2, K)
                End If
            Next K
        Next J
    End If
Next I
MsgBox ("Completato...")
End Sub


Function myM(ByRef myRan As Range, ByVal llFor As Integer, ByVal llMany As Integer) As Boolean
'Restituisce True se il valore cercato e' presente esattamente tante volte
    If Application.WorksheetFunction.CountIf(myRan.Resize(llMany, 1), llFor) <> llMany Then Exit Function     'Presente tante volte?
    If Application.WorksheetFunction.CountIf(myRan.Offset(-1, 0).Resize(llMany + 2, 1), llFor) <> llMany Then Exit Function     'e non di piu'?
    myM = True
End Function

Function nxC(ByRef myRan As Range, ByVal llMany As Integer, c0 As Integer) As Long
'Calcola la colonna libera di tabella Output in cui poter scrivere
Dim LastC
    On Error Resume Next
    LastC = myRan.Cells(1).Offset(-0, 0).Resize(llMany + 0, 1000).Find(What:="*", _
                       After:=myRan.Cells(1).Offset(-0, 0), _
                       Lookat:=xlPart, _
                       LookIn:=xlValues, _
                       SearchOrder:=xlByColumns, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Column
    On Error GoTo 0
If LastC < c0 Then
    nxC = c0
Else
    nxC = LastC + 1
End If
End Function
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel - Conteggio valori uguali contigui

Postdi syncoopate » 21/03/18 17:26

Anthony47
...Sei un Signore....Grazie :D
Molto interessante il tutto .... a breve altri grattacapo .....hahahahaa :D :D
syncoopate
Utente Junior
 
Post: 13
Iscritto il: 11/03/12 19:26


Torna a Applicazioni Office Windows


Topic correlati a "Excel - Conteggio valori uguali contigui":


Chi c’è in linea

Visitano il forum: Ricky0185 e 40 ospiti