Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
900to904
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
900to904
900to904
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Formel: Von Wochentag zum letzt. Tag (Vorwoche)

Formel: Von Wochentag zum letzt. Tag (Vorwoche)
30.08.2007 22:45:21
proxima05
Hallo,
ich habe da ein Problem. Ich habe eine Datenreihe, die täglich um eine Zeile wächst; darin enthalten sind nur Datumwerte in Spalte A mit dem entsprechenden Wert in Spalte B. Es sind nur Wochentage verfügbar, keine Wochenenden.
Ausgehend von dem aktuellsten Datum (z.B. heute Do, 30.08.2007) würde ich mir gerne über eine Funktion den Wert des letzten Tages der Vorwoche anzeigen lassen (z.B. Fr, 24.08.2007 .... wenn der nicht verfügbar ist, dann Do, 23.08.2007 etc.).
Kann mir da jemand einen Tipp geben.
Ich sage schon mal allerbesten Dank.
Gruß

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel: Von Wochentag zum letzt. Tag (Vorwoche)
30.08.2007 23:03:00
Josef
Hallo Ralph,
meinst du so?
Tabelle1

 ABCDEFG
1TagWert  Letzer Wert Vorwoche  
2Mo   20.08.2007221  TagWert 
3Di     21.08.2007225  Do  23.08.2007172 
4Mi    22.08.2007204     
5Do    23.08.2007172     
6Mo   27.08.2007155     
7Di     28.08.2007160     
8Mi    29.08.2007223     
9Do    30.08.2007211     
10       

Formeln der Tabelle
ZelleFormel
E3{=MAX(WENN(A2:A100<(MAX(A2:A100)-WOCHENTAG(MAX(A2:A100); 2)); A2:A100))}
F3=INDEX(B2:B100;VERGLEICH(E3;A2:A100;0))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Excel Tabellen im Web darstellen  Excel Jeanie HTML
Gruß Sepp

Anzeige
AW: Formel: Von Wochentag zum letzt. Tag (Vorwoche
30.08.2007 23:10:46
Siegfried
WOCHENTAG( HEUTE()) ergibt für heute Donnerstag den Wert 5.
Im Allgemeinen ergibt DATUM - (WOCHENTAG ( DATUM ) + 1 ) das Datum des letzen Freitag, ein paar Anpassungen sind ev. erforderlich
lg
Siegfried

AW: Formel: Von Wochentag zum letzt. Tag (Vorwoche
31.08.2007 09:48:00
Gert
Hallo "unbekannt" & Siegfried,
da bei uns der erste "Wochentag" der "Montag" ist, sollte die Formel so aussehen :
=WOCHENTAG(HEUTE();2)
siehe Excel-Hilfe zur Funktion "Wochentag".
mfg
Gert

AW: Formel: Von Wochentag zum letzt. Tag (Vorwoche)
31.08.2007 10:22:00
ingUR
Hallo, @proxima05,
ausgehend von dem Ansatz, den Siegfreid vorgestellt hat, kannst Du mit INDIREKT und VERGLEICH direkt den Schlußwert der letzten Woche benutzen:
 
 ABCD
1DatumcloseL/LW%Tag
226.11.19901.443,20#NVMo
327.11.19901.415,30#NVDi
428.11.19901.420,60#NVMi
529.11.19901.418,90#NVDo
630.11.19901.441,20#NVFr
703.12.19901.462,601,48%Mo
804.12.19901.446,300,35%Di
905.12.19901.471,002,07%Mi
1006.12.19901.504,704,41%Do
1107.12.19901.512,804,97%Fr
1210.12.19901.504,80-0,53%Mo
1311.12.19901.492,70-1,33%Di
1412.12.19901.517,200,29%Mi
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Hier wird in der Saplte C die Veränderung bezogen auf den vorherigen Wochenschlußwert berechnet mit der Formel:
=B2/INDIREKT("B"&VERGLEICH(A2-(WOCHENTAG(A2)+1);A$2:A2)+1)-1
In dier Formel liefert der Formelteil INDIREKT("B"&VERGLEICH(A2-(WOCHENTAG(A2)+1);A$2:A2)+1 den Schlußwert aus der letzten Woche.
Für das Osterwochenende des Jahres 2007 sieht der Tabelelnteil dan so aus:
 
 ABCD
412528.03.20076.816,89-1,19%Mi
412629.03.20076.897,08-0,03%Do
412730.03.20076.917,030,26%Fr
412802.04.20076.937,170,29%Mo
412903.04.20077.045,561,86%Di
413004.04.20077.073,912,27%Mi
413105.04.20077.099,912,64%Do
413210.04.20077.166,670,94%Di
413311.04.20077.152,830,75%Mi
413412.04.20077.142,950,61%Do
413513.04.20077.212,071,58%Fr
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Für den Mittwoch wird berechnet: 7152,83/7099,91-1=0,75%, wobei =INDIREKT("B"&VERGLEICH(A4133-(WOCHENTAG(A4133)+1);A$2:A4133)+1) den Wert 7099,91 liefert.
Gruß,
Uwe

Anzeige
Danke Allen für Input .... melde mich am WE (owT)
31.08.2007 21:42:39
proxima05
....

Klappt alles bestens, aber noch eine Frage (VBA)
02.09.2007 01:15:00
proxima05
Hallo,
ich habe mal die verschiedenen Lösungen durchprobiert. Sie funktionieren allesamt, am besten für meine Zwecke ist die von Josef.
In der Zelle E3 steht das ermittelt Datum:
{=MAX(WENN(A2:A100<(MAX(A2:A100)-WOCHENTAG(MAX(A2:A100); 2)); A2:A100))}
In der Zelle F3 der zugehörige Wert:
=INDEX(B2:B100;VERGLEICH(E3;A2:A100;0))
Die Frage, die ich jetzt noch habe ist, wie kann ich diese beiden Formeln in einen VBA-Code umsetzen. Die Formel macht mir dabei nicht das Problem, aber der Aufbau des Codes (erst Datum ermitteln, dann zugehörigen Wert auslesen und wegschreiben).
Wäre klasse, wenn da noch jemand einen Tipp hätte.
Ansonsten, allerbesten Dank für die tollen Lösungsvorschläge.
Gruß
Ralph

Anzeige
AW: Klappt alles bestens, aber noch eine Frage (VBA)
02.09.2007 10:53:00
Josef
Hallo Ralph,
probier mal. (Bezieht sich auf mein Beispiel)
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Sub LetzterVorwoche()
Dim objSh As Worksheet
Dim rng As Range, r As Range
Dim dblMax As Double, dblMin As Double

Set objSh = Sheets("Tabelle1") 'Tabellenname anpassen!

With objSh
    
    Set rng = .Range("A2:A" & Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, 2))
    
    dblMax = Application.Max(rng)
    dblMax = dblMax - Weekday(dblMax, vbSunday)
    dblMin = Application.Min(rng)
    
    Do While dblMax > dblMin
        
        Set r = rng.Find(what:=CDate(dblMax), LookIn:=xlFormulas, lookat:=xlWhole)
        
        If Not r Is Nothing Then
            .Range("E3") = r
            .Range("F3") = r.Offset(0, 1)
            Exit Do
        End If
        
        dblMax = dblMax - 1
        
    Loop
    
End With

Set r = Nothing
Set rng = Nothing
Set objSh = Nothing
End Sub

Gruß Sepp

Anzeige
Josef, danke ... komme Lauf der Woche zurück (owT)
02.09.2007 21:59:30
proxima05
...

Klappt wieder, aber jetzt kommt die Gier ;-)
03.09.2007 21:37:00
proxima05
Hallo Josef,
Dein VBA-Code funktioniert vortrefflich. Jetzt würde ich diesen aber gerne in einem etwas größeren
Zusammenhang verwenden. Worum geht's?
In einer Tabelle berechne ich für einen bestimmten Zeitraum (z.B. 10 Tage) eine bestimmte Standardabweichung.
In dieser Datenbereich lese ich dann mit Deinem Code den letzten Wert der Vorwoche aus. Soweit, so gut!
Jetzt benötige ich aber nicht nur die Standardabweichung über 10 Tage, sondern über 20, 30, 45, 90 und
100 Tage ... für die jeweiligen Ergebnisse soll dann wieder der letzte Wert der Vorwoche ausgelesen werden. Da ich aber nicht für jede STABW-Länge ein eigenes Tabellenblatt machen möchte, um die Berechnung und Handhabbarkeit nicht erheblich zu verschlechtern, stelle ich mir folgendes vor:
Die Formel für die STABW ist die folgende:
=WENN(A103<>"";STABW(L103:BEREICH.VERSCHIEBEN(L103;-MIN($S$7;ANZAHL($L$3:L103));0));"")
Dabei steht in $S$7 der Wert der Länge (10, 20, 30, ... etc.)
Idee:
[1] Mit einem VBA-Code wird die Länge ersetzt, die Berechnung der neuen STABW durchgeführt,
[2] sobald Blatt berechnet ist, mit Deinem VBA-Code den relevanten Vorwochenwert auslesen und in ein separate Blatt schreiben,
[3] die Länge des Codes durch die nächste ersetzen, Blatt berechnen lassen,
[4] [2] wiederholen,
[5] usw.
Letztlich scheint das nichts anderes zu sein, als ein wiederholtes Durchlaufen der Formel mit unterschiedlichen Längen, und zwischen jedem Ändern der Länge, erfolgt ein Auslesen eines bestimmten Wertes.
Kannst Du mir da einen Tipp geben.
Verbindlichsten Dank für diese und auf jeden Fall die bisherige tolle Unterstützung.
Gruß
Ralph
Anzeige

182 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige