Excel – Namen in formatierten Tabellen

Tabellen, die mit “Start / Als Tabelle formatieren” eingerichtet wurden, sind nicht einfach nur bunt. Sie bieten viele Möglichkeiten wie automatisches Filtern, Ergebniszeilen etc. Ich habe hier im Blog und auf meinem Lexikon schon darüber geschrieben (Zusammenfassung im Lexikon).

Heute möchte ich Ihnen vorstellen, welche automatischen Namen in Tabellen vorhanden sind und was Sie damit tun können. Gegeben ist eine Minitabelle mit ein paar Namen und Beträgen. Sie wurde als Tabelle formatiert:

26.06.2013_ 0008

Die aktive Zelle steht in der Tabelle (z. B. A2). Mit “Tabellentools / Entwurf” gebe ich den Tabellenname “Angebote”.

Jede Berechnung kann jetzt mit sprechenden Namen erfolgen. Zuerst kommt der Name der Tabelle (er wird schon vorgeschlagen, wenn Sie die ersten Buchstaben tippen), dann eine eckige Klammer:

=SUMME(angebote[

26.06.2013_ 0009

Excel bietet jetzt alle Spaltennamen an. Bei mir ist das “Name” und “Betrag”. Mit
=SUMME(angebote[betrag])
werden die Beträge addiert.

=MAX(angebote[betrag]) oder =MAX(angebote) zeigt mir den Betrag des höchsten Angebots. Weil es nur eine Spalte mit Ziffern gibt, kann ich auf den Spaltenname auch verzichten.

=ANZAHL2(angebote[name]) zählt alle Einträge in der Spalte “Name”.

Der Vorteil ist, dass diese Namen sich dynamisch erweitern – wächst die Tabelle, weil noch mehr Namen und Beträge hinzukommen, wird auch der Gültigkeitsbereich der Tabelle erweitert.

Sie können auch die Spalten oder die Tabelle markieren – Excel liefert dann unter Umständen weitere Namenszusätze zurück. Unten sehen Sie den Zustand, wenn ich eine Formel mit =ANZAHL2( beginne und dann mit der Maus die Spalte B markiere:

26.06.2013_ 0010

Excel hat den Namen der Tabelle eingetragen und dann in die eckige Klammer nicht nur “Betrag” als Spaltenbeschriftung übernommen, sondern auch noch den Zusatz “#Alle”. Das ist unerwünscht – denn “#Alle” zählt auch die Spaltenüberschrift mit.

Statt “#Alle” können Sie “#Daten” schreiben – oder Sie löschen diesen Bereich ganz raus.
Immer in Kombination mit “Tabellenname[[#Zusatz];[Spaltenname]]” gibt es diese Zusätze:

  • #Alle
    Alle Einträge der Spalte inklusive Spaltenüberschrift und Ergebniszelle
  • #Daten
    Nur die Daten – ohne Spaltenüberschrift und ohne Ergebniszelle
  • #Kopfzeilen
    Nur den Eintrag der Kopfzeile – das wäre das Wort “Betrag” bei =Angebote[[#Kopfzeile];[Betrag]]
  • #Ergebnisse
    Die Ergebniszelle dieser Spalte (sofern eine Ergebniszeile aktiviert wurde)
  • @ (diese Zeile)
    Den Inhalt der Zelle aus der gleichen Zeile – wenn ich in D2 schreibe =Angebote[@Betrag] erhalte ich 2325. In der gleichen Zeile wie D2 steht in der Spalte “Betrag” die Zahl 2325.

Ein Beispiel:
Mit =MAX(Angebote) erhalte ich die größte Zahl.

26.06.2013_ 0011

Zusätzlich möchte in E2 den Namen des Bieters bekommen. Dafür kombiniere ich mehrere Funktionen:
=BEREICH.VERSCHIEBEN(A1;VERGLEICH(MAX(Angebote);Angebote[Betrag]);0)
Ich drösel die Funktion mal auf:

26.06.2013_ 0012

In D2 wird das Maximum errechnet =MAX(Angebote)
In E2 wird ermittelt, in welcher Zeile das Maximum steht =VERGLEICH(D2;Angebote[Betrag])
In F2 wird mit BEREICH.VERSCHIEBEN ausgehend von Zelle A1 der “Zeiger” um 6 Zeilen nach unten und 0 Spalten nach rechts verschoben. Damit lande ich auf dem Namen “Baumann”.

Schritt für Schritt können Sie jetzt zuerst in die VERGLEICH-Funktion statt D2 die Formel MAX einbauen, anschließend diese Funktion anstelle von E2 in die BEREICH.VERSCHIEBEN.
Und der Mindestbieter? Der errechnet sich mit MIN und der Rest ist weitgehend identisch. Achten Sie aber beim Vergleich darauf, dass Sie das Argument “0” hinzufügen müssen. Lesen Sie mehr im Lexikon: Mindestbieter ermitteln

Hinterlasse jetzt einen Kommentar

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*