Kategorie: Merkwürdige Formeln

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

Merkwürdige Formeln

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

Kommentar hinterlassen

Datum gesucht

Merkwürdige Formeln

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!

 

Ein Kommentar , ,

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

Merkwürdige Formeln

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.

Kommentar hinterlassen

Feiertage wären klasse

Merkwürdige Formeln

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

2 Kommentare , , ,

mal links, mal oben

Merkwürdige Formeln

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.

Kommentar hinterlassen ,

Kein Kommentar? – Doch

Merkwürdige Formeln

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!

Kommentar hinterlassen

Inhalte einfügen – nicht ganz

Merkwürdige Formeln

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

Kommentar hinterlassen

Da fehlt doch was!

Merkwürdige Formeln

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!

Kommentar hinterlassen

Function not found

Merkwürdige Formeln

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

Kommentar hinterlassen , ,

Teile und herrsche …

Merkwürdige Formeln

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.

4 Kommentare

Seltsame Datumsangaben

Merkwürdige Formeln, Zahlenformate

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?

,

Ort und Datum kann nicht kombiniert werden

Merkwürdige Formeln, Zahlenformate

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)

2 Kommentare ,
1 2