Entwicklung Excel-basierter Mausklicksysteme

Heute gibt es eine Neuerung auf meinem Blog. Ich habe Ernst Fischer als Gastautor gewinnen können. Er stellt sich und sein Konzept zur Verarbeitung großer Datenmengen mit Excel gleich selbst vor.

Der Beitrag gliedert sich in zwei wesentliche Teile. Im ersten Teil beschreibt der Autor ein Praxisbeispiel inkl. eines selbst entwickelten Makros, der zweite Teil enthält einen Fachbeitrag, in dem er seinen Ansatz grundsätzlich erläutert.

Vielen Dank an Ernst Fischer für diesen interessanten Beitrag.

Zum Beitrag gehört eine Beispieldatei in .xlsx (am Ende des Praxisbeispiels). Lade sie Dir bei Interesse herunter und speichere sie unter .xlsm. Gehe mit Alt + F11 in den VBA-Editor, wähle dort unter Einfügen „Modul“ und füge dort über Kopieren und Einfügen das Makro „Sub Neuberechnung()“ ein. Verlasse den Editor mit Alt + Q. Jetzt kannst Du durch Anklicken des Buttons „Neuberechnung“ das Makro starten.

1. Praxisbeispiel

Zur Person:

Ich bin Industriemeister und technischer Betriebswirt, war bis zum 30. Lebensjahr in der Lebensmittelproduktion tätig, danach Studium, dann 8 Jahre Kostenrechner – nennen sich heute Controller – dann 8 Jahre in Beratungsfirmen, seit 1990 freiberuflicher Unternehmensberater.
Kernkompetenzen: Wirtschaftliche Gutachten, Excel für große Datenmengen.

Ein Praxisbeispiel:

1980: Ein Lebensmittelhersteller hat ein Sortiment mit 3.500 Artikelnummern. Im Betriebsbüro sitzt ein Weltmeister im Zahlentippen für die Statistik. Die EDV-Abteilung hat keine gute Lösung zu bieten.

1988: Mit PC plus Tabellenkalkulation hat sich eine neue Türe geöffnet: die vorhandene Struktur 1:1 in den PC übertragen und so automatisieren, dass auf Mausklick die Betriebsdaten einfließen und die gesamte Rechenarbeit und Darstellung automatisiert ist. Das sind dann 15 Minuten pro Tag anstelle des bisherigen Vollzeitjobs.
Zum Beitrag:
Ohne Makros ist Excel so gut wie nichts. Die üblichen Anleitungen sind für Einsteiger schwer verständlich, und taugen nicht für große Datenmengen. Stell dir vor, Du hast 5.000 Artikelnummern und monatlich 200.000 Datensätze aus der Produktion mit den Tagesmengen in kg je Artikel. Die Kostenrechnung liefert die Kostensätze je Artikel in €/kg. Der Chef will monatlich eine Aufstellung der Mengen und Kosten je Artikel. Eine Banalität, oder?
Die Herausforderung:
Mit SummeWenn, SVerweis und Zelle-für-Zelle-Schleifenabfragen kommst Du nicht weit. Dann heißt es gleich „mit Excel geht das nicht“. Geht aber doch, und zwar in 5 Sekunden. Das Geheimnis ist, aus den unzähligen Excel-Funktionen die besonders schnellen zu nutzen. Schnelle Routinen sind Sortieren, Kopieren, einfache Wenn-Abfragen. Die beschriebene Lösung ist im Aufzeichnungsmodus, damit es im Schrittmodus nachvollziehbar ist und ohne Variablendeklarationen, weil diese keinen relevanten Geschwindigkeitseffekt zeigen. Die Länge der Makros ist ebenfalls unerheblich.

 

Schritt-Anleitung:

Schritt 1: Neue Datei anlegen

Öffne eine neue Excel-Datei und speichere diese unter einem beliebigen Namen als Excel-Datei mit Makros. Bestücke die Datei mit den Blättern
Auswertung Mengen Kosten Ueb Formel

Schritt 2: Formeln hinterlegen

Hinterlege im Blatt Formel:
H11: =WENN(C11<>C12;1;“a“)
I11: =WENN(H11=1;C11;0)
J11: =WENN(H11=1;D11;0)
K11: =WENN(C11=C10;K10+E11;E11)
O11: =WENN(UND(G11<>““;I11=I12;L12<>0);L12;0)
P11: =WENN(UND(K11<>0;O11<>0);RUNDEN(K11*O11;2);0)

Schritt 3: Blatt Auswertung bestücken

Bestücke das Blatt Auswertung mit folgenden Elementen:

EFischer1

Lege in E7 und G7 je eine Summenformel für die Zeilen 11-5010 an.
Formatiere C11-G12.
Kopiere C12:G12 in das Blatt Formel nach I25.
Lege oben eine Schaltfläche an und verknüpfe diese später auf das Makro Neuberechnung.

Schritt 4: Blatt Mengen bestücken

Bestücke das Blatt Mengen mit folgenden Überschriften:

EFischer2

Kopiere Deine Betriebsdaten ab Zeile 11 ein (oder setze Beispielsdaten ein).
Die Spalte C kann auch leer bleiben.

Schritt 4: Blatt Kosten bestücken

Bestücke das Blatt Kosten mit folgenden Überschriften:

EFischer3

Kopiere die Kostensätze ein (oder setze Beispielsdaten ein).

Schritt 5: Makros erstellen

Kopiere folgendes Makro in ein VBA-Modulblatt ein:
Sub Neuberechnung()
Application.ScreenUpdating = False
Application.Calculation = xlAutomatic
‚Überleitung löschen (Sicherheit):
Sheets(„Ueb“).Select
ActiveSheet.UsedRange.Select
Selection.Clear
Application.CutCopyMode = False
Range(„A1“).Select
‚Stand löschen:
Sheets(„Auswertung“).Select
ActiveSheet.Unprotect
ActiveWindow.FreezePanes = False
Range(„C11:G5010“).Select
Selection.Clear
Application.CutCopyMode = False
Application.ScreenUpdating = True
Range(„C11“).Select
Application.ScreenUpdating = False
‚Mengen summieren:
Sheets(„Mengen“).Select
Range(„D11:F200010“).Select
Selection.Copy
Range(„A1“).Select
Sheets(„Ueb“).Select
Range(„C11“).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.Sort Key1:=Range(„C11“), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.CutCopyMode = False
‚Einfügeblock:
Range(„C11:C200010“).Select
Selection.Copy
Range(„H11:K11“).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
‚Summieren:
Sheets(„Formel“).Select
Range(„H11:K11“).Select
Selection.Copy
Sheets(„Ueb“).Select
Range(„H11“).Select
Selection.CurrentRegion.Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Selection.Sort Key1:=Range(„H11“), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.CutCopyMode = False
‚Blöcke trennen:
Range(„H10“).Select
ActiveCell.FormulaR1C1 = „a“
Range(„H10:H200010“).Select
Selection.SpecialCells(xlCellTypeConstants, 2).Select
Selection.ClearContents
Application.CutCopyMode = False
Range(„H300000“).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Select
Selection.Clear
Application.CutCopyMode = False
ActiveCell.Offset(1, 2).Select
Selection.CurrentRegion.Select
Selection.Clear
Application.CutCopyMode = False
ActiveCell.Offset(0, 6).Select
Selection.CurrentRegion.Select
Selection.Clear
Application.CutCopyMode = False
Range(„C11“).Select
Selection.CurrentRegion.Select
Selection.Clear
Application.CutCopyMode = False
Range(„H11:H5010“).Select
Selection.Clear
Application.CutCopyMode = False
‚Block benummern:
Range(„I11:I5010“).Select
Selection.Copy
Range(„G11“).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range(„G11“).Select
ActiveCell.FormulaR1C1 = „1“
Selection.CurrentRegion.Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
Application.CutCopyMode = False
‚Kostensätze darunter:
Sheets(„Kosten“).Select
Range(„C11:C5010“).Select
Selection.Copy
Sheets(„Ueb“).Select
Range(„I200011“).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets(„Kosten“).Select
Range(„E11:E5010“).Select
Selection.Copy
Range(„A1“).Select
Sheets(„Ueb“).Select
Range(„L200011“).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
‚Beide Blöcke sortieren:
Range(„G11:L205010“).Select
Selection.Sort Key1:=Range(„I11“), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.CutCopyMode = False
‚Einfügeblock:
Range(„I11:I205010“).Select
Selection.Copy
Range(„O11:P11“).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Sheets(„Formel“).Select
Range(„O11:P11“).Select
Selection.Copy
Sheets(„Ueb“).Select
Range(„O11“).Select
Selection.CurrentRegion.Select
Selection.PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range(„G11:P10010“).Select
Selection.Sort Key1:=Range(„G11“), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Application.CutCopyMode = False
‚Blöcke trennen:
Range(„G300000“).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Select
Selection.Clear
Application.CutCopyMode = False
‚Resultate andocken:
Range(„O11“).Select
Selection.CurrentRegion.Select
Selection.Copy
Range(„L11“).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
‚Zeilenformat:
Sheets(„Formel“).Select
Range(„I25:M25“).Select
Selection.Copy
Range(„A1“).Select
Sheets(„Ueb“).Select
Range(„I11“).Select
Selection.CurrentRegion.Select
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
‚Resultate in die Auswertung:
Selection.Copy
Sheets(„Auswertung“).Select
Range(„C11“).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‚Überleitung löschen:
Sheets(„Ueb“).Select
ActiveSheet.UsedRange.Select
Selection.Clear
Application.CutCopyMode = False
Range(„A1“).Select
‚Finale:
Sheets(„Auswertung“).Select
Rows(„11:11“).Select
ActiveWindow.FreezePanes = True
Application.Goto Range(„A1“), Scroll:=True
Range(„C11“).Select
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlNoRestrictions
Application.ScreenUpdating = True
End Sub

Download: ExcelBlocktechnik

2. Fachbeitrag 07/2013

Größere Investitionsvorhaben sollten durch eine Wirtschaftlichkeitsrechnung abgesichert werden. Bei EDV-Projekten stößt dieses Bemühen regelmäßig auf gewisse Schwierigkeiten: Die Bewertungsfaktoren sind nicht so gut greifbar, wie wir es aus anderen Sektoren kennen; Meinungen und Fakten sind oft schwer zu trennen.

Die etablierte Softwareindustrie weiß dies zu nutzen, und die EDV-Kosten entwickeln sich mancherorts in erstaunliche Höhen. MS-Excel wird allgemein als nur für kleinere Nebenaufgaben geeignet eingestuft – ein Vorurteil, genährt aus Oberflächlichkeit, die das Verborgene leichtfertig dem Fehlenden zurechnet.
Daten aus anderen Systemen in Excel weiter zu verarbeiten, liegt im Trend, und alle großen Softwarehersteller stellen Excel-Schnittstellen zur Verfügung. Oft liegen die Rohdaten aber nicht so geordnet vor, wie es die Weiterverarbeitung erfordert. Excel bietet weitreichende Möglichkeiten, dem beizukommen. Bei Wiederholungsarbeiten – und dies ist ein großer Teil der Arbeit – gilt es, die Abläufe zu automatisieren. Dann kann Excel einen wirksamen Beitrag zur Eindämmung ausufernder EDV-Kosten leisten.
Der Vorteil von Excel ist – neben der Flexibilität für individuell zu gestaltende Lösungen – dass es bereits ein fertiges Rechenprogramm mit integriertem Automatisierungswerkzeug ist. Deshalb müssen für die Automatisierung nur noch die Formeln aufgestellt und die Ablaufbefehle eingerichtet werden. Excel selbst kann unverändert bleiben.
Die Automatisierung beginnt mit der Einrichtung einer eigenen Bedieneroberfläche mit bunten, frei beschrifteten Schaltflächen, denen Makros zugewiesen sind. Die Makros erledigen all das, was normalerweise mit den vielen Excel-Werkzeugen in offener Tabellenarbeit durchgeführt wird. Lassen wir Excel arbeiten, anstatt mit Excel zu arbeiten.
Sobald größere Datenmengen aufzubereiten sind – und dies ist oft auch in kleineren Unternehmen der Fall – geht es darum, aus der Vielzahl der Möglichkeiten die schnellsten auszuwählen. Meistens gibt es mehrere Lösungswege, und nicht alles, was Excel bietet, ist in jedem Fall die beste Wahl.
Unrationelle Excel-Lösungen sind daran zu erkennen, dass Tabellen großflächig mit Formeln bestückt und gespeichert werden. Dies ist unpraktisch, da am Ende nur die Resultate interessieren. Formeln sind speicherintensiv. Deshalb hinterlegen wir diese in einem Depot, aus dem sie makrogesteuert in die Tabellen kommen und in Werte umgesetzt werden. Die pfadunabhängige Lauffähigkeit und automatisierte zeitliche Fortschreibung sind weitere Elemente der rationellen Excel-Anwendung.
Ein weiterer Schritt zur Beschleunigung ist die Input-/Output-Trennung. Im Umgang mit größeren Datenmengen spielt die rationelle Aufteilung eine entscheidende Rolle. Vielfach werden in den Auswertungen nur blanke Daten benötigt, während zuvor diverse Formeln und Formate eingesetzt sind. Dann speichern wir die Transferdaten in Zwischendateien. Diese werden von der Inputseite im Überschreibmodus erneuert und für die Auswertung lediglich gezogen, aber nicht neu gespeichert. Damit erreichen wir eine enorme Beschleunigung.
Geschwindigkeiten, die gelegentlich an die Grenze des Vorstellbaren gehen, sind der Lohn der Mühe. Ein gutes Beispiel ist eine Ergebnisplanung. Ist es vorstellbar, die Absatzmengen und Erlöse für 1000 Kunden x 1000 Produkte mit hinterlegten Kostensätzen in eine Planungsrechnung zu setzen, mit Blick auf den Vormonat und Vorjahresmonat und anschließendem Plan-Ist-Vergleich, einer Kapazität von monatlich 200000 Datensätzen und Aktualisierungs-zeiten unter 10 Sekunden?
*) www.fischer-konzept.de
Literaturhinweis:
Excel-Automatisierung
ISBN 978-3-732-23847-7
Das Buch enthält eine Tipp- und Instrumente-Sammlung zur Excel-Automatisierung. An einem Praxisbeispiel zum Nachbauen wird eine Ergebnisplanung Schritt für Schritt mit allen Formeln und Makros detailliert beschrieben. Spezielle Excel- und VBA-Vorkenntnisse sind nicht erforderlich. Die Makros sind zusätzlich im Internet abrufbar.

Advertisements

Autor: Gerhard Pundt

Auf meiner Site https://clevercalcul.wordpress.com geht es um die Tabellenkalkulation mit Excel. Es wird über Funktionen, Diagramme, Basiswissen u.a.m. geschrieben.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s