Knacknuss - Summierung über mehrere Zeitpunkte

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

Knacknuss - Summierung über mehrere Zeitpunkte

Knacknuss - Summierung über mehrere Zeitpunkte von Adrian vom 05.08.2014 17:49:10

AW: Knacknuss - Summierung über mehrere Zeitpunkte - von Johann am 05.08.2014 18:28:56
AW: Knacknuss - Summierung über mehrere Zeitpunkte - von Christian am 05.08.2014 18:41:27
- hier könnte man alternativ auch mehr WENN()en ... - von neopa C (paneo) am 05.08.2014 19:26:53
  - ich hatte auch darauf vertraut... - von Christian am 05.08.2014 19:54:20
  - AW: hier könnte man alternativ auch mehr WENN()en ... - von Adrian am 06.08.2014 11:08:38
    - AW: hier könnte man alternativ auch mehr WENN()en ... - von Johann am 06.08.2014 12:04:27
    - Du hast jetzt eine etwas andere Datenstruktur .... - von neopa C (paneo) am 06.08.2014 12:20:37
      - AW: Du hast jetzt eine etwas andere Datenstruktur .... - von Adrian am 06.08.2014 12:43:30
      - AW: Du hast jetzt eine etwas andere Datenstruktur .... - von Adrian am 06.08.2014 12:43:42
        
        weil ich Hunger hatte ... ;-) - von neopa C (paneo) am 06.08.2014 12:55:23
        
        AW: weil ich Hunger hatte ... ;-) - von Adrian am 06.08.2014 12:59:19

Betrifft: Knacknuss - Summierung über mehrere Zeitpunkte von: Adrian
Geschrieben am: 05.08.2014 17:49:10

Liebe Alle,

ich bin nahe daran, an einer echten Knacknuss zu verzweifeln und weiss einfach nicht, wie ich diese lösen kann.

Könnt ihr mir vielleicht helfen?

Ich habe einen Input-Table mit Risiken und dazugehörigen Risikohöhen, die "zeitgestempelt" sind (d.h. jeweils ein Anfangs- und ein Enddatum besitzen). Die Risikohöhe desselben Risikos kann also mehrere Male ändern im Verlauf der Zeit. Bei jeder Änderung wird eine neue Zeile erstellt (mit Anfangsdatum).

Ein benötigter Output bezieht sich auf eine Übersicht, die zu jedem Zeitpunkt zeigt, wieviele Risiken in der Input-Liste vorhanden (gesondert nach Risikohöhe).

Ein Beispiel habe ich angefügt:
https://www.herber.de/bbs/user/91916.xlsx

Ich krieg es nicht mal ansatzweise hin, diesen Output-Table ohne VBA zu erstellen. Gibt es eventuell eine simple Lösung mit VBA? Dies würde mir wirklich sehr, sehr helfen!

Danke 1000x für Eure Hilfe!

LG,
Adrian

Betrifft: AW: Knacknuss - Summierung über mehrere Zeitpunkte von: Johann
Geschrieben am: 05.08.2014 18:28:56

Hallo Adrian,

habe mir erlaubt, die OKs in Einsen zu ändern.

https://www.herber.de/bbs/user/91919.xlsx

Viele Grüße
Johann

Betrifft: AW: Knacknuss - Summierung über mehrere Zeitpunkte von: Christian
Geschrieben am: 05.08.2014 18:41:27

Hallo Adrian,

ein Ansatz:

	H	I	J	K
4		Tief	Mittel	Hoch
5	04.08.2014	0	2	0
6	05.08.2014	3	2	1
7	10.08.2014	5	2	1
8	23.08.2014	5	3	1
9	01.09.2014	4	2	1
10	05.09.2014	2	3	0
11	31.12.9999	1	3	0
12
13

Formeln der Tabelle

Zelle	Formel
H5	=MIN(C:D)
I5	=SUMMENPRODUKT(($H5>=$C$5:$C$17)($H5<=$D$5:$D$17)($E$5:$E$17=I$4))
H6	{=WENN(ZEILE(A2)>SUMME(1/ZÄHLENWENN($C$5:INDEX(D:D;ANZAHL(D:D)+4); $C$5:INDEX(D:D;ANZAHL(D:D)+4))); "";MIN(WENN($C$5:$D$1000>H5*($C$5:$D$1000<>""); $C$5:$D$1000)))}
I6	=SUMMENPRODUKT(($H6>=$C$5:$C$17)($H6<=$D$5:$D$17)($E$5:$E$17=I$4))

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

MfG Christian

Betrifft: hier könnte man alternativ auch mehr WENN()en ... von: neopa C (paneo)
Geschrieben am: 05.08.2014 19:26:53

Hallo Christian,

... im konkreten Fall z.B. etwas einfacher wäre in H6:

{=WENN(MAX(D:D)=MAX(H$5:H5);"";WENN(MAX(C:C)=MAX(H$5:H5);MAX(D:D);MIN(WENN($C$5:$C$999>H5; $C$5:$D$999)))) }

Gruß Werner
.. , - ...

Betrifft: ich hatte auch darauf vertraut... von: Christian
Geschrieben am: 05.08.2014 19:54:20

Hallo Werner,

... dass von Dir noch eine bessere und kürzere Lösung kommt! ;-)

MfG Christian

Betrifft: AW: hier könnte man alternativ auch mehr WENN()en ... von: Adrian
Geschrieben am: 06.08.2014 11:08:38

Liebe Alle,

erst einmal ganz herzlichen Dank für die sehr nützlichen Tipps!

Leider funktionieren beide Formeln (Christian / Werner) nicht ganz so, wie ich ursprünglich beabsichtigt habe, was wohl an meiner schlechten Erklärung der Anforderung liegt.

Im angehängten Beispiel habe ich den Input-Table noch um einige neuen Risiken (und folglich neuen Daten) ergänzt, die nun mit den vorgeschlagenen Formeln im Output-Table in Kolonne A leider nicht alle angezeigt werden:

https://www.herber.de/bbs/user/91931.xlsx

Sowohl aus der Kolonne Startdatum als auch aus der Kolonne Enddatum müssen alle explizit aufgeführten Daten jeweils einmal in EINER Kolonne eingetragen und absteigend sortiert werden. Dies schafft die vorgeschlagene Formel leider nicht, da sie gewisse nur in der Kolonne Enddatum vorhandene Daten (die aber nicht in der Kolonne Startdatum sind) nicht übernimmt.

Ebenfalls stimmt leider etwas mit der Summierung im Output-Table nicht. Ich habe von Hand gezeigt, wie das korrekte Resultat aussehen müsste. Die verwendete Logik habe ich im Excel-File beschrieben.

Könnt ihr mir noch einmal helfen? Wäre wirklich super lieb von Euch!

LG und einen schönen Tag!

Adrian

Betrifft: AW: hier könnte man alternativ auch mehr WENN()en ... von: Johann
Geschrieben am: 06.08.2014 12:04:27

Hallo Adrian,

vielleicht stehe ich ja auf dem Schlauch ^^,

ändere einfach kleiner gleich in kleiner in der Summenproduktformel, um den gewünschten Output zu erhalten

Betrifft: Du hast jetzt eine etwas andere Datenstruktur .... von: neopa C (paneo)
Geschrieben am: 06.08.2014 12:20:37

Hallo Adrian,

... (und auch teils andere Formeln genommen).
Formel A2 einfach nach unten kopieren, ergibt die von Dir gewünschte Reihenfolge:

Output

	A
1
2	04.08.2014
3	05.08.2014
4	10.08.2014
5	23.08.2014
6	01.09.2014
7	05.09.2014
8	29.11.2014
9	30.11.2014
10	31.12.9999

Formeln der Tabelle

Zelle	Formel
A2	=MIN(Input!B:C)
A3	{=WENN(MAX(Input!C:C)=MAX(A$2:A2); "";WENN(MAX(Input!B:B)=MAX(A$2:A2); MAX(Input!C:C); MIN(WENN(Input!B$2:C$999>A2;Input!B$2:C$999))))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Input

	A	B	C
1	Risiko Name	Startdatum	Enddatum
2	Risiko1	05.08.2014	10.08.2014
3	Risiko1	10.08.2014	23.08.2014
4	Risiko1	23.08.2014	01.09.2014
5	Risiko2	04.08.2014	31.12.9999
6	Risiko3	05.08.2014	23.08.2014
7	Risiko3	23.08.2014	31.12.9999
8	Risiko4	04.08.2014	05.08.2014
9	Risiko4	05.08.2014	10.08.2014
10	Risiko4	10.08.2014	01.09.2014
11	Risiko5	23.08.2014	31.12.9999
12	Risiko6	05.08.2014	01.09.2014
13	Risiko7	10.08.2014	05.09.2014
14	Risiko7	05.09.2014	31.12.9999
15	Risiko8	30.11.2014	31.12.2014
16	Risiko9	29.11.2014	30.11.2014
17

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...

Betrifft: AW: Du hast jetzt eine etwas andere Datenstruktur .... von: Adrian
Geschrieben am: 06.08.2014 12:43:30

Lieber Werner,

einmal mehr ein riesiges "MERCI"!

Ihr seid wirklich alle phänomenal!

Die Sache passt schon fast perfekt! Nur das jeweils zweitletzte Datum (in diesem Fall der 31.12.2014) wird leider nicht im Output angezeigt mit der neuen Formel. Woran könnte das liegen?

Viele Grüsse und einen sonnigen Nachmittag,
Adrian

Betrifft: AW: Du hast jetzt eine etwas andere Datenstruktur .... von: Adrian
Geschrieben am: 06.08.2014 12:43:42

Lieber Werner,

einmal mehr ein riesiges "MERCI"!

Ihr seid wirklich alle phänomenal!

Die Sache passt schon fast perfekt! Nur das jeweils zweitletzte Datum (in diesem Fall der 31.12.2014) wird leider nicht im Output angezeigt mit der neuen Formel. Woran könnte das liegen?

Viele Grüsse und einen sonnigen Nachmittag,
Adrian

Betrifft: weil ich Hunger hatte ... ;-) von: neopa C (paneo)
Geschrieben am: 06.08.2014 12:55:23

Hallo Adrian,

... und zum ME wollte. Jetzt frisch gestärkt, hab ich gesehen, dass ich noch ein Rudiment aus meiner gestrigen Formel mitgeschleppt habe, welches gar nicht mehr nötig ist und dies außerdem zu dem von Dir festgestellten führt.

Als Formel in A3 reicht gemäß Deiner Heute aktualisierten Angaben:

{=WENN(MAX(Input!B:C)=MAX(A$2:A2);"";MIN(WENN(Input!B$2:C$999>A2;Input!B$2:C$999))) }

und nach unten kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: weil ich Hunger hatte ... ;-) von: Adrian
Geschrieben am: 06.08.2014 12:59:19

You're the man!

Danke vielmal! Alles läuft perfekt!

Cheers,
Adrian