Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1612to1616
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Beschäftigungstherapie -.-

Beschäftigungstherapie -.-
16.03.2018 07:50:39
Burak
Guten Morgen meine Lieben,
also ich habe für die letzten zwei Wochen des Praktikums noch eine Aufgabe bekommen, da wir das Hauptprojekt frühzeitig abgeschlossen haben.
Funktionstechnisch habe ich alles hinbekommen (scheinbar habe ich dank euch echt ne Menge über vba gelernt! :*), aber bei der immensen Masse an Daten, sogar auf schnelleren Rechnern schwer ertragbar.
Es geht um den Import von Daten aus ner .data file. Und danach noch per Sverweis Informationen hinzufügen und eine Spalte umformatieren.
Das Problem ist, dass in der data-file dermaßen viele Daten sind, dass
1. diese Datei schon 1,41 GB groß ist
2. es die maximale Anzahl an Zeilen in Excel überschreitet (1.048.271 oder so)
3. der Import an sich vllt noch vertretbar wäre aber mit dem Sverweis und der Umformatierung es viel zu lange dauert.
Der Code:

Sub dataimport()
'Deklaration der Variablen
Dim ws As Worksheet, importdatei, Loletzte As Long, i As Long, k As Long
'Pfadermittlung für den Import
importdatei = Application.GetOpenFilename
'Abbruchfunktion
If importdatei = "Falsch" Then Exit Sub
'Makrobremsen lösen
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = False
End With
Set ws = ActiveWorkbook.Sheets("Rohdaten")
With ws
If .AutoFilterMode Then .Rows("1:1").AutoFilter
.Cells.Clear
End With
'Dateipfad und Ziel für Import
With ws.QueryTables.Add(Connection:="TEXT;" & importdatei, Destination:=ws.Range("A2"))
'Import als Textformatierung
.TextFileParseType = xlDelimited
'Spaltentrennung in Quelldatei per Komma
.TextFileSemicolonDelimiter = True
'Aktualisieren der externen Datenverbindung
.Refresh
End With
With ws
.Range("A1").Resize(, 14) = Array("Maschine", "Prüfplan", "Zeitstempel", "Barcode", "Bauteil", _
"LIBName", "Analysetyp", "w", "Fenster", "PIN", "Feat", "Wert", "Win", "Beschreibung")
Loletzte = .Cells(Rows.Count, 1).End(xlUp).Row
If Loletzte 
Beispieldatei:
https://www.herber.de/bbs/user/120452.xlsm
Die zu importierende Datei ist nicht mitanbei, da das Öffnen der Datei, den PC überlastet :D
Die Tatsache, dass es die maximale Anzahl an Zeilen überschreitet und daher nicht alles importiert ist zwar suboptimal, aber da muss bei der Lösungsfindung nicht die Priorität liegen. Hauptsache es geht schneller :D
Ich würde mich auch über kleine Erklärungen freuen, wieso bzw was an eurer Lösung performanter ist, damit ich nicht bei jeder Optimierung euch einbinden muss.
Freundliche Grüße und vielen lieben Dank

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Beschäftigungstherapie -.-
16.03.2018 08:28:49
Daniel
Hi
das was du in der DO-Schleife machst, kannst du wesentlich schneller erledigen, in dem du die entsprechenden Formeln in die Zellen schreibst und diese hinterher durch die Werte ersetzt.
hier braucht man sich auch nicht vor hilfsspalten zu scheuen.
Beispiel:
  i = 2
Do Until i = Loletzte + 1
.Cells(i, 7) = .Cells(i, 7).Value & " 13"
i = i + 1
Loop
geht bei großen Datenmengen schneller mit
With Range(Cells(2, 99), Cells(Loletzte, 99))
.FormulaR1C1 = "=RC7&"" 13"""
.Copy
Cells(2, 7).PasteSpecial xlpasteValues
.clearContents
End with
der SVerweis wird bei sehr großen Datenmengen sehr schnell, wenn man
den Quellbereich nach der ersten Spalte sortiert und die Variante mit 4. Parameter = wahr einsetzt.
sollte es vorkommen, dass der Suchbegriff nicht vorkmmt, bekommt man hier keinen Fehler sondern müsste dies zusätzlich prüfen (ist aber kein Problem, der Sverweis ist schnell genug)
=Wenn(SVerweis(Suchwert;Suchmatrix;1;wahr)=Suchwert;SVerweis(Suchwert;Suchmatrix;Spalte;Wahr);"nv") 

Gruß Daniel
Anzeige
AW: Beschäftigungstherapie -.-
16.03.2018 09:30:01
Burak
Gruß und Danke schonmal!
also der erste Part läuft schonmal sehr gut.
Zum SVerweis:
Wenn ich dich richtig verstanden habe bzw es richtig umgesetzt habe, wäre das:

i = 2
Do Until i = Loletzte + 1
If (Application.WorksheetFunction.VLookup(.Cells(i, 11), Sheets(4).Range("A2:D33"), 1, True)) =  _
.Cells(i, 11) Then
.Cells(i, 13).Value = Application.WorksheetFunction.VLookup(.Cells(i, 11), Sheets(4).Range("A2: _
D33"), 2, True)
.Cells(i, 14).Value = Application.WorksheetFunction.VLookup(.Cells(i, 11), Sheets(4).Range("A2: _
D33"), 3, True)
Else
.Cells(i, 13).Value = "nv"
.Cells(i, 14).Value = "nv"
End If
i = i + 1
Loop
Das läuft aber eher langsamer als schneller (habe die Zeit nicht gemessen, aber würde ich so schätzen. Macht ja auch Sinn, da er letzten Endes jetzt 3 Mal statt 2 Mal SVerweis macht.
Wo liegt mein Denkfehler?
Anzeige
AW: Beschäftigungstherapie -.-
16.03.2018 09:35:29
UweD
Hallo
&gt Wenn ich dich richtig verstanden habe bzw es richtig umgesetzt habe, wäre das:
Nein, das genau nicht.
- Anstelle in Schleife einzeln zu berechnen..
- ggf. in einer Hilfsspalte eine geeignete Formel in den gesamten Bereich reinschreiben
- und anschließend die Formelerfegnisse auf einen Schlag in Werte umwandeln
LG UweD
AW: Beschäftigungstherapie -.-
16.03.2018 09:48:56
Burak
Hast natürlich recht. Kam auch grad auf die Idee. Wenn er mir schon die Formel als Excel Formel gibt, dann wahrscheinlich deswegen.
Jedoch habe ich es nicht hinbekommen.

With Range(.Cells(2, 13), .Cells(Loletzte, 13))
.FormulaR1C1 = "=WENN(SVERWEIS(K2;Referenzliste!A2:C33;1;WAHR)=Rohdaten!K2;SVERWEIS( _
Rohdaten!K2;Referenzliste!A2:C33;2;WAHR);""nv"")"
End With
Abgesehen dass da noch paar $ reinmüssen. Aber "Anwendungs- oder objektdefinierter Fehler". :(
Anzeige
AW: Beschäftigungstherapie -.-
16.03.2018 09:51:45
ChrisL
=IF(VLOOKUP(RC[-2],Referenzliste!RC[-12]:R[31]C[-10],1,TRUE) =Rohdaten!RC[-2],VLOOKUP(Rohdaten!RC[-2],Referenzliste!RC[-12]:R[31]C[-10],2,TRUE),"nv")
AW: Beschäftigungstherapie -.-
16.03.2018 10:43:56
Burak
Syntaxfehler.
Egal ob mit "" drumherum oder ohne. Auch in Kombination mit & geht es nicht.

With ws
With Range(.Cells(2, 13), .Cells(Loletzte, 13))
.FormulaR1C1 = "=IF(VLOOKUP(RC[-2],Referenzliste!RC[-12]:R[31]C[-10],1,TRUE) =Rohdaten!RC[- _
2],VLOOKUP(Rohdaten!RC[-2],Referenzliste!RC[-12]:R[31]C[-10],2,TRUE),""nv"")"
End With
End With
Sorry, habe mit Formeln per VBA einfügen echt 0 Erfahrung :(
Anzeige
AW: Beschäftigungstherapie -.-
16.03.2018 11:11:23
ChrisL
Wenn du die Zeilenschaltung und _ entfernst funktioniert es...
Sub t()
Dim Loletzte As Long: Loletzte = 20
With Worksheets("Rohdaten")
With Range(.Cells(2, 13), .Cells(Loletzte, 13))
.FormulaR1C1 = "=IF(VLOOKUP(RC[-2],Referenzliste!RC[-12]:R[31]C[-10],1,TRUE)=Rohdaten!RC[-2] _
,VLOOKUP(Rohdaten!RC[-2],Referenzliste!RC[-12]:R[31]C[-10],2,TRUE),""nv"")"
End With
End With
End Sub

entweder...
16.03.2018 11:01:43
Werner
Hallo Burak,
..du nimmst FormulaR1C1 und dann aber die Formel von ChrisL
oder du nimmst die deutsche Schreibweise, so wie deine gepostete Formel, dann aber bitte nicht FormulaR1C1 sondern FormulaLocal
Übrigens habe ich dir zu deinem Post bzgl. der Ermittlung der letzten Zeile was geschrieben - scheint dich aber nicht mehr zu interessieren.
Gruß Werner
Anzeige
AW: entweder...
16.03.2018 11:09:46
Burak
Ok habe es in soweit fast geschafft mit der Formel von ChrisL, bis darauf, dass die Suchmatrix sich auch verändert im Laufe der "Schleife". Also in M2 geht es noch mit A2:C36 (übersetzt) aber in M3 steht dann A3:C37 usw.
Wie und wo kann ich das $ aus der Excel Formel in VBA mit einbinden?
Ich weiß leider nicht mehr welchen Post genau du meinst (Ermittlung letzte Zeile und die Suchfunktion "Ermittlung" findet nichts :D
Aber in den meisten Fällen lese ich alles zu meinen Topics, aber kann sein dass ich etwas übersehen habe.
Wenn es hier so sein sollte, bitte entschuldige.
Fertig! Gelöst! DANKE AN ALLE!!!! und sry! o.w.T
16.03.2018 11:18:02
Burak
Gerne u. Danke für die Rückmeldung. o.w.T.
16.03.2018 11:26:56
Werner
AW: Beschäftigungstherapie -.-
16.03.2018 10:46:11
Daniel
Hi
du hast 4 verschiedene Möglichkeiten, eine Formel in eine Zelle zu schreiben:
- .Formula: Formel muss in englisch geschrieben werden mit A1-Zellbezügen
- .FormulaLocal: Formel muss landestypisch geschrieben werden mit A1-Zellbezügen
- .FormulaR1C1: Formel muss in englisch geschrieben werden, mit R1C1-Zellbezügen
- .FormulaR1C1Local: Formel muss landestypisch geschrieben werden mit R1C1-Zellbezügen
englisch heißt:
- englische Funktionsnamen
- Komma als Parametertrennzeichen
- Punkt als Dezimalzeichen
landestypisch heißt mit einem deutschen Excel:
- deutsche Funktionsnamen
- Semikolon als Parametertrennzeichen
- Komma als Dezimalzeichen
Anführungszeichen, die zur Formel gehören, müssen immer gedoppelt werden, damit der Compiler sie von den Anführungszeichen unterscheiden kann, die den Teiltext begrenzen.
am einfachsten ist, wenn du die Formel funktionsfähig in eine Zelle schreibst und dir den Formeltext im Direktfenster mit
?Replace(Selection.Formula..., """", """"")

in der gewünschten Formula-Variante gleich mit gedoppelten Anführungszeichen anzeigen lässt.
dann kannst du die Formel von dort einfach in den Code kopieren.
Gruß Daniel
Anzeige
AW: Beschäftigungstherapie -.-
16.03.2018 08:40:38
ChrisL
Hi
Egal wie du das Makro änderst, mehr Zeilen erhält Excel dadurch nicht. Darum würde ich Access nehmen.
Für den SVERWEIS würde ich eine Abfrage/Query erstellen. SQL-Statement:
SELECT Rohdaten.*, Referenzliste.[Win Nr], Referenzliste.Beschreibung
FROM Rohdaten INNER JOIN Referenzliste ON Rohdaten.Feat = Referenzliste.[Feat Nr];
cu
Chris
PS: Wenn du Rohdaten veränderst, dann sind es nicht mehr Rohdaten ;)
AW: Beschäftigungstherapie -.-
16.03.2018 09:31:38
Burak
Zu den mehr Zellen kam mir nur die Idee, wenn es die maximale Zeilenzahl erreicht, in einem zweiten Tabellenblatt weiter importiert. Aber wie gesagt, das is zweitrangig.
Und was anderes als Excel kommt hier leider nicht in Frage. :(
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige