Excel Formel dynamisch auf wachsende Pivot anpassen?

Hallo!

ich habe eine Pivot Tabelle die monatlich aktualisiert wird, das heißt pro Monat kommt immer wieder eine Spalte hinzu um den jeweiligen Verlauf über die Zeit darzustellen.

Basierend auf dieser Pivot muss ich nun noch eine Berechnung durchführen die sich auf den Pivotbereich bezieht (z.B.: prüfe den Bereich B1:G1 und berechne ggf. irgendetwas). Die Frage ist jetzt wie kann ich in meiner Formel hinterlegen das dieser Bereich wächst? Also so das wenn der nächste Monat hinzu kommt der Bereich B1:H1 automatisch betrachtet wird?

Habt ihr vielleicht eine Idee?
Vielen Dank!

3 Antworten

IchMalWiederXY

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Microsoft Excel

04.04.2023, 20:47

Mit INDIREKT(Berechne den Bereich)
kann man wachsende Tabelle ohne Formelanpassung in den Griff bekommen.
ABER je nach dem welche Spalte jeden Monat dazukommt ist es ggf KEIN perfekte PIVOT Tabelle !
Annahme
Produkt | April 2023 | ..Monat +1 | MONAT +1 | .... ist kein "perfektes" Pivot.
dann besser die Datenstruktur ändern.

GutenTag2003

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

Microsoft Excel, Excel-Formel, Office- & Büroanwendungen

04.04.2023, 09:49

das heißt pro Monat kommt immer wieder eine Spalte hinzu

Du könntest die Tabelle bereits für ein ganzes Jahr (12 Monate *)) anlegen und bei der Datenauswahl die nicht anzuzeigendem Zeiträume, (Spalten) bei der Auswahl weglassen.

*) bei Bedarf eine 13. für die summierten Werte

geri3d

Nutzer, der sehr aktiv auf gutefrage ist

im Thema Microsoft Excel

04.04.2023, 09:05

Es wäre möglich, die Formel mit einem VBA Makro zu setzen, dabei ist zu beachten, dass die Formeln englisch sein müssen und , und ; unterschiedlich sind.

Eine gute Quelle für VBA Kniffe ist https://www.herber.de/

Woher ich das weiß:Berufserfahrung – Softwareentewickler / Unternehmensberater bei CSDIT iR

Hallo, ich hoffe mir kann jemand helfen.

Ich habe folgende Rohdaten in einer Tabelle in Excel:

A2-A262 Datum (Arbeitstage 2024)

B1-S1 (Tabellenüberschriften) verschiedene Kategorienamen

in den anderen Zellen jeweils Zahlen, wie oft die Kategorie an dem Datum gewählt wurde (also z.B. in B2 10, weil die Kategorie an dem Tag 10x gewählt wurde, in E10 5, weil diese Kategorie an dem Zag 5 mal gewählt wurde usw.)

Ich würde gerne ein Pivot Chart erstellen, das mir nur die Top 10 Kategorien pro Monat/Tag (möchte hier gerne eine Zeitachse einfügen, anhand der ich später filtern kann) anzeigt.

Wenn ich eine Pivot Tabelle erstelle und die Kategorie Namen in "Spalten" ziehe, zeigt mir Excel jedoch nur Zahlen an anstatt den Kategorienamen.

Ich hoffe auf Antworten, wie ich dieses Problem lösen kann.

Danke im Voraus!

...zur Frage

Wie kann sich die Summenformel in Excel beim einfügen einer neuen Spalte anpassen?

Hallo ich habe da folgende Frage.

In einem Tabellenblatt ist die letzte Spalte F
Ich habe in Zelle A1 folgende Summenformel: =sum(B1:F1)

Jetzt füge ich zwischen Spalte A und B eine neue Spalte ein.

Dann wird die Formel fälschlich zu: =sum(C1:G1) Denn ich will alle Spalten außer A natürlich in die Summe einbeziehen.

Wie geht das das die Formel dann aber so aussieht? = sum(B1:G1)

Ich Danke vielmals für Hilfe!

...zur Frage

Wie kann ich in Excel die Arbeitszeit automatisch füllen?

Hallo,

ich habe eine Excel Datei, in der ich meine Arbeitszeiten eintrage. Dabei ändere ich im Kopf nur den Monat ab (also z B 01.10.2023, das Datumsformat ist benutzerdefiniert MMMM JJJJ und wird wie folgt ausgegeben: "Oktober 2023").

Anhand des Monats wird die Tagesspalte automatisch gefüllt. Die Formel sieht wie folgt aus:

erster Tag

=($H$7["Oktober 2023"]+Zeile(B1)-1)*(MONAT(H7["Oktober 2023"]+1)=MONAT($H$7["Oktober 2023"]))

alle folgenden Tage

=($H$7["Oktober 2023"]+ZEILE(B2)-1)*(MONAT(B11[vorheriger Tag]+1)=MONAT($H$7["Oktober 2023"]))

Dabei habe ich selber noch nicht das Verhalten von "Zeile(B2)-1" verstanden

Das Datumsformat ist hier wie folgt: Benutzerdefiniert 0;-0;;@

Bsp Ausgabe: "So, 01"

Nun zur eigentlichen Frage :D

Ich arbeite Teilzeit und möchte die Arbeitszeiten automatisch von Dienstag bis Donnerstag eintragen lassen. Ich habe überlegt folgende Formel anzuwenden:

=WENN(B11[Tag z. B. "So, 01"]=LINKS("Di";2);"15:30";"")

Wenn der Tag mit "Di" beginnt, soll Arbeitsende 15:30 ausgegeben werden, wenn nicht, dann soll das Feld leer sein.

Folgende zwei Probleme

1) wenn ich bei Überprüfungen nur =links(B11;2) eingebe, wird mir "45" ausgegeben (egal, welcher Tag). Das liegt womöglich am Datumsformat 0;-0;;@

Ich möchte ungern eine lange Spalte vorn haben, wo dann z B. folgendes steht: "Sonntag, 01.10.2023". Die Angabe So, 01 ist doch äußerst praktisch und schnell zu lesen.

Außerdem, wenn ich das Datumsformat ändere, wird mir immer nur "So, 01" angezeigt (die Einstellung wird trotzdem übernommen)

2) welche Eingabe muss ich vornehmen, wenn ich Arbeitsende 15:30 oder vlt sogar 15:25 ausgeben möchte?

Gibt es bei diesem Fall noch eine andere Lösung, abgesehen von der Lösung mit einem sehr langem Datumsformat?

Vielen Dank vorab.

...zur Frage

Excel: Dynamisch Bereich selber auswählen?

Liebe Community,

ich habe folgendes Macro in meiner Excel Tabelle. Es funktioniert auch wunderbar, bis auf die Tatsache, dass dort der Tabellenbereich aus der fremden Excel-Datei definiert ist.

Leider sind es nicht immer 239 Zeilen. Mal mehr und mal weniger. Dass es bei A7 anfängt, das stimmt schon. Wie müsste ich die Formel ändern, damit Excel sich automatisch anpasst?

Kann einer sagen, was ich nun genau ändern müsste in der betroffenen Zeile, damit Excel dynamisch sich den Bereich auswählt?

Vielen lieben Dank im Voraus!

Es handelt sich konkret um diese Zeile.

wbQuelle.Worksheets(1).Range("A7:I239").Copy
Sub Quelle()

Hier der gesamte Code

Dim sPfad As String
Dim wbQuelle As Workbook
Dim zelle As Range

sPfad = "C:\Users\Beispiel\Desktop\Irgendeine Datei\Daten\Daten.xlsx"

If Dir(sPfad) <> "" Then

Set wbQuelle = Workbooks.Open(sPfad)

wbQuelle.Worksheets(1).Range("A7:I239").Copy ThisWorkbook.Worksheets(3).Range("B2")

wbQuelle.Close savechanges:=False

End If

End Sub

...zur Frage

Excel: Wie jeweils einen Monat dazu addieren?

Also z.B. Zelle A1 hat den Wert "01.07.2010", dann soll in Zelle B1 automatisch "01.08.2010" (also 1 Monat weiter) angezeigt werden. Weiß jemand eine Formel? Ich habe es schon mit string-Funktionen versucht, und jeweils 30 Tage dazuaddieren geht auch nicht. Weiß jemand Rat?

...zur Frage

Ecxel PivotTabelle - hartnäckiges Problem ohne Lösung?

Hallo liebe Community und Excel Profis,

ich hänge an einem Problem mit einer Pivot Tabelle, dass mich seit vorgestern nicht mehr in Ruhe lässt und ich keine Lösung finden kann.

Ich habe das ganze mal vereinfacht dargestellt, um mein Problem verständlich zu machen.

Ich möchte ein Dashboard erstellen, in dem ich die verbleibende Betriebszeit von Aggregaten einsehen kann. Sinn und Ziel ist die Einzel- oder Mehrfachauswahl von Aggregaten.

Also: "Wie lange reicht der Diesel wenn alle Aggregate laufen? Und wie lange, wenn nur Aggregat 1 und 3 läuft?

DAs ist die Grundtabelle mit meinen Daten. Die Diesel Lagermenge ist 500 Liter.

Wenn ich die Tivot Tabelle erstelle, wird die verbleibende Betriebszeit natürlich zusammen gerechnet. Dabei müsste sie sich verringern, je mehr Aggregate genutzt werden .

Das ist schon mal das Hauptproblem, dass ich auch nach langer Recherche nicht gelöst bekomme....

Danach kommt noch ein ormatierungsproblem.

Ich möchte nur einen einzigen Balken als Anzeige auswerfen. Dieser kann dann unterteilt sein in die einzelnen Aggregate.

Aktuell sieht es so aus:

Das erste Problem ist aber das größere.

Ich hoffe so sehr, das mir jemand helfen kann .... :(

Grüße

...zur Frage

Pivot: Wie kann ich Monatssummen auch über Jahreswechsel aufsummieren/kumulieren?

Hallo, wie kann ich bei Pivot Monatssummen über den Jahreswechsel hinaus aufsummieren?

Mein Problem: sobald der Zeitraum übers Jahresende geht (zB Sept 23 bis April 24), funktioniert das Kumulieren leider nicht mehr.

Variante 1: Bei Darstellung rein nach Monat werden die Monate leider nicht zeitlich richtig sortiert, sondern unabhängig vom Jahr. ZB: Februar 24, März 24, September 23, November 24, .... Somit habe ich bei den kumulierten Summen einen falschen Wert

Variante 2: Versuche ich dem entgegen zu wirken mit Gruppierung in Jahre, dann habe ich zwar die Monate jahresmäßig richtig sortiert. Es wird aber nur innerhalb vom Jahr kumuliert und ich habe eine Summe mit Dezember 23 und im Jänner 24 steht dann nur der mtl. Wert von Jänner und nicht der kumulierte Wert inkl. 23.

Hinweis: In der Datenbasis habe ich u.a. die Spalte Anmeldedatum (dd.mm.yy) und das Feld Anmeldedatum ziehe ich in den Bereich "Zeile".

Danke für die Hilfe und lg

Marietta

...zur Frage

Wie kann ich Geburtstage in Excel automatisch in den entsprechenden Monat übertragen und sie aufsteigend sortieren?

Hallo zusammen,

ich habe hier folgende Tabelle:

In Spalte A werden Namen eingegeben.

In Spalte B das zugehörige Geburtsdatum.

In Zeile D1 steht der Monat "Januar", in F1 "Februar", in H1 "März", etc.

Das in Spalte B eingetragene Geburtsdatum soll nun automatisch zusammen mit dem zugehörigen Namen in den entsprechenden Monat übertragen werden. Hier Februar.

In dem entsprechenden Monat (hier Februar) sollen die Geburtstage aufsteigend sortiert sein. 01., 03., 08. und 09.

Gibt es eine Formel, mit der ich das erreichen kann?

Vielen Dank für eure Rückmeldungen.

...zur Frage

Excel - Leerzeichen bei "Suchen und ersetzen"?

Hallo,

durch die Funktion Suchen und ersetzen habe ich Begriffe entfernt in der Zeile 9 - 11. Jetzt steht davor ein Leerzeichen. Gibt es eine Möglichkeit dieses Leerzeichen automatisch in der gesamten Spalte zu entfernen bzw. schon beim Vorgang "Suchen und ersetzen" dass das Leerzeichen erst gar nicht entsteht. Hier könnte ich es manuell ändern, aber bei einer Datei mit mehreren tausend Zeilen nicht.

...zur Frage

Excel 2010 Formel mit bedingung

Hey Leute,

ich habe in einer tabelle Zeiten eingetragen wo ich mir Dynamisch Überstunden ausrechnen lassen will.

Dazu habe ich bereits eine Formel in H4 die auch oben zu sehe ist, mit der die Gesamtstunden für den Monat August ausgerechnet werden. (Für September werden dementsprechend die Stunden für September ausgerechnet.)

Nun wollte ich aber nur die Überstunden alleine wissen.

Dafür wollte ich hinter die Formel in H4 noch so oft 8 Stunden abziehen lassen wie Tage im August gearbeitet wurden. In dem fall 15.

Aber da ich das Übertragbar gestalltet haben möchte kann ich nicht einfach -(15*8) rechnen...

Hat da jemand einen Vorschlag?

MfG Pinappple

...zur Frage

Wie kann ich in Excel eine Formel =SUMME(ZÄHLENWENN( nach rechts in die Spalten kopieren und Excel in den Suchkriterien eine Zeile nach unten geht?

Hallo liebe Excel Experten,
Ich habe eine Tabelle von $B$1:$G$1000 jetzt möchte ich die Formel =SUMME(ZÄHLENWENN( anwenden.
Die erste Spalte H Zelle 1 wäre dann {=SUMME(ZÄHLENWENN($B$1:$G$1;$B1:$G1))}. dann kopiere ich das bis H1000
Spalte I Zelle 1 gleicher Bereich aber Suchkriterien $B2:$G2.
Wer könnte mir die Formel so umbauen das Excel beim kopieren nach rechts in Spalte I,J,K,L usw. in den Suchkriterien automatisch eine Zelle nach unten geht? Ist das überhaupt möglich oder kann man diese Aufgabe auch irgendwie anders lösen?
Für eure Hilfe wäre ich natürlich wieder überaus dankbar.
MfG Uwe

...zur Frage

Excel Bezug auf anderes Tabellenblatt durch Zelllink?

Hallo zusammen,

ich habe da folgende Frage, ich habe mir eine kleine Formel gebastelt, die mir die Summen innerhalb eines gewissen Datumsbereichs auf einem anderen Tabellenblatt einsammelt: Die Formel ist die folgende:

=SUMMEWENNS('2019'!$G:$G;'2019'!$A:$A;">="&DATUM($A22;SPALTE()-1;1);'2019'!$A:$A;"<="&DATUM($A22;SPALTE()-1;28))

Die Formel tut zur Veranschaulichung folgendes auf Tabellenblatt 2019 schauen ob in Spalte A das Datum in dem Bereich liegt und bildet dann die Summe der Spalte G in den betroffenen Bereichen.

Damit ich mir die Formel einfach runter/zur Seite ziehen kann hab ich bei Monat und Jahr mit einem Direktbezug bzw. einfach mit dem Spaltenindex gearbeitet.

Damit ich das ganze aber noch voll automatisieren kann würde ich gerne ebenso das '2019'! durch einen Link auf die Zelle A22, die für das aktuelle Datum genutzt wird austauschen, ist das irgendwie möglich?

Ergänzend noch eine weitere Frage, der maximal Monatstag, in dem Fall im Februar 28 bzw. 29, kann ich den nicht auch irgendwie automatisch generieren?, denn schließlich variiert der von Monat zu Monat und ich müsste ihn in jeder Formel erneut eingeben und im Falle des Februars sogar noch drauf achten ob Schaltjahr oder nicht. Denn wenn ich einfach immer Jahr;Monat;31 eingebe, kommt murks raus, keine Ahnung was dann Excel rechnet, aber dann werden noch weitere Tage zu raten gezogen, die gar nicht im Februar liegen.

Ich bedanke mich schonmal für Eure Inspiration.

MfG Gipfelstürmer

...zur Frage

Excel - Pivot Tabelle nach Werten sortieren?

Hallo,
ich habe eine Pivot Tabelle in Google Tabellen erstellt, siehe Screenshot. Ich habe eine Zeile eingefügt und dann Werte eingefügt. Ich möchte gerne nach den Werten sortieren, also das die höchsten Verkäufe danach sortiert wird. Ich kann aber nur nach der Zeile sortieren. Habt ihr eine Idee wie ich nach Werten sortieren kann?

...zur Frage

Formeln für ein Parallelogramm?

Wie lautet bei einem Parallelogramm die Formel zum Ausrechnen der Umfangslänge, des Flächeninhalts, Seitenlängen G1 und G2 und die der zugehörigen Höhen H2 und H1?

...zur Frage

Was möchtest Du wissen?

Deine Frage stellen