Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Maximale Anzahl aufeinanderfolgender gleicher Werte in einer Zeile
479 views
Skip to first unread message
Robert Johnen
Oct 28, 2013, 5:20:55 PM10/28/13
to
Hallo zusammen,
Ich habe eine Tabelle in welcher in einer Zeile mehrere aufeinander folgende
Spalten leer sind. Dies dann in derselben Zeile in verscheidenen Anzahlen.
Ich brauche die Anzahl der leeren Spalten vom letzten Wert der Zeile bis
zu einer bestimmten Zelle (in den Formeln bis Spalte AE)
Beispiel (x=beliebiger Wert):
X X XXXX X XXXX X XXX XXX XXXX XX XXXX XXXXXX
in AF1 steht {=MIN(WENN(A1:AE1<>"";SPALTE(A:AE)))}
findet die erste Spalte mit einem Wert (nicht leer) in Zeile 1.
in AG1 steht =VERWEIS(2;1/(A1:AE1<>"");ZEILE(A:A))
findet die letzte Spalte mit einem Wert (nicht leer) in Zeile 1
in AH1 steht =ZÄHLENWENN(INDIREKT("ZS"&AG1;FALSCH):AE1;"")
gibt mir die Anzahl der leeren Spalten vom letzten Wert bis AE.
Ja, ich weiß, dass ZS sprachabhängig ist, wenns da was besseres gibt,
bin ich für Verbesserungen offen.
Bis dahin tut alles was es soll.
In der obigen Zeile sind zwischen dem ersten und dem letzten Wert zwei
Bereiche, mit jeweils 5 leeren Spalten. Das heißt, in Spalte AI1 soll
jetzt der Wert 5 stehen (5 leere Spalten ist das Maximum der aufeinander
folgenden leeren Spalten).
Kann ich das Makro, mit dem ich das momentan löse durch eine Formel ersetzen?
Momentan löse ich das über folgendes Makro:
=maxgleichefolge(A1:INDIREKT("ZS"&AG1;FALSCH);"")
Option Explicit
Function Maxgleichefolge(objektBereich As Range, Wert As Variant)
'
' Maxgleichefolge Makro
' findet die maximale Folge gleicher Werte auf einander folgender
' Werte in einem angegebenem Bereich MAXGLEICHEFOLGE([BEREICH];Wert)
'
Dim objektZelle As Range ' definiert jede abzufragende Zelle als Bereich
Dim bisheriges_Maximum, momentanes_Maximum As Long ' irgendwo muss ich die Werte reinschreiben
momentanes_Maximum = 0 ' da noch nichts gesucht wurde, sind ist das Maximum erst mal Null
bisheriges_Maximum = 0
Maxgleichefolge = 0 ' falls nichts gefunden wird, ist das Maximum Null
For Each objektZelle In objektBereich ' pruefe fuer jede Zelle im angegebenen Bereich
If objektZelle.Value = Wert Then ' wenn Wert in momentaner Zelle gleich angegebener Wert,
momentanes_Maximum = momentanes_Maximum + 1 ' dann erhoehe das momentane Maximum um 1
' wenn das momentane_Maximum groeßer ist, als das bisher
' festgestellte Maximum, setze das bisheriges Maximum
' auf das momentanes Maximum
If momentanes_Maximum > bisheriges_Maximum Then bisheriges_Maximum = momentanes_Maximum
Else
momentanes_Maximum = 0 ' wenn der Wert in der momentanen Zelle nicht
' dem uebergebenen Wert entspricht, dann wird das
' momentane Maximum wieder auf Null gesetzt.
' Ein worhandenes bisheriges Maximum bleibt
' davon unbehelligt ;-)
End If
Next
If bisheriges_Maximum > 0 Then Maxgleichefolge = bisheriges_Maximum
' Wenn das bisherige Maximum groesser als Null
' dann aendere das Resultat der Funktion auf
' das bisherige Maximum
End Function
Robert
--
Mit welcher Geschwindigkeit
breitet sich das Dunkel aus?
Ich habe eine Tabelle in welcher in einer Zeile mehrere aufeinander folgende
Spalten leer sind. Dies dann in derselben Zeile in verscheidenen Anzahlen.
Ich brauche die Anzahl der leeren Spalten vom letzten Wert der Zeile bis
zu einer bestimmten Zelle (in den Formeln bis Spalte AE)
Beispiel (x=beliebiger Wert):
X X XXXX X XXXX X XXX XXX XXXX XX XXXX XXXXXX
in AF1 steht {=MIN(WENN(A1:AE1<>"";SPALTE(A:AE)))}
findet die erste Spalte mit einem Wert (nicht leer) in Zeile 1.
in AG1 steht =VERWEIS(2;1/(A1:AE1<>"");ZEILE(A:A))
findet die letzte Spalte mit einem Wert (nicht leer) in Zeile 1
in AH1 steht =ZÄHLENWENN(INDIREKT("ZS"&AG1;FALSCH):AE1;"")
gibt mir die Anzahl der leeren Spalten vom letzten Wert bis AE.
Ja, ich weiß, dass ZS sprachabhängig ist, wenns da was besseres gibt,
bin ich für Verbesserungen offen.
Bis dahin tut alles was es soll.
In der obigen Zeile sind zwischen dem ersten und dem letzten Wert zwei
Bereiche, mit jeweils 5 leeren Spalten. Das heißt, in Spalte AI1 soll
jetzt der Wert 5 stehen (5 leere Spalten ist das Maximum der aufeinander
folgenden leeren Spalten).
Kann ich das Makro, mit dem ich das momentan löse durch eine Formel ersetzen?
Momentan löse ich das über folgendes Makro:
=maxgleichefolge(A1:INDIREKT("ZS"&AG1;FALSCH);"")
Option Explicit
Function Maxgleichefolge(objektBereich As Range, Wert As Variant)
'
' Maxgleichefolge Makro
' findet die maximale Folge gleicher Werte auf einander folgender
' Werte in einem angegebenem Bereich MAXGLEICHEFOLGE([BEREICH];Wert)
'
Dim objektZelle As Range ' definiert jede abzufragende Zelle als Bereich
Dim bisheriges_Maximum, momentanes_Maximum As Long ' irgendwo muss ich die Werte reinschreiben
momentanes_Maximum = 0 ' da noch nichts gesucht wurde, sind ist das Maximum erst mal Null
bisheriges_Maximum = 0
Maxgleichefolge = 0 ' falls nichts gefunden wird, ist das Maximum Null
For Each objektZelle In objektBereich ' pruefe fuer jede Zelle im angegebenen Bereich
If objektZelle.Value = Wert Then ' wenn Wert in momentaner Zelle gleich angegebener Wert,
momentanes_Maximum = momentanes_Maximum + 1 ' dann erhoehe das momentane Maximum um 1
' wenn das momentane_Maximum groeßer ist, als das bisher
' festgestellte Maximum, setze das bisheriges Maximum
' auf das momentanes Maximum
If momentanes_Maximum > bisheriges_Maximum Then bisheriges_Maximum = momentanes_Maximum
Else
momentanes_Maximum = 0 ' wenn der Wert in der momentanen Zelle nicht
' dem uebergebenen Wert entspricht, dann wird das
' momentane Maximum wieder auf Null gesetzt.
' Ein worhandenes bisheriges Maximum bleibt
' davon unbehelligt ;-)
End If
Next
If bisheriges_Maximum > 0 Then Maxgleichefolge = bisheriges_Maximum
' Wenn das bisherige Maximum groesser als Null
' dann aendere das Resultat der Funktion auf
' das bisherige Maximum
End Function
Robert
--
Mit welcher Geschwindigkeit
breitet sich das Dunkel aus?
Claus Busch
Oct 29, 2013, 3:13:33 AM10/29/13
to
Hallo Robert,
Am Mon, 28 Oct 2013 22:20:55 +0100 schrieb Robert Johnen:
> X X XXXX X XXXX X XXX XXX XXXX XX XXXX XXXXXX
> in AF1 steht {=MIN(WENN(A1:AE1<>"";SPALTE(A:AE)))}
> findet die erste Spalte mit einem Wert (nicht leer) in Zeile 1.
> in AG1 steht =VERWEIS(2;1/(A1:AE1<>"");ZEILE(A:A))
> findet die letzte Spalte mit einem Wert (nicht leer) in Zeile 1
> in AH1 steht =Z�HLENWENN(INDIREKT("ZS"&AG1;FALSCH):AE1;"")
> bin ich f�r Verbesserungen offen.
probiers mal so:
in AF1:
=VERGLEICH(WAHR;A1:AE1<>"";0)
als array-Formel eingeben (STRG+Shift+Enter)
In AG1:
=MAX(WENN(A1:AE1<>"";SPALTE(A:AE)))
auch als Array_Formel eingeben
In AH1:
=ANZAHLLEEREZELLEN(BEREICH.VERSCHIEBEN(A1;;AF1-1;;AG1-AF1))
Gr��te Serie leerer Spalten:
=MAX(KGR�SSTE((B1:AE1<>"")*(A1:AD1="")*SPALTE(A:AD);SPALTE(A:AD))-KGR�SSTE((B1:AE1="")*(A1:AD1<>"")*SPALTE(A:AD);SPALTE(A:AD)))
und diese wieder als Array-Formel eingeben.
Mit freundlichen Gr��en
Claus
--
Win XP Prof SP3 / Vista Ultimate SP2
Office 2003 SP3 /2007 Ultimate SP3
Am Mon, 28 Oct 2013 22:20:55 +0100 schrieb Robert Johnen:
> X X XXXX X XXXX X XXX XXX XXXX XX XXXX XXXXXX
> in AF1 steht {=MIN(WENN(A1:AE1<>"";SPALTE(A:AE)))}
> findet die erste Spalte mit einem Wert (nicht leer) in Zeile 1.
> in AG1 steht =VERWEIS(2;1/(A1:AE1<>"");ZEILE(A:A))
> findet die letzte Spalte mit einem Wert (nicht leer) in Zeile 1
> gibt mir die Anzahl der leeren Spalten vom letzten Wert bis AE.
> Ja, ich wei�, dass ZS sprachabh�ngig ist, wenns da was besseres gibt,
> bin ich f�r Verbesserungen offen.
probiers mal so:
in AF1:
=VERGLEICH(WAHR;A1:AE1<>"";0)
als array-Formel eingeben (STRG+Shift+Enter)
In AG1:
=MAX(WENN(A1:AE1<>"";SPALTE(A:AE)))
auch als Array_Formel eingeben
In AH1:
=ANZAHLLEEREZELLEN(BEREICH.VERSCHIEBEN(A1;;AF1-1;;AG1-AF1))
Gr��te Serie leerer Spalten:
=MAX(KGR�SSTE((B1:AE1<>"")*(A1:AD1="")*SPALTE(A:AD);SPALTE(A:AD))-KGR�SSTE((B1:AE1="")*(A1:AD1<>"")*SPALTE(A:AD);SPALTE(A:AD)))
und diese wieder als Array-Formel eingeben.
Mit freundlichen Gr��en
Claus
--
Win XP Prof SP3 / Vista Ultimate SP2
Office 2003 SP3 /2007 Ultimate SP3
Robert Johnen
Oct 29, 2013, 8:55:56 AM10/29/13
to
Am Tue, 29 Oct 2013 08:13:33 +0100 schrieb Claus Busch:
> probiers mal so:
> in AF1:
> =VERGLEICH(WAHR;A1:AE1<>"";0)
> als array-Formel eingeben (STRG+Shift+Enter)
Vorteil gegenüber meiner Lösung?
> probiers mal so:
> in AF1:
> =VERGLEICH(WAHR;A1:AE1<>"";0)
> als array-Formel eingeben (STRG+Shift+Enter)
> In AG1:
> =MAX(WENN(A1:AE1<>"";SPALTE(A:AE)))
> auch als Array_Formel eingeben
Vor allem, weil ich diese Lösung mit MIN in AF1 stehen hatte;-)
> In AH1:
> =ANZAHLLEEREZELLEN(BEREICH.VERSCHIEBEN(A1;;AF1-1;;AG1-AF1))
liefert ist das schon mal für internationale Nutzung portabel und
damit wesentlich besser als meine Variante.
> Größte Serie leerer Spalten:
> =MAX(KGRÖSSTE((B1:AE1<>"")*(A1:AD1="")*SPALTE(A:AD);SPALTE(A:AD))-KGRÖSSTE((B1:AE1="")*(A1:AD1<>"")*SPALTE(A:AD);SPALTE(A:AD)))
> und diese wieder als Array-Formel eingeben.
Waah. Ich hatte so ein ähnliches Konstrukt mal um die Anzahl
gleicher Serien in einer Zeile mit Summenprodukt zu bestimmen.
Werde ich ausprobieren, wenn ich nächste Woche wieder vor Excel sitze.
Danke sehr.
Gibt es eigentlich grundsätzliche Erkenntnisse über die Performanz
von Formelkonstruken mit und ohne Array{}-Formeln?
Makro-Lösungen sind mEn selbst als AddIn arschlahm aber meistens
hab ich die Lösung über Makro schneller als ein Formelkonstrukt.
Robert
--
Kindermund
Nach der Schlacht sah man Pferde denen zwei, drei oder mehr Gliedmaßen
abgeschossen wurden, ziellos durch die Gegend rennen.
Claus Busch
Oct 29, 2013, 9:06:32 AM10/29/13
to
Hallo Robert,
Am Tue, 29 Oct 2013 13:55:56 +0100 schrieb Robert Johnen:
> Gibt es eigentlich grundsätzliche Erkenntnisse über die Performanz
> von Formelkonstruken mit und ohne Array{}-Formeln?
> Makro-Lösungen sind mEn selbst als AddIn arschlahm aber meistens
> hab ich die Lösung über Makro schneller als ein Formelkonstrukt.
die eingebauten Funktionen sind wesentlich besser kompiliert, wie man
das bei einer eigenen UDF machen kann. Daher sind diese meist schneller
als Makros oder UDFs.
Wenn man aber bei großem Datenbestand ein kompliziertes Konstrukt
erstellen muss, um zu einem Ergebnis zu kommen, ist es besser VBA
einzusetzen.
Arrayformeln zwingen bei großen Datenbestand den Rechner in die Knie und
es ist dann besser Hilfsspalten zu machen, damit man "normale" Formeln
verwenden kann oder man verwendet dann VBA.
Ich würde keine Arrayformeln verwenden bei mehr als 500 Zeilen.
Einiges über Performance und ihre Verbeserung kannst du hier nachlesen:
http://msdn.microsoft.com/en-us/library/aa730921.aspx
Mit freundlichen Grüßen
Am Tue, 29 Oct 2013 13:55:56 +0100 schrieb Robert Johnen:
> Gibt es eigentlich grundsätzliche Erkenntnisse über die Performanz
> von Formelkonstruken mit und ohne Array{}-Formeln?
> Makro-Lösungen sind mEn selbst als AddIn arschlahm aber meistens
> hab ich die Lösung über Makro schneller als ein Formelkonstrukt.
das bei einer eigenen UDF machen kann. Daher sind diese meist schneller
als Makros oder UDFs.
Wenn man aber bei großem Datenbestand ein kompliziertes Konstrukt
erstellen muss, um zu einem Ergebnis zu kommen, ist es besser VBA
einzusetzen.
Arrayformeln zwingen bei großen Datenbestand den Rechner in die Knie und
es ist dann besser Hilfsspalten zu machen, damit man "normale" Formeln
verwenden kann oder man verwendet dann VBA.
Ich würde keine Arrayformeln verwenden bei mehr als 500 Zeilen.
Einiges über Performance und ihre Verbeserung kannst du hier nachlesen:
http://msdn.microsoft.com/en-us/library/aa730921.aspx
Mit freundlichen Grüßen
Robert Johnen
Nov 1, 2013, 9:18:51 AM11/1/13
to
Am Tue, 29 Oct 2013 14:06:32 +0100 schrieb Claus Busch:
> die eingebauten Funktionen sind wesentlich besser kompiliert, wie man
> das bei einer eigenen UDF machen kann. Daher sind diese meist schneller
> als Makros oder UDFs.
Das hab ich mir gedacht. Was mich allerdings jetzt trotz allem
> die eingebauten Funktionen sind wesentlich besser kompiliert, wie man
> das bei einer eigenen UDF machen kann. Daher sind diese meist schneller
> als Makros oder UDFs.
nicht doch noch motiviert C++ zu lernen.
Übrigens waren die Fragen zu den anderen Formeln durchaus ernst gemeint.
> Wenn man aber bei großem Datenbestand ein kompliziertes Konstrukt
> erstellen muss, um zu einem Ergebnis zu kommen, ist es besser VBA
> einzusetzen.
als Antwort ist eine entschiedenes "Kommt drauf an..." angebracht.
Andererseits, habe ich auch schon Dinge fabriziert, von denen ich
froh bin, dass ich sie nicht selber benutzen muss.
> Arrayformeln zwingen bei großen Datenbestand den Rechner in die Knie und
> es ist dann besser Hilfsspalten zu machen, damit man "normale" Formeln
> verwenden kann oder man verwendet dann VBA.
> Ich würde keine Arrayformeln verwenden bei mehr als 500 Zeilen.
performanter als mit meiner VBA-Funktion. Wobei ich schon versucht habe,
meinem Kollegen schonend beizubringen, dass aufgrund der Datenmenge eine
Datenbank wohl mittlerweile die bessere Wahl wäre. Vor allem, da der
Datenbestand wohl in nächter Zeit exponentiell wachsen wird.
> Einiges über Performance und ihre Verbeserung kannst du hier nachlesen:
> http://msdn.microsoft.com/en-us/library/aa730921.aspx
nachzuvollziehen, wo man so alles richtig und falsch gedacht hat.
Robert
--
Es ist schlimm, in einem Lande zu leben, in dem es keinen Humor gibt.
Aber noch schlimmer ist es, in einem Lande zu leben,
in dem man Humor braucht.
[Bertolt Brecht]
0 new messages