Alle unsere Tipps (Home)
Übersicht Buch- und Hobby-Tipps


Excel-Tipps: Beispiele, Tipps, Ideen, Bücher und Links zu Excel und Visual Basic

In dieser Seite habe ich einige meiner Excel-Tipps und -Tricks gesammelt. Nach manchen Lösungen musste ich selbst erst suchen oder länger dafür nachdenken. Außerdem werden hier hilfreiche Buchtipps und Links vorgestellt.

Inhalt:


Bücher

Mit Easy Excel 2003: Leicht, klar, sofort klappt der Einstieg in Excel bestimmt.

Das 800-Seiten-Nachschlagewerk Formeln & Funktionen - Das Maxibuch: Einführung in die Nutzung von Formeln und Funktionen von Excel 2000 bis 2007 lässt kaum Wünsche offen.

In der Reihe "... für Dummies" darf natürlich auch das Thema Excel nicht fehlen. Es gibt z. B. folgende Titel:
 
Microsoft Excel 2007 - Basiswissen
Die ersten Schritte in Excel 2007 werden in diesem Heft in sehr übersichtlicher Weise dargestellt: Excel Arbeitsumgebung, Zellformatierung, Formeln, Funktionen und Diagramme.

Microsoft Excel 2007 - Aufbauwissen
Excel 2007 für fortgeschrittene Anwender - einschließlich VBA-Einführung
Die wichtigsten Funktionen für Fortgeschrittene wie z.B. Matrix-, Statistik- und Logikfunktionen, Arbeit mit Datenbanken und Listen, Pivot-Tabellen, Makros, Diagramme und Einführung in die VBA-Programmierung.

Hefte für Excel 2010: Microsoft Excel 2010 - Basiswissen
und Microsoft Excel 2010 - Aufbauwissen
 
Und noch ein paar Bücher zur Einführung in die VBA-Programmierung:





Links

zum Seitenanfang


Programmiertipps und -ideen

Hier stelle ich ein paar Lösungen für Probleme vor, zu denen ich am häufigsten gefragt wurde und werde. Vielleicht stehen Sie gelegentlich vor ähnlichen Problemen und Fragen. Zunächst beschreibe ich reine Excel-Lösungen, später auch Lösungen mit Excel-VBA (Visual-Basic).
Viel Spaß beim Benutzen! - und lassen Sie sich dadurch zu eigenen Experimenten und Ideen anregen:



Ergebnisse runden

Excel zeigt zwar meist gerundete und scheinbar 'glatte' Ergebnisse an, rechnet aber intern mit bis zu 16 Nachkommastellen weiter. Für mathematische und technische Anwendungen kann das erfreulich und nützlich sein, bei Rechnungen mit z. B. kaufmännischen Zahlen ist es sehr unerwünscht.

Man könnte im Menü Extras/Optionen/Berechnung das "Genauigkeit wie angezeigt" aktivieren; dies gilt dann aber für alle Ihre Excel-Anwendungen und könnte unerwünschte Effekte in anderen Excel-Mappen auslösen. Wenn Sie außerdem Ihre Excel-Mappe an jemanden senden, der "Genauigkeit wie angezeigt" nicht aktiviert hat, gibt es rasch weitere Probleme. Fazit: Lieber jeweils aktiv entscheiden, wo gerundet werden muss und es dort eingeben.

Excel bietet die Funktionen AUFRUNDEN( ; ), ABRUNDEN( ; ) und das kaufmännische RUNDEN( ; ). Vor dem Semikolon wird der zu rundende Wert oder die zu rundende Formel angegeben. Hinter dem Semikolon wird angegeben, auf wieviele Stellen gerundet wird:
• Positive Zahlen: Anzahl Nachkommastellen
• Null: Runden auf ganze Zahlen
• Negative Zahlen: Runden auf Zehner, Hunderter usw.

Wenn man in speziellen Fällen mal in ganz anderen Schritten runden will, muss man den zu rundenden Wert erst durch diese Schrittweite teilen und das Ergebnis damit multiplizieren.
Beispiel für Runden in Schritten von 0,2:

=RUNDEN( A1/0,2 ; 0 )*0,2

Beispiel für Runden in 25er-Schritten:

=RUNDEN( A1/25 ; 0 )*25

Auch die Funktionen KÜRZEN() und GANZZAHL() runden Zahlen. KÜRZEN() schneidet Nachkommastellen einfach ab, GANZZAHL() rundet konsequent zur nächsten ganzen Zahl ab.
Bei positiven Zahlen verhalten sich beide Funktionen gleich, bei negativen unterschiedlich: KÜRZEN(-4,3) gibt -4 zurück, GANZZAHL(-4,3) gibt -5 zurück, da -5 die nächstkleinere ganze Zahl ist.

Tipp zur Vermeidung von Rundungsfehlern für Tabellen, die mit Geld zu tun haben:
Gewöhnen Sie sich generell an, überall dort auf 2 Stellen zu runden, wo Sie mit Zehntel-Cent zu tun haben (z. B. bei Preisen mit Zehntel-cent mal Menge) oder wo Mehrwertsteuer berechnet wird.
zum Seitenanfang



In Zellen angezeigte Werte formatieren

In Excel gibt es drei Methoden, mit denen man Zellen formatieren kann: Formatieren der ganzen Zelle, Formatieren des angezeigten Werts und "Bedingte Formatierung". In diesem Abschnitt geht es zunächst um das Formatieren von Zellen.

Häufig will man, dass in Zellen nicht nur "nackte" Zahlen angezeigt werden, sondern dass die Zahlen in eine für den Betrachter gut erfassbare Form gebracht werden oder dass auch physikalische oder Währungs-Einheiten dazu kommen.
Die zu formatierenden Zellen werden dazu markiert, und Excel bietet dann über Menü Format→Zellen....→'Registerblatt Zahlen' oder mit Maus-Rechtsklick und 'Zellen formatieren...'→'Registerblatt Zahlen' schon Einiges an Auswahl. Wenn man aber speziellere Wünsche hat, muss man dort ein benutzerdefiniertes Format eingeben.
Im Folgenden sind einige Beispiele und Möglichkeiten aufgelistet. Bitte beachten Sie, dass Excel die Zahlen zwar entsprechend ihrer Formatierung anzeigt, aber intern evtl. mit vielen Nachkommastellen weiterrechnet, wenn nicht per Formel gerundet wird! Mit der Formatierung wird nur die Anzeige des Wertes festgelegt. Man kann mit dem Wert (falls man ihn nicht mit @ als Text formatiert hat) weiterrechnen.

Formatierung Wert in Zelle Anzeige Beschreibung/Erklärung
#.##0,00 1234567,428 1.234.567,43 0 (Null) bezeichnet Ziffern, die angezeigt werden müssen,
# bezeichnet Ziffern, die angezeigt werden können,
Komma und Punkt bestimmen die Lage von Dezimalkomma und Tausender-Trennzeichen.
#.##0,0?? 4567,428
543,1
4.567,428
543,1  
Das Fragezeichen reserviert Platz für die angegebene Menge Nachkommastellen, auch wenn keine eingegeben wurden. Kommas stehen dadurch untereinander.
#.##0,00___A€ 4567,4 4.567,428     € Die drei Unterstriche und das A lassen einen Leerraum mit der gleichen Breite wie drei A entstehen.
"€"* #.##0,00

"€"*~#.##0,00
4567,4 €       4.567,428

€~~~4.567,428
Der Stern macht das, was links von ihm steht linksbündig und den Rest rechtsbündig. Der Freiraum wird mit dem Zeichen rechts vom Stern (hier z. B. ein Blank oder Tilde) aufgefüllt.
00000 56,95 00056 Die Zahl wird 5-stellig mit führenden Nullen (z. B. wie eine Postleitzahl) angezeigt.
Einheiten und Texte vor und hinter Zahlen
0,00 "€/m²"
#.##0 "km/h"
#.##0,00 "€/h"
"KW" 00
"1,5V "@" Batterie"
30,6
1600
0,3
35
AAA
30,60 €/m²
1.600 km/h
1.600 km/h
KW 35
1,5V AAA Batterie
Texte müssen in Gänsefüßchen. Das Leerzeichen kann dabei zwischen Zahl und Gänsefüßchen oder vor den Gänsefüßchen stehen.
Datum und Zeit
T.M.JJJJ
TT.MM.JJ
T. MMM. JJ
T. MMMM. JJ
TTTT TT.MM.
TTT.* TT.MM.
9.2.10 9.5.2010
09.02.010
9. Feb, 10
9. Februar 10
Mittwoch 09.02.
Mo.     09.02.
T und M: Zahlen einstellig
TT und MM: Zahlen zweistellig
MMM: Abgekürzter Monat; MMMM: Ausgeschrieben
TTT: Abgekürzter Tagesname; TTTT: Ausgeschrieben
hh:mm
h "Uhr" mm "Min."
hh:mm:ss,sss
[h]:mm
9:10
9:10
9:10:7,5
32:50
09:10
9 Uhr 10 Min.
9:10:7,500
32:50
hh und mm: Zahlen zweistellig
h und m: Zahlen einstellig
Stunden, Minuten, Sekunden und Millisekunden
mit [h] können über 24 Stunden angezeigt werden

Beachten Sie bitte, dass nach DIN 5008 bei allen Einheiten ein Leerzeichen (Blank) zwischen Zahl und Einheit stehen soll.
Ausnahmen: Winkel-Grade, -Minuten und -Sekunden (49°58'22" Nord) sowie Potenzen und Indices (103, H2SO4)
Link: www.din5008.de → Größenangaben und Formeln


Speziellere Formatierungen

Formatierung Beschreibung/Erklärung
#.##0,00;-#.##0,00;0,00;@

[Grün]+ #.##0,00;[Rot]- #.##0,00;;@
Mit Semikolons können mehrere Ausgaben festgelegt werden.
Oberes Beispiel: Positive Zahlen ohne Vorzeichen; negative Zahlen mit Vz.; Null mit 2 Nachkommastellen; alles Andere wird als Text formatiert.
Unteres Beispiel: Positive Zahlen grün und mit Vz.; negative Zahlen rot und mit Vz.; Null wird nicht angezeigt; Anderes als Text.
+ #.##0,00;- #.##0,00;;[Rot]"Nur Zahlen eingeben!!" Positive und negative Zahlen mit Vz.; Null wird nicht angezeigt; bei Anderem eine rote Warnmeldung
;[Rot]- #.##0,00;; Positive Zahlen werden nicht angezeigt; negative Zahlen rot und mit Vz.; Null und Anderes wird nicht angezeigt.
Achtung: Excel rechnet mit den nicht angezeigten Zahlen (hier: positive oder Null) weiter!
"€"* #.##0,00;[Rot]"€"* -#.##0,00 Positive Zahlen werden schwarz angezeigt; negative Zahlen rot. Der Stern sorgt dafür, dass das € linksbündig und der rest rechtsbündig ist. Nullen und Text werden, da hier nicht festgelegt, standardmäßig angezeigt.
[=1]0 "Haus";0 "Häuser"

[>100][Blau]0,00;[>=-100]0,00;[Rot]0,00
Mit [] können einfache Bedingungen erzeugt werden.
Oberes Beispiel: Bei Wert =1: 1 Haus; sonst z. B.: 2 Häuser
Unteres Beispiel: Bei Wert >100: blaue Schrift; bei >=-100: schwarz; sonst: rot
[Blau]0,000 "kg";[Farbe10]@ Zahlen werden blau, mit 3 Nachkommastellen und mit Einheit kg angezeigt, Texte dunkelgrün (Farbtabelle)
[Blau]0,000 "kg";[Farbe10]@ Zahlen werden blau, mit 3 Nachkommastellen und mit Einheit kg angezeigt, Texte dunkelgrün (Farbtabelle)
[>256][Blau]0;[>=1]"";[Rot]0;[Farbe10]@ Zahlen über 256 werden blau angezeigt, Zahlen von 1 bis 256 gar nicht, die übrigen Zahlen rot, Texte dunkelgrün

Aber Vorsicht: Besonders in Tabellen, die mehrere Leute bearbeiten, sollte man es mit dieser Art der Formatiererei nicht übertreiben. Das Formatieren ist für den Betrachter der Zellen und der Formeln darin zunächst unsichtbar, und das kann zu Verwirrung führen. Auch man selbst könnte Probleme bekommen, wenn man seine Tabelle nach einigen Monaten mal überarbeiten muss.

Wenn eine Zelle nur mal vorübergehend als Text formatiert werden soll, kann dies auch durch Eingabe eines Hochkommas ' am Anfang der Zelle geschehen.


Formatieren von Werten in einer Zelle

Bei Bedarf kann eine Zahl auch mit der Excel-Funktion TEXT(;) formatiert werden. Vor dem Semikolon kommt eine Formel oder ein Wert, danach die Formatangabe in Gänsefüßchen. Wenn die Formatangabe selbst Gänsefüßchen enthält, müssen sie verdoppelt werden.
Beispiele:

=TEXT( heute() ;"TTTT TT.MM.JJ")

=TEXT( A1 ;"#.##0,000 ""€/m²""")

Durch mehrfaches Nutzen der Funktion TEXT() kann man Werte aus verschiedenen Zellen kombinieren. Beispiel (ohne Return-Taste eingeben!):

="Heute, am " & TEXT(heute() ;"TT.MM.JJ") & " wurden " & TEXT((A3;"0,0 ""m³""") & " Gas verbraucht."

Nachteil: Der mit TEXT() formatierte Wert verhält sich wie ein Text; man kann mit ihm nicht weiterrechnen.

In VBA heißt diese Funktion Format( , ). Ein Datum muss dort mit englischen Abkürzungen formatiert werden:

MeinDatum = Format( date() , "dd.mm.yyy") MeinDatum = Format( 0.3 , "#.##0,00 ""€/km""")

Viele weitere Möglichkeiten für Formatierungen sind z. B. in diesen Seiten zu finden:
online-excel.de/excel/...,   ms-excel.eu/faqs/...,   excelabc.de/excel/...

Zuletzt:
Wenn man eine Zelle mit der Schriftart Wingdings formatiert, und JKLM oder ü eingibt, wird JKLM oder ü angezeigt.
zum Seitenanfang



Bedingte Formatierung

Mit der im vorigen Kapitel beschriebenen Formatierung wird nur der in einer Zelle angezeigte Wert formatiert. Mit der "bedingten Formatierung" (Menü Format→Bedingte Formatierung...) können z. B. Schriftart, Hintergrund, Rahmen usw. einer Zelle in Abhängigkeit von Werten in dieser oder anderen Zellen formatiert werden.

Es können hier Grenzen für Werte angegeben werden, oder die Formatierung kann vom Ergebnis von Formeln abängig gemacht werden. Zu einer einzigen Zelle können sogar mehrere Bedingungen und Formatierungen angegeben werden.

Links bis Excel 2003: online-excel.de/excel/..., excelabc.de/excel/...
Links ab Excel 2007: office-lernen.com/excel-bedingte-formatierung, office.microsoft.com/... (Excel 2007)
zum Seitenanfang



Umrechnung vom Brutto zum Netto

Vielen Menschen ist nicht wirklich klar, wie man aus einem Bruttopreis den Nettopreis und die Mehrwertsteuer berechnet.
Beim Bruttopreis steckt die Mehrwertsteuer sozusagen "mit drin". Es handelt sich also um die 119% und nicht um 100%. Um den Nettopreis zu erhalten, müsste man also den Bruttopreis durch 119 teilen und mit 100 multiplizieren.
Kürzer: Bruttopreis / 1,19 und das gerundet → Nettopreis
Diese Formel sieht in Excel (mit Bruttopreis in A1) so aus:

=RUNDEN( A1 / 1,19 ; 2 )

Das Runden auf zwei Stellen ist nötig, damit Excel wirklich mit dem angezeigten Wert weiterrechnet und nicht mit bis zu 16 Nachkommastellen, was immer wieder zu Rundungsfehlern führen würde.
Die Mehrwertsteuer bzw. Umsatzsteuer errechnet sich mit: Bruttopreis minus Nettopreis → Steuer.
Diese Reihenfolge "erst Netto, dann Steuer berechnen" ist im Umsatzsteuergesetz (§ 10 UStG) dadurch festgelegt, dass der Nettopreis als "Bemessungsgrundlage" bezeichnet ist.
Bei 7% Steuersatz sähe die Formel so aus:

=RUNDEN( A1 / 1,07 ; 2 )

Wenn der Bruttpreis in A1 stünde und der Steuersatz in B1, könnte die Formel so aussehen:

=RUNDEN( A1 / (1 + B1) ; 2 )

Vielleicht hätten Sie vermutet, dass es hier (1 + B1/100) heißen muss. Excel hat aber in Zellen, die als Prozent formatiert sind, nicht wirklich den Prozentwert, z. B. 19, gespeichert, sondern 'nur' den Prozentfaktor, also in diesem Beispiel 0,19. Dies ist zunächst ungewohnt, erleichtert bzw. verkürzt aber die Erstellung von Excel-Formeln.




Differenz zwischen Kalendertagen

Um die Differenz zwischen zwei Datumsangaben in Tagen zu errechnen, kann man die beiden Datumsangaben einfach voneinander abziehen. Das frühere Datum soll dabei in B1 stehen, das spätere in A1:

=B1-A1

Wenn dann als Ergebnis keine Zahl, sondern ein seltsames Datum aus dem Jahr 1900 erscheint, muss man die Zelle, in der diese Formel steht, noch mit Menü/Format/Zelle als ganze Zahl formatieren.
Wenn die angegebenen Anfangs- und Endtage (wie z. B. auch bei den Römern und zu biblischen Zeiten) mitgezählt werden sollen, muss noch +1 zur Formel addiert werden. Wenn beide Tage nicht mitgezählt werden sollen, kommt -1 zur Formel.

Weitere Möglichkeiten gibt es mit der versteckten Funktion =DATEDIF( ; ; )

Das Ermitteln von Arbeitstagen (Funktionen =ARBEITSTAG und =NETTOARBEITSTAGE) ist bei www.excelwelt.de gut erklärt.




Differenz zwischen Uhrzeiten

Man kann auch Uhrzeiten z. B. 11:30 (in Zelle B1) und 12:50 (in C1) mit =C1-B1 voneinander abziehen. Es erscheint wieder eine Uhrzeit: 1:20, die Differenz in Stunden und Minuten.
Wenn man das Ergebnis aber - weil man es z. B. mit anderen Zahlen addieren will - als Dezimalzahl formatiert, entsteht eine seltsame kleine Zahl: 0,5556. Das liegt daran, dass Excel in Tagen 'denkt'. Man muss das Ergebnis daher mit 24 multiplizieren, um Stunden und deren dezimale Anteile zu erhalten; zusätzlich sollte man das Ergebnis auf z. B. zwei Stellen runden, da Excel zwar ein gerundetes Ergebnis anzeigt, aber intern mit allen Nachkommastellen weiterrechnet.

=RUNDEN( (C1-B1)/24) ; 2)

Die Klammer um C1-B1 ist nötig, weil Excel sonst (wie in der Mathematik üblich) erst multipliziert und dann subtrahiert.




Kalenderwoche in Datum umrechnen

Falls man mal Jahr und Kalenderwoche in ein Datum umrechnen muss, hilft die nachfolgende Formel. Dabei wird vorausgesetzt/angenommen, dass das Jahr in Zelle A1 und die Kalenderwoche in B1 steht.

=("4.1." & A1) + B1*7-7 - REST("2.1." & A1 ; 7)

Wenn die Zelle, in der diese Formel steht, nach Drücken von Return nur eine mehrstellige Zahl anzeigt, muss man die Zelle noch mit Menü/Format/Zelle als Datum formatieren. Das angezeigte Datum ist der Montag der jeweiligen Kalenderwoche. Für den Dienstag muss dann noch +1 zur Formel addiert werden; für den Mittwoch +2 usw.

Der Weg vom Datum zur "deutschen" DIN-Kalenderwoche ist bei excelformeln.de beschrieben. Bei früheren Excel-Versionen ist das etwas kompliziert. Ab Excel 2010 funktioniert es mit

=KALENDERWOCHE(A1;21)

zum Seitenanfang



Mathematische Funktionen

In Excel sind bereits einige mathematische Funktionen vorhanden: WURZEL(), SIN(), COS(), LOG() usw. Sie werden besonders gerne auch von Schülern und Studenten genutzt. Manchmal funktionieren diese Funktionen aber nicht wie erwartet, oder man vermisst bzw. sucht Funktionen.

Potenzieren kann man mit der Funktion POTENZ(;) oder mit ^.
Hoch 3:

=POTENZ( A1 ; 3 )

=A1^3

Mit WURZEL() kann man Quadratwurzeln berechnen.
Für andere Wurzeln benutzt man die Funktion POTENZ(;) und dann den Kehrwert des Exponenten.
Dritte Wurzel:

=POTENZ( A1 ; 1/3 )

=A1^(1/3)

Die Klammer ist nötig, damit erst dividiert und dann potenziert wird.

Die Winkelfunktionen SIN(), COS(), TAN() usw. und ihre Umkehrfunktionen arbeiten bei Excel (wie in vielen anderen Programmiersprachen) im Bogenmaß. Dort entspricht ein Vollkreis 2π = 6,2832.
Um die gewohnten Werte zu erhalten, muss man erst Grad (in Zelle A1) in Bogenmaß umrechnen; dabei hilft die Excelfunktion PI(), die die Zahl π liefert:

=SIN( A1 / 180 * PI() )

=COS( A1 / 180 * PI() )

Für die Umkehrfunktionen ARCSIN(), ARCCOS(), ARCTAN() usw. muss man den erhaltenen Winkel von Bogenmaß in Grad umrechnen:

=ARCSIN( B1 ) / PI() * 180

=ARCCOS( B1 ) / PI() * 180


Logarithmen können erzeugt werden mit:
• LN()   natürlicher Logarithmus zur Basis e
• LOG10()   dekadischer oder Brigg'scher Logarithmus zur Basis 10
• LOG( ; )   Logarithmus zu beliebiger Basis
Umkehrungen:
• EXP()   Potenz zur Basis e
• 10^   Potenz zur Basis 10
• ^ oder POTENZ(;)   beliebige Potenzen

Die Zahl e wird mit =EXP(1) erzeugt; die Zahl π mit =PI().

Die Betragsfunktion |x| heißt in Excel ABS().

Im Bereich Statistik gibt es die Funktionen MITTELWERT(), MEDIAN(); FAKULTÄT(), KOMBINATIONEN(n;k), VARIATIONEN(n;k), KOMBINATIONEN(n;k) usw.

VARIATIONEN(n;k) entspricht n!/(n-k)! "Ziehen ohne Zurücklegen, bei dem die Reihenfolge der gezogenen Zahlen relevant ist";
Beispiel: Chips im Wert von 1 bis 9 werden gemischt, 3 Chips werden nacheinander gezogen und nebeneinander zu einer dreistelligen Zahl gelegt. Die Wahrscheinlichkeit für eine bestimmte Zahl beträgt VARIATIONEN(9;3).

KOMBINATIONEN(n;k) entspricht n!/( (n-k)!*k! ) "Ziehen ohne Zurücklegen, bei dem die Reihenfolge der gezogenen Zahlen egal ist".
Beispiel mit Lotto 7 aus 49: KOMBINATIONEN(49;7)

Bei MITTELWERT() werden auch Null und negative Zahlen berücksichtigt. Wenn man das nicht will, hilft diese Methode:

=SUMME(A1:A15) / ZÄHLENWENN(A1:A15;">0")

zum Seitenanfang



Funktionen zeichnen

Mit den Liniendiagrammen in Excel lassen sich Funktionen recht gut zeichnen. Diese Möglichkeit ist nicht nur bei Schülern und Studenten sehr beliebt.

Man erstellt dazu eine Wertetabelle (X-Werte z. B. in Spalte A, Y-Werte in Spalte B), z. B. mit X-Schrittweite 0,1. Danach gibt man den Spalten die Überschriften X und Y oder X und f(x), markiert die Tabelle und macht ein Liniendiagramm draus. In Zelle B2 Könnte dann für ein Polynom x²+2x+4 die Formel =A2^2+A2*2+4 oder =(A2+2)^2 stehen oder für einen Sinus im Gradmaß: =SIN(A2/180*PI()).

Wenn die von Punkt zu Punkt führende Linie (für Parabeln und andere Funktionen) zu eckig ist, kann man die Linie im Diagramm anklicken, und im Fenster "Datenreihen formatieren" das 'Linie glätten' aktivieren.
In OpenOffice Calc kann man bei der Auswahl des Diagrammtyps für Liniendiagramme die Kurvenglättung aktivieren.

Weiter unten in dieser Seite ist erklärt, wie man im Excel Zellen mit Null oder leere Zellen in Liniendiagrammen ausblenden kann.
zum Seitenanfang



Kommentare zu Excel-Zellen

Besonders wenn mehrere Menschen die selbe Excel-Tabelle benutzen, kann es zu Missverständnissen oder Unklarheiten bei der Nutzung kommen. Wenn man bestimmte Tabellen selten benutzt, will man sich vielleicht auch selbst ein paar Notizen hinterlegen.

Eine Möglichkeit wäre es, den Druckbereich der Tabelle festzulegen und außerhalb des Druckbereichs neben oder unter der Tabelle Kommentare und Anleitungen in einer anderen Farbe und Schriftart zu schreiben.

Excel-Zelle mit Kommentar Man kann aber auch zu einzelnen Zellen Kommentare hinzufügen:
Zellen mit Kommentaren sind mit einem kleinen roten Dreieck markiert. Die Kommentare werden beim Berühren der Zelle mit dem Mauszeiger eingeblendet, aber nicht mit der Tabelle gedruckt.

Kommentare können auch per Programm/Makro mit Excel VBA erzeugt werden. Beispiel:

Range("B7").Select With Selection 'Kommentar erzeugen und für den Normalfall ausblenden .AddComment .Comment.Visible = False 'Zeilenumruch mit Chr(10) .Comment.Text Text:="erste Zeile" & Chr(10) & "zweite Zeile" End With

zum Seitenanfang



Hyperlinks einfügen

In Word, Excel und anderen Office-Anwendungen besteht die Möglichkeit, Hyperlinks einzufügen. Man kann dem Leser/Benutzer dadurch die Möglichkeit geben, zugehörige Dokumente aufzurufen, zu passenden Internetseiten zu springen oder Erklärungen zu dem im Moment betrachteten Dokument zu lesen.

Einfügen eines Hyperlinks in einer Excel-Zelle:
Auch in Excel VBA steht eine Funktion zur Verfügung, mit der man Hyperlinks erzeugen kann. Wenn in der Zelle A3 ein Hyperlink erzeugt weden soll, könnte das z. B. so aussehen:

'Pfad und Mappenname ZielMappe = "C:\Eigene Dateien\Meine ExcelMappe.xls" ZielBlattname = "Tabelle3" ZielZelle = "B7" MeinLinktext = "Link zu was auch immer" 'Hyperlink in Zelle A3 einfügen ActiveSheet.Range("A3").Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, _ Address:=ZielMappe, _ SubAddress:="'" & ZielBlattname & "'!" & ZielZelle, _ TextToDisplay:=MeinLinktext, _ ScreenTip:="Klick mich!"

zum Seitenanfang



Hyperlinks in Excel dauerhaft formatieren

Eingegebene Hyperlinks (E-Mail-Adressen oder Homepage-Adressen) werden von Excel automatisch als Link erkannt und blau unterstrichen formatiert. Man kann die Formatierung der Zelle danach ändern, bei einer Korrektur des Inhalts erscheint aber immer wieder die frühere Formatierung. Dies lässt sich nur folgendermaßen ändern:
Excel 2003: Betroffene Zellen markieren, Menü Format / Formatvorlagen ... / bei 'Name der Formatvorlage' "Hyperlink" auswählen / Schaltfäche 'Ändern' anklicken / dann die Formatvorlage nach Wunsch formatieren
Excel 2007: Betroffene Zellen markieren, Register "Start" / Befehlsgruppe "Start" Zellenformatvorlagen. Dort Rechtsklick auf die Formatvorlage für Hyperlinks; Formatvorlage ändern.
zum Seitenanfang



Hyperlinks zu Excel 2007 (.xlsx) in HTML

Wenn man Exceldateien (.xls) zum Download anbietet, kann man sie einfach mit z. B.

<a href="Dateineme.xls" target="_blank">diese Datei</a>

in eine HTML-Homepageseite stellen. Sie wird dann beim Seitenbesucher (falls er Excel oder OpenOffice hat) problemlos geöffnet.

Bei einer Excel2007-Datei (.xlsx) kommt es - selbst wenn Excel 2007 instaliert ist - zu Problemen: Sie wird von manchen Browsern für eine .zip-Datei gehalten und völlig 'zerlegt' angezeigt. Mit Word2007-Dateien (.docx) passiert Ähnliches.
Abhilfe für die nächsten Jahre, bis alle Browser mit diesen Dateien umgehen können: Entweder Befehle in eine .htaccess-Datei setzen, die dem User-PC mitteilen, was er mit den machen soll:

AddType application/vnd.ms-word.document.macroEnabled.12 .docm AddType application/vnd.openxmlformats-officedocument.wordprocessingml.document docx AddType application/vnd.openxmlformats-officedocument.wordprocessingml.template dotx AddType application/vnd.ms-powerpoint.template.macroEnabled.12 potm AddType application/vnd.openxmlformats-officedocument.presentationml.template potx AddType application/vnd.ms-powerpoint.addin.macroEnabled.12 ppam AddType application/vnd.ms-powerpoint.slideshow.macroEnabled.12 ppsm AddType application/vnd.openxmlformats-officedocument.presentationml.slideshow ppsx AddType application/vnd.ms-powerpoint.presentation.macroEnabled.12 pptm AddType application/vnd.openxmlformats-officedocument.presentationml.presentation pptx AddType application/vnd.ms-excel.addin.macroEnabled.12 xlam AddType application/vnd.ms-excel.sheet.binary.macroEnabled.12 xlsb AddType application/vnd.ms-excel.sheet.macroEnabled.12 xlsm AddType application/vnd.openxmlformats-officedocument.spreadsheetml.sheet xlsx AddType application/vnd.ms-excel.template.macroEnabled.12 xltm AddType application/vnd.openxmlformats-officedocument.spreadsheetml.template xltx

oder (einfacher und funktionssicherer): Die Excel2007-Datei als zip-Datei komprimieren und es dem PC des Users überlassen, richtig mit ihr umzugehen.
zum Seitenanfang



Mit SVERWEIS() und INDIREKT() auf andere/externe Tabellen oder Mappen zugreifen

Mit SVERWEIS() kann man auf auf andere Tabellen oder sogar Tabellen in anderen Mappen zugreifen. Beim Zugriff auf eine andere Mappe muss diese geöffnet sein. Mit ein paar selbstgebauten Beispielen können Sie das rasch ausprobieren.
Eventuell möchte man nun die in SVERWEIS() angegebene Matrix variabel, also in einer Zelle angeben können. Dazu kann die Funktion INDIREKT() in SVERWEIS eingesetzt werden:
Wenn z. B. in K8 ein '[MeinePreislisten.xls]Preisliste2011'!A2:D100 steht, liefert INDIREKT(K8) den korrekten Bezug an SVERWEIS.
Falls der Inhalt für K8 verteilt in K5 bis K7 stünde (Mappe: MeinePreislisten.xls,Tabelle: Preisliste2011, Zellen: A2:D100), könnte man das mit in K8 mit folgender Formel zusammensetzen:

=WENN( K5<>"";"'["&K5&"]";) & K6 & WENN(K5<>"";"'";) & "!" & K7

Wenn es sich bei der Kombinationen von Tabellen mit SVERWEIS z. B. um eine Rechnung handelt, in die Daten aus einer Preisliste eingesetzt werden, sollte man bedenken, dass sich bei einer Preislistenänderung alle bereits geschriebenen Rechnungen nachträglich ändern!
Abhilfe: Das Rechnungsformular mit einem Schreibschutz versehen und die einzelnen für Kunden erzeugten Rechnungen nur als PDF abspeichern oder nur als Ausdruck aufbewahren.
zum Seitenanfang



Blattname in eine Zelle übertragen

Jede Excelmappe besteht aus mehreren Blättern. Der jeweilige Blattname steht unten auf dem Reiter/Fähnchen. Eventuell möchte man diesen Blattnamen automatisch in eine Zelle übertragen. Noch vor Kurzem hätte ich sehr überzeugt behauptet: "Das geht nicht! Man kann den Blattnamen nur mit Menü Datei→'Seite einrichten' in der Kopf- oder Fußzeile anzeigen.".
Zum Übertragen in eine Zelle gibt es zwar keine fertige Excelfunktion oder Variable, aber trotzdem eine Möglichkeit. In der gewünschten Zelle gibt man folgendes ein:

=TEIL( ZELLE("dateiname";A1) ; FINDEN( "]";ZELLE("dateiname";A1) )+1 ; 255)

Erklärung: Die Excel-Funktion Zelle liefert Pfad, Dateiname und Blattname. Mit der Funktion TEIL(;) wird der Teil ab der eckigen Klammer "]" abgetrennt.




Leere Zellen im Liniendiagramm ausblenden

Diagramme erfordern Know How, Übung und Erfahrung. Manche Fragen bleiben aber trotzdem noch offen, z. B.:
Wie erreiche ich es, dass bei leeren Zellen oder Zellen mit Wert Null nicht in einem Liniendiagramm Null angezeigt, sondern die Linie fortgesetzt wird?

Hierzu wird zunächst dafür gesorgt, dass in diesen Zellen #NV (Fehlermeldung für ungültigen Wert) steht. Das kann durch eine Wenn-Funktion, die "#NV" in die Zelle schreibt, geschehen oder - zumindest für den Wert Null z. B. mit folgender benutzerdefinierter Formatierung:
0,00;- 0,00;"#NV";"#NV"

Mit 0,00;- 0,00;[Weiß]"#NV";[Weiß]"#NV" werden die #NV in der Tabelle unsichtbar (weiße Schrift auf weißem Grund).
Das Unsichtbarmachen kann auch mit bedingter Formatierung erreicht werden: Dazu die Zelle (z. B. B5) mit Formel =ISTFEHLER(B5) abfragen und dann die Schrift auf weiß setzen.

Ab Excel 2007 ist die Methode mit dem #NV nicht notwendig:
In OpenOffice Calc kann man nach Rechtsklick auf die Linie/Datenreihe bei 'Objekteigenschaften'/Optionen angeben, was bei leeren Zellen zu geschehen hat: Lücke lassen, Null annehmen oder Linie fortsetzen.
zum Seitenanfang



Alle Blanks löschen

Gelegentlich muss man in Excel aus einer Zeichenkette (String) die Leerzeichen/Blanks löschen. Die Funktion GLÄTTEN( ) löscht nur die Blanks am Anfang und am Ende eines Strings (auch "führende" und "folgende" Blanks genannt). Wenn alle Blanks gelöscht werden sollen, braucht man die Funktion Wechseln( ):

=WECHSELN(A1 ; " " ; "")

In Excel-VBA (Visual-Basic) löscht die vorhandene Funktion trim() auch nur die führenden und folgenden Blanks. Man kann sich eine Funktion zur Löschung aller Blanks selbst definieren:

Function TrimAll(ByVal a As String) As String 'alle Blanks entfernen a = Trim(a) 'erst links und rechts entfernen TrimAll = Replace(a, " ", "") 'restliche Blanks entfernen End Function

Erklärung: Hier wird mit WECHSELN bzw. Replace das Blank (zwischen den 'Gänsefüßchen') gegen 'Nix' ausgetauscht.




Fragen zu Excel

Hier einige Fragen, die während Excel-Kursen oder bei anderen Gelegenheiten gestellt wurden:

Allgemeine Fragen zu Excel

zum Seitenanfang

Formatierung von Zellen

zum Seitenanfang

Excel-Funktionen

zum Seitenanfang

Fragen zu Diagrammen

zum Seitenanfang

Fragen zur Excel2007/2010-Oberfläche

zum Seitenanfang

Fragen zum Download von Excel2007-Dateien

zum Seitenanfang

Fragen zum Speichern von Excel-Dateien

zum Seitenanfang

Fragen zu Makros

zum Seitenanfang

Beispiele, Informationsmaterial, Tutorials

Manchmal sucht man Informationen und Beispiele um selbestständig einen neuen Bereich im Excel kennenzulernen. Wenn man mit Google nach Begriffen wie Beispiele, Inforamationsmaterial oder Tutorial sucht, wird man meist rasch fündig. zum Seitenanfang






© Klicktipps® (www.Klicktipps.de):
Dieser Ausdruck ist nur für den eigenen Gebrauch freigegeben.
Eine Vervielfältigung ist nicht gestattet und auch wenig sinnvoll,
da sich der Inhalt durch Aktualisierungen häufig ändert.