Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.10.2025 13:56:55
Wenn Funktion mit Xverweis oder ähnlich
20.10.2025 10:29:26
Userform Multipage - Page auswählen
19.10.2025 21:57:06
Diagrammpunkte in unterschiedlichen Farben je nach Wert
19.10.2025 18:21:56
Prüfen, ob Bild geladen wurde
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1584to1588
1584to1588
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Teilergebnis in Matrixformel mit variablen Bezügen

Forumthread: 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!
    Anzeige

    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
    .. , - ...
    Anzeige
    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
    .. , - ...
    Anzeige
    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);))
    Anzeige
    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!
    ;
    Anzeige
    Anzeige

    Infobox / Tutorial

    Teilergebnis in Matrixformel mit variablen Bezügen

    Schritt-für-Schritt-Anleitung

    1. Daten vorbereiten: Stelle sicher, dass Deine Daten in den Spalten C und D korrekt eingegeben sind. Die Zellen C10 bis C20 und D10 bis D20 sollten die Werte enthalten, die du summieren möchtest.

    2. Teilergebnis in Zelle B4: Verwende die Formel 

      =TEILERGEBNIS(9;D10:D20)

      um die Summe der Spalte D zu berechnen. Diese Formel summiert nur die sichtbaren Zellen, wenn ein Autofilter angewendet wird.

    3. Matrixformel für B5: Um die Werte in B5 zu summieren, wenn in Spalte C ein Wert steht, nutze die Matrixformel

      {=SUMME(WENN(C10:C20<>"";C10:C20;D10:D20))}

      Diese Formel wird mit Strg + Shift + Enter eingegeben, damit sie als Matrixformel erkannt wird.

    4. Diskontierung in B6: Für die Diskontierung kannst Du folgende Formel verwenden:

      {=SUMME(WENN(C10:C20<>"";C10:C20*(1-B10:B20);D10:D20*(1-B10:B20)))}

      Diese Formel berücksichtigt die Unsicherheit, indem sie die Werte in C und D mit (1 - Unsicherheit) multipliziert.

    5. Bedingte Summierung: Um sicherzustellen, dass nur sichtbare Zeilen summiert werden, musst Du die Funktion TEILERGEBNIS zusammen mit INDIREKT verwenden:

      =SUMME(INDEX(TEILERGEBNIS(109;INDIREKT("C"&ZEILE(C10:C20))) + TEILERGEBNIS(109;INDIREKT("D"&ZEILE(D10:D20)))*(C10:C20=0);))

      Diese Formel summiert die Werte je nach Sichtbarkeit und den Bedingungen in Spalte C.

    Häufige Fehler und Lösungen

    • Fehler bei der Matrixformel: Stelle sicher, dass Du beim Eingeben der Matrixformel Strg + Shift + Enter verwendest. Andernfalls funktioniert die Formel nicht korrekt.

    • Falsche Ergebnisse: Überprüfe, ob die Zellen, auf die Du Dich beziehst, tatsächlich die Werte enthalten, die Du erwartest. Wenn die Spalten C oder D leer sind, kann dies zu unerwarteten Ergebnissen führen.

    • Leistungseinbußen bei INDIREKT: Beachte, dass die Verwendung von INDIREKT in großen Datenmengen die Performance negativ beeinflussen kann. Wenn Du eine große Datenbasis hast, erwäge, die Formeln so zu optimieren, dass sie effizienter sind.

    Alternative Methoden

    • Hilfsspalte: Eine einfache Möglichkeit, die Berechnungen zu optimieren, besteht darin, eine Hilfsspalte zu erstellen. Hier könntest Du mit einer einfachen WENN-Formel arbeiten:

      =WENN(C10<>"";C10;D10)

      Diese Hilfsspalte kann dann in Deinen Summenformeln verwendet werden.

    • Verwendung von VBA: Wenn die Formeln zu langsam sind, kann eine VBA-Lösung effizienter sein, insbesondere bei großen Datenmengen. Eine einfache Schleife könnte so aussehen:

      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

    Praktische Beispiele

    • Beispiel für Diskontierung: Angenommen, in Spalte C stehen die Werte 100, 200, 300 und in Spalte D die Werte 50, 150, 250. Mit der Diskontierungsformel summierst Du die Werte unter Berücksichtigung der Unsicherheitswerte in Spalte B.

    • Einfache Summenberechnung: Wenn Du nur die Summe der sichtbaren Zellen in Spalte D möchtest, genügt die Funktion:

      =TEILERGEBNIS(9;D10:D20)

    Tipps für Profis

    • Verwende die neuesten Excel-Funktionen: Wenn Du Excel 365 oder Excel 2021 nutzt, kannst Du die Funktion FILTER verwenden, um die sichtbaren Werte dynamisch zu erfassen.

    • Kombination von Funktionen: Experimentiere mit der Kombination von TEILERGEBNIS und SUMMEWENN für noch flexiblere Berechnungen.

    • Testen von Formeln: Teste Deine Formeln regelmäßig mit verschiedenen Datensätzen, um sicherzustellen, dass sie unter allen Bedingungen korrekt arbeiten.

    FAQ: Häufige Fragen

    1. Was ist der Unterschied zwischen TEILERGEBNIS(9) und TEILERGEBNIS(109)? Der Unterschied liegt darin, dass TEILERGEBNIS(9) die Summe der sichtbaren Zellen berechnet, während TEILERGEBNIS(109) die Summe der sichtbaren Zellen in einer Matrixformel verwendet.

    2. Wie kann ich Diskontierung in Excel berechnen? Du kannst Diskontierung in Excel berechnen, indem Du die Werte mit (1 - Unsicherheit) multiplizierst. Eine Beispiel-Formel könnte so aussehen:

    =SUMME(WENN(C10:C20<>"";C10:C20*(1-B10:B20);D10:D20*(1-B10:B20)))

    3. Wie kann ich sicherstellen, dass meine Formeln schnell genug sind? Vermeide die Verwendung von INDIREKT in großen Datenmengen und überlege, stattdessen Hilfsspalten oder VBA-Lösungen zu verwenden, um die Leistung zu verbessern.

    Beliebteste Forumthreads (12 Monate)

    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
    Anzeige
    Anzeige
    Entdecke mehr
    Finde genau, was du suchst

    Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

    Suche nach den besten Antworten
    Unsere beliebtesten Threads

    Entdecke unsere meistgeklickten Beiträge in der Google Suche

    Top 100 Threads jetzt ansehen
    Anzeige