Zusammenfassung
Um die Prognose gegenüber der tatsächlichen Varianz basierend auf einem Datensatz zu berechnen, können Sie die SUMIFS-Funktion verwenden, um Summen zu erfassen, und andere grundlegende Formeln, um die Varianz und den Varianzprozentsatz zu berechnen. Im gezeigten Beispiel lautet die Formel in G5:
=SUMIFS(amount,type,G$4,group,$F5)
Dabei ist Betrag der benannte Bereich C5: C14 und Typ der benannte Bereich D5: D14 und Gruppe der benannte Bereich B5: B14.
Erläuterung
Dies ist eine ziemlich übliche Verwendung der SUMIFS-Funktion. In diesem Fall müssen wir die Beträge anhand von zwei Kriterien summieren: Typ (Prognose oder Ist) und Gruppe. Um nach Typ zu summieren, lautet das Bereich / Kriterien-Paar:
type,G$4
Dabei ist Typ der benannte Bereich D5: D14 und G4 eine gemischte Referenz mit gesperrter Zeile, um mit der Spaltenüberschrift in Zeile 4 übereinzustimmen, wenn die Formel kopiert wird.
Um nach Gruppen zu summieren, lautet das Bereich / Kriterien-Paar:
group,$F5
Dabei ist Gruppe der benannte Bereich B5: B14 und F5 eine gemischte Referenz mit gesperrter Spalte, um den Gruppennamen in Spalte F zu entsprechen, wenn die Formel kopiert wird.
Varianzformeln
Die Varianzformel in Spalte I subtrahiert einfach die Prognose von der tatsächlichen:
=G5-H5
Die Varianzprozentsatzformel in Spalte J lautet:
=(G5-H5)/H5
mit angewendetem Prozentzahlformat.
Anmerkungen
- Die hier gezeigten Daten funktionieren gut in einer Excel-Tabelle, die automatisch um neue Daten erweitert wird. Wir verwenden hier benannte Bereiche, um die Formeln so einfach wie möglich zu halten.
- Pivot-Tabellen können auch zur Berechnung der Varianz verwendet werden. Formeln bieten mehr Flexibilität und Kontrolle auf Kosten einer höheren Komplexität.
