Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1364to1368
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Summenprodukt (dynamischer Bereich)

Summenprodukt (dynamischer Bereich)
24.06.2014 14:18:38
Marcus
Hallo zusammen,
bisher habe ich durch probieren, diverse Foren etc. immer eine Lösung gefunden, aber diesmal beiße ich mir die Zähne aus. Dabei ist es vielleicht so einfach. :/
Folgender Sachverhalt:
https://www.herber.de/bbs/user/91254.xlsx
Quelle ist eine Datei mit je einem Tabellenblatt pro Mitarbeiter (MA 1, MA 2 usw.). Aufgelistet sind je Tabellenblatt die Aufträge (Zeilen) und gebuchte Stunden in den jeweiligen Monaten (Spalten). Da ich diese Datei monatlich bekomme, soll sie nur meine Quelldatei sein.
Ich benötige nun je MA die Summe der Ist-Stunden aller Monate zum jeweiligen Auftrag, also: suche nach Auftrag und addiere alle Werte in den Spalten "Gesamtsumme". Das Endergebnis ist im Tabellenblatt "Zusammenfassung" (später separate Datei) zu sehen. Damit ich die Quelle nicht öffnen muss, verwende ich =SUMMENPRODUKT. Das funktioniert im Tabellenblatt "Zusammenfassung" auch ganz gut, jedoch ist der Bezug immer absolut.
Jetzt das Problem: Die Anzahl der Aufträge (sprich Zeilen) kann je MA variieren. Ebenso kommen nach jedem Monat zwei neue Spalten (5/2014, 6/2014 usw.) hinzu.
Ich bräuchte also das SUMMENPRODUKT mit dynamischen Bereich, wie "Suche in Spalte A:A nach Auftrag und Zeile 18:18 nach Gesamtsumme" oder gern auch auf 100 Zeilen/Spalten beschränkt. Um Summenprodukt dynamisch zu gestalten, habe ich u.a. auch hier gelesen, dass man für den dynamischen Bereich einen Namen mit =BEREICH.VERSCHIEBEN definieren kann. Nur leider erhalte ich auch hier nur Fehler. Im Blatt "MA 1" im blauen Feld sind einige wenige meiner getesteten Formeln.
Die "russische Variante" steht derzeit in Zelle H14. Über SUMMEWENN könnte ich x Zeilen und Spalten bereits vorgeben, aber erstens ist das unschön und zweitens muss die Quelle geöffnet sein. Auch wenn ich VBA liebe, wollte ich hier doch drauf verzichten (z.B. zwecks Daten in Zieldatei als Werte kopieren).
Ich würde mich freuen, wenn jemand von euch einen Tipp oder eine Lösung hätte.
Vorab schon mal danke!
Gruß, Marcus

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
warum dynamisch ? ...
24.06.2014 14:26:44
der
Hallo Marcus,
... es reicht doch einfach =SUMMENPRODUKT(('MA 1'!$F$18:$AZ$18="Gesamtsumme")*('MA 1'!$A$19:$A$34=G5)*('MA 1'!$F$19:$AZ$34)) oder was meinst Du?
Gruß Werner
.. , - ...

AW: warum dynamisch ? ...
24.06.2014 15:28:42
Marcus
Hallo Werner,
...fast richtig. Du hast in deiner Formel die Spalten bis AZ ergänzt. Jedoch funktioniert das bei mir nicht, wenn ich ebenso die Zeilen bis z.B. Zeile 50 erweitern möchte.
Die Formel =SUMMENPRODUKT(('MA 1'!$F$18:$AZ$18="Gesamtsumme")*('MA 1'!$A$19:$A$50=G5)*('MA 1'!$F$19:$AZ$50)) bringt mir #WERT!. Scheinbar kommt der nicht mit leeren Zellen in Spalte A klar.
Gruß, Marcus

Anzeige
leere Zellen sind kein Problem ... doch ...
24.06.2014 15:37:11
der
Hallo Marcus,
... Du darfst in dem Zeilenbereich 19:50 in den Spalten mit der "Gesamtsumme" keine Textwerte stehen haben.
Gruß Werner
.. , - ...

AW: leere Zellen sind kein Problem ... doch ...
24.06.2014 18:43:01
Marcus
Hallo Werner,
du hast (leider) Recht. Wie peinlich, dass das nur an so einer Kleinigkeit liegt. Leider steht am Ende der letzten Zeile in jeder Spalte "Restbindung". Scheinbar muss ich nun doch ein Makro entwickeln, um diese Zeile zu löschen oder eben alles händisch löschen. Eine andere Lösung gibts vermutlich nicht?
Gruß, Marcus

das sollte auch anders möglich sein ...
25.06.2014 07:52:41
der
Hallo Marcus,
... ich komme dazu jedoch erst am späteren Nachmittag, weil ich gleich offline gehe.
Gruß Werner
.. , - ...

Anzeige
ist nicht eindeutig ...
25.06.2014 14:43:06
der
Hallo Marcus,
... aber entweder einfach den Bereich nur bis zur vorletzten Zeile definieren oder falls da doch noch auszuwertende Daten stehen, könnte folgende kleine Formeländerung schon zum Ziel führen:
=SUMMENPRODUKT(('MA 1'!$F$18:$AZ$18="Gesamtsumme")*('MA 1'!$A$19:$A$50=G5);'MA 1'!$F$19:$AZ$50)
(das letzte * ist jetzt ersetzt durch ein Semikolon)
Gruß Werner
.. , - ...

AW: ist nicht eindeutig ...
25.06.2014 19:19:18
Marcus
Hallo Werner,
das ist super! Klasse, vielen Dank. Der Trick mit dem Semikolon funktioniert. So kann ich überall den Bereich auf beliebig viele Zeilen und Spalten vergrößern und es können sogar Texte darin vorkommen.
Gruß, Marcus

Anzeige
aber mit Vorsicht anwenden ...
26.06.2014 10:58:59
der
Hallo Marcus,
... auch wenn diese Art der Anwendung von SUMMENPRODUKT() quasi der Standardnutzung von SUMMENPRODUKT() entspricht (siehe MS-Hilfe), solltest Du dies nur mit Bedacht einsetzen. Denn nicht immer willst Du wirklich das so ermittelte Ergebnis, wenn Unklarheit über die Datentypen in Deinen Tabellen bestehen könnte.
Die Variante mit * anstelle eines ; hat dann den "Vorteil", das man auf die unverträglichen Datentypen über die Fehlermeldung quasi mit der Nase drauf gestoßen wird und kann auf Ursachenforschung gehen.
Fazit: "Saubere" Datenstruktur ist die halbe Lösung.
Gruß Werner
.. , - ...
Anzeige

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige