Neue Vorlagen für Excel 2016 zur Demonstration von Business Analysis Features

Home / Neue Vorlagen für Excel 2016 zur Demonstration von Business Analysis Features

10. Dezember 2015

Categories: Excel Ticker

by Mourad Louha

Seit Anfang Dezember 2015 stehen drei neue Vorlagen in Excel 2016 zur Verfügung, die z.B. Power Query und Power Pivot Features in Excel 2016 beispielhaft demonstrieren. Die drei Vorlagen waren zum Zeitpunkt der Erstellung dieses Artikels nur in einer englischen Excel-Version verfügbar – d.h. nur beim Aufruf einer englischen Excel 2016 Version im Vorlagenkatalog sichtbar. Die neuen Vorlagen „My Cashflow“, „Stock Analysis“ und „My Calendar“ sind dort prominent platziert und unter dem Stichwort „Business analysis feature tour“ abgelegt.

Vorlagen in Excel 2016

Weitere Sprachvarianten sind wohl in Arbeit, denn wenn Sie in einer deutschen Excel 2016 Version in den Vorlagen nach dem Begriff „Business Analysis Feature“ suchen, erscheint die bereits eingedeutsche Fassung zur Vorlage „My Cashflow“, die als „Mein Cashflow“ im Suchergebnis auftaucht.

Vorlagen in Excel 2016

Im nachfolgenden Teil des Artikels beziehe ich mich jedoch nur auf die englischen Versionen und stelle diese kurz vor. Alle drei Vorlagen beinhalten mehrere Arbeitsblätter, wovon teilweise beim erstmaligen Aufruf einige ausgeblendet sind und je nach Benutzeraktion eingeblendet werden. Zudem enthalten alle drei Vorlagen VBA-Code, z.B. zur Navigation oder Aktualisierung von Abfragen. Es lohnt sich somit auch den VBA-Code anzuschauen.

Die Vorlage „My Cashflow“

Die Vorlage zeigt, wie ein Excel-Datenmodell zur Berechnung von Salden der Einzahlungen und Auszahlungen innerhalb einer Periode (Cashflow) verwendet werden kann.

Vorlagen in Excel 2016

Die Vorlage nutzt hierbei Power Pivot-Abfragen und DAX-Funktionen sowie Cube-Funktionen aus Excel. Um die Datentabellen aus Power Pivot einsehen zu können, muss somit das PowerPivot Add-In aktiviert sein, was Sie daran erkennen, ob im Menüband die Registerkarte Power Pivot zu sehen ist oder nicht. Ist bei Ihnen das Add-In nicht aktiv, rufen Sie die Excel-Optionen auf und aktivieren Sie das COM-Add-In zu PowerPivot. Die Vorlage beinhaltet ebenfalls einige ausgeblendete Spalten ab der Spalte X, wo sich eine gewöhnliche Pivot-Tabelle verbirgt.

Vorlagen in Excel 2016

Die Vorlage „My Stock Analysis“

Die Vorlage ermöglicht den Vergleich von drei Aktienkursen (NASDAQ) im Laufe einer definierbaren Zeitspanne. Im unteren Bereich lässt sich dabei per Schieberegler der gewünschte Zeitraum definieren. Als Datenquelle dient Yahoo Finance.

Vorlagen in Excel 2016

Die Abfragen sind in dieser Mappe mit Power Query erstellt worden. Power Query wurde in Excel 2016 integriert und muss nunmehr nicht mehr separat aktiviert werden. Allerdings wurde dabei auch Power Query „umbenannt“ und dessen Funktionen sind in der Gruppe „Get & Transform“ bzw. „Abrufen & Transformieren“ zu finden.

Vorlagen in Excel 2016

Die Vorlage „My Calendar“

Die Vorlage erfordert den Zugriff auf einen Exchange-Server, um Kalenderdaten auslesen zu können. Somit besteht der erste Schritt darin, eine Verbindung zu einem Exchange-Server herzustellen.

Vorlagen in Excel 2016

Excel merkt sich die Verbindung zum Exchange-Server. Wenn Sie diese, z.B. nach einem Test, wieder entfernen möchten, rufen Sie den Befehl zu den Datenquelleneinstellungen innerhalb der Auswahlliste Neue Abfrage in der Gruppe Abrufen und Transformieren in der Registerkarte Daten auf. Entfernen Sie dann im Dialogfeld die Verbindung zum Exchange-Server.

Vorlagen in Excel 2016

Nach dem Herstellen der Verbindung braucht die Vorlage eine Weile, bis alle Daten abgerufen sind und es wird per VBA-Code ein Arbeitsblatt mit einem Dashboard eingeblendet, das die Auswertung enthält. Bei mir taucht allerdings ein VBA-Fehler auf, da ich auf dem Server keinerlei Daten hinterlegt habe. Die Vorlage nutzt wie die Vorlage zur Aktienanalyse Power Query für die Abfragen, die in diesem Fall etwas komplexer erscheinen.

Vorlagen in Excel 2016

Fazit

Die drei neuen Vorlagen sind recht hübsch aufgebaut und können sicherlich als Grundlage für eingene Studien dienen, um Features zur Business Analyse kennen zu lernen und zu schauen, wie andere Entwickler diese verwenden. Für einen Anfänger sind meines Erachtens die Abfragen schon recht komplex, was aber keinen aufhalten sollte, sich die Vorlagen näher anzuschauen.

Andreas Thehos

22. Januar 2016 at 12:53

Hallo Mourad,
Merci für die Vorstellung der drei Vorlagen. Bei Stock Analysis werden mir nur Daten bis zum August 2015 angezeigt. Ist der Zeitraum irgendwo mit Parametern eingeschränkt?

Sonnige Grüße und bis zum nächsten Excel-Stammtisch
Andreas

Mourad Louha

22. Januar 2016 at 14:02

Servus Andreas,

Wenn Du den Aufgabenbereich zu den Abfragen einblendest, findest Du zwei Abfragen; die erste ist die Funktion „Q1“. Wenn Du diese bearbeitest, findest Du im Quelltext-Editor den String „…d=7&e=26&f=2015…“. Setze mal den Parameter f=2015 auf f=2016. Bei mir werden dann die Daten bis 2016 geladen. Die anderen Param’s habe ich nicht geprüft, kann aber sein, dass d=Monat und e=Tag ist; müsste man etwas rumspielen.

Viele Grüße & bis zum nächsten, hoffentlich baldigen, Stammtisch :-)

Mourad

Luschi

4. Februar 2016 at 11:08

Hallo Mourad,

wo kann ich denn diesen ‚Aufgabenbereich zu den Abfragen‘ einblenden?

Gruß von Luschi
aus klein-Paris

4. Februar 2016 at 12:10

Hallo Luschi,

das geht in Excel 2010/2013 unter der Registerkarte Power Query / Arbeitsmappenabfragen / Bereich Anzeigen und in Excel 2016 in der Registerkarte Daten / Abrufen und Transformieren / Abfragen anzeigen.

Gruß, Mourad

EuroCafe

16. Mai 2016 at 14:57

Hallo Herr Louha, ich habe schon einige Artikel von ihnen gelesen und bin immer wieder überrascht was Excel und Co. so alles können!. Aber ich habe ein kleines Problem nicht zu diesem Beitrag, aber im Netz habe ich noch nichts gefunden was mit weiter helfen könnte.

Ich verwende Excel 2016 in der 64 bit Version, in einer Datei, in einem Tabellenblatt sind 158 einzelen Abfragen über Query erstellt worden. Jede Saison änder sich der Zeitraum und ich müsste 158mal jede einzelne Abfrag bearbeiten, in die Quelle gehen und den Zeitraum von zum Beispiel „2015-2016“ auf „2016-2017“ ändern. Ich habe nichts gefunden wie man auf die Datenbank kommen könnte um die 158 Abfragen in einem Rutsch zu ändern, auch habe ich von Claus ein Makro bekommen aber das ändert die URL in den Abfragen nicht.
Sub HyperlinkAendern()

Dim i As Long
Dim HypAlt As String
Dim HypNeu As String

‚Hier alten und neuen Text anpassen
HypAlt = „2015-2016“
HypNeu = „2016-2017“

For i = 1 To ActiveSheet.Hyperlinks.Count
    With ActiveSheet.Hyperlinks(i)
        .Address = Replace(ActiveSheet.Hyperlinks(i).Address, HypAlt, HypNeu)
        .TextToDisplay = Replace(ActiveSheet.Hyperlinks(i).TextToDisplay, HypAlt, HypNeu)
    End With
Next
End Sub

Wenn ich das richtig sehe, würde dieses Makro doch nur den Link in der aktiven Tabelle verändern, aber da sind doch keine hinterlegt. Wie muss man die Abfrage ergänzen, damit direkt in der Abfrage in der Quelle die URL’s geändert werden?

Ich hoffe sehr das Sie als Experte einen Vorschlag haben, sonst muss ich 500 Abfragen manuell ändern und auf die neue Saison umstellen.

Vielen Dank für eine Antwort
und liebe Grüße aus dem LDK

17. Mai 2016 at 08:51

Guten Morgen

ich weiss (noch) nicht, ob ich da weiterhelfen kann. Welches „Query“ haben Sie verwendet? PowerQuery, MS Query, andere bzw. wie haben Sie die erstellt, falls Sie den Typ der Abfrage nicht kennen? Der Code oben ändert in der Tat Hyperlinks, die in der Mappe/Tabelle hinterlegt sind.

Viele Grüße, Mourad

18. Mai 2016 at 19:23

Sehr geehrter Herr Louha,
in Excel 2016 ist Power Query nun vollständig implementiert unter Daten/externe Daten/Web Abfragen und laut Internetrecherche kann man nun auch über VBA dirket auf eine Abfrage zugreifen, ich habe Codes gesehen die zeigen tp und QueryTablet an, aber an die Quelle die in der Abfrage verwendet wird, also der Link einer Seite an den komme ich nicht. Ich kann einen neue Abfrag erstellen, die dann auch unter Abfrageneinstellung angezeigt wird. Aber ich möchte ja nur in den Abfragen aus der vorhandenen Quelle einen Teil des Links ändern. Claus vom MS Forum hatte mir zwei Codes geschickt, aber beide ändern dieses nur im Tabellenblatt, aber nicht in der eigentlichen Abfrage und in dem Tabellenblatt sind keine Links zu sehen nur die Werte die die Abfrage zurück gegeben hat.

Mal als Beispiel:
wenn ich 158 Links in einer Tabelle stehen hätte, dann würde ich über Suchen und Ersetzten gehen, das geht auch über ein VBA Makros, einmal als Wert und einmal als Formel, wenn man sich die Links aus Absoluten Bezügen und varabelen Bezügen über =Hyperlink(Verketten) zusammen bastelt.
Aber jeder Link musste nun manuelle in jede Abfrage einzeln in die Quelle kopiert werden und das eben 158mal. Nun ist die Saison vorbei und ich müsste als Beispiel für die neue Saison 500 Abfragen manuelle ändern von 2015-2016 auf 2016-2017
http://de.futbol24.com/national/England/Premier-League/2015-2016/#statT-Limit=1&statT-Table=1
und genau in diesem Link möchte ich gerne per VBA das Jahr 2015-2016 durch 2016-2017 ersetzten.

Das sieht man im Abfrage-Editor im angewendeten Schritt Quelle
= Web.Page(Web.Contents(„http://de.futbol24.com/national/England/Premier-League/2015-2016/#statT-Limit=1&statT-Table=1“))

Wäre schön wenn Sie mir weiterhelfen könnten oder mir einen Tipp geben wie man oben den Code auf Query umstellt.

Einen schönen Abend ich gehe jetzt Liverpool schauen.
Mit freundlichen Grüßen
EuroCafe

19. Mai 2016 at 08:59

Guten Morgen,

Abfragen, die über PowerQuery laufen, können ab Excel 2016 im VBA-Code wie gewöhnliche Abfragen interpretiert werden. Hier ein Beispielcode, wie Sie z.B. eine Abfrage für PQ verändern:

Sub UpdateQueries()


  Dim objQuery   As WorkbookQuery
  For Each objQuery In ThisWorkbook.Queries
'   MsgBox objQuery.Formula

    objQuery.Formula = Replace(objQuery.Formula, "/2014-2015/", "/2016-2016/")

'   MsgBox objQuery.Formula
  Next

End Sub

Der Code hier oben ist nur rudimentär getestet; ggf. müssten m.E. z.B. checken, ob’s auch wirklich eine PQ-Abfrage ist. Bitte dann in einer Kopie testen. Und ggf. müssen auch die Abfragen danach aktualisiert werden.

Viele Grüße, Mourad Louha

20. Mai 2016 at 18:47

Sehr geehrte Louha,
ich habe eine Testtabelle erstellt und es hat einwandfrei Funktioniert, es waren aber auch nur 4 Abfragen.
Dann habe ich einer meiner Dateien mit den 158 Abfragen dem Makro unterzogen und Excel hat sich aufgehängt, so weit nicht schlimm. Nach 30 Minuten hat Excel ca. 12 GB RAM und 98% CPU blockiert, nach einer Stunde keine Änderung, die Werte schwanketen zwischen 8 bis 14 GB und CPU zwischen 70 und 98%. Aber auch nach zwei Stunden gab es keine Änderung der Werte. Was passiert wenn ich das Makro starte und den Wert 2016-2017 gibt es noch nicht auf der Internet-Seite?

Ich sage mal es gibt unterschiedliche Länder mit unterschiedlichen Saisonzahlen, so ist es in Europa üblich die Saison so zu deklarieren 2015-2016, in Skandinavien steht dann nur 2015 wie auch in Südamerica. Nicht jede Liga wird vom Anbieter innerhalb eines Zeitraumes angeboten, so kann es sein das Deutschland im August vorhanden ist aber Italien erst im September.

Kann ich irgendwie das Makro so verändern das zum Beispiel nur die Werte geändert werden, bei denen ich in einer bestimmten Zelle einen Wert festsetze?

Ich stelle mir das so vor, das ich zum Beispiel in A3 eine 1 eingeben, darunter steht die Tabelle der Abfrage, in der nächsten steht eine 0. 0 = noch nicht Aktualisieren und 1 Aktualisieren.

Denn ich denke solange auf der Seite keine Tabelle vorhanden ist für die neue Saison, solange wird das Makro dann in einer Endlosschleife laufen und ich kann nichts verhindern.

Nochmals vielen Dank für die super Hilfe an der ich schon fast 4 Monate am Suchen war und ist das nicht eine separaten Beitrag wert? Denn im Netz gibt es über Excel 2016 und Query fast nichts zu finden.

Ich wünsche ein schönes Wochenende
Mit freundlichen Grüßen und bestem Dank
EuroCafe

21. Mai 2016 at 10:16

Hallo,

denke mal, dass PQ direkt nach dem Ändern der Abfrage diese sowie ggf. Abhängigkeiten untereinander aktualisiert und sich dann Excel irgendwann mal aufhängt. Spontan würde mir dazu einfallen, die automatische Aktualisierung per Code temporarär abzuschalten. Wo das allerdings genau zu bewerkstelligen wäre, müsste ich erstmal z.B. im Objektkatalog von Excel selber suchen. Als Stichwort würde Refresh, Background verwenden. Ggf. muss dabei auf andere Objekte wie Connections oder OLEDBConnection zugegriffen werden.

Ihre Idee, nur bestimmte Abfragen zu ändern müsste gehen: Sie könnten zunächst die Liste der zu aktualisierenden Abfrage aus einer Tabelle in eine Collection (Set objAbfragen = New Collection) einlesen. Einfache Schleife, wo Sie die Namen in die Collection einlesen, wo die 1 steht. In einer zweiten Schleife durchlaufen Sie die PQ-Abfragen und prüfen, ob diese in der Collection enthalten ist und wenn Ja, dann aktualisieren. Hier dazu mal ein Pseudo-Code, den ich nur runtergeschrieben und nicht getestet habe:


  Dim objCollection As Collection

  Dim objQuery      As WorkbookQuery

  Dim strCheck      As String

  Dim lngIndex      As Long
  On Error Resume Next
  Set objCollection = New Collection
  With ThisWorkbook.Worksheets(1)
    For lngIndex = 1 To 100
      If .Cells(lngIndex, 2).Value > 0 Then
        objCollection.Add _

       .Cells(lngIndex, 1).Value , _

       .Cells(lngIndex, 1).Value
      End If
    Next
  End With
  For Each objQuery In ThisWorkbook.Queries
    strCheck = ""

    strCheck = objCollection(objQuery.name)
    If Len(strCheck) > 0 Then
      objQuery.Formula = Replace(objQuery.Formula, "/2014-2015/", "/2016-2016/")
    End If
  Next
  Set objCollection = Nothing

Stimmt, es gibt noch nicht allzuviel im deutschsprachigen Raum zu Excel 2016 + PQ + VBA. Was mich betrifft, ist einfach momentan die Zeit zu knapp. Ab August wird’s besser und ich habe dann vor, einige Artikel zu Excel + PQ zu schreiben. Die Themen stehen auch schon fest; nur wie gesagt, alles eine Zeitfrage.

21. Mai 2016 at 13:09

Vielen Dank Herr Louha, habe nun nach dem Test das erste Makro zur Aktualiserung der Tabellen angewendet, habe dort aber nicht das Jahr sondern die Tabellenabfrage geändert. Von #statT-Limit=0&statT-Table=1 auf #statT-Limit=1&statT-Table=1; das betrifft dann 78 Abfragen in der Tabelle. Gestartet um 12:10 Uhr, nach ca. 10 Minuten dachte ich das die Änderung durch sind, aber Excel rechnet sich im TaskManager tod. Es werden 15 GB RAM blockiert, es sind 9 Container mit Aktiviitäten zu sehen und das System CPU wird zu 98% belastet. Jetzt haben wir 13:00 Uhr, Excel reagiert nicht mehr, also kann ich auch nicht sehen ob die Quelle geändert wurde, denn Speichern kann ich auch nicht. Excel rechnet und rechnet, das sehe ich am blokierten RAM-Speicher, der geht mal runter auf 3 GB und dann wieder hoch auf 14-15 GB. Wo kann der Fehler liegen? Haben Sie eine Ahnung? Ich werde mal die Aktualisierung im Hintergrund abstellen, vllt. hilft das etwas, gleichzeitig werde ich die Auslagerungsdatei verdoppeln, habe nich gebügend Speicher zur Verfügung. Wenn das alles nichts hilft, muss ich meinen IT-Chef mal fragen ob ich den RAM-Speicher vergrößern kann, um das Problem zu umgehen. Wünsche ein schönes Wochenende Mfg EC

21. Mai 2016 at 16:03

Meine Test sind nun beendet, warum Excel mal will und mal nicht? Ich habe in den Abfragen nur die Heimwerte in der Quelle verändert also von statT-Limit=0&statT-Table=1 auf statT-Limit=1&statT-Table=1, das hat funktioniert, aber Excel hat sich aufgehängt und ich hatte Glück, ich konnte vorher noch speichern. Nun wollte ich das mit den Auswärtstabellen genauso machen also von statT-Limit=0&statT-Table=2 auf statT-Limit=1&statT-Table=2 ändern. Das Makro läuft durch, aber Änderungen werden nicht angezeigt, Abspeicher geht nicht, da relativ schnell sich Excel aufhängt. Auch nach einer Stunde belegt Excel ca. 14 GB RAM, über 90% CPU und es sind mindestens 8 MS Container mit Aktivitäten zu sehen. Also habe ich das mal im Debbug-Modus durchgeführt und siehe da, nach spätestens 10 mal F8 muss man eine Pause machen, denn Excel geht hoch auf 12 GB und 98% CPU. Wartet man etwas, dann kann man weiter machen :-) aber ich sehe das in VBA der Debbug-Modus langsamer wird, geöfters ich F8 drücke. Ich habe mich dann entschieden maximal 10 Abfragen zu aktualisieren und dann eine Pause einzulegen, bis Excel bei der CPU auf 1% steht. Das Ausschalten der Aktulaisierung im Hintergrund hat nichts gebracht, also habe ich in 16 Abfragen das wieder aktiviert und mal schauen was passiert. Macht man das über den Debbug-Modus, wird nur ein Container geöffnet und Excel verwendet max. 3 GB RAM!
Fazit: Mit F8 im Debbug-Modus wird der Link angepasst, wenn man nach 10 Abfragen wartet und dann die nächsten 10 abruft. Eine komplette Abfrage über alle 158 Abfragen ist nicht möglich, da Excel wohl im Hintergund Dinge aktualisiert, die CPU und RAM fressen, bis sich Excel vollständig verabschiedet. Aber mit F8 kann ich leben, ist einfacher als in jede Abfrage einzeln den Link zu ändern.

Also vielen Dank für die vielen Hilfen und sollten Sie einen Tester für Excel 2016 und Query benötigen, dann sagen Sie bitte bescheid. Gruß EC

22. Mai 2016 at 14:58

herzlichen Dank für die detaillierten Rückmeldungen und Erfahrungsberichte, was sicher auch für andere Leser und Leserinnen interessant sein könnte. Ich muss das auch mal genauer testen; vielleicht finde ich dazu auch noch was und würde hier berichten

23. Mai 2016 at 19:21

Hallo Herr Louha,
würde es helfen wenn ich ihnen einer meiner Exceltabellen zuschicken würde um das Verhalten von Excel 2016 zu testen?
Ich würde das gerne freiwillig zuschicken, Viren geprüft von AVG und keine Forderungen stellen.

Denn ich habe im Netz festgestellt, das keiner diese Probleme hat, wohl aber auch weil keiner so blöd ist mal eben 158 Abfragen in ein Tabellenblatt einzubetten.

Mfg EuroCafe

24. Mai 2016 at 07:43

Hallo EuroCafe,

sehr gerne, das würde mir das Anlegen von zig Abfragen sparen. Kann allerdings was dauern, bis ich zum Testen komme. Als E-Mail-Adresse bitte vba(at-zeichen)maninweb(punkt)de verwenden.

Viele Grüße

SanAndreasGames

1. Februar 2017 at 00:12

Super :)))