Anzeige
Archiv - Navigation
1176to1180
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

Datum aus früherer Spalte übernehmen - Bedingungen

Datum aus früherer Spalte übernehmen - Bedingungen
Peter
Guten Tag
Im einer Tabelle steht in einem Bereich in Spalte A eine Datumsreihe, in der Spalte B ab Position 2 oder später ein dazugehöriger Wert - einige Zellen auf Spalte B sind jedoch leer.
Nun möchte ich in Spalte C mittels Formel ein Datum aus Spalte A übernehmen, mit folgender Logik:
- sofern in einer Zelle in Spalte B kein Wert eingetragen ist, bleibt auch die entsprechende Zelle in Spalte C leer
- zur ersten Zahl in Spalte B gehört das erste Datum aus Spalte A
- in alle anderen Zellen in Spalte C gehört das Datum aus Zelle in Spalte A, in welcher der letzte vorige Eintrag in Spalte B steht
Bin leider nicht schlau geworden, wie ich das in eine Formel packen kann.
Wer kann mir helfen?
Danke und Gruss, Peter
https://www.herber.de/bbs/user/71508.xls
geht bestimmt auch kürzer...
13.09.2010 13:38:09
silex1
Hallo,
...aber mir viel nix besseres ein! :-))
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABC
429.08.2010  
501.09.2010100.01029.08.2010
602.09.2010100.02501.09.2010

ZelleFormel
C5=WENN(B5="";"";WENN(ISTFEHLER(VERWEIS(2;1/($B$4:B4>"");$A$4:A5));MIN($A$4:A4);VERWEIS(2;1/($B$4:B4>"");$A$4:A5)))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Formel nach unten kopieren!
VG, Rene
Anzeige
AW: geht bestimmt auch kürzer...
13.09.2010 13:59:30
Peter
Hallo René
Vielen Dank! Sieht zwar kompliziert aus, die Ergebnisse stimmen aber perfekt.
Nun habe ich mir überlegt, eine eigene Funktion zu schreiben (vgl. nachstehend).
Vielleicht kann mir ein VBA Profi sagen, ob das mittels VBA auch einfacher zu erreichen wäre?
Danke und Gruss, Peter
Function RelTag() Dim ze As Long, sp As Long, Monat As Long If Application.Caller.Offset(0, -1) = "" Then RelTag = "" Else ze = 1 'Erster Möglicher Zielwert befindet sich in 1 Zeile oberhalb Caller Monat = Month(Application.Caller.Offset(0, -2).Value) Do While Month(Application.Caller.Offset(-ze, -2).Value) = Monat And Application.Caller.Offset(- _ ze, -1).Value = 0 ze = ze + 1 Loop RelTag = Application.Caller.Offset(-ze, -2).Value End If End Function
Anzeige
AW: geht bestimmt auch kürzer...
13.09.2010 14:44:10
JogyB
Hallo Peter,
Deine Funktion geht so nicht, ändere mal einen Wert in Spalte B, das wird nicht übernommen. Es muss dann schon ein Application.Volatile an den Anfang der Funktion. Das ist aber auch eine Krücke, es so zu machen. Wenn, dann würde ich die beiden Spalten übergeben.
Mal als Vorschlag: Function relTag2(daTum As Range, werT As Range) Dim i As Long ' Wenn nicht aus Arbeitsblatt heraus aufgerufen, einfach raus If Not TypeOf Application.Caller.Parent Is Worksheet Then Exit Function End If With Application.Caller.Parent ' ein paar Prüfungen, ob sinnvolle Bereiche angegeben wurden If daTum.Columns.Count > 1 Or werT.Columns.Count > 1 Or daTum.Areas.Count > 1 Or _ werT.Areas.Count > 1 Or daTum.Row werT.Row Or _ daTum.Rows.Count werT.Rows.Count Or _ Intersect(daTum.EntireRow, Application.Caller) Is Nothing Then relTag2 = CVErr(xlErrRef) ElseIf .Cells(Application.Caller.Row, werT.Column) = "" Then relTag2 = "" Else ' Den letzten Eintrag in der Wertspalte suchen For i = Application.Caller.Row - 1 To werT.Row Step -1 If .Cells(i, werT.Column) "" And IsDate(.Cells(i, daTum.Column)) Then relTag2 = .Cells(i, daTum.Column) Exit Function End If Next ' wenn es hierhin kommt, dann hat es keinen vorigen Wert mit Datum gefunden, ' also niedrigstes Datum in Datumsspalte nehmen relTag2 = Application.Min(daTum) End If End With End Function
Gruß, Jogy
Anzeige
24 Zeichen kürzer ;)
13.09.2010 14:02:30
JogyB
Hallo Peter,
das kommt in Zelle C2:
{=WENN(B2="";"";WENN(MAX(C$1:C1)=0;MIN(A:A);INDEX(A:A;MAX(WENN(B$1:B1<>"";ZEILE(B$1:B1))))))}
Ist eine Matrixformel, die {} nicht mit eingeben, sondern die Eingabe mit Strg+Shift+Enter abschliessen.
Gruß, Jogy
...und noch 7 weniger...;-)
13.09.2010 17:13:13
silex1
Hallo,
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABC
29Beispiel 3  
3029.08.2010  
3101.09.2010  
3202.09.2010  
3303.09.2010100.04029.08.2010
3404.09.2010100.05503.09.2010
3505.09.2010100.07004.09.2010
3606.09.2010  
3707.09.2010  
3808.09.2010100.11505.09.2010

ZelleFormel
C30{=WENN(ANZAHL2($B$30:B30)=1;$A$30;WENN(B30="";"";MAX(WENN($B29:B$30>"";$A29:A$30))))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

VG, Rene
Anzeige
und nochmal 8 ;)
13.09.2010 22:05:28
JogyB
Hallo,
Beispiel wie vorher von Rene:
In C30: =WENN(B30="";"";A30)
In C31: {=WENN(MAX(C$30:C30);WENN(B31="";"";MAX(WENN($B30:B$30<>"";A$30:A30)));$A$30)}
Gruß, Jogy
AW: und nochmal 8 ;)
13.09.2010 22:47:53
Peter
Hallo miteinander
Das ist wirklich beeindruckend! Vielen Dank
@ Jogy: könntest du mir die Logik der Formel erklären - da fehlt mir das nötige Latein noch dazu.
Vielen Dank und Gruss, Peter
AW: und nochmal 8 ;)
14.09.2010 06:24:53
JogyB
Hallo Peter,
die in C30 ist ja klar. Für die weiteren Zellen nehme ich jetzt mal C35 als Beispiel, ist besser zu erklären, wenn man ein paar Zellen mehr nimmt:
{=WENN(MAX(C$30:C34);WENN(B35="";"";MAX(WENN($B30:B$34"";A$30:A34)));$A$30)}
Die Formel schaut nun zunächst nach dem Maximum in den vorigen Zeilen in Spalte C (Zeile 30 als Anfang des Suchbereichs ist fixiert!). Ist dort noch kein Datum vorhanden, dann ist das 0 = FALSCH und es nimmt den Wert aus A30.
Ist es 0, dann wird die Bedingung WAHR und es geht in die nächste WENN-Formel. Dort schaut es, ob B35 leer ist. Ist das der Fall, dann ist das Ergebnis auch ein leerer String.
Falls es nicht leer ist, geht es zu der MAX-Formel. Innerhalb der wird mit der WENN-Formel nun ein neuer Bezug Vektor aus Werten aufgebaut. Die geht nun Zelle B30 (die ist als Anfang fixiert) bis zur Zeile in Spalte B vor der aktuellen Zeile (also hier B34) durch. Ist dort ein Wert eingetragen, dann übernimmt es das entsprechende Datum aus Spalte A, wenn nicht, dann steht FALSCH drin. Die MAX-Funktion ingnoriert das FALSCH und nimmt nur die Datumswerte, d.h. das Ergebnis ist der größte Datumswert, der zu einer gefüllten Zelle in Spalte B gehört. Da die Datumswerte in Deiner Tabelle ansteigend angeordnet sind, ist das gleichbedeutend mit dem Datum der letzten gefüllten Zelle in Spalte B vor der aktuellen Zeile.
Das ganze ist dann eine Matrixformel, weil Du hier mit der WENN-Formel einen Bereich auswertest, was diese ja eigentlich nicht kann.
Gruß, Jogy
Anzeige
AW: und nochmal 8 ;)
14.09.2010 12:20:06
Peter
Hallo Jogy
Vielen Dank für die ausführliche Erläuterung!
Gruss, Peter
...und nochmal 12 abgeknappst ;)
14.09.2010 12:27:37
silex1
Hallo,
in C30 (Matrixformel!)
WENN(B30="";"";MAX($A$30;MAX((WENN($B29:B$30"";$A29:A$30)))))
VG, Rene
hast gewonnen...
14.09.2010 13:11:19
JogyB
jetzt könnte ich nur noch sparen, indem ich die drei unnötigen Dollarzeichen bei den Bezügen entferne ;)... obwohl, stehen in Spalte B vielleicht nur Zahlen oder Leerzellen? :D
Gruß, Jogy
die unnötigen $ hat der Server dazugeschrieben...
14.09.2010 18:23:02
silex1
...der Schuft! Oder ich war der Depp beim kopieren :-)))
VG, Rene
geht viel kürzer ;-)
14.09.2010 19:19:05
FP
Hallo Jogy, Rene usw.
... mit einem kleinen Trick :-)
Tabelle2
 ABC
129.08.2010<- Startdatum 
201.09.2010  
302.09.2010  
403.09.2010100.04029.08.2010
504.09.2010100.05503.09.2010
605.09.2010100.07004.09.2010
706.09.2010  
807.09.2010  
908.09.2010100.11505.09.2010
10   
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Formel in C2:
Tabelle2
 ABC
129.08.2010<- Startdatum 
201.09.2010  
Formeln der Tabelle
C2 : =MAX(A$1;VERWEIS(9;1/(B$1:B1<>""); A$1:A1))*(B2<>"")
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Servus aus dem Salzkammergut
Franz
Anzeige
AW: MAX ist sogar überflüssig
14.09.2010 20:23:07
FP
Hallo,
=VERWEIS(9;1/(B$1:B1<>"");A$1:A1)*(B2<>"")
genügt vollkommen!
B1 MUSS dabei belegt sein ( z.B. Text "<-Startdatum" )
Servus aus dem Salzkammergut
Franz
AW: MAX ist sogar überflüssig
14.09.2010 21:43:39
Peter
Hallo miteinander
Ich bin begeistert, was da alles herausgekommen ist, vielen Dank!
Die Frage von Jogy
.. obwohl, stehen in Spalte B vielleicht nur Zahlen oder Leerzellen? :D
kann ich beantworten mit:
- in Spalte B steht eine Formel, deren Ergebnis entweder eine Zahl oder dann nichts ...;"") ist.
@ Franz
Bis heute abend wusste ich nicht, dass es ein Salzkammergut gibt; jetzt weiss ich schon, wo das in etwa ist ...
Ich habe versucht herauszufinden, was die Argumente der Funktion VERWEIS, insbesondere die 9 sowie die 1/... machen, bin jedoch mit der Googlerei nicht zu einem Ziel gekommen. Könntest du mir diese Geheimnisse verraten?
DAnke, Peter
Anzeige
@Franz, Du Trickser...
14.09.2010 22:19:43
silex1
Hallo Franz,
Deine Zugang müsste man sperren lassen. Tauchst auf, still und heimlich und haust einen die Faust in den Magen! Autsch....heul...feix! ;-)
Hatte es auch mit dem VERWEIS() getestet (siehe meinen ersten Post) und bin aber an der leeren B Spalte gescheitert!
Genialen Idee, mit dem Startdatum! So ist dann auch die Matrixformel vom Tisch, denn bei langen Listen müsste sich das arme XL sehr quälen. Schön das Du auch über "ältere" Beiträge schaust, denn wie in diesem, werde ich bestärkt, auch mal "weiter" zu denken bzw. einen anderen Ansatz zu wählen! Danke!
@Peter
Du musst es optisch trennen, dann erkennst Du was die 9 und die 1 bedeuten.
=VERWEIS(9; 1/(B$1:B1"") ;
VERWEIS sucht eine Zahl. Wenn Du ne 1 durch irgendwas teilst, kommt max. ne 1 raus (1/1=1).
Da in der XL-Hilfe steht Ist das Suchkriterium kleiner als der kleinste Wert in Suchvektor, gibt VERWEIS den Fehlerwert #NV zurück.
MUSS das Suchkriterium größer sein!!!! Wahl der Zahl nach Belieben, obwohl 15 angebrachter wäre (Anzahl Stellen!!!)
VG, René
Anzeige
AW: @Franz, Du Trickser...
14.09.2010 22:47:21
Peter
Hallo René
ich scheine da noch einen grossen Knopf in der Leitung zu haben.
Könntest du mir das nochmals an Hand der Formel =VERWEIS(9;1/(B$1:B8"");A$1:A8)*(B9"")
erklären? Da B7 und B8 leer sind, gibt mir die Formel den Inhalt von A6 zurück.
Aber da sehe ich leider noch nicht klar. Fehlt mir da das Mathe-Wissen oder ein Stück Logik?
Gruss, Peter
AW: @Franz, Du Trickser...
14.09.2010 23:23:39
silex1
Hallo Peter,
ich hoff, ich kann es vermittel...bin kein guter Erklärbär!
Hab Dir mal aufgedrösselt, wie es XL sieht (C10).
Arbeitsblatt mit dem Namen 'Tabelle2'
 ABC
129.08.2010- Startdatum 
201.09.2010  
302.09.2010  
403.09.2010100.04029.08.2010
504.09.2010100.05503.09.2010
605.09.2010100.07004.09.2010
706.09.2010  
807.09.2010  
908.09.2010100.11505.09.2010
10  40426
11  40426

ZelleFormel
C9=VERWEIS(9;1/(B$1:B8>"");A$1:A8)*(B9>"")
C10=VERWEIS(9;{1;#DIV/0!;#DIV/0!;1;1;1;#DIV/0!;#DIV/0!};{40419;40422;40423;40424;40425;40426;40427;40428})*(B9>"")
C11=VERWEIS(9;1/(B$1:B8>"");A$1:A8)*(WAHR)
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

=VERWEIS(9; 1/(B$1:B1"")

Wenn B leer ist, ergibt 1/0=#DIV/0. Dieses Ergebnis scheitet aus!
Da der VERWEIS() ne Spalte von UNTEN nach OBEN durchpflügt, nimmt es die erste passende Kombination (1*40426). Dies ist A6!
Dann wird noch geschaut, ob B9 ungleich leer ist und ist es auch oK, dann halt A6.
Schau mal unter EXTRAS-FORMELÜBERWACHUNG-FORMELAUSWERTUNG. Da ist es auch besser nachvollziebarer.
VG, René
Anzeige
AW: @Franz, Du Trickser...
15.09.2010 11:40:57
Peter
Hallo René
Vielen Dank für die Erklärung
Woran ich mich gewöhnen muss ist die Tatsache, dass Zwischenergebnisse wie #DIV/0 nicht die ganze Berechnung zu Fall bringen. Aber das wird wohl "irgendwo" so geregelt sein.
Gruss, Peter

301 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige