ZMBP

Wissenschaftlich Arbeiten mit Computer und Internet
- Tabellenkalkulation

Uni Tuebingen

Kursübersicht

Suchen

Version Office 2021



1. Basiswissen

Übungsaufgaben

Beispielsdatei Bezüge
Beispieldatei Bedingte Formatierung Ausreisser
Beispieldatei Bedingte Formatierung gen-alignments
Beispieldatei Bedingte Formatierung Formel
Beispieldatei Sortieren Filtern Chemikalienliste
Beispieldatei - Pivot Insekten
Übungsdatei Tabellen in der Bachelorarbeit




Excel ist das Standardprogramm für Tabellenkalkulation. Im diesem Kursteil werden die grundlegenden Techniken erläutert.

Die Oberfläche von Excel wird besprochen und die Formatierung der Tabelle vorgestellt.
Das wesentliche an Tabellenkalkulation sind Bezüge und Formeln.
Da für Wissenschaftler die bedingte Formatierung sehr hilfreich sein kann, bekommt dieses Thema einen breiten Raum.
Allerdings erst mit dem nächsten Kapitel, Funktionen, bekommt dieses Thema seine volle Bedeutung.
Wie kann die Tabelle ausgewertet werden? Mit Sortieren und Filtern werden unübersichtliche Tabellen strukturiert.
Die Pivot-Tabelle ist eine optische Aufbereitung komplexer Datenbestände.
Am Ende werden weitere Werkzeuge eingeführt. Das Laden aus allen möglichen Datenquellen geht mit Power Query.
Power Pivot ist für riesige Datenmengen und mit Solver und Was-wäre-wenn-Analyse gibt es weitere Möglichkeiten die Daten auszuwerten.


1.1 Die Oberfläche von Excel

Menüband ("Ribbon")

Seit Office 2007 ist Excel mit den neuen Menübändern, Menüleisten oder Multifunktionsleisten (engl. Ribbon) versehen.

Oberfläche Excel 2016
Abb.: Oberfläche Excel 2016.

Die einzelnen Menübänder werden über die Register
Start, Einfügen, Seitenlayout, Formeln, Daten, Überprüfen, Ansicht, Entwicklertools [optional] aufgerufen.

Auf einem Menüband sind die Befehle in Gruppen geordnet.

Menübänder sind dynamisch.
Bei Bedarf erscheint auch ein neues Menüband.
Beispielsweise erscheint bei der Formatierung einer Tabelle das Menüband Tabellentools.
Je nach Platz auf dem Bildschirm wird das Aussehen der Leiste geändert.

Eine Ausnahme ist das Menüband Datei. Hier wird ein Menü geöffnet, das über das ganze Fenster geht.
Dort sind die wichtigen Befehle Öffnen, Speichern, Zuletzt verwendet, Drucken, Schließen, Beenden und Excel-Optionen untergebracht.

Office-Schaltfläche

Abb.: Office-Schaltfläche Excel - Öffnen einer Datei.




Die Befehle in den Menübändern werden auch über eine Tastenkombination aufgerufen.
Das ist manchmal praktischer, wenn Daten eingetippt werden, ist der Griff zur Maus umständlich.
Z.B. Alt + I und anschließend die F-Taste ruft das Dialogfeld Grafik einfügen auf.
Mit Alt und den Richtungstasten kann innerhalb des Fensters gewechselt werden. So kann völlig ohne Maus gearbeitet werden!

Multifunktionsleiste + Alt

Abb.: Multifunktionsleiste mit gedrückter ALT-Taste.


Größe der Ansicht ändern (Zoom)

  • Auf das Tabellenblatt klicken

  • Menüband Ansicht Gruppe Zoom
    oder
    Zoomregler in der Statusleiste unten rechts
    oder
    Strg + Rollrad mit der Maus


Eine Übersicht der Tastenkombinationen ist in der Excel-Hilfe (F1) erhältlich.
Dazu das Stichwort "Tastenkombinationen und Funktionstasten in Excel" eingeben.
Die Tastenkombinationen unterscheiden sich zwischen PC und Mac erheblich.

Übersicht von Microsoft


In der Tabelle bewegen

Strg + Pfeiltasten - Bewegen in einer zusammenhängenden Tabelle.
Es kann an den Anfang (Pfeil links oder das Ende (Pfeil rechts) einer Zeile gesprungen werden.
Leere Felder stehen für das Ende der Tabelle.
Pfeiltasten oben springt an den Anfang einer Spalte und Pfeiltaste unten an das Ende der Spalte.

Strg + Pos1 - Anfang des Tabellenblattes
Strg + Ende - Ende des Tabellenblattes.

Strg + Bild oben bzw. Strg + Bild unten - in den Tabellenblättern wechseln.


Zellbereich markieren

Beim Markieren von ganzen Bereichen ist meist die Umschalttaste dabei.

Umschalten + Pfeiltasten - Es wird zellenweise markiert, recht, links, unten und oben.

Umschalten + Bild oben bzw. Umschalten + Bild unten - Es wird je nach Bildauschnitt markiert, recht, links, unten und oben. Die Schrittweite hängt vom Zoom ab.

Strg + Umschalt + Pfeiltaste rechts - Alle Zellen rechts mit Inhalt werden markiert, links entsprechend, so kann eine zusammenhängende Tabelle markiert werden
Strg + Umschalt + Pfeiltaste unten - Alle Zellen nach unten mit Inhalt werden markiert, oben entsprechend.

F8 - Auswahl kann mit Pfeiltasten oder der Maus erweitert werden. Die Aktivierung wird mit Auswahl Erweitern in der Statusleiste angezeigt.
Erneutes Drücken von F8 deaktiviert diese Erweiterung.


Zusammenhängende Tabelle markieren:

  1. Mit der Maus oben links klicken
  2. Strg + Umschalt + Pfeiltaste rechts
  3. Strg + Umschalt + Pfeiltaste unten

Abkürzung:
Es kann mit Strg + Umschalt + Leertaste oder Strg + Umschalt + * eine zusammenhängende Tabelle markiert werden.
In den neuen Versionen geht auch Strg + A, wenn der Cursor in der Tabelle steht. Ein weiteres Strg + A markiert das ganze Tabellenblatt.

*Zusammenhängende Zellen in einer Tabelle - das bedeutet, es sind keine leeren Zellen, Spalten bzw. Zeilen dazwischen



Mit der Maus arbeiten

Multifunktionsleiste + AltZellbereiche werden mit gedrückter linker Maustaste markiert.
Die Maus zeigt ein weißes Kreuz.

Multifunktionsleiste + AltZeilen und Spalten werden mit der Maus in der Zeilen- und Spaltenköpfen markiert.
Die Maus zeigt einen schwarzen Pfeil.

Multifunktionsleiste + AltMaus auf die markierte Umrandung bewegen. Zellen können bewegt werden, die Maus zeigt ein schwarzer Doppelpfeil.

Multifunktionsleiste + AltDer Mauszeiger verwandelt sich in ein schwarzes Kreuz, wenn die rechte untere Ecke einer Zelle erreicht wird.
Das ist nützlich zum AutoAusfüllen der unteren oder rechts liegenden Zellen.

Multifunktionsleiste + AltDie Maus wird in den Spalten- oder Zeilenköpfen zu einem Kreuz mit Pfeilen.
Spaltenbreite bzw. Zeilenhöhe können verändert werden.



Multifunktionsleiste + Alt
Wenn die Umschalttaste gedrückt wird, kann auch mit zwei Klicks und mit der Maus markiert werden:
Das ist vor allem bei sehr großen Tabellen nützlich.
Klick auf den Anfang des Bereichs - Umschalttaste drücken und gedrückt halten - Klick auf das Ende des Bereichs.

Strg
Wenn die Strg-Taste gedrückt wird, können einzelne Zellen oder Bereiche mit der Maus gewählt werden




Menüband Ansicht - Zoom und Fenster

Auf dem Menüband Ansicht sind einige interessante Werkzeuge, die bei umfangreichen Tabellen sehr nützlich sind.

Zoom - Auswahl vergrößern
Fokussiert auf den markierten Bereich.

Fenster - Neues Fenster
Von der gleichen Datei wird ein neues Fenster geöffnet. Veränderungen werden in beide Fenster geschrieben.
Das ist nützlich bei großen Tabellen, wenn verschiedene Bereiche der Tabelle im Blick behalten werden sollen.

Fenster - Alle Anordnen
Die Fenster s.o. können horizontal, vertikal oder überlappend angeordnet werden.

Fenster - Fenster fixieren
Vorher markierte Spalten oder Zeilen darüber oder daneben werden fixiert. Gut für Überschriften oder 1. Spalte.
Wurde nur eine Zelle markiert, wird Zeile und Spalte darüber und daneben fixiert.
Mit der gleichen Schaltfläche wird die Fixierung aufgehoben.

Fenster - Teilen
Das Fenster der Tabelle in verschiedene Arbeitsbereiche teilen.
Teilung wird mit dem gleichen Schalter wieder aufgehoben

Ausblenden - Einblenden
Arbeitsmappe kann ein- oder ausgeblendet werden.

Nebeneinander anzeigen und Synchroner Bildlauf
Nur bei mehreren Arbeitsmappen aktiv, siehe Neues Fenster.
Erneutes klicken auf die Schaltfläche Nebeneinander anzeigen stellt die Fenster nebeneinander oder untereinander.
Verschiedene Arbeitsmappen werden nebeneinander/untereinander gezeigt.
Mit der Maus kann die Position und Größe eingestellt werden.
Fensterposition zurücksetzen stellt die ursprüngliche Größe und Position wieder ein.
Synchroner Bildlauf, Tabellen werden in allen Fenstern bewegt.




Symbolleiste für den Schnellzugriff und Menüband anpassen

Als Zugeständnis an Liebhaber der Oberfläche früherer Versionen mit den Symbolleisten wurde die Schnellzugriffsleiste eingeführt.
Das ist eine kleine benutzerdefinierte Symbolleiste. Häufig benutzte Befehle können in die Schnellzugriffsleiste zugefügt werden.


Anpassen der Schnellzugriffsleiste

  • Menüband Datei : Excel-Optionen

  • Register Symbolleiste für den Schnellzugriff.

  • Symbolleiste für den Schnellzugriff unter dem Menüband anzeigen aktivieren

  • Gewünschte Befehle mit dem Button Hinzufügen auswählen

Symbolleiste für den Schnellzugriff

Abb.: Symbolleiste für den Schnellzugriff aktivieren und anpassen.

  • Oder mit der rechten Maustaste auf eine freie Stelle der Schnellzugriffsleiste.
    Menüband anpassen...

    Symbolleiste für den Schnellzugriff anpassen

    Abb.: Symbolleiste für den Schnellzugriff anpassen.


  • Das Fenster der Excel-Optionen geht auf (siehe oben).

    Hinweis:
    Im Kontextmenü der Schnellzugriffsleiste kann auch die Position der Schnellzugriffsleiste geändert werden.
    Über oder unter dem Menüband (Siehe Abbildung).



Menüband anpassen

Seit 2010 kann auch das Menüband angepasst werden. Darüber hinaus kann ein eigenes Menüband definiert werden.
  • Menüband Datei : Excel-Optionen

  • Register Menüband anpassen

    Symbolleiste für den Schnellzugriff anpassen
    Abb.: Menüband anpassen



    Kontextmenü

    An allen möglichen Stellen wird mit der rechten Maustaste ein spezifisches Kontextmenü angezeigt.
    Hier ist man oft schneller fündig, als mühsam über die Menübänder zu suchen.



    Tabellen- und Diagrammschaltflächen

    Seit Excel 2013 sind rechts neben Tabellen und Diagramme besondere Schaltflächen.

    Bei Tabellen wird eine Schnellanalyse damit durchgeführt.
    Diagramme können mit diesen Diagrammschaltflächen konfiguriert werden.

    Weiter unten werden diese neuen Werkzeuge genauer erläutert.




    Was möchten Sie tun? und Online-Hilfe

    • Seit 2016 gibt es eine Schnellhilfe.
      Excel-Hilfe
      In der Menüleiste befindet sich das Feld Was möchten Sie tun?
      Hier können direkt Suchbegriffe eingegeben werden.

      In unserem Beispiel wurde das Stichwort "Zellen einfügen" eingegeben.

      Excel-Was möchten Sie tun?
      Abb.: Excel-Was möchten Sie tun? - Begriff "Zellen einfügen"

      Es wird unmittelbar auf die gesuchten Felder verwiesen.



    • Ist man mit Was möchten Sie tun? nicht fündig geworden,
      kann die Excel Hilfe mit Hilfe zu... in dem aufgeklappten Menü (Abb. siehe oben) aufgerufen werden.
      Es kann auch einfach das Tastenkürzel F1 verwendet werden.

      Excel-Hilfe
      Abb.: Excel-Hilfe.

    Diese Hilfe ist nur mit Internetanschluss brauchbar.
    Kritisch angemerkt. Den Suchbegriff in die Suchmaschine Google einzugeben ist oft hilfreicher.

    Bei den Symbolen im Menüband erscheint, wenn die Maus einige Sekunden ruhiggehalten wird, ein QuickInfo.
    Bei einigen wenigen Symbolen wird im Quickinfo ? Weitere Infos angeboten.
    Excel-Hilfe. - QuickInfo mit dem Hinweis ?Weitere Infos.
    Abb.: Excel-Hilfe. - QuickInfo mit dem Hinweis ?Weitere Infos.




    Arbeitsmappen

    Eine Excel-Datei enthält eine Arbeitsmappe, in der eine Vielzahl von Tabellenblätter abgelegt werden können.

    Beim Start von Excel werden automatisch eine neue Arbeitsmappen mit 1 Tabellenblatt erstellt.

    Wenn standardmäßig mehr als 1 Tabellenblatt erstellt werden soll:

    • Menüband Datei : Excel-Optionen

    • Register Allgemein

    • Beim Erstellen neuer Arbeitsmappen

    • So viele Arbeitsblätter einfügen
      Gewünschte Zahl einstellen




    Speichern

    Microsoft Info

    Das Exceldokument kann in verschiedenen Dateiformaten gespeichert werden. Das Excel Standardformat ist .xlsx.
    • Ist das Dokument noch nicht gespeichert worden:
      Menüband Datei : Speichern

      Würde das Dokument bereits gespeichert:
      Menüband Datei : Speichern unter...

    Menüband Datei.

    Abb.: Menüband Datei.

      Normalerweise wird das Dokument auf dem lokalen Rechner gespeichert,
      dann ist "Dieser PC" oder Durchsuchen richtig.

      Ein Dialogfenster öffnet sich und das Dateiformat kann eingestellt werden.

      Dialogfenster Speichern
      Abb.: Dialog Speichern unter.



    • Bei Exceldatei wird standardmäßig der Dateityp .xlsx verwendet.
      Hintergründe zu dem neuen Standard Office Open XML Format von Helmut Vonhoegen
      Es kann auch das binäre Dateiformat älterer Excelversionen eingestellt werden (.xls).
      Einstellung in Excel-Optionen : Speichern : Arbeitsmappe speichern : Datei in diesem Format speichern.
      Bei ältere Versionen (Excel 2003) zum öffnen und speichern von .xlsx-Dateien,
      wird ein zusätzliches Programm benötigt-> Compatibility Packs


      Daneben gibt es noch die Formate
      .xlsm - Makros werden in diesem Format mit gespeichert.
      .xlsb - Binärdateiformat für große und komplexe Tabellen (stärkere Komprimierung)

      Makros werden seit 2007 nicht mehr in der Standard Exceldateien (.xlsx) gespeichert, daher wird das neue Dateiformat .xlsm angeboten.
      Makros sind nützliche, aber auch gefährliche Bestandteile zum Programmieren einer Exceltabelle (weitere Infos).

      Daher Vorsicht bei xls und xlsm Dateien. Makros werden von Kriminellen benutzt, um auf fremden Rechnern Schadsoftware zu installieren
      Zum Beispiel könnten Erpressungstrojaner in xls- und xlsm-Dateien in E-Mail Anhängen versteckt sein.
      Es sollte daher nur noch das .xlsx Format verwendet werden.




    • Textdatei die Endung .txt .
      Verwendet werden txt-Dateien z.B. bei Adressdateien, die in E-Mail-Programme einfügt werden sollen
      oder als Austauschformat bei Messgeräten z.B. HPLC, Fluorimeter/Photometer

    • Textdatei (s.o.) mit der Endung .csv
      comma-separated values
      Eine Textdatei ohne Formatierungen. Trennung der Spalten durch Komma (Semikolon oder andere Zeichen)
      csv ist ein beliebtes Format zum Austausch unter verschiedenen Programmen und Datenbanken.
      Viele Analysegeräte in der Biologie verstehen das csv-Format


    • Webseite die Endung .html
      Viele Internet-Programmierer hassen den Microsoft html-Code, da er mit überflüssigen Formatierungen angefüllt ist.
      Beachte die Einstellung unter "Speichern"
      Gesamte Arbeitsmappe - alles wird gespeichert
      Auswahl - nur eine Auswahl wird gespeichert
      Titel ändern...Titel der Webseite
      Veröffentlichen...Die Tabelle kann bei jedem Abspeichern in der Webseite aktualisiert werden.
      Sinnvoll bei einem verbundenen Webserver.

      Tipp:
      dmcritchie.mvps.org/excel/xl2html.htm
      Diese Makros generieren einen schönen HTML Code aus der Exceltabelle


    • XML-Format. Austauschformat für bestimmte Anwendungen. Ist als Textdatei lesbar.
      Der Vorteil des XML-Formats ist, dass damit die Dokumente eine standardisierte Struktur aufweisen,
      aus der viele andere Anwendungen Informationen herauslesen können (Datenbanken und Log-Dateien).

    • PDF-Format.

      Um weitere Optionen zu bekommen ist unten links eine kleine grüne Schaltfläche
      Weitere Optionen...

      Dialogfenster Speichern. Dateityp PDF.
      Abb.: Dialogfenster Speichern. Dateityp PDF.

      Optimieren für: Standard (Onlineveröffentlichung und Drucken) oder Minimale Größe (Onlineveröffentlichung).
      Bei Minimaler Größe wird die Auflösung der Bilder verringert.
      Optionen... Hier kann eingestellt werden welcher Bereich für die pdf-Datei gespeichert wird.




    Tabellenblätter

    Tabellenblätter sind die einzelnen Seiten in einer Excel-Datei.
    Auf einem Tabellenblatt können auch mehrere Tabellen untergebracht werden, was aber nicht sehr übersichtlich ist.
    Vor allem bei umfangreichen Tabellen ist es besser, die Tabellen auf unterschiedlichen Tabellenblättern unterzubringen.
    Tabellen auf unterschiedlichen Tabellenblättern lassen sich ebenso aufeinander beziehen (siehe Zellbezug).

    Tabellen anlegen

    • Tabellenblatt einfügen

      Menüband Start Gruppe Zellen Symbol Einfügen und Blatt einfügen

      Tabellenblatt einfügen

      Abb.: Tabelle einfügen

      oder
      Tastenkombination Umschalt + F11

    • oder in der Statusleiste

      Tabelle einfügen. Umbenennen einfach mit Doppelklick auf den Tabellennamen
      Abb.: Tabelle einfügen. Umbenennen einfach mit Doppelklick auf den Tabellennamen.



    Tabellenblätter umbenennen oder kopieren und verschieben

    Blätter umbenennen, kopieren und verschieben sind wenig übersichtlich unter dem Symbol "Format" untergebracht.
    Hier hilft das Kontextmenü mit der rechten Maustaste besser.
    Es geht auch der Trick mit dem Doppelklick auf den Tabellennamen in der Statusleiste.
    Anschließend kann der Name direkt bearbeitet werden
    • Tabellenblatt, das kopiert werden soll, markieren.

    • Menüband Start Gruppe Zellen Symbol Format (Pfeil)
      Blatt umbenennen
      Blatt kopieren/verschieben.
      ..

      Tabellenblätter anordnen


      Blatt kopieren/verschieben
      Abb.: Blatt kopieren oder verschieben.



    Tipps:
    Tabellenblatt umbenennen auch mit Doppelklick auf den Tabellennamen in der Statusleiste.

    Tabellenblatt verschieben mit der Maus in der Statusleiste.

    Das Dialogfeld Verschieben oder Kopieren lässt sich einfacher über rechte Maustaste auf den Namen des Tabellenblattes in der Statusleiste aufrufen.



    Tabellenblatt löschen.

    • Menüband Start Gruppe Zellen Symbol Blatt löschen

    • Eventuell Löschabfrage bestätigen.


      Tabellenblatt löschen


      Alternativ geht das auch mit rechter Maustaste auf den Tabellennamen in der Statusleiste
      und anschließend Löschen klicken.


    Tabelle ausblenden/einblenden.

    Verwendung ist unklar. Wenn ein Blatt aus Versehen ausgeblendet wurde, wird das Blatt verzweifelt gesucht.
    Hier wird gezeigt wie es wieder eingeblendet wird.
    • Rechte Maustaste auf das Tabellenblatt in der Statusleiste
      Blatt/Ausblenden
      bzw. Blatt/einblenden

      Kontextmenü Tabellenblatt
      Abb.: Kontextmenü Tabellenblatt.




    Zeilen und Spalten

    Mit den Tastenkürzel Strg + + und Strg + - kann eine Zeile oder Spalte hinzugefügt bzw. gelöscht werden.
    Das kommt so häufig vor, diese Tastenkürzel sollte man daher kennen.

    Zeilen einfügen bzw. löschen

    • Cursor an die Stelle an der eine Zeile eingefügt werden soll.

    • Anzahl der Zeilen markieren die eingefügt werden sollen.

    • Menüband Start Gruppe Zellen Symbol Zeilen einfügen oder Strg + Pluszeichen.


    • Löschen mit Markieren und Strg + Minuszeichen.

    Spalten einfügen bzw. löschen

    • Cursor an die Stelle an der eine Spalte eingefügt werden soll.

    • Anzahl der Spalten markieren die eingefügt werden sollen.

    • Menüband Start Gruppe Zellen Symbol Blattspalten einfügen oder Strg + Pluszeichen.


    • Löschen mit Markieren und Strg + Minuszeichen.





    1.2 Bezüge und Formeln

    Die Tabellenzelle ist das Grundelement einer Tabelle.
    Durch die Spalten und Zeilen einer Tabelle hat jede Zelle einer Tabelle ihr einzigartiges Bezugsystem.
    Jede Zelle kann durch die Anzahl der Spalten von links und der Anzahl der Zeilen von oben bezeichnet werden.
    Zum Beispiel B6 ist die 2. Spalte von links und die 6. Zeile von oben.

    Der Bezug wird standardmäßig in der A1-Schreibweise angegeben. Die Buchstaben A,B, ..AA, AB.. sind die Spalten und 1...1.048.576 sind die Zeilen.
    In Excel (ab 2007) stehen 1.048.576 Zeilen und 16.384 Spalten zur Verfügung.



    Absoluter und relativer Bezug

    Beispielsdatei

    In der üblichen Bezugsart mit Spaltenbuchstaben und Zeilennummern bedeutet die Formel "=B3" in einer beliebigen Zelle, dass dort der Wert der Zelle B3 eingetragen wird.
    Ich beziehe mich auf "B3".
    Es gibt 2 Arten einen Bezug darzustellen.
    Der absolute und der relative Bezug.

    Beim relativen Bezug wird die "Entfernung" bis zur bezogenen Zelle beschrieben.
    Man könnte es so ausdrücken: "Das ist der Wert der Zelle x Spalten und y Zeilen von hier."
    Beim relative Bezug wird die Bezeichnung (z. B. =B3) beim Kopieren daher geändert.
    Ich kopiere die Zelle mit dem Bezug "=B3" in eine neue Zelle eine Zeile unterhalb.
    Dort wird automatisch ein neuer Wert errechnet und die Bezeichnung hat sich geändert (jetzt =B4).
    Es wird der relative Bezug kopiert!

    Der relative Bezug ist am meisten gewünscht.
    Daher ist der relative Bezug Standard bei Excel.
    Das ist in der A1 Schreibweise auf dem ersten Blick nicht ersichtlich.
    Wenn in einer Zelle "=B3" steht, heißt das der relative Bezug zu der Zelle B3.
    Wird auf die Z1S1 Bezugsart (siehe oben) umgestellt, wird das klarer.
    In der Zelle A1 steht anstatt =B3 die Formel =Z(2)S(1). Das bedeutet 2 Zeilen nach unten und 1 Spalte nach rechts.

    Der absolute Bezug wird bei der A1 Bezugsart mit einem vorangestellten "$" bezeichnet.
    Beim absoluten Bezug ist die Zahl der Spalten und Zeilen vom Ursprung auf dem Tabellenblatt festgelegt.
    Es ist genau "B3" gemeint. Beim Kopieren bleibt der Bezug B3 konstant.
    Ich kopiere die Zelle mit "=$B$3" in einer neuen Zelle. Dort steht auch wieder "=$B$3" und der alter Wert wird angezeigt.

    Werden neue Zeilen und Spalten eingefügt, so wird die Formel des relativen oder des absoluten Bezug neu berechnet,
    der Zahlenwert bleibt stets erhalten.

    Bezug Beschreibung
    $A1 Spalte A ist absolut, Zeile 1 ist relativ
    A$1 Spalte A ist relativ, Zeile 1 ist absolut
    $A$1 Spalte A ist absolut, Zeile 1 ist absolut
    A1 Spalte A ist relativ, Zeile 1 ist relativ

    Beispiel für absoluten und relativen Bezug Beispielsdatei


    Excel kann auf die Z1S1-Bezugsart umgestellt werden. Jetzt wird zuerst die Zeilennummer und dann die Spaltennummer angegeben!
    Zum Beispiel die Zelle Z6S2 ist 6 Zeilen von oben und 2 Spalten von links.
    Hier ist der absolute und relative Bezug klarer zu erkennen.
    Die Z1S1-Bezugsart ist allerdings sehr unüblich.

    Bezugsart umstellen

    • Menüband Datei : Excel-Optionen.

    • Register Formeln

    • Einstellungen Z1S2-Bezugsart

      Z1S1-Bezugart einstellen
      Abb.: Z1S1.Bezugsart umstellen.


    Die Z1S1.Bezugsart

    Abb.: Die Z1S1-Bezugsart .

    Z1S1-Bezugsart
    Der Kursor steht in Z1S1.
    Es soll auf Z3S2 bezogen werden =Z3S2 gibt den Wert der Zelle Z3S2 als absolute Bezug in die Zelle Z1S1.
    =Z3S2 ist der absolute Bezug auf die Zelle.
    =Z(2)S(1) ist der relative Bezug auf die Zelle zwei Zeilen nach unten und eine Spalte nach rechts.


    Die Schreibweise im Z1S1 Bezugsystem ist anders. Zum Beispiel bedeutet =Z3S2 der absolute Bezug. Also identisch mit =$B$3.
    Der relative Bezug wird mit Klammern angegeben.
    Zum Beispiel =Z(-2)S(3) ist relativ von der Ausgangszelle 2 Zeilen nach oben und 3 Spalten nach rechts.
    In dieser Schreibweise sind die Verhältnisse also klarer.

    Bezug Beschreibung
    Z(-1)S1 Zeile 1 ist relativ (eins nach oben), Spalte 1 ist absolut,
    Z1S(-1) Zeile 1 ist absolut, Spalte 1 ist relativ (eins nach links)
    Z1S1 Zeile 1 ist absolut, Spalte 1 ist absolut
    Z(-1)S(-1) Zeile 1 ist relativ (eins nach oben), Spalte 1 ist relativ (eins nach links)




    Die Tastenkombination für die Umwandlung von relativem in absolutem Bezug ist

    PC: Taste F4

    Mac: Tastenkombination Apfel + T




    Aufgaben:
    Gebe in A1 einen Zahlenwert ein. In C3 einen Bezug auf A1 (=A1).
    Füge dazwischen neue Spalten ein. Ändert sich der Zahlenwert?
    Füge vor A1 neue Spalten ein.
    Ändert sich der Bezug?
    Nun führe den Test mit einem absoluten Bezug durch. In C3 füge =$A$1 ein.
    Gibt es einen Unterschied zwischen absoluten und relativen Bezug?
    (Nein, Excel berücksichtigt jedes Mal, dass eine Spalte/Zeile eingefügt wurde. Die Formel ändert sich. der Wert ändert sich nicht.)

    Kopiere den Bezug in C3 in ein anderes Feld. Beobachte die Bezüge. Unterschied absoluter und relativer Bezug?
    (Ja, nur der Wert des absoluten Bezug bleibt erhalten)


    Bezug anderes Tabellenblatt

    Es geht auch ein Bezug in andere Tabellenblätter.
    Das Tabellenblatt wird mit einem Ausrufezeichen angegeben.

    =Tabelle1!B3
    bedeutet die Zelle "B3" in dem Tabellenblatt "Tabelle1".
    Dabei ist der Bezug auf die Zelle B3 relativ, nicht der Bezug auf das Tabellenblatt.
    Es ist genau "Tabelle1" gemeint. Nicht 1 Tabellenblatt neben dem aktuellen.
    =Tabelle1!$B$3
    wäre der absolute Bezug.


    Zellbereiche

    Ein Zellbereich wird mit einem Doppelpunkt angegeben.
    Zum Beispiel bedeutet B3:B5 die Zellen von B3, B4 und B5.

    B3:C4 sind die 4 Zellen B3, B4, C3 und C4.

    Eine Spalte kann zum Beispiel mit B:B angegeben werden und bedeutet die Spalte B.
    Zeile 3:3 ist die 3. Zeile.

    Zellbereiche sind in Funktionen eine große Erleichterung.
    Es ist einfacher =Summe(B3:D10) zu schreiben, als =B3+B4+...B10+C3+C4...C10+D3+D4..+D10.




    Einen Namen eingeben statt Bezug

    Beispielsdatei

    Für Felder und ganze Bereiche kann ein sprechender Namen eingegeben werden.
    Nun wird dieser Name statt der Bezug verwendet.
    Statt "=B3" kann einfach wie in dem Beispiel "=mwst" benutzt werden.
    Erlaubt sind Buchstaben und Zahlen, max. 255 Zeichen lang.
    Groß- und Kleinschreibung wird nicht unterschieden.
    Im Namen darf kein Leerzeichen sein.
    Der Namen gilt für die ganze Arbeitsmappe.
    Der Namensbezug wirkt wie ein absoluter Bezug.

    • Zelle markieren

    • Oben links im Namensfeld einen sinnvollen Namen eingeben.
      In unserem Beispiel ist das "mwst" für das Feld B3.

    • Eingabe-Taste drücken

      Zellen mit Namen belegen
      Abb.: Zellen mit Namen belegen . Namensfeld ist markiert.

      Alternativ kann in dem Menüband Formeln : Name definieren gewählt werden.
      Oder im Kontextmenü (rechte Maustaste) Name definieren.

    Jetzt kann das Feld statt mit "B3" nun mit dem Namen "mwst" angesprochen werden.

    Alternativ geht das auch mit dem Namens-Manager.

    • Menüband Formeln Gruppe Definierte Namen Symbol Name definieren

      Zellen mit Namen belegen

      Abb.: Namen definieren.

    • Symbol Namens-Manger
      Bereits vergebene Namensbereiche werden verwaltet.



    Bereiche mit Namen belegen

    Es können nicht nur Zellen sondern auch ganze Bereiche im Namensfeld mit einem Namen belegt werden.




    Strukturierte Verweise ("structured references")

    Die Bezüge auf Zellen und Zellbereiche in Formeln sind in langen Formeln nicht übersichtlich.
    Gut ist, wenn Zellen mit einem Namen versehen werden.
    Seit Excel 2007 können einzelne Bereiche der Tabellen angesprochen werden - Kopfzeilen, Spalten, Zeilen, Ergebnisse einer Tabelle.
    Es wird von strukturierten Verweisen gesprochen.

    Mit "Tabelle" ist eine Tabelle auf einem Arbeitsblatt gemeint, auf der mehrere Tabellen liegen können.
    Daher ist es wichtig, dass die Tabelle (=Tabellenobjekt) in Excel benannt wird.
    Dazu die Tabelle markieren und in der Menüband Einfügen : Tabellen : Tabelle aufrufen (Strg + T).
    Der Tabellenname wird anschließend im
    Menüband Tabellentools : Eigenschaften : <Tabellenname> eingetragen. Siehe Abbildung.
    Für Tabellenname kann ein beliebiger Name eingesetzt werden.
    Jetzt können z.B. die Daten mit =Tabellename[#Daten] angesprochen werden.

    Vorteil von strukturierten Verweisen ist, dass alles flexibler ist, da die Tabelle problemlos erweitert werden kann,
    während bei Zellbezügen die Formeln geändert werden müssen.

    Für die Tabelle steht nun ein zusätzliches Menüband Entwurf zur Verfügung:
    Die Tabelle kann hier formatiert werden.
    In Menüband Entwurf verwandelt der Befehl In Bereich konvertieren die Tabelle wieder in eine normale Tabelle.

    Siehe Excel-Hilfe.
    Die Bezeichnungen sind in einem deutschen Excel auch in Deutsch, ist aber fehlerhaft. Es wird Komma statt Semikolon verwendet (siehe unten).


    Strukturierte Tabelle
    Abb.: Strukturierte Tabelle



    Syntax

    Innerhalb der Tabelle entfällt der Tabellenname.
    Wird außerhalb der Tabelle auf Elemente bezogen, muß mit dem Tabellennamen begonnen werden.
    Beispiel
    Tabelle 'Messung' mit Spalten 'Mutante1', 'Mutante2' usw.

    Innerhalb der Tabelle:
    =[Mutante 1]*[Mutante 2]
    Außerhalb der Tabelle:
    =Messung[Mutante 1]*Messung[Mutante 2]

    Spaltenbezeichner - mit eckiger Klammer werden die Spalten bezeichnet.
    Beispiele sind zum Beispiel [Spalte1] oder [Mutante 1]

    Elementbezeichner - mit eckiger Klammer und # sind Bezeichnungen für besondere Elemente in der Tabelle
    Beispiele sind [#Alle], [#Daten], [#Kopfzeile], [#Ergebnisse]

    Eine Ergebniszeile muss eventuell eingefügt werden.
    Dazu in der Menüleiste Entwurf das Kästchen vor Ergebnisszeile aktivieren.

    Tabellenbezeichner - [[#Kopfzeile];[Mutante 2]]
    es wird die Überschrift der Spalte "Mutante2" ausgegeben.
    Doppelte eckige Klammer ist notwendig.

    Verweis auf Zellen in der gleichen Zeile - mit einem vorgestellten @ wird auf den Wert in dieser Zeile in der bezeichneten Spalte verwiesen.
    Beispiel
    =Messung[@[Mutante1]]
    Es wird der entprechende Wert in der gleichen Zeile aus der Spalte Mutante1 ausgegeben.

    Mehrere Spalten
    =SUMME(Messung[@[Messung 1]];Messung[@[Messung 3]])
    (In der deutschen Microsoft Excel Hilfe wird fäschlichweise dafür ein Komma verwendet (das geht in englischem Excel))
    Bereiche mit Doppelpunkt (:)
    =SUMME(Messung[@[Messung 1]:[Messung 3]])

    Mit der Matrixformeleingabe Strg + Umschalt + Eingabe kann auf ganze Zellbereiche bezogen werden.
    Beispiel
    Felder markieren, wie die Anzahl der Zeilen. Folgendes eingeben und mit Strg + Umschalten + Eingabe abschließen
    {=Messung[[Mutante 1]:[Mutante 4]]}





    Formeln eingeben

    Formeln führen Berechnungen in einem Arbeitsblatt aus. Dazu wird in eine Zelle eine Formel eingegeben.

    In Excel fangen Formeln mit einem Gleichheitszeichen = an.

    Der grundlegende Aufbau einer Formel
    = Operand Operator Operand

    Es werden arithmetische Operatoren verwendet (siehe unten).

    Die Besonderheit von Tabellenkalkulationsprogrammen besteht in der Verwendung von Zellbezügen als Operand in der Formel (Zellbezug).

    Es können auch Konstante als Operand eingesetzt werden, zum Beispiel =B3+3.

    Funktionen als Operand in Formeln erweitern die Möglichkeiten erheblich.

    In Excel wird bei Veränderungen der Tabelle, die Formel automatisch neu berechnet.

    Beispiele:
    =B3+3
    Gleichheitszeichen | Operand als Zellbezug | Operator | Operand als Konstante
    =SUMME(A1:A5) + 1000
    Gleichheitszeichen | Operand als Funktion mit Zellbezug | Operator | Operand als Konstante



    Arithmetische Operatoren

    +
    =2+3

    -
    =2-3

    /
    =2/3

    *
    =2*3

    Potenzen ^- das Zeichen "Dach" oder Caretzeichen
    =2^3

    %
    =2%

    &
    ="Text1" & "Text2"

    ;
    Semikolon kann in Funktionen mehrere Bezüge verketten
    =Summe(A1:A5;C1:C5)



    Beispiel

    Tipps:
    In Excel kann auch Text verkettet werden. Der Operator für die Verkettung von Text ist "&".
    ="Dieter" & "Steinmetz"
    gibt DieterSteinmetz aus.

    Text in doppelte Anführungszeichen
    ="Dieter" & " " & "Steinmetz"
    gibt Dieter Steinmetz aus.

    Bei der Verkettung wird eine Zahl oder ein Datum als Operand automatisch in einen Text umgewandelt.
    Bei Datum wird die Funktion =TEXT(A1;"TT.M.JJ") benötigt, sonst wird das Datum als eine Zahl dargestellt.


    Vergleichsoperatoren

    Vergleichsoperatoren werden vorwiegend in Funktionen eingesetzt.

    = gleich

    < kleiner

    > größer

    <= kleiner oder gleich

    >= größer oder gleich

    <> ungleich

    Beispiel:
    =A1>100
    gibt WAHR aus, falls in der Zelle A1 ein Wert über 100 steht, sonst FALSCH.


    Tipp
    Soll die Formel als Text sichtbar bleiben, so ist ein einfaches Hochkomma vor die Formel einzufügen.
    Beispiel '=3*4
    Das Hochkomma wird nicht angezeigt.
    Achtung, dann funktioniert die Formel natürlich nicht mehr.

    Es geht auch eine Leerzeichen statt ein Hochkomma, ist aber nicht so übersichtlich.



    Formeln analysieren

    In Excel stehen Werkzeuge zur Verfügung um Formeln zu überprüfen.



    Tipp:
    In der Statusleiste von Excel werden ohne Formeln der Mittelwert, die Anzahl und die Summe von markierten Zellen angezeigt.
    Das kann im Kontextmenü eingestellt werden.


    Formeln im Tabellenblatt anzeigen

    • Menüband Formeln Gruppe Formelüberwachung
      Symbol Formeln anzeigen

      Formel anzeigen
      Abb.: Formeln anzeigen.. .

    • F2 oder Strg + # (nur deutsche Version)
      alle Zellen, die mit der Formel zusammenhängen werden farblich markiert.
      Mit Esc wieder ausschalten.

      Formel anzeigen

      Abb.: ... Formeln werden angezeigt.




    Formelzellen markieren

    In einem Tabellenblatt werden die Zellen mit Formeln markiert.
    • Menüband Start : Bearbeiten : Suchen und Auswählen
      Strg + G
      oder F5

    • Inhalte...

    • Option Formeln aktivieren und OK.

    • Mit Tab kann jede einzelne Formel angesprungen werden.




    Formelüberwachung (Mac: Detektiv)

    • Menüleiste Formeln Gruppe Formelüberwachung
      Symbol Spur zum Vorgänger
      Symbol Spur zum Nachfolger


      Mehrmaliges Klicken auf das Symbol erweitert die Berechnung.


    Abb.: Formelüberwachung. Spur zum Vorgänger

    Beispielsdatei



    Tipp.:

    Bei Macs ist das unter Extras : Detektiv zu finden.



    Formelauswertung

    Die Formel kann Schritt für Schritt ausgewertet werden. So lassen sich leichter Fehler in langen Formeln finden.
    • Menüband Formeln Gruppe Formelüberwachung
      Formelauswertung

      Formel auswerten
      Abb.: Formel auswerten - Einzelschritte




    Teilberechnungen in einer komplexen Formel angeben

    So ähnlich wie Formelauswertung, siehe oben.
    • Formel markieren und doppelklicken

      Einen Teilbereich aus einer Formel markieren

    • F9 drücken und Excel berechnet das Teilergebnis.
      Mit ESC wird die ursprüngliche Formel wieder dargestellt.



    Formelüberwachungsfenster

    Mit dem Überwachungsfenster öffnet sich ein extra Fenster.
    Hier können ein oder mehrere Werte übersichtlich überwacht werden.
    Sehr nützlich bei riesigen Tabellen.
    • Menüband Formeln Gruppe Formelüberwachung
      Überwachungsfenster
      anzeigen

    • Überwachung hinzufügen... Auf die Formel klicken.


    Abb.: Formelüberwachungsfenster.



    Fehlerüberprüfung (ab Excel 2010)

    Ein neues Werkzeug hilft Fehler in der Formel aufzuspüren - Fehlerüberprüfung
    • Zelle markieren oder es wird die ganze Tabelle ausgewertet

    • Menüband Formeln Gruppe Formelüberwachung
      Formelüberprüfung anzeigen

      Fehlerüberprüfung
      Abb.: Fehlerüberprüfung






    Formeln in Festwerte umwandeln

    Manchmal ist es wünschenswert Formeln durch feste Werte zu ersetzen. Die Formel wird einfach in eine Zahl (Wert) verwandelt.
    Das wird eingesetzt wenn ein Wert kopiert werden soll

    • Formel(n) markieren

    • Menüband Start Gruppe Zwischenablage Symbol Kopieren
      oder Strg + C

    • Menüband Start Gruppe Zwischenablage Symbol Einfügen : Werte einfügen.

      Werte einfügen
      Abb.: Werte einfügen



      Tipp:
      Mit Inhalte einfügen... geht ein Fenster auf.
      jetzt kann der Inhalt differenziert werden.

      Inhalte einfügen
      Abb.: Inhalte einfügen


    Formel in Wert verwandeln
    Formel markieren und F2 und anschließend F9 drücken und mit Return abschließen.

    Bei Mac einfach F1 drücken.



    Tipp:
    Bei Kopieren erscheint unten rechts das Symbol Einfüge-Option.
    Hier auf Symbol Wert klicken. Dann wird statt der Formel der Wert eingefügt.

    Interessant ist auch Symbol Verknüpfung einfügen.
    Es wird ein absoluter Bezug auf die Zelle hergestellt.






    1.3 Daten eingeben

    Wissenschaftliches Arbeiten besteht oft darin langweilige Daten einzugeben.
    Excel hilft bei der effektiven Eingabe.

    Anzeige der Daten

    In der Tabelle soll ein Fehler sein. Das Ergebnis wird nicht ausgegeben.
    Oft verrät schon ein Blick, dass statt einer Zahl ein Text eingegeben wurde.
    Zahlen stehen rechtsbündig.

    Text - wird linksbündig angezeigt,
    Zahlen - werden rechtsbündig angezeigt,
    Datum - ist eine Zahl, daher rechtsbündig,
    Hyperlink - blau und unterstrichen
    Wahrheitswert - zentriert (WAHR oder FALSCH)

    Beispiel


    Tipps:
    Es können auch Brüche eingegeben werden.

    "1/3" funktioniert allerdings nicht, da Excel hier an ein Datum denkt (Anführungszeichen beim Einfügen in Excel weglassen!).
    Erst mit "0 1/3" kann korrekt ein Bruch eingegeben werden.
    Dementsprechend entspricht "2 1/2" genau 2,5. Brüche lassen sich mit einem entsprechenden Zahlenformat darstellen.


    Bei Jahreszahlen denkt Excel von 00 bis 29 an 2000 bis 2029. Bei 30 bis 99 an 1930 bis 1999.




    Wohin geht die Markierung nach der Eingabe?
    Nach der Eingabe der Daten wird meist die Eingabetaste gedrückt. Wohin springt dann der Cursor? Das kann eingestellt werden.

    • Office-Schaltfläche Excel-Optionen

    • Register Erweitert

    • Markierung nach dem Drücken der Eingabetaste verschieben
      Richtung Unten - Rechts - Oben - Links



    Tipp:
    Statt der Eingabetaste kann auch die Tabtaste gedrückt werden. Der Cursor springt dann nach rechts
    Oder es kann nach der Eingabe die entsprechende Pfeiltaste (links, rechts, oben unten) benutzt werden.



    AutoAusfüllen

    AutoAusfüllen ist ein sehr hilfreiches Werkzeug um Serien in die Tabelle einzugeben.
    Dabei versucht Excel eine Logik zu erkennen.
    Bei lineren Zahlenreihen berechnet Excel ein Inkrement, das ist die Differenz zwischen den Zahlen.

    Eine ausführliche Erläuterung und das AutoAusfüllen für rekursive Reihen findet sich unter
    http://www.uni-giessen.de/~g021/PDF/xl2010_automatisches_ausfuellen.pdf

    • Zellen markieren, Ausfüllkästchen, kleines schwarzes Kreuz Multifunktionsleiste + Alterscheint, unten rechts.

    • Beispiel Zahlenreihe

      Markiere 1;3;5 oder 2;4;6 -> ungerade und gerade Zahlenreihe

      Markiere 1 ;2,1 ;3,05 und 3,9 ->erkennt Excel kein Inkrement, es wird eine sogennate Trendrechnung gemacht

    • Beispiel mit dem heutigen Datum.

    • Es geht auch Text mit Zahlen.

    • Auto-Ausfülloptionen
      Zellen werden kopiert oder eine Datenreihe ausgefüllt



    Abb.: Auto-Ausfülloptionen - ungerade Zahlen.



    Tipps:
    • Letzte Auswahl wird der Standard für die nächste Auswahl.
    • STRG drücken beim AutoAusfüllen kopiert die Werte, wenn letzte Auswahl Datenreihe ausfüllen war und umgekehrt.

    • STRG + R drücken, um von links zu kopieren.
      STRG + U kopiert von oben
    • Doppelklick auf das Ausfüllkästchen fügt die Formel in allen belegten Zellen unterhalb ein.




    Verändern der Schrittweite

    • Werte markieren

    • Menüband Start Gruppe Bearbeiten Symbol Füllbereich
      Füllbereich


      Unten, Rechts, Oben und Links kopiert die Zahlenwerte in freie Zellen darunter, rechts daneben, darüber oder links daneben.


      Reihe...

    • Jetzt können verschieden Schrittweiten eingestellt werden.

      Es kann auch ein Endwert, der nicht überschritten wird, benutzt werden.



      Abb.: Ausfüllen, verschieden Schrittweiten einstellen.


      Endwert: Eine Reihe wird beim Endwert abgebrochen, auch wenn mehr Zellen markiert wurden.
      Angabe von Inkrement und Endwert notwendig.
      Soll eine Reihe von einem Anfangswert und Endwert bei unbekanntem Inkrement erstellt werden:
      Reihe mit der Zelle mit Anfangswert und der Zelle mit Endwert markieren.
      Dialog Reihe aufrufen, das erforderliche Inkrement wird berechnet.



    Aufgabe:

    • Bitte ein Beispiel mit einem Datum und Differenz von 14 Tagen erstellen.

      Noch schneller geht es wenn die ersten Werte eingegeben werden und mit AutoAusfüllen weiter gezogen werden.




    "Blocksatz"

    Das Werkzeug Blocksatz ist etwas unpassend unter Füllbereich untergebracht.
    Es kann eine Liste in eine Zelle schreiben. Siehe Abbildung.
    Geht nur mit Text.

    • Text der "blocksatzartig" aufgeteilt werden soll.

      Füllbereich - Blocksatz

    • Zellbereich markieren

      Füllbereich - Blocksatz



    • Menüband Start Gruppe Bearbeiten Symbol Füllbereich

      Füllbereich - Blocksatz
      Abb.: Füllbereich - Blocksatz.




    Eigene AutoAusfüll-Listen

    Nicht nur Zahlenwerte können mit AutoFüllen ergänzt werden, sonder jeder beliebiger Text.
    Einige Listen sind bereits in Excel integriert. Zum Beispiel Wochentage oder Monate. Es lassen sich auch eigene Listen erstellen.
    • Menüband Datei : Excel-Optionen

    • Register Erweitert

    • Gruppe Allgemein (Achtung ist ganz unten).

    • Benutzerdefinierte Listen bearbeiten.

    • Neue Listeneinträge eingeben und auf Hinzufügen klicken.

      Alternativ kann mit Importieren eine Reihe aus der Excel-Tabelle übernommen werden.

    Benutzerdefinierte Listen

    Abb.: Benutzerdefinierte Listen erstellen.

    Beispiel mit Guanin, Adenin, Cytosin und Thymin erstellen.





    Tipp:
    Löschen mit AutoAusfüllen.
    Autoausfüllen lässt sich auch zum Löschen benutzen.
    Dazu die Liste markieren und mit dem Kreuz AutoAusfüllen unten rechts "zurückfahren".



    Kopieren

    Wie in allen Office Programmen

    Strg + C und Strg + V


    Ausschneiden

    Strg + X und Strg + V




    Blitzvorschau - Werte aus Zellen trennen und kombinieren ("Flash Fill" Strg + E)

    Ein neues Werkzeug seit Excel 2013. Das Werkzeug geht noch einen Schritt weiter als AutoAusfüllen.

    Es muss mindestens eine Musterlösung vorgegeben werden. Excel nimmt die Werte aus der linken Spalten, um die Spalte auszufüllen.

    In einem Beispiel werden mit Hilfe der Blitzvorschau Werte in einer Zelle getrennt (siehe Abbildung unten).

    In einer Liste sind Vor- und Nachnamen in einer Zelle geschrieben (Spalte J "dieter steinmetz".
    Es soll der Vorname groß geschrieben in der Zelle daneben ausgefüllt werden.
    Dazu wird in einer Zelle der Vorname geschrieben (in Spalte K "Dieter").
    Anschließend in Menüband : Daten : Blitzvorschau geklickt und die restlichen Zellen werden automatisch ausgefüllt.

    Hier wird das Leerzeichen als Trennzeichen erkannt.
    Es werden auch Punkt und andere Trennzeichen erkannt.

    Beachte, dass sogar die Großschreibung übernommen wird.

    Tastenkürzel ist Strg + E.


    Blitzvorschau - Trennzeichen erkennen
    Abb.: Blitzvorschau - Trennzeichen erkennen - Blitzvorschau wurde bereits gedrückt

    Achtung: Was passiert bei "Karl Heinz Rummenige"? Wie lässt sich das lösen?
    Siehe auch Textfunktionen.

    In dem Beispiel unten wird das Datum in amerikanischer Schreibweise als "Jahr Monat Tag" aus einer Liste vorgegeben.
    Also zum Beispiel 20140104. Wie kann das in einer Liste als 4.1.2014 umgewandelt werden?

    Hier müssen die ersten 2-3 Zellen ausgefüllt werden.
    Excel "lernt" dann den Zusammenhang und kann die restlichen Daten mit Blitzvorschau eingeben.

     Blitzvorschau - AutoAusfüllen mit Blitzvorschau
    Abb.: Blitzvorschau - AutoAusfülloption ausgeklappt mit aktivierter Blitzvorschau


    Tipp:
    Manchmal, wie in dem Beispiel oben, müssen zuerst mehrere Zellen richtig ausgefüllt werden, bevor Blitzvorschau angewendet werden kann.


    Noch ein Beispiel aus der Biologie. Eine Liste Arten von Drosophila (Wikipedia) soll umgeschrieben werden.
    Eine Zeile richtig schreiben und Blitzvorschau genügen, um die Liste umzuwandeln.

     Blitzvorschau - AutoAusfüllen mit Blitzvorschau

    Abb.: Blitzvorschau - Drosophila - AutoAusfüllen mit Blitzvorschau

    Oder mit Arabidopsis (Wikipedia)

     Blitzvorschau - AutoAusfüllen mit Blitzvorschau
    Abb.: Blitzvorschau - Arabidopsis - AutoAusfüllen mit Blitzvorschau





    Transponieren

    Mit Transponieren kann eine Tabelle um 90° gedreht werden!

    • Zellenbereich kopieren

    • Cursor an eine neue Stelle setzen.

    • Menüband Start Gruppe Zwischenablage Symbol Einfügen (Pfeilsymbol)

    • Symbol Transponieren (T).
      Oder Inhalte einfügen... und Transponieren wählen




    AutoEingabe Funktion

    Diese Funktion gibt es nur für Text in Spalten. Es dürfen keine leere Zeilen dazwischen sein.
    Excel errät automatisch nach den ersten Buchstaben aus der Liste darüber, welcher Eintrag kommen könnte.

    AutoEingabe

    Abb.: Die Namen werden nach dem ersten Anfangsbuchstaben ergänzt.


    Bitte an einem Beispiel ausprobieren.


    Auswahllisten

    Mit der Pfeiltaste kann aus einer Liste gewählt werden.
    geht nur mit Text.
    • Aktivieren der Auswahlliste mit Alt + Pfeil nach unten.
      Oder mit rechter Maustaste gibt es Dropdown-Auswahlliste.
      Es wird eine Liste der bereits eingegebenen Werte dieser Spalte gezeigt.

    Auswahlliste

    Abb.: Auswahlliste mit Alt + Pfeil nach unten aktivieren.


    Bitte an einem Beispiel ausprobieren.



    Kommentar eingeben

    Jede Zelle kann mit einem Kommentar hinterlegt werden.
    Das ist nützlich, wenn eine Tabelle von mehreren Mitarbeitern genutzt wird und mit Anleitungen versehen werden soll.
    Zellen mit Kommentare werden mit einer roten Ecke gekennzeichnet.

    • Menüband Überprüfen Gruppe Kommentare Symbol Kommentar einfügen..

      oder

    • Umschalt + F2

    Kommentar einfügen
    Abb.: Ein Kommentarfeld - Alle Kommentare anzeigen wurde aktiviert .



    Tipp:
    Einfügen einer Grafik in das Kommentarfeld

    • Rechte Maustaste auf die Zelle Kommentar formatieren
      Rechte Maustaste auf das Kommentarfeld Kommentar bearbeiten..

    • Register Farben und Linien

    • Ausfüllen - Farbe

    • Ausfüllen Farbe

    • Fülleffekte

    • Grafik auswählen

    Ein Kommentarfeld mit Bild
    Abb.: Ein Kommentarfeld mit Bild .





    Datenüberprüfung

    Die Datenüberprüfung dient zur kontrollierten Eingabe der Daten.
    Es kann das Format (Zahl, Text, Datum) bestimmt werden.
    Bestimmte Werte können vorgegeben werden.
    Bei falscher Eingabe kann ein Hinweis generiert werden.
    Schließlich kann noch ein eigener Hinweis zur Dateneingabe bestimmt werden.

    • Menüband Daten Gruppe Datentools Symbol Datenüberprüfung...





    • Register Einstellungen

      Datenüberprüfung
      Abb.: Gültigkeitsprüfung Werte einstellen

    • Register Eingabemeldung

    • Register Fehlermeldung




    Fehlermeldung

    Abb.: Datenüberprüfung Eingabemeldung und Fehlermeldung



    Hinweis:
    Wird bei der Fehlermeldung der Typ "Stopp" gestellt, kann kein falscher Wert eingegeben werden.
    Der Typ "Hinweis" und "Warnung" lassen nach einem Hinweis auch falsche Werte zu.




    Liste mit Dropdown

    Mit Hilfe der Datenüberprüfung können auch Werte mit einer Droppdown-Liste vorgegeben werden.


    Abb.: Dropdownliste zum Daten eingeben

    - Zellen markieren
    -Datenüberprüfung für markierte Zellen öffnen
    - Register Einstellungen
    - Dazu im Feld Zulassen auf Liste stellen.
    Jetzt in dem Feld Quelle die Liste mit Semikolon getrennt eingeben.
    Es kann auch eine Liste von der Excel-Tabelle übernommen werden.
    Zellendropdown ist sehr nützlich und zeigt in der Tabelle die Dropdownliste

    Datenüberprüfung - Liste eingeben
    Abb.: Datenüberprüfung - Liste eingeben - Trennzeichen ist Semikolon




    Rechtschreibprüfung

    Zum Überprüfen des Textes ist eine Rechtschreibprüfung eingebaut.
    • Menüband Überprüfen Gruppe Dokumentenprüfung Symbol Rechtschreibung

      Dokumentenprüfung


    • Wörterbuchsprache wird folgendermaßen eingestellt
      Menüband Datei : Excel-Optionen : Dokumentenprüfung : Wörterbuchsprache



    Blaupausen

    Es sollen Eintragungen gleichzeitig auf mehreren Tabellenblätter gemacht werden.
    Anwendung bei Messprotokolle, die auf mehreren Tabellenblättern liegen, oder Formulare.
    • Tabellenblätter im unterem Register mit der Taste Umschalt markieren.

    • Jetzt werden alle Eingaben in den markierten Tabellenblättern gemacht.

    • Wieder auf einen Tabellennamen im unteren Register klicken und die Markierung wird aufgehoben.

    Im Kontextmenü für Tabellenblätter gibt es auch ein Alle Tabellenblätter auswählen.
    Jetzt kann gleuchzeitig in alle Tabellenblätter gearbeitet werden.


    Daten löschen

    Es gibt verschiedene Arten Werte in einer Tabelle zu löschen.
    Die Unterschiede müssen bekannt sein.

    Der Inhalt einer Zelle kann einfach mit der Entf-Taste gelöscht werden.
    Auch einzelne Zeichen können nach dem Markieren mit der Maus in gewohnter Weise mit Entf-Taste gelöscht werden.

    Für ganze Zellen, Zeilen, Spalten und Blätter stehen Befehle und Dialogfelder zur Verfügung.


    Inhalte löschen

    In markierten Zellen kann alles, der Inhalt oder nur das Format gelöscht werden.
    Der gesamte Inhalt (Alle löschen) kann auch einfach mit der Entf Taste auf der Tastatur erreicht werden.
    • Zellen markieren

    • Menüleiste Start
      Gruppe Bearbeiten Symbol Löschen Pfeilsymbol

    • Alle löschen
      Formate löschen
      Inhalte Löschen
      Kommentare löschen
      Links löschen und Links enfernen


      Format löschen

      Abb.: Alles Löschen, Formate löschen,Inhalte löschen, Kommentare löschen.




    Zellen löschen

    Jetzt geht es um die ganze Zelle mit dem Inhalt.
    Da in der Exceltabelle kein "Loch" verbleiben kann, muss entschieden werden, wie die Lücke gefüllt wird.
    Wenn es für Excel eindeutig ist, wird das automatisch gemacht, sonst fragt Excel nach.

    • Zelle(n) markieren:

    • Menüband Start Gruppe Zellen Symbol Löschen..

      Zellen löschen

      Abb.: Zellen, Zeilen, Spalten und Blätter löschen.

      Oft ist die Situation nicht eindeutig. Wie soll die Lücke gefüllt werden?
      Excel fragt automatisch mit dem Dialogfeld Löschen nach.

      Dialogfeld Löschen


      Abb.: Dialogfeld Löschen.







    1.4 Format

    Der Inhalt einer Zelle kann als Zahl, Text oder Datum/Uhrzeit formatiert sein.
    Das ist entscheidend für das Verhalten in Formeln und Funktionen.

    Darüber hinaus kann das Aussehen durch Formatierung bestimmt werden.
    Durch Formatierung wird die Tabelle optisch gestaltet. Beispiele sind Schriftart, Stellenanzahl oder Ausrichtung ändern.
    Die wichtigsten Möglichkeiten der Formatierung sind in dem Menüband Start zusammengefasst.

    Mit dem Pfeilsymbol lässt sich das Dialogfeld Zellen formatieren öffnen, in dem alle Formatierungen vorgenommen werden können.


    • Menüband Start
      Gruppe Schriftart
      Gruppe Ausrichtung
      Gruppe Zahl
      Gruppe Formatvorlagen


      Format
      Abb.: Formatierungsmöglichkeiten in der Multifunktionsleiste. Zur Vergrösserung auf das Bild klicken.



    Zellenformat

    Das Format der Zelle oder Kategorie einer Zelle entscheidet darüber, wie der Inhalt bearbeitet werden kann.
    Ist der Inhalt eine Zahl, ein Text oder ein Datum?
    Excel versucht das Zahlenformat der Zelle bei der Eingabe zu erraten.
    An der Ausrichtung kann man erkennen, ob Excel richtig geraten hat.

    Zahlen und Datum werden rechts ausgerichtet. Text ist links ausgerichtet.

    Das Zahlenformat der Zelle kann nachträglich geändert werden.

    • Zelle(n) markieren

    • Menüband Start Gruppe Zahl
      In dem Feld Zahlenformat kann das geeignete Format gewählt werden.


      Alternativ kann das Zahlenformat in einem Dialogfeld eingestellt werden.
    • Klick auf das Pfeilsymbol unten rechts oder Strg + 1

    • Dialogfeld Zellen formatieren
      Register Zahlen



      Zellen formatieren
      Abb.: Dialogfenster Zellen formatieren Register Zahlen.


    • Gewünschte Kategorie einstellen.

    Beispiele mit :
    Zahl
    Währung
    Datum



    Benutzerdefinierte Formate

    Es ist auch möglich eigene Formate zu definieren.

    Beachte, es wird in der Zelle die richtige Zahl gespeichert und es wird damit auch gearbeitet.
    Die benutzerdefinierten Formate bestimmen nur die Ausgabe in der Tabelle.
    • Strg + 1

    • Register Zahlen

    • Listenfeld Kategorie Benutzerdefiniert

    • Typ:
      #.##0,00 "m³"
      Das Beispiel zeigt 2 Dezimalstellen und Tausendertrennzeichen, die Einheit m³ wird angehängt.
      Die Einheit muss in doppelten Anführungszeichen eingschlossen werden.
      Die hochgestellte Zahl 3 wird mit der Tastenkombination AltGr + 3 erzeugt.

      Weitere Beispiele
      #.##0,00;[Rot]-#.##0,00
      (Dezimalstellen 2 und negative Zahlen werden rot dargestellt)
      Für Farben gibt es
      [Schwarz], [Weiß], [Rot], [Grün], [Blau], [Gelb], [Cyan], [Magenta]
      und Farbcodes von 1 bis 56 aus der Excel Farbpalette, zum Beispiel [Farbe 42]

      Beispiele mit Bedingungen [wenn] dann;sonst
      [=1] 0 "Tag";0 "Tage"
      Wenn 1 eingegeben wird, kommt "1 Tag", sonst "...Tage"

      [<10] #.##0,00;"Größer oder gleich 10"
      Kleiner Zehn wird die Zahl ausgegeben und größer oder gleich Zehn der Text "Größer oder gleich 10"

      Was macht?
      [Grün][>0] ### "Euro";[Rot] ### "Miese"




    Schriftart

    Zuweisung direkt über Menüband Start Gruppe Schriftart oder...

    • Strg + 1

    • Register Schrift

    • Schriftart , -schnitt, -grad usw. zuweisen.

      Schriftart
      Abb.: Dialogfenster Zellen formatieren Register Schrift .



    Tipp:
    Für Tabellen sind serifenlose Schriftarten, wie Arial, Helvetica, Verdana geeignet.

    Nicht geeignet sind Schriftarten mit Serifen wie Times New Roman etc.



    Ausrichtung

    Text soll gedreht werden.

    • Strg + 1

    • Register Ausrichtung

      Ausrichtung
      Abb.: Dialogfenster Zellen formatieren Register Ausrichtung.



      Beispiel Ausrichtung
      Abb.: So sieht es dann aus: Ausrichtung 45 °



    In dem Fenster Zelle formatieren und Register Ausrichtung gibt es noch 3 Einstellungen zur Textsteuerung.

    Zeilenumbruch

    Der Text in der Zelle wird automatisch umgebrochen
    Eventuell ist die Zellenhöhe anzupassen.

    An Zellgröße anpassen

    Die Schriftgröße (Schriftgrad) wird so angepasst, dass der Text exakt in die Zelle passt.



    Mehrere Zellen miteinander verbinden

    Mit Menüband Start Gruppe Ausrichtung Symbol Verbinden und zentrieren.

    Zellen verbinden

    oder

    • Strg + 1

    • Register Ausrichtung

    • Textsteuerung Zellen Verbinden


    Hinweis: Zellen verbinden nicht in Tabellen mit Spalten- oder Zeilenberechnungen verwenden.
    Nur in Überschriften sinnvoll.



    Mehrere Zeilen in einer Zelle

    Innerhalb einer Zelle mehrere Zeilen mit Alt + Return.

    Bei Mac Alt + Apfel + Eingabe.

    Leider wird kein Zeichen für diesen Zeilenumbruch angezeigt..

    mehrere Zeilen in einer Zelle
    Abb.: Mehrere Zeilen in einer Zelle



    Einzug

    Der Text wird in der Zelle von links oder von rechts eingerückt.
    Mit linker Einzug können Unterpunkte in einer Tabelle kenntlich gemacht werden.
    Darüber hinaus kann auch Blocksatz eingestellt werden.

    • Strg + 1

    • Register Ausrichtung

    Linker Einzug

    Abb.: Einzug von Text



      Spaltenbreite

      Mit der linken Maustaste ziehen oder

      • Menüband Start
        Gruppe Zellen Symbol Format
        .

        Zellengröße

      • Spaltenbreite...
        Spaltenbreite automatisch anpassen
        Standardbreite

      Tipp: Wenn auf die Grenze der Spalten im Lineal doppelt geklickt wird, dann wird die optimale Breite eingestellt.



      Zeilenhöhe

      Mit der linken Maustaste ziehen oder

      • Menüband Start
        Gruppe Zellen Symbol Format

      • Zeilenhöhe...
        Zeitlenhöhe automatisch anpassen.

      Tipps:
      Wenn auf die Grenze der Spalten im Lineal doppelt geklickt wird, dann wird die optimale Höhe eingestellt.

      Text in der Mitte der Zeilen:

      • Strg + 1

      • Register Ausrichtung

      • Vertikal Zentrieren.



      Rahmen

      Das Gitternetz der Tabelle wird standardmäßig nicht ausgedruckt.
      Wenn daher die Tabellengrenzen im Druck sichtbar sein sollen, muss ein Rahmen eingefügt werden.
      • Zellbereich markieren

      • Menüband Start
        Gruppe Schriftart Symbol Rahmenlinie unten Pfeilsymbol

        Rahmen

        Abb.: Rahmen


        oder

      • Strg + 1

      • Register Rahmen

        Zellen formatieren - Rahmen

        Abb.: Zellen formatieren - Rahmen

        Zuerst unter Art: die Linienart aussuchen, eventuell noch die Farbe.
        Dann in die entsprechende Stelle in das Bild unter Rahmen: klicken.


      Hintergrund

      Die Tabelle kann mit einem Hintergrund versehen werden.
      • Zellbereich markieren

      • Menüband Start
        Gruppe Schriftart Symbol Füllfarbe


        oder

      • Strg + 1

      • Register Ausfüllen

        Huntergrund

        Abb.: Hintergrund



      Hintergrund für das Arbeitsblatt wird folgendermaßen eingerichtet:
      • Menüband Seitenlayout
        Gruppe Seite einrichten Symbol Hintergrund

      • Nach dem Einfügen des Hintergrundes gibt es an der Stelle ein Icon mit
        Hintergrund löschen



      Hinweis:
      Es gibt keine Möglichkeit eine Grafik als Tabellenhintergrund für eine einzelne Tabelle in einem Tabellenblatt einzufügen.
      Der Hintergrund wird immer für das gesamte Tabellenblatt gewählt.

      Der Hintergrund wird nicht ausgedruckt.

      Soll der Hintergrund ausgedruckt werden, zum Beispiel ein Wasserzeichen für die Tabelle, greift man zu folgendem Trick

      Der Hintergrund wird in die Kopfzeile eingefügt!
      Menüband Seitenlayout Gruppe Seite einrichten Symbol Seitenränder...
      Benutzerdefinierte Seitenränder
      Kopf und Fußzeile
      Benutzerdefinierte Kopf und Fußzeile
      Mittlerer Abschnitt
      Auf das Grafiksymbol klicken und die gewünschte Grafik wählen.

      Die Grafik ist nur in der Ansicht Seitenlayout, Seitenansicht und beim Drucken sichtbar.
      (Hallo Microsoft, umständlicher geht es nimmer?!!)

      Ich habe bisher keine Möglichkeit gefunden ausgewählten Zellbereichen mit einem Hintergrundbild zu versehen.




      Vorgenommene Formatierung auf andere Bereiche übertragen

      • Zellbereich mit der gewünschten Formatierung markieren.

      • Symbol Format übertragen.

      • Auf den Zielbereich klicken.

        Format übertragen

        Abb.: Formatpinsel arretieren.

      • Doppelklick auf den Formatpinsel arretiert dieses Werkzeug.
        So kann das Format in mehrere Felder übertragen werden.




      Formatierung löschen

      • Zellen markieren

      • Menüband Start
        Gruppe Bearbeiten Symbol Löschen Pfeilsymbol

      • Formate Löschen

        Format löschen

        Abb.: Formate löschen.




      Formatvorlagen

      Tabellenformatvorlagen

      Mit Hilfe eingebauter Tabellenformatvorlagen, kann das Aussehen einer Tabelle mit einem Klick geändert werden.
      Es können auch eigene Tabellenformatvorlagen erstellt werden.
      • Bereich für eine Tabellenformatierung markieren

      • Menüband Start
        Gruppe Formatvorlagen Symbol Als Tabelle formatieren


        Formatvorlagen

        Abb.: Tabellenformate.


      • Gewünschtes Format wählen.

      • Unter Neue Tabellenformatvorlage... kann eine neue Formatvorlage eingerichtet werden.


      • Nachdem eine Tabellenformatvorlage gewählt wurde, erscheint ein neues Menüband Tabellentools

        Tabellentools

        Abb.: Tabellenformate einrichten.

        Das Format der Tabelle kann bestimmt werden.
        Eine Überschriftzeile eingerichtet, Ergebniszeile hinzugefügt oder das Aussehen der Spalten geändert werden.



      Tipp:
      Wenn eine eigene Tabellen Formatvorlage erstellt werden soll,
      empfiehlt sich eine bestehende Formatvorlage mit Duplizieren... zu kopieren und die Vorlage anschließend zu ändern.
      Duplizieren findet sich im Kontextmenü, wenn auf eine gewünschte Tabellenformatvorlage geklickt wird.
      Die eigene Vorlage wird in der Vorlagenübersicht oben als Benutzerdefiniert geführt



      Zellenformatvorlagen

      Zellenformatvorlagen sind für einzelne Zellen und Zellbereiche gedacht.
      • Bereich markieren

      • Menüband Start
        Gruppe Formatvorlagen Symbol Zellenformatvorlagen


        Zellenformatvorlagen

        Abb.: Zellenformatvorlagen


      • Gewünschtes Format wählen.

      • Unter Neue Zellenformatvorlage... kann eine neue Formatvorlage eingerichtet werden.





      Design

      • Menüband Seitenlayout
        Gruppe Designs Symbol Designs


        Farben
        Schriftarten
        Effekte

      Das Aussehen des gesamten Arbeitsblattes wird geändert. Schriftart, Aussehen der Diagramme etc.





      1.5 Bedingte Formatierung

      Mit der bedingten Formatierung werden Zellen nach vorher bestimmten Kriterien abgesucht.
      Ist die Bedingung wahr, so wird die Zelle durch eine Formatierung kenntlich gemacht.
      Die Zelle bekommt einen farbigen Hintergrund oder die Zahl oder der Text kann durch Farbe hervorgehoben werden.
      Dadurch können gesuchte Bereiche einer Tabelle sichtbar gemacht werden.
      Die Formatierung ist dynamisch.
      Wird ein Wert in einer Zelle geändert und die Bedingung wird jetzt erfüllt, wird automatisch die Formatierung geändert.

      Seit Excel 2007 sind viele vorgefertigte bedingte Formatierungen in Excel vorhanden.
      Mit Hilfe von Formeln lassen sich weitere bedingte Formatierungen nach eigenen Vorstellungen entwickeln.



      Bedingte Formatierung einfügen

      • Menüband Start
        Gruppe Formatvorlagen Symbol Bedingte Formatierung...


        Bedingte Formatierung
        Abb.: Bedingte Formatierung - Beispiel Datenbalken.

      Beispieldatei


      Beispiele für Farbskala oder "Heat maps":
      fussballdaten.de -> Fertiges Beispiel
      Basketball NBA (Quelle www.databasesports.com) -> Fertiges Beispiel
      gen-alignments - Das ist ein bekanntes Beispiel für Heat maps. Es gibt Lösungen mit Python (Skript Python siehe Quelle) und R



      Für wissenschaftliche Anwendungen sind die Regeln zum Hervorheben von Zellen interessant.

      Regeln zum Hervorheben von Zellen
      Abb. Regeln zum Hervorheben von Zellen.



      In dieser Reihe können Werte herausgehoben werden die größer oder kleiner als ein gegebener Wert sind.
      Auch Werte in einem Bereich oder exakt gleiche Werte sind möglich.
      Auch für spezielle Fälle wie Textinhalt, Datum und doppelte Werte gibt es ein Fenster.

      Mit der Schaltflächen Neue Regel oder Weitere Regeln öffnet sich ein weiteres Fenster.

      Neue Formatierungsregel
      Abb. Neue Formatierungsregel.

      Hier lässt sich die bedingte Formatierung noch spezifischer einstellen.




      Wie finde ich eine bedingte Formatierungen in einem Tabellenblatt?

      Auf dem Tabellenblatt sind die Zellen mit bedingter Formatierung nicht gekennzeichnet.
      Außerdem können sich die Regeln für bedingte Formatierungen für die geichen Zellbereiche überlagern, was oft keine gute Idee ist.
      Daher ist der Manager für Regeln zur bedingten Formatierung eine große Hilfe.

      Ein Kontrolle in Regeln verwalten.. ist bei nicht funktionierenden bedingten Formatierungen sehr hilfreich.
      Dabei ist Formatierungsregeln anzeigen für: Dieses Arbeitsblatt einzustellen.

      Bedingte Formatierung verwalten

      • Menüband Start
        Gruppe Formatvorlagen Symbol Bedingte Formatierung..

      • Regeln verwalten...

        Bedingte Formatierung verwalten
        Abb.: Bedingte Formatierung verwalten.

        Formatierungsregeln anzeigen für:
        Dieses Arbeitsblatt
        Wenn die Regeln nur für den markierten Bereich angezeigt werden soll, dann
        Aktuelle Auswahl


        Neue Regel...
        Regel bearbeiten
        Regel löschen


        Das Kontrollkästchen "Anhalten" simuliert das Verhalten von früheren Versionen von Excel (<2007)
        und funktioniert nur in diesen alten Versionen.
        In den alten Versionen können nur 3 bedingte Formatierungen eingestellt werden.
        Nur erste Regel auswerten -> aktiviere das Kontrollkästchen Anhalten für die erste Regel.
        Erste und zweite Regel auswerten -> aktiviere das Kontrollkästchen Anhalten für die zweite Regel.
        Erste, zweite und dritte Regel auswerten -> aktiviere das Kontrollkästchen Anhalten für die dritte Regel.

        Das Kontrollkästchen Anhalten kann nicht aktiviert oder deaktiviert werden,
        wenn bei der Regel mit einem Datenbalken, einer Farbskala oder einem Symbolsatz formatiert wird.

        Hinweis:
        Überlagerung von mehreren Regeln funktioniert nur, wenn sich die Regeln nicht wiedersprechen!




      Eine Alternative zum Auffinden einer bedingten Formatierung stellt Gehe zu... dar.
      In früheren Versionen von Excel war dies die einzige Möglichkeit um bedingte Formatierungen in einem Tabellenblatt aufzuspüren.

      • Menüband Start
        Gruppe Bearbeiten Symbol Suchen und Auswählen : Gehe Zu
        oder Strg + G
        oder F5

        Gehe zu..



      • Dialogfenster Gehe Zu Schaltfläche Inhalte.

        Gehe zu..

      • Inhalte auswählen Bedingte Formate.

        Gehe zu - Bedingte Formate
        Abb.: Dialogfenster Gehe Zu Inhalte auswählen.


      • OK

      • Mit Tab kann zwischen den gefunden Zellen gesprungen werden.





      Bedingte Formate löschen

      Regeln lassen sich am einfachsten in dem Manager für Regeln zur bedingten Formatierung löschen. Siehe oben.

      Sind die Felder bekannt kann die bedingte Formatierung auch direkt gelöscht werden.
      Es können auch die Regeln in einem ganzen Arbeitsblatt gelöscht werden.
      • Zellen mit bedingter Formatierung markieren (Suchen siehe oben)

      • Menüband Start
        Gruppe Formatvorlagen Symbol Bedingte Formatierung..

        Bedingte Formatierung löschen
        Abb.: Bedingte Formatierung löschen - ausgewählte Bereiche oder im gesamten Blatt.


      • Regeln löschen...

      • Regeln in ausgewählten Zellen löschen

        oder

        Regeln in gesamtem Blatt löschen



      Eigene Formeln eingeben

      In Excel 2016 sind viele bedingte Formatierungen bereits vorbereitet.
      Es kann aber auch frei nach eigenen Wünschen eine bedingte Formatierung gestaltet werden.
      Durch Verwendung von Formeln und Funktionen sind die Möglichkeiten fast grenzenlos.

      • Zellbereich markieren

      • Menüband Start
        Gruppe Formatvorlagen Symbol Bedingte Formatierung..

      • Neue Regeln...

      • Formel zur Ermittlung der zu formatierenden Zellen verwenden.


        Bedingte Formatierung Formeln

        Abb.: Bedingte Formatierungen mit Formeln arbeiten.

        Beispieldatei


      • In dem Feld Werte formatieren, für die diese Formel wahr ist:
        die gewünschte Formel eingeben
        In dem Beispiel wird
        =$C4>50 eingegeben
        "C4" ist der Anfangswert, "$" bedeutet, dass auf die Spalte C absolut bezogen wird.
        Die Spalte C bleibt also fest, während die Zeilennummern entsprechend automatisch ersetzt werden
        (In diesem Beispiel würde es auch ohne $ gehen, aber wenn weitere Spalten markiert wurden, ist hier $ notwendig)

      • Schaltfläche Formatieren...

      • Gewünschte Formatierung eingeben.
        Hier z.B. die Zelle mit einer Farbe ausfüllen oder eine andere Schriftfarbe wählen.
        Die Formatierung wird gezeigt, wenn die Formel =$C4>50, =$C5>50, =$C6>50 usw. wahr ist.


      Beispiele

      1. Zelle in Abhängigkeit vom Wert Hintergrund farbig (Beispieldatei)

      Ein einfaches Beispiel. Wenn die Bedingung in einer Zelle erfüllt wird, wird die Zelle farbig markiert.
      • Spalte markieren

      • Menüband Start
        Gruppe Formatvorlagen Symbol Bedingte Formatierung..

      • Regeln zum Hervorheben von Zellen...

      • Gleich

        Einen Wert eingeben. In unserem Beispiel "n".

        Formatierung einstellen "hellroter Füllung 2".

      Beispiel

      Abb.: Farbiger Hintergrund der Zelle in Abhängigkeit vom Zellwert

      .

      2. Zeile in Abhängigkeit vom Wert Hintergrund farbig (Beispieldatei)

      Wenn eine ganze Zeile eingefärbt werden soll, wenn die Bedingung in einer Zelle erfüllt ist, muss mit einer Formel gearbeitet werden.

      Für die bedingte Formatierung alle 3 Spalten A, B und C markieren.
      Formel: =$C1="n"

      Beispiel

      Abb.: Farbiger Hintergrund der Zeile in Abhängigkeit vom Zellwert.

      Hinweis:
      Die Spalte C in der Formel muss eine absoluten Bezug aufweisen, daher $C.
      Sonst wird nur die Spalte A formatiert.



      3. Wochenenden aufspüren Beispieldatei

      In dem nächsten Beispiel kommt eine Funktion in der Formel zur Verwendung.
      Es können also sehr komplexe Ausdrücke für bedingte Formatierungen benutzt werden.

      In einem Kalender sollen die Wochenenden angezeigt werden.
      Wieder wird eine Formel zur Ermittlung der zu formatierenden Zellen eingesetzt.

      Es wird die Funktion WOCHENTAG() benutzt.
      Erstes Argument ist das zu bewertende Datum.
      Das zweite Argument ist die Einstellung, wann die Woche beginnt. Bei Argument -> 1 wird Sonntag zu 1,Montag zu 2 usw.

      =WOCHENTAG(A1;1)=1
      Engl Version =WEEKDAY(A1;1)=1

      Bedingte Formatierung - Sonntage markieren

      Abb.: Wochenenden werden farbig gekennzeichnet Sonntag grün.

      Aufgabe: Markiere die Samstage gleichzeitig rot



      Zur Kontrolle die Wochentage in der Spalte daneben angeben.

      • Spalte B markieren

      • Spalte A in Spalte B kopieren (=A1 und Strg +Return)

      • Strg + 1 : Zahlen

      • Benutzerdefiniert: TTTT
        Damit wird der Wochentag als Wort ausgegeben (Montag, Dienstag, ...).
        TTT gibt die Abkürzungen der Wochentage aus (Mo, Di, Mi,...).

        Wochentage einfügen
        Abb.: Einfügen der Wochetage.



      4. Ausreißer aufspüren Beispieldatei

      Wieder wird eine einfache Funktion eingesetzt.

      Alle Werte in einer Messreihe grösser oder kleiner 10% des Mittelwertes sollen farbig gekennzeichnet werden.

      Der Mittelwert befindet sich in Zelle C14.
      =MITTELWERT(C4:C13)

      Es wird die Funktion ODER() benutzt, da beide Fälle größer und kleiner berücksichtigt werden sollen.

      (Wert/Mittelwert)-1>10% rechnet die Abweichung in % aus.

      Formel: =ODER((C4/$C$14)-1>10%;(C4/$C$14)-1<-10%)
      Engl Version: =OR((C4/$C$14)-1>10%;(C4/$C$14)-1<-10%)

      10% in Excel ist das gleiche wie 0,10

      Alternative Schreibweise mit (100*Wert/Mittelwert)-100>10:

      =ODER((100*C4/$C$14)-100>10;(100*C4/$C$14)-100<-10)
      Engl Version: =OR((100*C4/$C$14)-100>10;(100*C4/$C$14)-100<-10)


      Ausreisser

      Abb.: Abweichungen hier mit +-5% werden farbig markiert.


      Hinweis:
      Es gibt eine eingebaute bedingte Formatierung, mit der alle Werte 1-3 Standardabweichungen über oder unter dem Mittelwert gekennzeichnet werden.

      • Obere/Untere Regel - Über dem Durchschnitt
      • Formatierung wählen.
      • Erneut die Regel bearbeiten mit Regel verwalten...
      • Regel bearbeiten...
      • Werte formatieren für die gilt..
        1 Std Abw über
        2 Std Abw über
        usw. wählen

        Abweichungen über 1 Std Abw über dem Durchschnitt werden farbig markiert.

        Abb.: Abweichungen über 1 Std Abw über dem Durchschnitt werden farbig markiert.





      5. Extremwerte (Max und Min ) auffinden Beispieldatei

      Es werden Maximalwert und Minimalwert in einer Liste gesucht.

      In das freie Feld neben Maximalwert folgende Formeleingeben =MAX(C4:C13)

      In das freie Feld neben Minimalwert folgende Formeleingeben =Min(C4:C13)

      In welchen Zellen kommen diese Werte vor?

      Bedingte Formatierung mit

      Formel ist =<Zelle Maxwert>=<erste Zelle markierter Bereich>

      Max und Min markieren

      Abb.: Min- und Max-Werte suchen.



      Aufgabe: Wie lässt sich das noch effektiver einrichten, ohne dass Min und Max in der Tabelle erscheinen?

      Versuche die Formel
      =$C4=MAX(C$4:C$13)
      =$C4=MIN(C$4:C$13)



      6. Doppelte Werte aufspüren Beispieldatei

      Es sollen doppelte Werte in einer Tabelle gefunden werden.

      In dem Beispiel findet sich eine Spalte mit gefundene Genen. Es soll herausgefunden werden, ob die Nummern in der Spalte Gene ID vorkommen.
      Kurz, ob es Übereinstimmungen zwischen den beiden Spalten gibt.

      Tabellenbereich markieren in dem doppelte Werte gefunden werden sollen.

      Bedingte Formatierung mit

      =ZÄHLENWENN($A$6:$B$198;A6)>1
      Engl. Version: =COUNTIF($A$6:$B$198;A6)>1

      Bereich sind die beiden Spalten

      Kriterium ist die Spalte GeneID

      Hinweis: die Zellen sind im Beispiel ab A6 zu markieren!
      Sonst muss die Formel entsprechend korrigiert werden.

      Doppelte Werte

      Abb.: Dubletten in einer Tabelle suchen.


      Aus der Excel-Hilfe:

      ZÄHLENWENN(Bereich; Kriterien)
      Zählt die Zellen die mit dem Suchkriterium übereistimmen.

      Bereich ist der Zellbereich, in dem ermittelt werden soll, wie viele seiner Zellen einen Inhalt haben,
      der mit den Suchkriterien übereinstimmt.
      Kriterien gibt die Kriterien in Form einer Zahl, eines Ausdrucks oder einer Zeichenfolge an.
      Diese Kriterien bestimmen, welche Zellen gezählt werden.
      Zum Beispiel kann ein Suchkriterium als 32, "32", ">32" oder "Äpfel" formuliert werden.

      In unserem Beispiel ist der Bereich der gesamte ausgewählte Bereich und das Kriterium die erste Zelle.
      Die Funktion vergleicht den Wert der ersten Zelle mit allen Werten in dem Bereich und
      merkt sich wie viele identische Werte gefunden werden.
      Anschließend wird die zweite Zelle ausgewertet und so weiter.


      Hinweis:
      Das geht noch einfacher und ohne eine Funktion eingeben zu müssen.

      • Bereich markieren in dem doppelte Werte gefunden werden sollen.

      • Menüband Start
        Gruppe Formatvorlagen Symbol Bedingte Formatierung..

      • Regeln zum Hervorheben von Zellen...

      • Doppelte Werte...




      7. Schattieren jeder zweiten Zeile in einem Excel-Arbeitsblatt

      Das ist ein Beispiel in dem ineinander verschachtelte Funktionen eingesetzt werden.

      Die Tabelle sollte so aussehen:

      Tabelle mit Streifen
      Abb.: Streifentabelle

      Microsoft macht in der online-Hilfe 2 Vorschläge

      http://office.microsoft.com/de-de/excel/HA011373371031.aspx?pid=CL100570551031

      Wie geht das mit der bedingten Formatierung mit Zeile und Rest?



      Eine Lösung mit
      =REST(ZEILE($A4);2)=0
      Engl Version: =MOD(CELL($A4);2)=0

      Die Funktion Zeile() gibt die Zeilennummer aus.
      Die Funktion Rest() teilt durch 2 und gibt den Rest aus.
      Falls dieser Rest 0 ist wird die Bedingung erfüllt.


      Hinweis
      Das war nur eine Übung. Natürlich geht so etwas am einfachsten mit geeigneten Tabellenformatvorlagen (s.o.)



      Sparklines

      In Excel 2010 wird ein neues Konzept vorgestellt, dass die Trends in einer Tabelle darstellen kann - Sparklines.
      Infos

      Ob sich diese Idee auch im wissenschaftlichen Alltag bewährt, bleibt abzuwarten.

      Sparlines - Linie
      Abb.: Sparklines - Linie

      Beispieldatei

      Sparklines - Linie erstellen

      • Zelle oder Zellbereich markieren in denen Sparklines angezeigt werden sollen

      • Menüband Einfügen : Gruppe Sparklines : Symbol Linie

      • Datenbereich eingeben.
        der Positionsbereich wurde bereits durch die Auswahl bestimmt

        Sparlines - erstellen
        Abb.: Sparklines - erstellen


      • Die Sparklines werden erstellt.



      Sparklines anpassen

      Wird in Zellen mit Sparklines geklickt, öffnet sich im Menüband ein neues Register Sparklinetools Entwurf.

      Sparlines - erstellen

      In Anzeige kann eingestellt werden, ob etwa Höchst und Tiefpunkte gezeigt werden.
      Markierung kennzeichnen die einzelnen Datenpunkte.

      Es sind bereits einige Formatvorlagen für Sparklines vorgegeben.

      Sparklinefarbe und Datenpunktfarbe lassen sich einstellen.





      1.6 Auswertung der Daten und Datentools

      In Excel können die Daten in der Tabelle übersichtlich dargestellt werden.
      Dabei helfen besondere Werkzeuge.
      Dadurch ist eine wesentlich bessere Auswertung möglich.

      Das Sortieren einer Liste ist eine einfache Möglichkeit eine Liste zu ordnen.

      Schon in den vorherigen Versionen konnte eine Tabelle nach bestimmten Kriterien gefiltert werden.
      Eine Liste kann auch gruppiert werden.

      Mit der Pivot-Tabelle können umfangreiche Tabellen übersichtlich dargestellt werden.
      Pivottabellen werden im Finanzbereich häufig eingesetzt. Auch für die Biologie sehe ich Einsatzmöglichkeiten.

      Die Datentools Zielwertsuche, Solver und Szenario gehen einen Schritt weiter.
      Sie gehören zu speziellen Tabellenwerkzeugen mit denen sich die Daten nach besonderen Gesichtspunkten auswerten lassen.



      Beispiel Chemikalienliste

      Die Liste umfasst ursprünglich 1056 Chemikalien!
      Wie kann schnell ein bestimmter Stoff gefunden werden?
      Wie trage ich schnell eine neue Chemikalie ein?

      Chemikalienliste

      Abb.: Beispiel Chemikalienliste.




      1.6.1. Übersichtliche Anordnung
      Daten sortieren

      Sortiere Tabellen helfen die gesuchten Daten zu finden. Die Tabelle wird übersichtlicher.

      Es kann auf mehreren Ebenen sortiert werden.

      Es kann nach Spalten oder nach Zeilen sortiert werden.

      Vorsicht: Bei einer zusammenhängenden Tabelle reicht ein Klick in die Tabelle.
      Die Tabelle muss vor dem Sortieren nicht markiert werden.
      Allerdings unterbrechen leere Spalten oder Zeilen die automatische Erkennung der Tabelle.
      Unzureichend markierte Tabelle vor einer Sortierung, wenn nicht alle Spalten markiert wurden, zerstört die Tabelle!



      Beispiel Chemikalienliste


      • Die Liste mit der Überschrift markieren
        Strg+ Umschalt + *
        Markiert einen zusammenhängenden Tabellenbereich.
        Wenn leere Spalten oder Zeilen dazwischen sind, mit der Maus markieren!

      • Menüband Daten
        Gruppe Sortieren und Filtern Symbol Sortieren

        Sortieren
        Abb.: Dialogfenster Sortieren.

      • Daten haben Überschrift aktivieren, wenn eine Überschriftenzeile vorhanden ist.
        Weiter gewünschte Einstellungen vornehmen.
        So kann auch nach Zellenfarbe sortiert werden.
        Wurde vorher mit bedingter Formatierung ein Zellsymbol eingefügt, so kann auch nach den Zellsymbolen sortiert werden.

      • Es können weitere Suchebenen hierarchisch eingefügt werden

      • Mit der Schaltfläche Optionen... kann nach Spalten oder Zeilen sortiert werden.
        Klein- und Großschreibung beachten kann eingestellt werden.



      Aufgabe: Sortiere die Daten nach Artikel. Welches Problem gibt es in unserem Beispiel beim Sortieren nach Artikel? Lösungsvorschläge?




      1.6.2 Filter

      Filter sind bei unübersichtlichen Tabellen ein extrem nützliches Werkzeug.
      Es kann schnell, nach bestimmten Kriterien, die gesuchten Zeilen dargestellt werden.

      Beispiel Chemikalienliste

      Filter einschalten

      • Maus innerhalb der Liste platzieren.

        Vorsicht: Bei einer zusammenhängenden Tabelle reicht ein Klick in die Tabelle.
        Leere Spalten oder Zeilen unterbrechen die automatische Erkennung der Tabelle
        Sicherheitshalber die Liste markieren
        (Strg+ Umschalt + * markiert eine zusammenhängende Tabelle)
        Mit Menüband Daten Gruppe Sortieren und Filtern Symbol Filtern : Erweitert... kann der Bereich überprüft werden.
        dazu den Eintrag in Listenbereich beachten

      • Menüband Start
        Gruppe Bearbeiten Symbol Sortieren und Filtern : Filtern

        oder Menüband Daten
        Gruppe Sortieren und Filtern Symbol Filtern

        Filter einschalten


      • Es erscheint in jeder Zelle der Überschrift ein Pfeilsymbol (Drop-down-Pfeil).



      Filter einschalten
      Abb.: Filter aktiviert.

      Jetzt kann z.B. nach Artikel einer Firma gefiltert werden.

      Auf den Drop-down-Pfeil klicken. Es kann eine Sortierung oder Filterung eingestellt werden.

      So ist mit Von A bis Z oder Von Z bis A auch eine schnelle Sortierung möglich.

      Zusätzlich kann mit Nach Farben sortieren... nach ungewöhnlichen Kriterien sortiert werden. Z.B. nach Farben und Symbolen.
      Das kann z.B. eingesetzt werden, indem die Zellen von bestimmte Einträgen vorher systematisch eingefärbt wurden (Etiketten) und dann gefiltert werden können.

      Dialogfeld Sortieren

      Abb.: Dialogfeld Sortieren.

      Anfangs ist Alles auswählen aktiv und es werden alle Felder angezeigt.
      Soll nach einem Kriterium gefiltert werden, ist (Alles auswählen) zu deaktivieren. Jetzt kann ein Kriterium gewählt werden.
      Das Pfeilsymbol in der obersten Zeile der Tabelle ändert sich und es wird angezeigt, dass in dieser Spalte gefiltert wird.




      Filterkriterien löschen

      Sollen alle Artikel wieder angezeigt werden, einfach in dem Drop-Down-Menü der gefilterten Spalte auf (Alle) klicken.

      Alternativ kann mit Filter löschen aus... die Auswahl wieder abgewählt werden.

      Außerdem befindet sich dafür ein Symbol Löschen in dem Menüband, das allerdings alle Filterkriterien löscht.



      Filter ausschalten

      Die Filterdarstellung kann über das Menüband Daten Gruppe Sortieren und Filtern Symbol Filtern ausgeschalten werden.
      oder
      Menüband Start Gruppe Bearbeiten Symbol Sortieren und Filtern : Filtern



      Mehrere Kriterien

      Sollen mehrer Filter gleichzeitig angewendet werden, so werden mehrere Kriterien gewählt.

      Zum Beispiel Filtern nach den Firmen Aldrich und Amersham.

      Nach verschiedenen Kriterien filtern

      Abb.: Nach verschiedenen Kriterien filtern.


      Aufgabe: Bitte die Liste nach den Firmen Sigma und Biorad filtern



      Textfilter
      Seit Excel 2010 gibt es einen Textfilter.
      In das darunterliegende Feld einen Suchbegriff eintippen
      und sofort werden die gesuchten Felder angezeigt und können gefiltert werden.

      Textfilter
      Abb.: Textfilter.



      Filterung in mehreren Spalten gleichzeitig
      Es können auch Zeilen gefiltert werden, wenn in mehreren Spalten ein Filter eingestellt wurde.
      Z.B.
      1. Filterung nach Firma (Sigma)
      2. Filterung nach Aufbewahrung (RT)




      Filtern mit ODER Verknüpfung
      Bisher wurden die Filter mit einer UND-Verknüpfung gefiltert. Siehe oben das Beispiel gleichzeitig soll das Kriterium Firma=Sigma und Aufbewahrung=RT erfüllt sein.

      Nun suchen wir alle Daten, die F=x ODER Xi=x erfüllen.

      Das wird mit einem Spezialfilter durchgeführt.

      Zuerst außerhalb der Tabelle die Werte in eine kleine Kriterientabelle eintragen. Siehe Abbildung unten.

      Xi F
      x
      x

      Darauf achten, dass bei ODER die Kriterien in verschiedenen Zeilen stehen.

      Spezialfilter öffnen

      • Menüleiste : Daten : Sortieren und Filtern : Erweitert

      Mit Spezialfilter eine ODER Verknüpfung einfüge
      Abb.: Mit Spezialfilter eine ODER Verknüpfung einfügen.

      Listenbereich: Der Bereich der Liste eintragen

      Kriterienbereich:Bereich der Tabelle für die Kriterien

      Nun wird die Tabelle mit Xi=x ODER F=x gefiltert.




      1.6.3 Liste gruppieren und auswerten

      Damit eine unübersichtliche Tabelle leichter lesbar wird, können Spalten oder Zeilen gruppiert werden.

      Beispieldatei Chemikalienliste Gruppierung

      Zeilen oder Spalten gruppieren

      Eine umfangreiche Tabelle wird übersichtlicher, wenn bei Bedarf Zeilen oder Spalten ausgeblendet werden.

      Ein umfangreiches Tabellenblatt mit vielen ausgefüllten Zellen und zahlreichen Formeln wird übersichtlicher, wenn Sie bei Bedarf weniger wichtige Zeilen oder Spalten schnell ausblenden können. Befinden sich in Ihrem Tabellenblatt Detail- und Zusammenfassungsdaten, können Sie mit der automatischen Gliederung Zeilen und Spalten verschiedenen Gliederungsebenen zuweisen und damit schnell aus- und wieder einblenden.

      • Spalten oder Zeilen, die zusammengefasst werden sollten markieren

      • Menüband Daten
        Gruppe Gliederung Symbol Gruppieren

      • Spalten oder Zeilen auswählen.


      Hinweis: Es können auch mehrere Gruppen verschachtelt werden.


      Gruppierung aufheben

      • Menüband Daten
        Gruppe Gliederung Symbol Gruppierung aufheben



      Vorsicht:
      Mit Gruppierung und Gruppierung aufheben wurden Tabellen schon zerstört.
      Also Originaltabelle vorher speichern.



      AutoGliederung

      Excel führt eine automatische Gliederung durch. Dabei sucht Excel Zeilen oder Spalten mit Aggregatfunktionen wie z. B. SUMME, MITTELWERT, MIN oder MAX.

      In dem Beispiel wurden solche Funktionen eingefügt.

      • In die Tabelle klicken.

      • Menüband Daten
        Gruppe Gliederung Symbol Gruppieren

      • Pfeilsymbol, dann AutoGliederung

      AutoGliederung entfernen mit
      Menüband Daten : Gruppierung aufheben : Gliederung entfernen



      Teilergebnisse darstellen

      Beispieldatei

      Spalten oder Zeilen können gruppiert werden und gleichzeitig ausgewertet werden.
      Z. B. kann die Anzahl der Artikel berechnet werden.

      • Die Liste markieren
        (Strg+ Umschalt + *)

      • Menüband Daten
        Gruppe Sortieren und Filtern Symbol Sortieren
        Die Tabelle z.B. nach "Firma" und anschließend nach "Artikel" sortieren.

      • Menüband Daten
        Gruppe Gliederung Symbol Teilergebnisse
        Teilergebnisse



        Teilergebnisse
        Abb.: Dialogfenster Teilergebnisse und eine gruppierte Tabelle. Die Anzahlder Einträge wird angegeben.

        Interessant für das Labor sind auch die folgenden Einstellung.
        Gruppieren nach: Firma
        Unter Verwendung von: Summe
        Teilergebnisse addieren zu: Preis
        Ergebnisse unterhalb der Daten anzeigen


      Die Teilergebnisse entfernen

      • Menüband Daten
        Gruppe Gliederung Symbol Teilergebnisse

      • Schaltfläche Alle entfernen




      2. Beispiel - Teilergebnisse

      Beispieldatei - Insekten

      Es soll die Anzahl der Proben pro Jahr und Standort angegeben werden

      - Tabellenblatt Daten zuerst nach Datum und Standort sortieren - Wichtig!

      - Teilergebnisse:
      Gruppiert nach: Standorte
      Unter Verwendung von: Summe
      Teilergebnisse addieren zu: Probe

      Beispiel Insekten - Teilergebnisse
      Abb.: Beispiel Insekten - Teilergebnisse.





      1.6.4. Pivot-Tabelle

      Die Pivot-Tabelle (von franz. (se) pivoter = (sich) drehen), auch Drehtabelle genannt, ist eine spezielle Tabellenart zur übersichtlichen Darstellung komplexer Datenbestände.

      Bei Pivot-Tabellen handelt es sich um besondere Ansichten der Daten einer Tabelle.
      Man kann Daten zusammenfassen, berechnen, verschieben oder filtern ohne dass der Datenbestand selber, also die Excel-Tabelle verändert wird.
      Es ist ein Hilfsmittel zur Analyse von Datenbeständen und darin relativ mächtig.

      Beispieldatei - Insekten - erfundenes Experiment

      Beispieldatei - Dittus - Mäuse Reiz und Trigger
      Ausgangstabelle ist "Ausgangstabelle"; die Tabelle ist für Pivot unbrauchbar
      Daher wird mit einem Makro eine Tabelle "Result" geschrieben,
      aus dieser Result Tabelle kann ein Pivot erstellt werden, in Datenblatt "Pivot"


      Pivottabelle

      Abb.: Eine Pivot-Tabelle.


      Anlegen einer Pivot-Tabelle.

      • Eine Tabelle soll mit einer Pivot-Tabelle ausgewertet werden. Beispieldatei. Dazu in die Tabelle klicken.

      • Menüband Einfügen
        Gruppe Tabellen Symbol PivotTable

        Pivottabelle einfügen


      • PivotTable

      • Der Assistent geht auf. Auf Standardeinstellungen belassen und die Tabelle in einem neuen Arbeitsblatt erstellen.
        Den Bereich kontrollieren. Wurde die gesamte Tabelle erfasst?

        Pivottabelle erstellen
        Abb.: Eine Pivot-Tabelle erstellen.


      • PivotTable-Felder: In der rechten Spalte wird eine Liste gezeigt.

      • PivotTable-Felder
        Abb.: PivotTable-Felder.


        Durch Ziehen mit der Maus kann die Position des Feldes in der Tabelle bestimmt werden.
        In unserem Beispiel soll zunächst "Standort" und "Datum" in Filter (bzw. Berichtsfilter).

        "Habitat" wird in Zeilen (Zeilenbeschriftung), verschoben.

        "Spezies" in Spalten (Spaltenbeschriftung) und schließlich "Anzahl" in Werte .
        Natürlich kann die Auswahl variiert werden. Bitte ausprobieren was am meisten Sinn macht.


      Hinweis:
      Wichtig ist es nach Änderung der Ausgangstabelle (Tabelle der Daten) die Pivot-Tabelle zu aktualisieren.
      Dazu mit rechter Maustaste in die Pivot-Tabelle klicken und Daten aktualisieren wählen.



      Pivot-Tabellen formatieren

      • Pivot-Tabelle auswählen.

      • Menüband Pivot-Table Tools : Entwurf
        Gruppen Layout oder Optionen für Pivot Table-Formate : Pivot Table Formate.


      • Felder erstellen
        Abb.: Eine Pivot-Tabelle formatieren.



      Diagramm erstellen aus einer Pivot-Tabelle

      • Pivot-Tabelle auswählen.

      • Menüband Pivot-Table Tools
        Gruppe Tools Symbol PivotChart.

      • Gewünschter Diagrammtyp auswählen.

      • Diagramm ändert sich mit der entsprechenden Auswahl in der Pivot-Tabelle!

      Diagramm
      Abb.: Eine Pivot-Tabelle mit Diagramm.

      Ändere die Pivot-Tabelle und beachte das Diagramm




      1.6.5. Power Query - Daten abfragen aus allen möglichen Datenquellen

      support.office.com/en-us/article/combine-data-from-multiple-data-sources-power-query

      Power Querry ist ein leistungsstarkes Werkzeug um Daten in Excel zu importieren.

      Dabei wird das Informatik Konzept ETL (Extract Transform Load Wikipedia ETL-Prozess ) umgesetzt.
      Im ersten Schritt werden die Daten aus unterschiedlichsten Quellen geholt (Extract).
      Das können andere Exceldateien sein, Datenbanken, Webseiten oder Ordner mit vielen einzelnen Dateien.
      Die Daten werden anschließend für die Auswertung aufbereitet (Transform)
      Änderung des Zahlenfomats, Berechnungen, eine Auswahl der Daten oder zusammenführen von Daten.
      im letzten Schritt werden die Daten in Excel geladen.

      Die Schritte werden in Excel als Abfrage festgehalten, so daß die Daten leicht aktualisiert und neu verwendet werden können.

      Eine großartige Einführung gibt es von Excelhero Daniel Kogan
      www.youtube.com/watch?v=hEI5xckBKs8




      1. Beispiel Daten aus vielen Textdateien in einem Ordner in Excel laden

      Excelhero

      Daten

      Exceldatei

      • Menüband Daten : Daten abrufen...
      • Aus Ordner
        den Ordner öffnen (im Beispiel oben "ExcelPowerQuest")
      • Auf das kleine Dreieck bei Kombinieren klicken
        "Kombinieren und transformieren" wählen
      • Die Tabelle wird geladen
      • Pivottabelle erstellen

      Ein anderes Beispiel mit komplexer Transformation (Power Query M Formeln):
      Daten

      Exceldatei



      2. Beispiel Tabelle aus dem Internet extrahieren

      Exceldatei

      Extrahiere die Tabelle aus de.wikipedia.org/wiki/Biozid
      und de.wikipedia.org/wiki/Pharmahersteller

      • Menüband Daten : Aus dem Web
      • Fenster öffnet sich "Aus dem Web"
        URL eingeben https://de.wikipedia.org/wiki/Biozid
      • Tabelle auswählen
      • Laden


      3. Beispiel Tabellen zusammenfügen

      description.xlsx
      genes.xlsx

      In Excel aus dem Office Paket 365 und Office 2019 ist ein interessantes Werkzeug hinzugekommen.
      Mit Power Query können komplexe Abfragen erstellt werden.
      Es sollen 2 Tabellen aus einem A. thaliana RNA-Seq Experiment (C. Chaban, ZMBP) kombiniert werden.
      Allerdings nicht einfach wie mit kopieren und einfügen.
      Es gibt eine Index Spalte, die die Werte aufeinander bezieht.
      In der ersten Tabelle ist das die Spalte target und in der zweiten Tabelle die Spalte Locus Identifier.

      Tabelle 1 Gene, Messwerte - genes.xlsx

      Tabelle 1

      Tabelle 2 Gene - description.xlsx, Beschreibung aus TAIR

      Tabelle 2

      Tabelle 3 Zusammengeführte Tabellen, das Ergebnis

      Das Ergebnis

      Die neue Tabelle enthält nun die Spalten adj.pval aus der ersten Tabelle
      und die Spalten gene-description Column 2 und gene descrition column 3 aus der zweiten Tabelle.
      Bezugsspalte ist target.

      Die Lösung hätte man auch mit einer Funktion, zum Beispiel SVERWEIS (VLookup), durchführen können.
      Aber mit Abfragen (Power Query) ist das einfacher.

      Tabellen zusammenführen mit PowerQuest - Anleitung

      1. Tabelle 2 als Verbindung speichern
      Zuerst muss eine Verbindung zur 2. Tabellen erstellt werden.

      • Tabelle 1 in Excel öffnen.
        Exceldatei Gene, Messwerte genes.xlsx

        Menü Daten
        Daten abrufen
        Aus Datei
        Aus Arbeitsmappe

        Verbindung erstellen


      • Die Datei mit den Gen Beschreibungen auswählen - description.xlsx .
        Importieren
        Ein Fenster mit dem Navigator öffnet sich.
        Das Arbeitsblatt mit der Tabelle auswählen.

        Navigator


      • Schaltfläche Laden auf kleinen Pfeil daneben klicken. Laden in.. drücken.
        Neues Fenster geht auf und dort Nur Verbindung erstellen wählen.

        Nur Verbindung erstellen

      • Nun hat sich Excel diese Verbindung gemerkt.
        Es steht nun für die Abfrage zur Verfügung. Die Abfragen und Verbindungen können in der Arbeitsfläche angezeigt werden.

        Abfragen und Verbindungen





      2. Die Abfrage Kombination erstellen

      Nun können die beiden Tabellen kombiniert werden. In die Tabelle 1 mit den Meßwerten klicken und die Abfrage starten.

      • In Tabelle 1 die Daten markieren
        Menüleiste Daten - Aus Tabelle/Bereich

        Aus Tabelle / Bereich

        Der Power-Query Editor öffnet sich.


        Power-Query Editor

      • Die Schaltfläche Kombinieren (Merge) drücken und Abfragen zusammenführen starten.
        Das Fenster Zusammenführen öffnet sich

        Zusammenführen

        Die Verbindung wählen, im Beispiel gene-description.
        Die Spalten, die sich aufeinander beziehen, markieren
        Join-Art: Es wird der Linker äußerer Join gewählt. Alle Zeilen aus der ersten Tabelle (Links), in unserem Fall genes.xlsx, werden verwendet
        Mit OK abschließen.

      • Eine neue Spalte wird eingefügt.
        Kombination - neue Spalte

        Wir wollen nun diese Spalte erweitern. Dazu auf die kleinen Pfeile klicken (siehe oben)

        Diagramm

        Die erste Spalte wird nicht benötigt. Das war ja der identische Index. Daher nur die 2. und 3. Spalte wählen.
        Die ursprünglichen Spaltennamen sollen verwendet werden:
        Mit OK wird die Spalte erweitert.

        Spalte erweitern



      • Mit Schließen und Laden und Schließen und Laden als neues Arbeitsblatt wird die Tabelle gespeichert


        Die neue Tabelle - Kombination aus Tabelle 1 und 2

        Lösung PowerQuerry und Matrixfunktion - KombinierteTabelleMitVerweis.xlsx




      Power Pivot für 'Big Data'

      Excel Pivot wurde bereits vorgestellt. Warum ein weiteres Pivot?

      Der Hauptvorteil von Power Pivot ist, dass Excel damit sehr schnell mit großen Datenmengen umgehen kann, daher Power im Namen.
      Große Datenmengen und schnelle Berechnungen sind ein großer Schwachpunkt von Excel.

      Power Pivot ist ein Add-In und muss aktiviert werden.
      Excel : Start : Optionen : Add-Ins : Verwalten : COM-Add-Ins dort Power Pivot auswählen.

      Ein neues Menüband Power Pivot erscheint.

      Power Pivot kann Tabellen in Beziehung bringen. Dazu müssen die Tabellen zu dem Datenmodell hinzugefügt werden.
      Ein Datenmodell ist schlicht eine Sammlung von Tabellen, die eine Beziehung zueinander haben.
      Die Beziehungen werden mit Entwurf: Beziehungen hinzufügen erstellt.

      Die Daten können im Gegensatz zu Power Query nicht in Excel geladen werden.

      Die Auswertung erfolgt über Pivot im Datenmodell.

      Infos

      Microsoft Erste Schritte mit Power Pivot

      Tutorials

      Guter Einstieg, aktuell: Kevin Stratvert How to use Power Pivot 2021 (in Englisch)

      Sehr ausführlich mehrere Teile: Andreas Thehos Excel Power Pivot Erste Schritte 2013





      1.6.6 Textkonvertierungs-Assistent
      Import von Daten mit "." als Dezimalzeichen

      US-amerikanische Exceldateien (.xlsx) mit Dezimalzahlen und Punkt als Dezimalzeichen werden automatisch auch von deutschen Excel richtig erkannt und mit Komma als Dezimalzeichen geöffnet.

      Beispiel US Exceldatei

      Von vielen Analysegeräten werden Textdateien mit Zahlen ausgegeben, die statt des Dezimalzeichens Komma einen Punkt aufweisen.
      3.24 statt 3,24.
      Auch für Dateien, die mit einem Programm erstellt wurden (zum Beispiel Python, R) trifft dies zu.
      Die Textdateien haben sehr oft das Dateiformat .csv (comma separated values) oder .tsv (tab separated values).
      Diese Dateien können direkt mit Excel geöffnet werden.
      Excel erkennt automatisch beim Öffnen den Inhalt zunächst als Text und versucht das Zahlenformat zu erraten.
      3.24 ist für Excel ein Text und damit kann nicht gerechnet werden. Manchmal wird auch automatisch ein Datum erkannt. Aus 1.12 wird 1. Dezember.
      Wie kann das korrekt in Excel importiert werden? Wie kann ein korektes Zahlenformat eingestellt werden?

      Eine Lösung wurde bereits oben mit Power-Query vorgestellt. Allerdings ist das sehr komplex. Es gibt eine einfachere Lösung.

      Beispiel Textdatei

      • Die Textdatei in einem Texteditor Programm öffnen. Dazu kann das Windowsprogramm "Editor" oder noch besser das kostenlose Notepad++ verwendet werden.
        Die Tabelle markieren und kopieren.

      • In Excel in Menüband Start : Zwischenablage : Einfügen auf den kleinen Pfeil klicken


        Externe Daten
        Abb.: Kopierter Text mit Textkonvetierungs-Assistent öffnen.

      • Textkonvertierungsassistent startet

      • Schritt 3 von 3 Weitere...
        Weitere Textimporteinstellungen
        Dezimaltrennzeichen einstellen

        Externe Daten
        Abb.: Textkonvertierungs - Assistent



      Daten abrufen

      Die Dateien können auch mit Menüband Daten : Daten abrufen in Excel importiert werden.

      In Office 2019 und früher startete der Textkonvertierungs-Assistent mit Externe Daten Abrufen
      In späteren Versionen kann das in den Optionen nachträglich aktiviert werden.
      In den Excel Optionen unter Daten : Legacy-Datenimport-Assistenten anzeigen aktiviert werden.

      Externe Daten
      Abb.: Legacy-Datenimport-Assistenen aktivieren


      • Nach aktivierung des Legacy-Datenimport-Assistenten
        Menüband Daten
        Symbol Daten abrufen.

      • Office 2019: Aus Text
        Office 2021 und Microsoft 365: Legacy Assistenten : Aus Text (Legacy)

        Externe Daten
        Abb.: Excel 2021 und Microsoft 365: Aus Text (Legacy). Der Textkonvertierungsassistent startet.


        Externe Daten
        Abb.: Textdatei in Excel öffnen Excel 2019 - Externe Daten abrufen - aus Text

      • Der Textkonvertierungs-Assistent startet. Weiter wie oben.



      Hinweis:
      Eine andere Möglichkeit ist Excel zeitweise auf Punkt als Dezimalzeichen umzustellen
      Excel-Optionen : Erweitert : Optionen bearbeiten : Trennzeichen vom Betriebssystem übernehmen
      deaktivieren
      Jetzt den Punkt als Trennzeichen eingeben.
      Ist aber nicht sehr elegant!

      Tabellen können auch mit Hilfe von Funktionen bearbeitet werden. Ein Beispiel wird später bei Textfunktionen behandelt.


      Übung
      Beispieldatei
      Importiere die Daten aus einem Mikroplattenleser in Excel.
      Beachte die Software gibt die Werte der ersten Spalten in amerikanischer Schreibweise aus.
      Es wird ein Punkt statt einem Komma verwendet.




      1.6.7. Zielwertsuche

      Was-wäre-wenn-Analyse-Tool.

      Die Zielwertsuche ist ein Werkzeug, das hilft die Daten auszuwerten.

      Wenn das gewünschte Ergebnis einer einzelnen Formel, nicht aber der für die Formel benötigte Wert bekannt ist, wird die Zielwertsuche-Funktion verwendet.

      • Menüband Daten
        Gruppe Datentools Symbol Was-wäre-wenn-Analyse.

      • Zielwertsuche..


        Zielwertsuche

      Bei der Zielwertsuche variiert Microsoft Excel den Wert in einer bestimmten Zelle,
      bis eine sich auf diese Zelle beziehende Formel das gewünschte Ergebnis zurückgibt.

      Beispiel

      Es soll der "Ausreißer" solange variiert werden bis eine Standardabweichung von 10 erzielt wird.
      Kann eine Standardabweichung von 8 erreicht werden?

      Zielwertsuche
      Abb.: Zielwertsuche.




      1.6.8. Solver

      Solver ist ein Excel-Werkzeug, das die Daten analysiert und nach vorgegebenen Kriterien eine optimale Lösung findet.
      Solver ist ein sehr komplexes Programm und wurde von der Firma Solver.com entwickelt.

      Es wird lineare oder nicht-linear Programmierung für optimierte Lösungen eingesetzt.
      Eine optimierte Lösung wird definiert als minimales oder maximales Ergebnis, das eine Funktion mit allen vorgegebenen Kriterien bestmöglichst löst.

      Infos:

      http://ddl.me.cmu.edu/ddwiki/index.php/Excel_Solver

      http://office.microsoft.com/en-us/excel/HA011245951033.aspx

      Das Solver-Add-In muss installiert sein.

      • Menüband Datei : Excel-Optionen

      • Register Add-Ins

        Add-Ins

        Abb.: Excel Add-Ins - Solver ist installiert..


        In der oberen Liste werden die installierten Add-in gezeigt.
        Falls das Add-In Solver noch nicht installiert ist...

      • Button Gehe zu...

        Add-Ins


      • Solver aktivieren

        Hinweis: Eventuell muss das Add-In Solver von der Installations-CD installiert werden.






      Beispiel Michaelis-Menten Kinetik mit der Zielwertsuche Kurve finden

      • Menüband Daten
        Gruppe Analyse Symbol Solver.

      Beispiel
      Infos Michael-Menten Enzym Kinetik Wiki

      solver

      Abb.: Michaelis-Menten Modellierung mit Hilfe von Solver.

      • In B23 und B24 für A und B Anfangswerte eintragen
        Zum Beispiel 1 und 1.
      • Zelle D18 markieren und Solver starten.
      • Solver Parameter:
        Ziel festlegen: $D$18
        Min
        Durch Ändern von Variablenzellen: $B$23:$B$24



      1.6.9. Szenario

      Was-wäre-wenn-Analyse-Tool.

      Ein Szenario ist eine Menge von Werten, die Microsoft Excel speichert und in Ihrem Arbeitsblatt automatisch ersetzen kann.
      Szenarien können verwendet werden, um das Ergebnis eines Arbeitsblattmodells zu prognostizieren.

      • Menüband Daten
        Gruppe Datentools Symbol Was-wäre-wenn-Analyse.

        Zielwertsuche
      • Szenario-Manager..

        Szenarien

      Beispiel

      Erstelle 3 Szenarien indem die Messwerte variieren und eine unterschiedliche Standardabweichung ergeben.

      Szenario Bericht erstellen

      • Szenario-Manager Schaltfläche Zusammenfassung...

        Szenariobericht

      • Das Ergebnis:

      Szenariobericht
      Abb.: Szenario Bericht.




      1.6.10. Datentabelle

      Was-wäre-wenn-Analyse-Tool.

      Wie bei Szenarien werden auch bei Datentabellen eine Reihe möglicher Ergebnisse untersucht.
      Im Gegensatz zu Szenarien werden in Datentabellen jedoch alle Ergebnisse in einer Tabelle auf einem Arbeitsblatt angezeigt.

      Es können maximal 2 Variablen angegeben werde. Bei mehr Variablen sind Szenarien zu verwenden.

      Eine Datentabelle ist ein Zellbereich, der anzeigt, wie durch das Ändern von ein oder zwei Variablen in Formeln das Ergebnis dieser Formeln beeinflusst wird.
      Datentabellen stellen ein schnelles Verfahren zum Berechnen mehrerer Ergebnisse in einer Operation dar,
      und sie bieten die Möglichkeit, alle Ergebnisse der verschiedenen Varianten zusammen auf dem Arbeitsblatt anzuzeigen und zu vergleichen.



      Beispiel pH

      Datentabelle mit einer Variablen - Beispiel pH

      1. Eingabefeld mit einer Variablen B7
      2. Formel D6
      3. Formeln und Werte markieren C6:D16
      4. Menüband Daten Gruppe Datentools Symbol Was-wäre-wenn und Datentabelle
      5. Werte aus Spalte: B7
      6. Werte in D7:D16 werden automatisch eingefügt



      Beispiel Kreditzinsen (Beispiel Excel Hilfe)

      Anleitung Video






      1.7 Tipps

      1.7.1 Wiederholungszeile oder -spalte beim Drucken und in der Ansicht einstellen

      • Menüband : Seitenlayout
      • Gruppe Seite einrichten
      • Symbol Drucktitel
      • Register Blatt
      • Drucktitel
        Wiederholungszeile oben:
      • Zeilennummer eintippen ($1:$2  z.B. für die ersten beiden Zeilen) oder rechts auf das Icon klicken und die Zeilen markieren


        Hinweis: In der Ansicht Seitenlayout wird die Wiederholungszeile oder -spalte auch am Computer angezeigt.





      1.7.2 Duplikate entfernen

      Duplikate in einer Liste sind ziemlich lästig.
      In Excel 2007 ist ein neues Werkzeug integriert, das hilft Duplikate in einer Liste aufzuspüren.



      • Tabelle markieren

      • Menüband Daten Gruppe Datentools Symbol Duplikate entfernen


        Dialogfeld Duplikate entfernen.
        Abb.: Dialogfeld Duplikate entfernen.


        Spalten auswählen nach denen die Duplikate beurteilt werden sollen.






      Dieter Steinmetz, Universität Tübingen, ZMBP - Kursübersicht - Im Skript suchen