Tabellen unter Kontrolle
Tipps zum effizienten Arbeiten mit Excel
Nicht immer klappt in Excel alles wie erwartet. Mal deutet das Programm Eingaben falsch, mal markiert es Daten oder Formeln aus unerklärlichen Gründen als fehlerhaft oder weigert sich, Arbeitsstunden korrekt zu addieren. Mit unseren Tipps machen Sie solchen Unzulänglichkeiten ein Ende.
Nicht alle in Excel standardmäßig gesetzten Optionen sind eine Hilfe. Mitunter bietet das Programm gut gemeinte Korrekturen an oder macht durch ein kleines grünes Dreieck innerhalb einer Zelle auf vermeintliche Fehler aufmerksam. Klicken Sie auf ein solches Dreieck, erscheinen sogenannte Fehlerindikatoren mit verschiedenen Optionen zur Korrektur. Selbst wenn Sie hier „Fehler ignorieren“ wählen, erscheint der Fehlerhinweis wieder, sobald Sie die Zelle später ändern. Insbesondere der Hinweis „Die Formel schließt nicht alle angrenzenden Zellen ein“ ist meist entbehrlich. Diese Optionen bleiben so lange aktiv, bis sie deaktiviert werden. Unter „Datei/Optionen“ können Sie in der Kategorie „Formeln“ jede einzelne Regel dauerhaft ausblenden.
Rote Dreiecke am Zellenrand weisen auf Kommentare hin. Mit der Tastenkombination Strg+Umschalt+O markieren Sie schnell sämtliche Zellen mit Kommentaren und können sie über das Überprüfen-Menüband in einem Rutsch löschen. Sollen die Kommentare erhalten bleiben, können Sie die Anzeige in den Einstellungen (Alt–D–O) unter „Erweitert/Anzeigen“ unterdrücken.
Automatismen abschalten
Die Automatismen von Excel sind oft nützlich, können aber beim Arbeiten gehörig stören. Zum Glück können Sie entscheiden, was Ihnen wichtig ist. Das vordefinierte Ausfüllen benachbarter Zellen über das Ausfüllkästchen rechts unten in selektierten Zellen ist ein nützlicher Helfer. Excel ist sogar so clever, dass es bei der Aufzählung von Füllreihen wie Quartalen nur bis zum vierten zählt und dann wieder von vorn beginnt. Das permanente Einblenden der AutoAusfüll-Optionen-Schaltfläche (SmartTag) kann aber nerven. Zwar ist es bei der nächsten Aktion verschwunden, in den erweiterten Optionen können Sie es unter „Ausschneiden, Kopieren und Einfügen“ aber auch dauerhaft deaktivieren. Beim Ausfüllen durch das Ausfüllkästchen erscheint auch die Schaltfläche „Schnellanalyse“. Sie lässt sich unter „Allgemein“ in den Benutzeroberflächenoptionen abschalten.
Apropos Ausfüllkästchen: Möchten Sie in einer Spalte die Daten einer benachbarten Spalte über eine Formel berechnen, genügt ein Doppelklick auf das Kästchen, um alle Zellen bis zum letzten Wert der Nachbarspalte auszufüllen.
Beim Ausfüllen von Zellen schlägt Excel automatisch die Wortergänzung aus einer Liste vor, sobald Sie die Anfangsbuchstaben eintippen. Dies können Sie in den erweiterten Einstellungen unter „Bearbeitungsoptionen“ abschalten („AutoVervollständigen für Zellwerte aktivieren“).
Zahlen-Nerverei
Wenn Excel eine Zahleneingabe wie „123“ als „1,23“ darstellt, haben Sie unter „Erweitert/Bearbeitungsoption“ die Option „Dezimalkomma automatisch einfügen“ aktiviert. Hier können Sie festlegen, wie viele Dezimalstellen Excel anlegen soll.
In den meisten Fällen erkennt Excel nach einer Eingabe automatisch, um welchen Datentyp es sich handelt. Doch besonders die Eingaben von Zahlenformaten interpretiert Excel häufig falsch. Geben Sie einen Aufzählungspunkt wie „1.5.1“ ein, wird daraus das Datum „01.05.2001“. Die Eingabe von „29/05/18“ oder „29–5–18“ liefert das Datum „29.05.2018“. Möchten Sie die Eingabe unverändert in die Zelle übernehmen, formatieren Sie den Zellbereich zuvor als Text. Dabei kann die Fehlerprüfung die Meldung „Datumswert mit nur zweistelliger Jahreszahl“ ausgeben. Wenn das stört, schalten Sie die dafür zuständige Regel unter „Datei/Optionen/Formeln“ über die Option „Zellen mit zweistelligen Jahreszahlen“ aus.
Eine elfstellige Zahl wie „12345678901“ zeigt Excel in der Zelle genauso an, solange die Spalte breit genug ist. Sobald Sie eine weitere Ziffer eingeben, erscheint sie als „1,23457E+11“. Das „E“ für Exponent bedeutet, dass das Komma entsprechend viele Stellen weiter rechts steht, hier also an elfter Stelle. Um die Darstellung zu vermeiden, können Sie vor der Eingabe ein Hochkomma setzen und so einen Text daraus machen, können dann aber mit der Zahl nicht mehr rechnen. Besser ist es daher, das Zellformat von Standard auf Zahl zu ändern. Mit Strg+& schalten Sie wieder zur Exponent-Anzeige zurück.
Stellenpenibel
Um richtig zu runden, müssen Sie Excel genau mitteilen, wie Sie vorgehen wollen. Verwenden Sie den Befehl „Dezimalstelle entfernen“ im Start-Menüband, rechnet Excel mit den unterdrückten Nachkommastellen weiter – Rundungsdifferenzen sind die Folge. Die Anzahl der Nachkommastellen können Sie als benutzerdefiniertes Zahlenformat (Strg+1) festlegen. So wird die Zahl „77,777“ im Zahlenformat „0,00“ als „77,78“ dargestellt. Excel rechnet jedoch mit den drei Nachkommastellen weiter. Rundungsdifferenzen schließen Sie durch den Einsatz der Rundungsfunktion =RUNDEN(Zahl;Anzahl_Stellen) aus. Sie rundet Zahlen sowohl vor als auch nach dem Komma auf und ab. Mit einer positiven Zahl im zweiten Argument runden Sie Nachkommastellen. Eine negative Zahl gibt dagegen an, wie viele Stellen vor der Dezimalstelle gerundet werden sollen. So rundet –1 auf glatte Zehner, –2 auf Hunderter und so weiter.
In einem Steuerformular fürs Finanzamt müssen Sie bei bestimmten Positionen haargenau sein, bei anderen auf zwei Dezimalstellen runden oder auch einmal Nachkommastellen abschneiden. Sofern die Steuererklärung ganze Euro-Beträge erwartet, können Sie stets zu Ihren Gunsten auf volle Euro runden: Einnahmen werden abgerundet, sodass die Cent-Beträge wegfallen; bei Werbungskosten, Betriebsausgaben, Sonderausgaben und anderen Posten wird auf den nächsten Euro aufgerundet. Verwenden Sie dazu in einer Hilfsspalte die Funktionen =AUFRUNDEN (Zahl;Anzahl_Stellen) oder =ABRUNDEN (Zahl;Anzahl_Stellen), um auf die jeweiligen Nachkommastellen zu runden.
Minus-Zeiten berechnen
Der Excel-Kalender beginnt am 1.1.1900 und endet am 31.12.9999. Intern ist das Datum nur eine fortlaufende Zahl zwischen 1 und 2.958.465. Eine Zahl wandeln Sie seit Excel 2010 schnell über Strg+# in ein Datumsformat um. Diese Tastenkombination war in früheren Excel-Versionen für die Formelanzeige bestimmt.
Da es sich intern um eine Zahl handelt, können Sie in Excel bequem mit Zeitwerten rechnen. Mithilfe einer einfachen Wenn-Funktion können Sie aber trotzdem Nachtschichten berechnen, die über Mitternacht hinaus laufen: =WENN(B3<C3;C3–B3;C3–B3+1). Dabei steht die Uhrzeit des Arbeitsbeginns in Spalte B und das Schichtende in Spalte C. Die erste WENN-Anweisung rechnet die Zeitdifferenz innerhalb eines Tages; die zweite in Schichten, die bis zum nächsten Tag dauern. Die Summe der Stunden formatieren Sie als „[hh]:mm“.
Standardmäßig rechnet Excel nicht mit Minusstunden. Sie können mit ihnen weiterrechnen, wenn Sie unter „Optionen/Erweitert“ die Option „1904-Datumswerte verwenden“ aktivieren. Dieser Kalender diente ursprünglich zum Austausch von Tabellen aus frühen Excel-Versionen für den Mac, in denen die Zeitrechnung beim 1. Januar 1904 begann. Doch Vorsicht: Alle in der Arbeitsmappe bereits vorhandenen Datumswerte verschieben sich um vier Jahre und einen Tag (1900 war ein Schaltjahr), ohne dass Excel darauf hinweist. Am besten stellen Sie die Datumswerte sofort beim Anlegen einer neuen Tabelle um.
Richtiger Minus-Assistent
SAP und andere Branchensoftware stellt negative Zahlen mit einem Minuszeichen hinter statt vor der Zahl dar, sodass Excel sie beim Datenimport als Text interpretiert. In umfangreichen Listen wäre es eine Sisyphusarbeit, dies per Hand zu korrigieren. Ein etwas versteckter Textkonvertierungsassistent behebt das Problem. Markieren Sie den Zellbereich mit den fehlerhaften Daten und wählen Sie über das Menüband „Daten“ in der Gruppe „Datentools“ den Befehl „Text in Spalten“. Mit zwei Klicks auf „Weiter“ gelangen Sie zum dritten Schritt und wählen „Erweitert…“. Dort aktivieren Sie die Option „Nachstehendes Minuszeichen für negative Zahlen“. Über die Schaltfläche „Fertig stellen“ wandelt der Assistent die Daten in Zahlen um.
Wenn Sie den Textkonvertierungsassistenten nicht nach jedem Import aufrufen möchten, können Sie das Minus-Problem mit einer zusätzlichen Hilfsspalte und der Wenn-Funktion umgehen. Damit prüfen Sie zunächst, ob überhaupt ein Minuszeichen rechts neben der Zahl steht. Die Funktionen =RECHTS(Text,Anzahl_Zeichen) und =LINKS(Text,Anzahl_Zeichen) geben je nach Richtung die letzten Zeichen einer Zeichenfolge an. Die Funktion =LÄNGE(Text) zählt die Stellen. Mit einer Kombination aus diesen vier Funktionen wandert das Minuszeichen von rechts nach links: =WENN(RECHTS(A13;1)="–";LINKS(A13;LÄNGE(A13)–1)*–1;A13). Hier prüft die WENN-Funktion, ob das rechte Zeichen ein Minuszeichen ist, und hebt es dann auf. Dadurch konvertiert die Formel die als Text formatierten Zahlen in Zahlenwerte. Die Multiplikation mit –1 wandelt sie dann in negative Zahlen um.
Die Funktion =WECHSELN(Text;Alter_Text;Neuer_Text) erfüllt innerhalb der WENN-Funktion denselben Zweck. Dabei tauschen Sie das Minuszeichen einfach aus und setzen vor der Funktion ein Minuszeichen: =WENN(RECHTS(D13;1)="–";–WECHSELN(D13;"–";);D13). Sie können die Werte auch direkt mit den Zahlen aus der Hilfsspalte überschreiben. Markieren Sie dazu den Zellbereich und ziehen Sie diesen mit gedrückter rechter Maustaste auf die ursprünglichen Werte. Geben Sie zum Schluss im Kontextmenü „Hierhin nur als Werte kopieren“ an.
Zeichen-Nörgelei
Bei der Eingabe von Formeln versucht Excel, Fehler von sich aus auszubügeln. Falls Sie bei einfachen Formeln wie =Summe(A1:A10) die abschließende Klammer vergessen, moniert Excel das noch nicht einmal als fehlerhaft, sondern fügt die schließende Klammer automatisch hinzu. Doch beim mehrmals verzweigten Aufbau der WENN-Funktion wird penibel darauf geachtet, ob jede Klammer korrekt gesetzt ist. Die Funktion kann bis zu sieben Ebenen tief verschachtelt sein. Dabei beziehen sich die sieben Verschachtelungen jeweils auf den Dann- beziehungsweise Sonst-Wert. Da kann der Überblick schon mal verloren gehen.
Wenn Sie mehr WENN-Funktionen zusammen zum Einsatz bringen möchten, addieren Sie die Funktionen einfach: =WENN(…)+WENN() … +WENN(). Hier ist der Rückgabewert eine Zahl. Setzen Sie dagegen das kaufmännische Und (&) statt des Plus-Zeichens zum Verbinden ein, wird das Ergebnis als Text zurückgegeben.
UND und ODER können nicht zum Verbinden eingegeben werden, da sie Funktionen sind. Daher müssen sie immer vor den Argumenten angegeben werden: =WENN(ODER(A1>B1;A3);A1+A2;A4+A5).
In komplexen Formeln können Sie zur besseren Orientierung eine Hilfestellung geben, indem Sie die Funktion =N("Text") in eine Formel einbinden. In der Formel =WENN(A1>A2)+N("Gewinnermittlung") erscheint der persönliche Hinweis nur in der Bearbeitungsleiste als Gedächtnisstütze. Auf das Ergebnis hat die Funktion NC() keine Auswirkung, da sie den Wert 0 liefert.
Fehler ausbügeln
Mit den kryptischen Fehlermeldungen kann Excel gehörig nerven. Eine Meldung wie „#WERT“ verhindert weitere Berechnungen. Die Zelle als Text zu formatieren ist hier keine Lösung. In Funktionen wie MAX(), MITTELWERT() oder SUMME() haben die Fehlermeldungen einen großen Nachteil, da Excel dann keine Ergebnisse liefert.
Mit der Funktion =AGGREGAT(Funktion;Option;Bezug) umgehen Sie ab Excel 2010 dieses Problem und können trotzdem weiterrechnen. Die Funktion führt auch zu einem Ergebnis, wenn Berechnungsprobleme wie Fehlerwerte vorhanden sind. Dabei brauchen Sie nur die Funktion, die zuvor „gepatzt“ hat, als Argument anzugeben. AGGREGAT() lässt sich allerdings nur für Spalten einsetzen. Werden diese ausgeblendet, hat das keinen Einfluss auf das Ergebnis.
Beim ersten Argument geben Sie die Nummer der jeweiligen Funktion an. Das zweite Argument führt auf, was berücksichtigt werden soll: Fehlerwerte, ausgeblendete Zellen oder Teilergebnisse. Mit „6“ geben Sie zum Beispiel an, dass Fehlerwerte ignoriert werden sollen. Zum Schluss folgt der auszuwertende Zellbereich. Diesen können Sie auch als Matrix über Strg+Umschalt+Return anlegen.
Mit Fehlern leben
Fehler sind auch nicht mehr das, was sie mal waren. Sie können in Berechnungen und Anweisungen mit eingebaut werden. Die Division von Zellinhalten wie in A10/Keine Angabe führt zur Meldung „#Wert“, wenn in einer Zelle Text steht.
Bis einschließlich Excel 2003 konnte man die Fehleranzeige nur mit einer Kombination aus WENN- und ISTFEHLER-Funktionen aufheben: =WENN(ISTFEHLER (A10/B10);"Umsatz noch nicht vorhanden";A10/B10). Seit Excel 2007 geht das viel einfacher mit =WENNFEHLER(Wert; Wert_falls_Fehler).
Eine Fehlerbehandlung, die ohne Umwege etwas ausführt, wenn ein Fehler vorliegt, lautet =WENNFEHLER(A10/B10;"Umsatz noch nicht vorhanden"). Das zweite Argument gibt an, was die Funktion zurückgeben soll, wenn beim Berechnen der Formel ein Fehler auftritt. Die Funktion kann die Fehlertypen „#NV“, „#WERT!“, „#BEZUG!“, „#DIV/0“, „#ZAHL!“, „#NAME!“ und „#NULL!“ behandeln. Die Fehlermeldung „#NV“ hat sogar eine eigene WENN-Funktion zum Handeln: =WENNNV(A10;"Keine Zahlen vorhanden"). Häufig erscheint „#NV“ bei SVERWEIS() oder WVERWEIS(), wenn Excel einen Fehlerwert statt eines Suchkriteriums findet. Dies lässt sich mit =WENNNV(SVERWEIS(C1;Provisionen;2);"Keine Zahlen vorhanden") beheben.
Optisch korrigiert
In einem Diagramm führt die Anzeige „#NV“ leicht zu Irritationen. Fehlt in einem Liniendiagramm ein Wert in der Datenreihe, stürzt die Linie auf den Nullpunkt oder wird häufig sogar unterbrochen. Um das zu vermeiden, aktivieren Sie das Diagramm per Mausklick. Holen Sie nun das Menüband „Diagrammtools/Entwurf“ in den Vordergrund und klicken Sie hier auf die Schaltfläche „Daten auswählen“. Im Dialog „Datenquelle auswählen“ klicken Sie dann auf die Schaltfläche „Ausgeblendete und leere Zelle“. Dort haben Sie die Wahl, die leeren Zellen als Lücken oder mit Nullen anzuzeigen und die störende Fehlermeldung „#NV“ zu unterdrücken. Ebenso lassen sich Daten aus ausgeblendeten Zeilen und Spalten einbinden. Wenn Sie hier den Befehl „Datenpunkte mit einer Linie verbinden“ wählen, erscheint die Linie ohne Unterbrechung. (db@ct.de)