Category Archives: Formeln und Funktionen

Die Basis einer gesunden Ordnung ist ein großer Papierkorb …

Über die Rechenungenauigkeit in Excel ist schon viel geschrieben worden. Sie finden im Internet eine Reihe Artikel zu diesem Thema.

Auch ich habe auf diesem Blog mich schon einige Male darüber ausgelassen:
Rechenungenauigkeit II
Rechenungenauigkeit
Das Lästern ist die Wurzelbürste der Psyche
Die Küche sieht aus wie Sau. Hab das Licht ausgemacht. Jetzt geht’s.
Excel kann alles – außer Kaffee kochen und rechnen.
Ein Kompromiss ist nur dann gerecht, brauchbar und dauerhaft, wenn beide Parteien damit gleich unzufrieden sind. (Henry Kissinger)

Heute habe ich ein weiteres amüsantes Phänomen festgestellt. Kennen Sie es?

Tragen Sie in eine Zelle die Formel

=(0,5-0,4-0,1)*1

ein. Das Ergebnis ist eine Rechenungenauigkeit ≈ -0,000000000000000027755575615629.

Formatieren Sie diese Zahl als Währung. Das Ergebnis lautet:

0,00 € (oder eine andere Landeswährung)

Formatieren Sie diese Zahl mit der Kategorie Buchhaltung. Das Ergebnis lautet:

– 0,00 € (oder eine andere Landeswährung)

Na ja – halt so ungefähr 0; ±0 – so genau wollen wir es jetzt auch nicht wissen.

Gefühle sind heutzutage auch nur noch was für ganz Mutige.

Excel ist an vielen Stellen nicht konsequent. Der Text „1“ ist beispielsweise etwas anderes als die Zahl 1. Dennoch kann man den Text mit 1 multiplizieren.

Ebenso verhält es sich mit WAHR und 1. Eigentlich entspricht Wahr dem Wert 1 und Falsch dem Wert 0. Eigentlich. Multipliziert man die beiden Werte WAHR und FALSCH, beziehungsweise die Funktionen =WAHR() und =FALSCH() mit 1, erhält man 1 beziehungsweise 0. Intern handelt es sich jedoch um einen anderen Wert. Also
=WAHR=1

liefert FALSCH.

Und diese Unschärfe führt auch dazu, das Wahr-Werte nicht addiert werden können:

=SUMMENPRODUKT(C:C=“Briefpapier“)

liefert 0.

=SUMMENPRODUKT((C:C=“Briefpapier“)*1)

dagegen das korrekte Ergebnis:

Hatte Kribbeln im Bauch … dachte schon bin verliebt … aber war doch nur Hunger …

Schon mal probiert?

Eine Datei greift mit einer Formel auf eine zweite Datei zu. Kein Problem.

Doch: ist ein Problem – nämlich, wenn es sich bei der Formel um SUMMEWENN, ZÄHLENWENN & co handelt. Dann muss nämlich die Quelldatei offen sein, damit kein Fehler angezeigt wird. Sehr erstaunlich!

So ist es gut – komm auf die dunkle Seite der Macht!

Wollt ihr wissen, wie man Excel zum Absturz bekommt? Man muss die Funktion AGGREGAT in einem Namen verwenden und diesen in einem Diagramm.

Das Ganze geht so:

Eine Tabelle holt sich Werte aus einer anderen Liste. Da einige Werte nicht gefunden werden, werden diese als #NV angezeigt. In einem Diagramm werden die entsprechenden Kategorien verwendet:

Unschön, denke ich mir. Die Jahreszahlen, die keinen Wert haben, sollen ausgeblendet werden. Und lege vier Namen an: „Bau“, „IT“, „Verwaltung“ und „sonstiges“. Sie haben die Form:

=BEREICH.VERSCHIEBEN(Tabelle1!$D$2;1;0;1;AGGREGAT(2;6;Tabelle1!$D$3:$J$3))

AGGREGAT deshalb, weil es die Fehlerwerte übergeht.

Ich versuche nun den Namen im Diagramm einzufügen, das heißt aus der ersten Datenreihe

=DATENREIHE(Tabelle1!$C$3;Tabelle1!$D$2:$J$2;Tabelle1!$D$3:$J$3;1)

wird ein:

=DATENREIHE(Tabelle1!$C$3;Tabelle1!$D$2:$J$2;Tabelle1!Bau;1)

Das Ergebnis: ABSTURZ!

Die Lösung ist simpel: Man lagert die Funktion AGGREGAT in eine Zelle aus (hier: L3). Man gibt ihr einen Namen – beispielsweise AGGREGAT.

Und ändert nun die Namen in:

=BEREICH.VERSCHIEBEN(Tabelle1!$D$2;1;0;1;AGGREGAT)

Nun kann der Bereich geändert werden:

=DATENREIHE(Tabelle1!$C$3;Tabelle1!$D$2:$J$2;Tabelle1!Bau;1)

Wer dies ausprobieren möchte, kann die Dateien herunterladen: AGGREGAT und AGGREGAT02.

 

Ich bin sehr wohl spontan … wenn man mir früh genug Bescheid sagt

Eigentlich unterscheidet Excel zwischen Text und Zahl. Eigentlich. Sicherlich kennen Sie folgendes Phänomen:

Eine Spalte ist als Text formatiert:

In einer anderen Spalte stehen ZAHLEN. Diese werden mit der Funktion SVERWEIS als #NV (nicht vorhanden) quittiert:

Das habe ich schon mehrmals beschrieben – beispielsweise in: „Sverweis funktioniert nicht“
Ebenso werden sie bei der Überprüfung auf Gleichheit

{=ODER(K2=$G$2:$G$22)}

(als Matrixformel) korrekt als unterschiedliche Werte erkannt:

Verwendet man statt dem Gleichheitsoperator „=“ jedoch die Funktion IDENTISCH werden die Texte und Zahlen als gleich(wertig) eingestuft:

{=ODER(IDENTISCH(K2;$G$2:$G$22))}

Ebenso übergeht ZÄHLENWENN diesen Unterschied:

Auch hier gilt mal wieder – schade, dass Excel an so vielen unterschiedlich ist, beziehungsweise einfach nicht konsequent. Kein Verlass auf gar nichts!

Habe heute mal wieder Sport gemacht. Ritter Sport. Zwei Tafeln.

Amüsante Fehlermeldung. Dabei wollte ich doch nur ein weiteres Feld in eine Pivottabelle einfügen:

Aber die Ursache ist schnell gefunden: Neben der Pivottabelle befand sich eine Formel. Excel kann nicht eine Spalte einfügen, verschiebt also nicht die Tabelle, sondern überschreibt die Formel:

Die Frage bleibt: Hätte man nicht „Tabelle2 enthält bereits Daten“ etwas anders formulieren können?

Wer morgens kalt duscht, lügt auch den Rest des Tages

Irgendwie doof. Mal wieder nicht konsistent.

Kennen Sie das? Ich erstelle eine Liste mit Verkäufernamen, Monatsnamen und Umsatzzahlen. Über Formeln / Definierte Namen / Aus Auswahl erstellen werden die Spaltennamen und Zeilennamen zu Namen der entsprechenden Zeile und Spalte:

Nun kann man die Schnittmenge berechnen:

=Roth Umsatz

Leider kann man diese Werte nicht auslagern – das führt zu einem Fehler:

Das ist erstaunlich, denn folgende Formeln funktionieren problemlos:

=SUMME(INDIREKT(„C2:C7“))

=SUMME(INDIREKT(„Umsatz“))

Aber eben leider nicht:

=SUMME(INDIREKT(„Umsatz Roth“))

und auch nicht:

=SUMME(INDIREKT(„C2:C5 C3:C7“))

Schade, dass INDIREKT keine Schnittmenge verarbeiten kann.

Nachtrag: Danke an XLarium für den wertvollen Hinweis (⇓):

Es funktioniert mit:

=INDIREKT(„Umsatz“) INDIREKT(„Roth“)

und:

=SUMME(INDIREKT(„C2:C5“) INDIREKT(„C3:C7“))

An dem Tag, an dem du mich das erste Mal nackt siehst, wirst du denken: „Ich habe einen verdammt guten Wal getroffen!“

Kennen Sie das? Sie arbeiten mit Excel, Kinder oder Nichten und Neffen mit libreOffice, ein Freund mit Numbers oder Google Tabellen? Und Sie sind erstaunt, dass es in der einen Tabellenkalkulation Funktionen gibt, die in der anderen fehlt. Nervig und ärgerlich (gerade beim Austausch der Tabellen).

Der Zeitschriftenverlag Heise hat sich die Mühe gemacht, sämtliche Funktionen dieser vier Tabellenkalkulationen aufzulisten und zu vergleichen:

https://www.heise.de/mac-and-i/downloads/65/2/1/7/2/5/5/4/Formelfunktionen_Vergleich.pdf

Beim Durchsehen der Liste fällt auf, dass auch in dem geliebten Excel einige (wichtige) Funktionen fehlen, die in einem der anderen Programme integriert sind:

Ostersonntag, Tagname, Monatsname, BasisInZahl (habe ich noch nie vermisst), Laufzeit, ZGZ, ISEMAIL, ISURL, AKTUEL, FORMEL (heißt in Excel: FORMELTEXT), BEREICH.ÜBERSCHNEIDEN, BEREICH.VERBINDEN, POLYNOM, COUNTUNIQUE, FARBE, UMRECHNEN (entspricht EUROKONVERT), B, KOVARIANZ (heißt KOVAR, KOVARIANZ.P und KOVARIANZ.S in Excel), SCHÄTZER.EXP.VOR.MULT, SCHÄTZER.EXP.MULT, KLARTEXT, REGEXEXTRACT, REGEXMATCH, REGEXREPLACE und 59 weitere Funktionen …

Formeln im Vergleich (Ausschnitt)

Aus Langeweile hätte ich eben fast gearbeitet. Man muss aber auch höllisch aufpassen.

Gestern in der Excel-Schulung. Wir üben die WENN-Funktion. Standard-Beispiel: eine Provisionsberechnung. Ein Teilnehmer ruft mich, weil er eine Fehlermeldung erhält:

Ich gestehe: ich habe drei Mal hinschauen müssen, bis ich es entdeckt hatte: das schließende Anführungszeichen bei dem Sonst-Zweig (Wert_wenn_falsch) fehlt.

Auf diese Frage antworte ich mit einem entschiedenen Vielleicht.

Hi René,

ich kriege grad seit 2 h einen Vogel mit Pivot:

Habe unterschiedliche Mitarbeiter, die unterschiedliche Bonisätze bekommen sollen.

Die Bonitabelle liegt in anderem Tabellenblatt.

Damit ich in Pivot für jeden MA den richtigen Bonusbetrag anzeigen kann, habe ich mir mit SVerweis die Sätze in meine Ausgangstabelle geholt.

Und ein Feld berechnet. Soweit alles schön…

…nur, der Bonusbetrag ist um den Faktor 10 zu hoch!

Wer macht da was falsch : ich oder Excel?

Hiielfe! Kannst Du helfen?

Viele Grüße – Angelika

#####

Hallo Angelika,

der Knackpunkt in der Pivottabelle liegt im berechneten Feld. Das kann man leicht zeigen. Bau mal folgende Tabelle auf:

Setze eine Pivottabelle auf, gruppiere die Namen, summiere die Beträge:

Wenn du nun ein berechnetes Feld einfügst – Betrag * Provision – stellst du fest, dass die berechnete Provision doppelt so hoch ist, wie sie sein sollte:

Der Grund: die beiden Provisionssätze für Rene werden summiert (5% + 5% = 10%) und diese Zahl mit der Summe der Beträge multipliziert. Wenn du die Summe mit 5% multiplizieren möchtest, musst du den Provisionssatz (über einen SVERWEIS) reinholen und damit multiplizieren. Dann klappt es:

Sauer macht lustig, der Wald lacht sich tot!

Der Problem mit dem Datum hat mich gestern noch beschäftigt.

In der Zelle D3 steht nichts. Erstaunlicherweise liefert =JAHR(D3) keinen Fehler (wie ich vermutet hätte), sondern 1900. Warum?

Die Antwort: Schreiben Sie in eine Zelle das Datum 05.01.1900. Subtrahieren Sie von diesem Datum die Zahl 1. Setzen die Formel fort. Nach dem 01. Januar 1900 folgt der 00. Januar 1900. Dann ein Fehler:

Und das ist der Grund, warum man von einer leeren Zelle die Jahresinformation auslesen kann. Auch JAHR(0) liefert das Jahr 1900. Steht in der Zelle allerdings ein leerer Text („“) oder #NV, dann ist ein Fehler die Folge:

Das heißt im Umkehrschluss: WENNFEHLER(JAHR(D3);““) fängt keinen Fehler ab, wenn die Zelle D3 nicht gefüllt ist. Lediglich wenn in der Zelle kein Datum, also Text steht. Mit WENNFEHLER kann man diese Information also nicht abfangen.

Heute lebe ich. Morgen putze ich vielleicht.

Ich habe mich heute sehr amüsiert. Ich war in einer großen Firma, die Sie auch kennen. Dort haben mir Mitarbeiter eine große Exceltabelle gezeigt, mit der Bitte, ihnen die Formeln zu erklären und möglicherweise zu verbessern. Das Grundproblem tauchte an sehr vielen Stellen auf: In zwei unterschiedlichen Spalten stehen Datumsangaben. Allerdings: nicht in jeder Zelle.

Es sollen die Datumsdifferenzen berechnet werden. Allerdings nicht Ende – Anfang, da die leeren Zellen ein Ergebnis verfälschen würden. Nun hat ein Kollege – wahrscheinlich über Jahre – verschiedene Formeln eingetragen:

  • =WENN(D2>=1;D2-C2;““)

Ist okay – hier habe ich nichts zu nörgeln.

  • =WENN(D2>=1;D2-C2;“ „)

Das Leerzeichen stört mich; würde ich nicht machen – besser: „“.

  • =WENN(D2>=1;DATEDIF(C2;D2;“d“);““)

Warum einfach, wenn es auch umständlich geht. D2-C2 entspricht DATEDIF(C2;D2;“d“).

  • =WENNFEHLER(D2-C2;““)

Ganz schlecht: D2-C2 liefert keinen Fehler, wenn eine der beiden Zellen leer ist. Das Ergebnis ist beispielsweise -42780.

  • =WENN(ISTFEHLER(D2-C2);““;D2-C2)

Bis Excel 2003 gab es noch nicht die Funktion WENNFEHLER – bis dahin musste man WENN(ISTFEHLER(… schreiben. Falsch und überflüssig!

  • =WENN(UND(C2<>““;D2<>““);D2-C2;““)

Gut: beide Zellen werden überprüft!

Die Funktion ISTZAHL habe ich in der Tabelle nicht gefunden.

Wir haben uns amüsiert, weil das gleiche Problem von einem Anwender auf verschiedene Arten gelöst wurde. Das ist nicht konsistent und auch nicht nachzuvollziehen. Aber man kann ja mal vermuten, was die Ursache des Formelwandels war …

Ich bin gerade etwas neben der Spur. Ist schön da!

Letzte Woche in der Excel-Schulung. Ich erkläre, dass es manchmal besser ist, den Funktionsassistenten zu verwenden, weil dort einige Informationen angeboten werden, manchmal ist die Eingabe über die Tastatur die bessere Wahl, weil Parameter aufgelistet und erklärt werden.

Ein Teilnehmer meldet sich und fragt traurig, warum er keine Formelvervollständigung hat:

och!

och!

Die Lösung war schnell gefunden: In den Option war in der Kategorie „Formeln“ die Option „AutoVervollständigen für Formeln“ deaktiviert.

... und der Teilnehmer war wieder glücklich ...

… und der Teilnehmer war wieder glücklich …

Die Küche sieht aus wie Sau. Hab das Licht ausgemacht. Jetzt geht’s.

Ich erstelle eine 3 x 3-Matrix.

Berechne die inverse Matrix mit der Funktion MINV:

20170210Matrix01

Multipliziere die beiden Matrizen mit der Funktion MMULT – das Ergebnis – na, ja: fast richtig. Ein bisschen Abweichung ist halt häufig in Excel:

20170210Matrix02

Wenn ich die berechneten Zahle der inversen Matrix per Hand eingebe, erhalte ich eine korrekte Einheitsmatrix. Die Rechenungenauigkeit liegt also bei MINV.

Ich spreche drei Sprachen: ironisch, sarkastisch und zweideutig.

Auf nichts ist mehr Verlass:

Ich trage einige Zahlen in Excel ein. Schalte den Autofilter ein, filtere die Daten. Unter der Liste ein Klick auf das Summen-Symbol – die Funktion TEILERGEBNIS mit dem Parameter 9 wird verwendet. Nur die gefilterten Daten werden summiert.

20170207Teilergebnis01

Ich markiere eine Zeile und blende sie aus:

20170207Teilergebnis02

Ich bin irritiert: Die ausgeblendete Zeile wird nicht summiert.

Irritiert deshalb, weil die Hilfe erläutert, dass der Parameter 109 die ausgeblendeten und gefilterten Daten nicht summiert, der Parameter 9 jedoch nur die gefilterten.

Ich schalte den Filter aus, blende die Zeile aus – sie wird JETZT nicht mitsummiert.

Das heißt: der Parameter 9 summiert Werte von ausgeblendeten Zeilen, wenn kein Filter gesetzt ist, summiert sie jedoch nicht, bei einem eingeschalteten Filter.

Da ist doch was faul!

Danke an Maximilian für den Hinweis.

Wer morgens zerknittert aufsteht, hat am Tag die besten Entfaltungsmöglichkeiten!

Ich trage in einer Zelle die Funktion =exp(1) ein, da ich die Konstante e benötige. Ich benenne die Zelle e:

20170206e06

Ich schreibe nun die Formel

=Sin(e) in eine andere Zelle – die Konstante, das heißt: der Name, wird erkannt:

20170206e02

Nach Schließen der Klammer wird die Formel bestätigt:

20170206e03

Das Ergebnis verblüfft; aber des Rätsels Lösung ist schnell gefunden:

AutoKorrektur heißt der Bösewicht!

AutoKorrektur heißt der Bösewicht!

Denken ist wie googeln – nur krasser.

Amüsiert war ich schon. In einer Firma sollte ich letzte Woche Excel-Formeln anpassen. Man erklärte mir, dass jeder Auditvorgang ein Revisionsdatum hat. Möglicherweise auch ein zweites und ein drittes. Wenn es ein drittes Datum gibt, gibt es auch ein zweites. Also: die Tabelle ist gefüllt: entweder 1. Datum oder 1. und 2. oder 1. und 2. und 3. oder alle vier Spalten sind mit einer Datumsinformation gefüllt.

20170129Anzahl

Ein Kollege hatte eine Formel erstellt:

=WENN(H2=““;1;WENN(I2=““;2;WENN(J2=““;3;4)))

Meine Aufgabe war es diese Formel für weitere Datumsangaben anzupassen. Ich war etwas irritiert.

=ANZAHL(G2:J2)

hätte das Gleiche getan. Und ist leichter anzupassen. Die Teilnehmer waren begeistert und glücklich.

Ich brauche keinen Mittelfinger, ich kann das mit den Augen.

Heute in der Excel-Schulung. Ein Teilnehmer zeigt mir seine Lösung, wie er die Anzahl der Zahlen im 90er-Bereich ermittelt hat: Er berechnet die Differenz zweier ZÄHLENWENN-Funktionen:

=ZÄHLENWENN(C8:C37;“<100″)-ZÄHLENWENN(C8:C37;“<90″)

Ich zeige seinen Ansatz der Gruppe. Mit dem Funktionsassistenten rufe ich Zählenwenn auf:

Schritt 1

Schritt 1

Normalerweise trägt Excel im Funktionsassistenten um die Bedingung <100 automatisch die Anführungszeichen ein. Jedoch ein Klick in die Bearbeitungsleiste lehrt mich eines Besseren:

Schritt 2

Schritt 2

Ich fahre fort: tippe ein Minus und rufe erneut den Funktionsassistenten auf:

Schritt 3

Schritt 3

Der Blick in die Bearbeitungsleiste zeigt mir, dass die Anführungszeichen in der ersten Funktion fehlen. Und richtig: Das Bestätigen der Funktion wird mit einer Fehlermeldung quittiert:

Schritt 4

Schritt 4

Schade – denn gerade der Funktionsassistent nimmt Anwendern und Anwenderinnen, die mit Formeln noch nicht so sehr geübt sind, die Arbeit an vielen Stellen ab …

Also doch: Ich zeige dem Teilnehmer ZÄHLENWENNS, die ihm sehr gut gefällt …

Manchmal wäre ein bisschen weniger doch ein bisschen hilfreicher

Excel-Schulung. Ich erkläre die Funktion SUMME. Anschließend eine Übung. Unter der Spalte mit den laufenden Nummern soll die Summe der Preise gezogen werden. Die Preise befinden sich jedoch in einer anderen Spalte.

Ich schaue einer Teilnehmerin über die Schulter: Sie klickt auf die leere Zelle, in der sich die Summe befinden soll:

SUMME

SUMME

Der Vorschlag wird verworfen, indem sie auf der erste Zelle der Zahlenkolonne klickt:

Hier beginnt die SUMME

Hier beginnt die SUMME

Anschließend tippt sie einen Doppelpunkt:

SUMME bis ...

SUMME bis …

Und schaut mich nun erstaunt an, was Excel „tut“. Zugegeben: Excel schlägt eine Zelle vor bis zu der der Bereich ausgedehnt werden könnte. Jedoch ist die Anfängerin überfordert, weil nach der Eingabe eines Doppelpunktes etwas „anderes“ in der Formel steht.

Vielleicht wäre hier keine Hilfe besser gewesen.

Gestern bei offenem Fenster geschlafen. 10.569 Mücken gefällt das.

Hallo Herr Martin,
ich habe eine Liste, bei der ich Auswertungen erstellen soll. Es geht dabei um eine Gewichtung. Folgender Schlüssel liegt der Tabelle zugrunde:

20160912gewichtung01

Ich erstelle eine Formel:

=ZÄHLENWENN(C5:C70;“*x*“)*1+ZÄHLENWENN(C5:C70;“*P*“)*2+ZÄHLENWENN(C5:C70;“*EG*“)*3+
ZÄHLENWENN(C5:C70;“*En*“)*4+ZÄHLENWENN(C5:C70;“*Ew*“)*5+ZÄHLENWENN(C5:C70;“*Ek*“)*2+
ZÄHLENWENN(C5:C70;“*Em*“)*3+ZÄHLENWENN(C5:C70;“*B*“)*4+ZÄHLENWENN(C5:C70;“*A*“)*4+
N(„ZÄHLENWENN(C5:C70;““*Aw*““)*4″)+ZÄHLENWENN(C5:C70;“*K*“)*1+ZÄHLENWENN(C5:C70;“*V*“)*1+ZÄHLENWENN(C5:C70;“*U*“)*1

Vielleicht nicht clever – aber nachvollziehbar. Der Wert „Aw“ wurde auskommentiert – deshalb die Funktion „N“. Allerdings – an einer Stelle rechnet er nicht richtig – ich finde den Fehler nicht:

20160912gewichtung02

Die Antwort: Man muss schon genau hinschauen. Sie haben in die Matrix weitere Informationen eingetragen. Bei Frau Weiß finden sich die beiden Texte SPX. Dort wird das „x“ natürlich auch mitgezählt. Diese Texte dürfen Sie nicht in die Tabelle schreiben.

20160912gewichtung03

Ist das nur boshaft oder schon bösartig?

Ich hätte es nicht geglaubt. Sehen Sie selbst:

In einer gestalteten Tabelle wurden mehrere Zellen verbunden – hier: die Zellen E1:E3:

20160902Verbinden01

Wählt man nun das Werkzeug „Format übertragen“

20160902Verbinden02

und klickt (aus Versehen?) auf andere Zellen. Am besten solche, in denen Zahlen stehen:

20160902Verbinden03

Die Zellen werden nun nicht zu einer Zelle verbunden, sondern der Inhalt der unteren Zelle wird lediglich ausgeblendet. Davon kann man sich mit einem Blick auf die Summe überzeugen – das Überschlagen der sichtbaren der Spalte G Zahlen kann niemals die Summe 5538 ergeben:

20160902Verbinden04

Der Grund: Deaktiviert man die Option „Zellen verbinden“:

20160902Verbinden05

so taucht die verborgene (die verborgenen) Zelle(n) wieder auf – sie waren niemals gelöscht, sondern nur ausgeblendet:

20160902Verbinden06

Auf dieses unglaubliche Phänomen hat mich Andreas Thehos aufmerksam gemacht – danke dafür!

Hoffnung ist nicht die Gewissheit, dass alles gut wird. Sondern der Glaube daran, dass etwas Sinn macht.

Uff!

In einer Liste stehen Zahlenwerte. Daneben eine Spalte mit Berechnungen, beispielsweise die Mehrwertsteuer:

20160831Sort01

Trägt man nun statt der Zelle H2 den Tabellennamen und den Zellnamen ein (dies passiert, wenn man beispielsweise bei der Formeleingabe auf ein anderes Blatt wechselt), dann hat Excel kein Problem damit.

Jedoch: Sortiert man die Liste, so erstaunt das Ergebnis: Die Werte sind falsch. Schaut man sich die Formeln an, stellt man fest, dass die Bezüge nicht sortiert wurden. Anders wenn wenn man statt Blattname!Zellname nur Zellname eingegeben hätte.

Uuuuuaaaaahhhh!

Uuuuuaaaaahhhh!

Danke an Andreas Thehos für diesen wunderbaren Hinweis!

Nett kann ich auch – bringt aber nix!

Microsoft hat in Excel 2013 das Analysewerkzeug „Inquire“ eingeführt, das in Excel 2016 nicht geändert wurde. Damit erspart man sich die umständlich Suche, ob es Verknüpfungen, ausgeblendete Zeilen, Spalten, Blätter gibt, ob Zahlen als Text formatiert wurden, wo Formeln stecken, die einen Fehler liefern, wo Zirkelbezüge zu finden sind, …

Inquire

Inquire

Damit ist Microsoft auf dem richtigen Weg. Für alle, die fremde Dateien analysieren möchten („Was hat der Kollege denn da gemacht?“) oder die zwei Dateien miteinander vergleichen möchten – ein richtiger Schritt in die richtige Richtung. Jedoch mir fehlen:

  • eine Anzeige für „Genauigkeit wie anzeigen“
  • Überhaupt einige Optionen, die Anwender zur Verzweiflung bringen können: „in Zellen mit Nullwerten eine Null anzeigen“, „anstelle der berechneten Werte Formeln anzeigen“, „Dezimalkomma automatisch einfügen“, „1904-Datumswert, „Iterative Berechnung aktivieren“, „manuelle Arbeitsmappenberechnung“ und einige andere hübsche Optionen
  • ausgeblendete Zeilen und Spalten zu finden ist klasse – was aber, wenn die Zeilenhöhe auf 0,1 pt gesetzt wurde?
  • zu schmale Spalten – Zahlen werden mit dem Zahlenzeichen ########## dargestellt.
  • zu viele oder widersprüchliche bedingte Formatierungen
  • Rundungsfehler bei Zahlenformaten
  • Unsinnige Zahlenformate (beispielsweise 0,0 „%“). Allerdings: Wer entscheidet, was unsinnig ist?
  • Zahlenformate wie ;;
  • Zellen mit Leerzeichen
  • Zellen, die Text enthalten mit einem Leerzeichen am Ende: „Rene Martin“ ist etwas anderes als „Rene Martin „
  • Und schließlich: Objekte: Diagramme, die auf ein Pixel verkleinert wurden, Bilder, die auf Zellen liegen und so aussehen, als wären es Elemente der Zelle oder auch weiße Rechtecke, die auf einer Zelle liegen:
Wird leider nicht vom Inquire gefunden

Wird leider nicht vom Inquire gefunden – unter dem Rechteck befindet sich die Zahl 3000

Fazit: Guter Ansatz, muss jedoch erweitert werden. Wenn Microsoft mich fragen würde – ich könnte Ihnen viele Dinge nennen, die Anwendern Probleme verursachen.

Und: ein dankeschön an Stefan, der mir geholfen hat, das Teilchen auseinanderzunehmen.

Ich freue mich, wenn es regnet. Denn wenn ich mich nicht freue, regnet es auch. (Karl Valentin)

Sehr geehrter Herr Dr. Martin,

zu Ihrem obigen Buch habe ich eine kurze Frage. Auf Seite 125 habe ich die Aufgabe 7.2.1 gelöst. Mit der Zielwertsuche komme ich auf zwei Lösungen, wie Sie anhand der beigefügten Tabelle sehen können.

Gibt es zwei Lösungsmöglichkeiten?

Freundliche Grüße

CR

Zielwertsuche

Zielwertsuche

Hallo Herr R.,
in D3 steht die Formel =B3+C3 // in E3 jedoch =(B4*2)+C4.
Deshalb kommen zwei unterschiedliche Ergebnisse raus. Die zweite Tabelle ist korrekt durchgerechnet.
schöne Grüße
Rene Martin
20160727Zielwertsuche02

Excel hat es nicht so mit den Zahlen, oder?

Wie kann denn so etwas sein? Ich erhalte einen Download aus SAP, bewegen mich mit [Strg] + [↓] ans Ende der Liste (Zeile 572), markiere die Spalte (C) und lese in der Statuszeile Anzahl: 636. Wer kann hier nicht zählen?

Zählt Excel falsch?

Zählt Excel falsch?

Die Antwort: Drücken Sie erneut [Strg] + [↓]. Weiter unten auf dem Tabellenblatt befindet sich ein weiterer Bereich, der auch mitgezählt wird, den Sie aber nicht sehen …

Noch mehr Daten - also doch richtig gezählt.

Noch mehr Daten – also doch richtig gezählt.

Excel hält sich nicht an die Theoreme der Prädikatenlogik

Kennen Sie das: Ich benötige eine Liste, die sich dynamisch fortsetzt. In einer Zelle wird ein Wert eingetragen – die Nummer eines Zählers soll nur bis nur eingetragenen Nummer angezeigt werden.

Die Formel:

=WENN(A9=$B$4;A9+1;““)

funktioniert problemlos –

=WENN(A9=$B$4;““;A9+1)

jedoch nicht.

Einer geht und einer nicht.

Einer geht und einer nicht.

20160626weiter01

Der Grund ist offensichtlich. Befindet sich in einer Zelle ein leerer String („“), kann Excel zwar problemlos den Ausdruck

12 = „“

auswerte und FALSCH zurückgeben. Jedoch bei den Operatoren der Ungleichheit (<, >, <= und >=):

12 < „“

liefert Excel einen Fehler. Also aufpassen: man darf nicht immer einfach rumdrehen!

Humor ist der Knopf, der verhindert, dass uns der Kragen platzt. (Joachim Ringelnatz)

Nicht jede Formel darf sein!

Ich gestehe: Es ist mir lange Zeit nicht aufgefallen. Wahrscheinlich deshalb, weil ich die Symbole der Bedingten Formatierung in Excel nicht besonders schätze. Während man bei den Hintergrundfarben, Schriftfarben und Zellrahmen beliebige Formeln verwenden darf, ist dies bei den Skalen, Datenbalken und Symbolsätzen eingeschränkt. Genau:

Werte sind kein Problem

Werte sind kein Problem

Den Inhalt einer Zelle mit einem festen Wert abzugleichen ist kein Problem.

Absolute Bezüge geht auch

Absolute Bezüge geht auch

Den Wert auszulagern und mit einem absoluten Bezug darauf zuzugreifen ist auch kein Problem.

HEUTE() funktioniert

HEUTE() funktioniert

Auch Formeln wird HEUTE() stellen kein Problem dar.

Relative Bezüge leider nicht.

Relative Bezüge leider nicht.

Das Erstaunen ist groß, wenn man relative Bezüge verwendet. DAS ist verboten. Noch einmal hinschauen – das Kombination bietet „Formel“ an und nicht „Formel mit absolutem oder keinem Bezug“.

Okay – wahrscheinlich eine Kurzschreibweise …

Ein Kompromiss ist nur dann gerecht, brauchbar und dauerhaft, wenn beide Parteien damit gleich unzufrieden sind. (Henry Kissinger)

Wir wissen, dass Excel eine Rechenungenauigkeit hat. Aber so ungenau?

Man nehme drei Zahlen (beispielsweise Körpergrößen) und berechne den Mittelwert (C6). Von jeder der drei Körpergrößen wird die Differenz zum Durchschnitt berechnet (D2:D4). Diese drei Zahlen werden summiert (D6) – das Ergebnis sollte eigentlich 0 ergeben. Eigentlich …

Sehr ungenau

Sehr ungenau

Formel tippen oder Funktionsassistent?

Bei unserem letzten Excel-Stammtisch haben wir uns darüber unterhalten, wann eine Formel einfacher zu verstehen ist: beim Tippen oder wenn man den Funktionsassistenten zu Hilfe nimmt. Es gibt eine Reihe von Funktionen, da erhalten Sie Informationen beim Tippen, beispielsweise SVERWEIS, TEILERGEBNIS oder WOCHENTAG:

20160605Texte01

Assi vs. Tippen

Assi vs. Tippen

Bei anderen Funktionen ist es umgekehrt, beispielsweise RUNDEN:

20160605Texte03

Assi vs. Tippen

Assi vs. Tippen

Heute habe ich mir die Funktion RÖMISCH angesehen und musste schmunzeln:

20160605Texte05

Assi vs. Tippen

Assi vs. Tippen

„Typ ist eine Zahl, …“ ist völlig aussagelos.

„Knapper, knapper und knapper“ ist auch nicht sehr (vielver-)sprechend und wenig aussagekräftig.

Zur Ehrenrettung von Excel (und denen, die Hilfetexte erstellen) muss gesagt werden, dass das QuickInfo erläutert, dass:

Erstes Knapper: LDVLIV für 499

Zweites Knapper: XDIX für 499

Drittes Knapper: VDIV für 499

Alles klar?

Ich verstehe es nicht: Heute bin ich früh aufgestanden. Aber da war kein Wurm.

Ich bin nicht der einzige Nörgler.

Bei den Vorbereitungen zum einem Seminar „Excel und Statistik“ stoße ich auf der Microsoft-Seite

https://support.office.com/de-de/article/Verwenden-der-Analyse-Funktionen-um-komplexe-Datenanalysen-auszuf%C3%BChren-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6#__toc309744619

auf eine Erklärung des Zwei-Stichproben F-Tests:

„In dem Tool wird der Wert f einer F-Statistik (oder F-Verhältnis) berechnet. Ein f-Wert nahe 1 beweist, dass die Varianzen der Grundgesamtheiten gleich sind. In der Ausgabetabelle: Wenn f < 1 ist, gibt „P(F <= f) einseitig“ die Wahrscheinlichkeit an, dass ein Wert der F-Statistik beobachtet wird, der kleiner als f ist, wenn die Varianzen der Grundgesamtheiten gleich sind und „Kritischer F-Wert bei einseitigem Test“ einen kritischen Wert kleiner als 1 für die ausgewählte Irrtumswahrscheinlichkeit Alpha angibt. Wenn f > 1 ist, gibt „P(F <= f) einseitig“ die Wahrscheinlichkeit an, dass ein Wert der F-Statistik beobachtet wird, der größer als f ist, wenn die Varianzen der Grundgesamtheiten gleich sind und „Kritischer F-Wert bei einseitigem Test“ einen kritischen Wert größer als 1 für Alpha angibt.“

Conrad Carlberg kommentiert diese Erläuterungen in seinem Buch „Statistical Analysis: Microsoft Excel 2010“ auf Seite 157:

Got it? Neither did I.

 

Wie heißt die Formel?

Hallo Herr Martin,

ich habe ein Problem. Ich habe in einer Spalte Zahlen. Ich möchten von diesen Zahlen die letzten drei Stellen, also die drei Ziffern vor dem Komma abschneiden. Der Rest soll gerundet werden. Mit welcher Formel mache ich das?

so?

so?

Die Antwort: Dafür gibt es leider keine Formel. Aber wenn in G2 der Wert stehen, dann erhalten Sie den gewünschten Wert mit der Formel:

=RUNDEN(G2;-3)/1000

Enttäuscht?

Am Ende des Geldes bleibt immer so viel Monat übrig

Hallo Herr Martin,

ich finde in Excel einfach keine Funktion QUARTALSENDE. Konkret: Ich benötige den letzten Tag (als Datum) des Quartals, in dem sich ein Datum befindet. Also beispielsweise:

1.1.2016 -> 31.03.2016

2.2.2016 -> 31.03.2016

5.5.2016 -> 30.06.2016

und so weiter.

Ist das Quartal nicht zu Ende?

Ist das Quartal nicht zu Ende?

Das ist richtig: DIESE Funktion gibt es in Excel leider nicht. Man muss sie zusammenbauen. Wenn in A1 das Datum steht, dann beispielsweise so:

=DATUM(JAHR(A1);MONAT(A1)-REST(MONAT(A1)+2;3)+3;1)-1

oder so:

=DATUM(JAHR(A1);OBERGRENZE(MONAT(A1);3)+1;1)-1

(beide Funktionen suchen den letzten Monat des Quartals, addieren 1 (also verwenden den nächsten Monat) und von diesem Monat den ersten Tag. Davon wird 1 abgezogen. Oder auch so:

=MONATSENDE(A1;REST(3-MONAT(A1);3))

Man ermittelt die Anzahl der Monate, die zu dem Datum dazu gezählt werden muss.

Und wo ist das bei mir?

Kennen Sie folgendes Phänomen? In Excel-Schulungen werde ich ab und zu gefragt:

Ich zeige die Funktion SUMMEWENNS, die Teilnehmer schauen mir zu. machen anschließend mit und fragen mich nun, wo denn die Eingabefelder bei Ihnen verborgen sind.

Richtig, wenn man mit dem Funktionsassistent die Funktion SUMMEWENNS öffnet, sieht sie folgendermaßen aus:

Start

Start

Trägt man die Informationen ein, öffnet sich ein weiteres Pflichtfeld – allerdings erst dann:

und go!

und go!

Sehr unglücklich gemacht, dass nicht die ersten DREI Parameter angezeigt werden. Kein Trost: Während bei ZÄHLENWENNS die Parameter korrekt angezeigt werden, fehlen sie bei MITTELWERTWENNS ebenso.

Nomen est omen?

Eigentlich sind Namen in Excel ein prima Sache:

  • Man kann einer Zelle einen Namen geben und verweist nun in Formeln auf diesen Namen und damit auf die Zelle.
  • Man kann einem Bereich einen Namen geben und kann nun mit dem Namen rechnen.
  • Man kann über den Namensmanager einem Namen einen festen Wert als Konstante zuweisen.
  • Man kann über den Namensmanager einem Namen einen dynamischen Bereich zuweisen, der mit einer Formel ermittelt wird.

Vor allem: Name sind sprechend und können so leicht verwendet werden, wenn man das Konzept verstanden hat. Jedoch das Konzept hat einen großen Haken:

Namen können sowohl an die Datei gebunden sein als auch an das Tabellenblatt. Wenn Sie VBA können, wissen Sie sicherlich, dass sowohl das Workbook-Objekt als auch das Worksheet-Objekt einen Namen haben können.
Kostprobe gefällig: Markieren Sie ein Zelle oder einen Bereich auf einem Tabellenblatt. Geben Sie ihm einen Namen. Kopieren das Blatt in eine andere Datei. Kopieren Sie das Blatt noch einmal in eine andere Datei. Was passiert? Nicht die Datei, sondern die beiden Tabellenblätter haben eine Zelle oder einen Bereich, die den gleichen Namen tragen. Wenn man nun eine Formel verwendet:

Wo sind test2 und test3?

Wo sind test2 und test3?

ist nicht klar, von welchem Blatt sich Excel den Bereich zieht. Der Namensmanager zeigt es deutlich an:

Mehrere gleichlautende Namen

Mehrere gleichlautende Namen

Die zuerst erstellten (hinüberkopierten) Namen gelten global für die Abreitsmappe (und darauf nimmt test1, test2 und test3 Bezug), die danach erstellten Namen gelten nur lokal für das Blatt.

Das heißt ganz einfach: Höllisch aufpassen mit den Namen! Und: bloß keine Namen mehrmals verwenden! Sonst ist Chaos vorprogrammiert.

Das heißt auch: gutes Konzept, aber leider nicht ganz durchdacht.

 

Wenn das Wörtchen WENN nicht wär‘ …

Sehr geehrter Herr Dr. Martin,

Wahrscheinlich haben Sie wesentlich schneller eine Lösung parat als ich, die ich nun schon stundenlang damit rummache und jetzt irgendwie aufgebe.

Es geht um dieselbe Tabelle, also diese Zeitberechnung.

Nun soll aber noch folgendes dazu berechnet werden.

Wenn die Stundenanzahl >= 6 Stunden sollen 0,5 Stunden abgezogen werden, wenn die Stundenanzahl >= 10 Stunden soll eine Stunde abgezogen werden.

Ich habe versucht, mich langsam heran zu tasten und habe sogar die > 6 Stunden und >10 Stunden einzeln herausbekommen. Allerdings bekomme ich nun beim besten Willen diese ganzen Schachteln nicht zusammengesetzt…(Das mit dem „=“ dazu hat auch nicht so funktioniert…)

Haben Sie eine (schnelle) Lösung oder eine passende Erklärung mit einem Link?

Die Tabelle mit meinen Versuchen ist angehängt. Wie ich gelesen hatte, muss man dazu die Uhrzeit wieder in eine normale Zahl formatieren, was ich getan habe.

Herzlichen Dank für Ihre Bemühungen.

####

Stundenberechnung

Stundenberechnung

Hallo Frau P.,

Ich denke, Ihr Problem resultiert aus Folgendem: Sie sollten sich einen Baum malen, der die verschiedenen Fälle auflistet: tagsüber gearbeitet oder nachts? In beiden Fälle: mehr als 10 Stunden – > ja: eine Stunde abziehen. Nein: – > wenn mehr als sechs Stunden: -> ja, 0,5 Stunden abziehen – sonst nichts.

Und so müssen Sie die Formel eingeben.

Kommen Sie damit klar?

####

Hallo Herr Dr. Martin,

Jetzt habe ich gesehen, dass Sie die Sachen ja alle schon richtig ausgefüllt haben.

Das Resultat ist in „Zahl“ formatiert. Ich glaube, es wäre auch mit der „Uhrzeit 37:…“ gegangen. Allerdings nicht mehr in dieser Version.

Ich kann es nur hinnehmen und Ihnen danken, dass Sie es eingerichtet haben. Vom Verständnis bin ich leider noch etliches entfernt.

Aber ich denke, meine Kolleginnen werden sich freuen, endlich eine funktionierende Zeitberechnung zu haben. Bleibt zu hoffen, dass ihnen nicht noch etwas Neues einfällt…

Also noch einmal: Herzlichen Dank!

Ich wünsche Ihnen noch einen schönen Tag.

####

Gerne, Frau Pojer,

wenn Sie es verstehen möchten, machen Sie Folgendes:

Klicken Sie auf eine Zelle mit einer Formel. Klicken Sie auf den Funktionsassistenten.

Sie sehen die Bedingung. Klicken Sie nun in die Bearbeitungszeile auf den Teil den Sie sehen möchten. So können Sie sich den Baum entlanghangeln:

Der Funktionsassistent

Der Funktionsassistent

Umgekehrt: gehen Sie an ein solches Problem mit einem Blatt Papier und einem Stift ran. Ich „mal gerne Bäume“. Sicherlich kennen Sie diese Ablaufdiagramme: sie helfen:

Ein kleines Diagramm - als Hilfe

Ein kleines Diagramm – als Hilfe

Datum gesucht

Ich weiß; ich bin ja auch nicht glücklich darüber. Wir haben eine Liste, in der in einer Spalte dummerweise Datumsangaben, Texte, Zahlen (Kennziffern) und gar nichts eingetragen wird. Ich habe nun die Aufgabe, über eine Hilfsspalte die Zellen herauszufinden, in denen ein Datum steht. Allerdings: Wenn ich die Funktion ISTZAHL verwende, werden auch die „echten“ Zahlen gefunden. Die will ich aber nicht. Gibt es wirklich keine Funktion „ISTDATUM“? Auch nicht in Excel 2016?

Wo ISTDATUM?

Wo ISTDATUM?

Die Antwort: Wenn es möglich ist, prüfen Sie die interne Zahl, also beispielsweise:

=UND(I4>40000;ISTZAHL(I4))

Oder Sie verwenden die Funktion ZELLE. Sie liefert mit dem Parameter „format“ den Wert D1, der anzeigt, dass es sich um ein Datum handelt:

Geht doch!

Geht doch!

 

kon·se·quent [ˌkɔnzeˈkvɛnt]

Nein, konsequent ist Excel nicht. An einigen Stellen muss man bei Texten Anführungszeichen eintragen, bei einigen kann man an anderen Stellen wiederum darf man nicht.

Ich habe im Artikel

Die bedingte Formatierung funktioniert nicht

darauf hingewiesen. Bei der Funktionseingabe ist Excel auch inkonsequent:

Während man bei Tippen einer Funktion immer die Anführungszeichen eingeben muss, muss man es im Funktions-Assistenten nicht. Das Wort Achtung wird beim Verlassen des Textfeldes umgewandelt in „Achtung“. Jedoch: Wenn man im Funktionsassistenten eine innere Funktion einträgt, beispielsweise

und(B2=Fotografie;G2>100)

wird dies mit einer Fehlermeldung quittiert. Nicht konsequent, oder?

Manchmal muss man, manchmal muss man eben nicht.

Manchmal muss man, manchmal muss man eben nicht.

Meine Empfehlung: Wenn Sie nicht wissen, ob man muss oder nicht: Tragen Sie die Anführungszeichen ein.

Was denn Excel zusammengefügt hat, soll ein Mensch nicht scheiden.

Hallo. Ich brauche einen Tipp. Ich erhalten einige Male im Jahr eine Tabelle mit vielen Zahlen. Da die Zeilen keine eindeutige ID haben, habe ich mit der Funktion VERKETTEN mehrere Felder konkateniert (zusammengefasst), so dass ich eine ziemlich eindeutige ID habe. Ich wollte diese Formel weiter runterziehen, weil sich die Liste ab und zu erweitert. Jedoch – Excel zeigt in den leeren Zeilen Fehler. Das verstehe ich nicht: leer & leer & leer müsste doch leer sein, oder?

VERKETTEN - klappt gut, aber irgendwann mit Fehler

VERKETTEN – klappt gut, aber irgendwann mit Fehler

Die Antwort: Sie haben die Tabelle in eine „intelligente Tabelle“ verwandelt (Einfügen / Tabellen / Tabelle). Die Formel in der Zelle lautet:

=VERKETTEN(Tabelle1[@ProdH];Tabelle1[@[BG Cd]];Tabelle1[@[BD / BU]];Tabelle1[@[Main Hier. Name]];Tabelle1[@[Sold-to party]];Tabelle1[@[MAT15 Nbr]];Tabelle1[@[DQ Conf]])

also: Tabellenname Tabelle1[Spaltenname]. Das impliziert: gleiche Zeile wie Formelzelle.

Allerdings befindet sich keine Tabelle neben den neuen Zellen unterhalb der Tabelle. Wenn es Sie stört, können Sie um die Funktion die Formel WENNFEHLER bauen, also:

=WENNFEHLER(VERKETTEN(Tabelle1[@ProdH];Tabelle1[@[BG Cd]];Tabelle1[@[BD / BU]];Tabelle1[@[Main Hier. Name]];Tabelle1[@[Sold-to party]];Tabelle1[@[MAT15 Nbr]];Tabelle1[@[DQ Conf]]);““)

Oder Sie schreiben den Zellbezug „per Hand“ in die Formel:

=VERKETTEN(B1;C1;D1;G1;J1;L1;T1)

Dann erhalten Sie auch keine Fehler.

Vier von drei Leuten können nicht rechnen.

Heute in der Excel-Schulung. Eine Teilnehmerin zeigte mit eine Datei. Mit fiel auf, dass der Filter (hier: Black-Mitglieder) eine andere Zahl lieferte als die Funktion ZÄHLENWENN. Eine Pivottabelle lieferte das gleiche Ergebnis wie der Filter. Die Ergebnisse von ZÄHLENWENN waren um 1 zu groß.

Kann Excel nicht bis drei zählen?

Kann Excel nicht bis drei zählen?

Ich gestehe – ich habe eine Weile gesucht. Bis ich entdeckt hatte, dass über der Tabelle einige Zellen ausgeblendet waren. Und: richtig – dort stand die Liste, die als Bereich für die Datenüberprüfung verwendet wurde. Da ZÄHLENWENN die ganze Spalte zählte … Tja – halt einer zu viel.

Und hier wurde der Übeltäter versteckt.

Und hier wurde der Übeltäter versteckt.

Nicht ganz rund, die Sache

Ich weiß, ich weiß – Excel hat Rundungsprobleme. Die Sache mit der Gleitkommadarstellung und -berechnung ist schon hinlänglich beschrieben worden, beispielsweise bei:

http://www.cpearson.com/excel/rounding.htm

Aber ist jemandem schon Folgendes aufgefallen: Trägt man in Excel die Zahlen -0,3 und -0,2 untereinander in zwei Zellen ein, markiert diese und zieht sie nach unten, so klappt es. Ebenso bei -1,3 und -1,2. Jedoch bei -2,3 und -2,2 steht beim Herunterziehen in der entsprechenden Zelle nicht 0 sondern der Rundungsfehler -9,76996261670138E-15. Ebenso bei -3,3 und -3,2. Jedoch taucht dieser Fehler bei den Startwerten -2,4 und -2,3 nicht auf.

Ein seltsam Ding - ganz rund ist es ja nicht ...

Ein seltsam Ding – ganz rund ist es ja nicht …

Übrigens: einen anderen Rundungsfehler erhält man auch bei der Berechnung:

=5*(0,5-0,4-0,1)

Day after day – oder: So vergeht Jahr um Jahr, und es bleibt wie es war …

Anwender reiben sich verwundert die Augen: In Excel 2013 wurde die Funktion TAGE eingeführt. Damit kann man die Differenz zweier Datumsangaben in Tagen berechnen. Warum diese Funktion? Keine Ahnung – Excel kann doch seit Beginn seiner Existenz Datum2 – Datum1 berechnen. Da Datumsangaben intern als serielle Zahlen verwaltet werden, war das nie ein Problem in Excel. Auch die Funktion =DATEDIF(Anfangsdatum;Enddatum;“D“) konnte dies und hat das Gleiche erledigt. Ein Blick in die Liste der Funktionen: Nein, Excel 2013 hat leider keine Funktion MONATE und auch keine JAHRE. Vielleicht haben sie es vergessen. Wir warten auf die Version 2016. Erstaunt reiben wir uns hier die Augen – nein, sorry, auch in der aktuellen Version 2016 gibt es keine Funktion MONATE oder JAHRE. Geduld … vielleicht in der nächsten Version.

MONATE und JAHRE fehlt noch immer.

MONATE und JAHRE fehlt noch immer.

Feiertage wären klasse

Vor ein paar Tagen erreichte mich folgende Anfrage:

Sehr geehrte Damen und Herren,

zu dem in Betreff genannten Thema haben wir noch eine Frage:  Die Videoanleitung  zum Erstellen eines Internationalen Kalenders konnten wir gut nutzen. In dieser Anleitung wird u.a. geschildert, wie man Feiertage durch eine bedingte Formatierung farblich hervorhebt. Ganz schick wäre es noch, wenn zu diesem farblich markierten Feiertage auch automatisch der Feiertagsname mit angezeigt werden könnte. Ein entsprechendes Tabellenblatt mit diesen Informationen wurde im Verlaufe der Anleitung angelegt. Bei festen Feiertagen wie Neujahr. 1. Mai etc, könnte man dies händisch lösen, doch bei variablen Feiertagen wie Ostern und Pfingsten etc. wäre es wünschenswert, wenn diese gleich automatisch mit angezeigt werden. Leider wurde in dieser Videoanleitung nicht darauf eingegangen. Mit welcher Funktion kann der Feiertagsname automatisch angezeigt werden? Danke schon vorab für Ihre Hilfe.

Mit freundlichen Grüßen / with best regards

#####

Zur Info: Ich habe einen Kalender erstellt, der – nach Änderung des Jahres die Feiertage farblich kennzeichnet. Das klappt mit der bedingten Formatierung und der Funktion ZÄHLENWENN gut und einfach. Die Feiertage (hier: die bayrischen) habe ich auf ein zweites Tabellenblatt ausgelagert.

Die Feiertagsliste

Die Feiertagsliste

Der Kalender

Der Kalender

Ich habe zirka eine halbe Stunde benötigt, damit die Feiertage angezeigt werden – eine hübsche kleine Fingerübung:

Das Ergebnis

Das Ergebnis

Wer knobelt mit? Den ersten Kalender könnt Ihr unter Kalender herunterladen.

Viel Spaß im neuen Jahr mit Excel.

Rene

Tempus fugit: Ihr Video2Brain-Training: Excel 2013: Tipps, Tricks Troubleshooting – Frage zur Zeitberechnung

Sehr geehrter Herr Dr. Martin,

ich habe Ihr o. g. Training sehr interessiert durchgearbeitet (fast alles) und finde es auch äußerst hilfreich.

Nun habe ich folgendes Problem bei Ihrem Punkt Excel-Rechnen mit Uhrzeiten.

Ich habe eine Tabelle, in die Studierende Ihre Arbeitsstunden dokumentieren müssen. Wenn ich alles genau so formatiere wie Sie es in Ihrem Film gezeigt haben, sind die noch nicht ausgefüllten Zellen bei 24:00 Stunden. Eine Summenberechnung führt zu sehr hohen Stundenwerten. Eigentlich müsste ja 0:00 drin stehen, doch tut es bei der Berechnung mit 1-C3+B2 (Beispiel) nicht.

Bei einer Tabelle wie der Ihren im Film ist das kein Problem, da sie vollständig ausgefüllt ist. Bei meiner nicht ausgefüllten Tabelle ist es leider ein Problem. Vielleicht könnten Sie mir hier helfen. Wahrscheinlich gibt es auch da einen kleinen Trick mit großer Wirkung.

Nix arbeiten - und Stunden abrechnen!

Nix arbeiten – und Stunden abrechnen!

Sehr geehrte Frau P.,

habe ich das SO in meinem Video gezeigt? Dann ist mir ein Fehlerchen unterlaufen. Asche auf mein Haupt! Peinlich!

In der Zelle D2 stand die Formel:

=WENN(B2<C2;C2-B2;1-B2+C2)

Wenn B2 und C2 leer sind, dann ist B2 nicht kleiner als C2 – also wird gerechnet: 1 (Tag) – keine Uhrzeit – keine Uhrzeit → also: 1 Tag.

Die Formel müsste korrekt lauten:

=WENN(B2<=C2;C2-B2;1-B2+C2)

dann klappt es.

schöne Grüße

Rene Martin

Und schon klappt es!

Und schon klappt es!

Mein Gedächtnis ist schlecht. – Wie schlecht? – Wie schlecht ist was?

Ich verstehe es nicht. Kennen Sie einen Rat?

Ich habe eine Tabelle mit Abteilungsnummern. Da einige Abteilungen in der Form 4.2.2 oder 3.1.5 vorliegen, muss ich sie als Text formatieren oder ein Apostroph voranstellen. Soweit so gut.

Da ich das Apostroph nicht mag, ignoriere ich den „Fehler“ (es ja kein Fehler).

Fehler: ignorieren!

Fehler: ignorieren!

Sieht prima aus:

klasse!

klasse!

Allerdings: Sobald ich die Abteilungsnummer ändere – beispielsweise von 4.1.1 zu 4.1.5 – erscheint das grüne Eck von Neuem. Ich wollte doch den Fehler ignorieren:

hallo - ich bin wieder da!

hallo – ich bin wieder da!

Die Antwort: Excel hat nur die globale Einstellung: „Fehlerüberprüfung nicht aktivieren“ in den Optionen oder kann lokal einen Fehler zu ignorieren. Sobald der Inhalt geändert wird, wird die Fehlerüberprüfung wieder aktiviert. DAS kann man leider nicht abstellen – das heißt: es gibt keine Option HIER bitte nicht mehr fragen. Das heißt: Sie müssen sich leider für eine der beiden Varianten entscheiden: IMMER deaktivieren oder lokal ausschalten, und wenn die Informationen geändert werden, dann erneut ausschalten.

mal links, mal oben

Ist Ihnen folgender erklärbarer, aber auf den ersten Blick verwirrender Algorithmus aufgefallen?

In einer Exceltabelle befindet sich ein Zahlenblock. Klickt man in der er ersten Zelle daneben auf das Summensymbol, schlägt Excel die Reihe links daneben als Bereich vor. Eine Zelle tiefer wird ebenfalls die Zeile daneben vorgeschlagen. In der dritten Zeile jedoch die beiden Zahl (also die Summen) darüber.

Summe20151113_01

Summe20151113_02

Summe20151113_03

Das Algorithmus, der dahinter steckt, ist klar: Excel überprüft zuerst die Zellen darüber. Befindet sich keine Zahlen darin, werden die Zellen links neben der aktuellen Zelle geprüft (F2). Bei F3 erkennt Excel die Summe darüber und schlägt die gleiche Formel erneut vor. Würde in der Zelle darüber keine Funktion, sondern eine Zahl stehen, würde diese vorgeschlagen werden. In der dritten Zelle F4 werden zwei Formeln über der aktuellen Zelle (mit Zahlen als Ergebnis) erkannt und nun diese vorgeschlagen.

Kein Kommentar? – Doch

Okay – übersichtlicher wird die Formel sicherlich nicht, aber man kann mit der Funktion N etwas hineinkommentieren. Zu Erläuterung: N(„Beliebiger Text“) liefert den Wert 0. Und an den entsprechenden Stellen in einer Funktion kann man so etwas kommentieren. Einem Teilnehmer meiner Schulung gefiel dies heute sehr.

Kommentare in Funktionen - geht doch!

Kommentare in Funktionen – geht doch!

Geld ist nichts. Aber viel Geld, das ist etwas anderes. (Georg Bernard Shaw)

Es ist schon ein seltsames Ding um Excel. Trägt man in eine Zelle den Text WAHR ein und multipliziert diese Zelle (genauer: den Wert dieser Zelle) mit 1, so erhält man 1. Bei FALSCH lautet das Ergebnis 0. Das bedeutet, dass WAHR = 1 und FALSCH = 0.

Eben. In vielen Funktionen, die die beiden Parameter 0 oder 1 verlangen, kann man auch WAHR und FALSCH eingeben. Und umgekehrt.

Beispielsweise SVERWEIS. Oder RMZ. Analog: ZINS, ZZR, BW.

Jedoch: =KUMZINSZ(2,5%/12;12*10;50000;1;10;1) berechnet korrekt -901,01. Allerdings liefert =KUMZINSZ(2,5%/12;12*10;50000;1;10;WAHR)

einen Fehler. Bei KAPZ und ZINSZ darf ich bei dem Parameter F die beiden Werte 1 oder WAHR (beziehungsweise 0 oder FALSCH) eintragen. Muss ich das verstehen?

„Komm, wir gehen Pilze finden“ (Janosch)

Hallo – und ich würde gerne Formeln finden. Ich weiß, dass auf der Tabelle Formeln liegen – beispielsweise in den Zellen J6:J12. Aber Excel findet sie nicht!

Vom Suchen und Finden

Vom Suchen und Finden

Die Antwort: Sie dürfen nur eine Zelle auswählen! In Ihrem Beispiel haben Sie die Zellen A1:F1 ausgewählt (was man aufgrund der grünen Farbe nicht sehr deutlich sieht). Und DORT findet Excel KEINE Formeln. Also: Markierung auflösen – dann wird die Suche mit Erfolg gekrönt.

Ich finde den Fehler nicht – aber ich habe doch alles richtig gemacht!

Hallo Herr Martin,

ich habe die Formel abgeschrieben, die Sie in Ihren Buch veröffentlicht haben. Ich wollte aus einer Entfernungsliste die Distanz zweier Orte ermitteln. Aber es klappt einfach nicht!

Ist da ein Fehler in der Formel?

Es ist doch alles richtig, oder?

Es ist doch alles richtig, oder?

Die Antwort: Sie müssen ganz genau hinschauen. Wahrscheinlich sind Sie beim Abtippen der Formel eine Taste zu weit nach links gerutscht und haben fälschlicherweise statt eines $-Zeichens ein %-Zeichen getippt. Excel quittiert das mit einem Fehler!

Die Ursache

Die Ursache

Inhalte einfügen – nicht ganz

Ey – ich bin so genervt! Ich weiß, dass ich eine Spalte, in der sich Formeln befinden, kopieren kann und dann über „Inhalte einfügen“ die Formeln in Werte verwandeln kann. Aber was macht die Schweinebacke? – Richtig: Völliger Blödsinn!!! Schreibt einfach ANDERE Formeln rein. Das kanns doch wohl nicht sein“

Kopieren | Inhalte einfügen

Kopieren | Inhalte einfügen

Und dann das!!!

Und dann das!!!

Die Antwort: Du hast aus Versehen die Option „Multiplizieren“ gewählt – DIE muss ausgeschaltet werden – auf „Vorgang: Keine“. Dann funktioniert es auch.

Ein kleiner Klick zuviel

Ein kleiner Klick zuviel

Neugier ist die erste Stufe zur Hölle.

Ich will das auch sehen. Wenn mein Kollege die Funktion ZÄHLENWENN verwendet, sieht er bereits im Dialog das Ergebnis:

Das Ergebnis der Formel wird angezeigt.

Das Ergebnis der Formel wird angezeigt.

Bei mir jedoch nicht:

Hier nicht!

Hier nicht!

Erst wenn ich den Assistenten mit OK bestätige, steht das richtige Ergebnis in der Zelle. Warum bei mir nicht?

Die Antwort: Ihr Kollege sucht den Ort, indem er dort bereits die Anführungszeichen setzt. Sie haben keine Anführungszeichen eingegeben – de Text – hier Ulm – wird noch nicht als Text erkannt. Erst beim Bestätigen interpretiert der Funktionsassistent die drei Buchstaben Ulm als Text und ermittelt dann das richtige Ergebnis (und schreibt die Anführungszeichen in die Formel).

Texte zerschneiden

Ich habe in der letzten Excelschulung gelernt, dass man mit Textfunktionen Texte „manipulieren“ kann. Das wollte ich ausprobieren.

Ich habe eine Liste mit Vor- und Zunamen. Die Vornamen löse ich mit:

=LINKS(A2;FINDEN(“ „;A2)-1)

heraus. Klappt prima. Bei den Nachnamen bei der Formel

=RECHTS(A2;FINDEN(“ „;A2)-1)

macht er aber bei einigen Namen Blödsinn. Warum?

RECHTS klappt nicht!

RECHTS klappt nicht!

Das Problem: Die Funktion FINDEN (oder auch SUCHEN) findet einen Text innerhalb eines anderen VON LINKS. Das bedeutet: Sie schneiden aus dem Text VON RECHTS so viele Buchstaben heraus wie der Vorname lang ist. Das kann zufälligerweise funktionieren, normalerweise aber nicht. Sie lösen das Problem entweder mit der Gesamtanzahl der Buchstaben LÄNGE:

=RECHTS(A2;LÄNGE(A2)-FINDEN(“ „;A2))

oder mit der Funktion TEIL, die ab einem bestimmten Zeichen herausschneidet:

=TEIL(A2;FINDEN(“ „;A2)+1;999)

(zugegeben: die Zahl 999 ist „geschummelt“ – Sie schneiden nach dem Leerzeichen 999 (oder eine noch höhere Anzahl Buchstaben heraus) – aber es funktioniert)

Oder Sie löschen die ersten Buchstaben weg:

=ERSETZEN(A2;1;FINDEN(“ „;A2);““)

All das funktioniert.

So funktioniert es - drei Varianten

So funktioniert es – drei Varianten

Da fehlt doch was!

Ein Kollege von mir hat mir eine Formel in Excel erstellt. Ich verstehe die Formel aber nicht. Im dritten WENN-Teil fehlt doch ein Teil, oder?

Fehlt da nicht etwas?

Fehlt da nicht etwas?

Die Antwort: Schauen wir uns die Formel mal genauer an:

=WENN(AJ2=““;““;WENN((AJ2)>14;“zu lang“;WENN(AJ2<=14;“ok“;)))

Der erste Teil ist klar (=WENN(AJ2=““;““): Wenn die Zelle AJ2 leer ist, dann bleibe ich selbst auch leer. Prima.

Im zweiten Teil wird überprüft, ob in AJ2 ein Wert größer als 14 steht:

WENN((AJ2)>14;“zu lang“

Wenn das erfüllt ist, so liefert die Formel den Text „zu lang“. Nun wollte der Kollege überprüfen, ob der Wert kleiner oder gleich 14 ist. Er hat dies mit einer weiteren WENN-Funktion realisiert. Das ist nicht falsch, aber überflüssig.

Ich würde die Formel folgendermaßen schreiben:

=WENN(AJ2=““;““;WENN((AJ2)>14;“zu lang“;“ok“))

Sie liefert das gleiche Ergebnis und ist viel kürzer.

So geht es auch - das ist sogar eleganter!

So geht es auch – das ist sogar eleganter!

Function not found

Warum finde ich die Funktion nicht? Ein Kollege hat in einer Abrechnungstabelle für Kopierer unserer Firma eine Funktion DATEDIF eingefügt, die offensichtlich die Anzahl der Monate zwischen Vertragsbeginn und Vertragsende berechnet. Allerdings – in der Liste der Funktionen taucht sie nicht auf. Wo ist sie denn?

DATEDIF - nicht da!

DATEDIF – nicht da!

Die Antwort: Stimmt! Diese Funktion finden Sie nicht in der Liste der Funktionen im Funktionskatalog. Sie wurde aus Kompatibilitätsgründen zu Lotus 1-2-3 aufgenommen. Seit 2003 wurde diese Tabellenkalkulation nicht mehr weiterentwickelt, 2014 wurde der Support sogar eingestellt. Trotzdem finden sich noch immer ein paar Relikte von Lotus 1-2-3 in Excel. Zum Beispiel diese Funktion. Und: Wenn Sie auf das Symbol für den Funktionsassistenten f(x) klicken erhalten Sie die Funktionsargumente im Assistenten.

DATEDIF

DATEDIF

Ohne einen einzigen Feiertag

Warum klappt das nicht? Ich habe versucht – genau wie im Internet erklärt – einen Kalender zu erstellen. Ich habe die Feiertage berechnet und versuche auf meinem Kalender die Feiertage mit der Funktion ZAEHLENWENN anzeigen zu lassen. Aber er tut es nicht. Nicht einen einzigen Feiertag!

Die bedingte Formatierung funktioniert nicht.

Die bedingte Formatierung funktioniert nicht.

Die Antwort: Sie haben statt ZÄHLENWENN ZAEHLENWENN geschrieben. Erstaunlicherweise übergeht die bedingte Formatierung Tippfehler in den Funktionsnamen und – liefert gar nichts!

Mit ZÄHLENWENN funktioniert es!

Mit ZÄHLENWENN funktioniert es!

 

Teile und herrsche …

TEILERGEBNIS. Ich versteh mal wieder gar nichts. Ich habe eine Liste. Darunter stehen drei Funktionen:

=SUMME(C2:C42)

=TEILERGEBNIS(9;C2:C42)

=TEILERGEBNIS(109;C2:C42)

Drei mal erhalte ich den Wert 20.205.490.

TEILERGEBNIS

TEILERGEBNIS

So weit, so klar. Wenn ich nun filtere, liefert TEILERGEBNIS mit der 9 das Gleiche wie TEILERGEBNIS mit der 109. Natürlich einen anderen Wert wie die Summe.

TEILERGEBNIS bei gefilterten Daten

TEILERGEBNIS bei gefilterten Daten

Ich schaue in die Hilfe, um den Unterschied zwischen der Konstante 9 und 109 – beides Mal die Funktion SUMME zu ermitteln. Dort lese ich:

„ist eine Zahl von 1 bis 11 (bezieht ausgeblendete Werte ein) oder von 101 bis 111 (ignoriert ausgeblendete Werte), die festlegt, welche Funktion bei der Berechnung des Teilergebnisses innerhalb einer Liste verwendet werden soll.“ HÄ? Ich habe doch ausgeblendet. Trotzdem ist das Ergebnis das Gleiche!

Die Hilfe - nicht wirklich eine Hilfe ...

Die Hilfe – nicht wirklich eine Hilfe …

Die Antwort: „ausgeblendete Werte“ ist unglücklich formuliert. Excel meint die Zelle, die SIE ausgeblendet haben (beispielsweise mit der rechten Maustaste oder [Strg] + [9]). Er bezieht sich dabei nur auf „manuell“ ausgeblendete Zellen oder durch Gruppierung ausgeblendete Zellen, aber nicht auf ausgeblendete Zellen durch Filtern!

Beim Ausblenden von Zeilen wird der Unterschied zwischen 9 und 109 beim TEILERGEBNIS deutlich.

Beim Ausblenden von Zeilen wird der Unterschied zwischen 9 und 109 beim TEILERGEBNIS deutlich.

Einsam bist du klein aber gemeinsam …

Geht das nicht?

Ich habe eine Liste. darin befinden sich in einer Spalte Vornamen, in einer anderen Nachnamen. Das Verketten mit

=D2&E2

klappt hervorragend. Aber – darf ich denn kein Leereichen zwischen Vor- und Nachname schreiben. Ohne – das wäre schon ganz schön doof.

Verketten

Verketten

Die Antwort: Natürlich geht es. Aber Sie dürfen nicht das Leerzeichen direkt eingeben, sondern müssen es als Leerzeichen kennzeichnen. Also so

=D2&“ „&E2

Dann funktioniert es. Und: Vergessen Sie nicht das zweite Verkettungszeichen „&“!

Übrigens: Ich finde es erstaunlich, dass Excel das Leerzeichen akzeptiert – eigentlich sind Leerzeichen in Formeln verboten und werden mit einer Fehlermeldung quittiert!

Kann INDIREKT keine Tabellen?

Hallo. Ich habe eine Frage. Ich weiß, dass Excel nicht alles kann. Aber fragen kann man ja mal.

Ich habe für meinen Chef eine Liste in Excel erstellt. Habe über Erstellen / Tabelle eine Tabelle daraus gemacht. So weit, so gut.

Die Tabelle

Die Tabelle

Ich klicke in eine andere andere Zelle nebenan und erzeuge eine Summe:

Eine Spalte wird summiert.

Eine Spalte wird summiert.

Die Formel lautet:

=SUMME(Tabelle1[April])

Über ein Dropdownfeld kann man die Monate auswählen:

Die Monatsliste

Die Monatsliste

Und nun meine Frage. Eigentlich liebe ich die Funktion INDIREKT. Eine prima Sache. Aber kann sie nicht diese Formel zusammenbauen? Etwas so:

=SUMME(„Tabelle1[„&INDIREKT(„O2″)&“]“)

INDIREKT liefert einen Fehler.

INDIREKT liefert einen Fehler.

Die Antwort: Nein – nicht ganz – Sie müssen die Anführungszeichen anders setzen und INDIREKT VOR den gesamten Text stellen. Wenn Sie Formel wie folgt zusammenbauen, dann klappt es:

=SUMME(INDIREKT(„Tabelle1[„&O2&“]“))

Natürlich kann INDIREKT das.

Natürlich kann INDIREKT das.

 

Leerzeichen finden

Hallo. Ich habe schon eine ganze Weile gesucht, bis ich herausgefunden habe, warum er die gallischen Tiere falsch zählt. Eigentlich müsste die Formel ANZAHL2 die Zahl 2 ergeben und nicht 4:

ANZAHL2 zählt falsch

ANZAHL2 zählt falsch

Die Antwort habe ich nach langem Suchen gefunden: In einigen Zellen habe ich aus Versehen ein Leerzeichen eingegeben. Klar – das sehe ich nicht; das wird als Text mitgezählt.

Die Wurzel des Übels

Die Wurzel des Übels

Aber nun meine Frage: Wenn ich die Liste filtere – warum zeigt der Autofilter nicht an, dass einige Zellen Leerzeichen enthalten?

Die Antwort: Ja – Sie haben recht – der Autofilter übergeht zum Glück (oder leider?) die Leerzeichen. Der Vorteil: „Asterix“ und „Asterix “ (mit einem Leerzeichen am Ende) werden vom Filter als gleicher Text behandelt. Der Nachteil: Der Filter hilft nicht diese Leerzeichen, die an anderen Stellen Probleme verursachen, aufzufinden.

Der Autofilter übergeht Leerzeichen am Ende des Textes.

Der Autofilter übergeht Leerzeichen am Ende des Textes.

Man kann die Texte mit Suchen ([Strg]+[F]) auffinden. Oder mit Funktionen:

=LÄNGE(C2)

=WENN(LINKS(C2;1)=“ „;“x“;““)

Oder mit [Strg]+[↓] können Sie den Cursor nach unten versetzen; er springt nun zur ersten Zelle, in der etwas steht; stoppt also auch bei den Zellen, die mit einem Leerzeichen gefüllt sind.

Excel ohne Nullen

Bei meinem Kollegen ist alles irgendwie anders. Ich weiß auch nicht warum!

Ich habe die Datei genauso nachgebaut wie bei ihm; aber er zeigt mir bei der Formel nicht als Ergebnis den Wert „0“, sondern gar nichts. Haben Sie eine Erklärung?

Anzeige ohne 0

Anzeige ohne 0

Klar. Bei Ihnen ist mit Sicherheit die Option „In Zellen mit Nullwerten eine Null anzeigen“ ausgeschaltet. Deshalb wird beim Zahlenformat Standard, Zahl oder Währung nichts angezeigt, wenn die Formel den Wert 0 berechnet. Übrigens: „Buchhaltung“ würde hier „- €“ anzeigen.

In Zellen mit Nullwerten eine Null anzeigen

In Zellen mit Nullwerten eine Null anzeigen

 

 

Wir haben ein Problem bei dieser Formel festgestellt.

Das ist schön, dass Excel ein Problem bei dieser Formel feststellt – aber das hilft mir leider nicht weiter. Haben Sie eine Erklärung?

Fehlermeldung

Fehlermeldung

Hierfür kann es sicherlich mehrere Ursachen geben. Dummer Tipp: Versuchen Sie es noch einmal! Vielleicht haben Sie einfach [Enter] gedrückt oder mit der Maus auf [OK] geklickt ohne etwas einzugeben, beziehungsweise zu markieren:

Eine mögliche Lösung

Eine mögliche Lösung

TE – und ich erhalte kein Teilergebnis

Hallo zusammen,
Wenn ich eine Formel (per Tastatur) eingeben will, z. B. =Teilergebnis(…), dann zeigt mir Excel nach zwei Buchstaben auch schon die richtige Formel in einem Feld an, die ich dann mit den Coursor-Tasten auswählen kann. Wie kann ich diese Auswahl dann aber übernehmen (ohne Maus wohlgemerkt). Wenn ich nach =Te die ENTER drücke, weil dich die richtige Formel ausgewählt habe, speichert Excel nur eben =Te und erkennt es als Namen, den es natürlich nicht gibt, Ausgabe in Zelle : #Name? wie kann man denn per Tastatur die richtige Formel auswählen und bestätigen?
Nur TE und kein Teilergebnis

Nur TE und kein Teilergebnis

Die Antwort: Drücken Sie nicht die [Enter]-Taste, sondern die Tabulatortaste. Dann übernimmt Excel diese Funktion. Von Andreas Theos stammt noch der Tipp: „…und nach dem TAB noch STRG + A für den Formeldialog ;-)“ – danke!

Seltsame Funktion

Eine Kollegin von mir hat einen tollen Kalender erstellt. Wenn ich ihn mir jedoch genauer ansehe, dann finde ich dort seltsame Funktionen (_xlfn.ISOWEEKNUM). Sie sind nicht in einem Add-In vorhanden, hat mir die Kollegin gesagt. Ich finde die Funktionen auch nicht im Funktionsassitenten. Was passiert denn hier?

Seltsame Funktion

Seltsame Funktion

Die Antwort: Mit jeder Excel-Version kommen einige, wenige, neue Funktionen hinzu. Da Excel seit der Version 2007 das (fast) identische Dateiformat XLSX unterstützt, haben Sie ein kleines Problemchen. In Excel 2013 wurde beispielsweise die Funktion ISOKALENDERWOCHE eingeführt. Diese Funktion gab es in Excel 2007 und 2010 noch nicht. Wenn nun das entsprechende Servicepack auf Ihrem Rechner installiert ist oder wenn Excel 2013 installiert ist, kann diese neue Funktion verwendet werden, obwohl sie eigentlich gar nicht auf Ihrem Rechner installiert wird. Leider wird nicht der Name ISOKALENDERWOCHE angeziegt, sondern: _xlfn.ISOWEEKNUM.

Zählenwenn rechnet nicht

Warum rechnet die Funktion ZÄHLENWENN (übrigens auch SUMMEWENN) manchmal nicht?

ZÄHLENWENN rechnet nicht.

ZÄHLENWENN rechnet nicht.

Schauen Sie die Funktion genau an. ZÄHLENWENN (und auch SUMMEWENN) verlangt als Reihenfolge zuerst WO wird etwas gesucht und anschließend WAS wird gesucht. Wenn Sie die Reihenfolge vertauschen, zählt er, wie oft der Bereich in der einen Zelle vorkommt und liefert kein Ergebnis.

Die Reihenfolge ist hier falsch.

Die Reihenfolge ist hier falsch.

Excel rechnet falsch

Bei meinem Kollegen funktioniert es – auf meinem Rechner aber nicht!

Ein Kollege hat auf seinem Rechner eine Formel programmiert, die bei einem Geburtstagskind „happy birthday“ anzeigt. Wenn ich diese Datei bei mir öffne, dann sehe ich zwar die Geburtstagskinder aber auch noch viele andere. Warum?

So viele haben heute gar nicht Geburtstag!

So viele haben heute gar nicht Geburtstag!

Die Formel sieht folgendermaßen aus:

=WENN(TEXT(I2;“DD-MM“)=TEXT(HEUTE();“DD-MM“);“happy birthday“;““)

Wenn Sie genau hinschauen, fällt auf, dass das Datum in der Form DD-MM formatiert wurde, also day und month. Mit Sicherheit verwendet der Kollege eine englischsprachige Excelversion. Dort benutzt Excel DMY statt TMJ. Da diese Formatangabe als Text in Anführungszeichen in der Formel steht, wird sie nicht übersetzt (anders als beispielsweise die Formeln – aus SUM wird SUMME, aus VLOOKUP wird SVERWEIS, … Entweder Sie korrigieren die Formel in:

=WENN(TEXT(I2;“TT-MM“)=TEXT(HEUTE();“TT-MM“);“happy birthday“;““)

oder Sie schreiben es ohne die Funktion TEXT, die leider nicht mehrsprachig ist:

=WENN(UND(TAG(I2)=TAG(HEUTE());MONAT(I2)=MONAT(HEUTE()));“happy birthday“;““)

Man muss die Formel ändern oder eine andere Formel verwenden, damit man alle Geburtstagkinder sieht - alle die heute, am 13.01. Geburtstag haben.

Man muss die Formel ändern oder eine andere Formel verwenden, damit man alle Geburtstagkinder sieht – alle die heute, am 13.01. Geburtstag haben.

Fast alle Rechnungen sind falsch

Warum rechnet Excel in der ersten Zelle richtig, in fast allen anderen Zellen falsch?

Excel rechnet fast überall falsch.

Excel rechnet fast überall falsch.

Wenn ich diese Frage höre, gibt es eigentlich nur eine Antwort – irgendetwas stimmt mit relativ/absolut nicht. Wenn Sie die Formel anschauen, die in C4 steht:

=B4*B1

dann müssten Sie erkennen, dass Sie eigentlich B1 fixieren müssen. Am besten, indem Sie den Cursor vor den Buchstaben B, zwischen B und 1 oder hinter die Zeilennummer 1 setzen und dann [F4] drücken. So wandelt Excel den relativen Bezug in einen absoluten (festen) Bezug um und schreibt:

=B4*$B$1

Formel runterziehen – dann klappt es:

Mit dem absoluten Bezug funktioniert es.

Mit dem absoluten Bezug funktioniert es.

#ZAHL!

Manchmal – wenn auch recht selten – erhalte ich die Fehlermeldung #ZAHL! Wann passiert denn das?

Vor allem bei mathematischen Funktionen kann dieser Fehler auftreten. Es gibt einige Funktionen, die lassen nur einen bestimmten Wertebereich zu – sonst können sie nicht rechnen, weil sie im reellen nicht definiert sind. Beispielsweise verlangt Wurzel, Logarithmus und Fakultät eine positive Zahl, Arcsin und Arccos sind für Zahlen im Bereich [-1;+1] definiert. GGT und KGV sind nur für positive, ganze Zahlen definiert.

Erhalten sie negative Werte (beispielsweise WURZEL(-1) so ist #ZAHL! das Ergebnis.

Ebenso wachsen einige Funktionen sehr schnell. Mit FAKULTÄT(171) sprengt die Grenzen von Excel – Fakultät(170) ergibt 7,2574 x 10306. Mehr geht nicht. Auch mit der Funktion Potenz kommt man an die Grenzen von Excel.

Formel rechnet nicht

Ich habe eine Datei erhalten, die angeblich bei meinem Kollegen richtig rechnet. Bei mir sehe ich jedoch nur Fehler. Was habe ich falsch gemacht?

Fehler statt Formelergebnis

Fehler statt Formelergebnis

Sie haben alles richtig gemacht. Ein genauer Blick auf die Formeln zeigt, dass auf einem anderen Rechner mit der Funktion Ostersonntag gerechnet wurde. Diese Funktion gibt es nicht in Excel. Wenn Ihr Kollege sagt, dass diese Formel bei ihm funktioniert, kann es zwei Gründe haben:

1. Entweder er hat ein Add-In programmiert (oder programmieren lassen) oder zumindest installiert, das diese Formel (Ostersonntag) zur Verfügung stellt. Zwar rechnet die Formel korrekt auf seinem Rechnern, aber wird nun die Datei weitergegeben, rechnet die Funktion nicht mehr richtig auf einem anderen Rechner.

2. Er hat in einem anderen Programm gearbeitet, beispielsweise in openOffice.org oder in libreOffice. Dort existiert diese Funktion (einige der wenigen Funktionen die es in ooo oder libreOffice, aber nicht in Excel gibt). Die Datei kann als *.XLS gespeichert und weitergegeben werden. Beim Öffnen – Fehlermeldung!

Die Lösung: Sie müssen entweder das Add-In installieren oder die Formel auf Ihrem Rechner nachprogrammieren.

SVERWEIS rechnet nicht

Seit einer Weile arbeite ich mit dem SVERWEIS. Ich habe ihn schon recht gut verstanden. Aber manchmal rechnet er nicht richtig. Warum?

SVERWEIS rechnet nicht richtig.

SVERWEIS rechnet nicht richtig.

Dazu muss man sich die Formel genau ansehen:

=SVERWEIS(K2;$A$1:$A$32;3;FALSCH)

Sie suchen den Wert, der in der Zelle K2 steht in der Spalte A – genauer in den Zellen A1 bis A32. Soweit so gut. Sie möchten den Wert der dritten Spalte (3), also den Last Name wissen. Sie müssen den Bereich ändern: Es ist richtig – Sie suchen zwar in A1:A32, aber in der Spalte A steht nicht der Wert den Sie haben möchten. Sie müssen in der Matrix (in der Informationstabelle, in der die Daten gesucht werden), auch den Bereich einschließen, in dem sich die Daten befinden, also Spalte C. Sie können dabei gerne übers Ziel schießen, beispielsweise:

=SVERWEIS(K2;$A$1:$H$32;3;FALSCH)

Dann funktioniert es.

ZÄHLENWENN zählt zu wenig

Warum ermittelt die Funktion ZÄhLENWENN (und SUMMEWENN) nicht die richtige Zahl?

Zähelnwenn zählt falsch ?!

Zählenwenn zählt falsch ?!

Man muss sich die einzelnen Daten ansehen. In der Bearbeitungsleiste stellt man fest, dass in einigen der Texte noch weitere Informationen („München“) stehen (sie wurden übrigens weiß formatiert). Man hätte auch durch ein Ändern der Ausrichtung auf rechtsbündig feststellen können, dass hinter dem angezeigten Text noch etwas steht.

Die Bearbeitungsleiste liefert das Ergebnis

Die Bearbeitungsleiste liefert das Ergebnis

Oder man formatiert die Zellen rechtsbündig.

Oder man formatiert die Zellen rechtsbündig.

Übrigens: =ZÄHLENWENN(D2:D10;“Rene Martin*“) hätte die korrekte Anzahl ermittelt.

Summe rechnet nicht

Die Formel stimmt – aber warum rechnet die Summe nicht?

Die Summe rechnet nicht.

Die Summe rechnet nicht.

Erst ein Klick auf die Zellen liefert die Antwort: In den Zellen steht nicht 20, 40, 60, … sondern 20 EUR, 40 EUR, … – das heißt der Text „EUR“ wurde in die Zelle eingetragen und nicht hinzuformatiert.

Übrigens: Manche Anwender denken, dass durch ein Ändern der Ausrichtung in rechtsbündig aus einem solchen Text eine Zahl wird. Das ist natürlich nicht der Fall!

Falsche Werte in der Zelle verhindern das Rechnen.

Falsche Werte in der Zelle verhindern das Rechnen.

SVERWEIS klappt nur manchmal

In den ersten Zeilen rechnet der SVERWEIS noch richtig, aber dann gibt es Fehler.

SVERWEIS rechnet manchmal richtig.

SVERWEIS rechnet manchmal richtig.

Eigentlich müssten Sie den Fehler selbst finden können, wenn Sie die Formel anschauen. Der Bereich (hier: A1:B14) wurde nicht fixiert. Das bedeutet: er „wandert“ beim Herunterziehen mit nach unten. Was passiert, können Sie leicht überprüfen, wen Sie auf eine Zelle doppelklicken, die sich weiter unten befindet.

Der Bereich wurde nicht fixiert.

Der Bereich wurde nicht fixiert.

Wandeln Sie also A1:B14 in $A$1:$B$14 um (oder verwenden einen Namen für diesen Bereich)

#BEZUG!

Ich weiß nicht mehr, was ich gemacht habe. Ich sollte in einer Tabelle einer Kollegin die Formel für den Unterstützungsbeitrag unserer Firma anpassen. Irgendwann entdecke ich jedoch in einer Zelle die Fehlermeldung #BEZUG! Kann ich den Fehler lokalisieren? Oder die Formel wieder auf eine korrekte Form bringen?

#BEZUG!

#BEZUG!

Die Antwort: Leider nein! Wahrscheinlich haben Sie irgendwo etwas gelöscht (beispielsweise eine Zeile), die an anderer Stelle noch verwendet wurde. Menschen rechnen oft kreuz und quer in Excel; schreiben irgendwelche Konstanten in irgendwelche Zellen. Fremde Tabellenblätter zu analysieren ist schwierig und mühsam:

Tipp 1: Speichern Sie die Originaldatei unter einem anderen Namen ab.

Tipp 2: Bevor Sie etwas löschen, von dem Sie denken, dass es nicht mehr benötigt wird – überprüfen Sie mit der Spur zum Nachfolger, ob irgendwo eine andere Formel mit dieser weiter rechnet.

Summe rechnet nicht

Im Excel-Kurs habe ich gelernt, wie man eine Summe bildet. Aber bei mir funktioniert das nicht:

Warum rechnet die Summe nicht?

Warum rechnet die Summe nicht?

Die Antwort ist einfach. Sie haben eine englischsprachige Oberfläche. Sie müssen natürlich SUM statt SUMME eingeben, AVERAGE statt MITTELWERT, IF statt WENN, VLOOKUP statt SVERWEIS und so weiter …

SAP & co

Kennen Sie SAP? Das ist mein Freund!

Nein – das war sehr ironisch. Jeder, der häufig Daten aus SAP exportiert, kennt sicherlich das Problem: Ab und zu werden Textinformationen unter die Zellen geschoben. Das sieht man erstaunlicherweise nicht – die Zellen sind als „Standard“ formatiert. Oft erkennt man es daran, dass die Zahlen linksbündig in der Zelle stehen. Spätestens wenn Sie mit den Zahlen weiterrechnen möchten oder wenn Sie die Zahlen sortieren oder filtern oder als zahlen formatieren möchten … stellen Sie fest, dass Excel Ihnen nun einen Strich durch die Rechnung macht.

Sieht aus wie Zahl, ist aber Text.

Sieht aus wie Zahl, ist aber Text.

Ich habe für dieses Problem folgende Lösungen gefunden:

1. Wenn Sie Glück haben und das kleine grüne Dreieck sehen zur Fehlerüberprüfung, können Sie darüber die Texte in Zahlen zurückkonvertieren.

Manchmal geht es zurück.

Manchmal geht es zurück.

2. Wenn Sie nur einige wenige Zellen haben, können Sie auf die Zelle einen Doppelklick machen (oder mit [F2] die Zelle editieren und anschließend wieder mit [Enter] beenden. Dann „greift“ sich Excel das korrekte Zahlenformat.

3. Sie können in einer Hilfsspalte daneben den Wert der Zelle mit 1 multiplizieren (=O2*1). Die Formel herunterziehen, kopieren und die Inhalte als Werte einfügen.

4. Das Gleiche erledigt auch die Funktion =WERT

5. Oder Sie markieren die Spalten und verwenden den Assistenten „Text in Spalten“, den Sie im Register „Daten“ finden. Geben Sie dort ein absurdes Trennzeichen ein (beispielsweise eine ~); ein Trennzeichen, das es natürlich in den Zahlen nicht gibt. Dann überschreibt er die Werte mit sich selbst und „greift sich“ das korrekte, das heißt das darunterliegende Zahlenformat.

6. Die Zahl 1 in eine leere Zelle schreiben. Die Zelle kopieren, den Text-Zahl-Bereich markieren und mit Inhalten einfügen / Multiplizieren (Kontextmenü) „darüberklatschen“. Das Ergebnis ist das Gleiche wie in Punkt 2 oder 3 oder 5 – Excel greift sich nun das korrekte Zahlenformat.

So geht es auch.

So geht es auch.

Zur Ehrenrettung von SAP sei angemerkt: Viele mir bekannte Datenbanksysteme, die da heißen DATEV, KISS, ORBIS, EBIS und andere „schieben“ manchmal (nicht immer!) Textformate unter Zahlen beim Export nach Excel.

Excel rechnet nicht

Excel rechnet nicht. Warum?

Warum rechnet Excel nicht?

Warum rechnet Excel nicht?

Ein Blick in die Statuszeile hätte genügt: Dort steht, dass in D3 ein Zirkelbezug steht. Und wenn man sich die Formel genauer anschaut, wird klar, dass D3 auf D5 zugreift, D5 jedoch wiederum auf D3. Das darf nicht sein!

Der Zirkelbezug ist ausfindig gemacht.

Der Zirkelbezug ist ausfindig gemacht.

Meine Empfehlung: Zirkelbezüge sind schwierig zu finden. Wenn Sie einen Zirkelbezug haben, erhalten Sie immer eine Fehlermeldung. Unterbrechen Sie die weitere Arbeit und machen sich auf die Suche nach der Quelle. Denn sonst resultieren weitere Fehler aus dem Zirkelbezug.

Die Zirkelbezugsfehlermeldung

Die Zirkelbezugsfehlermeldung

#BEZUG!

Warum erhalte ich manchmal den Fehler #BEZUG! ?

Das kann verschiedenen Ursachen haben:

1. Entweder Sie kopieren eine Formel an eine Stelle wo sie nicht mehr „richtig rechnen kann“. Beispielsweise findet die Summe keine drei Zellen oberhalb und kann deshalb nicht drei Werte summieren:

Summe nicht möglich

Summe nicht möglich

2. Eine Formel greift auf eine Zelle zu (beispielsweise auf Zelle C1). Wird nun die Spalte C gelöscht, dann „findet“ die Formel keinen Wert mehr und meldet #BEZUG!

#BEZUG!

#BEZUG!

Das heißt: #BEZUG! wird immer dann angezeigt, wenn man eine Formel so kopiert oder verschiebt, dass sie nun nicht mehr rechnen kann. Oder eine Spalte so löscht oder einfügt, dass eine Formel nicht mehr „erkennen“ kann, wi der Wert liegt, mit dem sie ursprünglich gerechnet hat.

Summe funktioniert nicht

Sehr geehrter Herrn Martin,

In der Excel Tabelle die im Anhang beigefügt ist, bekommen wir Daten von unsere EDV (sehe Sheet 1 vor Verarbeitung). Ich gehe dann in „DATA“ und „Text to Columns“ und spalte diese Tabelle so dass sie nach diese Schritte wie im 2. Sheet (Daten nach Verarbeitung) aussieht.

Das Problem liegt indem einige Zahlen immer noch so erscheinen „1 150,000“ und keine weitere Formatierung möglich ist. Da ich auch eine Summe daraus ziehen möchte.

Wie kann man diese Problem Lösen ?

Originaldaten

Originaldaten

Daten nach dem Trennen

Daten nach dem Trennen

Die Antwort: Dummerweise liefert Ihr System die Spalte E so, dass nach dem Tausenderwert als Tausendertrennzeichen ein Leerzeichen verwendet wird. Diese müssen Sie löschen. Ich würde die Spalte (hier E) markieren und dann mit Home / Find & Replace (ganz rechts) das Leerzeichen (einfach ein Blank eintippen) durch nichts ersetzen.

 

Formel liefert falsches Ergebnis

Ich habe eine Formel genauso abgeschrieben, wie ich sie im Internet gefunden habe – aber sie rechnet falsch. Ich bin Widder – kein Stier!

Formel rechnet falsch ?!

Formel rechnet falsch ?!

Entweder ist ein Denkfehler in der Formel, aber wenn sie an anderer Stelle funktioniert, dann muss sie wohl korrekt sein. Möglicherweise wurden die Klammern falsch gesetzt. Wenn Sie auf die Zelle klicken, in der sich die Formel befindet und den Funktionsassistenten f(x) aufrufen, dann sehen Sie die Teile der Formel mit ihren Ergebnissen. Nun können Sie in der Eingabezeile auf die einzelnen Formeln klicken und sehen so, wo der Fehler steckt (in diesem Beispiel wurde die Klammer nach der Funktion TAG falsch gesetzt. Die korrekte Formel muss lauten:

=WENN(TAG(G2)<INDEX(D2:D14;VERGLEICH(MONAT(G2);E2:E13;0));INDEX(A2:A14;VERGLEICH(MONAT(G2);E2:E13;0));INDEX(A2:A14;VERGLEICH(MONAT(G2);E2:E13;0)+1))

Der Funktionsassistent hilft beim Ausfinden von Fehlern in Funktionen.

Der Funktionsassistent hilft beim Ausfinden von Fehlern in Funktionen.

Excel rechnet falsch

Manchmal stimmt die Summe nicht. Was ist los?

Die Summe rechnet falsch?!

Die Summe rechnet falsch?!

Der Blick in die Gruppe „Zahl“ auf das Zahlenformat „Zahl“ macht stutzig. Ein genauer Blick darauf, wie die Zelle formatiert ist und welcher Wert eigentlich in der Zelle steht, ergibt, dass die Zahl 0,6 ohne Dezimalstellen formatiert wurde. Die Lösung: Lassen Sie sich (mehr) Dezimalstellen anzeigen. Excel rechnet mit dem Wert, der sich in der Zelle befindet und nicht mit dem Wert, den Sie sehen.

0,6 ohne Nachkommastelle formatiert.

0,6 ohne Nachkommastelle formatiert.

Mehr Nachkommastellen!

Mehr Nachkommastellen!

Seltsame Datumsangaben

Ich habe mal nachgeschaut: Heute, am 07. Januar 2015 verwendet Excel intern die Zahl 42.011. Das erhalte ich, wenn ich die Zelle als Standard formatiere. In meinem openOffice und libreOffice genauso. Allerdings ist der 1. Januar 1900 bei Excel die Zahl 1, in openOffice und libreOffice die Zahl 2. Kann mir das mal einer erklären?

Fehlt ein Tag?

Fehlt ein Tag?

Das ist ganz einfach. Da hat einer nicht aufgepasst! Jedes Jahr, das durch vier teilbar ist, ist ein Schaltjahr. Alle Hundert Jahre ist kein Schaltjahr, alle 400 haben wir wieder ein Schaltjahr. Das heißt: 2016, 2020 und 2024 werden wir ein Schaltjahr haben, im Jahre 2000 hatten wir eines, aber nicht 1900. Die Macher von Excel haben das übersehen. Wenn Sie den 29.02.1900 eingeben, dann erhalten sie ein gültiges Datum, was Sie daran erkennen können, dass die Zahl rechtsbündig steht. Die Macher von openOffice/libreOffice haben dies richtig erkannt und dieses Datum weggelassen. Nun, da Excel im Jahre 1900 anfängt, sind also die ersten beiden Monate falsch. Also: geben Sie keine Datumsangaben zwischen dem 1.Januar 1900 und dem 28.Februar 1900 ein. Aber das haben Sie sowieso nicht vor, oder?

Kompatibilitätsprüfung

Warum erhalte ich eine Meldung nach der Kompatibilitätsprüfung?

Kompatibilitätsprüfung

Kompatibilitätsprüfung

Wenn eine Datei im Format *.xls (also in Excel 2003) erstellt wurde und Sie diese Datei im gleichen Format speichern möchten, müssen Sie ein paar Dinge beachten:

Excel unterstützt im Format *.xls nicht alle Funktionalitäten, die in *.xlsx vorhanden sind. Dazu gehören:

  • Farben
  • Tabellenformatvorlagen
  • Bedingte Formatierungen
  • Formeln mit einer bestimmten Länge
  • Formeln mit einer bestimmten Tiefe
  • Bestimmte Formeln (SUMMEWENNS, ZÄHLENWENNS, WENNFEHLER, …)
  • Maximal Anzahl der Spalten und Zeilen (65.536 Zeilen x 255 Spalten)
  • Pivottabellen
Die Meldung ist eindeutig!

Die Meldung ist eindeutig!

Mein Tipp: Werfen Sie einen Blick in die Liste der Dinge, die verloren gehen würden. Vor allen Formeln werden unwiderruflich gelöscht!

Diese Formel bereitet Schwierigkeiten.

Diese Formel bereitet Schwierigkeiten.

Würden Sie diese Datei in Excel 2003 öffnen, wäre die Formel weg!

Excel 2003 kann nur acht Ebenen verschachteln.

Excel 2003 kann nur sieben Ebenen verschachteln.

Nach dem Öffnen der Datei sieht die Wenn-Funktion folgendermaßen aus.

Nach dem Öffnen der Datei sieht die Wenn-Funktion folgendermaßen aus.

 

Wo ist das Kästchen?

Manchmal taucht das Kästchen nicht auf, mit dessen Hilfe ich eine Formel nach unten ziehen kann. Warum ist es manchmal verschwunden?

Das Kästchen ist verschwunden.

Das Kästchen ist verschwunden.

Die Antwort: Wenn Sie getrennte Bereiche mit gedrückter [Strg]-Taste markieren, dann können sie nicht unabhängig voneinander runtergezogen werden. Excel kann nur einen einzigen zusammenhängenden Bereich runterziehen.

So erscheint das Kästchen.

So erscheint das Kästchen.

Prozente werden falsch berechnet

Warum rechnet Excel manchmal falsch mit Prozentwerten?

Das Ergebnis ist falsch!

Das Ergebnis ist falsch!

Ein Blick auf die Zelle, in der die vermeintlichen 19% stehen, liefert die Antwort. In dieser Zelle stehen nicht 19%, sondern die Zahl 19. In der Gruppe „Zahl“ wird angezeigt, dass diese Zelle „benutzerdefiniert“ formatiert wurde. Ein Blick in die Zahlenformate liefert schließlich das Ergebnis: Der Anwender hat nicht 19% in die Zelle eingetragen oder die Zahl 0,19 als Prozent formatiert, sondern hat die Zahl 19 eingetragen und hinter diese Zahl benutzerdefiniert ein Prozentzeichen formatiert (so wie man m² oder kg formatieren kann). Deshalb rechnet Excel natürlich mit der Zahl 19 und „übergeht“ das Prozentzeichen.

Die Lösung: ein perfides Zahlenformat

Die Lösung: ein perfides Zahlenformat

Warum funktioniert ODER nicht in Wenn-Funktionen?

Die Funktion

=WENN((G2>180)ODER(I2=“Platinum“);G2+20;G2+10)

wird mit einer Fehlermeldung quittiert. Alle Kunden, die entweder Platinum-Mitglieder sind oder jetzt schon mehr als 180 Euro bezahlen, sollen demnächst 20 Euro mehr Jahresbeitrag bezahlen – die übrigen erhalten eine Erhöhung von 10 Euro.

Die Antwort: UND und ODER sind in Excel keine Verknüpfungsoperatoren (wie beispielsweise in Programmiersprachen), sondern Funktionen. Und Funktionen müssen immer VOR den Argumenten geschrieben werden, also so:

=WENN(ODER(G2>180;I2=“Platinum“);G2+20;G2+10)

beziehungsweise:

=WENN(UND(G2>180;I2=“Platinum“);G2+20;G2+10)

So funktioniert ODER nicht.

So funktioniert ODER nicht.

#DIV/0

Was bedeutet #DIV/0?

Entweder haben Sie durch eine Zelle geteilt, in der sich kein Wert befindet oder in der der Wert Null steht. Die Division durch 0 ist nicht erlaubt – in Excel nicht und in der Mathematik auch nicht.

Nicht nur bei der Division taucht dieser Fehler auf, auch bei einigen anderen Funktionen, beispielsweise MITTELWERT. Befinden sich noch keine Werte im Eingabeformular, dann rechnet MITTELWERT = SUMME (0) / ANZAHL (0) und liefert 0 / 0 = #DIV/0. Mann kann dies mit der Funktion WENNFEHLER abfangen. Bis Excel 2003 musste man die Funktion WENN(ISTFEHLER(… hierfür verwenden.

#DIV/0 - meist wurde eine falsche Zelle ausgewählt.

#DIV/0 – meist wurde eine falsche Zelle ausgewählt.

#DIV/0 liefern einige Funktionen, wenn noch keine Daten vorhanden sind.

#DIV/0 liefern einige Funktionen, wenn noch keine Daten vorhanden sind.

Man kann es mit WENNFEHLER abfangen.

Man kann es mit WENNFEHLER abfangen.

AutoSumme funktioniert nicht

Ich habe es nun schon mehrmals versucht – die Autosumme geht nicht.

Auch wenn jede Formel und Funktion mit einem Gleichheitszeichen beginnt, auch wenn Sie bei den Grundrechenarten das Gleichheitszeichen per Hand eintragen müssen, so verlangt das Symbol AutoSumme jedoch NICHT, dass Sie das Gleichheitszeichen eintragen. Sie müssen entweder die Formel per Hand eintragen:

=SUMME(G2:G12)

oder Sie setzen den Cursor auf eine leere Zelle und klicken anschließend auf das Symbol AutoSumme. Dann funktioniert es.

Die AutoSumme funktioniert nicht.

Die AutoSumme funktioniert nicht.

Excel multipliziert nicht

Warum klappt die Multiplikation nicht?

Auf manchen Tastaturen sieht das Multiplikationszeichen über dem Zahlenblock auch wie der Buchstabe „x“. Sie dürfen eine Formel jedoch nicht in der Form

=F8xE8

eingeben, weil Excel das „x“ als Buchstaben interpretiert.

Richtig:

=F8*E8

Übrigens: Auch die Division wird mit dem Schrägstrich „/“ durchgeführt und nicht mit einem Doppelpunkt, wie wir es in der Schule gelernt haben.

Excel multipliziert nicht.

Excel multipliziert nicht.

SUMME rechnet nicht

Kann ich mit der Summe nicht mehrere getrennte Bereiche addieren?

Doch. Aber die Bereiche werden nicht mit einem Leerzeichen, sondern mit einem Semikolon (;) getrennt.

Also – nicht so:

=SUMME(G5:G18 G30:G42 G56:G71 G80:G93)

sondern so:

=SUMME(G5:G18;G30:G42;G56:G71;G80:G93)

Übrigens: das Leerzeichen hat auch eine Funktion – es bedeutet: Schnittmenge. Man könnte berechnen:

=SUMME(C5:H21 G5:G26)

Damit würde die Summe über die Zelle G1:G21 gezogen werden.

Meine Empfehlung – tun Sie das nicht – das versteht kein Mensch, was Sie da tun! Und: wenn die Schnittmenge leer ist, quittiert Excel dies mit der Fehlermeldung #NULL!

Summe rechnet nicht.

Summe rechnet nicht.

Richtige Schreibweise: ; statt Leerzeichen!

Richtige Schreibweise: ; statt Leerzeichen!

Leerzeichen bedeutet Schnittmenge!

Leerzeichen bedeutet Schnittmenge!

Das würde funktionieren; ist aber unverständlich!

Das würde funktionieren; ist aber unverständlich!

 

 

SVERWEIS funktioniert nicht

Obwohl alles richtig ist, klappt der SVERWEIS nicht.

Die Lösung des Problems heißt Text uns Zahl. In der linken Hälfte der Liste sind die Postleitzahlen als Text formatiert (linksbündig), auf der rechten Seite als Zahl (rechtsbündig). Deshalb „behauptet“ Excel, dass die Postleitzahl nicht vorhanden (#NV) ist.

Die Lösung: Wandeln Sie die Zahlen in Texte um; beispielsweise mit der Funktion TEXT.

SVERWEIS klappt nicht.

SVERWEIS klappt nicht.

Excel summiert Uhrzeiten nicht richtig

Doch. Aber Excel schafft den Sprung in den neuen Tag, also über die 24-Stunden-Grenze nicht.

Die Lösung: Sie müssen die Uhrzeit vom Typ [h]:ss formatieren. Oder in der Kategorie „Uhrzeit“ finden Sie ein Beispiel „37:30:50“. Das macht das Gleiche.

Stunden werden falsch formatiert.

Stunden werden falsch formatiert.

Die Summe ist korrekt; sie muss mit [h]:ss formatiert werden.

Die Summe ist korrekt; sie muss mit [h]:ss formatiert werden.

Excel kann keine Stunden summieren

Ein Klick auf das Symbol „Autosumme“ und Excel weigert sich Stunden zu summieren. Dabei sind doch Uhrzeiten auch Zahlen – die muss er doch summieren können.

Die Antwort: Aus irgend einem Grund weigert sich Excel bei Uhrzeiten die Summe anzuwenden. Markieren Sie die Uhrzeiten – dann werden sie summiert. Keine Ahnung, warum er sich bei Uhrzeiten sträubt zu rechnen …

Excel weigert sich Stunden zu summieren.

Excel weigert sich Stunden zu summieren.

Summe rechnet falsch

Man braucht schon ein sehr gutes Auge, um auf Anhieb erkennen zu können, warum Excel hier falsch rechnet.

Ein Tipp: Wenn man mit den Mauszeiger über den Bereich streicht, fällt auf, dass er seine Gestalt bei der Zahl 3 ändert. Ein Klick darauf ertappt den „Bösewicht“: Auf der Zelle liegt ein weißes Textfeld, in dem die Zahl 3 steht. Darunter steht natürlich eine andere Zahl.

Warum rechnet die Summe nicht richtig?

Warum rechnet die Summe nicht richtig?

Der "seltsame" Mauszeiger gibt einen Hinweis.

Der „seltsame“ Mauszeiger gibt einen Hinweis.

Ein Textfeld ...

Ein Textfeld …

... und darunter eine andere Zahl.

… und darunter eine andere Zahl.

Excel rechnet nicht

Das kann nicht sein – diese Formel muss ein Ergebnis liefern. Das Resultat lautet jedoch: 0.

Der Fehler liegt in der falschen Formatierung. Natürlich ergibt die Funktion STABWN (Standardabweichung) ein Ergebnis – allerdings 0,15- Da die Zahl als Ganzzahl ohne Nachkommastellen formatiert ist, sieht man leider nur den Wert 0 und nicht das korrekte Ergebnis 0,15.

Kein Ergebnis sichtbar

Kein Ergebnis sichtbar

Excel rechnet nicht mehr, beziehungsweise rechnet falsch

Was ist los – Excel will nicht mehr rechnen. Am Anfang hat es funktioniert …

Schauen Sie in der Registerkarte „Formeln“ nach, ob die Berechnungsoptionen auf „manuell“ gestellt wurde. Wenn ja, so wird die Berechnung zwar beim Erstellen der Formel durchgeführt, aber beim Ändern der Werte nicht aktualisiert. Ändern Sie diese Option auf „automatisch“ oder aktualisieren Sie das Ganze mit der Funktionstaste [F9].

Excel will nicht mehr (richtig) rechnen.

Excel will nicht mehr (richtig) rechnen.

Die Lösung: Berechnungsoptionen

Die Lösung: Berechnungsoptionen

Ort und Datum kann nicht kombiniert werden

Die Formel

=“Hamburg, „&HEUTE()

funktioniert nicht.

Stimmt. Beim Verketten von Texten (übrigens würde die Funktion Verketten das gleiche Ergebnis liefern), wird nicht die formatierte Datumsinformation verwendet, sondern der interne Wert des Datums. Lösung schafft die Funktion:

=“Hamburg, „&TEXT(HEUTE();“TT.MM.JJJJ“)

Oder ein benutzerdefiniertes Datumsformat.

Datum verketten - funktioniert nicht

Datum verketten – funktioniert nicht

Mit der Funktion TEXT klappt es ...

Mit der Funktion TEXT klappt es …

... oder mit einem benutzerdefinierten Datumsformat (Zahlenformat)

… oder mit einem benutzerdefinierten Datumsformat (Zahlenformat)

Summe rechnet falsch

Offensichtlich rechnet die Summe falsch. Man muss nicht gut kopfrechnen können, um das festzustellen.

Die Lösung: zwei Zeilen (18 und 19) sind ausgeblendet. Dort verbergen sich die Zahlen, die zu der falschen Summe führen.

Die Antwort: Alles markieren und alle Zeilen einblenden. Dann sieht man die Zahlen, mit denen Excel rechnet.

Die Summe rechnet falsch.

Die Summe rechnet falsch.

Man muss schon genau hinschauen, um die Quelle des Fehlers zu finden.

Man muss schon genau hinschauen, um die Quelle des Fehlers zu finden.

Zirkelbezug

Ich gestehe – ich habe den Fehler nicht gleich gesehen. Warum rechnet die Summe in der Zelle D21 nicht? Zuerst dachte ich, dass ein falsches Format unter der Zelle liegt, dass die automatische Berechnung ausgeschaltet wurde, dass der Bezug nicht korrekt ist, dass die Formel fehlerhaft eingegeben wurde. War alles nicht der Fall.

Dann fiel mein Blick in die Statuszeile – in der Zelle D1 befand sich eine Zelle mit einem Zirkelbezug. Zu meiner Ehrenrettung – diese Zelle war außerhalb des sichtbaren Bereiches. Deshalb habe ich nicht gleich die 0 gesehen, die der Zirkelbezug liefert.

Zirkelbezug

Zirkelbezug

Text oder Zahl?

Eigentlich müsste man es sofort sehen: Texte stehen in Zellen am rechten Rand; Zahlen linksbündig. Wenn man sich vertippt, beispielsweise den Buchstaben „O“ statt die Ziffer „0“ oder den Buchstaben „l“ statt der Ziffer „1“ eingibt, kann Excel mit diesen Texten nicht rechnen.

Warum machen Menschen so etwas? Es gab einige Schreibmaschinen, auf denen gab es keine Ziffer „0“ oder keine Ziffer „1“. Dort musste man auf die Buchstaben „O“, beziehungsweise „l“ zurückgreifen.

Bitte kein "O" statt der Ziffer "0"; bitte kein "l" statt der Ziffer "1" eingeben.

Bitte kein „O“ statt der Ziffer „0“; bitte kein „l“ statt der Ziffer „1“ eingeben.

SUMME vs. + | PRODUKT vs. * | QUOTIENT vs. /

Ist Ihnen aufgefallen, dass der Rechenoperator + etwas anderes macht als die Funktion SUMME? Dass * anders rechnet als die Funktion PRODUKT? Dass die Funktion QUOTIENT etwas anderes berechnet als der Divisionsoperator / wird schnell klar – QUOTIENT liefert den ganzzahligen Anteil einer Division.

Bei Summe und +, beziehungsweise Produkt und * ist der Unterschied nicht ganz so offensichtlich:

Summe und Produkt übergeht Texte, während + und * einen Fehler (#WERT) liefern. Der Operator * interpretiert eine leere Zelle als Wert 0, während die Funktion Produkt diese übergeht.

Also Achtung: + ist nicht das Gleiche wie Summe in Excel, * nicht das Gleiche wie * und / schon gar nicht das Gleiche wie Quotient.

Übrigens – bei dem selten verwendeten Rechenoperator ^ rechnet Excel offensichtlich genauso wie mit der Funktion POTENZ.

Summe und +, beziehungsweise Produkt und * im Vergleich.

Summe und +, beziehungsweise Produkt und * im Vergleich.

Die dahinter liegenden Funktionen

Die dahinter liegenden Funktionen