ZMBP

Wissenschaftlich Arbeiten mit Computer und Internet
- Tabellenkalkulation

Uni Tuebingen

Kursübersicht

Suchen

Version Office 2021



Wissenschaftlich Arbeiten mit Microsoft Excel

2. Tabellenfunktionen

Übungsaufgaben

Beispieldatei_06_auseisser.xlsx
Beispiel_10_und.xlsx

Beispiel_15_zaehlewenn-einfach.xls -> Beispiel_15 - Lösung

Beispiel _Matrixformel1
Beispiel _Matrixformel2 Trendberechnung - Funktion Trend

Beispiel 11_verweis01.xlsx
Beispiel_12_verweis_telefon_Lösung

Mit Excelfunktionen können komplexe Aufgaben leicht bewältigt werden. Dazu werden keine Programmierkenntnisse benötigt!.
Das macht Excel zu einem wichtigen Werkzeug um in der Biologie Daten zu analysieren.

Tabellenfunktionen sind vordefinierte Formeln.
Sie führen Berechnungen unter Verwendung bestimmter Werte, bezeichnet als Argumente,
und in einer bestimmten Reihenfolge aus.

Es gibt nur eine begrenzte Anzahl von Tabellenfunktionen,
aber es ist möglich die Funktionen miteinander zu kombinieren. Das ermöglicht eine große Vielfalt.

Außerdem können eigenen Funktionen erstellt werden.

Daher sind Tabellenfunktionen ein mächtiges Werkzeug in Excel.


2.1 Grundlagen

In Excel sind über 470 Funktionen eingebaut.
Übersicht Funktionen nach Kategorien von Microsoft.
Übersicht Funktionen alphabetisch von Microsoft
Die Funktionen werden ständig verbessert und mit jeder neuen Version kommen ein paar neue hinzu.

Abonnenten von Microsoft 365 bekommen die aktuellsten Funktionen zuerst.
Office 2021 Besitzer müssen bis zum nächsten Update warten (Neue Funktionen in Excel ).


Struktur einer Funktion

Die Struktur einer Funktion beginnt mit einem Gleichheitszeichen (=) gefolgt von dem Funktionsnamen,
einer öffnenden Klammer, den durch Semikola getrennten Argumenten der Funktion und einer schließenden Klammer.

Groß- und Kleinschreibung im Funktionsnamen wird nicht beachtet.
Es ist aber übersichtlicher den Funktionsnamen in Großbuchstaben zu setzen.
Leerzeichen innerhalb der Klammer werden ignoriert.

Beispiele

=MAX(Zahl1;Zahl2;...)
=SUMME(Zahl1;Zahl2;...)




Argumente

Argumente können Zahlen, Text, logische Werte wie WAHR oder FALSCH, Matrizen, Fehlerwerte wie #NV oder Zellbezüge sein.
*Fehlerwert #NV = no value.

Argumente werden in einem deutschen Excel mit Semikolon ( ; ) getrennt. Im englischen Excel geht auch Komma (,).

Das Argument muss in einen für dieses Argument gültigen Wert und daher im richtigen Format sein.
Es ist also zum Beispiel kein Text möglich, wenn eine Zahl erwartet wird.

Argumente können auch Formeln oder andere Funktionen sein.
Dadurch können Funktionen in Funktionen verschachtelt werden.

Beispiel einer verschachtelten Funktion
=WENN(ISTFEHL(FINDEN(ZEICHEN(10);B4));B4;LINKS(B4;FINDEN(ZEICHEN(10);B4)-1))
(Erklärung: In einer Zelle sind zwei Nummern mit einem neue Zeile-Zeichen (ZEICHEN(10)) getrennt. Die Formel gibt die erste Nummer aus. Siehe Beispiel)
Damit kann in Excel gezaubert werden und die Lösung von komplexen Aufgaben sind möglich.


Argumente sind meist zwingend, können aber auch optional sein.
Wird kein optionales Argument eingegeben, dann wird das Argument durch einen definierten Standardwert ersetzt.

Text muss in doppelten Anführungszeichen " gesetzt werden.
Einfache Anführungszeichen gehen nicht.
=LINKS("Test";1) Das Argument ist "Test". Das erste Zeichen wird gesucht und diese Funktion gibt T aus.


Zellbezüge als Argumente:
Zellbezüge können eingetippt weren (zum Beispiel B1).
Einfacher ist es mit der Maus auf die Zelle zu klicken oder den Zellbereich zu markieren.
Achtung die Zellbezüge sind standardmäßig relative Bezüge (zum Beispiel B1).
Werden absolute Bezüge benötigt ist davor ein "$"-Zeichen zu setzen (zum Beispiel $B$1).
Der Unterschied absoluter und relativer Bezug ist im Kapitel Zellbezüge behandelt worden.



Es gibt auch argumentlose Funktionen. z.B.
=HEUTE()
=JETZT()
=PI()



Landesprache

Leider werden in MS Excel die Funktionsnamen in der jeweilige Landessprache eingesetzt.
Alpabethische Liste der Funktionen auf Englisch
So heißt es in der deutschen Version =MITTELWERT() in der englischen Version =AVERAGE() oder Niederländisch =GEMIDDELDE().
Zum Glück werden die Funktionen automatisch in die jeweilige Version übersetzt, wenn die Datei in einer anderen Excel Sprachversion geöffnet wird.
Eine Liste der deutsch-englischen Übersetzungen gibt es hier
excelnova.org/excel-ressourcen/excel-formeln-ubersetzt-englisch-deutsch/
Andere Sprachen
de.excel-translator.de/
In englischen-amerikanischen Excelversionen werden die Argumente mit einem Komma abgetrennt.
In deutschen Excel wird Semikolon verwendet. Das ist sinnvoll, da hier das Komma als Dezimalzeichen verwendet wird.

Hinweis: Auch in englisch-amerikanischen Sprachversionen kann ein Semikolon eingestellt werden.
Dazu in Windows : "Control Settings : Region and Language : Advanced settings : List separator" auf Semikolon stellen.


Grenzen der Tabellenfunktionen

Die Tabellenfunktionen haben ihre Grenzen. Einen Überblick dazu liefert Philipp von Wartburg

Microsoft Excel Spezifikationen und Beschränkungen

Es sind 8.192 Zeichen für Formeln erlaubt. Und es sind 64 Ebenen in verschachtelten Formeln zugelassen.
Das ist eine ganze Menge. Auf eine Word-Seite, DINA4, Schriftgrad 10 passen etwa 4.500 Zeichen.




Übersicht aller Funktionen

- In Excel gibt es eine Übersicht aller Funktionen

  • Menüband Formeln Gruppe Funktionsbibliothek


- Liste mit Tabellenfunktionen Hilfe von Microsoft online




Excel teilt sehr übersichtlich die Funktionen in folgenden Themenbereiche ein
Übersicht Funktionen nach Kategorien von Microsoft.

  • Finanzmathematik
  • Datum & Zeit
  • Mathematik & Trigonometrie
  • Statistik
  • Nachschlagen und Verweisen
  • Datenbank
  • Text
  • Logik
  • Informationen
  • Konstruktion
  • Cube, neu ab 2007 ("Datenwürfel", Datenbanksystem siehe OLAP, Infos Cube, Video)
  • Kompatibilität (nicht mehr gültige Funktionen, die aber aus Kompatibilitätsgründen weiterhin funktionieren)
  • Web




Eine Funktion einfügen

Um eine Übersicht der verfügbaren Funktionen zu erhalten, kann das Menüband Formeln verwendet werden.
Dort werden die wichtigsten Gruppen dargestellt. Ein Klick auf das jeweilige Symbol öffnet eine Dropdown-Liste.

Besser ist es das Fenster Funktion einfügen zu öffnen.
Es wird ein Assistent ausgeführt, der bei der Erstellung der Funktion hilft.

  • Menüband Formeln Gruppe Funktionsbibliothek Symbol fx Funktion einfügen.

    Funktionsbibliothek


  • Oder drücke auf das Symbol fx in der Bearbeitungsleiste.

Funktion einfügen
Abb.: Symbol fx in der Bearbeitungsleiste - Funktion einfügen.


  • Oder UMSCHALT+F3
    (auch Mac)


Hinweise:

Wenn eine Funktion in die Formel eingeben wird, wird im Dialogfeld Funktion einfügen der Name
der Funktion, jedes der zugehörigen Argumente, eine Beschreibung der Funktion und der Argumente,
das aktuelle Ergebnis der Funktion und das aktuelle Ergebnis der gesamten Formel angezeigt.

Unter "Hilfe für diese Funktion" wird eine ausführliche Anleitung für diese Funktion gegeben.
Hier ist die Hilfe von Excel sehr hilfreich.



Eingabe mit der Tastatur - Quickinfo

Während die Funktion mit der Tastatur eingegeben wird, wird eine QuickInfo mit der Syntax und den Argumenten eingeblendet.
Wenn beispielsweise "=MITTELWERT(" eingeben wird, wird ein QuickInfo wie in der Abbildung angezeigt.

Quick Info

Abb.: Quickinfo.


Hinweise:
QuickInfos werden nur für integrierte Funktionen angezeigt.
Es kann auch eine eigene Funktion definiert werden, dann wird leider kein Quickinfo angezeigt.




Beispiel

Aufgabe
Suche den Maximal- und Minimalwert sowie Mittelwert.
Suche Informationen über diese Funktionen.



Tipp:
In der Bearbeitungsleiste können ganz lange Formeln mit ALT + Eingabe umgebrochen werden.
Das Fenster kann größer gezogen werden. Es kann, wenn das Fenster zu klein ist, über Pfeiltasten rechts in der Bearbeitungsleiste in der Formel hoch und runter bewegt werden.

Quick Info



Die Funktionen Summe, Mittelwert, Anzahl, Max und Min

  • Die Funktion Summe und andere werden so häufig benötigt, dass dafür eigene Symbole vorhanden sind.

    Symbol AutoSumme in der Symbolleiste
    Menüband Start Gruppe Bearbeiten Symbol Summe

    Summe, Mittelwert, Anzahl, Max, Min, Weitre Funktionen...

    AutoSumme - Mittelwert
    Abb.: Autosumme - Mittelwert

Tipps:
Nicht zusammenhängende Bereiche mit Strg markieren.

In der Statusleiste werden Mittelwert, Anzahl und Summe angezeigt.

Summe
Abb.: Statusleiste


Beispieldatei



Schnellanalyse (Strg + Q)

Seit 2013 bietet Excel ein neues Werkzeug.
Unter jeder Tabelle (Zellen markieren) befindet sich unten rechts das Symbol für die Schnellanalyse.

Das macht es den Anfängern leichter eine Tabelle auszuwerten.

Schnellanalyse - Ergebnisse
Abb.: Schnellanalyse - Ergebnisse





Eine Auswahl wichtiger Funktionen

Es ist nicht sinnvoll hier alle 470 Excel-Funktionen zu besprechen.
Um das Arbeiten mit Funktionen kennenzulernen, werden exemplarisch einige Funktionen besprochen.

In den folgenden Beispielen wird auch die englische Version angegeben.




2.2 Logikfunktionen

Werden oft im Zusammenhang mit anderen Funktionen eingesetzt.
Daher ist es sinnvoll diese Funktionen als erste zu besprechen.
Logikfunktionen verwenden Argumente mit WAHR oder FALSCH.

WENN
WENNS (nur Excel 2021 und Microsoft 365)
ODER
UND
ERSTERWERT (nur Excel 2021 und Microsoft 365)
...

Funktion WENN (engl. IF)

Die Funktion enthält 3 Argumente

=WENN(Prüfung;Dann_Wert;Sonst_Wert)

Prüfung ist ein beliebiger Wert oder Ausdruck, der WAHR oder FALSCH sein kann.

Dann_Wert ist der Wert, der zurückgegeben wird, wenn Prüfung WAHR ist.
Dann_Wert kann eine andere Formel sein. Optional.

Sonst_Wert ist der Wert, der zurückgegeben wird, wenn Prüfung FALSCH ist.
Sonst_Wert kann eine andere Formel sein. Optional.

Statt Bezüge oder Formeln können auch Funktionen eingesetzt werden.
Dadurch sind Verschachtelungen mit weiteren Funktionen möglich.



Beispieldatei

=WENN(C4<50;"Ist kleiner 50";"ok")

Englische Version:
=IF(C4<50;"Ist kleiner 50";"ok")

Funktion Wenn
Abb.: Funktion Wenn.


Beispieldatei

In einem Beispiel sollen Text und Zahlen unterschieden werden.

Beispiel für verschachtelte Funktion.

=WENN(ISTTEXT(C4);"Text";"Zahl")

Englische Version:
=IF(ISTEXT(C4);"Text";"Zahl")

Funktion ISTTEXT
Abb.: Funktion WENN und ISTTEXT.



Wie wird ein Datum angezeigt?



Beispieldatei

Beispiel für eine Verschachtelung.
Es sollen die Zahlen aus einem bestimmten Bereich gekennzeichnet werden.

Es gibt 3 Bereiche,
größer gleich 90,
der Bereich zwischen 60 und 90
und kleiner 60.

=WENN(C4>80;"> 80";WENN(C4>=60;"60-80";"< 60"))

Hinweis:
Seit Februar 2016 gibt es für die Office 365 und Office 2021 Versionen die neuen Funktionen WENNS und ERSTERWERT (engl. IFS und SWITCH)
blogs.office.com/2016/02/23/6-new-excel-functions-that-simplify-your-formula-editing-experience/

support.office.com/de-de/article/WENNS-Funktion

support.office.com/de-de/article/ERSTERWERT-Funktion

Damit werden verschachtelte Funktionen vermieden.

Das obige Beispiel sieht dann so aus
=WENNS(C4>80;"> 80"; C4>=60;"60 - 80"; C4<60;"< 60")



Funktion ERSTERWERT (engl. SWITCH)

=ERSTERWERT(Zu vergleichender Wert; Wert für Übereinstimmung1...[2-126];Rückgabewert bei Übereinstimmung...[2-126];Rückgabewert bei fehlender Übereinstimmung)

Wie WENNS eine neue Funktion seit Excel 2019. Die Funktion stellt einen "Switch" dar.
Microsoft Hilfe

Funktion ISTTEXT
Abb.: Funktion ERSTERWERT.

Beispiel in F4:F7 ist eine kleine Liste. In G4 steht folgendes:
=ERSTERWERT(F4;1;"Gold";2;"Silber";3;"Bronze";"Nichts")
Es wird in der ersten Spalte der zu vergleichende Wert (F4) mit dem Wert für Übereinstimmung (1) verglichen. Falls das zutrifft wird der Rückgabewert "Gold" ausgegeben.
Das ist hier der Fall.
Es können bis zu 126 Vergleiche angegeben werden. In dem Beispiel sind das 2 für "Silber" und 3 für "Bronze",
Standardwert, falls keines zutrifft ist "Nichts".



Funktionen UND (engl. AND)

=UND(Wahrheitswert1;Wahrheitswert2; ...)

Wahrheitswert1   ; Wahrheitswert2; ...   sind bis zu 30 Bedingungen, die überprüft werden sollen und jeweils WAHR oder FALSCH sein können.

Damit die Funktion wahr ist müssen alle Bedingungen erfüllt sein.

Beispiel

Welche Messreihe liefert alle Werte unter 50?
(C4 ist der erste Messwert)

=UND(C4<50;D4<50;E4<50;F4<50)

=AND(C4<50;D4<50;E4<50;F4<50)

Welche Messwerte sind größer 40 und kleiner als 60?

=UND(C4>40;C4<60)

=AND(C4>40;C4<60)



Funktionen ODER (engl. OR)

=ODER(Wahrheitswert1;Wahrheitswert2;...)

Wahrheitswert1;Wahrheitswert2 ;... sind 1 bis 30 Bedingungen, die überprüft werden sollen und jeweils entweder WAHR oder FALSCH sind.

Damit die Funktion wahr ist muss eine Bedingung erfüllt sein.

Beispiel

Welche Messreihe hat mindestens ein Wert unter 50?

=ODER(C4<50;D4<50;E4<50;F4<50)

=OR(C4<50;D4<50;E4<50;F4<50)


Siehe auch das Beispiel Bedingte Formatierung 4. Ausreißer aufspüren





2.3 Mathematische Funktionen

In der Kategorie Mathematik & Trigonometrie befindet sich eine umfangreiche Sammlung an Funktionen.

Auswahl Mathematischer Funktionen

SUMME
SUMMEWENN
SUMMEWENNS
ZÄHLENWENN
ZÄHLENWENNS
RUNDEN
POTENZ
LN
ZUFALLSZAHL
RÖMISCH
ABS
...

Auswahl Trigonometrische Funktionen
SIN
COS
TAN
...

Bei Trigonometrischen Funktionen ist zu beachten, dass Excel nicht mit Grad, sondern mit Bogenmaß arbeitet.

360° ist ungefähr ca. 6,283 in Bogenmaß (exakt sind das 2 x Pi).

Zur Umrechnung gibt es die Funktionen GRAD() und BOGENMASS()

Ein Zusammenhang soll das Beispiel Trigonometrie.xlsx zeigen.





Funktion ZÄHLEWENN (engl. COUNTIF)

Zählt die nicht leeren Zellen eines Bereichs, deren Inhalte mit den Suchkriterien übereinstimmen.

Beispiel
Beispiel - Lösung

Syntax

=ZÄHLENWENN(Bereich;Kriterien)

Bereich      ist der Zellbereich, der ausgewertet wird, 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.



Funktion ZähleWenn
Abb.: Funktion Zaehlewenn. Suchkriterium ist "expressed protein" - es werden 2 Treffer gefunden.
Außerdem wird mit bedingter Formatierung nach "expre" gesucht (F4).
Die ersten 5 Buchstaben eingeben. Die Treffer werden grün markiert.



Aus einer Internetauswertung wurde die Beschreibung von verschiedenen Genen gefunden.
Zähle alle Zeilen, die "expressed protein" in der Beschreibung haben. Versuche auch "expr*" als Kriterium.

* und ? sind in Suchkriterien Platzhalter. Fragezeichen ersetzt ein Zeichen und ein Sternchen ersetzt beliebig viele Zeichen.
Soll nach dem Zeichen * oder ? gesucht werden so ist vorher eine Tilde ~ zu schreiben. Also ~* oder ~?

Wie könnten die Zellen mit bedingter Formatierung kenntlich gemacht werden?

Vorschläge:
mit bedingter Formatierung
=LINKS($B7;5)=$G$3
=LEFT($B7;5)=$G$3
(5 Zeichen von links werden ausgewertet)

Wie kann das verbessert werden? Ein Vorschlag:
=LINKS($B7;LÄNGE($G$3))=$G$3
=LEFT($B7;5)=$G$3
(Beliebige Zeichen von links werden ausgewertet)

Was macht folgende Formel für die bedingte Formatierung? Eine noch bessere Lösung!
=ISTZAHL(SUCHEN($G$3;$B7))
=ISNUMBER(SEARCH($G$3;$B7))
(Beliebige Zeichen im Text werden ausgewertet)





Hier vier kleine Beispiele, wie mit den Tabellenfunktionen ZÄHLENWENN und ZÄHLENWENNS bzw. SUMMEWENN und SUMMEWENNS gezaubert werden kann.

Beispiel 1 - Summe und Anzahl mit einem Kriterium
Beispiel mit 1 Kriterium

In diesem Beispiel wird ein Kriterium (Zahl > 10) eingesetzt.

Die Anzahl für das Kriterium <10 wird mit ZÄHLENWENN ermittelt
=ZÄHLENWENN(A4:A22;">10")

Die Summe für das gleiche Kriterium mit SUMMEWENN
=SUMMEWENN(A4:A22;">10")

Eine interessante Alternative ist es, eine Matrixformel (siehe unten) zu benutzen.
Nach der Eingabe der Formel in der Bearbeitungsleiste wird Strg + Umschalt + Eingabe gedrückt.
Die Zeichen "{" und "}" werden nicht mit der Tastatur eingegeben!
Als Matrixformel sieht das so aus.
Anzahl
{=SUMME((A4:A22>10)*(A4:A22>10))}
Summe
{=SUMME(WENN(A4:A22>10;A4:A22))}



Beispiel 2 - Summe und Anzahl mit zwei Kriterien
Beispiel mit 2 Kriterien

In dem 2. Beispiel soll in einer Messreihe nur die Messungen zwischen 10 und 50 gezählt werden.

Die Anzahl wird mit der Funktion ZÄHLENWENNS ermittelt
=ZÄHLENWENNS(A4:A22;">10";A4:A22;"<50")
ZÄHLENWENNS lässt im Gegensatz zu ZÄHLENWENN mehrere Kriterien zu.

Die Summe wird mit der Funktion SUMMEWENNS ermittelt
=SUMMEWENNS(A4:A22;A4:A22;">10";A4:A22;"<50")
SUMMEWENNS lässt im Gegensatz zu SUMMEWENN mehrere Kriterien zu.

Eine andere Lösung gibt es mit einer SUMME-Funktion, die in einer Matrixformel (siehe unten) steckt.
=SUMME(WENN(A4:A22>10;WENN(A4:A22<50;A4:A22)))
Nach der Eingabe wird Strg + Umschalt + Eingabe gedrückt. Die verwandelt die Formel in die Matrixformel
{=SUMME(WENN(A4:A22>10;WENN(A4:A22<50;A4:A22)))}
Die Anzahl wird mit
=SUMME((A4:A22>10)*(A4:A22<50))
dargestellt.




Beispiel3 - Summe wenn in einer anderen Spalte ein Kriterium erfüllt wird
Beispiel Kriterium in anderer Spalte

Hier wird in einer Spalte nach einem Kriterium gesucht und in einer anderen die Summe gebildet.
Anzahl
=ZÄHLENWENN(A4:A22;"Mutante")
Summe
=SUMMEWENN(A4:A22;"Mutante";B4:B22)

Summe als Matrixformel (siehe unten)
{=SUMME(WENN(A4:A22="Mutante";B4:B22))}



Beispiel 4 - ZÄHLENWENN um doppelte Werte aufzuspüren
siehe auch Bedingte Formatierung Beispiel

Wir haben die Funktion ZÄHLENWENN bereits bei bedingter Formatierung eingesetzt.
=ZÄHLENWENN($A$6:$B$198;A6)>1

=ZÄHLENWENN(Bereich;Kriterien)
Das heißt, wenn der Inhalt der Zelle mehr als 1 mal gefunden wird, wird die Zelle durch eine Formatierung markiert.



Matrixformel (Array Formular)

Hilfe Microsoft Excel-online

In der Matrixformel werden Bereiche als Matrizen (Arrays) aufgefasst und lassen sich miteinander berechnen. Es kann auch jede Funktion in einer Matrixformel verwendet werden.

Es werden ganze Bereiche als Matrize eingegeben und es können wieder Bereiche ausgegeben werden.
Ist die Matrixformel und die Ausgabe nur in einer Zelle wird von single-cell array formular gesprochen (Beispiel 1 unten).
Werden mehrere Zellen ausgegeben (Beispiel 2) ist von multi-cell array formular die Rede.

Matrixformel - einfaches Beispiel

Abb. In dem kleinen Beispiel wird jeder Wert der Spalte B2:B5 mit dem Wert der Spalte C2:C5 multipliziert
D2:D5 sollte markiert sein und die Formel mit Strg+Umschalt+Eingabe abschließen.

Zum Unterschied zu einer normalen Formel, steht die Matrixformel innerhalb von "{" und "}".
Nach der Eingabe der Formel in der Bearbeitungsleiste wird Strg + Umschalt + Eingabe gedrückt.
Die geschweiften Klammern erscheinen in der Bearbeitungsleiste.
Die Zeichen "{" und "}" werden nicht mit der Tastatur eingegeben! Das funktioniert nicht.
Soll die Formel geändert werden, muss an Ende wieder Strg+Umschalt+Eingabe gedrückt werden.
Die Formeln werden auf englisch auch als CSE formulas bezeichnet. Für die Control+Shift+Enter Tastenkombination beim Erstellen der Matrixformel.

Achtung: Matrixformeln gehen nicht in Tabellen als strukturierte Verweise. Es kann aber in eine Tabelle bezogen werden. Siehe Beispiel 1 unten.

Um die Arbeitsweise einer Matrixformel zu verstehen sind unten zwei einfache Beispiele angegeben.

Beispiel 1 Summe aus Anzahl und Einzelpreisen


Matrixformel - einfaches Beispiel
Abb.: Matrixformel - einfaches Beispiel, die Summe wird aus der Anzahl und den Einzelpreisen berechnet.
Die Berechnung ist bei umfangreichen Listen mit Matrixformeln viel einfacher, als mit der normaler Berechnung ("Gesamt einfach berechnet").
Wird die Tabelle als Tabelle formatiert ist die Formel noch übersichtlicher (strukturierte Verweise),
jetzt können die Spalten mit Namen in der Formel angesprochen werden ("Tabellenbezeichnungen")


In den Beispielen 1-3 für die Funktion Zählenwenn(s) sind weitere Matrixformeln angegeben.


Beispiel 2 Trendberechnung - Funktion Trend

In dem Beispiel wird mit der Funktion TREND eine lineare Regression errechnet.

Es müssen alle Zellen vorher markiert werden (im Beispiel C4:C7),
dann die Funktion eingeben und mit Strg+Umschalt + Eingabe abschließen.

{=Trend(y-Werte;x-Werte)}

Matrixformel - Trendberechnung
Abb.: Matrixformel - Trendberechnung mit Hilfe der Matrixformel.
Es müssen die entsprechenden Felder markiert werden. Hier C4:C7 (siehe Abb.).
Die Formel wird eingegeben =TREND(..) und mit Shift+Strg+Eingabe als Matrixformel definiert.

Aus dem Trend können auch mit einem 3. Argument neue Werte ermittelt werden:

{=Trend(y-Werte;x-Werte;neue-Werte)}

support.microsoft.com/de-de/office/trend-funktion

Die Funktion TREND() gibt einen linearen Trend aus.
Dagegen gibt die Funktion VARIATON() Werte für einen exponentialen Trend aus.




2.4 Datums- und Zeitfunktionen

Excel rechnet bei Datums- und Zeitwerten intern mit Zahlen.
Daher ist ein Datum oder eine Zeit, wie eine Zahl standardmäßig rechts angeordnet.

Die Zählung der Zeit beginnt bei Excel am 1.1.1900. Negative Werte gehen nicht. Historiker haben also Probleme mit Excel.
Charley Kyd (2006) macht einige Vorschläge um dieses Problem zu lösen
www.exceluser.com/explore/earlydates.htm
Excel auf alten Macs rechnen mit 1.1.1904 als Ausgangsjahr (Infos).
Es gibt zwischen Macs und PCs trotzdem meist kein Problem, weil das Datums-Bezugssytem in der Datei gespeichert ist und richtig berücksichtigt wird.

Die Uhrzeit wird intern als Bruchteil eines Tages behandelt, wobei dann zum Beispiel eine Sekunde etwa 0,0000116 ist und 23:59:59 ist 0,9999884.

Die aktuelle Zeit kann mit STRG + . eingefügt werden (Strg und Punkt).

Beispiele für Datums- und Zeitfunktionen

HEUTE()
JETZT()
DATUM()
DATWERT()
JAHR()
MONAT()
TAG()
WOCHENTAG()
KALENDERWOCHE()
ZEIT()
STUNDE()
MINUTE()
SEKUNDE()
...



Als Beispiel habe ich bereits die Bedingte Formatierung mit der Funktion WOCHENTAG behandelt, siehe im Skript.






2.5 Nachschlagen und Verweisen - Matrixfunktionen

Diese Gruppe wird auch als Gruppe der Matrixfunktionen bezeichnet. Mit Matrixfunktionen werden Such- und Auswerteaufgaben aus einem Zellbereich realisiert.

Ein Beispiel? Ich suche in einer zweiten Tabelle nach der ID einer Gensequenz und ich erhalte den Namen des Gens zurück und trage den Namen in die erste Tabelle ein..

Auswahl an Matrixfunktionen

VERWEIS
SVERWEIS
WVERWEIS
XVERWEIS (neu, zur Zeit nur in Office 365)
INDEX
INDIREKT
ADRESSE



Verweis (engl. LOOKUP)

Durchsucht die Werte eines Tabellenbereichs und ein entsprechender Wert wird zurückgegeben.

Für die VERWEIS-Funktion gibt es zwei Syntaxversionen: die Vektor- und die Matrixversion.



Vektorversion

Ein Vektor besteht nur aus einer Zeile oder Spalte.
Die Vektorversion von VERWEIS sucht in einem Bereich, der aus nur einer Zeile oder Spalte besteht (Vektor), nach einem bestimmten Wert und gibt einen Wert aus derselben Position in einem zweiten Vektor zurück.

=VERWEIS(Suchkriterium;Suchvektor;Ergebnisvektor)

Suchkriterium      ist ein Wert, nach dem VERWEIS im ersten Vektor sucht. Suchkriterium kann eine Zahl, eine Zeichenfolge (ein Text), ein Wahrheitswert oder ein Name bzw. ein Bezug sein, der sich auf einen Wert bezieht.

Suchvektor      ist ein Bereich, der nur eine Zeile oder Spalte enthält. Zulässige Elemente von Suchvektor sind Zeichenfolgen (Texte), Zahlen oder Wahrheitswerte.

Wichtig  
Die zu Suchvektor gehörenden Werte müssen in aufsteigender Reihenfolge angeordnet sein: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSCH, WAHR; andernfalls gibt VERWEIS möglicherweise einen falschen Wert zurück (siehe Beispiel unten). Bei Zeichenfolgen (Texten) wird nicht zwischen Groß- und Kleinbuchstaben unterschieden.

Welcher Arbeitsschritt sollte daher vor dem Suchen mit der Tabellenfunktion VERWEIS durchgeführt werden? (Lösung: Sortieren)

Ergebnisvektor  ist ein Bereich, der nur eine Zeile oder Spalte enthält. Dieser Vektor muss genauso viele Elemente umfassen wie der Suchvektor.

Beispiel Gene-ID

Einfache Suchmaske erstellen. Warum funktioniert das Beispiel nicht?
Bringe das Beispiel zum Laufen.

=VERWEIS($B$3;B5:B11;C5:C11)

=LOOKUP($B$3;B5:B11;C5:C11)



Matrixversion

Die Matrixversion von VERWEIS sucht in der ersten Zeile oder Spalte einer Matrix nach dem angegebenen Wert und gibt einen Wert aus derselben Position in der letzten Zeile oder Spalte derselben Matrix zurück.

=VERWEIS(Suchkriterium;Matrix)

Suchkriterium ist ein Wert, nach dem VERWEIS in der Matrix sucht. Suchkriterium kann eine Zahl, eine Zeichenfolge (ein Text), ein Wahrheitswert oder ein Name bzw. ein Bezug sein, der sich auf einen Wert bezieht.

Matrix ist ein Zellbereich, der entweder Text, Zahlen oder Wahrheitswerte enthält, die Sie mit Suchkriterium vergleichen möchten.

Hat Matrix mehr Spalten als Zeilen, gleicht VERWEIS die Elemente der ersten Zeile gegen das Suchkriterium ab. Ist Matrix quadratisch oder hat mehr Zeilen als Spalten, durchsucht VERWEIS die erste Spalte.

Hinweise:
Kann die VERWEIS-Funktion keinen Wert finden, der mit dem jeweiligen Suchkriterium übereinstimmt, verwendet sie den größten Wert in der Matrix, der bezogen auf das Suchkriterium kleiner oder gleich ist. Ist das Suchkriterium kleiner als der kleinste Wert der ersten Zeile oder Spalte (dies hängt von den Matrixdimensionen ab), gibt VERWEIS den Fehlerwert #NV zurück.

Welcher Arbeitsschritt sollte daher vor dem Suchen mit der Tabellenfunktion VERWEIS durchgeführt werden?

-> Lösung: Sortieren


Beispiel Gene-ID

Einfache Suchmaske erstellen.

=VERWEIS($B$3;$B$5:$C$11)

=LOOKUP($B$3;$B$5:$C$11)

Beispiel Telefonliste

Bitte eine Suchmaske erstellen.

Resultat könnte so aussehen.

Drop-Down-Liste wurde mit Datenüberprüfung erstellt. Siehe im Skript.
Datenüberprüfung : Zulassen: Liste

=VERWEIS($B$3;$B$6:$B$212;$C$6:$C$212)



SVERWEIS und WVERWEIS

SVERWEIS (engl. VLOOKUP)

Sucht in der am weitesten links gelegenen Spalte einer Tabelle nach einem Wert und gibt in der gleichen Zeile einen Wert aus einer angegebenen Spalte in der Tabelle zurück.

Verwende SVERWEIS, wenn sich die Vergleichswerte in einer Spalte links von den Daten befinden, die gesucht werden.

Das S in SVERWEIS steht für "Senkrecht".

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)

Suchkriterium ist der Wert, der in der ersten Spalte der Matrix gefunden werden soll.

Matrix ist die Tabelle mit Informationen, in der die Daten nachgeschlagen werden.
Bei den Werten in der ersten Spalte von Matrix kann es sich um Text, Zahlen oder logische Werte handeln. Bei Zeichenfolgen (Texten) wird nicht nach Groß- und Kleinbuchstaben unterschieden.

Spaltenindex ist die Spaltennummer in Matrix, aus der der entsprechende Wert zurückgegeben werden muss. Ein Spaltenindex von 1 gibt den Wert der ersten Spalte in Matrix zurück; ein Spaltenindex von 2 gibt den Wert der zweiten Spalte von Matrix zurück usw. Wenn Spaltenindex kleiner 1 ist, gibt SVERWEIS den Fehler #WERT! zurück; wenn Spaltenindex größer als die Anzahl der Spalten in Matrix ist, gibt SVERWEIS den Fehlerwert #BEZUG! zurück.

Bereich_Verweis ist ein logischer Wert, der angibt, ob SVERWEIS eine genaue oder eine ungefähre Entsprechung suchen soll. Wenn dieser Parameter WAHR ist oder weggelassen wird, wird eine ungefähre Entsprechung zurückgegeben. Anders ausgedrückt, wird der nächstgrößere Wert zurückgegeben, der kleiner als Suchkriterium ist, wenn keine genaue Entsprechung gefunden wird.
Die Werte sollten daher in aufsteigender Ordnung sortiert sein.
Wenn der Parameter FALSCH ist, sucht SVERWEIS eine genaue Entsprechung.
Sortierung ist daher nicht notwendig.
Wird keine Entsprechung gefunden, wird der Fehlerwert #NV zurückgegeben.

Beispiel Gene-ID

Bitte teste Bereich_Verweis mit WAHR oder FALSCH

Beispiel Telefonliste

Bitte eine Suchmaske erstellen. Eingabe der Vornamen.

Mögliche Lösung könnte so aussehen.
Welches Problem besteht noch?


Beispiel Chemikalienliste
Verweise auf einem 2. Tabellenblatt.
VERWEIS, SVERWEIS und strukturierte Tabelle
Suchmaske, INDEX/VERGLEICH


WVERWEIS (engl. HLOOKUP)

Die Daten werden in den Zeilen gesucht. "W" steht für waagrecht.



Tipp Index/Vergleich

SVERWEIS() gibt nur Spalten ausgehend von der Suchspalte, die rechts davon liegen, aus.
Und wenn ich eine Spalte links von der Suchspalte ausgeben will?
Eine Kombination aus den Funktionen Index() und Vergleich() kann das umgehen.

=INDEX(Matrix; VERGLEICH(Suchkriterium; Matrix;Vergleichstyp);Spalte)

Vergleichstyp = 0, ist genaue Übereinstimmung (1 ist kleiner als und -1 ist größer als)
Spalte = aus der ausgegeben werden soll

Formel für die Telefonliste, Ausgabe der Vornamen
=INDEX($A$6:$B$206;VERGLEICH($B$3;$B$6:$B$206;0);1)

http://www.excel-inside.de/funktionen-a-tipps/144-sverweis-vs-index-vergleich

http://www.excellernen.de/excel-index-vergleich-funktion/




XVERWEIS

Eine neue Funktion, die SVERWEIS und WVERWEIS ersetzt und zusätzlich weitere Einstellmöglichkeiten aufweist.

Zur Zeit nur in Microsoft 365 enthalten.

support.microsoft.com/de-de/office/xverweis-funktion-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Ein sehr gutes Video zur Einführung hat Excelhero Daniel Kogan gemacht
www.youtube.com/watch?v=nF32fyol0FU


=XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden]; [Vergleichsmodus]; [Suchmodus])

Suchkriterium ist der Ausdruck nach dem gesucht wird.

Suchmatrix Bereich in dem gesucht wird.

Rückgabematrix Bereich der zurückgegeben wird.
Wird eine Zeile angegeben ist das wie WVERWEIS.
Anders als bei SVERWEIS und WVERWEIS kann auch ein Array zurückgegeben werden. Also Werte von mehreren Splten bzw. Zeilen.
Die Rückgabe ist flexibler als bei SVERWEIS. Es kann zum Beispiel auch eine Spalte links von der Suchmatrix angegeben werden.

wenn_nicht_gefunden Wert der ausgegben wird, wenn nicht gefunden wird.

Vergleichsmodus
0
: genaue Übereinstimmung. Standardeinstellung.
-1: Wenn keine gefunden wurde, das nächstkleinere Element .
1: Wenn keine gefunden wurde, das nächstgrößere Element.
2: eine Platzhalterübereinstimmung, wobei *, ? und ~ eine Sonderbedeutung haben.

* bedeiten mehrere Zeichen
? genau 1 Zeichen
~ genau dieses Zeichen, ~* bedeutet, dass genau ein * gesucht wird. * Wäre ja ein Platzhalter.

Suchmodus
1 beim ersten Element beginnen. Das ist Standard. In einer Spalte von oben nach unten.
-1 beim letzten Element beginnen. In einer Spalte von unten nach oben.





2.6 Textfunktionen

Texte werden manipuliert und ausgewertet. Mit diesen Funktionen lassen sich Zellinhalte extrahieren oder in eine gewünschte Form umwandeln.
Einige charakteristische Textfunktionen sollen dies verdeutlichen.


Beispiele:

LINKS - Zeichenfolge links
RECHTS - Zeichenfolge rechts
TEIL - Zeichenfolge ab einem Zeichen
IDENTISCH - Prüft ob zwei Zeichenfolgen identisch sind
WECHSELN - Tauscht eine Zeichenfolge gegen eine andere aus



Beispiel 1 Spalte trennen PLZ

Postleitzahl und Ort wurden in eine Spalte geschrieben. Wie läßt sich die Postleitzahl aus der Spalte herausfiltern?

In A5 steht "72076 Tübingen"

Formel für B5 ist

=LINKS(A5;5)

=LEFT(A5;5)




Beispiel 2 Text extrahieren

In einer Spalte stehen Vor und Nachnamen. Zum alphabetischen Sortieren soll der Vor- und der Nachnamen in einer eigenen Spalte herausgezogen werden.

In Zelle A5 steht "Hans Müller"

1. Lösung:

Vorname:
Formel für B5 ist
=LINKS(A5;FINDEN(" ";A5))

Englisch
=LEFT(A5;FIND(" ";A5))

In B5 steht dann "Hans"

Nachname:
Formel für C5 ist
=RECHTS(A5;LÄNGE(A5)-FINDEN(" ";A5))

Englisch
=RIGHT(A5;LEN(A5)-FIND(" ";A5))

In C5 steht dann "Müller"

Tipp: Siehe auch Excel Hilfe. Stichwort "Aufteilen von Text in Spalten mit Hilfe von Funktionen".

Es geht auch mit der Funktion TEIL
=TEIL(A5;FINDEN(" ";A5)+1;LÄNGE(A5))

Statt der Funktion FINDEN geht auch SUCHEN.
SUCHEN unterscheidet in Gegensatz zu FINDEN nicht Groß- und Kleinschreibung und unterstützt Platzhalter (* oder ?).



2. Lösung

Spalte markieren

  • Menüband Daten Gruppe Datentools Symbol Text in Spalten...

Text in Spalten


Abb.: Text in Spalten.




Beispiel 3 - Praxisbeispiel Spalte trennen

Beispieldatei

Ein Praxisbeispiel aus der biochemischen Analyse. Ein Protokoll liefert eine Spalte mit zwei Werten die innerhalb einer Zelle mit einem Absatz getrennt sind.

Aufgabe ist die beiden Werte in der Spalte B in den nachfolgenden Spalten zu trennen. In Spalte B sind die Werte durch eine neue Zeile getrennt.

Benutzt wird das Absatzzeichen (CHAR(10)) in englischem Excel. Das ist das Zeichen für "neue Zeile".

"FIND(CHAR(10)" - Sucht die Stelle an der das Absatzzeichen vorkommt

"LEFT(B4;FIND(CHAR(10);B4)-1)" - Gibt die Zeichen links von dem Absatzzeichen wieder

Da einige Zeilen auch ohne Absatzzeichen vorkommen würde jetzt eine Fehlermeldung kommen.

"ISERR(FIND(CHAR(10)" - gibt bei Fehler TRUE aus

"IF(ISERR(FIND(CHAR(10);B4));B4;LEFT(B4;FIND(CHAR(10);B4)-1))" - gibt bei Fehlermeldung den Text aus (B4) und bei FALSE die Zeichen links von dem Absatzzeichen

Formeln auf Deutsch:

Formel1

=WENN(ISTFEHL(FINDEN(ZEICHEN(10);B4));B4;LINKS(B4;FINDEN(ZEICHEN(10);B4)-1))

Formel 2

=WENN(ISTFEHL(FINDEN(ZEICHEN(10);B4));"leer";RECHTS(B4;LÄNGE(B4)-FINDEN(ZEICHEN(10);B4)))



Beispiel 4 - Komma statt Punkt

Beim Datenaustausch mit amerikanischen Unis kommt es oft vor, dass Dezimalzahlen in Exceltabellen einen Punkt statt Komma aufweisen.
Eine Lösung wurde bereits hier gezeigt.

Mit Hilfe der WECHSELN-Funktion kann das auch einfach in der geöffneten Tabelle gemacht werden.

=WECHSELN(A1;".";",")

Da die Zahl mit einem Punkt als Komma, meist als Text formatiert ist, kann sie gleich mit der Funktion WERT in eine Zahl verwandelt werden.

=WERT(WECHSELN(A1;".";","))

Englisch
=VALUE(SUBSTITUTE(A1;".";","))


Hinweise:
Es geht auch mit der Funktion ERSETZEN, die an einer angegebenen Stelle eine bestimmte Anzahl an Zeichen ersetzen kann. Um die Stelle zu finden an der der Punkt kommt wird die Funktion SUCHEN benutzt.
=WERT(ERSETZEN(B6;SUCHEN(".";B6);1;","))
=VALUE(REPLACE(B6;SEARCH(".";B6);1;","))

Die Funktion WERT wandelt Text in Zahlen um.
Die Funktion TEXT macht das Gegenteil und wandelt eine Zahl in Text um.
Dabei kann eine Formatierung bestimmt werden
=TEXT(Zahl;Formatierung)
Die Funktion FEST wandelt Zahlen in Text um, rundet und fügt Dezimalstellen an.
=FEST(Zahl;Dezimalstellen;Keine_Punkte)

Noch einfacher mit der Funktion ZAHLENWERT.
=ZAHLENWERT(Text;[Dezimaltrennzeichen];[Gruppentrennzeichen])
=ZAHLENWERT(B6;".";",")



Beispiel 5 - Funktion Wiederholen (engl. REPT)

Wiederholt einen Text so oft wie angegeben. Verwenden Sie WIEDERHOLEN, um eine Zeichenfolge (eine Basiszeichenfolge) in einer bestimmten Häufigkeit in eine Zelle einzugeben.

Syntax

=WIEDERHOLEN(Text;Multiplikator)

Englisch
=REPT(Text;Multiplikator)

Text      ist der Text, den Sie wiederholen möchten.

Multiplikator     ist eine positive Zahl, die angibt, wie oft Text wiederholt werden soll.

Hinweise

  • Ist Multiplikator mit 0 (Null) belegt, gibt WIEDERHOLEN eine leere Zeichenfolge ("") zurück.

  • Ist Multiplikator keine ganze Zahl, werden deren Nachkommastellen abgeschnitten.

  • Das Ergebnis der Funktion WIEDERHOLEN darf nicht mehr als 32.767 Zeichen enthalten. Andernfalls gibt WIEDERHOLEN den Fehler #WERT! zurück.



Beispiel

Funktion Wiederholen
Abb.: Funktion WIEDERHOLEN.


Bitte das Beispiel ausprobieren. Zeichen und Multiplikator ändern.





2.7 Tabellenfunktionen in der Statistik

In Excel sind die wichtigsten statistische Funktionen für Naturwissenschaftler bereits eingebaut.
Da aber vieles vermisst wird, können noch weitere Analysefunktionen aus dem Excelpaket installiert werden.
Darüber hinaus gibt es kostenlose und kostenpflichtige Add-ins für Excel in diesem Bereich.

Excel 2007 wird wegen den Ungenauigkeiten in den statistischen Funktionen kritisiert (siehe Wikipedia).
Daher wurde der Algorithmus bei einigen Funktionen in Excel 2010 und 2013 geändert
http://office.microsoft.com/en-us/excel-help/what-s-new-changes-made-to-excel-functions-HA010355760.aspx?CTT=5&origin=HA010369709


Übersicht Statistik in Excel
http://excelformeln.de/formeln.html?gruppe=7

Kurse an der Tübinger Uni jeweils Sommersemester
- Prof. Peter Pilz, Einführung in die deskriptive und schließende Statistik, Campus SS 2021
Den Kurs gibt es vermutlich auch wieder im SS 22.


Software

XLSTAT Mac und Windows, Professionelles Statistikprogramm als Add-in für Excel
Nicht kostenlos.


Es gibt andere Software, die nicht aus dem Excelbereich ist, aber guter Exportmöglichkeit zu Excel enthalten:

SIGMAPlot (SPSS) - Stärke in Regressionsanalytik, Grafiken

DeltaGraph - für Windows und Mac, sehr gute Grafiken (eps)

SPSS - riesiges Statistikpaket
kostenlos für Tübinger Studenten. Siehe Uni Tübingen Campus Software.

SAS JMP
seit WS 08/09 kostenlos für Tübinger Studenten. Siehe Uni Tübingen Campus Software.


R und Statistiklabor
Das Statistiklabor wird leider nicht mehr weiter entwickelt.
R ist eine freie Programmiersprache und Statistik-Software
Eine Hilfe für Anfänger stellt das Programm Statistiklabor (Statistical Lab) dar.
Für die mathematischen Berechnungen verwendet das freie Programm die Programmiersprache R.

Für R gibt es ein Excel Add-In das RExcel
Leider keine weitere Entwicklung seit 2013
Literatur
Rainer Muche, Stefanie Lanzinger, Michael Rau, 2011, Medizinsiche Statistik Mit R Und Excel: Einführung in die Rexcel- und R-commander-oberflächen zur Statistischen Auswertung.


Minitab
Wird an amerikanischen Universitäten eingesetzt.
Wikipedia



Die wichtigsten Excel-Tabellenfunktionen in der Statistik

MIN - Minimalwert
MAX - Maximalwert
KGRÖSSTE und KLEINSTE (die k größten oder k kleinsten Werte; k ist der Rang)
MITTELWERT
MEDIAN (In der Statistik halbiert der Median eine Stichprobe.)
ANZAHL und ANZAHL2 (ANZAHL summiert alle Zahlenwerte, Formeln oder Text wird nicht gezählt, ANZAHL2 werden alle nichtleeren Zellen aufsummiert)
RANG (Liefert den Rang, den eine Zahl innerhalb einer Liste von Zahlen einnimmt.)
HÄUFIGKEIT (Häufigkeitsverteilung)



Weitere Analysefunktionen einfügen

    • Menüband Datei : Excel-Optionen

    • Register Add-Ins

    • Verwalten Excel-Add-Ins
      Schaltfläche Gehe zu...

    • Button Gehe zu...


      Add-Ins

    • Analysis-Funktionen aktivieren


Die Analysefunktionen können unter Menüband Daten
Gruppe Analysis Symbol Data Analysis aufgerufen werden.



2.8 Eigene Funktion mit Lambdafunktion - Variablen in Funktionen mit LET()

Lambdafunktion
Seit Dezember 2020 kann mit Excel auch sogenannte "Lambdafunktionen" erstellt werden.
Microsoft Hilfe

Lambdafunktionen sind anonyme Funktionen, sie haben keinen Namen zum Aufrufen. Mit dieser Funktion kann relativ einfach eine eigene Funktion erstellt werden.
Es wird keine Programmiersprache, wie in dem nächsten Abschnitt mit "User Defined Functions (UDF)" gezeigt wird, benötigt.

=LAMBDA([Parameter1, Parameter2, ...,] Berechnung)




Ebenfalls neu ist die Funktion LET().
Mit der Funktion LET() können Variablen in einer Excelfunktion definiert werden. Die Funktion wird klarer, der Code angeblich schneller.

=LET(Name_der_Variablen; Wert_der_Variablen; Berechnung)

Es können bis zu 126 Variablen eingeführt werden.

Microsoft hält in der Hilfe eine Beispieldatei vor, die allerdings in deutschem Excel nicht funktioniert (engl. Funktionsnamen und Komma statt Semikolon).
support.microsoft.com/de-de/office/let-funktion
Hier das korregierte Beispiel für das deutsches Excel beisp-excel-let-filter.xlsx


Hier noch weitere Beispiele aus dem Internet
it-service-ruhr.de/tipp/vorteile-der-neuen-excel-funktion-let




2.9 Erstellen einer eigenen Funktion
Excel JavaScript-API und User Defined Functions (UDF)

Der nächste Schritt eine eigene Funktion zu erstellen, ist in die Welt der Programmierung einzutauchen.
Es gibt dabe zwei verschiedene Ansätze.

Excel JavaScript-API

Seit Office 2016 kann eine Schnittstelle zu Excel hinzugefügt werden, die die Programmiersprache JavaScript versteht.
Das Excel JavaScript-API wird über die kostenlose Microsoft VisualStudio Variante installiert und verbindet Excel mit den im Browser (Firefox, etc.) laufenden HTML-Seiten.
Video the first steps - online-schulprogramm.de
visualstudio.microsoft.com/de/

Kurze Einführung, wie fängt man an:

  • Visual Studio Windows Community Vesion installieren
    Office SharePoint Development, Creat Office and SharePoint Add-ins
  • Visual Studio starten (Einstellung JavaScript)
  • Neues Projekt öffnen
    Visual Basic Excel-Web-Add-in
    Excel neue Funktionen hinzufügen
  • Ein Beispielobjekt wird geöffnet.

    Microsoft Visual Studio Community 2019 - Beispielcode für Excel JavaScript Add-inn
    Abb.: Microsoft Visual Studio Community 2019 - Beispielcode für Excel JavaScript Add-in

  • Auf Schaltfläche Starten klicken
    Dauert etwas, da der Code von der Microsoftseite appsforoffice.microsoft.com/lib/1/hosted/office.js geholt wird.
    Home.css
    Home.html
    Home.js
    sind die Scriptdateien

  • In Excel erscheint ein neuer Arbeitsbereich ExcelWebAddIn1


    Excel JavaScript Add-in. Beispielcode findet den max. Wert in einem Bereich und hebt ihn hervor.
    Abb.: Excel JavaScript Add-in. Beispielcode findet den max. Wert in einem Bereich und hebt ihn hervor.


Weitere Infos Microsoft
docs.microsoft.com/de-de/office/dev/add-ins/excel/excel-add-ins-core-concepts

Etwas einfacher geht es mit dem Office Add-in Script Lab.
Das ist ein Microsoft "Garage project", daher weiß man nicht wie lange das angeboten wird.
https://www.microsoft.com/en-us/garage/profiles/script-lab
github.com/OfficeDev/script-lab

Meine Zusammenfassung ist, Excel JavaScript-API bietet zwar riesen Möglichkeiten aber das ganze ist bisher nicht sehr populär.
Programmierer greifen doch lieber gleich zu Java, Python, R um ihre Daten auszuwerten und darzustellen und verwenden überhaupt nicht das lahme Excel.
(Diskussion - Anyone using Excel JavaScript?)




User Defined Functions (UDF)

Die zweite Möglichkeit bietet einen besseren Einstieg für Anfänger.
Es wird das in allen Officeprogrammen eingebaute Visual Basic for Application verwenden.
Es muss nur das Menüband Entwicklertools (s.u.) aktiviert werden.

Eigene Tabellenfunktionen können erstellt werden. Diese Funktionen werden als User Defined Functions (UDF) bezeichnet.
Aufgerufen werden die UDF im Arbeitsblatt wie jede andere eingbaute Funktion

Die Programmiersprache wird seit jahrzehnten in Excel eingesetzt.
Daher ist das Internet eine riesen Fundgrube für Beispielcode.


Kurze Anleitungen im Internet
www.goskills.com/Excel/Resources/Excel-user-defined-function-examples

www.vertex42.com/ExcelArticles/user-defined-functions.html



Kursbeispiel

Die Aufgabe besteht darin eine einfache eigene Funktion zu schreiben.
Die Funktion mwst rechnet automatisch die Mehrwertsteuer aus.

Dabei wird Visual Basic verwendet.

Eigene Funktion mwst
Abb.: Eigene Funktion mwst.

  • Menüband Entwicklertools Gruppe Code Symbol Visual Basic..

    oder ALT + F11

  • Im Fenster Microsoft Visual Basic im Menü Einfügen auf Modul.

  • Folgenden Code eingeben:

    Visual Basic Code eingeben

    Abb.: Visual Basic Code eingeben


    'Funktion Preis inkl Mehrwertsteuer
    Function mwst(zahl1)
    mwst = zahl1 * 1.19
    End Function

  • Fuction leitet den Subcode und End Function beendet den subcode.
    mwst
    ist der Name der Funktion, mit der die Funktion aufgerugen wird.
    zahl1 ist der Parameter, der auch im Code verwendet wird.

Der Aufruf der Funktion erfolgt in einem Excel Arbeitsblatt.
Die Funktion wird, wie jede Funktion in Excel mit zum Beispiel
=mwst(B1)
aufgerufen. Die Funktion wird auf die Zelle B1 angewendet.
QickInfo zeigt die Funktion sogar an.

Visual Basic Code eingeben



Hinweise:

Das Menüband Entwicklertools muss aktiviert werden:

  • Excel-Optionen : Register Menüband anpassen
    In der rechten Spalte Entwicklertools aktivieren

Auch in der deutschen Version werden in der VBA-Programmierung Punkt "." als Dezimalzeichen verwendet.



Die Funktion steht nach dem Speichern nur in dieser Datei zur Verfügung und nur wenn mit Excel-Arbeitsmappe mit Makros *.xlm abgespeichert wird.

Soll die Funktion auch in anderen Exceldateien benutzt werden; so kann die Funktion in ein Modul exportiert werden.

  • Im Visual Basic Fenster das Modul anklicken und Menü Datei :Datei exportieren...




Eine andere Möglichkeit besteht das Modul als Add-In abzuspeichern.

  • In der Excel Arbeitsmappe Menüleiste Datei : Speichern unter... wählen

  • Dateityp Microsoft Office Excel Add-In (*.xla )

  • In der neuen Excel Arbeitsmappe in den Excel-Optionen : Add-Ins : Verwalten Add-Ins : Gehezu..

  • Schaltfläche Durchsuchen und das abgespeicherte Add-In auswählen.

    Standarmäßig wird die Datei in
    C:\Users\bbzst02\AppData\Roaming\Microsoft\AddIns
    gespeichert.

Das Add-In kann dan in einer neuen Datei geladen werden.

  • Menüleiste Datei

  • Optionen

  • Add-Ins

  • Verwalten: Excel-Add-Ins

  • Gehe zu...

  • Add-In aktivieren oder mit Durchsuchen... öffnen.

Vorsicht bei eigenen Funktionen und Arbeitsmappen die weitergegeben werden.
Nicht vergessen die Funktion mitzugeben!
Daher muss die Datei als .xlsm abgespeichert werden.
Normale .xlsx Dateien können aus Sicherheitsgründen keine Makros (und Funktionen) speichern.



Beispiel Textfunktionen in eigener Funktion





2.9 Tipps

Siehe Tabellenfunktionen Wiederholen



Leere Zellen optisch hervorheben

Bedingte Formatierung

Formel =WENN(ISTLEER(A1);WAHR;FALSCH)


Zellen mit Datum und Text kombinieren

Bei der Kombination von Text und Datumsfelder geht die Zahlenformatierung verloren.
Daher muss die Formatierung mit

=TEXT(A1;"TTTT TT.MM.JJJJ")
mitgegeben werden.
Es kann dann z.B. folgendes eingegeben werden
In Zelle A1 steht ein Datum. Die Formel
="Das Datum lautet " & TEXT(A1;"TTTT TT.MM.JJJJ")
gibt das Datum mit dem Text aus.


Zufallszahlen erzeugen

Funktion = Zufallszahl()


F9 aktualisiert die Zahlen


Zeiten addieren

Z.B. 8:00 + 9:00+ 12:00 + 13:00 addieren (Format ist hh:mm)

Das Ergebnis Benutzerdefiniert formatieren mit [hh]:mm



Nullwerte in einem Diagramm unterdrücken

Für ein Diagramm. Es liegen für einige Messpunkte keine Werte vor.
Aber Null wäre falsch, da der Meßwert nicht Null ist, sondern nicht vorhanden ist. Daher

statt 0 "#NV" eingeben. NV bedeutet "Nicht verfügbar"


Pfadnamen in einer Mappe einfügen

=Zelle("Dateiname")

Genau so schreiben!

Hinweise: Die Datei muss vorher gespeichert werden. Und die Spalte muss evtl. vergrößert werden, damit der ganze Text hineinpasst.


Letzter Wert in einer Zeile

=INDEX(1:1;ANZAHL2(1:1))
Der letzte Wert in Zeile 1 wird ausgegeben


Letzter Wert in einer Spalte

=INDEX(A:A;ANZAHL2(A:A))
Der letzte Wert in Spalte A wird ausgegeben


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