Manchmal ist es in Excel tatsächlich eine Frage, einfach die passende Formel zu finden um mit dieser zu arbeiten. Während schon der Artikel "Excel rechnet mit Farben oder ZÄHLENWENN bzw. SUMMEWENN anhand der Hintergrundfarbe der Zelle dank ZELLE.ZUORDNEN ohne VBA" hier eine selten genutzte Formel für eine bestehende Anfrage genutzt hat hat doch zumindest der Artikel "Vorteil von Excel Formatvorlagen und Filter nach Farben oder Zellensymbolen aus bedingter Formatierung" die Möglichkeiten der Filterung von Daten ins Spiel gebracht und dabei auch die Frage aufgeworfen, wie denn nun mit gefilterten Daten umgegangen werden soll.

Sofern ich eine Tabelle "Als Tabelle formatiere" (siehe auch Artikel ""Als Tabelle formatieren" um eine dynamische Datenquelle für Pivot-Tabellen zu erhalten") liefert mir Excel automatisch eine Lösung in der Ergebniszeile.

Hier wird in der Ergebniszeile automatisch die Formel TEILERGEBNIS verwendet, ohne dass man sich hier auf Anhieb bewust ist, worum es sich dabei eigentlich handelt.

Es stehen in der Ergebniszeile einfach die Möglichkeiten zur Erstellung einer Summe zur Verfügung und ebendiese wird auch ausgewählt.



In der Zelle B6 wird hier direkt die Formel =TEILERGEBNIS(109;[Betrag Summe]) vorgeschlagen um eine Summe über die Spalte zu ziehen.
 

Summe und Summewenn bei Filterfunktionen

Eine der ersten Formeln in Excel (mal abgesehen vom direkten Rechnen mit den einzelnen Zellen wie =B2+B3+B4+B5) ist die Formel SUMME um eine Summe über einen Zellenbereich zu ziehen. Diese Formel hat jedoch einen gewissen Nachteil, da sie immer eine Summe über den Zellenbereich zieht unabhängig davon, ob nun alle Daten angezeigt werden oder nicht. Sofern ich in der Tabelle CAESAR filtere würde mir eine Formel SUMME(B2:B5) weiterhin als Ergebnis 40 liefern, während die Formel Teilergebnis dann tatsächlich nur Anton, Berta und Detlef mit jeweils 10 zu 30 addieren würde.

Auf der anderen Seite kann dieses auch direkt gewünscht sein, da wie in oberen Beispiel die als "Nicht bezahlt" markierten Personen auch weiterhin über die Summenformeln wie SUMMEWENN oder SUMME weiterhin berücksichtigt werden.

Mit gefilterten Werten ein "Teilergebnis" als Summe berechnen

Im Formeltext (sofern man nicht einfach die Auswahl wählt) ist die Formel wie folgt aufgebaut:

TEILERGEBNIS(   Funktion ; Bezug ; [Bezug])

Positiv fällt schon einmal auf, dass hier mehr als ein Bezug möglcih ist, so dass ich auch mehrere Zellenbereiche auswerten kann. Die Frage ist nun nur, was es mit der Funktion auf sich hat.

Hier ist dann tatsächlich die Formelhilfe von Excel notwendig, die leider nur über den Formelassistenten verlinkt ist.

Zusammengefasst gibt es insgesamt 11 Funktionen die anhand von Nummern direkt angesprochen werden. Dabei berücksichtigen die Funktionen 1 bis 11 auch ausgeblendete Werte (die man manuell ausblendet) und die Formel 101 bis 111 ignoriert ausgeblendete Werte. Ausgefilterte Werte ignorieren dafür beide Formelfunktionen.

In der "als Tabelle formatierten" Datentabelle kann in der Ergebniszeile die einzelnen Funktionen direkt ausgewählt werden, dabei werden tatsächlich die 101 bis 109 Funktionen vorgeschlagen, womit ausgblendete Werte auch für die Summe oder die gewählte Funktion ausgeblendet bleiben.

Zu den wichtigsten Funktionen gehören 1,101 für den Mittelwert, 3,103 für Anzahl2 (nicht leere Zellen) oder auch 9,109 für die Summe.

Im Rahmen einer Excel-Schulung würde ich nicht auf die direkte Eingabe der Formel TEILERGEBNIS eingehen, da man sich tatsächlich die Funktion 101 oder 109 merken muss, aber sofern die Grundtabellen als Tabellen formatiert werden ist der Umgang damit gleich um ein vielfaches leichter
.

Summe über rausgefilterte Werte einer Liste

Tatsächlich lassen sich für eine bestimmte Fragestellung die Formeln SUMME und TEILERGEBNIS ebenfalls kombinieren.

Wenn eine Summe über ausgefilterte Werte erhoben werden sollen bietet sich eine Kombination der Formeln an.

Im Beispiel:

=SUMME(B2:B5) - TEILERGEBNIS(109;B2:B5)

Damit werden tatsächlich nur die ausgefilterten Werte summiert, da die Funktion SUMME weiterhin alle Werte umfasst und das TEILERGEBNIS nur die gefilterten Werte erfasst.

Fazit

Manchmal sind es tatsächlich einfache Formeln die den Alltag erleichtern aber bei der Summe an Funktionen und Formeln innerhalb von Excel sehr schnell untergehen können und die nicht so ohne weiteres besser wird dadurch, dass es mit neuen Office-Versionen auch immer neue Formeln und Funktionien gibt. Dieses ist auch einer der Gründe warum ich immer wieder gerne Blogartikel lese in denen auch Grundlagen näher vorgestellt werden.

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.

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.

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. :-)

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.