Chris hat ein Arbeitsblatt erstellt, in dem er benannte Bereiche (Zeilen) in seinen Formeln verwendet. Er hat die gesamte Verkaufszeile als „Verkauf“ bezeichnet und verwendet dann den Verkaufsnamen in verschiedenen Formeln. Beispielsweise kann er in einer bestimmten Spalte = Sales sagen, und der Wert der Sales-Zeile für diese Spalte wird von der Formel zurückgegeben. Chris fragte sich, wie man dieselbe Formeltechnik verwendet, um auf Zellen in verschiedenen Spalten zu verweisen.

Es gibt verschiedene Möglichkeiten, dies zu tun. Zunächst können Sie die INDEX-Funktion verwenden, um auf die Zellen zu verweisen. Die strenge Methode, auf den Wert des Umsatzes in derselben Spalte zu verweisen, lautet wie folgt:

=INDEX(Sales,1,COLUMN())

Dies funktioniert, wenn sich der benannte Verkaufsbereich tatsächlich auf die gesamte Zeile im Arbeitsblatt bezieht. Wenn dies nicht der Fall ist (z. B. kann sich Sales auf die Zellen C10: K10 beziehen), bezieht sich die folgende Formel auf den Wert von Sales in derselben Spalte, in der die Formel vorkommt:

=INDEX(Sales,1,COLUMN()-COLUMN(Sales)+1)

Wenn Sie auf eine andere Spalte verweisen möchten, passen Sie einfach den Wert an, der der Spaltenbezeichnung in der INDEX-Funktion hinzugefügt wird. Wenn Sie beispielsweise die Differenz zwischen den Verkäufen für die aktuelle Spalte und den Verkäufen in der vorherigen Spalte ermitteln möchten, verwenden Sie Folgendes:

=INDEX(Sales,1,COLUMN()-COLUMN(Sales)+1) - INDEX(Sales,1,COLUMN()-COLUMN(Sales))

Die „Kurzform“ für diese Formel lautet wie folgt:

=Sales - INDEX(Sales,1,COLUMN()-COLUMN(Sales))

Neben INDEX können Sie noch andere Funktionen verwenden (z. B. OFFSET), aber die Technik ist immer noch dieselbe. Sie müssen einen Weg finden, um auf einen Offset aus der aktuellen Spalte zu verweisen.

Es gibt jedoch eine einfachere Möglichkeit, an die gewünschten Daten zu gelangen. Angenommen, Ihr Verkaufsbereich enthält auch eine Überschriftenzeile, ähnlich der folgenden: (Siehe Abbildung 1.)

image

Abbildung 1. Beispiel für Verkaufsdaten.

In der Überschriftenzeile werden die Jahre für den Bereich aufgeführt, und die Werte unter den Überschriften sind diejenigen, aus denen der Verkaufsbereich tatsächlich besteht. Führen Sie die folgenden Schritte aus, um sicherzustellen, dass diese Technik funktioniert:

  1. Wählen Sie Optionen aus dem Menü Extras. Excel zeigt das Dialogfeld Optionen an.

  2. Stellen Sie sicher, dass die Registerkarte Berechnung angezeigt wird. (Siehe Abbildung 2.)

  3. Stellen Sie sicher, dass das Kontrollkästchen Beschriftungen in Formeln akzeptieren aktiviert ist.

  4. OK klicken.

Nach dieser Konfigurationsänderung können Sie Folgendes als Formeln verwenden:

=Sales '2009' – Sales '2004'

Was Sie tatsächlich tun, ist Excel anzuweisen, mit Zellenverbänden zu arbeiten. In diesem Fall gibt Sales ‚2009‘ die Zelle am Schnittpunkt des Sales-Bereichs und der Spalte ‚2009‘ zurück. Eine ähnliche Vereinigung wird für den Teil der Formel rechts vom Minuszeichen zurückgegeben. Das Ergebnis ist die Subtraktion der beiden gewünschten Werte.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (3202) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier:

Relative Referenzen innerhalb benannter Bereiche.