Eliminare celle inutili

Hai un problema con Excel?

Nuova discussione

Eliminare celle inutili
di Orsero data: 11/05/2013 10:07:18
Buongiorno a tutti,
ho il seguente problema a cui spero di riuscire tramite voi a trovare una soluzione:
Il codice seguente processa i dati di una colonna, quando incontra una cella vuota si ferma, taglia i 10 valori iniziali e i 10 finali dei dati trovati e su essi calcola la media e la scrive in una cella prestabilita. Poi ricomincia il ciclo fino alla prossima cella vuota e cosi via.
Il problema è che, se incontra un gruppo di poche celle (una, due, tre etc) non potendo restringere il range di 10 valori perché non esistono, si blocca. Poiché tali valori a me non interessano, si può fare in modo che, se non esiste la possibilità di restringere il range, ignori tali valori e continui il ciclo fino ai prossimi valori utili?
Pensavo ad un ciclo if che dicesse: se il range di celle trovato è cosi piccolo da non poterlo restringere allora ignoralo e passa al successivo ma non sono stato capace di tradurlo in vba.
Spero di essere stato il più chiato possibile e confido in una vostra risposta. Il link al file di esempio è il seguente: h t t p s :// docs.google.com /file/d/0B5TqB-Y8jMqMYzlkRWdtV0xBYlU/edit?usp=sharing
Grazie a tutti per l'attenzione e buona giornata.

Sub MediaFiltrata()
'Esegue la media sulla colonna eliminando i primi e gli ultimi dieci valori

  Dim ws As Worksheet
  Dim rng As Range
  Dim cella As Range
  Dim nCol As Long
  Dim nRow As Long
   
  Set ws = ActiveSheet
 
  With ws
    Set rng = Intersect(.Columns(8), .Cells(1, 1).SpecialCells(xlCellTypeFormulas, 1))
    nRow = 3
    For Each cella In rng.Areas
      Set cella = cella.Resize(cella.Rows.Count - 20).Offset(10)
      .Cells(nRow, 10).Value = Application.Average(cella)  ' media da riga 3
      nRow = nRow + 1
    Next
  End With
 
  Set ws = Nothing
  Set rng = Nothing

End Sub

di raffaele_53 (utente non iscritto) data: 11/05/2013 12:40:47
Non sono sicuro e non posso provare (link errato), con l'istruzione "len()" ottieni il numero caratteri come richiedi.

...
Dim var As String
...
For Each cella In rng.Areasvar = Cella
if var >20 then
'copia dove vuoi
else
'oppure non fare nulla
end if
...
di raffaele_53 (utente non iscritto) data: 11/05/2013 12:46:46
Dim var As String
...
For Each cella In rng.Areas
cella = Var
if var >20 then
'copia dove vuoi
else
'oppure non fare nulla
end if

... almeno credo
di raffaele_53 (utente non iscritto) data: 11/05/2013 12:51:07
Scusate ancora..., sono fuso

if len(var)>20 then
di Orsero data: 11/05/2013 16:31:02
Grazie!!!
proverò a verificare ma, ad un primo tentativo, sembra non funzionare.
Il link è il seguente ma devi eliminare gli spazi tra le lettere perché non mi fa inserire il link nel messaggio...
h t t p s :// docs.google.com/file/d/0B5TqB-Y8jMqMYzlkRWdtV0xBYlU/edit?usp=sharing
di Raffaele_53 (utente non iscritto) data: 11/05/2013 17:07:12
Il link è una foto, meglio se allegavi il files in basso a destra.

Rileggendo il post mi è sorto un dubbio....
>>>quando incontra una cella vuota si ferma, taglia i 10 valori iniziali e i 10 finali dei dati trovati

Domanda....? Quali dati se la cella è vuota.
Ti riferisci ad una cella nella stessa riga?
Se fosse SI, devi spostare il valore di CELLA per TOT colonne
Forse non ho capito nulla, come hai scritto il CODICE mi sembravi bravo.
Fammi sapere.

.....
di Orsero data: 11/05/2013 17:33:06
Ciao,
il file non è una foto basta andare su file e cliccare su "scarica" per scaricare il file.
Riguardo alla tua domanda:
Scorro una lista di celle di una colonna fin quando ne incontro una vuota, a quel punto mi fermo e elimino dieci valori iniziali e dieci finali dei dati che ho incontrato fino a quella cella e poi ricomincio. Spero adesso sia più chiaro...
Come traduci in vba l'espressione 'oppure non fare nulla ?
grazie
di Orsero data: 11/05/2013 17:40:32
Ci siamo intesi male...
tu parli di tagliare i caratteri contenuti nella cella io invece lavoro sui valori che ha incontrato scorrendo la colonna fino a quella cella. spero che adesso sia più chiaro.
di Raffaele_53 (utente non iscritto) data: 11/05/2013 18:22:35
Più complesso di cosa pensavo.
Tu intendi dire (che quando trova una cella vuota, automaticamente toglie i valori di 10 righe sopra/sotto e deve farti la "media" su tutti gli altri valori)
Non saprei, vedo cosa posso fare
di Orsero data: 11/05/2013 19:53:34
Perfetto!!! La sub che ho postato però, fa già quello che hai detto. Il problema è che, se tra una cella vuota e l'altra ci sono pochi valori, cerca di togliere i 10 valori iniziali e finali e, non trovandoli, sì blocca. Io vorrei che, nel momento in cui conta un numero < di 30 celle, invece di bloccarsi, le ignori e prosegua a processare la colonna fino al successivo gruppo di celle. Se non è chiaro fammi sapere
di Raffaele_53 (utente non iscritto) data: 12/05/2013 02:26:36
Sono convinto di non aver capito bene, comunque mi sembra che sei in colonna H e parti dalla 3 riga.
Desideri la media dei valori delle 10 righe sotto/sopra???, oppure l'incontrario?

In modo artigianale
...
ultimariga = Cells(Rows.Count, 8).End(xlUp).Row
For x = 3 To ultimariga
If Cells(x, 8) = "" Then
If x - 10 > 3 Then
Cells(x, 9) = Application.Average(Cells(x - 10, 8), Cells(x + 10, 8))
End If
End If
Next
...

fosse l'incontrario
Cells(x, 9) = Application.Average(Cells(3, 8), Cells(x - 10, 8), Cells(x + 10, 8), Cells(ultimariga, 8))

di Raffaele_53 (utente non iscritto) data: 12/05/2013 10:54:07
Altro ritocco...Ex
Celle H3:H12 = 1
Cella H13 = vuota
Celle H14:H23 = 3

MEDIA(H3:H12;H14:H23) = 2

Questo codice appena trova una cella vuota, fà una verifica iniziale (se la cella - 10 è uguale o maggiore di 3). Se vero scrive a fianco in colonna I,la Media(tenendo valide solo 10 righe sotto e sopra.

La faccenda si ingarbuia molto, se oltre a verificare il meno-10 iniziale, dovesse tenere conto anche delle celle vuote e la fine della colonna.
Intendo dire come da esempio sopra se adesso anche la cella H16 fosse Vuota.
Come si dovrebbe comportare alla riga 13?
Fà lo stesso la media anche se la cella H16 è vuota? Oppure essendoci sotto solo due righe con valori e la terza-vuota, non esegue nulla?

...
ultimariga = Cells(Rows.Count, 8).End(xlUp).Row
For x = 3 To ultimariga
If Cells(x, 8) = "" Then
If x - 10 >= 3 Then
Cells(x, 9) = Application.Average(Range(Cells(x - 10, 8), Cells(x + 10, 8)))
End If
End If
Next
...

di Orsero data: 12/05/2013 11:57:21
Non so come ma non credo di essere riuscito a spiegarmi bene.
Il codice del mio primo post esegue già la media delle celle comprese tra le righe vuote della colonna quindi, di questo, non bisogna preoccuparsene.
Ti scrivo in italiano il ciclo che mi serve in VBA cosi spero che ci capiamo:

'Se le celle trovate sono < di 30 non fare niente
'altrimenti esegui il codice del mio primo post

lascia stare la media e tutto il resto, a quello ci pensa già il mio codice.
grazie e buona domenica

di Orsero data: 12/05/2013 13:36:25
Aggiungo il codice con i commenti del ciclo If che vorrei eseguire per capire come fare e se si può fare

Sub MediaFiltrata2()
'Esegue la media sulla colonna eliminando i primi e gli ultimi dieci valori

  Dim ws As Worksheet
  Dim rng As Range
  Dim cella As Range
  Dim nCol As Long
  Dim nRow As Long
    
  Set ws = ActiveSheet
 
  With ws
    Set rng = Intersect(.Columns(8), .Cells(1, 1).SpecialCells(xlCellTypeFormulas, 1))
    nRow = 3
    For Each cella In rng.Areas
    
    If cella > 30 Then 'Vorrei che eseguisse il seguente codice solo se il range trovato è maggiore di 30 celle
      Set cella = cella.Resize(cella.Rows.Count - 20).Offset(10)
      .Cells(nRow, 10).Value = Application.Average(cella)
      nRow = nRow + 1
    Else 'Se il range trovato è < di 30 non fare nulla
    
    End If 'fine dell'If
    
    Next
  End With
 
  Set ws = Nothing
  Set rng = Nothing

End Sub

di Raffaele_53 (utente non iscritto) data: 12/05/2013 16:31:23
Io non ho potuto vedere il Tuo allegato

Di sicuro non ho ancora capito il tutto, provo a fare un'altro tentativo.
>>>'Se le celle trovate sono < di 30 non fare niente

Usando For Each cella In rng.Areas, non ci riesco.
Posso contare quante celle piene ci sono tra due celle vuote e fargli fare un qualcosa...., in questo modo.
Questa conta le celle vuote in tutta la colonnna,partendo da H3.
Dopo inizia un For per quante celle vuote ci sono
Alla prima cella vuota, fa una verifica se sono uguali/+ di 30
Se Si, scrive nella cella adiacente della colonna I la media*
Se No, non fà nulla
* = la riga in verde fa la Media togliendo le prime 10 righe e ultime 10 righe a quelle famose 30 righe trovate o superiori.

Sub trovaVuote2()
Dim Area1 As Range, Area2 As Range
Dim Vuota1, Vuota2, QuanteVuote, Riga1, Riga2, Righe
Vuota1 = 2
Riga1 = Vuota1 + 1
ultimariga = Cells(Rows.Count, 8).End(xlUp).Row
Set Area1 = Range(Cells(Riga1, 8), Cells(ultimariga + 1, 8))
QuanteVuote = Application.WorksheetFunction.CountIf(Area1, "")
For Y = 1 To QuanteVuote
Set Area1 = Range(Cells(Riga1, 8), Cells(ultimariga + 1, 8))
Set Vuota2 = Area1.Find("", LookIn:=xlValues, LookAt:=xlWhole)
If Vuota2 Is Nothing Then
Else
Riga2 = Vuota2.Row - 1
Righe = Riga2 - Riga1 + 1
If Righe >= 30 Then
MsgBox ("Trovati " & Righe & " righe di dati dalla " & Riga1 & " alla " & Riga2)
Cells(Riga2 + 1, 9) = Application.Average(Range(Cells(Riga1, 8), Cells(Riga2, 8)))
'Cells(Riga2 + 1, 9) = Application.Average(Range(Cells(Riga1 +10, 8), Cells(Riga2 -10, 8)))
End If
End If
Riga1 = Riga2 + 2
Next Y
End Sub

di Raffaele_53 (utente non iscritto) data: 12/05/2013 17:20:15
>>>se non esiste la possibilità di restringere il range

Potresti fare....
se uguale 0 maggiore di 30 scrivi in colonna I (senza i 10 d'inizio e fine)
se inferiore, scrivi in colonna J (di tutti i valori trovati)

If Righe >= 30 Then
Cells(Riga2 + 1, 9) = Application.Average(Range(Cells(Riga1 +10, 8), Cells(Riga2 -10, 8)))
else
Cells(Riga2 + 1, 10) = Application.Average(Range(Cells(Riga1, 8), Cells(Riga2, 8)))
End If

di Orsero data: 13/05/2013 09:44:24
Ciao e, innanzitutto, grazie per le risposte e per il tempo dedicatomi.
Forse il tuo codice cosi come modificato potrebbe andare ma non riesco a fargli scrivere i diversi valori di media in sequenza a partire dalla cella (3,11) uno per ogni riga della colonna 11. il codice l'ho riscritto cosi e ho allegato il file come richiesto dato che non riesci a visualizzarlo con il precedente link.
Confido in una tua risposta. grazie ancora

Sub MediaRange()
Dim Area1 As Range, Area2 As Range
Dim Vuota1, Vuota2, QuanteVuote, Riga1, Riga2, Righe
Dim nRow
Vuota1 = 1
Riga1 = Vuota1 + 2
ultimariga = Cells(Rows.Count, 8).End(xlUp).Row
Set Area1 = Range(Cells(Riga1, 8), Cells(ultimariga + 1, 8))
'QuanteVuote = Application.WorksheetFunction.CountIf(Area1, "")
For Y = 3 To ultimariga
Set Area1 = Range(Cells(Riga1, 8), Cells(ultimariga + 1, 8))
Set Vuota2 = Area1.Find("", LookIn:=xlValues, LookAt:=xlWhole)
If Vuota2 Is Nothing Then
Else
Riga2 = Vuota2.Row - 1
Righe = Riga2 - Riga1 + 1
nRow = 3
If Righe >= 30 Then
'Cells(nRow, 11) = Application.Average(Range(Cells(Riga1, 8), Cells(Riga2, 8)))
Cells(nRow, 11) = Application.Average(Range(Cells(Riga1 + 10, 8), Cells(Riga2 - 10, 8)))
nRow = nRow + 1
End If
End If
Riga1 = Riga2 + 2
Next Y
End Sub

di Raffaele_53 (utente non iscritto) data: 13/05/2013 14:58:37
Avevo capito che trovando un range di 30 celle piene, dovesse tagliare dalla 1° alla 10° e dalla 21° alla 30° e fare una media delle celle dalla 11° alla 20°.
Questo il codice lo fà già perche scrive alla 31° riga la media delle righe 11°-20°.

Leggendo, Tu desideri che ogni riga abbia la SUA media. Mà devo capire di che cosa??? Forse....Exempio
1° riga non potrà far nulla (mancano 10 righe prima),Idem dalla 2° alla 10°
11° riga invece (visto che ci sono 10 righe precedenti), può intervenire mà cosa??? Forse la media delle stesse 10 righe precedenti e le 10 successive?
Significa che alla riga11 avremmo la media dalla 1° alla 21°
Significa che alla riga12 avremmo la media dalla 2° alla 22°
Significa che alla riga13 avremmo la media dalla 3° alla 23°
ecc ecc .....
Significa che alla riga21 non potrà fare più nulla, perchè non ci sono altre 10 righe dopo? Giusto così?

Il codice è terra-terra, sicuramente da migliorare.
Casomai sposta in questo forum un allegato, NON il Tuo, solo un esempio di 100 righe e per esempio metti in una colonna a fianco i dati che vorresti ottenere.

Ps. Le cosidette 30 righe, 10 prima e 10 dopo, sono modificabili.
Pps. A questo punto per restringere il range, esisterebbe ad una sola condizione.
Si potrebbe scrivere nella colonna K, la media di questi valori.
La 1° farebbe la media dalla 1° alla 11°
La 2° farebbe la media dalla 1° alla 12°
La 3° farebbe la media dalla 1° alla 13°
ecc ecc sino alla 10°, dopo inteviene in colonna J,
Per ritornare dopo a scriver in colnna K (naturale che sarà così...)
La 21° farebbe la media dalla 11° alla 30°
La 22° farebbe la media dalla 12° alla 30°
La 23° farebbe la media dalla 13° alla 30°
ecc ecc

Sub trovaVuote3()
Dim Area1 As Range, Area2 As Range, Area3 As Range, Area4 As Range, Area5 As Range, cella As Range
Dim Vuota1, Vuota2, QuanteVuote, Riga1, Riga2, Righe, riga
Vuota1 = 2
Riga1 = Vuota1 + 1
ultimariga = Cells(Rows.Count, 8).End(xlUp).Row
Set Area1 = Range(Cells(Riga1, 8), Cells(ultimariga, 8))
QuanteVuote = Application.WorksheetFunction.CountIf(Area1, "")
For Y = 1 To QuanteVuote
Set Area1 = Range(Cells(Riga1, 8), Cells(ultimariga + 1, 8))
Set Vuota2 = Area1.Find("", LookIn:=xlValues, LookAt:=xlWhole)
If Vuota2 Is Nothing Then
Else
Riga2 = Vuota2.Row - 1
Righe = Riga2 - Riga1 + 1
If Righe >= 30 Then
Set Area3 = Range(Cells(Riga1, 8), Cells(Riga1 + 9, 8))
Set Area4 = Range(Cells(Riga1 + 10, 8), Cells(Riga2 - 10, 8))
Set Area5 = Range(Cells(Riga2 - 9, 8), Cells(Riga2, 8))
For Each cella In Area3
cella.Activate
riga = Selection.Row
Cells(riga, 11).Value = Application.Average(Range(Cells(Riga1, 8), Cells(riga + 10, 8)))
Next cella
For Each cella In Area4
cella.Activate
riga = Selection.Row
Cells(riga, 10).Value = Application.Average(Range(Cells(riga - 10, 8), Cells(riga + 10, 8)))
Next cella
For Each cella In Area5
cella.Activate
riga = Selection.Row
Cells(riga, 11).Value = Application.Average(Range(Cells(riga - 10, 8), Cells(Riga2, 8)))
Next cella
End If
End If
Riga1 = Riga2 + 2
Next Y
MsgBox ("Fatto")
End Sub

di Raffaele_53 (utente non iscritto) data: 13/05/2013 15:09:04
For Y = 1 To QuanteVuote + 1
di Raffaele_53 (utente non iscritto) data: 13/05/2013 15:11:21
Riga1 = Riga2 + 1
di Raffaele_53 (utente non iscritto) data: 13/05/2013 15:40:53
Ho visto il files.
>>>MediaFiltrata, si d'accordo mà di che cosa?
Vedo solo una formula che prende un valore 60 righe dopo e sottrae il valore della riga stessa, dicendo che se è inferiore a xxx, faccia questo è altro.

Mi devi spiegare bene cosa intendi quandi scrivi tagliare i 10 valori sopra e sotto.
Ripeto i valori 30,10,10 sono cambiabili, anzi il
If Righe >= 30 Then' si potrebbe mettere a >=11

di Orsero data: 13/05/2013 17:10:45
Grazie di tutto ma noto che io ho la tendenza a non sapermi spiegare e tu la tendenza a complicare le cose.
Il tuo codice andava benissimo ma scriveva la media trovata nella colonna accanto e alla prima riga vuota. tutto quello che volevo era che scrivesse la stessa cosa ma in una riga di seguito all'altra della colonna adiacente.
In ogni caso ho trovato la soluzione al problema modificando il codice che ho postato la prima volta.
Inserendo il seguente ciclo if, come mi hanno suggerito, ho ottenuto ciò che volevo:
If cella.Rows.Count > 30 Then
Set cella = cella.Resize(cella.Rows.Count - 20).Offset(10)
.Cells(nRow, 10).Value = Application.Average(cella) ' media da riga 3
nRow = nRow + 1
Else
End If
Il codice completo è dunque il seguente.

Con un po' di tempo cercherò di fare sul tuo codice la modifica che dicevo cosi ci capiremo su quello che intendevo.
In ogni caso grazie per la pazienza e la disponibilità e a buon rendere.

Sub MediaFiltrata()
'Esegue la media sulla colonna eliminando i primi e gli ultimi dieci valori

  Dim ws As Worksheet
  Dim rng As Range
  Dim cella As Range
  Dim nCol As Long
  Dim nRow As Long
  
  Set ws = ActiveSheet
 
  With ws
    Set rng = Intersect(.Columns(8), .Cells(1, 1).SpecialCells(xlCellTypeFormulas, 1))
    nRow = 3
    For Each cella In rng.Areas
    If cella.Rows.Count > 30 Then
       Set cella = cella.Resize(cella.Rows.Count - 20).Offset(10)
      .Cells(nRow, 10).Value = Application.Average(cella)  ' media da riga 3
      nRow = nRow + 1
    Else
    End If
    Next
  End With
 
  Set ws = Nothing
  Set rng = Nothing

End Sub

Vuoi Approfondire?