Office: Summenprodukt mit Teilergebnis ohne volatile Funktionen möglich?

Guten Tag Zusammen,

ich habe vor einiger Zeit eine Excel Datei für eine Auswertung zusammengebaut, eine vereinfachte Form habe ich angehängt.

Im Prinzip gibt es folgende Spalten:
[A] Lieferant
Menge
[C] Einzelpreis

Ich wollte in [C1] die Gesamtsumme abhängig von der Filterung ermitteln. Wird in Spalte [A] nur ein Lieferant ausgewählt, habe ich auch nur dessen Gesamtsumme in [C1].
Das ganze sollte möglichst ohne Hilfsspalten auskommen, da die tatsächliche Exceldatei wesentlich größer ist und sich Hilfsspalten nicht ohne weiteres integrieren lassen.
Dies habe ich mit meiner Formel auch so geschafft:
=WENNFEHLER(SUMMENPRODUKT(TEILERGEBNIS(6;INDIREKT("C"&ZEILE(3:30));INDIREKT("B"&ZEILE(3:30))));0)

Allerdings habe ich mit "INDIREKT" eine volatile Funktion verwendet, die ich gerne vermeiden möchte.

Ich habe schon verschiedene Funktionen ausprobiert (MATRIX, AGGREGAT), aber keine scheint zu funktionieren.

Seht Ihr hier eine Möglichkeit dies ohne volatile Funktion umzusetzen?

Kriterien:Möglichst ohne Hilfsspalte, Ausgeblendete Zeilen ingorieren (also Autofilter berücksichtigen), ohne volatile Funktion.

:)

 

Hallo,

Es genügt ja eine einzige Hilfsspalte, um etliche Berechnungen auszuführen.

Hilfsspalte "Sichtbar" (ganz ans Ende...):
Code:

Dann kannst Du recht einfach gestrickte Formeln anwenden, z. B.:
Code:

[Edit]
Lästigerweise fliegt bei dieser Methode immer die letzte Zeile aus dem Filterbereich raus - egal ob Liste oder Blattbereich (Excel 2013).
Kann das einer auch in anderen Versionen bestätigen?

Grüße
EarlFred

 

Hallo EarlFred,

die Lösung funktioniert bei mir in der realen Tabelle. Vielen Dank dafür.
Kennst du noch eine Lösung ohne die Hilfsspalte?

Grüße

smax

 

Hallo smax,

eine nicht-volatile Formel auf den sichtbaren Bereich eines gefilterten Bereiches macht keinen Sinn und kann es auch nie geben!
Eine nicht-volatile Formel rechnet sich nur dann neu, wenn sich in den Zellen auf die sie sich bezieht inhaltlich etwas geändert hat. Durch die veränderte Einstellung eines Filters hat sich aber kein Zellinhalt verändert, also kann sich eine nicht-volatile Formel auch nicht neu berechnen!
Da das Verändern eines Autofilters bereits zu den auslösenden Aktionen einer volatilen Formel gehört, funktionieren volatile Formeln wunderbar auf veränderte sichtbare Bereiche und sind zwingendes Element für eine solchige Formel, wie Du sie gerne möchtest!

Übrigens: TEILERGEBNIS gehört auch zu den volatilen Formeln!

LG Ingo

 

Hallo Ingo,

gut erklärt!

Strenggenommen richtig, aber vergleiche mal das geradezu hysterische Verhalten von Indirekt() mit dem vom gutmütigen Teilergebnis(). Letzteres rechnet tatsächlich auch ohne Wertänderung in einer Zelle neu, aber nicht "wahllos" bei jeder Aktion. Da ist Indrekt() deutlich schneller dabei, sich neu zu berechnen. Insofern kommt das dem Wunsch des TE vielleicht schon recht nahe.

Grüße
EarlFred

 

Moin EarlFred!
Vielleicht verstehe ich dies ja falsch

Aber wie willst Du die "Gutmütigkeit" von Teilergebnis verifizieren, wenn Du nicht eine "wahllose" volatile Funktion wie JETZT() zur Überprüfung zugrunde legst?

Gruß Ralf

 

Habe es mir selbst beantwortet. *rolleyes.gif*
Das Filtern löst ein _Calculate aus.
Ein beliebiges Füllen einer x-beliebigen Zelle bei ausschließlichem Vorhandensein von Teilergebnis() nicht.
(Währenddessen Indirekt auf alles hüpft, was nicht bei drei auf dem Baum ist)

Sorry für die blöde Nachfrage!

 

Das Indirekt() in meiner Formel löst bei jedem öffnen der Excel Mappe ein Calculate aus, was dazu führt, dass bei jedem Schließen die Speichern Abfrage kommt. Das ist extrem nervig wenn man am Tag hunderte Male mit dieser Liste arbeitet, aber nur im Bruchteil der Fälle wirklich was verändert.
Aus diesem Grund wollte ich das Indirekt() los werden. Evtl. hätte ich das auch gleich im ersten Post so auf den Punkt bringen sollen...