In letzter Zeit hatte ich mich wieder verstärkt mit der Textverarbeitung in Winword beschäftigt und bin dabei auf die beiden Themen "Vorlagen" und "Änderungsnachverfolgung" gestoßen. Zu denen ich im Folgenden Artikel einige Anmerkungen machen möchte.

Hierbei möchte ich folgende Punkte im Artikel behandeln:

• Arbeiten mit Vorlagen in Microsoft Office 2010, 2013 usw.
  • Speicherort für Vorlagen
  • Cloudspeicher (Dropbox oder Onedrive) für Vorlagen und Microsoft Office Online
• Arbeiten mit der Änderungsnachverfolgung (Funktion Änderungen nachverfolgen
  • Kommentare und Änderungen von anderen Benutzern zwischen Dokumenten kopieren
  • Unterschiedliche Darstellung der Änderungen in Winword 2013 zu Winword 2010 (Einfache Markups, bzw. verkürzte Änderungen)

Wahrscheinlich ist für die meisten dieses schon bekannt, aber manchmal hilft es auch weiter sich mit den Grundlagen von Winword zu beschäftigen.
 

Arbeiten mit Vorlagen in Microsoft Office

Neben einer persönlichen Makroarbeitsmappe, die im Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" beschrieben worden ist, nutze ich auch sehr gerne Dokumentenvorlagen die sowohl in Powerpoint als auch in Winword eine echte Arbeitserleichterung darstellen.
 

Speicherort für Microsoft Office Vorlagen

Diese Vorlagen können in allen Programmen (wie Powerpoint oder Winword)  direkt geöffnet werden.

Innerhalb Office 2010 war dieses unter
Datei-> Neu -> Meine Vorlagen
und ab Office 2013 kann schon beim Start (oder alternativ ebenfalls über Datei->Neu)
zwischen  Empfohlenen oder Persönlichen Vorlagen gewechselt werden.

Ein echter Fortschritt in Office 2013 ist in meinen Augen, dass der Speicherpfad für Office Vorlagen nun individuell eingestellt werden kann.

Während bis Office 2010 dieses noch im Pfad

C:
Users
BENUTZERNAME
AppData
Roaming
Microsoft
Templates

erfolgte kann der Pfad ab Office 2013 individuell unter den Optionen angepasst werden.

Hierzu wechselt man in der Anwendung (Word, oder Powerpoint) auf
Datei-> Optionen

und kann unter Speichern  unter den Punkt "Dokument speichern" den "Standardspeicherort für persönliche Vorlagen" auswählen.

Hier können dann aus der Anwendung entsprechende Vorlagen direkt gespeichert werden, egal ob dieses nun für Powerpoint (*.POTX) oder Winword (*.DOTX) ist.

Als Vorlagen bieten sich neben den klassischen Briefkopf auch entsprechende Präsentationen mit Masterfolien an, Rechnungen oder ganz klassische Geburtstagskarten.

Cloudspeicher für Officevorlagen

Ab Office 2013 könnte, dank flexibler Speicherort, auch die Überlegung in Richtung Ablage von Vorlagen in die Cloud interessant sein. Sofern an einen Endgerät ohnehin Windows 8 eingesetzt wird (und ein Microsoft Konto verwendet wird) würde sich hier auch OneDrive oder Dropbox anbieten. Onedrive bietet dabei 15 GB Speicher (wobei durch den Partnerlink noch weitere 500 MB hinzu kommen) und Dropbox über den Partnerlink noch weitere 250 MB. Mir persönlich gefällt Dropbox besser, da hier auch unproblematisch Ordner für andere User frei gegeben werden können wohingehend OneDrive derzeit gefühlt relativ langsam und nicht ganz so komfortabel ist. Dafür ist es wesentlich besser in Windows 8 eingebunden und kann hier ebenfalls seine Vorteile entfalten. Ferner können auf die in Onedrive abgelegten Daten auch innerhalb Microsoft Office Online zugegriffen werden.

Arbeiten mit der Änderungsnachverfolgung bzw. die Funktion Änderungen überprüfen

Gerade beim Versand von Protokollen ist die innerhalb der Symbolleiste "Überprüfen" zu findende Funktion "Änderungen nachverfolgen" sehr hilfreich. Hier wird innerhalb Word hervorgehoben welche Änderungen am Dokument vorgenommen worden sind und diese können dann per Schaltfläche angenommen oder abgelehnt werden.Ferner können auch entsprechende Kommentare oder Rückfragen  hier eingefügt werden. Sämtliche Kommentare werden dann mit den Benutzernamen der bearbeitenden Person versehen, so dass eine entsprechende Nachvollziehbarkeit gegeben ist.

Änderungen nachverfolgen / Kommentare und Änderungen von anderen kopieren

Sofern von mehreren Personen verschiedene Abschnitte bearbeitet werden besteht das Problem, dass nun aus unterschiedlichen Dokumenten die Änderungen in das Hauptdokument kopiert werden sollen und die Kommentare und Änderungen der jeweiligen Person weiter erhalten bleiben sollen.

Um dieses zu erreichen ist zur Übernahme von Änderungsnachverfolgung und entsprechenden Kommentatoren in beiden Dokumenten die Änderungsnachverfolgung zu deaktivieren, so dass hier die entsprechenden Abschnitte kopiert und eingefügt werden können. Auf diese Weise bleiben alle hervorgehobene Änderungen und Kommentaren wie in der Kopiervorlage vorhanden.

Darstellung von Markups Änderungen nachverfolgen

Bis Office 2010  wurden gelöschte Texte in der Änderungsverfolgung als durchgestrichener Text dargestellt. Ab 2013 ist hier nur noch ein roter Strich an der Seite dargestellt.
Hier ist eine Umstellung innerhalb der Befehlsgruppe "Nachverfolgung" im Ribbon "Überprüfen" erforderlich.

Während in Winword 2010 hier noch im oberen Auswahlmenü "Für Überarbeitung anzeigen" der Punkt "Abgeschlossen: Markups anzeigen" ausgewählt ist, kann ab Winword 2013 zwischen "Einfaches Markup" (hier erfolgt nur ein Korrekturzeichen an der Seite) und "Markup: Alle" ausgewählt werden. Letzterer Punkt entspricht der bisherigen Darstellung der Änderungen im Dokument.

Ausgangslage
Da das SAP System ein paar Tage offline sein wird, soll für ein Bericht anhand einer Kostenträger festgestellt werden, in welchen Bereich diese liegt. Grundsätzlich sollten Kostenträger soweit sprechend sein, dass man auch anhand der Nummer erkennen sollte, wo diese zuzuordnen ist, aber teilweise kann dieses (zum Beispiel bedingt durch Umgliederung von Kostenstellen oder bei Innenaufträgen) problematisch sein.

Daher wurden die einzelnen Gruppen in einer Exceltabelle exportiert, so dass hier nachgesehen werden kann, in welcher Gruppe ein Kostenträger liegt. Hierbei können sowohl Einzelwerte als auch Intervalle eine entsprechende Gruppe darstellen.

Um das Beispiel etwas zu vereinfachen (und nicht mit der Kostenstellenstruktur zu arbeiten) sind in diesen Beispiel statt einzelner Kostenstellen Notenpunkte und als Kostenstellengruppen die entsprechende Schulnote genommen worden.

Im Beispiel soll durch die Eingabe der Kostenstelle 11 (Punkte) direkt ausgegeben werden, dass dieses der Kostenstellengruppe (Schulnote) "Note 2 - Gut" entspricht.

Hierzu wurde die Tabelle wie folgt aufgebaut.


Hierbei wurden im Namensmanager die einzelnen Spaltenüberschriften einen entsprechenden Zellenbereich zugeordnet (KS_von umfasst die Zellen A2 bis A7).
Die Definition von Namensräumen durch Verwendung des Namensmanager ist im Artikel "Formulare gestalten in Excel" beschrieben.

Wenn es sich bei den Werten um Einzelwerte handeln würde (dieses ist im Fall der 0 Punkte der Fall) könnte nun wie im Artikel "Die Funktion SVERWEIS ohne #NV ausgeben" verfahren werden. Da es sich jedoch um Intervalle handelt und zum Beispiel die Kostenstelle 11 zwischen 10 und 12 in der Kostenstellengruppe "Note 2 - Gut" liegt muss hier eine andere Formel verwendet werden.

Funktion VERWEIS  Syntaxversion VEKTORVERSION

Hierzu kann die die Vektorversion der Formel VERWEIS verwendet werden.

Der Syntax der Version lautet dabei wie folgt:

=VERWEIS(Suchkriterium;Suchvektor;[Ergebnisvektor])

Die Besonderheit dieser Funktion liegt darin, dass sie wenn die VERWEIS-Funktion keinen Wert finden kann, der mit dem jeweiligen Wert von Suchkriterium übereinstimmt, den größten Wert in Suchvektor verwendet, der kleiner oder gleich dem Wert von Suchkriterium ist.

Für unser Beispiel würde die Formel zur Ermittlung der Kostenstellengruppe wie folgt lauten:

=WENN(ISTNV(VERWEIS(1;1/(B10>=KS_von)/(B10<=KS_bis);KS_Gruppe));"Nicht zugeordnet";VERWEIS(1;1/(B10>=KS_von)/(B10<=KS_bis);KS_Gruppe))

Hierbei funktioniert die Funktion wie folgt:
Überprüfe jede Zeile, ob Teil 2 und Teil 3 (getrennt durch /) des SuchVEKTORS! wahr sind und liefere dann die passende Zeile aus dem Zielvektor zurück.

Dieses funktioniert, da durch den Ausdruck (B10>=KS_von) ein WAHR ausgegeben wird, welches einer 1 entspricht, ebenso würde der Fall WAHR (und damit 1) eintreten, wenn B10<=KS_bis entspricht. Sofern kein Treffer erfolgt würde ein #NV ausgegeben werden.
Dieser Fall ist in der obigen Formel durch die Formel ISTNV und der Wertausgabe "nicht zugeordnet" abgefangen.


Das Ergebnis dieser Formel kann dann auch wiederum in einer SVERWEIS oder vergleichbare Funktion verwendet werden um zum Beispiel Berichtsempfänger einer Kostenstelle (Kostenstellengruppenverantwortliche) zu erhalten oder um ergänzende Funktionen zu erhalten. So könnte zum Beispiel bei der Schulnote 4 ausreichend noch ein Hinweis auf Nachprüfung möglich erfolgen.

Kleiner Hinweis am Rand:

In der Zelle C11 wurde die Formel um ein =WENN(B11="";"";   ... ) ergänzt, da andernfalls kein Wert als 0 interpretiert werden würde und somit keine Angabe automatisch der Schulnote 6 - ungenügend entspricht.

Fazit
Zwar ist es schöner Kostenstellengruppen direkt im System auszuwerten, aber manchmal kann es hilfreich sein auch Berichte außerhalb des Systems nach den einzelnen Stammdaten auswerten zu können. Sehr hilfreich ist hier die im Artikel "Auflösen von Stammdatengruppen nach Einzelwerten - Einzelwerte zu Sets" beschriebene Methode zum Export von Stammdatengruppen aus SAP.

Weitere Verweisfunktionen in Excel

Insgesamt hat Excel hier drei Verweisfunktionen:

1. VERWEIS Funktion

Die in diesen Artikel dargestellte Formel/Funktion VERWEIS ist die Grundfunktion aller Verweisfunktionen und ist am dynamischsten in der Anwendung, wie hier ja auch zu sehen ist.

2. SVERWEIS Funktion

Die SVERWEIS Funktion dürfte eine der bekannteren Formeln im Controlling sein.
Dabei ist sie wie folgt aufgebaut.

    =SVERWEIS(Suchkriterum;Matrix;Spaltenindex;Bereich_Verweis)

Anhand eines Suchkriteriums wird eine Matrix (Suchbereich) durchsucht und die entsprechende Spalte ausgegeben, die mit der ersten Spalte übereinstimmt. Ferner wird über Bereich_Verweis festgelegt ob eine exakte übereinstimmung (FALSCH) oder ungefähre Übereinstimmung (WAHR) gewünscht ist.

Im Artikel "SVERWEIS ohne NV und dynamische Größen für Datenbereiche" ist diese anhand eines Beispiels beschrieben.
 

3. WVERWEIS Funktion

Diese Funktion arbeitet vergleichbar zur SVERWEIS Funktion gibt aber nicht Spalten sondern gibt das Ergebnis aus einen Zeilenindex heraus.

Dabei ist sie wie folgt aufgebaut.

    =WVERWEIS(Suchkriterum;Matrix;Zeilenindex;Bereich_Verweis)

Als Eselsbrücke könnte man sich merken, dass die SVERWEIS Funktion senkrecht nach der ersten Spalte die Daten durchsucht, wohingegen die WVERWEIS Funktion waagrecht in der ersten Zeile nach einer Übereinstimmung sucht und die folgenden Daten dann anhand des übergegeben Index ausgibt.

Ein Beispiel für die WVERWEIS Funktion ist im Artikel "Excel: WVERWEIS Funktion oder Jahresdaten umgruppieren für Mehrjahresvergleich" erläutert.

Ausgangslage:
Es werden die Ergebnisse (Saldo von Kostenstellengruppen) bei unterschiedlichen Rechenmodellen betrachtet. Hierbei sollen die Veränderungen der Variante C zur Variante B ausgedrückt werden.

Während beim "Saldo A" Die Verändeurng von 100 auf 150 korrekt mit + 50 % aus der Berechung (Variante C - Variante B) im Verhältnis zu (/) Variante B ausgewiesen wird, wird beim Saldo B bei dieser Berechnung ein Verändung um - 50 % dargestellt, obgleich der Saldo um 50 % (Veränderung von +50 zu -100) eine Wertsteigerung erfahren hat.

Ursache hierfür ist die mathematische Regel zum Teilen durch negative Zahlen.

Teilt man eine positive Zahl durch eine negative oder eine negative Zahl durch eine positive, so ist das Ergebnis negativ.

Mathematisch ausgedrückt: "Ein Bruch ist kleiner Null, wenn Zähler und Nenner verschiedene Vorzeichen haben."

Um nun doch noch eine "sinnvollere" Darstellung der Veränderung dieser Salden zu erhalten gibt es in Excel 3 Varianten.


Anhand des Beispiels stehen die Werte in den Spalten B3 und C3. Sofern wir mit negativen Zahlen rechnen, kann nicht die Formel (C3-B3)/B3 verwendet werden. Statt dieser Variante bieten sich drei andere Varianten ein.

1. Wenn Funktion
Die entsprechende Formel lautet hierbei:

=WENN(UND(C3>B3;B3<0);(C3-B3)/B3*-1;(C3-B3)/B3)

Sofern die Variante C einen höheren Wert als Variante B hat und der Wert der Variante B kleiner als 0 ist, wird die Veränderung von C zu B ins Verhältnis zur Variante B gesetzt und mit -1 multipliziert, andernfalls wird das Verhältnis direkt ausgegeben.

2. Vorzeichen Funktion
Die entsprechende Formel lautet hierbei:

=VORZEICHEN(C3-B3)*ABS((C3-B3)/B3)

Über die Funktion Vorzeichen wird ermittelt, ob es sich bei der Veränderung von C zu B um eine positive oder negative Veränderung handelt und danach die absolute Veränderung (ohne Verzeichen) von C nach B im Verhältnis zu B mit den entsprechenden ermittelten Vorzeichen ausgegeben.

3. Verhältnis zum absoluten Wert
Eine Kurzfassung und die in meinen Augen elegantere Variante ist in der Zelle D6 angewendet.
Hier lautet die Formel wie folgt:

=(C3-B3)/ABS(B3)

Hier wird die Veränderung zwischen der Variante C zur Variante B ermittelt und diese ins Verhältnis zum absoluten Wert der Variante B gesetzt. Das Vorzeichen der Veränderung wird anhand der Berechnung im Zähler des Bruches ermittelt, so dass hierdurch am einfachsten die prozentuale Veränderung ausgedrückt werden kann. Ferner ist diese Variante um einiges einfacher zu lesen, als die Variante mit der WENN Funktion.

Fazit:
Somit können sowohl negative, positive als auch gar keine prozentuale Veränderungen beim Vergleich zweier Ergebnis dargestellt werden. Gerade beim Vergleich von Salden einer Kontengruppe oder von verschiedenen Kostenträgern kann es öfters auch einmal ein negatives Ergebnis geben, so dass dieser Fall beim Vergleich zweier Varianten oder Geschäftsjahre durchaus relevant sein kann und bei der ersten Berechnung (wie in der Zelle D3) zu Verwirrungen führen kann.

Nachtrag / Fragen aus Kommentaren:
Ev:

"Danke! Wie ist das bei Veränderungen von Null auf eine positiven Wert? Ich nutze die Formel für eine Budgetkalkulation. Technisch gibt es hier eine "Verbesserung", d.h. es müsste einen prozentualen Anstieg geben, allerdings wird die Formel mit #DIV Fehlermeldung angezeigt. Wie kann man das lösen? Danke vorab!"

Dieses hatte ich wie folgt vorgeschlagen zu lösen:

wenn ich von 0 ausgehe ist prozentual eigentlich unendliches Wachstum beziehungsweise kann hier mathematisch kein prozentuales Wachstum von einer Basis von 0 sinnvoll angegeben werden. Die Steigerung müsste ja auch als Multiplikation dargestellt werden.  Wenn es um reine Darstellung geht würde ich im obigen Beispiel folgende Formel verwenden.   =(C3-B3)/ABS(WENN(B3=0;1;B3))  Hier würde dann also der Absolute Wert ausgegeben werden.

Insgesamt freue ich mich jedes Mal aufs Neue, wenn auch zu älteren Ertikeln noch spannende Fragen aufkommen : 

 

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.

Ausgangslage
Innerhalb einer Access-Datenbank sollen verschiedene Berichte zusammengefasst und durch Abfragen miteinander verbunden werden. Hierzu ist es jedoch erforderlich, dass die zugrunde liegenden Tabellen aktualisiert werden und dafür in die Datenbank importiert werden können. Da es sich um eine Vielzahl von Tabellen handelt ist die Importfunktion in Access etwas umständlich. Vergleichbar zum Artikel "Excel Umgang mit Makros und Visual Basic for Applications (VBA)" bietet aber auch Access die Möglichkeit Makros zur Arbeitserleichterung zu verwenden an.
 

Makro unter Access zum Import erstellen

Zur Erstellung eines Makros kann im Ribbon Erstellen in der Befehlsgruppe Andere die Funktion Makro ausgewählt werden.

Hierbei unterscheiden sich die Oberflächen zwischen Access 2007 und Access 2010 erheblich. Daher wird im Folgenden sowohl auf Access 2007 als auch Access 2010 eingegangen.

Makro unter Access 2007 erstellen

Damit alle Funktionen zur Erstellung eines Makros aktiviert sind sollten sowohl die Schaltfläche "Alle Aktionen anzeigen", "Bedingungen" als auch "Argumente" in der Symbolleiste Entwurf aktiviert sein.

Insbesondere die Möglichkeit Bedingungen vor der Ausführung von Aktionen zu hinterlegen ermöglicht es für jede Aktion vorher eine Rückfrage einzuholen.

1. Vorhandene Tabellen löschen
Als ersten Schritt sollen die bestehenden importierten Tabellen in der Datenbank gelöscht werden. Hierzu wird über die Aktion "Löschen Objekt" der Objekttyp Tabelle und über das Feld Objektklasse die eigentliche Tabelle gelöscht. Im Beispiel ist dieses die Tabelle "Grunddaten".

Durch die Bedigung Meldung("Tabelle löschen";1)=1 wird diese Aktion nur bei Bestätigung ausgeführt.

2. Neue/Aktualisierte Tabellen einfügen
Über die Funktion "TransferArbeitsblatt" kann sowohl der Import als auch Export von Tabellen erfolgen. Im Beispiel soll eine Exceltabelle (ExcelWorkbook) als Tabelle "Grundtabelle" importiert werden. Dieses funktioniert über den Transfertyp Importieren, Dateiformat Excel Workbook und Angabe des Tabellennamen "Grundtabelle"

Hierbei können sowohl der Dateiname hinterlegt werden (im Beispiel B:import.xls) als auch über das Feld Bereich das zu importierende Tabellenblatt (im Beispiel Grunddaten!A-Z) angegeben werden. Hier zeigt sich auch schon der Nachteil von Makros, da sowohl der Dateiname als auch der Speicherort fix angegeben werden müssen.Über die Option Besitzt Feldnamen wird die erste Zeile der Tabelle als Datensatzbeschreibung verwendet. Durch die Bedingung kann wiederum abgesichert werden, dass nicht aus Versehen unbeabsichtigt eine Tabelle importiert wird.

3. Abfrage per Makro starten
Wenn alle Tabellen importiert sind kann über die Aktion "ÖffnenAbfrage" eine entsprechende Abfrage gestartet werden.

Durch die Ansicht Datenblatt erscheint die Abfrage auch direkt als Auswertung und kann exportiert oder weiter bearbeitet werden.

Makro unter Access 2010 erstellen

Ab Access 2010 hat sich die Darstellung zur Erstellung von Makros von einer Tabellenform und darunter liegenden Attributen zu einer Baumstruktur verändert. Ferner wurde die Aktion TransferArbeitsblatt in die Aktion ImportierenExportierenTabellenblatt umbenannt. Bestehende Makros werden direkt umgewandelt.
Das eingangs beschriebene Makro würde unter Access 2010 wie folgt geschrieben werden:

Makro zu Visual Basic konvertieren

Sicherlich sind solche Makros eine Arbeitserleichterung beim Import und auch der Verarbeitung von außerhalb der eigentlichen Access Datenbank liegenden Tabellen und sonstigen Daten.

Allerdings sind prinzipbedingt die einzelnen Abfragen auch relativ eingeschränkt in ihrer Funktionsweise. So war es in Access 2007 nur möglich für jede einzelne Aktion eine entsprechende Bedingung zu definieren. Ab Access 2010 können innerhalb der Bedingung auch mehrere Aktionen eingefügt werden. Allerdings ist auch unter Access der Import von Tabellenblättern nur über die Angabe des genauen Dateinamens inklusive Speicherort möglich. Hier gibt es unter VBA aber darüber hinaus gehende Möglichkeiten.  Im ersten Schritt muss hierfür das angelegte Makro in eine VBA Funktion umgewandelt werden

Unter Access 2007 kann über die Datenbanktools die Funktion "Makros zu Visual Basic konvertieren" aufgerufen werden.

Ab Access 2010 befindet sich diese Funktion in der Entwurfsansicht des jeweiligen Makros.

Unter beiden Funktionen wird das vorher entworfene Makro dann als VBA Code dargestellt und kann als Modul im VBA Editor betrachtet und bearbeitet werden.

Der VBA Code für das oben beschriebene Makro würde dabei wie folgt umgewandelt werden.

Das beschriebene Makro ist nun als Function angelegt und steht als VBA Modul in der Access Datenbank zur Verfügung.

Per Makro VBA Funktion ausführen

Diese Funktion kann dann als Code über ein neues Makro aufgerufen werden.
Unter Access 2007 ist dieses über die Aktion "AusführenCode" möglich.

Hier wird direkt die nun vorhandene Funktion Import ohne weitere Argumente ausgeführt.
Ab Access 2010 hat sich hier eigentlich nur die Darstellung verändert, so dass auch hier der Code ausgeführt werden kann.

Erweiterung Makro um VBA Code

Der Vorteil bei der Umwandlung zu VBA besteht darin, dass nun die aus der Makrofunktion bekannten Funktionen erheblich erweitert werden können.

Dynamischer Speicherort zum Import von Daten verwenden

Wie beim Makro TransferArbeitsblatt beziehungsweise ImportierenExportierenTabellenblatt beschrieben muss zum Import einer Tabelle nach Access sowohl der Dateiname der Exceltabelle als auch der Speicherort (im Beispiel war dieses b:import.xls) angegeben werden. Sofern die Datenbank aber flexibel genutzt werden soll, oder aber sich der Speicherort der zu importierenden Daten auch ändern kann, kann folgende Ergänzung des VBA Codes sinnvoll sein.

    Dim Pfad As String
    Pfad = Application.CurrentProject.Path
    
    If MsgBox("Import aus " & Pfad & " ?", vbYesNo) = vbYes Then
       Pfad = InputBox("Pfadnamen ohne abschliessenden ")
    End If
    
    MsgBox "Import aus " & Pfad & " !", vbInformation, "Importquelle"

Über die Anweisung DIM Pfad As String wird eine Variable von Typ String definiert. Diese Variable bekommt als Wert den aktuellen Projektpfad der Accessdatenbank zugewiesen, so dass die Datenbank im Idealfall zu den zu importierenden Dateien kopiert werden kann.
Sofern die Importdaten jedoch in einen anderen Pfad liegen kann über die InputBox ein anderer Pfad angegeben werden. In Form einer Infobox wird danach noch einmal der Importpfad angegeben.

Die Anweisung zum Import der Tabelle import.xls kann nun wie folgt angepasst werden:

If (MsgBox("Tabelle importieren", 1) = 1) Then
        DoCmd.TransferSpreadsheet acImport, 10, "Grundtabelle", Pfad & "import.xls", True, "Grunddaten!A:Z"
End If

Hier wird nun ebenfalls das Tabellenblatt Grunddaten aus der Tabelle import.xls importiert. Jedoch kann der Speicherort vorher nochmals geändert werden. Sofern dieser Pfad nicht geändert wurde, wird der Pfad der Access-Datenbank verwendet.

Verschachtelte Bedingungen

Ein weiterer Vorteil innerhalb VBA ist, dass hier auch mehrere Bedingungen miteinander verschachtelt werden können oder auch mehrere Aktionen nach einer Bedingung gestartet werden können.

Sollen nach erfolgreichen Import der Tabellen auch mehrere Abfragen gestartet werden kann dieses über folgende Anweisung erfolgen.

    MsgBox "Daten wurden importiert", vbOKOnly, "Keine Panik"
    If (MsgBox("Auswertungen starten", 1) = 1) Then
        DoCmd.OpenQuery "110 Auswertung 1", acViewNormal, acReadOnly
        DoCmd.OpenQuery "120 Auswertung 2", acViewNormal, acReadOnly
        DoCmd.OpenQuery "130 Auswertung 3", acViewNormal, acReadOnly
    MsgBox "Speichern Sie die einzelnen Abfragen und kopieren die Daten über Inhalte Einfügen->Transponieren in die Zieltabelle", vbInformation, "Daten ausgewertet"
    End If

Hierdurch werden nach erfolgreichen Importiert der Tabellen drei Auswertungen gestartet und eine Anweisung ausgegeben, wie mit diesen weiter verfahren werden soll. Dieses erfolgt über die Anweisung DoCmd.OpenQuery verbunden mit der Darstellung Normal und Schreibgeschützt. Danach wird über die Anweisung MsgBox eine Anweisung zur weiteren Verarbeitung der Daten ausgegeben.

Insgesamt würde die angepasste Anweisung wie folgt lauten:

Entsprechend könnten auch weitere Anweisung innerhalb des VBA Codes angepasst werden und hierdurch eine wesentlich flexiblere Importfunktion gestaltet werden. Darüber hinaus können auch weitere Informationen und Anweisungen zur Handhabung der Datenbank hinterlegt werden.

Als Gesamtcode würde dieses dann in Access 2010 wie folgt aussehen:

Function Import()
 Dim Pfad As String
    Pfad = Application.CurrentProject.Path
    
    If MsgBox("Import aus " & Pfad & " ?", vbYesNo) = vbYes Then
       Pfad = InputBox("Pfadnamen ohne abschliessenden ")
    End If
    
    MsgBox "Import aus " & Pfad & " !", vbInformation, "Importquelle"

    If (MsgBox("Tabelle löschen?", 1) = 1) Then
        DoCmd.DeleteObject acTable, "Grunddaten"
    End If

If (MsgBox("Tabelle importieren", 1) = 1) Then
  DoCmd.TransferSpreadsheet acImport, 10, "Grundtabelle", Pfad & "import.xls", True, "Grunddaten!A:Z"
End If
MsgBox "Daten wurden importiert", vbOKOnly, "Keine Panik"
        If (MsgBox("Auswertungen starten", 1) = 1) Then
            DoCmd.OpenQuery "110 Auswertung 1", acViewNormal, acReadOnly
            DoCmd.OpenQuery "120 Auswertung 2", acViewNormal, acReadOnly
            DoCmd.OpenQuery "130 Auswertung 3", acViewNormal, acReadOnly
MsgBox "Daten über Inhalte Einfügen->Transponieren in die Zieltabelle", vbInformation, "Daten ausgewertet"
End If

Import_Exit:
    Exit Function

Import_Err:
    MsgBox Error$
    Resume Import_Exit

End Function

Ausgangslage
In einer Exceltabelle sind für verschiedene Kostenstellen entweder das Budget oder der gebuchte Aufwand je Geschäftsjahr in einer Tabelle angegeben. Nun soll eine entsprechende Zusammenfassung des Ergebnis pro Geschäftsjahr ausgegeben werden.

Lösung
Das ideale Werkzeug hierfür ist die Anwendung von Pivottabellen.

Die Ausgangsdaten sehen zum Beispiel wie folgt aus:

Innerhalb des Tabellenblattes sind in der Spalte A das Jahr, B die Kostenstelle, C das Budget und in D der im jeweiligen Jahr gebuchte Aufwand zu finden.

Da diese Tabelle fortgeschrieben wird, ist es nicht sicher, bis wohin die Daten gefüllt werden.

1. Grunddaten festlegen
Wie im Artikel SVERWEIS ohne NV und dynamische Größen für Datenbereiche unter "Die Funktion BEREICH.VERSCHIEBEN - Datenbereich dynamisch von der Größe anlegen" beschrieben kann es, sofern weitere Daten in der Liste erwartet werden, sinnvoll sein, die Datengrundlage der Pivottabelle entsprechend anzupassen.


Im Beispiel kann innerhalb des Ribbon "Formeln" in der Befehlsgruppe "Definierte Namen" ein entsprechender Name definiert werden (etwas ausführlicher ist dieses auch im Artikel Formulare gestalten in Excel erläutert).

Hierbei wird der "dynamische Datenbereich" wie folgt festgelegt:


Durch die Formel

• =Bereich.Verschieben($A$1;;;Anzahl2($A:$A);Anzahl2($1:$1))

wird den Namen Grunddaten automatisch der Inhalt der Tabelle im Blatt Grundtabelle zugewiesen. Dank der Formel wird der Bereich von der Zelle A1 bis zur Zelle D8 festgelegt (entsprechend ist dieser Bereich auch mit einer gestrichelten Linie von Excel hinterlegt).

Hierbei werden die Anzahl der Zeilen aus der Spalte A und die Anzahl der Spalten aus der Zeile 1 ermittelt.

Sofern nun weitere Daten hinzukommen wird der Bereich entsprechend angepasst.


2. Pivottabelle anlegen

Innerhalb des Ribbon "Einfügen" kann nun eine Pivottabelle eingefügt werden.



Als Bereich der zu analysierenden Daten wird nun der vorher angelegte Name durch

• =Grunddaten

eingetragen.

Auf diese Weise wird die Datengrundlage der Pivottabelle autmatisch erneut berechnet, sollten weitere Datenzeilen eingefügt werden.



Innerhalb des Spaltenvorrates können nun die einzelnen Datenfelder aus der Feldliste Als Spalten, Zeilen oder Werte einer Pivottabelle übernommen werden.



Innerhalb der unter Werte angegebenen Felder können über die Wertfeldeinstellungen festgelegt werden, wie diese Daten ausgegeben werden (die häufigst genutzten Methoden dürften wohl Summe, Anzahl oder auch Mittelwert sein).



Im Ergebnis sind nun nach Jahren Budget und Aufwand je Kostenstelle ausgegeben. Um nun das noch verfügbare Budget zu berechnen kann ein berechnetes Feld im Ribbon "Optionen" in der Befehlsgruppe "Berechnung" über "Felder, Elemente und Gruppen" ein "Berechnetes Feld" eingefügt werden.



Bei der Definition dieses Feldes kann dann auf die anderen Felder zugegriffen werden.



Im Beispiel hat das berechnete Feld den Namen "Verfügbar" und berechnet sich aus der Formel:

• =Budget - Aufwand

Sofern diese Formel definiert ist, kann das Feld durch "Hinzufügen" aktiviert werden und befindet sich nun ebenfalls in der Feldliste.



Im Beispiel kann nun in der Pivotabelle auch die Summe über Verfügbar ermittelt werden, so dass hier für 2013 ersichtlich ist, dass die Kostenstelle A noch 350 zur Verfügung hat (Budget in Höhe von 1000 abzüglich Aufwand in Höhe von 650).

Diese Berechnungen können natürlich auch mit anderen Formeln genutzt werden. So könnten bei Preisen die Bruttopreise aus den Nettopreisen ermittelt werden, oder eine prozentuale Lohnsteigerung berücksichtigt werden.

Sofern sich ein Überblick über die berechneten Felder geschafft werden soll ist dieses ebenfalls im Ribbon Optionen der Pivot-Tabelle unter "Berechnung" bei "Felder, Elemente und Gruppen" durch die Option "Formeln auflisten" möglich.



Hierdurch wird ein neues Tabellenblatt angelegt in dem alle berechnete Formeln aufgeführt sind.



Dieses Tabellenblatt kann dann auch direkt als Dokumentation verwendet werden.

Ein weiteres (umfangreicheres) Beispiel für berechnete Felder innerhalb Pivot-Tabellen ist im Artikel "Excel Pivottabelle Darstellung Grenzwerte Einnahmen auf Projekte je Person durch Zuordnung VZÄ auf verantwortlicher Kostenstelle" ausführlich beschrieben. Hier werden auch innerhalb der berechneten Felder passende Formeln verwendet.