Microsoft Excel: Projektformulare mit Datenarchivierung

Microsoft Excel bietet gute Möglichkeiten, um Daten mit Hilfe von Formularen einheitlich zu erfassen. Oft müssen diese Daten allerdings später umständlich manuell gesammelt und zusammengefasst werden, um sie auswerten zu können. Das Problem lässt sich elegant lösen, indem man die dezentral erfassten Daten automatisch in einer zentralen Datenbank, z.B. in einer Microsoft Excel-Tabelle speichert. Wie das geht, zeigt Tanja Kuhn in diesem Beitrag. Am Beispiel einer monatlichen Projektzeiten-Abrechnung für Mitarbeiter erfahren Sie, wie Sie eine gute Vorlage in Microsoft Excel aufbauen und die erfassten Daten für spätere statistische Auswertungen in einer Datenbank sammeln können. Die Autorin führt Sie dabei Schritt für Schritt durch den Erstellungsprozess und macht Sie auf Fallen und Hindernisse aufmerksam.

Microsoft Excel: Projektformulare mit Datenarchivierung

Microsoft Excel bietet gute Möglichkeiten, um Daten mit Hilfe von Formularen einheitlich zu erfassen. Oft müssen diese Daten allerdings später umständlich manuell gesammelt und zusammengefasst werden, um sie auswerten zu können. Das Problem lässt sich elegant lösen, indem man die dezentral erfassten Daten automatisch in einer zentralen Datenbank, z.B. in einer Microsoft Excel-Tabelle speichert. Wie das geht, zeigt Tanja Kuhn in diesem Beitrag. Am Beispiel einer monatlichen Projektzeiten-Abrechnung für Mitarbeiter erfahren Sie, wie Sie eine gute Vorlage in Microsoft Excel aufbauen und die erfassten Daten für spätere statistische Auswertungen in einer Datenbank sammeln können. Die Autorin führt Sie dabei Schritt für Schritt durch den Erstellungsprozess und macht Sie auf Fallen und Hindernisse aufmerksam.

Microsoft Excel bietet gute Möglichkeiten, Daten mit Formularen (Mustervorlagen) standardisiert und einheitlich zu erfassen. Komplexer ist der Weg, wenn Sie Daten aus mehreren Tabellenblättern und Arbeitsmappen konsolidieren wollen. In diesem Artikel erfahren Sie, wie Sie eine gute Vorlage in Excel aufbauen und die erfassten Daten für spätere statistische Auswertungen in einer Datenbank sammeln können.

Ausgangslage

Jeden Monat stellten sich der Firma Meier AG die gleichen Hürden, wenn es um die Erfassung der Arbeitszeiten ging. Jeder Mitarbeiter erfasste während des Monats seine Projektzeiten mit Hilfe einer Vorlage. Am Ende eines Monats wurden diese Zeiten an einer zentralen Stelle manuell konsolidiert, um die Rechnungen schreiben zu können. Anschließend bekam die Betriebsbuchhaltung die konsolidierten Zeiten zur Buchung.

Die Projektdaten sämtlicher Mitarbeiter zusammenzutragen, war immer mühsam und zeitintensiv. Der Kleinbetrieb scheute jedoch die Kosten für ein komplexes Projekterfassungs- und Controlling-System.

Lösungsansatz

Eine Excel-Datei bildet die Basis für den vorgestellten Lösungsansatz. Aus ihr erstellen wir eine Excel-Vorlage und eine Datenbank, in die sämtliche Daten aller Mitarbeiter pro Projekt automatisch eingetragen werden. Die Konsolidierung erfolgt aufgrund der Datenbankdaten "per Knopfdruck". Zudem besteht die Möglichkeit, die Daten über eine Schnittstelle direkt dem Programm für die Betriebsbuchhaltung zu übergeben.

Aufbau der Vorlage am Beispiel der Leistungserfassung

Als Fallbeispiel dient die Leistungserfassung. Wir erarbeiten dazu eine zentrale Vorlage für alle Mitarbeiter. Jeder von ihnen ruft pro Projekt und Monat eine solche Vorlage ab, um seine Zeiten einzutragen. Sobald er seine Datei speichert, schreibt das System die Daten automatisch in eine Datenbank. Spätere Änderungen der Daten werden in der Datenbank nachgetragen.

Zudem sollen die einzelnen Stunden zusammengefasst und monatlich ein Bericht über die Gesamtkosten der verschiedenen Projekte erstellt werden. Das klingt nach mühsamer Kleinarbeit - ist es aber nicht, wie Sie gleich sehen werden.

In Bild 1 ist eine Excel-Vorlage für die Leistungserfassung abgebildet. In diesem Formular sind zum besseren Verständnis bereits einige Daten eingetragen. Die Stunden berechnet das Programm automatisch.

Bild 1: Vorlage für Leistungserfassung mit Kopplung an eine Datenbank.

Vorlage erstellen und speichern

  • Legen Sie eine neue Excel-Datei an. Das erste Tabellenblatt nennen wir "Leistungserfassung", das zweite "Hilfstabellen". Mit Doppelklick auf den Tabellenblatt-Namen können Sie diesen ändern.
  • Erstellen Sie die Tabelle gemäß Bild 2 auf dem Register "Leistungserfassung".

Bild 2: Layout für die Leistungserfassung erstellen.

  • In der Zelle F11 erfassen Sie die Formel gemäß Bild 2. Diese rechnet die Stunden aus. Durch Multiplikation mit 24 erhalten Sie die Stunden und Minuten in Industriestunden (Refa).
  • In Zelle I5 erfassen Sie folgende Formel: =SUMMEWENN($B$11:$B$38;H5;$F$11:$F$38). Kopieren Sie diese für die restlichen Tätigkeiten in den Bereich I6:I10. Mit ihrer Hilfe erhalten Sie eine Zusammenfassung der Stunden pro Tätigkeit. Diese Daten werden später in die Datenbank übertragen.
  • Bilden Sie in den Zellen F40 + I11 die Summe der "Total Stunden".
  • Da Sie Ihre Vorlage mit Dropdown-Elementen (Kombinationsfelder) bestücken wollen, brauchen Sie noch diverse Hilfstabellen, auf die Sie verweisen können. Erstellen Sie die Tabellen gemäß Bild 3 auf dem Register "Hilfstabellen".
    Hinweis: Mit der "Verketten"-Funktion können Sie Informationen aus mehreren verschiedenen Zellen zusammenhängen. Erstellen Sie in den Zellen D3 und H3 die entsprechenden Formeln und kopieren Sie diese innerhalb des grünen Bereichs.

Bild 3: Hilfstabellen für Dropdown.

Namen zuweisen

Den grünen Bereichen weisen Sie Namen zu. Diese benötigen Sie später, um die Gültigkeit zu definieren. Markieren Sie den grünen Bereich (z.B. D3:D12) und geben Sie im Menü Einfügen / Name / Definieren einen Namen ein (z.B. Drop_Mitarbeiter), siehe Bild 3. Wiederholen Sie das für jeden Bereich.

Namen für Bereiche bieten den Vorteil, dass Sie später bei Änderungen (z.B. neue Mitarbeiter oder Projekte) nur den Namensbereich, nicht aber Formeln anpassen müssen. Zudem verbessern sie die Lesbarkeit von Formeln.

Gültigkeiten

Damit die Nutzer nur sinnvolle Eingaben machen können, versehen wir diverse Zellen mit dem Befehl "Gültigkeit". Wenn Sie diesen Befehl einsetzen, kann nur mit Hilfe von Namensbereichen auf andere Register, in unserem Fall "Hilfstabellen", zugegriffen werden.

Datum

  • Markieren Sie auf dem Register "Leistungserfassung" den Bereich A11:A38 und wählen Sie den Befehl "Gültigkeit" aus dem Menü Daten.
  • Hier definieren Sie, dass der Benutzer in diesem Bereich nur ein Datum eingeben kann und dieses nicht mehr als 31 Tage zurückliegen darf. Das bedeutet, dass sich bei alten Leistungserfassungen das Datum später nicht mehr ändern lässt. Stellen Sie Ihren Dialog gemäß Bild 4 ein.

    Bild 4: Gültigkeitsprüfung für das Datum.

Bewertungen und Kommentare

(nur angemeldete Benutzer)

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
Gesamt
Bewertungen 0
Alle anzeigen