Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1584to1588
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

Teilergebnis in Matrixformel mit variablen Bezügen

Teilergebnis in Matrixformel mit variablen Bezügen
25.10.2017 09:41:37
Philipp
Hallo zusammen,
ich habe mal wieder eine Frage zu einer (aus meiner Sicht) komplexen Formel. Ich habe hierzu eine Beispieldatei hochgeladen.
https://www.herber.de/bbs/user/117201.xlsx
Ziel ist, die Werte in den Spalten C:D zu summieren, jedoch unter verschiedenen Bedingungen.
  • Zelle B4: =TEILERGEBNIS(9;D10:D20) Summe aus Spalte D.Funktioniert soweit.

  • Zelle B5: {=SUMME(WENN(C10:C20"";C10:C20;D10:D20))} Wenn in Spalte C ein Wert steht, dann ist dieser robuster als der Wert in Spalte D. Es soll also mit Spalte C weitergerechnet werden.

  • Zelle B6: {=SUMME(WENN(C10:C20"";C10:C20*(1-B10:B20);D10:D20*(1-B10:B20)))} Wie Zelle B5, nur mit Diskontierung durch Unsicherheit. Das heißt, wo ein Prozentwert eingetragen ist, soll nur der sichere Anteil (1-Unsicherheit) mit dem Wert in Spalte C bzw. D multipliziert werden und in die Summe eingehen.

  • Auf den ganzen Summenbereich bezogen funktionieren meine Formeln. Jedoch sollen die Summen auf Autofilter reagieren, also nur sichtbare Zellen summieren. Und da liegt mein Problem. Ich habe diesen Thread gefunden (https://www.herber.de/forum/archiv/992to996/993830_Summe_Wenn_Matrix_iVm_Teilergebnis.html), jedoch weiß ich nicht, wie ich hier den variablen Bezug aus Spalte C und D einbringen kann.
    Alternativlösung wäre eine Hilfsspalte mit =WENN(C10"";C10;D10) zu erzeugen, die mir in jeder Zeile den Wert ausgibt, mit dem gerechnet werden soll. Auf diese Hilfsspalte könnte ich dann das Summenprodukt mit Teilergebnis anwenden. Aber eine saubere Lösung sieht für mich anders aus.
    Habt ihr Ideen, wie ich das in einer Formel unterbringen kann?
    Vielen Dank im Voraus!

    8
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: es bedarf keiner klassischen Matrixformel ...
    25.10.2017 10:13:59
    ...
    Hallo Philipp,
    ... in B5: =SUMME(INDEX((TEILERGEBNIS(9;INDIREKT("D"&ZEILE(D9:D20)))*(C9:C20""));))
    und wenn ich Deine Angaben für B6 richtig interpretiere dann da so:
    =SUMME(INDEX(TEILERGEBNIS(9;INDIREKT("D"&ZEILE(D10:D20)))*(1-B10:B20)*(C10:C20+D10:D20*(C10:C20=""));))
    Gruß Werner
    .. , - ...
    AW: Nachtrag zur Formel in B6 ...
    25.10.2017 10:32:45
    ...
    Hallo,
    ... da ich momentan keinerlei Vorstellung habe, was da wirklich ermittelt werden soll aber mir _ der Ergebniswert mit meiner jetzigen Formel doch etwas unrealistisch erscheint, könnte ich mir noch vorstellen, dass in die Formel noch eine Bedingung ergänzend eingebaut werden müsste, so dass diese dann so endet:
    
    ...*(B10:B20>0);))
    

    Gruß Werner
    .. , - ...
    Anzeige
    AW: Nachtrag zur Formel in B6 ...
    25.10.2017 16:27:42
    Philipp
    Hallo Werner,
    Dankeschön für deinen Input. Leider führt deine Formel nicht zu meinem Ziel.
    Was deine Formel in B5 macht: Es wird die Spalte D summiert, wenn in Spalte C ein Wert steht.
    Was sie eigentlich machen soll: Sie soll jede sichtbare Zeile summieren, aber ich Abhängigkeit davon, ob in Spalte C etwas steht oder nicht, soll entweder Spalte C oder Spalte D in die Summe aufgenommen werden.
    In VBA wäre das so lösbar (quick&dirty):
    For iRow = 10 to 20
    If Cells(iRow,3)  "" Then
    dbSum = dbSum + Cells(iRow,3)
    Else
    dbSum = dbSum + Cells(iRow,4)
    End if
    Next iRow
    Cells(5,2) = dbSum
    
    Ich hoffe, dadurch wird es verständlicher. Hast du eine Idee, wie ich das mittels Formel umsetzen kann? Die VBA-Lösung ist mir nicht performant genug, weil die Datenbasis 500 Zeilen groß ist und dynamisch berechnet werden soll.
    Anzeige
    AW: nachgefragt ...
    25.10.2017 17:46:32
    ...
    Hallo Philipp,
    ... Deine Vorgabe zu B5 hab ich nicht richtig gelesen. Sorry.
    Meinst Du folgendes Ergebnis zu B5:
    =SUMME(INDEX(TEILERGEBNIS(109;INDIREKT("C"&ZEILE(C10:C20)))+TEILERGEBNIS(109;INDIREKT("D"&ZEILE(C10:C20)))*(C10:C20=0);))
    Aber schneller als eine VBA-Auswertung dürfte dies auch nicht sein, zumal sich INDIREKT() in der Formel für weitere Auswertungen in der Tabelle ungünstig auswirkt.
    Gruß Werner
    .. , - ...
    AW: nachgefragt ...
    27.10.2017 08:44:10
    Philipp
    Hallo Werner,
    das ist genau das, was ich gesucht habe! B5 funktioniert damit einwandfrei. Vielen Dank dafür!
    Damit ich selbst verstehe, wie es funktioniert und damit es für die Nachwelt auch dokumentiert ist, habe ich B6 so gelöst:
    =SUMME(INDEX(TEILERGEBNIS(109;INDIREKT("C"&ZEILE(C10:C20)))
    + TEILERGEBNIS(109;INDIREKT("D"&ZEILE(C10:C20)))*(C10:C20=0)
    - TEILERGEBNIS(109;INDIREKT("C"&ZEILE(C10:C20)))*(B10:B20)*(B10:B200)
    - TEILERGEBNIS(109;INDIREKT("D"&ZEILE(C10:C20)))*(B10:B20)*(B10:B200)*(C10:C20=0);))

    Ich habe die Formel testweise in die Live-Version eingebunden und da war es jetzt nicht so langsam.
    Wie meinst du es, dass sich INDIREKT ungünstig auswirkt? Die Zellen B4:B6 sind in meiner Mappe nur deklaratorisch. Sozusagen ein Dashboard über den aktuell gefilterten Stand. Die Zellen haben jeweils keinen Nachfolger, es werden auf dieser Basis also keine weiteren Berechnungen durchgeführt.
    Anzeige
    AW: nachgefragt ...
    27.10.2017 08:54:49
    Philipp
    Kurze Anpassung:
    Den Teil ((B10:B200) braucht es nicht, denn ansonsten wird einfach mit Null multipliziert, was zum gleichen Ergebnis kommt.
    Die Formel lautet damit:
    =SUMME(INDEX(TEILERGEBNIS(109;INDIREKT("C"&ZEILE(C10:C20)))
    + TEILERGEBNIS(109;INDIREKT("D"&ZEILE(C10:C20)))*(C10:C20=0)
    - TEILERGEBNIS(109;INDIREKT("C"&ZEILE(C10:C20)))*(B10:B20)
    - TEILERGEBNIS(109;INDIREKT("D"&ZEILE(C10:C20)))*(B10:B20)*(C10:C20=0);))

    AW: dazu ...
    27.10.2017 11:44:41
    ...
    Hallo Philipp,
    ... schön wenn meine Formel für B5 Dir jetzt geholfen hat. Deine entsprechend modifizierte Formel für B6 hab ich mir jetzt nicht noch mal genauer angeschaut.
    Zu meinem Hinweis bzgl. INDIREKT(),sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=170
    Gruß Werner
    .. , - ...
    Anzeige
    AW: dazu ...
    27.10.2017 13:11:04
    Philipp
    Super, dank dir!

    310 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige