Sommerakademie – Excel

Zuerst beginnen Sie auf “Tabelle1” und tragen die Überschrift ein: Datum, Bezeichnung, Gebühr. Ich nenne das Blatt “Porto” – das ist aber nicht so wichtig.

1. In Spalte B dürfen nur feste Einträge aus einer Liste gewählt werden. Die Liste muss erweiterbar sein, weil möglicherweise noch weitere Bezeichnungen hinzukommen.

Der Schlüssel zum Erfolg ist für alles die “Formatierte Tabelle”.

Dafür legen Sie zuerst auf dem Blatt “Tabelle2” eine Tabelle an. Ich benenne das Blatt um in “System” – auch das ist mehr Kosmetik und nicht zwingend notwendig. In die erste Spalte kommen die Bezeichnungen, in die zweite die dazugehörigen Gebühren.

24.08.2015_ 0002

Jetzt kommen ein paar Vorarbeiten, damit es später alles klappt.  Halten Sie unbedingt die Reihenfolge genauso ein, sonst funktioniert es später nicht.

Markieren Sie zuerst A2 bis A10 und vergeben Sie den Namen “Basis_Bezeichnung”:

  • Klicken Sie auf “Formeln | Namen definieren”
  • Vergeben Sie den Namen “Basis_Bezeichnung” (ohne Anführungszeichen)
  • Vergewissern Sie sich, dass als Bereich “Arbeitsmappe” gewählt ist
  • Bestätigen Sie mit OK

24.08.2015_ 0003

Heben Sie die Markierung wieder auf, indem Sie irgendwo in die kleine Tabelle klicken und wählen Sie dann “Start | Als Tabelle formatieren”. Suchen Sie sich eine passende Farbe aus, bestätigen Sie mit OK. Klicken Sie anschließend in “Tabellentools | Entwurf” und tragen Sie bei “Tabellenname” einen Namen ein – meine heißt “Basis”.

24.08.2015_ 0004

Sortieren Sie die Tabelle unbedingt nach Spalte A! Jetzt muss Ihre Tabelle so aussehen (abgesehen von der Farbe, die bei Ihnen sicherlich anders ist):

24.08.2015_ 0005

Kehren Sie zurück auf das Tabellenblatt “Porto” – die erste Tabelle mit den Überschriften.

Klicken Sie in B2 und wählen Sie “Daten | Datenüberprüfung”. Stellen Sie ein:

  • Zulassen                       Liste
  • Quelle                            =Basis_Bezeichnung

24.08.2015_ 0006

Die Quelle muss exakt den gleichen Namen bekommen, den Sie der Spalte A vom Tabellenblatt “System” gegeben haben!

Bestätigen Sie mit OK und probieren Sie aus, ob die Bezeichnungen angezeigt werden. Klicken Sie in B2 auf den Auswahlpfeil am unteren rechten Rand der Zelle. Das muss so aussehen:

24.08.2015_ 0007

 

2. Der Betrag in Spalte C muss sich selber ermitteln.

Dazu wird die Funktion SVERWEIS verwendet, die auf die Tabelle verweist, die Sie im ersten Schritt angelegt haben. So sieht die Funktion für die erste Datenzeile (in C2) aus:

=SVERWEIS(B2;Basis;2;FALSCH)

24.08.2015_ 0009

B2 ist das Suchkriterium, der Eintrag aus der ersten Bezeichnung – hier die Bezeichnung “Brief”
Basis ist die Matrix – die Tabelle, in der gesucht wird. Tragen Sie entweder den Tabellennamen ein, den Sie vorhin vergeben haben oder markieren Sie auf dem System-Blatt die formatierte Tabelle.
2 ist die Spaltenzahl – aus der zweiten Spalte der Basis-Tabelle sollen die Werte übernommen werden.
FALSCH steht für den Bereich_Verweis “genaue Übereinstimmung”; damit verhindern Sie, dass Excel den nächsten Eintrag verwendet, wenn er einen Begriff nicht findet.

Bestätigen Sie die Funktion mit ENTER.

Bleiben Sie mit der aktiven Zelle im Bereich A1 bis C2 und wählen Sie “Start | Formatierte Tabelle”. Suchen Sie sich eine Farbe aus.

Die beiden Zeilen werden jetzt als formatierte Tabelle markiert, die letzte Zelle (C2) hat ein kleines Dreieck als Hinweis auf die letzte Zelle der Tabelle. Drücken Sie in C2 die TAB-Taste, um weitere Zeilen der Tabelle zu erzeugen. Sowohl die Datenüberprüfung aus B2 als auch die Formel aus C2 werden automatisch auf die nächste neue Zeile übertragen.

Da unsere Basis-Tabelle ebenfalls eine formatierte Tabelle ist, passen sich sowohl die Datenüberprüfung als auch der SVERWEIS automatisch an, wenn sie ergänzt wird.

3. Anzahl und Summe in Zeile 1 müssen sich an einen Filter anpassen
4. Summe und Anzahl müssen sich anpassen, wenn weitere Einträge am Ende der Tabelle hinzugefügt werden.

Anzahl und Summe werden mit der Funktion TEILERGEBNIS errechnet. Außerdem werden keine Zellbezüge in der Forma C2:C99 verwendet, sondern Bereichsnamen aus der Tabelle. Sie könne diese Funktion direkt oberhalb eintippen oder Sie können sich von Excel helfen lassen. Ich zeige den Weg mit Hilfe von Excel.

  • Klicken Sie in die Tabelle und wählen Sie “Tabellentools | Entwurf”.
  • Aktivieren Sie “Optionen für Tabellenformat | Ergebniszeile”.
  • Klicken Sie in die Ergebniszelle der Spalte B und wählen Sie dort aus der Auswahlliste “Anzahl”.

    26.08.2015_ 0000

    In Spalte B sollte in der Ergebniszelle bereits die Summe stehen. Falls nicht, wählen Sie aus der Auswahlliste die “Summe”.
    Die Funktionen lauten
    für die Anzahl in Spalte B =TEILERGEBNIS(103;[Bezeichnung]) und
    für die Summe in Spalte C =TEILERGEBNIS(109;[Betrag])
    TEILERGEBNIS berechnet nur die Zellen, die bei einem Filter übrigbleiben – durch Filter ausgeblendete Zellen werden nicht berechnet. Die Kennziffer 103 steht für die Anzahl, 109 für die Summe.
    Da wir in einer formatierten Tabelle stehen, werden automatisch die Bereichsnamen der Tabelle verwendet: [Bezeichnung] für die Spalte B und [Betrag] für die Spalte C. Diese Bereichsnamen sind dynamisch. Vergrößert sich die Tabelle, weil Sie neue Zeilen unten hinzufügen, vergrößert sich automatisch der zu berechnende Bereich.

  • Fügen Sie oberhalb der Tabelle eine neue, leere erste Zeile ein.
  • Kopieren Sie beide Formeln nach oben.
  • Deaktivieren Sie die Ergebniszeile wieder.

Probieren Sie aus, ob alles geklappt hat, indem Sie in Spalte B nur die Briefe anzeigen lassen. Jetzt müssen sich Anzahl und Summe ändern.

5. Die Bezeichnungen “Anzahl” und “Summe” stehen in der gleichen Zelle vor den Zahlen.

Das ist  in beiden Fällen das gleiche benutzerdefinierte Zahlenformat.

  • Klicken Sie B1 an und wählen Sie den kleinen Pfeil bei “Start | Zahl”, um den Dialog für das Zahlenformat aufzurufen. Sie können auch die rechte Maustaste in B1 klicken und “Zellen formatieren” wählen. Oder Sie können STRG+1 drücken.
  • In der linken Spalte klicken Sie auf “Benutzerdefiniert”.
  • Für die Anzahl wählen Sie als “Typ” die einfache 0 (Null) aus – die Anzahl hat keine Nachkommastellen, wird nicht in die Tausender gehen und braucht keine Währung. Klicken Sie in die Zeile direkt unter “Typ” links vor die Null und schreiben Sie in doppelten Anführungszeichen das Wort “Anzahl:”.
    Hinter das schließende Anführungszeichen kommt ein Sternchen und ein Leerschritt. Daran schließt sich die Null an.
    Das sieht so aus: “Anzahl:”* 0
    26.08.2015_ 0001

    Texte müssen immer in Anführungszeichen stehen. Das Sternchen sorgt dafür, dass das folgende Zeichen (ein Leerschritt) so oft wiederholt wird, bis die Zelle gefüllt ist. Damit wird “Anzahl” an den linken und die Ziffer an den rechten Zellenrand geschoben.

    26.08.2015_ 0002

  • Genauso gehen Sie für “Summe” vor. Wählen Sie aber statt des Typs  “0” den Typ #.##0,00 €. Tippen Sie davor “Summe:”*

    26.08.2015_ 0003

    Wenn die Zahlen ab Zelle C3 mit dem Währungsformat formatiert wurden, müssen Sie den kleinen Abstandshalter zwischen dem Euro-Zeichen und dem rechten Zellenrand noch einfügen. Nach dem € tippen Sie einen Unterstrich und danach einen Bindestrich: _-
    Der Bindestrich steht dafür, dass das folgende Zeichen nicht geschrieben wird, sondern nur der Platz als Leerraum eingefügt wird, den dieses Zeichen einnehmen würde. Hier also: Tippe keinen Bindestrich, sondern füge den Leerraum ein, den ein Bindestrich einnehmen würde.

    26.08.2015_ 0004

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.