Schnelle Analyse mit dem Schnellanalyse Tool

Wussten Sie schon? Excel verfügt jetzt über ein Schnellanalyse-Tool.

⚡📈 Wählen Sie einige Daten aus und das Symbol für die Schnellanalyse erscheint unten rechts mit vielen Optionen, die in folgende Bereiche unterteilt sind:

  • Formatierung
  • Diagramme
  • Ergebnisse (Summen)
  • Tabellen und
  • Sparklines (Mini-Diagramme in einzelnen Zellen).


Ein Beispiel: Wenn Sie mehrere Spalten zu summieren haben, können Sie das Schnellanalyse-Tool wie folgt verwenden.

  1. Selektieren Sie Ihre Daten
  2. Wählen Sie das angezeigte Schnellanalyse-Tool (oder verwenden Sie das Shortcut Strg Q)
  3. Wählen Sie Ergebnisse
  4. Wählen Sie Summe

🤩 Probieren Sie es aus!

 

Fixieren Sie Ihre Formeln

❌😣 Ein Mandant wollte den prozentualen Anteil des Umsatzes für alle GuV-Positionen berechnen. Eine solche "gemeinsamer Größe GuV" ist nützlich für Vergleiche z. B. von Jahren oder von Geschäftsbereichen. Seine erste Formel in Zeile 3 (siehe Screenshot) ergab ein korrektes Ergebnis. Aber als er diese nach unten kopierte, bezog sich der Nenner in jeder Formel auf die Zeile darüber und nicht auf den Umsatz in Zeile 2.

📗 Das liegt daran, dass Zellbezüge in kopierten Formeln automatisch von Excel angepasst werden, z. B. um sich auf Zellen in derselben relativen Zeile oder Spalte zu beziehen. Der Fachbegriff dafür ist relative Referenzierung. Oft ist dies sinnvoll, z. B. Sie möchten, dass eine kopierte SUMME-Formel immer die Werte in derselben Spalte wie die SUMME-Formel addiert. Aber manchmal (wie in diesem Beispiel) ist das nicht das, was Sie wollen.

✔🤑 Hier soll sich der Nenner in der Formel immer auf den Umsatz in Zeile 2 (Zelle B2) beziehen, also muss die Formel = (Zelle mit Wert)/B$2 lauten. Das Dollarzeichen vor der Zeilennummer 2 fixiert sie, so dass sie sich nicht ändert, wenn Sie die Formel kopieren (siehe Screenshot).

💡 Sie können eine Spalte oder eine Zeile oder beides fixieren, indem Sie das Dollarzeichen manuell an der/den entsprechenden Stelle(n) in Ihrer Formel eintippen. Alternativ können Sie einfach die F4- Taste drücken, wenn sich der Cursor in oder neben dem entsprechenden Zellbezug befindet. Dadurch werden sowohl die Zeile als auch die Spalte fixiert (Dollarzeichen werden vorangestellt). Drücken Sie erneut F4, um nur die Zeile zu fixieren. Drücken Sie erneut F4, um nur die Spalte zu fixieren. Drücken Sie erneut F4, um die Fixierung vollständig aufzuheben, wie die folgende Beispielsequenz zeigt.

B2 ➡ $B$2 ➡ B$2 ➡ $B2 ➡ B2

Sie können sich diese Reihenfolge folgendermaßen merken: beide, Zeile, Spalte, keine. Mit etwas Übung wird es zur zweiten Natur, zweimal F4 zu drücken, um nur die Zeile zu fixieren, oder dreimal, um nur die Spalte zu fixieren.

Als allgemeine Regel für das Fixieren gilt: Fixieren Sie so viel wie nötig, aber so wenig wie möglich. Im Beispiel ist es nur notwendig, die Zeile zu fixieren. Überprüfen Sie immer die Ergebnisse, um eventuelle Fehler zu erkennen und zu beheben.

Dies ist nur einer von vielen how2excel-Tipps, die Sie in meinem Best Practice-Buch finden werden: „Avoid Excel Horror Stories“, erhältlich bei Amazon.

Das Problem mit kopierten Exceldiagrammen

Sie haben ein Diagramm angepasst und dann sowohl das Diagramm als auch die zugehörigen Daten in einen anderen Bereich kopiert, der für neue Daten bereit ist. Dann ändern Sie die kopierten Daten, z. B. um die Umsätze eines anderen Unternehmens anzuzeigen und.... oh! Es gibt ein Problem...

Was Sie erwarten

Das kopierte Diagramm sollte mit den kopierten Daten verknüpft werden - das ist zwar logisch, aber nicht das, was tatsächlich passiert.

Was Sie bekommen

Das kopierte Diagramm ist mit den Originaldaten verknüpft, so dass es sich nicht ändert, wenn Sie die kopierten Daten anpassen. Die ausgewählten Daten für das kopierte Diagramm müssen manuell bearbeitet werden. Das ist sehr mühsam.

Und wenn Sie das tun, werden Sie möglicherweise ein zweites Problem feststellen...

Wenn Sie das ursprüngliche Diagramm bearbeitet haben, z. B. Linienfarben geändert, benutzerdefinierte Markierungen und Datenbeschriftungen hinzugefügt haben, gehen diese meist verloren, wenn Sie die Datenquelle im kopierten Diagramm ändern. ☹

Das Workaround

Sie können das gesamte Blatt kopieren und dann die Daten und das Diagramm auf dem kopierten Blatt ausschneiden und in das ursprüngliche Blatt einfügen, aber das ist ineffizient. Möglicherweise müssen Sie auch die Achsenbeschriftungen bearbeiten, z. B. für die Jahre, wenn diese mit einer Zeile am oberen Rand Ihres (kopierten) Arbeitsblatts verknüpft waren.

Die richtige Lösung

Liebes Excel-Entwicklungsteam bei Microsoft... könnt ihr das bitte beheben?

🗳 Ihre Meinung zählt

Bitte liken Sie meinen entsprechenden Beitrag auf der offiziellen Excel-Community, indem Sie auf das Pfeil-nach-oben-Symbol klicken

Excel-Berechnungsoptionen und Shortcuts

Berechnungsoptionen

Wenn Sie eine große Arbeitsmappe mit vielen Nachschlageformeln oder komplexen Berechnungen haben, kann die Neuberechnung der gesamten Arbeitsmappe sehr langsam sein, so dass Sie die Option "Manuelle Berechnung" aktivieren können.

Es gibt zwei Möglichkeiten, dies zu tun:

  1. Datei, Optionen, Formeln


  2. Ribbon Formeln, Berechnung, Berechnungsoptionen (klicken Sie auf den Abwärtspfeil, um die Dropdown-Liste zu öffnen)

In jedem Fall sehen Sie drei oder vier Optionen:

  • Automatisch (Standardeinstellung) - wird im Allgemeinen empfohlen, damit Sie die Auswirkungen von Änderungen in der Kalkulationstabelle sofort sehen können.

  • Automatisch außer bei Datentabellen - empfohlen, wenn Sie Datentabellen in Ihrer Kalkulationstabelle haben. Datentabellen eignen sich hervorragend für die Berechnung der Ergebnisse verschiedener Szenarien, um sie zu vergleichen, erfordern aber mehrere Berechnungen Ihres Modells, so dass diese Option in solchen Fällen sehr zu empfehlen ist, um Ihr Modell nicht unnötig zu verlangsamen.

  • Manuell - empfohlen für große und langsame Arbeitsmappen - dies erleichtert die Arbeit mit Ihrer Datei, aber Sie können die Auswirkungen von Änderungen nicht sofort sehen, daher müssen Sie eine Neuberechnung manuell einleiten (siehe unten).

  • Manuell: vor dem Speichern die Arbeitsmappe neu berechnen - diese zusätzliche Option ist nur über Datei, Optionen verfügbar. Ich empfehle Ihnen, diese Option zu deaktivieren, wenn Sie Ihre Datei häufig speichern (z. B. um im Falle eines Absturzes eine aktuelle Kopie Ihrer Datei zu haben), aber nicht möchten, dass Excel Ihre Datei jedes Mal neu berechnet, da dies bei großen, langsamen Modellen eine Weile dauern kann.


Unter Datei, Optionen erhalten Sie auch Optionen für iterative Berechnungen. Diese können Sie einschalten, wenn Sie logische Zirkelbezüge in Ihrem Modell haben, z.B. wirken sich Zinskosten auf den Endsaldo der Bank aus, der wiederum die Zinskosten beeinflusst. Die Standardeinstellungen (100 Iterationen, maximale Änderung 0,001) sind im Allgemeinen gut, aber bei Bedarf können Sie die Anzahl der Iterationen erhöhen und/oder die maximale Änderung verringern. Beachten Sie, dass die Genauigkeit hier auch für die Berechnungen der Zielsuche verwendet zu werden scheint, so dass Sie auch solche Änderungen vornehmen können, um die Genauigkeit der Zielsuchergebnisse zu verbessern.

 

Neuberechnungsoptionen und Shortcuts

Wenn Sie die manuelle Berechnungsoption verwenden, müssen Sie zu einem geeigneten Zeitpunkt (z. B. wenn Sie einen neuen Modellabschnitt abgeschlossen haben) manuell eine Neuberechnung starten. Auch hier gibt es einige Optionen, die zum Teil mit der Multifunktionsleiste/Maus, zum Teil mit Tastenkombinationen wie folgt zu erreichen sind.

  • Jetzt berechnen (1. kleine Symbol, Tastenkürzel F9) - berechnet alle geöffneten Arbeitsmappen neu. Schließen Sie unnötige Dateien, um dies zu beschleunigen.

  • Blatt berechnen (2. kleine Symbol, Tastenkürzel Umschalt F9) - berechnet nur das gerade aktive Blatt neu. Dies ist schneller als die Neuberechnung der gesamten Arbeitsmappe, wenn Sie nur ein einzelnes Blatt aktualisieren/prüfen müssen.

  • Eine einzelne Zelle berechnen (nur Tastenkürzel: F2 gefolgt von der Eingabetaste) - berechnet nur die ausgewählte Zelle neu. Dies ist die schnellste Option, aber nur für eine einzelne Zelle, was bei großen Blättern nützlich ist, bei denen selbst eine Blattberechnung eine Weile dauert. Wenn Sie einen Block von Zellen markieren, können Sie wiederholt die F2- und Eingabetasten drücken, um jede Zelle der Reihe nach zu berechnen; Excel springt dann zur nächsten Zelle.

  • Vollständige Neuberechnung erzwingen (nur Tastenkombination: Strg Umschalt Alt F9) - dies kann in den seltenen Fällen nützlich sein, in denen die Modellergebnisse die kürzlich vorgenommenen Änderungen nicht widerzuspiegeln scheinen. Um die Berechnungszeit zu verkürzen, berechnet Excel normalerweise nur die Nachfolger von Zellen, die sich geändert haben (und deren Nachfolger usw.) sowie alle volatile Funktionen wie HEUTE, INDIREKT und BEREICH.VERSCHIEBEN neu. Normalerweise funktioniert dies sehr gut. Gelegentlich kann es jedoch nützlich sein, eine vollständige Neuberechnung zu erzwingen, was jedoch eine Weile dauern kann. Schließen Sie daher unnötige Dateien bevor Sie beginnen, um dies zu beschleunigen.

 

toolKCit Add-in

Für mehr Bedienungskomfort und zusätzliche Berechnungsmöglichkeiten und Tools können Sie mein Excel-Add-In ToolKCit bei eloquens für kleines Geld kaufen und installieren. Verwenden Sie den Rabattcode HOW2EXCEL für 50% Rabatt.

  • Set manual calculation / Manuelle Berechnung einstellen - setzt die Berechnung auf manuell und keine Neuberechnung vor dem Speichern. Dies ist dasselbe wie über Datei, Optionen, aber mit einem einzigen Mausklick.

  • Calculate with timer / Berechnen mit Timer - führt eine normale Neuberechnung durch (wie F9), gibt aber an, wie lange sie gedauert hat. Nützlich, wenn Sie versuchen, Ihr Modell zu beschleunigen und alternative Formeln für Berechnungen vergleichen möchten, um zu sehen, welche schneller ist.

  • Force full calculation / Vollständige Neuberechnung erzwingen - bewirkt dasselbe wie die oben genannte Tastenkombination, jedoch mit einem Mausklick anstelle der Tastenkombination mit 4 Tasten.

Garys goldene Grundregeln

😲 Fast 90% aller Spreadsheets enthalten Fehler und viele sind auch schwer zu benutzen!
💰 Das kann teuer werden in Form von Geld, Zeit und Ruf.

Wie kann man also solche Probleme vermeiden?

Für mein neues Buch bin ich zu den Grundlagen zurückgekehrt und habe meine Best Practice-Ratschläge komplett überarbeitet. Das Ergebnis ist meine neue, verbesserte...

🥇 Garys goldene Grundregeln

Diese Regeln helfen Ihnen sicherzustellen, dass Ihre Spreadsheets klar und einfach zu handhaben sind, was wiederum das Risiko von Fehlern sowohl bei der Entwicklung als auch bei der Verwendung verringert.

1. Verwenden Sie eine klare, logische Struktur der Arbeitsmappe
Entscheiden Sie, welche Arbeitsblätter Sie benötigen, geben Sie ihnen klare Namen und ordnen Sie sie.

  • Verteilen Sie den Inhalt logisch auf die Arbeitsblätter
  • Organisieren Sie die Arbeitsblätter in Abschnitte, die den Datenfluss von den Eingaben zu den Ausgaben widerspiegeln
  • Fügen Sie ein Deckblatt ein, idealerweise mit Hypertext-Links, um die Navigation zu erleichtern
  • Trennen Sie Eingaben, Berechnungen und Ausgaben voneinander

 

2. Halten Sie Ihre Arbeitsblätter so klar und einfach wie möglich
Stellen Sie sicher, dass jedes Arbeitsblatt übersichtlich und einfach zu benutzen ist.

  • Verwenden Sie eine logische Struktur innerhalb jedes Arbeitsblatts, z. B. Verwenden Sie einheitliche Spalten, Zeilen, Formeln und ganze Blätter
  • Formeln
    • Stellen Sie sicher, dass die Berechnungen von links nach rechts und von oben nach unten fließen
    • Verwenden Sie das KISS-Prinzip: kurz und einfach halten (auf Englisch 'Keep It Short and Simple')
    • Befolgen Sie die Prioritäten der Formeln: (i) korrekt, (ii) verständlich und (iii) kurz
    • Befolgen Sie das COUNT-Prinzip: einmal berechnen, mehrmals verwenden (auf Englisch ‘Calculate Once, Use Numerous Times')

3. Verwenden Sie ein klares, sauberes, einheitliches Design
Stellen Sie sicher, dass die gesamte Arbeitsmappe ein klares, professionelles Erscheinungsbild hat.

  • Verwenden Sie einheitliche (vom Unternehmen festgelegte) Schriftarten, Farben, Stile und Datenformate
  • Verwenden Sie die vier wichtigsten Gestaltungsprinzipien: Kontrast, Wiederholung, Ausrichtung und Nähe
  • Kennzeichnen Sie alle Eingaben deutlich, z. B. als graue Zellen
  • Minimieren Sie nicht-datenbezogene Tinte wie Zellrahmen

 

4. Schränken Sie die Zugriffs-, Eingabe- und Änderungsmöglichkeiten ein
Stellen Sie sicher, dass nur befugte Benutzer auf Ihre Kalkulationstabelle zugreifen und sie ändern können und dass die Eingaben gültig sind.

  • Schränken Sie den Zugriff auf die Kalkulationstabelle ein, indem Sie Ordner mit eingeschränktem Zugriff und Passwörter verwenden
  • Verwenden Sie gegebenenfalls eine Datenüberprüfung, um sicherzustellen, dass die Eingaben gültig sind - siehe mein Video für Details
  • Ziehen Sie in Erwägung, Arbeitsmappen so zu schützen, dass Änderungen nur in Eingabezellen vorgenommen werden können - siehe mein Blog für Details

 

5. Schreiben Sie eine Anleitung für die Benutzer
Stellen Sie sicher, dass die Benutzer wissen, was sie in welcher Reihenfolge tun müssen, z. B. wie sie das Modell mit neuen Daten aktualisieren.

  • Erstellen Sie ein Arbeitsblatt mit einer Aktionsliste, der die Benutzer folgen sollen
  • Fügen Sie eine Statusspalte hinzu, um sicherzustellen, dass der Fortschritt klar ist

 


Haben Sie Interesse, mehr zu erfahren? Mein Buch (auf Englisch) enthält viele weitere Details sowie zahlreiche Beispiele und Screenshots. Lesen Sie mehr auf Amazon.