Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: 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

Anzeige

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

Anzeige
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
.. , - ...

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
.. , - ...

Anzeige
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

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
;
Anzeige
Anzeige

Infobox / Tutorial

Dynamisches Summenprodukt in Excel nutzen


Schritt-für-Schritt-Anleitung

Um ein dynamisches Summenprodukt in Excel zu erstellen, befolge diese Schritte:

  1. Datenstruktur anlegen: Stelle sicher, dass Deine Daten in einem konsistenten Format vorliegen, z.B. mit Aufträgen in einer Spalte und den entsprechenden Gesamtsummen in einer anderen.

  2. Bereich definieren: Anstatt feste Zellbezüge zu verwenden, kannst Du den Bereich dynamisch gestalten. Nutze die Funktion BEREICH.VERSCHIEBEN, um die Datenbereiche flexibel anzupassen.

    Beispiel:

    =BEREICH.VERSCHIEBEN('MA 1'!$F$19;0;0;ANZAHL('MA 1'!$A$19:$A$50);ANZAHL2('MA 1'!$F$18:$AZ$18))
  3. SUMMENPRODUKT verwenden: Nutze die Formel SUMMENPRODUKT in Verbindung mit dem dynamischen Bereich.

    Beispiel:

    =SUMMENPRODUKT((BEREICH.VERSCHIEBEN('MA 1'!$F$18;0;0;ANZAHL('MA 1'!$A$19:$A$50);ANZAHL2('MA 1'!$F$18:$AZ$18))="Gesamtsumme")*('MA 1'!$A$19:$A$50=G5)*BEREICH.VERSCHIEBEN('MA 1'!$F$19;0;0;ANZAHL('MA 1'!$A$19:$A$50);ANZAHL2('MA 1'!$F$18:$AZ$18)))
  4. Formel testen: Überprüfe, ob die Formel korrekt funktioniert und das erwartete Ergebnis liefert.


Häufige Fehler und Lösungen

  • #WERT! Fehler: Dieser Fehler tritt auf, wenn in den Zeilen mit der „Gesamtsumme“ Textwerte vorhanden sind. Achte darauf, dass in den relevanten Zeilen keine unerwarteten Daten stehen.

  • Leere Zellen: Wenn leere Zellen in den Bereichen vorhanden sind, kann dies ebenfalls zu Problemen führen. Stelle sicher, dass die Datenstruktur sauber ist.

  • Fehlende Daten: Wenn neue Spalten in der Quelldatei hinzugefügt werden, stelle sicher, dass die Formel dynamisch angepasst wird, um diese Änderungen zu berücksichtigen.


Alternative Methoden

Falls SUMMENPRODUKT nicht die gewünschten Ergebnisse liefert, kannst Du folgende Alternativen in Betracht ziehen:

  • SUMMEWENN: Diese Funktion ist nützlich, um Summen basierend auf einem Kriterium zu berechnen. Allerdings muss die Quelldatei in diesem Fall geöffnet sein.

  • Pivot-Tabellen: Eine weitere Möglichkeit, aggregierte Daten dynamisch zu analysieren, ist die Verwendung von Pivot-Tabellen. Diese bieten eine flexible und visuelle Möglichkeit, Daten zu summieren.


Praktische Beispiele

Angenommen, Du hast folgende Datenstruktur:

Auftrag 5/2014 6/2014 Gesamtsumme
A1 10 15 Gesamtsumme
A2 20 30 Gesamtsumme

Die Formel für das dynamische Summenprodukt könnte wie folgt aussehen:

=SUMMENPRODUKT((BEREICH.VERSCHIEBEN(A1;0;0;ANZAHL(A:A);1)="Gesamtsumme")*(BEREICH.VERSCHIEBEN(B1;0;0;ANZAHL(B:B);2)))

Tipps für Profis

  • Datenvalidierung: Sorge dafür, dass die Datenstruktur immer sauber bleibt. Verwende Datenvalidierungsfunktionen, um falsche Eingaben zu vermeiden.

  • Namen definieren: Vergib aussagekräftige Namen für Bereiche, um die Lesbarkeit Deiner Formeln zu verbessern.

  • Fehlerüberprüfung: Nutze die Fehlerüberprüfungswerkzeuge in Excel, um mögliche Probleme in Deinen Formeln frühzeitig zu erkennen.


FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen SUMMENPRODUKT und SUMMEWENN? SUMMENPRODUKT erlaubt komplexere Berechnungen mit mehreren Bedingungen, während SUMMEWENN einfacher ist und sich auf eine Bedingung konzentriert.

2. Wie kann ich leere Zellen in meiner Berechnung ignorieren? Stelle sicher, dass die verwendeten Bereiche nur Zahlen enthalten oder nutze die Funktion WENNFEHLER, um leere Zellen zu handhaben.

3. Funktioniert diese Methode in allen Excel-Versionen? Die beschriebenen Funktionen sind in den meisten aktuellen Excel-Versionen verfügbar, einschließlich Excel 2010 und neuer. Bei älteren Versionen können einige Funktionen möglicherweise abweichen.

Beliebteste Forumthreads (12 Monate)

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