Übersicht aller Excel-Tipps und -Tricks der ControllerSpielwiese
Übersicht aller Excel-Tipps und -Tricks der ControllerSpielwiese
Der Einfachheit halber haben wir alle 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 |
Mit Hilfe der Tastenkombination Strg + . wird das aktuelle Datum in die markierte Zelle eingegeben. Die Kombination der Tasten Strg + Shift + . trägt 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 deaktiviert den Pinsel schließlich wieder. |
| 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 |
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 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. 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) |
| 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 Buchhaltung kommt es vor, dass man Soll- und Habenwerte (z.B. zum Abgleich) getrennt voneinander aufsummieren möchte. Hierzu kann man eine Matrixformel und die Funktion ABS verwenden, die den Absolutwert einer Zahl liefert. ABS wandelt negative Zahlen in positive um, positive Zahlen bleiben dagegen positiv. Die Syntax lautet ABS(Zahl) wobei Zahl eine reele Zahl sein muss. Für die pos. und neg. Beträge lauten die Formeln 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. |
Schauen Sie auch in unserer Makroliste nach arbeitsvereinfachenden Möglichkeiten für Ihr Controlling!
Gerne können Sie bei uns werben. |