ODBC-Zugriff läuft aus

Der OBDC-Zugriff läuft aus. Abfragen sind ab jetzt auch per API möglich und werden weiter ausgebaut. Eine entsprechende Beschreibung findet sich hier: API & OTA-Integration. Daneben können einfache ODBC-Abfragen auch über die Tabellenkalkulation abgebildet werden. Eine Einführung findet sich hier: Tabellenkalkulation (ab profacto)

Achtung: Nur für fortgeschrittene Benutzer

Über die ODBC Schnittstelle können Sie Ihre Daten individuell auswerten. Dies ist nur erforderlich, wenn Ihnen die in profacto integrierten Funktionen nicht ausreichen. Im- und Exporte aller Art (z.B. Adressen oder Artikel) können Sie besser mit Bordmitteln erledigen. Solange die Bordmittel reichen, besteht keine Notwendigkeit, sich mit ODBC zu beschäftigen. Falls Sie ODBC benötigen, eignet sich Excel am besten für den Einstieg.

Einführung

ODBC (Open DataBase Connection) ist eine Technologie, die es ermöglicht, von "außen" auf die in Datenbanksystemen enthaltenen Daten zuzugreifen. Dies bedeutet für profacto, dass Sie

von einem Windows-Arbeitsplatz (nicht vom Mac)
auf eine profacto-Datenbank, die auf einem profacto Server läuft (nicht unter einer Einzelplatzversion)
mit Hilfe einer ODBC-fähigen Anwendung wie MS Excel, MS Access, Seagate Crystal Reports oder anderen datenverarbeitenden Anwendungen – wenn entsprechende Erweiterungen in diese Anwendungen installiert sind
lesend zugreifen können

ODBC bietet in Zusammenarbeit mit verschiedenen Programmen den Vorteil, dass Datenbankabfragen, die dann gewisse Datensätze zurückliefern, nur einmal definiert werden müssen und dann gespeichert werden können. Die Daten selbst können in aktualisierter Form sogar automatisch aus der Datenbank ausgelesen werden. Dies ermöglicht Auswertungen, die über die monatlich aktuelle Umsatzstatistik oder den tagesaktuellen Krankenstand hinausgehen. Stündlich oder sogar minütlich aktuelle Daten sind problemlos auswertbar, soweit dies sinnvoll ist.
In diesem Tutorial erfahren Sie nicht nur, wie Sie die richtigen Informationen aus profacto ermitteln, sondern auch, wie sinnvolle Verknüpfungen dieser Informationen und mächtige Auswertungen am Beispiel von MS Excel 2003 oder neuer erstellt werden können.

MS Query Erweiterung erforderlich

Sie benötigen Office 2003 oder höher mit der Erweiterung MS Query.

Installation

Da profacto auf Basis einer SQL-Datenbank arbeitet, muss vor der Treiberinstallation auf dem Arbeitsplatzrechner der SQL-Server innerhalb des profactoServers gestartet sein.

Server-Einstellungen

Unter Bearbeiten -> Einstellungen -> SQL haben Sie besonders die Möglichkeit den SQL-Server automatisch starten zu lassen und einen abweichenden TCP Port zu definierenSeit profacto 2011 wird der SQL-Server innerhalb des profacto-Server automatisch gestartet.

Im Administrationsfenster selber sehen Sie die aktuellen Einstellungen nochmals und haben die Möglichkeit den SQL-Server manuell zu starten und zu stoppen.

Außerdem kann die Verbindung via SSL eingerichtet werden. Dazu sind 2 Dinge erforderlich:

Haken bei SSL aktivieren setzen
Ihre '''eigenen''' SSL-Lizenzschlüssel "key.pem" und "cert.pem" in den Ordner \profacto2012 Server\Server Database\Preferences\SQL einfügen (sollte der Ordner nicht existieren, so können Sie diesen manuell erstellen). Bei einem Serverupdate müssen Sie daran denken, diesen in dem Ordner zu belassen.

Treiberdownload

Die jeweils aktuellen Treiber finden sich in den Release Notes bei den Downloadlinks der profacto-Installer. Bitte dort den jeweils passenden herunterladen. Für ältere Versionen bitte unten schauen.

Die Treiberversion (32-64-bit) richtet sich nach der kommunizierenden Anwendung (zB. Excel), nicht nach dem Betriebssystem.

Für Excel im MS Office 2016 finden Sie es folgendermaßen heraus: auf Datei gehen, dort Konto und dort "Info zu Excel". Hier sehen Sie ob, Sie eine 32-bit oder 64-bit Variante verwenden.

Treiber für ältere Versionen

profacto 2016.0 und neuer: http://profacto.extragroup.biz/Installation/4D_ODBC_Driver_v15.0_Windows_64-bit.exe (64-bit)
profacto 2016 und neuer: http://profacto.extragroup.biz/Installation/4D_ODBC_Driver_v15.0_Windows_32-bit.exe (32-bit)

profacto 2016.3 und neuer: 64-bit-Treiber Windows
profacto 2016.3 und neuer: 32-bit-Treiber Windows

profacto 2017.2 und neuer: http://profacto.extragroup.biz/Installation/4D_ODBC_Driver_v16.0_Windows_64-bit.exe (64-bit)
profacto 2017.2 und neuer: http://profacto.extragroup.biz/Installation/4D_ODBC_Driver_v16.0_Windows_32-bit.exe(32-bit)
profacto 2017.4 und neuer: http://profacto.extragroup.biz/Installation/4D_ODBC_Driver_v16_R2_Windows_64-bit.exe (64-bit)
profacto 2017.4 und neuer: http://profacto.extragroup.biz/Installation/4D_ODBC_Driver_v16_R2_Windows_32-bit.exe(32-bit)

profacto 2018.2.0 und neuer: http://profacto.extragroup.biz/Kundendownload/2018.2.0/4D_ODBC_Driver_v16_R5_Windows_64-bit.exe (64-bit)
profacto 2018.2.0 und neuer: http://profacto.extragroup.biz/Kundendownload/2018.2.0/4D_ODBC_Driver_v16_R5_Windows_32-bit.exe. (32-bit)

profacto 2018.3.0 und neuer: http://profacto.extragroup.biz/Kundendownload/2018.2.0/4D_ODBC_Driver_v16_R5_Windows_64-bit.exe (64-bit)
profacto 2018.3.0 und neuer: http://profacto.extragroup.biz/Kundendownload/2018.2.0/4D_ODBC_Driver_v16_R5_Windows_32-bit.exe. (32-bit)

Durch starten von 4D_ODBC_Driver_v1x.x.exe wird der Treiber automatisch installiert.
Nach erfolgreicher Treiberinstallation müssen Sie via Systemsteuerung -> Verwaltung -> Datenquellen (ODBC) öffnen, um eine neue DSN zu erstellen.

Windows 7 64Bit, Windows 8.x 64 bit, Windows 10 64-bit

Unter Windows 10 (64-bit) muss man beim Einrichten der System-DSN folgendes beachten:

via Systemsteuerung -> Verwaltung -> Datenquellen (ODBC) können nur 64Bit-Treiber angesprochen werden. Ein Einrichten einer System-DSN funktioniert somit für die meisten Anwender nicht optimal.
via C:\Windows\syswow64\odbcad32.exe kann mit dem 32Bit-Treiber eine System-DSN eingerichtet werden.
DSN einrichten

Prinzipiell ist die Einrichtung einer neuen DSN identisch wie unter profacto 7 (hier dokumentiert), daher wird hier nur auf die kleinen Differenzen eingegangen:

Fügen Sie eine neue Datenquelle hinzu und wählen den Treiber zum Beispiel «4D v15 ODBC Driver».
Name vergeben, IP und Port eintragen und testen.
Die Anmeldung ist ab pf2010 auf chef/chef und Benutzername/Passwort des Datenbankadministrators eingeschränkt.
Damit ist die Benutzer-DSN eingerichtet und Sie können unten weitermachen.

Abfragekomponenten installieren

Wenn Sie die Systemeinstellungen und DSNs eingerichtet haben, müssen Sie sicherstellen, dass auf dem Arbeitsplatz Werkzeuge für eine ODBC-Abfrage vorhanden sind. Wenn Sie mit Microsoft-Produkten arbeiten, wird hierfür «MSQuery» verwendet. Dieses Produkt wird nicht serienmäßig bei der Installation von MS-Office installiert. Sie müssen es daher nachinstallieren.

Wenn Sie ihre Office-CD eingelegt und das Installationsprogramm gestartet haben, wählen Sie die Option, um einzelne Komponenten hinzuzufügen. «MSQuery» findet sich im Bereich «Data Access». Markieren Sie «MSQuery» und starten Sie die Installation.

Richtig Daten abfragen in Excel

Start: Abfrage auslösen

Wählen Sie aus dem Menü «Daten/Externe Daten» den Befehl «Neue Abfrage erstellen».
Wählen Sie die richtige Datenquelle an und klicken Sie auf «OK».
Jetzt melden Sie sich bei profacto an und können dann die Abfragemöglichkeiten von MS Query nutzen.
Es erscheint nun die Dateiliste mit allen Dateien, die profacto nutzt.

Lizenzen für den ODBC Zugriff

Beachten Sie, dass bei jeder Datenaktualisierung kurzfristig eine Client-Verbindung benötigt wird. Dafür ist wie bei einem normalen Arbeitsplatz eine Client-Lizenz erforderlich. Falls Sie nun also eine Fehlermeldung erhalten, ist die maximale Anzahl an Client-Verbindungen ausgeschöpft. Sie können gerne jederzeit zusätzliche Clients erwerben.

Schritt 1: Die richtige Datei wählen

Nicht alle Dateien in profacto sind für die ODBC-Datenabfrage geeignet. Einige Dateien enthalten Bilddaten, die in Tabellen nichts zu suchen haben, andere enthalten Konstanten wie Textbausteine, Postleitzahlen, Anredefloskeln etc., die keinen besonderen Wert für Auswertungen besitzen. Auch Dateien mit temporären Daten wie «Dokumente» oder «kompZeit» sind für ODBC-Auswertungen nicht zu verwenden.

Wenn Sie eine ODBC-Abfrage starten, sollten Sie sich bei jeder Abfrage auf EINE Datei beschränken. Dank der SQL-Definition von ODBC ist zwar mit einer Abfrage auch die Verknüpfung mehrerer Dateien möglich, allerdings erkennt das Abfragewerkzeug nicht immer automatisch, über welche gemeinsamen Felder eine Verknüpfung erfolgen kann und dann sind Sie als Anwender gefragt, relativ komplexe Vorgänge manuell in einem Abfrageeditor einzustellen. Außerdem sind Verknüpfungen sehr langsam – in Excel schaffen wir so etwas schneller.
Im Folgenden stellen wir einige besonders wichtige Dateien vor, die oft auch dann abgefragt werden müssen, wenn wir sie für die Auswertung direkt nicht benötigen:

Artikel

In dieser Datei befinden sich die Artikelstammdaten. Obwohl in der Zwischenzeit nicht mehr alle Felder zu Artikeln in dieser Datei stehen, sind die wichtigsten Informationen wie Artikelgruppe, Preise, aktueller Lieferant, Kurzbezeichnung in dieser Datei zu finden.

Artikelgruppen

In dieser Datei finden sich die Definitionen der Artikelgruppen mit den spezifischen Aufschlagsfaktoren, der Standardmengeneinheit und der Materialeigenschaft (für IMOS).

Auftrag

Diese Datei enthält alle Projektkopfdaten wie Auftragsnummer, Adresse für Kunde und Lieferant, Kommission, Bauvorhaben, Rabatte und Skonto, aber auch das Auftragsvolumen. Nicht alle Felder, die Sie in der Projektbearbeitung sehen, sind hier auch abgespeichert, viele werden erst berechnet, wenn Sie ein Projekt bearbeiten.

Auftragsposition

Hier sind die Positionen zu einem Projekt gespeichert. Um die Daten in dieser Datei auswerten zu können, z.B. für eine Auswertung nach Artikelnummer und Kunde, müssen Sie auch die Datei Auftrag abfragen. In Auftragsposition finden Sie die Positionsnummer, die Dokumentnummer, die Artikelnummer, Bezeichnung, Preise und Rabatte wieder.

Stückliste

Diese Datei enthält alle Stücklistenteile. Hier ist es besonders wichtig, auch den Auftrag und die Auftragsposition abzufragen, wenn man die Stücklistendaten auswerten will, da in der Stücklistendatei selbst nur die Auftragsnummer und die Positionsnummer abgespeichert sind. In der Datei Stückliste sind alle Felder zu Trägermaterial, Belag und Kanten zu finden, die Beschichtungen jedoch sind wiederum in einer eigenen Datei, da es zu jedem Stücklistenteil beliebig viele Beschichtungen geben kann.

Bestellung

In dieser Datei sind alle Bestellkopfdaten zu finden. Sie ist daher mit der Datei «Auftrag» vergleichbar, hat jedoch weniger Felder.

BestellPos

Diese Datei ist mit «Auftragsposition» vergleichbar. Hier finden sich die Positionen einer Bestellung. Um Auswertungen über bei bestimmten Lieferanten bestellte Artikel zu erzeugen, müssen Sie immer «Bestellung» und «Bestellpos» abfragen.

Kunden

In dieser Datei sind die Hauptdaten für alle Kunden und Interessenten hinterlegt. Dazu gehört natürlich die Adresse, aber auch sonstige Felder wie Kundengruppe, Status, Umsatzsteuerstatus oder der VK-Faktor. Die Adressen der Ansprechpartner sind in der gleichnamigen Datei «Ansprechpartner» hinterlegt. Auch die kundenspezifischen Rabatte sind in einer eigenen Datei zu finden.

Lieferanten

Für die Lieferantendaten gilt das Gleiche wie für «Kunden». Zentrale Daten sind in dieser Datei abgelegt, Ansprechpartner sind in der Ansprechpartner-Datei zu finden. Genauso sind Warengruppen und Rabattgruppen in eigenen Dateien abgelegt.

Personal

In dieser Datei sind alle Mitarbeiter zu finden. Die Datei ist relativ umfangreich, enthält dafür aber auch alle für den Mitarbeiter erfassbaren Informationen, darunter auch der Resturlaub, die Überstunden oder der Produktivitätsfaktor.

Kostenstellen

In dieser Datei sind alle Definitionsdaten zu Kostenstellen hinterlegt, also auch die variablen und fixen Kosten und damit der resultierende Stundensatz der Kostenstelle. Sie finden hier auch den Kostenstellenbereich und die Gruppe, zu der die Kostenstelle gehört, die Definitionen von Bereich und Gruppe (insbesondere die dort hinterlegten Pauschal-Stundensätze) sind aber in eigenen Dateien hinterlegt.

Zeitdatei

Für sehr viele Auswertungen ist die Zeitdatei das Maß aller Dinge. Hier sind alle erfassten Zeiten gespeichert. Obwohl die Datei nicht sehr viele Felder hat, ist ihre Auswertung nicht ganz einfach, da sich in der Zeitdatei sowohl Anwesenheitszeiten als auch Auftragszeiten, also zu Projekten zugeordnete Zeiten finden. Der Unterschied wird über ein einziges Feld geregelt, das bei der Abfrage entsprechend zu berücksichtigen ist.

Arbeitszeit

Diese Datei enthält die auf einen Tag verdichteten Zeiten eines Mitarbeiters. Sie wird durch den Befehl «Tagesarbeitszeiten aktualisieren» gefüllt. Was für Zeiten hier enthalten sind, bestimmen Sie als Anwender, es können sowohl Auftragszeiten als auch Anwesenheitszeiten sein.

Zeitauswertung

Jeder Datensatz dieser Datei enthält die auf einen Zeitraum, normalerweise einen Monat, verdichteten Zeiten eines Mitarbeiters.

Schritt 2: Die richtigen Felder wählen

Nachdem Sie im linken Teil des Abfragefensters die abzufragende Datei bestimmt haben, können Sie diese doppelklicken und Sie erhalten eine Liste der abfragbaren Felder einer Datei. Durch einen Doppelklick auf das betreffende Feld erscheint dieses rechts in einer Art Ergebnisliste. Wenn Sie Felder auswählen, sollten Sie immer die Felder, nach denen gesucht werden könnte, zuerst übernehmen, da wir in Excel später am einfachsten nach Feldern suchen können, die in einer Tabelle ganz weit links stehen.

Bei der Auswahl der Felder sollten Sie bedenken, daß Sie immer alle Felder mit übernehmen, mit denen ein Datensatz eindeutig beschrieben werden kann. Das sind die sogenannten Schlüssel. Nachfolgend eine Auflistung der Schlüssel für die wichtigsten Dateien:

Artikel: ArtikelTypenNr (Alpha) und Artikelgruppe (Alpha)
Auftrag: AuftragsNr (Alpha)
Auftragsposition: AuftragsNr (Alpha) und PositionsNr (Zahl)
Stückliste: AuftragsNr (Alpha), PositionsNr (Zahl) und LfdNr (Zahl)
Bestellung: Bestellnr (Zahl)
Bestellpos: Bestellnr (Zahl) und LfdNr (Zahl)
Kunden: KundenNr (Alpha)
Lieferanten: LieferantenNr (Alpha)
Personal: PersonalNr (Alpha)
Kostenstellen: KStNr (Zahl)
Zeitdatei: Datum (Datum), PersonalNr (Alpha), AuftragsNr (Alpha), PositionsNr (Zahl), KStNr (Zahl) und Zeitart (Zahl)
Arbeitszeit: Datum (Datum) und PersonalNr (Alpha)
Zeitauswertung: VonDatum (Datum), BisDatum (Datum) und PersonalNr (Alpha)

Neben den Schlüsseln wählen Sie dann die Datenfelder aus, die Sie für die eigentliche Informationsdarstellung benötigen. Je mehr Felder Sie auswählen, desto umfangreicher wird die Abfrage und desto länger dauert es, bis die Daten übertragen werden. Dies ist besonders bei Textfeldern, die Bezeichnungen enthalten der Fall. Zahlenfelder sind weniger aufwendig. Wählen Sie auch Textfelder aus, die mehrzeilige Texte enthalten, so werden Zeilenumbrüche leider nur als Sonderzeichen übertragen. Sie müssen daher in diesem Fall die Spaltendarstellung in Excel so korrigieren, dass die Spalte mit Zeilenumbruch dargestellt wird, Excel setzt dann eigene Zeilenumbrüche.

Schritt 3: Die richtigen Datensätze durch Vergleichs-Kriterien bestimmen

Nachdem Sie bestimmt haben, welche Datenfelder Sie sehen sollen, wählen Sie nun durch Kriterien aus, welche Datensätze Sie selektieren wollen. Je nach Feldtyp gibt es unterschiedliche Selektionsmöglichkeiten. In der obigen Feldliste haben wir die Feldtypen in Klammern hinter die Feldbezeichner gesetzt, so daß Sie anhand der nachfolgenden Beispiele für Selektionskriterien dann einfach bestimmen können, welches Kriterium für welches Feld passt:

Für alphanumerische Felder (Alpha) sind folgende Kriterien möglich:

Gleich: Der Feldinhalt muß mit der gesuchten Zeichenkette exakt übereinstimmen. Auf Groß/Kleinschreibung wird jedoch nicht geachtet, auch Umlaute werden oftmals wie die normalen Zeichen behandelt. Die Suche nach einem Feldwert „Müller“ findet also alle Datensätze, in denen das betreffende Feld Werte wie „müller“, „mUller“ oder „MÜLLER“ enthält.
Nicht Gleich: Das Gegenteil zu „Gleich“. Dieser Operator ist sinnvoll bei Feldern, die nur wenige unterschiedliche Werte enthalten.
Beginnt mit: Hier müssen lediglich Wortanfänge übereinstimmen. Die Suche nach „Hau“ liefert also alle Datensätze zurück, bei denen im befragten Feld Werte wie „Haus“, „haupt“ oder „HAU-RUCK“ vorkommen. Dieses Kriterium ist sehr praktisch für die Suche nach Aufträgen, die mit einer bestimmten Vornummer beginnen. Falls Sie dieses Vergleichskriterium nutzen, müssen Sie bei einer späteren Änderung der Abfrage den entsprechenden Vergleich neu erfassen, weil MS Query die Abfrage in diesem Punkt leider zerstört.
Beginnt nicht mit: Gerade das Gegenteil zu „beginnt mit“. Suche nach „Haus“ liefert Felder mit Werten wie „Hof“, „Tür“ etc.
Enthält: Bei diesem Vergleichsoperator darf der gesuchte Begriff irgendwo im fraglichen Feld stehen. Suchen Sie also beispielsweise nach „99“, so werden Datensätze mit Werten wie „A990117“ oder „990345“ oder „A2003499“ zurückgegeben. Falls Sie dieses Vergleichskriterium nutzen, müssen Sie bei einer späteren Änderung der Abfrage den entsprechenden Vergleich neu erfassen, weil MS Query die Abfrage in diesem Punkt leider zerstört.
Enthält nicht: Wieder ein Gegenteil. Sinnvoll zum Ausschluß spezieller Datensätze, wenn der Schlüssel systematisch aufgebaut ist und die auszuschließende Zeichenkette immer an einer bestimmten Stelle im Wert auftaucht. Haben Ihre Auftragsnummern einen Aufbau wie „A99-0117“ und Sie schließen „99“ aus, werden auch Aufträge wie „A98-0299“ ausgeschlossen! Falls Sie dieses Vergleichskriterium nutzen, müssen Sie bei einer späteren Änderung der Abfrage den entsprechenden Vergleich neu erfassen, weil MS Query die Abfrage in diesem Punkt leider zerstört.
Größer: Diese Suche ist, auf Zeichenketten angewendet, mit Vorsicht zu genießen. Eine „größer“-Suche nach „Müller“ findet z.B. Datensätze mit Feldwerten wie „Müllermann“, aber auch „Schmidt“, „Mustermann“ oder „Zbiginiew“ – nicht aber „Müller“ selbst.
Größer oder gleich: Größer oder gleich schließt den Suchbegriff mit in die Suche ein. Eine „größer“-Suche nach „Müller“ findet z.B. Datensätze mit Feldwerten wie „Müller“, aber auch „Schmidt“, „Mustermann“ oder „Zbiginiew“.
Kleiner: Hier gilt mit umgekehrten Vorzeichen das gleiche wie bei der Suche über „größer“. Suchen Sie über „kleiner“ nach „Mül“, so wird „May“, „Albrecht“ oder „Brechtmühl“ gefunden.
Kleiner oder gleich: Kleiner oder gleich ist wieder das Pendant zur Suche „größer oder gleich“. Suchen Sie über „kleiner oder gleich“ nach „Müller“, so wird „Müller“, „May“, „Albrecht“ oder „Brechtmühl“ gefunden.

Zahlen-, Datums- und Wahrheitsfelder

Für Zahlen- und Datumsfelder sind die Kriterien Gleich, Nicht Gleich, Größer, Kleiner, Größer oder gleich und Kleiner oder gleich in der offensichtlichen Art zu verwenden, für Felder mit Wahrheitswerten kann ein Vergleich mit den Kriterien Gleich oder Nicht gleich vorgenommmen werden.

Schritt 4: Sortieren

Nachdem Sie die richtigen Felder und die gewünschten Datensätze ermittelt haben, geht es an die richtige Reihenfolge der Datensätze. In vielen Fällen ist eine bestimmte Reihenfolge gewünscht, um Information vielleicht gruppieren zu können oder weil für bestimmte Abfragen innerhalb einer Tabelle eine bestimmte Reihenfolge vorausgesetzt wird.

Wenn Sie die Daten sortiert haben, sortieren Sie wieder feldweise. Oftmals wird die Sortierung genau dem Aufbau der Schlüssel der Dateien entsprechen, manchmal sind aber auch Sortierungen nach Feldern, die Namen oder bestimmte Zahlenwerte beinhalten sinnvoll. Eine Sortierung kann über mehrere Felder erfolgen, bei der Datei Auftragspositionen beispielsweise über die Auftragsnummer und die Positionsnummer.

Für eine Sortierung müssen Sie nicht nur in der linken Liste das gewünschte Feld wählen und doppelklicken, sondern dann auch noch die Richtung bestimmen, also aufsteigend, von A bis Z oder von kleinen Zahlen zu größeren, oder absteigend, eben in umgekehrter Richtung.

Schritt 5: Daten übernehmen

Haben Sie all diese Schritte ausgeführt, werden die Daten in die Excel-Tabelle übernommen. Je nach Umfang der gewählten Datensätze und der Felder dauert dies nun einige Augenblicke oder mehrere Sekunden. Bei der Übernahme werden Sie nach der linken oberen Zelle gefragt, aber die Daten in die Excel-Tabelle übernommen werden sollen. Es empfiehlt sich, jede Abfrage in einem eigenen Tabellenblatt abzulegen. Dann ist die spätere Erweiterung von Abfragen, sei es durch mehr Spalten oder durch mehr Datensätze, sehr einfach.

Verknüpfung von Dateien

Mit den vorangegangenen Schritten haben wir Datensätze aus einer Datei in eine Excel-Tabelle gebracht. Das ist schön und gut, bringt uns aber oft nicht weiter. Ein Beispiel: Aus den Projektpositionen können wir wunderbar nicht nur einfach Umsätze ermitteln, sondern diese auch, bei Handelsware oder Produkten, die wir selbst herstellen, nach Produkten gruppieren. Was uns aber wiederum fehlt, ist eine Zuordnung zum Kunden, dieser ist nur im Projekt zu finden. Auch die Artikelgruppe des Artikels, der in der Projektposition zu finden ist, steht nicht direkt dort, sondern nur im Artikelstamm. Wir müssen daher Daten aus verschiedenen Dateien verknüpfen können, um zur gewünschten Information zu gelangen. Wir führen das einmal am Beispiel von Projektpositionen und Artikeln durch. Wir wollen einerseits ermitteln, in welchen Warengruppen wir Umsätze erzielt haben und wollen dann sehen, wieviel Umsätze mit individuell erzeugten Möbeln und Dienstleistungen erzielt wurden. Dazu müssen wir eine Abfrage auf den Artikelstamm und auf die Projektpositionen durchführen. Oben wurde zwar schon grundsätzlich erklärt, wie das im Einzelnen funktioniert, wir sehen uns das aber nochmals im Schnelldurchlauf an, um auch die richtigen Daten für eine Abfrage zu erhalten.
Um mehrere Dateien gleichzeitig in einer Excel-Arbeitsmappe zu halten, nutzen wir die Möglichkeit, beliebig viele Tabellen in eine Arbeitsmappe einzufügen.

Schritt 1: Artikel abfragen

Eigentlich können Sie Ihren kompletten Artikelstamm einfach abfragen und in eine Excel-Tabelle laufen lassen, jedoch ist es in der Regel so, daß es eine ganze Reihe von Artikeln gibt, von denen Sie fest wissen, daß Sie sie nie direkt verkaufen werden, z.B. die per Datanorm importierten Artikel. Daher stellen Sie bei einer Abfrage sicher, daß der Inhalt des Felds «Quelle» gleich «0» ist. Außerdem benötigen wir von den Artikeln nur die Artikelnummer und die Artikelgruppe. Nachdem die Daten in Excel sind, sollten Sie der Einfachheit halber für den Bereich, in dem die Artikeldaten stehen, einen Namen definieren. Das macht uns später die Arbeit leichter, weil wir dann in diversen Formeln nicht auf die konkrete Größe eines Datenbereichs Rücksicht nehmen müssen. Rufen Sie dazu die Funktion «Name->Definieren» aus dem Menü «Einfügen» auf. Bezeichnen Sie den Datenbereich beispielsweise als «Artikeldaten». Als Zeilenbereich sollten Sie eine Zahl eingeben, die größer ist als die Anzahl der Datensätze, die eingefügt wurden. Dadurch müssen Sie nicht nach jeder Abfrage die Definition dieses Namens anpassen. Also zum Beispiel:
$A$1:$B$2000

Schritt 2: Projektpositionen abfragen

Wenn Sie jetzt die Datei «Auftragspositio» abfragen, um an die Projektpositionen zu kommen, sollten Sie folgendes sicherstellen:

Schränken Sie die Auftragsnummern testweise auf das aktuelle Jahr ein. Meist gelingt dies durch ein Kriterium der Art «beginnt mit».
Schränken Sie die Menge der zu selektierenden Positionen auf die ein, die bereits abgerechnet sind. Dies gelingt ihnen, indem das Feld Status auf den Wert «5» abgefragt wird.
Auch für diese Daten sollten Sie einen Namen definieren – das lohnt sich eigentlich immer.

Schritt 3: Verknüpfung mit SVERWEIS

Jetzt verknüpfen wir die beiden Tabellen miteinander. Bewegen Sie sich mit dem Cursor in die erste Datenzeile der Projektpositionen, gleich in die erste Zelle nach der letzten Spalte.

Tragen Sie folgendes als Formel ein:
<nowiki>=wenn(ISTNV(SVERWEIS(ArtNrZelle; Artikeldaten;2;0));"leer";SVERWEIS(aktuelle Zelle; Artikeldaten;2;0))</nowiki>

ArtNrZelle steht hierbei für die Zelle, in dieser Zeile die Artikelnummer zu finden ist.

Diese Formel vervollständigen Sie bitte nach unten, indem Sie die Zelle rechts unten doppelklicken.

Diese Formel schreibt in die gewählte Zelle entweder die Artikelgruppe zum Artikel in dieser Auftragsposition oder die Zeichenkette „leer„ wenn die Artikelnummer leer ist oder keine Artikelgruppe zum Artikel existiert.

Aufbereitung von Daten

Im vorangegangenen Abschnitt haben wir gesehen, wie Daten aus der Datenbank miteinander verknüpft werden, um an die gewünschte Information zu gelangen. Oftmals ist es damit aber nicht getan, weil die Menge der Datensätze einfach zu groß ist, um daraus relevante Information zu gewinnen. Natürlich kann durch die Summierung von Spalten schnell eine Summe über alle Werte gebildet werden, doch oftmals müssen nur bestimmte Teilmengen der Daten summiert werden. Oft sind auch nur zusammengefaßte Informationen wichtig und nicht jedes Detail. Doch auch hier bietet uns Excel viele Werkzeuge an, wenn wir sie nur richtig nutzen.

Filtern mit Hilfe des AutoFilters

Haben Sie eine Abfrage durchgeführt und sind die Daten erfolgreich in Excel, so können wir direkt eine sogenannte Filterung vornehmen. Beim Filtern werden für jede Spalte alle unterschiedlichen Werte ermittelt. Haben Sie beispielsweise alle Aufträge eines Jahres abgefragt, so können Sie nach der Filterung einfach alle Aufträge zu einer bestimmten Kundennummer anzeigen lassen. Oder Sie lassen sich alle Aufträge anzeigen, deren Nettovolumen größer als 8.000 Euro war.
Um die Filterung durchzuführen, klicken Sie in den Datenbereich der abfragten Daten und rufen aus dem Menü Daten->Filter den Befehl «AutoFilter» auf. Damit erscheint bei jeder Spaltenüberschrift ein kleines Menü in Form eines Dreiecks.

Verweise mit SVERWEIS(Quelle;Zielbereich;Spalte;Kriterium)

Findet zur Zelle «Quelle» im «Zielbereich», der aus mehreren Zeilen und Spalten besteht, in der Spalte «Spalte» den passenden Wert. Kriterium ist «0», wenn «Quelle» mit dem Vergleichswert exakt übereinstimmen soll, «1», wenn der Vergleichswert auch größer sein kann. «Quelle» wird immer mit der ersten Spalte von «Zielbereich» verglichen!

Gültigkeitsprüfung mit ISTNV(Wert)

Ist wahr, wenn der Wert eigentlich die Fehlermeldung #ISTNV ausgeben würde. #ISTNV wird immer dann ausgegeben, wenn ein gesuchter Wert nicht gefunden werden kann, was bei SVERWEIS ziemlich häufig passieren kann. Deshalb sollte man SVERWEIS immer mit einem ISTNV kombinieren, um mit definierten Ergebnissen weiterarbeiten zu können.

=== Teilsummen mit TEILERGEBNIS ermitteln ===

Die Excel-Funktion Teilergebnis ist sinnvoll, wenn Sie aus einer mit Autofilter zusammengefaßten Tabelle nur die Werte summieren, zählen oder anderweitig auswerten wollen, die sichtbar sind.

Erzeugung von Gruppierungen wie Quartal, Auftragsgruppen, Positionsgruppen

Sehr häufig wollen Sie Ergebnisse nicht für einzelne Termine, sondern für Monate, Quartale oder nach anderen Zusammenfassungskriterien ermitteln. In diesem Fall ist es sehr einfach, die per ODBC abgefragte Tabelle hinten um einige Spalten zu erweitern, die diese Zusammenfassung erzeugen. Die Besonderheit von Excel ist hierbei, daß die angehängten Spalten auch dann alle neu berechnet werden, wenn sich nach einer Aktualisierung der ODBC-Abfrage die Anzahl der Zeilen ändert.

Haben Sie erst einmal eine solche Gruppierung erzeugt, dann ist es sehr einfach, in Pivot-Tabellen wesentlich aussagekräftigere Auswertungen zu erzeugen.

Ermittlung von Monat, Quartal oder Jahr

Praktischerweise kennt Excel einfache Funktionen, um aus dem Datum, das in einem Datensatz vorkommt, das gewünschte Ergebnis zu extrahieren. Mit den Funktionen

Tag(Datum)
Wochentag(Datum)
Monat(Datum)
Jahr(Datum)

erhalten Sie den Tag im Monat, den Wochentag von 1-7, den Monat im Jahr oder das Kalenderjahr des übergebenen Datums.

Das Quartal erhalten Sie mit etwas mehr Arbeit - Sie müssen nur in einer relativ langen Wenn-Kette abfragen, ob der Monat 1-3, 4-6, 7-9 oder 10-12 ist.

Andere Gruppierungen erzeugen

Mit Hilfe des Befehls SVERWEIS können Sie, wie bereits dokumentiert, Felder aus anderen per ODBC in Excel eingelesenen Dateien ziehen. Dies stellt eine Möglichkeit dar, beispielsweise die Mitabeitergruppe aus dem Personalstamm zu ziehen.

Alternativ können Sie auch anhand von Werten in einem Datensatz selbst eine Gruppierung vornehmen, z.B. indem Sie Verkaufspreise unterscheiden in Kleinumsatz oder Großumsatz. Hierfür verwenden Sie wieder die Wenn-Funktion von Excel.

Bedingte Summierung mit SUMMEWENN

Auch sehr hilfreich ist die Excel-Funktion SUMMEWENN, die eine bedingte Summierung vornimmt. Hierbei wird über die Syntax

SUMMEWENN(Kriterium;Kriterienbereich;Summierungsbereich)

definert, daß alle Zellen, in deren Zeile das Kriterium gefunden wird, summiert werden. Referenz dafür bildet der Kriterienbereich, in dem wird das Kriterium gesucht.

Die Hilfe von Excel erläutert dies im Detail.

Pivot-Tabellen

Pivot-Tabellen sind ein Instrument, um große Datenmengen zu verdichten. Eine Pivot-Tabelle besteht aus einem Datenbereich links und einem Summenbereich rechts.

Ein Beispiel:
Sie haben eine Tabelle aller abgerechneten Projektpositionen des Jahres 2006 und wollen wissen, wieviel Sie von jedem Artikel verkauft haben, sowohl in Stück, als auch im Umsatz. Dazu erzeugen Sie ine Pivot-Tabelle, ziehen die Artikelnummer in den Datenbereich und die Felder Anzahl und VKGesamt in den Summenbereich.
Eine Verfeinerung erfährt die Angelegenheit, wenn sie nun per SVERWEIS das Rechnungsdatum in die Positionstabelle hereinziehen, dieses dann auf den Monat verdichten und so eine monatsweise Umsatzstatisti erzeugen. Dies passiert ganz einfach, indem Sie dann den Monat als zweite Spalte in den Datenbereich der Pivot-Tabelle ziehen.

Pivot-Tabellen erzeugen

Um eine Pivot-Tabelle zu erzeugen, markieren Sie einfach eine Zelle in der zu verdichtenden Tabelle und rufen in Excel den Befehl '''Pivot-Tabelle...''' aus dem Menü '''Daten''' auf. Folgen Sie dann den Assistenten und ziehen Sie die erwähnten Spalten entweder in den linken Bereich oder in den Summenbereich.

Formatierung von Pivot-Tabellen

Am einfachsten formatieren Sie Pivot-Tabellen, indem Sie den Formatierungsassistent der Pivot-Tabelle aufrufen. Dort werden Ihnen diverse Musterformatierungen aufgezeigt, die alle eine optisch ansprechende und gleichzeitig kompakte Formatierung liefern.

Ändern der Abfrageeinstellungen

Haben Sie erst einmal eine Tabelle mit abgefragten Daten erzeugt, ist es sinnvoll, die Einstellungen der abgefragten Daten zu ändern, damit die Daten in Zukunft einfacher und so wie gewünscht in die Tabelle eingesetzt werden. Dazu rufen Sie das Icon für die Abfrageeinstellungen
auf, das genau dann in der Symbolleiste '''Externe Daten''' aktiv ist, wenn Sie eine Zelle markiert haben, die in abgefragten Daten steht.

Zuerst einmal lohnt es sich, die Option '''Kennwort speichern''' zu aktivieren. Damit werden Sie nicht immer nach dem Paßwort gefragt. Das klappt leider nicht, wenn Sie für den Benutzeraccount ein Paßwort definiert haben.

Dann stellen Sie weiterhin ein, daß '''Bestehende Zellinhalte überschreiben, nicht verwendete Zellen löschen''' aktiv ist. Damit wird verhindert, daß beim Erweitern einer Abfrage oder bei der Neugenerierung aus einer Datei mit vorhandenen Daten diese durch die Abfrage verschoben werden. Es wird dann immer an Ort und Stelle ersetzt.
'''Formeln in angrenzenden Zellen ausfüllen''' sollte auch immer aktiv sein.

Automatisch aktualisierte Abfragen

Wenn Sie wünschen, daß die Tabelle automatisch immer die neuesten Daten enthält, sollten Sie die Option A'''ktualisieren beim Öffnen der Datei''' aktivieren. Sie können dann sogar noch wählen, daß die Daten alle x Minuten neu berechnet werden - nett für eine Livedarstellung der Vorgänge im Betrieb, aber in der Praxis eher eine Spielerei.

'''Beachten Sie, daß bei jeder Datenaktualisierung kurzfristig eine Client-Verbindung benötigt wird. Eine ODBC-Verbindung wird gerechnet wie ein Arbeitsplatz, der sich kurzfristig bei profacto anmeldet.'''

ODBC-Auswertungen für einen anderen Server aufbereiten

Ein großer Nachteil der mit Excel erzeugten ODBC-Auswertungen ist, daß diese intern fest an seinen Server gebunden sind. Es wird nicht alleine der Name der DSN in der Excel-Datei gespeichert, sondern auch die physikalische IP-Adresse des Servers. Wenn Sie daher einmal einen neuen Rechner als Server verwenden und dieser eine andere IP-Adresse hat, funktionieren Ihre ODBC-Auswertungen nicht mehr.

Doch seit Excel 2003 gibt es eine Abhilfe hierzu. Seit dieser Version können Sie eine Arbeitsmappe auch als XML-Kalkulationstabelle abspeichern. Damit wird die komplette Tabelle in eine XML-Datei gesichert und kann dann sehr einfach mit einem Texteditor bearbeitet werden.

Öffnen Sie die XML-Datei mit Wordpad und suchen Sie mit dem Befehl "Finden" nach der bisher verwendeten IP-Adresse, z.B. "192.168.1.1". Ersetzen Sie diese durch die Adresse des neuen Servers, z.B. "192.168.1.100". Nun können Sie die XML-Tabelle wieder in Excel laden und erneut als normale Excel-Datei speichern. Der einzige Wermutstropfen ist, daß Sie nun bei Aktualisieren der Abfrage einmalig die Benutzernamen und Paßwortinformationen neu eintragen müssen.

Browser nicht unterstützt