Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1636to1640
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

Pivot – Formel für berechnetes Feld

Pivot – Formel für berechnetes Feld
27.07.2018 17:16:39
niclaus
Grüezi miteinander
Ich habe eine Tabelle "Mitgliederbeitraege". Jedes Mitglied erhält eine Rechnung (Spalte Rechnung). In den Spalten ZhlgDat und ZhlgBetr trage ich ein, wann und wieviel die Rechnungsempfänger bezahlt haben.
Als Grundlage für die Liquiditätsplanung der kommenden Jahre muss ich wissen, wie hoch der prozentmässige monatliche Anteil der Zahlungsbeträge am Gesamt-Rechnungsbetrag ist. Ich muss das in Prozentzahlen wissen, weil die Anzahl Mitglieder und die Höhe der Rechnungen jedes Jahr stark variieren. – Aufgrund der aktuellen Zahlen kann im kommenden Jahr damit rechnen, dass gut die Hälfte der Rechnungen im Mai bezahlt werden, gut 1/3 im Juni usw.
Um diese Prozentwerte zu berechnen, habe ich in die Pivot-Tabelle ein Feld "%_Rechnungen" eingefügt mit der Formel
=ZhlgBetr/10996.8
10996.8 entspricht dem Total der ausgestellten Rechnungen. Da sich dieses Total immer wieder ändert, muss ich die Feld-Formel immer wieder von Hand ändern.
Meine Frage: Gibt es eine Möglichkeit, diese Feld-Formel anders zu formulieren? Kann ich das Total der Rechnungen mit einer Formel oder mit einem Bezug in die Feld-Formel übernehmen?
Ich habe es (ohne brauchbares Ergebnis) versucht mit der Formel
=ZhlgBetr/(ZhlgBetr + Offen)
Oder muss ich die Pivot ganz anders aufbauen?
Vielen Dank und viele Grüsse
Niclaus
https://www.herber.de/bbs/user/122953.xlsx

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Pivot – Formel für berechnetes Feld
27.07.2018 18:01:47
Luschi
Hallo Niclaus,
für sowas fehlt den normalen Pivottabellen die Formelintelligenz von Power-Pivot.
In diesem kosternlosen COM-AddIn von M$ für Excel wurden die berechneten Felder (Measures) um viele DAX-Funktionen erweitert. So gibt es die SUMX-Funktion, die die von Dir erwähnte Aufgabe berechnet und als Extrafeld in der Pivotttabelle bereitstellt. So kann man dann in der Pivottabelle auf dieses Feld in einer Formel zugreifen.
Gruß von Luschi
aus klein-Paris
AW: eine kleine Standard-Formel tut es auch ...
27.07.2018 18:47:42
neopa
Hallo Niclaus,
... wie Luschi schon schrieb, ist die Funktionalität des berechneten Feldes dazu ungeeignet. Diese hat wohl immer noch die aus dem vorigen Jahrtausend. Aber man kann sich mit Standardfunktionalität helfen.
In Deinem Fall nutze z.B. folgenden Lösungsvorschlag in der Spalte neben Deiner Pivotauswertung. Formel C4 inklusive deren bedingten Formatierung einfach weit genug nach unten ziehend kopieren.
 ABC
3Datum
Zahlung
Summe von
ZhlgBetr
proz. Ant.
der R.-S.
4Mai53.401,2051,85%
5Jun35.076,1034,06%
6Jul12.211,3011,86%
7Gesamtergebnis100.688,6097,76%
8   

Formeln der Tabelle
ZelleFormel
C4=WAHL(1+(B4<>"")+(LINKS(A4;6)="Gesamt"); "";B4/Tabelle2[[#Ergebnisse];[Rechnung]];SUMME(C$3:C3))

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C41. / Formel ist =(LINKS(A4;6)="Gesamt")Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: eine kleine Standard-Formel tut es auch ...
27.07.2018 22:48:02
niclaus
Hallo Luschi, hallo Werner
Danke schön für Eure Beiträge.
@Werner: Auf das Naheliegendste - "normale" Formeln einzusetzen - bin ich gar nicht gekommen!
Ich habe in der Zwischenzeit ein Makro entwickelt, das die berechnete Formel "%_Rechnungen" aktualisiert.
Sub PivotAktual() On Error GoTo finis1 pname = ActiveCell.PivotTable.Name rtotal = Range("G3").Value ActiveSheet.PivotTables(pname).CalculatedFields("%_Rechnungen"). _ StandardFormula = "=ZhlgBetr/" & rtotal ActiveSheet.PivotTables(pname).PivotCache.Refresh Exit Sub finis1: MsgBox "Es muss mindestens eine Zelle der Pivot markiert sein!" End Sub
Ich habe einige Tests damit durchgeführt. Ich glaube, es verhebt. *)
Grüsse Niclaus
*) verheben: Das beste Schweizer Wort überhaupt! Es gehört zu den unübersetzbaren schweizerdeutschen Wörtern und bedeutet: standhalten, sich bewähren
Anzeige
AW: eine kleine Standard-Formel tut es auch ...
28.07.2018 10:52:06
fcs
Hallo Niclaus,
nach deinem Wunsch in dem anderen Thread hab ich mir deine Datei auch noch einmal angesehen.
Wenn du ohne Makro auskommen möchtest, dann kannst du im Blatt "Mitgliederbeitraege" eine Spalte ergänzen, in der der Prozentanteil der Rechnungsbetrage an der Summe der Beträge berechnet Wird.
Im Pivot-Bericht berechnest du dann die Summe der Prozentwerte.
Das Makro zur Aktualisierung des berechneten Feldes hab ich auch mal getestet.
Mit deutschen Systemeinstellungen hatte ich Probleme mit dem Dezimalzeichen im Summenwert.
Glückliche Scheiz, die hier das gleiche Zeichen verwendet wie USA - VBA ist im Hintergrund nun mal US-basierend.
Da musste ich noch eine Komma-Punkt-Konversion einbauen.
Folgende Möglichkeiten zur Optimierung gibt es noch:
  • Pivot-Tabellenname ermitteln
    Die Zelle zu der der Pivot-Table-Name ermittelt werden soll wird im Code fest vorgegeben

  • Summe der Rechnungsbeträge
    Das Makro liest die Summe der Rechnungsbeträge direkt ein. Dann kannst du dir die Berechnung in Zelle G3 sparen.

  • Fehlerbehandlung
    Die Behandlung von Fehler baue ich meistens etwas allgemeiner auf. So kann ich neben einer allgemeinen Fehlermeldung, die alle Fehler meldet noch einzelne Fehler-Nummern speziell behandeln und die Meldung anpassen oder besondere Aktionen per Code ausführen lassen.

  • Gruß
    Franz
    https://www.herber.de/bbs/user/122963.xlsm
    Anzeige
    AW: eine kleine Standard-Formel tut es auch ...
    29.07.2018 12:29:19
    niclaus
    Grüezi Franz
    Super! Herzlichen Dank!
    Glückliche Schweiz, die hier das gleiche Dezimal-Zeichen verwendet wie USA
    Oder umgekehrt: Switzerland first? ;-))
    Einen schönen Sonntag noch! Grüsse Niclaus

    300 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige