Smarte Tabellen
Excel-Funktionen clever nutzen
Importierte Daten bereinigen und ins richtige Zahlenformat umwandeln oder auf einfache Weise Sportergebnisse auswerten: Man kann mit Excel auch ohne Programmierkenntnisse viele Routineaufgaben meistern.
Beim Import von Daten in Tabellen kann vieles passieren – bisweilen entsteht ein ziemliches Durcheinander. So wimmelt es zum Beispiel vor überflüssigen Leerzeichen oder Zeilenumbrüchen, die den Inhalt womöglich noch auf mehrere Zellen aufteilen. Einige Add-ons stellen spezielle Funktionen bereit, um die Daten zu bereinigen. Doch das klappt auch ohne Zusätze oder Programmierung mit den eingebauten Funktionen von Excel.
Beim Übertragen von externen Daten in Tabellen können Steuer- und Sonderzeichen übernommen werden, die zwar nicht sichtbar sind, sich aber trotzdem auswirken. Bei Vergleichs- oder Suchoperationen kann Excel diese finden und Ergebnisse anzeigen, die dadurch zwar für Excel, jedoch nicht für den Nutzer nachvollziehbar sind. Bereinigen Sie solchen Textmüll mit der Funktion SÄUBERN(Text). In den Klammern gibt man die Zelle oder einen Bereich mit Text an. In Anführungszeichen können Sie ihn auch direkt in der Funktion übergeben.
Häufig werden überflüssige Leerzeichen übertragen. Diese jedes Mal per Hand zu entfernen wäre eine Sisyphos-Arbeit. Die Funktion GLÄTTEN(Text) übernimmt die Aufgabe. Sie können jede Zelle einzeln glätten und den Bereich über das Ausfüllkästchen erweitern, das in selektierten Zellen rechts unten als kleines Quadrat erscheint, oder direkt eine Matrixformel angeben.
Dabei wird die Liste in einen neuen Bereich eingefügt. Markieren Sie dazu einen gleich großen Bereich, zum Beispiel von A1 bis C11. Geben Sie die Funktion GLÄTTEN(A1:C11) zum Beispiel in die Zelle E1 ein und erstellen Sie über die Tasten Strg+Umschalt+Eingabe eine Matrixformel. Innerhalb der Zielmatrix findet sich nun eine Kopie der Liste, in der die überflüssigen Leerzeichen entfernt sind.
Möchten Sie eine angelegte Matrix bearbeiten, drücken Sie wiederum die Tasten Strg+Umschalt+Eingabe.
Die Funktion WECHSELN() erfüllt denselben Zweck. Dabei tauschen Sie einfach alt gegen neu aus. Das ist zum Beispiel nützlich, um in Zellen vom Benutzer eingegebene Zeilenumbrüche im Text zu entfernen, wenn diese in der Tabelle nicht gewünscht sind. Solche Zeilenumbrüche markiert Excel mit dem ASCII-Zeichen 10, sodass die Formel für einen Text in Zelle A1 folgendermaßen lautet:
=WECHSELN(A1;ZEICHEN(10);" ")
Die Funktion ersetzt jeden Zeilenumbruch durch ein Leerzeichen. Sie können genauso gut Buchstaben, Sonderzeichen und Umlaute austauschen, etwa „ue“ durch „ü“. Wenn Sie die Zelle mit der Formel selektieren, können Sie sie über das Ausfüllzeichen auf die Zeilen darunter anwenden. Dies gilt für alle weiteren Beispiele, die sich auf eine bestimmte Zelle beziehen.
Auf Punkt und Komma achten
Fehler machen ist menschlich – schnell gibt man bei der Zahleneingabe statt eines Kommas einen Punkt oder statt eines Dezimalpunkts ein Leerzeichen ein. Rasch ist aus der eigentlichen Zahl ein Text geworden, mit dem Excel nicht rechnen kann. Die Funktion ZAHLENWERT() ignoriert Leerstellen, auch wenn diese sich innerhalb der Zahl befinden. So wird aus dem Text „7 777“ die Zahl 7777. Beim Umwandeln des Textes in Zahlen berücksichtigt Excel bei Bedarf auch falsche Dezimaltrennzeichen und macht über die Formel ZAHLENWERT(A1;“.“;“,“) aus dem Text „77.77“ die Zahl 77,77. Über eine Matrixfunktion lassen sich auch Spaltenbereiche konvertieren:
{=ZAHLENWERT(C4:C11;".";",")}
Ketten kombinieren
In Excel gibt es einige Wege, um Zellen und Text miteinander zu verbinden. Mit dem kaufmännischen Und (Ampersand) lassen sich die Inhalte mehrerer Zellen verketten. Anstelle von Zellbezügen können Sie auch Texte in Anführungszeichen eingeben:
="Der Umsatz am "&A1&:
." beträgt für die Filiale "&
.B2&" "&B3&" Euro."
Doch statt des Datums wird Excel hier eine Zahl anzeigen. Um das zu ändern, binden Sie die Funktion TEXT(A1;"TT.MM.JJ") mit gewünschtem Datumsformat ein:
="Der Umsatz am "&TEXT(A1;:
."TT.MM.JJ")&:
."beträgt für die Filiale "&B4&:
." "&C4&" Euro."
Auf die Eingabe des Ampersand-Zeichens kann man auch ganz verzichten, wenn man die VERKETTEN()-Funktion einsetzt:
=VERKETTEN("Der Umsatz am ";A1;:
."für die Filiale ";B2;:
." beträgt ";B2;" ";B3;" Euro."
Die Funktion TEXTKETTE() ist mit VERKETTEN() identisch.
Mit der Funktion TEXTVERKETTEN() können Sie die einzelnen Angaben durch Trennzeichen trennen. Dabei können Sie den Inhalt hunderter Zellen in einer Formel berücksichtigen, indem Sie die Zellbereiche oder komplette Spalten und Zeilen angeben. Und falls Sie Texte zigfach wiederholen möchten, geben Sie einfach an:
=WIEDERHOLEN(Text;Multiplikator)
Sportliche Ergebnisse
Excel eignet sich sehr gut, um Sportergebnisse auszuwerten. Wenn Sie zum Beispiel joggen und Ihre Zeiten mit Excel verwalten, dann können Sie die Tabellenkalkulation nutzen, um die Tage mit den besten und schlechtesten Zeiten zu ermitteln. Excel bietet dazu die Funktionen KGRÖSSTE() und KKLEINSTE() an. Damit lassen sich nicht nur der beste und schlechteste Wert ermitteln, sondern auch die Ränge der besten drei Lauftage anzeigen. Da hier die schnellste Zeit der beste Wert ist, setzen Sie die Funktion KKLEINSTE(Liste;1) ein. Als zweites Argument können Sie auch eine Zelle übergeben. Für den zweitbesten Wert folgt die „2“ und so weiter. Die Angabe der Wochentage erfolgt dann kombiniert über die Funktionen INDEX() und VERGLEICH():
=INDEX(Liste;:
.VERGLEICH(E4;Liste;0);1)
Selbstverständlich können Sie auch andere sportliche Ergebnisse auswerten und statt wie hier die Wochentage zum Beispiel die besten Sportler eines Waldlaufs ermitteln.
Reiner Zufall
Manchmal brauchen Sie kein exaktes Ergebnis, sondern eher ein zufälliges. Mit der Funktion ZUFALLSZAHL() lässt sich eine pseudo-zufällige Zahl zwischen 0 und 1 bilden. Die Funktion besitzt keine Parameter und liefert eine Dezimalzahl zurück. Wenn Sie einen größeren Zahlenbereich benötigen, multiplizieren Sie das Ergebnis mit der oberen Grenze. So liefert ZUFALLSSZAHL()*100 eine Zahl zwischen 0 und 100.
Durch Kombination mit der Funktion RUNDEN() lässt sich das Ergebnis auf eine bestimmte Nachkommastelle runden, wobei der zweite Parameter die Anzahl der Dezimalstellen spezifiziert. So erzeugt die erste Formel eine Zufallszahl zwischen 0 und 100 ohne Nachkommastellen und die zweite eine Zahl zwischen 0 und 10.000.000:
=RUNDEN(ZUFALLSZAHL()*100;0)
=RUNDEN(ZUFALLSZAHL()*10.000.000;0)
Über die Taste F9 erfolgt stets eine Neuberechnung.
Die Funktion ZUFALLSBEREICH() generiert ebenfalls Zufallszahlen, erzeugt diese aber anders als ZUFALLSZAHL() in einem beliebigen Bereich, deren untere und obere Grenze die beiden Argumente vorgeben:
=ZUFALLSBEREICH(Untere_Zahl;:
.Obere_Zahl)
Die Funktion liefert nur ganze Zahlen, die Grenzen dürfen aber auch im negativen Bereich liegen. So liefert die folgende Funktion eine Zahl zwischen –100 und 100:
=ZUFALLSZAHL(-100;100)
Um Zahlen beispielsweise mit einer Nachkommastelle zu erzeugen, dividieren Sie das Ergebnis durch 10:
=ZUFALLSBEREICH(10;300)/10
Möchten Sie den Zufallsbereich für einen markierten Bereich angeben, drücken Sie die Tasten Strg+Eingabe.
Die Funktion lässt sich mit anderen Funktionen prima kombinieren. So ergibt =HEUTE()+ZUFALLSBEREICH(0;30) ein zufälliges Datum innerhalb der nächsten 30 Tage.
Wochentage von Geburtstagen
Es ist jedes Jahr das gleiche, es sei denn, Sie sind in einem Schaltjahr am 29. Februar geboren. Wenn Sie wissen möchten, an welchem Wochentag Sie Geburtstag feiern können, brauchen Sie keinen Blick auf den Kalender zu werfen. Stattdessen lassen Sie sich von Excel den Wochentag Ihres Geburtstages anzeigen. Dies funktioniert mit der Funktion TEXT(Zahl;Formatcode), die Zahlen mithilfe von Formatcodes besser lesbar anzeigt und diese bei Bedarf mit Text und Symbolen kombiniert. Bei der Angabe eines Datums setzt die Funktion dieses in einen Wochentag um. Tippen Sie dazu in Zelle A1 Ihr Geburtsdatum und setzen Sie woanders folgende Formel ein:
=TEXT(A1;"TTTT, TT. MMMM")
Als Ergebnis erscheint zum Beispiel „Sonntag, 22. Dezember“. Weitere Formatcodes finden sich in der Hilfe und im Dialog „Zellen formatieren“ unter „Benutzerdefiniert“. Nun können Sie Texte und Zellen leicht verketten:
C1: ="Mein Geburtsdatum "&A1&:
." fällt dieses Jahr auf einen "&:
.TEXT(A1;"TTTT")&"."
Datum mit Kalenderwoche
Sie möchten nicht nur den Wochentag Ihres Geburtstages, sondern auch die entsprechende Kalenderwoche wissen? Dazu steht die gleichnamige Funktion KALENDERWOCHE(Datum;Typ) zur Verfügung. Das zweite Argument bestimmt den Wochentag, an dem die Kalenderwoche beginnt. Mit dem Wert 11 startet sie an einem Montag und mit Werten von 12 bis 17 an den jeweiligen Tagen zwischen Dienstag und Sonntag.
Hierzulande haben diese Parameter allerdings einen Haken: In den USA beginnt die erste Kalenderwoche in der Woche, in die der 1. Januar fällt, in Europa hingegen zählt laut ISO-Norm als erste Kalenderwoche die Woche, in der der erste Donnerstag des Jahres liegt. Da sich die Kalenderwochen in den USA und Europa in manchen Jahren unterscheiden, musste man sich bis Excel 2007 mit komplexeren Formeln oder einer VBA-Funktion behelfen, um das Problem zu beseitigen. Dies entfällt seit Excel 2007, als Microsoft zusätzlich den Typ 21 (System 2, Beginn Montag) eingeführt hat, der sich an die ISO-Norm hält:
=KALENDERWOCHE(Datum;21)
Bei diesem System 2, wie Microsoft es nennt, darf die Woche nicht beliebig starten. So ist es hier nicht möglich, den Wochenbeginn beispielsweise über den Parameter 27 auf Sonntag zu verlegen.
Urlaubstage berechnen
Wie viele Urlaubstage haben Sie noch oder wann ist Ihr Urlaub denn schon wieder zu Ende? Die Funktion ARBEITSTAGE() berechnet von einem Startdatum plus Tage das Enddatum, wobei sie Wochenenden mit Samstag und Sonntag nicht hinzurechnet. Mit dem Argument „Freie Tage“ geben Sie an, welche Datumswerte nicht beachtet werden sollen, etwa Feiertage oder Betriebsferien. Dazu können Sie auch eine Liste mit den Feiertagen oder anderen freien Tagen anlegen und den Bereichsnamen in der Funktion angeben.
Dann gibt es noch die Funktion ARBEITSTAG.INTL(). Hier berücksichtigen Sie mit einem weiteren Argument, welche und wie viele Tage als Wochenendtage gelten. Ein Wochenende kann in anderen Ländern unterschiedlich interpretiert werden. Geben Sie hier nichts oder „1“ an, gilt das für uns bekannte Wochenende aus Samstag und Sonntag. Mit einer „2“ geben Sie den Sonntag und Montag an. Das könnte zum Beispiel für Friseurbetriebe gelten. Es geht so bis zur Zahl „7“ weiter, um das Wochenende mit Freitag und Samstag anzugeben.
Mit den Zahlen „11“ bis „17“ geben Sie wiederum einzelne Tage als Wochenende an. So steht die „11“ für den Sonntag, die „12“ für den Montag und die „17“ für den Samstag.
Die Zeiten zwischen zwei Datumsangaben lassen sich mit der Funktion NETTOARBEITSTAGE(Anfangsdatum;Enddatum) berechnen. Stehen zum Beispiel in einer Tabelle Anfangsdatum und Enddatum eines Projekts in jeweils einer Zelle, liefert die Funktion die Anzahl der Tage ohne Wochenenden, aus der sich beispielsweise die zu zahlenden Leistungen errechnen. Bei Bedarf lassen sich auch weitere freie Tage als zusätzliche Parameter übergeben. Stehen in einer Tabelle zum Beispiel in A2 das Anfangsdatum, in A3 das Enddatum und in den Zellen D2 bis D9 eine Liste mit Feiertagen, sähe die Formel folgendermaßen aus:
=NETTOARBEITSTAGE(A2;A3;D2:D9)
Die Funktion NETTOARBEITSTAGE.INTL() verfügt über einen weiteren Parameter, mit dem sich die freien Tage genau festlegen lassen. Die Angabe „11“ steht für eine Arbeitswoche von Montag bis Samstag, also mit 6 Werktagen.
Inoffizielle Funktionen
Vielleicht stellen Sie sich ab und an die Frage, wie lange Sie noch bis zur Rente arbeiten müssen. Es gibt in Excel Funktionen, die nicht einmal im Funktions-Assistenten vermerkt sind. Dabei können sie doch so nützlich sein: So ermittelt die Funktion DATEDIF(Ausgangsdatum;Enddatum;Zeiteinheit) die Anzahl der vollständigen Jahre, Monate und Tage.
In der Funktion geben Sie jeweils die Zellen an, in denen Anfangs- und Enddatum aufgeführt sind. Als drittes geben Sie in Anführungszeichen das Argument für den Zeitwert ein. Dabei steht „Y“ für die Anzahl der Jahre, „M“ für Monate und „D“ für Tage.
Wenn Sie wissen möchten, wie alt Sie in Tagen sind, können Sie diese Formel einsetzen:
=HEUTE()-DATUM(Jahr;Monat;Tag)
Dass Sie jeden Tag automatisch einen Tag älter werden, kann auch Excel nicht ändern. (db@ct.de)