Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.10.2025 19:34:56
Eine Zelle in 3 Spalten teilen
20.10.2025 19:24:08
Excel Form befüllen
20.10.2025 19:03:51
wenn istleer funktioniert nicht wegen formel
20.10.2025 13:56:55
Wenn Funktion mit Xverweis oder ähnlich
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1776to1780
1776to1780
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Werte aggregieren und Summen bilden

Forumthread: Werte aggregieren und Summen bilden

Werte aggregieren und Summen bilden
19.08.2020 11:50:35
Alex
Hallo zusammen,
ich brauche dringend eure Hilfe!
Ich habe eine Excel , in der ich aus dem Sheet „Kostenarten“ ein Sheet „Summe“ erzeugen möchte.
In dem Sheet „Kostenarten habe ich pro Jahr mehrere Kostenarten. Nun möchte ich das im Sheet „Summe“ aber immer nur die Summe aus den Kostenarten im jeweiligen Jahr erzeugt wird. Im Sheet „Summe“ seht ihr hierzu ein Beispiel. (Kostenarten werden hier nicht mehr aufgeführt, nur die Summen)
Die Zellbezüge in ab Spalte A müsste ich auch irgendwie ändern, sodass wirklich nur die Werte einmalig drinstehen, daher immer nur einmal für 2019, 2020 usw.
Hat jemand eine Idee wie das Formeltechnisch aussehen könnte? Sowohl für die Zellbezüge in den Spalten A bis I und in den Berechnungen in den Spalten J bis N?
Über Eure Hilfe würde ich mich sehr freuen!
Anbei findet ihr meine Testdatei. https://www.herber.de/bbs/user/139704.xlsx
LG
Alex
Anzeige

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: warum denn keine Pivotauswertung ? owT
19.08.2020 12:01:53
neopa
Gruß Werner
.. , - ...
AW: wenn doch Formel, dann einfach SUMMEWENN() owT
19.08.2020 12:17:05
neopa
Gruß Werner
.. , - ...
AW: wenn doch Formel, dann einfach SUMMEWENN() owT
19.08.2020 14:14:34
Alex
Pivot würde leider nicht funktionieren, da die Daten immer im gleichen Format bleiben müssen. :(
Gibt es denn eine Formel, sodass er immer nur eine Zeile pro Jahr erzeugt? Später könnte ich dann mit summewenns arbeiten ja...
Anzeige
AW: mit Pivotausw. ändert sich kein Format owT
19.08.2020 14:22:22
neopa
Gruß Werner
.. , - ...
AW: mit Pivotausw. ändert sich kein Format owT
19.08.2020 15:02:46
Alex
Ich bräuchte aber leider eine formel die ich runterziehen kann, welche dann die werte aggregiert
AW: in einer Fomel macht das doch ...
19.08.2020 15:14:08
neopa
Hallo Alex,
... die bereits Dir genannte Funktion SUMMEWENN()
In Summe!N3: =SUMMEWENN(Kostenarten!H:H;I3;Kostenarten!L:L) und nach unten kopieren. Oder was meinst Du wirklich?
Gruß Werner
.. , - ...
Anzeige
AW: in einer Fomel macht das doch ...
19.08.2020 15:27:23
Alex
Ich möchte ab der Spalte A alle Werte aggregieren. Sprich wenn ich für eine Maßnahme mehrere Kostenarten pro Jahr habe, möchte ich in dem Sheet "Summe" dafür nur eine Zeile stehen haben, dies gilt dann für jedes Jahr. Ich weiß wie ich die Werte am Ende summiere, weiß aber nicht wie ich in den Daten einen Abgleich mache, sodass die Formel z.B. erkennt, dass es pro Jahr und pro Maßnahme nur eine Zeile erzeugen soll. Ich brauche also immer nur pro Maßnahme 7 Zeilen, da ich ja die Kosten, Gegenläufer und Totals in einem Jahr summieren möchte.
Anzeige
AW: ials reine Formellös. mit INDEX() und ...
19.08.2020 16:15:55
neopa
Hallo Alex,
... z.B. kombiniert mit AGGREGAT() und ZÄHLENWENN()
Ich sträube mich hier allerdings etwas mit der Aufzeigung einer solchen aufwendigeren Lösung, weil ich nicht nachvollziehen kann, was gegen eine einfache Pivotauswertung spricht. Die 1. leichter erstellt, 2. übersichtlicher ist (es werden keine Textwiederholungen gelistet) und auch flexibler für weitere Auswertung ist.
Also was spricht gegen die Pivotauswertung?
Gruß Werner
.. , - ..
Anzeige
AW: ials reine Formellös. mit INDEX() und ...
19.08.2020 16:41:49
Alex
Ich importiere diese Daten immer in Power BI rein. Daher muss es immer in dem gleichen Format sein, da ich meine Abfrage auf dieses Format angepasst habe. Ich weiß, es gibt da bessere Lösung aber ich muss es irgendwie in einer Formel hinbekommen sonst bin ich echt aufgeschmissen. :(
AW: nun dann aber ...
19.08.2020 19:23:37
neopa
Hallo Alex,
... zunächst nur für die Daten Deiner Beispieldatei.
In Sunmme!A3 folgende Formel:
=WENNFEHLER(INDEX(Kostenarten!A:A;AGGREGAT(15;6;ZEILE(Kostenarten!A$3:A$16)/(Kostenarten!$H$3:$H$16&gtWENN(RECHTS($H2;4)="jahr";0;$H2));1));"")
und diese ziehend weit genug nach unten und nach rechts bis Spalte H kopieren.
Wenn es viele (tausende) Datensätze sind rate ich aber davon ab. Dann würde ich zu einer PowerQuery-Lösung raten.
Gruß Werner
.. , - ..
Anzeige
AW: nun dann aber ...
19.08.2020 19:46:10
Alex
Hallo Werner,
wo klasse vielen vielen Dank!
Jedoch habe ich das Problem, dass wenn eine weitere Maßnahme hinzukommt (Siehe Beispieldatei: https://www.herber.de/bbs/user/139717.xlsx) wird nur die erste Maßnahme in das Sheet Summe gezogen und aggregiert und es entsteht immer eine leere Zeile. Könnte man die Formel so anpassen, sodass die Maßnahmen immer untereinander stehen?
Lieben Gruß und nochmals Danke!
Alex
Anzeige
AW: dann als Formellösung nur mit Hilfsspalten ...
20.08.2020 09:34:51
neopa
Hallo Alex,
... nachfolgende Lösung ist momentan zudem nur ausgelegt auf unterschiedliche Maßnahmen und Jahre und setzt voraus, dass die Kostenartentabelle nach diesen sortiert gelistet ist. Das Hilfsspaltenkonzept ist so angelegt, dass es auf dieser Basis auch erweitert werden könnte für weitere unterschiedliche Daten z.B. bzgl. des Datums, Ressort ...
Aber n dieser Stelle zunächst nochmal der Hinweis darauf, dass eine PowerQuery einer solchen Formellösung vorzuziehen wäre und auch mit unsortierten Daten kein Problem hätte.
Nachfolgende Hilfsspaltenformeln in "Kostenarten" könnten auch in "Summe" verlegt werden.
Arbeitsblatt mit dem Namen 'Kostenarten'
 PQRS
3112019120193
4112019120205
5112020120217
6112020120229
71120211202311

ZelleFormel
P3=(ZÄHLENWENN(D$3:D3;D3)=1)+P2
Q3=(P3&H3)+0
R3=WENNFEHLER(AGGREGAT(15;6;Q3:Q999/(Q3:Q999&gtR2);1);"")
S3=WENN(R3="";"";VERGLEICH(R3;Q:Q;0))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Dies nach unten kopieren
Abschließend dann in SummeA3:

=WENNFEHLER(INDEX(Kostenarten!A:A;KKLEINSTE(Kostenarten!$S:$S;ZEILE(A1)));"")
und diese nach unten und rechts kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: dann als Formellösung nur mit Hilfsspalten ...
20.08.2020 10:51:17
Alex
Hammer vielen Dank hat geklappt!!! Darauf wäre ich einfach nie gekommen :(
Ich habe mal diesen Makro erstellt. Könnte man den so anpassen, sodass er beim extrahieren des Blattes Kostenarten, die Spalten P bis S löscht?
Sub Tabellenblatt_speichern()
ActiveSheet.Copy
Application.Dialogs(xlDialogSaveAs).Show ActiveSheet.Name & ".xlsx"
Anzeige
AW: bitteschön, jedoch ...
20.08.2020 13:26:26
neopa
Hallo Alex,
... Deine Zusatzrage kann ich Dir nicht beantworten, weil ich mich mit VBA prinzipiell nicht beschäftige.
Gruß Werner
.. , - ...
AW: nachgefragt ...
20.08.2020 13:33:27
neopa
Hallo nochmal,
... warum sollen diese Hilfsspalten dort gelöscht werden? Dann müsstest Du zuvor ja auch die Formeln zur Datenermittlung im Tabellenblatt "Summe" durch seine Werte ersetzen.
Außerdem hatte ich Dir ja mitgeteilt, dass die Hilfsspalten anstelle in "Kostenarten" im Ergebnistabellenblatt "Summe" angelegt werden können.
Dort müssten die Formeln lediglich noch etwas angepasst werden, so:
Arbeitsblatt mit dem Namen 'Summe'
 PQRS
2    
3112019120193
4112019120205
5112020120217
6112020120229
71120211202311
81120211202413
91120221202515

ZelleFormel
P3=WENN(Kostenarten!A3="";"";(ZÄHLENWENN(Kostenarten!D$3:D3;Kostenarten!D3)=1)+Kostenarten!P2)
Q3=WENN(P3="";"";(P3&Kostenarten!H3)+0)
R3=WENNFEHLER(AGGREGAT(15;6;Q$3:Q$999/(Q$3:Q$999&gtR2);1);"")
S3=WENN(R3="";"";VERGLEICH(R3;Q:Q;0))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

und diese wiet genug nach unten ziehend kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: nachgefragt ...
20.08.2020 15:12:37
Alex
Hallo Werner, ne das passt schon. Danke dir.
Ich lade die Kostenarten immer neu rein und erzeuge eben die Basistabelle, diese lade ich dann immer in einen Ordner zum Import in Power BI und hier gab es immer etwas probleme wenn die werte noch als Formeln hinterlegt waren...
AW: mit PQ könntest Du dies vermeiden, dass ...
21.08.2020 10:43:25
neopa
Hallo Alex,
... überhaupt Formel in den Tabellen notwendig werden., wie ich auch bereits geschrieben hatte.
Nicht eindeutig ist, ob Du mit "Basistabelle" die Tabelle "Summe" in deiner Beispieldatei meinst.
Wenn dem so ist, brauchst Du doch nicht die Hilfsspaltenformeln in "Kostenarten" zu löschen sondern müsstest lediglich in "Summe"-Tabellenblatt alle Formeln durch ihre ermittelten Werte ersetzen. Oder?
Gruß Werner
.. , - ...
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Werte in Excel aggregieren und Summen bilden

Schritt-für-Schritt-Anleitung

  1. Datenstruktur anpassen: Stelle sicher, dass deine Daten in der Tabelle "Kostenarten" entsprechend strukturiert sind. Jede Kostenart sollte in der gleichen Spalte stehen, und die Jahre sollten klar zugeordnet sein.

  2. Formel zum Aggregieren: Verwende die Funktion SUMMEWENN() in der Tabelle "Summe", um die aggregierten Werte zu berechnen. Die Formel könnte wie folgt aussehen:

    =SUMMEWENN(Kostenarten!H:H; I3; Kostenarten!L:L)

    Diese Formel summiert die Werte in der Spalte L basierend auf den Kriterien in der Spalte H.

  3. Zellbezüge anpassen: Achte darauf, die Zellbezüge in den Spalten A bis I so zu ändern, dass sie die richtigen Daten aus der Tabelle "Kostenarten" abbilden.

  4. Formel nach unten ziehen: Kopiere die Formel in der Zelle und ziehe sie nach unten, um die Aggregation für weitere Jahre zu ermöglichen.

  5. Hilfsspalten verwenden: Wenn nötig, verwende Hilfsspalten, um komplexe Berechnungen durchzuführen. Diese können in der Tabelle "Kostenarten" oder "Summe" erstellt werden.

Häufige Fehler und Lösungen

  • Leere Zeilen in der Tabelle "Summe": Wenn du leere Zeilen erhältst, kann das an einer falschen Bedingung in der SUMMEWENN()-Formel liegen. Überprüfe die Kriterien und stelle sicher, dass sie korrekt gesetzt sind.

  • Aggregierte Werte stimmen nicht: Achte darauf, dass die Kriterien in der Formel den korrekten Zellbereich abdecken. Eine häufige Fehlerquelle ist die falsche Spaltenreferenz.

  • Formel zieht keine neuen Daten: Stelle sicher, dass die Daten in der Tabelle "Kostenarten" aktuell sind und dass du die Formeln nach dem Hinzufügen neuer Daten aktualisiert hast.

Alternative Methoden

  • Pivot-Tabellen: Auch wenn es in deinem Fall nicht möglich ist, kann die Verwendung von Pivot-Tabellen eine einfache und effektive Methode sein, um Daten zu aggregieren.

  • Power Query: Eine andere Möglichkeit ist die Verwendung von Power Query, um die Daten zu laden und zu transformieren. Dies ermöglicht eine flexiblere Datenaggregation und kann auch mit unsortierten Daten umgehen.

Praktische Beispiele

  • Beispiel für eine einfache Aggregation: Angenommen, du hast folgende Daten in der Tabelle "Kostenarten": Jahr Kostenart Betrag
    2019 A 100
    2019 B 150
    2020 A 200

    Mit der Formel =SUMMEWENN(Kostenarten!A:A; 2019; Kostenarten!C:C) erhältst du die Summe für das Jahr 2019.

  • Beispiel für komplexere Aggregation: Wenn du die Gesamtkosten für jede Maßnahme über mehrere Jahre aggregieren möchtest, kombiniere SUMMEWENN() mit anderen Funktionen wie AGGREGAT(), um spezifischere Berechnungen vorzunehmen.

Tipps für Profis

  • Verwende dynamische Bereiche: Nutze dynamische Bereiche in deinen Formeln, um sicherzustellen, dass alle neu hinzugefügten Daten automatisch einbezogen werden.

  • Datenvalidierung: Setze Datenvalidierung ein, um sicherzustellen, dass nur gültige Daten eingegeben werden. Dies kann helfen, Fehler bei der Aggregation zu vermeiden.

  • Versteckte Spalten: Wenn du Hilfsspalten verwendest, kannst du diese auch ausblenden, um die Übersichtlichkeit deiner Tabelle zu erhöhen.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass meine aggregierten Werte immer aktuell sind?
Vergewissere dich, dass du die Formeln nach dem Hinzufügen neuer Daten aktualisierst und ziehe sie bei Bedarf nach unten.

2. Kann ich auch mehrere Kriterien in meiner Aggregation verwenden?
Ja, du kannst die Funktion SUMMEWENNS() verwenden, um mit mehreren Kriterien zu arbeiten.

3. Warum funktioniert meine Pivot-Tabelle nicht?
Wenn deine Daten nicht im richtigen Format vorliegen oder nicht konsistent sind, kann das zu Problemen führen. Überprüfe die Datenstruktur und die Formatierung.

4. Was ist, wenn ich Power BI verwende?
In diesem Fall solltest du sicherstellen, dass deine Daten in einem konsistenten Format vorliegen, da Power BI auf diese Struktur angewiesen ist.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
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