Excel für Unentschlossene: Was wäre wenn? 4

Was wäre, wenn ich diesen Excel-Blog im vergangenen September nicht gestartet hätte?
Was wäre, wenn ich nicht in zwischenzeitlich 50 Artikeln Excel-Tipps und Tricks hier vorgestellt hätte?

Gut, die Erde hätte sich mit hoher Wahrscheinlichkeit trotzdem genauso weitergedreht. Aber vielleicht hättest du dann nie von den Möglichkeiten der „Was-wäre-wenn-Analysen“ in Excel erfahren, was sehr schade wäre.

Unter diesem Begriff werden verschiedene Excel-Funktionalitäten zusammengefasst, die eine Berechnung von verschiedenen Optionen oder Szenarien ermöglichen. Der heutige Artikel beschäftigt sich mit der sogenannten Datentabelle, welche dir bei entsprechender Anwendung einiges an Arbeit ersparen kann.

Und so geht’s:

Die Beispieltabelle kannst du übrigens bei Bedarf hier herunterladen.

Was-wäre-wenn-Analyse

Unter dieser Kategorie werden in Excel drei verschiedene Funktionen zusammengefasst:

• Zielwertsuche
• Szenario-Manager
• Datentabelle

Letztgenannte Funktion werden wir uns heute näher ansehen, auf die anderen beiden gehe ich zu einem späteren Zeitpunkt noch ein.

Datentabelle

Im Internet findet man meistens Zins-, Renten- oder Kapitalberechnung als Beispiel für Datentabellen, was ich persönlich eher langweilig finde. Daher habe ich mir einen anderen Anwendungsfall ausgedacht:

Der Frühling hat schon lange Einzug gehalten und einer kleinen sportlichen Betätigung im Freien steht außer uns selbst nichts mehr im Weg. Wer also noch einen kleinen Motivationsschub braucht, kann sich mit Excel seinen Body-Mass-Index (BMI) berechnen.

Kurz gesagt setzt der BMI die Körpermasse (= Gewicht) in Bezug zur Körpergröße. Die Formel dazu lautet:
BMI = Gewicht / Körpergröße zum Quadrat

Dieses Wissen wollen wir nun verwenden, um in die Excel-Welt der Was-wäre-wenn-Analysen einzusteigen. In meiner Beispieltabelle gibt es zwei Eingabefelder: B3 für die Körpergröße und B4 für das Gewicht. Der BMI wird dann mit der oben genannten Formel in Zelle B6 ausgerechnet:

Berechnung des BMI

Schön wäre es nun, wenn wir aus einer Tabelle für alle möglichen Kombinationen aus Gewicht und Größe direkt den BMI ablesen könnten. Und genau dafür erstellen wir jetzt eine sogenannte Datentabelle.

Dazu habe ich in die Zeilen direkt unterhalb meiner BMI-Formel die verschiedene Gewichte aufgelistet. Und in die Spalten direkt rechts von der Formel kommen die Größenangaben, so wie hier gezeigt:

Die vorbereitete Tabelle

Wichtig ist hier nur, dass die Gewichte und die Größen direkt neben bzw. direkt unterhalb meiner Formel sind. Das heißt, die Berechnungsformel liegt genau im Schnittpunkt.

Nun markieren wir genau diesen Tabellenbereich einschließlich der BMI-Formel, der Gewicht und der Größen mit der Maus und rufen im Register „Daten“ die Schaltfläche „Was-wäre-wenn-Analyse“ auf. Dort wählen wir den Eintrag „Datentabelle…“ aus:

Datentabelle erstellen

Es erscheint ein ziemlich nichtssagendes Eingabefenster. Zur Erklärung: In das Feld „Werte aus Zeile“ geben wir die Zelle ein, in der die Größe für die Berechnung steht. Also $B$3.
Und in das Feld „Werte aus Spalte“ kommt der Zellbezug für das Gewicht, also $B$4:

Werte festlegen

Was bedeutet das?

Da wir alle Körpergrößen in eine Zeile geschrieben haben, werden diese Werte bei der anschließenden Berechnung als Variablen der Reihe nach in die Zelle B3 übernommen.
Und die Gewichtsangaben stehen alle untereinander in einer Spalte und werden durch diese Funktion ebenfalls nacheinander in die Zelle B4 eingesetzt.

Nach einem Klick auf OK ist die Datentabelle auch schon fertig und wir können den BMI direkt ablesen:

Die fertige Datentabelle

Wenn man sich oben die Bearbeitungszeile ansieht erkennt man, dass es sich um keine normalen Formeln handelt, sondern um sogenannte Mehrfachoperationen. Ohne diese praktische Datentabellen-Funktion wären in meinem Beispiel 441 einzelne Formeln (21 Zeilen * 21 Spalten) notwendig gewesen, um das gleiche Ergebnis zu erreichen.

Vielleicht fallen dir ja noch bessere Beispiele für die Anwendung der Datentabelle ein, dann lass uns in den Kommentaren unten wissen. Ich gehe jetzt erst mal zum Laufen.

Das könnte dich auch interessieren:

Vorher schon wissen, was hinterher rauskommt

Eingabemöglichkeiten einschränken

Dem Verbrecher auf der Spur

Das doppelte Lottchen, oder: Dublettensuche mit Excel