Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.09.2025 22:18:24
Liste mit Links - tricky
26.09.2025 18:34:36
Welches Datum gibt mein M-Code aus?
26.09.2025 10:29:46
Wert aus einer Zelle bei jeder Eingabe in neue Zelle
26.09.2025 08:51:00
Teilinhalt einer Zelle vergleichen
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
1308to1312
1308to1312
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Kombination aus Mittelwertwenn und Teilergebnis

Forumthread: Kombination aus Mittelwertwenn und Teilergebnis

Kombination aus Mittelwertwenn und Teilergebnis
02.05.2013 10:07:46
SKA
Hallo,
ich habe eine Spalte mit Werten (K2:K1638), von denen ich den Durchschnitt ausrechnen möchte. Das Ergebnis soll sich beim Filtern nach Kriterien anpassen und der Durchschnitt soll nur von denjenigen Werten berechnet werden, in denen in der Nebenspalte (J2:J1638) ein Wert zutrifft.
Kurz: ich suche eine Formel, die mir folgendes kombiniert:
- Mittelwertwenn
- Teilergebnis
Ich hatte bereits eine ähnlich Formel mit der ich die Anzahl der Werte in der Nebenspalte (I) gezählt habe, jedoch schaffe ich es leider nicht, diese so umzubauen, dass ich das obrige Problem lösen kann.
{=SUMME(TEILERGEBNIS(3;INDIREKT("I"&ZEILE(2:1638)))*(I2:I1638=I1671))}
Vielen Dank für Eure Tipps ;)
Sonja

Anzeige

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Kombination aus Mittelwertwenn und Teilergebnis
02.05.2013 12:56:33
fcs
Hallo Sonja,
eine entsprechende Matrix-Formel sieht so aus: 
=SUMME(TEILERGEBNIS(3;INDIREKT("J"&ZEILE(2:1638)))*(J2:J1638=J1648)*(K2:K1638)) /
SUMME(TEILERGEBNIS(3;INDIREKT("J"&ZEILE(2:1638)))*(J2:J1638=J1648))

Die Zelle J1648 in der Formel musst du ggf. anpassen.
Als Alternative zur Formellösung kommt für die Auswertung von Listen auch ein Pivottabellenbericht in Frage. Dann brauchst du dir nicht den Kopf über Formeln zerbrechen, sondern musst nur die Filter entsprechend setzen (ähnlich wie im Autofilter).
Gruß
Franz

Anzeige
AW: Kombination aus Mittelwertwenn und Teilergebnis
02.05.2013 14:44:35
SKA
Hallo Franz,
vielen Dank, so funktioniert die Berechnung :)
Ich habe noch eine ähnliche Frage gepostet: https://www.herber.de/forum/archiv/1308to1312/t1311124.htm, wo ich die Berechnung nach dem gleichen Prinzip durchführen möchte, ich jedoch nur den Durchschnitt berechnen möchte, wenn in einer anderen Spalte ein Text enthalten ist. Ich würde die Formel so umbauen:
=SUMME(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:1638)))*(B2:B1638="")*(AG2:AG1638)) /SUMME(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:1638)))*(B2:B1638=""))
Doch leider funktioniert die Berechnung nicht. Ich weiß leider nicht, wo mein Denkfehler ist :( vielleicht kannst du mir da weiterhelfen?
Vielen Dank
Sonja
PS: Vielen Dank für den Tipp mit der Pivot-Tabelle. Habe auch schon daran gedacht, jedoch denke ich, dass die Anzahl der Berechnungen und Abhängigkeiten so umfangreich sind, dass mir das in einer Tabelle leichter fällt (auch wenn ich Schwierigkeiten mit den Formeln habe ;) )

Anzeige
AW: Kombination aus Mittelwertwenn und Teilergebnis
02.05.2013 15:16:48
fcs
Hallo Sonja,
eine der beiden folgenden Matrix-Formelvarianten sollte funktionieren. Du muss in deinen Formeln also nur die Prüfbedingungen etwas anpassen. 
Formel 1:
=SUMME(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:1638)))*(B2:B1638"")*(AG2:AG1638))
/SUMME(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:1638)))*(B2:B1638""))
Formel 2:
=SUMME(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:1638)))*ISTTEXT(B2:B1638)*(AG2:AG1638))
/SUMME(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:1638)))*ISTTEXT(B2:B1638))
Formel 1 berücksichtigt alle Zellen, die ungleich Leerstring sind
Formel 2 berücksichtigt nur die Zellen mit Textinhalten (Leerstring "" ist auch Text!) und ignoriert alle Leerzellen und Zellen mit numerischen Werten(Zahlen, Datum, WAHR, FALSCH).
Gruß
Franz
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Kombination von Mittelwertwenn und Teilergebnis in Excel

Schritt-für-Schritt-Anleitung

Um den Mittelwert aus einer Spalte zu berechnen, während Du bestimmte Kriterien berücksichtigst und die Filterung anwendest, kannst Du die Kombination von Mittelwertwenn und Teilergebnis nutzen. Hier ist eine einfache Anleitung:

  1. Öffne Deine Excel-Tabelle und stelle sicher, dass Deine Daten in den Spalten J (Kriterien) und K (Werte) liegen.

  2. Gib die folgende Matrix-Formel in eine Zelle ein:

    =SUMME(TEILERGEBNIS(3;INDIREKT("J"&ZEILE(2:1638)))*(J2:J1638=J1648)*(K2:K1638)) /
SUMME(TEILERGEBNIS(3;INDIREKT("J"&ZEILE(2:1638)))*(J2:J1638=J1648))
    • Dabei musst Du J1648 anpassen, um das spezifische Filterkriterium zu setzen.
  3. Drücke Strg + Shift + Enter, um die Formel als Matrix-Formel einzugeben.

Diese Methode ermöglicht es Dir, den Mittelwert nur für die Daten anzuzeigen, die Deinen festgelegten Kriterien entsprechen.

Häufige Fehler und Lösungen

  • Fehler: Die Formel gibt einen Fehler aus.

    • Lösung: Überprüfe, ob Du die Formel als Matrix-Formel eingegeben hast (Strg + Shift + Enter) und ob die Zellreferenzen korrekt sind.

  • Fehler: Der Mittelwert ist nicht korrekt.

    • Lösung: Stelle sicher, dass die Kriterien in der Nebenspalte (z.B. Spalte J) tatsächlich mit dem Filterkriterium übereinstimmen.

Alternative Methoden

Eine weitere Möglichkeit, den Mittelwert zu berechnen, besteht darin, eine Pivot-Tabelle zu verwenden. Hier kannst Du die gewünschten Spalten einfach per Drag-and-Drop an die entsprechenden Bereiche ziehen und die Filter dann direkt anwenden. So musst Du Dir keine Gedanken über komplexe Formeln machen.

Praktische Beispiele

Beispiel 1: Mittelwert für Verkaufszahlen

Angenommen, Du hast eine Liste von Verkaufszahlen in Spalte K und die dazugehörigen Regionen in Spalte J. Um den Mittelwert der Verkaufszahlen für eine bestimmte Region zu berechnen, verwendest Du die oben angegebene Formel und ersetzt die Zellreferenzen entsprechend.

Beispiel 2: Mittelwert für Textinhalte

Wenn Du nur den Durchschnitt berechnen möchtest, wenn in einer anderen Spalte ein Text enthalten ist, passe die Formel wie folgt an:

=SUMME(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:1638)))*ISTTEXT(B2:B1638)*(AG2:AG1638)) /
SUMME(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:1638)))*ISTTEXT(B2:B1638))

Hierbei wird nur der Mittelwert der Zellen in Spalte AG berechnet, die mit textbasierten Werten in Spalte B übereinstimmen.

Tipps für Profis

  • Verwendung von Namensbereichen: Du kannst Namensbereiche für Deine Daten definieren, um Formeln übersichtlicher zu gestalten und die Wartung zu erleichtern.
  • Datenvalidierung: Stelle sicher, dass Deine Daten konsistent sind, um Fehler bei der Berechnung des Mittelwerts zu minimieren.
  • Vermeidung von Leerzeilen: Achte darauf, dass keine Leerzeilen in den Daten vorhanden sind, da dies die Berechnung des Teilergebnis beeinflussen kann.

FAQ: Häufige Fragen

1. Wie kann ich die Formel anpassen, um mehrere Kriterien zu berücksichtigen?
Du kannst die Formel erweitern, indem Du zusätzliche Bedingungen hinzufügst, z.B. *(B2:B1638="Kriterium2").

2. Funktioniert diese Methode in älteren Excel-Versionen?
Die beschriebenen Funktionen sind in Excel 2010 und späteren Versionen verfügbar. Bei älteren Versionen kann es zu Einschränkungen kommen.

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