Einfach einzugebende Formeln für Blatt- und Dateinamen

Hier ist ein großartiger Trick, um mit Hilfe der Autokorrektur ganz einfach eine Formel einzugeben, die den Namen des Blattes oder der Datei anzeigt. Einmal eingerichtet, können Sie die Formel mit einer einfachen Eingabe von zwei Buchstaben so oft verwenden, wie Sie möchten.

Ich verwende diese Formeln häufig, z.B. um ein flexibles Vorlagenblatt für meine Excel-Modelle zu erstellen. Wenn ich die Vorlage kopiere und umbenenne, wird der Blattname (z. B. in Zelle A1) automatisch aktualisiert... fantastisch!

Einrichtung
So richten Sie es für den Blattnamen ein (eine einmalige Aufgabe).

  1. Diese Formel kopieren
    =TEIL(@ZELLE("dateiname";A1);FINDEN("]";@ZELLE("dateiname";A1))+1;256)

  2. Gehen Sie zu Datei, Optionen, Dokumentprüfung, AutoKorrektur-Optionen...


  3. Geben Sie unter "Ersetzen:" BN (für Blattname) ein und fügen Sie unter "Durch:" die Formel ein, die Sie in Schritt 1 kopiert haben, dann "Hinzufügen" und zweimal "OK".

Um die Formel für den Dateinamen einzurichten, führen Sie dieselben Schritte aus, ersetzen aber DN (für Dateiname) durch diese Formel
=TEIL(@ZELLE("dateiname";A1);FINDEN("[";@ZELLE("dateiname";A1))+1;FINDEN(".xls";@ZELLE("dateiname";A1))-(FINDEN("[";@ZELLE("dateiname";A1))+1))

Verwendung

Gehen Sie nun einfach in eine leere Zelle, geben Sie BN (Leerzeichen) ein und schon haben Sie Ihre Formel für den Blattnamen!

Oder geben Sie DN (Leerzeichen) ein und Sie haben Ihre Formel für den Dateinamen!

(Technischer Hinweis: Diese Formeln verwenden die Funktion ZELLE, die nur funktioniert, wenn Sie Ihre Datei gespeichert haben, denn nur dann hat sie einen vollständigen Pfadnamen).

Viel Spaß damit!

Balkendiagramm mit Werten über/unter einem Minimum

Sie haben z.B. Plan-Banksalden für verschiedene Szenarien und auch einen Mindest- oder Zielwert.

Wäre es nicht toll, wenn Sie diese in einem Diagramm übersichtlich darstellen könnten und die Balkenfarben automatisch wechseln würden, je nachdem, ob der Szenario-Wert über oder unter dem Minimum liegt?

Nun, das können Sie! Ich werde Ihnen jetzt Schritt für Schritt zeigen, wie.

  1. Stellen Sie Ihre Daten mit zwei zusätzlichen Zeilen zusammen
  • Stellen Sie die Szenario-Werte in einer Zeile und die Mindestwerte in einer anderen

  • Zwei zusätzliche Zeilen zeigen die Werte, die (i) dem Minimum entsprechen oder darüber liegen und (ii) unter dem Minimum liegen. Wir werden diese als zwei separate Reihen im Diagramm darstellen, damit wir ihnen unterschiedliche Farben geben können.

2. Erstellen Sie Ihr Diagramm

  • Fügen ein gestapeltes Säulendiagramm (nicht eines gruppiertes Säulendiagramm) ein


  • Klicken Sie mit der rechten Maustaste in das Diagramm und wählen Sie "Daten auswählen..."

  • Für die Legendeneinträge (Reihen) wählen Sie die drei Serien aus oder fügen Sie sie hinzu: Über dem Minimum, Unter dem Minimum und Minimum



  • Für die Werte der horizontalen Achse fügen Sie, z. B. die Szenarionamen hinzu

  1. Formatieren Sie Balken und Linien im Diagramm
  • Klicken Sie mit der rechten Maustaste auf einen Balken der Reihe "Minimum" und wählen Sie "Diagrammtyp ändern..."

  • Für die Serie Minimum wählen Sie "Linie"
  • Klicken Sie mit der rechten Maustaste auf einen Balken der Reihe "Über Minimum" und wählen Sie "Datenreihen formatieren..." oder klicken Sie mit der linken Maustaste und verwenden Sie die Tastenkombination Strg 1

  • Ändern Sie die Farbe, z.B. in hellgrün

  • Führen Sie dasselbe für die Reihe "Unter Minimum" durch und ändern Sie die Farbe, z. B. in ein helles Rot

  • Führen Sie dasselbe für die Linienserie "Minimum" durch und ändern Sie die Farbe der Linie, z. B. in dunkelrot, und machen Sie sie eventuell zu einer gepunkteten Linie


  1. Hinzufügen und Formatieren von Datenbeschriftungen
  • Klicken Sie mit der rechten Maustaste nacheinander auf jede der drei Reihen und wählen Sie "Datenbeschriftungen hinzufügen"

  • Um zu vermeiden, dass bei "Über dem Minimum" Nullen erscheinen, wenn der Wert unter dem Minimum liegt (und umgekehrt), formatieren Sie die Zahlen der Datenbeschriftungen so, dass keine Nullen erscheinen: Klicken Sie mit der rechten Maustaste auf ein Datenetikett und wählen Sie " Datenbeschriftungen formatieren..." oder klicken Sie mit der linken Maustaste und verwenden Sie die Tastenkombination Strg 1. Geben Sie das benutzerdefinierte Format #.##0_(;(#.##0); und klicken Sie auf "Hinzufügen". Dieses Format hat drei Komponenten: positive Zahlen; negative Zahlen; Null (leer)


  • Schließlich können Sie einzelne Datenbeschriftungen für die Reihe "Minimum" auswählen und alle bis auf die letzte löschen, die Sie rechts anzeigen lassen können


  1. Diagrammtitel hinzufügen und die Legende aufräumen
  • Fügen eines eindeutigen Diagrammtitels mit Einheiten (z. B. EURk) hinzu

  • Wählen Sie die Legenden für "Über dem Minimum" und "Unter dem Minimum" aus und löschen Sie sie, da der Titel des Diagramms eindeutig genug ist.

Et voilá... Ihr großartiges Diagramm ist fertig!

Wenn Sie möchten, können Sie Ihr Diagramm als Vorlage speichern und wieder verwenden.

Allgemeine Tipps, um den besten Chart-Typ auszuwählen finden Sie in meinem besten Chart-Blog.

Alle Modelle sind falsch, aber einige sind nützlich

Dieses Zitat stammt aus einem 1976 veröffentlichten Aufsatz des britischen Statistikers George E. P. Box mit dem Titel "Science and Statistics" (Wissenschaft und Statistik). Obwohl es sich in erster Linie auf die Bereiche Statistik und analytische Modelle bezieht, kann es auch auf Finanz- und andere Modelle angewendet werden, die in Spreadsheets erstellt werden.

Ein Modell ist eine vereinfachte Darstellung der Realität. Der Akt der Vereinfachung bedeutet, dass alle Modelle falsch sind, weil sie nicht alle Elemente der Realität wiedergeben. Für eine vollständige Genauigkeit wäre z. B. eine Karte im Maßstab 1:1 erforderlich. Diese mag zwar genau sein, wäre aber für eine sinnvolle Routenplanung nicht brauchbar - sie wäre einfach viel zu groß und unhandlich.

Wenn wir andererseits einen Verlust an Genauigkeit in Kauf nehmen, können wir ein Modell haben, das "falsch", aber nützlich ist. In der Physik ist zum Beispiel das Bohr'sche Atommodell "falsch", kann aber dennoch sehr nützlich sein, um Ereignisse in der realen Welt vorherzusagen und zu erklären.

Das gleiche Prinzip lässt sich auch auf Spreadsheet-Modelle anwenden. Der Schlüssel liegt darin, den "Sweet Spot der Nützlichkeit" zwischen Genauigkeit und Komplexität auf der einen Seite und Einfachheit und Benutzerfreundlichkeit auf der anderen zu finden.

Das ist nicht immer einfach, aber wie bei den meisten Fertigkeiten kann man mit der Erfahrung besser werden.

Hier ist mein Rat, um Ihnen zu helfen:

1.

Überlegen Sie in der Planungsphase, wie detailliert das Modell sein müssen.

Besprechen Sie sich gegebenenfalls mit Kollegen, Modellpaten oder Nutzern, bevor Sie beginnen.

Denken Sie immer daran:

(i) der Zweck des Modells - welche Inhalte und Funktionen sind relevant und nützlich?

(ii) die Benutzer - welche Funktionen werden sie verstehen und in der Lage sein, sie zuverlässig und effizient zu nutzen (möglicherweise nach einer Schulung)?

2.

Konzentrieren Sie sich auf das Wesentliche und wenden Sie die 80:20-Regel an.

Oft lassen sich 80 % der Ergebnisse durch die 20 % wichtigsten Faktoren erklären, also konzentrieren Sie sich auf diese. Geringfügige Kosten (z. B.) sollten nicht einzeln geplant werden, sondern in ihrer Gesamtheit als Teil einer Position "Sonstige Kosten".

Konzentrieren Sie sich gegebenenfalls zunächst auf die wichtigsten 10 % der Faktoren, um ein funktionierendes Modell zu erstellen, das für eine frühzeitige Entscheidungsfindung genutzt und später erweitert werden kann.

3.

Bevor Sie die Komplexität erhöhen, sollten Sie überlegen, wie groß die Auswirkungen auf die Ergebnisse oder die Benutzerfreundlichkeit sein könnten.

Wenn die Erhöhung der Komplexität nur einen minimalen Zuwachs an Genauigkeit oder Funktionalität mit sich bringen würde, dann sollten Sie die Änderung vielleicht nicht vornehmen. Oder vielleicht würde eine vereinfachte Version ausreichen.

So kann beispielsweise eine Umsatzplanung auf der Ebene einzelner Produkte zu detailliert sein. Eine Planung auf Marken- oder Geschäftsfeldebene kann besser geeignet sein.

Sobald Sie Ihre Entscheidungen getroffen haben, sollten Sie bereit und in der Lage sein, diese zu begründen.

 

Ich möchte mit einem weiteren Zitat von George Box schließen, das die Situation sehr gut zusammenfasst.

"Alle Modelle sind Näherungswerte. Annahmen, ob implizit oder klar formuliert, sind nie exakt wahr. ... Die Frage, die Sie sich stellen müssen, lautet also nicht: "Ist das Modell wahr?" (das ist es nie), sondern "Ist das Modell gut genug für diese spezielle Anwendung?"

Ermitteln Sie Summen und Teilergebnisse

Ermitteln Sie sowohl SUMMEN als auch TEILERGEBNISSE für wichtige Datenspalten, um Ihre Daten besser zu verstehen!

  • Platzieren Sie SUMMEN für wichtige Datenspalten wie z.B. Umsatzwerte oberhalb Ihrer Daten, wo Sie sie leicht sehen können, und nicht unterhalb Ihrer Daten, was Sie zwingt, nach unten zu scrollen - das ist ineffizient, besonders wenn Sie lange Listen haben.

  • Berechnen Sie auch TEILERGEBNISSE. TEILERGEBNIS ist eine sehr nützliche und flexible Funktion, die Ihnen die Summe Ihrer gefilterten Daten anzeigt, was bei der Kontrolle oder Analyse Ihrer Daten sehr praktisch ist.

Die TEILERGEBNIS Funktion kann je nach der Zahl, die Sie in Klammern vor dem Daten-Bereich eingeben, unterschiedliche Ergebnisse liefern. Meistens wollen Sie die Summe, wofür Sie die Zahl 9 benötigen. Andere Möglichkeiten sind Durchschnitt, Anzahl, Maximum und Minimum. Sobald Sie TEILERGEBNIS( eingeben, erhalten Sie eine Dropdown-Liste mit allen Optionen.

Ich empfehle Ihnen, sowohl SUMME als auch TEILERGEBNIS für die wichtigsten Datenspalten in zwei separaten Zeilen zu berechnen, wie im Screenshot gezeigt. Die SUMME aller Beträge in der Spalte ist dann immer sichtbar und kann in der Arbeitsmappe weiterverknüpft werden (falls erforderlich), und das TEILERGEBNIS kann beim Filtern zur Datenanalyse verwendet werden. Wenn keine Filter gesetzt sind, sollten die beiden Formeln das gleiche Ergebnis liefern.

Hinweis: In dem Beispiel im Screenshot habe ich eine Tabelle verwendet, aber sowohl die SUMME- als auch die TEILERGEBNIS-Funktion funktionieren auch mit Daten, die sich nicht in einer Tabelle befinden. In diesem Fall geben Sie einfach die Zellbezüge als Ihren Daten-Bereich ein.

 

Excel Space-Tipp

Wussten Sie schon, dass Sie Formeln mit Leerzeichen versehen können, um sie besser lesbar zu machen?

Nun, das können Sie und das sollten Sie auch! 😊

ImMittelalterwurdenBüchervonMönchenohneLeerzeichenzwischendenWörterngeschrieben.

Fanden Sie das leicht zu lesen? Natürlich nicht - es hat keine Leerzeichen. Aber das war die gängige Praxis in mittelalterlichen Büchern. Im Laufe der Zeit wurden jedoch Leerzeichen zwischen den Wörtern eingefügt, um Texte leichter lesbar zu machen.

Jedoch wenn wir Spreadsheet-Formeln schreiben, folgen wir meist noch den mittelalterlichen Mönchen. Das bedeutet, dass Formeln - vor allem wenn sie länger oder komplexer sind - schwieriger zu lesen sind. Und schwieriger zu schreiben, zu kontrollieren, anzupassen und zu verstehen.

Daher empfehle ich Ihnen, Leerzeichen in Ihre Formeln einzufügen, z.B. nach jedem Argumenttrennzeichen (ein Komma oder ein Semikolon, je nach Ihren regionalen Einstellungen) oder vor jedem Pluszeichen. Dies erleichtert die Lesbarkeit usw. und hat keine Auswirkungen auf die Ergebnisse, da Excel sie ignoriert.

Wenn Sie Leerzeichen in Ihre Formeln "nachrüsten" möchten, erstellen Sie eine Sicherungskopie Ihrer Datei. Suchen Sie dann einfach alle Argumenttrennzeichen (ein Komma oder ein Semikolon) und ersetzen Sie sie durch ein Trennzeichen gefolgt von einem Leerzeichen.