2013-05-21

Excel – Standardmappe bei Start ändern

Eine neue Arbeitsmappe besitzt bereits viele Eigenschaften. Und noch mehr Starteinstellungen werden meist direkt verändert. Zusätzlich bietet Excel 2013 einen schicken Begrüßungsschirm, in dem die mitgelieferten Vorlagen angeboten werden. Diese Auswahl will ich aber nur sehen, wenn ich Datei – Neu klicke.

Wo bei Ihrer Installation von Office unter Ihrem Betriebssystem die Startverzeichnisse liegen, kann man über Datei – Optionen – Trust Center – Einstellungen für das Trust Center… herausfinden.

Die Datei Mappe.xlsx wird in den folgenden Ordner gespeichert:

%AppData%\Roaming\Microsoft\Excel\XLSTART

%AppData% ermittelt das Verzeichnis, in dem sich AppData des angemeldeten Benutzers befindet. Bei einer Standardinstallation auf meinem Windows 8-Rechner entspricht dies dem Verzeichnis:

%username%\AppData\Roaming\

Ich bin mit dem Benutzernamen Andreas angemeldet. Somit lautet mein Pfad für die XLSTART:

C:\Users\Andreas\AppData\Roaming\Microsoft\Excel\XLSTART

Über STRG + N ruft eine leere, unbehandelte Standardmappe auf, die nicht meiner in XLSTART gespeicherten Mappe.xlsx entspricht. Beim Einfügen eines neuen Tabellenblatts wird ebenfalls wieder der Standard genommen. Ebenso ignoriert der Aufruf einer Arbeitsmappe über das Kontextmenü des Windows-Explorers die in XLSTART gespeicherte Vorlage.

Wird die Mappe.xlsx auch als Excel-Vorlage unter dem Dateityp xltx gespeichert, kann diese über Datei – Neu – PERSÖNLICH bzw. Meine Vorlagen geladen werden.

Video bei YouTube:
Videolink: http://youtu.be/BZVIO1Bh0Kc

2013-05-19

Apps for Office – QR4Office – QR-Codes erzeugen

Im Register Einfügen befinden sich ab Office 2013 in Word, Excel und PowerPoint die Apps für Office. Hier lassen sich kostenlose und kostenpflichtige Apps im Microsoft Store herunterladen, welche sich in die Office-Dokumente integrieren.

Seit kurzer Zeit gibt es auch eine App zur Erstellung von QR-Codes. Michael Zlatkovsky hat die App QR4Office für Office 2013 programmiert und kostenlos zur Verfügung gestellt. Mir persönlich spart es deshalb Zeit, da ich nicht in andere Programme oder auf Webseiten wechseln muss, um mal eben einen QR-Code zu erzeugen. Michael hat erwähnt, dass es in Kürze auch einige Anpassungen und Neuerungen geben wird, so dass die Screenshots hier eventuell abweichen.

Ich nutze QR-Codes zum Beispiel in Schulungsunterlagen. Passend zum Thema gibt es dann vielleicht ein Video. Nun die Frage? Schreibe ich einen Link? Oder soll jemand auf ein Bildchen klicken? Oder soll jemand einen QR-Code abscannen und das Video direkt auf einem mobilen Device (Tablet, Smartphone) sehen?

Warum nicht alles drei auf einmal?

Über QR-Codes können auch andere Informationen, wie zum Beispiel Telefonnummern oder E-Mail-Adressen mitgeteilt werden.

Weisen die QR-Codes größere Fehlertoleranzen aus, können auch noch kleine Grafiken in die QR-Codes eingebettet werden. Hierfür wähle ich allerdings ein externes Grafikprogramm, in der das Logo bereits hinterlegt ist und kopiere dieses Logo in den QR-Code hinein.

Hier geht es zum YouTube-Video:
Videolink: http://youtu.be/n_KlpcG85-Q

2013-05-18

Excel – Tabellen verbinden – Data Explorer Preview

Das kostenlose COM-Add-In „Data Explorer“ Preview ermöglicht die Verknüpfung von Daten aus unterschiedlichen Datenquellen.

Oftmals liegen Daten ohne Verknüpfung untereinander auf getrennten Servern. Liegt mir dann eine monatliche Auswertung vor, kann ich die Daten noch mit den Informationen aus anderen Tabellen anreichern.

Zunächst importiere ich die Datenquellen über den Data Explorer. Anschließend kann im Register Data Explorer oder im Register Query auf Merge gedrückt werden.

Beim Merging wähle ich zunächst die beiden zu verknüpfenden Tabellen aus und wähle anschließend die übereinstimmenden Felder. Die Feldnamen müssen dabei nicht identisch sein.

Anschließend stehen die verbundenen Daten über den Eintrag Table zur Verfügung, hier muss noch ausgewählt werden, welche Spalten man aus der zweiten Liste ausgelesen bekommen möchte.

Unnötige SVERWEISE können damit verhindert werden. Ändert sich die Struktur der Daten, müssen Tabellen mit Formeln nicht angepasst werden. Änderungen in den Originaldaten führen auch nicht permanent zu Änderungen in der aktuellen Datei. Erst über einen Klick auf Refresh werden alle Infos neu eingelesen. In PowerPivot habe ich zwar auch die Möglichkeit der Verknüpfung von Tabellen. PowerPivot steht allerdings meines Wissens nach bei Excel 2013 nicht für alle Office-Pakete zur Verfügung.

Zum Video:
Videolink: http://youtu.be/QUdnghNApq8

2013-05-17

Excel – Data Explorer Preview – CSV importieren und matchen

Mehrere csv-Dateien (comma separated value-files) werden monatlich aktualisiert und müssen anschließend gemeinsam ausgewertet werden. Das kostenlose Add-In „Data Explorer“ Preview bietet die Möglichkeit, csv-Dateien zu importieren. Diese über den Data Explorer importierten Dateien können dann anschließend kombiniert werden.

Vorab ist aber beim Import der csv-Datei zu beachten, dass die Felder mit identischen Datentypen und der gleichen Spaltenzahl importiert werden.

Spätere Aktualisierungen der csv-Dateien führen über den Schalter Refresh zum erneuten Import und der Aktualisierung der kompletten Tabelle.

Hier bekommt man einen kleinen Einblick in den Aufbau der csv-Dateien. Die Werte sind jeweils mit einem Semikolon von einander getrennt.

Im Register Data Explorer findet man unter From File den Punkt From CSV.

Anschließend wählt man in einem Datei-Explorer die Datei aus (ohne Bild) und klickt auf Öffnen. Der Import würde hier in nur eine Spalte erfolgen.

Mit einem rechten Mausklick auf Column1 kann man einstellen, dass es ein Trennzeichen (Split) gibt. Parallel kann man zudem mitverfolgen, wie die einzelnen Schritte des Imports (Steps) aufgebaut werden. Wenn man rechts auf einen Step klickt, wird in der oberen Befehlszeile der Befehl inklusive Parametern angezeigt.

Hier soll bei einem Trennzeichen (Delimiter) in mehrere Spalten getrennt werden. Ich wähle das Semikolon als Trennzeichen.

Anschließend klicke ich mit rechts auf den Namen der Abfrage und dann auf Rename. Unter dem Namen dieser Abfrage identifiziere ich später die Tabelle.

Der Datentyp des Datumsfeldes wird noch auf Date verändert.

Anschließend importiere ich die erste csv-Datei mit dem Schalter Done. Für die zweite Tabelle gehe ich exakt genau nach diesem Muster vor, wähle aber dabei natürlich einen anderen Query-Namen.

Wenn beide Tabellen geladen sind, kann ich im Register Data Explorer oder im Register Query auf Append klicken.

Die beiden Tabellen stehen zur Auswahl.

Ich kann die Combine-Abfrage noch umbenennen.

Sollten später neuere csv-Dateien unter gleichem Namen im Zielverzeichnis liegen, kann ich über einen Klick auf den Schalter Refresh die Daten erneut einladen.

Zum YouTube-Video:
Videolink: http://youtu.be/SmMlU92j3Do

2013-05-15

Excel 2013 – PowerView – Visualisierung von Daten

Ein im Zeitverlauf animiertes Blasendiagramm ist in wenigen Augenblicken erzeugt. Die Daten liegen im Data Model oder über intelligente Tabellen vor? Excel 2013 ist installiert? Perfekt… Im Register Einfügen finden wir die kleine Gruppe Bericht, dort steht einsam und allein PowerView.

Ein animiertes Diagramm in weniger als 60 Sekunden!

Warum nicht? Excel mausert sich immer mehr zu einem Tool, große Datenbestände anzuzapfen und auszuwerten. Die Datenhaltung würde ich natürlich einer Datenbank überlassen.

Das Handling innerhalb von PowerView erstreckt sich über zwei Register. PowerView selbst und daneben das Register Entwurf. Meine ersten Versuche mit PowerView waren gescheitert, da ich zum einen meine Daten nicht als Intelligente Tabelle (Strg + T) formatiert hatte, und später dann auch nicht auf Entwurf geklickt hatte.

Im Video sind meine (fast) ersten Gehversuche mit PowerView dargestellt. Und wenn das meine ersten Versuche sind… was wartet noch auf mich, wenn ich PowerView genauer kennengelernt habe?

Übungsdatei zum Download:
E369_PowerView

Video bei YouTube:
Videolink: http://youtu.be/k3YEeVHgTQc

2013-05-14

Excel – Data Explorer Preview – Ordner auslesen

In meinem dritten Blog-Beitrag zum Add-In Data Explorer Preview für Excel 2010 / 2013 geht es um das Auslesen von Ordnern und Unterordnern.

Im Register Data Explorer kann man From Folder auswählen. Hier werden Verzeichnis- und Dateiinformationen eingelesen.

Zunächst muss jedoch ein Verzeichnis (1) ausgewählt werden. Anschließend wird mit OK (2) bestätigt.

Der Name der Abfrage wird vermutlich Query1 lauten (3). Über einen rechten Mausklick lässt sich der Name der Abfrage umbenennen. Hier möchte ich ein Bilderverzeichnis auslesen und benenne entsprechend um.

Anschließend möchte ich noch die Dateigrößen sehen. Diese verbergen sich in den Attributen (4). Zunächst wähle ich alle Einträge mit Select All Columns ab und aktivere dann das Kästchen bei Size. Mit OK (5) bestätigen.

Die Ordnerstruktur kann ebenfalls über mehre Spalten verteilt werden. Über einen rechten Mausklick auf Folder Path und dort Split Column kann der Trennwert By Delimiter definiert werden.

Statt des üblichen Kommas, Semikolons etc. wähle ich den Eintrag Custom und gebe den Schrägstrich nach links (Backslash) ein. Auch hier wird natürlich mit OK bestätigt.

Anschließend muss ich noch erkennen, dass hinter jedem Bildnamen noch entweder .jpg oder .JPG steht. Über einen rechten Mausklick auf die Spalte Name ersetze ich die entsprechenden Werte.

Bei Value To Find gebe ich erst .jpg ein und tausche gegen Nichts aus. Nach einem OK rufe ich Replace erneut auf und tausche auch .JPG gegen Nichts. Groß- und Kleinschreibung wird also unterschieden.

Zum Schluss führe ich die Abfrage aus und erhalte eine Auflistung der Dateien im angegebenen Verzeichnis inkl. aller Unterverzeichnisse.

Video bei YouTube:
Videolink: http://youtu.be/JFh_X0zgAOg

2013-05-13

Excel – gestapelte Prozentanzeige – Bedingte Formatierung

Bill Jelen hat in seinem Video-Podcast #1652 die grafische Umsetzung einer Prozentanzeige mit Kästchen demonstriert – ich finde die Idee ziemlich cool und habe das Ganze noch einmal mit einer alternativen bedingten Formatierung hinterlegt, welche keine Formeln oder Werte in den Zellen des Tabellenblatts erfordert.

Für das linke Diagramm Case 1 wurden folgende Einstellungen getätigt:

– Zellhintergrund und Rahmenfarbe wurden auf ein helle Orange gesetzt.
– Der Bereich B2:B11 wurde bedingt formatiert.
– In Zelle B12 befindet sich der Prozentsatz 86%.

Die bedingte Formatierung errechnet einen Wert, der sich auf ZEILE() und SPALTE() ergibt.

Die Formel aus der bedingten Formatierung arbeitet wie folgt:
=(11-ZEILE())*10+SPALTE()-1<=$B$12*100

=(11-ROW())*10+COLUMN()-1<=$B$12*100 (engl.)

Für jede Zelle der quadratischen 10×10 Kästchen großen Fläche gilt dieselbe Formel. Jede Zelle prüft für sich, in welcher ZEILE() und welche SPALTE() sie steht. Durch die Rechnung ergibt sich ein Raster, das die Zelle unten links den Wert 1 erhält und die Zelle oben rechts den Wert 100.

Der 10×10 Kästchen große Bereich errechnet für die Zelle oben links, also Zelle B2:
=(11-2)*10+2-1 =9*10+1 =91

Für jede Zelle des Kästchenbereichs wird nun ermittelt, ob der Wert kleiner als $B$12*100 ist. Falls WAHR, gilt die bedingte Formatierung.

Für Case 2 habe ich alle markiert und mit einer Spalte Versatz daneben eingefügt. Die Formel musste so angepasst werden, dass die neue Spaltennummer berücksichtigt wird. Der Vergleichswert steht in Zelle M12. Die Spaltennummer musste um einen um 11 höheren Wert reduziert werden.

=(11-ZEILE())*10+SPALTE()-12<=$M$12*100

Das Thema kann natürlich noch weiter ausgebaut werden. Wie wäre es mit zwei Prozentsätzen. Links steht IST, rechts steht SOLL. Alle IST-Werte größer SOLL sollen hervorgehoben werden. Alles SOLL, was größer IST ist, wird rot markiert.

Die Datei… ohne Makros – einfach F9 drücken: E366_Faesser_stapeln

Das Video für die einfache Variante ohne Soll/Ist-Vergleich bei YouTube:
Videolink: http://youtu.be/-px8WwGj1UY

Die komplexere Variante mit Soll-Ist-Vergleich:
Videolink: http://youtu.be/7isYKQ6L0AM

2013-05-13

Excel – Data Explorer Preview – XLSX einlesen

Im letzten Blog vom 12.05.2013 habe ich kurz das neue Add-In „Data Explorer“ Preview für Excel 2010 / 2013 beschrieben. Noch ist das Add-In nicht final, noch steht es nur in englischer Sprache zur Verfügung.

Eine Option im Register Data Explorer ist die Möglichkeit, Excel-Dateien einzulesen.

Nachdem eine Datei ausgewählt wurde, können auf der linken Seite die Tabellen der Datei ausgewählt werden (1).

Anschließend muss ich hier leider feststellen, dass die erste Zeile nicht als Überschriftenzeile erkannt wurde (2). Über einen rechten Mausklick in die obere Zeile kann die erste Zeile des Imports zur Überschriftenzeile erklärt werden (3) – Use first row as headers. Anschließend klicke ich auf Done (4).

Die Daten werden nun auf ein neues Tabellenblatt übertragen. Im Register Tabellentools taucht zusätzlich der Eintrag Query auf. Ich muss hier aber erkennen, dass die Datumswerte nicht als solche übernommen wurden (5).

Zudem finde ich den Namen der Abfrage mit Query1 nichtssagend (6). Diesen möchte ich ändern. Nachträgliche Änderungen können jederzeit über einen Klick auf Filter & Shape (7) durchgeführt werden.

Mit einem rechten Mausklick auf den Namen der Abfrage (8) kann der Eintrag umbenannt werden. Ich vergebe hier zum Beispiel den sprechenden Namen qry_Team_1.

Anders beim Datum. Dies wurde als Zahl interpretiert. Klicken Sie mit der rechten Maustaste auf den Feldnamen Date und dann auf Change Type und dann auf Date (9). Wählen Sie hier ein Format aus.

Das Ergebnis der gesamten Umformatierung können Sie in der oberen Zeile (10) ablesen. Bestätigen Sie anschließend wieder mit Done.

Sind mehrere Spalten markiert, können diese auch gruppiert werden. Als Ergebnis lassen sich die Summen festgelegter Spalten ziehen oder die Anzahl der Datensätze anzeigen.

Werden Daten in der Datenquelle verändert, kann über einen Refresh der Datenbestand aktualisiert werden.

Übungsdatei zum Download – ca. 6 MB:
E365_Lange_Liste

Video bei YouTube:
Videolink: http://youtu.be/YoUcyv6VLIM