Die Power-Kombination
Kombinieren Sie die Formeln SUMIF und OFFSET, eine Validierungsliste und ein Kombinationsfeld, um eine Zusammenfassung der Daten zurückzugeben, die nach Monat ausgewählt werden sollen – wirklich eine Leistungskombination!
Schritt 1: Auswählen von Elementen aus Validierungslisten
Auswählen eines Elements aus einer Validierungsliste (Spalte A im Screenshot)
Aktiviert Formeln, die in Zellen in den Spalten C und D eingegeben werden, um den Text zu identifizieren und die Zusammenfassungsergebnisse aus einer Spalte zurückzugeben, die die Kriterien (das ausgewählte Element) für den im Kombinationsfeld ausgewählten Monat enthält.
So fügen Sie einem Zellbereich eine Validierungsliste hinzu:
-
Wählen Sie den Zellenbereich aus (im Screenshot auf der vorherigen Seite sind die ausgewählten Zellen A12: A15)
-
Wählen Sie die Zellen A19: A23 aus.
-
Wählen Sie im Menü Daten die Option Validierung.
-
Wählen Sie im Dialogfeld Datenüberprüfung die Registerkarte Einstellungen und im Feld Zulassen die Option Liste aus.
-
Drücken Sie im Feld Quelle die Taste F3, wählen Sie den für die Liste definierten Namen aus (Stufe 3 in diesem Beispiel, siehe Screenshot auf der letzten Seite für diesen Tipp) und klicken Sie auf OK.
Schritt 2: Eingeben von Formeln, die zusammenfassende Salden für ausgewählte Elemente zurückgeben Die Formel in den Zellen C12: C15 lautet:
SUMIF (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2))
Die Formel in den Zellen D12: D15 lautet:
SUMIF (DataLevel3, A12, OFFSET (DataLevel3,0, MonthSelectionNumber + 2-12))
Erläuterung:
Die SUMIF-Formel in Spalte C fasst die Restbeträge aus der Spalte Dezember 2003 zusammen. Die SUMIF-Formel in Spalte D fasst die Restbeträge aus der Spalte Dezember 2002 zusammen.
Die SUMIF-Formel enthält drei Argumente:
Erstes Argument:
Der zu bewertende Bereich gemäß den im zweiten Argument der SUMIF-Formel eingegebenen Kriterien. In diesem Beispiel lautet der Bereich DataLevel3. Dies ist der Name, der für Spalte C in der Bilanzdatenbank definiert ist. Es enthält Level3-Posten der Bilanz, wie z. B. Bargeld, Debitorenbuchhaltung, Vorräte usw.
Zweites Argument:
Das Kriterium ist das Element, das aus der Validierungsliste Level3 ausgewählt wurde.
Drittes Argument:
Die Spalte, aus der die Daten zusammengefasst werden. Dies wird gemäß der OFFSET-Formel für die Monatsspalte ausgewählt, die durch die in der Monatsliste im Kombinationsfeld ausgewählte Nummer angepasst wird. Mit der OFFSET-Formel kann der ausgewählte Monat von der Basisspalte umgeleitet werden (Spalte C im folgenden Screenshot).
Funktionsweise der OFFSET-Formel
Spalte 29 ist die Spaltennummer für Dezember 2003 und die Spaltennummer für Dezember 2002 ist 17, also 12 Spalten zuvor (siehe Abbildung unten).
So ändern Sie die Überschriftentitel in einem Blatt von Zeichen in Zahlen: {leer} 1. Wählen Sie im Menü Extras die Option Optionen.
-
Überprüfen Sie auf der Registerkarte Allgemein den Referenzstil R1C1.
Wenn Dezember 2003 aus der Dropdown-Liste Kombinationsfeld (Monatsliste) ausgewählt wird, beträgt die Monatsnummer in dieser Liste 24 (dies wird berechnet, indem die Anzahl der Monate zwischen Januar 2002 und Dezember 2003 bestimmt wird:
2 Jahre * 12 Monate = 24). Die mit dem Kombinationsfeld verknüpfte Zelle erhält den Wert 24.
Im Datenblatt ist Spalte 3 die Basisspalte, die die SUMIF-Formel für die Kriterien im zweiten Argument der SUMIF-Formel auswertet.
In diesem Fall ist der Dezember 2003 um 24+ 2 = 26 (2 = Spalte D & Spalte E) von Spalte C entfernt.
Im dritten Argument sollte der Sum_range 26 Spalten von der Basisspalte entfernt sein. Die OFFSET-Formel gibt das Ergebnis von 26 zurück und veranlasst die SUMIF-Formel, die Zahlen aus der Spalte Dezember 2003 zusammenzufassen.