Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


nur gefilterte Werte über anderes Tabellenblatt summieren
#1
Nachdem ich vorher schon kompetent beraten wurde hätte ich noch eine letzte Frage Blush

Ich summiere Werte über einzelne Tabellenblätter und gebe diese dann auf einem Ergebnis-Tabellenblatt anzeigen. Funktioniert nun wunderbar.
=SUMMEWENN(Lieferungen!$B$2:$B$9999;[B.APLNR];Lieferungen!$C$2:$C$9999)

Problem: ich will die Tabellenblätter filtern und nur das gefilterte dann in das Ergebnis-Tabellenblatt übertragen.

Als Beispiel:
Tabellenblatt Lieferung: dort stehen alle Einzelsätze aller Lieferungen mit Artikel, Datum und Menge
4711 am 08.01.14 mit 1000 Stück
4711 am 01.01.15 mit 1200 Stück
4711 am 13.01.14 mit 1500 Stück
4712 am 12.01.14 mit 1200 Stück
4712 am 20.01.14 mit 1400 Stück
4712 am 04.01.15 mit 1800 Stück
usw.

Ohne Filterung habe ich per SUMMEWENN()-Formel folgendes Ergebnis:
4711: 3700 Stück
4712: 4400Stück

Nun kommen die Filter zum Einsatz. z.B. Filterung auf das Jahr 2014. Es sollte nun nur noch die gefilterten Werte in der Formel berücksichtigen. Also im Bsp. wie folgt:
4711: 2700 Stück
4712: 2600 Stück

Ich hoffe es gibt hierfür auch eine Lösung...
to top
#2
Hi,

schau Dir mal die Funktion =TEILERGEBNIS() an ...
to top
#3
Hallo,

nach meinem Verständnis des Postings könnte man auch mit Summenprodut

Ungestetet: =SUMMENPRODUKT((Lieferungen!$B$2:$B$9999="[B.APLNR]")*(JAHR(Lieferungen!$A$2:$A$9999)=2014)*(Lieferungen!C2:C9999))

oder mit einer Pivottabelle arbeiten.
Gruß
Peter
to top
#4
Hi,

(08.01.2015, 10:01)Peter schrieb: Ungestetet: =SUMMENPRODUKT((Lieferungen!$B$2:$B$9999="[B.APLNR]")*(JAHR(Lieferungen!$A$2:$A$9999)=2014)*(Lieferungen!C2:C9999))

oder mit einer Pivottabelle arbeiten.
bei SUMMENPRODUKT denke ich immer sofort an Pivot-Tabelle.

Das dürfte hier auch besser sein, denn wenn das die Statistik über die Produktion ist, werden das einige Zeilen werden und mit der Matrixformel SUMMENPRODUKT könnte das langsam werden.
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#5
Hallo,

Zitat:bei SUMMENPRODUKT denke ich immer sofort an Pivot-Tabelle.

Nicht nur du. 1919
Gruß
Peter
to top
#6
Danke für die Antworten. Eine Pivot hilft nachher nicht weiter weil es nachher eine Tabelle mit mehreren Werten ergibt.

Art, Bezeichnung, Index, Maschine, Material (Menge), Material (Wert) uvm.... Liefermenge (Stück), Liefermenge (Wert), Gutmenge (Stück), Gutmenge (Wert), Auschussmenge (Stück), Ausschuss (Wert)

Weiterhelfen würde mir die Funktion TEILERGEBNIS() allerdings nur in Kombination mit der SUMMEWENN()-Funktion.

=SUMMEWENN(Tabelle_lieferungen_einzel[P.ARTNR];[B.APLNR];Tabelle_lieferungen_einzel[P.MENGE])
Ich habe nun an allen Stellen versucht TEILERGEBNIS() einzubauen - erfolglos.
Meines Erachtens müsste diese Funktion im 3. Teil (Summenbereich) untergebracht werden, also folgendermaßen:
=SUMMEWENN(Tabelle_lieferungen_einzel[P.ARTNR];[B.APLNR];TEILERGEBNIS(9;Tabelle_lieferungen_einzel[P.MENGE])).

Das mag Excel aber nicht Angry
to top
#7
Noch ein Anmerkung: SUMMEPRODUKT() will ich nicht unbedingt verwenden weil der Endanwender dann in der Funktion das Datum eingeben müsste was zum einen nicht anwenderfreundlich und zum anderen auch nicht einfach ist. Bsp. alle Datensätze des Jahres von 2014 würde bedeuten man müsste darin auf von und bis eingrenzen weil jeder Datensatz ein Datenstempel beinhaltet (2014-05-18 12:20) wobei das Jahr nur ein Teil davon ist.
to top
#8
Hallo,

Code:
Datenstempel beinhaltet (2014-05-18 12:20)

wenn das von XL als Text erkannt wird dann könnte man in der Summenproduktformel z.B. mit der Funktion Links() arbeiten:

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCD
12014-05-18 12:205201410
22015-05-18 12:206  
32014-12-18 12:205  
4    

ZelleFormel
D1=SUMMENPRODUKT((LINKS(A1:A10;4)=C1)*(B1:B10))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Den gesuchten Parameter kann man in eine Zelle schreiben, hier C1. Das funktioniert sowohl wenn das Datum von XL als Text angesehen wird, als auch als Zahl. Man muss nur darauf achten, dass der Suchparameter in C1 das gleiche Format hat.
Gruß
Peter
to top
#9
(08.01.2015, 12:00)Peter schrieb: wenn das von XL als Text erkannt wird dann könnte man in der Summenproduktformel z.B. mit der Funktion Links() arbeiten:

oder einfach mit JAHR():

Tabelle1
ABCDE
118.05.2014 12:2052014010
218.05.2015 12:206
318.12.2014 12:205
4

verwendete Formeln
Zelle Formel Bereich N/A
D1=SUMMENPRODUKT((LINKS(A1:A10;4)=C1)*(B1:B10))
E1=SUMMENPRODUKT((JAHR(A1:A10)=C1)*(B1:B10))
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 18.19 einschl. 64 Bit

Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#10
Hallo Ralf,

stimmt, in dem von mir angedachten Fall ist die Funktion Links() gar nicht notwendig, da reicht die Funktion Jahr().
Gruß
Peter
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Doppelte Werte über 2 Spalten waldemarrrrr 1 45 01.12.2016, 15:39
Letzter Beitrag: shift-del
  Filtern Liste und Daten auf anderes Sheet kopieren Rabe 5 83 01.12.2016, 15:02
Letzter Beitrag: snb
  (berechnete) Teilbereiche in anderes Blatt kopieren Timo 12 219 23.11.2016, 16:29
Letzter Beitrag: Gast 123
  per VB werte im anderen Tabellenblatt zusammjenfassen derhanfti 1 47 07.11.2016, 09:03
Letzter Beitrag: Crazy Tom
  VB Daten in anderes Tabellenblatt übertragen derhanfti 3 95 07.11.2016, 07:56
Letzter Beitrag: derhanfti
  Summieren und Wenn Funktion Strong 2 67 31.10.2016, 10:20
Letzter Beitrag: coemm
  Daten nach Entscheidung in anderes Blatt schreiben appelschnut 5 94 27.10.2016, 17:03
Letzter Beitrag: appelschnut
  S-verweis auf gefilterte Matrix Frankie 4 109 22.10.2016, 17:55
Letzter Beitrag: Jockel
  Nach Kategorien Summieren über mehrere Arbeitsblätter thyslo 6 144 13.10.2016, 19:08
Letzter Beitrag: thyslo
  Nach zwei Kriterien in zwei Spalten gefilterte Zeilen lückenlos kopieren (oder so) Sommer 2 131 26.09.2016, 12:40
Letzter Beitrag: Sommer

Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste