Beim Einrichten des neuen Rechners an der Arbeit (siehe "Arbeitsrechner neu einrichten Wechsel auf Windos 10, aktuelle Office Version sowie SAP und Excel anpassen") hatte ich ja schon die PERSONAL.XLSB erwähnt die ich von der Office 2010 nach Office 2016 mit umgezogen hatte.

Wie im Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" erläutert arbeite ich gerne mit einer kleinen Sammlung von Makros die ich in vielen Excelmappen anwende. Dazu sind einige hilfreichen Makros zum Beispiel zur Aufhebung eines Passwortblattschutz oder Anlegen eines Inhaltsverzeichnis in der persönlichen Makroarbeitsmappe gespeichert.

Über  + R kann die Funktion SHELL:APPDATA aufgerufen werden.

Im Unterordner

• ROAMING
• MICROSOFT
• EXCEL
• XLSTART

liegt dann im Quellrechner die Datei PERSONAL.XLSB die auch direkt auf den neuen Rechner kopiert werden kann, so dass hier die alten Makros direkt wieder genutzt werden können. Die Makros in der persönlichen Arbeitsmappe stehen immer zur Verfügung und sind nicht an einer geöffneten Excelmappe gebunden.

Hierzu muss die Mappe jedoch einmalig eingerichtet werden. Dieses ist am einfachsten möglich indem ein "Dummy-Makro" (ein beliebiges Makro) aufgezeichnet wird und in der persönlichen Arbeitsmappe gespeichert wird.

Ein nicht direkt dieser Mappe zugeordnetes Problem war aber das Auftreten einer Geistermappe beim Öffnen einer Excel-Tabelle ein zweites Excel ohne Inhalt geöffnet wurde, ganz so als sei da eine Mappe ausgeblendet (was auch tatsächlich der Fall ist, aber eigentlich hätte diese nicht eine zweite Excel Instanz starten sollen).

Das geheimnisvolle zweite Excel-Fenster oder Geisterarbeitsmappe

Sofern ebenfalls eine PERSONAL.XLSB aus der alten Office Version im Einsatz habt wird neben einer neuen Excel-Datei (oder einer geöffneten Exceldatei) stets auch ein zweites leeres Excel-Fenster geöffnet. Dieses scheint daran zu liegen, dass die persönlcihe Excelarbeitsmappe in einer älteren Version gespeichert worden ist und ihr diese nicht vollständig ausblenden könnt.

Wie oben beschrieben kann in den Ordner XLSSTART die Datei PERSONAL.XLSB gelöscht werden und eine neue erzeugt werden. Hierzu muss im Ribbon ANSICHT in der Befehlsgruppe MAKROS ein neues Makro aufgezeichnet und dieses in der persönlichen Makroarbeitsmappe gespeichert werden.



Danach kurz ein paar Eingaben, die Makroaufzeichnung abschliessen und das Makro bearbeiten. Hierzu muss die PERSONAL.XLSB über ANSICHT->FENSTER-> EINBLENDEN wieder eingeblendet werden. Das neu aufgezeichnete Makro kann durch die bisher angelegten Makros aus der alten Makroarbeitsmappe überschrieben werden.

Ein in meinen Augen sehr sinnvolles Makro für die Persönliche Makroarbeitsmappe ist das Makro Excel Blattschutz aufheben, Inhaltsverzeichnis in Excel über vorhandene Tabellenblätter oder auch die im Artikel Office Integration - Excelansicht in SAP und Daten kopieren nach Excel beschriebene Makros, welche nicht in der Zieldatei gespeichert werden müssen aber sinnvollerweise immer einmal wieder angewendet werden können.

Geister in Excel oder Folgen des Ausblenden einer ganzen Arbeitsmappe

Auch wenn die obere Fehlermeldung stark nach 1. April klingen mag, hat sich dieser Fehler tatsächlich bei mir so zugetragen und es hat eine ganze Weile gedauert, bis ich die Ursache gefunden habe.. Die Ausblendenfunktion in Excel (zu finden im Ribbon Ansicht in der Befehlsgruppe Fenster) hat aber auch eine Kollegin zur Verzweiflung gebracht die eine Arbeitsmappe nicht öffnen und bearbeiten könnte, da diese angeblich gesperrt war. In Wahrheit hatte sie diese selbst ausgeblendet was zu einigen merkwürdigen Fehlern beim Absturz von Excel geführt hatte. Es war für niemanden mehr möglich diese Datei zu bearbeiten. Immerhin war sie in Libre- bzw. OpenOffice noch zu öffnen.
 

Frohe Ostern

Gerne mag ich hier die Gelegenheit nutzen und auch hier im Blog an alle Lesende "Frohe Ostern" und schöne Feiertage wünschen.



Auch wenn manche Software das ein oder andere Easteregg enhtalten mag, noch immer bin ich von "Easter Egg: Piraten in Excel 2010!" ein großer Fan, hoffe ich doch, dass der heutige Feiertag in vertrauter schöner Umgebung, Entspannung und vielleicht mit etwas weniger Arbeit begannen werden kann.

Von daher wünsche ich sei es bei Osterei und Osterspaziergang oder bei einer anderen Tradition am heutigen Tag alles Gute und freue mich schon darauf wieder voneinander zu lesen, hören oder zu sehen.
 

In der "Buchempfehlung »Excel Pivot-Tabellen für dummies« von Martin Weiß ( tabellenexperte.de )" sind nicht nur umfangreichere Themen rund um  das Thema Excel-Pivot-Tabellen vorgestellt worden (wie Datenmodelle oder der Aufbau eines Management-Desktop sondern gerade im ersten Kapitel sind auch auf Grundlagen eingegangen worden die ich mir nun ebenfalls angeeignet habe.

Darunter zählt auch die Funktion "Als Tabelle Formatieren" die im Ribbon Start in neueren Excel-Versionen zu finden ist. Die Funktion ist in der Befehlsgruppe "Formatvorlagen" zu finden und eignet sich nicht nur dafür, dass hier Tabellen schön formatiert sind.

Bisher empfand ich die Funktion schon hilfreich um "Mehrere Autofilter im Tabellenblatt einer Exceltabelle setzen" oder einfach damit die Tabellen etwas schöner aussehen als ich sie formatiert hatte.

Zur Aktualisierung der Daten aus "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" möchte ich die Grundlage für die Pivot-Tabelle um weitere Jahre zu erweitern.

Bisher habe ich hier immer den Datenbereich neu definieren müssen und hatte auch an anderer Stelle die ein oder andere Hürde zu überwinden. Wie üblich in Excel gibt es aber mehr als einen Weg um dieses zu bewerkstelligen und so habe ich nun eine neue Funktion in Excel sehr zu schätzen gelernt, die ich bisher eher zur Seite geschoben habe.

Daten als Tabelle formatieren

Der erste Schritt ist, dass ich die Tabelle entweder durch die Schaltfläche "Als Tabelle formatieren" (siehe 1 in unterer Abbildung) oder durch die Tastenkombination STRG und T in eine formatierte Tabelle umwandele. Wichtig ist, dass die bisherige Tabelle hier komplett erfasst ist und die Option Tabelle hat Überschriften (siehe 2 in unterer Abbildung) markiert ist.

Datentabelle Namen zuweisen

Damit sieht die Tabelle nicht nur wesentlich hübscher aus sondern kann, wenn man eine Zelle in der Tabelle markiert hat über die Tabellentools im Ribbon Entwurf (1 in unterer Abbildung) einen eigenen Namen (unter 2 in unterer Abbildung) erhalten.



Statt Tabelle1 kann dieser Name zum Beispiel Grunddaten_Drittmittel sein. Dieser Name kann dann als Datenherkunft einer Pivot-Tabelle verwandt werden.

Dynamischer Datenbereich als Grundlage für Pivot-Tabellen

Soweit ist dieses noch kein Hexenwerk, eine spannende Option ist jedoch, dass sobald ich weitere Daten an die Tabelle anfüge sich die Tabelle automatisch um die neuen Zeilen erweitert, so dass ich durch Zuweisung des Tabellennamen als Datenquelle für die Pivot-Tabelle nicht mehr die Datenherkunft aktualisieren muss oder aber mit der  "Funktion BEREICH.VERSCHIEBEN - Datenbereich dynamisch von der Größe anlegen" wie im Artikel "SVERWEIS ohne NV und dynamische Größen für Datenbereiche" hier etwas umfangreicher den Datenbereich über eine Formel identifizieren muss.

Ergebniszeile und Datenschnitten bei als Tabelle formatierte Daten

Ein weiterer Vorteil dieser formatierten Tabelle ist, dass hier auch eine Ergebniszeile (aktiviert durch 1 in folgender Abbildung) markiert werden kann und hier über einzelne Spalten Summen, Anzahl oder andere Ergebnisse (siehe 2 in folgender Abbildung) gebildet werden diese aber nicht in die Datenquelle der Pivottabelle mit übernommen werden.

Dabei wird hier als Option direkt die Funktion TEILERGEBNIS angeboten, so dass hier Anzahl, Summe und diverse andere Optionen ausgewählt werden können.



Ein Kollege hat mich per Mail zum Artikel "Excel rechnet mit Farben oder ZÄHLENWENN bzw. SUMMEWENN anhand der Hintergrundfarbe der Zelle dank ZELLE.ZUORDNEN ohne VBA" auf den Artikel "In Excel mit Farben rechnen" hingewiesen wo ebenfalls diese Funktion angesprochen wird.

An dieser Stelle trifft dann tatsächlich der Kommentar meiner Frau zu, dass mein Internet relativ betrachtet klein ist und tatsächlich zu bestimmten Themen immer wieder bekannte Quellen auftauchen :-). Zumindest ist dies eine gute Begründung zur kleinen Anekdote im Abschnitt "Nachtrag Makrofunktionen und Excel 2016:".

Im Artikel "Pivottabellen ab Excel 2010 dynamischer filtern mit Datenschnitten am Beispiel Hochschulfinanzstatistik" ist auch noch ein weiterer Vorteil dieser Tabellen erwähnt.

Durch Martin Weiß (tabellenexperte.de) ist ein weiterer Vorteil der Datenschnitten ab Excel 2013 erwähnt. Im Artikel "Bequemer filtern mit Datenschnitten" weist er darauf hin, dass ab Excel 2013 tatsächlich Datenschnitten auch auf "als Tabelle formatierte" Tabellen angewandt werden kann.

Fazit: Bücher 📖  und Blogs lesen bildet und erleichtert das Tagesgeschäft

Gerade solche Empfehlungen sind ein guter Grund sich nicht nur mit Excel-Blogs sondern auch mit Büchern zum Thema Excel Pivot-Tabellen (Excel, Pivot-Tabellen/Charts, Datenmodelle, Dashboard) zu beschäftigen.

In meiner Buchempfehlung zu "Excel Pivot-Tabellen für dummies" finden sich auch weitere gute Argumente für dieses Buch und auch in meiner Blogroll befinden sich einige von mir gerne genutzte Quellen zur eigenen Weiterbildung.

Manchmal sind es Kleinigkeiten in Excel die gewaltig das Leben erleichtern. Während Martin Weiß (tabellenexperte.de) unter "Tipps für ein besseres Tabellen-Design (Excel Best Practise)" einige Hinweise zusammengestellt hat die ich gerade bei neuen Tabellen nun auch mehr oder minder häufig versuche umzusetzen bin ich diese Woche sehr dankbar für eine der grundlegenderen Funktionen in Excel.

Ich meine die durch Copy & Paste auch in anderen Programmen bekannte Funktion "Inhalte einfügen" die sich in Excel hinter der rechten Maustaste verbirgt.



Selbstverständlich eignet sich diese Funktion hervorragend um Werte, Formate oder Formeln in eine Tabelle einzufügen aber hinter "Inhalte einfügen" verbirgt sich abseits der naheliegenden Funktionen (die auch direkt als Icon zur Verfügung stehen) noch einige praktische weitere Möglichkeiten, die mit den richtigen Wert in der Zwischenablage (sprich den richtigen kopierten Wert) weitere Möglichkeiten offenbart.

In folgender Abbildung sind einige dieser Funktionen markiert die ich derzeit relativ häufig und gerne genutzt habe.


 

Automatisch Verknüpfung zu Zellen erstellen

Über die Schaltfläche "Verknüpfen" (siehe 1 in oberer Abbildung) werden statt den Werten oder Inhalten aus der Zwischenablage eine Verknüpfung zu den Zellen bezieungsweise der Quelle (Tabelle, Tabellenblatt, Zeile, Spalte, Zellen) eingefügt. Dieses funktioniert allerdings nur, wenn bei Einfügen auch "Alles" markiert ist.

Besonders praktisch ist dieses, wenn zum Beispiel eine Spalte mit der Bezeichnung der einzelnen Lehreinheiten oder Zuordnungen zu Bereichen an mehrere Stellen verwandt werden soll und nur in der Haupttabelle die Werte aktualisiert werden sollen.

Vorzeichen umkehren, Werte halbieren, ...

Gerade bei Finanzberichten die aus SAP kommen stellt sich oftmals die Frage, ob Aufwand und Ertrag nun mit Negativ-Vorzeichen dargestellt werden soll oder nicht.

In der Regel werden Erträge in SAP negativ und Aufwand positiv dargestellt, aber für Berichtsempfänger ist eine umgekehrte Darstellung vielfach hilfreicher.

Im Laufe der Zeit habe ich mir hier einige Makros zugelegt um Text in Zahlen oder auch um Vorzeichen umzukehren (siehe Artikel "Office Integration - Excelansicht in SAP und Daten kopieren nach Excel"). Wesentlich schneller kann dieses aber erreicht werden wenn einfach ein Wert wie "1" oder "-1" kopiert wird und in der Abbildung unter 2 "Werte" zum einfügen gewählt wird und  unter 3 "Multiplizieren" ausgewählt wird.

Dieses hat den Vorteil, dass nun alle Werte mit -1 multipliziert werden und damit die Vorzeichen umgekehrt werden (auch aus einer schwarzen + 0 wird damit eine rote - 0 ;-)).

Da ich aus einer Datenbankabfrage die Grunddaten zweifach verarbeitet habe und nicht erneut alle Abfragen durchlaufen lassen wollte (die Grunddaten waren eindeutig doppelt eingespielt) ist auch die Multiplikation mit 0,5 hilfreich um die Werte zu halbieren. Denkbar wäre auch 2 zu kopieren und die Werte zu Dividieren, jedoch hat ein 0 Wert die Eigenschaften, dass dieser zwar mit 0,5 multipliziert aber nicht durch 2 dividiert werden darf.

Schnelle Datenkontrolle

Angenommen es liegt eine Saldenliste von Innenaufträgen vor. Dann kann es hilfreich sein zur Qualitätskontrolle zu vergleichen ob diese Daten vollständig sind und die vorliegende Liste mit Innenaufträgen mit einer anderen Liste zu vergleichen. Dieses ist natürlich mit Einfügen der Werte in eine Kontrollspalte und der Excelfunktion IDENTISCH möglich.

Handelt es sich um Zahlen kann die Liste aber auch kopiert werden und mit den Vorgang 4 "Subtrahieren" von den anderen Daten abgezogen werden. Wenn überall nun eine 0 steht war wohl alles in Ordnung und mit STRG + Z (Rückgängig machen) erscheinen wieder die ursprünglichen Daten.

Daten transponieren

Das wunderbare Theme Transponieren von Daten hatte ich im Artikel "Zeilen und Spalte vertauschen (Daten transponieren)" eingegangen. Hier bin ich auch auf das Thema Transponieren von Formeln mit absoluten Bezügen unter Beibehaltung der Bezüge zu Zellen eingegangen.

Fazit

Oft sind es kleine Kniffe die dann tatsächlich das Leben mit Excel erleichtern. So bin ich auch heute noch sehr froh, bei soprani.at auf die Funktion FEST (siehe Artikel "ich hab die #Zahlen am #allerschönsten" gelandet zu sein durch die auch verknüpfte Zellen (bspw. Korrekturwert von Zuweisungswert als

=FEST(a1;0)&" / "&FEST(a2;0)

mit Tausenderpunkten und 2 Nachkommastellen in lesbarer Form dargestellt zu bekommen oder über rechte Maustaste "Zellen formatieren" im Register Ausrichtung bei der Textausrichtung Horizontal mit der Option "Über Auswahl zentrieren" statt mit Zellen verbinden Überschriften formatieren zu können.

Das schwierige ist nur sich an diese Möglichkeiten zu erinnern...was mit ein Grund zum Bloggen und natürlich Blog lesen ist.

Neben Kommentaren in Winword (und das Kopieren dieser Anmerkungen im Artikel "Microsoft Office Vorlagen und Änderungsverfolgungen") bemühe ich mich auch darum in Excel nicht nur sprechende Formeln zu verwenden sondern auch durch Kommentare Hinweise zum Beispiel zur Datenherkunft zu geben. Gerade wenn später noch andere Personen mit diesen Arbeitsmappen arbeiten sollen ist es nützlich hier entsprechende Hinweise zu hinterlegen, so dass gerade bei Eingabemasken (siehe "Formulare gestalten in Excel")  oder auch umfangreichere Tabellen eine kleine Hilfestellung zu bieten.

Aus Gründen der Kreativität und weil ich durch ein Excel-Blog auf diese Möglichkeit hingewiesen worden bin habe ich mir entschieden bei besonders wichtigen Kommentaren diese auch eine andere Form zu geben, so dass diese dann auch direkt bemerkt werden.



Offensichtlich ist dieses gelungen, so dass ich diese Möglichkeit hier gerne ebenfalls festhalten möchte.

Eigentlich können Kommentare nur von der Schrift her formatiert werden. Allerdings ist es möglich durch die Symbolleiste für den Schnellzugriff die Funktion "Form ändern" auch auf Kommentare anzuwenden.

Wie in der folgende Abbildung kann links oben die Schnellzugriffsleiste bearbeitet werden. Alternativ ist dieses auch über DATEI->OPTIONEN im Reiter Symbolleiste für den Schnellzugriff möglich.



Hier kann nun die Befehlsgruppe "Zeichentools Format Registerkarte" ausgewählt werden und die Schaltfläche "Form ändern" in den Schnellzugriff hinzugefügt werden. Wenn nun ein Kommentar markiert ist kann die Form des Kommentar je nach Wichtigkeit angepasst werden, wie im Beispiel zu sehen ist.



Somit ist es sogar möglich in einer einfachen Exceltabelle für Excel-Begeisterte eine Botschaft zum Valentinstag zu verbergen . Natürlich sind auch sonst verschiedene Möglichkeiten vorhanden... aber insgesamt zeigt sich, dass auch in Excel immer einmal wieder ein wenig Kreativität freigesetzt werden kann, wobei dieses auch schon beim tabellenexperten im Artikel "Das How-To zum Christbaum-Simulator" ersichtlich ist. :-)

Neben der ansprechenden Formatierung von Trends und Diagrammen von Zahlenreihen (siehe "Datentrends für Drittmittelstatistik mit Sparklines ab Excel 2010 darstellen durch Liniendiagramme in Zellen") sind es manchmal solch kleine Kniffe die dann doch für eine Aufmerksamkeit sorgen und für angenehme Überraschungen sorgen können.
 

Im Artikel "Text als nicht druckbar markieren (ausblenden)" ist erläutert worden, wie Text ausgeblendet werden kann, so dass entsprechende Hinweise (wie ENTWURF oder andere Ergänzungen) nicht mit ausgedruckt werden. Ebenso wird darauf hingewiesen an welcher Stelle auch ausgeblendeter Text wieder eingeblendet werden kann.

Allerdings war es hier einer Kollegin zu mühsam immer erst in den Optionen zu gehen um diese Einstellung zu aktivieren, bzw. zu deaktivieren, so dass sich hier Gedanken darum gemacht worden ist, ob eine solche Umstellung nicht auch über die Symbolleiste für den Schnellzugriff aufgerufen werden kann.

Als eigene Funktion ist dieses nicht vorhanden, aber innerhalb VBA bzw. für MAKROS gibt es für die Optionen eines Worddokumentes eigene Eigenschaften.

Das Objekt Options.PrintHiddenText steuert die entsprechende Ausgabe. Dabei handelt es sich um ein Objekt vom Typ boolean, so dass es entweder FALSE oder TRUE sein kann.

Durch die VBA Anweisung Options.PrintHiddenText = True  wird ausgeblendeter Text mitgedruckt und durch Options.PrintHiddenText = False erscheint eben dieser nicht im Ausdruck.

Ebenso kann der Objektzustand im Rahmen einer IF Schleife abgefragt werden und entsprechend umgestellt werden.

Das entsprechende VBA Coding lautet:

Sub SwitchHiddenText()
If Options.PrintHiddenText = False Then
 If MsgBox("Soll Ausgeblendeten Text drucken aktiviert werden?", vbYesNo) = vbYes Then
   Options.PrintHiddenText = True
   MsgBox ("Option ausgeblendeten Text drucken wurde aktiviert")
  End If
Else
 If MsgBox("Soll Ausgeblendeten Text drucken deaktiviert werden?", vbYesNo) = vbYes Then
   Options.PrintHiddenText = False
   MsgBox ("Option ausgeblendeten Text drucken wurde deaktiviert")
 End If
End If
End Sub 

Sinnvollerweise sollte dieses als Makro in der NORMAL.DOTM als globale Vorlage hinterlegt werden, so dass dieses in allen Worddokumenten aufgerufen werden kann. Die folgende Anleitung gilt dabei für alle Office Versionen ab 2010.

In der Symbolleiste ANSICHT kann in der Befehlsgruppe MAKROS direkt die Schaltfläche MAKROS wie in unterer Abbildung aufgerufen werden. Wichtig ist nun dass neben den Makronamen (in der Abbildung unter 1 SwichtHiddenText) die Option "Normal.dotm (globale Vorlage) (in der Abbildung unter 2) gewählt wird.



Durch die Wahl der globalen Vorlage können alle künftigen Worddateien auf dieses Makro zugreifen und das jeweilige Makro daraus aufgerufen werden.

Im erscheinenden Visual Basic Editor kann obiger Code kopiert und eingefügt werden, so dass das Makro entsprechend gespeichert werden kann.

Im Ergebnis ist das Makro dann konstant vorhanden.

Sinnvollerweise kann dieses Makro nun in der Schnellzugriffsleiste (links oben), wie in der zweiten Abbildung zu sehen, als Schaltfläche hinzugefügt werden.



Dadurch ist das Makro auch direkt per Symbol aufrufbar, sofern es a) ausgewählt und b) über die Schaltfläche HINZUFÜGEN der Symbolleiste hinzugefügt worden ist.

Im Ergebnis kann nun zwischen "Ausgeblendeten Text drucken" oder "Ausgeblendeten Text nicht drucken" hin und her geschaltet werden. Durch die IF Anweisung erscheint jedoch zuvor eine Messagebox in der vorher eine Rückfrage erscheint.



Im Ergebnis kann hier also im Dokument zum Beispiel die Kennzeichnung Entwurf oder beliebige Anmerkungen eingeblendet oder ausgeblendet werden. Damit können bestimmte Informationen bzw. Textbestandteile (Entwurf, Verteiler, etc.) nur im Bedarfsfall mit ausgedruckt werden. Der Nachteil an solchen ausgeblendeten Texten ist jedoch der Zeilenumbruch, so dass bei Anmerkungen in meinen Augen die Kommentarfunktion die bessere Wahl ist. Der Umgang mit Markups und Kommentaren ist im Artikel "Microsoft Office Vorlagen und Änderungsverfolgungen" behandelt worden.