Welcher Planer, Kostenrechner, Vertriebsmitarbeiter oder Controller kennt das Problem nicht. Abfragen an ein ERP-System zu Umsätzen oder Kosten können meistens nur beschränkt für ein Geschäftsjahr gestellt werden.
Entweder Du fragst zum Vorjahr, Vorvorjahr oder zum Geschäftsjahr an. Eine Abfrage für den Zeitraum z.B. von Mitte des Vorjahres bis Mitte des Geschäftsjahres ist in der Regel nicht möglich. Und schon gar nicht lässt sich mit den Werten weiter rechnen. Jahresübergreifende Daten können nicht immer ausgewertet werden.
Mit einem kleinen Excel-Modell lässt sich eine brauchbare Lösung finden. Voraussetzung ist allerdings, dass die IST-Werte Jahr für Jahr und Monat für Monat fleißig erfasst werden.
Die Datei enthält für diesen Zweck das Arbeitsblatt „B“ wie Basisdaten. Der Bereich C4:N8 erhielt über Formeln / Namen definieren den Namen „B.Werte“. Das „B“ steht wieder für Basisdaten. Die Ansicht zeigt einen Ausschnitt mit 4 Monaten über die Jahr 2010 bis 2014:
Mal angenommen, Du möchtest den Mittelwert des Zeitraumes von Juli 2011 bis August 2014 erfahren, um daraus einen neuen Jahresplanwert zu bestimmen. Du wählst diesen Zeitraum, weil er Dir repräsentativ für Dein Unternehmen erscheint.
Dazu gehst Du in das Arbeitsblatt „A“ wie Auswertung und triffst zunächst die Auswahlen für die Jahre und Monate.
Im ersten Jahr brauchst Du nur „vonMonat“, im zweiten nur „bisMonat“ eintragen. Für die Auswahl der Jahre und Monate kannst Du über die Datenüberprüfung auch jeweils eine Auswahlliste anlegen, hier wurde darauf verzichtet. Die entsprechenden Listen sind im Arbeitsblatt „L“ wie Listen bereits angelegt.
Beachte bitte: Für Zelle D2 wurde in der Datenüberprüfung nur eine Ganzzahl, die größer als die Zahl in C2 sein muss, zugelassen. Ansonsten erscheint eine Fehlermeldung mit einem entsprechenden Hinweis. Probiere es einfach mal aus.
Nachdem die Auswahl getroffen ist, errechnen sich in der Auswertungstabelle die Summen für die Zeiträume, die Anzahl der Monate in den Zeiträumen, die Mittelwerte der Zeiträume und die Hochrechnung auf einen möglichen Planwert (Mittelwert der Summe x 12).
Die Zeilen 7, 9, 11, 13 und 16 enthalten zu diesem Zweck eine Menge an Formeln, darunter auch ziemlich lange, die hier nicht aufgelistet werden sollen. In der Beispieldatei MehrJahresAbfrage im Arbeitsblatt „A“ sind alle Formeln aufgeführt.
Der Text in B16 ist das Resultat der Verkettungsfunktion. Die darin enthaltene Anzahl der Werte wird aus H11 übernommen.
Im Ergebnis sieht die Auswertungstabelle nun so aus:
Das Modell kann bei Bedarf ohne weiteres entsprechend den Anforderungen erweitert werdenDie Datei zum Downloaden:
Ist das Modell aus Deiner Sicht praxistauglich? Kannst Du Dir andere Anwendungsfälle vorstellen? Bitte schreib mir einen Kommentar oder eine E-Mail.
Entdecke mehr von Clevercalcul
Melde dich für ein Abonnement an, um die neuesten Beiträge per E-Mail zu erhalten.
Eine Struktur mit Basisdaten (B), Listen (L), Anzeige (A), etc. und der Vergabe sprechender, dynamischer Namen wie hier gezeigt, kann ich nur dringend empfehlen. Das ist auch genau mein Ansatz – wenn auch mit etwas anderen Begriffen. So ein Ansatz ist selbstdokumentierend und man selbst oder Kollegen können die Arbeit auch noch nach Jahren auf Anhieb verstehen. Leider, leider trifft man einen solchen Ansatz so gut wie nie an.
Um die lästige und fehleranfällige doppelte Datenhaltung zu vermeiden, setze ich VBA ein und greife direkt auf Tabellen und/oder Views der Unternehmensdatenbanken (z.B. Oracle) zu. D.h. nach Eingabe der Parameter stellt Excel-VBA ein entsprechendes SQL-Statement zusammen und zieht genau die erforderlichen Daten in das Arbeitsblatt Basis. Fallen sehr große Datenmengen an, so schalte ich auch gerne eine Access-Datenbank als Datenpuffer zwischen. Seit einiger Zeit bietet auch Power-Pivot die Möglichkeit mit großen Datenmengen performant zu arbeiten.
Hallo Herr Hornauer,
vielen Dank für den sehr ausführlichen Kommentar. Ihre über meine Ausführungen hinausgehenden Gedanken sind natürlich ein ganz anderes Niveau, ich denke,.da muss man mit seinem IT – Wissen schon etwas besser aufgestellt sein, als ich. Danke dafür, dass Sie mit meinen Gedanken grundsätzlich mitgehen. Das sollte den „Normaluser“ anspornen, eigene Lösungen zu erarbeiten.
Viele Grüße
Gerhard