Ein Controller ist für seine Berichtserstellung meistens auf Zuarbeiten anderer Bereiche eines Unternehmens angewiesen. Handelt es sich um eine einzige Zuarbeit, ist sehr schnell festgestellt, ob alle erforderlichen Daten enthalten sind.
Kommt wöchentlich / monatlich eine größere Zahl an Zuarbeiten, kann es schon etwas Zeit kosten, diese auf Vollständigkeit zu prüfen. Die Prüfung muss aber sein, denn sind einige benötigte Werte nicht vorhanden, macht ein Bericht keinen Sinn bzw. er liefert falsche Aussagen.
Eine Lösung zur Prüfung mit Excel ist Gegenstand dieses Beitrages. Zunächst habe ich gedacht, dass das eine leichte Übung ist, aber so einfach war es dann doch nicht, weil ich doch einige Bedingungen in meine Abfrage einbauen wollte.
1.Wo sollten die Berichtszuarbeiten abgelegt sein?
Hierzu gibt es mit Sicherheit viele unterschiedliche Auffassungen. Ich denke, die Zuarbeiten sollten durch den Controller selbst in Verzeichnissen abgelegt werden, auf die nur er bzw. sein Vertreter zugreifen können. Nur dann kann er sicherstellen, dass sein Bericht auf den ursprünglich gelieferten Daten basiert.
Liegen die Zuarbeiten auf Verzeichnissen, auf die sehr viele Berechtigte zugreifen dürfen, kann er, ohne etwas unterstellen zu wollen, Manipulationen und Korrekturen in Folgemonaten nicht ausschließen. Vertrauen ist gut, Kontrolle ist eben besser.
Die Zuarbeiten können der eigentlichen Berichtsdatei durch Import zugeführt werden oder sie befinden sich in anderen Dateien. Die Varianten betrachte ich im Folgenden, wobei sich die Daten jeweils je Werk einzeln oder für alle Werke gesamt in Datentabellen befinden.
2. Zuarbeit in anderem Arbeitsblatt, je Werk einzeln
Angenommen, ein Unternehmen fertigt ein Produkt in drei Werken. Jedes Werk berichtet monatlich seine Produktionsmengen.
Die zugearbeiteten Daten liegen in einem oder mehreren Arbeitsblättern der Berichtsdatei, für jedes Werk gibt es eine Tabelle. Das könnte so aussehen:
Ich habe mir für die Auswertungen in einem Arbeitsblatt eine Liste mit dem Namen “Werke” angelegt, darin enthalten sind die Positionen “Werk1”, “Werk2”, “Werk3” und “Gesamt”. Ebenso gibt es eine Liste “Monate” mit den Werten von 1 bis 12. Die Zellen A4, A9 und A14 habe ich mit “KnotenWerk1”, “KnotenWerk2” und “KnotenWerk3” namentlich festgelegt.
In der Berichtsdatei habe ich die Zellen B8, B9 und A11 für DropDown-Felder vorgesehen. B8 und B9 erhalten über die Datenüberprüfung die Zulassung für “Liste” und als Quelle jeweils die Liste “Monate”.
A11 wird zugelassen für “Liste” und die Quelle “Werke”.
Im obigen Bild ist zu sehen, dass ich die Monate 1 bis 5, also Januar bis Mai, und alle Werke abgefragt habe. Mit der Abfrage will ich wissen, ob alle in Frage kommenden Zellen, also B4:F4, B9:F9 und B14:F14 Werte enthalten. Dazu frage ich, ob Leerzellen vorhanden sind.
Hierfür standen mir die Funktionen ANZAHLLEEREZELLEN oder ZÄHLENWENN zur Verfügung. Ich habe mich in diesem Beitrag für ZÄHLENWENN entschieden.
Da in A11 statt “Gesamt” auch “Werk1”, “Werk2” oder “Werk3” stehen könnten, unterscheide ich diese Fälle mit einer WENN-Abfrage:
=WENN(A11=”Gesamt”;
Jetzt war die “Dann”-Aktion, die nur für die Auswahl von “Gesamt” in A11 gilt, in der WENN-Formel zu formulieren. Für jedes Werk, d.h., jeden Tabellenbereich werden die Leerzellen gezählt und schließlich die Summe gebildet.
=WENN(A11=”Gesamt”;SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(KnotenWerk1;
1;B8;1;B9-B8+1);””);ZÄHLENWENN(BEREICH.VERSCHIEBEN(KnotenWerk2;
1;B8;1;B9-B8+1);””);ZÄHLENWENN(BEREICH.VERSCHIEBEN(KnotenWerk3;
1;B8;1;B9-B8+1);””));
Die Anzahl der Zeilen in der Funktion BEREICH.VERSCHIEBEN ist stets =1, die Höhe ebenfalls. Die Spalte wird mit der Auswahl für “vonMonat” in B8 benannt, die Breite wird mit B9-B8+1 berechnet. Warum “+1”? Im Beispiel rechnet sich 5-1=4, gebraucht werden aber 5 Spalten, 1 bis 5.
Für die “Sonst”-Aktion habe ich den Knotennamen durch Verkettung gebildet und greife so auf den Tabellenbereich des Werkes, das in A11 ausgewählt wurde, zu.
SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(INDIREKT(“Knoten”&A11);
1;B8;1;B9-B8+1);””)))
Mit BEREICH.VERSCHIEBEN definierte ich den Bereich in der ZÄHLENWENN-Funktion, mit “” das Suchkriterium.
Die gesamte Formel in C11 lautet:
=WENN(A11=”Gesamt”;SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(KnotenWerk1;
1;B8;1;B9-B8+1);””);ZÄHLENWENN(BEREICH.VERSCHIEBEN(KnotenWerk2;
1;B8;1;B9-B8+1);””);ZÄHLENWENN(BEREICH.VERSCHIEBEN(KnotenWerk3;
1;B8;1;B9-B8+1);””));SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(
INDIREKT(“Knoten”&A11); 1;B8;1;B9-B8+1);””)))
3. Zuarbeit in anderem Arbeitsblatt, alle Werke zusammen
Die zugearbeiteten Daten liegen in der gleichen Arbeitsmappe in einem anderen Arbeitsblatt. Anders als im Fall zuvor ist, dass die Werke in einer Tabelle zusammengefasst sind, die Abbildung zeigt es.
Die Zelle A3 erhält den Namen “KnotenWerke”. Ich verwende auch hier wieder die Funktion BEREICH.VERSCHIEBEN, um die Matrix zu definieren. Für die Höhenangabe verwende ich allerdings keine feste Zahl, hier wäre es 3, da die Tabelle 3 Zeilen enthält.
Ich verwende die Formel =ANZAHL2(Werke)-1 und bin damit flexibel, wenn sich die Anzahl der Werke im Unternehmen ändert. Mit “-1” wird gerechnet, da die Listenposition “Gesamt” hierbei nicht mitgezählt wird.
Die gesamte Formel für diesen Fall (alle Werke in einer Tabelle) lautet:
=WENN(A21=”Gesamt”;SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(KnotenWerke;1;B18;ANZAHL2(Werke)-1;B19-B18+1);””));SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(KnotenWerke;
VERGLEICH(A21;Werke;0);B18;1;B19-B18+1);””)))
4. Zuarbeit in anderer Arbeitsmappe, je Werk einzeln
Im nächsten Fall liegen die Daten für jedes Werk einzeln vor, wie im Abschn. 2, nur in einer anderen Arbeitsmappe.
Auch hier sind wie oben die “KnotenWerk1”, “KnotenWerk2” und “KnotenWerk3” benannt. Es kann deshalb die Formel aus Abschn. 2 genutzt werden. Sie ist allerdings um den Pfad und den Dateinamen der anderen Arbeitsmappe zu ergänzen. Das wird so geschrieben:
BEREICH.VERSCHIEBEN(Zuarbeiten.xlsx!KnotenWerk1;
Die andere Arbeitsmappe soll “Zuarbeiten.xlsx” heißen. Sie enthält gleiche Daten, wie die Beispiele zuvor.
Beachte bitte, dass die Datei in diesem Beitrag geöffnet sein muss, damit Daten gezogen werden können.
Die Formel gesamt lautet:
=WENN(A31=”Gesamt”;SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Zuarbeiten.xlsx!KnotenWerk1;
1;B28;1;B29-B28+1);””);
ZÄHLENWENN(BEREICH.VERSCHIEBEN(Zuarbeiten.xlsx!KnotenWerk2;
1;B28;1;B29-B28+1);””);
ZÄHLENWENN(BEREICH.VERSCHIEBEN(Zuarbeiten.xlsx!KnotenWerk3;
1;B28;1;B29-B28+1);””));
SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(INDIREKT(“Zuarbeiten.xlsx!Knoten”&A31);
1;B28;1;B29-B28+1);””)))
5. Zuarbeit in anderer Arbeitsmappe, alle Werke zusammen
In diesem Fall liegen die Daten analog Abschn. 3 in einer Tabelle, nur auch wieder in einer anderen Arbeitsmappe vor.
Ich habe wieder den “Knoten Werke” in der Zelle A3 des Datenblattes definiert und füge Pfad- und Dateinamen in die Formel ein.
=WENN(A41=”Gesamt”;SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(Zuarbeiten.xlsx!KnotenWerke;
1;B38;ANZAHL2(Werke)-1;B39-B38+1);””));
SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN(INDIREKT(“Zuarbeiten.xlsx!Knoten”&A41);
1;B38;1;B39-B38+1);””)))
Die beiden Dateien kannst du hier finden: