Strukturierte Verweise für Excel Tabellen

Forumthread: Strukturierte Verweise für Excel Tabellen

Reinhard

Hallo Wissende,
In dieser Mappe: https://www.herber.de/bbs/user/75500.xlsm
wird z.B. in G12 des Hauptblattes diese Formel benutzt:
=[Geplante Kosten]-[Ist-Kosten]
Sowas kannte ich nicht, jmandd. verwies auf diesen Link zu Microsoft:
http://office.microsoft.com/de-at/excel-help/verwenden-von-strukturierten-verweisen-fur-excel-tabellen-HA010155686.aspx
Scheinbar habe ich das dort nicht verstanden denn mein Versuch in Blatt "Tabelle1" das von G12 nachzubauen scheiterte wie man sieht:-(
Was müßte ich da in Tabelle1 abändern?
Ich habe eigene Bereichsnamen angelegt, Test, Bereich, _TitelA,_TitelB, die sehe ich auch wie gewohnt in diesem Code:

Sub tt()
Dim N
For Each N In ThisWorkbook.Names
MsgBox N.Name & " " & N.RefersToLocal
Next N
'MsgBox ThisWorkbook.Names("Ersparnisse") '.RefersToLocal
End Sub

Aber die anderen ca. 10 Namen die dort im Namensmanager stehen kann ich nicht ansprechen.
Sind das keine Namen?
Wie spreche ich die an in Vba?
Gruß ^ Danke
Reinhard

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

Strukturierte Verweise für Excel Tabellen - Listen

fcs
Superklasse :-))

Reinhard

Strukturierte Verweise für Excel Tabellen - Listen

01.07.2011 07:35:54

fcs

Hallo Reinhard,
bei dieser Art von Datenbereichen handelt es sich um Tabellen, die unter Excel 2007 und via Einfügen--Tabelle anglegt werden, nachdem man den entsprechende Tabellenbereich (möglichst schon mit Spaltentiteln) markiert hat. In älteren Excelversionen geht's via Menü Daten--Liste. Leider gibt es im Deutschen die Bezeichnung "Tabelle" sowohl für das Tabellenblatt als auch für diese speziellen Tabellenbereiche, was etwas verwirrend ist. Deshalb verwendet Microsoft statt "Tabellenblatt" meist "Arbeitsblatt". Im Englischen gibt es "Sheet" für das Tabellenblatt und "List" für Tabelle.
Nur die Namen dieser Listobjekte werden angezeigt, aber nicht die aus den Spaltentiteln der Liste ableitbaren Zellbereiche. In den Formeln, die Tabellenbereiche auswerten, können dann die Spaltennamen und die Namen von bestimmten Bereichen -in eckige Klammern gesetzt- verwendet werden. Teilweise muss auch der Name des Tabellenobjekts in den Formeln angegeben werden. Am einfachsten ist es, hinter dem Namen des Tabellenobjekts die öffende eckige Klammer [ zu schreiben, dann wird eine Liste mit den Namen der verfügbaren Zellbereiche angezeigt, und nach der Auswahl des Namens die schliessende eckige Klammer ]. In Verbindung mit vielen Funktionen muss immer erst der Name des Tabellenobjekts ausgewählt werden, dann mit [ der Bereich.
Unter Vba ist so eine Tabelle ein Listobject und man kann diese Listobjekte mit ihrem Namen ansprechen, oder mit der Index-Nr.
Listenobjekte sind Unterobjekte von Tabellenblättern. Einzelne Bereiche (Titelzeile, Ergebniszeile, Datenbereich) können als Unterobjekte des Listobjektes angesprochen werden. Zellen, Zeilen und Spalten dieser Bereiche müssen mit ihrer Indexnummer angegeben werden ähnlich wie in Tabellenblättern mit Cells(Zeile, Spalte), Rows(Zeile), Columns(Spalte).
Ich hab in deiner Tabelle ein paar Beispielformeln eingebaut und das Makro so angepasst, dass Informationen und Auswertungen zum Listobject angezeigt werden.
https://www.herber.de/bbs/user/75534.xlsm
Gruß
Franz

Superklasse :-))

01.07.2011 08:44:30

Reinhard

Hallo Franz,
bedanke mich sehr bei dir für die Ausführlichkeit.
Anstelle von
=B2+C2
zu schreiben:
=[TitelA]+[TitelB]
hat schon sichtbare Vorteile die Formel schnell zu verstehen bei Blick auf die Spaltentitel.
Die gleichwertige Formel:
=MeineLieblingsTabelle[[#Diese Zeile];[TitelA]]+MeineLieblingsTabelle[[#Diese Zeile];[TitelB]]
ist für mich erst noch gewöhnungsbedürftig.
Lieben Gruß
Reinhard

Infobox / Tutorial

Strukturierte Verweise für Excel Tabellen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Tabelle erstellen: Öffne Excel und markiere den gewünschten Datenbereich. Gehe zu Einfügen > Tabelle, um eine strukturierte Tabelle zu erstellen. Achte darauf, dass deine Spalten Titel haben.
Strukturierte Verweise verwenden: In einer Zelle kannst du auf andere Zellen der Tabelle mit dem Format =[Spaltenname] zugreifen. Zum Beispiel:
```
=[TitelA]+[TitelB]
```
Formel anpassen: Wenn du eine Formel mit einem strukturierten Verweis auf eine Zelle anpassen möchtest, nutze folgendes Format:
```
=MeineLieblingsTabelle[[#Diese Zeile];[TitelA]] + MeineLieblingsTabelle[[#Diese Zeile];[TitelB]]
```
Referenzieren von Tabellen: Um eine Excel-Tabelle zu referenzieren, kannst du den Tabellennamen direkt verwenden. Beispiel:
```
=SUMME(MeineLieblingsTabelle[TitelA])
```
Verweise dynamisch gestalten: Du kannst die strukturierte Verweise dynamisch machen, indem du Funktionen wie INDIREKT verwendest, aber achte darauf, dass dies die Lesbarkeit beeinträchtigen kann.

Häufige Fehler und Lösungen

Fehlerhafte Bezugnahme: Wenn ein strukturierter Verweis nicht funktioniert, überprüfe die Spaltentitel auf Tippfehler oder Leerzeichen. Excel ist hier sehr empfindlich.
Deaktivierung strukturierter Verweise: Wenn du strukturierte Verweise deaktivieren möchtest, gehe zu Datei > Optionen > Formeln und deaktiviere die Option für strukturierte Verweise.
Namen in VBA ansprechen: Wenn du mit VBA arbeitest, stelle sicher, dass du die richtigen Objekte verwendest. Du kannst ListObjects ansprechen:
```
ThisWorkbook.Sheets("Tabelle1").ListObjects("MeineLieblingsTabelle").DataBodyRange
```

Alternative Methoden

Verwendung von Bereichsnamen: Anstelle strukturierter Verweise kannst du auch benannte Bereiche erstellen. Dies kann die Formeln übersichtlicher machen:
```
=Ersparnisse + Kosten
```
Einfache Zellreferenzen: Wenn du nicht mit Tabellen arbeiten möchtest, kannst du auch einfache Zellreferenzen nutzen:
```
=B2+C2
```

Praktische Beispiele

Beispiel 1: Angenommen, du hast eine Tabelle mit den Spalten TitelA und TitelB, um die Gesamtkosten zu berechnen:
```
=SUMME([TitelA]) + SUMME([TitelB])
```
Beispiel 2: Um den Durchschnitt der Werte in TitelA zu berechnen:
```
=MITTELWERT([TitelA])
```
Beispiel 3: Eine dynamische Berechnung, die sich an die Größe der Tabelle anpasst:
```
=SUMME(MeineLieblingsTabelle[[#Alle];[TitelA]])
```

Tipps für Profis

Verweise fixieren: Um sicherzustellen, dass deine strukturierten Verweise auch beim Kopieren oder Verschieben von Zellen bestehen bleiben, solltest du sie fixieren:
```
=$A$1 + [TitelA]
```
Verwendung von Filter: Nutze die Filteroptionen in Excel, um die Daten in deinen Tabellen besser zu analysieren und gezielt zu referenzieren.
VBA und strukturierte Verweise: Wenn du viel mit VBA arbeitest, mache dir Notizen über die Namen der ListObjects. Das erleichtert die Programmierung erheblich.

FAQ: Häufige Fragen

1. Was sind strukturierte Verweise in Excel?
Strukturierte Verweise sind eine spezielle Art von Formeln, die es ermöglichen, auf Tabellenbereiche anhand ihrer Spaltennamen zuzugreifen.

2. Wie kann ich strukturierte Verweise deaktivieren?
Gehe zu Datei > Optionen > Formeln und deaktiviere die Option für strukturierte Verweise.

3. Kann ich strukturierte Verweise in älteren Excel-Versionen verwenden?
Nein, strukturierte Verweise sind erst ab Excel 2007 verfügbar. In älteren Versionen musst du auf Listen zurückgreifen.

4. Wie kann ich auf einen bestimmten Zellbereich in einer Tabelle zugreifen?
Du kannst auf einen bestimmten Zellbereich zugreifen, indem du den Tabellennamen und die Spalte in eckigen Klammern angibst, z.B. =[MeineTabelle[TitelA]].

Beliebteste Forumthreads (12 Monate)

Strukturierte Verweise für Excel Tabellen

Forumthread: Strukturierte Verweise für Excel Tabellen

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Strukturierte Verweise für Excel Tabellen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread