Excel-Formeln: Bezüge erstellen, Array-Formeln und Namen

Bezüge erstellen, um automatisch Werte in anderen Zellen zu generieren

Bezüge sind Verweise auf Zellen und Bereiche in Excel. Sie haben im Rahmen der Formelerstellung schon Be­züge kennengelernt, da sich Formeln auf Zellen bezie­hen. Aber auch ohne Formeln können Sie mit Bezügen arbeiten. Tippen Sie zum Beispiel in die Zelle A1 einer leeren Tabelle die Zahl 1200 ein.

Tippen Sie in die Zelle F1 ein Gleichheitszeichen ein, klicken Sie dann auf die Zel­le A1, und betätigen Sie die [Enter]-Taste. Die Zelle F1 übernimmt jetzt auch bei Aktualisierungen den Wert aus der Zelle A1. So können Sie komplette Bereiche in Beziehung setzen – auch tabellenübergreifend. Sie ha­ben sogar die Möglichkeit, sich mit den Beziehungen auf andere Arbeitsmappen zu beziehen.

Erstellen Sie für ein Beispiel von übergreifenden Bezü­gen einmal folgenden Tabellenbereich:

Sparte Mitglieder

• Tennis 22
• Handball 14
• Yoga 21

Sie benötigen diese Angaben in einer weiteren Tabelle, um die Beitragssätze zu ermitteln. Tippen Sie in dieser Tabelle auf die Zelle, ab der die Daten dargestellt wer­den sollen. Geben Sie ein Gleichheitszeichen ein und klicken in die Tabelle, aus der Sie die Daten beziehen wollen. Hier klicken Sie noch in die gewünschte Zelle und bestätigen mit der [Enter]-Taste. In diesem Beispiel wird der Bezug=Bezugstabelle A1 erstellt.

Ziehen Sie den Bezugswert per Kopieranfasser in der unteren rechte Ecke der Zelle drei Zeilen nach unten. Wie von Zauberhand erscheinen jetzt die Werte aus der Bezugstabelle. Ziehen Sie den Kopieranfasser eine Spal­te nach rechts, werden alle Mitgliedszahlen angezeigt.

Bei komplexen Bezügen empfiehlt es sich Array-Formeln anzuwenden

Mit Array-Formeln haben Sie die Möglichkeit, komple­xe Berechnungen auszuführen. Am Beispiel der Häu­figkeitsberechnung wollen wir das ausprobieren. Sie erstellen zum Test die folgende Umsatztabelle:

Monat Umsatz

• Januar 500,00 €
• Februar 2.900,00 €
• März 300,00 €
• April 1.200,00 €
• Mai 1.100,00 €
• Juni 3.000,00 €
• Juli 500,00 €
• August 100,00 €
• September 2.100,00 €
• Oktober 2.900,00 €
• November 3.100,00 €
• Dezember 3.900,00 €

Sie interessiert, wie häufig ein Umsatz bis 1000 Euro, bis 2000 Euro, bis 3000 Euro und wie häufig ein Umsatz grö­ßer als 3000 Euro erreicht wurde. Dazu erstellen Sie ab der Spalte D die Klassenaufstellung:

Klasse

• 1.000,00 €
• 2.000,00 €
• 3.000,00 €
• 4.000,00 €

In der Spalte E soll jetzt die Häufigkeit berechnet wer­den. Da eine Matrixformel eingesetzt werden soll, mar­kieren Sie zunächst den Bereich, in dem das Ergebnis stehen soll. In diesem Beispiel also der Bereich E2 bis E5. Tippen Sie jetzt die Funktion für die Häufigkeit ein:

=HÄUFIGKEIT(B2:B13;D2:D4). Damit die Formel als Mat­rixformel ausgeführt wird, betätigen Sie jetzt die Tas­tenkombination [Strg]+[UMSCHALT]+[Enter]. Dadurch wird eine geschweifte Klammer um die Formel gelegt und die Häufigkeiten im markierten Bereich werden berechnet.

WICHTIG: Durch die manuelle Eingabe von geschweiften Klammern vor und hinter einer Formel wird diese nicht in eine Matrixformel konvertiert.

Für absolute Verweise kann man mit Namen arbeiten

Damit Sie es noch etwas einfacher haben, können Sie in Excel Namen für Zellen und Bereiche vergeben. Sie brauchen dann keine Dollarzeichen für feste Bezüge mehr zu setzen, da Namen immer als absoluter Verweis erstellt werden. Sie möchten zum Beispiel in dieser Tabelle wissen, wie viel Geld noch übrig ist.

Einnahmen: Ausgaben: Ergebnis:

2.000,00 € 1.700,00 € Aktivieren Sie das Register Formeln. Klicken Sie auf den Wert mit den Einnahmen und dann in der Gruppe Definierte Namen auf den Befehl Namen definieren. Das Dialogfenster Neuer Name wird eingeblendet. Aus der ersten Zeile wird der Name übernommen.

Mit einem Klick auf die Schaltfläche OK wird der Name festgelegt. Legen Sie auf die gleiche Art und Weise den Namen für die Zelle mit den Ausgaben fest. In der Ergebniszelle des Beispiels rechnen Sie jetzt einfach mit den Namen: =Ausgaben-Einnahmen.