Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1816to1820
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1816to1820
1816to1820
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel als VBA-Code

Formel als VBA-Code
06.03.2021 09:15:38
Thomas
Hallo zusammen,
in einer Exceldatei habe ich folgende Formel: 
SUMMENPRODUKT((VERGLEICH($A$1:$A$15000&$F$1:$F$15000;$A$1:$A$15000&$F$1:$F$15000;0)=ZEILE($X$1:$X$15000))*($A$1:$A$15000=N2)*($F$1:$F$15000""))
In der Spalte A stehen Gruppen 1,2,3,4 in der Spalte F Namen Thomas,Franz,Theodor.
Die Formel zählt wie viele unterschiedliche Namen in jeder Grupp sind.
Die Formel funktioniert braucht aber bei einer Zeilenlänge von 15000 mehrere Stunden. Deshalb meine Frage, wäre die Bearbeitung mit einem VBA Code schneller und wenn ja, wie würde dieser lauten?
Vielen Dank und schöne Grüße
Thomas

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel als VBA-Code
06.03.2021 10:38:00
onur
Nicht ohne (Beispiels-) Datei.

AW: Formel als VBA-Code
06.03.2021 11:39:12
Thomas
Hallo zusammen,
anbei eine Beispieldatei:
https://www.herber.de/bbs/user/144514.xlsx
Mir wäre schon eine VBA-Lösung lieb, da ich vermute mit einer anderen Formel wird es nicht schneller.
Der Echt-Datensatz hat etwa 15000 Gruppen
Schöne Grüße
Thomas

AW: unique A1&B1?
06.03.2021 11:51:02
Fennek
Wenn ich die Formel richtig verstanden habe, wird die Anzahl der eindeutigen Namen in Spalte B pro Nr.in Spalte A ermittelt.
Wenn dem so ist, geht das in VBA mit "Dictionary" extrem schnell.

AW: unique A1&B1?
06.03.2021 11:55:44
Thomas
Hallo Fennek,
es ist genauso gewollt wie du es beschrieben hast.
Wie würde die Lösung mit VBA aussehen?
Schöne Grüße
Thomas

Anzeige
AW: Formel als VBA-Code
06.03.2021 12:23:29
Daniel
Hi
dann probier mal folgendes Makro:
Sub Anzahl_ProGruppe()
With Range("E1")
Range("A:A,B:B").Copy .Cells(1, 1)
With .CurrentRegion
.Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlYes
.RemoveDuplicates Array(1, 2), xlNo
With .CurrentRegion.Columns(2)
.FormulaR1C1 = "=IF(RC[-1]=R[1]C[-1],R[1]C+1,1)"
.Formula = .Value
End With
.Cells(1, 2).Value = "Anzahl"
.RemoveDuplicates 1, xlNo
End With
End With
End Sub
der ist jetzt passend für deine Beispieldatei.
dh um das für deine Echtdatei anzupassen musst du folgendes verändern:
- für "A:A" die Spalte mit den Gruppen
- für "B:B" die Spalte mit den Namen
- für "E1" die linke obere Zelle, ab der das Ergebnis ausgegeben werden soll
der Code geht davon aus, das Zeile 1 die Überschriftenzeile ist.
sollten die Gruppenbezeichnungen für die Auswertung schon vorgegeben sein und nicht automatisch ermittelt werden sollen, könnte man das per UDF auch beschleunigen, aber dann wirds etwas komplizierter.
Gruß Daniel

Anzeige
AW: Formel als VBA-Code
06.03.2021 12:38:06
Thomas
Perfekt!!!
Genau das wars.
Vielen vielen Dank!

AW: Formel als VBA-Code
06.03.2021 12:44:24
Daniel
Hi
gut.
da du ja offensichtlich mehrere Foren befragt hast, gib bitte dort bescheid, dass die Aufgabe gelöst ist und setzte einen Link auf diesen Beitrag, falls dort jemand interessiert, wie das Problem gelöst ist.
Wenn du möchstest, dass Crossposting (Anfrage in mehreren Foren gleichzeitig stellen) weiterhin akzeptiert wird, solltest du das tun.
bitte beachte:
im Herberforum gibt es einen aktiven Teil (diesen) und ein Archiv.
nach c.a. einer Woche werden die Beiträge aus dem aktiven Teil gelöscht und es existiert nur noch der Archiveintrag.
Wenn du daher auf einen Beitrag im aktiven Teil verlinkst, dann funktioniert der Link nach einer Woche nicht mehr.
Wenn du also einen Link auf einen Beitrag im Herberforum setzen willst, dann musst du zuerst auf den Button "zum Archivthread" klicken und auf den Beitrag dort verlinken.
Gruß Daniel

Anzeige
AW: als "SummeWenn()"?
06.03.2021 10:41:23
Fragender
Vor kurzem ist es mir nicht gelungen ein
=summenprodukt((A1:A20=1)*(B1:B20))
in VBA umzusetzen, wegen der prüfung auf =1
Umgesetzz in eine =SummeWenn() ging in VBA problemlos.
-----------------
Generell gilt solche Fragen nur mit ursprünglichen VBA-Befehlen anzugehen und nicht mit Worksheetfuction.sumproduct()

AW: Formel als VBA-Code
06.03.2021 11:06:21
Daniel
Hi
Mit Basiskenntnissen so:
1. Liste nach Gruppe (Prio 1) und Name (Prio 2) sortieren
2. eine Hilfsspalte einfügen mit folgender Formel ab Zeile 2 
=1-(A2=A1)*(F2=F1)

Oder etwas ausführlicher: 
=wenn(und(a2=A1;f2=F1);0;1)
3. dann Auswertung dieser Hilfsspalte mit einfacher SummeWenn nach Gruppennamen: 
=SummeWenn(A:A;n2;Hilfsspalte)

Falls du die Liste in anderer Sortierung brachte, vor dem Rücksortieren Ergebnisformeln oder Hilfsspalte kopieren und durch Werte ersetzen.
Oder wenn du sehr viele Gruppen hast, mit Pivottabelle.
Gruß Daniel

Anzeige
AW: Formel als VBA-Code
06.03.2021 11:59:24
SF
Toll dass du im anderen Forum Bescheid gesagt hast das es jetzt hier weitergeht....

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige