Übersicht aller Excel-Tipps und -Tricks fürs Controlling mit Excel auf der ControllerSpielwiese

Der Einfachheit halber haben wir alle Excel-Tipps und -Tricks in dieser Liste vereinigt.
Sie können Sie somit zum Beispiel komplett ausdrucken und nach und nach ausprobieren.

Tipp 1: Dateinamen in einer Zelle ausgeben

Geben Sie in eine beliebige Zelle die Formel =ZELLE("Dateiname") ein. Excel liefert Ihnen in dieser Zelle dann den Speicherpfad, den Namen der Datei und auch den Namen des aktuellen Tabellenblattes.
Die Funktion liefert nur ein Ergebnis, wenn es sich um eine zuvor bereits gespeicherte und keine neue Datei handelt. Der Name des Tabellenblattes wird nicht ergänzt, wenn es den selben Namen wie die Datei trägt und kein weiteres Tabellenblatt existiert. Nur den Tabellennamen können Sie mit Tipp 39 ausgeben.


 
Tipp 2: Anzeige von Nullwerten unterdrücken

Sie haben in einer Tabelle bereits vorbereitend Formeln für z.B. Folgemonate o.ä. einepflegt, welche nun mit Nullwerten erscheinen. Um die Übersichtlichkeit zu erhöhen können Sie die Anzeige der Nullwerte wie folgt vermeiden:

Wählen Sie den Befehl Optionen aus dem Menü Extras.
Wechseln Sie auf die Registerkarte Ansicht.
Deaktivieren Sie das Kontrollkästchen Nullwerte.
Bestätigen Sie Ihre Wahl mit OK.


 
Tipp 3: Zeilenumbruch in einer Zelle per Tastenkombination

Will man einen längeren Text in einer Zelle eingeben und einen Zeilenumbruch erzeugen kann man sich im Menü Format/Zellen/Ausrichtung bedienen. Cleverer und schneller ist jedoch an der entsprechenden Stelle im Text mit der Tastenkombination ALT + ENTER den Umbruch zu erzwingen, um sofort in der nächsten Zeile weiterschreiben zu können.
Das gleiche ist sehr hilfreich, wenn längere Begriffe für die Legende in Diagrammen verwendet werden, aber in der Darstellung zu viel Platz nehmen. Einfach in der originären Zelle den Begriff mit ALT + ENTER trennen und schon wird in der Legende auch entsprechend getrennt. Die Legende kann somit schmaler angezeigt und Platz gespart werden.


 
Tipp 4: Römische Zahlen anzeigen

Für das Konvertieren von arabischen in römische Zahlen hält Excel die Funktion RÖMISCH bereit. Soll z.B. eine arabische Zahl in der Zelle A2 in eine römische Zahl konvertiert werden, geben Sie in eine neue Zelle die Funktion =RÖMISCH(A2) ein.


 
Tipp 5: Zahlenwerte in mehreren Zellen in andere Formate umwandeln

Sollen z.B. Zahlenwerte in einer Tabelle statt in EUR in TEUR dargestellt werden, gehen Sie wie folgt vor:

Geben Sie in eine beliebige freie Zelle den Wert 1000 ein.
Kopieren Sie diese Zelle (z.B. mit Tastenkombination STRG + C)
Markieren Sie alle Zellen, deren Werte umgewandelt werden sollen.
Wählen Sie im Menü Bearbeiten den Befehl Inhalte einfügen...
Im folgenden Dialog wählen Sie die Option Werte und die Option Dividieren.
Bestätigen Sie Ihre Wahl mit OK.

Alle Werte wurden jetzt durch 1000 geteilt. Auf diese Art und Weise können Sie Werte auch addieren, subtrahieren und multiplizieren. Prozentuale Veränderungen können ebenso vorgenommen werden (* 1,x oder / 0,x).


 
Tipp 6: Benutzerdefinierte Seitennummerierung

Wenn Sie den Ausdruck eines Arbeitsblattes nicht mit der Seite 1 beginnen möchten, z.B. um die Seite(n) in eine bestehende Präsentation einzugliedern, ist es möglich eine benutzerdefinierte Seitennummer in der Fußzeile festzulegen:
 
Wählen Sie im Menü Datei den Befehl Seite einrichten... und dann die Registerkarte Kopfzeile/Fußzeile. Der Button Benutzerdefinierte Fußzeile öffnet die Bearbeitungsfelder der verschiedenen Abschnitte. Geben Sie direkt hinter die Definition der Seitennummer ("Seite &[Seite]") den Zusatz "+5 " an, wenn z.B. der Ausdruck mit Seite 6 beginnen soll. Beenden Sie die Dialoge mit OK, um die Änderung zu bestätigen. Hinweis: Der Zusatz des Leerzeichens nach der Seitenzahl ermöglicht das Verschieben um mehr als 9 Seiten, in dem Beispiel "Seite &[Seite]+15 " um mit Seite 16 zu beginnen.


 
Tipp 7: Datum per Ziffernblock eingeben

Wenn Sie ein Datum mit dem Ziffernblock eingeben wollen, fehlt Ihnen ja bekanntlich hier der Dezimalpunkt. Excel kann das Datum aber auch anders interpretieren:
 
Anstelle des Punktes können Sie bei der Eingabe auch das Divisionszeichen / oder das Subtraktionszeichen - verwenden. Aus 04/04/02 oder 04-04-02 macht Excel nach "Enter" automatisch 04.04.02. Um ein Datum des aktuellen Jahres am Ziffernblock einzugeben, reicht es auch schon aus, z.B. 04-04 einzugeben. Das Jahr wird automatisch ergänzt.


 
Tipp 8: Datum oder Uhrzeit per Tastenkombination eingeben

Oft will man in Excel schnell das aktuelle Datum in eine Zelle einfügen. OK, man könnte es einfach eintippen; manch einer bedient sich auch der HEUTE()-Funktion. Das eine dauert etwas und ist zumindest etwas umständlich, das andere hat den Haken, das Excel das Datum bei jedem Öffnen der Arbeitsmappe automatisch aktualisiert. Die HEUTE()-Funktion ist somit in manchen Situationen unbrauchbar, aber das automatische Einfügen des aktuellen Datums bekommen Sie auch anders hin. Um das aktuelle Datum schnell und konstant einzufügen, so dass auch an den kommenden Tagen noch der gleiche Wert angezeigt wird, können Sie die Tastenkombination Strg + . (Punkt) drücken (Steuerungstaste gedrückt halten und den ganz normalen Punkt eingeben). Excel fügt jetzt das aktuelle Datum als Wert in die betreffende Zelle ein und formatiert im Standardformat für Daten. Die Kombination der Tasten Strg + Shift + . trägt übrigens die aktuelle Uhrzeit ein.


 
Tipp 9: Zellinhalte in Abhängigkeit vom Wert formatieren - Bedingte Formatierung

Mit Hilfe der Bedingten Formatierung lassen sich Zellinhalte je nach Ihrem Wert beliebig optisch herausheben. Die Schrift sowie Zellrahmen und -hintergrund können dabei angepaßt werden.
 
Wählen Sie den zu formatierenden Zellbereich und dann den Befehl Bedingte Formatierung aus dem Menü Format.
Sie können jetzt Bedingungen mit logischen Operanden für die herauszuhebenden Werte definieren oder auch konkrete Werte bestimmen um dann mit dem Button Format in den folgenden drei Registerkarten die Zellformate festzulegen. Beispiele können sein Minuswerte rot, Sonntage kursiv, Werte > 1000 grün, bestimmte Namen blau oder was auch immer ...


 
Tipp 10: Text aus einer Zelle auf zwei (oder mehrere) Zellen aufteilen

Zellinhalte, welche aus mehreren Wörtern bestehen aber in verschiedenen Zellen weiterverarbeitet werden sollen, können in Hilfsspalten aufgeteilt werden. Stehen z.B. in Zelle A1 ein Vor- und ein Nachname, so ist die Aufteilung auf B1 und C1 wie folgt möglich:
 
Steht als Zellinhalt von A1 z.B. Joachim Becker so kann in der Zelle B1 mit der Formel =LINKS(A1;SUCHEN(" ";A1)-1) der Vorname Joachim und in der Zelle C1 mit der Formel =RECHTS(A1;LÄNGE(A1)-(SUCHEN(" ";A1))) der Nachname Becker dargestellt werden.
 
Die Funktion SUCHEN gibt als Formelergebnis die Position des Suchtextes (hier " ") wieder, welche für die linke bzw. rechte Beschneidung des Zellinhaltes von A1 benötigt wird. Tipp: Man kann mit dieser Formel auch prima Email-Adressen in die Texte vor und nach dem @-Symbol separieren.
 
Excel sieht mitlerweile aber auch einen Menüpunkt im Menü DATEN vor: "Text in Spalten". Im folgenden Fenster kann mit einer Vorschau nach verschiedenen Kriterien separiert werden...


 
Tipp 11: Texte aus 2 Zellen in einer Zelle zusammenfügen

Zellinhalte aus meheren Zellen können in einer Zelle zusammengefügt werden. Stehen z.B. in Zelle A1 ein Vorname und in B1 ein Nachname, so ist die Zusammenfassung in C1 wie folgt möglich:
 
Mit der Formel =A1 & " " & B1 können der Zellinhalt von A1 z.B. Joachim mit dem Zellinhalt Becker aus B1 in C1 zu Joachim Becker zusammengefügt werden. Alternative Formel mit dem gleichen Ergebnis: =VERKETTEN(A1;" ";B1) Mit " " wird das Leerzeichen zwischen den einzelnen Wörtern eingefügt. Mit dieser Möglichkeit können sehr gut Ergebnisse aus Tabellen in Überschriften o.ä. verarbeitet werden.


 
Tipp 12: Verketten von Zellen mit Darstellung von Formaten

Aufbauend auf Tipp 11 können auch die Formate von unterschiedlichen Zellinhalten beim Verketten meherer Zellen übernommen bzw. neue Formate zugewiesen werden. Steht z.B. in Zelle A1 ein Datum und in B1 ein weiterer beliebiger Text, so kann die Formel wie folgt lauten: =VERKETTEN(TEXT(A1;"TT.MM.JJJJ");B1)
Steht in A1 z.B. eine zweistellige Nachkommazahl, so kann diese mit B1 wie folgt verkettet werden: =VERKETTEN(TEXT(A1;"0,00");B1)


 
Tipp 13: Excel-Diagramm in Powerpoint einfügen und Verknüpfung aufrechterhalten

Um ein in Excel erstelltes Diagramm in eine Powerpoint-Präsentation zu übernehmen und dabei eine spätere automatische Aktualisierung von Änderungen aus Excel nach Powerpoint zu gewährleisten muss man zunächst das Diagramm in Excel markieren, dann kopieren und in Powerpoint danach nicht über Einfügen / Objekt, sondern über Bearbeiten / Inhalte einfügen ... / Link einfügen gehen und Microsoft-Excel-Arbeitsblatt-Objekt auswählen. So ist gewährleistet, dass die Verknüpfung funktioniert und die Daten jeweils auf Basis des zuletzt gespeicherten Standes der Excel-Tabelle in Powerpoint aktuell angezeigt werden.


 
Tipp 14: Zufallszahl zwischen zwei Grenzwerten erzeugen

Die Funktion ZUFALLSBEREICH liefert eine Zufallszahl zwischen einer unteren und einer oberen Grenze:
=ZUFALLSBEREICH(untereGrenze;obereGrenze)
Allerdings ist hierbei zwingend zu beachten, dass das Add-In "Analyse-Funktionen" installiert ist. Menü Extras / Add-Ins-Manager...
Ohne das beschriebene Add-In funktioniert dies auch über die Formel:
=ZUFALLSZAHL()*(obere Grenze-untere Grenze)+untere Grenze


 
Tipp 15: Verschachtelung von WENN-Funktionen erweitern

Da die Möglichkeiten einer verschachtelten WENN-Funktion ja bekanntlich begrenzt sind =WENN(A1=1;1;WENN(A1=2;1;WENN(...))), können mehrere WENN-Funktionen einfach addiert werden, um entsprechend viele Möglichkeiten abzudecken: =WENN(A1=1;1)+WENN(A1=2;2)+WENN(A1=3;3) ... Auch wenn es bessere, jedoch kompliziertere Lösungen gibt, so kommt doch der Laie hiermit schnell zu einem Ergebnis.
Bei Verwendung von Excel 2002 oder älter sollte die Formel folgendermaßen verändert werden:
=WENN(A1=1;1;"")&WENN(A1=2;2;"")&WENN(A=3;3;"")...


 
Tipp 16: Formate mehrfach übertragen

Mit dem Pinsel-Symbol aus der Menüleiste kann man bekanntlich Formate von zuvor markierten Zellen auf andere Zellen oder Zellbereiche übertragen. Wenn man ein bestimmtes Format mehrfach übertragen möchte kann man erneut auf das Pinsel-Symbol klicken oder aber noch einfacher: Ein Doppelklick auf das Pinsel-Symbol aktiviert dieses permanent und nacheinander kann nun das gewählte Format auf verschiedene Zellen übertragen werden ohne jedesmal auf dieses Symbol zu klicken. Ein Klick auf das Symbol deaktiviert den "dauerhaften" Pinsel schließlich wieder. Die Formatübertragung kann aber noch schneller mit der ESC-Taste beendet werden.


 
Tipp 17: Gefilterte Teilergebnisse kopieren

Gefilterte Teilergebnisse können nicht ohne weiteres in ein neues Tabellenblatt kopiert werden. Um wirklich nur die angezeigten Teilergebnisse und nicht alle Datensätze angezeigt zu bekommen, muss man folgendermaßen vorgehen:
Markieren Sie den sichtbaren/gefilterten Bereich und wählen Sie dann im Menü BEARBEITEN den Punkt GEHE ZU und klicken Sie im folgenden Dialogfenster auf die Schaltfläche INHALTE. Markieren Sie die Option „Nur sichtbare Zellen“ und klicken Sie auf OK. Jetzt können Sie die markierten Daten wie gewohnt kopieren und lediglich diese in einen anderen Bereich einfügen. Sollten Sie schon mit Excel2003 unterwegs sein, ist diese Vorgehensweise überflüssig, da es bereits im Programm direkt umgesetzt ist.


 
Tipp 18: Zellen mit Formeln oder bedingten Formatierungen suchen

In einer Tabelle möchten Sie sich alle Zellen anzeigen lassen in denen eine Formel steht oder die bedingt formatiert sind. Wählen Sie dazu im Menü BEARBEITEN den Punkt GEHE ZU und klicken Sie im folgenden Dialogfenster auf die Schaltfläche INHALTE. Markieren Sie die Option "Formeln" und klicken Sie auf OK. Jetzt sind alle Zellen in denen eine Formel steht markiert. Analog gehen Sie vor um sich Zellen mit bedingter Formatierung anzeigen zu lassen. Hier wählen Sie dann die Option "bedingte Formate".


 
Tipp 19: Gitternetz für Präsentationen ausblenden

Das Hintergrundgitter in Excel leistet als Orientierungshilfe einen ausgezeichneten Dienst. Will man die Inhalte einer Tabelle aber z.B. mit einem Beamer vorführen, kann das Gitternetz von Excel stören. Um das Gitter auszublenden, wählen Sie unter EXTRAS/OPTIONEN das Register ANSICHT. Entfernen Sie den Haken vor GITTERNETZLINIEN und bestätigen Sie mit OK. Setzen Sie den Haken wieder, um das Gitter anzuzeigen. Unter dieser Option finden Sie übrigens eine Auswahlliste FARBE. Damit können Sie das Gitternetz anders einfärben, was in Kombination mit farbigen Linien in der Tabelle für weitere Visualisierungsmöglichkeiten sorgen kann.


 
Tipp 20: Alle Formeln mit shortcut ansehen

Wenn Sie sich in Excel alle Formeln, die im aktuellen Datenblatt vorhanden sind ansehen möchten, können Sie dies durch die Tastenkombination STRG + # erledigen. Durch nochmaliges Drücken dieser Kombination ist die normale Ansicht wieder vorhanden.


 
Tipp 21: Ausschnitt einer Tabelle als Grafik weiterverarbeiten

Wollen Sie beispielsweise einen Ausschnitt aus einer Excel-Tabelle als Grafik in eine Präsentation oder an anderer Stelle in eine Excel-Tabelle einfügen gehen Sie folgendermaßen vor: Markieren Sie einfach den gewünschten Bereich, drücken die UMSCHALT-Taste, halten diese gedrückt und wählen aus dem Menü BEARBEITEN den Befehl BILD KOPIEREN. Dieser Befehl wird nur aktiviert, wenn Sie die UMSCHALT-Taste gedrückt halten! Durck diesen Trick wird der Standardbefehl KOPIEREN, der normalerweise an dieser Stelle im Menü steht durch diesen neuen Befehl ersetzt. Die Grafik kann nun bequem in jeder anderen Anwendung weiterbearbeitet werden!


 
Tipp 22: Mehr als 7 Wenn-Verschachtelungen

Wenn Sie eine Wenn-Abfrage starten wollen, welche mehr als 7 Wenn´s beinhaltet, so müssen Sie diese nach den ersten 7 Wenn mit einem + oder einem & verbinden. Folgend ein einfaches Beispiel:
=WENN(B2=1;1;WENN(B2=2;2;WENN(B2=3;3;WENN(B2=4;4;WENN(B2=5;5;WENN(B2=6;6;WENN(B2=7;7)))))))+WENN(B2=8;8;WENN(B2=9;9;WENN(B2=10;10;)))


 
Tipp 23: Eingabe von Brüchen in einer Zelle

Haben Sie schon einmal probiert einen Bruch z.B. 1/3 in eine Zelle einzugeben? Excel interpretiert dies als ein Datum - nämlich den 01. März! Um nun statt des Datums einen Bruch angezeigt zu bekommen gehen sie folgendermaßen vor: Klicken Sie im Menü FORMAT den Befehl ZELLEN. Gehen Sie nun auf das Blattregister ZELLEN und wählen Sie aus dem Listenfeld KATEGORIE den Eintrag BRUCH aus. Nun wählen Sie im angezeigten Listenfeld FORMATE den Eintrag EINSTELLIG aus. Nun bekommen Sie den Eintrag als Bruch angezeigt.


 
Tipp 24: Zahl der Rückgängigschritte auf 100 erhöhen

Beim Aufstellen komplexer Formeln machen Sie nach fehlerhaften Versuchen häufig zahlreiche Arbeitsschritte über das Menü rückgängig. Excel merkt sich die letzten 16 Schritte, die reichen aber leider oft nicht aus. Sie können diesen Zwischenspeicher auf 100 Schritte erhöhen. Dazu ist die Anpassung eines Registry-Eintrags notwendig. Klicken Sie dazu auf Start und danach auf Ausführen. Hier geben Sie die Anweisung "regedit" ein. Navigieren Sie im Registry-Editor zu dem Schlüssel: HKEY-CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options
Dies gilt für Excel 2000. Für Excel XP steht die Versionsnummer 10.0 und für Excel 2003 11.0. Jetzt suchen Sie im rechten Teil des Fensters den DWORD-Wert "UndoHistory". Sofern er nicht vorhanden ist, erzeugen Sie ihn mit "Bearbeiten", "Neu", "DWORD-Wert". Nun klicken Sie doppelt auf diesen Eintrag, aktivieren im Dialog die Option "Dezimal". Jetzt tragen sie den Wert 100 ein. Abschließend bestätigen Sie den Dialog mit "OK" und schließen die Registry. Ab dem nächsten Start merkt sich Excel 100 Arbeitsschritte.


 
Tipp 25: Zellwert in Textfeld anzeigen

Wenn Sie beispielsweise eine Zahl besonders hervorheben wollen, ist es praktisch wenn Sie nicht an die Begrenzung von Zeilen und Spalten gebunden sind. Dies funktioniert mit einem Textfeld. Ein Textfeld hat den Vorteil, dass es unabhängig von Zeilen und Spalten formatiert werden kann.
Sie können den Inhalt des Textfeldes flexibel gestalten, indem Sie den Bezug auf eine Zelle setzen, in der sich der Inhalt verändert. Erzeugen Sie zuerst ein Textfeld: Sie finden die Schaltfläche in der "Zeichnen" Symbolleiste. Das Textfeld hat zunächst einen schraffierten Rahmen. Klicken Sie nun mit der Maus auf den Rahmen, so dass dieser gerastert angezeigt wird. Jetzt können Sie in die Bearbeitungszeile klicken. Dort geben Sie ein "=" - Zeichen ein und klicken auf die Zelle die den variablen Wert enthält; damit wird der Zellbezug erzeugt. Das Textfeld lässt sich jetzt frei auf der Tabelle verschieben und in der Größe anpassen.


 
Tipp 26: Variable Bezüge mit INDIREKT

Der Befehl INDIREKT erzeugt aus einem (variablen) TEXT Bezüge auf Zellen und Bereiche. Beispiel:
Es soll ein Wert ausgegeben werden, der in Spalte "C" steht, aber dessen Zeilenbezug variabel ist. Die Zeilennummer steht als (variabler) Wert in Zelle A1 (z. B. 14)
anstelle des FIXEN Bezuges
=C14
lautet der VARIABLE Bezug
=INDIREKT(VERKETTEN("C";A1))
Der Blattname (und Dateiname) könnte ebenfalls als Parameter in die Formel integriert werden:
=INDIREKT(VERKETTEN("'";A2;"'!";"C";A1))
(wobei in der Zelle A2 der Blattname stehen müsste) Analog können mit dieser Methode auch variable Bezüge auf ZELLBEREICHE generiert werden.
(eingesandt von Earl.Fred)


 
Tipp 27: Werte in anderen Tabellen (mit SVERWEIS) suchen

Über den Befehl SVERWEIS lassen sich Werte aus anderen Tabellen mit Hilfe eines Suchwertes auffinden:
SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Suchkriterium: Der Suchwert, z. B. eine Variable oder ein Wert
Matrix: Die Tabelle bzw. der Bereich, aus dem mit Hilfe des Suchkriteriums Werte ausgelesen werden sollen. Die erste Spalte der Matrix muss zwingend die Spalte sein, in der das SUCHKRITERIUM gesucht werden soll.
Spaltenindex: Ist die Spalte, bei 1 beginnend zu zählen, in der der gewünscht Wert steht. Eingabe als Wert oder Variable möglich.
Bereich_Verweis: Gibt an, ob das Suchkriterium GENAU erfüllt sein muss (Bereich_Verweis=falsch) oder UNGEFÄHR (Bereich_Verweis=wahr oder leer).
Am besten zieht man anfangs den Funktions-Assistenten von Excel zu Rate. Voraussetzung ist noch, dass das Suchkriterium in der Matrix EINDEUTIG ist (d. h. wirklich GENAU EINMAL in der zu durchsuchenden Spalte vorkommt), da ansonsten nur der zuerst gefundene Bezugswert zurückgegeben wird. Wird nicht in Zeilen, sondern in Spalten gesucht, funktioniert dies analog mit dem Befehl WVERWEIS.
(eingesandt von Earl.Fred)


 
Tipp 28: Zwei Tabellenbereiche automatisch vergleichen

Wollen Sie vollautomatisch die Werte in zwei Tabellenbereichen vergleichen und die Zellen mit Unterschieden automatisch markieren? Angenommen Sie haben ein Budget erstellt. Die Ist-Werte wurden ebenfalls in eine Tabelle mit gleichem Aufbau wie das Budget eingetragen. Wenn Sie jetzt die Zellen markieren wollen bei denen das Budget überschritten wurde, gehen Sie folgendermaßen vor:
Markieren Sie den Bereich mit den Ist-Zahlen, und öffnen Sie das Menü "Bedingte Formatierung".
In der ersten Liste klicken Sie auf den Eintrag "Zellwert ist", und markieren in der zweiten Liste "größer als".
In das letzte Feld geben Sie eine Formel mit einem Bezug zur linken oberen Zelle des Bereiches mit Budget-Zahlen ein. Für unser Beispiel soll das die Zelle B3 sein. Die Formel lautet dann so: "=Budget!B3". Der Budgetposten müsste also in einer Tabelle "Budget" in der Zelle "B3" liegen.
Klicken Sie nun auf Format und legen Sie das Format fest, mit dem Excel die Zellen mit überschrittenen Budgetbeträgen hervorheben soll (z. B. Schriftfarbe rot).
Nachdem Sie mit OK bestätigt haben, hebt Excel jetzt die Zellen hervor, deren Wert größer ist als in der vergleichbaren Zelle im Budgetbereich.
Diese Technik lässt sich auch für die Prüfung anderer Bedingungen einsetzen. Voraussetzung ist das die zu vergleichenden Bereiche gleich aufgebaut sind. Sie müssen den Vorgang übrigens nicht wiederholen wenn sich die Werte ändern!


 
Tipp 29: Variabler Diagrammtitel

Der Titel eines Diagramms wird ja bekanntlich beim Erstellen eines Diagrammes mit dem Assistenten festgelegt. Um diesen bequem nachträglich in Abhängigkeit einer bestimmten Zelle ändern zu können, bauen Sie folgende Funktion ein: Klicken Sie im Diagramm auf den Diagrammtitel, so dass dieser markiert ist. Nun klicken Sie in die Bearbeitungszeile, tippen ein "="-Zeichen ein und klicken anschließend auf die Zelle, deren Inhalt im Diagrammtitel angezeigt werden soll. Der Bezug erscheint jetzt in der Bearbeitungszeile. Schließen Sie die Eingabe mit der "Enter"-Taste ab. Im Titel wird nun der Eintrag der entsprechenden Zelle angezeigt. Wenn Sie jetzt deren Zellinhalt ändern, ändert sich auch der Titel Ihres Diagrammes.


 
Tipp 30: Änderungen im neuen Excel 2007

Die neue Office Version und das neue Excel 2007 kommen auf den ersten Blick ganz anders daher. Für den Umsteiger beschreiben wir einige wichtige Änderungen, um die Neuerungen schneller zu erschließen - wie gesagt, einige für den Einstieg in die neue Version ...! Sollten Sie weitere Hinweise ergänzen wollen, schreiben Sie uns unter excel@controllerspielwiese.de und wir fügen Ihre Ergänzungen hier an!
zum Dokument


 
Tipp 31: Datumsreihe erstellen - aber ohne Daten der Wochenenden

Angenommen Sie möchten für Wochenübersichten oder eine Projektplanung Datumslisten ohne die Wochenenden erstellen, dann ist auch das mit Excel schnell zu erstellen:
Geben Sie zunächst das Startdatum ein, z.B. "02.07.2007" und lassen Sie die Zelle markiert. Ziehen Sie dann mit gedrückter rechter Maustaste am Ausfüllkästchen nach unten oder nach rechts. Wenn Sie die Maustaste los lassen erscheint nun das Kontextmenü mit 2 interessanten Einträgen. Klicken Sie dort auf den Befehl "Wochentage ausfüllen" und in Ihrem markierten Zellbereich werden nun die Daten der Wochentage eingetragen unter Aussparung der Wochenenden. Weiterhin können Sie jetzt z.B. auch den entsprechenden Wochentag, hier z.B. "Montag" daneben schreiben und die Namen der Wochentage auf dem gleichen Wege ergänzen. Wollen Sie alle 7 Wochentage funktioniert es übrigens auch mit dem automatischen Ausfüllen; hierzu nur den Eintrag "Tage ausfüllen" aus dem gleichen Kontextmenü selektieren.


 
Tipp 32: Formeln durch Namensvergabe übersichtlicher gestalten

Bei der Arbeit mit größeren Tabellen oder komplizierten Formeln werden die eingegebenen Bezüge durch die Koordinaten- oder Bereichsangaben in Formeln schnell unübersichtlich. Häufig liefert eine Formel wie beispielsweise A1*D5*F4+D4/G2*F7, kaum noch einen Rückschluss auf das, was eigentlich berechnet wird. Zur Verbesserung der Übersichtlichkeit und zur Vereinfachung der Formelerstellung können Sie jedoch Zellen und Zellbereiche mit Namen versehen und die vergebenen Namen in den Formeln nutzen.
Um einer Zelle oder einem Bereich einen Namen zu geben gehen Sie wie folgt vor:
1. Markieren Sie die Zelle oder den Bereich, die bzw. der benannt werden soll.
2. Klicken Sie in das Namensfeld an der linken Seite der Bearbeitungsleiste, und geben Sie den gewünschten Namen ein.
3. Bestätigen Sie Ihre Eingabe mit Return.
Nun können Sie diesen Namen in Formeln verwenden. Stehen z.B. in der Spalte B2 bis B5 lauter Einzelkosten, geben Sie um die Summe zu berechnen als Bereich B2:B5 ein. Haben Sie diesen Bereich vorher in Einzelkosten umbenannt, sieht die Formel so aus: Summe(Einzelkosten).


 
Tipp 33: Zellinhalte und Formatierungen für mehrere Tabellen gleichzeitig festlegen

Das man einen Zellinhalt in mehrere Tabellen gleichzeitig eingeben kann, indem man mit STRG und Mausklick (links) die entsprechenden Tabellen auswählt, ist schon weitgehend bekannt. Der dann in eine Zelle eingegebene Wert befindet sich danach in jeder Tabelle in der gleichen Zelle. Sie können auf diese Weise aber auch schnell und einfach Formatierungen an Ihren Tabellen durchführen und sogar die Kopf- und Fußzeile für alle so ausgewählten Tabellen gleichzeitig benutzerdefiniert einstellen.


 
Tipp 34: Mit WAHL-Funktion verschachtelte WENN-Formeln umgehen

Stellen Sie sich folgende Aufgabe vor: Sie wollen die geplante Absatzmenge eines Kalendermonats anhand des jeweils aktuellen Tagesdatums ermitteln. Die 12 Absatzmengen stehen in der Regel in 12 angrenzenden Zellen. Mit der WENN-Funktion ließe sich das Beispiel nur über verschachtelte WENN-Funktionen lösen. Da die Funktion normalerweise maximal 7 Verschachtelungen zulässt, hier aber insgesamt 12 Möglichkeiten (Monate) zu berücksichtigen sind, wird es mehr als nur schwierig. Mit Hilfe der WAHL-Funktion bieten sich dagegen bis zu 29 Wahlmöglichkeiten und hierfür eine Lösung.
Bei der Formel WAHL(MONAT(A5);B2;C2;D2;E2;F2;G2;H2;I2;J2;K2;L2;M2) nehmen wir an, dass in der Tabelle die geplanten Absatzmengen in den Zellen B2 bis M2 stehen und anhand des aktuellen Tagesdatums (15.03.2008) in A5 die Menge des Monates März heraus gelesen werden soll. Die MONAT-Funktion ermittelt die Zahl 3 (für März), daraufhin selektiert die WAHL-Funktion den 3. Wert aus seiner Wertauflistung und gibt den Inhalt der Zelle D2 wieder.


 
Tipp 35: Mit Zeitangaben rechnen

Das Multiplizieren von Zeitangaben stellt in Excel eine besondere Berechnungsart dar. Zum Beispiel beim Berechnen von Telefonkosten kann dies nützlich sein. Gehen Sie wie folgt vor:
Wählen Sie "Zellen formatieren"und dann "Uhrzeit" aus. Addition und Subtraktion funktioniert so problemlos. Schwieriger gestaltet sich die Multiplikation. Im Beispiel soll der Preis für ein 10-minütiges Telefonat vom Festnetz ins Handy-Netz berechnet werden. Hierbei liegt der Minutenpreis bei 12 Cent, also 0,12 Euro. Es entsteht eine Fehlermeldung, wenn der Anwender einfach =00:10*0,12 in ein Tabellenfeld eingibt. Um keine Fehlermeldung zu erhalten, muss in die Zelle eingegeben werden: =('00:10')*60*24*0,12. Manche Programm-Versionen erwarten noch eine zusätzliche Angabe, hier muss die Berechnung wie folgt definiert werden: =ZEITWERT('00:10')*60*24*0,12. Die Anführungsstriche rund um die Minutenzahl in der Stundendarstellung sind besonders wichtig. Indem der Zeitwert mit 60 (steht für die Minuten einer Stunde) und 24 (steht für die Stunden eines Tages) multipliziert wird, wird die interne Datenverarbeitung im Programm berücksichtigt, der daraus entstehende Wert ist im Beispiel 10. Die Multiplikation mit 0,12 gibt den Minutenpreis in Euro an, der Preis für das Beispiel-Telefonat liegt demnach bei 1,20 Euro.


 
Tipp 36: Kommentare direkt in Formeln schreiben

Hatten Sie auch schon das Problem, dass Sie nicht mehr wußten was eine von Ihnen erstellte Formel genau berechnet? Toll wäre es, wenn Sie eine Formel mit einem kurzen Kommentar als Gedächtnisstütze versehen könnten. Das ist in Excel leider generell nicht vorgesehen oder nur durch einen extra zu setzenden Kommentar möglich, aber es gibt noch einen Trick:
Benutzen Sie dafür die Funktion N(). Dabei handelt es sich um eine eigentlich obsolete Funktion, die in der Praxis nicht benötigt wird und nur noch aus Kompatibilitätsgründen vorhanden ist. N() wandelt Zellinhalte in Werte um. Dabei werden Texte, die nicht als numerischer Wert interpretierbar sind, auf den Wert 0 gesetzt. In der Arbeitsmappe selbst erscheint dabei nur der berechnete Wert, der Text wird nur in der Eingabeleiste angezeigt.
Der Trick besteht nun darin, den Kommentar mit +N("Hier tragen Sie den Kommentar ein") anzuhängen. N() liefert hier den Wert 0, eine Addition von 0 hat keine Auswirkung auf das von Excel berechnete Ergebnis und verändert damit auch nicht die Anzeige der jeweiligen Zelle. Wählen Sie die Zelle dagegen aus, sehen Sie in der Eingabezeile sowohl die Formel als auch den Kommentar.
Ein einfaches Beispiel. Statt =5*13 können Sie auch = 5*13 + N("Hier werden zwei Werte multipliziert") benutzen, ohne das Ergebnis der Berechnung durch den Kommentar zu beeinflussen.


 
Tipp 37: Pivot-Tabelle: Datenbereich dynamisch anlegen

Beim Erstellen einer Pivot-Tabelle fragt ein Dialogfeld, welche Daten man analysieren will. Dort muss man dann den Bereich eingeben, z.B. $A$1:$E$50. Damit ist ein fester Bereich festgelegt. Nachteilig ist dies, wenn die der Pivottabelle zugrunde liegende Tabelle in der Größe (mehr/weniger Spalten/Zeilen) schwankt: Es muss immer wieder manuell eingegriffen werden. So schaffen Sie Abhilfe:
Die Lösung besteht darin dem Datenbereich der Tabelle einen Namen zuzuweisen und den Bereich über eine Formel zu dynamsieren.
1. Sie befinden sich im Tabellenblatt, in dem Ihre Daten liegen, die Grundlage Ihrer späteren Pivot-Tabelle
2. Hier gehen Sie über Menü/Einfügen/Namen/Definieren...
3. Geben Sie nun den gewünschten Namen (z.B. Datenbasis) in das Dialogfeld "Namen in der Arbeitsmappe" ein
4. Geben Sie in das Feld "bezieht sich auf" folgende Formel ein:
=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);ANZAHL2($1:$1))
Sie müssen berücksichtigen, dass die Überschriftenzeile mit in den Bereich fallen muss, in unserem Formelbeispiel steht die erste Datenüberschrift in A1.
Zum Abschluss klicken Sie auf den Button "Hinzufügen" und anschließend schließen Sie das Dialogfenster durch Klick auf OK. Excel fügt den Tabellenblattnamen automatisch der Formel hinzu. Ab nun deckt der vergebene Name immer den passend großen Bereich Ihrer Tabelle ab und zwar in Bezug auf Spalten und Zeilen. (eingesandt von Christian S.)


 
Tipp 38: Mit negativen Uhrzeiten rechnen

Wollen Sie z.B. bei einer Zeiterfassung die Ist mit den Soll Stunden vergleichen und es ergibt sich daraus ein negativer Wert (z.B. 7:00 Uhr - 8:00 Uhr), zeigt Ihnen Excel nur Rauten an. Um das richtige Ergebnis (hier -1:00) angezeigt zu bekommen, gehen Sie wie folgt vor: Gehen Sie auf Extras -> Optionen -> Berechnungen. Hier wählen Sie dann bei den Arbeitsmappenoptionen "1904-Datumswerte" aus. Jetzt können Sie auch mit negativen Uhrzeiten rechnen. (eingesandt von Christian S.)


 
Tipp 39: Registername in einer Zelle ausgeben

Wie Sie den Speicherpfad mit Dateiname und Registerblatt in einer Zelle ausgeben steht bereits in Tipp 1. Folgendermaßen können Sie hiervon nur den Registernamen anzeigen. Geben Sie in eine beliebige Zelle die Formel  =TEIL(ZELLE("Dateiname");SUCHEN("]";ZELLE("Dateiname"))+1;31) ein. Excel liefert Ihnen in dieser Zelle aufgrund ZELLE("Dateiname") zwar zunächst den ganzen Speicherpfad incl. dem Namen der Datei und auch dem Namen des aktuellen Blattes, nimmt allerdings durch den TEIL-Zusatz wieder den nicht darzustellenden ersten Teil weg. Dabei ist 31 die maximale Anzahl der Stellen, mit der ein Registerblatt (zumindest) bis Excel 2002 tituliert werden kann. (eingesandt von Gerold K.)


 
Tipp 40: Markierung nach Drücken der Eingabetaste verschieben

Wenn Sie in Excel die Return-Taste drücken, springt die Zellmarkierung in der Grundeinstellung von Excel automatisch nach unten in die nächste Zeile. Manchmal kann es jedoch vorteilhaft sein, dies zu ändern, beispielsweise wenn Sie eine Formel aufbauen und noch weiter testen wollen oder auch wenn mehrere Dateneingaben in einer Zeile erfolgen sollen. Die Richtung der Verschiebung der Zellmarkierung können Sie u.a. über die Excel-Optionen verändern.
In Office 2007 öffnen Sie das Fenster "Excel-Optionen" über die Schaltfläche "Office". Klicken Sie in der linken Seite auf den Befehl "Erweitert" und setzen Sie auf der rechten Seite bei "Markierung nach Drücken der Eingabetaste verschieben" ein Häkchen, in dem Sie das Feld anklicken. Dann können Sie im Listenfeld die gewünschte Richtung wählen.
In vorhergehenden Office Versionen erledigen Sie dies indem Sie über das Menü "Extras/Optionen" das Register "Bearbeiten" auswählen. Dort stehen die gleichen Möglichkeiten zur Verfügung.
Damit die Zellmarkierung nach dem Drücken der Eingabetaste auf der gleichen Stelle bleibt, entfernen Sie das Häkchen vor dem Eintrag "Markierung nach drücken der Eingabetaste verschieben". Das ist dann praktisch, wenn Sie Formeln erstellen und anschließend weiter testen wollen oder die gleiche Zelle noch weiter formatieren möchten.
Eine andere Möglichkeit die Markierung nach der Eingabe in die gewünschte Richtung springen zu lassen sind die Pfeiltasten. Wenn Sie zum Abschließen der Eingabe anstelle der Return-Taste die Pfeiltasten verwenden, springt die Zellmarkierung in die Richtung der jeweils gewählten Pfeiltaste.


 
Tipp 41: Teilergebnisse von Formeln anzeigen

Oftmals nehmen die Formeln in Excel beachtliche Ausmaße an und sind mehrfach verschachtelt bzw. eine lange Reihenfolge von Rechenoperationen - da ist es oft schwierig den Überblick zu behalten, welche Teilergebnisse einzelne Teile der Formel aktuell liefern. Mit einem kleinen Trick ist es jedoch möglich, das Teilergebnis einzelner Operationen direkt in der Bearbeitungsleiste ablesen zu können:
Beispielformel: =(A1+A2)/(B1-B2)
Sie wollen nun wissen, was (A1+A2) ergibt? Wenn Sie in der Bearbeitungsleiste den Formelbereich (A1+A2) markieren, so dass dieser schwarz hinterlegt ist und die Taste F9 drücken erscheint der entsprechende Wert direkt an der Stelle in der Klammer. Drücken Sie jetzt die Enter-Taste ist die Formel durch den Wert ersetzt, mit der ESC-Taste erscheint wieder die ursprüngliche Formel. Dies klappt natürlich mit allen Teil- und auch dem Gesamtergebnis - passen Sie nur auf, dass Vorzeichen, Klammern und ähnliches vollständig in der Markierung enthalten sind um keine falschen Teilergebnisse angezeigt zu bekommen.


 
Tipp 42: Rot-Grün-Invertierung

Wenn die in einem Diagramm darzustellenden Datenpunkte positiv und negativ sind, werden sie in der Regel trotzdem in der von Excel gewählten Farbe angezeigt. Es ist jedoch möglich, die positiven Datenpunkte z.B. grün und die negativen rot darzustellen, ohne die einzelnen Punkte manuell ändern zu müssen. Mit einem Rechtsklick auf eine Datenreihe und der Auswahl "Datenreihen formatieren" kann im angezeigten Dialogfenster das Kontrollkästchen "Invertieren falls negativ" aktiviert werden. Dabei werden die negativen Datenpunkte allerdings immer weiss dargestellt. Um jetzt die negativen Datenpunkte aber andersfarbig darzustellen kann wie folgt vorgegangen werden:
 
In Excel 97 und 2003 ist nach dem aktivieren des Kontrollkästchen "Invertieren falls negativ" die Schaltfläche "Fülleffekte" zu wählen und in der Registerkarte "Muster" sind ein x-beliebiges Muster, dann als Vordergrund "Rot" und als Hintergrund "Grün" zu selektieren. Mit Schaltfläche "OK" jetzt alle Fenster schließen und erneut Rechtsklick auf die Datenreihe, "Datenreihen formatieren" und in der Registerkarte "Muster" "Grün" auswählen. Mit der Schaltfläche "OK" den Vorgang abschließen.


 
Tipp 43: Anzahl Werte gemäß Bedingung zählen

Wenn Sie in einer Datentabelle mit vielen Datensätzen die Anzahl von Zellen zählen wollen, geht das leicht mit der Funktion ANZAHL, welche berechnet, wieviele Zellen in einem Bereich Zahlen enthalten oder mit ANZAHL2, welche die nicht leeren Zellen zählt. Möchten Sie jedoch die Anzahl der Zellen mit bestimmten Werten gemäß einer Bedingung, wie z.B. ungleich Null zählen, steht Ihnen hier die Funktion ZÄHLENWENN zur Verfügung. Sollen also z.B. in einem Bereich A1:A9999 die Anzahl der Zellen mit Werten größer Null gezählt werden sieht die Funktion folgendermaßen aus: =ZÄHLENWENN(A1:A9999;">0"). In den Anführungsstrichen können Sie das Suchkriterium logisch definieren und somit so ziemlich alles zählen...


 
Tipp 44: Datentabellen schnell auf Plausibilitäten prüfen

Eine Aufgabe des Controllers zum Monatsabschluß ist sicherlich das Auswerten von Datenbanken wie z.B. Bestand oder Forderungen zum Stichtag sowie Umsatz oder Materialaufwand im Zeitraum. Oftmals sind dies aufgrund vieler Geschäftsfälle große Datentabellen. Um diese möglichst schnell auf Plausibilität zu prüfen steht mit dem Autofilter ein mächtiges Werkzeug zur Verfügung, welches für ausgewählte Kriterien die entsprechenden Werte anzeigt und Plausibiltätsprüfungen somit vereinfacht. Für eine optimale Verarbeitung markieren Sie die jungfräuliche Tabelle incl. der Kopfzeilen und wählen im Menü "Daten / Autofilter". Als nächstes empfiehlt es sich einen beliebigen Filter per Klick auf ein Kästchen mit dem Pfeil zu setzen und im gefilterten Zustand unter der Tabelle für die notwendigen Spalten/Werte Auto-Summen "=SUMME(...)" zu setzen. Excel wendet aufgrund der Filterung gleich anstatt "=SUMME(..." das "=TEILERGEBNIS(..." an, was für die späteren Auswertungen von Nutzen ist. Die Summenzeile sollte auch immer mit Abstand einer leeren Zeile zu den Daten gewählt werden und die leere mit einbeziehen, was das nachträglich Einfügen von Zeilen erleichtert, da diese automatisch mit einbezogen werden. Wer immer auch das Gesamtergebnis im Blick haben möchte fügt eine weitere Summenzeile unter dem Teilergebnis ein. Dann kann das Filtern auf Plausi beginnen. Häufig werden Daten nicht korrekt eingegeben, oder Bezeichnungen sind fehlerhaft, Werte sind Null, negativ oder leere Zeilen stören in der Tabelle. Ein erster Anhaltspunkt kann oftmals bereits in dem Filterauswahlfeld gewonnen werden, wo auffällige Daten wie z.B. #NV oder auch negative Vorzeichen erkennbar sind. Mit dem gesetzten Filter kann der Ursache nachgegangen werden. Für bestimmte Datenbereiche steht über "Benutzerdefiniert" die Möglichkeit der Eingabe von mehreren Kriterien zur Verfügung wie z.B. zum Filtern von Kundennamen oder Produktbezeichnungen beginnend mit bestimmten Buchstaben (Ku*) oder Werten über/unter bestimmten Grenzen. Leere Zellen können leicht mit dem Eintrag "Leere" gefiltert und dann durch Markierung der Zeilenköpfe im Kontektmenü (rechte Maustaste) über "Zellen löschen" entfernt werden. Es empfielt sich oftmals auch den Tabellenbereich für den Druck zu formatieren: Beim Drucken werden nur die Zeilen der Datenbank ausgedruckt, die beim Filtern sichtbar sind. So lassen sich sehr einfach Ausdrucke nach verschiedenen Kriterien erzeugen. Die Werte eines Lagerortes, Forderungen > 60 Tage, bestimmte Kunden, Produkte als einfacher Bericht oder auch Unstimmigkeiten zur Weitergabe und Datenpflege durch die Kollegen. Die Filterung kann mit dem Eintrag "Alle" wieder aufgehoben.


 
Tipp 45: Unerwünschte Leerzeichen entfernen

Beim Import von Daten aus anderen Anwendungen oder auch während der "Zerlegung" von längeren Feldinhalten kann es vorkommen, dass unerwünschte Leerzeichen hinter einer Information bei der weiteren Verarbeitung Fehler erzeugen. Diese Leerzeichen können mit der Funktion =GLÄTTEN entfernt werden. Somit kann ein eindeutig vergleichbarer Text erzeugt bzw. ein Zellwert für Rechenoperationen generiert werden. Um z.B. in A1 einer Zellinformation die nachstehenden Leerzeichen abzuschneiden verwenden Sie =GLÄTTEN(A1). Soll mit den Informationen/Werten weitergerechnet werden, empfiehlt es sich zur Sicherheit die anschließende Multiplikation mit 1 durchzuführen. Somit wird eine importierte Information von Excel als Zahl erkannt. Die Funktion sieht dann wie folgt aus: =GLÄTTEN(A1)*1 Als unerwünscht werden alle Leerzeichen angesehen, die am Anfang oder am Ende stehen. Die Anzahl der Leerzeichen zwischen zwei Wörtern wird auf eins reduziert, falls sich dort mehrere befinden sollten. Es empfiehlt sich bei einer solchen Operation in den Zellen rechts der Textzellen die Funktion zu erstellen und dann nach erfolgter Glättung die rechten Zellen auf die linken zu übertragen / überschreiben.


 
Tipp 46: Minus hinter einer Zahl in echtes Minus umwandeln

Beim Import von Daten aus anderen Anwendungen kann es vorkommen, dass das Minuszeichen hinter einem Wert angezeigt wird (123-). Da Excel nur mit dem Minus vor dem Wert (-123) rechnen kann, muss die Zellinformation entsprechend bearbeitet werden. Sollen ganze Spalten mit mehreren tausend Werten angepaßt werden, haben diese Werte meist eine unterschiedliche Anzahl von Stellen, was zu berücksichtigen ist. Mit einer einfachen Verbindung von WENN, RECHTS, LINKS und LÄNGE läßt sich eine solche Umsetzung leicht realisieren. Die Formel, für eine Bearbeitung eines Wertes in C2 lautet wie folgt: =WENN(RECHTS(C2;1)="-";-LINKS(C2;LÄNGE(C2)-1)*1;C2)
Hierbei wird:"Wenn rechts ein Minus, dann die Länge des Wertes ohne das Minus, sonst der Wert in C2" ausgegeben. Die obligatorische Multiplikation mit 1 führt eine etwaige Text-Information in einen Wert über, mit dem Excel weiterrechnen kann.


 
Tipp 47: Überwachung von Zellen mit Kamera-Funktion

Neben dem Überwachungsfenster der "Formelüberwachung", welche unter "Extras" zu finden ist, besteht mit der "Kamera"-Funktion eine recht einfache Möglichkeit zur sofortigen Einsicht von Auswirkungen bei sich ändernden Ergebnissen in anderen Datenblättern. Dies kann sehr günstig sein, wenn mehrere Arbeitsmappen und große Tabellenblätter mit vielen Verknüpfungen verarbeitet werden, da es das viele hin- und herklicken erübrigt. Die "Kamera"-Funktion bietet auch noch einige Vorteile gegenüber der Formelüberwachung: Es können mehrere angrenzende Zellen schnell und übersichtlich einbezogen werden, Daten aus anderen Dateien mit integriert und auch sämtliche Formate mit angezeigt werden. Da die Kamera nicht über die Standard-Menüleiste erreichbar ist, empfiehlt es sich, sie in die Symbolleiste aufzunehmen. Bis Excel 2003 über "Ansicht", "Symbolleisten", "Anpassen ..." und im Register "Befehle" in der Kategorie "Extras" die Kamera selektieren. In Excel 2007 die Symbolleiste für den Schnellzugriff anpassen - "weitere Befehle", "alle Befehle" zeigt alphabetisch gelistet die Kamera. Mit dem integrierten Symbol kann sodann die zu überwachende Zelle zunächst markiert werden. Ein Klick auf das Kamera-Symbol selectiert dann diese Zelle und der folgende Klick in das Tabellenblatt, von wo aus überwacht werden soll, fügt das "Foto" ein. Alle Veränderungen aufgrund bestehender Verknüpfungen sind nun leicht ersichtlich.


 
Tipp 48: Unterjährig Durchschnittswerte korrekt berechnen

Durchschnittswerte sind grundsätzlich leicht zu errechnen, da lediglich die Gesamtsumme durch die Anzahl der einzelnen Werte geteilt werden muss. Was jedoch, wenn der Datenbereich ständig eine unterschiedliche Anzahl von Werten aufweist?
Oft kommt es z.B. vor, dass man unterjährig anhand von mehreren Monatswerten den Durchschnitt der bereits vorhandenen Monate in einem vorgegebenen Listenbereich errechnen möchte. Da die Anzahl der Werte hierbei jeden Monat zunimmt muss auch in der Durchschnittsberechnung dieser Wert flexibilisiert werden. Ansicht Beispiel
Als Beispiel sei eine Tabelle definiert, die von B2 bis B13 Monatswerte enthält. Die folgende Formel prüft mit WENN zunächst, ob in B2 überhaupt ein Anfangswert vorliegt (um keinen Fehlerwert zu erzeugen) und berechnet dann die SUMME aller vorliegenden Werte (immer über alle 12 Zellen). Weiterhin wird dann anhand der Funktion ZÄHLENWENN die Anzahl der Werte ermittelt, die größer Null sind (somit die Anzahl der vorhandenen Einträge). Summe durch Anzahl Werte ergibt dann den Durchschnitt. =WENN(B2<>0;SUMME(B2:B13)/ZÄHLENWENN(B2:B13;">0");0)
Beispiel: Excel Datei 97-2003
Da der allgemein bekannte Durchschnitt in der Statistik der arithmetische Mittelwert ist, kann auch die Funktion =MITTELWERT(B2:B13) genutzt werden. Beides führt zum gleichen Ergebnis.


 
Tipp 49: Schnelle Zahlenformatierung über die Tastatur

Für die Festlegung eines Zahlenformats bietet die Dialogbox "Zellen formatieren" eine Vielzahl von Optionen an. Um diese Box aufzurufen, können Sie auch die Tastenkombination STRG 1 wählen. Wenn Sie allerdings genau wissen, welches Format Sie wollen und nicht den etwas umständlichen Weg über die Menüs und die Dialogbox gehen möchten, erreichen Sie verschiedene gebräuchliche Formatierungen auch direkt über eine Tastenkombination.
Mit den folgenden Tasten weisen Sie in Kombination mit den Tasten STRG und UMSCHALT die genannten Formate zu:
1 = Tausendertrennzeichen und zwei Dezimalstellen
2 = Exponentialformat mit zwei Dezimalstellen
3 = Datumsformat mit Tag, Monat und Jahr
4 = Währungsformat, zwei Dezimalstellen
5 = Prozentformat
6 = Standard-Zahlenformat


 
Tipp 50: Abfangen und ausblenden von Fehlermeldungen wie z.B. #DIV/0 oder #NV

Häufig kommt es vor, dass Zellen in einer Arbeitsmappe bereits mit einer Formel gefüllt sind, deren Vorgängerzellen für die Berechnung allerdings noch leer sind oder für die Berechnung ungültige Werte wie z.B. Text enthalten. Somit wird ein Fehlerwert angezeigt und weitere Berechnungen oder Charts funktionieren nicht mehr optimal. Sie können diesen Fehlerwert abfangen, damit die Zelle z.B. leer bleibt oder eine Null ausgibt, solange die Formel noch nicht das gewünschte Ergebnis liefert. Dies ist möglich, indem Sie Ihre Formel mit der Funktion ISTFEHLER verketten, einem sogenannten Fehlerhandler. Die Syntax lautet: =ISTFEHLER(Wert). Wenn Sie bei Formeln also Fehler erwarten, stecken Sie die Formel z.B. in diese Bedingung:
=WENN(ISTFEHLER(Formel);"";Formel)
Beispiel 1: =ISTFEHLER(12/0) gibt den Fehlerwert WAHR zurück, da eine Division durch Null den Fehlerwert #DIV/0! erzeugt. Diesen Wahrheitswert können Sie dann im Wenn-Teil der Wenn-Funktion verwenden.
Beispiel 2: =WENN(ISTFEHLER(12/A1);"";12/A1) gibt dann eine leere Zelle zurück, falls die Zelle A1 eine Null enthält; dagegen eine 6, wenn A1 eine 2 enthält. Die Gänsefüßchen im Dann-Teil der WENN-Funktion erzeugen einen leeren Zelleninhalt, welcher auch für die Aufbereitung in Charts vorteilhaft ist, da kein Nullwert angezeigt wird.
Falls eine Formel eine Null ausgibt, die Sie unterdrücken möchten, genügt es also auch, dieses mit einer einfachen WENN-Funktion zu erledigen: =WENN(Formel=0;"";Formel)
Beliebt ist die Verwendung der Fehlerunterdrückung bei SVERWEISen, die in großen, Datenbeständen abfragen und den Abfragewert ggfs. nicht finden, da nicht vorhanden. Hier lautet der Fehlerwert dann #NV (no value, nicht vorhanden). Ein Beispiel für die Fehlerunterdrückung wäre hierzu:
=WENN(ISTFEHLER(SVERWEIS(B2;D2:E2000;2;0));""; SVERWEIS(B2;D2:E2000;2;0))
Wenn Sie konkret nur den Fehlerwert #NV ausschließen wollen heißt die Lösung ISTNV:
=WENN(ISTNV(SVERWEIS(B2;D2:E2000;2;0));""; SVERWEIS(B2;D2:E2000;2;0))
Seit Excel 2013 geht das mit WENNFEHLER bzw. mit WENNNV auch noch kürzer.
=WENNFEHLER(FORMEL();"Fehlermeldung") und auch für das Beispiel mit #NV:
=WENNNV(SVERWEIS(B2;D2:E2000;2;0);"")


 
Tipp 51: Diagramme schnell und einfach um Daten ergänzen

Um in ein bestehendes Diagramm neue Datenreihen einzufügen können Sie je nach Excel-Version über "Daten hinzufügen" oder "Daten auswählen" in der Menüleiste und/oder im Kontextmenü die Assistenten zum selektieren der neuen Werte öffnen und diese dort hinzufügen. Leichter und schneller geht das jedoch mit einer einfachen Methode: Markieren Sie die einzufügenden Daten incl. der Datenbeschriftung und drücken Sie die Tastenkombination STRG C. Jetzt klicken Sie das Diagramm an, um es zu selektieren/markieren und drücken dann die Tastenkombination STRG V und Excel fügt die Daten als neue Datenreihe in das bestehende Diagramm ein.


 
Tipp 52: Datenreihen in Diagrammen einfach ausblenden

Manchmal kommt es vor, dass man in einem Diagramm viele Datenreihen dargestellt hat und in der Präsentation somit nur schwer ein Verständnis für die Werte bzw. überhaupt ein Überblick geschaffen werden kann. So ist es evtl. güstig, Datenreihen in einem Diagramm einfach auszublenden bzw. nach und nach einzublenden. Wenn Sie Ihr Diagramm fertig erstellt haben können Sie ganze Datenreihen ausblenden, indem Sie die die Daten beinhaltende Zeile in Excel über die Gruppierungsfunktion in dem Menü "Daten/Gliederung" ausblenden. Das Öffnen bzw. Schließen einer Zeile blendet also die Datenreihe jeweils ein bzw. aus. Hierbei werden für die Skalierung der Achsen nur noch die verbleibenden Werte berücksichtigt. Möchten Sie die Skalierung beibehalten können Sie die Linienfarbe der Datenreihe im Kontektmenü Datenreihen formatieren auf "keine Linie" bis Excel 2010 bzw. auf "ohne" in späteren Versionen ändern. Das Diagramm ändert sich bis auf die verschwindende Linie nicht.


 
Tipp 53: Suche nach den n kleinsten(/größten) Werten in einem Bereich

Um nach den kleinsten Werten in einem Bereich zu suchen, z. B. um diese aus einer Datentabelle informativ herauszustellen (Verkäufer mit den niedrigsten Umsätzen; Produkte mit den niedrigsten Deckungsbeiträgen o.ä.), können Sie die Funktion KKLEINSTE verwenden und mit einem entsprechenden Array arbeiten.
Am folgenden Beispiel wird gezeigt, wie nach den fünf kleinsten Werten in einer Datentabelle von E2 bis E1000 (hier stehen die Umsätze, DB's o.ä.) gesucht werden kann und wie diese separat in dem Zellbereich H2 bis H6 aufgelistet werden.
Hierzu ist zunächst der Bereich zu markieren, in dem die Daten aufgelistet werden sollen, im Beispiel: H2 bis H6. Diese Gruppe von Zellen wird dann die von der Arrayformel ermittelten Werte enthalten. Die Formel selber ist in die Bearbeitungsleiste einzugeben und durch Drücken von STRG+UMSCHALT+EINGABETASTE zu aktivieren. Die Formel lautet:
=KKLEINSTE(E2:E1000;{1;2;3;4;5})
Die Formel verwendet eine Arraykonstante, um die Funktion KKLEINSTE fünfmal auszuwerten und das kleinste (1), das zweitkleinste (2), das drittkleinste (3) ... etc. ... Element im Array zurückzugeben, welches in dem Zellbereich E2:E1000 enthalten ist. Für die Suche nach weiteren Werten fügen Sie zusätzliche Argumente zur Konstante und eine entsprechende Anzahl von Ergebniszellen zum Bereich H2:H6 hinzu. Sie können diese Formel auch mit zusätzlichen Funktionen verknüpfen. Um nach den größten Werten in einem Bereich zu suchen, können Sie die Funktion KKLEINSTE durch die Funktion KGRÖSSTE ersetzen.


 
Tipp 54: Datenlücken erkennen und zählen

Vielfach weisen große Datentabellen einzelne Datenfelder ohne Inhalte auf, da diese evtl. schon im Vorsystem nicht korrekt geflegt worden sind. Weniger kritisch ist dies, wenn es sich hierbei nur um mitlaufende Textfelder handelt - wehe nur sie werden in weiterführende Berechnungen einbezogen. Sie können alle Datenfelder in einer Excel-Tabelle dahingehend überprüfen, ob sie auch Zellinhalte besitzen. Dazu verwenden Sie die Funktion ANZAHLLEEREZELLEN.
Sie übergeben der Funktion einen Zellbereich (z.B. A2:H5000). Daraufhin ermittelt die Funktion die Anzahl der Zellen, die in diesem Bereich leer sind.
=ANZAHLLEEREZELLEN(A2:H5000)
Sofern die leeren Zellen auf fehlende Informationen in Vorsystemen schließen lassen können diese somit auch gezielt überarbeitet werden.


 
Tipp 55: Differenzen zwischen Datumswerten berechnen

Um in einer Exceltabelle z.B. anhand der Geburtsdaten der Mitarbeiter das Durchschnittsalter der Belegschaft zu berechnen oder anhand des Eintrittsdatums die Dauer der Betriebszugehörigkeit zu ermitteln, steht seit Excel 5 die undokumentierete Formel DATEDIF zur Verfügung.
Die Syntax lautet: = DATEDIF(Startdatum;Enddatum;"Zeiteinheit")
Die "Zeiteinheit" ist ein Schalter für die zurückzugebende Information in Tagen, Monaten oder Tagen. Sie muss in englischen Kürzeln y, m oder d übergeben werden. Auch Kürzel wie md, ym und yd sind möglich, welche nur Teildifferenzen aus Tagen und/oder Monaten berechnen. md gibt z.B. den Unterschied in Tagen aus, wobei Monate und Jahre ignoriert werden.
Das Startdatum muss <= dem Enddatum sein, sonst gibt die Funktion einen Fehler zurück. Dieser kann jedoch mit =DATEDIF(MIN(A1;A2);MAX(A1;A2);"y") abgefangen werden, wobei in diesem Beispiel in A1 und A2 die verschiedenen Daten stehen.


 
Tipp 56: Tagesdaten ohne Wert-Information in Diagrammen ausblenden

Für die Visualisierung von täglichen Daten kann man schnell z.B. ein Liniendiagramm in Excel erstellen, welches die Werte vom Monatsersten bis zum Monatsletzten darstellt. Standardmäßig stellt Excel dann aber auch alle Tage dar, für die gar keine Werte vorliegen (z.B. die Wochenenden). Dies nennt man auch "gestreckte Darstellung" - die Werte liegen auf der X-Achse in ungleichmäßigen Abständen nebeneinander. Um diese leeren Zeiträume auszublenden und die Anstände anzupassen kann man die Diagrammoptionen entsprechend ändern.
Hierzu markieren Sie das Diagramm und wählen bis Excel 2003 die DIAGRAMMOPTIONEN und welchseln in das Register ACHSEN. Ab Excel 2007 finden Sie ACHSEN unter DIAGRAMMTOOLS / LAYOUT. Hier können Sie nun den Achsentyp verändern, der von Excel standardmäßig "automatisch" dargestellt wird. Bis zu der Version 2003 ist nun der Achsentyp "Kategoie" zu wählen. Ab Excel 2007 klickt man den Typ "Textachse" an, um lediglich die gewünschten Wert-Informationen anzeigen zu lassen.


 
Tipp 57: Ausrichtung von Zahlen am Komma

Zur übersichtlicheren Darstellung von Zahlen mit unterschiedlich vielen Nachkommastellen kann es sinnvoll sein, die Ausrichtung der Zahlen nicht auf den rechten Zellrand sondern auf das Komma zu fokusieren. Mit einem Format der Art 0,00 würden ab der dritten Nachkommastelle keine Werte mehr angezeigt werden und ggfs. lassen sich die Dimensionen der Daten nicht schnell überblicken bzw. es kommt zu Fehlinterpretationen. Über ein benutzerdefiniertes Format lassen sich diese Dezimalzahlen einfach am Komma ausrichten. Hierzu markieren Sie zuerst die auszurichtenden Zellen und geben dann im Kontektmenü "Zellen formatieren" im Register "Zellen" das benutzerdefinierte Format 0,???? an. Die Anzahl der Fragezeichen sollte der maximalen Anzahl von vorkommenden Nachkommastellen bei Ihren Zahlen entsprechen. Die Fragezeichen füllen bei Zahlen ohne oder mit abweichender Anzahl Nachkommastellen die Stellen zum Komma auf, welches somit bei allen Zahlen untereinander steht.


 
Tipp 58: Überschriften automatisch mit aktuellem Datum oder Monat verketten

Oft kommt es vor, dass für einen Bericht in dessen Überschrift ein aktuelles Datum oder der aktuelle Monat aufgeführt sein soll. Z.B. kann das lauten "Umsatzdaten vom 24.11.2011" oder "Monatsbericht November 2011". Um diese Überschriften nicht jedes Mal neu aktualisieren zu müssen (und ggfs. auch mal zu übersehen vor der Berichtsabgabe), kann man die Aktualisierung des Textes mit einer Formel automatisieren. Ziel hierbei ist, dass Excel ein zugrundeliegendes Datum nicht als Zahlenwert anzeigt bzw. den Monat im Klartext aufführt. In den folgenden Fällen werden sowohl Textbausteine wie auch Formeln miteinander verkettet. Zunächst ist es notwendig, dass aktuelle Datum in einer Zelle zu hinterlegen, z.B. durch Eingabe in Zelle H3 mit der Funktion HEUTE().
Jetzt können Sie das erste obige Beispiel in einer beliebigen Zelle wie folgt erzeugen:
="Umsatzdaten vom "&TEXT(H3;"TT.MM.JJJJ")
Mit dem "&"-Zeichen werden der Text und das Datum aneinander gekettet. Das ginge auch mit:
=VERKETTEN("Umsatzdaten vom ";TEXT(H3;"TT.MM.JJJJ"))
Um jetzt nur den Monat aus dem Datum zu selektieren geben Sie folgendes ein:
="Monatsbericht "&TEXT(Tabelle2!H3;"MMMM JJJJ")
oder bei dem Bezug des Datums aus einem anderen Tabellenblatt mit entsprechender Ergänzung:
="Monatsbericht "&TEXT(Tabelle2!H3;"MMMM JJJJ")
Die Ausgabe von Tagen, Monaten und Jahren kann man hier also über T,M und J variieren (ggfs. bei englischer Version mit D,M und Y).


 
Tipp 59: Positive und negative Zahlen getrennt summieren

In der Finanzbuchhaltung kommt es vor, dass man die in einer Spalte stehenden Soll- und Habenwerte (z.B. zum Abgleich der Summen) getrennt voneinander aufsummieren möchte. Hierzu kann man eine Matrixformel und die Funktion ABS verwenden, welche den Absolutwert einer Zahl liefert. ABS wandelt negative Zahlen in positive um, positive Zahlen bleiben dagegen positiv. Die Syntax der Funktion lautet ABS(Zahl) wobei Zahl eine reele Zahl sein muss. Für die positiven und negativen Summen lauten die Formeln dann wie folgt:
Für positive Zahlen: {=Summe(<Bereich>+ABS(<Bereich>))/2}
Für negative Zahlen: {=Summe(<Bereich>-ABS(<Bereich>))/2}
Da man die geschweiften Klammern der Matrixformeln nicht selbst eingeben kann, muss man mittels Tastenkombination "Strg-, Shift- und Returntaste" die Eingabe in der Bearbeitungszeile bestätigen.
In der Summenformel werden im ersten Teil alle Zahlen inklusive ihrer Vorzeichen addiert. Im zweiten Teil wird aufgrund der ABS-Funktion die Summe aller Beträge des Bereiches unabhängig von deren Vorzeichen dazugezählt.
Die negativen Zahlen werden im ersten Teil abgezogen, da sie mit Vorzeichen summiert werden. Im zweiten Teil werden sie aber wieder zu der Summe dazugezählt, da dort der Betrag addiert wird. Also werden sie einmal abgezogen und einmal dazugezählt – und treten somit in der Summe nicht auf.
Die positiven Zahlen werden dagegen zweimal addiert, so dass das Ergebnis der Summe-Formel am Ende noch durch zwei geteilt werden muss. Bei der Formel für die Errechnung der negativen Zahlen wird durch das negative Vorzeichen vor der ABS-Funktion die Summierung umgekehrt.


 
Tipp 60: Großbuchstaben in Kleinbuchstaben umwandeln

Häufig werden Daten in Vorsystemen in GROSSBUCHSTABEN eingegeben und verarbeitet. Beim Export in Excel und der dortigen Weiterverarbeitung kann dies stören. Das Umwandeln von Buchstaben kann leicht anhand einer vordefinierten Funktion erfolgen. So gibt es Funktionen, die den Text sowohl komplett in Kleinbuchstaben wie auch vollständig zurück in Großbuchstaben umwandeln, aber auch eine Funktion, die alle Wörter einer ausgewählten Zelle in Kleinbuchstaben mit jeweils führendem Großbuchstaben darstellt, so wie es bei Substantiven notwendig ist.
Während die Funktionen KLEIN und GROSS die Umwandlung aller Buchstaben in das jeweilige Format umsetzen, lautet die Funktion für die Darstellung eines führenden Großbuchstabens und folgender Kleinbuchstaben GROSS2 und deren Syntax =GROSS2(Zelle). Die Funktion wandelt den ersten Buchstaben aller Wörter einer Zeichenfolge in Großbuchstaben um.
Die Funktion sollte in einer neuen Spalte untergebracht werden, deren Ergebnis anschließend ggfs. wieder in die Ursprungsspalte per KOPIEREN und INHALTE EINFÜGEN/WERTE übertragen werden kann.
Teilweise tauchen in der Praxis auch Lösungen in der Form =GROSS2(Klein(Zelle)) zum Umwandeln der Buchstaben auf. In der aktuellen Version von Excel ist es jedoch nicht (mehr) notwendig, die Großbuchstaben zunächst alle in Kleinbuchstaben umzuwandeln.


 
Tipp 61: Bereiche dynamisch berechnen

Um im laufenden Jahr eine monatliche (z.B. Umsatz-) Übersicht mit zugehörigen Planwerten und ggfs. noch einem dynamischen Diagramm zu erstellen, gilt es, am Ende eines jeden Monats den auszuwertenden Bereich um eine Zeile (den aktuellen Monat) zu erweitern. Dies kann man natürlich manuell tun und die entsprechenden Formeln von Hand ändern. Besser ist es jedoch, wenn sich die Auswertungen dynamisch an die nach und nach ausgefüllten Zellen anpassen. Excel kann automatisch erkennen, bis wohin ein Bereich aktuell ausgefüllt ist. Für diese Zwecke (und natürlich auch für andere und weitaus kompliziertere) gibt es die Funktion BEREICH.VERSCHIEBEN, mit der man Bereiche auf eine eigene Weise definieren kann. Die Syntax lautet wie folgt:
=BEREICH.VERSCHIEBEN(AusgangsBereich;ZeilenVerschub;SpaltenVerschub;HöheZeilen;BreiteSpalten)
Beispielhaft soll eine Umsatzstatistik dienen, welche wie folgt aufgebaut ist: In Spalte A stehen einer Überschrift folgend die Monate Jan-Dez.; in Spalte B die Plan-Umsatzwerte (B2 bis B13) und deren Summe in B14; in Spalte C die Ist-Umsätze bis einschließlich Monat März (C2 bis C4) und die Summe C2 bis C13 in Spalte C14. Nun sind die beiden Summen nicht vergleichbar, da es 12 Plan- und nur drei Istwerte sind. Mit folgender Formel können die den Istwerten zugehörigen Planwerte aufsummiert werden:
=SUMME(BEREICH.VERSCHIEBEN(B2;0;0;ANZAHL(C2:C13);1))
Mit ANZAHL wird die Anzahl der vorhandenen Werte ausgerechnet und damit der Bereich von B2 ausgehend definiert. Die Summenformel summiert dessen Inhalte. Diese Formel kann in beliebiger Zelle stehen, also auch die Summenformel in B14 ersetzen. Eine Beispieldatei finden Sie hier: Bereich.Verschieben


 
Tipp 62: Bedingte Summierung über mehrere Suchkriterien

Mit der Funktion SUMMEWENN kann man schnell eine Datenbank nach einem Kriterium durchsuchen und dann die Summe der jeweiligen Einzelwerte ausgeben. Möchte man jedoch nach mehreren Kriterien in einer Datenbank suchen und deren Gesamtsumme ausrechnen, wären hierfür schon mehrere Summewenn-Funktionen notwendig bzw. möglich. Mit der Funktion DBSUMME können jedoch in einer Formel gleich mehrere Bedingungen mitgegeben werden. Die Syntax der Funktion lautet:
=DBSUMME(Datenbank;Feld;Suchkriterien)
"Datenbank" ist der Zellbereich, der die Datentabelle darstellt; die erste Zeile muss die Spaltenüberschriften enthalten.
"Feld" ist die Überschrift der Spalte, welche durchsucht werden soll
"Suchkriterien" ist ein Zellbereich, der die Bedingungen für die Summierung festlegt; die erste Zeile muss die Spaltenüberschrift enthalten.
Ein einfaches Beispiel finden Sie hier: DBSUMME.XLS
Im Beispiel werden in Zelle C14 aus der Datenbank A1:C10 diejenigen Umsätze aus Spalte C addiert, deren Suchkriterien (=Verkaufsgebiet) in dem Zellbereich F1:F3 definiert sind.


 
Tipp 63: Zellen vor ungewollten Eingaben schützen

Um z.B. in Formularen bestimmte Zellen vor ungewollten Eingaben oder dem Überschreiben von Inhalten zu schützen, können diese mit einem Zellschutz belegt werden. Standardmäßig ist für alle Zellen in einem Tabellenblatt der Zellschutz aktiviert. Dies ist im Dialogfenster ZELLEN FORMATIEREN im Register SCHUTZ ersichtlich; hier ist GESPERRT aktiviert. Zum entfernen des Zellschutzes wählen Sie alle betreffenden Zellen aus, die nicht geschützt sein sollen (Mehrfachmarkierung mit Hilfe der gedrückten STRG-Taste) und entfernen den Haken im Kontrollkästchen bei GESPERRT. Klicken Sie nun im Register ÜBERPRÜFEN auf BLATT SCHÜTZEN und vergeben Sie ein Kennwort. Dieses kann bis zu 255 Zeichen lang sein und sich aus einer beliebigen Kombination von Buchstaben, Ziffern, Leer- und Sonderzeichen zusammensetzen. Wenn Sie hier das Kontrollkästchen vor "Gesperrte Zellen auswählen" deaktivieren, können diese im geschützten Blatt nicht mehr angeklickt werden. Nach dem Klick auf OK geben Sie das Kennwort erneut ein. Sie bekommen von nun an eine Fehlermeldung, wenn Sie versuchen geschützte Zellen zu überschreiben oder zu löschen. Von einer zur nächsten ungeschützten Zelle kann direkt mit der Tabulatortaste gesprungen werden.


 
Tipp 64: Eingabezellen in Formularen farbig markieren

Werden Tabellen anderen Anwendern als Formulare zum Ausfüllen von bestimmten Feldern zur Verfügung gestellt, empfiehlt es sich, bestimmte Zellen zu schützen und jene für die Eingaben notwendigen, ungeschützten Zellen farbig zu markieren. Diese Markierung kann einerseits durch Auswahl aller ungeschützten Zellen und dem Ändern der Zellformatierung erfolgen. Komfortabler und schneller geht es mit einer bedingten Formatierung, insbesondere dann, wenn die geschützten und ungeschützten Zellen nachträglich geändert werden müssen. Entfernen Sie hierzu zunächst wie in Tipp Nr.63 beschrieben in allen zu formatierenden Eingabezellen den Zellschutz. Markieren Sie nun den gesamten Formularbereich, zum Beispiel A1:F50 und starten Sie den Dialog BEDINGTE FORMATIERUNG. Als Regeltyp wählen Sie "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus (Excel 2003: "Bedingung Formel ist") und geben als Regelbeschreibung die Formel: =ZELLE("Schutz";A1)=0 ein. Hier ist nur die erste Startzelle A1 des ausgewählten ungeschützten Bereiches einzugeben. Sie können über die Schaltfläche FORMATIEREN in den Registerkarten AUSFÜLLEN z.B. einen gelben Zellhintergrund definieren oder in RAHMEN verschiedene Zellrahmen bestimmen. Nach dem Klick auf OK werden nun alle Zellen, auf denen der Zellschutz deaktiviert ist, farbig und mit der definierten Zellumrahmung hervorgehoben.


 
Tipp 65: PivotTabellen-Assistent ab Excel 2007 / PivotTabelle aus mehreren Datentabellen erstellen

Mit PivotTabellen können Sie umfangreiche Datenbestände auf einfache Weise analysieren, anordnen und zusammenfassen sowie Drilldowns auf die Detaildaten vornehmen. Bis Excel 2007 konnte für die Erstellung der PivotTabellen noch ein PivotTabellen-Assistent über die Menüs direkt angeklickt werden. Dieser erleichterte die Auswahl der Daten und ermöglichte es auch, mehrere Datentabellen zu einer PivotTabelle zusammen zu fassen. Ab Excel 2007 ist dieser Assistent gut versteckt und kann nur noch anhand einer Tastenkombination erweckt oder durchs Einbinden ins Menüband bzw. in die Symbolleiste für den Schnellzugriff aufgerufen werden. Die Tastenkombination für den PivotTabellen-Assistenten lautet Alt + N + P. Für das dauerhafte Einbinden in das Menüband bzw. die Symbolleiste kann über das Register DATEI in den Excel-Optionen der Befehl "PivotTable - & PivotChart-Assistent" (neben weiteren PivotTabellen-Befehlen) hinzugefügt werden. "Befehle auswählen" und "Alle Befehle" zeigt die alphabetische Liste aller möglichen Befehle in Excel - dann Auswahl des Befehls und Schaltfläche "Hinzufügen" klicken. Der PivotTabellen-Assistent fragt im Gegensatz zu dem einfachen Fenster "PivotTabelle erstellen" bereits vorab, welche Daten man analysieren möchte und bietet zum Beispiel die Möglichkeit "Mehrere Konsolidierungsbereiche" für das tabellenübergreifende Erstellen an.


 
Tipp 66: Spaltenüberschriften auf allen Druckseiten anzeigen

Wenn eine aus mehreren Seiten bestehende Excel-Tabelle fertig berechnet und gestaltet ist, kommt es beim Ausdruck oft zum Entsetzen: Der Zahlenfriedhof ist ab der zweiten Seite unübersichtlich, da die Spaltenüberschriften fehlen. Dabei können diese doch ganz leicht auf den Folgeseiten mit einer Funktion wiederholt werden. Hierzu ist es wichtig, in das Menü SEITE EINRICHTEN zu gelangen. In dessen Registerkarte TABELLE läßt sich das dann einstellen. Doch gelangt man hier schon lange nicht mehr einfach so hin. Ging es früher noch über das Menü DATEI, wird es ab Excel 2007 schon schwieriger und unübersichtlicher, so dass man sich am besten gleich in die Symbolleiste für den Schnellzugriff unter "weitere Befehle ..." das passende Icon zieht. Dies dann angeklickt und das Register geöffnet kann im Bereich DRUCKTITEL zunächst festgelegt werden, ob die Spalten- und/oder Zeilenbeschriftungen wiederholt werden sollen. Um z.B. die Spaltenüberschriften auf allen Folgeseiten zu wiederholen, klicken Sie in das Feld bei WIEDERHOLUNGSZEILEN OBEN. Jetzt ist es möglich, mit der Maus direkt in die Zeilenköpfe (linker Rand) der Tabelle zu klicken. Excel übernimmt daraufhin die Zellkoordinaten in das Dialogfenster und Sie bestätigen mit OK. Wenn Ihre Überschrift aus mehreren Zeilen besteht, klicken Sie übrigens auf die erste Zeilennummer, halten die Maustaste gedrückt und ziehen über weitere Zeilennummern. Excel druckt jetzt auf jeder Seite die ausgewählten Zeilen als Überschriften.


 
Tipp 67: Veränderungen in Excel-Tabellen protokollieren und nachvollziehen

Sollen mit einer Excel-Datei mehrere Anwender arbeiten, so ist es oft sinnvoll, alle Änderungen genau zu erfassen und zu protokollieren. Fehlerhafte oder ungewollte Änderungen können somit nachvollzogen und korrigiert werden. Wer hat also zu welchem Zeitpunkt welche Zellinhalte verändert? Excel bietet hierfür eine Protokoll-Funktion an, welche folgendermaßen genutzt werden kann:
Öffnen Sie zunächst die Arbeitsmappe, in der Sie Veränderungen nachvollziehen möchten. In Excel ab Version 2007 klicken Sie das Register ÜBERPRÜFEN im Menüband oder der Multifunktionsleiste an und klicken dann in der Gruppe ÄNDERUNGEN auf ÄNDERUNGEN NACHVERFOLGEN - ÄNDERUNGEN HERVORHEBEN. In Excel bis einschließlich Version 2003 rufen Sie über das Menü EXTRAS das Komando ÄNDERUNGEN NACHVERFOLGEN - ÄNDERUNGEN HERVORHEBEN auf. In allen Versionen zeigt Ihnen Excel dann ein Dialogfenster an, in dem Sie das Kontrollkästchen ÄNDERUNGEN WÄHREND DER EINGABE PROTOKOLLIEREN. ARBEITSMAPPE WIRD FREIGEGEBEN aktivieren müssen. Die wählbaren Optionen hinsichtlich WANN und WER werden zumeist mit IMMER und JEDER sinnvoll gewählt. Möchten Sie die Änderungen direkt in dem Arbeitsblatt sehen, sollten Sie ÄNDEURNGEN AM BILDSCHIRM HERVORHEBEN wählen. Die Protokollierung kann aber auch auf einem neuen Blatt stattfinden (welches man dann ggfs. auch noch verstecken kann...) - hierzu entsprechend klicken. Nach Bestätigung mit OK muss die Arbeitsmappe gespeichert werden.
Excel ergänzt nun nach jeder Änderung an Zellen einen Kommentar und führt den angemeldeten User namentlich mit den durchgeführten Änderungen auf. Die Protokollierung schalten Sie auf dem gleichen Wege wieder aus, indem Sie das Kontrollkästchen ÄNDERUNGEN WÄHREND DER EINGABE PROTOKOLLIEREN. ARBEITSMAPPE WIRD FREIGEGEBEN deaktivieren.


 
Tipp 68: Begriffe mit identischer Schreibweise in Tabellen finden

Oft kommt es vor, dass in großen Datenbanken Begriffe oder Zahlen, wie z.B. ein Artikel, ein Ort oder auch ein Kunden mit gleicher Schreibweise und somit mehrfach vorliegen. Wenn das nicht gewollt ist, bietet sich eine Plausibilitätsprüfung an, ob der Begriff vorkommt und wenn ja, in welcher Zeile. Und: Dabei soll natürlich bitte auch nach GROSS-und kleinbuchstaben unterschieden werden. Dies geht in strukturierten Datenbanken mit einem Datenfilter, kann aber auch leicht mit einer Funktion und einer Matrixformel gelöst werden.
Hierzu bietet sich die Formel IDENTISCH an, welche zunächst prüft, ob zwei Zeichenfolgen identisch sind. In diesem Fall wird WAHR ausgegeben. Andernfalls gibt die Funktion den Wert FALSCH zurück. IDENTISCH beachtet die Groß- und Kleinschreibung, ignoriert aber Formatierungsunterschiede. Die Syntax lautet: IDENTISCH(Text1;Text2)
Um jetzt z.B. zu überprüfen, ob der Begriff "Müller" in der Spalte B in dem Bereich B2 bis B2500 vorkommt, lautet die Matrix-Formel {=ODER(IDENTISCH("Müller";B2:B2500))}. Da nicht lediglich ein Wert verglichen wird, sondern ein Zellbereich durchsucht werden soll, muss mit ODER und der Matrix gearbeitet werden.
Die Zeilennummer des ersten gefundenen Wertes kann mit der Formel
{=MIN(WENN(IDENTISCH("Müller";B2:B2500);ZEILE(2:2500)))} ermittelt werden.
Anmerkung: Die Formel muss als Matrixformel eingegeben werden. Nachdem Sie das Beispiel in ein leeres Arbeitsblatt kopiert haben, markieren Sie hierzu jede Formelzelle mit einer Matrixformel einzeln. Drücken Sie F2, und dann zusätzlich STRG+UMSCHALT+EINGABE. Nach jeder Änderung an der Formel ist dieser "Kunstgriff" (leider) wieder von Nöten ...


 
Tipp 69: Prüfen, ob Zellbereiche identisch sind

Neben dem Suchen von identisch geschriebenen Begriffen (s. Excel-Tipp 68) kann man auch recht einfach ganze Spalten und Zellbereiche auf ihre Inhalte prüfen und feststellen, ob sie identisch sind. Weiterhin ist es dann z.B. auch möglich, die Anzahl der unterschiedlichen Zellen auszugeben und somit evtl. Fehler oder Falscheingaben ermitteln und bearbeiten zu können. Hierzu bietet sich ebenfalls die Funktion IDENTISCH an.
Angenommen in den zu vergleichenden Spalten A und B sind jeweils Werte in den Zeilen 2 bis 20000. In einer beliebigen anderen Zelle kann man nun folgende Array-Formel eingeben, die die Zellbereiche vergleicht und nur bei exakter Identität (auch Groß-/Kleinschreibung) den Text "Zellen identisch" ausgibt:
{="Zellen "&WENN(UND(IDENTISCH(A2:A20000;B2:B20000));"";"nicht ")&"identisch"}
Wichtig ist das Eingeben der Array-Formel {Matrixformel} ohne die geschweiften Klammern. Diese {Klammern} werden nicht eingegeben sondern zum Abschluss der Formel durch gleichzeitiges Drücken von Strg+Shift+Enter erzeugt (statt Enter allein). Sonst erscheint ggfs. die Fehlermeldung #WERT!
Sind die Zellbereiche nicht identisch, liefert die Wenn-Funktion für den Sonst-Wert den Text "nicht", welcher durch die vor- und nachgesetzten Texte "Zellen" & "identisch" zu dem Ergebnis "Zellen nicht identisch" verkettet wird.
Die Anzahl der abweichenden Inhalte kann in einer separaten Zelle mit dieser Array-Formel angezeigt werden, welche sich zum Zählen der unterschiedlichen Zellen der N-Funktion bedient:
{=SUMME(N(A2:A20000<>B2:B20000))}


 
Tipp 70: Fehlerwerte auf dem Bildschirm anzeigen aber nicht drucken

Ab Excel 2002 können Sie beim Drucken bestimmen, ob bzw. in welcher Form Fehlerwerte dargestellt werden. Unter Menü / Datei / Seite einrichten / Tabelle / Fehlerwerte als, gibt es diesbezüglich vier Varianten zur Auswahl: Dargestellt, leer, -- und #NV. Die Bildschirmanzeige wird von dieser Einstellung nicht beeinflusst.


 
Tipp 71: Benutzerdefinierte Datenreihen schnell erstellen

Wie in Tipp 31 bereits erklärt, lassen sich bestimmte vordefinierte Datenreihen in Excel schnell erstellen. So können z.B. Listen mit Monatsnamen und Wochentagen (mit und ohne Wochenenden) in verschiedenen Formaten schnell durch ziehen der markierten Zelle erzeugt werden. Aber auch benutzerdefinierte häufig wiederkehrende Datenreihen können leicht angelegt und über diese Funktion Autoausfüllen dann schnell erstellt werden. Dies können z.B. die firmeneigenen Produktgruppen, Vertreterlisten, Materialgruppen, Liste der Firmenwagen o.ä. sein.
Angenommen Sie haben immer wieder verschiedene Auswertungen mit bestimmten Materialgruppen zu erstellen und wollen beim Aufbau einer neuen Auswertung diese Materialgruppen schnell in einer Liste untereinander stehen haben. Vor dem ersten Mal müssen Sie Excel mit der Liste einmalig "bekannt" machen und diese als sogenannte Benutzerdefinierte Liste importieren. Dazu sind die einzelnen Daten der Liste wie z.B. Alu, Stahl, Holz, Gummi, etc. untereinander in ein Tabellenblatt zu schreiben und zu markieren. Bis Excel 2007 kann das Fenster Benutzerdefinierte Liste über Extras / Optionen aufgerufen werden, ab 2010 über Datei / Optionen / Erweitert im Bereich Allgemein. Jetzt kann die ausgewählte Liste mit der Schaltfläche Importieren angelegt und mit OK gespeichert werden - fertig. Schreiben Sie nun in ein Feld Ihrer Wahl die erste Materialgruppe Alu, markieren es und ziehen den Anfasser (kleines Quadrat unten rechts in der Markierung) nach unten, sehen Sie beim Ziehen bereits die Vorschau der Werte Stahl, Holz, … und erhalten beim Loslassen die fertige Liste.


 
Tipp 72: Kalenderwoche berechnen

Natürlich bietet Excel auch eine Funktion, um die Kalenderwoche zu berechnen. Die Funktion KALENDERWOCHE hat folgende Syntax:
=KALENDERWOCHE(Datum;Rückgabe)
Das Argument Datum sollte mit einem Verweis auf eine Zelle mit Datum versehen werden, für das Sie die Woche wissen möchten. Excel erwartet das Datum als sogenannte Fortlaufende Zahl (seit dem 01.01.1900). Besondere Beachtung verdient dann auch das Argument Rückgabe, das bis Excel 2007 aus dem Wert 1 oder 2 bestehen konnte. Es bestimmt, an welchem Tag eine Woche beginnt. Wenn Sie eine 1 eingeben, beginnt die Woche an einem Sonntag. Nach diesem Muster wird zum Beispiel in den USA verfahren. Wie in den meisten europäischen Ländern startet die Woche auch in Deutschland am Montag. Sie müssen also eine 2 eintippen, um hier zu einem korrekten Resultat zu kommen. Mitlerweile gibt es weitere Parameter: 11-17 für den Wochenbeginn von Montag bis Sonntag sowie 21 für den Montag gem. ISO (s. unten). Ab Excel 2013 kann auf Kalenderwoche verzichtet werden. Es gibt nun die Funktion ISOWEEKNUM(date), welche aus dem angegebenen Datum die erste Kalenderwoche gemäß ISO-Norm ermittelt. In Europa wird die ISO 8601 verwendet. Als erste Kalenderwoche im Jahr wird die Woche mit dem ersten Januar-Donnerstag definiert. Dadurch fällt der 4. Januar immer in die erste Kalenderwoche.


 
Tipp 73: Annuität berechnen mit der Funktion RMZ

Um zum Beispiel bei Annuitätendarlehen oder Leasingverträgen die monatlichen Zahlungen auszurechnen, kann die Funktion RMZ (RMZ = Regelmäßige Zahlung / die zu zahlende Annuität pro Periode) in Excel genutzt werden. Eine Annuität ist eine regelmäßige, gleich bleibende Zahlung für z.B. einen Kredit. Sie setzt sich aus den Zinsen und der Tilgung zusammen. Während sich Zins- und Tilgungsanteil ständig verändern, bleibt die Annuität jedoch als Summe beider konstant. Die Voraussetzung für die Anwendung dieser Funktion sind konstante Zahlungen und ein konstanter Zinssatz.
Die Syntax lautet: =RMZ(Zins;Zzr;Bw;Zw;F)
Die einzelnen Argrumente haben dabei folgende Bedeutung:
Zins :   Zinssatz pro Periode (Zahlungszeitraum)
Zzr :   Anzahl der Zahlungszeiträume
Bw :   Barwert: Der Gesamtbetrag, den eine Reihe zukünftiger Zahlungen zum gegenwärtigen Zeitpunkt wert ist. Dieser Wert wird auch Kreditbetrag genannt
Zw :   Zukünftiger Wert (Endwert), den Sie nach der letzten Zahlung erreicht haben möchten. Fehlt das Argument Zw, wird der Wert 0 (Null) angenommen, d. h. der Endwert eines Kredits ist gleich 0
F :   Fälligkeit: Gibt an, wann Zahlungen fällig sind; kann den Wert 0 (am Ende einer Periode) oder 1 (am Anfang) annehmen
Beispiel: Es soll ein Darlehen über 50.000 (Bw) aufgenommen werden. Der Zinssatz beträgt 6,0 % p.a. (Zins = 6,0%/12 = 0,5/Monat) und die Laufzeit 5 Jahre (Zzr = 5*12 = 60 Monate). Der Endwert (Zw) bei vollständiger Rückzahlung ist 0; die Fälligkeit (F) da Zahlung am Periodenanfang ist 1. Wie hoch ist die monatliche Rate?
Die Formel lautet:  =RMZ(6,0%/12;5*12;50000;0;1)
Die monatlich zu zahlende Rate inklusive der Zinsen beträgt: -961,83
Die Zinsen betragen übrigens in diesem Beispiel 7.709,86 und der Gesamtaufwand 57.709,86 bei einem effektiven Jahreszinssatz von 6,17% p.a. (interner Zinssatz, IRR).
Da der Wert negativ (als Zahlungsrate) dargestellt wird, können Sie mit der Absolut-Funktion das Ergebnis auch lesbarer gestalten: =ABS(RMZ(6,0%/12;5*12;50000;0;1))
Sie sollten unbedingt darauf achten, dass Sie für Zins und Zzr zueinander passende Zeiteinheiten verwenden (im Beispiel Monate). Wenn Sie für einen Kredit mit fünfjähriger Laufzeit und einem jährlichen Zinssatz von 6% monatliche Zahlungen leisten, müssen Sie für Zins 6%/12 und für Zzr 5*12 angeben.
Wenn Sie den von RMZ zurückgegebenen Wert mit Zzr (5*12) multiplizieren, können Sie den Gesamtbetrag berechnen, den Sie bis zum Ende zu zahlen haben.
Aber Achtung: Ein von RMZ zurückgegebener Betrag umfasst immer nur die Tilgung und die Zinsen. Nicht eingerechnet werden Steuern, Ausgleichszahlungen oder Gebühren, die gelegentlich für Darlehen und Kredite zu berücksichtigen sind.


 
Tipp 74: Druckbereiche dynamisch gestalten

In Excel gibt es die Möglichkeit von großen Tabellen nur bestimmte Bereiche auszudrucken. Hierzu markieren Sie die entsprechenden Zellbereiche der Tabelle und wählen unter "Seitenlayout" die Option "Druckbereich" und den Menüpunkt "Druckbereich festlegen". Der so definierte Druckbereich ist jetzt unabhängig von Änderungen und Ergänzungen in der Tabelle festgelegt. Wenn sich die Tabelle um weitere Zeilen oder Spalten vergrößert, werden diese nicht automatisch mit ausgedruckt sondern der Druckbereich muss neu festgelegt werden. Mit einer Formel kann man den Druckbereich auch in Abhängigkeit von der Anzahl der gefüllten Zeilen gestalten.
Hierzu wählen Sie im Register "Formeln" den Punkt "Namen definieren" und es erscheint ein Fenster, in dem Sie einen Namen für den Druckbereich vergeben können. Geben Sie hier das Wort ‚Druckbereich' ein. In das Feld "Bezieht sich auf" geben Sie noch folgende Funktion ein:
=INDIREKT(TABELLE1!$A$1)
Beenden Sie die Eingaben mit "OK".
$A$1 bezieht sich dabei auf eine Hilfszelle, die auch anderswo stehen könnte und die den nachfolgenden Inhalt haben muss. Angenommen ist im Folgenden auch, dass Ihre Datentabelle in den Spalten A bis H Daten enthält und diese in der Zeile 2 beginnen: Zunächst markieren Sie in diesem Beispiel die Zelle A1 Ihrer Tabelle und geben in A1 folgende Formel ein:
="A2:H"&ANZAHL2(H:H)
Diese bewirkt, dass das Ergebnis der Prüfung, wie viele nicht leere Zellen in der Spalte H sind, an die Definition des Druckbereiches A1:H angefügt wird. Wenn gemäß diesem Beispiel in der Spalte H weitere Zellen mit Inhalten versehen werden, verändert sich künftig automatisch auch der Druckbereich.


 
Tipp 75: Pivot-Tabellen - Detaildaten schnell und einfach anzeigen

Mit Hilfe von Pivot-Tabellen können Sie große Datenmengen schnell und einfach in übersichtliche Tabellen zusammenfassen und analysieren. Ist die Pivot-Tabelle auf der Grundlage von einer großen Anzahl von Datensätzen erst einmal erstellt, kommt es nicht selten vor, dass die zusammengefassten Informationen wieder daraufhin untersucht werden müssen, wie sie sich zusammensetzen. Hierzu kann man natürlich in den Ursprungsdaten mit dem Excel-Filter arbeiten und über ein oder mehrere Kriterien filtern, mit Teilergebnissen die Summen kontrollieren und die ausgewählten Datensätze prüfen. Einfacher und schneller kommt man jedoch mit einem Sprung direkt aus der Pivot-Tabelle zu diesem sogenannten Datenextrakt.
Haben Sie zum Beispiel in einer Pivot-Tabelle mehrere Tausend Umsätze nach Ländern und/oder Produktgruppen sowie für bestimmte Verkäufer aus den Jahres- oder Monatsdaten zusammengefasst, können Sie mit einem einfachen Doppelklick auf die Zelle mit dem zu analysierenden Umsatz einen Datenextrakt (Detaildaten) in einer neuen Tabelle generieren, der die Original-Datensätze enthält, welche in ihrer Summe den angeklickten Umsatzwert ergeben. Excel erstellt die Tabelle immer in einem neuen Tabellenblatt, die Ursprungsdaten bleiben erhalten. Ein Filtern in der Ursprungstabelle ist gar nicht mehr nötig und Sie können in dem obigen Beispiel sofort ersehen, in welchen Ländern mit welchen Produktgruppen der Verkäufer X seine einzelnen Umsätze generiert hat.


 
Tipp 76: Zellkommentare mit Grafiken und Bildern optimieren

Kommentare in Excel sind eine feine Sache, um einzelne Einträge in der Tabelle zu erläutern, Formeln zu erklären oder auch um mehr Hintergrundinformationen zu den Werten zu geben, ohne dass diese die ganze Zeit sichtbar sind und Platz für andere Berechnungen rauben. Dabei lassen sich nicht nur Texte in das Kommentarfeld eingeben sondern vielmehr können auch ganze Grafiken, Bilder, Diagramme u.ä. aufgenommen werden, welche dann erst sichtbar werden, wenn die Zelle mit dem Maus-Cursor angesteuert wird.
Liegt die anzuzeigende Grafik noch nicht als Datei vor, sollte diese zunächst erstellt werden. Das kann u.a. mit Hilfe eines beliebigen Zeichenprogrammes erfolgen. Durch das Kopieren (Str. + c) von bestimmten Zellbereichen oder Diagrammen, dem Einfügen (Strg + v) dieser Daten in das Zeichenprogramm und mit dem Abspeichern dieser so erstellten neuen Datei, z.B. im Bitmap-Format, ist dies schnell erledigt.
Ist die Datei erstellt kann Ihr Inhalt in den Kommentar an der gewünschten Stelle eingefügt werden. Ein Kommentarfeld selber fügen Sie in der markierten Excel-Zelle ein, indem Sie mit der rechten Maustaste auf diese klicken und im erscheinenden Kontextmenü den Menüpunkt "Kommentar einfügen" wählen. Das umrahmte Textfeld kann dann wiederum durch Klick mit der rechten Maustaste auf den Textrahmen weiterbearbeitet werden. Hierzu den Menüpunkt "Kommentar formatieren" wählen. In dem sich öffnenden Fenster können Sie nun diverse Formatierungen für das Kommentarfeld definieren. In dem Register "Farben und Linien" können Bilder und Grafiken eingefügt werden. Hierzu wählen Sie in dem ersten Auswahlfeld "Farbe" den Punkt "Fülleffekte" und in dem nächsten Fenster das Register "Grafik". Über die Schaltfläche "Grafik auswählen" gelangen Sie in ein Dialog-Fenster, in dem Sie zu Ihrer Grafik oder einem Bild für den Kommentarhintergrund navigieren können. Markieren Sie das gewünschte Bild und drücken Sie auf "Einfügen" sowie auf "OK" in den bereits geöffneten Dialogen. Im Register "Farben und Linien" können Sie auch noch die Transparenz des Bildes einstellen, was oft sehr hilfreich ist. Über das Kontextmenü der rechten Maustaste können Sie Kommentare jederzeit wieder bearbeiten, löschen oder auch dauerhaft ein- und ausblenden. Achtung: Zusätzlich eingegebener Text wird über der Grafik angezeigt.


 
Tipp 77: Springen in letzte, erste, linke oder rechte Zellen

Wer viel mit großen und umfangreichen Excel-Tabellen arbeiten muss, der kennt das: Der Cursor steht irgendwo mitten in der Tabelle und man möchte nun direkt in die letzte Zelle einer Spalte springen (um vielleicht das Spaltenergebnis zu kontrollieren). Bei nur wenigen Zeilen ist das ja kaum ein Problem. Aber wenn die Tabelle über hunderte oder tausende von Zeilen hat - auf alle Fälle größer ist als der sichtbare Bereich -, dann ist das Ganze nicht mehr so einfach überschau- und durchführbar. Entweder muss nun gescrollt werden, man nutzt die Sprungtasten und/oder -tastenkombinationen oder man klickt sich einfach per Doppelklick hin: Und letzteres geht so:
Wenn Sie z.B. an das untere Ende einer Tabelle springen möchten (die letzte Zelle einer Spalte), gehen Sie so vor: Markieren Sie eine beliebige Zelle in der Spalte. Es wird jetzt die Randmarkierung dieser Zelle sichtbar und Sie können mit dem Mauscursor über die Markierung gehen. Hierbei verändert der Cursor sein Aussehen. Mit einem Doppelklick auf die untere Randmarkierung der gewählten Zelle springen Sie in die unterste Zelle der Spalte. Genaugenommen springen Sie bis zu der letzten Zelle in der Spalte, bevor eine leere Zelle auftritt. Per Doppelklick können Sie auch in andere Richtungen springen: obere Randmarkierung führt nach oben, linke und rechte in die entsprechenden Richtungen der Zeile.
Das Springen funktioniert wie oben erwähnt auch mit Hilfe von Tastenkombinationen: Mit Strg + Ende springen Sie ans Ende der Tabelle. Mit Strg + Pos1 in die erste Zelle (A1) der Tabelle. Mit Shift + Strg + Pfeiltaste in die entsprechende Pfeilrichtung bis zur letzten gefüllten Zelle des gewählten Zellbereiches.


 
Tipp 78: Summen trotz Fehlerwerten richtig berechnen

Fehlerwerte wie z.B. #NV lassen sich beim Arbeiten mit großen Datenmengen und mehreren Tabellenverknüpfungen oft gar nicht vermeiden und sind auch oft gar nicht im Blickfeld des Betrachters bzw. nicht im gerade angezeigten Tabellenbereich. Die Folge ist jedoch, das Excel mit diesen fehlerhaften Informationen nicht weiterrechnen kann und nachfolgende Berechnungen wie z.B. Summenformeln ebenfalls diese Fehlerwerte liefern. Wenn dies nicht gewollt ist und mit den restlichen Werten weitergerechnet werden muss, können Sie mit einer Funktion trotz der Fehlerwerte z.B. eine Summe bilden.
Mit Hilfe der Tabellenfunktion SUMMEWENN kann der Fehlerwert aus der Summenbildung ausgeschlossen werden. Die Formel lautet für eine Summe in Zelle B100 über die Spalte B2 bis B99 dann z.B. =SUMMEWENN(B2:B99;"<>#NV")
Hierbei wird der Funktion - von dem Semikolon getrennt - zunächst der zu summierende Bereich übergeben und dann als zweites Argument in Anführungszeichen das Kriterium für das Summieren. In diesem Beispiel alles was ungleich dem Fehlerwert #NV ist. Auch andere Fehlerwerte lassen sich so abfangen.


 
Tipp 79: Mit Eingabemeldungen andere Benutzer auf Anforderungen hinweisen

Neben den Kommentaren in einzelnen Zellen gibt es noch weitere Möglichkeiten, andere Nutzer einer Datei auf bestimmte Anforderungen z.B. bei der Eingabe von Daten hinzuweisen. Dies sind Eingabemeldungen, welche bei dem Anklicken der jeweiligen Zelle erscheinen. Ähnlich wie ein Kommentar, nur das die Zellen mit Eingabemeldungen nicht wie diese markiert werden (rote Ecke) und das Eingabemeldungen nicht über das Kontextmenü von dem Anwender bearbeitet werden können.
Diese Meldungen können über die Datenüberprüfung (bis Excel 2003: Gültigkeitsprüfung) erstellt werden. Eigentlich ist die Datenüberprüfung dafür vorgesehen, Eingabemöglichkeiten durch festgelegte Regeln zu beschränken, um Fehler zu vermeiden. Wenn Sie auf die Regeln verzichten, können Sie trotzdem die sehr praktischen Eingabemeldungen nutzen und andere Nutzer informieren und warnen. Dazu markieren Sie die Zellen, die Sie mit Eingabemeldungen versehen möchten. Auch mehrere Zellen können gleichzeitig markiert werden, wenn sie identische Meldungen bekommen sollen. Klicken Sie im Register "Daten" im Bereich "Datentools" auf "Datenüberprüfung" (bis Excel 2003: "Daten"/"Gültigkeit").
In der erscheinenden Dialogbox aktivieren Sie das in der folgenden Dialogbox dargestellte Register "Eingabemeldung" und können dann in der Dialogbox in das Feld "Titel" eine Überschrift für Ihren Hinweis und in das Feld "Eingabemeldung" die notwendigen Informationen eingeben. Diese Eingaben bestätigen Sie mit einem Klick auf "OK". Sobald der Anwender nun eine mit einer Eingabemeldung versehene Zelle aktiviert, wird die Meldung angezeigt.


 
Tipp 80: Prüfen, ob in einem Bereich nur Zahlen auftreten

Wenn Sie einmal schnell prüfen möchten, ob in einem großen und unübersichtlichen Bereich nur Zahlen auftreten, können Sie dazu die Statusleiste einsetzen. Rechts in der Statusleiste zeigt Excel automatisch (und im Standard) die Summe der Zahlen an, die aktuell markiert sind. Also: Ganzen Bereich markieren und wenn in mindestens einer der Zellen ein Fehlerwert auftritt, erscheint diese Summe nicht.


 
Tipp 81: Brüche problemlos in Excel-Zellen eingeben

Wenn Sie einen Bruch mit einem Querstrich in eine Zelle eingeben, wandelt Excel die Eingabe automatisch in ein Datum um. Aus 1/4 wird dann automatisch der 1. April. Um die Darstellung des Bruches mit einem Querstrich zu erzeugen und mit diesem Wert auch weiterrechnen zu können, geben Sie davor einfach die Zahl 0 und anschließend ein Leerzeichen ein. Dann wandelt Excel die Eingabe nicht in ein Datum um. für das erwähnte Viertel geben Sie also 0 1/4 ein. Wenn Sie mit diesem Wert in anderen Zellen weiterrechnen, wird das Format beim Erstellen der Formel auch in diesen Zellen angewandt, kann aber z.B. in Dezimale abgeändert werden (Zellen formatieren).


 
Tipp 82: Prüfen, ob eine bestimmte Datei bereits geöffnet ist

Bei der Arbeit mit komplexeren Daten kann es notwendig sein, dass neben der aktuellen Excel-Datei noch weitere Dateien geöffnet sein müssen, um z.B. dort Daten hineinzuschreiben. Arbeitet man zeitgleich mit mehreren geöffneten Dateien, verliert man ggfs. auch schon mal den Überblick, was alles auf ist und was nicht. Dies kann man zum Beispiel mit einem Makro abfragen; auch kann man die Datei dann gleich mit einem Makro öffnen, wenn sie es noch nicht ist. Allerdings kann man sich auch ohne Makro direkt in einer Zelle anzeigen lassen, ob die Datei XYZ schon geöffnet ist. Hierfür hat Excel nicht direkt eine Lösung parat, kann aber mit Hilfe einer vorhandenen Funktion und einem kleinen Umweg diese Information bereit stellen.
Nehmen wir an, Sie benötigen die Datei "Daten.xls" mit dem Tabellenblatt "Januar". Excel schreibt diese Namen bekanntlich als [Daten.xls]Januar!
Mit Hilfe der Funktion INDIREKT können Sie die Frage beantworten, ob diese Datei geöffnet ist. Die Funktion kann nämlich nicht auf geschlossene Dateien zugreifen. Wenn Sie den Dateinamen direkt in die Formel eingeben möchten sieht es wie folgt aus:
="Die Datei ist "&WENN(ISTFEHLER(INDIREKT("'[Daten.xls]Januar'!A"&1));"nicht";"")&"geöffnet."
Hierbei werden die drei Teile in den Anführungszeichen durch das "&" miteinander verknüpft. Betrachten wir nur den Mittelteil der im geschlossenen Zustand das "nicht" liefern soll:
WENN(ISTFEHLER(INDIREKT("'[Daten.xls]Januar'!A"&1));"nicht";"")
Hier will INDIREKT auf die Datei und deren Zelle A1 zugreifen. Schafft INDIREKT das jedoch nicht, weil die Datei nicht geöffnet ist, wird ein Fehler erzeugt, den ISTFEHLER auffängt und gemäß den WENN-Bedingungen das "nicht" den Verknüpfungen zufügt. Natürlich können Sie die Dateinamen und Blattnamen auch in Zellen hinterlegen. Z.B. in A1: Daten und in B1: Januar, dann lautet der Formelteil: … INDIREKT("'["&A1&".xls]"&B1&"'!A"&1)


 
Tipp 83: Nur positive oder nur negative Werte einer Liste addieren

Oftmals sind in Listen positive und negative Werte bunt gemischt vorhanden (z.B. Rechnungs- und Gutschriftsbeträge). Soll in einer Spalte nun nicht die Gesamtsumme aller Werte aufaddiert, sondern nur die Teilsumme der positiven oder negativen Werte, dann geht das z.B. wie in den folgenden Formeln dargestellt mit Hilfe der Funktion SUMMEWENN:
=SUMMEWENN(A1:A100;">0")
=SUMMEWENN(A1:A100;"<0")



 
Tipp 84: Summierung bei mehreren Suchkriterien durchführen

Gibt es für die Summierung von bestimmten Werten mehrere Suchkriterien in verschiedenen Spalten, so kommt man mit der einfachen Funktion SUMMEWENN nicht weiter, denn SUMMEWENN durchsucht nur eine Spalte nach einem Suchkriterium.
Wenn z.B. alle Zellen summiert werden sollen, bei denen in der 1. Spalte ein bestimmtes Suchkriterium (z.B. Kunde) steht und zusätzlich in der 2. Spalte ein weiteres (z.B. Produkt), schafft SUMMEWENN dies nicht, genauso wie es nicht mehrere Spalten aufsummieren kann.
Abhilfe schafft eine Arrayformel oder ab Excel 2007 auch schon mal SUMMEWENNS.
Fangen wir doch mal einfach mit der Funktion SUMMEWENNS an: Angenommen wir haben in einer Tabelle in Spalte A die Kunden und in B die Produkte. Aufzusummieren sind die Umsätze in Spalte C. Wollen wir nun für einen Kunden A die Summe aller gekauften Produkte B haben, so lautet die Formel:
=SUMMEWENNS(C2:C7;A2:A7;"A";B2:B7;"B")
Im Beispiel ist der Umsatzwertebereich in C2:C7 (Anders als bei SUMMEWENN steht dies Kriterium am Anfang der Formel). Aus den Kunden in A2:A7 soll Kunde A gesucht und dann zusätzlich in B2:B7 nach Produkt B und die Summe der Umsätze bei dieser Kombination ausgegeben werden. Dies lässt sich auch um weitere Kriterien ergänzen, es sind bis zu 127 Paare zulässig ... wer's braucht ...
Mit einer Arrayformel sieht das Ganze dann so aus:
{=SUMME(WENN((A:A="A")*(B:B="B");C:C))}
Hier bedienen wir uns "nur" SUMME und WENN, allerdings innerhalb der Matrix ... Ein Beispiel und Infos zur Erstellung der Arrayformel/Matrixformel finden Sie in unserer Beispieldatei.


 
Tipp 85: Mit der Anzahl von veränderlichen Werten einer gefilterten Liste weiterrechnen

Mit der Funktion ANZAHL kann man kontrollieren, wieviel Zellen bereits mit Werten gefüllt sind und mit diesem Ergebnis z.B. in anderen Zellen weiter rechnen. Nutz man die Funktion jedoch unterhalb einer Liste, deren Zeilen man filtert, liefert die Formel weiterhin das gleiche Ergebnis. Sie bezieht also die gefilterten und ungefilterten Werte mit ein. Möchte man allerdings mit der veränderlichen Anzahl von gefilterten Werten oder Datensätzen weiterrechnen, so empfiehlt sich eine andere Funktion.
Mit der Funktion TEILERGEBNIS, welche vielen vom Berechnen von Summen in gefilterten Listen bekannt ist, geht dies auch. Allerdings kombiniert mit einer anderen Funktionskonstanten als der bekannten SUMME, welche Werte summiert. Die Funktion TEILERGEBNIS(Funktion;Bezug1;[Bezug2];...) hat an der ersten Stelle der Klammer eine Funktionskonstante, deren Wert bestimmt, was Excel mit den Daten in "Bezug" anstellt. Weitbekannt ist hier der Wert 9 für das Bilden von Summen: =TEILERGEBNIS(9;A2:A100)
Für das Berechnen der Anzahl der Werte verwendet man die 2: =TEILERGEBNIS(2;A2:A100)
Weitere Möglichkeiten für Teilergebnisse liefern die Werte 1 für Mittelwert, 3 für Anzahl2, 4 für Max, 5 für Min, 6 für Produkt, 7 für Stabw, 8 für Stabwn, 10 für Varianz und 11 für Varianzen.
Mit Bezug2 können Sie übrigens bis zu 254 Bereiche oder Bezüge benennen, für die Sie das Teilergebnis berechnen möchten… nur falls Sie das mal dringend brauchen ;-)


 
Tipp 86: Aufzählungszeichen ganz leicht in Excel anzeigen

Mal eben eine Aufzählung aus Word in Excel kopiert - und schon sind die Aufzählungszeichen weg. Kennen wir alle, nicht wahr. Leider kann in Excel ein bereits bestehender Text nicht ohne weiteres mit Aufzählungszeichen formatiert werden. Als kleine Hilfe kann da eine Tastenkombination dienen. Aber auch eine Formatierung der Zellen ist mit einem kleinen Trick möglich. Doch zunächst zur Tastenkombi: Drücken Sie in einer leeren Zelle die Kombination Alt + 7 am Ziffernblock (nicht die 7 in der Zahlenreihe auf der Tastatur, denn die ist vorbelegt!). Jetzt fügt Excel einen Aufzählungspunkt ein. Evtl. bereits vorhandener Text wird überschrieben, da die Zelle nicht lediglich formatiert wird. Folgend sollte zur besseren Übersicht ein Leerzeichen und dann der eigentliche Aufzählungstext eingegeben werden. Der Punkt wird als Symbol eingefügt und ist in der Bearbeitungszeile sichtbar. Tipp: je nach Modell kann mit der Tastenkombi Alt + FN + 7 auch am Laptop der Ziffernblock angesprochen werden (ansonsten wird der 7. Befehl in der Quick Access Toolbar ausgeführt).
Um das Überschreiben zu verhindern und auch nachträglich Werte einfügen zu können, sind Zellen auch mit dem Aufzählungspunkt formatierbar. Wichtig auch hierfür ist die Aktivierung des Ziffernblocks auf Ihrer Tastatur. Markieren Sie zunächst die Zellen, in denen Sie die Aufzählungszeichen einfügen wollen. Öffnen Sie das Menü "Zellen formatieren", wählen "Zahlen" und die Kategorie "Benutzerdefiniert" (ganz unten). Klicken Sie in das Textfeld unter "Typ" und löschen Sie den vorhandenen Eintrag. Halten Sie die Alt-Taste gedrückt, geben Sie auf dem Ziffernblock die Zahlen 0149 ein und lassen dann los. Nun erscheint der Aufzählungs-Punkt in diesem Feld. Drücken Sie die Leertaste und danach das @-Symbol. Excel sollte jetzt o @ anzeigen und Sie können Sie mit OK bestätigen und Ihre Aufzählungstexte eingeben.
Haben Sie als Zeichen lieber einen Strich, können Sie alternativ auch einen Bindestrich/Minuszeichen oder etwas anderes für Sie sinnvolles (- ,+ ,= ,$ ,§ , #, …) statt des Punktes (0149) eingeben. Wichtig für die Zuweisung des Formates ist lediglich das @-Symbol.


 
Tipp 87: Durchmesser-Zeichen (Ø) mit Tastenkombination erzeugen

Um das Durchmesser-Zeichen in einer Zelle anzuzeigen kann man sich der Windows-Zeichentabele bedienen und dies dort suchen, kopieren und einfügen. Schneller geht es, wenn man angeleht an Tipp 86 mit einer Tastenkombination arbeitet. Vorausgesetzt, man kann sich Alt-0216 merken ... also Alt-Taste gedrückt halten und auf der Zifferntastatur (wichtig, geht nur dort!) die Ziffernreihenfolge 0216 eintippen. Sobald man die Alt-Taste wieder losläßt, erscheint das Ø-Zeichen. Die Tastenkombinationen aller Sonderzeichen, welche über die deutsche Tastatur erreichbar sind, finden Sie auf der folgenden WebSite in sinnvollen Gruppen gegliedert: http://typefacts.com/tastaturkuerzel


 
Tipp 88: Dropdown-Liste in Excel erstellen

Insbesondere wenn mehrere Personen eine Excel-Datei bearbeiten und diese um Datensätze ergänzen, kann es zu unterschiedlichen Benennungen von Werten oder auch Schreibfehlern u.ä. bei den Einträgen kommen. Diese erschweren nachträgliche Auswertungen wiederum sehr und müssen ggfs. nachträglich korrigiert werden. Hier lohnt es sich vielleicht eine Dropdown-Liste zu erstellen, so dass Sie in ein bestimmtes Feld nur fest vorgegebene Werte eintragen (lassen) können. Auch muss man die Werte nicht immer erneut schreiben, sondern wählt sie einfach aus einer Liste aus.
Bevor Sie eine Dropdown-Liste erstellen können, müssen Sie die möglichen Eingabewerte definieren und in einem (ggfs. nicht sicht-/druckbaren Bereich) als Liste untereinander eingeben. Die Dropdown-Liste erzeugen Sie nun, indem Sie die Zelle anklicken, in der die Auswahl später verfügbar sein soll und dann im Register "Daten" den Bereich "Datentools" und dort "Datenüberprüfung" klicken. In dem sich öffnenden Fester wählen Sie im Register "Einstellungen" bei "Gültigkeitskriterien" im Feld "Zulassen" den Wert "Liste" und geben bei "Quelle" den Bezug zu dem zuvor angelegten Wertebereich ein oder markieren einfach den gewünschten Zellbereich mit der Maus. Stellen Sie jetzt noch sicher, dass das Kontrollkästchen "Zellendropdown" aktiviert ist und drücken Sie "OK". Somit ist die Liste schon fertig und kann in der definierten Zelle genutzt werden.
Optional können Sie in den beiden anderen Registern noch definieren, ob Eingabemeldungen beim Klicken auf die Zelle angezeigt werden sollen, oder wie Excel bei der Eingabe von ungültigen Daten reagieren soll ... einfach ausprobieren!


 
Tipp 89: Auswahlliste mit bereits eingegebenen Daten anzeigen lassen

Manchmal ist es sinnvoll, dass Excel beim Eingeben von neuen Daten bereits Textvorschläge macht. Sie geben die ersten Zeichen eines Wortes ein und erhalten daraufhin einen Vorschlag zur automatischen Vervollständigung des Zelleintrags, also für den gesamten Inhalt der Zelle. Der Vorschlag ist erst noch markiert, kann aber mit der Eingabetaste komplett übernommen werden. Das Vorgehen funktioniert immer, wenn in den darüberliegenden Zellen bereits Inhalte mit gleichem Wortanfang stehen und es keine Verwechslung/Dopplung der Einträge gibt. In manchen Fällen sind aber relativ viele Zeichen einzugeben, bis Excel endlich einen eindeutigen Eintrag erkennt und vorschlägt. Schwierig bzw. viel Aufwand ist es also, wenn viele gleich beginnende oder auch komplexe Inhalte in den Zellen verarbeitet werden sollen. Sind die einzugebenden Texte immer wiederkehrend, gibt es auch die Möglichkeit, sich alle bereits über der Zelle eingetragenen Inhalte in einer Auswahlliste anzeigen zu lassen und den gewünschten Wert einfach zu selektieren.
Um diese Auswahliste zu erhalten, müssen Sie nur die Tastenkombination "ALT" + "Pfeil nach unten" drücken. Excel bietet dann in der markierten Zelle eine Auswahlliste mit allen darüber liegenden Informationen an. Die Auswahl können Sie nun mit der Maus oder mit den Pfeiltasten treffen und mit der Eingabetaste bestätigen - fertig. Sollte bei Ihnen schon das automatische Vervollständigen der Zellwerte nicht funktionieren, ist es evtl. in den "Excel-Optionen/Erweitert" in den "Bearbeitungsoptionen" zu aktivieren.


 
Tipp 90: Datum eines Wochentages in einer bestimmten Kalenderwoche errechnen

Im Projektcontrolling kann es sinnvoll sein, in Berichten einzelne Fälligkeitsdaten für bestimmte Projektschritte anzugeben. Oftmals wird hierzu der letzte Tag der Woche gewählt und dieses Datum per Hand eingetragen. Dabei gibt es Möglichkeiten das Datum z.B. eines Freitages automatisch mithilfe der Jahreszahl und der Kalenderwoche zu berechnen - und diese werden ja üblicherweise in Projekten mitgeführt. Zur Lösung dieses Problems kombinieren Sie die Funktionen DATUM und WOCHENTAG miteinander, welche die folgende Syntax haben:
DATUM(Jahr;Monat;Tag)     und     WOCHENTAG(Zahl;Typ)
Die Formel für die Berechnung des Freitags der Kalenderwoche 16 in 2016 lautet beispielsweise:
=DATUM(2016;1;7*16+1-WOCHENTAG(DATUM(2016;;);3))
Als Ergebnis wird das Datum 15.04.2016 ausgegeben, der heutige Freitag!
Excel kommt mit den beiden verschachtelten Funktionen somit allein in die Nähe des Tages, konkret aber immer nur bis zum Donnerstag und braucht somit von Ihnen noch die klare Info, welchen Tag es ausgeben soll. Für Freitag ist somit +1 im Mittelteil der Formel einzugeben. Für die anderen Wochentage verwenden Sie die folgenden Werte: Montag -3, Dienstag -2, Mittwoch -1, Donnerstag -0, Samstag +2 und Sonntag +3.


 
Tipp 91: Spalten einer Tabelle nach Zeileninhalt sortieren

Die Standardeinstellung für das Sortieren in Excel ist das Sortieren nach Spalteninhalt. Dies ist sicherlich auch die gebräuchlichste Variante, jedoch nicht die einzige Möglichkeit, um Listen zu sortieren. Falls Sie eine Liste mit Zeilenstruktur sortieren möchten oder z.B. in einer Tabelle die Spalten nach den Überschriften in alphabetische Reihenfolge sortieren möchten, können Sie dies problemlos erledigen. Lediglich die Option hierfür hat man in Excel etwas versteckt:
Markieren Sie die komplette Liste, welche Sie sortieren wollen, ggfs. incl. der Spaltenüberschriften. Klicken Sie im Register DATEN auf SORTIEREN. Stören Sie sich nicht daran, dass die erste Zeile nicht mitmarkiert ist, zu diesem Zeitpunkt glaubt Excel noch, dass Sie "normal" sortieren wollen und erkennt Überschriften. Soweit also alles noch ganz bekannt von dem üblichen Sortieren nach Spalteninhalt. Jetzt wählen Sie jedoch die Schaltfläche OPTIONEN. In dem sich öffnenden Fenster ist "Zeilen sortieren" ausgewählt. Wenn Sie die Option "Spalten sortieren" wählen und mit OK bestätigen, ändern sich die Eingabemöglichkeiten in dem vorherigen Fenster. Sie können jetzt nach Zeilen sortieren und z.B. die Zeile, nach der sortiert werden soll, auswählen. Dies könnte z.B. die Zeile mit der Spaltenüberschrift sein. Klicken Sie auf OK und die Spalten werden sortiert.
Wenn Sie im selben Arbeitsblatt eine andere Liste wieder zeilenweise sortieren möchten, dann müssen Sie die Option wieder entsprechend umstellen. Schalten Sie dazu die Option "Spalten sortieren" im Dialogfenster zurück auf "Zeilen sortieren". Auf Sortiervorgänge in anderen Mappen oder anderen Arbeitsblättern hat das Umstellen auf das Sortieren nach Spalten keinen Einfluss.
Übrigens: Wenn Sie Zeilen sortieren, die Teil einer Arbeitsblattgliederung sind, werden die Gruppen der höchsten Ebene (Ebene 1) von Excel sortiert, sodass die Detailzeilen oder -spalten zusammen bleiben, selbst wenn die Detailzeilen oder -spalten ausgeblendet sind.


 
Tipp 92: Unterschiedliche Tabellenbereiche zur Präsentation auf einer Seite ausdrucken

Oftmals hat man in größeren Tabellen und Dateien mehrere Teilergebnisse, Diagramme und Darstellungen, welche nicht direkt beieinander stehen und somit selbst mit einer Druckbereich-Optimierung nicht auf einer Seite ausgedruckt oder auf ein PDF gebracht werden können bzw. nicht auf eine Präsentationsseite für PPT passen. Wenn Sie die Bereiche markieren und drucken, werden leider alle auf einzelne Seiten gedruckt/gespeichert. In wiederkehrenden Berichten die Ergebnisse immer wieder kopieren und zusammenzuflicken, ist eine zu aufwendige Lösung. Mit der bereits für die Überwachung von Formeln in Tipp 47 vorgestellten Kamerafunktion können Sie die Teilbereiche jedoch einmal auf einem extra Tabellenblatt in einem späteren Druckbereich zusammenstellen und auch künftig bei aktualisierten Daten immer noch leicht auf einer Seite verfügbar haben.
Da die Kamera nicht über die Standard-Menüleiste erreichbar ist, empfiehlt es sich, sie in die Symbolleiste aufzunehmen. Bis Excel 2003 über "Ansicht", "Symbolleisten", "Anpassen ..." und im Register "Befehle" in der Kategorie "Extras" die Kamera selektieren. Ab Excel 2007 die Symbolleiste für den Schnellzugriff anpassen - "weitere Befehle", "alle Befehle" zeigt alphabetisch gelistet die Kamera. Markieren Sie dann einen gewünschten Tabellenbereich, ein Diagramm o.ä. und klicken Sie auf das Kamera-Symbol. Excel selectiert dann diese Zelle und ein folgender Klick in einen neuen Tabellenbereich/Druckbereich fügt das "Foto" als dynamisches Bild ein. Stellen Sie mit Hilfe solcher verknüpfter Bilder nun in dem neuen Tabellenbereich eine Druckansicht aller Tabellenbereiche und Darstellungen zusammen, die Sie für den Ausdruck brauchen. Alle künftigen Veränderungen in den Originalzellen werden aufgrund der bestehenden Verknüpfung in Ihrem neuen Druckbereich aktuell und komprimiert dargestellt. Wichtige Voraussetzung für den Druck: Sorgen Sie dafür, dass im Kontextmenü unter "Grafik formatieren" auf der Registerkarte "Eigenschaften" die Option "Objekt drucken" aktiviert ist.


 
Tipp 93: Schnelles Kopieren von Tabellenblättern

Kopien von Tabellenblättern können in einer Exceldatei mit 5 Klicks erstellt werden. Gewöhnlich ist das ein Rechtsklick auf den Reiter des Tabellenblattes für das Öffnen des Kontextmenüs, dann in der Auswahl auf "Verschieben/Kopieren" sowie noch die Wahl der Stelle zum Einfügen der Kopie und den Haken für Kopie, damit das Blatt nicht einfach nur Verschoben wird. Die Bestätigung mit "OK" erstellt dann die Kopie. Das ganze geht aber auch mit nur einem Klick!
Wenn Sie den Klick auf das zu kopierende Tabellenblatt halten und zusätzlich die Taste "Strg" drücken und gedrückt halten, kann eine Kopie des Tabellenblattes nun mit dem Mauszeiger an eine beliebige Position neben das bereits bestehende Tabellenblatt gezogen und dort fallengelassen werden (Loslassen der Maustaste). Excel zeigt beim Ziehen bereits, analog zum Kopieren von Dateien im Windows Explorer, durch ein kleines "+" an, dass das Objekt/Tabellenblatt nun nicht nur verschoben, sondern kopiert wird.


 
Tipp 94: Differenz zweier Datumsangaben unter Berücksichtigung von Schaltjahren

Ab Excel 2013 kann man die Differenz zweier Datumsangaben (Anzahl Tage) zusätzlich zu der in Tipp 55 beschriebenen Funktion DATEDIF() auch mit der Funktion DAYS() berechnen. Hierbei werden auch die Schaltjahre korrekt berücksichtigt. Die Syntax lautet wie folgt: DAYS(Endedatum; Anfangsdatum). Selbstverständlich können Sie auch mithilfe einer einfachen Subtraktion zweier Datumswerte eine Differenz erhalten, achten Sie dabei aber auf die Schaltjahre ... ;-)


 
Tipp 95: Text in gültigen Zahlenwert umwandeln

Gerade bei aus Vorsystemen importierten Daten kommt es immer wieder vor, dass Zahlen von Excel nicht als solche erkannt, sondern als Text interpretiert werden. Bis Excel 2007 blieb dann nur der Weg über den Textimport-Assistenten oder die Funktion WERT, welche allerdings mit Dezimalzahlen nicht funktioniert. Seit Excel 2013 gibt es hierfür die neue Funktion NUMBERVALUE. Diese Funktion wandelt eine als Text erkannte Zahl in einen gültigen Zahlenwert um. Als Argumente übergeben Sie der Funktion NUMBERVALUE(Text, Decimal_separator, Group_separator) den Text und das in der Datenquelle verwendete Dezimaltrennzeichen sowie optional noch ein Tausendertrennzeichen. Als Ergebnis erhalten Sie dann eine Zahl mit dem Standard-Dezimaltrennzeichen und ggfs. dem Tausendertrennzeichen.


 
Tipp 96: Inhalte schnell in alle angrenzenden Zellen kopieren

Sie haben neben einer bestehenden Tabelle in einer neuen Spalte Werte für eine Berechnung in der obersten Zelle eingetragen und die Formel passt. Jetzt möchten Sie diese natürlich gerne bis an das Ende der Tabelle runter kopieren. Das geht mit kopieren und einfügen sicher auf unterschiedliche Art und Weise mit Maus und/oder Tastatur, ist bei sehr langen Tabellen manchmal umständlich, wenn das Tabellenende nicht im sichtbaren Bereich ist. Den in der Zelle stehenden Inhalt können Sie aber auch schnell mit der Maus in alle angrenzenden Zellen unterhalb dieser Zelle kopieren. Hierzu genügt es, die Zelle mit dem zu kopierenden Inhalt zu markieren. Es wird jetzt die Randmarkierung dieser Zelle sichtbar und Sie können mit dem Mauscursor über die Markierung an der rechten unteren Ecke gehen, wo sich der sogenannte "Anfasser" (auch: Ausfüllkästchen) befindet. Dabei verändert der Cursor sein Aussehen zu einem kleinen, schwarzen Kreuz. Hier mit der Maus ein Doppelklick und der Inhalt wird bis zu der letzten ausgefüllten, angrenzenden Zelle kopiert. Vorsicht ist geboten, wenn zwischendurch angrenzende Zellen leer sind, dann wird auch der Einfügevorgang hier abgebrochen.


 
Tipp 97: Formel schnell ohne Anpassung der relativen Zellbezüge kopieren

Wenn Sie eine Formel von einer Zelle in die darunter liegende kopieren, passt Excel die standardmäßig verwendeten relativen Zellbezüge automatisch an. Oft ist es jedoch erwünscht, eine Formel mit genau denselben Bezügen in eine andere Zelle zu kopieren. Eine Möglichkeit ist hierbei, die Formel in der Bearbeitungsleiste zu markieren, dort zu kopieren und dann in die gewünschte Zelle einzufügen. Alternativ kann man natürlich auch nach dem Kopieren die Bezüge ändern oder kurzfristig zu absoluten Bezügen wechseln. Diese Möglichkeiten sind, insbesondere bei aufwendigen Formeln, recht umständlich und es lässt sich einfacher lösen. Excel bietet hierzu eine spezielle Tastenkombination an. Sie markieren zunächst die Zelle unterhalb der Zelle mit der zu kopierenden Formel. Mit der Tastenkombination Strg + , können Sie jetzt die Formel über der aktiven Zelle übernehmen. Die Tastenkombination Strg + Shift + , macht übrigens das Gleiche bei Werten. Soll die Kopie der Formel nicht in der Zelle direkt unter dem Original landen, sondern in einer beliebigen anderen Zelle, kann die Tastenkombi evtl. auch weiterhelfen. Wenn keine Formate zu Schaden kommen, lässt sich die neu erzeugte Formel durch "Anfassen" und "Verschieben" an den gewünschten Ort rücken, da ja beim Verschieben die relativen Zellbezüge erhalten bleiben. Gleiches gilt übrigens auch für das Ausschneiden: Sie können die Formel per Maus mit Ausschneiden und Einfügen oder per Tastenkombi STRG + x und STRG + v an den neuen Ort bringen.


 
Tipp 98: Zeiten abrunden und Zeitformate ändern

Für das Runden von Zahlen bietet Excel bekanntlich einige Standardfunktionen wie RUNDEN, ABUNDEN, AUFRUNDEN, VRUNDEN, OBER- und UNTERGRENZE und vielleicht noch mehr an. Doch wenn es an das Rechnen mit Zeiten und unterschiedliche Schreibweisen von Zeiten (hh:mm und hh,mm) geht, wird es oftmals schwierig. Angenommen also, Sie möchten Werte in den Stundenaufschreibungen im Format hh:mm für den Bericht auf die volle Viertelstunde abrunden und damit weiterrechnen. Soll heißen aus der erfassten Zeit 8:14 sollen 8,00 Stunden werden, aus 8:21 nur noch 8,25 oder aus 8:59 nur 8,75 Stunden.
Da diese Zeitwerte nicht im Zehnersystem ausgedrückt werden, müssen Sie hierfür einen kleinen Trick nutzen: Multiplizieren Sie die Zeit zunächst mit der Anzahl der Stunden eines Tages, also 24. Da wir auf Viertelstunden runden möchten, müssen wir die Stunde noch weiter in 4 Viertelstunden teilen - also multiplizieren mit 4. Sie können auch direkt mit 96 multiplizieren. Für Rundungen auf zehn Minuten übrigens mit 144, auf fünf Minuten mit 288. Dieses Ergebnis können Sie jetzt runden und dann durch die gewählten Multiplikatoren teilen. Das Ergebnis sind jetzt die gerundeten Stunden (noch in dem selben Zeitformat), also z.B. 8:00 Stunden. Die Formel für einen Zeitwert in der Zelle A1 lautet bis hier: =ABRUNDEN((A1*24*4);0)/24/4 oder alternativ: =ABRUNDEN((A1*96);0)/96
Fehlt aber noch die Umrechnung des Formates. Hierfür ist es nützlich zu wissen, dass Excel zum Rechnen im Zehnersystem den Wert von 1/24-tel Tag für eine Stunde ansetzt. Also ist für die Umrechnung vom Format hh:mm in das Zehnersystem mit 24 zu multiplizieren und umgekehrt durch 24 zu dividieren. Wenn wir uns in der obigen Formel das multiplizieren und dividieren mit 24 kürzen, lautet die fertige Formel: =ABRUNDEN((A1*24*4);0)/4


 
Tipp 99: Zellinhalte mit Bezügen, Formaten und Spaltenbreite komfortabel in andere Bereiche kopieren

Nachdem Sie einmal strukturierte Daten, wie z.B. Produkt-, Projekt- oder Arbeitsschrittbezeichnungen in einem Arbeitsblatt angelegt haben, werden diese häufig auch in anderen Arbeitsblättern wieder für andere/folgende Auswertungen benötigt. Ärgerlich ist es dann, wenn man diese "nur" einfach kopiert hat und die Struktur oder Reihenfolge der Daten sich nachträglich ändert oder erweitert. Die elegantere Methode ist der Einsatz von relativen Bezügen aus dem zweiten Arbeitsblatt auf den Quellbereich im ersten. Anstatt einen relativen Bezug jedoch von Hand einzugeben und durch Kopieren, Einfügen und Autoausfüllen oder auch Ziehen mit der Maus auf entsprechend viele Zelle zu übertragen, lässt sich auch eine recht unbekannte Excel-Möglichkeit nutzen, welche schneller und komfortabler ist.
Markieren Sie zunächst den gesamten Bereich, auf den Sie sich beziehen möchten und kopieren Sie diesen. Klicken Sie jetzt in die linke obere Zelle in Ihrem Zielbereich. In den älteren Excel-Versionen rufen Sie den Befehl "Inhalte einfügen" auf. Hier finden Sie die Schaltfläche "Verknüpfen". In den neueren Versionen finden Sie "Verknüpfungen einfügen" in den "Einfügeoptionen" unter "Weitere Einfügeoptionen" als Schaltfläche mit dem "Kettensymbol".
Der kopierte Bereich wird jetzt verknüpft eingefügt. Dies bedeutet, das bei Änderungen der Ursprungszellen automatisch auch die Inhalte in den verknüpften Zellen geändert werden. Beim verknüpften Einfügen überträgt Excel nur die reinen Daten, nicht die Formate der Ursprungszellen. Die Formate und sogar die Zellenbreite können Sie in weiteren Arbeitsschritten zusätzlich übernehmen, wenn Sie jeweils wiederholt "Inhalte einfügen"/"Einfügeoptionen" wählen und nacheinander einzeln auf "Formate" bzw. "Spaltenbreite" klicken. Dies funktioniert solange, wie sich die kopierten Informationen noch im Zwischenspeicher befinden.


Schauen Sie auch in unserer Excel-Makroliste nach arbeitsvereinfachenden Möglichkeiten für Ihr Controlling!

© 2001/2017 ControllerSpielwiese.de powered by Joachim Becker WebSolutions
Gerne können Sie bei uns werben.