Formeln können sich aus Excel-Funktionen, fixen und dynamischen Bezügen (Formel-IDs), Konstanten und Operatoren zusammensetzen. Es kann aber auch einfach nur ein Text angegeben werden, welcher dann beim Ausführen der Auswertung in die Excel-Zellen geschrieben wird.
Wichtig
Beachten Sie, dass in einer Formel die englischen Excel-Funktionsnamen, Kommas statt Strichpunkte und Punkte als Dezimaltrennzeichen verwendet werden müssen.
Sollte es Probleme mit den Angaben in einer Formel geben, werden Sie von Excel beim öffnen der Arbeitsmappe darauf aufmerksam gemacht.
Excel-Formel / Excel-Funktion
Wenn die Formel mit einem Gleichheitszeichen beginnt, wird eine Berechnung in Excel durchgeführt und der Zellenwert ist das Ergebnis einer Excel-Formel.
Eine einfache Excel-Formel wäre z.B.:
= 1 + 2 + 3
Eine Excel-Formel kann sich aber auch aus Bezügen und Excel-Funktionen zusammensetzen wie z.B.:
= SUM(A1,B1) - 25 * MIN(C1:C6) + D1
Excel-Funktionen können über die Aktion Excel-Funktion hinzufügen zu einer Formel hinzugefügt werden. Dabei wird eine Seite geöffnet, welche eine Übersicht aller eingerichteten Excel-Funktionen anzeigt.
Weitere Informationen zu Formeln und Funktionen finden Sie unter:
https://support.microsoft.com/de-de/office/294d9486-b332-48ed-b489-abe7d0f9eda9
Fixe Bezüge
Beispiele für fixe Bezüge:
Diese Formel
Bezieht sich auf
Und gibt Folgendes zurück
=C2
Zelle C2
den Wert in Zelle C2
=A1:F4
Zellen A1 bis F4
die Werte in allen Zellen, Sie müssen jedoch zusätzlich das Feld Matrixformel setzen
Statt eines fixen Bezuges kann auch eine Formel-ID angegeben werden, die dann beim Ausführen der Auswertung durch den aktuellen Wert ersetzt wird.
Formel-IDs können über die Aktion Formel-ID hinzufügen zu einer Formel hinzugefügt werden. Dabei wird eine Seite geöffnet, welche eine Übersicht aller verfügbaren Formel-IDs anzeigt.
Folgende Formel-IDs stehen in jeder Auswertung zur Verfügung:
Formel-ID
Wird ersetzt durch
[%1]
den Wert des aktuellen Feldes
[TOTALVALUE]
Wird bei einem numerischen Feld (Dezimal, Integer, BigInteger) durch die Summe der Feldwerte aller Datensätze ersetzt.
Mit diesen Formel-IDs lässt sich z.B. die alte NC Cube Feldeinstellung Anzahl-/Summenfeld umsetzen.
Da die Funktionalität jetzt als Formel-ID realisiert wurde, ergeben sich neue Möglichkeiten.
Wird z.B. beim Feld Verkauf (MW) des Debitors die Formel =100*[%1]/[TOTALVALUE] angegeben, wird der prozentuale Wert des Betrags berechnet.
[TOTALVALUE] wird dabei durch den gesamten Verkauf (MW) aller Debitoren innerhalb der Filter ersetzt.
[%1] wird durch den Verkauf (MW) des Debitors ersetzt.
Tipp
Die Summen werden immer über alle Datensätze innerhalb der Filter berechnet. Dabei spielt es keine Rolle, wie viele Datensätze tatsächlich ausgegeben werden.
Wenn Sie z.B. zu einem Debitor die Summe(n) bestimmter Debitorenposten berechnen möchten, können Sie in den Tabelleneinstellungen der Debitorenposten die Beziehung auf Erster Datensatz setzen.
Dadurch kann ein automatischer Zeilenumbruch vermieden werden. Die Summen werden nach wie vor über alle Debitorenposten berechnet.
[COL]
die aktuelle Excel Spaltennr.
[ROW]
die aktuelle Excel Zeilennr.
[USERID]
die aktuelle Benutzer ID
[COMPANY]
den aktuellen Mandantennamen
[PERIOD_TEXT]
den aktuellen Zeitraum (z.B. 2022/03 März) einer (übergeordneten) Datumsschleife
[PERIOD_DATE]
den aktuellen Zeitraum (z.B. 01.03.22..31.03.22) einer (übergeordneten) Datumsschleife
[TOTALRECORDS]
die Anzahl aller Datensätze
[RECORDNO]
eine fortlaufende Nummer für den aktuellen Datensatz
[RECORDID]
die RecordID des Datensatzes
Benutzerdefinierte Formel-IDs
Wird bei einem Feld eine Formel-ID angegeben, kann die ID dazu verwendet werden, um in Formeln einen Bezug zum Feldwert herzustellen oder darauf zuzugreifen.
Die Formel-IDs in den Formeln werden beim Ausführen der Auswertung durch die aktuellen Zellenbezüge der Felder ersetzt.
Wird einem internen Feld eine Formel-ID zugewiesen, kann in Formeln auf den Wert statt auf den Bezug des internen Feldes zugegriffen werden.
Hinweis
Die Zeilennr. im Zellenbezug einer Formel-ID wird nach Durchlaufen der 1:n Verknüpfung oder der Baumstruktur, in der die Formel-ID definiert ist, auf die aktuelle Zeilenr. zurückgesetzt.
Interne Felder müssen, damit sie einen Wert enthalten, vor den Formeln angegeben sein, in denen sie verwendet werden.
Der Wert eines internen Feldes wird nach Durchlaufen der 1:n Verknüpfung oder der Baumstruktur, in der die Formel-ID definiert ist, zurückgesetzt.
Beispiel 1
Anmerkung: Dieses Beispiel dient nur zur Erklärung und nimmt eine sehr vereinfachte Datenbasis an in welcher nur positive Debitorenposten existieren.
Das Beispiel kann im Downloadbereich heruntergeladen werden.
Folgendes Datenblatt wurde angelegt:
Für jeden Debitorenposten wird zuerst der prozentuale Anteil vom Saldo (MW) des Debitors berechnet.
Dafür wurde dem Feld Saldo (MW) die Formel-ID [S] und dem Feld Betrag (MW) die Formel-ID [B] zugewiesen.
Der Prozentwert kann dadurch in einem Formel-Feld über die Formel =100/[S]*[B] berechnet werden.
Zusätzlich werden alle Posten markiert, die über einem gewissen Prozentwert liegen.
Dafür wurden dem Prozent Formel-Feld die Formel-ID [P] zugewiesen.
Der Grenzwert ist in diesem Beispiel über eine Textzeile vor der Daten-Tabelle realisiert.
Als Default-Wert sind für den Grenzwert 50 Prozent hinterlegt. Dem Grenzwert wurde die Formel-ID [GP] zugewiesen.
Folgendes Ergebnis wird nach dem Erstellen der Auswertung in Excel angezeigt:
Tipp
Um sich in Excel in den Zellen die Formeln anstelle der Ergebnisse anzeigen zu lassen, klicken Sie in der Menüleiste auf den Reiter Formeln und wählen Sie im Bereich Formelüberwachung die Aktion Formeln anzeigen aus.
Die dynamischen Bezüge wurden wie folgt umgewandelt:
[S] wurde durch den Zellenbezug des Feldes Saldo (MW) ersetzt.
[B] wurde durch den jeweiligen Zellenbezug des Feldes Betrag (MW) ersetzt.
[P] wurde durch den jeweiligen Zellenbezug des Prozent Formel-Feldes ersetzt.
[GP] wurde durch den Zellenbezug des Grenzwert Feldes ersetzt.
Beispiel 2
Für jedes Feld (ausgenommen interne Felder), für das eine Formel-ID angegeben wurde, stehen zusätzlich 2 weitere Formel-IDs zur Verfügung.
Für das Feld Saldo (MW) sind z.B. zusätzlich noch folgende Formel-IDs verfügbar:
[S:COL] wird z.B. durch die Excel Spaltennr. des Feldes Saldo (MW) ersetzt.
[S:ROW] wird z.B. durch die Excel Zeilennr. des Feldes Saldo (MW) ersetzt.
Aufbauend auf Beispiel 1 soll nun zusätzlich die Anzahl der Posten zum Debitor angezeigt werden.
Das Beispiel kann im Downloadbereich heruntergeladen werden.
Version 1:
Hier wird die *:COL] Formel-ID verwendet.
Die Änderungen wurden im Bild markiert.
In Version 1 wird das Feld Debitorennr. zu den Posten hinzugefügt.
Über die Formel =COUNTIF([PNR:COL]:[PNR:COL],[NR]) werden alle Zeilen gezählt, bei denen in der Spalte mit der Formel-ID [PNR] der Wert der Formel-ID [NR] und somit die Nr. des Debitors steht.
Version 2:
Nicht immer findet sich ein einfaches Kriterium für die Abfrage.
Es könnte auch sein, dass sich das Kriterium in nachfolgenden Zeilen wiederholt.
Version 2 zeigt eine alternative Lösung.
Hierfür werden die *:COL], *:ROW] und [ROW] Formel-IDs verwendet.
Die Änderungen wurden im Bild markiert.
In dieser Version wird ein Formel-Feld Debitor RowID zu den Posten hinzugefügt.
Das Feld wird über eine Formel mit der Zeilennr. des Feldes Anzahl Posten und somit mit der Zeilennr. des Debitor-Datensatzes befüllt. Damit hat jeder Posten ein eindeutiges Kriterium für die Abfrage.
Über die Formel =COUNTIF([RID:COL]:[RID:COL],[ROW]) werden alle Zeilen gezählt, bei denen in der Spalte mit der Formel-ID [RID] der Wert der Zeilennr. des Debitor-Datensatzes steht.
Formel-Hilfe
Die Aktion Formel-Hilfe zeigt eine Übersicht aller in der aktuellen Formel verwendeten Excel-Funktionen und Formel-IDs an. Über diese Übersicht kann z.B. die Hilfe zu den verwendeten Excel-Funktionen aufgerufen werden. Zusätzlich wird für alle Formel-IDs angezeigt, wodurch diese ersetzt werden, wenn die Auswertung ausgeführt wird.
Übersetzen
Beachten Sie, dass in einer Formel die englischen Excel-Funktionsnamen, Kommas statt Strichpunkte und Punkte als Dezimaltrennzeichen verwendet werden müssen. Falls notwendig, können die Excel-Funktionsnamen der Formel mit dieser Aktion in englische Excel-Funktionsnamen übersetzt werden.
Zusätzliche Einstellungen
Formel formatieren als
Gibt an, ob die Formel bzw. das Ergebnis der Formel in Excel als Zahl oder als Datum formatiert werden soll.
Dezimalstellen
Gibt die Anzahl der Dezimalstellen an, die in Excel angezeigt werden sollen, wenn die Formel als Zahl formatiert wird.
Ohne 1000er-Trennzeichen
Gibt an, ob ein 1000er-Trennzeichen verwendet werden soll oder nicht.
Beim Öffnen der Arbeitsmappe werden die Formeln von Excel überprüft.
Falls dabei ein Problem festgestellt wird, z.B. wenn eine Formel falsch angegeben wurde, erscheint folgende Meldung:
Wir haben ein Problem bei einigen Inhalten in '...xlsx' erkannt. Sollen wir so viel wie möglich wiederherstellen?
Klicken Sie auf Ja um weitere Informationen zu erhalten und überprüfen Sie die Arbeitsmappe.
Formeln
Weitere Informationen zu Formeln und Funktionen finden Sie unter:
https://support.microsoft.com/de-de/office/294d9486-b332-48ed-b489-abe7d0f9eda9
Für jeden Debitorenposten wird zuerst der prozentuale Anteil vom Saldo (MW) des Debitors berechnet.
Dafür wurde dem Feld Saldo (MW) die Formel-ID [S] und dem Feld Betrag (MW) die Formel-ID [B] zugewiesen.
Der Prozentwert kann dadurch in einem Formel-Feld über die Formel =100/[S]*[B] berechnet werden.
Zusätzlich werden alle Posten markiert, die über einem gewissen Prozentwert liegen.
Dafür wurden dem Prozent Formel-Feld die Formel-ID [P] zugewiesen.
Der Grenzwert ist in diesem Beispiel über eine Textzeile vor der Daten-Tabelle realisiert.
Als Default-Wert sind für den Grenzwert 50 Prozent hinterlegt. Dem Grenzwert wurde die Formel-ID [GP] zugewiesen.
Folgendes Ergebnis wird nach dem Erstellen der Auswertung in Excel angezeigt:
[S] wurde durch den Zellenbezug des Feldes Saldo (MW) ersetzt.
[B] wurde durch den jeweiligen Zellenbezug des Feldes Betrag (MW) ersetzt.
[P] wurde durch den jeweiligen Zellenbezug des Prozent Formel-Feldes ersetzt.
[GP] wurde durch den Zellenbezug des Grenzwert Feldes ersetzt.
Die Änderungen wurden im Bild markiert.
In Version 1 wird das Feld Debitorennr. zu den Posten hinzugefügt.
Über die Formel =COUNTIF([PNR:COL]:[PNR:COL],[NR]) werden alle Zeilen gezählt, bei denen in der Spalte mit der Formel-ID [PNR] der Wert der Formel-ID [NR] und somit die Nr. des Debitors steht.
Die Änderungen wurden im Bild markiert.
In dieser Version wird ein Formel-Feld Debitor RowID zu den Posten hinzugefügt.
Das Feld wird über eine Formel mit der Zeilennr. des Feldes Anzahl Posten und somit mit der Zeilennr. des Debitor-Datensatzes befüllt. Damit hat jeder Posten ein eindeutiges Kriterium für die Abfrage.
Über die Formel =COUNTIF([RID:COL]:[RID:COL],[ROW]) werden alle Zeilen gezählt, bei denen in der Spalte mit der Formel-ID [RID] der Wert der Zeilennr. des Debitor-Datensatzes steht.
