Webseite: Excel-Makros · Speichern der Excel-Tabelle 36. Beschreibung Teil 2 - Erstellen der...

VHS Vaterstetten. Wendelsteinstraße 10, 85591 Vaterstetten Hubert Schierl, Tel 08106-359020, Fax 08106-359016, Email [email protected]

Webseite: www.statistik-tool.de

Excel-Makros

zur Auswertung

von Win/SQLBasys-Datenbanken

der KUFER Software Konzeption Mühldorf Version 21

http://www.statistik-tool.de/

Kapitel Überblick

Inhaltsverzeichnis:

ÜBERBLICK 5

Einsatz-Voraussetzungen 5 Kurzbeschreibung 5 Beispiel "Einfache Auswertung" 6 Beispiel "Vergleich" 6 Beispiel "Vergleich" als Diagramm 7 Hilfstabellen 8

KUNDENANPASSUNG 9

Individuelle Kufer-Export-Vorlagen bis SQLBasys Version 3.10 9 Individuelle Kufer-Export-Vorlage definieren 9 Individuelle Kufer-Export-Vorlage ausführen 11 Individuelle Kufer-ExportVorlage importieren/exportieren 13 Wichtiger Hinweis für den Wechsel von Export-Vorlagen 13

Individuelle Kufer-Export-Vorlagen ab SQLBasys Version 3.10 14 Individuelle Kufer-Export-Vorlage definieren 14 Individuelle Kufer-Export-Vorlage ausführen 14 Individuelle Kufer-ExportVorlage importieren/exportieren 17 Wichtiger Hinweis für den Wechsel von Export-Vorlagen 17

Dialog "Kundenanpassung" 18 Windows-Anmeldung 18 Starten des Dialogs 18 Kufer-Programme 19 DB Typ-Auswahl 19 Pfade und Dateinamen 19 Struktur Kursnummer KNR 19 Löschen überflüssiger Export-Spalten 20 Speichern der Kundenanpassung 20

Festlegen und Speichern Pfad für Init-Dateien 21

Erstellen Eigener Fachbereichs-Tabellen 22

Anpassungen Office 2002 und 2003 23 Makro-Sicherheit 23 Makro-Schaltflächen 23 Das Makro AKTUALISIEREN 24

Anpassungen Office 2007 27 Allgemeines 27 Makro-Sicherheit 27 Makro-Schaltflächen 29

Einstellungen der BDE-Verwaltung (Umlaute) 30

DIE "EINFACHE" AUSWERTUNG 31

Beschreibung Teil 1 - Erstellen der Kurstabelle 31 Vorbereitung 31

Rolf Borchert 2 24.06.2008

Kapitel Überblick

Start des Makros 31 Anpassung der Auswertung 32 Einlesen dbf-Datei und Bearbeitungsschritte 33 Speichern der Excel-Tabelle 36

Beschreibung Teil 2 - Erstellen der Pivot-Tabellen 37 Löschen "Leichen" 37 Erstellen Pivot-Tabellen 37 Aufbau der Pivot-Tabellen 38 Besondere Pivot-Tabellen 39 Abschließende Programmschritte 40

DIE "VERGLEICHENDE" AUSWERTUNG 42

Beschreibung Teil 1 - Erstellen der Kurstabelle 42 Vorbereitung 42 Start des Makros 42 Anpassung der Auswertung 43 Einlesen dbf-Datei und Bearbeitungsschritte 44 Speichern der Excel-Tabelle 47

Beschreibung Teil 2 - Erstellen der Pivot-Tabellen 48 Löschen "Leichen" 48 Erstellen Pivot-Tabellen 48 Aufbau der Pivot-Tabellen 49 Besondere Pivot-Tabellen 50 Abschließende Programmschritte 51 Balkendiagramme 53 Abschluß des Makros 54

BVV-KENNZAHLEN 55

Allgemeine Funktionsbeschreibung 55 Beschreibung der Kennzahlen 55 Beschreibung des Summenblatts 56 Beschreibung der Blätter nach HPM und BVV-ALLG 57

NICHT-KUFER-DATENBANKEN 58

Allgemeine Funktionsbeschreibung 58 Erweiterung der Kundenanpassung 58 Anpassung Anderer Datenbanken 59

FEHLERAUSWERTUNG 60

Allgemeine Funktionsbeschreibung 60 Starten der Fehlerauswertung 60 Durchführen der Fehlerauswertung 61

HPM TODO-LISTEN 63

Allgemeine Funktionsbeschreibung 63 Beschreibung der Filter 63 Ablauf des Programms 63

LISTGENERATOR 66

Allgemeine Funktionsbeschreibung 66


Kapitel Überblick

Erzeugen Tabellenblatt LISTGENERATOR 67 Grundsätzliches Arbeiten mit dem Tabellenblatt LISTGENERATOR 68 Erstellen eines Spezialfilters 68 Erstellen einer Ausgabeliste 69 Sonstige Makros für den Listgenerator 70

INFORMATION AN DIE PRESSE 72

Allgemeine Funktionsbeschreibung 72 Die Definition des Filters für den Listgenerator 73 Die Verarbeitung 74 Bemerkung zu Excel 2007 76


Kapitel Überblick

Überblick Einsatz-Voraussetzungen - SQLBasys ab Version 2 bzw. WINBasys Version 2.4 oder andere Datenbank-Software, die den DBF-Export von Kursdaten ermöglicht. - Excel Version 2002, 2003, 2007 - Ab Version 17 sind auch Office 2007 und Vista unterstützt - allerdings läuft die ausgelieferte Programmversion im Excel 2003 Modus und wird unter Excel 2007 im Kompatibilitätsmodus ausgeführt. Mit Speichern Unter kann auf das aktuelle 2007-Format XLSM umgestellt werden. - Ab Version 20 werden die Auswertungen unter Office 2007 automatisch im Format XLSM erstellt. Hierbei wird die Funktion " Application.Version" verwendet.

Kurzbeschreibung In der Excel-Datei

KUFERAUSW.XLS

sind Makros enthalten, die ein oder mehrere KUFER-Exportdateien für Lehrgänge im DBF-Format einlesen und in Pivot-Tabellen/Diagrammen auswerten. Dabei unterscheiden sich die Auswertungen danach, ob nur eine DBF-Datei eingelesen wird oder mehrere. Im ersten Fall werden die Pivotzahlen pro Fachbereich nach HPM aufgeschlüsselt ("Einfache Auswer-tung"). Werden dagegen mehrere DBF-Dateien zur Auswertung ausgewählt, werden die Pivot-Tabellen "3-dimensional": Pro Fachbereich erfolgt ein Vergleich über die Inhalte der DBF-Dateien (z.B. verschie-dene Semester, verschiedene VHSen usw.), in der 3.Dimension kann dann ein oder auch alle HPM ausgewählt werden ("Vergleich"). Für die Auswertung der Kursnummern nach Fachbereichen kann für die Zeilen der Pivot-Tabellen gewählt werden zwischen

• BVV-Schlüsseln (entsprechende Struktur ist beigefügt) • Individuellen Schlüsseln (Struktur muss individuell erstellt werden, siehe "Erstellen Eigener

Fachbereichs-Tabellen" auf Seite 22). Neben dieser Grundfunktion werden noch folgende Auswertungen zur Verfügung gestellt: 1. Der Bayerische Volkshochschulverband e.V. (bvv) hat eine Liste sog. "bvv-Kennzahlen" für das

"Benchmarking" Bayerische vhs'en entwickelt. Diese Kennzahlen werden - soweit aus dem Zahlenmaterial möglich - ermittelt, und zwar aufge-schlüsselt nach HPM und bvv-Fachbereichs-Schlüsseln. Zudem ist ein Summenblatt eingefügt, in dem noch weitere Angaben eingetragen werden können.

2. Es ist auch ein Schnittstellenprogramm integriert, mit dem nicht-Kufer-Datenbanken auf das Kufer-Format umgesetzt werden können. Dieses Schnittstellenprogramm beinhaltet die Konfigu-ration von - Umcodierung von Umlauten und anderen deutschen Sonderzeichen - Umbenennung von Spaltenköpfen (Feldnamen).


Kapitel Überblick

3. Erstellen eines Fehlerprotokolls für die ausgewählte Datenbank (DBF-Datei). Geprüft werden fehlende Einträge und Unplausibilitäten.

4. sog. ToDo-Listen für den HPM (zu bearbeitende Kurse, Presse usw.).

5. Ein Listgenerator, der die Definition von Spezialfiltern für die Kurs-Datenbank ermöglicht. Eine Verwaltung für derartige Filter (Speichern, Rückspeichern, Löschen, Formatieren, Drucken) ist ebenfalls integriert.

Diese Dokumentation beschreibt den derzeitigen Stand im Detail.

Beispiel "Einfache Auswertung"

Beispiel "Vergleich"


Kapitel Überblick

Darüberhinaus werden die Pivot-Tabellen der Auswertungen "Vergleich" werden auch in Diagramm-Form dargestellt:

Beispiel "Vergleich" als Diagramm


Kapitel Überblick

Hilfstabellen Die Auswertungen erfolgen nach sog. "Fachbereichen". Jeder Kursnummer wird über Hilfstabellen eine Fachbereichs-Bezeichnung zugewiesen (z.B. "Musik", "Politik", "Englisch"), nach diesen Texten sind dann die Pivot-Zeilen geordnet. Dabei gibt es 2 Hierarchie-Ebenen, die in Tabellen der Excel-Datei KUFERAUSW.XLS beschrieben werden müssen: ALLG Tabelle Allgemein ("Hauptkategorien") SPEZ Tabelle Speziell ("Unterkategorien") Außerdem sind 2 Auswertungstypen unterstützt: EIGENE VHS-spezifische FB-Tabellen zur Interpretation der Kursnummer BVV BVV-Standard-Tabellen zur Interpretation der BVV-Schlüssel, die beim Anlegen des Kurses festgelegt wurden. Derzeit sind als Muster die Organisationen der VHS Vaterstetten sowie die BVV-Schlüssel integriert.


Kapitel Kundenanpassung

Kundenanpassung

Individuelle Kufer-Export-Vorlagen bis SQLBasys Version 3.10

Individuelle Kufer-Export-Vorlage definieren Der Standard-Kufer-Export enthält sehr viele Felder (d.h. die Export-Dateien sind relativ groß) und nicht alle wünschenswerten Felder sind enthalten. Insbesondere WinBasys exportiert so viele Spalten, dass die Kapazität von Excel in diesem Makro gesprengt wird. Eine eigene Definition ist unbedingt angeraten. Das Erstellen einer Export-Vorlage ist in WinBasys und SQLBasys gleich und im Folgenden be-schrieben: SQLBasys oder WinBasys starten / anmelden / EINSTELLUNGEN / DEFINITION DER EXPORTVORLAGEN / KURSE / NEU Name = "Makro Exportvorlage" Ordner und Dateiname angeben, in den später exportiert werden soll, z.B. F:\STATISTI\KUFERAUSWERTUNGEN\KE_JJJJMM Diese Name wird später beim Export durch ".dbf" ergänzt und kann nachgebessert werden. Zur Erleichterung wird durch die Ordnerangabe gleich der "richtige" Spei- cherort ausgewählt. HINZUFÜGEN Feldgruppe auswählen KURSGEBÜHREN KURSDATEN ALLGEMEINE DATEN STATISTIK-DATEN weitere … aus den angezeigten Feldnamen durch Mehrfach-Selektion auswählen und mit OK hinzufügen Beispiel VHS Vaterstetten:

Lfd Feldbezeichnung Feldgruppe *HPM Fundamental für Pivot-Auswertungen 01 * KNR Kursdaten 02 * KURSTITEL Kursdaten 03 PROGTITEL Kursdaten

04 * BEGINNDAT * BEGINN_DAT

Kursdaten SQLBasys Kursdaten WINBasys

05 ENDE_DAT Kursdaten 06 BEGINN_TAG Kursdaten 07 ENDE_TAG Kursdaten 08 * UHRVON Kursdaten darf fehlen !!! 09 * UHRBIS Kursdaten darf fehlen !!! 10 * DAUER Kursdaten darf fehlen !!! 11 * STUNDENANZ Kursdaten darf fehlen !!! 12 * MINTN Kursdaten darf fehlen !!! 13 * MAXTN Kursdaten darf fehlen !!! 14 SEMESTER Kursdaten



15 FACHB Kursdaten 16 * FACHB_DVV Kursdaten 17 FACHB_LAND Kursdaten 18 * KURSORT Kursdaten 19 RAUMKURZ Kursdaten 20 RAUMPLZORT Kursdaten 21 AUSSENST Kursdaten 22 VERANSTART Kursdaten 23 ZIELG_KEY Kursdaten 24 VART_TXT Kursdaten 25 HPM Kursdaten 26 * ANZ_ANM Kursdaten darf fehlen !!! 27 * ANZ_FREI Kursdaten nur in SQLBasys , darf fehlen! 28 * ANZ_WL Kursdaten darf fehlen !!! 29 DOZ_ANREDE Kursdaten nur in SQLBasys !!! 30 DOZ_NAME Kursdaten 31 DOZ_VORN Kursdaten 32 DOZ_TEL Kursdaten 33 DOZ_EMAIL Kursdaten erst ab Version 2 !!! 34 * SUM_SPES Kursdaten 35 * SUM_HON Kursdaten 36 * SUM_FAHRTK Kursdaten 37 * HON_PRO_UE Kursdaten darf fehlen !!! 38 VORSCHUSS Kursdaten 39 HON_WIEOFT Kursdaten 40 KTO_HON Kursgebühren 41 KST_HON Kursgebühren 42 KTO_FAHRT Kursgebühren 43 KST_FAHRT Kursgebühren 44 KTO_SPESEN Kursgebühren 45 KST_SPESEN Kursgebühren 46 KURSGEB Kursgebühren 47 KURSERM Kursgebühren 48 DECKUNG Kursgebühren 49 DECKUNG2 Kursgebühren 50 DECKUNG3 Kursgebühren 51 SUM_OFFEN Kursgebühren 52 * ERWART_GEB Kursgebühren 53 ERW_ANDERE Kursgebühren 54 ERW_GESAMT Kursgebühren 55 EINNAHMEN Kursgebühren 56 AKT_STAND Kursgebühren 57 KURSAUSGAB Kursgebühren 58 AUSZAHLUNG Kursgebühren 59 VERLAUF Kursdaten 60 VERLAUFTXT Kursdaten 61 KWAHL1 Kursdaten 62 KWAHL2 Kursdaten 63 KWAHL3 Kursdaten 64 KWAHL4 Kursdaten 65 KWAHL5 Kursdaten 66 KWAHL6 Kursdaten 67 KWAHL7 Kursdaten 68 KWAHL8 Kursdaten 69 KWAHL9 Kursdaten 70 KWAHL0 Kursdaten



71 FKZ1 Kursdaten 72 FKZ2 Kursdaten 73 FKZ3 Kursdaten 74 FKZ4 Kursdaten 75 FKZ5 Kursdaten 76 WARNUNG 77 INTNOTIZ 78 VERW_GES 79 VERW_UE 80 VERW_PAU 81 KSK

Die mit einem * gekennzeichneten Felder werden in dem Makro benutzt und müssen daher auf jeden Fall ausgewählt werden - sonst gibt es Ausführungsfehler !!! Diese kritischen Felder sind daher nochmals aufgelistet:

Feldbezeichnung * KNR * KURSTITEL * BEGINNDAT BERGINN_DAT * STUNDENANZ * MINTN * MAXTN * FACHB_DVV * KURSORT * ANZ_ANM * ANZ_WL * SUM_SPES * SUM_HON * SUM_FAHRTK * HON_PRO_UE * ERWART_GEB

Das Feld BEGINNDAT ist in WINBasys und SQLBasys leider unterschiedlich benannt und wird nur für die FEHLERAUSWERTUNG benutzt.

Individuelle Kufer-Export-Vorlage ausführen Durch einen Kufer-Export werden dbf-Dateien erzeugt, die dann von den Makros bearbeitet werden. Alle Exporte sollten in einen Standardordner erfolgen, der bereits während der Definition der Export-Vorlage festgelegt werden kann (siehe oben). Derselbe Ordner sollte dann auch in den Makros für die Ausgabedateien hinterlegt werden (Dialog "Kundenanpassung"). Der Export wird wie folgt gestartet:

Win/SQLBasys starten / anmelden / EXTRAS / EXPORTE VON DATEN / KURSDATEN Filter setzen, z.B. Kursnummer G100 - G9500 (VHS Vaterstetten) EXPORT Das Fenster mit den Exportvorlagen wird angezeigt. "Makro-Exportvorlage" auswählen OK



Speicherort und DBF-Name werden abgefragt, z.B. F:\Statisti\Kuferauswertungen\ KE_JJJJMM.dbf



Individuelle Kufer-ExportVorlage importieren/exportieren SQLBasys oder WinBasys starten / anmelden / EINSTELLUNGEN / DEFINITION DER EXPORTVORLAGEN / KURSE / IMPORTIEREN bzw. EXPORTIEREN Der Export erzeugt eine *.EVO Datei an einer Stelle Ihrer Wahl Der Import erlaubt die Auswahl einer *.EVO Datei Die oben beschriebene Exportvorlage ist als EVO-Datei für beide Programmversionen beige-fügt: Kursexport WinBasys.evo für WinBasys Statistik-Tool-sql.evo für SQLBasys

Wichtiger Hinweis für den Wechsel von Export-Vorlagen Der Wechsel der vhs-individuellen Export-Vorlage ist sehr kritisch, weil sich dadurch in der Regel die Spaltenorganisation verändert. Wenn dann später DBF-Dateien für eine "vergleichende Auswertung" ausgewählt werden, die mit verschiedenen Export-Vorlagen erstellt wurden, passen die Spalten nicht mehr zueinander und das Makro endet zwangsläufig mit einem Fehler. Dokumentieren Sie daher derartige Wechsel sorgfältig und machen sie sie auch in den Namen der Ausgabedateien des Makros deutlich, z.B. Dateiname_EVO1.XLS wobei EVO1 für die erste verwendete Export-Vorlage steht.



Individuelle Kufer-Export-Vorlagen ab SQLBasys Version 3.10

Individuelle Kufer-Export-Vorlage definieren Diese Mühe hat Herr Schierl von der VHS Vaterstetten bereits auf sich genommen und eine Vorlage im Vorgriff erstellt

STATISTIK-TOOL-SQL.EVO

Diese enthält bereits alle erforderlichen und gewünschten Felder und ermöglicht eine starke Vereinfa-chung der Exporte.

Individuelle Kufer-Export-Vorlage ausführen SQLBasys 3.10 starten / anmelden / EXTRAS / EXPORTE VON DATEN / KURSDATEN

Kursauswahl für das Semester…



Kursnummern-Einschränkung auf das jeweilige Semester. Der Zeitraum kann großzügiger gewählt werden…

Auf Export klicken…



Die Exportvorlage: „Statistik-Tool“ wählen…

Einen geeigneten Dateinamen wählen / verändern… Beispiel: SEMESTER_JahrMonatTag.dbf – Das Datum sollte das „ERSTELL-DATUM“ sein.

Und „OK“ klicken…

Die dbf-Datei wird erstellt ................



Nach einer Wartezeit erfolgt eine Bestätigung über die exportierten Datensätze.

Der 1.Schritt (Export von Kursdaten aus Kufer) ist getan! Die Export-Datei kann dann in dem Excel-Tool ausgewählt und verarbeitet werden. TIPP: Die Exporte sollten regelmäßig mind. 1x wöchentlich immer am gleichen Wochentag durchgeführt werden. In der Einschreibezeit (über ca. 2 Wochen hinweg täglich). WARUM? Kursdatenexporte sind „Momentaufnahmen“ zu einem bestimmten Zeitpunkt. Die Momentaufnahmen verschiedener Semester können verglichen werden und Tendenzen eindeutig abgelesen werden. Z.B. Wenn entsprechend die Exporte vorhanden sind: Herbstsemester 2005 / 2006 / 2007 exakt drei Wochen nach den jeweiligen Kursbeginnen verglei-chend auswerten.

Individuelle Kufer-ExportVorlage importieren/exportieren SQLBasys oder WinBasys starten / anmelden / EINSTELLUNGEN / DEFINITION DER EXPORTVORLAGEN / KURSE / IMPORTIEREN bzw. EXPORTIEREN Der Export erzeugt eine *.EVO Datei an einer Stelle Ihrer Wahl Der Import erlaubt die Auswahl einer *.EVO Datei Die oben beschriebene Exportvorlage ist als EVO-Datei für beide Programmversionen beige-fügt: Kursexport WinBasys.evo für WinBasys Statistik-Tool-sql.evo für SQLBasys

Wichtiger Hinweis für den Wechsel von Export-Vorlagen Der Wechsel der vhs-individuellen Export-Vorlage ist sehr kritisch, weil sich dadurch in der Regel die Spaltenorganisation verändert. Wenn dann später DBF-Dateien für eine "vergleichende Auswertung" ausgewählt werden, die mit verschiedenen Export-Vorlagen erstellt wurden, passen die Spalten nicht mehr zueinander und das Makro endet zwangsläufig mit einem Fehler.



Dialog "Kundenanpassung"

Windows-Anmeldung Sie müssen als Administrator angemeldet sein, damit die Kundenanpassung gespeichert werden kann (Zugriffsberechtigung auf C:\) !!!

Starten des Dialogs Starten KUFERAUSW.XLS / Tabellenblatt "Makro-Aufrufe" / Schaltfläche "Kundenanpassung"



Kufer-Programme Es gibt die Programmvarianten SQLBasys WINBasys Die Varianten benutzen zwar verschiedene Datenbank-Methoden, die Datenbank-Felder sind aber mit (fast) gleichen Namen in beiden Programmen enthalten, so dass im Makro (fast) keine programmier-ten Unterschiede erforderlich sind. DB Typ-Auswahl Neben Kufer-Datenbanken gibt es auch andere Datenbanken, die einen DBF-Export von Kursdaten ermöglichen. Um diese Dateien auch auswerten zu können, wurde eine Programm-Erweiterung hinzugefügt, die unter "Nicht-Kufer-Datenbanken" ab Seite 58 beschrieben ist. Wird die Option ANDERE DATENBANKEN gewählt, wird eine 2. Konfigurationsseite angezeigt. Pfade und Dateinamen

Ordner der Init-Dateien Derjenige Ordner, in dem die Daten dieser Konfigurationen abgespeichert werden sollen. Wird bei Makro-Start erstmalig festgelegt (siehe "Festlegen und Speichern Pfad für Init-Dateien" auf Seite 21) und kann hier verändert werden.

Ordner der dbf-Dateien Dieser Ordner ist deshalb wichtig, weil sein Inhalt an dbf-Dateien beim Start des Makros zur Auswahl der zu verarbei-tenden Dateien angezeigt wird. Die Pfadangabe sollte mit "/" abgeschlossen werden

Standard-Ausgabedatei Hier können Sie Pfad und Dateiname der Ergebnisdatei an geben (incl. Datei-Endung ".xls"). Aufgrund dieser Angabe wird später gleich der "richtige" Aus-gabeordner angezeigt - der angegebene Dateiname kann dann noch nachgebessert werden, z.B. um ein Tagesdatum. Dieser Standardname wird danach vom Makro noch "nachge-bessert um "_n" n = Anzahl ausgewählter dbf-Dateien "_BVV" bei Auswertung nach BVV

Struktur Kursnummer KNR Derzeit sind Kursnummern KNR bis zu 8 Stellen berücksichtigt. Von links beginnend können den einzelnen Ziffern der KNR über Listenfelder Attribute zugewiesen werden. Diese sind.:

Semestercode Meist 1-stellige Kennzeichnung des Semesters, meist alpha-numerisch. Kann auch mehrstellig sein, aber die Stellen müs-sen nebeneinander liegen. Mit dem Code arbeitet das Makro selber nicht, er wird nur in



einer eigenen Spalte SEM für eigene Auswertungen zur Verfü-gung gestellt.

VHS-Code Falls überhaupt vorhanden, meist 1-stellige Kennzeichnung der VHS in Verbänden, meist alphanumerisch. Kann auch mehrstellig sein, aber die Stellen müssen nebeneinander lie-gen. Mit dem Code arbeitet das Makro selber nicht, er wird nur in einer eigenen Spalte VHS für eigene Auswertungen zur Verfü-gung gestellt.

Kern-Kursnummer die eigentliche Kursnummer, meist 4 - 5 Stellen. Bei EIGENEN FB-TABELLEN wird unterstellt, dass die linken Positionen die Informationen für die FB-Zuordnung enthalten. Die Kern-Kursnummer wird als Ganzes in einer Excel-SVERWEIS-Funktion mit der linken Spalte in den FB-Tabellen verglichen. In der rechten Spalte steht dann der zugehörige Text des zutreffenden Fachbereichs.

Suffix Falls überhaupt vorhanden, meist 1-stellige Kennzeichnung der Variation eines Kurses, meist alphanumerisch. Kann auch mehrstellig sein, aber die Stellen müssen nebeneinander lie-gen. Mit dem Code arbeitet das Makro selber nicht, er wird nur in einer eigenen Spalte SUFFIX für eigene Auswertungen zur Verfügung gestellt.

n.b. nicht benutzt (die letzten Zeichen der max. 8 Stellen). leer das Feld wird vor dem Speichern gelöscht (alte Inhalte).

Löschen überflüssiger Export-Spalten Die DBF-Exportdateien dürfen wegen Excel-Beschränkungen maximal 240 Spalten (Feldnamen) ha-ben. Besonders der WinBasys-Standard-Export verletzt diese Forderung. Darum können Sie in der Konfiguration ein Kürzel angeben. Alle Spalten, deren Feldnamen mit die-sem Kürzel beginnen, werden dann vom Makro automatisch gelöscht.

Speichern der Kundenanpassung Die eingegebenen Werte werden nach der Bestätigung mit OK automatisch in einer Textdatei MAKROINIT.TXT gespeichert und bei Starten einer Auswertung in das Makro eingelesen. Der Ordner, in den gespei-chert wird, kann beliebig festgelegt werden - siehe nachfolgender Abschnitt "Festlegen und Speichern Pfad für Init-Dateien". Beim erneuten Starten der Konfiguration werden die vorhandenen Werte aus der Datei angezeigt. Ohne Konfiguration läuft das Makro nicht an!!! Bemerkung:

Wenn die Kundenanpassung nicht gespeichert wird, sind die Zugriffsrechte für das Schreiben nicht ausreichend. In diesem Fall melden Sie sich bitte als Administrator an.



Festlegen und Speichern Pfad für Init-Dateien Wie soeben erwähnt, werden die Konfigurationsangaben in Textdateien gespeichert und von dort für Modifikationen oder Auswertungen eingelesen. Es gibt 2 derartige Textdateien:

MakroInit.txt Inhalt des soeben beschriebenen Konfigurationsfensters mit Ausnahme des Textfeldes "Ordner der Init-Dateien". Diese Ordnerangabe muss separat verwaltet werden, damit auch vor dem erstmaligen Erstellen der MakroInit.txt geprüft werden kann, ob die Datei bereits existiert. Details hierzu folgen sofort.

DBInit.txt Diese Datei wird nur benötigt, wenn keine Kufer-Datenbanken benutzt werden (siehe "Nicht-Kufer-Datenbanken" ab Seite 58. Dann werden in dieser die Umkonfigurationen der Umlaute und Spaltenüberschriften des entsprechenden Konfigurationsfens-ters festgehalten.

Der Pfad, wo diese Testdateien abgelegt werden, ist frei wählbar. Da bereits beim ersten Programmstart geprüft werden muss, ob es die Konfigurationsdateien schon gibt oder ob sie neu angelegt werden müssen, wird bei allen Makro-Aufrufen geprüft, ob die Pfadan-gabe bereits festgelegt ist. Falls NEIN, wird diese Pfadangabe durch eine sog. Inputbox abgefragt:

Wo wird der Pfad für Init-Dateien gespeichert? Der eingegebene Pfad wird gespeichert in der Datei

C:\VHS\KuferInitPfad.txt Pfad und Name dieser Datei sind nur im Programmcode zu ändern. Bei jedem Programmstart wird auf die Existenz dieser Datei geprüft. Ist sie nicht vorhanden, wird sie angelegt und über die eben erwähnte Inputbox gefüllt. Bei jedem Makro-Start wird diese Datei gelesen und die Variable INITPFAD wird gesetzt. Damit weiß dann das Programm auch, wo obige Textdateien gespeichert sind bzw. werden sollen. Kann dieser Pfad für Init-Dateien noch geändert werden? Ja - über das Makro KONFIGURATION auf dem Registerblatt MAKRO-AUFRUFE (siehe oben).

Wichtig: Der ausführende Benutzer muss Schreibrechte in dem Init-Pfad haben !!!



Erstellen Eigener Fachbereichs-Tabellen Wenn neben BVV-Auswertungen auch Auswertungen der Eigenen Kursnummern erfolgen sollen, muss

1. die Struktur der Kursnummer in der Kundenanpassung konfiguriert werden (siehe "Struktur Kursnummer KNR" auf Seite 19.

2. eine Beschreibung der Fachbereichsbezeichnungen erfolgen. Dabei ist zwingend, dass die Kursnummer links die Fachbereichsschlüssel enthält !!! Diese Schlüssel müssen in 2 Detail-Stufen beschrieben werden: ALLG (Allgemein) auf dem Tabellenblatt "MatrixFB-ALLG" SPEZ (Speziell) auf dem Tabellenblatt "MatrixFB-SPEZ" Nach diesen Tabellen werden über SVERWEIS-Funktionen die Pivotzeilen erstellt.

Beispiel der VHS Vaterstetten für Beschreibung der Fachbereiche:



Anpassungen Office 2002 und 2003

Makro-Sicherheit Bemerkung:

Natürlich muss Excel so eingestellt sein, dass Makros ausgeführt werden können: EXTRAS/MAKRO/SICHERHEIT/ MITTEL. Dann erscheint beim Starten der Tabelle und manchmal bei Makros das folgende Fenster:

MAKROS AKTIVIEREN.

Makro-Schaltflächen Zum Einrichten von Makro-Aufrufen in eine Symbolleiste muss das Fenster ANPASSEN geöffnet sein. ANSICHT/SYMBOLLEISTEN/ANPASSEN

BEFEHLE/MAKROS BENUTZERDEFINIERTE SCHALTFLÄCHE mit der linken Maustaste an eine beliebige Position in ei-ner beliebigen Symbolleiste ziehen und dort fallenlassen (Einfügebalken !)



Mit rechter Maustaste das Kontextmenü der neuen Schalfläche aufrufen und diese nach Be-lieben konfigurieren - vor allem aber MAKRO ZUWEISEN, wichtigstes Beispiel folgt.

ANPASSEN-Fenster SCHLIESSEN - erst dann wird die neue Schaltfläche wirksam !!!

Das Makro AKTUALISIEREN Bei den Auswertungen werden in die -Tabellen ALLG KURSE und SPEZ KURSE nachträglich die AUSGEFALLENEN KURSE in die Pivot-Tabellen eingetragen. Anschließend werden die Pivottabellen "per Hand" um prozentuale Angaben ergänzt (wie viel Prozent der Kurse sind jeweils ausgefallen). Diese Ergänzung bezieht sich nach der Erstellung der Auswertungstabellen immer auf die Original-Pivottabelle. Wird diese z.B. durch Auswahl in einem der Pivot-Listenfeldern (HPM, DATEINAME usw.) verändert, so ändern sich die prozentualen Auswertungen nicht mit. Beispiel:



Deshalb wird ein Makro mit dem Namen AKTUALISIEREN mitgeliefert, welches das aktive Tabellenblatt nach derartigen Änderungen aktualisiert, in unserem Beispiel zu:

Es kann mit dem eben beschriebenen Verfahren vereinfacht über ein Symbol aufgerufen werden. Bemerkung:

Excel stellt zwar bei derartigen Änderungen einer Pivottabelle grundsätzlich sog. "Ereignisse" zur Verfügung (vor allem CHANGE), an die das Makro AKTUALISIEREN angekoppelt werden könnte, diese setzen allerdings das Pivot-Tabellenblatt voraus. Da die Tabellenblätter wegen der großen Anzahl aber erst durch die Makros erzeugt werden, ist dieser Weg leider nicht gangbar (sehr aufwendig).

Bemerkung:



Wenn eine durch derartige Veränderungen verkleinerte Pivottabelle durch erneute Verände-rungen wieder vergrößert wird, fragt Excel, ob die Zielzellen überschrieben werden sollen.

OK. Bemerkung:

Wenn durch das Überschreiben der Zielzellen auch die Überschrift "Ausfall in %" überschrie-ben wurde, erzeugt eine Fehlerprüfung das folgende Fenster:

OK.



Anpassungen Office 2007

Allgemeines Excel 2007 bringt - wie alle Komponenten von Office 2007 - eine Reihe von Neuerungen. Die Wich-tigsten sind: • Multifunktionsleiste statt der bisherigen vielfachen SymbollLeisten, deren Funktionen

alle integriert sind, wenn auch ungewohnt zu finden.

• Symbolleiste für den Schnellzugriff Dies ist neben der Multifunktionsleiste die einzige Symbolleiste, normalerweise oben am Fensterrand untergebracht. In dieser Leiste kann man Symbole für schnelles Anklicken unterbrin- gen - so auch Symbole für Makros (siehe unten).

• Dateiformat Leider hat sich auch dieses gegenüber Office 2003 verändert, so dass mit Excel 2007 erstellte Dateien nur mit Hilfe eines Zusatztools von Microsoft Der Vorteil: das neue Format basiert auf dem offenen OpenXML ist damit nicht mehr MS-proprietär. Um diese Dateien (*.XLSX, *.XLSM usw. mit den älteren Office- Versionen öffnen zu können, braucht man das "Microsoft Office Com patibility Pack für Dateiformate von Word, Excel und PowerPoint 2007", das MS kostenlos bereitstellt. (siehe http://www.microsoft.com/ downloads/details.aspx?displaylang=de&familyid= 941B3470-3AE9-4AEE-8F43-C6BB74CD1466).

Makro-Sicherheit

1. Sichtbar machen der VB-Umgebung: Schaltfläche OFFICE / EXCEL-OPTIONEN / HÄUFIG VERWENDET /

ENTWICKLERREGISTERKARTE IN DER MULTIFUNKTIONSLEISTE ANZEIGEN.



2. Übersicht Makro-Sicherheit: Schaltfläche OFFICE / EXCEL-OPTIONEN / VERTRAUENSSTELLUNGSCENTER/ EINSTELLUNGEN FÜR DAS VERTRAUENSSTELLUNGSCENTER / EINSTELLUNGEN FÜR MAKROS oder Registerkarte ENTWICKLERTOOLS / Gruppe CODE / MAKROSICHERHEIT dann

ALLE MAKROS AUSSER DIGITAL SIGNIERTEN MAKROS DEAKTIVIEREN

3. Erstellen eines Digitalen Zertifikats: entweder von einer kommerziellen Zertifizierungsstelle (CA = Certificate Authority) oder selber mit SELFCERT.EXE: START / PROGRAMME / MICROSOFT OFFICE / MICROSOFT OFFICE TOOLS / DIGITALES ZERTIFIKAT FÜR VBA-PROJEKTE Zertifikatsname robo (Beispiel)

INTERNET EXPLORER / EXTRAS / INTERNETOPTIONEN / INHALT / ZERTIFIKATE / PERSÖNLICH robo eintragen.

4. Digitales Signieren einer Excel-Datei (Beispiel KUFERAUSW.XLSM): Öffnen KUFERAUSW.XLSM / Registerkarte ENTWICKLERTOOLS / Gruppe CODE / VISUAL BASIC EDITOR / Links im Explorer KUFERAUSW.XLSM markieren / EXTRAS / DIGITALE SIGNATUR / WÄHLEN robo auswählen



5. Das Projekt ist jetzt signiert. Allerdings wird diese Signatur nach jeder Änderung entfernt, aber automatisch neu signiert - sofern das digitale Zertifikat auf demselben PC gespeichert ist.

6. Datei Speichern

7. Datei wieder Öffnen: Sofern ein Fenster erscheint „Die Signatur ist gültig, aber stammt von einem Herausgeber, dem Sie noch nicht vertrauen“,

GESAMTEM INHALT DES HERAUSGEBERS VERTRAUEN. Die Makros werden jetzt ausgeführt.

Makro-Schaltflächen Zusätzliche Makro-Schaltflächen können wohl nur in der SYMBOLLEISTE FÜR DEN SCHNELLZUGRIFF ein-gefügt werden: Rechte Maustaste auf die SYMBOLLEISTE FÜR DEN SCHNELLZUGRIFF / ANPASSEN /

BEFEHL AUSWÄHLEN MAKROS MAKRO MARKIEREN / HINZUFÜGEN RECHTS AUSWÄHLEN / ÄNDERN ANZEIGENAMEN SYMBOL Erst einmal eingerichtet, sind diese Symbole immer verfügbar. Beispielhaft sine Symbole für die Makros angezeigt, die bei dem Listgenerator Verwendung finden: Starten Spezialfilter Löschen Kriterien Löschen Listausgabe Drucken Listausgabe Speichern Filter Rückspeichern Filter Formatieren Listausgabe Optimale Breite Listausgabe



Einstellungen der BDE-Verwaltung (Umlaute) Damit in den Kufer-Exporten die deutschen Umlaute richtig angezeigt werden, muss die BDE-Verwaltung richtig konfiguriert sein.

SYSTEMSTEUERUNG/BDE-VERWALTUNG/ öffnet C:\PROGRAMME\COMMON FILES\BORLAND SHARED\BDE\IDAPI32.CFG Hierin ist einzustellen: KONFIGURATION/KONFIGURATION/TREIBER/NATIVE/DBASE/ TYPE = FILE LANGDRIVER = DBASE DEU CP850 LEVEL = 5 MDX BLOCK SIZE = 1024

KONFIGURATION/KONFIGURATION/SYSTEM/INIT/ DEFAULTDRIVER = DBASE LANGDRIVER = DBASE DEU CP850 LOW MEMORY USAGE LIMIT = 32 MAXBUFSIZE = 8192 MAXFILEHANDLES = 150 MEMSIZE = 32 MINBUFSIZE = 128 MTS POOLING = FALSE SHAREDMEMSIZE = 8192


Kapitel Die "einfache" Auswertung

Die "einfache" Auswertung

Beschreibung Teil 1 - Erstellen der Kurstabelle

Vorbereitung

Mit SQLBASYS wird die komplette Kursdatei incl. aller Datenfelder exportiert. Das Vorgehen ist unter "Individuelle Kufer-Export-Vorlage ausführen " auf Seite 11 beschrieben. Das Ergebnis ist eine dBase-Datei *.dbf. Alle dbf-Dateien müssen im "Ordner der dbf-Dateien" abgelegt werden, wie er bei der Kundenanpassung (Seite 19 ) festgelegt wurde.

Start des Makros

Das Excel-Makro ist Teil der Exceldatei KUFERAUSW.XLS die auf dem Registerblatt MAKRO-AUFRUFE auch eine Schaltfläche zum Starten des Makros enthält ("Erstellen Auswertung").



Anpassung der Auswertung

Die im "Ordner der dbf-Dateien" enthaltenen dbf-Dateien werden in dem Fenster ANPASSUNG DER AUSWERTUNG angezeigt: Dort wird eine einzige Datei durch Anklicken ausgewählt. In demselben Fenster wird noch der gewünschte Auswertungstyp festgelegt mit Eigenen Fachbereichstabellen mit BVV-Tabellen



Einlesen dbf-Datei und Bearbeitungsschritte

Danach liest das Makro die ausgewählte Datei in die Excel-Tabelle KURS-ÜBERSICHT ein, die vorsorglich vorher gelöscht wird. Anschließend führt das Makro folgende Arbeitsschritte in dieser Tabelle aus: Löscht vorsorglich den Inhalt des Tabellenblatts KURS-ÜBERSICHT. Liest in dasselbe Tabellenblatt die Quelldatei ein. Formatiert die Überschriften-Zeile 1 und fixiert sie. Fügt 4 Detailspalten vor der Spalte "KNR" ein (SEM, VHS, KURS, INDEX) und füllt diese entsprechend der Kundenanpassung aus KNR (Kursnummer). Alle Spalten mit Breite > 40 werden auf Breite 40 reduziert. Fügt Zusatzspalte "AUSFALL" für ausgefallene Kurse ein (1 = Ausfall) Setzt für ausgefallene Kurse die Felder - sofern vorhanden - "ANZ_ANM" "ANZ_WL" "MINTN" "MAXTN" "HON_PRO_UE" "SUM_HON" "SUM_FAHRTK" "SUM_SPES" "STUNDENANZ" "ANZ_FREI" auf Null Fügt 3 Spalten "JAHR", "QUARTAL" und "MONAT" für das Beginn-Datum hinter "BEGINNDAT" (SQLBasys) resp. ""BEGINN_DAT" (WINBasys) ein und füllt sie mit Hilfe von Zeit-Funktionen bzw. manueller Rechnung. Danach Umwandlung in Feste Werte. Prüft die Felder UHRVON und UHRBIS auf Zeitformat "hh:mm" und korrigiert ggfs. Unkorrigierbare Einträge werden auf "00:00" gesetzt. Die Anzeige im Feld DAUER erfolgt in der Form "nn x". Der Wiederholungsfaktor " x" wird gelöscht, damit mit den Einträgen gerechnet werden kann. Fügt 2 Spalten "FB-ALLG" und "FB-SPEZ" vor "AUSFALL" ein und füllt sie mit Hilfe einer SVERWEIS-Funktion sowie den Tabellenblättern "MatrixFB-ALLG" "MatrixFB-SPEZ" mit Fachbereichs-Texten. Sind die zugrundeliegenden Kursnummern nicht rein numerisch (siehe z.B. VHS Taufkirchen), werden Alpha-Zeichen durch Null ersetzt. Außerdem werden aus Performance-Gründen die SVERWEIS-Funktionen in Feste Werte umgewandelt. Löscht in der Spalte "FACHB_DVV" den ".", sofern vorhanden (Spalteninhalte müssen numerisch sein). Fügt 2 Spalten "BVV ALLG" und "BVV SPEZ" vor "FACHB_DVV" ein und füllt sie mit Hilfe einer SVERWEIS-Funktion sowie den VorausgefülltenTabellenblättern "BVV-ALLG" "BVV-SPEZ"



mit BVV-Texten. Sind die zugrundeliegenden bvv-Codes nicht rein numerisch (siehe z.B. VHS Taufkirchen), werden Alpha-Zeichen durch Null ersetzt. Außerdem werden aus Performance-Gründen die SVERWEIS-Funktionen in Feste Werte umgewandelt. Schließlich Stellt dann alle Spalten auf Optimale Breite

Fügt eine Spalte "STUNDENANZ * ANZ_ANM" vor "KURSORT" ein (STUNDENANZ = UE = Unterrichtseinheiten für Statistiken), füllt diese mit Formeln und wandelt sie aus Performancegründen in Festwerte um.



Fügt eine Spalte "SUM_AUSG" hinter "SUM_FAHRTK" ein, als Summe der ausgaben-bezogenen Spalten "SUM_SPES" "SUM_HON" "SUM_FAHRTK" "KURSAUSGAB" und füllt diese, weiterhin eine Spalte "DECK_BEITRAG" als Differenz von Einnahmen und Ausgaben: "ERW_GESAMT" minus "SUM_AUSG" Dabei ist ERW_GESAMT = ERWART_GEB + ERW_ANDERE

Schließlich bekommt die Tabelle KURSÜBERSICHT standardmäßig einen "Autofilter", d.h. in jeder Spalte kann über ein Listenfeld auf einen Wert in dieser Spalte gefiltert werden:



Speichern der Excel-Tabelle Abschließend wird den Name der Ausgabedatei abgefragt. Dabei wird die Angabe der "Kundenanpassung" verwendet (Pfad und Dateiname), die Anga-ben können hier allerdings auch nachgebessert werden (z.B. um ein Tagesdatum). Bei konfigurierter BVV-Auswertung wird der Dateiname automatisch um den Anhang "_BVV" erweitert. Also wird z.B. aus KA2006.XLS KA2006_BVV.XLS.

Die Datei wird gespeichert.



Beschreibung Teil 2 - Erstellen der Pivot-Tabellen

Löschen "Leichen"

Löscht evtl. vorhandene Pivottabellen-Blätter, erkennbar an Namenbeginn mit "ALLG *" "SPEZ *" Löschen muss pro Tabellenblatt 2 x bestätigt werden:

Erstellen Pivot-Tabellen

Es werden folgende 17 Tabellen erstellt:

Beschriftung Inhalt "ALLG Anm." Grobe Struktur der Fachbereiche - Summe Anmeldungen ANZ_ANM "ALLG Kurse" Grobe Struktur der Fachbereiche - Anzahl Kurse ANZ_ANM + AUSFALL "ALLG Einn.." Grobe Struktur der Fachbereiche - Summe Gebühren ERW_GESAMT "ALLG UE" Grobe Struktur der Fachbereiche - Unterrichtseinheiten STUNDENANZ "ALLG UE x TN" Grobe Struktur der Fachbereiche - UE * Anz. Anmeldungen

STD*ANZ_ANM "ALLG Ausg." Grobe Struktur der Fachbereiche - Summe der Ausgaben Sum_Ausg "ALLG DB" Grobe Struktur der Fachbereiche - Deckungsbeitrag (Einnahmen-

Ausgaben) Deck_Beitrag "SPEZ Anm." Detail Struktur der Fachbereiche - Summe Anmeldungen ANZ_ANM "SPEZ Kurse" Detail Struktur der Fachbereiche - Anzahl Kurse ANZ_ANM + AUSFALL "SPEZ Einn." Detail Struktur der Fachbereiche - Summe Gebühren ERW_GESAMT "SPEZ UE" Detail Struktur der Fachbereiche - Unterrichtseinheiten STUNDENANZ "SPEZ UE x TN" Detail Struktur der Fachbereiche - UE x Anz. Anmeldungen

STD*ANZ_ANM "SPEZ Ausg." Detail Struktur der Fachbereiche - Summe der Ausgaben Sum_Ausg "SPEZ DB" Detail Struktur der Fachbereiche - Deckungsbeitrag (Einnahmen-

Ausgaben) Deck_Beitrag "Quartale Einn." Monats-Einnahmen nach Quartalen aufgefächert, Jahr einstellbar. "Quartale Ausg." Monats-Ausgaben nach Quartalen aufgefächert, Jahr einstellbar. "Quartale DB" Differenz (Deckungs-Beitrag) nach Quartalen aufgefächert, Jahr einstell-

bar. Die Quartals-Tabellen werden immer am Ende der Mappe eingefügt



Aufbau der Pivot-Tabellen

Alle erstellten Tabellen haben zunächst als Zeilenfeld die Fachbereiche oder BVV-Bereiche (ALLG bzw. SPEZ) als Spaltenfeld die Päd.Mitarbeiter (HPM)

Die Pivot-Zeilen werden entsprechend dem gewählten Auswertungstyp erstellt: EIGEN oder BVV

Beispiel einer Standard-Pivot-Tabelle:



Besondere Pivot-Tabellen Für die "Kurse"-Tabellen wird in die Daten noch die Anzahl der ausgefallenen Kurse in abso-luten Zahlen integriert. Zusätzlich werden per Programm noch die Prozentwerte ausgefallener Kurse berechnet, - und zwar für die Gesamtergebnisse in Zeilen und Spalten.



Für die "DB-Tabellen" (Deckungsbeitrag) wird eine 2-fache Nachbereitung vorgenommen:

1. Zunächst werden in den Zeilen der Pivot-Tabelle per Programm eingefügt: Einnahmen Ausgaben Differenz Einnahmen - Ausgaben Quotient Einnahmen / Ausgaben in % abzüglich 100% (als Maß für die "Güte" des Deckungsbeitrags)

2. Danach wird die so erweiterte Tabelle nach unten verschoben und am alten Platz eine neue Tabelle erstellt, die alle Zellinhalte der alten Tabelle ersetzt durch Quotient Einnahmen / Ausgaben in % abzüglich 100% (als Maß für die "Güte" des Deckungsbeitrags) um die Aussagekraft der "Güte" des Deckungsbeitrages noch weiter herunterzubre-chen.

Abschließende Programmschritte • Schließlich wird noch die Druckausgabe aller Tabellen auf Querformat, zentriert

mit Kopf/Fußzeilen umgestellt. • Die bvv-Tabellenblätter:

BVV-KENNZAHLEN HPM und BVV-KENNZAHLEN BVV-ALLG werden gefüllt. Details siehe "bvv-Kennzahlen" ab Seite 55 .

• Es werden noch 3 weitere Tabellenblätter am Ende der Arbeitsmappe (Tabfarbe rosa) zum Thema Finanzen erstellt. Ziel hier ist, bereits vorliegende Ergebnisse auf Jahr und Quartal herunterzubre-



chen. Folgende Finanz-Kriterien sind vorgesehen: EINNAHMEN AUSGABEN DECKUNGSBEITRAG Beispiel Deckungsbeitrag:

• Die Datei wird nochmals gespeichert.

Danach kann sie noch verändert werden, z.B. durch zusätzliche individuelle Aus-wertungen.


Kapitel Die "Vergleichende" Auswertung

Die "VERGLEICHENDE" AUSWERTUNG

Beschreibung Teil 1 - Erstellen der Kurstabelle

Vorbereitung

Mit SQLBASYS wird die komplette Kursdatei incl. aller Datenfelder exportiert. Das Vorgehen ist unter "Individuelle Kufer-Export-Vorlage ausführen " auf Seite 11 beschrieben. Das Ergebnis ist eine dBase-Datei *.dbf. Alle dbf-Dateien müssen im "Ordner der dbf-Dateien" abgelegt werden, wie er bei der Kundenanpassung (Seite 19 ) festgelegt wurde. Es können beliebig viele derartige dbf-Dateien erstellt werden.

Start des Makros

Das Excel-Makro ist Teil der Exceldatei KUFERAUSW.XLS die auf dem Registerblatt MAKRO-AUFRUFE auch eine Schaltfläche zum Starten des Makros enthält ("Erstellen Auswertung").



Anpassung der Auswertung

Die im "Ordner der dbf-Dateien" enthaltenen dbf-Dateien werden in dem Fenster ANPASSUNG DER AUSWERTUNG angezeigt: Dort werden mehrere Datei durch Anklicken ausgewählt (Tasten Steuerung und Shift). In demselben Fenster wird noch der gewünschte Auswertungstyp festgelegt mit Eigenen Fachbereichstabellen mit BVV-Tabellen



Einlesen dbf-Datei und Bearbeitungsschritte

Danach liest das Makro die ausgewählten Dateien in die Excel-Tabelle KURS-ÜBERSICHT ein, die vorsorglich vorher gelöscht wird. Dabei wird in einer Spalte "DATEINAME" ganz links der Name der jeweiligen Datei festgehal-ten, um einen späteren Vergleich der Datei-Inhalte zu ermöglichen. Anschließend führt das Makro folgende Arbeitsschritte in dieser Tabelle aus: Formatiert die Überschriften-Zeile 1 und fixiert sie. Fügt 4 Detailspalten vor der Spalte "KNR" ein (SEM, VHS, KURS, INDEX) und füllt diese entsprechend der Kundenanpassung aus KNR (Kursnummer). Alle Spalten mit Breite > 40 werden auf Breite 40 reduziert. Fügt Zusatzspalte "AUSFALL" für ausgefallene Kurse ein (1 = Ausfall) Setzt für ausgefallene Kurse die Felder - sofern vorhanden - "ANZ_ANM" "ANZ_WL" "MINTN" "MAXTN" "HON_PRO_UE" "SUM_HON" "SUM_FAHRTK" "SUM_SPES" "STUNDENANZ" "ANZ_FREI" auf Null Fügt 3 Spalten "JAHR", "QUARTAL" und "MONAT" für das Beginn-Datum hinter "BEGINNDAT" (SQLBasys) resp. ""BEGINN_DAT" (WINBasys) ein und füllt sie mit Hilfe von Zeit-Funktionen bzw. manueller Rechnung. Danach Umwandlung in Feste Werte. Fügt 2 Spalten "FB-ALLG" und "FB-SPEZ" vor "AUSFALL" ein und füllt sie mit Hilfe einer SVERWEIS-Funktion sowie den Tabellenblättern "MatrixFB-ALLG" "MatrixFB-SPEZ" mit Fachbereichs-Texten. Sind die zugrundeliegenden Kursnummern nicht rein numerisch (siehe z.B. VHS Taufkirchen), werden Alpha-Zeichen durch Null ersetzt. Außerdem werden aus Performance-Gründen die SVERWEIS-Funktionen in Feste Werte umgewandelt. Löscht in der Spalte "FACHB_DVV" den ".", sofern vorhanden (Spalteninhalte müssen numerisch sein). Fügt 2 Spalten "BVV ALLG" und "BVV SPEZ" vor "FACHB_DVV" ein und füllt sie mit Hilfe einer SVERWEIS-Funktion sowie den VorausgefülltenTabellenblättern "BVV-ALLG" "BVV-SPEZ" mit BVV-Texten. Sind die zugrundeliegenden bvv-Codes nicht rein numerisch (siehe z.B. VHS Taufkirchen), werden Alpha-Zeichen durch Null ersetzt. Außerdem werden aus Performance-Gründen die SVERWEIS-Funktionen in Feste Werte umgewandelt. Schließlich



Stellt dann alle Spalten auf Optimale Breite

Fügt eine Spalte "STUNDENANZ * ANZ_ANM" vor "KURSORT" ein (STUNDENANZ = UE = Unterrichtseinheiten für Statistiken), füllt diese mit Formeln und wandelt sie aus Performancegründen in Festwerte um.

Fügt eine Spalte "SUM_AUSG" hinter "SUM_FAHRTK" ein, als Summe der ausgaben- bezogenen Spalten "SUM_SPES" "SUM_HON"



"SUM_FAHRTK" "KURSAUSGAB" und füllt diese. weiterhin eine Spalte "DECK_BEITRAG" als Differenz von Einnahmen und Ausgaben: "ERW_GESAMT" minus "SUM_AUSG" Dabei ist ERW_GESAMT = ERWART_GEB + ERW_ANDERE

Schließlich bekommt die Tabelle KURSÜBERSICHT standardmäßig einen "Autofilter", d.h. in jeder Spalte kann über ein Listenfeld auf einen Wert in dieser Spalte gefiltert werden:



Speichern der Excel-Tabelle Abschließend wird den Name der Ausgabedatei abgefragt. Dabei wird die Angabe der "Kundenanpassung" verwendet (Pfad und Dateiname), die Anga-ben können hier allerdings auch nachgebessert werden (z.B. um ein Tagesdatum). Bei konfigurierter BVV-Auswertung wird der Dateiname automatisch um den Anhang "_BVV" erweitert. Also wird z.B. aus KA2006.XLS KA2006_BVV.XLS.

Die Datei wird gespeichert.



Beschreibung Teil 2 - Erstellen der Pivot-Tabellen

Löschen "Leichen"

Löscht evtl. vorhandene Pivottabellen-Blätter, erkennbar an Namenbeginn mit "ALLG *" "SPEZ *" Löschen muss pro Tabellenblatt 2 x bestätigt werden:

Erstellen Pivot-Tabellen

Erstellt folgende 17 Pivottabellen - mit Vergleichszahlen der ausgewählten Dateien ("Se-mester"),: Beschriftung Inhalt "ALLG Anm." Grobe Struktur der Fachbereiche - Summe Anmeldungen ANZ_ANM "ALLG Kurse" Grobe Struktur der Fachbereiche - Anzahl Kurse ANZ_ANM + AUSFALL "ALLG Einn." Grobe Struktur der Fachbereiche - Summe Gebühren ERW_GESAMT "ALLG UE" Grobe Struktur der Fachbereiche - Unterrichtseinheiten STUNDENANZ "ALLG UE x TN" Grobe Struktur der Fachbereiche - UE * Anz. Anmeldungen

STD*ANZ_ANM "ALLG Ausg." Grobe Struktur der Fachbereiche - Summe der Ausgaben Sum_Ausg "ALLG DB" Grobe Struktur der Fachbereiche - Deckungsbeitrag (Einnahmen-

Ausgaben) Deck_Beitrag "SPEZ Anm." Detail Struktur der Fachbereiche - Summe Anmeldungen ANZ_ANM "SPEZ Kurse" Detail Struktur der Fachbereiche - Anzahl Kurse ANZ_ANM + AUSFALL "SPEZ Einn." Detail Struktur der Fachbereiche - Summe Gebühren ERW_GESAMT "SPEZ UE" Detail Struktur der Fachbereiche - Unterrichtseinheiten STUNDENANZ "SPEZ UE x TN" Detail Struktur der Fachbereiche - UE x Anz. Anmeldungen

STD*ANZ_ANM "SPEZ Ausg." Detail Struktur der Fachbereiche - Summe der Ausgaben Sum_Ausg "SPEZ DB" Detail Struktur der Fachbereiche - Deckungsbeitrag (Einnahmen-

Ausgaben) Deck_Beitrag "Quartale Einn." Monats-Einnahmen nach Quartalen aufgefächert, Jahr einstellbar. "Quartale Ausg." Monats-Ausgaben nach Quartalen aufgefächert, Jahr einstellbar. "Quartale DB" Differenz (Deckungs-Beitrag) nach Quartalen aufgefächert, Jahr einstell-

bar. Die Quartals-Tabellen werden immer am Ende der Mappe eingefügt



Aufbau der Pivot-Tabellen

Bezüglich der Quelldaten für die Pivottabellen bezieht sich "ALLG Anm." direkt auf die Tabelle " Kurs-Übersicht", die anderen Pivottabellen dann auf "ALLG Anm." (Ressourcen-Problem !!!). Alle erstellten Tabellen haben als Zeilenfeld die Fachbereiche oder BVV-Bereiche (ALLG bzw. SPEZ) als Spaltenfeld den Namen der dbf-Dateien (DATEINAME) als Seitenfeld die Päd.Mitarbeiter (HPM) (d.h. per Mausklick die Daten pro PM oder für Alle). Die Pivot-Zeilen werden entsprechend dem gewählten Auswertungstyp erstellt: EIGEN oder BVV Beispiel einer Standard-Pivot-Tabelle:



Besondere Pivot-Tabellen Für die "Kurse"-Tabellen wird in die Daten noch die Anzahl der ausgefallenen Kurse in abso-luten Zahlen integriert. Zusätzlich werden per Programm noch die Prozentwerte ausgefallener Kurse berechnet, - und zwar für die Gesamtergebnisse in Zeilen und Spalten.



Für die "DB-Tabellen" (Deckungsbeitrag) wird eine 2-fache Nachbereitung vorgenommen:

1. Zunächst werden in den Zeilen der Pivot-Tabelle per Programm eingefügt: Einnahmen Ausgaben Differenz Einnahmen - Ausgaben Quotient Einnahmen / Ausgaben in % abzüglich 100% (als Maß für die "Güte" des Deckungsbeitrags)

2. Danach wird die so erweiterte Tabelle nach unten verschoben und am alten Platz eine neue Tabelle erstellt, die alle Zellinhalte der alten Tabelle ersetzt durch Quotient Einnahmen / Ausgaben in % abzüglich 100% (als Maß für die "Güte" des Deckungsbeitrags) um die Aussagekraft der "Güte" des Deckungsbeitrages noch weiter herunterzubre-chen.

Abschließende Programmschritte • Schließlich wird noch die Druckausgabe aller Tabellen auf Querformat, zentriert

mit Kopf/Fußzeilen umgestellt. • Die bvv-Tabellenblätter:

BVV-KENNZAHLEN HPM und BVV-KENNZAHLEN BVV-ALLG



werden gefüllt. Details siehe "bvv-Kennzahlen" ab Seite 55 .

• Es werden noch 3 weitere Tabellenblätter am Ende der Arbeitsmappe (Tabfarbe rosa) zum Thema Finanzen erstellt. Ziel hier ist, bereits vorliegende Ergebnisse auf Jahr und Quartal herunterzubre-chen. Folgende Finanz-Kriterien sind vorgesehen: EINNAHMEN AUSGABEN DECKUNGSBEITRAG Beispiel Deckungsbeitrag:



Balkendiagramme

Jeweils vor ein Pivottabellen-Blatt wird die jeweilige Tabelle noch als Balkendiagrammm dar-gestellt. Auch diese Diagramme können mit Hilfe von Pivotschaltflächen "konfiguriert" werden (also z.B. die Daten für einen einzelnen HPM anzeigen oder nur einige Fachbereiche).

Um etwas Übersicht zu bewahren, sind die Tabulatoren der einzelnen Tabellenblätter farblich gegeneinander abgesetzt.



Einen Sonderfall stellen die Diagramme für den Deckungsbeitrag dar: ALLG DB D SPEZ DB D diese greifen auf die vollständig per Programm erstellten oberen Tabellen in den Pivot-Tabellenblättern ALLG DB SPEZ DB zurück und mussten deshalb ebenfalls vollständig unter Programmkontrolle erstellt werden. Rein op-tisch sind sie gut angepasst. Beispiel:

Abschluß des Makros Die Datei wird nochmals gespeichert. Danach kann sie noch verändert werden, z.B. durch zusätzliche individuelle Auswertungen.


Kapitel bvv-Kennzahlen

bvv-Kennzahlen Allgemeine Funktionsbeschreibung Aus einem bvv-Arbeitskreis entstanden die Vorgaben für das "Benchmarking" der bayerischen vhs'en. Das Ergebnis ist ein Liste von Kennzahlen, die zumindest teilweise aus dem vorhandenen Zahlenma-terial ermittelt werden können. Die entsprechende Liste "bvv-Kennzahlen" ist in das Programm vollständig integriert, und zwar in 3 Tabellenblättern: • Summenblatt • Aufschlüsselung der Summen nach HPM • Aufschlüsselung der Summen nach bvv-Fachbereichstabellen (siehe Tabellenblatt BVV-ALLG). Im Programmablauf werden zuerst die Aufschlüsselungen erzeugt (mit Hilfe von Pivottabellen), dann das Summenblatt.

Beschreibung der Kennzahlen Als Messgrößen für die Kennzahlen verwendet der BVV folgende EFQM-Kriterien: • Anteil der Neu-KundInnen • KundInnenzufriedenheitsindex • * Nachfrageentwicklung Teilnahmem • * Innovationsrate im Angebot • * Ausfallquote der Kurse • Anteil der StammKundInnen, ggf. gegliedert nach Zielgruppen • MitarbeiterInnenzufriedenheitsindex • Fortbildungsquote • * Ermäßigungenquote • * Anzahl der Kurse • * Zahl der Kursdoppelstunden • Umsatz • Kosten • Zuschüsse, Drittmittel, Spenden, Projektförderung • Entwicklung der kommunalen Mitfinanzierung • * Deckungsbeitragsrate I • * NekundInnenanteil, ggf. gegliedert nach Zielgruppen • * Kursauslastung • * Personalkosten pro Doppelstunde • Investitionsquote • Eigenfinanzierungsquote Die mit einem * gekennzeichneten Kennzahlen können aus den Kuferdatenbanken gewonnen werden (weiße Kennzeichnung der entsprechenden EFQM-Zelle). Für die anderen Kennzahlen sind andere Anwendungen erforderlich, vor allem die Buchhaltung (hell-grüne Kennzeichnung der entsprechenden EFQM-Zelle).



Beschreibung des Summenblatts



Die in oben stehendem Abbild enthaltenen Zelleninhalte sind fest in das Excel-Tabellenblatt eingetra-gen. Bis auf die Zahlen in der Spalte WERTE DER VHS, die aus den Folgeblättern "bvv-Kennzahlen HPM" bzw "bvv-Kennzahlen BVV-ALLG" aus den Summenspalten übernommen werden (fest eingetragener Fernverweis). Für die Messgrößen Deckungsbeitragsrate I Personalkosten pro Doppelstunde sind in den nicht ausgefüllten roten Zellen der Spalte ERHEBUNGSRHYTHMUS noch manuelle Nachbes-serungen möglich, nämlich Vorlaufwert für Raummiete (Kosten für Deckungsbeitrag I) Manuell ermittelte Personalkosten (Kosten pro Deoppelstunde), weil diese Werte aus der Datenbank nicht abgeleitet werden können. Beschreibung der Blätter nach HPM und BVV-ALLG Diese Tabellenblätter bvv-Kennzahlen HPM bvv-Kennzahlen BVV-ALLG schlüsseln die Messgrößen nach HPM bzw. nach der Allgemeinen BVV-Fachbereichsstruktur (Tabelle am Ende der Arbeitsmappe) auf. Zu deren Berechnung wird am Fuß der Tabelle vom Programm eine weitere Tabelle mit Hilfsdaten eingefügt, deren Inhalte wiederum über Pivot-Tabellen im nicht sichtbaren Bereich des Tabellenblattes ermittelt werden.


Kapitel Nicht-Kufer-Datenbanken

Nicht-Kufer-Datenbanken Allgemeine Funktionsbeschreibung Die Makros setzen normalerweise DBF-Export-Dateien von WIN Basys oder SQLBasys voraus. Andererseits haben auch andere VHS'en mit nicht-Kufer-Datenbanken Interesse an den Auswertun-gen bekundet (allen voran die vhs Götzis, Österreich). Solche Nicht-Kufer-Datenbanken haben 2 Problemkreise: Anders codierte Umlaute und sonstige deutsche Sonderzeichen Andere Feldnamen (z.B. KURS statt KNR) Für diese Fälle wurden die Konfigurationsmöglichkeiten erweitert.

Erweiterung der Kundenanpassung Auf dem Registerblatt MAKRO-AUFRUFE wird zunächst die Schaltfläche KONFIGURATION gedrückt, wie bei jeder Konfiguration. Siehe "Dialog "Kundenanpassung"" auf Seite 11. Im oberen Teil dieses Fensters ist auszuwählen: ANDERE DATENBANKEN.

Wenn nach der sonstigen Konfiguration die Bestätigungstaste OK unten im Fenster gedrückt wird, wird aufgrund dieser Auswahl ein 2. Konfigurationsfenster ANPASSUNG ANDERER DATENBANKEN geöff-net:


Kapitel Nicht-Kufer-Datenbanken

Anpassung Anderer Datenbanken In diesem Fenster können nun die beschriebenen Umwandlungen beauftragt werden. • Sonderzeichen

Es muß jeweils angegeben werden, aus welchem ANSI-Code welches deutsche Sonderzeichen werden soll. Um ANSI-Werte zu ermitteln, ist ein kleines Werkzeug eingefügt: Man kopiert das Zeichen, für das der ANSI-Wert gesucht wird, in die Zelle A2 des Tabellenblatts KURS-ÜBERSICHT. Alle sonstigen Inhalte müssen gelöscht werden. Sodann drückt man die Schaltfläche ANSI-WERT VON ZELLE A2 und bekommt den Wert in einer Messagebox angezeigt.

• Spaltenüberschriften / Feldnamen Es ist jeweils einzutragen, aus welcher Spaltenüberschrift (Andere Datenbank) welche andere werden soll (Kufer-Termini).


Drücken der Schaltfläche OK speichert die Konfiguration in der Datei DBINIT.TXT im Pfad der Init-Dateien (siehe "Festlegen und Speichern Pfad für Init-Dateien" auf Seite 21.

Kapitel Fehlerauswertung

Fehlerauswertung Allgemeine Funktionsbeschreibung Die Idee ist, das Tabellenblatt KURS-ÜBERSICHT auf formale Fehler zu überprüfen. Dazu muss das Makro ablaufen, bis dieses Tabellenblatt erstellt ist - auf die weiterführenden Pivot-Tabellen und Diagramme kann dann versichtet werden. Stattdessen findet die Fehler-Überprüfung statt, erkannte Fehlerzellen werden rot markiert und dann wird das Fehlerprotokoll mit entsprechender Formatierung auf einem separaten Tabellenblatt ausge-geben. Dabei werden überflüssige Spalten und Zeilen weggelassen.

Starten der Fehlerauswertung mit der entsprechenden Schaltfläche auf dem Tabellenblatt MAKRO-AUFRUFE.

Zunächst wird ein Konfigurationsfenster zur Eingabe folgender Prüf-Kriterien angezeigt: • Angabe des Semesters, exakt im Anzeigeformat der Spalte SEMESTER (z.B. 1/06). • frühest mögliches Kursbeginn-Datum in irgendeinem Datumsformat (z.B. TMJJ oder TTMMJJJJ). • Überspringen der Prüfung der Spalten

HPM KTO_ ... KST_ ... falls entsprechende Inhalte nicht vorhanden sind.

• Ausschalten der Überprüfung des Uhrzeiten-Formats.

Danach läuft das Programm wie gewohnt: • es wird die eine zu prüfende DBF-Datei ausgewählt.



• diese wird vom Makro eingelesen … • … formatiert … • … Spalten werden eingefügt und gefüllt … • … so wird das Tabellenblatt KURS-ÜBERSICHT erstellt … • … und nach Ihren Angaben gespeichert.

Durchführen der Fehlerauswertung Zunächst wird das Blatt KURS-ÜBERSICHT in ein neues Tabellenblatt KURS-ÜBERSICHT FEHLER kopiert. Dort erfolgt zeilenweise von unten nach oben eine Prüfung auf Fehler, Zeilen ohne erkannte Fehler werden weggelassen. Derzeit werden in der KURS-ÜBERSICHT FEHLER folgende Prüfungen durchgeführt, Fehler werden durch Roten Hintergrund der entsprechenden Zelle angezeigt. • Ohne Zuständigkeit (leere Zelle bei HPM) • Feld KURSORT ohne Eintrag • Falsches Semester (Vergleich mit Konfigurations-Eingabe) • Falsches Kursbeginn-Datum (Vergleich mit Konfigurations-Eingabe) • Ohne Veranstaltungsart (leer) • Kurse, die auf Ausfall gesetzt sind (AUSFALL = 1) • Ohne Kursleiter (HPM leer) - sofern das Kontrollkästchen UNTERDRÜCKEN nichtgesetzt ist. • Ohne Stundenanzahl ( 0 oder leer) • Kursnummer enthält nichtnumerische Zeichen (z.B. "O" statt "0") • Ohne Fachbereichsangabe (leer, wichtig für Statistik) • KTO und / oder KST fehlt (leer), 6 Felder ! - sofern das Kontrollkästchen UNTERDRÜCKEN nicht

gesetzt ist. • MAXTN = 0 • Format "hh:mm" für UHRVON und UHRBIS Nach der Prüfung werden noch Formatierungen vorgenommen: • Registerblatt orange • Überschriften um 90° gedreht, Überschriften horizontal rechtsbündig, Ausnahmen zentriert. • Löschen Spalte DATEINAME • Spaltenbreiten Optimal bzw. Soll-Breiten, möglichst eng wegen Druckausgabe auf 1 Blatt breit. • Tabelle fixieren an Zelle C2. • Spalten ohne Fehler werden gelöscht. • Zeilen ohne Fehler werden gelöscht • Seitenlayout "mit allem" (Kopf/Fußzeile, zentrieren usw.).


Kapitel HPM ToDo-Listen

HPM ToDo-Listen Allgemeine Funktionsbeschreibung Da mit der "Kurs-Übersicht" ein nahezu vollständiges Bild von der Kursvielfalt der vhs vorliegt, liegt es auch nahe, Auswertungen vorzunehmen zur Unterstützung der HPM's bei Beurteilung ihrer Kurse, z.B. • welche Kurse sind gefährdet, weil nicht genügend Anmeldungen vorliegen? • Für welche dieser gefährdeten Kurse lohnt noch ein Presse-Aufruf? • Für welche Kurse gibt es bereits eine Warteliste, so dass eine Erweiterung des Angebots interessant ist? Diese Auswertungen stellen im Grunde eine Filterung des Kursbestandes für den HPM dar. Dementsprechend wird auch die Excel-Filterfunktion benutzt, genauer die berechnenden Spezialfilter

Beschreibung der Filter Alle Filter

Auswahl des HPM ( oder Wildcard "*") Start-Datum für Kurse Ende-Datum für Kurse AUSFALL-Kennzeichen nicht gesetzt (leer , ungleich 1)

Gefährdete Kurse

Anzahl der Anmeldungen kleiner oder gleich Minimale Anzahl Teilnehmer

Presse

Anzahl der Anmeldungen kleiner oder gleich Maximale Anzahl Teilnehmer minus Presse-Vorgabe (z.B. 6) und Anzahl der Anmeldungen größer oder gleich Minimale Anzahl Teilnehmer

Warteliste

Anzahl der WL-Plätze größer oder gleich 1

Ablauf des Programms Nach der Auswahl der Anwendung "HPM ToDo-Liste" auf dem Tabellenblatt MAKRO-AUFRUFE wird zunächst ein Konfigurationsfenster angezeigt



HPM

Die Schreibweise muss exakt wie in der Spalte HPM der Datenbank erfolgen, sonst kann kei-ne Übereinstimmung festgestellt werden und der HPM hat "keine" Kurse. Eingabe * als Wildcard für alle HPM ist möglich (Hilfe für die Verwaltung).

Startdatum

Voreingestellt ist das aktuelle Tagesdatum. Es kann aber überschreiben werden, z.B. für die Auswertung älterer dbf-Dateien.

Zeitfenster

Voreingestellt ist ein Zeitfenster von 14 Tagen (Ende-Datum wird daraus errechnet). Es kann aber überschreiben werden, z.B. längerfristigere Vourausschauen.

Presse-Vorgabe

Voreingestellt ist ein Wert von 6 Anmeldungen, um die der Maximalwert von Teilnehmern un-terschritten werden muss, damit eine Presse-Aktion vorgeschlagen wird. Dieser Wert kann aber auch überschrieben werden.

Nach der Konfiguration der Teilanwendung erfolgt die übliche Auswahl der zu verarbeitenden dbf-Datei (die in der Regel sehr aktuell sein sollte). Es darf nur eine einzige dbf-Datei ausgewählt werden. Danach erfolgt das übliche Laden der dbf-Datei in Excel und das Erstellen der "Kursübersicht". Der Zwischenstand wird nach Ihren Angaben abgespeichert.



Nun erfolgt die spezifische Verarbeitung für die ToDo-Listen:

1. Alle Tabellenblätter werden gelöscht - bis auf die KURS-ÜBERSICHT und die MAKRO-AUFRUFE. 2. Eine neues Tabellenblatt TODO-LISTEN wird eingefügt. 3. Bestimmte Spalten der Datenbank werden aus der KURS-ÜBERSICHT in das Blatt TODO-LISTEN

kopiert - und zwar rechts von dem auf dem Bildschirm sichtbaren Bereich. 4. Weitere Überschriften werden hinzugefügt, die Überschriften werden formatiert. 5. Der Seitenkopf wird erstellt

HPM Startdatum Endedatum

6. Die Spezialfilter werden erstellt - rechts von dem auf dem Bildschirm sichtbaren Bereich, links von der Teildatenbank.

7. Die Spezialfilter werden ausgeführt und erzeugen die gewünschten 3 Auflistungen.

8. Abschließend werden Teildatenbank und Filterdefinitionen gelöscht. die Druckaufbereitung vorgenommen (Kopfzeile, Fußzeile, Breite 1 Blatt, Zentrierungen) Registerblatt rot.

Plausibilitätsprüfungen durchgeführt.


Kapitel ListGenerator

ListGenerator Allgemeine Funktionsbeschreibung Aus der Praxis für die Praxis: Der Listgenerator ist ein Tabellenblatt, dass auf Wunsch erzeugt wird (Makro-Schaltfläche auf der Startseite der KUFERAUSW.XLS) und auf dem Excel-Spezialfilter beliebig mit Hilfe der Felder der Datenbank im Tabellenblatt KURS-ÜBERSICHT definiert werden können. Auch die Listausgabe der Datensätze, die so herausgefiltert werden, sind frei konfigurierbar. Für die Verwaltung dieser Spezialfilter gibt es weitere Makros (Speichern und Rückspeichern der Fil-ter, Löschen, Drucken, Formatieren usw.)



Erzeugen Tabellenblatt LISTGENERATOR Wie bei allen Auswertungen wird zunächst die Datei KUFERAUSW.XLS geöffnet. Auf dem Tabellenblatt MAKRO-AUFRUFE gibt es eine Schaltfläche LISTGENERATOR.

Nach Anklicken laufen folgende Arbeitsschritte ab:

• Einlesen der Konfiguration des Makros aus MAKROINIT.TXT

• Anzeigen der vorhandenen dbf-Dateien

• Auswahl der zu berücksichtigenden dbf-Dateien (auch mehrere mit Strg oder Shift) sowie dem Typ der Auswertung (Eigene Fachbereichstabellen oder BVV).

• Auch weitere Bearbeitungsschritte genau wie bei der normalen Kufer-Auswertung, beschrieben "Einlesen dbf-Datei und Bearbeitungsschritte"unter “ ab Seite 11. Am Ende steht das fertig aufbereitete Tabellenblatt KURS-ÜBERSICHT.

• Einfügen neues Tabellenblatt LISTGENERATOR in die Tabelle, löschen aller nicht relevanten ande-ren Arbeitsblätter.

• Einfügen Überschriften FILTER-KRITERIEN und FILTER-LISTENAUSGABE in das neue Tabellenblatt, diverse Formatierungen.

• Erzeugen Listenfelder mit den Feldnamen aus der Kursübersicht an folgenden Positionen: - ZEILE_FILTER : Spalte 1 bis Spalte SPALTEN_FILTER - ZEILE_LISTAUSG Spalte 1 bis SPALTEN_LISTAUSG Die Variablen sind im Programm änderbar und zunächst gesetzt auf: - ZEILE_FILTER = 3 - ZEILE_LISTAUSG = 15 - SPALTEN_FILTER = 8 - SPALTEN_LISTAUSG = 12



• Erzeugen eines eigenen Menüpunktes LISTGENERATOR in der Menüzeile von Excel mit Makros zur Verwaltung der Filter.

• Abspeichern der Arbeitsmappe.

Grundsätzliches Arbeiten mit dem Tabellenblatt LISTGENERATOR 2 Zeilen unterhalb der Überschriftszeilen für FILTER-KRITERIEN und FILTER-LISTENAUSGABE wurden jeweils Zellen blau eingefärbt. Anklicken einer dieser Zellen erzeugt rechts ein Listenfeldsymbol, in dem einer der Feldnamen des Registerblatts KURS-ÜBERSICHT ausgewählt werden kann.

Auf diese Weise werden Spezialfilter definiert und auch die Listenausgabe darunter kann frei konfigu-riert werden. Mit Hilfe des Menüpunktes LISTGENERATOR werden diese Festlegungen dann bedient (s.unten).

Abschließend sollte die Datei nochmals gesichert werden.

Erstellen eines Spezialfilters Das Erstellen erfolgt in mehreren Schritten:

• Zunächst werden die Feldnamen zurechtgeklickt, die für die Definition des Filters eine Rolle spie-len sollen. Die Feldnamen müssen nicht zwingend lückenlos eingetragen werden.

• In den Leerzeilen darunter werden nun die Inhalte der Feldnamen eingetragen, die herausgefiltert werden sollen. Dabei sind folgende Excel-Regeln einzuhalten:



• Der Spezialfilter wird zeilenweise von oben nach unten abgearbeitet. Die Zeilen werden dabei mit der ODER-Funktion verknüpft. Im Beispiel: Es wird nach Kursen mit Dozentennamen "Schierl" ODER "Borchert" gesucht.

• Einträge in einer Zeile dagegen werden mit UND verknüpft. Im Beispiel: Vom Dozenten "Schierl" werden nur die Kurse berücksichtigt, die außerdem noch an Kursort "V*" stattfinden. Für "Borchert" dagegen gibt es keine Einschränkungen - alles Kursorte sind zugelas-sen.

• Bei der Angabe von Kriterien sind auch Sonder-Bedingungen erlaubt, z.B. Wildcards * und ? "*" steht für beliebige Texte, "?" für 1 beliebiges Zeichen < und > in der Sortierung früher oder später >=, <= wenn auch der Filtertext selber zugelassen sein soll UND und ODER Verknüpfung zweier Bedingungen auch Datumsangaben als Filtertext sind zugelassen (z.B. >=1.2.99)

• Im Beispiel bedeutetet "V*", dass alle Kursorte, die mit "V" beginnen, zugelassen sind (also min-destens alle Veranstaltungen in Vaterstetten).

Erstellen einer Ausgabeliste In der Kopfzeile werden zunächst die Feldnamen zurechtgeklickt, deren Inhalte nach der Filterung angezeigt werden sollen. Wichtig dabei: die Ausgabeliste darf - im Gegensatz zur Filterdefinition - keine Lücken aufweisen. Ansonsten gibt es Fehlerhinweise und der Filter kann nicht ausgeführt wer-den. Beispiel oben: Kursnr. / Kurstitel / Dozentenname / Fachbereich / Kursort / Beginn-Datum Danach wird das Makro zum Erstellen der Listenausgabe gestartet und das Ergebnis wie im Beispiel erstellt. LISTGENERATOR / STARTEN SPEZIALFILTER Die Ausgabeliste wird zur besseren Lesbarkeit abwechselnd grau / weiß formatiert (s. Beispiel).



Sonstige Makros für den Listgenerator In dem Excel-Menüpunkt LISTGENERATOR werden folgende Makros zur Verfügung gestellt:

• STARTEN SPEZIALFILTER Erzeugt die Filterausgabe, nachdem die Kopfzeilen von FILTER-KRITERIEN und FILTER-LISTAUSGABE ausgefüllt sind.

• LÖSCHEN KRITERIEN Löscht die Inhalte des Bereichs FILTER-KRITERIEN einschl. Kopfzeile

• LÖSCHEN LISTAUSGABE Löscht die Inhalte des Bereichs FILTER-LISTAUSGABE einschl. Kopfzeile und alle Formate, außer in der Kopfzeile.

• DRUCKEN LISTAUSGABE Druckt die Listausgabe im Querformat mit Benutzerdefinierten Kopf- und Fußzeilen, 1 Seite breit, ggfs. mehrere Seiten lang mit Wiederholung der Kopfzeile, zentriert.

• SPEICHERN FILTER Die Definitionen des Filters und der Listausgabe werden als Exceldatei mit einem Namen Ihrer Wahl in einem Ordner Ihrer Wahl abgelegt. Voreingestellt ist der Pfad für Ihre DBF-Dateien und dort ein Unterordner FILTER, der beim ersten Speichern automatisch angelegt wird. Als Dateiname: FILTER.XLS

• RÜCKSPEICHERN FILTER Mit einem entsprechenden Dialog wird auch das Rückspei-

chern von Filtern in das Arbeitsblatt LISTGENERATOR ermög- licht.



• FORMATIEREN LISTAUSGABE Durch Manipulationen an der Listausgabe (Löschen von Zei- len, Umsortieren usw.) kann es zur "Zerstörung" der zeilen weisen grau / weiß -Formatierung kommen. Mit diesem Makro wird neu formatiert.

• OPTIMALE BREITE LISTAUSGABE Standardmäßig stellt das Programm die Spaltenbreiten optimal über die gesamten Spalteninhalte ein. Mit diesem Makro werden die Spaltenbreiten gezielt auf die Inhalte der Listausgabe ausgerichtet.

Dieses "benutzereigene" Menü steht nach der Ausführung des Listgenerator-Makros zur Verfügung, nicht aber in der Originaldatei. KUFERAUSW.XLS und auch nicht nach dem Schließen einer Auswertungsdatei. Um die Makros auch beim Wieder-Öffnen einer bereits endgültig abgespeicherten Auswertungsdatei auch wieder zur Verfügung zu haben, ist eine AUTO_OPEN-Routine beigefügt, die in diesen Fall das Menü wieder erstellt.


Kapitel Information an die Presse

Information an die Presse

Allgemeine Funktionsbeschreibung Aus der Praxis der VHS Vaterstetten erwuchs folgende Anwendung des Listgenerators: Zunächst werden durch einen Filter die demnächst beginnenden Kurse festgestellt, in denen noch Plätze frei sind, für die also noch "Reklame" gemacht werden soll. Diese Liste wird dann mit den PMs abgestimmt. Die "Reklame" erfolgt in Vaterstetten gemeinhin über die lokale Presse, die sich ihrerseits die Informa-tionen für die Veröffentlichung in einem bestimmten Format wünscht, z.B.

Das Makro setzt die von von PMs bereinigte Liste voraus und erstellt auf einem neuen Tabellenblatt die Information an die Presse in obigem Format. Es wird automatisch Word gestartet, die Infomation an die Presse dorthin kopiert und ausgedruckt.



Die Definition des Filters für den Listgenerator

Filterkriterien BEGINNDAT > erster Tag der gewünschten Beginn-Periode BEGINNDAT < letzter Tag der gewünschten Beginn-Periode ANZ_FREI >0 KURSTITEL <>Ausfall:* Verlauf <>erster Wert in Verlauf, der ausgeschlossen werden soll Verlauf <>zweiter Wert in Verlauf, der ausgeschlossen werden soll Verlauf <>dritter Wert in Verlauf, der ausgeschlossen werden soll Dies ist ein Beispiel, welches für Vaterstetten sinnvoll ist. Definition der Listenausgabe KURSTITEL *) Kurstitel BEGINN_TAG *) in der Form Mo, Di, Mi, ... BEGINNDAT *) in der Form TT.MM.JJJJ UHRVON *) in der Form hh:mm UHRBIS *) in der Form hh:mm KURSORT *) besteht aus Ort, Straße und Raum KURS Kursnummer DAUER in Tagen DOZ_NAME Name des Sozenten ANZ_FREI Anzahl noch freier Plätze Die mit *) markierten Felder sind Pflichtfelder und werden im Makro verwendet. Fehlen sie, wird eine Fehlermeldung ausgegeben und das Programm wird abgebrochen.



Die Verarbeitung Für den Aufruf der Makroerweiterung wird ein Menüpunkt FORMATIERUNG FÜR DIE PRESSE in dem Hauptmenü LISTGENERATOR zur Verfügng gestellt.

• Zunächst erfolgt die Prüfung, ob alle für die Ausführung des Makros erforderlichen Felder (Variab-

len) in der Ausgabeliste zur Verfügung stehen. Fehlt eines, wird eine Messagebox ausgegeben und das Programm bricht ab. In welcher Spalte die Felder stehen, ist unwichtig.

• Dann wird hinter dem Tabellenblatt LISTGENERATOR eine weiteres Blatt PRESSE-INFO eingefügt. In dieses wird ab der Zelle C1 die Filter-Listenausgabe kopiert.

• Es finden eine Reihe von Formatierungen statt, um die endgültige Ausgabe zu ermöglichen.

• Insbesondere wird der KURORT in 2 Spalten ORT und STRASSE aufgeteilt

• Dadurch wird ermöglicht, die kopierte Tabelle nach ORT und BEGINNDAT zu sortieren, damit die Ergebnisliste entsprechend sortiert ist.

• Nun wird die Ergebnisliste in Spalte 1 erstellt, mit Überschriften und Einträgen in der Formatierung so wie auf obigem Beispiel als Wunsch angegeben. Auch die Spaltenbreite ist entsprechend gesetzt.

• Die Tabelle wird nochmals gespeichert, um die Ausdrucke später noch manuell nachbearbeiten zu können.

• Danach wird Word geöffnet, die Ergebnisliste in Spalte 1 dorthin kopiert (Zwischenablage) und dann ausgedruckt.

Die Excel- und Word-Darstellungen sind auf der nächsten Seite exemplarisch dargestellt.



Excel Word




Bemerkung zu Excel 2007 Zumindest derzeit gibt es in Excel 2007 ein unangenehmes Problem: Filterkriterien des Typs <12.06.2008 mit dem alle Datumer vor dem 12.06.2008 erfasst werden sollen, arbeiten zwar bei manuellem Aufruf des SEZIALFILTERS, nicht aber aus dem VBA-Programm, welches mit Hilfe des Makro-Recorders er-stellt wird. Es gibt derzeit auch keine Umgehung, so dass der Einsatz dieses Funktion INFOPRESSE UNTER OFFICE 2007 eingeschränkt ist, bis hoffentlich Microsoft für das Problem eine Lösung mit Updates bereitstellt.

Webseite: Excel-Makros · Speichern der Excel-Tabelle 36. Beschreibung Teil 2 - Erstellen der...

Documents

Transcript of Webseite: Excel-Makros · Speichern der Excel-Tabelle 36. Beschreibung Teil 2 - Erstellen der...