Am 14. September 2017 haben wir eine überarbeitete Fassung unserer Datenschutzrichtlinie veröffentlicht. Wenn Sie video2brain.com weiterhin nutzen, erklären Sie sich mit diesem überarbeiteten Dokument einverstanden. Bitte lesen Sie es deshalb sorgfältig durch.

Excel-Tipps: Jede Woche neu

Excel-Tipp 16: Dynamische Diagramme erzeugen

LinkedIn Learning kostenlos und unverbindlich testen!

Jetzt testen Alle Abonnements anzeigen
René Martin hat in seiner Arbeit als Trainer zahllose Excel-Tipps gesammelt. Mit diesen Tipps lassen sich typische Anwendungsaufgaben schneller und eleganter lösen oder Fallstricke vermeiden. Dieses Wissen gibt er Ihnen in kleinen Häppchen Woche für Woche weiter. Seien Sie gespannt, welchen kleinen Leckerbissen er in der neuen Woche für Sie bereithält. Und mehr als 10 Minuten brauchen Sie nicht zu investieren.
14:01
  Lesezeichen setzen

Transkript

Hallo, ich heiße René Martin und unterrichte Excel. In den vielen Schulungen kam schon häufig die Frage nach dynamischen Diagrammen. Nun, Sie wissen, selbst wenn sich die Zahlenwerte ändern werden auch die Balken, Säulen, Linien angepasst. Allerdings, wenn ein neuer Datensatz hinzukommt oder eine neue Reihe, dann soll dynamisch eine neue Linie eingefügt werden. Oder noch komplexer, über ein Drop-down-Feld, ein Kombinationsfeld also, wird ein Jahr ausgewählt. Das Diagramm greift sich nun die entsprechenden Daten und erstelle damit das entsprechende Diagramm. Ich zeige Ihnen, wie man bei so etwas vorgehen kann. Ein dynamisches Diagramm. Ich zeige Ihnen mal, was ich hier gebastelt habe. Wenn ich hier von der Jahreszahl 2017 auf das nächste Jahr 2018 umschalte, sehen Sie, verändert sich diese Bevölkerungspyramide. Es geht hier um die Bevölkerung in Deutschland, der Altersaufbau für die entsprechenden Jahre für die entsprechende Generation. Ich gehe mal weiter, oh, das geht aber stark weiter und nehme mal so was oder ganz am Ende 2060 oder ich gehe zurück in die Vergangenheit, so etwas. Interessant! Das heißt, basierend auf dieser Drop-down-Liste haben wir ein dynamisches Diagramm, das sich natürlich die entsprechenden Daten in einer Tabelle sucht und dann auswertet, heißt verwendet. Wie habe ich denn so was gebastelt? Ist nicht ganz trivial, aber wie Sie sehen, es funktioniert. Schritt eins: ich brauche die Daten. Hier sind die Originaldaten, hochgerechnet vom Jahr 2009,10,11, wo wir die entsprechenden Jahreszahlen haben, für männlich, weiblich hier insgesamt, hier dann verteilt auf die entsprechenden Altersinformation. So habe ich die Tabelle nicht brauchen können oder wollen, vor allen Dingen, weil einer nach links, einer nach rechts zeigt, ich habe eine der beiden Kategorien negativ verwendet in dem Beispiel die Männer. Das heißt, ich habe hier links die Männer genommen und habe den Männern jeweils mit einem "-" ein Minus hinzugefügt Ich mach mal eine neue Tabelle, gehe zurück auf die alte Tabelle, markiere den Bereich mit "Strg+*" Steuerung mit dem Multiplikationszeichen, kopiere den Bereich auf die neue Tabelle, an die gleiche Stelle, weil wir hier ja Bezüge haben, Verweise haben. Ich fange mal bei A12 an, einfügen, haben wir den Bereich, wunderbar. Er fängt an bei 2009 und er hört auf, ich fahr mal runter, "Strg+Pfeil runter" im Jahr 2060. Das heißt mit anderen Worten, Ich kann nebendran eine Drop-down-Liste basteln. Dazu mache ich eine Hilfsspalte: "2009", Ich habe jetzt keine Lust, die runterzuziehen, deshalb markiere ich das Ganze, vielleicht so weit, hol mir, hier oben ist das Menüband noch ausgeblendet, das Menüband. Registerkarte, und hole jetzt von dieser Anfangszahl eine Reihe und die Reihe soll natürlich in Einer-Schritten hochzählen bis ins Jahr 2060. Dann habe ich alle Zahlen, "Strg+Pfeil nach unten" bis ins Jahr 2060 hier eingefügt. Ich schreib mal oben drüber "Jahr", hier kommt eine Drop-down-Liste rein, das heißt, mithilfe der Datenüberprüfung, "Daten" "Datenüberprüfung" mache ich eine Drop-down-Liste, die eine Liste verwendet, basierend auf der Quelle hier neben dran, von hier "Shift+Strg+Pfeil nach unten", bis hierhin, "OK", "Enter", abschicken. Schauen wir nach. Im Moment steht noch nix drin, aber jetzt kann ich auswählen: 2011, 2016 und so weiter. Jetzt suchen wir diese Zahl, die wir hier ausgewählt haben, natürlich neben in dieser Zeile. Wo bitte ist die Zeilennummer? Es gibt zwei Möglichkeiten, das Ganze ist in Dreier-Schritten, das heißt, ich könnte natürlich die Jahreszahl mal drei plus die entsprechende Zahl multiplizieren, dann käme ich auf die Zeilenummer. oder ich kann es auch mit der Funktion, ich schreib mal oben drüber "Zeile", mit der Funktion "VERGLEICH" machen, also "=VERGLEICH", such mal bitte die Zahl, wo suchst du sie? Antwort: ganz links. Fangen wir bei A1 an, bis... sagen wir mal hier runter, so etwa, können übers Ziel rausschießen, Semikolon, wie suchen wir? Wir versuchen natürlich "Genau", eine exakte Suche, "OK", "Enter", abschicken. Und jetzt stelle ich fest, ein Fehler. Warum, das sehen Sie sofort. Hier habe ich das Jahr 2016 rechtsbündig, dagegen hier drüben steht, basierend auf der Datenquelle, das Jahr linksbündig als Text. Das berühmte alte Text-Zahl-Spiel. Entweder, ich müsste die linken Jahreszahlen in Zahlen verwandeln oder ich kann natürlich die Funktion "VERGLEICH" auch anweisen, bitte nimm nicht die Information aus AB12, sondern bitte nimm die in einen Text umgewandelte Information und zwar als Null, als Standardzahl formatierter, umgewandelter Text als Zahlinformationen und jetzt können Sie es vergleichen. Jetzt sagt er, 2016 finde ich in vierunddreißigster Zeile. Schauen wir mal nach. Vierunddreißigste Zeile, tatsächlich, hier steht 2016, klasse. So, ich fahre wieder nach oben, ich fahre wieder nach rechts, damit haben wir das Jahr als Drop-down und darüber ermittelt die Zeileninformation, wo das steht. Jetzt muss ich den Bereich der Männer und Frauen ermitteln. Schauen wir mal nach. Angenommen, der Anwender wählt "2009", dann muss er ausgehend von der Zelle 1,2,3,4 nach rechts gehen und davon die "Männer"-Zeile greifen. Bei den Frauen natürlich vier nach rechts, eins nach unten und davon die "Frauen"-Zeile. Das heißt, "2009" würde in dreizehnter Zeile stehen. Schauen wir mal kurz an. "2009" steht in dreizehnter Zeile. Das heißt, unsere Männer, Frauen, weiblich soll er sich holen, "=" mit der Funktion BEREICH.VERSCHIEBEN. Bitte fang mal an, bei der Zelle A1, ich mache hier einen absoluten Bezug. Von dieser Zelle geht es runter, wie viele Zellen? Antwort: so viele Zellen runter, aber Achtung, das ist nicht die dreizehnte Zeile, sondern ich gehe ja von A1 aus, A1+13 würde ich in A14 landen, da ich aber in A13 landen will, muss ich von der Zahl 1 abziehen. Machen wir das erstmal absolut. F4-1, eins wieder nach oben, weil ich ja von der ersten Zelle angefangen habe. Spalten, wie viel Spalten gehen wir rüber? Antwort: vier Stück, habe ich gezählt. Der Bereich, den wir uns greifen, der hat eine Höhe, die Höhe lautet, eine Zeile hoch und die Breite sind 21. Das habe ich vorher schon abgezählt. Klammer zu, BEREICH.VERSCHIEBEN Ich bekomme eine Fehlermeldung, klar, weil BEREICH.VERSCHIEBEN liefert mir einen Bereich, er hätte ganz gerne eine Information in der Zelle, das heißt, damit ich aus diesem Bereich etwas extrahieren kann, mache ich mal das MAXIMUM, Klammer zu, "OK", und bekomme "-24" Wow, sehen wir, die größte Zahl von diesen negativen dürfte hier -24 sein. Das heißt mit anderen Worten, wenn ich die Formel jetzt markiere, kopiere, eine Zelle tiefer nochmal einfüge, und bei den Frauen nicht -1, sondern -1, +1, eins tiefer. "OK", bestätigt, haben wir hier 3.448, schauen wir schnell nach, das müsste in dieser Zeile sein, 3,3,3, sehen Sie schon, da ist sie, 3.448 tatsächlich, die Zahl taucht auf. Das heißt, der Bereich, ohne Maximum natürlich, scheint zu stimmen, fahren wir wieder rüber. So, warum mache ich das Ganze in Excel? ich will doch eigentlich ein Diagramm haben. Nun, das Diagramm muss leider einen Namen verwenden und der Name muss eben diese Formel verwenden. Deshalb das Ganze so ein bisschen umständlich, Das heißt, ich weiß jetzt, in welchem Bereich mein Diagramm suchen soll. Nun, machen wir erstmal ein Diagramm. Das heißt, ich fahre hier nach links, Ich hätte ganz gerne euch drei Informationen, "Strg-Taste", ab hier, nicht die Gesamtsumme aus der Spalte "D" die hätte ich vorher auch mit Formel wegnehmen können, habe ich nicht, "OK", dieser Bereich. Aus diesem Bereich, mach mal bitte, "Einfügen" "Balkendiagramm", so eins, wunderbar. Das werden wir am Ende noch ein klein bisschen hübscher machen, jetzt erstmal nur so weit. Ich hätte ganz gerne die Orangenen auf der gleichen Höhe wie die Blauen, das heißt, ich muss euch, "rechte Maustaste" so formatieren, dass die Überlappung nicht 0 Prozent, sondern 100 Prozent, volle Überlappung ist. Eigentlich zeigt ja der eine nach rechts, und der andere auch nach rechts, dann werden Sie eben echt überlappt, aber da der eine negativ nach links zeigt, der andere positiv nach rechts, funktioniert das Ganze. Gut. Hier "Daten auswählen" zeigt er mir jetzt an, die Männer greifen zu auf den Bereich E13 bis X13 und der Witz ist, den Bereich muss ich ersetzen durch den Bereich, den wir hier in der Zelle zusammengebastelt haben. Problem: Ich kann leider, leider die Formel nicht direkt im Diagramm eintragen Das muss man über einen Umweg machen und dieser Umweg heißt eben: Name und das ist der letzte Schritt, den wir noch brauchen, das heißt, ich muss hier diese Formel, BEREICH.VERSCHIEBEN, markieren, aber Achtung, ohne MAX, das brauche ich hier ja nicht. Das sind jetzt die Männer, den kopier ich, "Abbrechen" Ich lege über "Formeln" über den "Namens-Manager" einen neuen Namen an und wie Sie sehen, es gibt schon Frauen und Männer, natürlich von dem anderen Diagramm. Ich lege hier die "Männer" an, die waren hier oben. Ich nenne sie "Männer2" Die "Männer2" beziehen sich auf "=", "Strg+V", einfügen, diesen Bereich, "OK", dann habe ich meine "Männer2", wunderbar. Das Gleiche kann ich mit den Frauen machen. "Neu", "Frauen2", die beziehen sich auf "=" diesen Bereich, aber Achtung, eins tiefer, nicht "-1", sondern "0" die Zeile. Damit habe ich die "Frauen2". Wunderbar, kann ich das Ganze schließen. So, der letzte Schritt ist, ich muss dem Diagramm sagen, bitte verwende die beiden Namen "Männer2" und "Frauen2" und Achtung, leider leider, Excel verlangt an dieser Stelle den Namen der Datei, also in dem Fall "16_Dynamisches_Diagramm.xlsx" Da ich faul bin und keine Lust habe, den Namen abzuschreiben, wechsle ich über "Datei" "Speichern unter" kopier hier den Namen raus, brech das Ganze wieder ab, wechsle in mein Diagramm und über "Entwurf" "Daten auswählen" sage ich den Männern, ihr bekommt, "Bearbeiten" hier unten nicht diesen harten Bereich, sondern "=" jetzt Achtung, in meinem Namen tauchen Sonderzeichen auf, ein Unterstrich und ein Punkt, deshalb muss ich hier mit einem einfachen Hochkomma arbeiten. Hochkomma, "Strg+V", füge ich es ein, ".xlsx" nicht vergessen, Hochkomma, der Name ist zu Ende, Ausrufezeichen. Damit sage ich ihm, das ist der Name der Datei. in dieser Datei hat "Männer2" einen Namen, "OK", und damit haben wir das Erste geschafft. Das Gleiche bei den Frauen. Auch hier wieder "=" Hochkomma Dateiname Hochkomma Ausrufezeichen für Dateiname-Ende, "Frauen2", "OK". und auch die Frauen sind fertig, "OK". Schauen wir uns das Ganze mal an. So kann man das machen, dass man beides sieht. bestimmt, ist noch nicht ganz hübsch. Wenn ich jetzt hier das Jahr wechsle, Wow, sehen Sie, haben wir ein dynamisches Diagramm. Eine keine Spielerei, ich hätte hier auf dem Diagramm natürlich noch ganz gerne "Entwurf" als Diagramm-Element eine "Datenbeschriftung" am besten am Ende außerhalb, so, und hätte natürlich gern die Männer nicht negativ, sieht irgendwie doof aus, deshalb formatiere ich diese Zahlenreihe "Datenbeschriftung" "Formatieren" hier als Zahl, indem ich sage, ich hätte ganz gerne ein Format für die positiven Zahlen, eine Null haben wir hier nicht, interessiert mich nicht, für die negativen Zahlen auch eine Null, die werden nicht negativ, sondern eben auch positiv dargestellt, "Hinzufügen", schließe das Ganze. Letzter Versuch, wenn ich jetzt hier, klick, die Zahl ändere, die Jahreszahl ändere, sehen Sie, er ändert das Ganze auch. Das Ganze kann man natürlich noch etwas größer schieben, die Balken formatieren und so weiter, das ist klar, aber mir ging es hier darum, Ihnen zu zeigen, dynamische Diagramme müssen leider Namen verwenden. Namen werden über Formeln im Namens-Manager eingefügt. Mein Tipp: Testen Sie die Namen vorher in Excel und wenn Sie diese Namen gefunden haben, können Sie eben über eine Auswahlliste oder über eine sich vergrößernde Liste, mit mehr Spalten, mehr Zeilen und so weiter von dem Diagramm dynamisch auf diese Bereiche zugreifen.