Microsoft Excel

Herbers Excel/VBA-Archiv

Knacknuss - Summierung über mehrere Zeitpunkte

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:

 HIJK
4 TiefMittelHoch
504.08.2014020
605.08.2014321
710.08.2014521
823.08.2014531
901.09.2014421
1005.09.2014230
1131.12.9999130
12    
13    

Formeln der Tabelle
ZelleFormel
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 
204.08.2014
305.08.2014
410.08.2014
523.08.2014
601.09.2014
705.09.2014
829.11.2014
930.11.2014
1031.12.9999

Formeln der Tabelle
ZelleFormel
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

 ABC
1Risiko NameStartdatumEnddatum
2Risiko105.08.201410.08.2014
3Risiko110.08.201423.08.2014
4Risiko123.08.201401.09.2014
5Risiko204.08.201431.12.9999
6Risiko305.08.201423.08.2014
7Risiko323.08.201431.12.9999
8Risiko404.08.201405.08.2014
9Risiko405.08.201410.08.2014
10Risiko410.08.201401.09.2014
11Risiko523.08.201431.12.9999
12Risiko605.08.201401.09.2014
13Risiko710.08.201405.09.2014
14Risiko705.09.201431.12.9999
15Risiko830.11.201431.12.2014
16Risiko929.11.201430.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


 

Beiträge aus den Excel-Beispielen zum Thema "Knacknuss - Summierung über mehrere Zeitpunkte"