Microsoft Excel - Tipps und Tricks
Auf dieser Seite finden sie einige Tipps und Tricks zu Formeln, der Bedienung und Funktionen in der Tabellenkalkulation Excel von Microsoft.
Excel Tabelle als Grafik Speichern
Excel Tabellen und Ausschnitte aus deren, kann man ganz leicht als eine Grafik speichern um diese dann in anderen Programmen einbinden zu können und somit unveränderlich zu machen.
Markieren Sie dazu die Tabelle oder den gewünschten Bereich und klicken mit gedrückter Umschalttaste auf Bearbeiten in der Menüleiste. Anstelle des Befehls kopieren wird nun der Befehl Bild kopieren angezeigt. In dem Dialog kann man nun unter Darstellung die Option Wie ausgedruckt aktivieren und dann mit OK schließen. Excel kopiert nun die Tabelle bzw. den Ausschnitt in den Zwischenspeicher (mit der im Druckertreiber eingestellten Auflösung).
Der Inhalt des Zwischenspeichers lässt sich nun mit der üblichen Tastenkombination Strg+V zB. in Word, Grafikprogrammen und anderen Programmen einfügen.
In Excel ab Version 2007 findet man den Befehl direkt im Ribbon Start, wenn man unter dem Befehl Einfügen auf den Pfeil Button klickt und im Menü nun als Bild - Als Grafik kopieren anwählt.
Schneller Zugriff auf Excel-Registerblätter
Bei Tabellen mit vielen Registerkarten muss man oft suchen und entsprechend scrollen um das gewünschte Blatt zu finden.
Einfacher und schneller geht es über das Kontextmenü, welches man aufruft indem man mit der rechten Maustaste auf die Scroll Schaltflächen neben den Blatt Registern klickt. Nun werden die in der Datei vorhandenen Tabellenblätter in einer Liste angezeigt, in der man nun die gewünschte Tabelle auswählen kann.
Zellbereiche mit gleichen Werten füllen
Möchten man in der Tabellenkalkulation Excel einen ganzen Zellbereich mit gleichen Werten füllen, geht dies am schnellsten wenn man zuerst alle Zellen markiert, in denen dieser Wert erscheinen soll. Geben Sie dann die Zahl oder den Text ein (die Markierung bleibt dadurch erhalten) und bestätigen dies einfach mit der Tastenkombination Strg+Return.
Alle Zellen des zuvor markierten Bereichs werden nun mit den gleichen Daten gefüllt.
Ermitteln wie oft ein Wert vorkommt
Um zu ermitteln wie oft ein bestimmter Wert innerhalb einer Excel-Tabelle vorkommt, gibt es die ZÄHLENWENN Funktion. Mit dieser kann man sich ausgeben lassen wie oft die definierte Bedingung vorkommt.
Einfache Beispielformel ist zB =ZÄHLENWENN(A1:A20;8), mit der man zuerst den Bereich angibt in dem gesucht wird und dann die Bedingung/Suchkriterium festlegt die erfüllt werden soll. Also wie oft die 8 in diesem Bereich vorkommt. Anstelle der 8 könnte hier auch zB. Auto und anderer Text stehen. Genauso könnte der Text Auto auch schon in einer anderen Zelle stehen wie B1, dann gibt man einfach als Bedingung den Zellbezug B1 anstelle des Textes in die Formel ein.
Ein weiteres Beispiel =ZÄHLENWENN(A1:A20;“>8“). Hier wird dann als Ergebnis ausgeworfen wie oft Werte in dem Bereich größer als 8 sind.
Es ist auch möglich Ausgaben zu addieren mit der Formel =ZÄHLENWENN(A1:A18;4)+ZÄHLENWENN(A1:A18;6). Hier wird dann einfach die Anzahl von 4 und 6 addiert und das Ergebnis ausgegeben.
Tage zwischen zwei Datumsangaben berechnen
In Excel kann man leicht die Differenz zweier Datumswerte berechnen, um zu ermitteln wie viele Tage zwischen den beiden Datumsangaben liegen. Dazu nutzt man die einfache Formel zum subtrahieren, wie eben auch bei normalen Zahlenwerten wie zB. =B1-A1.
Erhält man nun aber anstelle der Anzahl der dazwischenliegenden Tage einen unsinnigen Wert, muss noch das Format der Ergebniszelle umgestellt werden. Dazu einfach die Zelle markieren und nach einen rechtsklick mit der Maus auf Zellen formatieren, im sich öffnenden Kontextmenü gehen. Dort dann im Register Zahlen die Einstellung Standard wählen, um den Wert korrekt angezeigt zu bekommen.
Excel will einige Zellen partout nicht als Zahlen erkennen
Excel hat manchmal das Problem den Inhalt von Zellen nicht als Zahlen zu erkennen, selbst nach Umstellung in das richtige Format.
Eine einfache Lösung für dieses Problem ist eine "1" in die Zwischenablage zu kopieren und danach die betreffenden Zellen zu markieren. Im Dialogfeld Inhalte einfügen (Kontextmenü mittels rechter Maustaste) wählt man dann als Vorgang Multiplizieren und bestätigt dies mit der OK Schaltfläche.
Excel multipliziert nun die Werte in den betroffenen Zellen mit der 1 aus dem Zwischenspeicher und schreibt das Ergebnis in die Zellen zurück.
Eine Anmerkung in diesem Fall, man sieht das man mit Inhalte einfügen auch Grundrechenarten ausführen kann mit Zahlen aus dem Zwischenspeicher, wodurch jedoch immer die Zahlen im markierten Bereich mit dem Ergebnis überschrieben werden!
Funktion um den höchsten und den zweithöchsten Wert zu ermitteln
Um den größten Wert in einem Tabellenbereich zu ermitteln, gibt es die einfache Funktion MAX. Die Funktion benötigt dazu nur die Angabe des Bereiches in dem gesucht werden soll. Dies sieht dann so aus: =MAX(A1:B10).
Um nun den zweit oder dritthöchsten Wert zu ermitteln nutzt man die Funktion KGRÖSSTE, welche aber nun neben dem Bereich eine zweites Argument benötigt. Der Aufbau sieht dann wie folgt aus: =KGRÖSSTE(A1:B10;2).
Auch hier wir der Bereich A1 bis B10 durchsucht, in diesem Fall nach dem zweithöchsten Wert was durch die 2 angegeben ist. Eine 3 ist dann der dritthöchste Wert usw. und eine 1 erfüllt dann die gleiche Funktion wie vorher MAX.
Inhalte von Zellen verbergen
Wenn man den Inhalt einer Zelle sowohl auf den Bildschirm als auch auf den Monitor verbergen möchte gibt es dafür verschiedene Möglichkeiten. Eine davon ist der Zelle ein entsprechendes Format zuzuweisen.
Dazu markiert man die Zelle und ruft über einen Rechtsklick mit der Maus den Befehl Zellen formatieren im Kontextmenü auf. In dem sich öffnenden Dialogfenster wählt man das Register Zahlen und dort dann die Kategorie Benutzerdefiniert aus. Mit der Eingabe von drei Semikolons (;;;) und anschließenden bestätigen mit der OK Schaltfläche, wird der Inhalt der Zelle nun nur noch in der Eingabezeile angezeigt.
Werte verschiedenen Zahlenbereichen zuordnen
(verschachtelte Wenn Funktion)
Hierfür gibt es mit dem Beispiel eines Kontos und der Zinsführung eine gute Vorlage. Wenn in der Zelle A1 ein Wert über 100 steht soll 1% in der Zelle B1 angezeigt werden, über 500 dann 2,5%, zwischen 5000 und 10000 dann 3% und alles über 10000 soll 4% ausgegeben werden.
Die Formel zur entsprechenden Ausgabe baut sich entsprechend auf:
=WENN(A1>10000;4;WENN(A1>5000;3;WENN(A1>500;2,5;WENN(A1>100;1;))))
Um die Verschachtelung leichter zu machen, fängt man mit dem größten Wert an. Sobald die Funktion feststellt das in A1 der Wert nicht über 10000, liegt wird mit der nächsten Wenn Funktion geprüft, ob der Wert zwischen 5001 und 10000 liegt. Dies geht solange weiter bis der Wert in A1 in einen der Bereiche liegt und dann der gewünschte Wert in B1 angezeigt wird.
Vereinfachtes Schema der Wenn Funktion: =WENN(Prüfung;Dann_Wert;Sonst_Wert)
Mit dem ersten Parameter (Prüfung) wird die Bedingung geprüft und mit positiv bzw. negativ bewertet. Der zweite Parameter (Dann_Wert) gibt den Wert aus bei einen positiven Ergebniss, während der dritte (Sonst_Wert) bei einen negativen Ergebniss ausgeworfen bzw. ausgeführt wird.
Formel Ausgabe
Bei einer Vorlage in der man Formeln festgelegt hat und erst später die Werte zur Berechnung einträgt, werden oft Ausgaben wie zb. bei einer Divison #DIV/0 angezeigt. Das folgende einfache Beispiel zeigt wie man mit der Wenn Funktion diese unterdrücken kann.
Die Formel steht im Beispiel in der Zelle C1: =WENN(B1="";"";WENN(B1=0;"";A1/B1))
Zuerst wird geprüft ob in der Zelle B1 kein Wert eingetragen ist, trifft dies nicht zu (also positiv) wird so angewiesen in C1 nichts einzutragen, dies erfolgt mit den beiden Anführungszeichen. Steht in B1 nun ein Wert (also negative Prüfung), kommt die zweite Wenn Funktion ins Spiel. Ist der Wert jedoch eine 0, wird ebenfalls mit der zweiten WENN Funktion nichts ausgegeben. Sobald nun aber ein Wert größer als 0 eingetragen wird (zweite negative Prüfung), erfolgt die Berechnung wo der Wert in Zelle A1 durch den Wert in B1 dividiert und das Ergebnis in C1 angezeigt wird.
Auswahlliste für Feldeingaben erzeugen
Möchte man in einer Zelle eine Auswahlliste mit verschiedenen Werten hinterlegen, die man beim anklicken der Zelle angeboten bekommt, geht man wie folgend vor.
In Excel ab Version 2007 findet man unter dem Ribbon Daten, den Befehl Datenüberprüfung. Nach dem anklicken öffnet sich ein Dialogfeld, in dem zuerst das Register Einstellungen interessant ist. Dort wählt man unter Zulassen die Option Liste aus und gibt die gewünschten Werte unter Quelle ein, in dem mehrere durch ein Semikolon getrennt werden. Auch gibt es die Möglichkeit einen Bezug zu einen Bereich mit den gewünschten Werten zu erstellen.
Formeln und Zellen schützen ohne Blattschutz
Es gibt Anwendungsfälle in Excel wo es nicht möglich ist Listen mit Formeln zu verlängern usw., solange der Blattschutz aktiv ist. Die folgende Lösung funktioniert ohne Blattschutz und es ist sogar möglich eine Meldung ausgeben zu lassen, die erscheint sobald versucht wird die Formel zu überschreiben. Es ist jedoch kein Problem die Zelle zu kopieren, die Kopie hat dann selbstverständlich den gleichen Schutz. Dazu geht man also wie folgend vor.
In Excel Version 2007, gibt es unter dem Ribbon Daten, den Befehl Datenüberprüfung. Nach dem anklicken öffnet sich ein Dialogfeld, in dem zuerst das Register Einstellungen interessant ist. Dort wählt man unter Zulassen die Option Textlänge aus und wählt unter Daten die Option zwischen. Darunter gibt man nun noch für Minimum und Maximum jeweils den Wert 0 ein. Unter dem Karteireiter Fehlermeldung ist es dann auch Möglich die Meldung einzugeben, welche angezeigt werden soll falls sich jemand an der Zelle zu schaffen macht.
Einen Nachteil hat diese Lösung jedoch. Die Formel bzw. Werte in der Zelle lassen sich zwar nicht überschreiben, aber immer noch mit der Entf Taste löschen!
Auf dieser Seite finden sie einige Tipps und Tricks zu Formeln, der Bedienung und Funktionen in der Tabellenkalkulation Excel von Microsoft.
Excel Tabelle als Grafik Speichern
Excel Tabellen und Ausschnitte aus deren, kann man ganz leicht als eine Grafik speichern um diese dann in anderen Programmen einbinden zu können und somit unveränderlich zu machen.
Markieren Sie dazu die Tabelle oder den gewünschten Bereich und klicken mit gedrückter Umschalttaste auf Bearbeiten in der Menüleiste. Anstelle des Befehls kopieren wird nun der Befehl Bild kopieren angezeigt. In dem Dialog kann man nun unter Darstellung die Option Wie ausgedruckt aktivieren und dann mit OK schließen. Excel kopiert nun die Tabelle bzw. den Ausschnitt in den Zwischenspeicher (mit der im Druckertreiber eingestellten Auflösung).
Der Inhalt des Zwischenspeichers lässt sich nun mit der üblichen Tastenkombination Strg+V zB. in Word, Grafikprogrammen und anderen Programmen einfügen.
In Excel ab Version 2007 findet man den Befehl direkt im Ribbon Start, wenn man unter dem Befehl Einfügen auf den Pfeil Button klickt und im Menü nun als Bild - Als Grafik kopieren anwählt.
Schneller Zugriff auf Excel-Registerblätter
Bei Tabellen mit vielen Registerkarten muss man oft suchen und entsprechend scrollen um das gewünschte Blatt zu finden.
Einfacher und schneller geht es über das Kontextmenü, welches man aufruft indem man mit der rechten Maustaste auf die Scroll Schaltflächen neben den Blatt Registern klickt. Nun werden die in der Datei vorhandenen Tabellenblätter in einer Liste angezeigt, in der man nun die gewünschte Tabelle auswählen kann.
Zellbereiche mit gleichen Werten füllen
Möchten man in der Tabellenkalkulation Excel einen ganzen Zellbereich mit gleichen Werten füllen, geht dies am schnellsten wenn man zuerst alle Zellen markiert, in denen dieser Wert erscheinen soll. Geben Sie dann die Zahl oder den Text ein (die Markierung bleibt dadurch erhalten) und bestätigen dies einfach mit der Tastenkombination Strg+Return.
Alle Zellen des zuvor markierten Bereichs werden nun mit den gleichen Daten gefüllt.
Ermitteln wie oft ein Wert vorkommt
Um zu ermitteln wie oft ein bestimmter Wert innerhalb einer Excel-Tabelle vorkommt, gibt es die ZÄHLENWENN Funktion. Mit dieser kann man sich ausgeben lassen wie oft die definierte Bedingung vorkommt.
Einfache Beispielformel ist zB =ZÄHLENWENN(A1:A20;8), mit der man zuerst den Bereich angibt in dem gesucht wird und dann die Bedingung/Suchkriterium festlegt die erfüllt werden soll. Also wie oft die 8 in diesem Bereich vorkommt. Anstelle der 8 könnte hier auch zB. Auto und anderer Text stehen. Genauso könnte der Text Auto auch schon in einer anderen Zelle stehen wie B1, dann gibt man einfach als Bedingung den Zellbezug B1 anstelle des Textes in die Formel ein.
Ein weiteres Beispiel =ZÄHLENWENN(A1:A20;“>8“). Hier wird dann als Ergebnis ausgeworfen wie oft Werte in dem Bereich größer als 8 sind.
Es ist auch möglich Ausgaben zu addieren mit der Formel =ZÄHLENWENN(A1:A18;4)+ZÄHLENWENN(A1:A18;6). Hier wird dann einfach die Anzahl von 4 und 6 addiert und das Ergebnis ausgegeben.
Tage zwischen zwei Datumsangaben berechnen
In Excel kann man leicht die Differenz zweier Datumswerte berechnen, um zu ermitteln wie viele Tage zwischen den beiden Datumsangaben liegen. Dazu nutzt man die einfache Formel zum subtrahieren, wie eben auch bei normalen Zahlenwerten wie zB. =B1-A1.
Erhält man nun aber anstelle der Anzahl der dazwischenliegenden Tage einen unsinnigen Wert, muss noch das Format der Ergebniszelle umgestellt werden. Dazu einfach die Zelle markieren und nach einen rechtsklick mit der Maus auf Zellen formatieren, im sich öffnenden Kontextmenü gehen. Dort dann im Register Zahlen die Einstellung Standard wählen, um den Wert korrekt angezeigt zu bekommen.
Excel will einige Zellen partout nicht als Zahlen erkennen
Excel hat manchmal das Problem den Inhalt von Zellen nicht als Zahlen zu erkennen, selbst nach Umstellung in das richtige Format.
Eine einfache Lösung für dieses Problem ist eine "1" in die Zwischenablage zu kopieren und danach die betreffenden Zellen zu markieren. Im Dialogfeld Inhalte einfügen (Kontextmenü mittels rechter Maustaste) wählt man dann als Vorgang Multiplizieren und bestätigt dies mit der OK Schaltfläche.
Excel multipliziert nun die Werte in den betroffenen Zellen mit der 1 aus dem Zwischenspeicher und schreibt das Ergebnis in die Zellen zurück.
Eine Anmerkung in diesem Fall, man sieht das man mit Inhalte einfügen auch Grundrechenarten ausführen kann mit Zahlen aus dem Zwischenspeicher, wodurch jedoch immer die Zahlen im markierten Bereich mit dem Ergebnis überschrieben werden!
Funktion um den höchsten und den zweithöchsten Wert zu ermitteln
Um den größten Wert in einem Tabellenbereich zu ermitteln, gibt es die einfache Funktion MAX. Die Funktion benötigt dazu nur die Angabe des Bereiches in dem gesucht werden soll. Dies sieht dann so aus: =MAX(A1:B10).
Um nun den zweit oder dritthöchsten Wert zu ermitteln nutzt man die Funktion KGRÖSSTE, welche aber nun neben dem Bereich eine zweites Argument benötigt. Der Aufbau sieht dann wie folgt aus: =KGRÖSSTE(A1:B10;2).
Auch hier wir der Bereich A1 bis B10 durchsucht, in diesem Fall nach dem zweithöchsten Wert was durch die 2 angegeben ist. Eine 3 ist dann der dritthöchste Wert usw. und eine 1 erfüllt dann die gleiche Funktion wie vorher MAX.
Inhalte von Zellen verbergen
Wenn man den Inhalt einer Zelle sowohl auf den Bildschirm als auch auf den Monitor verbergen möchte gibt es dafür verschiedene Möglichkeiten. Eine davon ist der Zelle ein entsprechendes Format zuzuweisen.
Dazu markiert man die Zelle und ruft über einen Rechtsklick mit der Maus den Befehl Zellen formatieren im Kontextmenü auf. In dem sich öffnenden Dialogfenster wählt man das Register Zahlen und dort dann die Kategorie Benutzerdefiniert aus. Mit der Eingabe von drei Semikolons (;;;) und anschließenden bestätigen mit der OK Schaltfläche, wird der Inhalt der Zelle nun nur noch in der Eingabezeile angezeigt.
Werte verschiedenen Zahlenbereichen zuordnen
(verschachtelte Wenn Funktion)
Hierfür gibt es mit dem Beispiel eines Kontos und der Zinsführung eine gute Vorlage. Wenn in der Zelle A1 ein Wert über 100 steht soll 1% in der Zelle B1 angezeigt werden, über 500 dann 2,5%, zwischen 5000 und 10000 dann 3% und alles über 10000 soll 4% ausgegeben werden.
Die Formel zur entsprechenden Ausgabe baut sich entsprechend auf:
=WENN(A1>10000;4;WENN(A1>5000;3;WENN(A1>500;2,5;WENN(A1>100;1;))))
Um die Verschachtelung leichter zu machen, fängt man mit dem größten Wert an. Sobald die Funktion feststellt das in A1 der Wert nicht über 10000, liegt wird mit der nächsten Wenn Funktion geprüft, ob der Wert zwischen 5001 und 10000 liegt. Dies geht solange weiter bis der Wert in A1 in einen der Bereiche liegt und dann der gewünschte Wert in B1 angezeigt wird.
Vereinfachtes Schema der Wenn Funktion: =WENN(Prüfung;Dann_Wert;Sonst_Wert)
Mit dem ersten Parameter (Prüfung) wird die Bedingung geprüft und mit positiv bzw. negativ bewertet. Der zweite Parameter (Dann_Wert) gibt den Wert aus bei einen positiven Ergebniss, während der dritte (Sonst_Wert) bei einen negativen Ergebniss ausgeworfen bzw. ausgeführt wird.
Formel Ausgabe
Bei einer Vorlage in der man Formeln festgelegt hat und erst später die Werte zur Berechnung einträgt, werden oft Ausgaben wie zb. bei einer Divison #DIV/0 angezeigt. Das folgende einfache Beispiel zeigt wie man mit der Wenn Funktion diese unterdrücken kann.
Die Formel steht im Beispiel in der Zelle C1: =WENN(B1="";"";WENN(B1=0;"";A1/B1))
Zuerst wird geprüft ob in der Zelle B1 kein Wert eingetragen ist, trifft dies nicht zu (also positiv) wird so angewiesen in C1 nichts einzutragen, dies erfolgt mit den beiden Anführungszeichen. Steht in B1 nun ein Wert (also negative Prüfung), kommt die zweite Wenn Funktion ins Spiel. Ist der Wert jedoch eine 0, wird ebenfalls mit der zweiten WENN Funktion nichts ausgegeben. Sobald nun aber ein Wert größer als 0 eingetragen wird (zweite negative Prüfung), erfolgt die Berechnung wo der Wert in Zelle A1 durch den Wert in B1 dividiert und das Ergebnis in C1 angezeigt wird.
Auswahlliste für Feldeingaben erzeugen
Möchte man in einer Zelle eine Auswahlliste mit verschiedenen Werten hinterlegen, die man beim anklicken der Zelle angeboten bekommt, geht man wie folgend vor.
In Excel ab Version 2007 findet man unter dem Ribbon Daten, den Befehl Datenüberprüfung. Nach dem anklicken öffnet sich ein Dialogfeld, in dem zuerst das Register Einstellungen interessant ist. Dort wählt man unter Zulassen die Option Liste aus und gibt die gewünschten Werte unter Quelle ein, in dem mehrere durch ein Semikolon getrennt werden. Auch gibt es die Möglichkeit einen Bezug zu einen Bereich mit den gewünschten Werten zu erstellen.
Formeln und Zellen schützen ohne Blattschutz
Es gibt Anwendungsfälle in Excel wo es nicht möglich ist Listen mit Formeln zu verlängern usw., solange der Blattschutz aktiv ist. Die folgende Lösung funktioniert ohne Blattschutz und es ist sogar möglich eine Meldung ausgeben zu lassen, die erscheint sobald versucht wird die Formel zu überschreiben. Es ist jedoch kein Problem die Zelle zu kopieren, die Kopie hat dann selbstverständlich den gleichen Schutz. Dazu geht man also wie folgend vor.
In Excel Version 2007, gibt es unter dem Ribbon Daten, den Befehl Datenüberprüfung. Nach dem anklicken öffnet sich ein Dialogfeld, in dem zuerst das Register Einstellungen interessant ist. Dort wählt man unter Zulassen die Option Textlänge aus und wählt unter Daten die Option zwischen. Darunter gibt man nun noch für Minimum und Maximum jeweils den Wert 0 ein. Unter dem Karteireiter Fehlermeldung ist es dann auch Möglich die Meldung einzugeben, welche angezeigt werden soll falls sich jemand an der Zelle zu schaffen macht.
Einen Nachteil hat diese Lösung jedoch. Die Formel bzw. Werte in der Zelle lassen sich zwar nicht überschreiben, aber immer noch mit der Entf Taste löschen!