[VBA Excel] Filtrare e mediare una lista di dati

[F@bio]

Ciao a tutti,
sto usando VBA per excel e sto scrivendo un programmino per trattare dei dati che mi arrivano da una macchina di prova.
I dati sono scritti del tipo riportato sotto e ogni dato è in una cella (le lineette sono una cella vuota!)
A ----- B
0.0 ----- 0.0
0.1 ----- 100
0.1 ----- 102
0.1 ----- 106
0.2 ----- 110
0.3 ----- 115
0.5 ----- 130
0.5 ----- 132
0.6 ----- 135

Quello che vorrei fare, ma non riesco a trovare un modo per farlo, è fare la media dei dati della colonna di destra riportando quindi un solo valore della colonna di sinistra, quindi, nel caso della lista di cui sopra vorrei avere:
A ----- B
0.0 ----- 0.0
0.1 ----- 104
0.2 ----- 110
0.3 ----- 115
0.5 ----- 131
0.6 ----- 135

Ho scritto tutto l'import dei dati e la trattazione dei file (calcolate che l'operazione va fatta per circ 100 file di cui ognno ha circa 2000 righe!, quindi non posso farlo a mano!).

Grazie mille a tutti coloro che vorranno aiutarm o discutere con me il problema.

Fabio

[gibra]

La fase cruciale è quella dell'importazione dei dati, ed è in quel punto che (almeno in teoria) si dovrebbe modificare il codice per calcolare le medie.

Ma se non vediamo il codice che usi, che aiuto possiamo darti?

[F@bio]

Ciao,
hai ragione, pensavo che bastasse un ragionamento generico, comunque metto sotto tutto il codice, partendo dall'importazione dei file fino all'inizio della parte di calcolo.
Personalmente nn credo sia molto utile per la soluzione del problema, comunque ecco il codice (ovviamente in vba per excel) in 2 parti perchè è troppo lungo:

PARTE!
================================================== =======
Private Sub CommandButton1_Click()

'Defizione delle variabili e loro iniziazione
Dim Dir As String
Dim FileName As String
Dim DirName As String
Dim CompDirFile As String

Dim NumFile As Integer 'conta il numero di file da importare
Sheets("Lista_file_da_importare").Select
NumFile = ActiveSheet.Cells(1, 2).End(xlDown).Row 'Con la funzionalità END si trova l'ultima cella con valori

Dim i As Integer 'valore che scorre i vari nome dei file da importare

Dim Rig As Integer 'Variabile usata per contare le righe su cui fare la trasformazione e i vari conti
Dim c As Integer 'Variabile usata per scorrere le celle su cui fare i conti (arriverà fino ad un valore pari a R)
Dim c1 As Integer ' Cella istantanea su cui eseguo il conto

Dim tup As Double 'Tratto utile provino
tup = Sheets("Dati_provino").Cells(1, 2)

Dim sez As Double 'sezione provino
sez = Sheets("Dati_provino").Cells(4, 2)

Dim deltaL As Double 'delta L calcolato dalla macchina di prova

Dim F As Double 'Forza registrata dalla macchina di prova

Dim epselonENG As Double 'allungamento ingegneristico
Dim sigmaENG As Double 'sigma ingegneristico
Dim sigmaTRUE As Double 'sigma reale
Dim epselonTRUE As Double 'epselon reale
Dim epselon002 As Double 'variabile che definisce l'allungamento allo 0.2% e serve per calcolare E
Dim sigma002 As Double 'variabile che definisce lo sforzo quando l'allungamento è 0.2% e serve per calcolare E
Dim E As Double 'modulo elastico
Dim A As Double 'variabile che serve per il calcolo di epselonENG

Dim prima As Integer 'variabile che serve per togliere evidenziatore durante identificazione all. 0.2%


For i = 2 To NumFile
FileName = Sheets("Lista_file_da_importare").Cells(i, 3)
DirName = Sheets("Lista_file_da_importare").Cells(i, 2)

CompDirFile = DirName & "/" & FileName

'' IMPORTAZIONE E TRATTAMENTO DATI

'Seleziono il file da importare e creo il suo foglio nel'importazione
Sheets("Lista_file_da_importare").Select
Sheets.Add.Name = FileName
ActiveSheet.Move After:=Sheets("Lista_file_da_importare")
ActiveSheet.Cells(1, 1).Select

'Operazioni di copiatura dei dati
ChDir DirName
Workbooks.OpenText FileName:=CompDirFile _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True

ActiveSheet.Columns("A:B").Select
Selection.Copy
Windows("Importazione_automatica_dati.xls").Activa te
ActiveSheet.Paste

ActiveSheet.Rows("1:1").Select
Selection.Insert Shift:=xlDown

ActiveSheet.Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "F [N]"
ActiveSheet.Range("B1").Select

'Parte che serve per scrivere le intestazioni dell righe con l'alfabeto greco
ActiveCell.FormulaR1C1 = "DL [mm]"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=2, Length:=3).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=6, Length:=2).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

ActiveSheet.Range("C1").Select
ActiveCell.FormulaR1C1 = "DL[mm]"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=2, Length:=6).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

ActiveSheet.Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]/1000"
ActiveSheet.Range("C2").Select

'Variabile per avere la stessa formula fino all'ultima riga
Rig = ActiveSheet.Cells(1, 2).End(xlDown).Row

'Calcolo variabili ingegneristiche
For c = 2 To Rig
c1 = ActiveSheet.Cells(c, 2) 'variabile che definisce il valore in microm
ActiveSheet.Cells(c, 3) = c1 / 1000 'trasformazione da microm a mm

'Calcolo del DeltaL e epselon ingegneristica
deltaL = ActiveSheet.Cells(c, 3)
epselonENG = deltaL / tup
ActiveSheet.Cells(c, 5) = epselonENG

'Calcolo sigma ingegneristica
F = ActiveSheet.Cells(c, 1)
sigmaENG = F / sez
ActiveSheet.Cells(c, 6) = sigmaENG

' Ciclo per il calcolo dell'allungamento allo 0.2% e del modulo elastico
If epselonENG <= 0.002 And epselonENG > 0 Then
epselon002 = epselonENG
sigma002 = sigmaENG
E = sigma002 / epselon002

'tolgo evidenziatore a cella precedente
prima = c - 1
ActiveSheet.Cells(prima, 6).Select
With Selection.Interior
.ColorIndex = xlNone
End With

ActiveSheet.Cells(prima, 5).Select
With Selection.Interior
.ColorIndex = xlNone
End With

'evidenzio cella attuale
ActiveSheet.Cells(c, 6).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

ActiveSheet.Cells(c, 5).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End If

Next c

'Calcolo variabili Reali come richiesto dal file input di Abaqus
For c = 2 To Rig

'Calcolo sigma TRUE
sigmaTRUE = ActiveSheet.Cells(c, 6) * (1 + epselonENG)
ActiveSheet.Cells(c, 8) = sigmaTRUE

'Calcolo epselon TRUE
'Le prime due righe di seguito usano la formula corretta, ma poichè si hanno valori
'negativi, di comune accordo con l'assistenza di abaqus abbiamo tolto il termine sigma / E

'epselonTRUE = A - (sigmaTRUE / E)
A = WorksheetFunction.Ln(1 + ActiveSheet.Cells(c, 5))
epselonTRUE = A
ActiveSheet.Cells(c, 7) = epselonTRUE

Next c


'Inserisco le righe e le colonne che spostano tutto il foglio in modo da poter scrivere le varie
'variabili di interesse
ActiveSheet.Columns("A:A").Select
Selection.Insert Shift:=xlToRight
ActiveSheet.Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown

[F@bio]

PARTE 2:


' Cerazione intestazioni per le righe sigma - epselon ingegneristiche
ActiveSheet.Range("F12").Select
ActiveCell.FormulaR1C1 = "e [mm/mm]"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=2, Length:=5).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Range("G12").Select
ActiveCell.FormulaR1C1 = "s [Mpa]"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=2, Length:=6).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Range("F11:G11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "s - e eng"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=2, Length:=3).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=6, Length:=4).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

' Cerazione intestazioni per le righe sigma - epselon reali
ActiveSheet.Range("H12").Select
ActiveCell.FormulaR1C1 = "e [mm/mm]"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=2, Length:=5).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Range("I12").Select
ActiveCell.FormulaR1C1 = "s [Mpa]"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=2, Length:=6).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveSheet.Range("H11:I11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveCell.FormulaR1C1 = "s - e true"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=2, Length:=3).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=1).Font
.Name = "Symbol"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=6, Length:=4).Font
.Name = "Arial"
.FontStyle = "Normale"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With



'Dati caratteristici del provino
ActiveSheet.Range("A1").Select
ActiveCell.FormulaR1C1 = "Dati Ricavati dal provino"
ActiveSheet.Range("A2").Select
ActiveCell.FormulaR1C1 = "Modulo di Young [MPa]"
ActiveSheet.Range("B2").Select
ActiveCell.FormulaR1C1 = E

'Chiusura del file di testo aperto per la copia dei dati
ActiveWindow.ActivateNext
ActiveWindow.Close

Next i

End Sub
================================================== =====

Credo sia troppo lungo, ma nn sò come fare a postarlo tutto ....

Ciao e grazie mille ...

Fabio

[gibra]

A 'noi' (del forum) sarebbe bastata la parte di codice in cui 'acquisisci' i dati dal file per capire come lo fai e vedere quindi di trovare una soluzione.

Purtroppo non hai formattato il codice (come da Regolamento!) quindi la lettura del tuo codice è praticamente improponibile.

Ad ogni modo, la cosa che ho visto è che tu invece di importare i dati dal file di testo (come pensavo, dato che nel 1° post hai menzionato importo ), apri direttamente il file in Excel, il che rende praticamente impossibile poter intervenire in fase di acquisizione che, a mio avviso, è l'unica soluzione praticabile.

Caricando direttamente i dati ti troveresti costretto a modificare riga per riga, cella per cella i dati già acquisiti; mi sembra un lavoro immane .

Siccome 2000 righe x 100 file è un lavoro duro, la via che userei è questa:


1. Importo i dati in una tabella Access (SELECT INTO ....)

2. poi li importo in Excel utilizzando una query che mi restituisca i dati raggruppati, per intenderci:

SELECT ColonnaA, Sum(ColonnaB) As ColonnaB
FROM TabellaAccess
GROUP BY colonnaA

3. infine procedo alla formattazione del font, colori, ecc... (ma questa parte l'hai già)

[F@bio]

Ciao,
scusa se sfrutto la tua gentilezza, come faresti tu ad importare dei dati?
Io da autodidatta ho trovato la modalita che ho esposto sopra (apro il file di test ocon excel, copio i dati nel mio foglio e chiudo il foglio con i dati), ma se ci fosse qualcosa di meglio sono anche disponibile a buttare tutto nel cestino e rifare il codice ...
I file che ottengo dalla macchina hanno un separatore ( , ) tra i dati di interesse.
Mi daresti qualche dritta su come ricominciare e poter fare questo lavoro?

Grazie mille,
Fabio

P.S.: come si fa ad inserire il codice? io ho provato cliccando su CODE ma mi sembrava non funzionasse ....

[gibra]

Per importare un file in una tabella Access :
ImportText.exe Importing Text into Access with ADO/RDO/DAO/Filesys/Automation (August 5, 2004)
http://support.microsoft.com/kb/230265/en-us
Ti raccomando di scegliere l'esempio per ADO


Ti raccomando di usare un file SCHEMA.INI così da poter definire i campi (nome, tipo e dimensione) della tabella nel formato desiderato:
ACC: How to Use Schema.ini for Accessing Text Data (January 19, 2007)
http://support.microsoft.com/kb/149090/EN-US/

Eventualmente, il file SCHEMA.INI può anche essere creato al volo
ACC: How to Create a Schema.ini File Programmatically (January 19, 2007)
http://support.microsoft.com/kb/155512/EN-US/

Tieni presente che se non usi lo SCHEMA.INI ti troverai tutti i campi di tipo TEXT a 255, il che non è proprio una bella cosa perchè poi ti crea problemi quando dovrai esportare la tabella da Access in Excel.


Trasferire tabella da Acces ad Excel
Vi sono 2 metodi:

Il primo ESPORTA da Access a Excel usando una connessione ADODB.Connection :

Dim CN As ADODB.Connection
Set CN = New ADODB.Connection
CN.CursorLocation = adUseClient
CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPercorsoCompletoFileMDB
CN.Execute "SELECT * INTO [Excel 8.0;Database=c:\tmp\file.xls].[Foglio1] FROM Clienti"
CN.Close
Set CN = Nothing

Considera che nel FROM, al posto del nome della tabella (Clienti), puoi usare una tua query (che ti accennavo nel precedente post) per cui
Dim sSQL As String
sSQL = "SELECT ..... FROM ... GROUP BY ...."

CN.Execute "SELECT * INTO [Excel 8.0;Database=c:\tmp\file.xls].[Foglio1] FROM " & sSQL

Il secondo IMPORTA in Excel da Access usando il metodo di Excel CopyFromRecordset (solo con recordset di tipo ADO!)
http://msdn.microsoft.com/en-us/library/aa165427.aspx

io preferisco il primo.



P.S.
per formattare il codice con il tag CODE:
- seleziona il codice
- clic sul pulsantino [CODE] nella finestra di modifica del messaggio.

[F@bio]

Ciao,
grazie mille per la risposta, ho letto i primi articoli e mi si apre un mondo nuovo ...
Devo dire che non conoscendo per nulla access (mi sono sempre arrangiato con excel) ho un pò il timore di utilizzarlo poichè non vorrei imbarcarmi in un'avventura epica ...
La mia domanda è: ma se anche io importassi i dati da Access come indicatomi nelle indicazioni poi sposteri il problema del filtraggio e delle medie in query di access, ma avrei sempre il problema di scegliere i dati e mediare i valori associati no?
Non potrebbe essere un'idea usare uno o più cicli if? (purtroppo non ho ben chiara la struttura da usare ...)

Comunque proverò con i link ke mi hai dato.

Grazie ancora,
Fabio

[gibra]

Originariamente inviato da F@bio
Ciao,
grazie mille per la risposta, ho letto i primi articoli e mi si apre un mondo nuovo ...
Devo dire che non conoscendo per nulla access (mi sono sempre arrangiato con excel) ho un pò il timore di utilizzarlo poichè non vorrei imbarcarmi in un'avventura epica ...

E' ovvio, qualsiasi cosa nuova spaventa.
Ma se ti ci metti un po' (non è così difficile come sembra) vedrai che la soddisfazione che ne otterrai è impagabile.
Soprattutto perchè il codice da usare è veramente pochissimo!

Originariamente inviato da F@bio
La mia domanda è: ma se anche io importassi i dati da Access come indicatomi nelle indicazioni poi sposteri il problema del filtraggio e delle medie in query di access, ma avrei sempre il problema di scegliere i dati e mediare i valori associati no?

Perchè parli di "filtraggio"?
La tua domanda non mi è chiara, ma credo che tu no abbia ancora ben compreso come funziona. E' normale.
Prima 'rompiti la testa', caso mai 'te la fasci' dopo...

Originariamente inviato da F@bio
Non potrebbe essere un'idea usare uno o più cicli if? (purtroppo non ho ben chiara la struttura da usare ...)

Assolutamente no.
Guarda che entrambe le operazioni sono abbastanza banali.

Intanto pensa a realizzare il primo passo: importare i dati in Access.
Per il secondo passo ti ho già dato, più o meno, la query da usare.

Intanto puoi provare l'importazione senza usare il file SCHEMA.INI, così vedi come funziona la cosa.
Poi aggiungiamo lo SCHEMA.INI per impostare correttamente i tipi di campi.

Sarebbe utile se riesci a postare una decina di righe del file di testo che devi importare, così anche noi sappiamo di cosa hai per le mani, e soprattutto di come è la formattazione dei dati.