Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[Excel] Conta.se in versione VBA

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] Conta.se in versione VBA

Postdi mpsinf » 21/11/12 20:23

Codice: Seleziona tutto
 Dim offerenti As Range
 Dim chioffre As Range

 Set chioffre = Range("H:H")
 chioffre.Clear

 Set offerenti = Range("C:C")

For Each cl In offerenti
 If cl.Value = "" Then  'se nome è vuoto allora si ferma uscendo dal ciclo For
    Exit For
    Else
   If Application.CountIf(chioffre, cl.Value) = 0 Then  ' se CountIf restituisce zero (quindi "nome" non è presente), allora
    cellavuota = cellavuota + 1
    Cells(cellavuota, 8) = cl.Value  'e scriviamo "nome dell'offerente" nella colonna H
   End If
  End If
 
Next
 
 ' riordina celle
 Range("H:J").Select
 Selection.Sort Key1:=Range("I1"), Order1:=xlDescending, Header:=xlNo, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers
End With


Con questo codice analizzando i dati delle due colonne C e D così composte
pippo___0,6
pluto____0,54
pippo____0,32
minni____0,23
gastone__0,22
minni____0,19

ricreo una colonna H:H con i singoli nomi... inoltre sul foglio nella colonna I:I ho la formula =CONTA.SE(INDIRETTO("$C$1:$C$10000");H1) copiata per n. righe (che mi conteggia quante volta è presente il nome in H1..Hn) così pure la colonna J riporta =CERCA.VERT(H1;C:D;2;0) che mi cerca il primo valore in colonna D del nome H1

Ora visto che il foglio dove erano riportate queste formule viene ogni volta cancellato/ricreato, come posso convertire questo controllo di Excel in versione VBA ?
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Sponsor
 

Re: [Excel] Conta.se in versione VBA

Postdi scossa » 21/11/12 20:42

mpsinf ha scritto:ricreo una colonna H:H con i singoli nomi... inoltre sul foglio nella colonna I:I ho la formula =CONTA.SE(INDIRETTO("$C$1:$C$10000");H1) copiata per n. righe (che mi conteggia quante volta è presente il nome in H1..Hn) così pure la colonna J riporta =CERCA.VERT(H1;C:D;2;0) che mi cerca il primo valore in colonna D del nome H1

Ora visto che il foglio dove erano riportate queste formule viene ogni volta cancellato/ricreato, come posso convertire questo controllo di Excel in versione VBA ?


A dire il vero sia il codice che le formule utilizzate (che senso ha usare INDIRETTO("$C$1:$C$10000")....) mi lasciano alquanto perplesso.
Puoi allegare il tuo file?
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [Excel] Conta.se in versione VBA

Postdi mpsinf » 21/11/12 22:22

Indiretto l'avevo usato perchè, se in C viene inserito un dato, con l'abbassamento della riga verso il basso, l'intervallo rimane costante.
Inviare il file non ha molto senso visto che è composto da 2 colonne, e devo fare il conteggio delle volte che è presente un determinato nome in una lista.
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Re: [Excel] Conta.se in versione VBA

Postdi Anthony47 » 21/11/12 23:01

Se vuoi inserire quelle formule in I1 e J1 userai le istruzioni
Codice: Seleziona tutto
    Range("I1").Formula = "=COUNTIF(INDIRECT(""$C$1:$C$10000""),H1)"
    Range("J1").Formula = "=VLOOKUP(D1,A:A,1,0)"

Poi calcoli nella variabile LastR l' ultima riga su cui devi ricopiare le formule; ad es
Codice: Seleziona tutto
LastR=Cells(Rows.Count, "A").End(Xlup).Row   'Calcolo fatto su colonna A
E infine trascini I1:J1 con l' istruzione
Codice: Seleziona tutto
    Range("I1:J1").AutoFill Destination:=Range("I1:J" & LastR), Type:=xlFillDefault

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

Re: [Excel] Conta.se in versione VBA

Postdi Flash30005 » 21/11/12 23:09

Ma forse ti occorre una macro così?
Codice: Seleziona tutto
Sub CreaEleConta()
UR1 = Range("C" & Rows.Count).End(xlUp).Row
For RR1 = 1 To UR1
Tr = 0
UR2 = Range("H" & Rows.Count).End(xlUp).Row + 1
    For RR2 = 1 To UR2
    If Range("H" & RR2).Value = Range("C" & RR1).Value Then
        Range("I" & RR2).Value = Range("I" & RR2).Value + Range("D" & RR1).Value
        Tr = 1
    End If
    Next RR2
    If Tr = 0 Then
    Range("H" & UR2).Value = Range("C" & RR1).Value
    Range("I" & UR2).Value = Range("I" & UR2).Value + Range("D" & RR1).Value
    End If
Next RR1
End Sub


in "H" crea l'elenco nomi unici e in "I" fa la somma corrispondente al nome trovato

Ciao


EDIT: C'è un messaggio di Anthony prima di questo
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [Excel] Conta.se in versione VBA

Postdi mpsinf » 21/11/12 23:15

Wow quante istruzioni nuove da imparare e collaudare!! Grazie ad entrambi ...vamos al collaudo
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Re: [Excel] Conta.se in versione VBA

Postdi scossa » 22/11/12 08:51

mpsinf ha scritto:Indiretto l'avevo usato perchè, se in C viene inserito un dato, con l'abbassamento della riga verso il basso, l'intervallo rimane costante.


????
mi spieghi la differenza tra
=CONTA.SE(INDIRETTO("$C$1:$C$10000");H1)
e
=CONTA.SE($C$1:$C$10000;H1)
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [Excel] Conta.se in versione VBA

Postdi mpsinf » 22/11/12 13:38

Se in C viene inserito un dato, con un copia incolla con l'abbassamento della riga verso il basso copiata da una macro che elenca valori in sequenza =CONTA.SE($C$1:$C$10000;H1) diventa =CONTA.SE($C$2:$C$10001;H1) con indiretto invece l'intervallo rimane sempre da 1 a 10.000
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Re: [Excel] Conta.se in versione VBA

Postdi scossa » 22/11/12 13:58

mpsinf ha scritto:Se in C viene inserito un dato, con un copia incolla con l'abbassamento della riga verso il basso copiata da una macro che elenca valori in sequenza =CONTA.SE($C$1:$C$10000;H1) diventa =CONTA.SE($C$2:$C$10001;H1) con indiretto invece l'intervallo rimane sempre da 1 a 10.000


Ah, ok ora ho capito cosa intendevi.
Grazie.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [Excel] Conta.se in versione VBA

Postdi scossa » 22/11/12 14:39

mpsinf ha scritto:Ora visto che il foglio dove erano riportate queste formule viene ogni volta cancellato/ricreato, come posso convertire questo controllo di Excel in versione VBA ?


Questo codice dovrebbe fare quello che chiedi:

Codice: Seleziona tutto
Public Sub prova()
  Dim ws As Worksheet
  Dim offerenti As Range
  Dim chioffre As Range
  Dim cl As Range
  Dim cellavuota As Long

  Set ws = ThisWorkbook.ActiveSheet
  Set chioffre = ws.Range("H1")
  Set offerenti = ws.Range("C1").CurrentRegion
 
  chioffre.CurrentRegion.Clear
 
  For Each cl In offerenti.Resize(, 1)
    If Application.CountIf(chioffre, cl.Value) = 0 Then 
      cellavuota = cellavuota + 1
      chioffre.Cells(cellavuota).Value = cl.Value
      Set chioffre = chioffre.CurrentRegion
    End If
  Next
 
  For Each cl In chioffre
    cl.Offset(0, 1) = Application.CountIf(offerenti.Columns(1), cl)
    cl.Offset(0, 2) = Application.VLookup(cl, offerenti, 2, 0)
  Next
 
 
  ' riordina celle
  Set chioffre = chioffre.CurrentRegion
  chioffre.Sort Key1:=chioffre(1, 2), Order1:=xlDescending, Header:=xlNo, _
  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
  DataOption1:=xlSortTextAsNumbers
 
  Set chioffre = Nothing
  Set offerenti = Nothing
  Set ws = Nothing
End Sub
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [Excel] Conta.se in versione VBA

Postdi scossa » 22/11/12 15:05

scossa ha scritto:
mpsinf ha scritto:Ora visto che il foglio dove erano riportate queste formule viene ogni volta cancellato/ricreato, come posso convertire questo controllo di Excel in versione VBA ?


Questo codice dovrebbe fare quello che chiedi:


Ovviamente dando per scontato che le colonn B E G K siano vuote, altrimenti devi circoscrivere UsedRange.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [Excel] Conta.se in versione VBA

Postdi mpsinf » 22/11/12 20:00

Pur apprezzando lo sforzo e l'insegnamento sul Region ho risolto con il tuo aiuto in maniera un po' più spartana visto che nelle altre colonne c'erano altri dati ma non utili.

Codice: Seleziona tutto
Sub A3_Rush()
With Worksheets("Rush")
 Dim offerenti As Range
 Dim chioffre As Range

 Range("H:J").Clear
 
 Set chioffre = Range("H:H")
 Set offerenti = Range("C:C")

For Each cl In offerenti
 If cl.Value = "" Then  'se nome è vuoto allora si ferma uscendo dal ciclo For
    Exit For
    Else
   
   If Application.CountIf(chioffre, cl.Value) = 0 Then 
    cellavuota = cellavuota + 1
    Cells(cellavuota, 8) = cl.Value  'e scriviamo "nome dell'offerente" nella colonna H
   End If
 End If
Next
 
Set offerenti = Range("C1:D" & Cells(Rows.Count, "D").End(xlUp).Row)

For Each cl In chioffre
    cl.Offset(0, 1) = Application.CountIf(offerenti.Columns(1), cl)
    cl.Offset(0, 2) = Application.VLookup(cl, offerenti, 2, 0)
    i = i + 1
    If i = .Cells(Rows.Count, "H").End(xlUp).Row Then Exit For
Next
 
 ' riordina celle per n. di offerte
 Range("H:J").Select
 Selection.Sort Key1:=Range("I1"), Order1:=xlDescending, Header:=xlNo, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers
End With
End Sub
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Re: [Excel] Conta.se in versione VBA

Postdi Flash30005 » 22/11/12 20:37

Ma nella colonna "J" non volevi i totali dei singoli nomi?
Nella macro da me postata questo lo ottenevi nella colonna I (ma non hai detto nulla a riguardo)
ora, visto che ti occorre anche contare le presenze (in I) ho implementato la stessa macro per ottenere i risultati corretti

Codice: Seleziona tutto
Sub CreaEleConta()
UR1 = Range("C" & Rows.Count).End(xlUp).Row
 Range("H:J").Clear
For RR1 = 1 To UR1
Tr = 0
UR2 = Range("H" & Rows.Count).End(xlUp).Row + 1
    For RR2 = 1 To UR2
    If Range("H" & RR2).Value = Range("C" & RR1).Value Then
        Range("I" & RR2).Value = Range("I" & RR2).Value + 1
        Range("J" & RR2).Value = Range("J" & RR2).Value + Range("D" & RR1).Value
        Tr = 1
    End If
    Next RR2
    If Tr = 0 Then
    Range("H" & UR2).Value = Range("C" & RR1).Value
    Range("I" & UR2).Value = 1
    Range("J" & UR2).Value = Range("D" & RR1).Value
    End If
Next RR1
 Range("H:J").Select
 Selection.Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlNo, _
 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
 DataOption1:=xlSortTextAsNumbers
Range("H1").Select
End Sub


Ciao
Flash
Win7 + Office 2010 Ita
"Fotografica" al servizio dell'immagine

Ottime opportunità di lavoro (part-time o full-time) con guadagni immediati. Info in MP
Avatar utente
Flash30005
Moderatore
 
Post: 8460
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: [Excel] Conta.se in versione VBA

Postdi mpsinf » 23/11/12 08:11

Alla fine ho scelto il tuo codice Flash30005 in quanto molto breve e lineare

Ho solo tolto la seguente riga
Codice: Seleziona tutto
Range("J" & RR2).Value = Range("J" & RR2).Value + Range("D" & RR1).Value

perchè ha me interessava solo il primo valore trovato e non la somma dei risultati

e unico neo l'elencazione parte dalla 2a riga ma poi il comando dati ordina nella parte finale mette tutto a posto..

Grazie anche agli altri perchè credo che questo post faccia parte di quegli esempi di come uno stesso caso con risultati quasi uguali che portano allo stesso obiettivo, si possa affrontare in maniera con approcci differenti. :!:
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Re: [Excel] Conta.se in versione VBA

Postdi mpsinf » 24/11/12 09:38

Nelle varie esperienze di VBA mi sono accorto che per la selezione di n. righe si usano in VBA dei Rows.Count, ma volevo chiedere la differenza sostanziale o solo formale che ci sono tra questi 2 cicli for
Codice: Seleziona tutto
With Worksheets(Sheets(1).Name)

For n = 1 To .Range("K" & Rows.Count).End(xlUp).Row

For n = 1 To .Cells(Rows.Count, "K").End(xlUp).Row
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Re: [Excel] Conta.se in versione VBA

Postdi scossa » 24/11/12 10:00

mpsinf ha scritto:Nelle varie esperienze di VBA mi sono accorto che per la selezione di n. righe si usano in VBA dei Rows.Count, ma volevo chiedere la differenza sostanziale o solo formale che ci sono tra questi 2 cicli for
Codice: Seleziona tutto
With Worksheets(Sheets(1).Name)

For n = 1 To .Range("K" & Rows.Count).End(xlUp).Row

For n = 1 To .Cells(Rows.Count, "K").End(xlUp).Row


Non so dove hai trovato With Worksheets(Sheets(1).Name) ma non ha senso, meglio:
With Worksheets(1) che però è dipendente dall'ordine in cui hai i fogli nella cartella, o meglio ancora
With Worksheets("Nome del foglio")

Riguardo alle due notazioni: sono equivalenti, la prima fa riferimento all'oggetto Range (di cui specifica una solo cella), il secondo alla proprietà Cells dell'oggetto Worksheet.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [Excel] Conta.se in versione VBA

Postdi scossa » 24/11/12 10:05

mpsinf ha scritto:Alla fine ho scelto il tuo codice Flash30005 in quanto molto breve e lineare


Solo chiarezza, senza spirito di polemica (non me ne voglia Flash): se si omettono le dichiarazioni delle variabili, la distruzione delle istanze degli oggetti (set = ... nothing), i commenti e le righe vuote (che rendono più leggibile il codice) vedrai che anche il mio codice diventa breve ed è assolutamente lineare.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [Excel] Conta.se in versione VBA

Postdi mpsinf » 17/03/13 09:50

Anthony47 ha scritto:Se vuoi inserire quelle formule in I1 e J1 userai le istruzioni
Codice: Seleziona tutto
    Range("I1").Formula = "=COUNTIF(INDIRECT(""$C$1:$C$10000""),H1)"
    Range("J1").Formula = "=VLOOKUP(D1,A:A,1,0)"


Quando eseguo la riga di codice
Codice: Seleziona tutto
Range("B4").Formula = "=CONTA.SE(K:K;" >= " & B2-2)"

in B4 mi appare VERO invece del numero del conteggio
selezionando la cella B4 NON mi ritrovo la formula al suo interno :eeh: come posso fare per inserirla?

Inoltre volevo sapere se c'era un modo per evitare il copia incolla (del codice qui sotto), in quanto facendo girare la macro sul PC dove lavoro, quando uso il copia incolla in un altro programma mi ritrovo la clipboard contenente i dati che sta copiando la macro... (a dire il vero mi succede addirittura da altro PC collegato solo con un KVM :evil: ?? ) ed anche perchè ad ore di distanza di esecuzione mi da un errore di invalid Range alla riga di insert

Codice: Seleziona tutto
If .Cells(a, "E").Value <> "" And .Cells(a, "E").Value > .Range("L1").Value Then
     .Range("D" & a & ":H" & a).Copy 'brutto codice a vedersi
     .Range("K1:O1").Insert Shift:=xlDown  '  [b]qui alle volte va in errore[/b]
   End If
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Re: [Excel] Conta.se in versione VBA

Postdi mpsinf » 17/03/13 10:38

Codice: Seleziona tutto
Range("B4").Formula = "=MAX(O:O)"
funziona
Codice: Seleziona tutto
Range("B4").Formula = "=CONTA.SE(K:K;" >= " & B2-2)"
NO !!
la questione mi pare di capire sia sulle ""
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Re: [Excel] Conta.se in versione VBA

Postdi mpsinf » 17/03/13 10:54

Codice: Seleziona tutto
Range("B4").FormulaLocal = "=CONTA.SE(K:K;"">="" & B2-2)"

con questa è andata !! mistero o :idea:
XP + Excel 2003 Ita
W7 + Excel 2007 Ita
mpsinf
Utente Senior
 
Post: 168
Iscritto il: 03/09/03 18:33

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "[Excel] Conta.se in versione VBA":


Chi c’è in linea

Visitano il forum: Nessuno e 5 ospiti

cron