Logo vorlab.de - Das Vorlagenlabor

Home > Excel Tipps, Tricks & Phänomene

Excel Tipps, Tricks & Phänomene

Aus der Bearbeitung eigener Aufgabenstellungen in Excel resultieren verschiedene Tipps, Tricks, Formeln oder selbstgebaute Funktionen, die hier in loser Folge erscheinen. Die Palette reicht von einfachen Tipps bis zu professionellen Tricks und von einfachen Formeln bis zu benutzerdefinierten Funktionen.
Manchmal geschieht in Excel Unerwartetes und man stößt auf eigenartige Phänomene. Einige davon sollen hier beleuchtet werden. Beim näheren Betrachten wird sich herausstellen, dass es sich nicht um die viel beschworenen Excel-Bugs handelt. Oft handelt es sich nur um Probleme, die sich bei besserer Dokumentation in der Excel-Hilfe vermeiden ließen, und manches hängt einfach damit zusammen, dass Computer zwar vermeintlich gut rechnen können, aber von Mathematik eigentlich keine Ahnung haben.

Inhalt

Das OsterLAMBDA

19.03.2024
Ostern steht vor der Tür. Wir kennen den Termin. Und wenn nicht, erinnert uns der Einzelhandel daran. Wer aber in Excel Kalender erstellt, die in jedem Jahr gültig sein sollen, kommt nicht umhin, das Osterdatum zu berechnen. Die Rechenschritte dazu habe ich bereits in einem früheren Beitrag vorgestellt. Die dort gezeigte schrittweise Berechnung des Osterdatums ist jedoch ungeeignet, wenn ich möglichst keinen Raum für Nebenrechnungen verschwenden will, und etwas umständlich, wenn ich sie in verschiedenen Arbeitsmappen wiederverwenden will. Eine Möglichkeit wäre, diese Berechnung in einer einzigen Formel zusammenzufassen. Diese würde, wenn in C9 das Jahr steht, so aussehen:

=DATUM(C9;3;21+REST(19*REST(C9;19)+15+GANZZAHL((3*GANZZAHL(C9/100)+3)/4)-GANZZAHL((8*GANZZAHL(C9/100)+13)/25);30)-(GANZZAHL(REST(19*REST(C9;19)+15+GANZZAHL((3*GANZZAHL(C9/100)+3)/4)-GANZZAHL((8*GANZZAHL(C9/100)+ 13)/25);30)/29)+(GANZZAHL(REST(19*REST(C9;19)+15+GANZZAHL((3*GANZZAHL(C9/100)+3)/4)-GANZZAHL((8*GANZZAHL(C9/100)+13)/25);30)/28)-GANZZAHL(REST(19*REST(C9;19)+15+GANZZAHL((3*GANZZAHL(C9/100)+3)/4)-GANZZAHL((8*GANZZAHL(C9/100)+ 13)/25);30)/29))*GANZZAHL(REST(C9;19)/11))+7-REST(21+REST(19*REST(C9;19)+15+GANZZAHL((3*GANZZAHL(C9/100)+3)/4)-GANZZAHL((8*GANZZAHL(C9/100)+13)/25);30)-(GANZZAHL(REST(19*REST(C9;19)+15+GANZZAHL((3*GANZZAHL(C9/100)+3)/4)- GANZZAHL((8*GANZZAHL(C9/100)+13)/25);30)/29)+(GANZZAHL(REST(19*REST(C9;19)+15+GANZZAHL((3*GANZZAHL(C9/100)+3)/4)-GANZZAHL((8*GANZZAHL(C9/100)+13)/25);30)/28)-GANZZAHL(REST(19*REST(C9;19)+15+GANZZAHL((3*GANZZAHL(C9/100)+3)/4)- GANZZAHL((8*GANZZAHL(C9/100)+13)/25);30)/29))*GANZZAHL(REST(C9;19)/11))-(7-REST(C9+GANZZAHL(C9/4)+2-GANZZAHL((3*GANZZAHL(C9/100)+3)/4);7));7))

Es ist offensichtlich, dass diese Formel viel zu sperrig zum Eintippen ist. Und beim Kopieren, muss ich 30 Mal das Argument C9 anpassen. Fehlern sind hier Tür und Tor geöffnet. Wie wäre es mit einer Funktion, bei der ich nur einmal das Jahr als Argument übergeben muss? Diese Funktion soll meinetwegen OSTERSTG heißen. Und hier kommt LAMBDA ins Spiel. Was LAMBDA ist und wie es funktioniert habe ich hier schon einmal beschrieben.
Deshalb an dieser Stelle nur eine Kurzanleitung:
Öffne den Namens-Manager im Menüband Formeln und gehe zu Neu...
Im Dialogfenster gibst Du bei Name: den gewünschten Funktionsnamen ein (hier OSTERSTG, Du kannst aber einen beliebigen selbstgewählten Namen unter Beachtung der Excel-Syntaxregeln eintragen).
Dann gibst Du unter Bezieht sich auf: die LAMBDA-Funktion in dieser Form ein:

=LAMBDA(jahr;DATUM(jahr;3;21+REST(19*REST(jahr;19)+15+GANZZAHL((3*GANZZAHL(jahr/100)+3)/4)-GANZZAHL((8*GANZZAHL(jahr/100)+13)/25);30)-(GANZZAHL(REST(19*REST(jahr;19)+15+GANZZAHL((3*GANZZAHL(jahr/100)+3)/4)-GANZZAHL((8 *GANZZAHL(jahr/100)+13)/25);30)/29)+(GANZZAHL(REST(19*REST(jahr;19)+15+GANZZAHL((3*GANZZAHL(jahr/100)+3)/4)-GANZZAHL((8*GANZZAHL(jahr/100)+13)/25);30)/28)-GANZZAHL(REST(19*REST(jahr;19)+15+GANZZAHL((3*GANZZAHL(jahr/100)+3)/4)- GANZZAHL((8*GANZZAHL(jahr/100)+13)/25);30)/29))*GANZZAHL(REST(jahr;19)/11))+7-REST(21+REST(19*REST(jahr;19)+15+GANZZAHL((3*GANZZAHL(jahr/100)+3)/4)-GANZZAHL((8*GANZZAHL(jahr/100)+13)/25);30)-(GANZZAHL(REST(19*REST(jahr;19)+1 +GANZZAHL((3*GANZZAHL(jahr/100)+3)/4)-GANZZAHL((8*GANZZAHL(jahr/100)+13)/25);30)/29)+(GANZZAHL(REST(19*REST(jahr;19)+15+GANZZAHL((3*GANZZAHL(jahr/100)+3)/4)-GANZZAHL((8*GANZZAHL(jahr/100)+13)/25);30)/28)-GANZZAHL(REST(19 *REST(jahr;19)+ 15+GANZZAHL((3*GANZZAHL(jahr/100)+3)/4)-GANZZAHL((8*GANZZAHL(jahr/100)+13)/25);30)/29))*GANZZAHL(REST(jahr;19)/11))-(7-REST(jahr+GANZZAHL(jahr/4)+2-GANZZAHL((3 *GANZZAHL(jahr/100)+3)/4);7));7)))

Die Formel beginnt mit =LAMBDA(jahr;". Der erste Parameter jahr ist der Bezeichner für das Argument, das später beim Funktionsaufruf übergeben wird. Er muss in der folgenden Rechenvorschrift in exakt dieser Schreibweise verwendet werden. Du kannst ihn auch Osterjahr oder MeinJahr oder einfach nur J nennen. Hauptsache Du übernimmst ihn so in die Rechenvorschrift.
Nach diesem Parameter folgt die Rechenvorschrift. Es ist die oben angezeigte Formel ohne das Gleichheitszeichen, wobei C9 durch den Parameter jahr ersetzt wird. Danach darf die schließende Klammer für die LAMBDA-Funktion nicht fehlen.
Klicke anschließend auf OK. Nun ist die Funktion fertig und in der Arbeitsmappe verfügbar. Zur Berechnung des Datums für den Ostersonntag ist nunmehr lediglich einzutragen: =OSTERSTG(C9).
Mehr Einzelheiten sind in der Datei osternmitlambda.xlsx zu sehen.

Pfeil nach oben

Wie funktioniert LAMBDA?

12.02.2024
Es ist kein Hexenwerk. Die neue Funktion LAMBDA (Office 365) bietet nicht mehr, aber auch nicht weniger als die Möglichkeit, benutzerdefinierte Funktionen zu erstellen. Damit erspart sie uns das Eintippen langer und komplizierter Formeln. Das verringert den Aufwand und vermeidet Fehlerquellen.
Außerdem werden weder VBA noch Makros benötigt und somit sind keine Programmierkenntnisse nötig.
Hier zeige ich es an einem einfachen Beispiel: In meiner Praxis benötige ich öfters eine Formel, mit der ich das Datum des ersten Tags des Monats berechnen kann. Für das Monatsende gibt es eine Funktion in Excel, für den Monatsanfang jedoch nicht.
In der abgebildeten Excel-Tabelle steht in C2 ein Datum. Zu diesem möchte ich den Monatsersten wissen.

Formel entwickeln

Zunächst gebe ich die Formel ein, mit der ich mit herkömmlichen Mitteln das gewünschte Datum berechne:

=MONATSENDE(C2;-1)+1

Tabelle mit Formel

Ich will nun eine Funktion haben, die diese Berechnung erledigt, und sie soll MONATSANF heißen.

LAMBDA testen

Dazu benutze ich die LAMBDA-Funktion, die als Argumente die Parameter hat, die in die Berechnung einfließen (beliebig viele, aber mindestens eines), und anschließend die Anweisung für die Berechnung. In diesem Beispiel sieht das so aus:

=LAMBDA(datum;MONATSENDE(datum;-1)+1)

Das erste Argument habe ich "datum" genannt. Es ist das Datum, für das die Berechnung ausgeführt werden soll. Den Namen kann ich frei wählen, ich kann auch "Tag" oder "Stichtag" oder einfach nur "x" usw. nehmen. Es gelten die Excel-Syntaxregeln für Namen mit dem Zusatz, dass der Punkt (.) nicht erlaubt ist.
Wichtig ist, dass ich diesen Namen in der darauffolgenden Rechenvorschrift als Variablenbezeichner verwende.
Wenn ich die Formel so, wie sie hier steht, in das Tabellenblatt eingebe, bekomme ich die Fehlermeldung #KALK!. Das liegt daran, dass die Funktion nicht weiß, was sich hinter dem Parameter „datum“ verbirgt und deshalb auch die Rechenvorschrift nicht anwenden kann.

Lambda mit Fehler

Um die LAMBDA-Funktion zu testen, muss ich zusätzlich das Argument für das Parameter anfügen, und zwar in Klammern unmittelbar hinter der LAMDA-Funktion (Achtung: Da ist kein Leerzeichen dazwischen!).

=LAMBDA(datum;MONATSENDE(datum;-1)+1)(C2)

Lambda-Test

Wenn alles richtig ist, erscheint das richtige Ergebnis. Wenn die LAMDA-Funktion fehlerhaft ist oder ein falsches Argument angegeben ist, erhalte ich nicht das erwartete Ergebnis oder eine Fehlermeldung.
Und das ist auch der einzige Grund, weshalb wir die LAMBDA-Funktion in das Tabellenblatt schreiben: um sie zu testen.
Denn eigentlich gehört die LAMBDA-Funktion hier nicht hin. Wir wollen ja eine Funktion haben, die jederzeit in der Arbeitsmappe verfügbar ist. Und das geschieht im nächsten Schritt.

LAMBDA speichern

Im Menüband Formeln wähle ich den Namens-Manager und gehe zu Neu…
Im Dialogfenster gebe ich den Namen der benutzerdefinierten Funktion ein und unter Bezieht sich auf: die LAMDA-Funktion in ihrer ganzen Schönheit.

Lambda-Eintrag

Danach steht die Funktion MONATSANF zur Verfügung und kann im Arbeitsblatt verwendet werden.

Benutzerdefinierte Funktion

Selbstverständlich kann man den Eintrag im Namensmanager auch ohne die vorausgehenden Schritte machen. Es ist aber sicherlich einleuchtend, dass ein vorheriger Test der LAMBDA-Funktion empfehlenswert ist.

Und was ist mit mehreren Argumenten?

Am Anfang der LAMDA-Funktion können beliebig viele Parameter stehen, die dann in der Rechenvorschrift als Argumente Anwendung finden. Der letzte Parameter ist immer die Berechnung.
Als Beispiel soll hier die Berechnung der Hypotenuse eines rechtwinkligen Dreiecks (Satz des Pythagoras) mit zwei Parametern dienen. Die LAMBDA-Funktion könnte zum Beispiel so aussehen:

=LAMBDA(a;b;WURZEL(a^2+b^2))

In der Datei lambdademo.xlsx können die beschriebenen Schritte nachvollzogen werden.

Fazit

Der entscheidende und unschlagbare Vorteil von LAMBDA ist die Möglichkeit der verkürzten Schreibweise und damit der Eingrenzung der Fehleranfälligkeit bei der Eingabe oder beim Kopieren von Formeln.

Für viele benutzerdefinierte Funktionen ist damit keine Programmierung mit VBA erforderlich. Das erhöht auch die Sicherheit, da Dateien mit Makros als potenzielles Sicherheitsrisiko eingestuft werden und in manchen Organisationen gar nicht ausgeführt werden dürfen.

Eine weitere Besonderheit ist, dass mit LAMBDA auch Rekursionen möglich sind, das bedeutet, dass sich das LAMBDA selbst aufrufen kann. Das ist eine etwas anspruchsvollere Sache, schafft aber weitere unschätzbare Möglichkeiten in der Anwendung von LAMBDA. Eine sehr gute Einführung zu diesem Thema (in englischer Sprache) findest Du hier.

Pfeil nach oben

EINDEUTIG - Endlich Excel-Funktion zum Erzeugen einer Liste der eindeutigen Werte

06.02.2023
Wie man in Excel die Anzahl eindeutiger Werte in einer Liste mit Hilfe von Formeln ermitteln kann und was man dabei beachten sollte, wurde auf diesen Seiten schon einmal vogestellt. Eine etwas schwierigere Aufgabe ist das Herausfiltern einer Liste mit den Unikaten als separate Liste. Wie man das mit einigen Formeln bewerkstelligen kann, wurde hier gezeigt.
Freilich etwas umständlich. Doch seit Excel für Microsoft 365 bzw. Excel 2021 gibt es eine Funktion, die das mit einem Schlag erledigt. Sie heißt EINDEUTIG(), und wie sie funktioniert, soll hier erklärt werden:
Hier ist eine Liste, die in der ersten Spalte Obstsorten enthält.

Tabelle mit Obst

Nun wollen wir eine Liste erzeugen, die die verschiedenen Obstsorten auflistet, die in der Tabelle vorkommen. Dazu geben wir an der Stelle, wo diese Liste erscheinen soll, diese Formel ein:

=EINDEUTIG(A1:A20)

Alles, was zu beachten ist, dass unter der Formel genügend Platz für das Ergebnis ist. Da wir in der Regel nicht wissen, wieviel Unikate es gibt, nehmen wir am Besten eine leere Spalte. Hier ist das Ergebnis zu sehen, wenn die Formel in Zelle D1 eingetragen wird:

Tabelle mit Obst

Wenn der Platz für das Ergebnis nicht reicht, wird die Fehlermeldung #ÜBERLAUF! angezeigt, wie in der folgenden Abbildung zu sehen. Da steht etwas in Zelle D7, und die Ergebnismatrix kann nicht angezeigt werden.

Tabelle mit Fehlermeldung

Noch ein Hinweis zur Syntax: die Funktion hat zwei weitere, optionale Argumente.

=EINDEUTIG(Matrix;[nach_Spalte];[genau_einmal])

Setzt man [nach_Spalte] auf WAHR, wird spaltenweise ausgewertet, und wenn nur die Werte angezeigt werden sollen, die nur einmal in der Matrix vorkommen, ist [genau_einmal] auf WAHR zu setzen. Standard ist für beide Optionen FALSCH. Da wir in der vorliegenden Aufgabenstellung die Zeilen durchsuchen und alle eindeutigen Werte sehen wollen, können diese Argumente weggelassen und die Formel in der oben gezeigten verkürzten Form verwendet werden. Und das wird meistens der Fall sein.
Leere Zellen werden mit verarbeitet und als 0 zurückgegeben.

Pfeil nach oben

In Excel zwei Listen vergleichen

Oft erfordert die Praxis, zwei Listen zu vergleichen, um festzustellen, ob Einträge aus der einen Liste in der anderen enthalten sind und umgekehrt. Für diese Aufgabe kann man die Funktionen SVERWEIS oder VERGLEICH benutzen. Wenn geprüft werden soll, ob der Eintrag in C2 der aktuellen Liste in der Vergleichsliste A4:A15 enthalten ist, kann eine der folgenden beiden Formeln verwendet werden:

=SVERWEIS(C4;$A$4:$A$15;1;FALSCH)

=VERGLEICH(C4;$A$4:$A$15;0)

Die SVERWEIS-Funktion gibt den gesuchten Eintrag zurück. Wenn der Wert nicht enthalten ist, erscheint die Fehlermeldung #NV.
VERGLEICH gibt die Position des gesuchten Werts in der Vergleichsliste zurück. Ist der Eintrag nicht enthalten, wird auch hier #NV angezeigt.
Da in der Regel nur die Information benötigt wird, ob vorhanden oder nicht, und die Anzeige #NV oft irritierend für den Nutzer ist, kann man die Formeln etwas eleganter schreiben:

=WENN(ISTNV(SVERWEIS(C4;$A$4:$A$15;1;FALSCH));"NEIN";"JA")

=WENN(ISTNV(VERGLEICH(C4;$A$4:$A$15;0));"NEIN";"JA")

Beispiel Tabellenvergleich mit Formeln

Zu beachten ist, dass Groß- und Kleinschreibung nicht unterschieden werden. Führende oder nachgestellte Leerzeichen oder andere nicht druckbare Zeichen werden in den Vergleich einbezogen und können deshalb zu unerwünschten Ergebnissen führen. Führende Apostrophe (') werden ignoriert.

Beispiel Tabellenvergleich mit Bedingter Formatierung

Alternativ kann man die Bedingte Formatierung benutzen, um nicht gefundene Einträge zu markieren. Die Abbildung zeigt die Gegenüberstellung zweier Tabellen, bei der die Einträge, die in der jeweils anderen Tabelle nicht gefunden worden sind, hervorgehoben werden.

Die Arbeitsweise kann in der Datei listcomparison.xlsx nachvollzogen werden.

Pfeil nach oben

Feiertage berechnen in Excel

Die Berechnung von Feiertagen in Excel wird oft gewünscht und stellt keine sehr große Herausforderung dar. Es gibt in Excel keine Formel für die Berechnung von Feiertagen. Um selbst Feiertage zu berechnen, muss man zwischen festen und beweglichen Feiertagen unterscheiden. Die beweglichen Feiertage werden in der Regel vom Datum des Ostersonntag abgeleitet, so dass die Hauptaufgabe in der Berechnung des Osterdatums liegt.

Zunächst zu den festen Feiertagen. Sie werden mit der Funktion DATUM berechnet. Beispielsweise lautet die Formel für die Heiligen Drei Könige (immer am 6. Januar), wenn das Jahr in A1 steht:

=DATUM(A1;1;6)

Die Berechnung des Osterdatums ist unter anderem auf den Internetseiten der Physikalisch-technischen Bundesanstalt beschrieben. Die Umsetzung in Excel kann in der Datei ostern.xlsx nachvollzogen werden.
Hat man erst mal das Osterdatum, lassen sich weitere Feiertage sehr einfach berechnen. Zum Beispiel liegt Christi Himmelfahrt 39 Tage nach Ostern. Die Formel für die Berechnung des Himmelfahrtsdatums lautet demnach, wenn in A2 das Osterdatum steht:

=A2+39

Eine Liste der Feiertage ist zum Beispiel nützlich zur Berechnung von Fristen oder Dauern unter Berücksichtigung von arbeitsfreien Tagen mit Hilfe der Funktionen ARBEITSTAG.INTL oder NETTOARBEITSTAGE.INTL.
Auch das wird in ostern.xlsx demonstriert.

Pfeil nach oben

Sich anpassende fortlaufende Nummer in Tabelle mit Formel generieren

Um die Zeilen einer Tabelle fortlaufend durchzunummerieren, kann man die folgende Formel verwenden:

=ZEILE()-ZEILE($A$2)+1

Nummerierte Zeilen

Dabei ist A2 die erste Zelle der Tabelle (unter der Überschrift). Die Formel ist so in A2 einzutragen und nach unten zu kopieren.
Sie hat den Vorteil, dass die Nummern automatisch angepasst werden, wenn eine Zeile gelöscht oder eingefügt wird.

Lücken in Nummerierung

Wenn aber gewünscht ist, dass beim Filtern oder Ausblenden von Zeilen die Nummerierung angepasst wird, versagt diese Formel.

Einen Ausweg bietet die folgende Formel:

=AGGREGAT(3;5;BEREICH.VERSCHIEBEN($A$1;0;0;ZEILE()-ZEILE($A$1);1))

Nummerierung angepasst

Dabei ist A1 die Zelle unmittelbar vor der ersten Zeile der Tabelle (in der Regel die Überschrift). Die Formel hat außerdem den Vorteil, dass sie auch angepasst wird, wenn eine Zeile vor der ersten Tabellenzeile eingefügt wird.

Pfeil nach oben

Manchmal gilt: Genau ist gut, Runden ist besser

Das kommt schon mal vor: Da hat man in Excel eine Berechnung nach allen Regeln der Kunst erstellt, und es kommt auch ein plausibles Ergebnis heraus. Dann kommt jemand, nimmt den guten alten Taschenrechner zur Hand, gibt den letzten Rechenschritt ein und erhält ein anderes Ergebnis. Die Abbildung zeigt beispielhaft eine Kalkulation. Tippt man nun im Taschenrechner 143037,05*129,578 ein, erhält man als Ergebnis 18534454,8649 und nicht 18534454,61 wie in Excel. Das sollte man schon erklären können. Kann man es nicht, wird möglicherweise, obwohl die Abweichung verschwindend gering ist, die gesamte Berechnung in Zweifel gezogen.

Beispiel für notwendiges Runden

Natürlich gibt es eine Erklärung, und natürlich hat Excel keinen Fehler gemacht, und natürlich ist das Problem so alt wie Excel selbst. Die Erklärung ist: Excel rechnet mit dem genauen Zellinhalt, nicht mit dem angezeigten Wert. Der angezeigte Wert kann - wie in diesem Fall - wegen Rundung vom exakten Wert abweichen (Währungsformat mit zwei Nachkommastellen). Excel rechnet also nicht mit dem Wert 143037,05, den wir sehen, sondern mit 143037,048. Das würde sogar dem Taschenrechner das Ergebnis 18534454,605744 entlocken. Das kann man versuchen, jemandem zu erläutern. Man kann aber auch gar nicht erst in Erklärungsnot geraten.
Wenn zur Weiterberechnung nicht das exakte Zwischenergebnis benutzt werden soll (und das ist hier eindeutig der Fall, denn es gibt keine Tausendstel Euro), dann sollte man das Zwischenergebnis vor der Weiterberechnung runden. Die Formel =C5*D5 in E5 muss deshalb geändert werden in =RUNDEN(C5*D5;2).
Das Beispiel zeigt, dass die RUNDEN-Funktion in Excel wahrscheinlich häufiger angewendet werden muss als vermutet. Eine einfache Möglichkeit, den damit verbundenen Aufwand zu umgehen, geht so:

Datei | Optionen | Erweitert | Beim Berechnen dieser Arbeitsmappe | Genauigkeit wie angezeigt festlegen

Bei dieser Einstellung rechnet Excel immer mit dem Wert, der angezeigt wird. Da diese Einstellung für die gesamte Arbeitsmappe gilt, muss man sicherstellen, dass die Zellen, deren exakter Inhalt verwendet werden soll, im Standardzahlenformat formatiert sind. Einzelheiten sind in roundme.xlsx. zu finden.

Pfeil nach oben

Formel zum Anzeigen des Blattnamens in einem Arbeitsblatt

Um den Namen eines Arbeitsblatts (also das, was im Blattregister steht) im Arbeitsblatt selbst anzuzeigen, kann man die folgende Formel verwenden:

=RECHTS(ZELLE("dateiname";A1);LÄNGE(ZELLE("dateiname";A1))-FINDEN("]";ZELLE("dateiname";A1)))

Sie hat den einzigen Nachteil, dass das Löschen der Zelle A1 einen Fehler erzeugen würde. Eine robuste Variante könnte so aussehen:

=RECHTS(ZELLE("filename";INDIREKT(ADRESSE(1;1)));LÄNGE(ZELLE("filename";INDIREKT(ADRESSE(1;1))))-FINDEN("]";ZELLE("filename";INDIREKT(ADRESSE(1;1)))))

Diese Formel wird durch das Löschen von Zellen nicht beinflusst. Zusätzlich wurde hier das Argument "dateiname" gegen die englische Form "filename" ausgetauscht. Damit funktioniert die Formel in beliebigen Sprachumgebungen.
Ein sinnvoller Anwendungsfall für die Formel könnte zum Beispiel dann gegeben sein, wenn in einem Text auf ein anderes Arbeitsblatt verwiesen werden soll. Falls dieses Arbeitsblatt umbenannt würde, wird der Text automatisch angepasst. Eine Demonstration ist in der Datei blattname.xlsx enthalten.

Pfeil nach oben

Alternierende Färbung von Zeilen auch beim Filtern

Eine bekannte Methode, Zeilen von Tabellen abwechselnd einzufärben, ist die Anwendung der bedingten Formatierung. Die Regel wird dabei so gestaltet, dass nur die geraden (oder nur die ungeraden) Tabellenzeilen gefärbt werden. Da die Formatierung allein von der Zeilennummer abhängig ist, funktioniert sie auch, wenn Zeilen in der Tabelle eingefügt oder gelöscht werden oder die Tabelle anders sortiert wird. Die Regel für die bedingte Formatierung sieht so aus:

=ISTGERADE(ZEILE())

Die Methode versagt aber, wenn Zeilen der Tabelle ausgeblendet werden, beispielsweise durch Filtern. In der folgenden Abbildung wurde die Tabelle nach Priorität 1 gefiltert. Die Abwechslung ist gestört.

Beispiel für Versagen der Methode beim Filtern

Eine Alternative ist, in der bedingten Formatierung zu prüfen, ob die fortlaufende Nummer ausschließlich der eingeblendeten Zeilen (anstelle der Zeilennummer) gerade oder ungerade ist. Die Regel dazu lautet (wenn beispielsweise A3 die erste Zelle in der Tabelle ist):

=ISTGERADE(TEILERGEBNIS(103;$A$3:$A3))

Das funktioniert auch beim Filtern.

Beispiel für abwechselnde Färbung beim Filtern

Nachvollziehbar ist die beschriebene Methode in altrowcolor.xlsx.

Pfeil nach oben

Excel-Funktionen nicht ohne Sprachverwirrung

Eigentlich scheint alles ganz einfach: Wenn man eine Excel-Datei an einen Kollegen oder Geschäftspartner in New York oder London verschickt, der sie in einer englischsprachigen Umgebung öffnet, dann werden die Funktionen automatisch übersetzt. Anstelle SUMME steht dort SUM, und aus ZÄHLENWENN wird COUNTIF. Und auch der Freund in Moskau wird nicht solche Sprachungetüme wie BEREICH.VERSCHIEBEN zu lesen bekommen. Das geschieht ohne unser Zutun, und die Berechnungen funktionieren genauso reibungslos wie zu Hause.
So weit, so gut. Und trotzdem gibt es Situationen, wo man sich darum kümmern muss, was am anderen Ende passiert. Probleme können nämlich dann auftreten, wenn Funktionen verwendet werden, denen Textkonstanten mitgegeben werden. Diese Textkonstanten werden in der jeweiligen Arbeitsumgebung entsprechend der Spracheinstellung interpretiert. Sie werden nicht übersetzt.
Zu den üblichen Verdächtigen zählen die Funktionen INFO und ZELLE. Beide erfordern als erstes Argument eine Textkonstante. Benutzt man zum Beispiel die Funktion ZELLE, um den Dateinamen zurückzugeben, könnte die Formel so aussehen:

=RECHTS(ZELLE("dateiname";A1);LÄNGE(ZELLE("dateiname";A1))-FINDEN("]";ZELLE("dateiname";A1)))

Diese Formel würde in einer englischen Sprachumgebung so übersetzt werden:

=RIGHT(CELL("dateiname";A1);LEN(CELL("dateiname";A1))-FIND("]";CELL("dateiname";A1)))

Das Argument "dateiname" kann dabei nicht ausgewertet werden, und die Formel würde den Fehlerwert #VALUE! zurückgeben.
Wenn man solchen Pannen vorbeugen will, sollte man immer die englische Version der Argumente verwenden. Das wird von Excel in jeder Sprachumgebung verstanden. Die obige Formel müsste demnach so aussehen:

=RECHTS(ZELLE("filename";A1);LÄNGE(ZELLE("filename";A1))-FINDEN("]";ZELLE("filename";A1)))

Damit wäre der Drops schon gelutscht, wenn es nicht noch eine Besonderheit gäbe. Einige Rückgabewerte der Funktionen INFO und ZELLE sind sprachspezifisch und können damit nicht unabhängig von der Sprachumgebung ausgewertet werden. Eine Idee, wie man mit diesem babylonischen Sprachgewirr umgehen kann, gegebenenfalls auch unter Anwendung von VBA, zeigt die Datei babylon.xlsb.

Pfeil nach oben

Gleiche Werte nicht gleichrangig?

Beispiel Verschiedene Ränge für gleiche Werte

Die Funktion RANG.GLEICH (früher nur RANG) vergibt Zahlenwerten in einer Reihe die Ränge, die sie in der auf- oder absteigend sortierten Liste dieser Werte einnehmen würden. Gleiche Werte erhalten zwangsläufig denselben Rang. Doch das ist nicht immer so, wie das folgende Beispiel zeigt:

In der Spalte C stehen berechnete Werte. Die Formel in C2 ist: =A2*B2

In Spalte D werden die Ränge nach der Formel =RANG.GLEICH(C2;$C$2:$C$6;0) vergeben.

Die Formeln sind entsprechend nach unten kopiert.
Wie man sieht, vergibt Excel unterschiedliche Ränge für die Werte in C3 und C5, die - wie man leicht nachprüfen kann - gleich groß sind. Vermutlich werden die berechneten Werte für die Weiterverarbeitung durch RANG.GLEICH als Gleitkommazahl mit geringfügigen Abweichungen zwischengespeichert, was zur Ungleicheit führen kann.
Die Empfehlung lautet: Berechnete Werte vor der Verarbeitung mit RANG.GLEICH runden.
Im gegebenen Beispiel würde das heißen, die Formel in Spalte C abzuändern in:

=RUNDEN(A2*B2;2)

Nun erkennt RANG.GLEICH die Gleichheit und vergibt die Ränge richtig. Das Fallbeispiel kann in rangungleich.xlsx nachvollzogen werden.

Pfeil nach oben

SVERWEIS: Stolperfalle Spaltenindex

Wer kennt das nicht: Da hat man mit Mühe und Schweiß ein oder mehrere Rechenblätter mit trickreichen Formeln für verschiedene Auswertungen aufgebaut. Anschließend fügt man hier und da eine brauchbare Spalte ein oder löscht die eine oder andere überflüssige Spalte, und plötzlich zeigen die Ergebniszellen anstelle der vorherigen Resultate alle möglichen Fehlerwerte an. Nach mühseliger Suche, die sich bei großen Tabellen und arbeitsblattübergreifenden Formeln oft sehr aufwändig gestaltet, stellt sich häufig eine SVERWEIS-Funktion als Fehlerquelle heraus. Der Grund ist, dass das Argument Spaltenindex als Konstante eingegeben worden ist, während sich durch das Hinzufügen oder Löschen von Spalten der Spaltenversatz geändert hat.

Beispiel Tilde im Suchbegriff

An einem simplen Beispiel soll das erläutert werden: In Zelle E3 steht die Formel:

=SVERWEIS(E2;A2:B8;2;FALSCH)

Der Spaltenindex ist hier mit der Konstante 2 angegeben, d. h. SVERWEIS soll den Wert aus der 2. Spalte der Matrix zurückgeben.
Später wird vor der Spalte B eine weitere Spalte eingefügt. In F4, wo das Ergebnis der SVERWEIS-Funktion weiterverarbeitet wird, erscheint jetzt ein Fehler.

Beispiel Tilde im Suchbegriff

Es ist klar, dass durch das Einfügen einer Spalte der notwendige Spaltenindex in SVERWEIS jetzt 3 sein muss, da aber 2 in der Formel steht, der falsche Wert geliefert wird. In komplexen Ausarbeitungen ist die Fehlersuche jedoch oft langwierig, zumal nicht immer offensichtlich ist, dass die SVERWEIS-Funktion die Fehlerquelle ist. Dass der Wert in F3 unbrauchbar ist, springt nicht sofort ins Auge, während der Fehler in F4 augenfällig ist.
Zum Vorbeugen eines solchen Fehlers kann man den Spaltenindex variabel (dynamisch) gestalten. Die ursprüngliche Formel in E3 müsste dann das folgende Aussehen erhalten:

=SVERWEIS(E2;A2:B8;SPALTE(B2)-SPALTE(A2)+1;FALSCH)

Diese Formel fängt die Änderung des Spaltenindex beim Einfügen oder Löschen von Spalten oder beim Verschieben (Drag&Drop) von Tabellenspalten ab. Die Formeln können in der Datei sverweisrobust.xlsx nachvollzogen werden.

Pfeil nach oben

Tilde (˜) im Text wird nicht gefunden

Verblüffend: Beim exakten Textvergleich wird das Suchkriterium falsch ausgewertet. Eine Zeichenfolge, die eine Tilde (˜) enthält, wird nicht gefunden.
Hier ein Beispiel:

In Spalte A stehen ein paar Texte und in Spalte B Werte dazu. In Zelle E2 soll die SVERWEIS-Funktion den Wert zum Suchbegriff zurückgeben. Der Suchbegriff steht in Zelle E1. Die Formel in E2 lautet:

=SVERWEIS(E1;A2:B6;2;FALSCH)

Beispiel Tilde im Suchbegriff

Das letzte Argument zeigt, dass nach exakter Übereinstimmung gesucht werden soll. Es besteht wohl kein Zweifel, dass die Zeichenfolgen in A3 und E1 exakt übereinstimmen. Trotzdem ergibt die Formel einen Fehler.
Die Ursache ist, dass die Tilde (˜) im Suchkriterium eine besondere Funktion hat. Sie muss den Platzhaltern ? oder * vorangestellt werden, wenn nach den Zeichen ? oder * selbst gesucht werden soll. Das ist in der Hilfe zu SVERWEIS auch ausführlich beschrieben. Nicht beschrieben ist, was zu tun ist, wenn nach der Tilde (˜) selbst gesucht werden soll.
Naheliegend ist, die Tilde im Suchkriterium zweimal hintereinander einzugeben. In diesem Beispiel wäre der Suchbegriff dann "B˜˜C". Das funktioniert offensichtlich. Um für solche Fälle, dass das Suchkriterium eine Tilde (˜) enthält, vorzusorgen, kann man mit Hilfe der WECHSELN-Funktion dieses Zeichen einfach verdoppeln. Die sichere Formel lautet dann:

=SVERWEIS(WECHSELN(E1;"˜";"˜˜");A2:B6;2;FALSCH)

Sinngemäß gilt das Gesagte für alle Funktionen mit Suchkriterien. Dazu zählen: VERGLEICH(), ZÄHLENWENN(), ZÄHLENWENNS(), MITTELWERTWENN(), MITELWERTWENNS(), SUMMEWENN(), SUMMEWENNS(), SVERWEIS(), WVERWEIS(), SUCHEN(), SUCHENB() und die Datenbankfunktionen (DBMITTEL(), DBSUMME() usw.). Das Beispiel kann in tildeimtext.xlsx nachvollzogen werden.

Pfeil nach oben

Summe ergibt nicht 100% ist kein Excel-Problem

Berechnungsbeispiel

Häufig wird von Nutzern beklagt, dass die Summe der von Excel errechneten Prozentwerte nicht 100% ergibt. Hier ein Beispiel:
Wenn man die Werte in der Spalte Anteil zusammenzählt, kommt man auf 101%. Das ist kein Excel-Fehler! Oft wird einfach nicht beachtet, dass zwischen dem Wert (Inhalt der Zelle) und der Anzeige (Format der Zelle) unterschieden werden muss. Excel rechnet mit den exakten, nicht mit den angezeigten Werten. Deshalb ergibt auch die Summe genau 100%. Beim Runden entsteht ein Rundungsfehler, das ist die Abweichung des gerundeten Werts vom genauen Wert. Dieser Fehler steckt in den gerundeten Werten. Wenn, wie in diesem Beispiel - vereinfacht gesagt - öfter auf- als abgerundet wird, ist die Summe der gerundeten Werte größer als die genaue Summe. Damit hat Excel nichts zu tun. Das passiert auch beim Rechnen auf dem Papier (falls noch jemand weiß, wie das geht).
So viel zur Erklärung. Zufriedenstellend ist das natürlich noch nicht, denn die Aufgabenstellung heißt ja, dass die Summe immer 100% sein muss, auch für die gerundeten Werte. Um das zu erreichen, muss man die Rundungsregeln außer Kraft setzen. Die Lösung heißt summenerhaltendes Runden, und wie das alles geht, findet man in rundungsfehler.xls.
Anmerkung: Man sollte wissen, dass Excel veranlasst werden kann, mit den gerundeten Werten zu rechnen. Dazu ist die folgende Einstellung vorzunehmen: Datei | Optionen | Erweitert | Beim Berechnen dieser Arbeitsmappe | Genauigkeit wie angezeigt festlegen. Die Einstellung gilt für die gesamte Arbeitsmappe. Für das hier dargestellte Problem stellt das jedoch keine Lösung dar.

Pfeil nach oben

Anzahl Unikate berechnen - Fehler im Ergebnis?

Eine häufige Fragestellung, für die es in Excel keine eingebaute Funktion gibt, ist die Ermittlung der Anzahl von verschiedenen Einträgen (eindeutige Elemente, Unikate) in einer Liste. Microsoft bietet für diese Aufgabenstellung einige Formeln an, die auf verschiedene Fälle zugeschnitten sind (Zahlen oder Text, mit oder ohne leere Zellen). Einzelheiten sind auf den folgenden Seiten zu finden:

  1. http://support.microsoft.com/kb/268001/de
  2. http://support.microsoft.com/kb/823573/de

Alle hier vorgestellten Formeln erfüllen natürlich den versprochenen Zweck. Trotzdem gibt es einen kleinen Haken. Es kann in einigen Fällen zu Fehlern kommen, wenn die Ergebnisse, die mit den unter (2) vorgestellten Formeln ermittelt worden sind, weiterverarbeitet werden sollen. Konkret ist das zu beobachten, wenn mit der VERWEIS- oder der VERGLEICH-Funktion nach exakter Übereinstimmung gesucht wird. Angenommen, in A1:A10 ist die Liste, in der die Unikate ermittelt werden sollen. In B1:B10 stehen fortlaufende Nummern von 1 bis 10. In C1 steht die Formel zur Ermittlung der Anzahl der Unikate nach dem zweiten Muster:

{=SUMME(1/ZÄHLENWENN(A1:A10;A1:A10))}

Diese Zahl soll nun in der Liste gefunden werden:

=VERGLEICH(C1;B1:B10;0)

Erstaunlicherweise liefert die Formel manchmal einen Fehlerwert, obwohl die ermittelte Anzahl in B1:B10 enthalten ist. Offensichtlich liegt die Ursache in der Berechnung mit Gleitkommazahlen. Das Ergebnis wird zwar als ganze Zahl angezeigt, bei der Weiterverarbeitung aber wahrscheinlich intern als Gleitkommazahl mit einer geringfügigen Abweichung zwischengespeichert. Diesem vorhersehbaren Fehler kann durch explizite Umwandlung des Ergebnisses in eine ganze Zahl begegnet werden. Die folgende Formel liefert das erwartete Ergebnis:

=VERGLEICH(GANZZAHL(C1);B1:B10;0)

Ein Beispiel und eine Übersicht der Formeln für verschiedene Anwendungsfälle ist in der Datei anzunikate.xlsx zu finden.

Pfeil nach oben

Kalenderwoche mit Jahresangabe

In Excel kann man mit der Funktion KALENDERWOCHE() die Nummer der Kalenderwoche ermitteln. Dabei ist die Besonderheit zu beachten, dass in unseren Breiten die Kalenderwoche nach ISO 8601 definiert wird. Das bedeutet vereinfacht, dass die letzten Tage im Dezember bereits zur ersten Kalenderwoche des folgenden Jahres und die ersten Tage im Januar noch zur Kalenderwoche 52 oder 53 des Vorjahres gehören können (mehr dazu unter http://de.wikipedia.org/wiki/Kalenderwoche#Kalenderwoche). Seit Version 2007 berücksichtigt Excel diese Regel. Dazu muss man der Funktion KALENDERWOCHE als zweites Argument den Parameter 21 mitgeben. Wenn in A1 das Datum steht, zu dem die Kalenderwoche ermittelt werden soll, dann lautet die richtige Formel:

=KALENDERWOCHE(A1;21)

Das Ergebnis ist eine ganze Zahl, deren Anzeige durch benutzerdefinierte Zahlenformate gesteuert werden kann. Für eine zweistellige Anzeige (ggf. mit führender Null) und dem Zusatz KW kann man beispielsweise das Zahlenformat "KW "00 verwenden.
Will man nun zur Darstellung die Jahreszahl hinzufügen (z. B. in der Form: KW 53/15), muss man eine Formel zu Hilfe nehmen, die das richtige Jahr ermittelt, denn die Kalenderwochen 52, 53 oder 1 können wie oben erwähnt unter Umständen zum vorigen oder zum nächsten Jahr gehören. Die Formel dazu könnte so aussehen:

="KW "&TEXT(KALENDERWOCHE(A1;21);"00")&"/"&TEXT(A1+WENN(KALENDERWOCHE(A1;21)=1;7;0)-WOCHENTAG(A1;2);"JJ")

Alternativ kann man die Formel so schreiben:

=VERKETTEN("KW ";TEXT(KALENDERWOCHE(A1;21);"00");"/";TEXT(A1+WENN(KALENDERWOCHE(A1;21)=1;7;0)-WOCHENTAG(A1;2);"JJ"))

Die Formeln können in der Datei kwjahr.xlsx nachvollzogen werden.

Pfeil nach oben

Datum einer Kalenderwoche

Aussagen wie diese sind der Albtraum des Terminplaners: Die Lieferung erfolgt voraussichtlich in KW 20. Ja, wann ist das wohl? Der Kalender an der Wand zeigt drei Monate und endet im April mit KW 18. Wahrscheinlich ist es also Mitte Mai. Excel benötigt aber ein konkretes Datum, da es mit den Terminen rechnen soll.
Die folgende Formel berechnet den Montag der Kalenderwoche 20 (nach ISO 8601) im Jahr 2014:

=DATUM(2014;1;-2-WOCHENTAG(DATUM(2014;1;4);2))+7*20

Das Ergebnis ist der 12.05.2014. Da kann man doch schon mal was damit anfangen. Das Dumme ist: Der Kunde denkt, die Lieferung erfolgt am Anfang der Kalenderwoche. Der Lieferant meint mit Kalenderwoche meistens das Ende der Woche. Wann ist also der Freitag der Kalenderwoche? Allgemein lautet die Formel so:

=DATUM(Jahr;1;-2-WOCHENTAG(DATUM(Jahr;1;4);2))+7*Kalenderwoche+Wochentag-1

Dabei ist das Jahr vierstellig einzugeben, Kalenderwoche ist die Nummer der gewünschten Woche, und Wochentag ist eine Zahl von 1 für Montag bis 7 für Sonntag.
Der VBA-Code für eine benutzerdefinierte Funktion könnte so aussehen:

				
Function KWDATUM(ByVal KWNr As Integer, Optional ByVal Jahr As Variant, _
Optional ByVal Wochentag As Integer = 1) As Date
	Jahr = IIf(IsMissing(Jahr), Year(Date), CInt(Jahr))
	KWDATUM = DateSerial(Jahr, 1, -2 - Weekday(DateSerial(Jahr, 1, 4), _
	vbMonday)) + 7 * KWNr + Wochentag - 1
End Function
				
			

Diese Funktion berechnet das Datum des Wochentags der Kalenderwoche aus den bekannten Parametern. Fällt das Jahr weg, wird das aktuelle Jahr (Computerdatum) genommen. Bei Fehlen des Wochentags wird der Montag berechnet.
Mehr zur Definition der Kalenderwoche findet man bei Wikipedia (http://de.wikipedia.org/wiki/Kalenderwoche#Kalenderwoche). Einzelheiten zu den Formeln können in der Arbeitsmappe kwdatum.xlsm nachvollzogen werden. Die Datei enthält Makrocode (VBA).

Pfeil nach oben

Mathematisches Runden

Wenn man die Tabellenfunktion RUNDEN in Excel verwendet, sollte man wissen, dass diese Funktion das sogenannte Kaufmännische Runden anwendet. Es sollte jedoch bekannt sein, dass es auch ein Mathematisches Runden (englisch: round to even) gibt. Vereinfacht gesagt, wird beim Kaufmännischen Runden von 1 bis 4 ab- und bei 5 bis 9 aufgerundet, während beim Mathematischen Runden bei der Endziffer 5 zur nächsten geraden Zahl ab- oder aufgerundet wird. Mehr dazu kann man bei Wikipedia (http://de.wikipedia.org/wiki/Rundung) erfahren.
Wer nun auf das Mathematische Runden angewiesen ist, wird von Excel leider im Regen stehen gelassen. Die folgende - zugegeben etwas aufwändige - Formel kann Abhilfe schaffen:

=WENN(UND(ISTUNGERADE(2*A1);REST(10*A1;5)=0);GERADE(A1-0,5);RUNDEN(A1;0))

Diese Formel liefert den ganzzahligen mathematisch gerundeten Wert der Zahl in Zelle A1. Für das Runden auf eine Stelle nach dem Komma gilt:

=WENN(UND(ISTUNGERADE(20*A1);REST(100*A1;5)=0);GERADE(10*A1-0,5)/10;RUNDEN(A1;1))

Für das Runden auf zwei Stellen nach dem Komma ist die Formel:

=WENN(UND(ISTUNGERADE(200*A1);REST(1000*A1;5)=0);GERADE(100*A1-0,5)/100;RUNDEN(A1;2))

Universal kann die Formel so eingesetzt werden:

=WENN(UND(ISTUNGERADE(2*10^$C$1*A1);REST(10^($C$1+1)*A1;5)=0);GERADE(10^$C$1*A1-0,5)/10^$C$1;RUNDEN(A1;$C$1))

Dabei steht in A1 wiederum die Zahl, die gerundet werden soll. In C1 steht die Anzahl der Stellen, auf die gerundet werden soll.
Ein weiterer Ausweg ist eine benutzerdefinierte Funktion. Der VBA-Code würde dann ungefähr so aussehen:

				
Function RundenMath(ByVal Zahl As Double, Optional ByVal Stellen As Long) _
As Double
	RundenMath = Round(Zahl, Stellen)
End Function
				
			

Wenn bei dieser Funktion die Anzahl der Stellen nicht mitgegeben wird, rundet sie auf ganze Zahlen.
Kuriosum: Die Tabellenfunktion RUNDEN() rundet kaufmännisch. Die VBA-Funktion Round() rundet mathematisch. Das ruft bei vielen Nutzern, die je nachdem wo sie zur Schule gegangen sind, das ein oder andere Ergebnis erwarten, Verwirrung hervor, und manche glauben, wieder einmal einen der berüchtigten Excel-Bugs gefunden zu haben. Tatsächlich rechnet Excel nicht falsch. Der Fehler besteht darin, dass in der jeweiligen Hilfe zu den Funktionen nicht dokumentiert ist, ob kaufmännisch oder mathematisch gerundet wird.
Die Formeln können in der Datei rundenmathematisch.xlsm nachvollzogen werden. Die Datei enthält Makrocode (VBA).

Pfeil nach oben

Begrenzen des Eingabebereichs in einem Arbeitsblatt - Alternative zu geschütztem Arbeitsblatt

Gelegentlich will man in Excel-Arbeitsblättern verhindern, dass der Nutzer die Tabelle nach unten oder rechts erweitert. Ein Grund dafür könnte sein, dass das Arbeitsblatt Formeln enthält, die dann angepasst werden müssten (zum Beispiel wenn sie SUMME(), MITTELWERT() o. ä. enthalten). Wenn der Nutzer das nicht weiß oder versehentlich nicht beachtet, kann das zu falschen Ergebnissen führen.
Eine einfache Methode ist das Ausblenden aller Zeilen unter und aller Spalten rechts von der Tabelle.

Vorschaubild Eingabebereich vorher

Angenommen, das Arbeitsblatt sieht aus wie hier abgebildet.
Die Vorgehensweise ist die: Cursor unmittelbar unter die Tabelle in Spalte A setzen (hier in A7). Dann Strg+Umschalt+→+↓ drücken. Anschließend Start | Format | Ausblenden & Einblenden | Zeilen ausblenden aufrufen. Danach Cursor unmittelbar rechts von der Tabelle in Zeile 1 setzen (hier in G1), dann Strg+Umschalt+→+↓ drücken und anschließend Start | Format | Ausblenden & Einblenden | Spalten ausblenden aufrufen.

Vorschaubild Eingabebereich vorher

Das Ergebnis sieht aus wie abgebildet.
Durch diesen einfachen Handgriff erspart man sich, das Arbeitsblatt zu schützen und daraufhin das Passwort zu vergessen. Ob das optisch gelungen ist, darüber kann man sich streiten. Aber es schafft mindestens eine gewisse Barriere, die der geübte Nutzer zwar überwinden kann, jedoch tut er es dann höchstwahrscheinlich mit Bedacht.
Hinweis: Zum Wiedereinblenden setzt man den Cursor in die obere linke Ecke, wo sich Zeilen- und Spaltenkopf schneiden (das Feld mit dem kleinen Dreieck). Dann nacheinander Start | Format | Ausblenden & Einblenden | Zeilen einblenden und Start | Format | Ausblenden & Einblenden | Spalten einblenden aufrufen. In Datei | Optionen | Benutzeroberflächenoptionen | Farbschema kann man zwischen drei Farbschemata wählen, die den Hintergrund silber (wie in der Abbildung), blau oder schwarz einfärben (wirkt sich auch auf Menü- und Statusleiste, Tabs und Zeilen- und Spaltenköpfe aus). Diese Einstellung kann jedoch der Datei nicht mitgegeben werden, sie ist arbeitsplatzbezogen.

Pfeil nach oben

Tipp für die Anwendung des Autofilters bei "mehrzelligen" Überschriften

Breite Überschriften

Bei der Anwendung des Autofilters in Excel kommt es vor, dass der Filter-Button Teile der Überschrift verdeckt. Eine Verbreiterung der Spalte ist nicht immer der beste Ausweg, wenn die Tabelle ohnehin schon sehr breit ist und bereits mit Mühe auf eine Bildschirm- oder Papierbreite passt.

Mehrzellige Überschriften 1

Werden die Überschriften auf mehrere Zeilen verteilt und kommen vielleicht auch noch verbundene Zellen hinzu, ist das Filtern mit weiteren Schwierigkeiten verbunden. Zum einen können bei verbundenen Zellen einzelne Spalten von der Filterung ausgeschlossen sein, zum anderen können Überschriften in den Filter einbezogen sein, wenn der Filter oberhalb der letzten Überschriftenzeile angeordnet ist.

Mehrzellige Überschriften 2

Ein verblüffend einfacher Trick kann hier helfen: Es genügt, zwischen Überschriften und Daten eine leere Zeile einzufügen, die allein dem Filtern dient.

Zeile für Autofilter

Wichtiger Hinweis: Wenn die Tabelle mittels Datenbankfunktionen oder einer Pivot-Tabelle ausgewertet werden soll, darf keine Leerzeile zwischen Überschrift und erster Datenzeile sein, da diese sonst als zur Datenbank gehörend betrachtet und mit ausgewertet würde. Außerdem müssen die Überschriften "einzellig" sein. In diesem Falle ist es richtig, die Überschriftenzeile in der Höhe entsprechend anzupassen und auf Mehrzeiligkeit und verbundene Zellen zu verzichten.

Einzellige Überschriften

Pfeil nach oben