Office
Excel-Formeln: Bezüge erstellen, Array-Formeln und Namen
- Excel-Formeln meistern: Tipps für Berechnungen, Pläne und Co.
- Excel-Formeln: Zusammengesetzte Funktionen für eine Zelle
- 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 Bezüge kennengelernt, da sich Formeln auf Zellen beziehen. 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 Zelle 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 haben 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 werden 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 Spalte nach rechts, werden alle Mitgliedszahlen angezeigt.
Bei komplexen Bezügen empfiehlt es sich Array-Formeln anzuwenden
Mit Array-Formeln haben Sie die Möglichkeit, komplexe Berechnungen auszuführen. Am Beispiel der Häufigkeitsberechnung 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 werden. Da eine Matrixformel eingesetzt werden soll, markieren 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 Matrixformel ausgeführt wird, betätigen Sie jetzt die Tastenkombination [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.
