Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1328to1332
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

Sricption Dictionary für Arrayformel denkbar ?

Sricption Dictionary für Arrayformel denkbar ?
18.09.2013 14:26:19
Thorben
Hallo liebe Excelgemeinde,
es ist Bergfest und ich dachte ich schau mal wieder vorbei :-)
Ich habe folgende Formel die ich in meinem Sheet in Zelle C2 eintrage und nach unten kopiere.
Leider hatte es schon zur Folge das Excel dicht macht und mir sagt:"...kann mit vorhandenen Resourcen nicht weitermachen...!"
=WENN(A2=A3;"";SUMME(1/MMULT(IDENTISCH(WENN($A$2:A2=A2;$B$2:B2);MTRANS(WENN($A$2:A2=A2;$B$2:B2)))*1; ZEILE($2:2)/ZEILE($2:2)))-1)
Aufbau siehe Datei
https://www.herber.de/bbs/user/87335.xlsx
Mögliche Zeilen min. 18000 max irgendwo bei ca.65000
Ich weiß nicht wie lange die Formel auf meine Art benötigen würde.
Performance ist bei dieser Tabelle wichtig, deswegen dachte ich an Sricption Dictionary.
Kann mir hier jemand weiterhelfen und diese Formel entsprechend tunen.
Vielen Dank schon einmal für eure Hilfe.
MfG
Thorben

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sricption Dictionary für Arrayformel denkbar ?
18.09.2013 15:04:09
Rudi
Hallo,
probier mal ob
=WENN(A2=A3;"";SUMMENPRODUKT((VERGLEICH(A$2:A2&"x"&B$2:B2;A$2:A2&"x"&B$2:B2;0)=ZEILE($1:1))*1) -1-SUMME(C$1:C1))
schneller ist.
Gruß
Rudi

Danke, aber Excel steigt aus "zu wenig Resourcen"
18.09.2013 15:37:24
Thorben
Moin Rudi,
jo, das sieht schonmal gut aus allerdings steigt Excel aus: "kann aufgrund verfügbarer Resourcen nicht weiermachen, bitte weniger Daten wählen bla bla"
Aktuelle Zeilenzahl = 18450
Ich möchte die Datei nicht aufsplitten müssen.
Hast Du eine Idee?
MfG
Thorben

AW: Danke, aber Excel steigt aus "zu wenig Resourcen"
18.09.2013 15:59:31
Rudi
Hallo,
dann per UDF
In ein Modul:
Function zaehlen(strMatch As String, rngA As Range, rngB As Range)
Dim arrA, arrB
Dim i As Long, objCount As Object
arrA = rngA
arrB = rngB
If UBound(arrA)  UBound(arrB) Then
zaehlen = "ERROR"
Else
Set objCount = CreateObject("Scripting.dictionary")
For i = 1 To UBound(arrA)
If arrA(i, 1) = strMatch Then
objCount(arrA(i, 1) & "_" & arrB(i, 1)) = _
objCount(arrA(i, 1) & "_" & arrB(i, 1)) + 1
End If
Next
zaehlen = objCount.Count
End If
End Function

C2: =WENN(A2=A3;"";zaehlen(A2;A$2:A2;B$2:B2))
Gruß
Rudi

Anzeige
Läuft "fast" Rund da Leerzeichen vorkommen
18.09.2013 16:53:19
Thorben
Rudi / Thorben
Besten Dank. Jetzt läufts "fast" rund da Leerzeichen zwischen den Code Nummern in Spalte B vorkommen.
Damit zählt die Funtion so nicht richtig.
Kannst Du mir noch die "Leerzeichen sollen nicht gezählt werden" Anweisung einbauen!?
Dann wäre alles Rund :-)
Nocheinmal vielen Dank
MfG
Thorbeb

Sorry "Leerzellen" nicht Zeichen!!!
18.09.2013 16:55:54
Thorben
so ists richtig !

dann passt meine AW. owT
18.09.2013 17:06:52
Rudi

AW: Läuft "fast" Rund da Leerzeichen vorkommen
18.09.2013 17:05:11
Rudi
Hallo,
so?
Function zaehlen(strMatch As String, rngA As Range, rngB As Range)
Dim arrA, arrB
Dim i As Long, objCount As Object
arrA = rngA
arrB = rngB
If UBound(arrA)  UBound(arrB) Then
zaehlen = "ERROR"
Else
Set objCount = CreateObject("Scripting.dictionary")
For i = 1 To UBound(arrA)
If arrA(i, 1) = strMatch And arrB(i, 1)  "" Then
objCount(arrA(i, 1) & "_" & arrB(i, 1)) = _
objCount(arrA(i, 1) & "_" & arrB(i, 1)) + 1
End If
Next
zaehlen = objCount.Count
End If
End Function

Gruß
Rudi

Anzeige
Saustark! Danke. Hab noch eine allg, Frage
18.09.2013 17:17:24
Thorben
Perfekt!
Unfassbar was für eine Performance dieses Scription Dictionary hat.
Jetzt werde ich mir das mal genauer ansehen.
Wie ist deine Meinung eigentlich dazu?
Es heißt ja immer die normale Formelanwendung ist i.d.R. schneller oder gleich schnell als VBA
Aber wenn ich das hier sehe wiederspricht sich das doch!
Kann man das SD eigentlich auf alle Formeln anwenden?
Höre gern
Beste Grüße aus Bremen
Thorben

Das ist KEIN Widerspruch, ...
18.09.2013 20:52:14
Luc:-?
…Thorben,
denn in VBA gibt's - wie in jeder Pgmiersprache - „schnellere“ und „langsamere“ Komponenten. Man muss halt immer das für den jeweiligen Zweck geeignetste wählen. ;-)
Es soll ja auch Leute geben, die in XL mit 2-3 Fktt auskommen, in VBA ist das ähnlich. Wer nur einen Typ zyklischer Verarbeitung (sog „Schleife“) kennt und folglich auch nur den anwendet, wird mit seinem Pgm oft langsamer sein als die meisten Fmln, die immerhin auf xl-immanente, direkt pgmierte und kompilierte DLLs zurückgreifen, während VBA eine Mischung aus kompilierten und interpretierten Befehlen ist.
So ziemlich das „Langsamste“ in VBA ist eine „klassische Schleife“ ala For i=1 To n über einen Zellbereich. Etwas schneller geht's mit diesem Typ, wenn er auf ein Datenfeld (hier VBA-Array) angewendet wird. Noch schneller ist ein For Each x In xyz-Zyklus, der sowohl auf Objekte wie Zellbereiche als auch Datenfelder angewendet wdn kann, wobei wir mit letzterem dem Scripting Dictionary schon recht nahe kommen, denn lt MS handelt es sich hierbei um eine Art „assoziatives Array“ wie es zB in Perl verwendet wird. So etwas kann man in VBA auch nachbauen, indem man als Elemente eines Vektors ebenfalls Vektoren verwendet. Dadurch entsteht ein Variant mit einem Array, kein klassisches n-dimensionales VBA-Array, das aber ohnehin in der Pgmierwelt eine Ausnahme­erscheinung sein dürfte. Der Variant lässt sich jedenfalls für viele Zwecke viel besser und auch mit Performance-Gewinn einsetzen. Da er aber erst mit VBA-Mitteln aus einem Zellbereich oder einem VBA-Array erzeugt wdn muss, ist das Dictionary schneller, da es auf fertige DLL-Routinen zurückgreifen kann. Außerdem kann es als Values auch unterschiedlich lange Vektoren, genau wie der Variant, enthalten.
Deine Frage „Kann man das SD eigentlich auf alle Formeln anwenden?“ ist recht unpräzise — man kann es in VBA anwenden und damit auch - wie von Rudi demonstriert - in UDFs für den Einsatz in ZellFmln. Anders kann man es natürlich nicht in Fmln unterbringen! Da muss man sich schon auf das verlassen, was in die StandardFktt eingebaut wurde…! ;-]
Gruß (aus BB.TF.BM), Luc :-?

Anzeige
eigentlich hat das nichts ....
19.09.2013 15:27:44
Rudi
Hallo,
... mit dem Dictionary zu tun. Das benutze ich nur aus Bequemlichkeit als Sammler. Kann man auch mit einem String realisieren.
Function zaehlen(vntMatch, rngA As Range, rngB As Range)
'Unikate zählen in 2 Spalten
'1. Spalte muss sortiert sein
Dim lngRow As Long, strKey As String
Dim str As String
If rngA.Row  rngB.Row Or rngA.Rows.Count  rngB.Rows.Count Then
zaehlen = "ERROR"
Else
lngRow = Application.Caller.Row - rngA.Row + 1
Do While rngA(lngRow, 1) = vntMatch
If rngB(lngRow, 1)  "" Then
strKey = vntMatch & "_" & rngB(lngRow, 1)
If InStr(str, strKey & "|") = 0 Then
str = str & strKey & "|"
End If
End If
lngRow = lngRow - 1
If lngRow = 0 Then Exit Do
Loop
zaehlen = UBound(Split(str, "|"))
End If
End Function

Gruß
Rudi
Anzeige

299 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige