Excel 2016: Management-Reports

Diagrammbasis mit Formeln aufbauen und Steuerelemente erstellen

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
Die Rohdaten werden mit neuen Informationen angereichert. In der Diagrammbasis stellen Sie alle benötigten Daten für die Diagrammdarstellung per Formel bereit. Mit der Bildlaufleiste lässt sich der gewünschte Datenbereich auswählen.

Transkript

In der Übungsarbeitspappe sind zwei Arbeitsblätter enthalten. Auf dem Arbeitsblatt "Report" wird der gesamte Report erstellt: aus den Diagrammen, dem Kontrollkästchen und der Bildlaufleiste. Auf dem Arbeitsblatt "Daten" sind die Rohdaten enthalten. Und zwar vom 01.03.2016 bis zum 27.05.2016. Zu jedem Datum ist das jeweilige Datenvolumenaufkommen notiert. Mit Formeln werden mir Werte aufkumuliert, bzw. Diagrammbasis gebildet. Zunächst zu den aufkumulierten Werten. Sie sollen monatlich erstellt werden. Immer wenn der Monat mit einem ersten beginnt, wird neu aufkumuliert. Deshalb frage ich per Formel nach, ob die Tageszahl dieses Datums eine "1" ist. Wenn ja, wird das Datenvolumen um, wenn nein: der Vorgängerwert mit dem neuen Datenvolumen addiert. Ist gleich "WENN", "Klammer auf", "der Tag", dieses "Datums" gleiche "1", dann nimm das Datenvolumen. Ansonsten nehme den Vorgängerwert, hier "D4" und addiere das neue Datenvolumen auf. "C5", "Klammer zu", "Enter". Diese Formel kann ich per Doppelklick auf den Anfässern nach unten hin ausfüllen. Doppelklick. Jetzt wird hier zwar auch die Formatierung mit genommen, aber hier gibt es die "Option"-Schaltfläche "Ohne Formatierung ausfüllen". Schau ich einmal nach. Hier endet der "März" und ab 01.04. mit dem neuen Wert, dem neuen Datenvolumen neu gearbeitet und aufkumuliert. Prima! Damit ich im Diagramm durch die Daten durchscrollen kann, erstelle ich eine Bildlaufleiste. Auf der Registerkarte "Entwicklertools" klicke ich auf die Schaltfläche "Einfügen" und dort bei den "Formularsteuerelementen" Ziehe die Bildlaufleiste auf, führe einen Rechtsklick darauf aus und wähle den Kontextmenü "Steuerelement formatieren". Als aktuellen Wert nehme ich mal die "1", als Minimalwert die "1". Der Maximalwert ergibt sich aus der Menge der Datensätze. Das wären hier "88" minus dem, was ich mindestens immer angezeigt haben möchte, "31 plus 1". Also "58". Schrittweiten- "1", wenn ich auf die Enden der Bildlaufleiste klicke. Seitenwechsel, den stelle ich mal auf "7" ein. 7 Tage weiter, wenn ich in diesen Graubereich hineinklicke. "Zellverknüpfung" ist das Arbeitsblatt "Daten!", die Zelle "F5". Okay! Ich klicke ins Arbeitsblatt und kann mit der Bildlaufleiste jetzt durchscrollen oder 7 Tage weiter: "29", "36" oder andersherum - "29", "22". Unterhalb der Zelle "F5" trage ich die Formel ein: ist gleich die Zahl, die sich dort ergibt plus "1", "Enter". Diese Formel kopiere ich nach unten, das war ein bisschen weit, und über die Option-Schaltfläche "Ohne Formatierung ausfüllen". Dann kann ich noch mal kontrollieren. Die Bildlaufleiste ganz nach oben ziehen: "31", ganz nach unten: "88". Prima! Genau das brauche ich. Anhand dieser Daten werden jetzt das Datum und das Datenvolumen aus den Rohdaten gezogen. Um das Datum aus den Rohdaten ziehen zu können, möchte ich hier die Funktion "BEREICH.VERSCHIEBEN" benutzen. Sie erwartet im ersten Argument einen Startpunkt. Und zur Vorbereitung benenne ich mal die Zelle "B4" über das Namensfeld mit dem Namen "Startpunkt", "Enter". Ich markiere Zelle "G5" und gebe dort ein: ist gleich "BEREICH.VERSCHIEBEN", Taste "F3", der vergebene Name "Startpunkt", ab dort soll es losgehen, "Semikolon", den Zeilenversatz, der erwartet wird der steht hier in dieser Nummer drin. Also "F5". Unter Taste "F4" halte ich diese Adresse in der Weise fest, dass es halt "$F5" lautet, "Semikolon", also um eine Zeile vom Startpunkt aus, heruntergegangen. das wäre die 01.03. Einen Spaltenversatz gibt es nicht. Deshalb gebe ich dort die Zahl "0" ein und schließe die Eingabe mit einer Klammer, "Enter". Ich formatiere diese Serielzahl über die Registerkarte "Start" als Datum. Datum kurz, okay, und mit einem Doppelklick auf den Anfasser wird diese Formel nach unten hin ausgefüllt. "Option"-Schaltfläche "Ohne Formatierung ausfüllen". Okay, dann habe ich zwar geschafft, Uhr zurückerhalten, aber das Datumsformat fehlt mir. Gut. Über die Registerkarte "Start" wird das Datumsformat kurz noch einmal eingetragen. Das Datenvolumen ziehe ich auf die gleiche Weise. Ich markiere die Zelle "H5". Ist gleich "BEREICH.VERSCHIEBEN". Unter Taste "F3" öffne ich das Dialogfeld für die vergebenen Namen, markiere den Startpunkt, "OK". "Semikolon", Datensatznummer, Taste "4" ein, zwei, drei Mal. "Semikolon", und der Spaltenversatz aus Sicht dieser Zelle beträgt jetzt "1". Also gebe ich dort auch eine "1" ein. "Klammer zu", "Enter". "Doppelklick" auf den Anfasser und "Ohne Formatierung ausfüllen". In der Spalte "Hoch" werden die Daten ermittelt, die größer sind als 10 GB. Ich markiere die Zelle "I5", gebe das gleiche Zeichen ein. "WENN", "Klammer auf", das Datenvolumen größer ist als der Wert aus der Zelle "N5", Taste "4" bitte einmal drücken, festhalten, denn die Formel soll nach unten kopiert werden. "Semikolon". Wenn das der Fall ist, dann nimm doch bitte das Datenvolumen, ansonsten trage eine "0" ein. "Klammer zu", "Enter". Mit einem "Doppelklick" auf den Anfasser kopiere ich die Formel nach unten, aber bitte "Ohne Formatierung ausfüllen". Und da sind es die Werte, die größer sind als 10 GB. Da ich mich auf Zelle "N5" bezogen habe, kann ich dort den Wert ändern. Ich trage jetzt den Wert "5" ein, 5 GB. Wird entsprechend die Formel neu berechnet und zeigt mir andere Datenvolumen an, die jetzt über diesen Wert liegen. Das mach ich noch mal wieder rückgängig mit mit "Strg+Z". Größer als 10 GB. In der Spalte "Normal" werden die Daten ausgegeben, die kleiner oder gleich 10 GB sind. Also im Grunde der Gegenwert. Da, wo eine "0" steht, muss es eine Zahl stehen und da, wo eine Zahl steht, muss dann eine "0"l stehen. Ist gleich, "WENN", "Klammer auf", das Datenvolumen kleiner-gleich dem Wert in Zelle "N5", festhalten mit der Taste 4, "$N$5", dann bitte das Datenvolumen, ansonsten eine "0"l, "Klammer zu", "Enter". "Doppelklick" auf den Anfasser" und nach unten hin ausfüllen. "Ohne Formatierung ausfüllen". Da ist jetzt gut zu sehen, haben wir genau die Gegenwerte, wo bei "Hoch" ein Wert ist, ist dort eine "0", wo bei "Hoch" eine "1" ist, ist dort ein Wert. In der Spalte "Kumuliert" übernehme ich die berechneten Werte aus der Spalte "D". Dazu benutze ich hier die Funktion "BEREICH.VERSCHIEBEN". Ist gleich "BEREICH.VERSCHIEBEN", "Klammer auf", Taste "F3", "Startpunkt" "OK" "Semikolon", und zwar die Zeilennummer als Zeilenversatz Taste 4 eins, zwei, drei Mal gedrückt, "Semikolon", und das wäre aus diesem Blickwinkel ein, zwei Spalten weiter rechts. Deshalb gebe ich dort eine "2" ein. "Klammer zu", "Enter". Mit einem "Doppelklick" übernehme ich damit die kumulierten Werte. Aber bitte "ohne Formatierung ausfüllen". Bevor ich die Spalte "über 75 GB" mit einer Formel ausfülle, erstelle ich ein Kontrollkästchen. Über die Registerkarte "Entwicklertools" "Einfügen" wähle ich das Kontrollkästchen aus, klicke drauf und ziehe es im Arbeitsblatt auf. Auf dem Steuerelement führe ich einen "Rechtsklick" aus und wähle im Kontextmenü Steuerelement "Formatieren". Auf der Registerkarte "Steuerung" gebe ich einmal die Zellverknüpfung ein und zwar mit Arbeitsblattnamen: "Daten!". Zelle "P5", "3D-Schattierung" und "OK". Ich vergebe noch den Text, dazu entferne ich mal hier die Beschriftung "Kontrollkästchen 3". "Werte anzeigen" nenne ich das Ganze und klicke ins Arbeitsblatt. Aktiviere ich das Kontrollkästchen, bekommt die Zelle "P5" den Wert "WAHR". Deaktiviere ich das Kontrollkästchen, steht dort der Wahrheitswert "FALSCH". Damit die Werte über 75 GB angezeigt werden, müssen in der Formel zwei Bedingungen erfüllt sein. Einmal, dass der kumulierte Wert tatsächlich über 75 GB ist, und dass Kontrollkästchen "Werte anzeigen" aktiviert ist. Mit der Funktion "UND" kann ich genau diese zwei Bedingungen abfahren. Also. Zelle "F5" ist gleich: "WENN" , "Klammer auf", "UND" , "Klammer auf", erste Bedingung der kumulierte Wert ist größer, als mein Wert, den ich in Zelle "O5" eingetragen habe. Taste 4, festhalten. "Semikolon". Für den zweiten Wahrheitswert klicke ich einfach nur auf die Zelle "P5". Taste-"4" festhalten, "Klammer zu". Wenn beide Bedingungen erfüllt sind, dann zeige mir doch bitte den kumulierten Wert an. Ansonsten eine "0", "Klammer zu". "Enter" "Doppelklick" und bitte "ohne Formatierung ausfüllen". Im Moment ist nur eine Bedingung erfüllt für diese Zelle. Sobald ich aber das Kontrollkästchen aktiviere, ist auch die zweite Bedingung erfüllt. Ab jetzt werden die Werte größer 75 GB auch angezeigt. Trage ich in die Zelle "O5" einen anderen Wert ein, nehme ich mal 160 GB, reagiert entsprechend die Formel und zeigt mir nur die Werte an, die eben größer sind, als 160 GB. Deaktiviere ich das Kontrollkästchen, werden keine Werte aus dieser Spalte mehr angezeigt.

Excel 2016: Management-Reports

Präsentieren Sie Ihre Zahlen verständlich und überzeugend mit den Werkzeugen und Arbeitstechniken aus diesem Video-Training.

4 Std. 7 min (38 Videos)
Derzeit sind keine Feedbacks vorhanden...

Dieser Online-Kurs ist als Download und als Streaming-Video verfügbar. Die gute Nachricht: Sie müssen sich nicht entscheiden - sobald Sie das Training erwerben, erhalten Sie Zugang zu beiden Optionen!

Der Download ermöglicht Ihnen die Offline-Nutzung des Trainings und bietet die Vorteile einer benutzerfreundlichen Abspielumgebung. Wenn Sie an verschiedenen Computern arbeiten, oder nicht den ganzen Kurs auf einmal herunterladen möchten, loggen Sie sich auf dieser Seite ein, um alle Videos des Trainings als Streaming-Video anzusehen.

Wir hoffen, dass Sie viel Freude und Erfolg mit diesem Video-Training haben werden. Falls Sie irgendwelche Fragen haben, zögern Sie nicht uns zu kontaktieren!