Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
612to616
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
612to616
612to616
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Anzahl Datensätze ermitteln

Anzahl Datensätze ermitteln
17.05.2005 19:26:07
Rolf
Hallo Excelprofis,
folgendes Makro ermittelt die Anzahl der Datensätze von Artickelnummern.
Sind mehrere gleiche Artikelnummern vorhanden, werden diese als ein Datensatz
gezählt.
In Tabelle 1 ab Zelle B2 befinden sich die Artikelnummern. In Tabelle 2 Zelle A1 gebe ich z.B. die Ziffer 4 ein. Jetzt ermittelt das Makro die Anzahl der Datensätze in denen 4 gleiche Artikelnummern vorkommen. Die Anzahl wird über eine MsgBox ausgegeben.
Da es ca 20000 oder mehr Datensätze gibt dauert der Ablauf des Makros sehr lange.
Gibt es ein Makro, dass diese Aufgabe schneller erledigt und die ermittelte Anzahl in Tabelle 2 Zelle A2 ausgibt?

Sub DatensätzeZählen()
Dim lngCount As Long
lngCount = [SUM(IF(COUNTIF(Tabelle1!B2:B20000,Tabelle1!B2:B20000)=Tabelle2!A1,1/COUNTIF(Tabelle1!B2:B20000,Tabelle1!B2:B20000)))]
MsgBox lngCount
End Sub

Vielen Dank im voraus
Rolf

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Schneller geht es nicht...
17.05.2005 21:52:32
Boris
Hi Rolf,
...als mit "evaluierten" Matrixformeln. Die Alternative ist eine schnarchlangsame For-Next-Schleife.
Aber das hatte ich dir auch schon im letzten Beitrag geschrieben.
Grüße Boris
AW: Schneller geht es nicht...
17.05.2005 22:29:33
Rolf
Hallo Boris,
schade das es keine schnellere Möglichkeit gibt.
Noch einmal vielen Dank.
Gruß Rolf
AW: Schneller geht es nicht...
18.05.2005 15:00:14
Luc:-?
Hallo Rolf & Boris,
also ich weiß ja nicht - "schnarchlangsam" scheint mir eher die evaluierte Matrixformel (eMf) zu sein. Nachdem ich herausgefunden hatte, worum es sich bei dieser Formel (aus offensichtlich Boris' Trickkiste - sehr elegant!) handelt, habe ich es mal mit For Each...In...Next und COUNTIF (ZÄHLENWENN) versucht. Egal ob mit 2 oder 20 000 Zeilen, Win98SE oder NT2000p, AMD Athlon oder Intel PIII, die Schleife war unter Office2000 deutlich schneller, wenn auch immer noch recht langsam (10:26 ggüber 27:08 min auf PIII-Plattform hier im Institut - zuhause mit Athlon sicher halbe Zeiten). Wahrscheinlich ist die eMf wegen der Vielzahl der wohl pro Zelle lfd Transformationen/-aktionen hier die langsamere Methode. Die stärkere Integration in das XL-Programmgerüst scheint sich deshalb nicht zeitverkürzend auszuzahlen. Evtl erhält man andere Ergebnisse, wenn sehr viele Artikelnr mehrfach auftreten?!
Übrigens bin ich davon ausgegangen, dass in jeder Zeile (=1 DS i.e.S.) nur eine Artikelnr steht, aber weitere Zeilen an dieser Stelle die gleiche Artikelnr enthalten können. Alle Zeilen mit gleicher Artikelnr bilden dann einen DS i.w.S. (edv-technisch = Datenblock).
Normalerweise sind Schleifen schon recht schnell - es kommt nur darauf an, was sie enthalten und wieviel noch zur Laufzeit kompiliert und transformiert wdn muss. Evtl könnte es noch schneller gehen, wenn sich Gesamt- und rückwärts gerichteter Vgl mit CountIf ersetzen bzw optimieren ließen. Vielleicht versuche/t ich/ihr das mal.
In den folgenden Code habe ich eine Eingabealternative mittels InputBox aufgenommen (wenn A1 in Tabelle2 leer ist):
Sub DSZählen()
Rem Autor Luc:-? -- 20050517
Static mAN As Variant
Dim lngCount As Long, x As Range, z As Date
z = Now()
If IsEmpty(Sheets("Tabelle2").Cells(1, 1).Value) Then
mAN = InputBox("Bitte Anzahl angeben!", "Anzahl gleicher ANr/DS", mAN)
Else: mAN = Sheets("Tabelle2").Cells(1, 1).Value
End If
If mAN = "" Then Exit Sub
Sheets("Tabelle1").Select
For Each x In ActiveSheet.Columns(2).Cells
If x.Row = 1 Or IsEmpty(x.Value) Then GoTo nx
With WorksheetFunction
If .CountIf(Range(Cells(2, 2), x), x.Value) = 1 Then
If .CountIf(Columns(2), x.Value) = mAN Then
lngCount = lngCount + 1
End If
End If
End With
nx: Next x
z = Now() - z
MsgBox lngCount & " (Zählzeit: " & z & ")", vbOKOnly, "DS mit " & mAN & "facher ANr"
End Sub
Die Abarbeitung wird für die gesamte Spalte ab B2 durchgeführt, Leerfelder wdn übergangen. Wenn die Abarbeitung bei einem Lehrfeld beendet wdn soll, muss die Zeile...
If x.Row = 1 Or IsEmpty(x.Value) Then GoTo nx
durch die Zeilen
If x.Row = 1 Then GoTo nx
und
If x.Value = "" Then Exit For
ersetzt wdn. Auf Grund deiner Erklärung, Boris, habe ich hier noch 2-3 Stunden Test unter möglichst realen Bedingungen aufgewendet (hat mir meinen Tagesplan etwas durcheinander gebracht), komme aber vorerst nur zu dem Ergebnis, dass leider alles inakzeptabel langsam ist. Viele Grüße Luc :-?
Anzeige
AW: Schneller geht es nicht...
18.05.2005 16:05:23
Lothar
Hi,
wusstest du, dass Goto seit 1968 unter Strafe steht(außer bei Fehlerbehandlung)?
mfg Lothar
...und - geht's ohne schneller? In den 60ern...
18.05.2005 16:54:51
Luc:-?
...sind die 1.höheren Sprachen, bei denen man schon mal über den Verzicht auf Sprungbefehle nachdenken konnte, mal gerade erst entstanden. Die 1., die das dann konsequent umgesetzt haben, waren die Macher von dBase. Aber wir sprechen hier ja auch nicht von Lisp oder Prolog, sondern nur von VBA. Da können wir ruhig in den Niederungen bleiben. Es gibt hier weitaus Schlimmere(s) als ein GoTo innerhalb einer Schleife.
mit Gruß an "Lothar"
Luc :-?
PS: Woher willst du das wissen? Selbst an damaligen Diskussionen teilgenommen?
Anzeige
AW: ...und - geht's ohne schneller? In den 60ern...
18.05.2005 18:25:40
Rolf
Hallo Lothar und Luc:-?,
danke für Eure Mühe - funktioniert.
Gruß Rolf
Schön, aber geht's auch schneller und dir...
18.05.2005 20:45:49
Luc:-?
...schnell genug, Rolf?
Das würde mich schon noch interessieren. Und was Boris dazu meint natürlich auch.
Gruß Luc :-?
PS: Was hat Lothar damit zu tun? Hast du denn das GoTo durch eine vollständige If...EndIf-Einbettung ersetzt?
AW: Schön, aber geht's auch schneller und dir...
19.05.2005 10:05:15
Boris
Hi Ludwig,
Und was Boris dazu meint natürlich auch
Ich hatte meinen Ursprungstest nicht mit 20000 Zeilen durchgeführt.
Dabei geht die Formel natürlich in die Knie.
Deine Lösung mag schneller sein - allerdings würd ich da die grundsätzliche Dateistruktur eher mal überdenken.
Grüße Boris
Anzeige
@Boris
19.05.2005 11:27:13
Luc:-?
Hallo Boris,
danke für deine Stellungnahme. Habe auch erst auf Grund deiner Auslassungen mit 20000 Zeilen getestet - mit 2000 war die Schleife allerdings auch schon schneller.
Ich vermute, dass du mit grundsätzlicher Struktur mehr meinst als nur ein GoTo. Hatte zwar schon angedeutet, dass noch anderes möglich wäre, weiß aber nicht, ob ich die Zeit dafür finde. Kannst aber hier gerne zeigen wie du dir die Alternative vorstellst.
Gruß & schönen Tag
Luc :-?
Ich meinte die DATEIstruktur
19.05.2005 14:18:48
Boris
Hi Ludwig,
...und nicht deinen Code.
Grüße Boris
Ach so! Das hat nun Martin B. erledigt! owT
19.05.2005 14:54:19
Luc:-?
:)
AW: Alternative bei sortierten Datensätzen
19.05.2005 13:04:47
Martin
Hallo alle,
wenn die Datensätze aufsteigend sortiert sind, läuft bei mir folgendes Makro in wenigen Sekunden durch:

Sub Anzahl_Datensaetze()
CritVal = 4
lastrow = Range("B65536").End(xlUp).Row
erg = 0
z = 0
For i = 2 To lastrow
If Cells(i, 2) = Cells(i - 1, 2) Then
z = z + 1
Else
If z = CritVal - 1 Then erg = erg + 1
z = 0
End If
Next i
MsgBox erg
End Sub

Falls die Daten unsortiert sind, könnte man per VBA eine temporäre Kopie des Blattes anlegen, die Sortierung und Auszählung durchführen und dann das temporäre Blatt wieder löschen.
Gruß
Martin Beck
Anzeige
AW: Alternative bei sortierten Datensätzen
19.05.2005 13:50:15
Luc:-?
Hallo Martin,
dass man ja vorher auch sortieren könnte, geht bei mancher Problem(lösungs)vorlage glatt unter. Man wird da zu sehr in bestimmte Denkrichtungen gelenkt.
Gruß Luc :-?

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige