Eine variable Datentabelle in Excel

Was ist eine Datentabelle mit einer Variablen in Excel?

Eine Variable Datentabelle in Excel bedeutet, dass die eine Variable zusammen mit mehreren Optionen geändert wird und die Ergebnisse für mehrere Szenarien abgerufen werden.

Wie erstelle ich eine Datentabelle mit einer Variablen in Excel? (mit Beispielen)

Sie können diese Excel-Vorlage für eine Datentabelle mit einer Variablen hier herunterladen - Excel-Vorlage für eine Datentabelle mit einer Variablen

Beispiel 1

Sie nehmen einen Kredit von Rs. 2, 50.000 für 2 Jahre. Sie diskutieren mit dem Kreditsachbearbeiter über den Zinssatz.

Sie müssen zu unterschiedlichen Zinssätzen analysieren, wie hoch die monatliche Zahlung ist, die Sie zur Tilgung des Kredits zahlen müssen. Nehmen Sie für die Berechnung den Basiszinssatz von 12% pro Jahr.

  • Schritt 1: Berechnen Sie die monatliche EMI mit der PMT-Funktion.

  • Schritt 2: Erstellen Sie nun eine Szenariotabelle wie in der folgenden Abbildung gezeigt.

  • Schritt 3: Wählen Sie die Zelle E9 aus und geben Sie einen Link zur Zelle B6 (EMI-Betrag). Jetzt zeigt die Zelle E9 das monatliche EMI.

  • Schritt 4: Wählen Sie den Bereich von D9 bis E22.

  • Schritt 5: Klicken Sie auf die Registerkarte Daten und dann auf  Was-wäre-wenn-Analyse und Datentabelle

  • Schritt 6: Das Dialogfeld Datentabelle wird angezeigt. Wählen Sie in der Spalte Eingabezelle die Zelle B5 aus (die den Basiszinssatz für Darlehen enthält).

Bei einer variablen Datentabelle in Excel ignorieren wir immer entweder die ROW-Eingabezelle oder die Spalteneingabezelle. Das hängt von unserer Tabellenstruktur ab. Wenn in unserer Szenariotabelle unterschiedliche Zinssätze vertikal sind, ignorieren wir die Zeileneingabezelle, und wenn unsere Zinssätze in der Szenariotabelle horizontal sind, ignorieren wir die Spalteneingabezelle. In diesem Beispiel habe ich die Zeileneingabezelle ignoriert, da in der Szenariotabelle unterschiedliche Zinssätze vertikal sind.

  • Schritt 7: Klicken Sie nun auf die Schaltfläche OK, um verschiedene Szenarien zu erstellen.

Jetzt zeigt der Bereich E10: E22 einige neue Werte. Aus der Tabelle geht sehr klar hervor, dass das monatliche EMI bei einem Zinssatz von 12,5% 11.827 INR und das monatliche EMI bei 13,5% 11.944 INR usw. betragen wird.

So funktioniert eine variable Datentabelle in Excel. Sie können dies auch in einem Diagramm anzeigen.

Beispiel 2

Angenommen, Sie sind Verkaufsleiter eines Unternehmens. Vom Management haben Sie von Ihrem Team ein monatliches Umsatzziel von 1, 70, 00 USD erhalten. Die folgende Tabelle zeigt das Verkaufsziel für 6 Mitglieder. Sie müssen analysieren, wie hoch ihre Effizienz sein sollte, um das Ziel von 1,7 lakh USD in einem Monat zu erreichen.

Das Gesamtziel Ihrer Teams liegt bei 2,04 Lakhs. Sie sind sich nicht sicher, wie viel Prozent der Effizienz sie auf den Tisch bringen müssen, um das vom Management vorgegebene Ziel zu erreichen.

Ihr Team kann einen Wirkungsgrad von maximal 90% angeben, und Sie haben den Gesamtumsatz mit einem Wirkungsgrad von 90% berechnet.

Bei einem Wirkungsgrad von 90% kann Ihr Team in einem Monat einen Gesamtumsatz von 1,83 lakh USD erzielen. Sie müssen das Effizienzniveau erreichen, um das vom Management vorgegebene Umsatzziel zu erreichen.

Erstellen Sie eine Szenariotabelle wie in der folgenden Abbildung gezeigt.

  • Schritt 1: Unterhalb der Tabelle müssen Sie eine Excel-Datei erstellen.

Diese Tabelle zeigt auf verschiedenen Effizienzstufen, wie hoch der Umsatz sein wird.

  • Schritt 2: Wählen Sie die Zelle H3 aus und geben Sie einen Link zur Zelle B11 an (bei einer Umsatzzelle mit einem Wirkungsgrad von 90%). Jetzt zeigt die Zelle H3 den Umsatz mit einem Wirkungsgrad von 90%.

  • Schritt 3: Wählen Sie den Bereich von G3 bis H12

  • Schritt 4: Suchen Sie nun die Datentabelle im Abschnitt Was wäre wenn Analyse.

  • Schritt 5: Wenn Sie auf Datentabelle klicken, müssen Sie einen Link in der Spalteneingabezelle angeben und die Zelle B10 auswählen (die den prozentualen Wirkungsgrad enthält).

Für die Spalteneingabezelle habe ich den Link zur Zelle B10 angegeben, da wir basierend auf den verschiedenen Effizienzstufen die Szenarien erstellen werden. Jetzt geht die Datentabelle davon aus, dass der Umsatz bei 90% 1,83 lakh USD betragen wird. In ähnlicher Weise werden Szenarien für 100%, 95%, 90%, 85%, 80% usw. erstellt.

  • Schritt 6: Klicken Sie auf OK, um verschiedene Szenarien zu erstellen.

Nun zeigt der Bereich G3: H12 Szenarien. Das Management hat das Ziel von 1,70 lakh USD für diesen Monat festgelegt. Um so viel Umsatz zu erzielen, muss Ihr Team eine Effizienz von mindestens 85% erreichen.

Auf diese Weise können Sie mithilfe der Datentabelle verschiedene Analysen erstellen und ein geeignetes Szenario auswählen, um die Ziele zu erreichen.

Dinge, an die man sich erinnern sollte

  • Sobald die Szenarien erstellt wurden, können wir die Aktion nicht mehr rückgängig machen. Es wird gleich bleiben.
  • Wir können die Zellenwerte nicht einmal ändern, da sie zu einer Excel-Array-Formel werden und Sie alles löschen müssen, nicht einzeln.
  • Lassen Sie in einer variablen Datentabelle die Zeileneingabezelle immer weg, wenn das Szenario in vertikaler Form angezeigt werden soll. Wenn das Szenario in horizontaler Form angezeigt werden soll, verlassen Sie die Spalte Eingabezelle.
  • Sie können den Wert in der Hauptdatenbank ändern, um die Echtzeitergebnisse bei verschiedenen Arten von Alternativen anzuzeigen.