Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 08:56:18
Textbox in Tabellenblatt ohne Bestätigung in Zelle eintragen
20.04.2024 07:57:54
Tablle fürs E-Auto - Preis wenn kürzer als 60 Min Ladezeit
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1776to1780
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1776to1780
1776to1780
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

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

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige