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.
Bitte 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.