Excel Toolbox

Hier finden Sie Tipps, Tricks und Tools für Excel im Controlling, Beispiele aus unseren Büchern und Seminaren und jede Menge Tools zum Downloaden.

Tipps & Tricks

Mappen und Tabellen

Die Startmappe

Excel bietet im Gegensatz zu Word oder PowerPoint keine Druckformatvorlage für neue Mappen an. Wenn Sie eine Mappe so vorbereiten wollen, dass sie automatisch als Vorlage für neue Mappen (auch die leere Mappe nach dem Start) dient, gehen Sie so vor:

Formatieren Sie die Mappe wie gewünscht, weisen Sie Zahlenformate, Kopf/Fußzeilen, Layout zu und tragen Sie Formeln, Texte und Zahlen in die Tabellen ein. Speichern Sie die Mappe als Mustervorlage mit der Bezeichnung MAPPE.XLT bzw. MAPPE.XLTX im Startordner:

C:\Users\B\AppData\Roaming\Microsoft\Excel\XLSTART
(So finden Sie Sie Ihren Roaming-Ordner schnell: Geben Sie im Explorer ein: %appdata%)

Mit Zellen arbeiten

Transponieren

Um einen horizontalen Bereich vertikal oder umgekehrt einen vertikalen Bereich horizontal anzuzeihen, kopieren Sie den Bereich, markieren die erste Zielzelle und wählen im Kontextmenü der Maustaste Inhalte einfügen/Transponieren.

Soll der Bereich verknüpft sein, verwenden Sie die Matrixfunktion MTRANS(): Markieren Sie so viele Zielzellen, wie der Quellbereich enthält, schreiben Sie MTRANS(bereich) und drücken Sie [Strg]+[Umschalt]+[Eingabe]. Das funktioniert auch gut für Bereichsnamen: =MTRANS(Umsatz)

Nur sichtbare Zellen kopieren

Bei gefilterten Teilergebnissen und gruppierten Daten werden die unsichtbaren Daten mitkopiert. Markieren Sie den Bereich, drücken Sie [F5] und wählen Sie Inhalte. Klicken Sie auf Nur sichtbare Zellen.

Formatieren

Zeilenumbruch im Text

Verwenden Sie [Alt]+[Eingabe], um in längeren Texten einen Zeilenumbruch einzufügen. Damit wird gleichzeitig die Formatierung Zeilenumbruch in die Zelle eingefügt

Tausender-Zahlenformat

Um eine Zahl um 3 Nullen (Tausend) zu reduzieren, weisen Sie ihr einfach dieses Zahlenformat zu:
0.

Mit zwei Punkten werden 6 Nullen ausgeblendet:
0..

Spezielle Zahlenformate

Um eine Zahl mit einem Text zu versehen, weisen Sie der Zelle ein spezifisches Zahlenformat zu. Drücken Sie [Strg]+[1] und tragen Sie das benutzerdefinierte Zahlenformat ein:
0" Mio" -> 12 Mio
0" Tage" -> 23 Tage
oder, mit Bedingung:
[>0]"Gewinn: "#.##0;[Rot][ Gewinn: 100, Verlust: 300

Bedingte Formatierung

Richtig markieren

Markieren Sie alle Zellen, für die das Bedingunsformat vorgesehen ist und wählen Sie Start/Formatvorlagen/Bedingte Formatierung. Wenn Sie das Bedingungsformat ändern wollen, suchen Sie zuerst alle Zellen, die formatiert wurden. Drücken Sie dazu [F5] (Gehezu), klicken Sie auf Inhalte und wählen Sie die Option Bedingte Formate. Wenn Sie die zweite Option Alle verwenden, werden alle Zellen mit Bedingungsformaten markiert. Schalten Sie um auf Gleiche, werden alle Zellen mit dem Format der aktiven Zelle markiert.

Letzten Eintrag hervorheben

Mit dieser Formel markieren Sie den letzten Eintrag in einer Spalte (hier A):

=ANZAHL2($A:$A)=ZEILE(A1)

Tagesdatum kennzeichnen

Um in einem Bereich mit Datumswerten das aktuelle Tagesdatum hervorzuheben, markieren Sie diesen (z. B. A1:c20), wählen Start/Formatvorlagen/Bedingte Formatierung und geben unter Formeln ... ein:

=A1=HEUTE()

Wochenendtage hervorheben

Markieren Sie alle Datumswerte, in denen Sie die Samstage und Sonntage kennzeichnen wollen. Wählen Sie Start/Formatvorlagen/Bedingte Formatierung und geben Sie diese Formeln ein (hier mit Zelle A1 als aktive Zelle):

Samstage: =WOCHENTAG(A1)=7 Sonntage: =WOCHENTAG(A1)=1

Doppelte Werte hervorheben

Markieren Sie den Bereich, wählen Sie Start/Formatvorlagen/Bedingte Formatierung und Regeln zum Hervorheben von Zellen/Doppelte Werte.

Datum und Zeit

DATEDIF()

Die Funktion DATEDIF() ist aus gutem Grund nicht dokumentiert, auch nicht in Excel 2007/2010. Wir empfehlen, sie nicht zu verwenden, da sie in einigen Fällen falsch rechnet.

Kalenderwoche

Bis zur Excel-Version 2007 hat die Funktion KALENDERWOCHE() falsch gerechnet, die Kalenderwoche 1 ist nach DIN 1330 die erste Woche, die 4 Tage enthält. In Excel 2010 kann ein Parameter angegeben werden, mit dem die Kalenderwoche richtig berechnet wird:

=KALENDERWOCHE(Zelle;21)
Beispiel:

A1: 01.01.2011
A2: =KALENDERWOCHE($A$1;21)
Ergebnis: 52

Für frühere Versionen hier die KW-Funktion, die richtig rechnet:

=KÜRZEN(($A$1-DATUM(JAHR($A$1+3-REST($A$1-2;7));1;REST($A$1-2;7)-9))/7)

Rechnen mit Zeit

Ein Zeitwert ist eine Dezimalzahl zwischen 0 und 1. Die Zeit 12:00 ist dezimal 0,5. Um mit einem Zeitwert rechnen zu können, multiplizieren Sie den Zellinhalt mit 24. Die Funktion ZEITWERT() rechnet einen Text dezimal um.

Rechnen mit Negativzeiten

Da der Excel-Kalender am 1.1.1900 beginnt, ist die Berechnung einer Negativzeit (z.B. 18:00 - 21:00) nicht möglich. Das Ergebnis wäre ein Zeitwert am Vortag des ersten Tages, Excel zeigt diesen mit einer endlosen #-Kette an. Alternative: Stellen Sie den ersten Kalendertag auf den 1.1.1904 (eine Einstellung, die Excel aus Kompatibilitätsgründen zum OS von Apple mitführt):

  • Excel 2007: Office-Menü/Excel-Optionen
  • Excel 2010/2013: Datei/Optionen
  • Kategorie Erweitert, Beim Berechnen dieser Arbeitsmappe/1904-Datumswerte verwenden.

Unabhängig von dieser Einstellung können Sie mit einer WENN-Funktion dafür sorgen, dass Negativzeiten richtig berechnet werden. Hier am Beispiel einer Arbeitszeitberechnung:

=WENN(C2>B2;C2-B2;1-B2+C2)

Zeitsummen berechnen

Ergibt die Summe aus mehreren Zeitwerten einen Wert größer als 24, zeigt Excel mit dem Zahlenformat (hh:mm) für Zeitwerte nur die ersten 24 Stunden an. Ändern Sie das Zahlenformat in Datum um (TT.MM.JJ hh:mm), sehen Sie das Ergebnis in Tagen und Stunden. Wollen Sie das Ergebnis als Stundensumme sehen, setzen Sie den Platzhalter für Stunden einfach in eckige Klammern:

[hh]:mm

Rechnen und Kalkulieren

Spalten eines Bereiches adressieren

Im Controlling sollten Sie so oft wie möglich mit Bereichsnamen arbeiten. Um eine bestimmte Spalte eines Bereiches zu adresieren, verwenden Sie die Funktion INDEX(). Geben Sie nur die Spaltennummer an, lassen Sie die Zeilennummer weg. Beispiel: Der Bereich "Datenbank" listet in der 3. Spalte alle Absätze. Mit dieser Funktion berechnen Sie die Summe:

=SUMME(INDEX(Absatz;;3))

Das gleiche gilt für Zeilen, lassen Sie die Spaltennummer weg, wird die gesamte Zeile adressiert. Mit dieser Funktion zählen Sie zum Beispiel, wie viele Einträge ein Bereich in de ersten Zeile hat:

=ANZAHL2(INDEX(Bereich;1;))

Runden auf 5 Cent

Mit dieser Funktion runden Sie einen Beitrag in Zelle a1 auf 5 Cent. VRUNDEN rundet auf (weg von Null), wenn der Rest der Division von Zahl durch Vielfaches größer gleich der Hälfte von Vielfaches ist. =VRUNDEN(A1;0,05)

Zufallszahlen mit ZUFALLSBEREICH()

Die Funktion ZUFALLSBEREICH() erzeugt Zufallszahlen, geben Sie den Bereich in beiden Argumenten an:

=ZUFALLSBEREICH(von;bis)

In Excel bis Version 2003 müssen Sie die analyse-Funktionen dazu einschalten (Extras/Add-Ins).

Mehr als 7 WENN() schachteln

Die WENN()-Funktion kann bis zu 7mal geschachtelt werden. Wenn Sie mehr WENN() brauchen, addieren Sie die Funktionen einfach:

=WENN(...)+WENN() ... +WENN()

In Excel 2010 können mehr als 7 WENNs geschachtelt werden.

Tabellennamen im Formeln verwenden

Wenn Sie den Namen einer Tabelle in einer Formel verwenden wollen, verknüpfen Sie ihn in einer INDIREKT()-Funktion. Mit dieser Formel erhalten Sie beispielsweise den Inhalt der Zelle A1 in der Tabelle "Umsatz":

=INDIREKT("Umsatz!A1")

Natürlich können Sie dabi auch Zellen verknüpfen. Steht der Name der Tabelle in C5, schreiben Sie:

=INDIREKT(C5&"!A1")

Einträge zählen

Wie viele verschiedene Einträge enthält ein Bereich? Schreiben Sie eine Matrixformel, hier für den Bereich A1:A100. Drücken Sie zum Abschluss [Strg]+[Umschalt]+[Eingabe]:

=SUMME(1/ZÄHLENWENN(A13:A100;A1:A100))

Wenn der Bereich Leerzeilen enthält, muss die Formel erweitert werden. Schließen Sie wieder mit [Strg]+[Umschalt]+[Eingabe] ab:

=SUMME(1/WENN(ZÄHLENWENN(A1:A100;A1:A100)=0;1; ZÄHLENWENN(A1:A100;A1:A100)))-ANZAHLLEEREZELLEN(A1:A100)

Zeilenumbruch per Formel

Zeilenumbrüche erzeugen Sie in Zellinhalten mit [Alt]+[Eingabetaste]. Wollen Sie beispielsweise einen mehrzeiligen Text für einen Diagrammtitel oder eine Textverknüpfung konstruieren, verwenden Sie die Funktion =ZEICHEN(10). Die Funktion =ZEICHEN() erzeugt ein beliebiges ASCII-Zeichen, 10 und 13 sind die Code für CR/LF (carriage return/line feed). Beispiel:

="Das ist die erste Zeile"&ZEICHEN(10)&"Das ist die zweite Zeile"

Schutz und Sicherheit

Zellschutz mit Datenüberprüfung

valid1Ein einfacher, aber wirkungsvoller Schutz für einzelne Zellen, wenn der Blattschutz nicht angebracht ist: Markieren Sie den Bereich, weisen Sie ihm eine Datenüberprüfung zu:

Verknüpfungen

Mit der Funktion ZELLE("Dateiname") kann der Name der Tabelle in einer Zelle abgebildet werden:

=TEIL(ZELLE("Dateiname");FINDEN("]";ZELLE("Dateiname"))+1;500)

Wir legen einen Bereichsnamen (z. B. TabName) an und weisen ihm diese Formel als Bezug zu, dann kann dieser in Formeln verwendet werden. Mit dem Parameter A1 wird die Formel automatisch neu berechnet (gilt dann aber nur lokal):

=ZELLE("Dateiname";A1)

Diagramme

Farbmarkierungen nutzen

Solange sich nur die Daten ändern, die das Diagramm bereits anzeigt, werden Sie keine Probleme damit haben, denn jede Änderung wird sofort optisch umgesetzt. Schwieriger wird es, wenn Daten wegfallen oder neue Daten hinzukommen: In diesem Fall stimmt der zugewiesene Datenbereich nicht mehr. Bevor Sie jetzt ein neues Diagramm erstellen, testen Sie einfach diese Techniken:

Markieren Sie das Diagrammobjekt und achten Sie auf die Farbmarkierungen, die dabei in der Tabelle angebracht werden:

  • Eine magenta-gefärbte Linie kennzeichnet den Bereich, der die Beschriftungen (X-Achse im Säulendiagramm, Legende für Torten) enthält.
  • Grün wird diejenige Zelle markiert, die die Überschrift enthält (Legende im Säulendiagramm, Titel im Kreisdiagramm).
  • Die blaue Linie kennzeichnet den Datenbereich.

Nullabfall verhindernÄndert sich der Datenbestand, können Sie auch über Menüs oder die Multifunktionsleiste die Bereiche neu bestimmen, schneller geht es aber über die entsprechenden Farbmarkierungen. Zeigen Sie auf den Rand der Farbmarkierung und ziehen Sie diesen mit gedrückter Maustaste an eine neue Position. Um den Bereich zu vergrößern oder zu verkleinern, ziehen Sie das Füllkästchen der Farbmarkierung nach unten oder oben.

Verknüpfte Titel

Um im Titel eines Diagramms den Inhalt einer Zelle anzuzeigen, verknüpfen Sie das Titelelement: Markieren Sie es, schreiben Sie die Verknüpfung in die Bearbeitungsleiste und drücken Sie [Eingabe].

=C5

In Excel ab Version 2007 zeichnen Sie ein Textfeld, markieren den Rand des Objektes und schreiben die Verknüpfung in die Bearbeitungsleiste (nicht in die Zelle).

Datenreihen einfügen und löschen

Um eine neue Datenreihe zu erzeugen, kopieren Sie einfach die Daten für die Reihe in die Zwischenablage, markieren das Diagrammobjekt und drücken die Eingabe-Taste. Die Reihenfolge der Datenreihen im Diagramm regelt das letzte Argument der Funktion =DATENREIHE(). Tragen Sie hier die Position (bei horizontalen X-Achsen von links) ein:

=DATENREIHE(Legende;Achsenbereich:DatenBereich;Position)

Nullabfall im Liniendiagramm verhindern

Wenn in einem Liniendiagramm die Rubrikenachse größer ist als die Wertereihe, fallen in der Regel alle Datenpunkte auf den Nullwert zurück, die noch keinen Eintrag haben. Mit einer Optionseinstellung stellen Sie sicher, dass die Diagrammreihe mit dem letzten Datenpunkt endet:

Diagrammtools/Entwurf/Daten auswählen, Ausgeblendete und leere Zellen: Datenpunkte mit einer Linie verbinden.

Nullabfall verhindernDieser Tipp funktioniert leider nicht, wenn die Werte für das Diagramm mit Formeln berechnet werden oder aus Verknüpfungen stammen. In diesem Fall sind die Zellen nicht leer, auch wenn die Formel keinen Wert oder einen Nullwert berechnet. Hier hilft nur die Berechnung der Datenreihen über eine Matrixfunktion:

Drücken Sie [Strg]+[F3] und erstellen Sie einen neuen Bereichsnamen. Dieser Bereichsname überprüft, wie viele Zahlen sich in Spalte C befinden und berechnet daraus eine einspaltige Matrix ab Zelle B6:

Bereichsname: KostenKumuliert
Bezieht sich auf: =BEREICH.VERSCHIEBEN(Tabelle2!$C$6;0;0;ANZAHL(Tabelle2!$C:$C);1)

Markieren Sie im Diagramm die Datenreihe (Linie) und ändern Sie die Formel mit der Funktion DATENREIHE() in der Bearbeitungsleiste ab. Schreiben Sie den Bereichsnamen an Stelle des absoluten Bezuges:

=DATENREIHE(;Tabelle2!$A$6:$A$17;Tabelle2!KostenKumuliert;1)

Damit wird die Diagrammlinie nur bis zum letzten Eintrag gezeichnet, auch die Rubrikenachse passt sich automatisch an. Wenn Sie die Rubrikenachse vollständig zeichnen wollen, kopieren Sie eine Nullreihe in das Diagramm und entfernen die Linienfarbe dieser auf der Nulllinie gezeichneten Reihe.

Tools

Analyse-Funktionen

Excel bietet bis zur Version 2003 ein Add-In namens funcres.xla (Extras/Add-Ins). Dieses Add-In schaltet einen zusätzlichen Eintrag "Analyse-Funktionen" im Extras-Menü, unter diesem Eintrag werden Assistenten für Statistik-Funktionen (z. B. Korrelation, Histogramm) angeboten. Das Add-In hat aber noch eine weitere, wichtigere Aufgabe: Nach der Aktivierung stehen 112 (!) neue Funktionen in den verschiedenen Funktionskategorien bereit. Wer das Passwort des Add-Ins kennt, kann dieses öffnen und die Liste der Funktionen in einem Tabellenblatt überprüfen. In Excel 2007/2010 sind diese Funktionen komplett integriert, das Add-In mit den Statistik-Assistenten wird weiterhin angeboten.

PresentationMaker

presentationmakerTransportiert Bereiche aus Excel automatisch in PowerPoint-Folien, wahlweise in bereits gespeicherte oder neue Präsentationsdateien. Einfach Name der Datei, Tabellenblattname und Bereich eintragen und PresentationMaker starten. Für Excel-Versionen ab 2003.

Kennzahlenrechner

KennzRechnerAlle wichtigen Controlling-Kennzahlen auf einen Blick: Kennzahlenrechner starten, Daten eintragen oder aus der Tabelle übernehmen und Kennzahl berechnen. Für Excel ab Version 2003, in Excel 2007/2010 Register "Add-In" aktivieren zum Starten und Schließen des Makros.

SAP2XL

SAP2ExcelLernen Sie mit dieser PDF-Dokumentation die vielfältigen Möglichkeiten des Excel-Exports aus SAP kennen.

WikiVBA

WikiVBAEine vollständige Dokumentation zu VBA (Visual Basic for Applications) von Hans Herber.

varNamen

varNamenProblem: Eine Namensliste hat alle Varianten (Vorname, 2. Vorname, Name, Titel)
varNamen macht einen einheitlichen Namen daraus

EnvironmentCockpit

envcockpitEin Beispiel aus dem Buch "Geschäftszahlen visualisieren": Cockpit-Charts über dynamische Bereichsnamen realisiert, ohne Makros, aber mit raffinierten Formel- und Funktionentricks.

Projekt-Map

pmapSo lässt sich die Excel-Kamera wirkungsvoll im Controlling-Report einsetzen. Ein Beispiel aus dem Buch "Geschäftszahlen visualisieren".

Balanced Scorecard

BScorecardDie Balances Scorecard enthält Controlling-Kennzahlen aus unterschiedlichen Perspektiven: Finanzen, Kunden, Prozesse. In diesem Beispiel aus dem Buch "Geschäftszahlen visualisieren" werden die Kennzahlen über verschiedene Diagrammtypen (Tachometer, Balken) und mit Ampeln aus Bedingungsformaten visualisiert.

Sondernewsletter der ControllerSpielwiese

Mit Super-Tipps, nicht nur für Personalcontroller:

ControllerSpielwiese

Video: PowerPivot für Controller

Übungsdateien

Paketinhalt:

  • Sales Golfstore.accdb
  • Golfstore_Shops.xlsx
  • SQLMonatsumsätze.txt

Suche