Excel-Tipps

Überwachungsfenster nutzen

Beim Arbeiten mit mehreren Tabellen können Sie sich bestimmte ausgewählte Werte ständig in einem  Überwachungsfenster anzeigen lassen.
Dieses Fenster liegt ständig über allen Tabellen.

Überwachungsfenster hinzufügen:

Excel 2003 / XP:
Extras / Formelüberwachung / Überwachungsfenster

Excel 2007 oder 2010:
Register FORMELN / Formalüberwachung / Überwachungsfenster

 

Im sich öffnenden Fenster klicke man auf "Überwachung hinzufügen" und wähle die zu beobachtenden Zellen aus – am einfachsten, in dem man in der jeweiligen Tabelle die interessierenden Zellen markiert.
Im Überwachungsfenster können Zellen aus verschiedenen Tabellen nebeneinander angezeigt werden!

nach oben

Vorsicht beim Rückgaängig machen

Aus Computerwissen.de vom 20.3.2017

Mit Strg + Z macht man Arbeitsschritte, z. B. Fehleingaben oder Formatierungen Schritt für Schritt  rückgängig. Fährt man mit dem Befehl fort und hatte man mehrere Arbeitsmappen geöffnet, so schaltet der Rückgängig-Befehl in die nächste Mappe um und macht dort Aktionen rückgängig – eventuell auch nicht erwünschte!

Fenster einfrieren oder fixieren

Wenn man beim Bildschirm-Scrollen in größeren Tabellen die obere(n) Zeile(n) (i. a. die Spaltenüberschriften) oder die linke(n) Spalte(n) (Zeilenköpfe) immer sehen möchte kann man im Menüband "Ansicht" den Befehl "Fenster einfrieren" benutzen. In Version Excel2010 scheint allerdings ein Entwurfsfehler vorzuliegen. Der Text "Fenster einfrieren" wird für zwei Schaltflächen angeboten, die linke davon hieß in Version 2007 "Zoommodus auswählen" und stellt den ausgewählten Tabellenbereich größtmöglich (maximal 400% Zoom) dar. Diese Funktion wird auch bei Version 2010 ausgeführt. In Version Excel 2013 hat man den Fehler abestellt – dort heißt die Schaltfläche "Auswahl vergrößern".

Beim Befehl "Fenster fixieren" oder "Fenster einfrieren" aktiviert man vor der Befehlsanwahl die Zelle, die direkt unter und

nach oben

Formel ohne Anpassung kopieren

Tipp von experto.de vom 13.2.2017

 Oft möchte man eine Formel in die daunterliegende Zelle ohne Bezugsanpassung kopieren, d.h. die automatische Anpassung der Zelladresse stört:
Man stellt sich in die Zelle unterhalb der Formel, drückt die Kombination Strg und Komma. Nach Enter ist die Formel übernommen.

nach oben

Kursorbewegen innerhalb einer Markierung

Auf Anregung von SmartTools Publishing <excel-weekly-text-reply@smarttools.de> vom 5.8.2014

Hat man einen Tabellenbereich markiert, so kann man den Kursor nicht auf eine spezielle Zelle des Bereichs stellen, ohne die Markierung wieder zu verlieren.

Durch die Tastenkombination STRG + ' werden nacheinander die vier Ecken des markierten Bereichs angesprungen, ohne die Markierung zu verlieren.

nach oben

Schnelldiagramm

Wenn Sie einen Tabellenbereich markieren und dann F11 drücken, erstellt Excel 2016/2013/2010/2007 das Standarddiagramm in einem separaten Diagrammblatt. Wenn Sie dagegen Alt+F1 drücken, legt Excel das Diagramm als Objekt in dem Tabellenblatt mit den markierten Zellen an.

Summe per Tastendruck

aus SmartTools Office XP Insider 28/2016:

Zum Bilden einer Zeilen- oder Spaltensumme wird üblicherweise das Sigma-Summensymbol angeklickt, welches die Funktion SUMME aufruft und zugleich den vom Programm erkannten zu summierenden Zahlenbereich mit einem Rahmen kennzeichnet. Den Rahmen korrigiert man ggf. und nach Enter erhält man den Summenwert.

Das Gleiche kann mit dem Tastencode Alt+Umschalt+0 erreicht werden. Hat man mehrere Zellen unter bzw. neben einem Zahlenblock markiert, so werden durch dien Tastencode automatisch mehrere Spalten- bzw. Zeilensummen erzeugt.


Diagramm von Daten lösen

Aus www.computerwissen.de/ vom 26. Juni  2016

Normalerweise ist ein in Excel  erstelltes Diagramm immer mit den Daten verknüpft, eine Datenänderung ruft auch eine Diagrammänderung hervor. Will man diese Abhängigkeit aufheben so wirken folgende Schritte:

1. Anwählen einer Datenreihe im Diagramm
2. Die Bearbeitungszeile zeigt “=Datenreihe(…..)“ an
3. Diese Zeile ist per Maus zu markieren
4. Taste F9
5. Enter

löst die Verbindung zwischen Diagramm und Daten.

Arrayformel ändern

Bei der Nutzung von Arrays wird eine einzige Formel gleichzeitig in mehrere Zellen eingetragen und simultan ausgewertet.

Ein einfaches Beispiel:
Man hat für Klausuren anhand von Punktwerten Noten vergeben und möchte den Notenspiegel ermitteln; die Funktion ZÄHLENWENN kann für jede Note einzeln abgerufen oder anhand des Notenfelds 1 .. 6 können die sechs Ergebniswerte mit einer Arrayfunktion bestimmt werden.
Wichtig! – die Arrayfunktion muss mit Strg + Shift + Enter beendet werden – im Ergebnis sind die Funktionswerte in geschweifte Klammern eingeschlossen.

Will man die Matrixformal ändern, muss zuvor wieder den Matrixbereich markiert werden: F5 (Gehe zu), Inhalte / Aktuelles Array oder noch einfacher Strg / , die Änderung vornehmen und mit Strg + Shift + Enter beenden.

nach oben

Spaltenweise sortieren

Auf Anregung von Excel-Praxistipps [excel@mail.computerwissen.de]Die "normale" Sortierung in Excel erfolgt zeilenweise. Aber auch spaltenweise kann Excel durch Optionseinstellung sortieren. 

 

A

B

C

D

E

F

1

Gehalt

1800

3200

2750

3655

1500

2

Name

Laube

Emsig

Bastian

Kraus

Gerbig

3

Abteilung

Azubi

EK

VK

Control

Azubi

Die Liste möge nach dem Gehalt aufsteigend sortiert werden, das heißt links in Spalte B soll das niedrigste und rechts in Spalte F das höchste Gehalt stehen!

  • Markieren des Wertebereichs ohne die Überschriften, d.h. B1:F3.
  • Menübefehl "Daten/Sortieren/Optionen/Spalten sortieren.
  • Unter "Sortieren nach" wähle man die Sortierzeile – hier die Zeile 1 und bestätige mit OK

 

A

B

C

D

E

F

1

Gehalt

1500

1800

2750

3200

3655

2

Name

Gerbig

Laube

Bastian

Emsig

Kraus

3

Abteilung

Azubi

Azubi

VK

EK

Control

Die Liste ist nun nach dem Gehalt geordnet! Man sollte abschlie0end die Option wieder auf die "normale" zeilenweise Sortierung zurücksetzen, da sonst die Spaltenoption eingeschaltet bliebt.

Der Umweg über Transponieren, zeilenweise sortieren und wieder zurücktransponieren sollte nach dieser einfachen Lösung nicht mehr gewählt werden!

 

 

Ein- und Ausblenden von Formeln

Wer viel mit Formeln in Excel arbeitet, für den ist es sehr praktisch, schnell alle Formeln ein- und wieder ausblenden zu können.
Mit der Tastenkombination STRG + # macht man in den Excel-Versionen 95 bis 2007 sofort alle Formeln sichtbar.
Um wieder auf die Formelergebnisse zurückzuschalten, nutzt man erneut STRG + # als Tastenkombination.

In der Version 2010 von Excel funktioniert STRG + # leider nicht mehr wie gewohnt.
Verwenden Sie STRG + # und stehen mit dem Zellcursor auf einer Zelle mit einem Wert, wird dieser in ein Datum umformatiert.
Hier hilft nur das Umformatieren oder ein sofortiges Rückgängigmachen der ungewünschten Datumsformatierung.
Stehen Sie auf einer leeren Zelle bewirkt die Tastenkombination STRG + # in der Version 2010 nichts.

 Als Alternative können Sie in der Version 2010 von Excel die Tastenkombination ALT + M + O verwenden!
Drücken Sie hierzu die Tasten ALT + M und dann O nacheinander.
ALT + M springt sofort auf das Register "Formeln" und zeigt Buchstaben bei den Befehlen an. Der Buchstabe O steht dann für das Ein- und Ausblenden von Formeln. Auch diese bewirken ein Einblenden und Ausblenden der Formeldarstellung in den Zellen.

nach oben

Tabellen ohne Formeln übergeben

(Anregung von SamrtTools Office XP Insider vom 9.1.2014)

Man hat in einer Tabelle die Zellen geeignet formatiert, Hintergrund, Spaltenbreite usw. gewählt, möchte aber die Tabelle ohne die zugrunde liegenden Formeln, d.h. nur mit den sich ergebenden Werten weitergeben/veröffentlichen. Die gewünschte Tabellenkopie kann man leicht über den Befehl „Inhalte einfügen“ erzeugen:

  • Markieren des Tabellenbereichs und mit Strg C in die Zwischenablage kopieren,
  • Zieltabellenblatt wählen,
  • Im Menüband START in Gruppe Zwischenablage den Befehl Einfügen/Inhalte einfügen/Werte wählen,
  • unmittelbar danach Einfügen/Inhalte einfügen/Formate wählen und im letzten Schritt
  • Einfügen/Inhalte einfügen/Spaltenbreite auswählen.

Danach sollte die Ergebnistabelle wie die Ausgangstabelle aussehen, aber mit Werten anstelle von Formelergebnissen.

 

 

Formelzellen schützen

Zellen, die Formeln enthalten sollen vor Überschreiben geschützt werden, sonstige Wertezellen sollen aber für Änderungen/Eingaben zugänglich bleiben. Dazu sind folgende Schritte nötig:

  • Im ausgewählten Tabellenblatt werden mit STRG-A alle Zellen markiert.
  • Mit STRG+1 wird das Dialogfenster ZELLEN FORMATIEREN aufgerufen, im Register SCHUTZ entferne man das Häkchen bei Gesperrt.
  • Nach F5 (Gehe Zu) wähle man INHALTE und dort speziell FORMELN: Es werden alle Zellen markiert, die Formeln enthalten.
  • Mit Strg+1 setzt man nun im Register SCHUTZ das Häkchen bei Gesperrt.
  • Unter START-ZELLEN-FORMAT kann man nun Blatt schützen anwählen. Das gleiche erreicht man mit ÜBERPRÜFEN-Blatt schützen. In Version XP/2003 wird der Blattschutz über EXTRAS-SCHUTZ-Blattschutz aktiviert.

Wenn man jetzt versucht, eine Formelzelle zu ändern, so zeigt Excel eine Fehlermeldung an.

nach oben

Anzeige von Programmversion und Dateiname samt Pfad

Die interne Zählung der Programmversionen von Microsoft läuft nicht ganz synchron mit der Zählung der Office-Versionen. So hat Excel 2003 die interne Versionsnummer 11.0, Excel 2007 die 12.0 und Office 2010 die Version 14.0 - ob die Version 13 aus Aberglauben ausgelassen wurde???

Die Funktion =INFO("version") liefert Ihnen diese interne Versionsnummer, bei Excel 2007 eben z. B. "12.0"

Zur Anzeige Ihrer Office-Version nutzen Sie die Funktion

="Excel " & WAHL(WECHSELN(INFO("version");".";",")-6;"95";"97"; "2000";"XP";"2003";"2007";"";"2010")

Die Funktion Wechseln (xxx; ".": ".") ersetzt den Punkt in '12.0' durch ein Komma, macht also die Zahl 12,0 daraus.

Die Funktion WAHL(Index n; Wertliste) sucht nun das n-te Argument in der Wertliste – hier das (12-6)-te – der Wert -6 wird vom Index abgezogen und erspart die Angabe von 6 Leerargumenten.

 

Die Funktion

=ZELLE("Dateiname")

liefert als Ergebnis den Dateinamen UND den Pfad zum Speicherort.

nach oben

Erweiterbare Dropdownlisten

Problemsituation: Bei der Anmeldung zum Firmenlauf war man gehalten, sich einem bereits gemeldeten Team anzuschließen oder ein neues Team als Teamleiter zu eröffnen.

Mit der Gültigkeitsfunktion von Excel kann man zwar sehr schnell einen Eintrag aus einer vorhandenen Liste (fehlerfrei) übernehmen, der Zweck der Gültigkeitsprüfung verbietet alerdings die Eingabe neuer Werte. Die Funktion "Auswahlliste" erlaubt es nun, beide Wünsche zu erfüllen.

Steht man in einer Spalte mit Textwerten unmittelbar unter dem letzten Eintrag, so erhält man mit der Tastenkombination "Alt + Cursor nach unten" die Liste aller darüber stehenden Werte zur Auswahl. Man kann nun einen Wert übernehmen oder einen neuen Wert ergänzen. Der neue Wert steht in der Folgezelle auch mit zur Auswahl.

Das Verfahren klappt nur bei Textwerten und direkt unter dem letzten Listeneintrag, aber in allen Excelversionen.

Zahlen in Exponentialdarstellung anordnen

Hat man es mit Messdaten wissenschaftlicher Ergebnisse zu tun, kann es passieren, dass sehr große und sehr kleine Zahlen aufeinandertreffen. Zur Darstellung bietet sich da die Exponentialdarstellung mit Mantisse und Exponent an - wenn Excel nicht schon selbst diese Darstellung gewählt hat.

Durch ein benutzerdefiniertes Format kann man auch diese Anzeigeform beeinflussen. Mit dem Format

0,000 * E+00

erreicht man, dass die Zahlen mit einer Stelle vor dem Komma, drei Nachkommastellen und der zweistelligen Exponentenangabe nach dem E dargestellt werden. Der Stern und das folgende Leerzeichen bewirken, dass zwischen Mantisse und Exponentialteil der durch die Spaltenbreite bestimmte Freiraum durch Leerzeichen gefüllt wird und die Werte damit übersichtlich dargestellt werden.

nach oben

Vormonat bestimmen

Zu einem beliebigen Datum soll jeweils die Nummer des Vormonats berechnet werden, zu einem Aprildatum soll "3" geliefert werden, zu einem Januardatum eine "12".Wenn das aktuelle Datum in Zelle A2 steht, so liefert die Formel

=MONAT(A2- TAG(A2)) die gewünschte Zahl.

 

 

nach oben

Brüche eingeben und mit ihnen rechnen

Gibt man einen normalen Bruch in eine Zelle ein, so wird das von Excel immer als Datum interpretiert , das führt zu einer Datumsanzeige und einem damit verbundenen völlig anderen Zahlenwert. Die Eingabe ⅔ wird als 2. März 2012 (das aktuelle Jahr) interpretiert mit dem internen Zahlenwert 40604.

Gibt man vor dem Bruch die Ziffer 0 und ein Leerzeichen ein, so zeigt Excel den gewünschten Bruch an und rechnet intern mit dem Zahlenwert des Bruches in der größtmöglichen Excelgenauigkeit.
Die Eingabe 0 2/7 führt zur Anzeige in der Zelle 2/7 und zum Zahlenwert in der Bearbeitungszeile von 0,285714285714286. Multipliziert man den Zellenwert mit 7, so erhält man korrekt den Wert 2. Das Ergebnis von Rechenoperationen wird nun als Bruch angezeigt

– zur Festlegung der Anzeige siehe Bruchzahlenanzeige

nach oben

Anzeige von Bruchzahlen in Excel

Excel kann einen Zahlenwert auch als Bruchzahl anzeigen. Dazu ist ein passendes Textformat einzusetzen. Allerdings können Nenner bzw. Zähler auf diese Weise nur bis maximal drei Stellen angeben werden. Bei Bruchwerten größer 1 wird der ganzzahlige Anteil separat dargestellt (1,25 wird als 1 ¼ und nicht als 5/4 dargestellt).

Diese Beschränkungen können durch ein benutzerdefiniertes Format wie ####/#### oder ????/???? umgangen werden.
Die Anzahl der verwendeten # bzw. ? legt fest, wie viele Ziffern maximal dargestellt werden sollen. Das #-Zeichen steht fuer optionale Stellen, das heisst, dass nicht benoetigte Stellen auch nicht angezeigt werden. Weitere Alternativ sorgt das Fragezeichen dafuer, dass für nicht signifikante Nullen Leerzeichen eingefügt werden. Das ist hilfreich, wenn Sie mehrere Zahlenverhaeltnisse mit unterschiedlichen Ziffernanzahlen untereinander in eine Tabellenspalte schreiben und die Werte uebersichtlich am Schraegstrich ausrichten wollen, d.h. der Bruchstrich (der Schraegstrich) immer an derselben Position innerhalb der Zellen steht.

Beispiele:

Zahlenwert

Format

Anzeige

1,33

# ?/5

1   2/5

1,33

# ?/?

 1/3

1,33

#?/?

4/3

1,33

??/12

16/12

1,33

???/???

133/100

1,33

??/??

4/3   

1,25

??/??

5/4   

1,33

##/##

4/3

1,15

??/??

23/20

 

 

nach oben

Vorsicht vor dem Tausenderpunkt und Prozentzeichen

Weist man einer Zahl in Excel das Prozentformat zu, so wird die Zahl mit Hundert multipliziert und das Prozentzeichen an die Zahl angefügt – angezeigt sowohl in der Zelle als auch in der Bearbeitungszeile. Löscht man in der Bearbeitungszeile das Prozentzeichen weg, so wird die Zahl nochmals mit 100 multipliziert und wieder ein Prozentzeichen angefügt. Eine Fehldeutung sollte damit ausgeschlossen sein. Anders mit dem „Tausenderpunkt“:

In der (benutzerdefinierten)  Formatschablone #.### führt der Punkt zur Gruppierung der Zahl in Dreierblöcke: "12345678 in der Bearbeitungszeile wird als 12.345.678  in der Zelle angezeigt

Die Formatschablone #.###. hingegen führt zur Anzeige 12.345, d.h. die Zahl wird durch Tausend geteilt, #.###.. sogar zu 12 ohne einen Hinweis. Man kann diese Funktion des Tausenderpunktes nutzen, um große Zahlen übersichtlicher darzustellen, sollte aber zur Sicherheit in der Formatangabe einen Hinweis der Art #.### ." Tsd."  oder #.###.. " Mio" ergänzen. Besonders wichtig ist der Zusatz, wenn man Kommastellen erwartet:  Die Formatschablone 0,00.. führt bei 12345678 zur Anzeige 12,35  !

Zahlen in mehrere Excel- Zellen zugleich eingeben

Eine Zahl oder ein Wert soll in eine Reihe von Zellen eingegeben werden. Ein Weg dazu ist es, den Inhalt in ene Zelle einzugeben und anschließend in die anderen Zellen zu kopieren.

Ein schnellerer Weg ist der Folgende:

  • Man markiert den zu füllenden Bereich bzw. die Zellen - falls Bereich nicht zusammenhängend ist, die Strg-Taste beim Markieren drücken.
  • Wert über Tastatur eingeben und mit Strg + Enter abschließen! Sofort füllt Excel den Bereich mit dem von Ihnen eingegebenen Inhalt.
    Alle Zellen erhalten den gleichen Inhalt.

nach oben

Spinnennetze in Excel

Excel bietet eine Vielzahl von Diagrammtypen zur Veranschaulichung von Datenreihen an. Ein wenig bekannter Typ ist das Netzdiagramm, das es erlaubt, mehrere Datenreihen gut vergleichbar darzustellen. Besonders gut geeignet ist es für quasizyklische Datenreihen.

Bei einem Netzdiagramm werden mehrere Achsen von einem Mittelpunkt nach außen gezogen. Jede Datenreihe wird durch eine Linie dargestellt. Je weiter weg vom Mittelpunkt der Datenpunkt liegt, desto größer ist der Wert der Reihe für diese Achse.
Hat man mehrere Datenreihen, so ergibt sich eine spinnennetzähnliche Struktur.

Man markiere die darzustellenden Daten und wähle Einfügen / Diagramm / Netze / Netz mit Datenpunkten. In Excel 2010 haben Sie evtl. den Zwischenschritt "Weitere".

Zur besseren Übersichtlichkeit kann man die Gitternetzlinien ausblenden (dazu Gitternetz anklicken und Entf-Taste oder rechte Maustaste – Kontextmenü "Löschen"),
evtl.  die Linienstärke erhöhen und/ oder besser unterscheidbare Linienfarben wählen.

nach oben

Tastenkombinationen in Excel

Über Tastenkombinationen können Sie Zellen in Ihren Excel-Tabellen schnell und einfach formatieren.
Markieren Sie zuerst eine einzelne Zelle oder einen Zellbereich und setzen Sie anschließend eine der folgenden Tastenkombinationen zum Ein-/Ausschalten ein:

    • STRG+2 = Fett                   oder Strg+Shift+F 
    • STRG+3 = Kursiv                oder Strg+Shift+K
    • STRG+4 = Unterstrichen     oder Strg+Shift+U
    • STRG+5 = Durchgestrichen
    • STRG+PUNKT = Einfügen des aktuellen Datums
    • STRG+UMSCHALT+PUNKT = Einfügen der aktuellen Zeit
    • STRG+UMSCHALT+LEERTASTE = Markierung aller Zellen eines zusammenhängenden Bereichs
    • STRG+POS1 = bewegt Zellmarkierung zum Anfang des aktuellen Tabellenblatts
    • STRG+ENDE = bewegt Zellmarkierung zum Ende des aktuellen Tabellenblatts
    • STRG+A = Markierung aller Zellen im aktiven Tabellenblatt
    • STRG+1 = Aufruf des Dialogfensters „Zellen formatieren“
    • STRG+N = Einfügen einer neuen Arbeitsmappe
    • STRG+P = Drucken des aktiven Tabellenblatts
    • UMSCHALT+F11 = Einfügen eines neuen Tabellenblatts in die aktuelle Arbeitsmappe
    • STRG+F6 = Sprung zur nächsten geöffneten Arbeitsmappe
    • Strg+Shift+1  Zahl mit zwei Dezimalstellen
    • Strg+Shift+2  Zahl in Exponentialdarstellung
    • Strg+Shift+3  Zahl in Datumsanzeige
    • Strg+Shift+4  Zahl mit Währungsangabe
    • Strg+Shift+5  Zahl in Prozentangabe
    • Strg+Shift+6  Zahl ggf. ohne Dezimalzahlen
    • STRG+9: Markierte Zeilen ausblenden.
    • STRG+UMSCHALT+9: Ausgeblendete Zeilen in der Markierung einblenden.
    • STRG+8: Markierte Spalten ausblenden.
    • STRG+UMSCHALT+8: Ausgeblendete Spalten in der Markierung einblenden.
    • Umschalt+F3: Dialogfenster "Funktion einfügen" aufrufen
    • Während der Formeleingabe mit Strg+A Funktionsargumente anzeigen

nach oben

Zeilen/Spalten mit Tastenkombination markieren, löschen oder einfügen

Um eine Zeile oder Spalte zu markieren, klickt man im Allgemeinen mit der linken Maustaste auf den Zeilen- oder Spaltenkopf.
Schneller geht es mit der Tastatur!

Wenn eine bestimmte Zelle aktiv ist, so wird die zugehörige Spalte durch
Strg Leertaste
, die zugehörige Zeile durch Shift Leertaste markiert.

Mit der Tastenkombination STRG – löscht man die markierte Zeile bzw. Spalte, die anderen Zeilen bzw. Spalten rücken heran.

Die Tastenkombination STRG + fügt eine Zeile bzw. Spalte vor der markierten Zeile bzw. Spalte ein, die anderen Zeilen/Spalten rücken nach unten bzw.rechts.

Mit Strg + Shift + Cursortaste wird ausgehend von der aktuellen Zelle die Markierung bis zum Ender der ausgefüllten Zeile/Spalte nach oben/unten bzw. rechts/links erweitert.

nach oben

Einfügen von Datum und Uhrzeit in Excel

Da der Computer Datum und Uhrzeit kennt, kann man diese auch in Excel aufrufen.
Dazu nutzt man die Funktionen HEUTE() und JETZT(), die den internen Zahlenwert des aktuellen Datums ohne bzw. mit Dezimalstellen liefern. In der Anzeige wird man für HEUTE ein Datumsformat wünschen, bei JETZT könnte man Datum und Uhrzeit oder nur die Uhrzeit anzeigen.

Die Funktionen liefern eine "lebende" Anzeige, bei jedem Neuaufruf der Arbeitsmappe und bei jeder Aktualisierung mit F9 wird die Anzeige auf die aktuelle Zeit korrigiert.
Für das Einfügen des festen Momentanwertes stehen die folgenden Tastenkombinationen zur Verfügung:

  • Strg und Punkt liefert das aktuelle Datum
  • Strg und Doppelpunkt liefert die aktuelle Uhrzeit

nach oben

Zeitangaben in Excel auf nächste Viertelstunde runden

In Excel ist es möglich, Zeitangaben auf die nächste Viertelstunde zu runden. Dabei soll z.B. aus "11:06" "11:00" werden, aus "11:11" hingegen "11:15".

Zeitabgaben werden in Excel als Bruchteile von 1 dargestellt - 1 entspricht dabei 24 Stunden. Ein Tag hat folglich 96 Viertelstunden. Im o.g. Beispiel ist der Zeitwert von "11:06" 0,46, in Viertelstunden 44,4. Dieser Wert kann mit der Funktion RUNDEN auf die nächste ganze Zahl gerundet werden (zweiter Parameter 0), also 44. Teilt man den gerundeten Wert durch 96 und zeigt ihn im Uhrzeit-Format an, so wird der Wert "11:00" ausgegeben. Der Zeitwert in Zelle A1 auf die nächste Viertelstunde gerundet wird also durch folgende Funktion dargestellt:

  • =RUNDEN(A1*96;0)/96

Falls Excel daraufhin einen Dezimalwert anstelle einer Zeitangabe anzeigt, ist der Formelzelle noch ein geeignetes Uhrzeitformat zuzuweisen. Analog wird mit der Funktion ABRUNDEN immer auf die nächste Viertelstunde abgerundet, mit AUFRUNDEN auf die nächste Viertelstunde aufgerundet.

nach oben

Autosumme per Tastendruck einfügen

Gut bekannt ist die Funktion der Autosumme (des großen Sigma) - im Menüband ab Excel 2007 im Register Start oder Formeln bzw. im Menü von Excel 2003. Wenig bekannt ist, dass sich die Autosumme auch über einen Tastencode einfügen lässt:

Steht die Markierung unter oder neben den zu summierenden Zahlen, so wird mit "Alt und =" die Funktion "=SUMME" eingefügt und der zu summierende Bereich markiert und als Funktionsargument angeboten - mit Enter bestätigt man die Funktion. Es können auch mehrere Summenformeln mit einem Schritt berechnet werden: Hat man Zellen unter bzw. neben mehreren Zahlenblöcke markiert, so ermittelt Excel automatisch die richtigen Bereiche und fügt in jede markierte Zelle die passende Summenformal ein.

Tipp von SmartTools Excel Weekly vom 4.6.2013

Tabellen mit Format und Formeln automatisch erweitern

Excel bietet die Möglichkeit, Formate und Formeln in einer Tabelle für solche Daten automatisch zu übernehmen, die unmittelbar unterhalb einer bestehenden Liste eingegeben werden. Dabei entsprechen die neuen Daten anschließend der Struktur der darüber stehenden Liste.
Formeln, die sich in jeder Zeile wiederholen, werden automatisch kopiert.
Solche Erweiterungen sind nur möglich, wenn Formate und Formeln in mindestens vier Listenzeilen vor der neuen Zeile auftreten.

Diese Funktion kann wie folgt aktiviert bzw. deaktiviert werden:


Excel 2003: Extras/ Optionen/ Bearbeiten/ Datenbereich und Formeln erweitern,

Excel 2007/ 2010: Office-Schaltfläche oder Datei/ Excel-Optionen /Erweitert/ Bearbeitungsoptionen Datenbereichsformate und -formeln erweitern.

nach oben

Verwenden der Funktion SUMMEWENN

Um Werte eines Bereichs in Abhängigkeit von einer Bedingung zu summieren, nutzt man die Funktion SUMMEWENN: SUMMEWENN (Kriterienbereich; Kriterium; Wertebereich)

Ist das Kriterium ein fester Wert, so kann man ihn unmittelbar in die Funktion eintragen.
Beispiel: In zwei Gruppen G1 und G2 werden Arbeiten mit Punkten zwischen 0 und 100 bewertet. In Spalte A steht die Gruppenzugehörigkeit eines Prüfungsteilnehmers, in Spalte B die erreichten Punkte.

Interessiert man sich für die Summe der in Gruppe G1 vergebenen Punkte, so lautet die Formel SUMMEWENN(A2:A12; "=G1"; B2:B12) Die Punktsumme aller bestandenen Prüfungen, d.h. mit Punkten ab 50, bestimmt sich mit aus SUMMEWENN(B2:B12; ">=50")

Steht jedoch der Grenzwert in einer anderen Tabellenzelle, so muss der Vergleichsoperator mit der Zelladresse mittels & verbunden werden. Ist also der Punktegrenzwert in Zelle C4 abgespeichert, so berechnet man die Punktsumme der bestandene Prüfungen mit SUMMEWENN (B2:B12; ">=" & C4)

Im Bild sind weitere Beispiele dargestellt: wie Umsatzwerte in Abhängigkeit von einem festen Grenzwert bzw. dem Wert in einer Zelle summiert werden, wie die Summe in Abhängigkeit vom Anfangsbuchstaben des Monatsnamens bestimmt wird, wie die Summe auch abhängig von einem Funktionswert in einer Zelle gebildet werden kann.
Außerdem wird gezeigt, dass die Verknüpfung des Vergleichsoperators mit einer Zelladresse mittels & auch für die Funktion ZÄHLENWENN gilt.

nach oben

Unikate oder Duplikate suchen und hervorheben

In gewissen Situationen möchte man in Excel-Tabellen auf mehrfach auftretende Werte hingewiesen werden. Ebenso kann Interesse an nur einzeln auftretenden Werten bestehen.

Für diese Fälle verfügt Excel über die Funktion: ZÄHLENWENN (Bereich; Testwert).
Im Bild ist die Wirkung der Funktion durch die sich ergebenden Wahrheitswerte ausgewiesen.

Zudem können alle Unikate oder Duplikate durch Verwendung der bedingten Formatierung farblich hervorgehoben werden.
Dazu sind folgende Schritte auszuführen:

  • Markierung des zu prüfenden Bereichs.
  • Excel bis Version 2003: FORMAT/ Bedingte Formatierung/ unter Bedingung wählen "Formel ist",
    Excel ab Version 2007: Start/ Bedingte Formatierung/ Regeln zum Hervorheben/ Weitere Regeln/ "Formeln zur Ermittlung…".
  • Als Formel wird in Zelle A2 zur Unikatsuche eingetragen: ZÄHLENWENN(A$2:A$12;A2)=1. (A2 : A12 ist im Bild der Testbereich und muss natürlich an die Gegebenheiten angepasst werden.)
  • Auswahl einer Musterfarbe zur Hervorhebung. In Excel ab Version 2007 ist bereits ein Schaltfeld zur Hervorhebung von doppelten bzw. eindeutigen Werten vorhaben, so dass für diesen Selektionsfall die Eingabe der Formel vorweg genommen ist.

 

Ähnlich kann vorgegangen werden, um bei der Eingabe von Werten keine Duplikate zuzulassen:

  • Markierung des (evtl. teilweise bereits gefüllten) Eingabebereichs.
  • Excel bis Version 2003: Daten/ Gültigkeit/ Benutzerdefiniert,
    Excel ab Version 2007: Daten/ Datenüberprüfung/ Benutzerdefiniert.
  • Als Formel wird eingetragen: ZÄHLENWENN(A$2:A$30;A2)=1. Damit werden doppelte Eingabewerte abgewiesen. Bitte beachten Sie, dass der Bereichsstartwert und der Testwert (im Beispiel A2) gleich sein müssen.

nach oben

Vorsicht vor dem Format TEXT!

Sie haben in eine Zelle Text eingefügt – er erscheint linksbündig und ist damit von Excel richtig als Text gedeutet worden. Zusätzlich haben Sie noch das Format Text zugewiesen.
Beim einfachen Bezug auf den Text wird der Zielzelle ebenfalls das Format Text zugewiesen (Im Bild Zeile 4), ebenso beim Verketten von zwei Textfeldern a2&a3 oder von Teilen der Texte Links(a2;3) & Links(a3;6) (Zeilen 7 und 8). Die Ergebnisse der Anwendung der Textfunktionen LINKS oder TEIL liefern jedoch das Zellenformat Standard !! ?? Ebenso überraschend ist das Ergebnis in Zeile 11, wo lediglich Leerzeichen zwischen die Textteile eingefügt wurden.

Ein Problem entsteht, wenn man in einer als Text formatierte Zelle eine Formel einträgt: Sie wird als Text gedeutet und auch so angezeigt aber nicht ausgewertet:  = 3*7 wird eben nicht zu einer 21 sondern bleibt als =3*7 stehen. Das Zuweisen des Zellenformats Standard ändert daran noch nichts! Erst die erneute Bearbeitung über die Funktionstaste F2 und Enter führt zum erwarteten Ergebnis und einer mitrechnenden Excelzelle im Format Standard!

Löschen des Zellinhalts ändert nicht das Zellformat – dazu ist explizit das Format zu löschen!
Die Funktionen ISTTEXT bzw. ISTKTEXT werten den Inhalt von Zellen aus, nicht ihre Formatierung! Es ist also zu unterscheiden zwischen Datentyp und zugewiesenem Format.
Die Falle schlägt in allen Excel-Versionen XP, 2007 und 2010 zu!

Zahlenformatierung auf Tastendruck

Bei der Arbeit mit Excel müssen Sie Zellen immer wieder Zahlenformate zuweisen. Ihnen stehen dafür einige Symbole für die Zuweisung per Mausklick zur Verfügung.
Für die wichtigsten Formate gibt es auch einige sehr nützliche Tastenkombinationen. Ausgangspunkt sind dabei die Zifferntasten "1" bis "6" am oberen Rand der Tastatur.

Wenn Sie Strg+Umschalt zusammen mit einer dieser Tasten drücken, weist Excel den markierten Zellen eines der folgenden Formate zu:

Strg+Umschalt+1:Tausendertrennzeichen und 2 Dezimalstellen1.234,00
Strg+Umschalt+2:Exponentialformat mit 2 Dezimalstellen123E+03
Strg+Umschalt+3:Datumsformat mit Tag, Monat und Jahr
Strg+Umschalt+4:Währungsformat mit 2 Dezimalstellen18.05.1903
Strg+Umschalt+5:Prozentformat123400%
Strg+Umschalt+6:Standard-Zahlenformat1234

Zumindest bei Währungs- und Prozentformaten können die Zeichen auf den entsprechenden Tasten über den Ziffern ($ und %) eine kleine Gedankenstütze sein. Beim Datumsformat kann man zwar in ein Zahlenformat wandeln, das dann den internen Zählwert des Tages ausweist, aber eine Rückumwandlung in ein Datum funktioniert über die Tastenkombination nicht in allen Excel-Versionen erfolgreich. In Excel 2010 wirkt infolge eines Fehlers die Datumsumwandlung nicht – dafür aber Strg+#.

nach oben

Formel direkt dokumentieren

Kommentare in Ihren Formeln sind wichtig, wenn Sie auch nach einiger Zeit noch nachvollziehen möchten, was Sie sich bei bestimmten Berechnungen gedacht haben.
Sie sind erst recht unverzichtbar, um Kalkulationen und Vorgaben so zu dokumentieren, dass Kollegen und Partner sie nachvollziehen können.

Neben der Möglichkeit, Kommentare zu einzelnen Zellen anzulegen, können Sie Ihre Formeln auch direkt in der Formel kommentieren.
Dazu setzen Sie die Funktion N ein. Dieser Funktion übergeben Sie einen Text Ihrer Wahl. Sie liefert dazu den Wert 0, hat somit keinen Effekt, sondern dient nur dazu, den Kommentar in der Formel unterzubringen. Die folgende Abbildung zeigt ein Beispiel für den Einsatz der Funktion in der Praxis: Praktisch an der N-Funktion ist die Tatsache, dass Sie die Funktion auch mehrfach in einer Formel verwenden können. Hängen Sie einen N-Ausdruck einfach per Pluszeichen an eine Formel an, wie in der folgenden Anweisung: =SUMME(A1:A100)+N("Die erste Summe ist die Basis")+SUMME(B2:B10)+N("Hinzu kommen variable Kosten")

nach oben

Wie bildet man in Excel Zeitsummen über 24 Stunden?

Hat man Zeitangaben zu addieren, so hat man bei Summen über 24 Stunden evtl. den Eindruck, dass Excel falsch rechnet.
Excel stellt Datums- und Zeitangaben intern als Dezimalzahlen dar. Der gebrochene Teil wird dabei als Uhrzeit, der ganze Teil als Tagesangabe gedeutet.

Bei der Summierung von Uhrzeiten wird der ganze Teil zunächst ignoriert (erste Spalte – es ergibt sich als Zahlenwert 1,28 – die 0,28 steht für 6.40 Uhr).
Durch die Angabe des benutzerdefinierten Formats [hh]:mm werden die Stunden wie gewünscht angezeigt:

Formathh:mm[hh]:mm
08:1008:10
08:3008:30
09:4509:45
04:1504:15
Summe06:4030:40
Zahlenwert1,28

nach oben

Umrechnung großer Zeitwerte in Minuten

Um Zeitsummen von über 24 Stunden korrekt anzuzeigen, muss man das benutzerdefinierte Format [hh]:mm verwenden.

Die Funktion STUNDE liefert immer nur den Stundenanteil der Nachkommastellen und ist daher nicht geeignet, wenn der Wert in Minuten ermittelt werden soll. Grund dafür ist, dass Excel die Zeitangabe intern als Dezimalzahl hinterlegt, wobei der ganze Teil für die Tage und der Dezimalteil für den Stundenanteil eines Tages stehen. Daher führt die Funktion STUNDE(xx)*60 + MINUTE(xx) nicht zum richtigen Ergebnis.

Eine einfache Lösung des Problems wäre es, den Dezimalwert der Zeitsumme mit 1.440 (=24 Stunden*60) zu multiplizieren und eine Anzeige im Standardformat einzustellen. Beispiel: Zeitsumme im Format hh:mm 6:50, im Format [hh]:mm 54:50, als Dezimalzahl 2,28472222 – das ergibt multipliziert mit 1440 den Wert 3.290 (Minuten).

nach oben

Makros und PERSONL.XLS

Um oft gebrauchte benutzerdefinierte Funktionen oder Makros in allen Arbeitsmappen nutzen zu können, sollten diese in einer gesonderten Arbeitsmappe gespeichert werden.
Excel bietet dafür eine eigene Mappe an, die den Namen Personl.xls trägt und im Verzeichnis XLSTART stehen muss. Dieses Verzeichnis findet man unter C:\Dokumente und Einstellungen\ Anwendername\ Anwendungsdaten\ Microsoft\Excel\XLSTART. Die Anlage dieser Datei und dieses Ordners wird von Excel beim ersten Aufzeichnen eines Makros angeboten.

Alle Excel-Dateien, die sich im o.g. Verzeichnis befinden, werden automatisch bei jedem Start von Excel geöffnet. Daher ist nur sinnvoll, die Makro-Sammeldatei hier abzulegen. In ihr gespeicherte Makros sind somit aus jeder Excel-Datei immer zugriffsbereit, sie ist sozusagen ein "Code-Behälter".
Beim Aufruf von Extras/ Makro werden alle in PERSONL.XLS befindlichen Makros zur Ausführung, Modifikation oder Ergänzung angeboten.

In Excel 2007 und 2010 werden Mappen mit Makros mit dem Typ XLSB oder XLSM abgespeichert. Sie sind über Entwicklertools/ Makros sichtbar bzw. aufrufbar. Es entsteht also eine Datei PERSONAL.XLSM. Entwicklertools werden über Datei/ Optionen/ Menüband anpassen und das Setzen des entsprechenden Häkchens aktiviert. Beim Aufruf weiterer Excel-Mappen aus Excel heraus werden nur diese Mappen geöffnet. Ein Doppel-Klick-Aufruf aus dem Explorer heraus wird als neuer Excel-Start gedeutet. Daher erfolgt ein Hinweis, dass Personl.xls bereits geöffnet ist (und ggf. die weiteren Dateien aus XLSTART ebenfalls) und die zusätzliche Version von Personl.xls nur schreibgeschützt geöffnet werden kann.

Personl.xls wird zwar bei jedem Start von Excel geöffnet, ob sie sichtbar ist, hängt jedoch vom letzten Excel-Beenden-Status ab. Mit Fenster/Einblenden kann man die Personl.xls sichtbar machen, mit Ausblenden wieder unsichtbar. Je nachdem, ob man Excel mit eingeblendeter oder ausgeblendeter Personl.xls beendet, ist sie beim nächsten Aufruf sichtbar oder nicht. Will man seine Makros einem Anderen übergeben, kann man die Personl.xls auch als Datei kopiert und im Zielrechner unter einem anderen Namen neben die dort vorhandene Personl.xls kopiert werden. Damit stehen die Makros aus beiden Dateien zur Verfügung.

nach oben

Zufallszahl und Zufallsbereich in Excel

Excel wird zwar normalerweise für korrekte Berechnungen eingesetzt. Es gibt aber auch Situationen, in denen man einen zufälligen Wert ermitteln möchte: Beispiele: einen zufälligen Kunden aus einer Liste auswählen, vom Programm die Glückszahlen für das Wochenendlotto geliefert bekommen oder für Simulationsaufgaben. Excel verfügt dazu über zwei Funktionen:

  • Funktion Zufallszahl() liefert einen Wert zwischen 0 und 1
  • Funktion Zufallsbereich(Startwert; Endwert) liefert eine ganze Zahl aus dem Bereich.

Bei der Verwendung dieser Funktion wird bei jedem Programmaufruf und bei jedem Betätigen der Taste F9 ein neuer zufälliger Wert (nach einem nicht veröffentlichten Algorithmus) geliefert. Damit die Funktionen zur Verfügung stehen, muss bis Version 2003 unter EXTRAS /Add-Ins/ Analysefunktionen angekreuzt sein, ab Version 2007 sind die Zufallsfunktionen generell in Excel integriert.

nach oben

Verwendung des Zeichens „?“ zur Zahlenformatierung in Excel

Excel gibt kaum einen Hinweis auf das Fragezeichen als Formatierungssymbol bei nutzerdefinierten Formaten. Das Fragezeichen fügt auf beiden Seiten des Dezimalkommas Leerzeichen für nicht signifikante Nullen ein, um Dezimalzahlen am Dezimalkomma auszurichten. Damit  kann man erreichen, dass Zahlenwerte kommagerecht untereinander stehen, ohne Beachtung von eingeschobenen Dezimal-Nullen.

315,     315, 000 315

31,  5   31,  500 31,5

3,   15  3,   150 3,15

,    315 0,   315 0,315

,    032 0,   032 0,0315

,    003 0,   003 0,00315

?,   ??? 0,   000 Standard

Bei dem Wert in der vorletzten Zeile 0,0315 soll die Wirkung der Rundung bei der Anzeige demonstriert werden: bei nur 3 Dezimalstellenanzeige wird hier aufgerundet, obwohl der Zahlenwert Excel-intern selbstverständlich nicht geändert wurde.

nach oben

Alle Kommentar-Zellen markieren

Excel-Zellen lassen sich mit Kommentaren versehen. Man kann sie beispielsweise nutzen, um eine Formel zu erläutern oder auf die Bedeutung des Zellenwertes hinzuweisen. Nach Zuweisung eines Kommentars erscheint eine kleine rote Markierung. Fährt man mit dem Mauszeiger über die Zelle, so wird der Kommentartext eingeblendet.

Mit der Tastenkombination Strg+Umschalt+O werden alle Zellen der aktuellen Tabelle markiert, die mit einem Kommentar versehen wurden. Auf diese Art hat man einen Überblick über die Kommentare und kann mit der Taste Tab von einer markierten Zelle zur nächsten springen.

nach oben

Bilder in Excel komprimieren

Excel-Tabellen werden oft zur besseren Veranschaulichung des Zahlenwerkes mit Bildern ergänzt. Wenn man diese Dateien per E-Mail versenden will, kann es aufgrund des Speicherbedarfs der Bilder zu langen Übertragungszeiten kommen. Da man aber auf die grafische Aufwertung nicht verzichten möchte und lange Übertragungsraten nicht in Kauf nehmen möchte, muss hier vor dem Speichern der Datei der Arbeitsschritt „Komprimieren von Bildern und Grafiken“ eingefügt werden. Damit bleiben die Daten in ihrer Darstellung aufgewertet und die Dateigröße erhöht sich nur in einem erträglichen Maß.

Um die Komprimierung durchführen zu können, klicken Sie einfach die Grafik oder das Bild einmal mit der Maus an, sodass bei der Excel-Version 2007 und 2010 im Menüband die Registerkarte "Bildtools" eingeblendet wird. In der Excel-Version 2003 erscheint stattdessen die Symbolleiste "Grafik". Klicken Sie nun auf das Symbol "Bilder komprimieren". Wir haben hier verschiedenste Auswahlmöglichkeiten. Bei der Option "Nur für dieses Bild übernehmen" legen sie fest, ob Sie nur das angeklickte Bild komprimieren möchten oder alle Bilder in der Mappe.

Wichtig ist hier auch die Option "Zugeschnittene Bildbereiche löschen" da Sie hier bei zugeschnittenen Bildern auch tatsächlich den abgeschnittenen Bereich entfernen. Ansonsten kann es passieren, wenn Sie das Bild verschicken, dass jemand einfach das Bild wieder erweitern kann.

In der Zielausgabe legen Sie fest, wie stark das Bild komprimiert werden soll. Bei der Option "Drucken" ist eine sehr hohe Auflösung gegeben, sodass die Komprimierung nicht so stark ist und damit die Dateigröße auch nicht besonders minimiert wird. Bei den Optionen "Bildschirm" und "E-Mail" ist dagegen eine starke Komprimierung gegeben und die Dateigröße wird entsprechend verkleinert. Diese Auflösungen reichen aber meistens für eine normale Korrespondenz aus.

nach oben

Stundenlohnberechnung

Berechnung des Stundenlohns

nach experto.de Computertipps von A bis Z vom 6.6.2011

Nach einer Erfassung der wöchentlichen Arbeitszeit möchte man die Leistungsstunden summieren und zur Abrechnung mit einem Stundensatz multiplizieren. Die interne Darstellung von Zeitangaben in Excel bringt da eventuell unerwartete Ergebnisse mit sich. In Excel wird ein Tag mit dem Zahlenwert 1 belegt, 12 Stunden folglich mit 0,5, 6 Stunden haben den Zahlenwert 0,25. Summiert man Zeitangaben, so wird standardisiert nur die Stundenangabe angezeigt, Tage – d.h. Werte über 1 – werden nicht angezeigt. Mit dem benutzerdefinierten Format [hh]:mm bekommt man die volle Stundensumme zwar zu sehen, kann aber trotzdem mit dem Wert nicht ohne weiteres rechnen. Nachvollziehbar ist die Formel für die Gesamtstunden, die dann mit dem Stundensatz zu multiplizieren ist:
TAG(B8)* 24 + STUNDE(B8) + MINUTE(B8) / 60
Das gleiche Ergebnis erhält man, wenn der Zeitwert als Zahl gedeutet und mit 24 multipliziert wird.

 

nach oben

letzte Änderung: 20.03.2017

Hilfe

Servicedesk

Neues Augusteum
2. Etage, Raum A252

Telefon: +49 341 97-33333
E-Mail

Suche

Nur in "Anleitungen A-Z" suchen