Hallo ihr Lieben Ich habe eine Excel Tabelle mit etwa 400.000 eintragen und muss aus den Bestimmte Werte finden und diese Werte in ein Diagramm eintragen. Die Tabelle sieht in Etwa so aus: Dokument | Name | Datum | Zeit | Datum + Uhrzeit | Wert ----------------+----------------+---------------+------------+--------- ------------+---------------- Speichersystem | AU-Temp. (°C)| 12.05.20 | 05:14:03 | 12.5.20 5:14 | 2,265553474 Speichersystem | AU-Temp. (°C)| 12.05.20 | 06:05:17 | 12.5.20 6:05 | 2,774776936 Speichersystem | AU-Temp. (°C)| 12.05.20 | 06:29:32 | 12.5.20 6:29 | 3,278880119 Speichersystem | AU-Temp. (°C)| 12.05.20 | 06:46:34 | 12.5.20 6:46 | 3,780426502 Die Spalten "Dokument" und "Name" haben Filter. Wenn ich ein bestimmtes Diagramm mir ansehen will dann wähle ich ein Bestimmtes Dokument aus und dann den Namen des wertes. in diesen fall "AU-Temp.". Dann bekomme ich eine Liste mit allen eingräten und erstelle daraus ein Diagramm. Ich möchte nun ein Makro erstellen das Zwei Comboboxen enthält mit den ich auswählen kann, welchen Wert die Filter einnehmen sollen und dann auf ein "Erstellen" Butten klicken und mir das Fertige Diagramm ansehen. Mit diesen Projekt komme ich zum ersten mal mit einen Bedienfeld in Excel in Kontakt und ich habe vorher nur wenig Erfahrung sammeln können, wie ich Makros in Excel programmiere.
Da werfe ich mal als Stichwort Pivot Tabelle rein. Sind am Anfang ungewohnt aber sehr Leistungsfähig. Wenn du das mit einem Makro machen möchtest gibt es im Netz sehr viele Beispiele wie das funzt. Selber würde ich das in SQLite einlesen und dann per ODBC abfragen. Das ist einfacher und auch besser zu handhaben als verteilte Dokumente.
Danke für die Antwort.
Die erstelle Tabelle ist eine Zusammenfassung vieler Dokumente. Die
erste Spalte dient der Orientierung aus welchen Dokument, oder besser
gesagt, aus welcher Tabelle die daneben stehenden Werte kommen.
Ich habe mich im Netz umgesehen und leider kein Code gefunden, der mein
Problem löst, dafür aber eine "Wegbeschreibung".
Zuerst ermittel ich die einzelnen Werte die ich in der Combobox
darstellen möchte und nach der Auswahl übermittel ich das Ergebnis zu
den Filter.
Um die Werte zu bekommen habe ich eine einfache Suchfunktion geschieben,
die auf die Tabelle abgestimmt ist. In den allermeisten Fällen stehen
die gleichen Namen untereinander und die Namen wiederholen sich in der
regel nicht.
Für den häufigsten Fall muss der Computer nur 5 Zeilen abarbeiten. Bei
70.000 Zeilen hat der Relativ Einfache Rechner aber dafür fast eine
Minute gebraucht.
Die Tabelle hat in ihrer vollständigen Fassung 400.000 Datensätze. Grob
Hochgerechnet bräuchte er für das durchsuchen einer Spalte dann 6 Min.
Ich Arbeite in einer Gruppe zusammen und Excel ist der " kleinste
gemeinsame Nenner" auf den wir uns einigen konnten, daher ist es nicht
möglich eine Andere Programmsprache zu nutzen.
Hier ist der Code, denn ich geschrieben habe:
Dim Anzahl As Integer
Anzahl = 0
Dim Namen(100) As String
Dim brauchenNeuenWert As Integer
brauchenNeuenWert = 0
Dim i As Integer
ActiveSheet.Range("B4").Select
Namen(0) = ActiveCell.Value
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
If Namen(Anzahl) <> ActiveCell.Value Then
For i = 0 To Anzahl Step 1
If Namen(i) = ActiveCell.Value Then
brauchenNeuenWert = 0
Exit For
Else
brauchenNeuenWert = 1
End If
Next i
If brauchenNeuenWert = 1 Then
Anzahl = Anzahl + 1
Namen(Anzahl) = ActiveCell.Value
brauchenNeuenWert = 0
End If
End If
Loop
Ich bitte darum, mir ein schnelleren weg zu Zeigen, wie ich an die
Einzelnen Namen komme.
Hallo Rob,
ich bitte Dich, Groß- und Kleinschreibung zu beachten, denn ich habe
eine Nicht-Rechtschreibungsallergie!
Hinweise zu Deinem Code:
1. Ungeschickte Zellabfrage mittels ActiveCell und Verschieben des Fokus
Die Folge davon ist nebenbei, dass Du bei der Suche nach Namen den
Bildschirm zum permanenten Neuzeichnen zwingst.
Das Provisorium besteht darin, ScreenUpdates zwischenzeitlich
auszuschalten mit Application.Screenupdating=False
Schlauer wäre es mit der Cells-Methode auf die Zellen zuzugreifen.
Sauberer wäre auch eine eindeutige Adressierung der Zelle. Anstatt
ActiveSheet eher
Thisworkbook.Sheets("Rob").cells(x,2).value
zum Auslesen der Namen benutzen!
2. Lineare Laufzeit der inneren Schleife
Die Laufzeit Deines Programms liegt in der Klasse O(zeilen*namen), weil
die Namenssuche und das Wiederauffinden nicht effizient geschrieben
wurde.
Wenn die Namen in den Zeilen alle verschieden wären, wäre die Laufzeit
sogar O(zeilen^2). Das ist schlecht!
Du kannst nie besser sein als O(zeilen), aber das Finden eindeutiger
schon gespeicherter Namen geht in O(log2(namen)).
Bei maximal hundert Namen musst Du im schlimmsten Fall 100 Mal die
innere Schleife durchlaufen. Das geht aber auch in
aufgerundet(log2(100))=7 Durchläufen. Dir fehlt übrigens auch die
Überlaufprüfung für Dein Namensfeld.
Entweder programmierst Du dafür etwas ordentliches, was schnell läuft
oder Du bedienst Dich einer Struktur namens Collection, weil die
Verwendung von Schlüsseln bei Collection verhindert, dass Du linear
Deine Namensliste durchlaufen musst um zu gucken, ob der Name schon da
ist!
In die schmeisst Du die gefundenen Namen herein und übergibst den Namen
gleichzeitig als Schlüssel "key" (!!! Wichtig für das schnelle
Wiederauffinden).
Dadurch drückst Du die Laufzeit der inneren Schleife von O(namen) auf
O(log2(namen)).
Hier ein Beispiel, das Dir zeigt, wie Du mit dem Abfangen der
Fehlernummer feststellt, ob der Schlüssel in der Collection vorhanden
ist oder nicht.
Das ist gleichbedeutend damit, dass Du den Namen der Collection
hinzugefügt hast.
https://stackoverflow.com/questions/38007844/generic-way-to-check-if-a-key-is-in-a-collection-in-excel-vba
Am Ende iterierst Du über Collection.item(x) aufwärts bis zu
Collection.Count wenn Du Deine Auswahlbox befüllem musst.
Für Mäkler: O(log2(namen)) = O(log(namen))
Viel Erfolg!
:
Bearbeitet durch User
Rob schrieb: > Ich Arbeite in einer Gruppe zusammen und Excel ist der " kleinste > gemeinsame Nenner" auf den wir uns einigen konnten, daher ist es nicht > möglich eine Andere Programmsprache zu nutzen. Also SQL ist imo ca. 10x schneller erlernt als VBA. Außerdem kann man es immer mal brauchen und die Abfragen funzen auch in der nächsten Release. Wenn's aber Excel sein soll, da gibt es auch db Funktionen. https://www.pc-magazin.de/bildergalerie/excel-datenbank-galerie-1516538-369051.html
Toby P. schrieb: > Wenn's aber Excel sein soll, da gibt es auch db Funktionen. Ja, aber keine passenden Befehle für die Problemstellung. Die Funktionen DBANZAHL DBANZAHL2 DBMAX DBMIN DBSUMME DBMITTELWERT sind für die Fragestellung ungeeignet.
Hey
Ich danke euch beiden für die Bemühung, mir zu Helfen.
Besonders dir, Peter M, möchte ich meinen Dank aussprechen den dein
erster Beitrag war sehr hilfreich für mich.
Allerdings muss ich zugeben, dass es nicht leicht war, deinen Text zu
verstehen. Offenbar benutzen wir sehr unterschiedliche Formulierungen,
um das gleiche Auszudrücken.
Ich Hoffe im groben alles umgesetzt zu haben, worauf du mich aufmerksam
gemacht hast:
Dim Anzahl As Integer
Anzahl = 1
Dim zeile As Long
zeile = 4
Dim Namen As Collection
Set Namen = New Collection
Dim brauchenNeuenWert As Integer
brauchenNeuenWert = 0
Dim i As Integer
ActiveSheet.Range("B4").Select
Namen.Add ActiveCell.Value
Do While ThisWorkbook.Sheets("Daten").Cells(zeile, 2).Value <> ""
If Namen.Item(Anzahl) <> ThisWorkbook.Sheets("Daten").Cells(zeile,
2).Value Then
For i = 1 To Anzahl Step 1
If Namen.Item(i) = ThisWorkbook.Sheets("Daten").Cells(zeile,
2).Value Then
brauchenNeuenWert = 0
Exit For
Else
brauchenNeuenWert = 1
End If
Next i
If brauchenNeuenWert = 1 Then
Anzahl = Anzahl + 1
Namen.Add ThisWorkbook.Sheets("Daten").Cells(zeile, 2).Value
brauchenNeuenWert = 0
End If
End If
zeile = zeile + 1
Loop
For i = 1 To Namen.Count
ComboBox1.AddItem Namen.Item(i)
Next i
Ach so, das Einfrieden des Bildschirmes fehlt noch.
Es läuft aber mittlerweile um ein vielfaches schneller als die erste
Version. Es dauert jetzt etwa 2 Sekunden, bis der Code durchgelaufen
ist. bei 400.000 Zeilen wird es dann wieder länger aber das ist dann
auch noch vertretbar.
@Toby P.: Ich kenne SQL nicht besonders gut. gibt es dort auch die
Möglichkeit, Daten Grafisch darzustellen, ohne ein weiteres
Hilfsprogramm zu benutzen? Das Makro an dem ich gerade schreibe dient
nämlich dazu die gesammelten Daten grafisch darzustellen. Daraus
entwickel ich etwa 15 unterschiedliche Diagramme. Diese ordnet das Makro
auf 8 unterschiedliche Excel Sheets und gestaltet diese Druckfertig.
Zur Rechtschreibung will ich noch mitteilen, das ich Legastheniker bin
und dies das Minimum an Fehlern ist, die ich in einen selbst verfassten
Text leisten kann. Anders als die hier erwähnte Allergie, was nur eine
Umschreibung für Intoleranz ist, habe ich mit meiner Eigenschaft
wirklich jeden Tag zu tun und möchte in diesem Zusammenhang nicht
zusätzlich mit pseudo Argumenten Belastet werden. Es ist in meiner
Wahrnehmung legitim zu erwarten, dass alle Benutzer sich verständlich
und Sauber ausdrücken können. Eine nicht unerhebliche Anzahl der in
Deutschland lebenden Menschen kann es eben aus unterschiedlichen Gründen
nicht und zumindest ich brauche dafür keine Extra Aufmerksamkeit. Ein
einfaches: "Versuch beim nächsten mal mehr auf deine Rechtschreibung zu
achten", genügt in meiner Wahrnehmung. Mehr Toleranz, und dabei ist es
fast egal in welchen Gebiet, tut uns als Gesellschaft sicher gut.
Rob schrieb: > Allerdings muss ich zugeben, dass es nicht leicht war, deinen Text zu > verstehen. Offenbar benutzen wir sehr unterschiedliche Formulierungen, > um das gleiche Auszudrücken. Hier eine anschauliche Quelle zur O-Notation und deren praktischer Nutzung in der realen Welt: http://www.saar.de/~awa/ONotation.html Ich drücke den Sachverhalt mal mit anderen Worten einfacher aus: Die lange Laufzeit Deines Codes wird durch zwei Dinge verursacht: 1. Malen auf den Bildschirm, was durch den alten Code verursacht wird, der dauernd "activecell" neu setzt. 2. Zwei geschachtelte Schleifen Die Anzahl der äußeren Schleifendurchläufe ist unvermeidbar! Die Anzahl der inneren Schleifendurchläufe lässt sich aber massiv reduzieren. Die im Link gezeigte Funktion ermöglicht es Dir, das Vorhandensein des Namens viel schneller abzufragen: Function HasKey(coll As Collection, strKey As String) As Boolean Dim var As Variant On Error Resume Next var = coll(strKey) HasKey = (Err.Number = 0) Err.Clear End Function Anstelle der inneren Schleife fragst Du nur noch "Ist der Schlüssel drin?" Bei FALSCH fügst Du den neuen Namen hinzu, ansonsten musst Du nichts tun. Der Trick besteht darin, dass die Suche nach dem Namen aufgrund der Verwendung des Schlüssels bei "var = coll(strKey)" viel schneller von statten geht, als beim linearen Durchsuchen Deiner Namensliste. Warum? Wenn Du 100 lexikalisch geordnete Namen hast und Du hast einen neuen Namen und willst wissen, ob der in der Liste ist, musst Du nicht die Liste komplett durchlaufen. Es reicht, in der Mitte zu gucken und dann rechts oder links weiterzusuchen. Mit der Methode halbiert sich die Suchmenge in jedem Schritt um die Hälfte. Du brauchst dann schlimmstens etwa 7 Durchläufe um die Frage zu beantworten. Die Verwendung von Schlüsseln "keys" führt dazu, dass Excel für die Verwaltung der Collections solche Methoden benutzt. Du müsstest also die innere Schleife noch verbessern, dann gibt es in meinen Augen keine Möglichkeit mehr, den Code zu verbessern. Zum Thema Rechtschreibung möchte ich Dir folgendes sagen: Viele Beiträge sind einfach nur unterirdisch. Da wird geplenkt, keine Verwendung von Punkt und Komma, alles klein geschrieben, Gedankenfetzen anstelle von ganzen Sätzen, lange Romane ohne Stellung einer Frage etc... Solche Beiträge ignoriere ich einfach, weil ich davon ausgehe, dass der Autor sein Problem sowieso nicht strukturiert lösen kann, weil er es anderen gegenüber nicht strukturiert formulieren kann. Wenn ich mich bemühe, jemanden zu helfen, erwarte ich von demjenigen auch ein bestimmtes Maß an Bemühung. Nebenbei führen Fehler in Schlüsselworten wie hier "Brauche ich bald 4 Steromzähler? Sektorenkopplung?" auch dazu, dass die Suchfunktion bei all denjenigen scheitert, die das Schlüsselwort richtig eingeben.
:
Bearbeitet durch User
Ich danke dir sehr für deine ausführlichen Antworten.
VBA ist für mich eine neue Programmiersprache die ich erst vor einer
Woche "entdeckt" habe. Daher will ich "das neue" in meinen Code erstmal
außerhalb Testen und zu diesen Zweck das kleine Programm zusammen
Kopiert:
Private Sub UseKey()
Dim collMark As New Collection
collMark.Add 45, "Bill"
collMark.Add 67, "Hank"
collMark.Add 12, "AU-Temp. (°C)"
collMark.Add 89, "Betty"
' Print AU-Temp. (°C) marks
Debug.Print collMark(ThisWorkbook.Sheets("Daten").Cells(4, 1).Value)
' Print Bill's marks
Debug.Print collMark("Bill")
End Sub
Hier bekomme ich die Fehlermeldung: Laufzeitfehler '5' - Ungültiger
Prozessaufruf oder ungültiges Argument
Ich vermute ich habe das Argument falsch und ich vermute auch dass ich
anstelle von .Value etwas anderes benutzen solle, doch weiß ich nicht,
wie ich das herausfinden kann, was da sehen soll und / oder anders
gefragt: was soll da sehen, damit es richtig ist?
Rob schrieb: > Daher will ich "das neue" in meinen Code erstmal > außerhalb Testen und zu diesen Zweck das kleine Programm zusammen > Kopiert: Das ist sinnvoll. Rob schrieb: > Hier bekomme ich die Fehlermeldung: Laufzeitfehler '5' - Ungültiger > Prozessaufruf oder ungültiges Argument Benutze den Debugger, z.B. die Einzelschrittfunktion um zu sehen, wo es knallt. Ich habe Dir den Code ein bisschen kommentiert. Es fehlen bei Dir übrigens
1 | On Error Resume Next |
und die Auswertung der Fehlernummer mit Hilfe von Err.Number.
1 | Function HasKey(coll As Collection, strKey As String) As Boolean |
2 | Dim var As Variant |
3 | On Error Resume Next 'bei Fehler weitermachen |
4 | var = coll(strKey) 'fehlerträchtige Operation durchführen |
5 | HasKey = (Err.Number = 0) 'gucken, ob Fehler entstand |
6 | Err.Clear 'Fehlerspeicher löschen für's nächste Mal. |
7 | End Function |
:
Bearbeitet durch User
Und wieder danke ich für deine Ausführliche Erklärung.
mein Code sieht jetzt so aus:
ThisWorkbook.Sheets("Daten").Cells(4, 2).Select
Namen.Add ActiveCell.Value, ActiveCell.Value
Do While ThisWorkbook.Sheets("Daten").Cells(zeile, 2).Value <> ""
If Namen.Item(Anzahl) <> ThisWorkbook.Sheets("Daten").Cells(zeile,
2).Value Then
If Not HasKey(Namen, ThisWorkbook.Sheets("Daten").Cells(zeile,
2).Value) Then
Anzahl = Anzahl + 1
Namen.Add ThisWorkbook.Sheets("Daten").Cells(zeile,
2).Value, ThisWorkbook.Sheets("Daten").Cells(zeile, 2).Value
End If
End If
zeile = zeile + 1
Loop
Leider kann ich nicht sagen ob es schneller geworden ist. Dafür ist die
Zeitspanne zu Kurz. Ich will jetzt auch keine weitere Untersuchen
Starten wie viel schneller diese Variante nun geworden ist. Mir reicht
die Gewissheit, dass es schneller sein sollte.
Natürlich haben sich nach der Lösung dieses Problems neue Probleme
entwickelt und ich Hoffe, es ist in Ordnung, wenn ich diese hier weiter
bespreche:
Die Tabelle ist nun auf eine bestimmte Datenreihe Gefiltert. z.B. sind
wir in Zeile 256 und die Auflistung der Daten fängt an. Nach ein Paar
Duzend oder Hundert Datensätzen ist die Tabelle zu Ende.
Mit den nun Dargestellten Daten will ich automatisch eine Grafik
erstellen lassen und diese Grafik in ein neues Sheet Darstellen.
Dies Erledigt mir dieser Code:
Private Sub CommandButtenDiagrammErstellen_Click()
Dim Name As String
Dim endeDerTabelle As Long
Dim anfangDerTabelle As Long
Worksheets("Daten").Range("B1048576").Select
Selection.End(xlUp).Select
endeDerTabelle = Selection.Row
Name = ActiveCell.Value
Selection.End(xlUp).Select
NaechsteSichtbareZeileSelectieren 2
anfangDerTabelle = Selection.Row
ThisWorkbook.Charts.Add After:=Worksheets("Daten")
With ActiveChart
.ChartType = xlXYScatterLinesNoMarkers
.SetSourceData
Worksheets("Daten").Range(Sheets("Daten").Cells(anfangDerTabelle, 5),
Sheets("Daten").Cells(endeDerTabelle, 6))
.FullSeriesCollection(1).Name = Name 'Diagrammane
End With
Worksheets("Daten").Range("B1048576").Select
End Sub
Ich weiß nicht woran es liegt, doch nach Erstellung der Grafik ist es
mir nicht mehr möglich das Skelet am ende auszufügen. Eine andere
.Select Methode auf eine andere Zelle geht übrigens auch nicht. Die
Fehlermeldung ist:
Laufzeitfehler '1004'
Die Select-Methode des Range-Objektes konnte nicht ausgeführt werden.
Hallo Rob, Rob schrieb: > ThisWorkbook.Sheets("Daten").Cells(4, 2).Select > Namen.Add ActiveCell.Value, ActiveCell.Value Warum vor der Schleife? Das geht doch auch innerhalb. Der Umweg über Select ist auch nicht nötig. Der Abschnitt
1 | If Namen.Item(Anzahl) <> ThisWorkbook.Sheets("Daten").Cells(zeile,
|
2 | 2).Value Then |
scheint mir dann auch überflüssig zu sein.
Das Ergebnis des Ausdrucks "ThisWorkbook.Sheets("Daten").Cells(zeile,
2).Value" hätte ich in eine Variable vom Typ String gepackt, da Du den
Ausdruck mehrfach nutzt und der immer wieder neu ausgewertet werden
muss.
Nur nach Erhöhung Deines Zeilenzählers muss der Ausdruck neu ausgelesen
werden.
> Worksheets("Daten").Range("B1048576").Select
Ist 1048576 überhaupt zulässig?
Oder muss es 1048576-1 sein?
Guck mal ob
Columns("B") geht oder Range("B1:B1048575").
:
Bearbeitet durch User
Peter M. schrieb: > Warum vor der Schleife? > Das geht doch auch innerhalb. > Der Umweg über Select ist auch nicht nötig. ja das stimmt Peter M. schrieb: > Der AbschnittIf Namen.Item(Anzahl) <> > ThisWorkbook.Sheets("Daten").Cells(zeile, > 2).Value Then > scheint mir dann auch überflüssig zu sein. Ich denke, das ist so etwas schneller denn es überprüft ob der Wert von der letzten Zeile noch der gleiche ist. Falls ja, springt er gleich danach in die nächste Zeile. Im Übrigen ist es nicht mehr notwendig diesen Codeabschnitt zu diskutieren. Die nun erreichte Geschwindigkeit ist ausreichen. Peter M. schrieb: > Ist 1048576 überhaupt zulässig? > Oder muss es 1048576-1 sein? ja 1048576 ist zulässig. Ich habe es in der gleichen Funktion am Anfang auch angesteuert. Es muss aber nicht diese Zelle sein. Ich bekomme diese Fehlermeldung, wenn ich auf eine Beliebige Zelle aus dem Blatt "Daten" springen will. Dieser Fehler ist für mich vollkommen unverständlich.
Rob schrieb: > Dieser Fehler ist für mich vollkommen unverständlich. Für mich auch. Bitte meinen Hinweis zur Adressierung mit Thisworkbook.... beachten.
Hey
weiß immer noch nicht warum es den Fehler gibt, konnte ihn aber mit
einen:
Sheets("Daten").Select
umgehen. Es ergibt für mich kein Sinn warum dieses "allgemeine" Select
funktioniert und das "spezielle" nicht.
Ich bitte nochmal um Hilfe: Im Code habe ich Außerhalb einer Funktion: Public verschiedendeDiagramme As Collection Set verschiedendeDiagramme = New Collection stehen. Damit will ich die Collection "verschiedendeDiagramme" von unterschiedlichen sub Funktionen aus zugreifbar machen. Leider kommt eine Fehlermeldung, wenn ich in einer Pivate Sub Funktion: verschiedendeDiagramme.Add ActiveCell.Value aufrufe. Was mache ich Falsch?
Rob schrieb: > Leider kommt eine Fehlermeldung, wenn ich in einer Pivate Sub Funktion: > > verschiedendeDiagramme.Add ActiveCell.Value > > aufrufe. Was mache ich Falsch? Keine Ahnung. Vielleicht hilft hier die geheime Fehlermeldung weiter?
Guten Morgen, die Fehlermeldung ist: Laufzeitfehler 424 Objekt erforderlich Auch wenn ich mir das Programm im Debug Modus ansehe, kann ich die neue Collection nirgendwo finden. Ist es eigentlich egal an welchen Ort ich die Collection definiere? also natürlich außerhalb vom Funktionen aber kann es zwischen den verschiedenen Funktionen geschrieben werden?
Variablen haben einen Gültigkeitsbereich. In Deinem Fall scheinst Du eine Variable außerhalb ihres Gültigkeitsbereichs verwenden zu wollen.
Thread beobachten
Seitenaufteilung abschaltenBitte melde dich an um einen Beitrag zu schreiben. Anmeldung ist kostenlos und dauert nur eine Minute.
Bestehender Account
Schon ein Account bei Google/GoogleMail? Keine Anmeldung erforderlich!
Mit Google-Account einloggen
Mit Google-Account einloggen
Noch kein Account? Hier anmelden.