Excel-Tabellen in Word-Berichte einbetten

Als Controller kennst du diese Aufgabe bestimmt. In einem monatlichen Managementbericht, den du in MS Word erstellst, ist u.a. die Liquiditätsentwicklung des Unternehmens darzustellen.

Sinnvollerweise führst du die Liquiditätsentwicklung in MS Excel. Die Aufgabe besteht nun darin, die Excel-Tabelle in den Word-Bericht einzuarbeiten und beides zu verknüpfen.

Weiterlesen „Excel-Tabellen in Word-Berichte einbetten“

Advertisements

Rangfolgebestimmung bei doppelten Werten

Ein allgemeiner Fall

Wenn in Berichten eine Bewertung von mehreren Ergebnissen untereinander vorgenommen werden soll, wird oftmals mit der Excel-Funktion RANG (bis Excel 2007) bzw. RANG.GLEICH (ab Excel 2010) gearbeitet.

So kann es aber vorkommen, dass mehrere Ergebnisse gleichlautend sind. Die RANG-Funktion gibt dann den höheren Rang aus. Haben z.B. zwei Ergebnisse den Rang 3, wird der 4. Rang übersprungen.

Ein kleines Beispiel:

Rangfolge1

Der Rang wurde mit dieser Formel berechnet:

=RANG.GLEICH(C7;$C$6:$C$12;0)

Du siehst, Rang 4 ist doppelt, Rang 5 gar nicht vorhanden.

Üblicherweise wird in der Praxis mit einer Hilfsspalte dem Umsatz ein sehr kleiner Wert hinzugefügt. Den kannst du mit

=ZEILE()/1000000

berechnen.

Rangfolge2

Den Rang 2 errechnest du dann mit:

=RANG.GLEICH(E6;$E$6:$E$12;0)

Nun sind alle Ränge von 1 bis 7 vorhanden, es gibt keine Dopplungen mehr.

Versehe jetzt den Umsatz noch mit dem Zahlenformat #.##0

Wenn ein zweites Kriterium vorhanden ist

Für die Fußballvereine, egal welcher Liga, und für die Fußballfans ist die Abschlusstabelle schon fast ein Heiligtum.

In einem früheren Beitrag habe ich eine Excel-Variante zum Führen einer solchen Tabelle für die 1. Bundesliga schon einmal vorgestellt. Der letzte Schritt bei allen Berechnungen war die Bestimmung des Ranges eines jeden Vereins.

In diesem Beitrag soll es weniger um Fußball, von dort kommt nur dieses geeignete Beispiel, sondern mehr um die Excel-Funktion RANG.GLEICH gehen.

Ganz wichtig dabei ist, wie der Rang bestimmt werden kann, wenn zwei Mannschaften zunächst gleichrangig sind.

Als Beispiel nutze ich die „Abschlusstabelle der 1. Bundesliga – Saison 2016 / 2017“, die ich bei BuLi-Box [1] gefunden habe.

Ausgehend von der anfänglichen Mannschaftsaufstellung für die Saison habe ich die Punkte entnommen und hier zusammengestellt:

Rangfolge3

Zu sehen ist, dass es zwei 5., zwei 8. , zwei 13. und zwei 15. Plätze gibt. So ist das nicht gewollt, ich will nur eindeutige Werte von 1 bis 18 haben.

Was kann getan werden?

Die Regeln der Liga besagen, dass zur Rangbestimmung bei Punktgleichheit zunächst noch die Tordifferenz ( geschossene abzgl. empfangene Tore) zu berücksichtigen ist.

Ich entnehme deshalb aus der Abschlusstabelle die Tordifferenzen und füge sie in meine Tabelle ein.

Dann bilde ich jeweils eine Summe aus Punkte und Tordifferenz, für die ich dann den Rang bestimmen will.

Rangfolge4

Die Summe errechne ich mit der Formel:

=C3+(E3/100)

wobei ich den Tordifferenzen durch die Division durch 100 eine geringere Gewichtung gebe, denn zuerst entscheiden die Punkte, dann die Differenzen.

Schließlich füge ich die Spalte Rang 2 hinzu und rechne mit der Formel

=RANG.GLEICH(F3;$F$3:$F$20;0)

die ich bis F20 herunter ziehe, die Ränge.

Zum Schluss erstelle ich noch die Abschlusstabelle, die mit den Angaben zu Rang, Mannschaft, Punktezahl und Tordifferenz, die endlich so aussieht:

Rangfolge5

Die Spalte Rang erstelle ich zuerst manuell und hole die zugehörigen Inhalte mit der Funktion BEREICH.VERSCHIEBEN aus der vorangegangenen Tabelle.

Jetzt entsprechen die Rangzahlen der „amtlichen“ Abschlusstabelle von BuLi-Box.

Quellen:

[1] http://www.bulibox.de

Wie du in Pivottabellen den Beschriftungsfilter einsetzt

Eine Pivottabelle erstellen

Um eine Pivottabelle erstellen zu können, ist zunächst eine Datensammlung in einer Datenbank erforderlich.

Wie es dann weiter geht, habe ich in meinem Beitrag zu Absatzmengenplanung gezeigt.

In diesem Beitrag will ich dir die Funktionsweise des Beschriftungsfilters erklären.

Zunächst erstellst du die Datenbank für deine Pivottabelle. Darin enthalten sind Informationen zur Region, zum Filialort, zum Produkt sowie zugehörige Mengenangaben.

PivotSchrift1

Daraus erstellst du eine Pivottabelle. Du erinnerst dich?

Menü Einfügen / Tabellen / PivotTabel / PivotTable

Der Berichtsfilter soll die Region enthalten. In die Zeilenbeschriftungen schiebst du die Orte, in die Spaltenbeschriftungen die Produkte. Der Wertebereich schließlich soll die Mengen enthalten.

PivotSchrift2

Auf eine Spalte Gesamtergebnis kannst du in der Zeile verzichten, denn es macht keinen Sinn, Betten und Stühle zu addieren. Klicke dazu mit der rechten Maustaste in die Pivottabelle und entferne in den PivotTable-Optionen den Haken unter Summen & Filter.

Wie gewohnt, kannst du mit den Filtern der Pivottabelle nun Regionen, Orte und Produkte auswählen.

Einen Beschriftungsfilter setzen

Der Beschriftungsfilter ermöglicht es dir zusätzlich, z.B. nur alle Orte anzuzeigen, die ein „o“ enthalten. Klicke dazu auf den Pfeil neben Zeilenbeschriftungen.

PivotSchrift3

Wähle dort „Enthält“ und gib ein „o“ ein.

PivotSchrift4

Bestätige mit OK und die Pivottabelle zeigt nur noch diese Daten:

PivotSchrift5

Alle weiteren möglichen Varianten siehst du im Screenshot oben.

 

Prüfung, ob vertikale und horizontale Summen identisch sind

Enthält eine Tabelle mehrere Spalten und mehrere Zeilen, Zeilensummen und Spaltensummen sowie eine Gesamtsumme, vertraue ich oftmals darauf, alle Formeln zur Summierung richtig eingetragen zu haben.

Wie darüber die Kontrolle behalten werden kann, ist Gegenstand dieses Beitrages.

Die folgende Tabelle zeigt beispielsweise die produzierten Mengen aus sechs Werken für die ersten sechs Monate eines Jahres.

Spaltensumme1

Die Zellen B11:G11 sollten richtigerweise die Mengen der Werke 1 bis 6 addieren.

Die Zellen H5:H10 sollten ebenso richtig die Mengen der Monate Januar bis Juni addieren.

Und schließlich soll in Zelle H11 entweder die Summe von B11:G11 oder die Summe der Werte aus H5:H10 gebildet werden.

Die Summe aus B11:G11 und die Summe aus H5:H10 müssen identisch sein. In H11 kann aber nur eine Formel eingetragen werden. So kann es durchaus passiert sein, das irgendeine Summe einen Wert aus der Spalte oder aus der Zeile „vergessen“ hat. Ein solcher Fehler ist aber nicht sofort sichtbar.

Wie lässt sich im Arbeitsblatt eine Kontrolle darüber einflechten?

Eine Möglichkeit ist, irgendwo auf dem Arbeitsblatt, z.B. in I11, eine der folgenden Formeln einzutragen:

  • =WENN(SUMME(H5:H10)=SUMME(B11:G11);“OK“;“Fehler“)
  • =IDENTISCH(SUMME(H5:H10);SUMME(B11:G11))
  • =SUMME(H5:H10)=SUMME(B11:G11)

Die zweite und dritte Formel geben als Ergebnis ein „WAHR“ oder ein „FALSCH“ heraus.

Darüber hinaus kann durch eine bedingte Formatierung die Zelle H11 manipuliert werden, so dass sofort ersichtlich ist, ob die Summenformeln richtig sind.

bedingte Formatierung in H11

neue Regel / Formel: =SUMME(H5:H10)<>SUMME(B11:G11)

formatieren / ausfüllen: rot

und als zweite Formel:

bedingte Formatierung in H11

neue Regel / Formel: =SUMME(H5:H10)=SUMME(B11:G11)

formatieren / ausfüllen: grün

Ergänzend kann unterhalb von H11, z.B. in H13, noch eine kurze Erläuterung des Fehlers konstruiert werden. Dazu kannst du diese Formel verwenden:

=WENN(SUMME(H5:H10)<SUMME(B11:G11);“Zeilensumme ist kleiner“;WENN(SUMME(B11:G11)<SUMME(H5:H10);“Spaltensumme ist kleiner“;“OK“))

Folgende Tabelle enthält in der Summe des Monats April (gelb markiert) einen Fehler. Und der wird angezeigt:

Spaltensumme2

Wenn ein Fehler vorhanden ist und dies auch angezeigt wird, müssen die Zeilen-, Spalten- und Gesamtsummen nur überprüft und korrigiert werden. Dann ist H11 wieder grün.

Runden oder nicht runden?

Wer kennt das Problem nicht? In einem Bericht werden die Zahlenwerte mit zwei Nachkommastellen dargestellt. Im Bericht selbst werden aber Prozentwerte berechnet oder Konstanten wie die Kreiszahl Pi oder die Eulersche Zahl e bei Rechenoperationen verwendet. Die Ergebnisse haben dann in der Regel mehr Nachkommastellen als zwei.

Die gezeigten Endergebnisse sind ohne Zweifel richtig errechnet. Aber wenn ein Kollege oder ein Vorgesetzter horizontal und vertikal die sichtbaren Ergebnisse mit dem Taschenrechner nachrechnen, passt irgendetwas meistens nicht.

Ein Beispiel soll das verdeutlichen:

runden1

Formeln:

D6   =WENN(UND(C6>180000;C6<200000);0,03; WENN(UND(C6>200000;C6<220000);0,05; WENN(C6>220000;0,07;““)))

E6   =WENN(D6<>““;C6*D6;““)

E13   =SUMME(E6:E12)

Dabei gelten diese Regeln:

Umsatz >220000   Provision 7%
Umsatz >200000   Provision 5%
Umsatz >180000   Provision 3%

Je nachdem, welche der Regeln auf einen Verkäufer zutrifft, wird der erreichte Umsatz mit dem Prozentsatz multipliziert und die Provision mit zwei Nachkommastellen ausgewiesen, eben so, wie dann die Auszahlung erfolgen wird.

Nun verändere das Zahlenformat für die Provisionen derart, dass vier Nachkommastellen gezeigt werden.

runden3

Zu sehen ist, dass jeweils eine dritte Nachkommastelle errechnet wurde.

Addierst du die Werte so, wie sie zu sehen sind, also 16.895,515+5.957,364 usw., erhältst du das Ergebnis 65.689,971, so wie es jetzt in der Tabelle zu sehen ist.

Addierst du aber die Werte so, wie du sie in der ersten Tabelle siehst, also 16.895,52+5.957,36 usw., lautet die Summe 65.689,98. In E13 steht aber 65.689,97.

Fest steht, dass Werte gerundet werden müssen, aber welche?

Es könnten die einzelnen Positionen oder die Summe der Provisionen gerundet werden.

Zuerst rundest du die einzelnen Provisionen:

runden4

Die Formel in E6 hast du geändert in:

=WENN(D6<>““;RUNDEN(C6*D6;2);““)

Das Zahlenformat kann jetzt wieder auf zwei Nachkommastellen reduziert werden und das Ergebnis 65.689,98 entspricht nun auch dem Additionsergebnis aus den sichtbaren Werten in der ersten Tabelle.

Du willst noch die zweite Variante beim Runden ausführen und nur die Summe runden.

runden5

Die Formeln lauten:

In E89   =WENN(D6<>“;C6*D6;““)

In E13   =RUNDEN(SUMME(E6:E12);2)

Zu sehen ist, dass in diesem Beispiel eben der Wert erscheint, der schon ursprünglich zu sehen war. Er ist nicht exakt.

Fazit:

Wird mit Prozenten oder Konstanten wie MWSt-Satz, Pi oder e gerechnet, sollten sinnvollerweise die unmittelbaren Einzelergebnisse auf die Anzahl Nachkommastellen gerundet werden, die sichtbar sein soll.

Das Argument „Anzahl_Stellen“ in der Rundungsformel muss der Anzahl der Nachkommastellen gemäß Zahlenformat entsprechen. Die Regel ist, dort zu runden, wo die Abweichungen entstehen.

Dies ist nun die korrekte Tabelle:

runden6

Bilder dynamisch in Berichte einfügen

Größere Unternehmen haben oft Niederlassungen in anderen Staaten. Das monatliche Berichtswesen aus diesen Niederlassungen folgt in der Regel einer einheitlichen Vorgabe.

Angenommen, du hast die Aufgabe, die monatlichen Meldungen aus den Niederlassungen in die erwünschte Berichtsform zu bringen. Wie wäre es, das Berichtsdesign mit der jeweiligen Nationalflagge ein wenig aufzufrischen?

Excel bietet dazu mehrere Möglichkeiten, von denen ich dir eine reine Excel- und eine VBA-Variante zeigen will.

Weiterlesen „Bilder dynamisch in Berichte einfügen“

Update 1. Bundesliga 2016-17, V2

Kurz vor Beginn der Spielsaison 2016-17 stellte ich eine erste Version meiner Ligaverwaltung vor.

fusballiga1

Von meinem Leser Hubert … (er möchte nicht näher benannt werden, was in Ordnung ist) erhielt ich mehrere Hinweise zur Erweiterung des Modells. Er machte einen Vorschlag, wie die Mannschaften automatisch den einzelnen Spieltagen zugeordnet werden können. Da ich die Spielansetzungen nun aber schon manuell in die Tabellen der Spieltage eingetragen hatte, habe ich dies in dieser Version noch nicht umgesetzt. Ich plane das aber für die Version zur Saison 2017-18.

Er riet mir auch, die Rangfolgeberechnung zu überdenken. Den Gedanken hatte ich selbst auch schon. Ich habe eine Veränderung hinsichtlich der Gewichtung vorgenommen.

Neu in dieser Version ist das Entscheidungskriterium „Auswärtstore“. Bisher waren nur der Punktestand, die Tordifferenz und die erzielten Tore in die Berechnung eingeflossen.

Das Modell enthält in dieser Version die Spielergebnisse bis zum 16. Spieltag. Ab dem 17. Spieltag darfst du die Ergebnisse selbst eintragen, wenn du das möchtest.

Und hier findest du die neue Version: update-1-bundesliga-2016-17_v2