Robert Johnen
2013-10-28 21:20:55 UTC
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
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?
Mit welcher Geschwindigkeit
breitet sich das Dunkel aus?