Teilergebnis mit Summenprodukt und Bereichen verwenden

Moderator: ModerationP

Teilergebnis mit Summenprodukt und Bereichen verwenden

Beitragvon RebekkaB » 14. Jun 2018, 17:04

Hallo,

ich möchte mir anzeigen lassen, wieviele "Datensätze" (also Zeilen) der aktuell gefilterten Zeilen in Bereich2 nicht "FGR" stehen haben und gleichzeitig in Bereich3 einen Wert > 0 enthalten.

Da in Bereich1 immer ein Wert steht, hatte ich gedacht es so umsetzen zu können:

Code: Alles auswählen
=SUMMENPRODUKT((TEILERGEBNIS(3;BEREICH1)*(BEREICH2<>"FGR")*(BEREICH3>0)))


Allerdings erhalte ich hier als Ergebnis die Zahl 5896989, was aber mehr als dem 1100-fachen der Zeilenanzahl der Bereiche entspricht.

Zur Info:
Die Bereiche beziehen sich jeweils nur auf eine Spalte. Sie sind etwas groß, eigentlich werden sie garnicht so groß benötigt (derzeit ca 2500 benutzte Zeilen), aber sicher ist sicher...

Code: Alles auswählen
BEREICH1 ='Daten'!$M$7:$M$5000

BEREICH2 ='Daten'!$D$7:$D$5000

BEREICH3 ='Daten'!$G$7:$G$5000


Setze ich statt der Bereichsnamen die Zellbereiche ein, erhalte ich das gleiche Ergebnis:
Code: Alles auswählen
=SUMMENPRODUKT((TEILERGEBNIS(3;'Daten'!$M$7:$M$5000)*('Daten'!$D$7:$D$5000<>"FGR")*('Daten'!$G$7:$G$5000>0)))


Schreibe ich den ersten Teil jedoch etwas anders, komme ich auf richtige Ergebnisse:
Code: Alles auswählen
=SUMMENPRODUKT((TEILERGEBNIS(3;INDIREKT("'Daten'!D"&ZEILE(7:5000)))*('Daten'!G7:G5000<>"FGR")*('Daten'!M7:M5000>0)))


Ich möchte allerdings nicht den Bereich in der Formel angeben sondern entweder über die Bereichsnamen angeben oder mir die letzte Zeile (also hier die 5000) über eine Zelle reinholen. Hier könnte man dann in der Zelle eingeben, bis zu welcher Zeile die Datensätze betrachtet werden sollen. Wenn es also die Möglichkeit gibt die letzte Formel so umzubauen, dass sie sich die letzte Zeile immer aus z.B. Zelle A1 holt wäre das schon vollkommen ausreichend. Aber auch das bekomme ich nicht hin. :-(

Wo liegt hier mein Denkfehler? Kann mir jemand helfen / einen Denkanstoß geben?

Danke, Bekki
RebekkaB
 

Re: Teilergebnis mit Summenprodukt und Bereichen verwenden

Beitragvon Klaus-Dieter » 14. Jun 2018, 17:45

Hallo Bekki,

versuche es mal in dieser Art: =SUMMENPRODUKT((A2:A20="FGR")*(B2:B20>0))
Viele Grüße
Klaus-Dieter
Lösungsvorschläge sind, wenn es keinen anders lautenden Hinweis gibt, von mir getestet.
Künstliche Intelligenz ist besser als natürliche Dummheit.
Benutzeravatar
Klaus-Dieter
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 17582
Registriert: 27. Nov 2003, 23:03
Wohnort: Sassenburg

Re: Teilergebnis mit Summenprodukt und Bereichen verwenden

Beitragvon HKindler » 15. Jun 2018, 11:03

Hi,

soweit ich weiß, funktioniert der Trick mit Teilergebnis(3;...) nur zusammen mit INDIREKT(). Allerdings mag ich INDIREKT bzw. irgendeine volatile Funktion in einer Summenprodukt-Formel gar nicht, da das sehr schnell sehr viel Rechenaufwand bedeutet.

Ich selbst verwende in so einem Fall eine Hilfsspalte mir der Überschrift "Zeile sichtbar?" In diese Spalte kommt eine Formel, die WAHR ergibt, falls die Zeile sichtbar ist. Das erreicht man z.B. durch =TEILERGEBNIS(103;$A7)>0 oder durch =AGGREGAT(3;3;$A7)>0 beide Formeln liefern WAHR, falls Zeile 7 eingeblendet ist und FALSCH, falls Zeile 7 ausgeblendet ist. Voraussetzung ist natürlich, dass in Zelle A7 irgendetwas steht, diese also nicht leer ist. Diese Formel nach unten kopieren.

Nehmen wir einmal an diese Hilfsspalte ist Spalte X, dann definierst du den Namen Sichtbar = Daten!$X$7:$X$5000 . Falls du jetzt nur sichtbare Zellen verwenden willst, lautet deine Formel: =SUMMENPRODUKT(Sichtbar*(BEREICH2<>"FGR")*(BEREICH3>0))
Gruß,
Helmut

----------------------------
Windows 10 Enterprise (64 Bit) / Office Professional Plus 2016 (32 Bit)
Benutzeravatar
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 2404
Registriert: 04. Jul 2013, 09:02

Re: Teilergebnis mit Summenprodukt und Bereichen verwenden

Beitragvon lupo1 » 15. Jun 2018, 11:09

1. Volatilität wird überdramatisiert. Notfalls kann man entsprechende Formeln jeweils plätten und nur für eine Neuberechnung neu ausfüllen. Das geht natürlich auch hübsch mit VBA.

2. TEILERGEBNIS kann in SUMMENPRODUKT auch sehr schön mit BEREICH.VERSCHIEBEN (ebenfalls volatil).
MfG (und Hallo! natürlich auch) Lupo
Benutzeravatar
lupo1
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 8367
Registriert: 25. Okt 2012, 13:38


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: beneran, HKindler, KW2903, MisterBurns, Robbi Dick und 28 Gäste