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

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())

Beispiel für Versagen der Methode beim Filtern

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 abwechselnde Färbung 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.

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