Guten Morgen zusammen,
ich hatte im April 2021 schon mal ein Thema erstellt. Hierzu bekam ich eine Hilfe über Power Query, wobei ich sagen muss dass ich mich mit den Befehlsarten etwas schwer tue. Bei meiner Excel-Aufstellung handelt es sich um eine Jahresauswertung mit 2015 Datensätzen.
Spalte A (Startdatum)
Spalte B (Enddatum)
Spalte C (Wert)
Das Start- bzw. Enddatum haben pro Datensatz immer unterschiedliche Werte und somit unterschiedliche Anzahl an Zeiträumen (Tagen) zwischen Start- und Enddatum. Der Wert (Spalte C) muss dann in dem Zeitraum von Start- bis Enddatum mit eingerechnet werden.
Am Ende möchte ich eine Auswertung für das ganze Jahr erstellen könne, wo ich sehen kann an welchem Tag dieses Zeitraums ich den höchsten Wert vorweisen kann.
Vielen Dank im Voraus für eure Antworten und die Hilfe.
Hallo
meinst du das so?
| A | B | C | D | E | F | G | H | I |
| 1 | Startdatum | Enddatum | Wert | | | | Max | Start | Ende |
| 2 | 29.10.2021 | 05.01.2022 | 5.857,94 € | | | | 122.871,79 € | 10.10.2022 | 21.10.2022 |
| 3 | 01.11.2021 | 05.01.2022 | 3.369,45 € | | | | | | |
| 4 | 01.11.2021 | 01.12.2021 | 3.927,73 € | | | | | | |
| 5 | 01.11.2021 | 01.12.2021 | 3.788,33 € | | | | | | |
| 6 | 01.11.2021 | 01.12.2021 | 33.409,02 € | | | | | | |
| 7 | 01.11.2021 | 02.12.2021 | 3.788,33 € | | | | | | |
| 8 | 01.11.2021 | 02.11.2021 | 7.700,00 € | | | | | | |
| 9 | 01.11.2021 | 02.12.2021 | 1.016,04 € | | | | | | |
| Zelle | Formel |
| G2 | =MAX(C:C) |
| H2 | =INDEX(A:A;VERGLEICH($G2;$C:$C;0)) |
| I2 | =INDEX(B:B;VERGLEICH($G2;$C:$C;0)) |
Oder so?
| A | B | C | D | E | F | G | H | I | J | K | L | M |
| 1 | Startdatum | Enddatum | Wert | | | | Eingabe Betrachtungszeitraum: | | | | Ergebnis: | | |
| 2 | 29.10.2021 | 05.01.2022 | 5.857,94 € | | | | Von | Bis | | | Max | Start | Ende |
| 3 | 01.11.2021 | 05.01.2022 | 3.369,45 € | | | | 01.11.2021 | 10.11.2022 | | | 122.871,79 € | 10.10.2022 | 21.10.2022 |
| 4 | 01.11.2021 | 01.12.2021 | 3.927,73 € | | | | | | | | | | |
| 5 | 01.11.2021 | 01.12.2021 | 3.788,33 € | | | | | | | | | | |
| 6 | 01.11.2021 | 01.12.2021 | 33.409,02 € | | | | | | | | | | |
| Zelle | Formel |
| K3 | =MAXWENNS($C:$C;$A:$A;">="&G3;$B:$B;"<="&H3) |
| L3 | =INDEX(A:A;VERGLEICH($K3;$C:$C;0)) |
| M3 | =INDEX(B:B;VERGLEICH($K3;$C:$C;0)) |
Hallo,
danke für die schnelle Rückantwort. Nein, ich meine es etwas anders. An jedem Tag zwischen Startdatum und Enddatum muss ich den Wert aus Spalte C pro Datensatz voll mit einrechnen. Jetzt möchte ich wissen, an welchem Tag X innerhalb dieses Jahres ich den höchsten Wert rausbekomme.
Ich hab nochmal eine andere Datei mit weniger Datensätzen zur Veranschaulichung mitgeschickt.
Hi,
z.B. so, in B14, nach unten ziehen:
Code:
=SUMME($C$2:INDEX($C$2:$C$11;VERGLEICH(A14;$A$2:$A$11;1)))-SUMMEWENN($B$2:$B$11;"<"&A14;$C$2:$C$11)
anhand Deiner Originaltabelle sieht das Ganze allerdings ganz anders aus:
[
attachment=46234]
Hallo
per VBA in ein normales Modul
Code:
Sub Zusammenfassung()
Dim TB1 As Worksheet, TB2 As Worksheet, LR1 As Long, i As Long, LR2 As Long
Dim Datum As Date, MMax As Double, Zeile As Long, Am As String
Dim FirstAddr As String, C As Range
Set TB1 = Sheets("Tabelle1")
Set TB2 = Sheets("Tabelle2")
'Reset
With TB2
.UsedRange.ClearContents
.Cells(1, 1) = "Datum"
.Cells(1, 2) = "Summe"
.Cells(1, 5) = "Max"
.Cells(1, 6) = "An den Tagen"
End With
With TB1
LR1 = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte
For i = 2 To LR1
For Datum = .Cells(i, 1) To .Cells(i, 2)
' erste freie Zeile
LR2 = TB2.Cells(TB2.Rows.Count, "A").End(xlUp).Row + 1
'Ist Datum schon vorhanden
If WorksheetFunction.CountIf(TB2.Columns(1), Datum) = 0 Then
'noch nicht vorhanden, also anlegen
TB2.Cells(LR2, 1) = Datum
TB2.Cells(LR2, 2) = .Cells(i, 3)
Else
'bereits da in Zeile
Zeile = WorksheetFunction.Match(CDbl(Datum), TB2.Columns(1), 0)
TB2.Cells(Zeile, 2) = TB2.Cells(Zeile, 2) + .Cells(i, 3)
End If
Next
Next
End With
With TB2
MMax = Application.Max(.Columns(2))
.Cells(2, 5) = MMax
'Tage zusammensetzen
Set C = .Columns(2).Find(MMax, LookIn:=xlFormulas)
If Not C Is Nothing Then
FirstAddr = C.Address
Do
Am = Am & .Cells(C.Row, 1) & " / "
Set C = .Columns(2).FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddr
End If
'Letzte / weg und in Zelle schreiben
.Cells(2, 6) = Left(Am, Len(Am) - 3)
End With
End Sub
Ergibt dann:
| A | B | C | D | E | F |
| 1 | Datum | Summe | | | Max | An den Tagen |
| 2 | 01.01.2020 | 10.000,00 € | | | 60.000,00 € | 05.01.2020 / 06.01.2020 / 07.01.2020 / 08.01.2020 |
| 3 | 02.01.2020 | 10.000,00 € | | | | |
| 4 | 03.01.2020 | 20.000,00 € | | | | |
| 5 | 04.01.2020 | 40.000,00 € | | | | |
| 6 | 05.01.2020 | 60.000,00 € | | | | |
| 7 | 06.01.2020 | 60.000,00 € | | | | |
| 8 | 07.01.2020 | 60.000,00 € | | | | |
| 9 | 08.01.2020 | 60.000,00 € | | | | |
| 10 | 09.01.2020 | 40.000,00 € | | | | |
| 11 | 10.01.2020 | 40.000,00 € | | | | |
| 12 | 11.01.2020 | 10.000,00 € | | | | |
| 13 | 12.01.2020 | 10.000,00 € | | | | |
LG UweD
Mit Powerquery und Powerpivot kommt man ohne VBA auf folgendes Ergebnis
[
attachment=46235]
Hallo,
danke für die Antwort. Wäre es möglich die Excel Datei, die ohne VBA sondern mit PowerQuery bzw. PowerPivot erstellt wurde, einmal zu bekommen?
Vielen Dank.
Hab es hinbekommen. Danke nochmal an alle die mir geantwortet und geholfen haben.