Excel-Tipps


Berechnen der zur Verfügung stehenden Arbeitstage zwischen zwei Daten

 

Bei Projektplanungen oder zur Einhaltung von Lieferterminen brauchen Sie keinen Kalender zur Hand zu nehmen, um die Zahl der tatsächlichen Arbeitstage unter Berücksichtigung von Wochenenden und Feiertagen zu ermitteln. In Microsoft Excel gibt es eine Funktion, die die Nettoarbeitstage zwischen zwei Datumsangaben errechnet. Für die Berechnung der Dauer einer Datumsdifferenz mit der Anzahl der dazwischenliegenden Arbeitstage kann die Funktion NETTOARBEITSTAGE verwendet werden.

 

 

 

Bei Anwendung des optionalen Arguments FREIE_TAGE könnten neben den Wochenenden auch noch individuell festlegbare Feiertage berücksichtigt werden.

 

Extra-Tipp: In Microsoft Excel gibt es noch die Funktion NETTOARBEITSTAGE.INTL (https://support.office.com/de-de/article/NETTOARBEITSTAGE-INTL-Funktion-a9b26239-4f20-46a1-9ab8-4e925bfd5e28?CorrelationId=9430ca93-e4d9-4649-b0e9-2b747e8ad408&ui=de-DE&rs=de-DE&ad=DE). Diese Funktion hat einen weiteren Parameter, mit dem Sie auch die Wochenendtage frei definieren können, beispielsweise falls der Samstag als Arbeitstag gelten soll.

 

Dipl.-Kfm. Daniel Unrein, Senior-Controller bei der NORMA Group SE, Verfasser des bei Vahlen erschienenen Fachbuchs „Excel im Controlling”

 

 

 

 

Excel-Tipps von Daniel Unrein

 

 

1. Top-/Flop-X-Analysen

In den meisten Management-Reports oder Dashboards (engl. Armaturenbrett, Instrumententafel) empfiehlt es sich, eine Top-X-Analyse durchzuführen – also nicht etwa auf alles zu schauen, sondern nur auf die Top (oder gegebenenfalls auch Flop) 3,5 oder auch 10 besten Produkte, Verkäufer, Absatzgebiete etc. Damit soll dem Management nur wirklich Relevantes in einem Report/Dashboard präsentiert werden.

Hierzu kann eine (Pivot-)Tabelle mit dem Top-X-Filter gefiltert werden, und zwar unter DATEN → FILTERN → ZAHLENFILTER → TOP 10…. Es kann hier sowohl nach den obersten und untersten X-Elementen als auch nach Prozent gefiltert werden.

Zudem kann auch die bedingte Formatierung verwendet werden, um die gewünschten Werte optisch herauszustellen. Dies geht unter START → BEDINGTE FORMATIERUNG → OBERE/UNTERE REGELN.

 

 

 

 

 

2. Die Schnellzugriffsleiste nutzen

Wichtige und häufig verwendete Befehle (oder auch Makros) sind am besten in den Schnellzugriff zu hinterlegen, um sie in Reichweite der Maus zu haben und sie rasch ansteuern zu können.
Hierzu ist im Menüband die rechte Maustaste zu klicken und PASSEN SIE DIE SYMBOLLEISTE FÜR DEN SCHNELLZUGRIFF AN und unter BEFEHLE AUSWÄHLEN „Nicht im Menüband enthaltene Befehle“ zu wählen.

 

 

3. Mit dem Pinsel Formate übertragen

Eine sehr nützliche und schnelle Möglichkeit zum Kopieren von Zellformatierungen (neben KOPIEREN → INHALTE EINFÜGEN → FORMAT) ist FORMAT ÜBERTRAGEN (das „Pinsel-Icon“) in der Symbolleiste START. Hierzu ist zuerst die Ausgangszelle anzuklicken, dann FORMAT ÜBERTRAGEN/der Pinsel und anschließend die Zelle/den Zellbereich auf den das Format übertragen werden soll.
Wenn ein Format auf verschiedene Zellen übertragen werden soll, dann kann mit Doppelklick auf FORMAT ÜBERTRAGEN das Format solange auf verschiedene Zellen übertragen werden, bis wieder auf das Pinsel-Symbol geklickt wird. Dies ist ein nützlicher kleiner „Trick“, der Ihnen in der Praxis viel Zeit beim – doch eher lästigen – Formatieren ersparen kann.

 

 

4. „AutoVervollständigung“ bei Funktionseingabe

Beim Arbeiten mit Formeln ist die AUTOVERVOLLSTÄNDIGUNG ein nützliches Werkzeug. Dieses wird bei der Eingabe einer Funktionsbezeichnung in eine Zelle aktiviert und kann von Ihnen benutzt werden, um Formeln schneller einzugeben. Nach Eingabe des ersten Zeichens einer Funktionsbezeichnung werden die infrage kommenden Funktionen automatisch angezeigt und können dann mit der Tab-Taste (siehe Abb.) schnell ausgewählt werden. Dies ist deutlich schneller, als die Formel komplett zu tippen. Zudem werden (sobald eine öffnende Klammer oder innerhalb des Klammerausdrucks ein Semikolon eingegeben wird) alle geeigneten Optionen für die Argumente der Funktion aufgelistet.

Gerade für Anfänger im Formelbereich kann dies eine gute Hilfe – insbesondere bei der Eingabe komplexerer Formeln – darstellen.

 

 

Abb.: Tab-Taste

 

 

5. Tastenkürzel F4

Durch (mehrmaliges) Drücken von F4 in der Bearbeitungsleiste können bei Eingabe einer Formel absolute in gemischte und relative Bezüge umgewandelt werden.

Im Arbeitsblatt wiederholt F4 hingegen den letzten Befehl oder die letzte Aktion, sofern möglich.

 

 

6. Optimale Spaltenbreite mit Doppelklick einstellen

Ist ein Text länger als die Spalte, dann wird der Text scheinbar in die benachbarte Zelle geschrieben. Sofern die benachbarte Zelle einen Eintrag enthält, wird der Text hingegen abgeschnitten.
Um die optimale Spaltenbreite für die aktuell benötigte Textgröße schnell einstellen zu können, empfiehlt es sich, zwischen die beiden Spalten mit einem Doppelklick zu klicken. Die Maus muss zuvor wie ein Kreuz (mit Pfeilen links und rechts) aussehen.

 

 

7. Formeln kopieren/AUTOAUSFÜLLEN

Die Funktion zum AUTOAUSFÜLLEN ist eines der effizientesten Werkzeuge bei der Eingabe von Werten und Daten. Durch Ziehen am Ausfüllkästchen rechts unten in der Zellmarkierung können Werte wahlweise fortgeschrieben oder aber kopiert werden. Das bringt Zeitersparnis und ist sicherlich die bekannteste sowie eine der geeignetsten Methoden, Formeln rasch in angrenzende Zellen zu übertragen. Zur Nutzung von AUTOAUSFÜLLEN gelten dabei die folgenden Regeln:

  • Einfaches Ziehen mit linker Maustaste am Datenkreuz: Zahlen werden auf diese Weise kopiert; Datumswerte oder zeitliche Abfolgen, wie 01.01.2015, Mai oder Donnerstag, werden hingegen als Datenreihe fortgeschrieben. Dasselbe gilt für Text-Zahlen-Kombinationen wie „Quartal 1“ (nach dem 4. Quartal 2014 folgt das 1. Quartal 2015 und nicht das 5. Quartal 2014). Begriffe von benutzerdefinierten Listen werden dabei ebenfalls fortgeschrieben.
  • Mit linker Maustaste und gedrückter STRG: Diese Kombination dient sozusagen als Umkehrung von Füllen und Kopieren. Wird beispielsweise an einer Zelle mit dem Wert 5 gezogen, erstellt Excel eine fortlaufende Nummerierung und keine Kopie des Werts. Hingegen wird ein Datumswert dann kopiert und nicht wie beim normalen Ziehen fortgeschrieben (vgl. Arbeitsblatt „AUTOAUSFÜLLEN“).
  • Mit rechter Maustaste ziehen: Beim Loslassen der Maus öffnet sich das Kontextmenü, welches in Abhängigkeit des Datentyps verschiedene Ausfüll-Optionen anbietet. Bei Zahlen können hiermit die beiden obigen Varianten abgebildet, aber auch lineare oder geometrische Reihen erstellt und somit eine lineare bzw. exponentielle Trendentwicklung dargestellt werden. Bei Datumswerten bestehen weitere hilfreiche Optionen, wie etwa Tage oder auch nur Wochentage ausfüllen (vgl. Arbeitsblatt „AUTOAUSFÜLLEN“).

 

 

8. Formeln kopieren/AUTOAUSFÜLLEN

Befinden sich rechts oder links von der zu kopierenden Zelle bereits Daten in der Spalte, können Sie durch Doppelklick auf das AUSFÜLLKÄSTCHEN Formeln sehr schnell kopieren. Excel füllt dann in einem Arbeitsschritt den gesamten Zellbereich bis zur letzten Zeile der Tabelle. Dabei können auch mehrere nebeneinander liegende Zellen markiert und per Doppelklick nach unten kopiert werden. In diesem Fall kehrt die STRG-Taste die beiden Optionen des Kopierens bzw. Ausfüllens – im Gegensatz zum Ziehen – nicht um (siehe Tipp 7). Datumswerte werden also immer fortlaufend gefüllt, und Werte werden stets kopiert.

 

 

9. Nach Farbe(n) filtern/sortieren

In den neueren Excel-Versionen können Sie auch nach Farbe(n) filtern bzw. sortieren. Diese Funktionalität ist gerade auch in Kombination mit der bedingten Formatierung besonders nützlich.

  • Der Menüpunkt DATEN → FILTERN → NACH FARBE FILTERN wird dabei nur aktiviert, wenn in der zu filternden Spalte unterschiedliche Farbformatierungen vorhanden sind. Im Menü auf NACH FARBE FILTERN stehen die verwendeten Farben zur Verfügung und können ausgewählt werden.
  • Ebenfalls können Sie auch nach Farben sortieren – und zwar ist unter DATEN → SORTIEREN → SORTIEREN NACH „Zellfarbe“ zu wählen (alternativ über „Daten/Filtern/Nach Farbe sortieren“); werden mehrere Farben verwendet, kann an dieser Stelle die Reihenfolge für die Sortierung der einzelnen Farben festgelegt werden.

 

 

10. Formeln debuggen/analysieren

Verschachtelte Formeln (gerade Array- und Matrix-Formeln) können sehr komplex sein. Es gibt daher eine Möglichkeit, das Ergebnis des Teils einer Formel anzuzeigen. Dies nennt man auch Debuggen. Hierfür wird das Tastenkürzel F9 verwendet.

Vorgehensweise: Eine Formel (innerhalb einer verschachtelten Formel) markieren und F9 drücken. Das Ergebnis der Formel wird dann in der Bearbeitungsleiste angezeigt.

 

 

11. Benutzerdefinierte Listen

Benötigen Sie eine bestimmte Abfolge von Begriffen (z.B. wie im Folgenden Produkte oder auch Gesellschafts-, Standortnamen etc.) nicht nur einmal, sondern immer wieder, können Sie eine sog. BENUTZERDEFINIERTE LISTE erstellen. Am besten sind dazu alle Begriffe, die in der Liste enthalten sein sollen, in die Zellen eines Tabellenblatts zu schreiben. Unter DATEI → OPTIONEN → ERWEITERT → ALLGEMEIN → BENUTZERDEFINIERTE LISTEN BEARBEITEN → NEUE LISTE sind die im Tabellenblatt geschriebenen Begriffe zu markieren; dann ist auf IMPORTIEREN und abschließend auf OK zu klicken.

 

 

 

 

Nun kann immer wieder auf diese Liste zurückgegriffen werden, ohne diese jedes Mal neu eingeben zu müssen. Hierbei können Sie benutzerdefinierte Listen auf zwei unterschiedliche Arten in Excel verwenden:

  • zum Füllen von Zellbereichen,
  • zum benutzerdefinierten Sortieren von Tabellen.

Um einen Zellbereich mit den Werten der Liste zu füllen, schreiben Sie einen Begriff aus der Liste in eine Zelle des Tabellenblatts und ziehen den Inhalt dann per AUTOAUSFÜLLEN nach unten oder nach rechts. Zum automatischen Ausfüllen: Platzierung des Mauszeigers in das kleine Quadrat in der rechten unteren Ecke der Zellenmarkierung; es erscheint ein kleineres Kreuz, dann linke Maustaste klicken und „ziehen“.

Um eine Liste nicht in der Standardsortierreihenfolge, sondern entsprechend der individuellen Prioritätensetzung zu sortieren, ist nach Klick auf die rechte Maustaste auf SORTIEREN UND FILTERN (oder nur SORTIEREN) → BENUTZERDEFINIERTES SORTIEREN zu klicken (vgl. auch den nächst7en Tipp zum Sortieren von bereits voreingestellten Listen/von Monaten).

 

 

12. Listen nach Monaten von Januar bis Dezember sortieren

Wollen Sie eine Liste nach Monatsnamen sortieren, wird diese korrekt alphabetisch angezeigt – genau das ist im Regelfall aber selbstverständlich nicht erwünscht. Mit einem einfachen Trick können Sie die Monate nach ihrer zeitlichen Abfolge von Januar bis Dezember ordnen.

Hierzu sind die Monate zu markieren und unter DATEN → SORTIEREN → REIHENFOLGE ist „Benutzerdefinierte Listen“ zu wählen. Hier finden Sie dann die Monatsnamen bzw. auch deren Abkürzungen. Nach Bestätigung mit OK liegen die Monate in der gewünschten Reihenfolge vor.

 

 

 

 

 

13. Tastenkürzel: F2

Das Tastenkürzel „F2“ bearbeitet die aktive Zelle und positioniert die Einfügemarke am Ende des Zelleninhalts (egal ob Text oder Formel). Dadurch lässt sich der Zellinhalt schnell bearbeiten.

 

 

14. Mit der Statusleiste arbeiten

Wenn Daten in einem Arbeitsblatt markiert sind, können Sie relevante Berechnungen wie Summe, Minimum, Maximum, Mittelwerte oder Anzahl der Zahlenargumente auch direkt in der Statusleiste ablesen. Die Statusleiste ist ein sehr praktisches Hilfsmittel gerade für eine schnelle Basis-Analyse von Daten.

Ist die gewünschte Berechnung nicht enthalten, müssen Sie die Statusleiste mit der rechten Maustaste anklicken und im Kontextmenü den entsprechenden Eintrag wählen.

 

 

 

15. Tastenkürzel STRG+a

Bei Eingabe einer Formel, d.h. nach Erkennung der Formel durch Excel, kann mit STRG+a der Funktionsassistent aufgerufen werden.

Bei Drücken von STRG+a im Arbeitsblatt wird hingegen die gesamte Tabelle markiert (und kann dann beispielsweise formatiert werden).

 

 

16. Zellenformatvorlagen

Das Formatieren von Zellen geht am einfachsten mittels der bestehenden ZELLENFORMATVORLAGEN im Menü START → FORMATVORLAGEN.

Neben der Übernahme vorgefertigter Vorlagen besteht auch die Möglichkeit, diese selbst zu definieren. Insbesondere in Kalkulationsmodellen kann es beispielsweise zweckmäßig sein, Input- von Berechnungszellen optisch unterscheidbar zu machen. Die Verwendung von speziellen individuellen Formatvorlagen bietet dabei verschiedene Vorteile, wie Zeitersparnis bei der Modell-Entwicklung, unternehmensweit einheitliches Aussehen etc. Zudem ist es nutzerfreundlich, da beispielsweise die Eingabezellen schnell(er) identifizierbar sind.

 

 

 

17. Formeln kopieren mit der Tastatur

Befindet sich in einer Zeile eine Formel in der äußert linken Ecke, so kann diese mit STRG+R in alle rechts anliegenden Zellen kopiert werden.

Alternativ kann mit STRG+U die erste Zelle einer Markierung nach unten in der Spalte kopiert werden.

STRG+ kopiert eine Formel aus einer Zelle oberhalb der Markierung ohne jegliche Anpassung, verdoppelt diese also quasi.

 

 

18. Inhalte von Zellen unsichtbar machen

Der Inhalt einer Zelle kann selbstverständlich unsichtbar gemacht werden. Entweder indem Sie die Zellfarbe auf Weiß einstellen – gegebenenfalls auch abhängig von einem Wert mit der bedingten Formatierung – oder durch Einsetzen der benutzerdefinierten Formatierung. Mit „;;;;“ werden alle Inhalte einer Zelle ausgeblendet (siehe Abbildung).

 

 

 

 

19. Duplikate entfernen/markieren

Duplikate in Listen stellen meist entweder einen Fehler dar, oder die mehrfach vorkommenden Werte sollen aggregiert/summiert werden. Will man aus mehrfach anzutreffenden Werten eine Liste mit Unikaten (nur jeweils einmal vorhandene Werte) schaffen, auf die sich beispielsweise Formeln beziehen können – und kann oder will nicht Pivot einzusetzen –, dann lässt sich hierzu auch auf die Funktion DATEN → DUPLIKATE ENTFERNEN zurückgreifen. Die doppelt bzw. mehrfach vorkommenden Werte werden dann einfach von Excel entfernt; es bleiben nur Unikate/eindeutige Werte übrig.

Mithilfe der bedingten Formatierungen können diese markiert werden – und zwar unter START → BEDINGTE-FORMATIERUNG → REGELN ZUM HERVORHEBEN VON ZELLEN → DOPPELTE WERTE.

  

 

 

 

20. Zellen mit bedingter Formatierung finden/markieren

In der normalen Excel-Ansicht können Sie bedingt formatierte Zellen nicht einfach von „normalen“ Zellen unterscheiden. Sollten Sie nicht mehr wissen, wo bedingte Formatierungen eingesetzt wurden, lassen sich diese wie folgt auffinden: START → SUCHEN UND AUSWÄHLEN → BEDINGTE FORMATIERUNG. In der Folge werden alle bedingt formatierten Zellen (hier die doppelten Werte, siehe auch den vorherigen Tipp) markiert.

 

  

 

 

 

 

21. Hyperlink mit Tastenkürzel einfügen

Mit STRG+k rufen Sie das Dialogfeld LINK EINFÜGEN zum Einfügen eines neuen Hyperlinks auf. Ist ein vorhandener Hyperlink markiert, wird hingegen das Dialogfeld LINK BEARBEITEN angezeigt.

Hyperlinks bieten hilfreiche Navigations- und Interaktionsmöglichkeiten; beispielsweise kann durch Einsatz von Hyperlinks eine Benutzerführung in Excel-Reports ermöglicht werden.

 

 

22. Die Blitzvorschau (ab Excel 2013)

Ein kleines Highlight in Sachen neuer Funktionalität in Excel 2013 ist sicherlich die Blitzvorschau – eine Erweiterung des automatischen Ausfüllens. Die Anwendung sucht bei Dateneingabe nach möglichen Erfassungsmustern. Sobald die Blitzvorschau erkannt hat, welches Muster vorliegt, gibt es die restlichen Daten sofort komplett ein bzw. schlägt die Vervollständigung nach dem erkannten Muster vor.

Wollen Sie beispielsweise zu einer Liste von Namen noch rechts davon die sich aus den Anfangsbuchstaben von Vor- und Nachnamen zusammensetzenden Kürzel ausgeben, kann die Blitzvorschau (unter DATEN → DATENTOOLS → BLITZVORSCHAU oder STRG+E) eingesetzt werden. Es ist in die Zelle rechts vom ersten Namen (Patrick Müller) zu gehen und „PM“ einzutippen. Sobald Sie in der nächsten Zelle das „D“ (für Daniel) eingeben, erkennt Excel das Muster und ergänzt das „U“ (für Unrein) – sowie auch für alle anderen Kürzel automatisch. Passt das von der Blitzvorschau vorgeschlagene Muster, können Sie die vorgeschlagenen Werte durch Drücken von ENTER übernehmen.

 

Extra-Tipp: Die Blitzvorschau funktioniert auch mit einer Vielzahl an weiteren Mustern (bspw. Nummern aus Texten herauslesen). Wird ein Muster nicht direkt automatisch erkannt, können Sie mit der manuellen Blitzvorschau arbeiten. Dann sind – je nach Datenmaterial – ein paar (mehr) Vorgaben zu machen und der zu vervollständigende Bereich ist samt der Vorgaben zu markieren. Unter DATEN ist dann BLITZVORSCHAU anzuklicken.

 

 

 

 

 

 

23. Zahlen und Text mit ANZAHL/ANZAHL2 zählen

Die Funktion ANZAHL ermittelt, wie viele Zahlen eine bestimmte Liste von Argumenten enthält, also wie viele Zellen in einem Bereich Zahlen enthalten.

Im Unterschied zu ANZAHL berücksichtigt ANZAHL2 auch alle Text-, Wahrheitswerte und Fehlerwerte, ermittelt also wie viele Zellen in einem Bereich nicht leer sind.

Es gibt demnach keine Standard-Funktion, um auch die Anzahl der Textzellen zu zählen. Dies ergibt sich aber aus Kombination der beiden oben genannten Formeln, indem Sie ANZAHL2-ANZAHL rechnen. Demnach wird durch ANZAHL2(A1:100)-ANZAHL(A1:100) die Anzahl aller Textzellen in diesem Bereich berechnet.

 

 

24. Den letzten Wert einer Spalte ermitteln

Mit der Funktion INDEX können Sie in Verbindung mit ANZAHL2 den letzten Wert bzw. Text, der in einer Zeile oder Spalte steht, ermitteln. Voraussetzung ist, dass keine Leerzellen dazwischen vorkommen. Die Formel für die Ermittlung der letzten Zelle mit Inhalt aus Spalte A lautet: =INDEX(A:A;ANZAHL2(A:A))

 

 

25. Leere Zellen in einer Spalte/in einem Bereich zählen

Um die leeren Zellen (bspw. als Indikator für noch fehlende Einträge in einer Liste) in einem Bereich zu zählen, steht Ihnen die Funktion ANZAHLLEEREZELLEN zur Verfügung. Dabei werden neben echten leeren Zellen auch Zellen mitgezählt, in denen leere Zeichenfolgen nach einem Gleichheitszeichen (Formeln) und bspw. bei WENN-Funktionen stehen. Zellen, die Nullwerte enthalten, werden hingegen nicht mitgezählt.

Um den Bereich D1:D20 nach leeren Zellen abzusuchen und die noch offenen, auszufüllenden Punkte zu ermitteln, können Sie die folgende Formel eingeben:

=ANZAHLLEEREZELLEN(D1:D20)

 

 

26. Leere Zellen optisch hervorheben

Um nicht ausgefüllte Zellen/Felder in einer Liste optisch hervorzuheben, kann die bedingte Formatierung eingesetzt werden. Unter START → BEDINGTE FORMATIERUNG → NEUE REGEL → NUR ZELLEN FORMATIEREN, DIE ENTHALTEN ist im Drop-Down-Menü „Nur Zellen formatieren mit“ LEERZEICHEN zu wählen.

Alternativ kann zudem auch eine eigene Regel/Bedingung über die Formel ISTLEER definiert werden. Dann ist einzutragen unter START → BEDINGTE FORMATIERUNG → NEUE REGEL-FORMEL ZUR ERMITTLUNG DER ZU FORMATIERENDEN ZELLEN VERWENDEN:

=ISTLEER(A1:G217).

 

 

 

27. Berechnung der CAGR (Compound Annual Growth Rate) in Excel

Als Wachstumsrate bezeichnet man die relative Zunahme einer Größe in einem Zeitraum. Eine spezielle und für Fragestellungen im Finanzbereich häufig genutzte Wachstumsrate ist die jährliche Wachstumsrate (engl. Compound Annual Growth Rate, abgekürzt CAGR). Sie stellt das durchschnittliche jährliche Wachstum einer zu betrachtenden Größe dar. Zur Berechnung in Excel wird der aktuelle Wert (hier im ersten Fall 25) durch den Ausgangswert (hier im ersten Fall 21) geteilt. Von dem Ergebnis wird die n-te Wurzel gezogen, wobei n die Anzahl der Jahre (hier 3 = 2014 ./. 2011) ist, die betrachtet werden. Die CAGR kann dabei nicht nur über eine/diese manuelle Formel [(25/21)^(1/3)-1] berechnet werden, sondern auch über die Funktion ZINS.

Diese Funktion berechnet den Zinssatz einer konstanten Zahlung je Periode und kann sehr gut genutzt werden, um den Zinssatz von Guthabenverzinsungen oder Annuitätendarlehen zu berechnen. Diese Logik kann für die Berechnung der CAGR etwas angepasst werden: Die Wachstumsrate des Umsatzes von 2011 bis 2014 (im Beispiel, siehe Abb.) entspricht letztlich dem Zins für ein Darlehen in Höhe des Startwerts (BW, muss negativ eingegeben werden), für das während der Laufzeit (Zzr; hier: 3 Jahre) keine Zinszahlungen (RMZ) getätigt werden und das am Ende mit dem Endwert (ZW) zurückbezahlt wird.

 

 

 

28. Die Schnellanalyse (ab Excel 2013)

Auch wenn viele Analysen im Controlling meist gerade nicht schnell oder „mal eben so einfach“ gehen, gibt es auch Fälle, in denen eine erste Basisanalyse schon ausreichend sein kann, um sich einen Eindruck zu verschaffen.

Excel 2013 bietet eine Funktion, die es erlaubt, Daten per SCHNELLANALYSE auswerten zu können. Alle hierin enthaltenen Funktionalitäten sind selbstverständlich auch im normalen Menü verfügbar, aber über die SCHNELLANALYSE schnell und komfortabel durch ein direkt bei den Daten erscheinendes Menü ansteuerbar. Sobald Sie Daten mit der Maus markiert haben, erscheint am unteren rechten Rand das Icon – und bei Klick darauf – das Menü zur SCHNELLANALYSE. Hierin können Sie in wenigen Schritten Ergebnisse über Formeln wie Summen oder Mittelwerte berechnen, intelligente Tabellen und Pivots erstellen oder auch Visualisierungen in Form von Diagrammen, der bedingten Formatierung und Sparklines vornehmen.

 

 

 

29. Runden

Mit der Formel RUNDEN kann sowohl auf Nachkommastellen als auch auf ganze Zahlen gerundet werden. Hierbei gilt die einfache kaufmännische Regel: Werte über fünf werden aufgerundet, Werte unter fünf abgerundet. Bei 0 Nachkommastellen wird auf ganze Zahlen gerundet. Sollen Werte definitiv immer auf- oder abgerundet werden, bedient man sich zwei anderer Excel-Funktionen:

  • Die Formel ABRUNDEN rundet eine Zahl auf eine vorgegebene Anzahl von Stellen ab.
  • AUFRUNDEN funktioniert ähnlich wie ABRUNDEN, nur eben für das Aufrunden von Zahlen.

Anbei ein paar Beispiele:

 

 

 

30. Fenster fixieren

Bei größeren Tabellenblättern empfiehlt es sich, immer unter ANSICHT → FENSTER FIXIEREN die oberste Zeile/die Überschriften-Zeile zu fixieren, damit diese beim Scrollen nach unten noch sichtbar bleibt und so eine bessere Zuordnung der Daten ermöglicht.

 

 

 

 

31. Datenüberprüfung beim Dateninput einsetzen – nur Zahlen > 0 erlauben

In Reports oder Tabellen-Blättern, in denen Nutzer/Kollegen Eingaben machen müssen, empfiehlt es sich, mögliche Fehler durch ungewünschte Nutzereingaben beim Dateninput möglichst zu vermeiden/proaktiv abzufangen. Hierzu eignet sich die Excel-Funktionalität DATEN → DATENÜBERPRÜFUNG → DATENÜBERPRÜFUNG.

Beispielweise dürfen für eine Verkaufspreisliste keine Werte kleiner gleich Null vorkommen. Um dies einzustellen, muss im Datenüberprüfungs-Menü unter ZULASSEN die Kategorie „Dezimal“ gewählt werden, da die Werte Dezimalwerte enthalten (können), die bei der Wahl der Kategorie „Ganze Zahl“ fälschlicherweise als Fehler ausgegeben werden würden. Anschließend müssen die Gültigkeitsparameter eingegeben werden – für das hier gewählte Beispiel stellen alle Werte größer 0 gültige Daten dar (vgl. Abb.).

 

 

 

32. Datenüberprüfung beim Dateninput einsetzen II – Listen einsetzen

In Reports oder Tabellen-Blättern, in denen Nutzer/Kollegen Eingaben machen müssen, empfiehlt es sich, mögliche Fehler durch ungewünschte Nutzereingaben beim Dateninput möglichst zu vermeiden/proaktiv abzufangen. Hierzu eignet sich die Excel-Funktionalität DATEN → DATENÜBERPRÜFUNG → DATENÜBERPRÜFUNG. Es kann beispielsweise zweckmäßig sein, den Kollegen eine LISTE mit auszuwählenden Inhalten zur Verfügung zu stellen („Zulassen: Liste“). Dann können nur aus diesen ausgewählten Vorgaben-Werten/-Begriffen gewählt – und keine falschen/sonstigen Eingaben getätigt – werden.

 

 

Extra-Tipp: Zudem kann eine solche Liste als ein komfortables und interaktives Auswahlfeld fungieren und so (beispielsweise indem sich Formeln wie der SVERWEIS auf die Auswahlzelle beziehen) für Dynamik/Flexibilität in Excel-Reports sorgen.

 

 

33. Datenüberprüfung beim Dateninput einsetzen III – Nur Zahlen/Text zulassen

In Reports oder Tabellen-Blättern, in denen Nutzer/Kollegen Eingaben machen müssen, empfiehlt es sich, mögliche Fehler durch ungewünschte Nutzereingaben beim Dateninput möglichst zu vermeiden/proaktiv abzufangen. Hierzu eignet sich die Excel-Funktionalität DATEN → DATENÜBERPRÜFUNG→ DATENÜBERPRÜFUNG.

Mit benutzerdefinierten Gültigkeitsregeln („Zulassen: Benutzerdefiniert“) lassen sich im Vergleich zu den Vorgaben für Zahlen-, Datums-, Textlängen- oder Listeneingaben (siehe die vorherigen Tipps) weitere interessante Vorgaben gestalten. Hierbei ist eine Formel einzugeben, die den Wahrheitswert WAHR oder FALSCH ergeben muss. Die Eingabe im Prüfungsbereich wird nur akzeptiert, wenn die Formel WAHR ist. Es kann beispielsweise vorab festgelegt werden, ob in einer Spalte nur Zahlen – oder alternativ nur Text – eingetragen werden sollen.

Sollen beispielsweise (wie im Fall bei den Standardkosten) in Ihrer Liste nur Zahlen eingetragen werden können, ist die jeweilige Zelle zu selektieren und dann unter DATENÜBERPRÜFUNG → ZULASSEN die Option „Benutzerdefiniert“ zu aktivieren und die Formel aus der Abb. unten einzugeben. Soll hingegen nur Text eingegeben werden können, wäre hingegen =ISTTEXT einzugeben.

 

 

 

34. Datenüberprüfung beim Dateninput einsetzen IV – Duplikate vermeiden

Wie in den vorherigen Tipps erläutert, können vorab über die DATENÜBERPRÜFUNG definierte Gültigkeitsregeln dabei helfen, dass bestimmte Fehler erst gar nicht entstehen, sondern direkt bei der Eingabe abgefangen werden. Mit benutzerdefinierten Gültigkeitsregeln lassen sich im Vergleich zu den Vorgaben für Zahlen-, Datums-, Textlängen- oder Listeneingaben weitere interessante Vorgaben gestalten.

So kommt es z.B. bei der Pflege von (Stammdaten-)Listen in Excel zu dem häufig auftretenden Problem, das doppelte Werte (wie Produktcodes) vorhanden sind, obwohl diese eigentlich (ein-)eindeutig sein sollten. Eine sinnvolle Einschränkung, die sich bei einer in Excel geführten Stammdatenliste – insbesondere bei Elementen wie dem Produkt- bzw. sonstigen eindeutigen Identifikations-Codes oder -Schlüsseln – anbietet, ist daher also die Vermeidung von doppelten Einträgen (sog. Duplikate). Auch dies lässt sich mit benutzerdefinierten Gültigkeitsregeln (analog zur Vorgehensweise mit ISTZAHL, siehe den vorherigen Tipp) mit der Funktion ZÄHLENWENN umsetzen: DATEN → DATENÜBERPRÜFUNG→ DATENÜBERPRÜFUNG → ZULASSEN, die Option „Benutzerdefiniert“ aktivieren und die Formel aus der Abb. unten eingeben.

 

 

 

 

35. Datenüberprüfung beim Dateninput einsetzen V – Sprechende Fehlermeldung einsetzen

Beim Einsatz von Datenüberprüfungen – und der damit beschränkten Möglichkeit der Dateneingabe (siehe auch die vorherigen Tipps) – ist es im Regelfall immer empfehlenswert den Kollegen zu erläutern, was sie denn eigentlich genau tun bzw. eingeben sollen. Mit jeder Regel können daher Eingabe- und Fehlermeldungen formuliert werden.

Die Eingabemeldung erscheint, sobald Sie mit der Maus auf eine mit einer Gültigkeitsregel versehene Zelle gehen; die Fehlermeldung, wenn falsche – d.h. vom Gestalter der Tabelle/Ihnen nicht gewünschte – Werte in eine Tabelle eingeben werden.

Die Standard-Fehlermeldung trägt dabei nicht sonderlich zum Verständnis bei. Diese Meldungen können aber auch im Menü (siehe Abb.) unter EINGABEMELDUNG bzw. FEHLERMELDUNG frei eingegeben werden (hier exemplarisch für Excel-Tipp 30 – nur Werte größer Null sind erlaubt). Unter FEHLERMELDUNG ist unter TYP auch die weitere Vorgehensweise festzulegen: Dabei wird mit „Stopp“ die Eingabe geblockt, und der Vorgang muss abgebrochen werden, während bei „Warnung“ und „Information“ die Eingabe – trotz der erscheinenden Warnhinweise – getätigt werden kann.

 

 

 

 

36. Inhalte gruppieren (und nicht ausblenden)

Auszublendende Inhalte (Spalten oder Zeilen) sind immer nur über GRUPPIEREN (DATEN GRUPPIEREN) auszublenden, niemals tatsächlich über AUSBLENDEN.

Gruppiert werden kann zudem auch mit der Tastatur (sog. Shortcuts/Tastenkürzel): ALT + SHIFT + (Pfeiltaste nach rechts)

Ungruppiert mit ALT + SHIFT + (Pfeiltaste nach links)

 

 

37. Einfach nach Texten filtern

Filtern gehört zu den Routinetätigkeiten in Excel. Seit Excel 2010 ist die Funktionalität dabei um eine hilfreiche und sehr flexible Suchfunktion erweitert worden. Bei einer Kunden-Liste (oder wie hier im Beispiel Produkt-Liste), die durchaus sehr viele Daten enthalten kann, entfällt nun das mühsame Scrollen an das Listenende, um etwa einen bestimmten Zulieferer- oder Produktnamen zu finden. Stattdessen können Sie nun die zu filternden Textteile direkt eingeben.

 

 

 

 

38. Schnelle Zeilennummerierung

Um schnell eine fortlaufende Zeilennummerierung zu erstellen, kann mit ZEILE gearbeitet werden. Einfach =Zeile() in die erste Zeile schreiben und die Formel mit AUTOAUSFÜLLEN (siehe Excel-Tipp Nr. 7.) herunterziehen. Eine Spaltennummerierung können Sie hingegen mit der Funktion SPALTE, also =Spalte(), erstellen.

 

 

39. Ganze Spalte/Zeile markieren mit Tastenkürzeln/Shortcuts

Zeilen oder Spalten können Sie mit der Maus markieren, indem Sie auf die Zeilennummer oder auf den Spaltenbuchstaben mit der linken Maustaste klicken. Schneller wird es oft über die Tastatur mit den folgenden Tastenkürzeln/Shortcuts gehen:

  • Ganze Zeile markieren: SHIFT+LEERTASTE
  • Ganze Spalte markieren: STRG+LEERTASTE

 

 

40. In Arbeitsmappen zwischen Blättern navigieren

Einzelne Arbeitsblätter in einer Datei können Sie nicht nur mit der Maus, sondern auch schnell mit einer Tastenkombination ansteuern. Drücken Sie hierzu auch STRG+Bild, wenn Sie in der Arbeitsmappe vorwärtsschreiten wollen, und STRG+Bild, wenn Sie zurücklaufen möchten.

 

 

41. Intelligente Tabellen einsetzen I – automatische Erweiterung

Ab Excel 2007 können Sie die sog. intelligenten Tabellen (unter: START → ALS TABELLE FORMATIEREN oder über Shortcut STRG+t) einsetzen. Im erscheinenden Menü ist der zu markierende Bereich zu prüfen und festzulegen, ob die Tabelle Überschriften enthält, und abschließend mit OK zu bestätigen.

Nach der Umwandlung sind das automatische Setzen der Filter sowie das neue Layout (kann selbstverständlich unter TABELLENFORMATVORLAGEN geändert werden) die äußerlich markantesten Änderungen. Zudem erscheint das Menü TABELLENTOOLS, in dem alle relevanten Einstellungen für die intelligente Tabelle getätigt werden können. Ähnlich der Formatvorlagen aus früheren Versionen können Tabellen durch die zur Verfügung gestellten Layout-Optionen schnell und einfach formatiert werden – eine durchaus nützliche Funktionalität! Dies ist aber noch lange nicht alles (siehe die nächsten Tipps).

 

 

 

42. Intelligente Tabellen einsetzen II – automatische Erweiterung

Im letzten Tipp (Nr. 41) wurden die Formatierungsmöglichkeiten von intelligenten Tabellen vorgestellt. Dabei ist der Menü-Name ALS TABELLE FORMATIEREN – vorsichtig formuliert – etwas unglücklich, ja fast schon irreführend gewählt: Denn Wirkung und Anwendungsmöglichkeiten der intelligenten Tabellen gehen weit über die Formatierungsunterstützung hinaus. Deutlich wichtiger sind die zusätzlichen/eigentlichen Funktionalitäten, die einhergehende Objektorientierung, die die Tabellen wirklich intelligent machen: Die Tabelle wird durch Eingabe eines neuen Zellenwerts (in der Spalte rechts oder der Zeile darunter) automatisch erweitert. Damit werden Bezugsfehler (beim Einfügen neuer Werte) unwahrscheinlicher und auch der sonst notwendige Anpassungsaufwand bei der Erweiterung von Bezügen entfällt.

Dies kann das Arbeiten mit Formeln, Diagrammen und Pivot-Tabellen erheblich erleichtern. Auch die Formatierung der Tabelle kann durch Ziehen an der rechten unteren Tabellenecke automatisch erweitert werden. Tabellen erhalten zudem automatisch einen Namen, der selbstverständlich geändert werden kann – dieser (und nicht mehr etwaige Zellbezüge) dienen dann als Referenz (z.B. für Pivot-Tabellen, siehe auch die Abbildung unten).

 

 

 

 

43. Intelligente Tabellen einsetzen III – strukturierte Verweise

Neben dem Formatierungs- und Erweiterungseffekt der intelligenten Tabellen (siehe die zwei vorherigen Tipps) ergeben sich auch Änderungen/Vorteile beim Arbeiten mit Formeln:

Durch Verwendung von intelligenten Tabellen (unter: START → ALS TABELLE FORMATIEREN oder über Shortcut STRG+t) entstehen beim Arbeiten mit Formeln sog. strukturierte Verweise (anstatt einfacher Zellbezüge) und somit sprechende Namen (beispielsweise Preis*Menge anstelle A2*B2), die Excel-Reports und Formel-Konstrukte verständlicher – und damit auch besser nachvollziehbar – machen können (siehe Abb.).

 

Weitere Vorteile der intelligenten Tabellen sind:

  • Bei Eingabe einer Formel in die erste Zelle einer Spalte wird die restliche Spalte automatisch berechnet. DAS AUTOAUSFÜLLEN/„Herunterziehen“ entfällt somit.
  • Einfach Teilergebnisse auswerten durch Hinzufügen einer Ergebniszeile.

Weitere Vorteile ergeben sich zudem auch in Verbindung mit Diagrammen (siehe nächster Tipp).

  

 

 

 

44. Intelligente Tabellen einsetzen IV – Diagramme

Die bereits vorgestellten intelligenten Tabellen (siehe die vorherigen Tipps) bieten auch nützliche Anwendungsmöglichkeiten zur Dynamisierung bzw. interaktiven Nutzung von Diagrammen:

  • Einfache Erstellung von dynamischen, z.B. monatlich „mitwachsenden“ Diagrammen aufgrund der automatischen Erweiterung des Diagramms bei Tabellenerweiterung.
  • Werden Daten in der Tabelle gefiltert, spiegeln Diagramme, die auf dieser Tabelle basieren, automatisch auch die gefilterten Daten wider. Eine „normale Filterung“ in der intelligenten Tabelle schlägt sich auf das Diagramm durch: Es können also </> oder auch Top-X-Analysen (siehe Tipp Nr. 1) für die Zahlenbasis durchgeführt und mit dem Diagramm visualisiert werden (siehe Abb.).

 

 

Ab Excel 2013 können zudem auch Datenschnitte auf intelligente Tabellen angewendet werden (siehe nächster Tipp).

 

 

45. Intelligente Tabellen einsetzen V: Datenschnitte als Mittel zur interaktiven Analyse in Reports/Dashboards (ab Excel 2013)

Datenschnitte bieten die Möglichkeit zur dynamischen und interaktiven Analyse von Daten durch visuelle Filter. Dabei werden alle Elemente und aktiven Filterkriterien im Live-Modus angezeigt. Datenschnitte sind daher ein überaus nützliches Tool, gerade um schnell und flexibel verschiedene Analysen durchführen zu können! Sie eignen sich somit auch hervorragend zum Einsatz in Verbindung mit intelligenten Tabellen/Diagrammen (ab Excel 2013) sowie mit aus Pivot-Tabellen und -Charts erstellten Analyse-Reports/Dashboards (ab Excel 2010). Sie können den Empfängern eines Reports eine einfache interaktive Steuerung bzw. im Rahmen eines Meetings (z.B. zur Ergebnisbesprechung) dem Controlling auch das spontane Beantworten spezifischer Fragen ermöglichen.

Um Datenschnitte in Verbindung mit normalen Diagrammen in Excel 2013 einzusetzen, ist im Menü TABELLENTOOLS (erscheint bei Klicken in die intelligente Tabelle) DATENSCHNITT EINFÜGEN zu wählen. Hier kann dann aus den vorhandenen Spaltenüberschriften gewählt werden. Im Beispiel ist „Standorte“ anzuklicken und mit OK zu bestätigen. Es erscheint dann der Datenschnitt mit allen Standorten. Wählt man nun einen oder mehrere (durch Halten von STRG) Standorte aus, so aktualisiert sich die intelligente Tabelle – und damit auch ein auf ihr basierendes Diagramm.

 

 

 

Ab Excel 2010 können Datenschnitte (in einer Pivot-Tabelle stehend) unter PIVOTTABLE → TOOLS → ANALYSIEREN → FILTERN → DATENSCHNITT EINFÜGEN eingefügt werden. Datenschnitte stellen also eine sehr einfache Möglichkeit dar, um interaktive Diagramme zu erstellen.

 

 

46. Einstellen der Standard-Anzahl von Tabellenblättern in einer neuen Arbeitsmappe

Standardmäßig enthält jede neue Arbeitsmappe drei leere Tabellenblätter. Wird eines davon (oder vielleicht auch zwei) nicht benötigt, so sollten diese gelöscht werden. Damit muss der Empfänger eines Berichts sich nicht erst durch die leeren Tabellenblätter klicken, um zu erkennen, dass hierin keinerlei Informationen enthalten sind.

Es kann daher empfehlenswert sein, die Standard-Einstellung zu ändern und nur ein oder zwei Tabellenblätter in einer neuen Arbeitsmappe zu haben. Falls weitere benötigt werden, lassen sich diese dann später (durch Klick auf das Plus-Zeichen in der Leiste) hinzufügen.

Diese Standard-Einstellung kann geändert werden unter: DATEI → OPTIONEN → ALLGEMEIN → SO VIELE ARBEITSBLÄTTER EINFÜGEN

 

 

47. Kopieren von Tabellenblättern

Um ein bereits bestehendes Tabellenblatt zu kopieren, klickt man mit einem Rechtsklick auf das entsprechende Tabellenblatt am unteren Rand der Datei und wählt dann VERSCHIEBEN ODER KOPIEREN …

In dem sich öffnenden Menü muss nun im Feld KOPIE ERSTELLEN ein Häkchen gesetzt werden; unter „Einfügen vor“ kann ausgewählt werden, vor welchem bestehenden Tabellenblatt die Kopie eingefügt werden soll. Excel erstellt dann die gewünschte Kopie des ausgewählten Tabellenblatts.

Schneller können Sie eine Kopie zudem mit der Taste STRG (Ctrl auf englischen Tastaturen) erstellen. Halten Sie diese gedrückt hält und ziehen Sie das gewünschte Tabellenblatt an eine andere Stelle der Leiste. Die Kopie wird dann eingefügt.

 

 

48. Entfernen der Gitternetzlinien

Unter ANSICHT können die Gitternetzlinien durch Wegnahme des Häkchens ausgeblendet werden. In vielen Fällen ist dies empfehlenswert und übersichtlicher. Außerdem werden die Gitternetzlinien nicht mitgedruckt; die Ansicht ohne Gitternetzlinien vermittelt auch einen besseren Einblick darüber, wie eine Excel-Tabelle in gedruckter Form aussieht.

 

 

49. Mit STRG+1 einfach formatieren

Stehen Sie in einer Zelle, können Sie mit STRG+1 den Formatierungsdialog aufrufen (START→ FORMAT → ZELLEN FORMATIEREN).

 

 

 

Extra-Tipp: Dasselbe gilt aber auch bezüglich des Formatierungs-Menüs jedes anderen beliebigen Objekts in Excel, wie insbesondere bei Diagrammen, aber auch Formen etc. Gerade bei der Formatierung von Datenreihen in Diagrammen kann das Selektieren mit der linken Maustaste und Drücken von STRG+1 deutlich schneller/einfacher sein als das Anklicken mit der rechten Maustaste und der Auswahl des entsprechenden Unter-Menüs.

 

 

50. Druckbereiche definieren und aufheben

Ein typisches Problem: Sie möchten (nur) einen bestimmten Bereich Ihrer Tabelle ausdrucken – beispielweise den Output einer Berechnung. Auf demselben Blatt befinden sich aber noch Nebenkalkulationen etc., die nicht für den Druck benötigt werden. Hierfür können Sie – einfach, aber effektiv! – Druckbereiche einsetzen:

  • Dafür markieren Sie zunächst den gewünschten Bereich.
  • Anschließend wechseln Sie in das Register SEITENLAYOUT. Unter „Seite einrichten“ klicken Sie auf die Schaltfläche und wählen DRUCKBEREICH FESTLEGEN. Unter DRUCKBEREICH AUFHEBEN kann dieser wieder aufgehoben werden.

 

 

Nun wird Excel nur den markierten Bereich – und nicht das ganze Arbeitsblatt – drucken!

 

 

51. Tabellenfilter schnell an-/ausschalten

Wer viel mit Listen arbeitet, wird regelmäßig Filter anwenden (müssen). Schneller, als diese über das Menü DATEN FILTERN aufzurufen, geht es – wie immer – mit einer Tastenkombination. Um schnell einen Filter hinzufügen bzw. auszuschalten, einfach in die Liste klicken und

STRG + SHIFT+ L

drücken.

 

 

52. Formeln mit dem Funktions-Assistenten eingeben – Schritt 1

Neben der direkten Eingabe von Formeln können Sie auch die menügestützte Variante über den Funktionsassistenten verwenden. Dies eignet sich insbesondere für Anfänger, die den Aufbau gerade von komplexeren Funktionen mit mehreren Argumenten nicht kennen oder auch bei der Eingabe von verschachtelten Funktionen Probleme haben. Der Funktionsassistent kann unter FORMELN → FUNKTION EINFÜGEN oder durch Klick auf die „fx-Schaltfläche“ in der Formel-Eingabeleiste aufgerufen werden (vgl. auch die untere Abb.):

  • Im Bereich FUNKTION SUCHEN können Sie den Namen der gesuchten Funktion eingeben und diesen mit Klick auf die Schaltfläche OK suchen.
  • Der Bereich KATEGORIE AUSWÄHLEN bietet Ihnen die Möglichkeit, die gesuchte Funktion aus einer bestimmten Kategorie auszuwählen. Die Unterteilung aller vorhandenen Funktionen in mehrere Kategorien dient der Übersichtlichkeit. Unter „Zuletzt verwendet“ finden Sie die Funktionen, die Sie tatsächlich auch als letztes verwendet haben.
  • Je nach gewählter Kategorie finden Sie im Bereich FUNKTION AUSWÄHLEN eine Liste aller Funktionen, die es in dieser Kategorie gibt. Mit einem Klick auf eine Funktion wählen Sie diese aus.

 

 

 

53. Formeln mit dem Funktions-Assistenten eingeben – Schritt 2

Nachdem im Tipp der letzten Woche der erste Schritt, die Funktionseingabe, erläutert wurde, geht es im zweiten Tipp zum Funktionsassistenten um die konkrete Eingabe der Argumente der Funktion, die im ersten Schritt gewählt wurde (hier: LINKS, siehe vorherigen Tipp). Gerade bei neuen Formeln kann die hierin vorhandene Beschreibung und Erklärung der jeweiligen Argumente gegebenenfalls hilfreich sein. Auch die Hilfefunktion steht hier zur Verfügung.

 

 

 

 

Alternativ gelangen Sie in dieses Menü bei der konkreten Eingabe einer Formel – wenn diese bereits von Excel erkannt wurde – durch Klick auf die „fx-Schaltfläche“ oder auch mittels der Tastenkombination STRG+a (siehe auch Tipp 15).

 

 

54. Formel- bzw. Arbeitsblatt-Zusammenhänge bei fremden Dateien verstehen

Insbesondere beim Empfang von fremden Date(ie)n kann es vorteilhaft sein, sich einen ersten Überblick zu verschaffen und in Erfahrung zu bringen, in welchen Zellen sich welche Formeln/Funktionen bzw. Funktionalitäten befinden. Formel- bzw. Arbeitsblatt-Zusammenhänge lassen sich beispielsweise unter START → SUCHEN UND AUSWÄHLEN im Menü INHALTE AUSWÄHLEN erkennen. Beim Klicken auf „Formeln“ werden nur die Zellen markiert, in denen sich Formeln bzw. Funktionen befinden. Zudem lassen sich auch noch mehr spezifische Details zu Formeln wie Fehler- oder Wahrheitswerte (WAHR/FALSCH) abfragen. Oder es kann auch nach Zellen gesucht werden, die Konstanten, bedingte Formatierungen, Datenüberprüfungsregeln, Kommentare oder Textwerte enthalten. Auch sich im Arbeitsblatt befindliche Leerzellen oder Objekte wie Diagramme können durch dieses Menü identifiziert bzw. ausgewählt werden – sehr hilfreich gerade bei fremden Dateien! 

Nachdem im Tipp der letzten Woche der erste Schritt, die Funktionseingabe, erläutert wurde, geht es im zweiten Tipp zum Funktionsassistenten um die konkrete Eingabe der Argumente der Funktion, die im ersten Schritt gewählt wurde (hier: LINKS, siehe vorherigen Tipp). Gerade bei neuen Formeln kann die hierin vorhandene Beschreibung und Erklärung der jeweiligen Argumente gegebenenfalls hilfreich sein. Auch die Hilfefunktion steht hier zur Verfügung.

 

 

 

 

55. Überblick über die verwendeten Formeln verschaffen

Insbesondere beim Erhalten von fremden Date(ie)n ist es vorteilhaft, sich einen ersten Überblick zu verschaffen und in Erfahrung zu bringen, in welchen Zellen sich welche Formeln/Funktionen bzw. Berechnungen befinden. Unter FORMELN → FORMELN → ANZEIGEN kann die Formelsicht aktiviert werden: Statt den Formelergebnissen werden dann die Formeln angezeigt, die zu diesen führen. Dies stellt einen ersten zweckmäßigen Schritt zur Orientierung in fremden Dateien dar. Es kann sich zudem anbieten, ein neues Fenster zu öffnen, um so beide Sichten (d.h. sowohl die gewohnte Formelergebnis- als auch die Formelansicht) unter- oder nebeneinander gleichzeitig zu betrachten (vgl. Abb.).

 

 

 

Extra-Tipp: Auch die Funktion FORMELTEXT, die Ihnen ab Excel 2013 zur Verfügung steht, kann für solche Zwecke hilfreich sein. Diese gibt eine Formel als eine Zeichenfolge zurück bzw. den in der Bearbeitungsleiste angezeigten Eintrag der Zelle, die als Bezug gewählt wurde.

 

 

56. Formelzusammenhänge verstehen

Unter FORMELN → FORMELÜBERWACHUNG gibt es die Funktionalitäten SPUR ZUM VORGÄNGER bzw. NACHFOLGER:

  • SPUR ZUM VORGÄNGER zeigt zu einem Formelergebnis, welche Zellen in die Berechnung eingeflossen sind (siehe auch die untere Abb.)
  • SPUR ZUM NACHFOLGER zeigt für eine Zelle/Formel, in welche weiteren Formeln diese eingeflossen ist.

 

 

Dies ist sehr praktisch, um sich gerade bei fremden Arbeitsblättern einen Überblick zu verschaffen. Auch kann man sich vor dem Löschen einer Zelle vergewissern, ob sie nicht noch irgendwo in Formeln verwendet wird oder aber bei einem Fehler schauen, welche Zellen an der Berechnung beteiligt sind.

 

 

57. Formeln/Dateien auf Fehler prüfen

Es kann sich oft empfehlen (egal ob in fremden oder eigenen Dateien), die Korrektheit der Formeln im Arbeitsblatt zu prüfen. Hierzu ist im Menü FORMELN die Funktionalität FEHLERÜBERPRÜFUNG zu starten:

 

 

Es kann dann mit Schaltflächen zwischen allen im Arbeitsblatt befindlichen Formeln navigiert und entschieden werden, ob etwaige Fehler bearbeitet oder ignoriert werden sollen. Auch eine Hilfe-Funktionalität steht hier zur Verfügung. Im Zweifel können alle Berechnungsschritte bis zum Formelergebnis nachvollzogen werden.

 

 

58. Formeln schützen

Formeln sind meist der empfindlichste Teil eines Excel-Modells. Aus Versehen vorgenommene Änderungen können dabei große Auswirkungen auf die Ergebnisse einer Kalkulation haben. Unter Umständen kann es sehr schwierig sein, den Fehler zu finden. Es kann sich daher empfehlen, Formeln zu sperren und den Arbeitsblattschutz zu aktivieren, so dass niemand Änderungen an den Formeln vornehmen kann. Bei Aktivierung des Blattschutzes können nur noch Zellen angesprochen werden, die nicht das Attribut GESPERRT besitzen. Standardmäßig blockiert Excel allerdings das ganze Tabellenblatt. Es kann sich aber auch eine Beschränkung auf die wirklich schützenswerten Zellen erfolgen. Dazu ist es zuerst erforderlich, dieses Attribut zunächst für alle Zellen auszuschalten und dann gezielt für die relevanten (hier die Formel-)Zellen wieder einzuschalten und erst dann den Blattschutz zu aktivieren. Ist dies gelungen, zeigt Excel eine Fehlermeldung an, wenn versucht wird, eine Formel in der Tabelle zu ändern oder zu löschen. Im Detail ist wie folgt vorzugehen:

  1. Das Zellformat GESPERRT – das standardmäßig gesetzt, aber nicht aktiv ist – ist auf dem ganzen Tabellenblatt zu entfernen. Hierzu ist zuerst mit STRG+a das ganze Blatt zu markieren und dann mit STRG+1 (siehe auch Tipp Nr. 49) der Dialog zur Zellenformatierung zu öffnen. In diesem Menü ist in der Registerkarte „Schutz“ das Kästchen GESPERRT zu deaktivieren und mit OK zu bestätigen.
  2. Nun sind alle Zellen zu markieren, die Formeln enthalten. Hierzu mit der Taste F5 den Dialog GEHE ZU öffnen. Unter INHALTE… ist FORMELN zu wählen. Damit wurden alle Formelzellen der Tabelle markiert.
  3. Nun ist das in Schritt 1 deaktivierte GESPERRT wieder zu aktivieren, um das Zellenformat nur für die im obigen Schritt markierten Formel-Zellen zu setzen.
  4. Zuletzt ist nun der Blattschutz unter ÜBERPRÜFEN → BLATT SCHÜTZEN zu aktivieren. Falls die gesperrten Zellen zumindest markiert und kopiert werden dürfen, können die Einstellungen unter „Blatt schützen“ bleiben wie sie sind. Andernfalls ist die Box vor GESPERRTE ZELLEN AUSWÄHLEN zu deaktivieren. Im Feld „Kennwort zum Aufheben des Blattschutzes“ kann zum Aufheben des Blattschutzes ein Kennwort eingegeben werden.

 

 

59. Formeln richtig einsetzen – Input-Parameter auslagern

Annahmen und Parameter (wie Wachstumsraten, Wechselkurse, Provisions- und Steuersätze etc.), auf die sich Formeln beziehen, sind auf Input-Blätter oder -Zellen auszulagern und nicht direkt in den Formeln zu verwenden (siehe gelbe Markierung der Provisionsberechnung bei Verkäufer 1 in der Abbildung). Das ist übersichtlicher, und die Formeln können so auch viel leichter angepasst und verstanden werden.

 

 

 

 

60. Formeln unverändert übertragen

Wollen Sie eine Formel aus einer Zelle in eine andere Zelle kopieren, werden – bei Verwendung von relativen Bezügen – diese entsprechend auf die neue Zelle angepasst. Ist dies nicht gewünscht, dann ist die Zelle am besten mit STRG+x auszuschneiden und an der gewünschten Stelle wieder einzufügen. Dadurch unterbleibt eine Anpassung auch bei relativen Bezügen.

 

 

61. Zeit- und Datumseingabe einfach gemacht – mit Kürzeln und Funktionen

Um das aktuelle Datum aufzurufen, können Sie einfach STRG +. [Punkt] drücken.

Für die aktuelle Zeit hingegen STRG+SHIFT+. [Punkt]

Je nach Anwendungszweck eignen sich diese Tastenkürzel vielleicht nicht immer. Soll sich das Datum beispielsweise jeden Tag bei Aufruf der Datei ändern, können Sie die Funktion =HEUTE() einsetzen.

 

 

Extra-Tipp: Soll auch noch die aktuelle Zeit ergänzt werden, bietet sich hingegen die Funktion =JETZT() an.

  

 

 

62. Überschreiten von Deadlines automatisch markieren

Mit der bereits aus dem vorherigen Tipp bekannten Funktion HEUTE – in Kombination mit der bedingten Formatierung – kann beispielsweise hervorgehoben werden, ob das aktuelle Datum größer (oder kleiner, gleich etc.) als ein anderes Datum (etwa eine gegebene Deadline in einem Projekt oder Reporting-Kalender) ist.

Unter START → BEDINGTE FORMATIERUNG → NEUE REGEL → FORMEL ZUR ERMITTLUNG DER ZU FORMATIERENDEN ZELLE VERWENDEN geht dies im unteren Beispiel durch

=HEUTE()>D2 (siehe auch die Abb.)

Das in Rot gefärbte Datum („06. Dez“) signalisiert nun die Überschreitung der Deadline und damit dringenden Handlungsbedarf. Gezielt eingesetzt, können somit überfällige Aktivitäten gut hervorgehoben werden.

 

 

 

 

 

63. Jahre, Monate und Tage in Excel

Mit der bereits aus dem vorherigen Tipp bekannten Funktion HEUTE in Kombination mit TAG, MONAT oder JAHR kann – unter Bezug auf die Zelle, in der HEUTE steht (hier: G1) – ermittelt/ausgelesen werden, welcher aktuelle Monat, Tag oder welches aktuelle Jahr es ist. Dieses „Zerlegen“ eines Datums funktioniert selbstverständlich auch bei beliebig anderen Daten. Ohne Zellverweise ergibt es den aktuellen Tag, Monat oder Jahr.

 

 

Extra-Tipp: Mit der Funktion DATUM, können Sie (z.B. über die obigen Funktionen) zerlegte Teile eines Datums auch wieder zusammensetzen.

 

 

64. Mit der Funktion MONAT und der Funktion WAHL die Monatszahlen in Monate umwandeln

Die bereits bekannte Funktion MONAT wandelt ein Datum in einen Monat um. Der Monat wird dabei als ganze Zahl ausgegeben (siehe den vorherigen Tipp Nr. 63), die einen Wert von 1 (Januar) bis 12 (Dezember) annehmen kann. Soll die Ziffernangabe in einen Monatsnamen umgewandelt werden, so kann u.a. mit der Funktion WAHL (gehört zu den Verweisfunktionen und verwendet einen Index, um einen Wert aus der Liste der Werteargumente zurückzugeben) gearbeitet werden.

Hierzu müssen als Wert1 bis Wert12 die einzelnen Monate (von Januar bis Dezember) des Jahres als Werteargumente von WAHL definiert werden. Dann gibt WAHL den entsprechenden Monat zurück, wenn eine Zahl zwischen 1 und 12 als Index verwendet wird. Im Beispiel werden die jeweiligen Index-Werte von WAHL also dynamisch über MONAT kreiert.

 

 

 

 

65. Wochentag

Über die Tabellenfunktion WOCHENTAG (Eingabe von „=Wochentag“ in eine Zelle) können Sie überprüfen, welcher Wochentag an einem gewählten Datum ist. Für jeden Tag gibt Excel eine Zahl aus – einen Wert von 7 (für Samstag) bzw. einen Wert von 1 (für Sonntag) (siehe auch die Tabelle unten).

Durch Verknüpfen mit FORMELN (wie beispielsweise WENN) können Sie beispielsweise herausfinden, ob ein Wochentag vorliegt oder das Datum am Wochenende liegt. Auf diese Weise könnten Sie dann beispielsweise mit variablen Stundensätzen für Arbeitstage und Wochenenden rechnen.

 

 

Tage in Excel

1

Sonntag

2

Montag

3

Dienstag

4

Mittwoch

5

Donnerstag

6

Freitag

7

Samstag

 

 

Reminder: Falls eine Umwandlung der ausgegebenen Zahl in den Tag gewünscht ist, können Sie dann beispielsweise wieder mit WAHL (siehe vorherigen Tipp), mit WENN oder auch über die oben abgebildete Hilfsliste und einem SVERWEIS arbeiten.

 

 

66. Kalenderwoche in Excel ermitteln

Geht es um Termine, meist in der Zukunft, dann ist die Kalenderwoche hierzulande häufig eine der wichtigsten Angaben. Dementsprechend ist es zweckmäßig, die korrekte Excel-Funktion hierfür zu kennen. Mit Excel 2013 hat eine neue Funktion (und zwar ISOKALENDERWOCHE, siehe unten) Einzug gehalten, die uns dies erleichtert.

Denn bis einschließlich Excel 2010 stand in Excel nur die Formel KALENDERWOCHE zur Verfügung. Diese Funktion rechnet allerdings nach amerikanischem Standard. In Amerika ist die erste Kalenderwoche immer diejenige Woche, die den 01. Januar enthält. In Deutschland hingegen ist die erste Woche des Jahres laut ISO (Norm ISO 8601) als die Woche definiert, in die mindestens 4 Tage fallen (vereinfacht: die Woche, die den 04. Januar enthält).

Um mit KALENDERWOCHE zum korrekten Ergebnis zu kommen, muss(te) hierzulande noch ein Parameter (21) eingegeben werden =KALENDERWOCHE(Datum;21)

Mit der neuen Excel 2013-Funktion ISOKALENDERWOCHE fällt dies nun weg. Diese gibt immer die richtige, also die für Deutschland offiziell geltende Kalenderwoche aus (siehe Abb.).

 

 

 

67. Die „versteckte“ Funktion DATEDIF

Über die „versteckte“ Funktion DATEDIFF (taucht nicht in der Autovervollständigung auf) können Sie eine genaue Datumsdifferenz gemäß den Angaben unten ermitteln.

 

 

„y“ ---> Differenz in Jahren

„m“ --> Differenz in Monaten

„d“ ---> Differenz in Tagen

„ym“ --->Differenz in Monaten, sofern die Datumsangaben im gleichen Jahr liegen

„yd“ ---> Differenz in Tagen, wenn die Datumsangaben im gleichen Jahr liegen

„md“ --->Differenz in Tagen, sofern die Datumsangaben im gleichen Jahr und Monat liegen

  

 

Durch Angabe der beiden Daten und „d“ können Sie beispielsweise ermitteln, wie viele Tage noch zwischen den beiden Daten liegen.

 

 

 

Die Berechnung der Zahl der zwischen zwei Terminen liegenden Kalendertage kann einfach mit einer Subtraktion erfolgen. Allerdings erlaubt DATEDIF eben noch weitere Möglichkeiten der Anzeige (siehe oben). Sollen hingegen nur die dazwischenliegenden Arbeitstage gezählt werden, gibt es weitere Möglichkeiten (siehe hierzu den nächsten Tipp).

 

 

Dipl.-Kfm. Daniel Unrein, Senior-Controller bei der NORMA Group SE, Verfasser des bei Vahlen erschienenen Fachbuchs „Excel im Controlling”

 

 

 

BC 2015 / 2016 / 2017