Excel- Programmierung in größeren Datenmengen

MS Excel ist heute aus dem Controlling nicht mehr wegzudenken. Es eröffnet den Sachbearbeitern die Möglichkeit, ihre Arbeit selbst einer rationellen Lösung zuzuführen. Eine Vielzahl von Bedienschritten im monatlichen Reporting und diversen weiteren Controllingaufgaben lässt sich relativ einfach durch Makros automatisieren. Schnelle Hardware und ein erweitertes Tabellenvolumen ab Excel 2007 erlauben neue Wege für anspruchsvolle Aufgaben in größeren Datenmengen. Der Vorteil der Makrosprache VBA ist die bereits vorhandene Einbindung, so dass die Excel- Funktionalität voll genutzt werden kann.

Ein Paradebeispiel dafür sind die in statistischen Arbeiten häufig anstehenden Verdichtungen und Zuordnungen. Die dafür vorgesehenen Standardfunktionen SVERWEIS und SUMMEWENN sind in großen Datenbereichen viel zu träge, weil sie in jeder Ergebniszelle den gesamten Quellbereich abfragen. Häufig ist sowohl eine Zuordnung als auch eine Verdichtung erforderlich. Dann sind die Standardfunktionen hintereinander im Einsatz, und die Bildschirmwartezeit wird immer länger. Mit einem relativ einfachen Makro geht es besser. 

Workshop- Beispiel: Verdichten Sie 50.000 Datensätze auf die darin enthaltenen Nummern, ordnen Sie diesen die in einer zweiten Tabelle aufgelisteten 5.000 Kostensätze zu und berechnen Sie daraus die Kosten.

Im Blatt Mengen sind 50.000 Tagesmengen einkopiert:
Ausschnitt Tagesmengen
 
Im Blatt Kosten stehen 5.000 Kostensätze:
Kostensätze
 
Die Mengen und Kosten je Artikel sind im Blatt Tab1 wie folgt aufzulisten:
Ausschnitt Zieltabelle
 
Ihre Arbeitsmappe enthält die Registerblätter Tab1, Mengen, Kosten, Ueb und Fo.
Erstellen Sie im Blatt Tab1 die Tabellenköpfe, beginnend in Zelle C9. In die Zel-len E7 und G7 setzen Sie eine Summenformel für die Zeilen 11-5010 ein.
Setzen Sie Ihre Quelldaten in die Blätter Mengen und Kosten.

Hinterlegen Sie im Blatt Fo folgende Formeln:

H11:
=WENN(C11=C10;H10;F11)
I11:
=WENN(UND(E11<>0;C11<>C12);1;"a")
J11:
=WENN(C11=C10;J10+E11;E11)
K11:
=WENN(I11=1;RUNDEN(J11*H11;2);0)

Legen Sie im Blatt Fo in C25:G25 ein Zeilenformat an:
 
Erstellen Sie folgendes Makro >>
 
Legen Sie im Blatt Tab1 eine Schaltfläche an, der Sie das Makro zuweisen.

Klicken Sie auf die Schaltfläche. In spätestens 2 Sekunden stehen die Mengen der aus 50.000 Datensätzen extrahierten Nummern verdichtet und formatiert mit 5.000 zugeordneten Kostensätzen und Kosten im Bildschirm.

Das Makro können Sie mit dem Makrorekorder aufzeichnen, mit Ausnahme folgender Befehle:

Application.ScreenUpdating = False
= Bildschirm aus
Application.ScreenUpdating = True
= Bildschirm ein
ActiveCell.Offset(1, 0).Select
= Cursor 1 Zeile tiefer in der gleichen Spalte.

Die kurze Bildschirmwartezeit resultiert aus folgenden Spezialroutinen:



letzte Änderung Ernst Fischer am 16.09.2020

RSS
Literaturhinweise
Excel-Tools

Weitere Fachbeiträge zum Thema

Premium-Stellenanzeigen


Northrop Grumman LITEF GmbH
Freiburg
Universitätsklinikum Münster
Münster
Universitätsklinikum Münster
Münster
GCP – Grand City Property
Berlin

Eigenen Fachbeitrag veröffentlichen? 

Sie sind Autor einer Fachpublikation oder Entwickler einer Excel-Vorlage? Gern können Sie sich an der Gestaltung der Inhalte unserer Fachportale beteiligen! Wir bieten die Möglichkeit Ihre Fachpublikation (Fachbeitrag, eBook, Diplomarbeit, Checkliste, Studie, Berichtsvorlage ...) bzw. Excel-Vorlage auf unseren Fachportalen zu veröffentlichen bzw. ggf. auch zu vermarkten. Mehr Infos >>

Kommentar zum Fachbeitrag abgeben

Nur registrierte Benutzer können Kommentare posten!