Das Thema Datum ist gerade im Tagesgeschäft ein wichtiger Punkt der sich auch im Berichtswesen, wie im Artikel "Datumsfunktionen in Excel hier am Beispiel aus Stichtag Vormonat ermitteln" beschrieben, abzeichnet zeigt sich aber manchmal auch bei etwas einfacheren Fragestellungen im beruflichen Alltag.

Beim Einspielen von Bewegungsdaten über LSMW (siehe Artikel "Massenstammdatenpflege mit LSMW oder SECATT dank Transaktionsaufzeichnung - Handbuch erweiterte computergestützte Test-Tool (eCATT) und LSMW") wo sich eben diese Tools nicht nur zur Stammdatenpflege sondern auch für Massendaten wie Leistungsverrechnung, interner Kostenumbuchung oder Planwerterfassung (Budget) umfassen.

Am Beispiel einer Tabelle ist hier eine Liste mit Datum, Kostenstelle und Betrag zu finden.



Dabei gibt es aber beim Einbuchen der Daten das Problem, dass die Maske das Datum als TTMMJJJJ erwartet. Zwar lässt sich in der LSMW auch an einer Stelle die Option Datum in Format umwandeln aktivieren (siehe hierzu den Punkt "Datum einlesen" in dem die Datumswerte passend umgewandelt werden), aber diese SAP seitige Lösung einmal außen vor gelassen stellte sich die Frage im Kreis von Kolleginnen und Kollegen wie das Datum in einer Form ohne . geändert werden kann.

Die Änderung des Formats hat hier leider keine Wirkung, da diese inhaltlich den Datumswert weiterhin behält.

Der nächste Gedanke ist die Formel TEXT. So gibt die Formel =TEXT(A2;"TTMMJJJJ") das Datum ohne . als fixen Wert aus.



Per Formel ist dieses natürlich über eine Hilfsspalte möglich, die dann direkt auf die erste Spalte übertragen werden kann.

Ein erheblich einfacher Weg ist allerdings meine Lieblingsfunktion innerhalb Excel die über die Tastenkombination STRG und H genutzt werden kann. Diese Tastenkombination ruft dei Funktion "Suchen und Ersetzen" auf.



Damit sind die Punkte aus den Datumswerten entfernt.Durch die vorherige Formatierung als Datum erscheint nun aber das Zeichen # in Excel. Dieses ist ein Platzhalter, wenn entweder die Spalte zu klein ist oder Excel aus anderen Gründen den Wert nicht darstellen kann.



Dieses kann durch die Umstellung auf das Format Standard geändert werden, wie in folgenden zwei Schritten in der abschliessenden Abbildung ersichtlich ist.



Dieses ist zum Glück schnell über die rechte Maustaste und "Zelle formatieren" möglich und je nach Symbolleiste ebenfalls möglich umzustellen.

Im Ergebnis ist die Welt von Excel ein klein wenig mehr an die Welt von SAP angepasst.. Zum Ausgleich kann man aber fairerweise auch den Hinweis geben, dass natürlich auch an anderer Stelle sich SAP an Excel anpassen lässt.

Im Artikel "Darstellung negatives Vorzeichen in SAP - Standardlayout anpassen für vorangestelltes Vorzeichen" ist dieses ebenso behandelt wie es auch Tipps in Excel gibt (siehe "Grundlagen: Inhalte in Excel einfügen (Vorzeichen umkehren, Werte halbieren oder Verknüpfungen einfügen)") oder mein Lieblingsthema "Office Integration - Excelansicht in SAP und Daten kopieren nach Excel").

Das hier dargestellte Beispiel zeigt aber auch wunderbar, dass es in Excel nicht immer komplexe Formeln braucht sondern manchmal auch einfache Lösungen das gewünschte Ergebnis zu verbringen vermögen.



 

Im Controlling ist, unabhängig von eingesetzter Software oder Branche, immer auch eine Tabellenkalkulation wie Excel im Einsatz, so dass auch für umfangreichere SAP Berichte diese dann doch in der ein oder anderen Form in einer Anwendung wie Microsoft Excel weiter verarbeitet werden. Dieses hat aber gleichzeitig auch den Vorteil, dass hier die Datengrundlage aus SAP geliefert werden kann und die Daten später dann zum Beispiel in Form einer Pivot-Tabelle aktiv gefiltert werden können. Wie im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" beschrieben können so an die Adressaten eines Berichtes auch direkt Schnittchen gereicht werden, so dass die Daten passend aufbereitet werden können (bspw. durch Selektion einzelner Lehreinheiten) oder auch in Form von Diagrammen wie im Artikel "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen" dargestellt aufbereitet werden.

Im aktuellen Fall habe ich sowohl Plan/Ist Vergleiche (Planwerte als CO Budget) als auch Investitionen (als Anlagenzugang wie im Artikel "Auswertung Anlagenzugänge als Investitionen im Report Painter mit Ausweis CO Objekte Innenauftrag und Kostenstelle" beschrieben) und auch Verbindlichkeiten (als Obligo siehe Artikel "Report Painter Bericht für Obligo auf CO-Objekten mit interaktive Stammdatengruppen") sowie diverse Query zu Stammdaten zusammen gestellt.

Im Rahmen eines Quartalsberichtes sollen nun diese Daten zum Stichtag ausgewertet werden. Dabei habe ich die Spaltenüberschriften (wie Saldo zum Berichtszeitraum) so gestaltet, dass diese nicht direkt die Periode in der Spaltenüberschrift beinhalten. Dadurch ist es mir eher möglich nur die Datengrundlage auszutauschen und diverse Pivot-Tabellen einfach zu aktualisieren.

Im Folgeschritt habe ich dann in der Zelle eines Übersichtsblattes das Datum des Stichtags zum Bericht festgehalten. Eigentlich soll der Bericht quartalsweise erfolgen, so dass ja der Berichtszeitraum zwischen dem 1. und 4. Quartal liegen sollte. Abhängig vom Stichtag wäre dieses in der Regel dann der Vormonat / 3 unter der Annahme, dass ich immer im April, Juli, Oktober, Januar) die Auswertung erstelle.

Formel TEXT und DATUM

Durch bestimmte Ereignisse kann es aber auch sein, dass einfach zum Vormonat der Bericht im August erstellt wird. Entsprechend habe ich eine Formel gesucht in der aus einen gegebenen Datum direkt der Vormonat ausgegeben wird.

Der erste etwas komplizierte Ansatz war folgende Formel:

=TEXT(DATUM(JAHR(D1);MONAT(D1)-1;1);"MMMM")&" "&JAHR(D1)

Dabei wurde über die Formel Datum der Stichtag aus Zelle D1 genommen und einfach der 1. des Vormantes genommen. Diese Formel hat jedoch einen gewaltigen Nachteil, wenn mal ein Bericht zum Januar erstellt wird. Dieses wäre dann eine Neuauflage von "Zurück in die Zukunft mit Excel".

Der Vorteil in Excel ist, dass jedes Datum als Tag vom 1.1.1900 gezählt wird. Jeder neue Tag wird also als weiterer Tag vom 1.1.1900 an gezählt.

Kurioses zur Behandlung von Datumswerten in Excel

Durch den Artikel "#Schaltjahre – #Hoppala" auf soprani.at verweise ich hier gerne auf eine Besonderheit bzgl. des 60. Tages. Dieser wird in Excel als 29.2.1900 ausgewiesen, obgleich dieses gar kein Schaltjahr ist. Ein Schaltjahr tritt dann auf, wenn ein Jahr durch 4, aber nicht auch durch 100 ohne Rest teilbar ist, mit der Ausnahme, dass ein durch 400 ohne Rest teilbares Jahr wiederum ein Schaltjahr ist. Somit handelt es sich beim Jahr 1900 nicht um ein Schaltjahr...aber auch Excel darf sich mal irren.

Da Datumswerte in Excel unformatiert einfach nur Zahlen sind kann der Vormonat relativ einfach durch die Formel Datum - Tag im Monat errechnet werden. Dadurch erhalten wir den letzten Tag des vorherigen Monats.

Über die Formel TEXT( Zahl, "Formatcode") kann dieses Datum dann entsprechend den Erfordernissen angepasst werden.

Dabei ist der Formatcode identisch zu den Formaten, die auch in der Funktion Zelle Formatieren bei Sonderformaten dargestellt werden.

Im folgenden Beispiel (siehe Abbildung) dürfte dieses klarer werden:



In der Zelle D1 ist der aktuelle Stichtag (Datum der Berichtserstellung) eingetragen. Dieses ist im Beispiel der 12.01.2018. Durch die Tastenkombination STRG und . kann auch das aktuelle Tagesdatum eingetragen werden (durch STRG und , als Zahlenwert).

In der Zelle D2 wird vom Stichtag der Tag des Monats abgezogen (im Beispiel vom 12.01.2018 also 12. Man könnte jetzt annehmen, dass es sich damit um den 0.1.2018 und somit 31.12.2017 handelt. Als Zahlenwerte entspricht das Datum jedoch 43112 für den 12. Januar 2018 (der 43.112 Tag nach 1.1.1900) bzw. um 12 reduziert 43100 und somit der 31.12.2017.

Nun sind jedoch sowohl die Zelle D1 als auch D2 als Datum formatiert.

Durch die Formel

=TEXT(D2;"MMMM JJJ")

Weise ich hier das Format MMM für den ausgeschrieben Monatsnamen und JJJ für das vierstellige Jahr zu. Entsprechend ist hier der Wert Dezember 2017 als Ausgabe erfolgt. Dieses entspricht auch den Monat zu den die Daten erhoben worden sind (bis Periode 12 2017).

Zusammengefasst kann dies also auf die Formel

=TEXT(D1-TAG(D1);"MMMM JJJ")

reduziert werden und es wird automatisch der Vormonat zum Stichtag erhoben.

Fazit

Manchmal sind es nur Kleinigkeiten die eine Arbeitsmappe wesentlich erleichtern, aber gerade durch solche Kniffe lassen sich dann Berichte recht gut anpassen und für eine spätere Verwendung auch recyclen. Ein weiterer Punkt, der nicht zu vernachlässigen ist sollte das Thema Tabellenformatvorlagen (wie im Artikel "Die eigene Tabellenformatvorlage (auch Pivot)"). Wobei das Thema Vorlagen für Excel-Arbeitsmappen und auch Methoden zur Gestaltung von Tabellenblättern im Artikel "Dateipfad und Dateiname als Fußzeile automatisch in Excel setzen" näher behandelt worden ist.

Ehrlicherweise muss ich jedoch zugeben, dass ich eher die Arbeitsmappe vom letzten Bericht verwende (und hier die Grunddaten austausche) anstatt hier mit Vorlagen zu arbeiten. Allerdings gehört eine gescheite Vorlage für künftige Tabellen tatsächlich auf die ToDo Liste nachdem die Sommererkältung abgeklungen ist.

Mittlerweile sind hier im Blog etwa 606 Artikel veröffentlicht und einige davon nehmen auch an der Ausschüttung der VG Wort für Texte im Internet teil. Sobald wieder einmal 100 Artikel fertig sind darf ich mich erneut um das Anfordern von neuen Zählpixeln der VG Wort kümmern und suche meistens hierzu wieder eine Excel Arbeitsmappe die mir aus der CSV Datei eine Liste der Identifikationscode für die VG Wort Zählpixel erstellt. Damit ich es künftig mit der Suche leichter habe, mag ich an dieser Stelle kurz meinen Workflow dazu vorstellen.

Ausgangslage:Was hat es mit der VG Wort bzw. METIS auf sich?

Die Verwertungsgesellschaft Wort (VG Wort) dürfte für die meisten durch die sogenannte Kopiererabgabe bekannt sein. Diese Einnahmen werden an Autoren und andere Urheber verteilt. Seit 2007 werden auch Onlinetexte durch die VG-Wort-Abteilung Meldesystem für Texte auf Internetseiten (METIS) vergütet. Dabei bekommen Texte auf Internetseiten ab einer bestimmten Länge und Zugriffszahl aus Deutschland Tantiemen je Artikel ausgezahlt (in der Regel trifft dieses auf Artikel mit 1.800 Zeichen und mindestens 1.500 Aufrufe im Jahr zu. Sehr hilfreich bei der Zählung von Zeichen in Blogartikeln ist das Angebot von www.zeichenzähler.de wo per Formular ein Text nach Zeichen, Wörter, ohne Leerzeichen und Absätze gezählt werden kann.

Alternativ kann hier auch das Tool von Sven Hörig genutzt werden, dass auch selbst per Teilwortsuche Füllwörter die man doch gerne verwendet zählt und beim Vermeiden hilft. Ein weiteres Tool ist "Wörter zählen" auf buchstaben.com. Diese Anwendung hat als zusätzlichen Nutzen, dass neben der Anzahl Wörter, Zeichen, Absätze und Sätze auch die Lesezeit und Sprechzeit mit aufgeführt wird.


Zum Hintergrund der VG Wort Vergütung bei Blogartikeln verweise ich auf "Autorenvergütung für Bücher oder auch Onlineartikel". 

Dazu werden sogenannte Zählpixel im Artikel eingebunden. Die einzelnen Texte müssen ferner kostenlos und frei verfügbar, also ohne Passwortschutz, im Netz lesbar sein. Damit ist dieses natürlich absolut passend für Blogartikel. Da bei ausreichenden Zugriffszahlen (per eingebundene Zählpixel gemessen) die Texte nur einmal an die VG Wort gemeldet werden müssen ist dieses auch kaum ein Aufwand und ich muss Anfang des Jahres lediglich die Texte nachmelden die entsprechende Zugriffszahlen hatten und noch nicht gemeldet waren.

Jedoch müssen beim Erfassen eines Artikels, mit entsprechender Länge, diese Zählpixel eingebunden werden um an der Zählung teilzunehmen. Diese können im T.O.M. (Texte online melden) der VG Wort heruntergeladen werden. 

VG Wort Zählpixel als CSV

Beim Herunterladen der VG Wort Zählpixel (zu finden unter tom.vgwort.de unter

• METIS (reguläre Ausschüttung)
• Zälmarkenbestellung

oder aber direkt über die URL

• https://tom.vgwort.de/portal/metis/secure/editOrderPersonalizedPixel

können nach Bestätigung der benötigten Anzahl der Zählmarken diese entweder direkt von der Seite, als PDF oder auch als CSV Datei heruntergeladen werden.

Mit Excel erhalten Sie hier eine Auflistung der Zählmarken als CSV Datei, wie auch im Dokument Projekthandbuch auf https://tom.vgwort.de/Documents/pdfs/dokumentation/metis/DOC_Urhebermeldung.pdf näher erläutert wird.

Dabei sind als Beispiele die Zählmarken sowohl als Zählmarke für HTML Texte als auch für Zählmarke für Dokumente (erlaubte Formate: PDF, ePub) angegeben.

In der folgenden Abbildung sind hier in der Spalte B die Zählmarken für HTML Texte zu sehen:



Aus nachvollziehbaren Gründen habe ich den entsprechenden öffentlichen Zählpixel markiert bzw. anonymisiert habe.

In der Spalte C wiederum sind die Zählmarke für Dokumente (erlaubte Formate: PDF, ePub) sowie der Private Identifikationscode zu sehen.



Hierbei ist der Öffentlicher Identifikationscode hinter den gelben Balken und hinter den roten Balken der private Identifikationscode zu finden.

Anhand des HTML Code ist schon zu sehen, dass hier die Einbindung des Zählpixel über den HTML Code

<img src="http://vg09.met.vgwort.de/na/ÖFFENTLICHER-IDCODE" width="1" height="1" alt=""> als 1x1 Zählpixel erfolgt.

Dieses ist schon problematisch, wenn hier eine Seite über SSL Verschlüsselung aufgerufen wird, da hier das VG Wort Zählpixel über eine andere Domain und per https eingebunden werden muss.

Auf dieses Thema bin ich im Abschnitt "Einbindung VG Wort Zählmarken einer SSL verschlüsselten Webseite (https)" im Artikel "Webhosterwechsel und Umstellung von http:// auf https:// (SSL Verschlüsselung) und VG Wort Zählmarken" eingegangen.

Dankenswerterweise unterstützt mein Blogsystem am Ende eines Artikels im Editor auch gleichzeitig die Angabe ds VG-Wort Zählpixel und bindet dieses direkt über https ein, so dass ich später bei der Meldung nur noch aus einer Exceltabelle den privaten Identifikationscode heraussuchen muss und bei der Meldung meinen Blogartikel entsprechend kopieren kann.


Nachdem ich nun also weitere Zählmarken bestellt habe möchte ich diese gerne in einer Tabellenform haben, die folgende Spalten beinhaltet:

• Privater Identifikationscode
• Öffentlicher Identifikationscode

Dabei hilft mir seit Jahren eine einfache Exceltabelle in der ich die CSV Datei hineinlade und als Tabellenblatt VGWORT_ZÄHLMARKEN kopiere. Danach speichere ich diese als XLS Datei und wende folgende Schritte an.

Zwischenblatt Zählmarken
Hier ändere ich die VG Wort Zählmarken so ab, dass ich direkt nur die relevanten Zeilen der CSV Datei angezeigt bekomme. Bezug ist das Tabellenblatt VGWORT_Zählmarken und ich wende folgende Formeln an:
 

Auswertung VG Wort Marken

Spaltenüberschrift	Formel
Zählmarke	=WENN(VGWORT_Zaehlmarken!A7<>"";VGWORT_Zaehlmarken!A7;"")
Privat	=WENN(A3<>"";VGWORT_Zaehlmarken!C8;"")
Öffentlich	=WENN(A3<>"";VGWORT_Zaehlmarken!C7;"")

Dieses lasse ich von der Zeile 3 bis Zeile 300 Automatisch ausfüllen, so dass die 3 Zeilen je Zählmarke im Ergebnis auf eine Zeile zusammengefasst sind (siehe Schritt 1 in folgender Abbildung) und filtere dieses später nach Nicht-Leere Zeilen (siehe Schritt 2 in folgender Abbildung).



Im Ergebnis habe ich nun als nur noch Zeile 3, 6 , ... usw. mit den Privaten Identifikationscode als auch den HTML Code für die Einbindung eines Dokumentes als PDF oder ePub (als URL).

Dieses kopiere ich in ein neues Blatt und ersetze den HTML Code durch Suchen und Ersetzen (STRG + H) nach den nicht relevanten Teilen des Textes.

Somit wird die URL gelöscht (siehe Abbildung):



aber auch der Teil nach der Zählmarke wird entfernt



Damit sind die bestellten Zählmarken für den Bereich Texte im Internet / METIS der VG Wort direkt aufgeteilt nach Privater und Öffentlicher Identifikationscode.

Nachtrag:
Tatsächlich lassen sich die Formeln noch etwas optimieren, da die Zählmarken eine fixe Länge haben.

Derzeit habe ich in meiner "VG Wort Tabelle" ein Tabellenblatt CSV in das ich immer die heruntergeladene CSV aus TOM lade.

Im Tabellenblatt "Marken_aus_CSV" verfahre ich wie oben beschrieben, allerdings habe ich die öffentliche Zählmarke mit den Formeln LINKS, RECHTS optimiert:



Die Formeln lauten nun wie folgt.
 

VG Wort nur die Marken

Spalte	Formel zur Berechnung
Zählmarke	=WENN(CSV!A7<>"";CSV!A7;"")
Privat	=WENN(A3<>"";CSV!C8;"")
Öffentlich	=WENN(A3<>"";LINKS(RECHTS(CSV!C7;62);32);"")

Persönlich ergänze ich mir diese Liste noch nach Einbauunghsdatum (sehr hilfreich ist hier die Taste STRG + . welche das aktuelle Datum in Excel einfügt) sowie die Artikel-ID bzw. die URL zum gemeldeten Blogartikel.

Da ich mir nach 100 Artikeln, die den Kriterien der VG Wort entsprechen, immer wieder die entsprechende Vorlage heraussuchen muss ist es vielleicht hilfreich hier ebenfalls einmal die Formeln festzuhalten.

Mit ein wenig Aufwand ist dann das spätere Verwalten der Zählpixel gar nicht weiter schwer :-) Nebenbei ist das Thema VG Wort Zählmarken, wie schon im Artikel "EU DSGVO - Info- bzw. Link-Sammlung mit Schwerpunkt auf die Umsetzung bei Blogs oder der eigenen Website (hier: Autorenwebsite, Blogger, KMU)" beschrieben ebenfalls einer der Punkte zu denen weitere Informationen in der Datenschutzerklärung zu finden ist, aber das ist wieder ein anderes Thema :-)

Fazit

Sicherlich gibt es für andere Blogsysteme oder gar für das ein oder andere CMS auch eine Pluginlösung die automatisch die CSV einliest, Artikeln die entsprechenden Identifikationscode zuweist und auch einen internen Zähler bzgl. der Zeichen je Artikel integriert hat, aber gerade hier bin ich sehr dankbar, dass die Eigenentwicklung dieser Seite diese Punkte nicht umgesetzt hat sondern tatsächlich Inhalt und weniger die Zielzahl im Vordergrund steht. Die Freiheit ohne Redaktionsplan und zu Themen die mich gerade selbst beschäftigen oder die mir aus anderen Gründen wichtig sind schreiben zu können ist einer der Vorzüge dieser Bloggerei vor Ort. :-)

Blogartikel rund um das Thema VG Wort
Das Thema VG Wort hat mich auch in unterschiedlichen Blogartikeln beschäftigt.

• "CSV der VG Wort Zählmarken für den Bereich Texte im Internet (METIS) in Tabelle mit öffentlichen und privaten Identifikationscode der Zählmarken umwandeln" (also der derzeitige Artikel)
• "Webhosterwechsel und Umstellung von http:// auf https:// (SSL Verschlüsselung) und VG Wort Zählmarken"
• "VG Wort Nicht gemeldete URLs deaktiviert - Mindestzugriff nicht mehr gegeben durch Referrer Links auf Zählpixel"

 

---

Hinweis:

Mehr zum Thema #Autorenleben findet sich im Artikel "Autorenleben - Steuern und Selbstständigkeit, Verwertungsgesellschaft Wort (VG Wort), Autorenleben in Nebentätigkeit"  und wird von mir regelmäßig aktualisiert.

---

Während der Erstellung eines Finanzberichtes über eine Pivot-Tabelle zu den einzelnen Berichten kam auch ein altbekanntes Problem wieder auf, auf dass ich gleich noch einmal eingehen mag.

Zum Hintergrund der Finanzbericht beruht im wesentlichen auf die Auswertungen und Probleme die ich auch schon in folgenden Artikeln beschrieben habe:

Exkurs: Welche Daten wurden als Grundlage zusammen gestellt?
Auswertungen in Excel:

• "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt" (Excel, Grundtabelle)
• "SUMMEWENN über mehrere Spalten in Excel oder Personalkostenhochrechnung auf Innenauftrag zusammenfassen" (Excel, Grundtabelle)
• ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten" (Excel, Grundtabelle)

Auswertungen in SAP:

• "Report Painter Bericht für Obligo auf CO-Objekten mit interaktive Stammdatengruppen"
• "Auswertung Anlagenzugänge als Investitionen im Report Painter mit Ausweis CO Objekte Innenauftrag und Kostenstelle"
• Query mit Stammdaten
  • "Auswertung sprechender Nummernkreisintervalle von CO Innenaufträgen mit Query Zusatzfeldcoding und Unterscheidung numerischer oder alphanumerischer Schlüssel"
  • "Query zur Auswertung von Klassifizierungsmerkmale PSM Fonds zu CO Innenauftrag und Datumsfelder mit Konvertierung von FLOAT zu DATUM"
  • "Query über COEP, AUFK und FMFINCODE für Einzelposten Istkosten Innnenauftrag mit Stammdaten aus CO und PSM-FM sowie Spalten für Ertrag und Aufwand - Erster Teil Infoset als Datengrundlage"
  • "SAP Query Stammdaten PSM / CO Innenauftrag"
• "Saldenliste für Fonds im Haushaltsmanagement Saldo gegen Ertrag und Saldo gegen Budget"

Datenzeile gelöscht in Grundtabelle mit Grunddaten für Pivotauswertung

Im Ergebnis erhalte ich eine Grundtabelle, die ich auch als T_Grunddaten benannt habe und zu der ich eine Pivot-Tabelle angelegt habe. Wie in der folgenden Abbildung zu sehen sind hier für die Kostenstellen A bis Kostenstelle E diverse Werte vorhanden.



Allerdings gehört die Kostenstelle D einen anderen Fachbereich an, so dass in der Pivot-Tabelle hier später nur Kostenstellen A bis C ausgewiesen werden sollen. Wie in der folgenden Abbildung zu sehen ist hier aber auch die Kostenstelle D vorhanden.



Für die einzelnen Fachbereiche lösche ich nun in der Grundtabelle die nicht relevanten Datenzeilen (hier Kostenstelle D) und aktualisiere die Pivottabelle.

Geisterfelder in Filterauswahl

Wenn ich aber über die Zeilenbeschriftung (Kostenstelle) einen Filter setze erscheint hier immer noch die Kostenstelle D obgleich ich den Eintrag in den Grunddaten gelöscht habe, zur Verdeutlichung ist hier in den Grunddaten eine leere Zeile.

Welche in folgender Abbildung als (1) sowohl in den Grunddaten als auch in der Pivot-Tabelle zu sehen ist. Dennoch ist die Kostenstelle D weiterhin als (2) im Wertfilter vorhanden.



Martin Weiß (tabellenexperte.de) hat diese Felder treffenderweise im Buch "Excel Pivot-Tabellen für dummies" * als "Geisterfelder" bezeichnet.

Gerade im Controlling sind Pivottabellen weiterhin sehr hilfreich und  weitere Informationen zum Buch sind sowohl in den Buchempfehlungen "Excel Pivot-Tabellen (Excel, Pivot-Tabellen/Charts, Datenmodelle, Dashboard)"  als auch im Artikel "Buchempfehlung »Excel Pivot-Tabellen für dummies« von Martin Weiß ( tabellenexperte.de )" zu finden.

Da ich an der Arbeit selbst immer wieder nach der Ursache für diese Geisterfelder im Buch blättere mag ich die Lösung hier ebenfalls kurz vorstellen, obgleich diese auch schon Lukas Rohr (excelnova.org) im Artikel "Alte Elemente in Pivot Tabelle löschen" erläutert hat.

Über die Eigenschaften der Pivot-Tabelle ist im Register Daten der relevante Punkt zu finden:


Unter den Punkt Elemente behalten, die aus der Datenquelle gelöscht wurden ist die Option "Anzahl der pro Feld beizubehaltenden Elemente" auf KEINE zu setzen und alle Geisterfelder sind verschwunden.

Fazit: es spukt in Excel

Offensichtlich scheinen derzeit wirklich viele Geister in Excel zu stecken, denn neben Geisterfelder haben auch schon Geistermappen ihr Unwesen in meiner Tabellenkalkulation getrieben, wie auch im Artikel "Geistermappen in Excel durch alte Version der persönlichen Markroarbeitsmappe PERSONAL.XLSB und Frohe Ostern" zu sehen war.

Man könnte also tatsächlich sagen "In Excel sind die Geister los" und verwirren mich im Controlling und Berichtswesen.

Hier bin ich sehr dankbar, dass mir manch andere bei der Geisterjagd helfen.

Allerdings bin ich an der Arbeit auch noch auf ein anderes Problem aufmerksam gemacht worden und vielleicht hat hier ja jemand eine Idee:

Knobelaufgabe für Excelanwendende

In einer Grundtabelle für eine Pivottabelle wurden folgende Zeilen als Datenherkunft definiert:



Diese Pivottable bezieht sich auf eine Grundtabelle (Zelle A2 bis CG99643). Da dieses doch sehr viele Datensätze sind möchte ich am Anfang einige Zeilen löschen.

Also markiere ich diese Zellen Bspw. Zeile 3 - 180 und möchte diese per "Zeile löschen" entfernen.

Hier erhalte ich dann aber die Fehlermeldung:



"Wir können diese Änderung an den ausgewählten Zellen nicht vornehmen, da sie sich auf eine PivotTabelle auswirken. Wenn Sie Zellen einfügen oder löschen mögen, verschieben Sie die PivotTable, und versuchen Sie es dann erneut."

Vielleicht hat ja jemand hier eine Idee über einen Kommentar oder Mail würde ich mich sehr freuen.

Lösung:
Die Ursache dieser Fehlermeldung war dann tatsächlich überraschend simpel. Etwas verschoben von den Grunddaten war testweise eine Pivot-Tabelle eingefügt worden, so dass die nicht die Pivottabelle auf den einzelnen Blättern gemeint war sondern die Rumpfstruktur auf Höhe der Datengrundlage.... hier muss ich tatsächlich einen Kollegen gratulieren, der die Ursache gefunden hatte :-)

Über eine Rückmeldung als Kommentar oder per Mail (gerne auch per Facebook, Twitter oder Google) freue ich mich.


* Amazon Partnerlink
Als Amazon-Partner verdiene ich an qualifizierten Käufen über Amazon.

Wie schon am Ende im Artikel "Index und Vergleich statt SVERWEIS endlich verstanden und Suche über Verweis nur, wenn es auch etwas zu finden gibt" angekündigt sind die Verweisfunktionen (SVERWEIS oder auch INDEX,VERGLEICH) eingeschränkt wenn mehr als eine Übereinstimmung übernommen werden soll.

Hier bietet sich dann tatsächlich die Formel SUMMEWENN an.

Anhand des Syntax

=summewenn(BEREICH;SUCHKRITERIEN;SUMMEBEREICH)

hatte ich die Hoffnung, dass als Summenbereich tatsächlich mehr als eine Spalte genommen werden kann und so habe ich meine Hochrechnung als Tabelle formatiert und als Hochrecnung benannt.

Um ein einfaches Beispiel zu haben sieht diese Tabelle wie folgt aus:



Hier sind für einzelne Innenaufträge mehrere Hochrechnungen erfasst. Da es sich dabei aber um eine Hochrechnung von Personalkosten handelt kann ein Innenauftrag auch mehrfach vorkommen. Im Beispiel bekommt hier Andreas sowohl in Zeile 3 als auch Zeile 8 entsprechende Zahlungen. Ziel der Auswertung ist nun die zu erwarteten Kosten für Mai bis Dezember für den Innenauftrag Andreas zu erhalten.

Mein erster Versuch war hier tatsächlich statt SVERWEIS oder INDEX die Formel SUMMEWENN.

=SUMMEWENN(T_Hochrechnung[Innenauftrag];D3;T_Hochrechnung[[Mai]:[Dezember]])

Hier sollte eine Summe über die Spalten Mai bis Dezember für den Eintrag Andreas  beziehungsweise das Suchkriterium in der Zelle D3 erstellt werden.

Das Ergebnis von 10, wie in folgender Abbildung zu sehen war dann aber enttäuschend.



Offensichtlich kann der Summenbereich nur eine Spalte umfassen.

Zum Glück gibt es aber die Seite excelformeln.de auf der dann eine Lösung für mein Problem zu finden war.

Mal wieder eine Matrixfunktion, die im Beitrag "Nach Suchkriterium suchen und mehrere Spalten summieren (193)" erklärt wird.

In folgender Abbildung ist dann auch tatsächlich der zutreffende Wert von 80 berechnet worden.



Wichtig ist dabei, dass die Formel

=SUMME((T_Hochrechnung[Innenauftrag]=D3)*T_Hochrechnung[[Mai]:[Dezember]])

durch die Tastenkombination STRG + SHIFT und ENTER

als Matrixformel gespeichert wird.

Der Trick funktioniert dabei wie folgt:
Im ersten Teil der Summenfunktion wird geprüft ob die Spalte Innenauftrag mit der Zelle D3 übereinstimmt. Ist dieses der Fall ist das Ergebnis 1 (WAHR) sonst 0 (Falsch) dieses wird dann mit den Werten der Spalte Mai bis Dezember multipliziert, so dass hierdurch ein Ergebnis erzielt wird.

Der Nachteil dieser Vorgehensweise ist jedoch, dass man bei Änderung des Berichtszeitraum hier ebenfalls daran denken muss, dass es sich um eine Matrixformel handelt und diese mit der Tastenkombination angepasst werden muss, so dass aus Versehen diese, sofern nicht als Matrixformel abgeschlossen, einen Fehler hervorruft.

Wenn die Formel nicht als Matrixformel abgeschlossen wird ist das Ergebnis #WERT! (Fehler in Wert) und das mag auch niemand.



Nur als Matrixformel erscheint die {} im Formelfeld (ein manuelles Eingeben dieser Klammer hilft leider auch nichts).

Um etwaige Fehler zu vermeiden habe ich mich dazu entschlossen die Tabelle Hochrechnung um eine weitere Spalte zu erweitern:



Das schöne an "Als Tabelle formatierte" Tabellen ist ja, dass solche Formeln auch automatisch für jede Zeile mit ausgefüllt werden.

Damit habe ich also für jeden Innenauftrag die Formel
=SUMME(T_Hochrechnung[@[Mai]:[Dezember]])

und kann nun mit Summewenn wieder arbeiten, da ich hier ja nur nach einer Spalte suchen muss.



Durch die Unterstützung von Excel kann ich sogar direkt die Spalte Berichtszeitraum auswählen und erhalte so die Formel:

=SUMMEWENN(T_Hochrechnung[Innenauftrag];D3;T_Hochrechnung[Berichtszeitraum])



Diese Variante ist dann tatsächlich noch etwas einfacher als eine Matrixformel, auch wenn eine solche natürlich etwas cleverer ist.

Insgesamt erleichtert der Umgang mit der Funktion "Als Tabelle formatieren", wie im Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten", an vielen Stellen der Zugang auch zu komplexeren Formeln und Funktionen :-)
 

Berichtswesen nicht nur mit Excel
Beruflich ist ein Schwerpunkt meiner Arbeit das Controlling und Berichtswesen. Neben Excel arbeite ich hier auch besonders gerne mit SAP. Schon bei der Konzeption eines umfangreichen Berichtes und etwaiger Dashboards ist es hier hilfreich sich im Vorfeld passende Gedanken zu machen. Hier habe ich im Buch »Berichtswesen im SAP®-Controlling« (Buchvorstellung, für 19,95 EUR bestellen) einige Punkte festgehalten.

Im Blog finden Sie aber auch regelmäßig Praxisbeispiele rund um die Themen SAP, Berichtswesen und Controlling. Viele Beispiele sind dabei mit Bezug zur Hochschule aber können, wie der Artikel "Statistische Kennzahlen für Verrechnung in SAP - Umlage und Verteilung nicht nur im Hochschulcontrolling und Hochschulberichtswesen" auch für andere Branchen genutzt und als Grundlage zum Aufbau eines eigenen Berichtswesens genutzt werden.

Ich würde mich freuen, wenn meine Bücher (Publikationen) aber auch Schulungen (Workshop & Seminare) auch für Sie interessant wären. Weitere Partnerangebote, wie auch eine Excel Schulung zu Pivot finden Sie ebenfalls unter der Rubrik Onlineshop.