Rechnen mit Excel - Formeln und Funktionen

Befreiung vom Dogma der Funktionskategorien

Obwohl die integrierte Excel-Hilfe zu den einzelnen Funktionen teilweise sehr ausführlich und viel besser als ihr Ruf ist, scheuen sich die meisten User, sich wirklich mal durch den dichten Dschungel des Funktionskatalogs zu kämpfen. Stattdessen werden Berechnungen in VBA programmiert, obwohl für die aktuelle Problemstellung eventuell die passende Excel-Funktion standardmäßig verfügbar ist. Doch woran liegt das? Ein Grund dafür könnte sein, dass die Kategorisierung der Funktionen nicht besonders hilfreich ist. Statistische Funktionen, was heißt das schon? Muss man Statistiker sein, um damit etwas anfangen zu können? Bei manchen Funktionen mag das der Fall sein, aber Funktionen wie MAX oder MIN kann eigentlich jeder Excel-Anwender gebrauchen. Trotz Kategorisierung sind die Funktionen so bunt durcheinander gewürfelt, dass es einfach nicht möglich ist, einen globalen Überblick zu behalten, geschweige denn jemand anderem zu vermitteln.

Ein Beispiel: In der Kategorie Statistik stehen die Funktionen VARIANZENA, VARIATION und VARIATIONEN unmittelbar untereinander und klingen sehr ähnlich. Funktional haben sie aber überhaupt nichts miteinander zu tun. Die erste ermittelt ein Streuungsmaß, die zweite Werte eines exponentiellen Trends und die dritte gehört in den Bereich der Kombinatorik. Sie passt damit viel besser zur Funktion KOMBINATIONEN, die sich aber als völliger Außenseiter in der Kategorie Math. & Trigonom. tummelt. Auch der Begriff Matrixfunktionen ist kein aussagekräftiger Begriff für die Funktionen, die sich dahinter verbergen.

Um diese Verwirrung zu beseitigen, wollen wir uns von dem Dogma der vorgegebenen Funktionskategorien befreien und Funktionen so gruppieren, wie sie wirklich zusammengehören. Dadurch wird unseres Erachtens ein übergreifendes Verständnis des vollen Funktionsumfangs der Standardfunktionen erheblich erleichtert. Für eine detaillierte Darstellung der kompletten Funktionspalette reicht ein Kapitel natürlich nicht aus. Dies soll nur ein grober Überblick sein und Ihr Interesse für die eine oder andere Gruppe von Funktionen wecken. Wenn Sie dann tiefer in die Materie einsteigen wollen, geben Sie der integrierten Excel-Hilfe eine Chance und beherzigen Sie den abgedroschenen, aber dennoch wahren Satz: Probieren geht über Studieren. Die Funktionen wurden in Excel 2007 insofern aufgewertet, als es nun eine Rubrik innerhalb des Menübands gibt, die die verschiedenen Kategorien anzeigt. In Excel 2010 wurde dann noch eine Unterkategorie "Kompatibilität" ergänzt, in die einige Funktionen anderer Kategorien ausgelagert wurden, die offenbar auf der Abschlussliste stehen (Abb. 3.1).

Abweichend von den vorgegebenen neun Funktionskategorien gliedert Markt + Technik die Standardfunktionen in folgende 22 Fachgebiete (Anm. d. Red.: Wir stellen hier die ersten zwölf Fachgebiete vor. Die Fachgebiete ab Kombinatorik sind in "Excel - das Zauberbuch" von Markt und Technik nachzulesen).

Wirklich neue, nennenswerte Funktionen gibt es seitdem mit SUMMEWENNS, ZÄHLENWENNS, MITTELWERTWENN, MITTELWERTWENNS, WENNFEHLER und AGGREGATE nur sechs. Diesmal integrieren wir aber die ehemaligen Add-in-Funktionen in den Rundflug. Einige davon ließen sich recht gut den vorhandenen 19 Gruppen zuordnen. Für die übrigen wurden drei eigene Pakete geschnürt. Eines davon behandelt ausschließlich Wertpapierfunktionen, ist also besonders für Finanzakrobaten interessant. Die Funktionen der Gruppe Cube, die ausschließlich für die Anbindung von OLAP-Datenbanken gedacht sind, behandeln wir nicht.

1. Datumsfunktionen

2. Zeitfunktionen

3. Textfunktionen

4. Datentypbeschreibung und -umwandlung

5. Rechnen mit Bedingungen

6. Rundungs- und Formatierungsfunktionen

7. Verweisfunktionen

8. Bereichsrückgabefunktionen

9. Mathematik allgemein

10. Lageparameter

11. Streuungsmaße

12. Regressionsrechnung

13. Kombinatorik

14. Statistische Verteilungen

15. Trigonometrie

16. Matrizenrechnung

17. Zinseszins- und Rentenrechnung

18. Abschreibungsmethoden

19. Exoten

20. Wertpapierfunktionen

21. Komplexe Zahlen

22. Umwandlung Zahlensysteme

Datumsfunktionen

Die Funktion DATUM ermittelt aus den Bestandteilen Jahr, Monat und Tag ein Datum. In Excel steht hinter jedem Datum eine fortlaufende Ganzzahl, die der Anzahl der Tage entspricht, die zwischen dem 01.01.1900 und diesem Datum vergangen sind. Mit den Funktionen JAHR, MONAT und TAG kann dieses Datum wieder in die Einzelteile Jahr, Monat und Tag zerlegt werden. Die Funktion DATWERT wandelt ein als String angegebenes Datum in die entsprechende fortlaufende Ganzzahl um. Die Funktion HEUTE ermittelt bei jeder Neuberechnung die fortlaufende Ganzzahl des aktuellen Datums. Die Funktion WOCHENTAG ermittelt eine Zahl von 1 bis 7, die den Tagen einer Woche entspricht.

Darüber hinaus gibt es in der Kategorie Datum sechs neue Funktionen, die aber nichts wirklich Neues bieten, da man sie auch in früheren Versionen durch herkömmliche Funktionen ersetzen konnte. Diese Substitution durch die Veteranen ist zwar nicht mehr wirklich nötig, hilft aber nach wie vor, die Rechenlogik zu verstehen, die dahinter steht. MONATSENDE liefert den letzen Tag des Monats eines angegebenen Datums, wobei noch eine bestimmte Anzahl Monate voraus- oder zurückgerechnet werden kann. MONATSENDE(Datum;0) ergibt dasselbe wie DATUM(JAHR(Datum);MONAT(Datum)+1;0)

In Worte gefasst: Der letzte Tag des laufenden Monats ist der »nullte« Tag des nächsten (+1) Monats. Wird noch ein Tag addiert, hat man stets den ersten Tag des Folgemonats. EDATUM addiert oder subtrahiert ebenfalls zu einem bestimmten Ausgangsdatum eine vorgegebene Anzahl Monate. EDATUM(Datum;Monate) ersetzt die herkömmliche Formel: =MIN(DATUM(JAHR(A3);MONAT(A3)+B3+{1.0};{0.1}*TAG(A3))) BRTEILJAHRE wandelt die Anzahl der ganzen Tage zwischen Ausgangsdatum und Enddatum in Bruchteile von Jahren um.

Wie bei vielen finanzmathematischen Funktionen kann noch die Jahresbasis eingeben werden. Bei einer Jahresbasis von 2 mit 360 Tagen gilt die Gleichung BRTEILJAHRE(A1;E1;2)=(E1-A1)/360 Die Funktion NETTOARBEITSTAGE und deren komplizierte Herleitung mit herkömmlichen Funktionen haben wir bereits in Kapitel 2 Pro-Add-in: neue Freunde demonstriert. Sie gibt die Anzahl der Arbeitstage in einem Zeitintervall zurück. Feiertage in dem Intervall können subtrahiert werden. Welche Feiertage dies sind, muss man aber selbst definieren. ARBEITSTAG macht genau das Gegenteil. Hier wird eine Anzahl Arbeitstage vorgegeben und Excel berechnet das resultierende Enddatum des Intervalls. Feiertage können ebenfalls definiert und berücksichtigt werden. Schließlich gibt es die Funktion KALENDERWOCHE. Da die Funktion nach amerikanischem Standard rechnet, ist sie für Deutschland ungültig. Wir definieren die erste Kalenderwoche eines Jahres als die Woche, in die mindestens vier Tage fallen (ISO 8601). Der amerikanische (der weltweit am häufigsten verbreitete) Standard besagt, dass die erste Kalenderwoche eines Jahres die Woche ist, die den 01. Januar enthält. Der Wochentag des Beginns der nächsten Kalenderwochen in den USA ist der Sonntag. Das führt in manchen Jahren zu der kuriosen Situation, dass es an drei aufeinanderfolgenden Tagen drei unterschiedliche Kalenderwochen gibt. Freitag, der 31.12.1999 ist die KW 53, Samstag, der 01.01.2000 die KW 1 und Sonntag, der 02.01.2000 die KW 2. Die korrekte deutsche Berechnung lautet bei Datumsangabe in A1: =KÜRZEN((-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)

Beispiele zu Zeitfunktionen

Die Funktion ZEIT ermittelt aus den Bestandteilen Stunde, Minute und Sekunde eine Uhrzeit. In Excel steht hinter jeder Zeit eine Bruchzahl zwischen 0 und 1. 0,25 steht für 06:00 Uhr morgens, 0,5 steht für 12:00 mittags und 0,75 für 18:00 Uhr abends. Die Funktionen STUNDE, MINUTE und SEKUNDE zerlegen diese als Bruchzahl ausgedrückte Uhrzeit wieder in ihre Bestandteile Stunde, Minute und Sekunde. Die Funktion ZEITWERT wandelt eine als Zeichenkette angegebene Uhrzeit in die entsprechende Bruchzahl um. Die Funktion JETZT ermittelt bei jeder Neuberechnung die fortlaufende Ganzzahl des aktuellen Datums, analog zu HEUTE zuzüglich der Bruchzahl der aktuellen Uhrzeit. Nur die aktuelle Uhrzeit erhalten Sie durch Subtraktion beider Funktionen: =JETZT()-HEUTE().

Textfunktionen

Die Funktion CODE rechnet jedes Zeichen in die fortlaufende Zahl des ANSI-Zeichensatzcodes um. ZEICHEN wandelt diesen Code in das entsprechende Zeichen um und ist somit die Umkehrfunktion von CODE. Mit Umkehrfunktion ist gemeint, dass bei Verschachtelung beider Funktionen der Eingabewert der inneren Funktion dem Ergebnis der äußeren Funktion entspricht: =CODE(ZEICHEN(66))=66

Die eine Funktion macht quasi die Berechnung der anderen Funktion wieder rückgängig. Die Funktionen SUCHEN und FINDEN suchen innerhalb eines Textes eine Zeichenfolge und geben die Positionsnummer des Suchtextes innerhalb des durchsuchten Textes zurück. Sie unterscheiden sich voneinander dadurch, dass Erstere nicht nach Groß- und Kleinschreibung unterscheidet, Letztere dagegen schon. Die Funktion TEIL gibt den Teil eines Textes zurück, der sich aus der Vorgabe von Positionsnummer und Länge des Teilstrings ergibt. Sie ist in etwa die Umkehrfunktion von FINDEN/SUCHEN. Wenn die Position des zurückzugebenden Teilstrings links oder rechts beginnt, nehmen Sie statt der Funktion TEIL die Funktionen LINKS oder RECHTS.

Häufig ist es sinnvoll, eine dieser Funktionen mit der Funktion LÄNGE zu kombinieren, die die Anzahl Zeichen eines Textes zählt. Mit LINKS(Text;LÄNGE(Text)-3) können Sie beispielsweise die drei ersten Zeichen von rechts abschneiden. Die Funktion VERKETTEN hängt verschiedene Texte aneinander, was allerdings durch das kaufmännische & genauso gut erledigt werden kann. WIEDERHOLEN verkettet ebenfalls Text. Hierbei ist es aber immer der gleiche Text und Sie können nur vorgeben, wie oft er wiederholt werden soll. Die Funktionen KLEIN und GROSS wandeln alle Zeichen eines Textes in Klein- oder Großbuchstaben um. Auf numerische Ziffern oder Sonderzeichen haben diese Funktionen keine Auswirkung. GROSS2 schreibt nur das erste Zeichen jedes Worts in einem Text groß und den Rest klein.

Die Wörter müssen nicht unbedingt durch Leerzeichen getrennt sein. Eine Trennung durch Bindestrich, Unterstrich oder ein anderes Sonderzeichen hat den gleichen Effekt. GLÄTTEN entfernt überflüssige Leerzeichen (mehr als eines nebeneinander) eines Textes und entspricht in etwa den Trimm-Funktionen, die in diversen Programmiersprachen bekannt sind. SÄUBERN entfernt alle nicht druckbaren Zeichen. IDENTISCH prüft, ob zwei Texte identisch sind, und unterscheidet sich von der einfachen Prüfung =Text1=Text2 dadurch, dass sie zwischen Groß- und Kleinschreibung unterscheidet. Für Excel ist standardmäßig ="a"="A"=WAHR. Die Funktion DELTA macht dasselbe, funktioniert aber nur für numerische Zeichen, was ihr jegliche Daseinsberechtigung entzieht. Statt WAHR/FALSCH liefert sie 1/0.
Die Funktion WECHSELN ersetzt eine Zeichenfolge innerhalb eines Textes durch eine
andere. Die Funktion ERSETZEN macht fast das Gleiche. Der Unterschied besteht darin,
dass Sie bei WECHSELN eine Zeichenfolge vorgeben, die innerhalb des Textes gesucht
werden muss. Bei ERSETZEN geben Sie stattdessen die Position und die Länge der Zeichenfolge vor, die ausgetauscht werden soll.

Datentypbeschreibung und -umwandlung

Unter Programmierern gehört es zum elementaren Basiswissen, dass Daten unterschiedliche Datentypen besitzen. Die in Excel relevanten Datentypen sind Zahl, Text (String), boolescher Wert (WAHR/FALSCH) und Fehlerwert. Die Funktion T wandelt ein Argument in einen Text um. Zahlen werden in eine leere Zeichenfolge (Text mit Länge 0) umgewandelt. Die Funktionen N und WERT wandeln ein Argument in eine Zahl um. Da sich ein alphanumerisches Zeichen nicht wirklich in eine Zahl umwandeln lässt (vom Zeichencode einmal abgesehen), liefert WERT in diesem Fall den Fehlerwert #WERT!. N macht stattdessen eine 0 daraus. N eignet sich unter anderem prima dazu, Kommentare in Formeln zu platzieren, ohne Einfluss auf das Ergebnis zu nehmen. Beispiel: =IKV({-10.4.4.4};0)+N ("Diese Formel berechnet den internen Zinsfuß einer Zahlungsreihe") Die Funktionen TYP und FEHLER.TYP geben eine Ganzzahl zurück, die angibt, um welchen Datentyp bzw. Fehlertyp es sich bei einem Argument handelt. Eine ähnliche Aufgabe erfüllen die Funktionen ISTFEHL, ISTBEZUG, ISTFEHLER, ISTKTEXT, ISTLEER, ISTLOG, ISTNV, ISTTEXT und ISTZAHL. Das Ergebnis dieser Funktionen ist aber keine Ganzzahl, sondern ein boolescher Wert: WAHR oder FALSCH. Mit ISTGERADE und ISTUNGERADE können Sie prüfen, ob eine Zahl durch zwei teilbar ist. Sie ignorieren Nachkommastellen und rechnen das Gleiche wie: ISTGERADE: =REST(KÜRZEN(A1);2)=0 ISTUNGERADE: =REST(KÜRZEN(A1);2)=1 VORZEICHEN gibt an, ob eine Zahl positiv, negativ oder null ist.

Die Funktion ANZAHLLEEREZELLEN zählt leere Zellen und Zellen mit der Länge 0. Eine Zelle mit der Länge 0 muss nicht unbedingt leer sein, sie könnte auch einen sogenannten Nullstring ="" enthalten. Die Funktion ISTLEER liefert FALSCH, wenn eine Zelle einen solchen Leerstring enthält. Für die Funktion ANZAHLLEEREZELLEN ist sogar eine Zelle mit der Formel =WENN(1;"") leer und sie zählt deshalb eine 1 für diese Zelle. Sehr vielseitig ist die ehemalige Addin- Funktion UMWANDELN, die eine in einer bestimmten Maßeinheit angegebene Zahl in eine andere Maßeinheit umwandelt. Die Maßeinheiten sind in folgende Kategorien unterteilt:

• Gewichte: z.B. Gramm, Unze

• Entfernung: Meter, Meile, Seemeile, Fuß, Yard, Zoll, Angstrom, Pica

• Zeit: Jahr, Tag, Stunde, Minute, Sekunde

• Druck: Pascal, Atmosphäre; mm Quecksilber

• Kraft: z.B. Newton

• Energie: z.B. Joule, Kalorien, Wattstunde

• Magnetismus: Tesla, Gauss

• Temperatur: Celsius, Fahrenheit, Kelvin

• Flüssigmaße: z.B. Esslöffel, Tasse, Liter, Gallone

Metrische Maße wie Gramm und Meter können außerdem in die Einheitenpräfixe kilo, mega, giga usw. umgewandelt werden. Beispielsweise entspricht eine Seemeile =UMWANDELN(1;"Nmi";"km") = 1,852 Kilometern. Stammen die Parameter Von_Maßeinheit und In_Maßeinheit nicht aus der gleichen Gruppe, gibt es einen Fehlerwert. Logo, denn sieben Esslöffel lassen sich nicht in Grad Fahrenheit umrechnen.

Rechnen mit Bedingungen

Ein Gebiet, das auch allen Programmierern und Datenbankentwicklern sofort ein Begriff ist, ist das Rechnen mit Bedingungen. Mit zentraler Bedeutung fallen einem dazu die Begriffe WENN - UND-ODER - DANN - SONST ein. Die Funktionen UND, ODER und NICHT liefern Wahrheitswerte, die zum Ausdruck bringen, ob eine Bedingung erfüllt ist oder nicht. Die Wahrheitswerte WAHR und FALSCH besitzen gleichnamige Funktionen, die völlig sinnfrei sind und laut Excel-Hilfe nur aus Kompatibilitätsgründen zu anderen Programmen zur Verfügung stehen. =WAHR() liefert WAHR. Das ist so, als gäbe es eine Funktion =EINS(), die den Wert 1 liefert. Die Funktion WENN kann abhängig von einer solchen Bedingungsprüfung eine Dann- Berechnung beziehungsweise eine Sonst-Berechnung durchführen. Die Funktion WAHL kann - im Gegensatz zu den zwei Alternativen (Dann_Wert/ Sonst_Wert) der WENN-Funktion - 254 alternative Berechnungen durchführen. Die Prüfung erfolgt deshalb nicht über die Wahrheitswerte WAHR und FALSCH, sondern über einen fortlaufenden Index von 1 bis 254. Vor Excel 2007 konnte man nur 29 Alternativen eingeben.

Die Funktionen WENN und WAHL überprüfen (standardmäßig) eine Zelle auf eine oder mehrere Bedingungen. Die Funktionen ZÄHLENWENN und SUMMEWENN fragen einen ganzen Bereich ab, können dabei aber nur eine Bedingung prüfen. ZÄHLENWENN zählt alle Zellen, auf die die Bedingung zutrifft; SUMMEWENN summiert für jede Zelle mit erfüllter Bedingung den Zellwert dieser oder benachbarter bzw. gleich indizierter Zellen. Die Datenbankfunktionen DBSUMME, DBANZAHL, DBMAX, DBMIN usw. können beliebig viele Zellen (Datensätze) hinsichtlich beliebig vieler Bedingungen überprüfen. Damit vereinen sie die Möglichkeiten von WENN, SUMMEWENN und ZÄHLENWENN und sind damit sehr mächtig. Außerdem können sie nicht nur zählen und summieren, sondern auch weitere statistische Größen, beispielsweise Standardabweichung und Varianz, berechnen. Leider ist ihre Handhabung etwas umständlich und sie arbeiten nicht autark, da jede unterschiedliche Berechnung einen Bereich in der Tabelle verlangt, in dem die Suchkriterien definiert werden. Aufgrund dieser umständlichen Bedienung sind die Datenbankfunktionen nicht besonders beliebt. Trotzdem besteht eine rege Nachfrage nach Auswertungen mit mehreren Bedingungen. Deshalb wurden die beiden neuen Funktionen SUMMEWENNS und ZÄHLENWENNS geschaffen, die mehrere Bedingungen auf einmal verarbeiten können. Und für die goldene Mitte gibt es mit MITTELWERTWENN und MITTELWERTWENNS nun auch Entsprechungen. Mit MINWENN und MAXWENN hätte man das konsequent fortführen können, hat man aber nicht. Schade, darauf müssen wir wohl bis Excel ???? warten.

Eine geniale Erfindung ist die längst überfällige Funktion WENNFEHLER, mit der ein wesentlich einfacheres Abfangen von Fehlern in Formeln möglich wird. Zum Beispiel beim SVERWEIS. Wenn diese Funktion einen Suchbegriff nicht finden kann, liefert sie den Fehlerwert #NV. Stattdessen möchte man eventuell lieber den Text »nicht vorhanden« sehen. Bisher hätte man in diesem Fall wie folgt formuliert: =WENN(ISTNV(SVERWEIS("x";A:C;3;0));"nicht vorhanden"; SVERWEIS("x";A:C;3;0)) Zum einen nervt die doppelte Eingabe desselben Formelteils. Zum anderen bindet die redundante Berechnung natürlich unnötige CPU-Ressourcen. Mit der neuen Funktion genügt stattdessen =WENNFEHLER(SVERWEIS("x";A:C;3;0); "nicht vorhanden") Der redundante Formelteil wird nicht mehr benötigt, Excel weiß auch so, was zu tun ist.

Rundungs- und Formatierungsfunktionen

Die Funktion TEXT formatiert eine Zahl und wandelt das Ergebnis in einen Text um. Fast alle Zahlenformate, die sich über das Dialogfeld Zellen formatieren einstellen lassen, können Sie ebenso mit dieser Funktion erzeugen. Somit können Sie mit dieser Funktion unter anderem auch runden. Excel bietet eine ganze Reihe weiterer Rundungsfunktionen, die auf den ersten Blick oft gleiche Ergebnisse liefern, aber trotzdem feine Unterschiede haben, die man nicht außer Acht lassen darf. Die Funktionen RUNDEN, FEST und DM runden nach einer beliebigen Anzahl Stellen auf oder ab. Ab 0,5 wird stets aufgerundet. Sie liefern das identische Ergebnis, außer dass FEST und DM Texte liefern. DM liefert zusätzlich das gemäß Ländereinstellung aktuell gültige Währungssymbol. Bei DM setzt die Multiplikation mit 1 voraus, dass das Währungssymbol mit einer Textfunktion abgeschnitten wurde. Die nicht dokumentierte Funktion USDOLLAR macht exakt dasselbe.

Die Funktionen ABRUNDEN, KÜRZEN, UNTERGRENZE und GANZZAHL runden nach unten ab. Bei ABRUNDEN und KÜRZEN kann eine beliebige Anzahl Stellen vorgegeben werden. Enthält das Argument Anzahl_Stellen eine negative Zahl, kann auf volle Zehnerpotenzen (vor dem Komma) gerundet werden. Mit UNTERGRENZE kann man noch feiner justieren, da auf das nächstliegende Vielfache einer beliebig definierbaren Schrittweite abgerundet werden kann. GANZZAHL schneidet einfach alle Nachkommastellen ab. Die gleiche Funktion mit zwei G, also GGANZAHL führt die völlig banale Prüfung =(A1>=B1)*1 durch. QUOTIENT ist keinen Deut nützlicher und liefert lediglich den ganzzahligen Teil einer Division, genau wie GANZZAHL(Zahl1/Zahl2) AUFRUNDEN und OBERGRENZE runden nach oben auf und funktionieren ansonsten wie ihre Pendants zum Abrunden. VRUNDEN rundet auf das beliebige Vielfache einer Zahl auf oder ab und macht damit das Gleiche wie =RUNDEN(Zahl/Vielfaches;0)*Vielfaches VRUNDEN liefert stets entweder das Ergebnis von UNTERGRENZE, falls abgerundet wird, oder das von OBERGRENZE, falls aufgerundet wird. GERADE und UNGERADE runden positive Zahlen auf die nächste gerade bzw. ungerade Zahl auf und im negativen Bereich ab. ABS liefert den Absolutwert einer Zahl.

Verweisfunktionen

Schauen Sie noch einmal auf die angebotenen Funktionskategorien in der ersten Abbildung dieses Kapitels. Fällt Ihnen etwas auf? Excel spricht jetzt auch von einer Gruppe Verweisfunktionen (Nachschlagen und Verweisen) und gibt damit indirekt zu, dass der Name der Kategorie Matrix irreführend ist. Hat sich Microsoft das etwa von uns abgeschaut? Immerhin haben wir diese Funktionsgruppe auch in der ersten Auflage von Excel - Das Zauberbuch schon so benannt ;-). Verweisfunktionen durchsuchen Bereiche nach Suchkriterien. Wer mit großen Datenmengen hantiert und verschiedene Datentabellen miteinander in Beziehung setzen muss, benötigt Verweise. In einer Datenbank geschieht dies durch die Verknüpfung von Datenbanktabellen über (in der Regel) sogenannte 1:n-Beziehungen. In Excel wird diese Methodik mit Verweisfunktionen durchgeführt. SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis) durchsucht die linke Spalte eines Bereichs oder einer Matrix nach einem Suchkriterium und gibt vom ersten Treffer eine rechts benachbarte Zelle zurück. WVERWEIS macht das Gleiche, nur 90 Grad versetzt, durchsucht also die oberste Zeile eines Bereichs oder einer Matrix und gibt einen Wert zurück, der sich in derselben Spalte wie der Treffer befindet. Bei beiden Funktionen kann vorgegeben werden, ob nur dann ein Ergebnis geliefert werden soll, wenn eine genaue Übereinstimmung mit dem Suchkriterium besteht. Soll auch ein Ergebnis geliefert werden, wenn es keine genaue Übereinstimmung gibt, muss die Suchspalte oder -zeile aufsteigend sortiert sein. Dann wählt die Funktion den größten Treffer aus, der kleiner oder gleich dem Suchkriterium ist.

Diese Variante ist dann sinnvoll, wenn innerhalb von Intervallen gesucht werden soll. Ein klassischer Anwendungsfall ist hierbei die umsatzabhängige Provisionsvergütung oder die Schulnote, die von der erreichten Punktzahl abhängig ist. VERWEIS(Suchkriterium;Suchvektor;Ergebnisvektor) ohne S und W erfüllt fast den gleichen Zweck und ist dabei hinsichtlich der Suchrichtung etwas flexibler. Sie kann zeilen- oder spaltenweise suchen, je nach Vorgabe. Sie könnte sogar in einer Zeile suchen, das Ergebnis aber aus einer Spalte liefern. Andererseits muss bei VERWEIS der Suchvektor stets aufsteigend sortiert sein, um ein verlässliches Ergebnis zu gewährleisten. Geliefert wird immer der bestmögliche Treffer, der kleiner oder gleich dem Suchkriterium ist. Da in der Mehrzahl der Fälle allerdings nach einer genauen Übereinstimmung gesucht wird, sind die Einsatzgebiete von VERWEIS begrenzt.

VERGLEICH (Suchkriterium;Suchmatrix;Vergleichstyp) durchsucht ebenfalls wahlweise eine Spalte (vertikale Matrix) oder eine Zeile (horizontale Matrix) und sucht entweder nach einer genauen Übereinstimmung oder dem nächstgelegenen Treffer. Zusätzlich zu (W/S)VERWEIS kann der Suchvektor hier auch absteigend sortiert sein, dann wird der kleinste Eintrag gewählt, der größer oder gleich dem Suchkriterium ist. In diesem Fall muss die Suchmatrix absteigend sortiert sein. Bei dieser Funktion muss keine Ergebniszeile oder -spalte angegeben werden, da sie als Ergebnis eine Ganzzahl liefert, die die Position des Suchkriteriums innerhalb der zu durchsuchenden Zeile (horizontale Matrix) oder Spalte (vertikale Matrix) darstellt. Die Datenbankfunktion DBAUSZUG(Datenbank;Datenbankfeld;Suchkriterien) sucht immer vertikal und kann nach links oder rechts blicken. Sie ist die einzige Verweisfunktion, die standardmäßig mehrere Bedingungen verarbeiten kann. Sie sucht stets nach einer genauen Übereinstimmung. Wenn die Suchkriterien aber auf mehrere Datensätze zutreffen, nimmt sie nicht den ersten Treffer (wie die anderen Verweisfunktionen), sondern liefert eine Fehlermeldung.

Beispiele zu den Bereichsrückgabefunktionen

Die Funktionen dieser Gruppe sind 100 Prozent Excel-spezifisch. Zeilen, Spalten und Zellen beschreiben Excel-Bereiche bzw. -Tabellen, die sozusagen das lebensnotwendige Skelett einer jeden Excel-Anwendung darstellen. Mit den Funktionen BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten; Höhe;Breite), INDEX (Bezug;Zeile;Spalte) und INDIREKT(Bezug) werden Excel-Bereiche definiert und verändert. Die ersten beiden können aus einem vorgegebenen Ausgangsbereich eine ganze Spalte, Zeile oder einzelne Zelle zur weiteren Verarbeitung herauspicken. Nur bei INDEX kann statt des Bereichs auch eine Matrix(-konstante) vorgegeben werden. Mit BEREICH.VERSCHIEBEN können Bereiche sogar völlig neu dimensioniert und verschoben werden. INDIREKT wandelt eine Bereichsangabe im Textformat in einen Bereich um. Dabei kann der Bereich (Bezug) direkt als Text oder aber auch als Bezug auf eine Zelle, die den Bereich in Textform enthält, angegeben werden. Die Bereichsangabe kann auch auf eine nicht aktive Tabelle und sogar auf eine andere Arbeitsmappe verweisen, die aber geöffnet sein muss, um ein Ergebnis zu erhalten.

MTRANS transponiert einen Bereich oder eine Matrix, macht also aus Spalten Zeilen und aus Zeilen Spalten. Die Funktionen ZEILE und SPALTE liefern die fortlaufende Ganzzahl der Zeile bzw. Spalte des angegebenen Bezugs. Enthält der angegebene Bezug mehrere Zeilen oder Spalten, lassen sich flexibel einsetzbare Zahlenfolgen erzeugen, worauf im Laufe dieses Buchs noch sehr ausführlich eingegangen wird. ZEILEN und SPALTEN geben die Anzahl der Zeilen und Spalten des angegebenen Bereichs zurück. ADRESSE gibt aus der Angabe von Zeilen- und Spaltennummer eine Zelladresse im Textformat aus. Da INDIREKT eine solche Zelladresse in Textform erwartet, können diese beiden Funktionen gut miteinander kombiniert werden.

Die Funktion BEREICHE zählt die innerhalb eines Bezugs aufgeführten Bereiche.

Mathematik allgemein

Diese Funktionen gehören auch auf jeden handelsüblichen Taschenrechner. EXP liefert eine Potenz der Eulerschen Zahl e = 2,718 und LN kehrt diese Berechnung um, indem sie den natürlichen Logarithmus zur Basis e liefert. LOG10 liefert den Logarithmus einer Zahl zur Basis 10 und LOG zu einer beliebigen Basis. Auch die Funktionen POTENZ, PRODUKT, SUMME und WURZEL können kaum verbergen, was sie im Schilde führen.

Ein besonderes Augenmerk verdient die Funktion REST, die den Rest einer Division
zurückgibt.

=REST(5;3)=2 denn 5/3 = 1 + 2/3
=REST(26;7)=5 denn 26/7 = 3 + 5/7

Das scheint auf den ersten Blick nichts Besonderes zu sein, doch diese Funktion ist sehr nützlich. Mit dem Divisor 7 ersetzt sie beispielsweise die Funktion WOCHENTAG vollständig, denn es gilt =REST(Datum-2;7)+1 = WOCHENTAG(Datum;2) Beides nummeriert die Wochentage von Montag bis Sonntag von 1 bis 7 durch. Aber das ist nur eine von vielen Facetten dieses Allrounders. GGT und KGV sind zwei leistungsstarke neue Funktionen (ehemalige Add-ins), die man bis Excel 2003 nur sehr mühsam und eingeschränkt mit Standardfunktionen nachbauen konnte. Sie ermitteln die größten gemeinsamen Teiler bzw. kleinsten gemeinsamen Vielfache von bis zu 255 Zahlen zurück.

=KGV(2;4;8;12;16) = 48
=GGT(51;85;136)=17

Lageparameter

Damit kommen wir zu den ersten statistischen Funktionen, die aber kein Hexenwerk irgendwelcher Spezialisten sind, sondern auch von Otto Normalverbraucher verwendet werden können. Unter Lageparametern versteht man einfach zu verstehende und in der Regel vertraute statistische Kennzahlen, von denen Excel folgende zur Berechnung anbietet: MAX liefert den größten Wert einer Datenreihe, MIN den kleinsten. Wenn Sie auch der zweitgrößte oder zweitkleinste Wert interessiert, nehmen Sie die Funktionen KGRÖSSTE(Matrix;k) bzw. KKLEINSTE(Matrix;k). Die Funktion RANG(Zahl;Bezug; Reihenfolge) stellt die Umkehrfunktion dieser Funktionen dar. Bei ihr geben Sie einen Wert der Datenreihe vor. Sie berechnet dann, der wievielt größte oder kleinste er in dieser Datenreihe ist. Wenn statt des absoluten Rangs der relative RANG gewünscht ist, erledigt dies die Funktion QUANTILSRANG, die den Rang der gesuchten Zahl in das Verhältnis zur Anzahl aller Werte stellt. Wie genau sie rechnet, veranschaulicht folgende Gleichung:

=QUANTILSRANG(Werte;x)=(RANG(x;Werte;1)-1)/(ANZAHL(Werte)-1)

Auch QUANTILSRANG hat eine Umkehrfunktion namens QUANTIL, die quasi wie KKLEINSTE funktioniert, mit dem Unterschied, dass sie statt eines Absolutwerts keine Prozentzahl alpha erwartet. Die Umkehrung beider Funktionen erhält man mit:

=QUANTIL(Werte;QUANTILSRANG(Werte;X;15))=X

Der MEDIAN berechnet die Zahl, die in der Mitte aller Werte der Datenreihe liegt. Das heißt, die eine Hälfte der Werte ist kleiner als der Median und die andere Hälfte der Werte ist größer. Bei einer geraden Anzahl der Werte innerhalb der Datenreihe errechnet er sich aus dem arithmetischen Mittel der beiden Werte, die am nächsten in der Mitte liegen. Die Funktion QUARTILE teilt die beiden durch den Median geteilten Hälften in zwei weitere Hälften, so dass es drei Quartile (unteres, mittleres und oberes) gibt. Die Funktion liefert zwar fünf Werte, doch der erste und fünfte Wert sind überflüssig, denn die entsprechen dem Minimum bzw. dem Maximum. Zwischen Quartilen und Quantilen besteht der Zusammenhang:

=QUARTILE(A2:A22;0)=MIN(A2:A22)
=QUARTILE(Werte;1)=QUANTIL(Werte;25%)
=QUARTILE(Werte;2)=QUANTIL(Werte;50%)
=QUARTILE(Werte;3)=QUANTIL(Werte;75%)
=QUARTILE(A2:A22;4)=MAX(A2:A22)

Der MODALWERT liefert den häufigsten Wert einer Datenreihe. Wenn jeder Wert einmalig ist, liefert er eine Fehlermeldung. Weiterhin gibt es eine Reihe von Durchschnittsfunktionen. MITTELWERT liefert das arithmetische Mittel, das man auch mit

=SUMME(Werte)/ ANZAHL(Werte)

darlegen könnte.

GEOMITTEL liefert das geometrische Mittel mit dem Pendant =PRODUKT(Werte)^(1/ANZAHL(Werte)) und HARMITTEL das harmonische Mittel einer Datenreihe, die gleichfalls der Logik {=1/(SUMME(1/Werte)/ANZAHL(Werte))} gehorcht.

GESTUTZMITTEL liefert das arithmetische Mittel einer Datenreihe, bei der Ausreißer an den Rändern unberücksichtigt bleiben. Die Funktionen MITTELWERT, MIN und MAX gibt es in einer zweiten Ausführung, bei der dem Funktionsnamen A angehängt wird, also MITTELWERTA, MINA und MAXA. Diese Funktionen unterscheiden sich dadurch, dass Texte und Wahrheitswerte anders interpretiert werden. In der Standardform werden Texte und Wahrheitswerte ignoriert. Mit dem A-Anhang wird WAHR als 1 und FALSCH und Text als 0 interpretiert.

Streuungsmaße

Lageparameter reichen oft nicht aus, um Merkmale einer Datenreihe befriedigend zu beschreiben. Angenommen, Sie haben ein Steak gegrillt, das auf der einen Seite noch roh und blutig, aber auf der anderen Seite total verkohlt ist. Im Durchschnitt ist es gut durch, aber es schmeckt garantiert scheußlich. Zwei Datenreihen können gleiche Extrempunkte oder Mittelwerte haben, aber trotzdem eine völlig unterschiedliche Streuung aufweisen, die mit Hilfe von Streuungsparametern bestimmt werden.

Die Funktion HÄUFIGKEIT(Daten;Klassen) teilt die Werte der Datenreihe in Intervalle bzw. Häufigkeitsklassen ein. Das Array {6.9} im Argument Klasse teilt die Datenreihe in drei Klassen auf. Die erste Klasse enthält die Werte <=6, die zweite Klasse enthält alle Werte >6 und <=9 und die dritte Klasse enthält die übrigen Werte >9. Als Ergebnis liefert die Funktion die Häufigkeit der Werte in den drei Klassen. Streuungsmaße messen durchschnittliche Abweichungen der Werte einer Datenreihe von ihrem Mittelwert. Um zu verhindern, dass sich positive und negative Abweichungen neutralisieren, hat man zwei Möglichkeiten. Entweder man betrachtet nur die Absolutwerte der Abweichungen (so macht es die Funktion MITTELABW zur Berechnung der mittleren, absoluten Abweichung) oder man quadriert die Abweichungen. Nach diesem Prinzip wird die Varianz berechnet und weiterhin die Standardabweichung, die die Wurzel der Varianz ist.

Varianz=SUMME((Werte-MITTELWERT(Werte))^2)/ANZAHL(Werte)

Die Berechnung der Varianz gibt es in den Alternativen VARIANZEN, VARIANZENA, VARIANZ und VARIANZA. Die ersten beiden gehen davon aus, dass die Datenreihe aus einer vollständigen Grundgesamtheit besteht. Die letzten beiden unterstellen, dass die beobachteten Werte lediglich eine Stichprobe der Grundgesamtheit darstellen. Die Alternativen mit der A-Erweiterung interpretieren WAHR als 1 und FALSCH und Text als 0. Multipliziert man die Varianz mit der Anzahl der Werte der Datenreihe, so erhält man die Summe der quadrierten Abweichungen. Den gleichen Zweck erfüllt die Funktion SUMQUADABW. Zieht man von allen vier Varianzfunktionen die Quadratwurzel, erhält man ihre Pendants zur Berechnung der Standardabweichung STABWN, STABWNA, STABW und STABWA. KOVAR berechnet die Varianz von zwei zueinander in Beziehung gesetzten Datenreihen und wird beispielsweise in der Kapitalmarkttheorie (Capital Asset Pricing Model) benötigt. Rechnerisch entspricht KOVAR(DatA;DatB) dem Ergebnis aus {=MITTELWERT((DatA-MITTELWERT(DatA))*(DatB-MITTELWERT(DatB)))} Die folgende Abb. 3.6 vergleicht Datenreihen mit gleichem arithmetischen Mittel, aber unterschiedlichen Streuungen. Wie zu sehen ist, sind die Streuungsmaße der Wertereihe B kleiner, da ihre Werte näher am arithmetischen Mittelwert liegen.

Regressionsrechnung

Die Regressionsrechnung ist ein statistisches Verfahren, das die Abhängigkeit einer Datenreihe (Y-Werte) von einer zweiten Datenreihe (X-Werte) analysiert und einen funktionalen Zusammenhang zwischen beiden Größen herstellt. Aus den daraus erlangten Kenntnissen sollen zukünftige Prognosen bzw. Trends abgeleitet werden können. Excel stellt eine Gruppe von Funktionen zur Verfügung, die sich dieser Thematik annehmen und sich sehr gut ergänzen und teilweise auch substituieren lassen.

Die meisten Funktionen gehen standardmäßig von einem linearen Zusammenhang zwischen abhängiger und unabhängiger Größe aus. Es werden folglich lineare Trends unterstellt. Abb. 3.7 zeigt in Spalte A die unabhängige Größe und in Spalte B die abhängigen Messwerte, die in einem Punkt (XY)-Diagramm dargestellt werden. Die Gerade stellt den linearen Trend dieser Messwerte dar.

Die Funktion STEIGUNG(Y_Werte;X_Werte) gibt die Steigung der Trendgeraden zurück. ACHSENABSCHNITT(Y_Werte;X_Werte) liefert den Y-Wert, an dem die Gerade die Y-Achse schneidet. Die Funktion RGP(Y_Werte; X_Werte) liefert ein Array aus zwei Werten in obiger Form, die ebenfalls die Steigung und den Y-Achsenschnittpunkt bestimmen. Darüber hinaus besitzt sie noch zwei weitere optionale Parameter, über die weitere statistische Kennzahlen der Trendfunktion abfragbar sind.

Die Funktion TREND berechnet aus vorgegebenen X-Werten einzelne Y-Werte der Trendfunktion, was natürlich auch über Steigung und Y-Achsenschnittpunkt ableitbar wäre: =TREND(x)=STEIGUNG*x+ACHSENABSCHNITT Die Funktion SCHÄTZER(x; Y_Werte; X_Werte) macht genau das Gleiche wie TREND, nur dass die Parameter in einer etwas anderen Reihenfolge verlangt werden. TREND hat aber noch ein paar spezielle Tricks auf Lager, die Sie mit SCHÄTZER nicht machen können, bleiben Sie deshalb lieber gleich bei TREND. Die Funktionen BESTIMMTHEITSMASS(Y_Werte; X_Werte), KORREL(Matrix1; Matrix2) und PEARSON(Matrix1; Matrix2) drücken aus, wie stark die Y-Werte überhaupt von den X-Werten abhängig sind. Die beiden Letztgenannten liefern stets ein identisches Ergebnis. Werden beide quadriert, ergibt sich das BESTIMMTHEITSMASS. Bei rein zufälligen Y-Werten, die absolut nichts mit den X-Werten zu tun haben, liefern alle drei Funktionen einen Wert nahe 0. Bei vollständiger Abhängigkeit liefern sie den Wert +1. KORREL und PEARSON können auch eine negative Korrelation von bis zu -1 darstellen. Da die Funktion BESTIMMTHEITSMASS deren Quadrat ist, liegt ihr Ergebnis im Bereich von 0 und 1.

Es besteht übrigens auch ein Zusammenhang zwischen diesen Funktionen und der im vorherigen Abschnitt erwähnten Kovarianz und der Standardabweichung: =KORREL(WerteA;WerteB)*STABWN(WerteA)*STABWN(WerteB) =KOVAR(WerteA;WerteB) STFEHLERYX(Y_Werte;X_Werte) ist ein weiteres Maß zur Bestimmung der Abhängigkeit zwischen Y-Werten und X-Werten. Je stärker die Abhängigkeit der Y-Werte ist, desto kleiner ist das Ergebnis dieser Funktion. Im Extremfall einer vollkommenen Abhängigkeit liefert sie #DIV/0!. =STFEHLERYX({5.10.15};{1.2.3})=#DIV/0! Der Zusammenhang zwischen Y-Werten und X-Werten muss nicht immer linear sein. Statt einer linearen Funktion y=a+b*x ist auch eine polynomische Funktion y=a + b*x + c*x^2+d*x^3+… darstellbar. Werden die x-Werte in den Funktionen RGP und Trend mit den Potenzen der Polynomfunktion ^{1.2.3…} verquickt, liefern sie die richtigen Koeffizienten bzw. y-Werte des Funktionsgraphen, wie Abb. 3.8. Die Messwerte werden hier durch eine polynomische Trendfunktion 3. Ordnung angenähert. Neu dazu gesellt sich die Funktion POTENZREIHE, mit der man zwar nicht unmittelbar Regressionsrechnungen durchführt, die aber zusammen mit RGP und TREND ein sich schön ergänzendes Dreigestirn bildet. TREND ermittelt ja Y-Werte aus vorhandenen X/Y-Werten. POTENZREIHE kann dieselben Y-Werte aus den Koeffizienten herleiten, die RGP als Ergebnis liefert. Dass wir uns hier schon sehr nahe am Bereich der Finanzmathematik befinden, demonstriert der Zusammenhang POTENZREIHE(1/(1+5%);1;1;Zahlungsreihe) =NBW(5%;Zahlungsreihe) Mit beiden Funktionen kann gleichermaßen der Barwert einer Zahlungsreihe gebildet werden. Dies soweit nur als kleiner Appetithappen auf Ausführungen im Vertiefungsteil dieses Buchs.

Noch mal zurück zur Trendrechnung. Handelt es sich weder um einen linearen noch um einen polynomischen Trend, sondern um einen exponentiellen Zusammenhang zwischen unabhängiger und abhängiger Größe, werden die Funktionen RGP und TREND durch die Funktionen RKP und VARIATION ersetzt, die ansonsten gleichermaßen zu handhaben sind. Sie beschreiben Wachstumsfunktionen. RGP und RKP liefern jeweils Koeffizienten der Trendfunktion, TREND und VARIATION einzelne Y-Werte oder eine ganze Reihe von Y-Werten. Die Formeln =VARIATION({ 1 . 1,1 . 1,21 . 1,331 };{ 0 . 1 . 2 . 3 };4) = 1,4641 =RKP({ 1 . 1,1 . 1,21 . 1,331 };{ 0 . 1 . 2 . 3 }) = {1,1.1} ... sind so zu interpretieren: Ein Taler wächst in jedem Zeitabschnitt um den Faktor 1,1 (er wird mit 10% verzinst). Nach vier Perioden ist er auf 1,4641 Taler angewachsen. (PC-Welt)