Ungenauigkeit kann genau richtig sein!

Genau oder ungenau?

Zunächst sollten wir uns über den Unterschied zwischen genauen und ungenauen Übereinstimmungen im Klaren sein.

Genaue Übereinstimmungen

Wenn Sie eine Suche mit VERGLEICH, XVERWEIS oder SVERWEIS durchführen, möchten Sie normalerweise eine exakte Übereinstimmung in Ihrer Nachschlagetabelle finden. Sie wollen zum Beispiel den Skontoprozentsatz für einen bestimmten Kunden. Wenn Sie die Kontonummer des Kunden in Ihrer Nachschlagetabelle suchen, möchten Sie das Ergebnis für den bestimmten Kunden finden und nicht für einen anderen Kunden, der zufällig eine ähnliche Kontonummer hat.

In solchen Fällen müssen Sie das optionale Argument 0 für VERGLEICH oder XVERWEIS bzw. FALSCH für SVERWEIS verwenden. Die Daten der Lookup-Tabelle können in beliebiger Reihenfolge stehen, Excel sucht so lange, bis es die erste Übereinstimmung findet, oder meldet einen Fehler, wenn es das gesuchte Element überhaupt nicht findet.

 

Ungenaue Übereinstimmungen

In einigen Fällen kann es jedoch notwendig sein, eine ungenaue Übereinstimmung zu verwenden, wenn das gesuchte Element evtl. nicht genau in der Nachschlagetabelle zu finden ist, z. B. eine Liste von Verkaufsrabatten auf der Grundlage der verkauften Menge (siehe Beispiel 1 unten).

In solchen Fällen benötigen Sie das optionale Argument 1 oder -1 für VERGLEICH und XVERWEIS oder TRUE für SVERWEIS. Die Daten der Nachschlagetabelle müssen dann in aufsteigender oder absteigender Reihenfolge vorliegen, wie in der QuickInfo angegeben (beachten Sie, dass SVERWEIS nur mit Nachschlagedaten in aufsteigender Reihenfolge umgehen kann). Excel sucht dann so lange, bis es entweder eine exakte Übereinstimmung findet oder das Ergebnis zurückgibt, das "nicht zu weit" entfernt ist. Dies lässt sich am besten anhand einiger Beispiele veranschaulichen.

In allen vier Beispielen gibt es eine Eingabezelle (grau hinterlegt) und eine Output-Zelle (grün hinterlegt). Die Output-Zelle verwendet die leistungsstarke Kombination der Funktionen INDEX und VERGLEICH, um das richtige Ergebnis aus der Nachschlagetabelle mit einer ungenauen Übereinstimmung zu erhalten. Sie könnten stattdessen XVERWEIS verwenden (oder vielleicht sogar SVERWEIS, aber ich empfehle diese Funktion nicht, da sie einige Schwächen und Risiken aufweist).

Sie können die Excel-Datei mit allen vier Beispielen hier herunterladen: Ungenaue-Übereinstimmung_Download

  1. Umsatzrabatt
  • Aufgabe: Geben Sie die verkaufte Menge ein, um den Rabattprozentsatz zu erhalten.

     
  • Lösung: Wir müssen die größte "verkaufte Menge" in der Lookup-Tabelle finden, die kleiner oder gleich der Eingabemenge ist, um den entsprechenden Rabattprozentsatz zu erhalten. Wir verwenden daher das Argument der ungenauen Übereinstimmung 1.
  • Nachschlagetabelle: Dies erfordert, dass die Nachschlagetabelle in aufsteigender Reihenfolge sortiert ist.

 

  1. Prüfungsnote
  • Aufgabe: Geben Sie das Prüfungsergebnis in Prozent ein, um die Prüfungsnote zu erhalten.

  • Lösung: Ähnlich wie in Fall 1. Das Argument der ungenauen Übereinstimmung ist ebenfalls 1
  • Nachschlagetabelle: Per Fall 1, aufsteigend sortiert.

 

  1. Zahlungsdatum
  • Aufgabe: Geben Sie das Fälligkeitsdatum der Rechnung ein, um das nächste Zahlungsdatum zu erhalten.
    Dies kann für Liquiditätsplanungstools sehr nützlich sein: verwenden Sie einen Kreditorenbericht, um Zahlungstermine zu ermitteln.

  • Lösung: Wenn wir eine Tabelle in absteigender Reihenfolge nachschlagen müssen, ist die Lösung relativ einfach, aber in der Realität wird eine Liste von Daten wahrscheinlich in aufsteigender Reihenfolge sein. Das macht die Lösung ein wenig komplizierter, aber mit ein wenig Probieren können Sie den richtigen Ansatz finden.
    > Suchdatum: Wir müssen 1 vom Eingabedatum abziehen, um das richtige Ergebnis auch zu erhalten, wenn das Eingabedatum genau mit einem Zahlungsdatum übereinstimmt, sonst erhalten wir das folgende Zahlungsdatum.
    > VERGLEICH findet das größte (d.h. letzte) Zahlungsdatum, das kleiner oder gleich dem Suchdatum ist, d.h. es liefert uns das vorherige Wir müssen also zum Ergebnis von VERGLEICH eins addieren, um das nächste Zahlungsdatum zu erhalten.
  • Nachschlagetabelle: Die Nachschlagetabelle ist in aufsteigender Reihenfolge sortiert (weil sie wahrscheinlich so sein wird), hat aber nur eine Dimension. Die Formel sucht nach dem Fälligkeitsdatum (Eingabe) in der Liste der Zahlungstermine und gibt das nächste Zahlungsdatum aus derselben Spalte zurück.

 

  1. Obstsortierer
  • Aufgabe: Zum Schluss haben wir noch einen Obstsortierer 😊.
    Geben Sie die Größe des Obstes in Zentimetern ein, um die kleinste Kiste zu finden, die groß genug ist, um es aufzunehmen.

  • Lösung: Da wir nach der kleinsten Kiste suchen, die größer oder gleich der Obstgröße ist, müssen wir das Argument der ungenauen Übereinstimmung -1 verwenden.
  • Nachschlagetabelle: In diesem Fall muss die Nachschlagetabelle in absteigender Reihenfolge sortiert sein. Hier gibt es eine zusätzliche Spalte, um deutlich zu machen, in welcher Kiste das Obst landet. Dies wird durch eine einfache WENN-Formel mit einem Obstsymbol bestimmt... dies ist ein Emoji, das Sie durch Drücken der Windows-Taste und . auswählen können. Geben Sie dann das Wort Obst ein, um eine Auswahl von Obstsymbolen zu sehen. Nach meiner Auswahl habe ich die Textfarbe der Spalte "Obst" in ein dunkles Orange geändert.
  • Und Aktion! Hier ist die Lösung in Aktion...



Sie wollen mehr Ergebnisse?

Diese Ansätze sind nicht auf eine einzelne Output-Zelle beschränkt, sondern können in einer großen Tabelle mit Berechnungen verwendet werden, bei denen die Ergebnisse für jede Zeile berechnet werden, wie unten für den Obstsortierer mit einem Diagramm der Ergebnisse zur Analyse gezeigt.


Ich hoffe, dieser Blog hilft Ihnen bei Ihrer Arbeit!

 

Mehr Hilfe

Weitere Erläuterungen zur Verwendung von INDEX und VERGLEICH oder XVERWEIS finden Sie in meinen Videos. Hier erfahren Sie auch, welche Probleme und Risiken mit SVERWEIS verbunden sind.

Ein BASE-Korkenzieher zur Planung von Anlagevermögen und Darlehen

In einem Finanzmodell müssen Sie die Bilanzpositionen planen. Es gibt verschiedene Techniken, um dies zu tun, und die am besten geeignete Technik hängt von der Bilanzposition ab. Für Anlagevermögen und Darlehen kann ein BASE-Korkenzieher ideal sein.

BASE auf English steht für Begin, Add, Subtract, End. Auf Deutsch:

  • Anfangsbestand = Endbestand der Vorperiode
  • + Zugänge
  • - Abgänge
  • = Endbestand

Beim Anlagevermögen stehen die Zugänge für Investitionen und die Abgänge für Abschreibungen. Die Abgänge könnten auch Verkäufe von Vermögenswerten darstellen, aber in der Regel planen wir keine Veräußerung von Vermögenswerten, sondern ersetzen sie einfach (CapEx), wenn sie vollständig abgeschrieben sind.

Bei Krediten stellen die Zugänge Kreditinanspruchnahmen und die Abgänge Kreditrückzahlungen dar. Beachten Sie, dass sich die Zinsen nicht auf den Kreditsaldo auswirken (es sei denn, Sie zahlen die Zinsen nicht, sondern fügen sie dem Kreditsaldo hinzu, ein so genanntes PIK-Darlehen = payment in kind). Die Zinsen sind Kosten, die in die Gewinn- und Verlustrechnung einfließen.

Wie sieht also eine BASE-Berechnung in Excel aus und warum wird sie Korkenzieher genannt?

Der Screenshot zeigt ein Beispiel für das Anlagevermögen. Ich habe den Endbestand des ersten Jahres ausgewählt und die Schaltfläche "Spur zum Nachfolger" wiederholt verwendet (diese finden Sie in der Multifunktionsleiste "Formeln" unter "Formelüberwachung"). Der Zickzack-Korkenzieher-Effekt der Datenflüsse ist deutlich sichtbar.

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?"