Daten auswerten

1.Daten speichern

Hinweise

Eine Karteikarte speichert Personen-Daten in einem Datensatz:

Für einen Rechner muss dieses Modell angepasst werden. Datenbanken in EXCEL stellen spaltenweise angeordnete Datenmodelle dar, die in den Spalten 'sortenreine' Daten-(Felder) aufweisen (alle sind vom gleichen Datentyp - Text, Ganzzahl, Zahl, Datum, logischer Wert). In einer Zeile steht ein Datensatz (zusammengehörige Daten z.B. auf einer Karteikarte).
Die Fakturierung enthält 3 Datenbanktabellen RechnungsAusgang, Artikel und Kunden. Die Daten aus Artikel und Kunden werden in einer Rechnung (Datenbankformular) zusammengestellt. Die Daten der Rechnung werden in RechnungsAusgang gespeichert.


Übung

Studieren sie das Fakturierungsmodell!
  • Rechnung schreiben:
    Kunden(Nummer) eintragen und Artikel(nummern) erfassen
  • Buchen & Drucken
  • Führen sie mehrere Buchungsvorgänge aus
  • Die Anwendung ist VBA-Makro gesteuert (Makros freischalten)

2.Filtern und Abfragen

Hinweise

    Abfragen sind das Filtern von Daten.
    Grundsätzlich gibt es eine Direktauswahl per Klick mit Auswahl-Checkbox (links) oder sie verwenden vorgefertigte Filterschablonen (rechts).
  • Textfilter
  • Zahlenfilter
  • Datumsfilter

Aufgaben

Fiterbedingungen Tabelle
  • Adressen {FR = FT} - {ANREDE = 1 (Frauen)} - {PLZ √ Beginnt mit .... 9} 39
  • Adressen {NAME √ Endet mit ... mann} - {KURS √ >=20 und <=39} 497 Zahlwerte
  • Adressen {GEDDAT √ Nach... (jünger als 30 {Gebdat nach 1985}) 20
  • Produktionsübersicht {Tag = März} - Art = Grill 7
  • Produktionsübersicht {Menge < 500} - Tag = von 31.3 bis 4.4 11
  • Überlegen sie welche Filter in den unterschiedlichen Tabellen eine sinnvolle Datenauswahl abgeben!
  • Berechnen sie aussagkräftige TEILERGEBNISse über die Filter!
Funktion Teilergebnis
TEILERGEBNIS(Funktion; ZellenBereich; [Zellenbereiche...])
Berechnet die mit dem Code (102 ≈ ANZAHL 103 ≈ ANZAHL2 104 ≈ MAX 105 ≈ MIN 106 ≈ PRODUKT 107 ≈ STABW 108 ≈ STABWN 109 ≈ SUMME 110 ≈ VARIANZ 111 ≈ VARIANZEN ) benannte Funktion und ignoriert ausgeblendete Werte. SIEHE OH!

In den grauen Buttons wird die zu erwartende Zahl an Filter-Datensätzen angegeben. Daten-Typen von GEBDAT und PLZ beachten - ggf. einen numerischen bzw. Datumswert herstellen! (siehe Button 'Problem Datumsfilter')

3.Spezialfilter

Hinweise

Der Spezialfilter erweitert die Abfrage um wesentliche Eigenschaften:
  • umfangreichere Filterbedingungen (mehr als 2 Kriterien)
  • Berechnete Filterkriterien
  • Das Filterergebnis in eine andere Tabelle kopieren
  • Das Filterergebnis um Dubletten bereinigen (doppelte Datensätze entfernen)
  • Joker: * = beliebige Zeichenkette, ? = beliebiges Zeichen
  • Der rote Datenbank-Bereich wird gefiltert.
    Die blauen Kriterien-Bereiche enthalten die Filterbedingungen (4 Beispiele).
    In einer Kriterien-Spalte stehen ODER Kriterien:
    die verschiedenen Schreibweisen von Mayer, Maier, Meyer Meier
    In einer Kriterien-Zeile stehen UND Kriterien:
    Name endet auf *mann UND Vorname Beginnt mit Alex*
    ODER Name endet auf *bach UND Vorname beliebig.
    Mit der Option 'an andere Stelle kopieren' geben sie einen Ausgabe-Bereich an, der die auszugebenden Spalten des Datenbereiches enthält.

    Aufgaben

    Bearbeiten sie die Aufgaben Spezialfilter (VerkaufFilter.xlsx)
    • Wenden sie die in obigem Bild angegebenen Filter auf die Tabelle Spezialfilter an und untersuchen sie das Filter-Ergebnis.
    • Bearbeiten sie das Tabellenblatt
    • Filtern Sie die Tabelle , dass alle Bestellungen von Kunden die den Begriff “Küchen” im Namen führen und gleichzeitig die Bestellungen aller Kunden, die per Kurier beliefert wurden, herausgefiltert werden.
    • Richten sie einen Ausgabebereich für die Spalten Kunde, KD NUMMER, BST DATUM, BST WERT, VERSAND ein.


    4.Kreuz oder Pivot-Tabellen

    Hinweis

    Pivot-Tabellen oder Kreuztabellen sind ein leistungsfähigeres Instrument zur Datenanalyse und Datenauswertung. Mit der Funktion Pivot-Tabellen(XL) oder Datenpilot(Calc) können wir etwas Statistik betreiben und die Daten nach verschiedenen Gesichtspunkten gruppieren, zusammenstellen und einfache Berechnungen (Summen, Durchschnitte, Zählen) anstellen.

    Wir sind z.B. daran interessiert, die Mengen pro Produkt zusammenzufassen und zu vergleichen.
    D.h. wir wollen aus der Produktionsübersicht der Mappe Datenbanken die Menge eines jeden Produktes aufaddieren.

    Aufgabe

    Gehen sie die Datentabellen in 'Beispiel Datenbanken' durch und überlegen sie welche Auswertungen sie über Pivot-Tabellen erstellen können.
    Erklären sie mit eigenen Worten welche Auswertungen die im Folgenden beschriebenen Pivot-Tabellen darstellen.

    5.Relationale Datenbanken

    Hinweise

    Aus dem Aufbau eines Datenbanksystems ergeben sich vorteilhafte Eigenschaften, die gleichzeitig Kriterien für ein gutes DBS sind, z.B.:
    • Integritätssicherung,
      Daten werden auf Korrektheit (bereits während der Eingabe) überprüft und Fehlmanipulationen verhindert Redundanzarmut, es gibt keine ungeordnete Mehrfachspeicherung von Datenwerten
    • Datensicherheit,
      ungewollter Datenverlust wird durch interne Backup- und Prüfmechanismen verhindert
    • Datenschutz,
      Zugriffskontrolle und spezifische Sichten sorgen für einen Zugang gemäß der Rechte des Nutzers
    • Mehrbenutzerbetrieb,
      viele Benutzer können parallel auf der Datenbank arbeiten

    Aufgaben

    Für die Arbeit mit Excel ist das von Bedeutung, wenn wir Daten, z.B. aus dem betrieblichen Umfeld, aus einer Datenbank übernehmen, was hier am Beispiel einer MS Access Datenbank Datei gezeigt werden soll.
    • Machen sie sich anhand der Einführung mit den Datenbank-Begriffen bekannt.
    • Verwenden sie Access oder Base um die Datenbank-Datei Adressen.mdb zu öffnen und erstellen sie die angesprochene Abfrage 'qry Kundenliste'. Z.B. mit LO Base
    • Übernehmen sie über 'Externe Daten abrufen' die beiden Datenzusammenstellungen:
    • Kundenliste mit korrekter Anrede und Wohnort
    • Kunden mit akademischen Titel
    • in eine Tabelle der Tabellenkalkulation
    • oder MS Query



    'MS Query Abfragen (Ersetze SPEICHERORT durch Pfad zur Datei) siehe Kapitel SQL'

    SELECT
    Kunden.KNr,
    Anrede.Titel,
    Kunden.Anrede,
    Kunden.Vorname,
    Kunden.Name,
    Kunden.PLZ,
    PLZDaten.Ort
    FROM
    `SPEICHERORT\Adressen.mdb`.Anrede Anrede,
    `SPEICHERORT`.Kunden Kunden,
    `SPEICHERORT\Adressen.mdb`.PLZDaten PLZDaten
    WHERE Kunden.PLZ = PLZDaten.PLZ
    AND Kunden.Anrede = Anrede.AnredeNr
    AND ((Anrede.Titel Like '%Dr.') OR (Anrede.Titel Like '%Dipl.%'))

    ...
    WHERE
    Kunden.PLZ = PLZDaten.PLZ
    AND Kunden.Anrede = Anrede.AnredeNr
    AND (
        (Anrede.Titel Like '%Dr.') AND (PLZDaten.Ort='Karlsruhe')
    OR  (Anrede.Titel Like '%Dipl.%') AND (PLZDaten.Ort='Karlsruhe')
    )

    SELECT
    Kunden.KNr, Kunden.Name, Sum(Verkauf.Umsatz), Count(Kunden.KNr) AS 'Gesamt'
    FROM
    `SPEICHERORT\Adressen.mdb`.Anrede Anrede,
    `SPEICHERORT\Adressen.mdb`.Kunden Kunden,
    `SPEICHERORT\Adressen.mdb`.PLZDaten PLZDaten,
    `SPEICHERORT\Adressen.mdb`.Verkauf Verkauf
    WHERE
    Kunden.PLZ = PLZDaten.PLZ
    AND Kunden.Anrede = Anrede.AnredeNr
    AND Kunden.KNr = Verkauf.KNr
    GROUP BY Kunden.KNr, Kunden.Name


    SELECT
    Verkauf.Verkaufsdatum, Sum(Verkauf.Umsatz), Count(Kunden.KNr) AS 'Gesamt'
    FROM
    `SPEICHERORT\Adressen.mdb`.Anrede Anrede,
    `SPEICHERORT\Adressen.mdb`.Kunden Kunden,
    `SPEICHERORT\Adressen.mdb`.PLZDaten PLZDaten,
    `SPEICHERORT\Adressen.mdb`.Verkauf Verkauf
    WHERE
    Kunden.PLZ = PLZDaten.PLZ
    AND Kunden.Anrede = Anrede.AnredeNr
    AND Kunden.KNr = Verkauf.KNr
    GROUP BY Verkauf.Verkaufsdatum

    6.SQL - mit Datenbanken sprechen

    Einführung

    Mit Hilfe von SQL (Structured Query Language, Strukturierte Abfragesprache) können sie relationale Datenbanken, abfragen, aktualisieren und verwalten. Befehlsstruktur
      SELECT Feldliste
      FROM Tabellennamen IN Datenbankname
      WHERE Suchbedingungen
      GROUP BY Feldliste
      HAVING Suchbedingungen
      ORDER BY Feldliste
    SELECT gibt an, welche Felder sie in die Liste aufnehmen möchten. Mit Hilfe der Klausel FROM geben sie an, in welchen Tabellen diese Felder zu finden sind. SELECT ist normalerweise das erste Wort in einer SQL-Abfrage. Wenn sie mehr als ein Feld angeben, müssen sie die Feldnamen durch Kommas voneinander trennen. Listen sie die Felder in der Reihenfolge auf, in der sie angezeigt werden sollen. Wenn ein Feldname in mehr als einer der in der Klausel FROM aufgelisteten Tabellen enthalten ist, geben sie vor dem Feldnamen den Tabellennamen und den Operator . (Punkt) an. Mit dem Joker (ein Sternchen *) können Sie alle Felder in einer Tabelle wählen.

    Access-Daten bearbeiten mit MDBPlus

    Laden sie die Anwendung MDBPlus und die Access-Datenbank Adressen.mdb des Kapitels 'Relationale Datenbanken' auf ihren Rechner und öffnen sie die Adressen-Datenbank. Im Gegensatz zu MS Query können sie mit MDBPlus auch Daten ändern - machen sie einem Duzend Kunden die Freude und verpassen ihnen einen akademischen Titel....





    Diese Beispiele zeigen beispielhaft die Anwendung von SQL zur Erzeugung einer Abfrage wie wir sie im Kapitel 'Relationale Datenbanken' zum Daten-Import in die Tabellenkalkulation aufgestellt haben. Letztendlich kommunizieren alle Datenbankanwendung mit SQL (oder einer anderen Sprache) um Datenbanken zu lesen, zu erzeugen, zu verwalten oder zu manipulieren.

    Importieren sie 'qry Kundenliste' in die Tabellenkalkulation und erstellen sie dort die Abfragen und Pivot-Auswertung wie die in 'Codebook-Eintrag' hinterlegten SQL-Abfragen.