Excel- Grundlagen
Vorerst: Hier ist ein einfacher
zur Zeiterfassung und Abrechnung von Arbeitsstunden ( Stundenkonto )
zum Herunterladen.
Hier
Erweitert:Excel- Grundlagen
Das ist in Excel etwas verdreht: wenn Sie die Sperrung von Zellen aufheben möchten, so daß deren Inhalt geändert werden kann, darf das Tabellenblatt nicht geschützt sein.
Ggf. Tabellenschutz aufheben -
Markieren Sie den Zellenbereich, dessen Sperrung Sie aufheben
möchten.
Klicken Sie im Menü Format auf Zellen und dann auf die
Registerkarte "Schutz".
Deaktivieren Sie das Kontrollkästchen "Gesperrt".
Anmerkung: Nachdem Sie das Tabellenblatt ( wieder )
geschützt haben, können nur die Zellen im Tabellenblatt geändert
werden, deren Schreibschutz Sie mit diesem Verfahren aufgehoben haben.
Tip: Um zwischen nicht gesperrten Zellen in einem geschützten Tabellenblatt zu wechseln, klicken Sie auf eine nicht gesperrte Zelle, und drücken Sie dann TAB.
Aus: MS Excel- Tips
Mit Kopieren geht es nicht, aber in die Zelle, in der der Übertrag
erscheinen soll, das Gleichheitszeichen eingeben, in die Zelle des
Arbeitsblattes, deren Betrag übernommen werden soll, wechseln und
bestätigen. Das war's.
Tip:
KSD
Die Zelle rechts und unterhalb der gewünschten Zeile(n) und Spalte(n)
markieren und "Fenster, fixieren" aufrufen
Tabellen - Überschrift auf jeder Seite fixieren: [ Siehe auch
in Word ]
Fenster / Fenster fixieren.
Zickig!
Für den Druck geht man anders
vor: Datei / Seite Einrichten / Tabelle: Wiederholungszeilen
oben und Wiederholungsspalten links
Tip:
PC Welt
Dazu: Die Gitternetzlinien oder die grauen
Spalten - und Zeilenköpfe ausblenden in: Extras /
Optionen / Ansicht / "Zeilen - und
Spaltenüberschriften" bzw. "Zeilen - und
Spaltenköpfe" deaktivieren.
Diese Einstellung wird dann in dem betreffenden Tabellenblatt gespeichert.
Wenn in einer Tabelle Zahlen nicht bündig untereinander stehen, ist
die Übersichtlichkeit dahin. Dabei lassen sich Kalkulationsprogramme
so trimmen, daß die Dezimalkommata exakt ausgerichtet sind.
Unter Excel geht man hierzu wie folgt vor:
Markieren Sie die zu formatierenden Tabellenfelder.
Klicken Sie die zuvor markierten Zellen mit rechts an, und
wählen sie dann den Befehl "Zellen formatieren" aus.
Aktivieren Sie die Registerkarte Zahlen, und klicken Sie im Feld
"Kategorie" auf die Option "Benutzerdefiniert"
Im Feld "Formate" können Sie ein benutzerdefiniertes Format
eingeben;
Zum Beispiel:
" 0,??? "
Die Anzahl der Fragezeichen legt fest, wieviele Nachkommastellen angezeigt
werden.
Tip:
HNA
Excel schreibt manchmal standardmäßig bei der Eingabe
automatisch Dezimalstellen, zum Beispiel "0,01" "
statt "1"
Zum Abstellen in Extras / Optionen / Bearbeiten /
Einstellungen:
"Feste Dezimalstellen setzen" de - aktivieren.
Die Umrandung von Zellen kann innerhalb der Microsoft - Tabellenkalkulation
relativ leicht ein - oder ausgeblendet werden. Dazu muß man nur
das "Extras" - Menü öffnen und auf "Optionen" klicken. Über
den Menüpunkt "Gitternetzlinien" schaltet der Anwender die Markierung
ein oder aus. Mit einem Klick auf "OK" werden die Vorgaben aktiviert.
Möchte man allerdings nur in einem bestimmten Bereich die Rasterlinien ausblenden, hilft dieser Trick: Man markiert einen Bereich, klickt mit der rechten Maustaste auf die Markierung, um "Zellen formatieren" auszuwählen. Über "Muster" wählt man anschließend die Farbe Weiß aus und klickt auf "0k ". Der Effekt: Im gewünschten Bereich ist die Zellenumrandung verschwunden.
Tip:
HNA
Anzeigen oder Ausblenden bestimmter Fensterelemente
( Gitternetz u. a.):
gibt es zwei Möglichkeiten zu Wiederherstellung:
Es lassen sich in Excel nicht nur vorgegebene Listen automatisch
weiterführen, sondern auch eigene in Extras / Optionen /
Autoausfüllen / Neue Liste / Erstellen aufbauen.
Excel ergänzt auch selbständig angefangene Wortfelder. Diese Automatik läßt sich abschalten in: Extras / Optionen / Bearbeiten: Autoeingabe
Um zu verhindern, daß Excel selbständig Summen - Bereiche und ähnliches ausweitet, in: Extras / Optionen / Bearbeiten: Listenformat und Formeln erweitern abschalten.
Alle markierten Tabellenblätter werden gleichzeitig bearbeitet.
| STRG + | "." | Aktuelles Datum ( unveränderlich ) | |
| STRG + UMSCHALT + | "." | Aktuelle Uhrzeit ( unveränderlich ) | |
| STRG + UMSCHALT + | "!" | Alle markierten Zellen mit zwei Kommastellen | |
| STRG + | "+" | Zellen einfügen | |
| STRG + | "-" | Zellen löschen | |
| STRG + | "0" | Spalten mit markierten Zellen ausblenden | |
| STRG + UMSCHALT + | "0" | Ausgeblendete Spalten innerhalb der Markierung wieder einblenden | |
| ALT + UMSCHALT + | "0" | Summenformel für markierte Zellen | |
| STRG + | F2 | Zelleneigenschaften | |
| STRG + | "#" | Formeln anzeigen; Umschalten zwischen Formel und purem Zellwert | |
| STRG ( + UMSCHALT ) + | "&" | Standardformat hestellen | |
| |
|||
| STRG + UMSCHALT + ENDE | Gesamtes Blatt - soweit gefüllt - markieren | ||
| STRG + LEERTASTE | Spalte markieren | ||
| UMSCHALT + LEERTASTE | Zeile markieren | ||
| ALT + RETURN | Umbruch in Zelle | ||
| STRG + BILD UNTEN | Nächstes Tabellenblatt | ||
| STRG + F3 | Bereichsnamen für markierte Zelle (n) vergeben | ||
| F5 | Gehe zu (Bereichsnamen) | ||
Weitere Excel - Shortcuts ( Tastenkombinationen ) befinden
sich hier ( als Excel - Tabelle ):
http://www.excel-tuning.de/
Weitere Tips für den Umgang mit Excel
Funktionstasten in Excel
Datum und Zeit in Excel
Format / Zellen / Zahlen / Kategorie: Bruch
Dort das gewünschte Format auswählen
In Extras / Optionen / Bearbeiten / Einstellungen:
"Markierung nach dem Drücken der Eingabetaste verschieben" das
grundsätzlich gewünschte Ziel angeben oder die Funktion ganz
deaktivieren.
Mit Hilfe von
![[ Strg ]](../tasten/strg.gif)
+
![[ Enter ]](../tasten/enter4.gif)
nach Belieben steuern.
Extras / Optionen / Allgemein: Standardarbeitsordner ändern
Hier das gewünschte Verzeichnis angeben.
( Läßt man das Feld frei, nimmt Excel praktischerweise
das zuletzt geöffnete Verzeichnis. )
Zu diesem Zweck kann ein kleines Makro geschrieben werden:
Sub Heute()
ActiveCell.Value = Date
End Sub
Tip:
PC Welt
Excel stellt in Diagrammen auch ungültige Werte oder leere
Zellen als 0 dar.
Um dieses zu vermeiden, kann man über eine Formel wie
=WENN(C1=1;1;NV())
in der Zelle als Resultat die Anzeige '#NV' erzwingen, im Diagramm
fehlt der Wert dann vollständig.
Wenn auch noch die fraglichen Zellen zumindest leer ERSCHEINEN sollen,
Tip:
Stefan Peschke
Excel-Tabellen sollen optimale Übersicht
bieten, indem sie das Über- oder Unterschreiten bestimmter Werte optisch
signalisieren; Excel bietet unter "Format, Bedingte
Formatierung" mit "Zellwert ist" eine Reihe einfacher Kriterien
für die automatische Formatierung:
Wenn man etwa "Größer als"
wählt und rechts daneben eine Zahl eingibt, erhalten die vorher markierten
Zellen beim Überschreiten dieses Grenzwerts das Format, das man über
den "Format"-Button definiert.
Da für einen Zellbereich insgesamt drei Bedingungen möglich sind,
kann man so mühelos etwa hohe, niedrige und durchschnittliche Werte
farblich abgrenzen.
Die Funktion "Zellwert ist"
stößt aber schnell an Grenzen: Erstens arbeitet Excel hierbei
mit starren Konstanten, zweitens kann man mehrere Bedingungen nicht im Sinn
eines logischen "UND" aneinander koppeln, drittens sind nur numerische Werte
sinnvoll. Mit "Formel ist" löst man auch anspruchsvolle Aufgaben:
Um beispielsweise in Spalte A jeden Wert optisch hervorzuheben, der
über 8000 liegt (starre Konstante), den Durchschnittswert der
Spalte um das Doppelte schlägt (intelligente Variable) oder das
Maximum der Spalte darstellt, kann man folgende Formel verwenden:
oder(al>mittelwert(a:a)*2; a1=max(a:a);
a1>8000)
Bei der Hervorhebung niedriger Werte sollte man immer ausdrücklich
vermeiden, dass leere Zellen (Wert "0") mitformatiert werden, also etwa:
=und(Al<mittelwert(A:A)/2; A1>0)
Der relative Bezug (A1) wird wie üblich auf den gesamten vorher
markierten Bereich übertragen. "Formel ist" kann auch Text oder
Datumswerte gezielt formatieren:
=und(A1>0; heute()-A1>730)
Diese Bedingung zeigt alle Zellen in einem definierten Format an, die ein
Datum enthalten, das - vom aktuellen Datum ab gerechnet - länger
als zwei Jahre zurückliegt.
Tip:
PC Welt
=SUMMEWENN(A1:A50;">10")
( addiert alle Werte im Bereich A1 bis A50, deren Wert größer
ist als 10 )
=SUMMEWENN(A1:A50;">"&B1)
( addiert alle Werte im Bereich A1 bis A50, deren Wert größer
ist als der in B1 )
Die Plazierung der Anführungszeichen ist wichtig!
Tip:
PC Welt
Excel ist zwar grundsätzlich in der Lage, geordnete,
d. h. mit Semikolon, Komma oder Tabulator
u. a. aufgeteilte Texte ( beispielsweise Listen ) in
eine Excel - Tabelle umzuwandeln, wenn man die entsprechende
Text - oder andere ASCII - Datei einfach in Excel öffnet.
[ Versuch: Der Textdatei die Endung ".XLS" verpassen ]
Es versagt aber häufig, wenn es wegen unklarer Trennzeichen mehr als
eine Möglichkeit gibt, diesen Text in einer Tabelle zu sortieren.
Abhilfe:
Exkurs:
Excel - Tabellen in Word öffnen ( das Folgende
gilt auch für Libre Office zwischen
Kalkulations - und Wort - Dokumenten )
Word kann Excel - Tabellen
zwar importieren, behandelt sie aber meistens als fremdes Objekte, die weiter
als Excel - Tabelle fungieren. Oft wird jedoch gewünscht,
eine fertige Excel - Tabelle einfach in ein
Word - Dokument einzubringen.
Vorgehen:
=MONATSENDE(HEUTE();0)
Zeigt den letzten Tag des aktuellen Monats an, wenn
=MONATSENDE(HEUTE();-1) zeigt den letzten Tag des Vormonats
an, usw.
Will die Anzahl der Tage für feste Monate bestimmen, setzt man
DATWERT ein:
=MONATSENDE(DATWERT("01.02.04");0) ergibt 29 ( Februar
2004, ein Schaltjahr! )
=MONATSENDE(DATWERT("01.02");0) zeigt den letzten Tag des Februar
des laufenden Jahres an; 29 bei einem Schaltjahr, sonst 28.
Tip:
PC Welt
Tip:
PC Welt
Manchmal ist es notwendig, Datensätze und nicht Daten zu addieren bzw.
Vorkommen zu zählen, zum Beispiel Zugriffe auf verschiedene Webseiten
aus einer Log-Datei oder "5 Geranien + 3 Nelken +
7 Geranien + 2 Nelken + 34 Geranien" und das von verschiedenen
Plätzen und aus unterschiedlichen Zeiten.
Automatisch geht das in Excel nicht - außer ggf. mit einer manchmal
etwas schwierig zu bauenden Pivot-Tabelle ( hier dieses Beispiel:
400 kb, als 6 kb -
Zip-Datei gepackt ) - wenn
man diese nicht vorher einzeln in einer Tabelle aufführt und dann manuell
für jeden einzelnen Posten die neue Zahl hinzufügt.
Es geht aber auch anders: und zwar in Kombination mit ACCESS,
wenn auch auf Umwegen.
Jede neue Eingabe wird dabei zu den bestehenden dazuaddiert.
NB:
ACCESS kann im
übrigen dazu dienen, korrupte ( zerstörte ) Excel -
Dateien zu öffnen, wenn es nicht zu schlimm ist, um so wenigstens
einen Teil der Daten zu retten. Man kann zu diesem Zweck versuchen, die kaputte
Excel - Datei in ACCESS zu importieren und
so die einzelnen Tabellen mit den enthaltenen Datensätzen zu
retten. Die Excel - Funktionen gehen dabei natürlich
verloren.
Oft will man unterbinden, daß in einer Tabelle in mehr als in einem
Feld Werte eingetragen werden ( hier im Beispiel in den Feldern
C1 - C5 ).
Lösung: prüfen lassen, ob die Gesamtsumme der Einträge
sich von dem Eintrag in einem einzelnen Feld unterscheidet:
=WENN(SUMME(C1:C5)=C1;""; ... WENN(SUMME(C1:C5)=C5;"";"Bitte immer nur einen Wert eingeben!") ... )
[ Arbeitet dann korrekt, wenn negative Eintragungen und Null verboten
bzw. unschädlich sind ]
Neben den globalen benutzerdefinierten
Zellenformaten kann man jede Zelle individuell formatieren.
Da ist sehr viel möglich, wodurch Zellen wiederholte
- auch längere -
Texte enthalten können.
Z. B.:
allerdings kann Excel mit diesem
Wert nicht weiter rechnen; das geht nur bei den besagten globalen
benutzerdefinierten Zellenformaten.
Deshalb dienen solche individuellen Zellenformate nur der Optik und dem
Verständnis. Man sollte darum auch solche Formate am Ende einer Berechnung
einsetzen, in dem man mit "= X1" auf die Zelle mit dem Ergebnis verweist.
( Tip:
PC Welt )
Aus der Excel 97 - Hilfe:
Ausblenden von Nullwerten oder anderen Daten in markierten Zellen
Normalerweise sollten dazu die gewünschten
Spalten mit Köpfen markiert, über
"Bearbeiten" kopiert und die selbe Anzahl Spalten in einem neuen Blatt
( Einfügen / Tabelle ) ebenso mit
Spaltenköpfen markiert und der Bereich eingefügt werden.
Das geht, ist aber etwas umständlich und Excel wehrt sich
manchmal mit Speicherproblemen.
Kopiert man den markierten Bereich in einem Tabellenblatt ohne
Tabellenköpfe in ein neues, leeres Tabellenblatt, in dem man ihn dort
in die Zelle A1 einfügt, wird zwar der gesamte Tabelleninhalt,
nicht aber die Spaltenbreite übertragen.
Lösung 1: Wenn schon neue Werte eingetragen wurden und nur die
Spaltenbreite nachträglich angeglichen werden soll:
Excel die Mappe zweimal öffnen lassen ( z. B.
über "senden an"; eine davon
ist dann automatisch schreibgeschützt ), beide Programmfenster
untereinander setzen und die Spaltenbreite in dem gewünschten Fenster
durch Ziehen angleichen.
Lösung 2: In Excel eine Vorlage der gewünschten
Tabelle erstellen, den Reiter unten an diesem Tabellenblatt markieren
und mit gedrückter STRG - Taste nach rechts ziehen
( = vollständig kopieren, mit Blattschutz ).
Über die rechte Maustaste auf dem Tabellenreiter und
"Verschieben / Kopieren" läßt sich die Vorlage auch
in eine völlig neuen Mappe hineinkopieren ( Achtung: Haken vor
"Kopieren" setzen! ) sowie umbenennen.
Das Herunterziehen am
![[ + ]](../bilder/anker-p.gif)
rechts unten an einem Tabellen - Feld verschiebt automatisch den Bezug
einer darin enthaltenden relativen Formel, allerdings immer nur um jeweils
ein Feld.
Manchmal liegen die gewünschten Bezugsfelder aber weiter auseinander.
Ist dieser Abstand regelmäßig, läßt sich der Zeilensprung
auf einen beliebigen Wert erhöhen, mit:
=BEREICH.VERSCHIEBEN(G$21;((ZEILE(A1)-1)*20);0)
Damit läßt sich die Formeln automatisch weiterführen.
wichtig ist hier (ZEILE(A1)-1),
das den Term 0 liefert - und beim Herunterziehen am
rechts unten am Feld zu
=BEREICH.VERSCHIEBEN(G$21;((ZEILE(A2)-1)*20);0)
=BEREICH.VERSCHIEBEN(G$21;((ZEILE(A3)-1)*20);0)
... etc.
wird,
G21
G41
G61
... etc.
generiert und abfragt. Hier ein Beispiel: joggen.xls als
Zip-Datei gepackt - zum Erfassen von
Trainings - Zeiten und gelaufenen Kilometern
Statt A1 in (ZEILE(A1)-1)
ließe sich jede andere Zelle nehmen, es sollte aber eine Spalte sein,
in der keine Berechnungen stattfinden, um Fehler durch Selbstbezug zu
vermeiden.
Mit
=BEREICH.VERSCHIEBEN(A$1;0;((ZEILE(A1)-1)*1))
=BEREICH.VERSCHIEBEN(A$1;0;((ZEILE(A2)-1)*1))
... etc.
läßt sich analog die Zeile A in eine Spalte umwandeln.
Auch Kombinationen sind möglich
NB: Jeden zweiten Wert im Bereich A1:A25 der Spalte (!) A berechnen:
=SUMME(WENN(REST(ZEILE(A1:A25);2)=0;A1:A25))
ACHTUNG: Es handelt sich um eine Matrixfunktion! Nicht wie üblich mit
bestätigen! +
Es erscheint dann in der Eingabezeile:
{=SUMME(WENN(REST(ZEILE(A1:A25);2)=0;A1:A25))}
( Die Zahl 2 bestimmt den Zeilensprung, und mit SPALTE statt ZEILE läßt sich die Formel auf eine Zeile anwenden )
Tip:
PC Welt
Nach Markieren des gewünschten Zellbereichs - etwa einer gesamten Spalte
- und Aufruf des Dialogs "Daten, Gültigkeit" bietet Excel
wesentliche Kriterien direkt an.
So genügt es oft, die Vorgabe "Ganze Zahl" mit Minimum
und Maximum auszuwählen, um etwa negative und unrealistische
Werte sowie Brüche zu verhindern.
Bei Texteingaben kann die Vorgabe der "Textlänge"
nützlich sein, wenn die Tabelleninhalte später in ein festes Layout
exportiert werden sollen.
Nützlich ist ferner die Vorgabe einer Liste (irgendwo auf demselben
Tabellenblatt) als Quelle: Der markierte Bereich verweigert dann jede Eingabe,
die nicht in dieser Liste steht, und bietet außerdem per Drop-down-Pfeil
alle Einträge dieser Liste an.
Die "Quelle" kann als Bezug in der Form "Z1:Z100" oder als
Name, etwa "Produkte", angegeben werden, sofern für den Zellbereich
vorher ein Name definiert wurde.
Wenn die Kriterien komplizierter werden, kommt man unter "Zulassen"
nur noch mit dem Eintrag "Benutzerdefiniert" und einer Formel weiter.
Auf diesem Weg kann man für einen Zellbereich zum Beispiel festlegen,
dass nur Werte aus einer bestimmten Liste eingetragen werden dürfen
und außerdem kein Eintrag doppelt auftreten darf.
Die Formel dafür lautet:
=und(vergleich(D1;$L$1:$L$100;
0)>0;zählenwenn(SD:SD;D1)<2)
Wie bei der vorgegebenen Option "Liste" entscheidet Excel anhand einer Werteliste
(L1:L100), ob der Eintrag in Spalte D erlaubt ist. Doppel sind
ausgeschlossen, weil "Zählenwenn" dann eine "2" meldet. Den eingegebenen
relativen Bezug - hier D1 -überträgt Excel automatisch auf
den gesamten markierten Bereich, also etwa auf die gesamte Spalte D.
Ein weiteres Beispiel für eine benutzerdefinierte Gültigkeitsregel:
=und(Links(B1;3)="pcw";Rechts
(B1;4)=".zip";länge(B1)>10)
Das erzwingt für die Eingabe von Dateinamen eine vorgegebene Systematik
und Mindestlänge.
Um doppelte Eingaben in einer Tabellenspalte
zu verhindern, so daß jede Zahl und jeder Text nur eine einziges Mal
vorkommen kann,
Duplikate werden nun nicht mehr zugelassen und führen zu der Fehlermeldung.
Tip:
PC Welt
Dreiecke berechnen: Zwei Seiten und einen Winkel
oder zwei Winkel und eine Seite eines beliebigen Dreiecks eingeben - und
Bingo! Die fehlenden Winkel und Seiten werden berechnet; dazu Umfang und
Fläche. Hier mit Kreisfunktion.
Oder auch
Widerstand - Berechnung
für elektronische Schaltungen
u.v.a.m.
+
![[ Alt ]](../tasten/alt.gif)
+
![[ F9 ]](../tasten/f9.gif)
![[ Rollen ]](../tasten/scroll.gif)
- Taste![[ Druck ]](../tasten/druck.gif)
- Taste ] - Taste erneut drücken.
Zur Berechnung von Winkel-
Funktionen wie Sinus und Kosinus (Bogenmaß) siehe hier:
Winkelberechnung in Excel.
RÜCK
| Von
FreeFind Inhaltsverzeichnis |