Tipps Excel
Tipps & Tricks
Diagrammbezüge absolut definieren
Erstellen Sie ein Diagramm, erzeugt Excel die Bezüge zu jeder Datenreihe wie in folgendem Beispiel zu sehen:
=DATENREIHE(Tabelle1!$E$7;;Tabelle1!$E$8:$E$10;1)
Greifen Sie für das Diagramm zum Beispiel auf externe Daten zurück und wollen das Diagrammblatt an Fremde weitergeben, dann müssten Sie diese Daten einschließen. Andernfalls würde sich ein Fehler bei der Anzeige des Diagramms ergeben. Doch diese Weitergabe der Daten ist manchmal nicht möglich oder nicht gewünscht.
Mit der folgenden Methode wandeln Sie die Bezüge in Absolutwerte um. Danach stehen die für das Diagramm benötigten Daten direkt in der Datenreihendefinition.
- Markieren Sie die erste Datenreihe im Diagramm.
- In der Bearbeitungsleiste sehen Sie die Formel zum Erstellen dieser Reihe wie oben dargestellt.
- Markieren Sie diese Formel und drücken die Taste [F9].
- Excel hat den Bezug in Absolutwerte umgewandelt.
- Verfahren Sie für die anderen Reihen ebenso.
Anschließend ist keinerlei Bezug mehr zu den Daten im Tabellenblatt vorhanden. Eine Änderung der Daten im Tabellenblatt hat demnach keine Auswirkungen auf das Diagramm. Sie können dieses Diagramm jetzt auf ein leeres Tabellenblatt verschieben und wie jedes andere Diagramm formatieren.
=DATENREIHE("Wert1";{1.2.3};{100.200.300};1)
Datenänderungen sind über Anpassungen der Datenreihenformel in der Bearbeitungsleiste weiterhin möglich.
Namen benannter Bereiche anzeigen lassen
Bekanntlich können Sie einzelne Zellen oder ganze Bereiche mit Namen versehen. Wenn Sie eine Übersicht über alle benannten Bereiche benötigen, dann stellen Sie einfach den Zoomfaktor auf den Wert 39% oder kleiner ein. Die Zellbereiche werden daraufhin inklusive ihrer Namen angezeigt.
Ein zweiter Weg bietet sich über das Register Formeln, Abschnitt Definierte Namen, Schaltfläche In Formeln verwenden an. Nach dessen Aufruf wählen Sie den Eintrag Namen einfügen und dann Liste einfügen. Excel fügt daraufhin an der aktuellen Zelle eine Liste aller mit Namen versehenen Bereiche ein.
Zellinhalte verbergen
Sie wollen den Inhalt von Zellen in einer Excel-Tabelle beim Ausdruck verbergen. Die Veränderung der Schriftfarbe auf Weiß ist eine mögliche Variante, die aber Probleme beim Ausdruck bereiten kann.
Eine Möglichkeit ohne Nebeneffekte ist das Unsichtbar-Formatieren der Inhalte. Markieren Sie dazu die betreffenden Zellen und öffnen den Format-Dialog.
Wechseln Sie auf die Registerkarte Zahlen. Wählen Sie unter Kategorie den Eintrag Benutzerdefiniert. Unter Formate tippen Sie drei Strichpunkte/Semikola (;;;) ein. Nach dem Bestätigen mit OK sind die Zellen optisch verschwunden. In der Bearbeitungsleiste sind die Inhalte aber noch zu sehen. Selbstverständlich lassen sich auch ausgeblendete Zelleninhalte in Formeln und Funktionen verwenden.
Arbeitszeit über einen Tagwechsel richtig berechnen
Geht Ihre Arbeitszeit in einer Nachtschicht über Mitternacht hinaus, wird die Berechnung der Zeiten nicht korrekt durchgeführt.
Bei Arbeitszeiten innerhalb eines Tages genügt es, den Zeitpunkt des Arbeitsanfangs vom Arbeitsende abzuziehen. Liegt das Arbeitsende jedoch nach 24:00 Uhr, kommt auf diese Art kein vernünftiges Ergebnis heraus.
Verwenden Sie die folgende Formel zur Lösung des Problems. Dabei enthält im Beispiel die Zelle C1 die Anfangszeit, D1 die Endezeit.
=WENN(C1<=D1; D1-C1; "24:00"-C1+D1)
Alternativ können Sie auch so rechnen:
=WENN(C1<D1;D1+1-C1;D1-C1)
Eingabe von Brüchen
Excel kann in der Bearbeitungszeile durchaus mit Brüchen umgehen. Die automatische Formaterkennung wandelt ein 3/4 jedoch leider in den 3. April um. Bei anderen Brüchen sieht es ähnlich aus.
Sie können dies umgehen, wenn Sie vor den Bruch eine 0 mit Leerzeichen, also z.B. 0 3/4 eingeben. Die Anzeige des 3/4 bleibt erhalten, in der Bearbeitungszeile erscheint jetzt aber korrekterweise 0,75.
Anzahl von Arbeitstagen vorwärts/rückwärts rechnen
Wenn Sie in einer Datumsberechnung von einem bestimmten Tag ausgehend eine Anzahl von Arbeitstagen vorwärts oder rückwärts rechnen wollen, dann verwenden Sie hierfür die Funktion
ARBEITSTAG(Ausgangsdatum;Tage;[Freie_Tage])
oder besser die neuere Funktion
ARBEITSTAG.INTL(Ausgangsdatum;Tage;[Wochenende];[Freie_Tage])
Die Parameter bedeuten:
- Ausgangsdatum
Ausgangsdatum für die Berechnung - Tage
Gewünschte Anzahl der Arbeitstage hinter dem Startdatum zur Berechnung des Zieldatums.
Geben Sie also zum Beispiel 5 an, erhalten Sie den Termin, der 5 Arbeitstage hinter dem Startdatum liegt.
Dieser Wert kann auch negativ sein. In diesem Fall wird rückwärts gerechnet. - [Wochenende]
Mit diesem optionalen Parameter legen Sie die freien Tage fest. Wird nichts angegeben, gelten Samstag und Sonntag als freie Tage.
Die freien Tage lassen sich über numerische oder Textwerte festlegen. Schlagen Sie die Eingabemöglichkeiten bei Bedarf in der Funktionshilfe nach. - [Freie Tage]
Mit diesem optionalen Parameter übergeben Sie der Funktion eine Liste sonstiger freier Tage, wie Feiertage, Urlaubszeiten etc. Diese werden bei der Berechnung ebenfalls berücksichtigt.
Sollten Sie nach der Eingabe der Formel die Fehlermeldung #NAME erhalten, sind die sogenannten Analysefunktionen nicht aktiviert. Sie können das ganz einfach nachholen, indem Sie in den Optionen das Add-In anwählen und aktivieren.
Datum nach Tag und Monat sortieren
Wenn Sie z.B. in einer Geburtstagsliste die einzelnen Daten nach Tag und Monat sortieren wollen, die Jahre aber unberücksichtigt bleiben sollen, dann rechnen Sie vor dem Sortieren das Datum nach folgender Formel in einer Hilfsspalte um:
=MONAT(Datum)*100+Tag(Datum)
Lassen Sie sich nicht durch das etwas seltsam anmutende Hilfsdatum verwirren. Die Sortierspalte können Sie anschließend vor dem Druck ausblenden.
Dezimalzahlen am Komma ausrichten
Excel bietet die Möglichkeit, über ein benutzerdefiniertes Zahlenformat Dezimalzahlen am Komma auszurichten.
- Markieren Sie die auszurichtenden Zellen.
- Wählen Sie im Formatdialog, Register Zahlen den Typ Benutzerdefiniert. Geben Sie in das Eingabefeld die folgende Zeichenfolge ein. Beachten Sie, dass die Anzahl der Fragezeichen abhängig von der Anzahl der maximalen Nachkommastellen in der Spalte ist.
0,????
Alter berechnen aus Geburts- und aktuellem Datum
Bei der Berechnung des Alters reicht es nicht, einfach die Jahre voneinander abzuziehen. Ebenso ist der Monat und der Tag zu berücksichtigen. Hierfür bietet Excel die undokumentierte Funktion
DATEDIF(altesDatum;jüngeresDatum;Art)
In A1 steht das Geburtsdatum, in B1 das aktuelle Datum (z.B. per Funktion HEUTE()).
Mögliche Parameter für die Art:
- y - Gibt die Anzahl der vollständigen Jahre (year) im angegebenen Zeitraum aus,
- m - Liefert die vollständigen Monate (month) im angegebenen Zeitraum.
- d - Gibt die Anzahl der Tage (day) im Zeitraum an.
- md - Liefert die Differenz zwischen den Tagen im Anfangs- und Enddatum. Die Monate und Jahre der Datumsangaben werden ignoriert.
- ym - Liefert die Differenz zwischen den Monaten im Anfangs- und Enddatum. Die Tage und Jahre der Datumsangaben werden ignoriert.
- yd - Liefert die Differenz zwischen den Tagen im Anfangs- und Enddatum. Die Jahre der Datumsangaben werden ignoriert.
=DATEDIF(A1;B1;"y")
Beispiel: A1 beinhaltet das Datum 1.11.2002, A2 beinhaltet das Datum 12.12.2015
- y ergibt die Zahl 13 (ganze Jahre zwischen den beiden Daten; 2002-2015)
- m ergibt die Zahl 157 (ganze Monate zwischen den beiden Daten)
- d ergibt die Zahl 4789 (Tage zwischen den beiden Daten)
- md ergibt die Zahl 11 (Differenz zwischen den Tagen ohne Monate/Jahre; 1-12)
- ym ergibt die Zahl 1 (Differenz zwischen den Monaten 11 und 12; ohne Tage und Jahre zu berücksichtigen)
- yd ergibt die Zahl 41 (Differenz zwischen dem 1.11. und dem 12.12. ohne Berücksichtigung der Jahre)
Hundertstel Sekunden anzeigen
Für sehr genaue Zeiterfassungen (z.B. Messreihen) kann es notwendig sein, die Angabe der Hundertstel-Sekunden zu erhalten. Kein Problem mit Excel.
Geben Sie z.B. in eine Zelle die folgende Funktion ein
=JETZT()
und formatieren diese mit dem benutzerdefinierten Zahlenformat
m:ss,00
Minuszeichen an den Anfang stellen
Wenn Sie Zahlen erhalten, bei denen das Vorzeichen hinter der Zahl steht, also z.B. 100-, dann erkennt Excel diesen Wert nicht als Zahl, sondern interpretiert ihn als Text. Rechnen lässt sich damit nicht.
Mit der folgenden Formel korrigieren Sie dies. Im Beispiel steht die Zahl in Zelle A1:
=WENN(RECHTS(A1;1)="-";LINKS(A1;LÄNGE(A1)-1)*(-1);A1)
Minimum suchen, ohne Nullwerte zu beachten
Wenn Sie eine Tabelle nach dem kleinsten Wert durchsuchen wollen, Nullwerte dabei aber unberücksichtigt bleiben sollen, dann hilft nur eine Formel weiter. Die betreffenden Zahlen stehen in den Zellen A1 bis A10.
=MIN(WENN(A1:A10>0;A1:A10))
Beachten Sie dabei, die Zeile als Matrixformel einzugeben. Das bedeutet, dass Sie zur Bestätigung der Eingabe die Tastenkombination [Strg]+[Umschalt]+[Return] betätigen. Excel setzt danach die gesamte Formel in geschweifte Klammern. Diese dürfen nicht von Hand eingegeben werden. Die fertige Formel sieht demnach wie folgt aus:
{=MIN(WENN(A1:A10>0;A1:A10))}
Ersetzen Sie > durch <>, um auch negative Werte zu erfassen und nur die Nullwerte auszuschließen.
Namen des aktuellen Tabellenblattes in Zelle ausgeben
Wenn Sie den Namen der Arbeitsmappe, der Tabelle oder des Pfades der Datei benötigen, dann hilft Ihnen die Funktion ZELLE weiter.
=zelle("dateiname")
Die Anführungszeichen sind dabei mit einzugeben! Außerdem muss die Datei bereits gespeichert worden sein, damit ein Ergebnis erscheint, das dann z.B. wie folgt aussieht:
C:\Rechnen\[test.xls]Tabelle1
Wie Sie sehen, werden die Angaben zum Laufwerk sowie den Ordnern in windows-typischer Schreibweise ausgegeben. Der Dateiname selbst steht in eckigen Klammern. Die letzte Angabe betrifft den Tabellenblattnamen.
Wollen Sie also Teile dieser Ausgabe erhalten, z.B. nur den eigentlichen Dateinamen ohne Pfadangaben oder nur den Tabellenblattnamen, so müssen wir diese aus der Gesamtangabe ausschneiden.
Dazu berechnen wir, wo die eckigen Klammern stehen. Zwischen diesen steht der Dateiname, nach ihnen der Tabellenblattname. Zum Berechnen verwenden wir die Funktion FINDEN, die als Ergebnis die Position der Fundstelle liefert. Die Angabe in Gänsefüsschen stellt den zu suchenden Begriff dar, in unserem Fall die öffnende eckige Klammer. Nach dem Semikolon wird angegeben, wo gesucht werden soll. Als Ergebnis erhalten wir bei obiger Konstellation den Wert 12.
=FINDEN(“[“;ZELLE(“dateiname”))
Gleiches machen wir mit der schließenden eckigen Klammer. Aus diesen beiden Werten berechnen wir die Anzahl Zeichen, die dazwischen stehen, also die Länge des Dateinamens. Hierbei dürfen wir nicht vergessen, dem ersten Wert eins zuzuschlagen (da der Name ja nach der Klammer steht) und dem zweiten Wert eins abzuziehen (da der Name vor der Klammer aufhört). Den daraus resultierenden Wert verwenden wir mit der Textfunktion TEIL, die aus einem Text einen Teil von bestimmter Länge ab einer bestimmten Stelle extrahiert. Die fertige Funktion lautet (in einer Zeile geschrieben):
=TEIL(ZELLE("dateiname"); FINDEN("[";ZELLE("dateiname"))+1;
FINDEN("]";ZELLE("dateiname"))-1- FINDEN("[";ZELLE("dateiname")))
Die erste Funktion ZELLE liefert als Ergebnis den Gesamtnamen incl. Pfad und Tabellenname. Die erste FINDEN-Funktion ermittelt die Startposition des Dateinamens, die zweite die Länge des Dateinamens. Die dritte FINDEN liefert letztlich die Länge, die für die Funktion TEIL benötigt wird.
Wollen wir nur den Tabellennamen ausgeben, sieht die Formel ein wenig einfacher aus. Hierbei brauchen wir nur den Gesamtnamen von rechts bis zur schließenden eckigen Klammer abschneiden:
=RECHTS(ZELLE("dateiname"); LÄNGE(ZELLE("dateiname"))- FINDEN("]";ZELLE("dateiname")))
Zeilenumbruch für Datumsangabe
Wenn Sie in einer Zelle ein Datum in einer langen Form ausgeben, das nicht in die Zelle passt, erhalten Sie die Überlaufzeichen # angezeigt. Ein Umstellen des Zeilenumbruchs im Formatmenü ändert daran nichts.
Sie können diesen Umstand jedoch ändern, wenn Sie das Datum zur Ausgabe mittels der Funktion TEXT in einen Text konvertieren. Dieser kann umgebrochen werden. Als Parameter erwartet die Funktion den umzuwandelnden Wert (in unserem Beispiel ein Datum) sowie Formatierungskennzeichen, die angeben, wie die Ausgabe erfolgen soll. Anschließend können Sie den Textumbruch im Formatmenü noch angeben, um den Umbruch zu erhalten.
=TEXT(JETZT();"TTTT TT. MMMM JJJJ hh:mm")
Größte/Kleinste Werte aus einem Bereich ermitteln
Wenn Sie aus einem Bereich die größte bzw. die kleinste Zahl ermitteln wollen, verwenden Sie die Funktionen MAX bzw. MIN. Wollen Sie jedoch die drei größten/kleinsten herausfinden, dann helfen diese Funktionen nicht weiter.
Dieses Problem lösen Sie mit den Funktionen KGROESSTE bzw. KKLEINSTE. In einem Beispiel haben Sie Daten im Bereich A1:A50 eingegeben. Jetzt möchten sie die drei höchsten Werte ermitteln. Geben Sie dazu die folgenden Tabellenfunktionen ein:
=KGROESSTE($A$1:$A$50;1)=KGROESSTE($A$1:$A$50;2)=KGROESSTE($A$1:$A$50;3)
Im ersten Argument geben Sie den Bereich an, aus dem die Maximal-Werte ermittelt werden sollen. Im zweiten Argument geben Sie den Rang an.
Anzahl der Werte größer Null ermitteln
Wenn Sie in einer Tabelle die mit Zahlen belegten Zellen zählen wollen, verwenden Sie die Funktion ANZAHL. Wollen Sie jedoch die mit 0 belegten Zellen unberücksichtigt lassen, hilft diese Funktion nicht weiter, da sie alle Zellen zählt, deren Inhalt numerisch ist - also auch die 0.
Setzen Sie statt dessen die Funktion ZÄHLENWENN bzw. ZÄHLENWENNS ein. Wie der Name sagt, zählt sie in einem Zellbereich, wobei eine Bedingung für das Zählen integriert werden kann.
=ZÄHLENWENN(A1:A10;"<>0") bzw. =ZÄHLENWENNS(A1:A11;"<>0")
Diese Funktion zählt alles im Bereich A1-A10, wobei die 0-Werte ausgeklammert werden.
Wollen Sie nur positive Zahlen zählen, dann lautet die Formel
=ZÄHLENWENN(A1:A10;">0") bzw. =ZÄHLENWENNS(A1:A10;">0")
bei Beschränkung auf negative Werte
=ZÄHLENWENN(A1:A10;"<0") bzw. =ZÄHLENWENNs(A1:A10;"<0")
Eine ähnliche Funktion, die jedoch summiert anstatt zu zählen ist SUMMEWENN bzw. SUMMEWENNS. Sie funktioniert analog der ZÄHLENWENN. Um z.B. nur die positiven Werte zu summieren, verwenden Sie
=SUMMEWENN(A1:A10;">0") bzw. =SUMMEWENNS(A1:A10;">0")
Nur sichtbare Zellen kopieren
Mit dem AutoFilter von Excel können Sie Listen komfortabel sortieren und abfragen. Wenige Mausklicks genügen beispielsweise, um nur die Bestellungen eines bestimmten Tages oder eines Kunden anzuzeigen. Wenn Sie ein solches Filterergebnis kopieren möchten, markieren Sie einfach die angezeigten Datensätze, kopieren diesen in die Zwischenablage und fügen den Bereich in eine Tabelle oder ein anderes Programm ein.
Das klappt allerdings nur, weil Excel automatisch eingreift und die korrekte Markierung für Sie vornimmt. Tatsächlich handelt es sich bei dem Ergebnis eines AutoFilters um nichts anderes als die komplette Ausgangsliste mit ausgeblendeten Zeilen. Beim Kopieren erkennt Excel, dass ein AutoFilter eingeschaltet ist und schließt die ausgeblendeten Zeilen von der Markierung aus.
Leider lässt sich der AutoFilter nicht für alle Tabellen einsetzen und so gibt es Situationen, in denen Sie Zeilen und Spalten entweder manuell oder beispielsweise mit der Gliederungsfunktion ausblenden. In diesen Fällen führen Kopiervorgänge aber zu unerwünschten Ergebnissen, denn Excel kopiert dann auch die ausgeblendeten Zellen. Mit der folgenden Methode klappt es trotzdem:
Nachdem Sie den gewünschten Bereich mit sichtbaren und ausgeblendeten Zellen markiert haben, wählen Sie im Register Start, Abschnitt Bearbeiten den Befehl Suchen und Auswählen und darin Inhalte auswählen. Im sich öffnenden Dialog wählen Sie die Option Nur sichtbare Zellen.
Formatierte Zellen mit Text kombinieren
In Excel ist es problemlos möglich, Texte und Zahlen miteinander zu verbinden. Hierzu verwenden Sie einfach das Verkettungssymbol &. So liefert z.B. die folgende Formel die Ausgabe Summe: 1000.
="Summe: " & SUMME(A1:A10)
Als Zahlen können einfache Zellbezüge, direkt eingegebene Werte oder auch Formelergebnisse zum Einsatz kommen. Was dabei leider nicht möglich ist, ist die Formatierung des anzuzeigenden Zahlenwertes. Das Format-Menü hilft hier nicht weiter, da der Zellinhalt für Excel eine Zeichenkette (Text) darstellt, der über das Zahlen-Format nicht beeinflussbar ist..
Wir können uns jedoch behelfen, wenn wir zusätzlich die Funktion TEXT verwenden, mit deren Hilfe eine Zahl formatiert werden kann und erst anschließend in eine Zeichenkette umgewandelt wird. Hierfür benötigt die Funktion die (berechnete) Zahl sowie das Zahlenformat analog der Form, wie es im Format-Dialog definiert wird. Also zum Beispiel:
="Summe: " & TEXT(SUMME(A1:A10);"#.##0,00 EUR")
In der Tabelle erscheint das Ergebnis dann zum Beispiel so:
Summe: 1.234,56 EUR
Genaue Anzahl von Monaten addieren/abziehen
Sie haben eine Liste mit Daten. Auf diese Daten wollen Sie eine bestimmte Anzahl von Monaten addieren, um ein neues Datum zu erhalten. Genau für diesen Zweck wurde die Funktion EDATUM gemacht. Diese gehört jedoch zum Add-In Analyse-Funktionen, das Sie ggf. zuvor über die Optionen aktivieren müssen.
=EDATUM(A1;15)
Der erste Parameter (hier A1) enthält das zu berechnende Datum bzw. eine Bezug zu einer Zelle mit einem Datum, der zweite die Anzahl der Monate, die dem Datum hinzugerechnet werden sollen. Wollen Sie eine Anzahl Monate zurück rechnen, geben Sie als zweiten Parameter einen negativen Wert an.