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 darüber eine Überschriftenzeile, ähnlich wie im Folgenden dargestellt: (Siehe Abbildung 1.)

image

Abbildung 1. Beispieldaten für ein Arbeitsblatt.

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. Zeigen Sie das Dialogfeld Excel-Optionen an. (Klicken Sie in Excel 2007 auf die Schaltfläche Office und dann auf Excel-Optionen. Zeigen Sie in Excel 2010 und Excel 2013 die Registerkarte Datei des Menübands an und klicken Sie dann auf Optionen.)

  2. Klicken Sie links im Dialogfeld auf den Link Formeln. (Siehe Abbildung 2.)

  3. Stellen Sie sicher, dass das Kontrollkästchen Tabellennamen in Formeln verwenden aktiviert ist.

  4. OK klicken.

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

=Sales '2013' – Sales '2008'

Was Sie tatsächlich tun, ist Excel anzuweisen, mit Zellenverbänden zu arbeiten. In diesem Fall gibt Sales ‚2013‘ die Zelle am Schnittpunkt des Sales-Bereichs und der Spalte ‚2013‘ 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 (9177) gilt für Microsoft Excel 2007, 2010 und 2013. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Relative Referenzen innerhalb benannter Bereiche.