Category Archives: Merkwürdige Formeln

Auf der Verpackung des Wildlachses, den ich gerade verzehre, steht: „Fisch mit Zukunft“. Ich will ja nicht schwarzsehen, aber rosig wird diese Zukunft sicherlich nicht …

Auch wenn die bedingte Formatierung in Excel bei Datenbalken, Farbskalen und Symbolsätzen behauptet, sie könne „Formeln“, unterstützt sie jedoch nur absolute Bezüge. Schade!

Ein Teilnehmer der letzten Excelschulung hatte folgendes Problem. Eine Liste von Mitarbeitern und Mitarbeiterinnen nehmen an Fortbildungen teil. In einer Spalte werden die Summen der Stunden, an sie an Fortbildungen teilgenommen haben, aufgelistet. Eigentlich sollten sie bis zum ersten Quartal 15 Stunden absolviert haben, bis Ende des zweiten Quartals 22,5 Stunden, 30 bis Ausgang des dritten Quartals und 45,5 bis Ende des Jahres. Die Hälfte wäre noch okay – wünschenswert ist die volle Stundenzahl.
Nun möchte der Controller durch lustige Fähnchen (grün, gelb und rot) den Status visualisieren. Ein gemischter Bezug der Form =C$2 wäre gut. Allerdings weigert sich die bedingte Formatierung:

Absolute Bezüge werden unterstützt, relative und gemischte leider nicht. Also muss man für die vier Quartale vier Bezüge erstellen. Zum Glück sind es nur vier!

Ohne Brille werde ich oft angesprochen – ich weiß nur leider nicht, von wem.

Excelschulung. Eine Teilnehmerin möchte eine Dropdownliste durch eine Datenüberprüfung haben, in der Smileys angezeigt werden. Ich überlege: in der Schriftart Wingdings gibt es drei Smileys. Man kann sie über Einfügen / Symbol einfügen, oder indem man die Buchstaben J, K und L mit der Schriftart Wingdings formatiert.

Fügt man eine Datenüberprüfung ein, werden jedoch nur die drei Buchstaben dargestellt – auch das Formatieren der Zelle nutzt nichts:

Ich überlege: vielleicht werde ich in den nicht druckbaren Zeichen fündig, die man mit [ALT] + [1], [ALT] + [2], … erzeugen kann. Jedoch finden sich nur zwei Smileys hinter den Nummern 1 und 2:

Mourad hat eine Idee und hilft mir. Im Unicode-Zeichensatz (beispielsweise der Schriftart Calibri) finden sich Smileys:

https://de.wikipedia.org/wiki/Unicodeblock_Smileys?fbclid=IwAR0LB-Y2bptAeo9O0qfDe7QXo_ArCgd8ektotaDSVsFqDC5exITcmk39fbQ

Wenn ich Word die Unicode-Zahl eintrage (beispielsweise U+1F600) und anschließend [ALT] + [C] erhalte ich das dahinter liegende Symbol:

In Excel funktioniert das leider nicht. Muss ich die Zeichen von Word nach Excel kopieren? Quatsch, meint Mourad – du kannst sie doch direkt von der Internetseite nach Excel kopieren:

Oder mit der Funktion UNIZEICHEN umwandeln, also beispielsweise:

=UNIZEICHEN(128512)

Klappt! Und so können wir eine lustige Auswahlliste erstellen:

Auch mein Add-In [Strg] + [Q] funktioniert:

Eine großes Dankeschön an Mourad Louha für die Hilfe.

Meine Lust ist gerade losgegangen meine Motivation zu suchen. Jetzt sind beide weg!

Hallo zusammen,

ich möchte folgendes in Excel durchführen.

Ich habe verschiedene Spalten in Excel mit Daten.

Spalte A:

Name

Spalte B:

Vorname

Spalte C:

Geburtsdatum

Spalte D:

Organisation

Excel soll mir jetzt die Einträge markieren die doppelt sind, jedoch nur wenn Name und Vorname identisch sind sprich Spalte A UND B.

Wie kann ich das am besten lösen.

Doppelte Werte anzeigen ist ja an sich nicht schwierig jedoch weiss ich nicht wie ich in dem o.g. Fall vorgehen muss.

Habt ihr da zufällig eine Lösung

Hallo Jörg,

hast du XVERGLEICH?

Dann könntest du es mit folgender Formel in der bedingten Formatierung lösen:

=WENNNV(XVERGLEICH($A2&$B2;$A3:$A$27&$B3:$B$27;0);0)+WENNNV(XVERGLEICH($A2&$B2;$A1:$A$1&$B1:$B$1;0);0)

wenn in A und B Name und Vorname stehen. Ich fange immer in einer Zelle an; trage dort die Bed. Formatierung ein und erweitere ANSCHLIESSEND den Bereich.

cool. Die Funktion xVergleich kenne ich gar nicht. Vielen Dank für den Tipp

XVERWEIS und XVERGLEICH erweitern SVERWEIS und VERGLEICH. Umgekehrt: ich habe es zuerst mit

=Zählenwenn(A:A&B:B;“Rene Martin“)

versucht – geht aber leider nicht … ZÄHLENWENN versagt hier … Schade!

Ich führe gerade mit dem Rad einen Autokorso an. Es wird gehupt und geschrien – die Stimmung ist riesig!

Excelschulung. Wir üben die WENN-Funktion. Ich erkläre, dass man Text in Excel in Anführungszeichen setzen muss – manche Assistenten machen dies automatisch; andere nicht.

Meine Empfehlung: Immer per Hand die Anführungszeichen setzen:

Danach üben wir die bedingte Formatierung – auch eine Art „WENN“. Da die Teilnehmerinnen und Teilnehmer meinen Rat befolgen, tragen Sie den Text in Anführungszeichen ein:

Was passiert? – Nichts! Der Grund:

Excel wandelt den Text „Pandora Papers“ in „““Pandora Papers“““ um – Excel geht davon aus, dass die Anführungszeichen Teil des Suchtextes sind. Also raus damit!

Ich esse eine Tafel Schoklade meistens innerhalb von drei Minuten auf. Das liegt daran, dass mir wirklich wichtig ist, dass sie frisch ist und nciht so lange geöffnet!

Für eine Firma erstelle eine Feiertagstabelle. Der Lieferant verlangt einen Zuschlag, wenn in der Woche (Mo – Fr) ein Feiertag liegt. Die Basis sind die Feiertage von NRW.

Diese Liste wird auf einem anderen Tabellenblatt verwendet:

Ich klicke auf das Symbol f(x), um die Formel im Funktionsassistenten zu bearbeiten:

Das Ergebnis: „Formelergebnis = Veränderlich“ ?!? Ein Klick auf [OK] und das Meldungsfenster wird geschlossen. Aha!

Immerhin: die Formelüberwachung funktioniert:

Wer spät zu Bett geht und früh heraus muss, weiß, woher das Wort Morgengrauen kommt.

Zuerst hört sich die Frage ganz einfach an, aber dann kam ich ins Schleudern:

„Hallo Rene

zum Thema Datumsberechnungen hätte ich gleich eine Frage:

kann Excel auch Zeiträume erkennen, die sich überschneiden, aber unterschiedliche Anfangs- und Endzeiten haben?

Also zum Beispiel:

Mitarbeiter A arbeitet vom 01.05.2021 bis 31.08.2021

Mitarbeiter B arbeitet vom 01.06.2021 bis 15.09.2021

In welchem Zeitraum haben beide gearbeitet

Oder

Von Bis Thema
8:00 12:00 Nachdenken
11:30 12:30 Pause
12:30 15:00 Nix tun

Ich überlege. Und erweitere das Beispiel. Das Oktoberfest hätte in diesem Jahr vom 18. September bis 03. Oktober stattgefunden. Adelheid ist von 01.09. bis 30.09 in München; Basti vom 25.09. bis 25.10.; Christoph vom 27.09. bis 30.09.; Doris vom 10.09. bis 10.10.; Erich vom 01.09. bis 10.09. und Franziska vom 10.10. bis 20.10. Wie viele Tage hätten sie aufs Oktoberfest gehen können?

Im ersten Schritt habe ich das visualisiert:

Ich beginne mit Erich und Franziska: Wenn Ende < Beginn Oktoberfest, dann 0. Wenn Anfang > Ende Oktoberfest, dann 0:

=WENN(B33<$B$21;0)

Dann die umschließenden Bereiche:

=WENN(UND(B28>=$B$21;B29<=B22);B29-B28+1;0)

Und schließlich die überschneidenden Bereiche:

Wenn Anfang <= Anfang Oktoberfest und Ende <= Ende Oktoberfest, dann Ende – Anfang Oktoberfest:

=WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0)

Analog die zweite Überschneidung:

Wenn Ende >= Ende Oktoberfest und Anfang >= Anfang Oktoberfest, dann Ende Oktoberfest – Anfang:

=WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)

Dabei fällt auf, dass die ersten beiden Fälle mit einbezogen werden – man muss sie explizit ausschließen, beispielsweise mit einem dritten Fall:

und Ende >= Anfang Oktoberfest, beziehungsweise: Anfang <= Ende Oktoberfest:

=WENN(UND(B24<=$B$21;B25<=$B$22;B25>=$B$21);B25-$B$21+1;0)

beziehungsweise:

=WENN(UND(B27>=$B$22;B26>=$B$21;B26<=$B$22);$B$22-B26+1;0)

so:

Ein Summieren der Varianten verbietet sich, da stets mit >= und <= gearbeitet wurde. So könnte ein Datumsbereich vom 18.09. bis 20.09. in zwei der sechs Kategorien fallen. Entweder man ändert einige der <=in < oder man baue den Baum auf:

  1. Fall: leere Menge
  2. Fall: ganzer Bereich
  3. Fall: Überschneidung
  1. Fall:
=WENN(ODER(B33<$B$21;B32>$B$22);0)

2. Fall a: Datumsbereich liegt im Oktoberfestzeitraum:

=WENN(ODER(B33<$B$21;B32>$B$22);0;WENN(UND(B32>=$B$21;B33<=$B$22);B33-B32+1;0))

2. Fall b: Oktoberfestzeitraum liegt im Datumsbereich:

=WENN(ODER(B31<$B$21;B30>$B$22);0;WENN(UND(B30>=$B$21;B31<=$B$22);B31-B30+1;WENN(UND($B$21>=B30;$B$22<=B31);$B$22-$B$21+1;0)))

Und schließlich die beiden Fälle Nummer 3:

=WENN(ODER(B25<$B$21;B24>$B$22);0;WENN(UND(B24>=$B$21;B25<=$B$22);B25-B24+1;WENN(UND($B$21>=B24;$B$22<=B25);$B$22-$B$21+1;WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0))))

und:

=WENN(ODER(B27<$B$21;B26>$B$22);0;WENN(UND(B26>=$B$21;B27<=$B$22);B27-B26+1;WENN(UND($B$21>=B26;$B$22<=B27);$B$22-$B$21+1;WENN(UND(B26<=$B$21;B27<=$B$22);B27-$B$21+1;WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)))))

Natürlich hätte man die Bedingungen auch umdrehen können.

Natürlich hätte man den Bedingungsbaum auch anders aufbauen können:

  1. Fall: Anfang <= Oktoberfest Anfang? Ja:
    Fall a) Ende vor Oktoberfest Anfang?
    Fall b) Ende nach Oktoberfest Ende?
    Fall c) Ende zwischen Oktoberfest Anfang und Ende?
    […]

Die Teilnehmerinnen waren begeistert und erschöpft. Leider haben sie nur Excel 2016. Ich überlegte mir, ob man das Problem nicht mit den Matrixfunktionen SEQUENZ & co lösen kann. Die Antwort: Man kann:

Wir beginnen mit Adelheid. Im ersten Schritt werden alle Adelheid-Tage und Oktoberfesttage aufgelistet. Die Funktion SEQUENZ hilft hierbei:

=SEQUENZ(A6-A5+1;1;A5)

Nun kann man zählen, wie oft jeder Adelheid-Tag in der Oktoberfest-Tagesliste vorkommt – einmal oder kein Mal:

=ZÄHLENWENN($E$1#;C1)

Und diese Kolonne kann man summieren.

Oder in einer Formel:

=SUMME(ZÄHLENWENN($C$1#;SEQUENZ(A6-A5+1;1;A5)))

Geht das auch mit FILTER? Klar:

=FILTER(C1#;(C1#>=A5)*(C1#<=A6))

Oder in einer Formel:

=FILTER(SEQUENZ(A2-A1+1;1;A1);(SEQUENZ(A2-A1+1;1;A1)>=A5)*(SEQUENZ(A2-A1+1;1;A1)<=A6))

Ich bin sicher, dass es für dieses Problem noch weitere Lösungen gibt. Viel Spaß beim Knobeln.

Hallo Rene,
meine Lösungsvorschlag kommt mit einer einer einfachen Formel (ohne Matrixformel) aus.

Das Startdatum des Oktoberfest steht in B2
Das Enddatum des Oktoberfest steht in B3

Der erste Tag von Adele in München steht in B5
Der letzte Tag von Adele in München steht in B6

Dann berechnet folgende Formel die Überschneidung, also die Anzahl von Tagen die Adele auf das Oktoberfest gehen kann.

=ZEILEN(INDEX(A:A;B2):INDEX(A:A;B3) INDEX(A:A;B5):INDEX(A:A;B6))

Im Falle Adele 13 Tage.

Für die anderen Personen muss diese Formel nur entsprechend angepasst werden.
Gibt es keine Überschneidung wie bei Erich und Franziska gibt die Formel den Wert #NULL! aus.
Wichtig ist das Leerzeichen in der Mitte der Formel. Dadurch wird die Schnittmenge ermittelt.

Salü

Ernst

PS: Nachtrag:

Allerdings kann man durch eine kleine Änderung der Formel meines Lösungsvorschlages (aus Zeilen() mache Zeile() und gebe die Formel als Matrixformel ein) eine einspaltige Matrix erzeugen, in der die Datumswerte der Schnittmenge eingetragen sind.

Also

=ZEILEN(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6))

Ergebnis = 13

{=ZEILE(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6)) }

Ergebnis ( Zellen als kurzes Datum formatiert.):

18.09.2021
19.09.2021
20.09.2021
21.09.2021
22.09.2021
23.09.2021
24.09.2021
25.09.2021
26.09.2021
27.09.2021
28.09.2021
29.09.2021
30.09.2021

Danke Ernst – sehr clever!

Und schließlich hat Helmut Cantzler eine Lösung mit SUMMENPRODUKT gefunden:

=SUMMENPRODUKT((SEQUENZ($B$3-$B$2+1;;$B$2;1)<=B6)*(SEQUENZ($B$3-$B$2+1;;$B$2;1)>=B5))

Danke an Helmut – auch sehr clever!

Und schließlich kann man das Problem auch mit VBA lösen. Man muss zwei Bereiche (Range) definieren und die Schnittmenge (Application.Intersect) bestimmen:

    Dim xlBereich1 As Range
    Dim xlBereich2 As Range
    Dim xlSchnittmenge As Range
    With ThisWorkbook.Worksheets("Helmut")
        Set xlBereich1 = .Range(.Cells(Range("B2").Value2, 1), 
           .Cells(.Range("B3").Value2, 1))
        Set xlBereich2 = .Range(.Cells(Range("B5").Value2, 1), 
           .Cells(.Range("B6").Value2, 1))
    End With
    Set xlSchnittmenge = Application.Intersect(xlBereich1, xlBereich2)
    MsgBox xlSchnittmenge.Cells.Count

Oder in einem Befehl:

MsgBox Application.Intersect(Range(Cells(Range("B2").Value2, 1), Cells(Range("B3").Value2, 1)), Range(Cells(Range("B5").Value2, 1), Cells(Range("B6").Value2, 1))).Cells.Count

Klappt! Danke an Andreas Protzmann für diesen Hinweis. Auch clever!

Und schließlich reagiert Christian:

Auch sehr clever! Und sehr elegant! Danke, Christian.

Karotten verbessern die Sehkraft. Bier verdoppelt sie!

Hallo René,

ich lese regelmäßig deinen Blog „Excel nervt“ und hatte dir vor einiger Zeit auch schon einmal eine Mail zu deinem Buch „Excel: Zahlen. Rechnen. Formeln“ geschickt, die du mir sehr freundlich und ausführlich beantwortet hattest.

Heute bräuchte ich mal deine Hilfe. Ich sitze hier vor einem Problem, bei dem ich alleine nicht weiterkomme. Es geht um einen Milchviehbetrieb, der seine Jungtiere von einem Aufzuchtsbetrieb großziehen lässt. Ich soll nun rückwirkend die Aufzuchtskosten pro Monat überprüfen. Dazu steht mir eine Tabelle zur Verfügung mit dem Abgangsdatum der Tiere vom Milchviehbetrieb (= Zugangsdatum Aufzuchtsbetrieb) und dem Zugangsdatum der Tiere beim Milchviehbetrieb. Ist das Tier aktuell noch beim Aufzuchtsbetrieb, ist jeweilige Feld für das Zugangsdatum leer. Pro Tag, den eine Kuh beim Aufzuchtsbetrieb ist, erhält der Aufzuchtsbetrieb eine Pauschale (z.B. 1€ pro Kuh und Tag). Ich hatte irgendwie gedacht, dass man das relativ leicht über eine Formel ermitteln könnte. Problematisch ist vor allem ein angebrochener Monat, wenn beispielsweise eine Kuh am 5. März 2021 an den Aufzuchtsbetrieb geliefert wird, erhält der Aufzuchtsbetrieb für diese Kuh ja theoretisch 27 €.

Anbei habe ich eine Beispieltabelle hinzugefügt. In den Spalten E bis R möchte für den jeweiligen Monat und für jede Kuh die Tage ermitteln, die diese beim Aufzuchtsbetrieb war.

Kannst du mir da weiterhelfen? Irgendwie stehe ich gerade auf dem Schlauch.

Vielen Dank im Voraus.

Hallo,

hübsche Fingerübung.

Ich würde in die erste Zeile jeweils den 01. April 2020, 01. Mai 2020, … und als April 2020, Mai 2020, … formatieren. Also mit MMM JJJJ

Du musst einen „Baum abarbeiten“:

1. Fall: sind Jahr und Monat identisch -> dann rechne die Anzahl Tage bis Ende des Monats.

=WENN(UND(JAHR(E$1)=JAHR($C2);MONAT(E$1)=MONAT($C2));MONATSENDE($C2;0)-$C2+1;0)

Die Erklärung: Ich brauche in diesem Fall die Anzahl der Tage bis zum Ende des Monats. Die Funktion

MONATSENDE($C2;0)

berechnet den letzten Tag des Monats (hier: 30.04.2020). Und davon wird das Datum abgezogen (hier: 11.04.2020). Das Ergebnis lautet 19; plus 1 = 20 = die Anzahl der Tage vom 11. bis zum 30. (beide einschließlich)

2. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem nächsten Monat -> dann voller Monat (die zweite WENN-Funktion wird die Stelle der 0 gesetzt:)

=WENN(UND(JAHR(F$1)=JAHR($C2);MONAT(F$1)=MONAT($C2));
MONATSENDE($C2;0)-$C2+1;
WENN(UND($C2<F$1;$D2>=G$1);TAG(MONATSENDE(F$1;0));0))

Die Erklärung:

TAG(MONATSENDE(F$1;0))

liefert die Anzahl der Tage des Monats – hier 31 beim Mai.

3. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem Datum (bspw. 01. April) -> Anzahl der Tage des Monats

=WENN(UND(JAHR(M$1)=JAHR($C2);MONAT(M$1)=MONAT($C2));
MONATSENDE($C2;0)-$C2+1;
WENN(UND($C2<M$1;$D2>=N$1);TAG(MONATSENDE(M$1;0));
WENN(UND($C2<M$1;$D2>M$1);TAG($D2);0)))

Auch hier wird wieder die 0 durch eine neue, dritte WENN-Funktion ersetzt. Die Funktion

TAG($D2)

berechnet die Anzahl der Tage bis zum Tag, also aus dem 17.12.2020 wird die Zahl 17 berechnet.

4. Fall: noch kein Zugang eingetragen und Abgang vor dem Datum (bspw. 01. April) -> dann Tag des Monats

=WENN(UND(JAHR(Q$1)=JAHR($C7);MONAT(Q$1)=MONAT($C7));
MONATSENDE($C7;0)-$C7+1;
WENN(UND($C7<Q$1;$D7>=R$1);TAG(MONATSENDE(Q$1;0));
WENN(UND($C7<Q$1;$D7>Q$1);TAG($D7);
WENN(UND($D7="";$C7<Q$1);TAG(MONATSENDE(Q$1;0));0))))

Die Erklärung: Ebenso wie oben berechnet

TAG(MONATSENDE(Q$1;0)

die Anzahl der Tage des Monats.

Stimmt das?

Hallo Rene,

vielen Dank für die schnelle Antwort.

Für diese Kühe hatte ich die Auswertung händisch vorgenommen und komme auf das gleiche Ergebnis wie mit deiner Formel. Scheint also zu passen.

Einen „Baum“ hatte ich mir auch schon überlegt, allerdings hatte ich Schwierigkeiten bei dem Umgang mit den „angebrochenen“ Monaten und den Kühen ohne Zugangsdatum.

In der ersten Zeile hatte ich sogar schon jeweils den 1. des Monats als Datum eingetragen, weil ich damit rechnen wollte. Ich hatte es dann über „Zellen formatieren…“ – Kategorie: Datum – Typ: Mrz. 12 umgewandelt. Blöd, dass Excel dann beim Mai auch einen Punkt macht, wie mir gerade auffällt.

Vielen, vielen Dank für deine Hilfe und mach weiter so. Ich werde deinen Blog auf jeden Fall weiter verfolgen.

Liebe Grüße

Ein Pessimist flucht, wenn ihm ein Vogel auf dem Kopf kackt. Ein Optimist freut sich, dass Kühe nicht fliegen können.

Schöne Frage in der letzten Excelschulung:

Wie kann ich es erreichen, dass eine Datenreihe im 15-Minuten-Takt fortgesetzt wird? Beispielsweise für einen Stundenplan. Ich schaue nach:

Erstaunlicherweise lässt der Assistent „Datenreihe“, den man in der Gruppe „Bearbeiten“ in der Registerkarte „Start“ findet, keine Uhrzeiten zu …

Natürlich könnte man es mit einer Formel erreichen:

=A2+15/24/60

Da in der Schulung Anfängerinnen waren, die bislang noch wenig Erfahrung mit Formeln hatten, schlage ich die naheliegende Lösung vor: zwei Startzeiten eintragen, markieren und runterziehen:

Geht auch so!

Grundregeln im Restaurant: Alles, was sich bewegt – bedienen! Alles, was sich nicht bewegt: Putzen! Ausnahme: Der Chef. Den nicht putzen!

Sehr geehrter Herr Martin,

mit großem Interesse verfolge ich Ihre Excel-Seminare und mag es sehr von Ihnen neue Dinge zu lernen und mein Wissen zu erweitern. Helfen Sie auch bei speziellen Excel-Problemen? Ich habe hier eine größere Datenbank. Basis sind verschiedene Materialnummer in einer Spalte, welche mehrmals auftreten, da es zu den Materialien mehrere Bestellungen gibt mit unterschiedlichen Konditionen. Mich interessiert pro Materialnummer der Maximalpreis. Ich habe hier an die 80 Materialien und dazu jeweils mehrere Bestellungen. Der Maximalpreis sollte dann in einer zz. Spalte erscheinen. Mit freundlichen Grüßen

Hallo Frau S.,
ich würde eine Pivottabelle erstelle. Gruppieren Sie die Materialnummern und ziehen Sie dann die Preise in das Wertefeld. Ändern Sie in den Wertfeldeinstellungen die Summe in MAX.
Alternative: Wenn Sie Excel in Microsoft 365 haben: mit der Funktion EINDEUTIG erhalten sie die eindeutige Liste der Materialnummern. Mit MAXWENN können Sie das MAX pro ID berechnen lassen. Hilft Ihnen das?
LG :: Rene Martin

Hallo Herr Martin, vielen Dank für die prompte Antwort. Die Pivottabelle ist der Lösung für mich. Entsprechende Seminare dazu stehen noch auf meiner persönlichen Agenda. Für den akuten Fall, haben Sie mir prima geholfen. Grüße,

Intelligente Überschriften zu schreiben, ist gar nicht so einfach. Schon ein einziger Buchstabendreher kann den ganzen Inhalt urinieren.

… und ich kann es nicht lassen, zu mailen, mit den Infos:

  • aktuelle Formel (Konstrukt von gestern), wo noch etwas fehlt:
    =WENN(ANZAHL(J9;L9;N9)=0;““; WENN(ODER(ANZAHL(J9;L9;N9)=2;ANZAHL(J9;L9;N9)=3); MITTELWERT(J9;L9;N9); „“))
  • drei Eingabezellen
  • wenn keine Eingaben in den Zellen >> Zelle mit Ergebnis ist leer
  • der MW wird berechnet, wenn zwei oder drei Zellen Werte enthalten

Frage: Kann man noch prüfen
wenn ODER(Prüfung auf TEXT(J9), Prüfung auf TEXT(L9), Prüfung auf TEXT(N9)): „“
D.h.: Wenn mindestens eine der Zellen Text beinhaltet >>> Ergebnis der Zelle: Nix drinnen
Ich habe schon ein wenig schlechtes Gewissen …
Ich maile noch ein DANKESCHÖN & Gruß
Jürgen

Hallo Jürgen,

deine Bedingungen werden mit

=WENNFEHLER(WENN(ANZAHL(J9;L9;N9)>1;MITTELWERT(J9;L9;N9);"");"")

abgefangen. Die Funktion MITTELWERT übergeht (ebenso wie SUMME, ANZAHL, MAX, …) Texte. Senn du das explizit abfangen willst, dann beispielsweise so:

=WENNFEHLER(WENN(ANZAHL(J9;L9;N9)<ANZAHL2(J9;L9;N9);"";WENN(ANZAHL(J9;L9;N9)>1;MITTELWERT(J9;L9;N9);""));"")

Liebe Grüße

Rene

Haben heute ein Koffein-Shampoo benutzt. Meine Haare sind jetzt schon auf dem Weg zur Arbeit. Ich suche noch die Hose.

Excelschulung. Eine junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:

Die Werte werden kummuliert. In der ersten Zelle steht

=WENN(F15<>"";F15;"")

Darunter befindet sich die Formel:

=WENN(G15<>"";G15+F16;"")

So hatte ich es vor vielen Jahren auch gemacht. Inwzischen löse ich das Problem der kummulierten Werte mit einer Formel, die ich nach unten ziehe:

=SUMME($F$15:F15)

Fazit: Lehrer und Lehrerinnen sollten regelmäßig ihre Übungsaufgaben durchsehen und überprüfen, ob es bessere Lösungestechniken, neue Funktionen gibt, mit denen man Aufgaben eleganer lösen kann und testen, ob sie einige Bedingungen vergessen haben.

Ich achte auf meine Ernährung: Was schmeckt, wird gegessen.

Excelschulung. Eine junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:

Der prozentuale Anteil wird berechnet:

=WENN(UND(A15<>"";B15<>"");D15*100/$D$25;"")

Okay, mit der Funktion

UND(A15<>"";B15<>"")

wird überprüft, ob die Zellen der Spalte A und B gefüllt sind. Eine hübsche Übung, um UND und WENN zu üben.

Dennoch: WENNFEHLER wäre sicherlich besser gewesen, um ALLE Fehler abzufangen.

Aber noch mehr irritiert mich die Berechnung

D15*100/$D$25

Warum wird mit 100 multipliziert? Ich hätte den Anteil von einer Dezimalzahl in eine Prozentzahl formatiert. Und damit weitergerechnet.

Wäre das nicht eher im Sinne von Excel gewesen?

Stellt euch vor, ihr lasst eine Dornenhecke um euer Schloss wachsen, um in Ruhe schlafen zu können und dann kommt ein übermotivierter Prinz und versaut wieder alles.

Excelschulung. Eine junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:

Ich komme bei der Verwendung der Funktion RANG ins Grübeln:

=RANG(B15;$B$15:$B$24;0)

Wenn man diese Funktion in Excel eintippt, sieht man vor dem FUnktionsnamen ein Ausrufezeichen. Das bedeutet, dass diese Funktion durch andere erstetzt wurde und aus Kompatibilitätsgründen noch zur Verfügung steht. Richtig: in Excel 2010 wurd diese Funktion RANG durch RANG.GLEICH und RANG.MITTELW abgelöst. Man findet RANG nun in der Kategorie Kompatibilität:

Das Problem bei der Funktion Rang ist die Antwort auf die Frage, welche Zahl weisen wir zwei gleich großen Werten zu? Also: wenn es den größten Werte zwei Mal gibt? Zählen wir dann 1; 1; 3 (so rechnet RANG.GLEICH und RANG) oder 1,5; 1,5; 3 – so rechnet RANG.MITTELW.

Ich probiere es aus, ändere zwei Werte so, dass sie gleich groß sind. Das Ergebnis: Fehler in der weiteren Berechnung, die nicht abgefangen wurden:

Fazit: vielleicht hätte der Lehrer oder die Lehrerin in den letzten zehn Jahren einmal das Beispiel neu nachrechnen sollen und auf Konsistenz prüfen sollen. Und vor allem: prüfen sollen, ob es inzwischen nicht neue, bessere Funktionen zur Lösung des von ihm oder ihr gestellten Problems gibt.

Warum bestellen wir runde Pizzas in quadratischen Schachteln und essen sie dann als Dreiecke?

Excelschulung. Eine junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:

In Spalte A stehen Lieferantennummern, in Spalte B Umsatzzahlen. In Spalte C wird der Rang berechnet:

=RANG(B15;$B$15:$B$24;0)

Doch schon Spalte D lässt mich stutzen:

=INDEX($A$15:$B$24;(VERGLEICH(1;$C$15:$C$24;0));2)

Darunter befindet sich die Formel:

=INDEX($A$15:$B$24;(VERGLEICH(2;$C$15:$C$24;0));2)

Danach Nummer 3, Nummer 4, … Uff! Kennt der Lehrer oder die Lehrerin nicht die Funktion ZEILE? Ich stutze und wundere mich …

#####

Hallo Rene,

kannst du vielleicht auch deinen Verbesserungsvorschlag für die Formel dazu schreiben. Mir erschließt sich grad nämlich nicht, wie man die Funktion ZEILE hier einbauen soll. Wahrscheinlich steh ich nur auf dem Schlauch…

Lieber Anonymous,

die Funktion ZEILE hat zwei „Gesichter“:

=ZEILE()

liefert die Zeilennummer der aktuellen Zelle. Steht also diese Funktion in C7,liefert =ZEILE() die Zahl 7. Beim Herunterziehen erhalte ich 8, 9, 10, …

Die Funktion der Lehrerin oder des Lehrers

VERGLEICH(1;$C$15:$C$24;0)

sucht den Wert 1 in der Rang-Spalte, also die Zeile, die den größten Wert enthält.

VERGLEICH(2;$C$15:$C$24;0)

sucht den zweitgrößten Wert.

Ich bin sicher, dass die Lehrerin oder der Lehrer die Werte 1, 2, 3, 4, … per Hand getippt hat. Da die Formel in D15 stand, hätte man auch schreiben (und herunterziehen) können:

=INDEX($A$15:$B$24;(VERGLEICH(ZEILE()-14;$C$15:$C$24;0));2)

Auch das ist noch etwas umständlich. Man kann mit ZEILE einen Bezug auf eine Zelle herstellen und von dieser Zeile die Zeilennummer ermitteln. Also:

=ZEILE(A1)

liefert 1. Und beim Herunterziehen erhält man die Werte1, 2, 3, 4, die den größten, zweitgrößten, drittgrößten, … Wert liefert. Also:

=INDEX($A$15:$B$24;(VERGLEICH(ZEILE(A1);$C$15:$C$24;0));2)

Entweder finden Jogger Leichen oder sie werden selbst ermordet. – Was soll daran gesund sein?

Excelschulung. EIne junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen WENN und SVERWEIS:

Ich sehe folgende Formel:

=WENN(A17="";"";SVERWEIS(A17;Leistung;2))

Ist ja okay zu überprüfen, ob A17 leer ist, aber wäre es nicht besser ALLE Fehler mit einem WENNFEHLER abzufangen?

Sicherlich dient diese Formel WENN zum Üben …

Zweite Grundregel der Gastronomie: Immer Butter bei die Tische!

Hallo Rene!

Hoffe, dass du, deine Familie und dein Umfeld alle gesund seid.

Du hast mir vor einiger Zeit geholfen mehrere Belegnummern in eine Zelle zu schreiben. Nun wollte ich auch das Datum (auch mehrere) auf gleiche Weise anzeigen lassen. Herausgekommen sind dann die Zahl(en) vom Datum. Wenn nur eine Zahl (=Datum) angezeigt wurde konnte ich das Datumformat. Wenn nur ein Zahlenwert vorhanden ist, habe ich, wie gelernt, die Formel mit 1 multipliziert und dann das Datumformat angewendet. Meine Vermutung ist, dass man die Formel um eine Formatierungsformel ergänzt. Videos über die allgemeine Formatierung mit Formeln in der Zelle habe ich leider nicht gefunden. Bitte um Lösung.

Vielen Dank, Peter

Hallo Peter,   die Formel muss statt

=TEXTVERKETTEN(", ";WAHR; FILTER(tbl_KF_Kosten[Datum];tbl_KF_Kosten[Pferd]=E30;"0")) 

  lauten:   

=TEXTVERKETTEN(", ";WAHR; TEXT(FILTER(tbl_KF_Kosten[Datum];tbl_KF_Kosten[Pferd]=E30;"0");"TT.MM.JJJJ")) 

   Die Funktion TEXT formatiert eine Zahl (und damit auch ein Datum).

LG Rene

Wenn man Kindern erzählt, dass Toffifee aus karamellisierten Kuhaugen gemacht wird, kann man die Schachtel alleine leeressen.

Gestern in der Excelschulung. Interessante Frage: Wenn ich ein Datum herunterziehe, ist die Schrittweise ein Tag. Wenn ich eine Uhrzeit herunterziehe ist die Schrittweise eine Stunde. Kann ich das in Excel einstellen, dass Excel im 15-Minuten-Takt zählt?

Ich überlege. Da gibt es doch den Assistenten Ausfüllen / Datenreihe im Register „Start“:

Allerdings: dort kann man nur die Schrittweite für Datumsangaben – nicht für Uhrzeiten eintragen:

Damit bleibt nur:

  • Entweder zwei Uhrzeiten eintragen, markieren und herunterziehen
  • Oder eine Formel: Bezug auf die obere Zelle und dann plus 15 (Minute) / 24 (Stunden) / 60 (Minuten):

Bei diesen Temperaturen Bier in einem geschlossenen Auto zu lassen – das ist schon Bierquälerei!

Kennen Sie den Median? Die Excel-Funktion MEDIAN?

Gemeint ist – umgangssprachlich – der mittlere Wert einer Datenreihe. Hat man zwei mittlere Werte (bei einer geraden Anzahl Elemente), berechnet sich der Median als Durchschnitt der beiden mittleren Werte.

Wer braucht denn so etwas?

Stellen Sie sich vor, sie haben eine Basketballmannschaft, die aus fünf Spielern besteht: vier recht kleinen und einem sehr langen Spieler. Der Mittelwert würde das Bild „verzerren“, weil der Ausreißer den Durchschnitt nach oben verschiebt. Der Median gibt dagegen ein besseres Bild der Mitte, weil er resistent gegenüber Ausreißern ist:

Median – eine statistische Größe also. Nun finde ich in einer Berechnung von Transportkosten folgende Formel:

=WENN(G15="";"";WENN(UND(ODER(MAX(Logistikdaten!I38:K38)>120;MEDIAN(Logistikdaten!I38:K38)>60;MIN(Logistikdaten!I38:K38)>60);$G$15<=440);"Ja";"Nein"))

Verwundert reibe ich die Augen? Warum werden die Logistikkosten mit einem Median berechnet?

Dann verstehe ich: es wird Bezug genommen auf die drei Werte Länge, Breite und Höhe. Von diesen drei Werten wird der größte Wert (MAX), der kleinste Wert (MIN) und der mittlere Wert (MEDIAN) berechnet. Sehr clever!

Die wollen doch tatsächlich, dass ich auch Dienstag bis Freitag komme. In der Stellenanzeige stand ausdrücklich: „Facharbeiter für Montage gesucht.“

Ich muss per Programmierung den Inhalt einer Formel mit einem Bezug auswerten. Ich überlege:

Jede Bezugsformel innerhalb einer Arbeitsmappe hat in Excel die Form:

=Blattname!Zellbezug

Prima! Liegt auf dem Blatt „BMW“ in der Zelle „Z8“ ein Wert, kann man die Formel

=BMW!Z8

gut auswerten, indem man an dem Ausrufezeichen trennt. Vor dem Ausrufezeichen: Blattname; hinter dem Ausrufezeichen: Zellbezug. Gesagt – getan.

Jedoch: mir fällt auf, dass mein Programm manchmal einen Fehler produziert. Beispielsweise beim Bezug auf das Blatt „Alfa Romeo“, „Aston Martin“, „Rolls-Roycs“, und so weiter. Klar, ein Bezug auf das Blatt „Alfa Romeo“ wird dargestellt als:

=’Alfa Romeo‘!P1

Da der Blattname ein Leerzeichen (oder Gedankenstrich) enthält, muss ich den Apostroph aus dem Blattnamen löschen. Wirklich? Sollte ich ihn nicht besser von links und rechts löschen? Also: Wenn das erste Zeichen = ‚, dann entfernen. Wenn das letzte Zeichen = ‚, dann entfernen? Kann ein Blattname ein Apostroph enthalten? Ich probiere aus:

Tatsächlich: Mercedes‘ Benz funktioniert! Excel verbietet bei Namen von Tabellenblättern am Anfang und am Ende ein Apostroph, aber innerhalb des Namens ist es erlaubt.

Und wie sieht der Bezug auf dieses Blatt aus?

='Mercedes'' Benz'!W117

Erstaunlich! Der Apostroph wird entwertet, indem das Zeichen zwei Mal geschrieben wird! Das ist mir noch nie aufgefallen!

Das heißt: ich muss zwei Hochkommata (‚ ‚) durch eines ersetzen und muss den Apostroph am Anfang und am Ende löschen. Perfide!

Übrigens: Bevor Sie jetzt erboste Kommentare schreiben: die in Sindelfingen produzierende Automobilfirma heißt MERCEDES BENZ – ohne Apostroph!

Nur noch zwei Mal joggen, dann ist wieder Weihnachten.

Eine sehr hübsche Knobelaufgabe. Ich erstelle Formulare für eine Firma, die Waren liefert. Dabei sind einige komplexe Berechnungen nötig. Beispielsweise folgende:

„Sollte das Lieferdatum in eine Woche mit einem Feiertag fallen, werden 25% mehr Kosten berechnet“.

Ich frage nach: „Welche Feiertage?“ Die Antwort: die Feiertage von NRW.

Zweite Frage: Wenn der Feiertag auf einen Samstag oder Sonntag fällt? Die Antwort: dann soll er nicht berücksichtigt werden.

Erster Schritt: Ich erstelle eine Liste der Feiertage von Nordrhein-Westfalen:

Man kann die beweglichen Feiertage auf Basis des Ostersonntags berechnen, für den es eine Formel gibt. Und die festen Feiertage berechnen. Oder man kopiert sich diese Liste aus dem Internet. Oder greift mit PowerQuery auf eine Feiertagsliste im Internet zu.

Im zweiten Schritt erstelle ich eine Spalte mit Datumsangaben – beispielsweise vom 30.12.2019 bis zum 31.12.2034.

Die Formel

=WENN(UND(ZÄHLENWENN($B$2:$P$13;A21)>0;WOCHENTAG(A21;2)<=5);"F";"")

prüft, ob das Datum ein Feiertag ist (also in der Feiertagsliste steht) und ob der Feiertag auf einen Tag von Montag bis Freitag fällt (also Wochentag <= 5):

Danach überprüfe ich, ob in der Woche ein Feiertag („F“) liegt:

Dazu verwende ich die Formel:

=WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A21;-WOCHENTAG(A21;3);1;7;1);"F")>0;"F";"")

Der Rest ist trivial, oder? Man sucht ein Datum in der ersten Datumsspalte und prüft, ob in der Wochenspalte ein „F“ steht oder nicht:

=WENN(SVERWEIS(H20;A21:C5501;3;FALSCH)="F";25%;0%)

Beispiel: der 01. Mai 2021 fiel auf einen Samstag, also ist für den 29. April kein Zuschlag erforderlich.

Pfingstmontag war in diesem Jahr am 24. Mai – also muss für den 26. Mai 2021 ein Zuschlag bezahlt werden.

Eine hübsche Knobelaufgabe – ich habe ein bisschen überlegen müssen.

Eine Fee fragte mich, ob ich einen riesigen Penis oder ein tolles Gedächtnis haben möchte. Ich habe gewählt. Aber ich kann mich nicht mehr erinnern, wofür ich mich entschieden habe.

Hallo Herr Martin,

könnten Sie bitte noch berechnen, ob das Paket Sperrgut ist.

Ich frage zurück: „Woran erkenne ich, ob es Sperrgut ist?“ Ich erhalte die schriftliche Antwort:

„Länge und Breite dürfen nicht größer als 2,40 Meter sein.“

Ich überlege:

Länge + Breite < 2,4?

Länge < 2,4 UND Breite < 2,4?

Länge < 2,4 ODER Breite < 2,4?

Was ist mit der Höhe? – Kann man das Paket nicht drehen?

Ich warte noch auf die Antwort.

Wahrscheinlich überlegen sie selbst …

Schatz – wir lebennun seit fünf Jahren hier zusammen – findest du mich schüchtern? – DU WOHNST HIER?

Letzte Woche hatte ich eine Einzelplatzschulung. Nennen wir es Coaching: ein älterer Herr wollte sich fit in den Anwendungsprogrammen machen. Ich erkläre die Grundlagen: Text und Zahl. „Bitte schließen Sie die Eingabe immer mit [Enter] ab.“ (Dass es noch andere Möglichkeiten gibt, wusste er selbst.) „Bitte drücken Sie immer die Ok-Taste. In der Kirche wird sie auch die [Amen]-Taste genannt.“ Er schmunzelt.

Wir üben Formeln und drücken [Enter]. Immer. Nicht wegklicken! Okay – verstanden.

Wir ziehen die Formel am Ausfüllkästchen nach unten. Er drückt [Enter]. Okay … ich muss mich ein bisschen korrigieren.

Kann man bei dem Auto noch etwas retten? – Ja, man könnte ein neues Auto zwischen die Nummernschilder schrauben.

Ich habe hier einige Artikel zu dem kostenlosen PlugIn Excel-DNA geschrieben, mit dem man Excelfunktionen (und andere Werkzeuge) in Excel schreiben kann und welche Probleme es dabei gibt.

Nun habe ich einen Artikel – nein: genauer: es sind drei Artikel – darüber veröffentlicht: in der neuen dotnetpro.

 

OLYMPUS DIGITAL CAMERA

Menschen, die mich an der Kasse vorlassen, weil sie sehen, dass ich es eilig habe und nur wenige Dinge in der Hand habe, kommen übrigens in den Himmel.

Bernhard hat mich darauf aufmerksam gemacht. Ist mir bislang nicht aufgefallen.

Die Funktion DATEDIF in Excel und die VBA-Funktion DateDiff rechnen unterschiedlich.

Trägt man in zwei Zellen die Datumsangaben 20.05.2021 und 01.06.2021 ein, so beträgt bei der Excelfunktion DATEDIF mit dem Parameter „M“ das Ergebnis 0, bei der VBA-Funktion dagegen 1.

Ich habe eine kleine Tabelle aufgebaut: im oberen Teil einige Datumsdifferenzen auf Basis des Monats:

Im unteren Teil verwende ich ein kleines VBA-Makro:

Sub BerechneDateDIFF()
     Dim intZeile As Integer
     Dim intSpalte As Integer

     For intZeile = 21 To 30
         For intSpalte = 2 To 20
             ActiveSheet.Cells(intZeile, intSpalte).Value = DateDiff("M", ActiveSheet.Cells(intZeile, 1).Value, ActiveSheet.Cells(20, intSpalte).Value)
         Next intSpalte
     Next intZeile

End Sub

Das Ergebnis:

Die Unterschiede habe ich mit einer bedingten Formatierung farblich hervorgehoben.

Ein Dankeschön für den wertvollen Hinweis an Bernhard Ramroth.

Meine Kaffeemaschine wurde repariert. Der Techniker meinte, die Muffbrieme war verbrömmelt und angeeumelt. Man musste nur die Dichtbrumme abquastern und neue Flanschpaddendranwöhnern. Die sind nun vierfach gefröbbelt und neu umwienert. Na – das hätte ich auch selbst machen können.

Witzig. Vorlauter Formeln und Assistenten übersieht man manchmal die Wirklichkeit.

Wir schauen die Zielwertsuche an. Mit der Funktion RMZ (regelmäßige Zahlungen) wird die Annuität berechnet.

Nun soll „zurückgerechnet“ werden. Ich möchte wissen wie hoch der Zinssatz bei einer Rückzahlung von 220 Euro ist. Also Zielwertsuche (sie soll ja auch gezeigt werden:

Sie rechnet und liefert:

-4,92% !?! Minus !?!

Es dauert eine Weile, bis wir verstehen: Das Ergebnis ist korrekt. Man müsste mit negativen Zinsen rechnen, um auf diesen Betrag zu kommen … Doch nicht falsch und auch nicht so unrealistisch.

Danke an Hartmut Hilbich für dieses Beispiel.

Natürlich spreche ich mit mir selbst. Manchmal brauche ich eben eine kompetente Beratung.

Gestern im Excelstammtisch. Frank Arendt-Theilen macht darauf aufmerksam, das PowerQuery einen Parameter bei der Funktionen RUNDEN (Number.Round) anbietet:

IntelliSense zeigt allerdings in M, dass diese Funktion einen weiteren Parameter besitzt: roundingMode mit fünf Konstanten:

Die Standardeinstellung von PowerQuery ist RoundingMode.ToEven. Damit unterscheidet sich diese Rundenfunktion von RUNDEN in Excel. Dort wird RoundingMode.AwayFromZero verwendet. Deshalb unterscheiden sich diese beiden Runden-Funktionen: PQ rundet wie VBA; Excel rundet anders …

Vielen Dank, Frank für diesen sehr, sehr wertvollen Hinweis!

Und: während sich auf der Microsoft-Hilfeseite

https://docs.microsoft.com/de-de/powerquery-m/number-round

leider kein Hinweis dazu findet, hat Ken Pulse es sehr gut erläutert:

https://www.excelguru.ca/blog/2014/09/17/power-query-the-round-function/

XLarium weist darauf hin, dass es noch weitere Artikel und Videos zu diesem Thema gibt:

MrExcel hat vor kurzem ein Video dazu gemacht:

Weitere Videos zum Thema Runden:

MROUND In Power Query

TRUNC In Power Quer

CEILING oder FLOOR in Power Query

Nein, ich habe deine Kochkünste nicht kritisiert. Ich habe lediglich gesagt, dass wir den einzigen Hund im Ort haben, der nicht am Tisch bettelt.

Hallo Herr Martin,

ich filtere in dem Kunden-Excel die Werte mittels einer Pivot-Tabelle. Der Filter zeigt aber nur die tatsächlich vorhandenen Werte an.

Jetzt möchte ich im Pivot einen festen Wertefilter definieren, unabhängig welche aktuellen Werte vorhanden sind:

Beispiel:

Ich möchte immer alle Einträge < 24 Stunden gefiltert haben

Aktuelle Werteinträge sind 1 und 2 Stunden. Den Filter 24 Stunden kann ich aber erst auswählen, wenn es mindestens einen Eintrag mit 24 Stunden gibt. Lässt sich dies im Pivot einstellen. Ich habe bislang keine Möglichkeit gefunden.

Hallo Herr H.,

der Gedanke der Pivottabelle ist ja, die vorhandenen Werte zu gruppieren und die Zahlen zusammenzufassen (aggregieren, also: summieren, zählen, …) Wenn Sie andere Werte sehen möchten, müssen diese in der Liste stehen (man müsste sie ausblenden).

Hallo Herr Martin,

es gibt im Office 365 Excel den Befehl FILTER, der genau das macht, was ich benötige. Nur hat mein Kunde leider eine ältere Version. Lässt sich das in einem älteren Excel mit einem workaround bauen?

Viele Grüße

Hallo Herr H.,

Nein – bitte nicht die Funktion FILTER verwenden – sonst hat die Firma ein Problem!

Was würde ich tun?

* entweder die Daten dazwischen verstecken (und die Zeilen ausblenden)

* oder die Daten auf einem anderen Blatt sammeln und dort alle notwendigen Daten einsammeln.

Hum. Sonst? Müsste mal überlegen

Liebe Grüße

Rene Martin

Hallo Herr Martin,

Filter geht beim Kunden nicht, aber ist die Funktion so gefährlich?

Ich habe mittels Pivot die Daten auf ein anderes Blatt ausgelagert und nutze das Ergebnis für die Dropdown-Felder.

Ich habe jetzt einen Dummy-Wert eingefügt, damit die Pivots die Auswahlfelder behalten, auch wenn keine Daten auszuwerten sind. Ggf. wäre eine Lösung ganz ohne Pivots zu arbeiten, aber dazu bräuchte ich so etwas wie die Filter-Funktion.

Hallo Herr H.,

nein, nein: FILTER & co sind klasse – Problem: nicht jeder hat diese Funktionen. Deshalb: bauen wir den Filter doch nach!

Werfen Sie mal einen Blick in meine Liste: In Spalte H befinden sich die sechs Werte. Einer wird in J2 ausgewählt. In Spalte L ermittle ich die Zeilennummer, falls gefunden. In Spalte M sammle ich diese Nummern ein; gruppiere sie also. Mit BEREICH.VERSCHIEBEN baue ich die Liste ab O1 auf (ich hätte auch INDIREKT oder INDEX / VERGLEICH verwenden können).

Ich könnte es auch mit AGGREGAT aufbauen – aber lassen wir das …

kommen Sie damit klar?

Liebe Grüße

Rene Martin

Die Ehe ist ein lustiges Wort für die Übernahme eines erwachsenen männlichen Kindes, dessen Eltern nicht mehr in der Lage waren, die Situation zu bewältigen.

Hallo Herr Martin,

ich wieß nicht mehr genau, ob ich Sie einmal auf folgende Thematik angesprochen hatte.

Es ging um die Umformatierung von Postleitzahlen aus einer Spalte in Aneinanderreihung

in einer Zelle mit Komma/-Lehrzeichen-Trennung (siehe Anhang „screenshot“):

Diese Schreibweise (nebeneinander) ist die auf unserem content-management-system.

Wenn ich nun größere Zahlenmengen erhalte, wird es händisch sehr aufwendig.

Könnten Sie mir bei diesem Prozedere wieder behilflich sein?

Vielen Dank schon vorab.

Mit besten Grüße

Hallo Herr S.,

welche Excel-Version haben Sie denn? Haben Sie die Funktion TEXTVERKETTEN? DAMIT klappt es ganz einfach:

=TEXTVERKETTEN(„, „;WAHR;E3:E14)

E3:E14 ist natürlich der Bereich der Postleitzahlen. Dann kopiere ich den Bereich an eine andere Stelle und füge ihn als Werte ein – schon habe ich die Liste.

Klappt das?

Liebe Grüße

René Martin

Hallo Herr Martin,

vielen dank für die schnelle Reaktion. Ich habe die 2010’er Version Version 14.7263.5000 (32 Bit)

Direkt das Wort TEXTVERKETTEN ist in den Formeln nicht enthalten.

Gibt es unter dieser alten Version vielleicht trotzdem eine Lösung? 

Beste Grüße nach München C. S.

Hallo Herr S.,

oder so:

wiederholen Sie in der ersten Zelle den Wert, bspw.: =E16

schreiben Sie darunter: =F16&“, „&E17

ziehen Sie die untere Formel runter!

das Ergebnis der letzten Zelle können Sie kopieren und an andere Stelle als Wert einfügen

Liebe Grüße

Rene Martin

Einfach genial,

Sie machen einem Freude! ! !

Einfach soo logisch, dass ich mir immer wieder die Frage stelle,

warum komme ich nicht einmal selbst auf solch logische Lösungen.

Ich hoffe, dass ich ihre Geduld und Zeit nicht zu sehr in Anspruch nehme. 

Ganz herzlichen Dank. Bleiben Sie gesund und zuversichtlich

C. S.

Ich bin froh, dass ich mein Essen nicht selbst jagen muss. Ich wüsste gar nicht, wo Pommes leben!

Christian ist irritiert. Zwar übertragen und übernehmen die Arrayfunktionen (SORTIEREN, FILTERN, SEQUENZ, …) nicht die Formatierungen:

Aber wenn man mit dem Gleichheitszeichen auf einen Bereich zugreift, wird in der ersten Zelle das Zahlenformat übernommen, in den übrigen nicht:

Erklärbar – dennoch: irritierend.

Seit ich gesehen habe, wieviel schlanker ein Bär nach seinem Winterschlaf ist, kommt mir das Konzept noch viel attraktiver vor.

Hallo Rene,

Ich hätte da nochmal eine Frage zu den Matrixfunktionen. Wenn ich die Rückgabematrizen in einen Bereichsnamen packe, funktioniert der auf dem Tabellenblatt wunderbar. Wenn ich allerdings diese Matrix über den Bereichsnamen in einer Datenüberprüfung als Liste ausgeben möchte, kann ich ihn zwar über F3 ansprechen, erhalte dann aber eine Fehlermeldung.

Gruß

Hannes

Hallo Hannes,

du musst den Namen im INDIREKT in einen Bezug umwandeln. Meinst du das?

Liebe Grüße :: Rene

Hallo Rene,

ja genau, da sind einige Haken drin.

Die Liste in der Datenüberprüfung übernimmt offensichtlich nur Werte aus Bereichen die auf einem Tabellenblatt liegen und keine Rückgabewerte aus Funktionen.

Aber wenn ich innerhalb einer „formatierten“ Tabelle einen benannten Bereich anlege passt sich dieser Bereich auf die Tabellengröße an und die Werte können auch in der Datenüberprüfung dynamisch verwendet werden.

Wenn ich über die Sequenz-Funktion Werte ermittle und die in einen Bereichsnamen packe, werden die Werte innerhalb eines Tabellenblattes zurückgegeben, innerhalb der Datenüberprüfung jedoch nicht als Liste übernommen.

Die Problematik ist wahrscheinlich, dass die Datenüberprüfung ausschließlich Werte aus dem Tabellenblatt ausliest, daher auch die Bezugsherstellung mit der Indirekt-Funktion.

Keine Ahnung, ob man das irgendwie austricksen kann.

Gruß

Hannes

PS: hier mein „Versuchsaufbau“ zu dem ich ein bisschen was erzählen könnte. Kannst ja vorab schon mal einen Blick drauf werfen, ob das interessant sein könnte

Ich habe gerade „ach-da-brauchst-du-nichts-draufschreiben-man-sieht-ja-was-drin-ist“ aus der Gefriertruhe geholt und bin gespannt, was ich heute koche.

Hi Rene,

sag mal: kann man in intelligenten Tabellen keine Matrixfunktionen verwenden? Hintergrund: ich möchte gerne mit der Funktion SORTIEREN oder SORTIERNNACH eine Liste sortieren und diese sortierte Liste als Basis für eine Tabelle verwenden. Also so:

Und dann passiert:

Hallo Johannes,

eine kleine Überlegung: (Intelligente) Tabellen „denken“ nur zeilenweise oder in Bezug auf eine ganze Spalte. Also:
=[@Umsatz]*19%
oder:
=SUMME(Tabelle1[Umsatz])
analog:
=TEILERGEBNIS(109;[Umsatz])

DAS kollidiert mit einer Arrayfunktion (EINDEUTIG, SORTIEREN, SORTIENNACH, FILTER), die dynamisch einen Bereich definiert oder ZUFALLSMATRIX und SEQUENZ, in die die Größe eines Bereichs eingetragen wird.

Moin Rene,

Danke Dir für die ergänzenden Infos 🙂   Ich habe es jetzt so gelöst … So hat sich auch der Vorteil ergeben, dass es etwas übersichtlicher ist 🙂

Neuer Trend bei Deutschlehrern: Kommasaufen.

Sehr geehrter Herr Martin,

ich habe eben begonnen, Ihre Übungen zu den statistischen Funktionen durchzuführen.

Bei dem Thema Häufigkeit sehe ich eine Differenz zwischen dem, was Sie zeigen und dem, was ich gemacht habe, obwohl die Zahlen, die Sie benutzen, die Zahlen sind, die ich auch genommen habe (ich habe diese Zahlen von Ihrem Beispiel abgetippt).

Ich verstehe nicht den Unterschied, obwohl ich wie Sie die Funktion Häufigkeit verwendet habe.

Das betrifft auch das Ausrechnen der Häufigkeit als Matrix. Auch hier unterscheiden sich die Zahlen.

Zur Überprüfung schicke ich Ihnen meine Datei mit.

Hallo Herr F.,

HÄUFIGKEIT liegt in zwei Varianten vor: als Matrixfunktion und als „normale“. Wenn Sie im „neuen“ Excel HÄUFIGKEIT verwenden, wird es als Arrayfunktion interpretiert: wie viele Daten sind bis zu Wert x, wie viele darüber:

Bei den „älteren“ Excel-Versionen ist dies nicht nötig. So kann ich die Funktion runterziehen. Dazu muss der Datenbereich fixiert werden. Die Daten werden also kumuliert:

Da HÄUFGKEIT matrixfähig ist, muss ich (in denen neueren Excel-Versionen) nicht mehr mit Umschalt + Strg + Enter beenden. Dann werden die einzelnen Bereichen berechnet:

Warum sind Blondinenwitze immer so kurz? – Damit auch Männer sie verstehen!

Lieber Herr Martin,

Excel nervt nicht, ich weiß nur noch nicht ob es kann was ich gerne hätte.

Ich müsste Zellen in Spalten verknüpfen, wenn diese einer Bedingung in einer anderen Zelle

Entsprechen. So wie SummeWENN ich diese Zellen zusammenaddieren kann ist die Frage ob

Ich den Inhalt der Zellen auch verkettet ausgeben kann.

Vielen Dank

Bevor ich eine Antwort gebe, Frau L.,

zwei Gegenfragen: sind die Daten sortiert (nach den Kategorien, nach denen sie gruppiert werden sollen) und: haben Sie die Funktionen FILTER und EINDEUTIG? Haben Sie XVERWEIS? (damit geht es „recht einfach“)

Liebe Grüße

Rene Martin

So ist es mir recht,

leider kein XVERWEIS und kein WENNS… hoffe auf ein besseres 2021…

Eindeutig? Index? Geht nicht mit Liste sondern nur Matrix oder Bezug, damit kenn ich mich nicht aus…

Gruppiert nach der Nummer insofern dass sie untereinander geschrieben wurden (händisch…) allerdings Filter vorhanden

Die erste Spalte soll als Referenz herangezogen werden die letzte Spalte beinhaltet die Informationen die ich gerne zusammen verkettet hätte, also

Für 20-44 sollte dort MFM, MFM; CPS, PIB stehen, fantastisch wäre, wenn jeder Wert nur 1x vorkommen würde, aber soweit wage ich nicht zu träumen…

Beim Trennzeichen wäre ich emotional flexibel

Die erste Spalte wird in einem Übersichtssheet sozusagen als Einzeiler ausgegeben.

Vielen Dank

Hallo Frau L.,

ich hoffe, Sie haben die Funktion TEXTVERKETTEN. DAMIT klappt es.

Entweder Sie setzen eine Pivottabelle auf die Liste auf, gruppieren die Daten und verketten dann die Infos der letzten Spalte. Dann haben Sie allerdings Duplikate.

Oder Sie erstellen zwei Pivottabellen: eine für die gruppierten Infos der ersten Spalte und eine mit den Werten der ersten und letzten Spalte. Und verketten so die Texte.

Die erste Formel lautet:

=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($I$1;
VERGLEICH(L2;$A$2:$A$27;0);0;
VERGLEICH(L2;$A$2:$A$27;1)-
VERGLEICH(L2;$A$2:$A$27;0)+1))

Die zweite:

=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($M$31;
VERGLEICH(O32;$L$32:$L$51;0);0;
VERGLEICH(O32;$L$32:$L$51;1)-
VERGLEICH(O32;$L$32:$L$51;0)+1))

Klappt das? Ist es das, was Sie möchten?

Oma: Homofiss – iss datt wedder son Schwulengrupp? – Enkel: Oma: datt heet Homeoffice. Net Homo-fiss!

Angelika ruft erneut an: „Hallo Rene: SVERWEIS kann DOCH zwischen Groß- und Kleinschreibung unterscheiden. Schau mal, ich hab’s gefunden!“

Prinzipiell glaube ich andere Menschen NICHT. Ich schaue die Datei an, die sie mir geschickt hat:

„Schau“, sagt sie, „der Text arnstein steht einmal groß- und einmal kleingeschrieben in der Liste. Wenn ich die Liste nun sortiere, und den Parameter WAHR beim SVERWEIS verwende, findet Excel den unteren, großgeschriebenen Eintrag.“

Ich prüfe das und trage den Text in Kleinbuchstaben ein: „arnstein“.

Auch hier wird der untere gefunden. Ich stutze: „Angelika: deine Liste ist nicht sortiert. Probier mal aus: =CODE(„A“) ergibt 65, =CODE(„a“) liefert 97. Arnstein ist kleiner als arnstein. Du musst anders herum sortieren.

Wir probieren es aus:

und auch:

Beide Male wird der untere Text gefunden. Die folgende Vermutung liegt nahe:

SVERWEIS „läuft“ bei der Verwendung des Parameters WAHR in [Bereich_Verweis] in einer Liste so lange nach unten bis ein größerer Wert als der gesuchte gefunden wird. Dann „stoppt“ SVERWEIS und gibt den Wert der zuletzt gefundenen Zeile zurück. Dabei wird weder bei WAHR noch bei FALSCH zwischen Groß- und Kleinschreibung unterschieden (wie fast an kleiner Stelle in Excel).

Fazit: Wenn man Texte in Listen mit SVERWEIS suchen muss (was durchaus sein kann – Artikelnummern, Personalnummer, Länderkennzeichen, etc. die alphanumerisch aufgebaut sind), sollte man NIEMALS die Liste sortieren und mit dem Parameter WAHR (oder ohne diesen Parameter) in SVERWEIS arbeiten. Schließlich möchte man ein eindeutiges Ergebnis. WAHR ist nur bei numerischen Reihen sinnvoll, beispielsweise: Umsatzzahlen, Erlöse, Gewinne, Kosten, Kilometer … von Betrag x bis Betrag y. Oder Datum von d1 bis d2.

Ich glaube, Angelika war mit meiner Erklärung einverstanden. Ganz sicher bin ich mir nicht. Ich werde sie noch einmal anrufen.

Habe meiner Pflanze vorgeschlagen, sie nur noch einmal im Monat zu gießen. Sie ist darauf eingegangen.

Angelika fragt weiter: „Kennst du das Problem? Ich habe eine Liste in der Begriffe mehrmals in unterschiedlicher Schreibweise vorliegen – manchmal in Großbuchstaben, manchmal nur in Kleinschreibweise. SVERWEIS findet leider nur den ersten Eintrag – egal wie er geschrieben ist.“

Bevor ich antworten kann, beantwortet Angelika ihre Frage selbst: „Ich habe vor Kurzem einen Artikel von Martin gelesen; dem Tabellenexperten – er beschreibt dort das Problem mit der Lösung IDENTISCH. Diese Funktion unterscheidet Groß- und Kleinschreibung.“

https://www.tabellenexperte.de/wie-sverweis-nur-richtig/

„Ja“, pflichte ihr bei: „den Artikel habe ich auch gelesen. Ich habe es ausprobiert – ja, er hat recht. Und: seine Lösung funktioniert so!“

In der Singlebörse wollte sich jemand mit mir treffen um spazieren zu gehen. Solche Sportsfreaks blockier ich doch gleich!

Lieber Herr Martin,

Ich muss zugeben, Excel nervt mich normalerweise nicht, aber ev beschäftige ich mich zuwenig

Damit..

Jetzt habe ich einen neuen Job und muss SVERWEISE nachvollziehen, prinzipiell fühle ich mich dazu

In der Lage, allerdings habe ich heute als Spaltenindex folgendes gefunden.

=SVERWEIS($A393;’specs quotation‘!$F$14:$DD$150;F$2;FALSCH)

Was bedeuted F$2

Vielleicht können sie mir ja helfen.

Vielen Dank

Judith

Ps: ich liebe ihre Seite :-J

Hallo Judith,

danke für das Lob.

Die Antwort ist (wahrscheinlich) einfach:

Schauen Sie mal in dem Tabellenblatt nach, welcher Wert in der Zelle F2 steht. Mit Sicherheit die Nummer der Spalte aus der der Wert des Bereichs !$F$14:$DD$150 des Blattes „specs quotation“ geholt wird.

Ich vermute, dass der SVERWEIS in mehreren Zellen der gleichen Spalte verwendet wird – deshalb wurde die Zeile 2 fixiert, also statt F2 wurde geschrieben F$2. Vielleicht wird er auch in mehreren Spalten verwendet. Was steht in G2? Was in H2? Wenn der SVERWEIS auch in mehreren Spalten benutzt wird, dann bedeutet F (ohne $-Zeichen) ein relativer Bezug – das heißt: in der Spalte daneben wird der Wert aus G$2 verwendet.

Wenn SVERWEIS nur in einer Spalte zu finden ist, hätte man auch $F$2 schreiben können, F$2 ist aber auch okay.

verstehen Sie die Ausführung?

schöne Grüße aus dem kalten und sonnigen München
Rene Martin

Lieber Herr Martin,

Vielen Dank, in Zelle F2 steht leider nix und in allen Formel Zeilen nach unten steht immer F$2 (eh klar),

die Info die in der Zelle aufscheint steht in Spalte :-J…

Es braucht definitiv Smiley in Outlook…

Ev nervt es ja doch, noch so kurz vor Weihnachten…

Danke

Lg Judith

Hallo Judith,

klicken Sie mal bitte auf die Zelle mit der Formel SVERWEIS.

klicken Sie mal auf den Funktionsassistenten fx neben der Eingabezelle.

Dort müsste der Werte von F$2 angezeigt werden:

Die Erleuchtung kam gerade, yup, in F2 steht 5, dass ist die 5te Spalte in der der Wert

Steht ausgehend von der Matrix.. Excel nervt doch nicht J

Vielen Dank!

Liebe Grüße ins wunderschöne München!

Frohe Feiertage und einen guten Rutsch ins nächste Jahr!

Judith

Wenn du die Wahl hättest zwischen Traumfrau und Traumauto – welche Felgen würdest du dann nehmen?

Hallo Rene!

Benötige bitte deine Hilfe.

Zum Jahresende fallen die Auszahlungen der Subventionen an.

Die vorgelegten Unterlagen entsprechen nicht immer den Formvorschriften.

Damit bei dem Jahresbericht zu lesen ist warum etwas nicht anerkannt wurde habe ich die beiliegende Tabelle erstellt.

Nun möchte ich, dass bei der Zusammenfassung der Paare bei den Anmerkungen und Beleg-Nr. die Ziffern aus der Aufstellung jeweils in einer Zwelle eingetragen werden.

Bitte um deine bewährte Hilfe.

Bleib gesund,

mit besten Grüßen

Peter

Meine erste Überlegung: mit VERGLEICH(F28;tbl_KF_Kosten[Turnierpaar];1)-VERGLEICH(F28;tbl_KF_Kosten[Turnierpaar];0) kann ich die Differnez zwischen letzten gefundenem Wert und erstem gefundenem Wert ermitteln. BEREICH.VERSCHIEBEN liefert mir den Bereich; TEXTVERKETTEN fasst sie zusammen. Das Problem hierbei ist jedoch, dass die Werte nicht gruppiert untereinander stehen und auch nicht alphabetisch sortiert sind. Also: zweiter Versuch: FILTER:

=TEXTVERKETTEN(",";WAHR;FILTER(tbl_KF_Kosten[Bel
Klub];tbl_KF_Kosten[Turnierpaar]=F28;""))

Und damit geht es hervorragend!

Nach den ganzen Fleisch- und Wurstskandalen der letzten Monate und Jahre habe ich beschlossen nur noch Lebkuchen zu essen. Einen Lebkuchenskandal gab es noch nie!

Ich verwende beim Programmieren und auch oft in Excel gerne das Pipe-Zeichen (senkrechter Strich) „|“ ([Alt Gr] + [<] – auf der Schweizer Tastatur [Alt Gr] + [7]; [ALT] + 1 2 4) als Trennzeichen. Dabei bin ich ziemlich sicher, dass es in einem „normalen“ Text nicht vorkommt. Also beispielsweise:

Brummbär|Pimpel|Happy|Chef|Hatschi|Schlafmütz|Seppel

Umso erstaunter war ich über das Ergebnis der folgenden Formel:

Warum liefert die Formel

=ZÄHLENWENNS(Tabelle2[@[Jan 20]:[Dez 20]];"I")

den Wert 6. Ich habe eine Weile überlegen müssen, bis ich verstanden habe, dass der Buchstabe „I“ nicht „|“ ist – bei einigen Schriften sehe die beiden Zeichen sehr ähnlich aus. Vielleicht sollten wir – analog dem Türkischen – auch ein Punkt auf das große I setzen: İ.

Die einen gehen nachts feiern. Andere haben nachts wilden Sex. Und ich lade nachts mein Handy auf.

Seit ein paar Tagen habe ich in Microsoft 365 die neue Funktion LET. Damit kann man Formeln unter einem Namen ablegen, um sie (mehrmals) wieder zu verwenden. Beispielsweise so:

=ZELLE(„dateiname“)

oder besser:

=ZELLE(„filename“)

liefert den Namen und Speicherort der aktuellen Datei:

Benötigt man nun den Pfad, kann man das Zeichen „[“ suchen (oder finden):

=SUCHEN(„[„;D3)

liefert in unserem Beispiel 19. Bis ein Zeichen vor diesem Zeichen kann von links der Text herausgelöst werden:

=LINKS(D3;D4-1)

liefert hier: D:\Eigene Dateien\

Oder zusammengefasst:

=LINKS(ZELLE(„dateiname“);SUCHEN(„[„;ZELLE(„dateiname“))-1)

Darin wird nun zwei Mal die Funktion ZELLE(„dateiname“) verwendet. Man kann sie an einen Namen in der Funktion LET übergeben, beispielsweise an „Dateiname“:

=LET(Dateiname;ZELLE(„dateiname“);LINKS(Dateiname;SUCHEN(„[„;Dateiname)-1))

Allerdings: Das Ergebnis ist länger als die vorherige Funktion. Also ein kürzerer Funktionsname:

=LET(DN;ZELLE(„dateiname“);LINKS(DN;SUCHEN(„[„;DN)-1))

Das erhöht allerdings nicht gerade die Lesbarkeit!

Fazit: LET ist sicherlich eine nützliche und praktische Funktion. Allerdings kommt sie wohl erst bei sehr langen Formeln zum Einsatz. Und auch nur dann wenn Formelteile sich mehrmals wiederholen. Legt man diese Teil unter einem (langen) sprechenden Namen ab, wird das Ergebnis weder kürzer noch gut lesbar. Ich fürchte, sie wird wohl in den wenigsten Berechnungen in Excel in Microsoft 365 Einzug finden …

Jahrelang hat man uns eingeredet, dass nach 18 Uhr gegessene Kohlenhydrate dick machen. Und jetzt kommt raus: Kohlehydrate wissen gar nicht, wie spät es ist.

Hallo Rene!

Nach längerer Zeit wieder eine Bitte!

Durch den Corona-Virus musste ich die Termine für meine ABO-Vorstellungen selbst zusammenstellen.

Die Auswahl der Stücke war in ‚ABO Stücke‘ bald erstellt. Mit Pivot habe ich mir die Theaterstücke zusätzlich unterschiedlich angeordnet. 

In diesen Tabellen wollte ich die Zellen färbig (Der Leuchtturm) markieren, die in ‚ABO Stücke‘ grün sind. Das war mir nicht möglich.

Im Internet habe ich entdeckt, dass eine Zellfärbung in der Pivot-Tabelle nur mit den dortigen Daten möglich ist.

Nachdem Excel so viele Möglichkeiten bietet die Daten aufzubereiten und darzustellen meine oben erwähnte Bitte

um eine Lösung.

Mit freundlichen Grüßen

Peter

Hallo Peter,

das geht so nicht. Excel kann nicht per Formel Formatierungen abfragen.

Du benötigst im Tabellenblatt „ABO Stücke“ eine Hilfsspalte (bspw. Premiere: ja/nein)

und kannst dann mit einer Formel in der bedingten Formatierung anzeigen lassen:

=UND(INDEX(‚ABO Stücke‘!$F:$F;VERGLEICH($A15;’ABO Stücke‘!$D:$D;1))=“ja“;
B$14=INDEX(‚ABO Stücke‘!$B:$B;VERGLEICH($A15;’ABO Stücke‘!$D:$D;1)))

wirf mal einen Blick auf die Datei – ich habe drei Mal „ja“ eingefügt.

Liebe Grüße Rene

Hallo Rene,

danke für die schnelle Hilfe und den Hinweis.

Da stand ich auf der Leitung.

Klar, dass Excel nur mit Zahlen und Buchstaben arbeiten kann. Die Auswahl mit farbiger

Zelle ist optisch leichter zu überblicken, darum habe ich sie angewendet aber nicht bedacht,

dass Pivot natürlich einen ‚echten‘ Wert benötigt.

Mit besten Grüßen Peter

gerne, Peter,

stimmt: EXCEL kann nur Zahlen und Texte verarbeiten. Mit VBA hätte man die Möglichkeit zu überprüfen:

If Zelle.Interior.Color = …

Liebe Grüße Rene

Schau mich nicht in diesem Ton an!

Hallo Her Martin,

ich bin auf der Suche nach einer Info zu Excel auf ihre Seite „https://www.excel-nervt.de/das-nichts-in-excel/

gekommen und habe folgende (aus meiner Sicht) falsche Aussage gefunden: 

zuerst der betreffende Textabschnitt:

———–

Text, leerer Text oder kein Text

Ähnlich wie bei Zahlen geht Excel bei Texten vor. Eine Zelle kann leer sein, kann Text oder eine leere Zeichenkette beinhalten. Letzteres kann das Ergebnis einer Funktion sein oder auch direkt eingegeben werden:

=““

Die folgenden drei Abfragen liefern dann WAHR als Ergebnis:

=WENN(ISTLEER(A1);…

=WENN(A1=““;…

=WENN(ISTTEXT(A1);…

———–

Wenn ich aber einer Zelle A1 den Wert „“ (also leeren String) zugewiesen habe, liefert mir die Formel

=WENN(ISTLEER(A1);…

leider nicht den Wert wahr, sondern falsch. Die Zelle wird nicht als „leer“ erkannt.

Hiermit habe ich übrigens genau ein Problem. Ich habe nämlich das „ISTLEER“ oft benutzt und zu spät bemerkt, daß es nicht wie erwartet funktioniert. Jetzt versuche ich, die Formeln zu ändern (z.B. durch Abfrage auf Länge = 0) und die vorhandenen „“ aus Wertfeldern zu eliminieren. Leider habe ich für beides bisher noch keine ganz einfache, schnelle und sichere Vorgehensweise gefunden.

Mit freundlichen Grüßen

Michael Richter

Hallo Herr Richter,

    ich freue mich immer sehr, wenn Leserinnen und Leser auf meine Artikel reagieren – Kritik äußern, Dinge korrigieren, die ich falsch behauptet habe oder Lob äußern.

    Sie haben recht – die Artikel, die Sie unter „über“ und „schade“ finden, sind recht alt und sollten überarbeitet werden. Umgekehrt: wenn Sie „ISTLEER“ in das Suchfeld eintragen, finden Sie weitere Artikel von mir zu diesem Thema.

    Liebe Grüße

    Rene Martin

Hallo Herr Martin,

Und jetzt noch Lob:

Ihre Antwort auf meine Zuschrift find ich vorbildlich.  Außerdem freue ich mich immer, wenn jemand im Internet nützliche Informationen zur Verfügung stellt, und Ihre  Seiten sehen „recht gut gemacht“ aus (ich habe sie allerdings bisher noch nicht näher angesehen, daher nur eine Beurteilung des Eindrucks auf den ersten Blick). 

Liebe Grüße

Michael Richter

Früher war alles leichter. Ich auch.

War das früher besser? Zumindest ging das früher!

Vorn vorne: die Excel-Funktion HÄUFIGKEIT ist eine Matrixfunktion. Mit ihrer Hilfe (ebenso wie mit ZÄHLENWENN oder SUMMENPRODUKT) kann man die Häufigkeit von Daten in einer Liste bestimmen, beispielsweise, wie oft Notenwerte vorhanden sind. Dabei muss Die Funktion als Matrixfunktion verwendet werden, das heißt mit [Umschalt] + [Strg] + [Enter] beendet werden:

Das funktioniert bei exakt vorkommenden Werten, aber auch bei „Zwischenwerten“ – von – bis:

Man kann die Funktion aber auch als eine Funktion verwenden und runterziehen – dann werden die Werte kumuliert:

Nein – so konnte man das früher machen – vor den Spillfunktionen (den Arrayfunktionen SEQUENZ, SORTIEREN, EINDEUTIG, …) machen. Seit Microsoft diese Funktionen und diese Arbeitstechnik eingeführt hat, erzeugt HÄUFIGKEIT beim Markieren eines Wertes zwei Ergebnisse: Wie oft taucht dieser Wert auf und wie viele andere Werte sind vorhanden.

Und heute? – Man muss HÄUFIGKEIT mit einem @ entwerten, dann hat man wieder die gleiche Funktionalität wie früher:

Danke an Mourad Louha für diesen Hinweis.

Mein Elan-Empfang ist heute wieder schlecht. Und mein Tatenvolumen ist schon aufgebraucht.

Mal nichts Nervendes. Ein Gimmick zum Schmunzeln:

Vor Kurzem habe ich folgenden Text gefunden:

D1353 M1TT31LUNG Z31GT D1R, ZU W3LCH3N GRO554RT1G3N L315TUNG3N UN53R G3H1RN F43H1G 15T! 4M 4NF4NG W4R 35 51CH3R NOCH 5CHW3R, D45 ZU L353N, 483R
M1TTL3W31L3 K4NN5T DU D45 W4HR5CH31NL1ICH 5CHON G4NZ GUT L353N, OHN3 D455 35 D1CH W1RKL1CH 4N5TR3NGT. D45 L315T3T D31N G3H1RN M1T 531N3R 3NORM3N L3RNF43HIGKEIT. 8331NDRUCK3ND, OD3R? DU D4RF5T D45 G3RN3 KOP13R3N, W3NN DU 4UCH 4ND3R3 D4M1T 83G315T3RN W1LL5T

Kann man / kann ich selbst solche Texte produzieren? Mit Excel natürlich! Klar die Funktion WECHSELN hilft dabei:

Ich trage einen Text in die Zelle A3 ein. Die Funktion GROSS verwandelt ihn in Großbuchstaben. In den Zelle C1:G2 stehen die Werte E, I, S, B und A, die durch 3, 1, 5, 8 und 4 ersetzt werden. In C3 befindet sich die Formel

=WECHSELN(B3;C1;C2)

Rüberziehen. Und sich über das Ergebnis freuen:

4L5 GR3GOR 54M54 31N35 MORG3N5 4U5 UNRUH1G3N TRÄUM3N 3RW4CHT3, F4ND 3R 51CH 1N 531N3M 83TT ZU 31N3M UNG3H3U3R3N UNG3Z13F3R V3RW4ND3LT. 3R L4G 4UF 531N3M P4NZ3R4RT1G H4RT3N RÜCK3N UND 54H, W3NN 3R D3N KOPF 31N W3N1G HO8, 531N3N G3WÖL8T3N, 8R4UN3N, VON 8OG3NFÖRM1G3N V3R5T31FUNG3N G3T31LT3N 84UCH, 4UF D3553N HÖH3 51CH D13 83TTD3CK3, ZUM GÄNZL1CH3N N13D3RGL31T3N 83R31T, K4UM NOCH 3RH4LT3N KONNT3. 531N3 V13L3N, 1M V3RGL31CH ZU 531N3M 5ON5T1G3N UMF4NG KLÄGL1CH DÜNN3N 831N3 FL1MM3RT3N 1HM H1LFLO5 VOR D3N 4UG3N.

Hatte neulich Stress. Sagte cool: „Fass mich nicht an – ich kann Kibotu“. Muss ja keiner wissen, dass das Kinderbodenturnen heißt.

Hallo Rene, ich mache zurzeit Ihre Ecel Schulung Tipps & Tricks, bis jetzt bin ich begeistert, vielen Dank. Eventuell können Sie mir mit einer Formel helfen? Ich habe zwei Tabellenblätter und möchte Emailadresse, die in Blatt 1 in Spalten aufgelistet sind, zu Blatt 2 einen automatischen Bezu herstellen, sodass dort in einer Zelle die EMailadressen automatisch erscheinen, wenn ich sie in Blatt 1 eingetippt habe. Ich danke bereits für Ihre Hilfe.

Hallo Anna, wenn sich der Cursor auf der Zelle befindet, wo die Email-Adresse stehen soll, genügt ein „=“, dann ein Klick auf das erste Tabellenblatt und ein zweiter Klick auf die Zelle, in der sich die Mailadresse befindet. Achtung: Nicht zurückklicken! Sondern mit [Enter] bestätigen! Dann steht in der Zielzelle beispielsweise =Tabelle1!C3 Hilft das? Oder denke ich zu einfach? schöne Grüße Rene Martin

Lieber Rene,    danke für die supr schnelle Antwort.  Es ist leider doch ein wenig komplizierter, aber ich denk für Sie ebenfalls problemlos zu lösen. Ich füge ein Beispieldokument ein zum besseren Verständnis.  Aus Tabelle1 möchte ich automatische alle Email-adressen von Runde 1 in aufgelistet in ein Zelle im Tabellenblatt ‚Mailverteiler‘ auflisten.  Wie lautet die Formel des Zellbezugs?   Besten Dank für Ihre Hilfe.

Hallo Anna, hast du eine neueres Excel? Und dort die Funktion TEXTVERKETTEN? Damit geht es LG  ::  Rene

Es ist mit egal, wer dein Vater ist. Solange ich hier angle, läuft mir keiner übers Wasser!

Hallo Herr Martin,

Bei meiner täglichen Arbeit ist mir ein sehr gravierender Excel Fehler untergekommen der mich fast an Excel zweifeln lässt.

Ich bin sehr guter Excel Anwender wie VBA Programmierer.

Anbei ein Vorher/Nachher Bild und eine Excel Datei, die den Fehler beschreibt und zeigt.

Könnten Sie diesen Fehler vielleicht an Microsoft melden und in einem Ihrer Videos auf „LinkedIn“ kund tun?

Hallo Herr F.,

Vielen Dank für Ihren Hinweis. Kennen Sie meine Seite excel-nervt.de ? Dort sammle ich Bugs, Ungereimtheiten, merkwürdige Dinge, …

Ich habe das Phänomen mit/ohne Blattnamen bereits 2016 veröffentlicht:

Weitere interessante Dinge rund um Blattnamen finden Sie beispielsweise  auf

Aber kann diesen Bug gerne noch einmal veröffentlichen.

Andererseits – ich bin leider kein MVP und habe auch keinen direkten Kontakt zu Microsoft. Es gibt eine Seite, über die man Fehler melden kann – allerdings bin ich bei der Anmeldung gescheitert …

Hallo Herr Martin,

ich danke Ihnen für Ihre schnelle Antwort.

Nun fürchte ich, am nächsten Wochenende ihre komplette Web-Seite lesen zu müssen.

Mit einem freundlichen Händedruck

*lach*
Sie müssen nicht alles lesen!

Manche Faultiere fahren den Kreislauf so weit runter, dass sie aus Versehen sterben. – Als ich das gelesen habe, habe ich mich erstmal aufrecht hingesetzt.

Doof! In einer intelligenten Tabelle habe ich drei Spalten: ID, Vorname und Nachname. XVERWEIS kann einen Vornamen finden:

=XVERWEIS(G2;tbl_Kunden[Nr];tbl_Kunden[Vorname])

XVERWEIS kann ALLE Vornamen (als Matrixfunktion) zurückgeben:

=XVERWEIS(G2:G9;tbl_Kunden[Nr];tbl_Kunden[Vorname])

XVERWEIS kann Vor- und Nachnamen (hätten wir noch mehr Informationen – so auch diese) zurückgeben:

=XVERWEIS(G2;tbl_Kunden[Nr];tbl_Kunden[Vorname]:tbl_Kunden[Nachname])

Aber leider: beides geht nicht: mehrere Spalten zurückgeben von mehreren IDs:

Schade!

Beifahrer, die sagten „mach doch mal die üble Musik leiser“, fragten auch: „Wann darf ich wieder aus dem Kofferraum raus?“

Die ersten dunklen Wolken am Horizont der neuen Funktion XVERWEIS.

Wenn die Funktion nicht nur einen Wert aus einer Spalte, sondern aus mehreren Spalten zurückgibt, kann man die Funktion leider nicht mit einem Doppelklick herunterziehen.

Also:

=XVERWEIS(O2;tbl_Kunden3[Nr];tbl_Kunden3[Vorname]:tbl_Kunden3[Ort];“nicht gefunden“)

Ich komme aus einer Zeit, in der freie Knöchel ein Zeichen von falscher Hosengröße war. Man empfand es als peinlich!

Manchmal ist es irritierend. Aufgabe in der Excelschulung: Erhöhen Sie den Jahresbeitrag der Blue-Mitgleider um 5 Euro, der anderen Mitglieder um 10 Euro. Ein Teilnehmer arbeitet mit einer Hilfsspalte:

=WENN(J2=“Blue“;5;10)

Ich sage, dass dieses Vorgehen völlig okay sei, dass er aber auch direkt in der WENN-Funktion rechnen könne. Ich korrigiere und klicke auf die Zelle J2:

=WENN(J2=“Blue“;H25;10)

Der Teilnehmer schaut mich irritiert an. Ich füge ein Pluszeichen ein – und schon wird klar, wie gerechnet wurde:

Der zweite Teil muss natürlich noch eingefügt werden:

=WENN(J2=“Blue“;H2+5;H2+10)

Männer kennen nur zwei Empfindungen: hungrig und geil. Wenn du also einen Mann ohne Erektion siehst, dann mach ihm was zu Essen.

Irritierend, verwirrend und nicht konsequent!

In Excelschulungen erzähle ich immer, wenn ich den Assistenten „Daten / Text in Spalten“ vorstelle, dass es in Excel keine Tabulatoren gibt – man kann die Grundeinstellung (die beim Import von Textdateien verwendet wird) ruhig stehenlassen:

Ich werde eines Besseren belehrt: Wenn Sie ein Worddokument oder einen Mailtext aus Outlook nach Excel kopieren, werden die Tabstopps durch Spalten getrennt:

Ein Worddokument mit Tabulatoren
… in Excel

Wenn Sie dagegen Texte IN Zellen kopieren (das heißt: die Zelle mit [F2] editieren) und dann den kopierten Text einfügen, stehen Tabstopps in Zellen. Merkwürdigerweise sieht man sie nicht, wenn der Cursor AUF der Zelle steht – lediglich IN der Zelle werden sie angezeigt:

Der Assistent „Daten / Text in Spalten“ interpretiert die Tabstopps als Leerzeichen!?!

Daten / Text in Spalten mit Tabstopps
Daten / Text in Spalten mit Leerzeichen

Die Funktion CODE liefert den Wert 32 – den gleichen wie Leerzeichen!?!

Wenn man in den Ersetzen-Dialog einen Tabstopp hineinkopiert und durch ein Leerzeichen ersetzen lässt, funktioniert dies hervorragend.

Fazit: Finger weg von Tabstopps in Exceltabellen! Excel behandelt diese Zeichen nicht konsequent als eigene Zeichen – eine Unterscheidung durch Leerzeichen ist nicht an allen Stellen durchgeführt. Und wenn ein Anwender Texte in die Zellen eingefügt hat – dann bleibt nur noch der Ersetzen-Assistent oder die WECHSELN-Funktion.

Die Nasentropfen brennen – helfen aner wie Sau! – Das ist Türschlossenteiser!

Ein Dankeschön an Mourad Louha für seinen Vortrag beim Excelstammtisch Hessen zum Thema Internationalisierung bei Excel und worauf geachtet werden muss, wenn Dateien von einer Sprachregion in eine andere geschickt werden.

Amüsiert habe ich mich über seine Forlie „Kuriositäten“, in welcher er amüsante Dinge bei der Übersetzung einiger Funktionen zeigte: die längste Excelformel (AVRUND.GJELDENDE.MULTIPLUM.OPP.MATEMATISK im Norwegischen), SE.NON.DISP., die einen Punkt am Ende hat, CURRENCY – die Übersetzung von DOLLAR im Griechischen …

Ein Dankeschön an Mourad Louha, der mir seine Präsentzation zur Verfügung gestellt hat.

Schokolade erweitert dein Speckdrum.

Heute in der Excelschulung. Ich zeige die Datenüberprüfung. Eine Artikelliste. In die Mengenspalte darf nur eine ganze Zahl eingetragen werden. Ein Teilnehmer fragt, ob man die Eingabe auch so begrenzen kann, dass die Gesamtmenge < 100 sein muss. Klar – kein Problem: Benutzerdefiniert – =SUMME(D:D)<100

Klappt.

Und dann möchte der Teilnehmer, dass die Eingabe nur ganze Zahlen akzeptiert. Leider kann man keine zwei Datenüberprüfungen miteinander verketten.

Man müsste die Formel erweitern, beispielsweise so:

=UND(RUNDEN(D1;0)=D1;SUMME(D:D)<100)

Ich hatte schon so lange keinen Sex – ich weiß gar nciht mehr wie das geht. – Ist wie Fahrradfahren. – Brauch ich nen Helm?

Danke an die Einladung zum Excelstammtisch in Hessen. Auch in Frankfurt – wie bei uns in München – eine geballte Ladung an Wissen, Freude über Excel, Staunen und Spaß.
Klasse war es.
Danke auch an Andreas Thehos, der sich mit dem gleichen Problem rumgeschlagen hat wie ich gestern. Nur: die Aufgabenstellung war eine andere.
Gegeben sei eine Namensliste. Sie ist Basis für eine Pivottabelle. Es erstaunt, dass ein Name zwei Mal auftaucht. Und nein: es sind keine Leerzeichen hinter dem Namen.

Die Ursache: auch hier hat sich ein geschütztes Leerzeichen (ein non-breaking space) mit dem CODE 160 zwischen den Vornamen und Nachnamen geschummelt.
Wie passiert so etwas? Entweder wurden die Daten aus einer Internetseite entnommen – oder von einem Word-Dokument. DORT kann man mit der Tastenkombination [Umschalt] + [Strg] + [Leertaste].

Sex zu haben, hilft das Gedächtnis fit zu halten. Ein schönes Neues Jahr 2012!

Ich habe mich über Mourad Louhas Kommentar zu den maschinell übersetzten Hilfetexten von Microsoft.

Wer schauen möchte:

https://techcommunity.microsoft.com/t5/deutsch/excel-eindeutig-online-hilfe-nicht-%C3%BCbersetze-terme-und-fehler-in/idi-p/1091294?fbclid=IwAR1-eJ9AQqa6t36yZT3tZhVKbtuoIKiZg-oNm_MVbVQdIfrvrSsW1fCC-x8#M868

Er spricht mir aus der Seele.

Gib nicht Weihnachten die Schuld, dass du dick geworden bist. Du warst schon im August fett.

Ich habe drei Mal hinschauen müssen. Bis ich verstanden habe:

https://support.office.com/de-de/article/berechnen-einer-laufenden-summe-in-excel-1359bf89-180b-4771-b5b4-c6f6558549c5

Die Zeilennummerierung wurde eine Zeile zu hoch angesetzt – Zeile 1 darf nicht die Zeile neben den Köpfen sein, sondern muss eine Zeile tiefer beginnen. Dann würde es stimmen!

Je mehr Männer ich kennenlerne, desto netter finde ich Hunde

Ist Ihnen das schon aufgefallen? Sie möchten eine laufende Nummer eintragen und berechnen die erste Zelle mit:

=SUMME(A2:$A$2)

Und ziehen die Formel nach unten:

Die Folge:

=SUMME(A$2:$A3)

Das ist zwar richtig, aber doch erstaunlich. Beginnt man in der zweiten Zelle mit

=SUMME($A$2:A3)

wird die Formel nach unten (und auch noch oben) in dieser Form weitergezählt.

Nüchtern ins Bett? Was kommt als nächstes? Pünktlich zur Arbeit oder was?

Liebe Microsoftis: die neue Funktion EINDEUTIG ist – ebenso wie die anderen fünf neuen ARRAY-Funktionen klasse! Wirklich, ehrlich: super-spitzen Klasse! Brauchbar ohne Ende! Aber, bitte, bitte: übersetzt die Parameter ins Deutsche. Wie bei den anderen fünf Funktionen!

Das Gegenteil von „umfahren“ ist „umfahren“.

Boah – muss ich mich wieder ärgern!
Ich schaue mir gerade die beiden neuen Funktionen XVERWEIS und XVERGLEICH an (wirklich klasse!) und werfe einen Blick auf die Hilfeseite von Microsoft:

https://support.office.com/de-de/article/xvergleich-funktion-d966da31-7a6b-4a13-a1c6-5a33ed6a0312?NS=EXCEL&Version=90&SysLcid=1031&UiLcid=1031&AppVer=ZXL900&HelpId=xlmain11.chm60676&ui=de-DE&rs=de-DE&ad=DE

Das Beispiel 1 ist völlig falsch! Es wird keine Platzhaltersuche (4) verwendet, sondern eine exakte Übereinstimmung oder das nächst größere Element. Deshalb liefert 1 das Ergebnis 2! Der Parameterwert 4 würde #NV liefern, weil Gra? nicht vorhanden ist. Nur Gra?? oder Gra*.
Boah!
Das Boah geht weiter: Beispiel 2: Der Satz „Beachten Sie, dass diese Methode erfordert, dass Ihre Daten in absteigender Reihenfolge sortiert sind.“ Nein – das erfordert sie nicht – es wird der Wert darüber ODER darunter zurückgegeben.
Beispiel 3 ist korrekt; bei Beispiel 4 hätte ich mir eine vollständige Übersetzung gewünscht:
=XVERGLEICH(4;{5;4;3;2;1})
Nun ja!

Nicht vergessen: heute werden die Waagen um fünf Kilo zurückgestellt – auf Weihnachtszeit!

manchmal freue ich mich auch über Excel. Kennt ihr das? Probleme, die nicht lösbar scheinen, finden doch eine Lösung.

Kennt ihr die neuen Array-Funktionen in Excel 365? Beispielsweise ZUFALLSMATRIX:

=ZUFALLSMATRIX(10;1;1;10;WAHR)

generiert zehn zufällige ganze Zahlen zwischen einschließlich 1 und 10. Okay.

=SUMME(ZUFALLSMATRIX(10;1;1;10;WAHR))

summiert zehn zufällige Zahlen und liefert ein Ergebnis zwischen 10 und 100. Okay.

Vor einer Weile wollte ein Kunde aus einer Liste von zirka 100.000 Werten 100 Werte zufällig herausgreifen und von diesen den Durchschnitt berechnen. Mit einer Hilfsspalte ist das kein Problem. Jedoch scheinen die Matrixformeln zu versagen, weil {…ZUFALLSBEREICH …} 100 Mal die gleiche Zufallszahl liefert und nicht 100 verschiedene. Die Lösung für dieses Problem liefert ZUFALLSMATRIX:

In A2:A100000 stehen Zahlen. Die Funktion

=MITTELWERT(BEREICH.VERSCHIEBEN($A$1;ZUFALLSMATRIX(100;1;1;100000;WAHR);0))

berechnet einen Durchschnitt für diese 100 zufällig gefundenen Werte. [F9] zum Neuberechnen liefert einen anderen Wert. Markiert man einen Teil der Formel, dann zeigt [F9], dass tatsächlich zufällige Werte ermittelt wurden. Die Zahl 100 kann ausgelagert und erhöht werden. Je mehr man sich 100000 nähert, umso mehr nähert sich der Zufalls-Mittelwert dem echten Mittelwert.

Und wer sich nun fragt: „wer braucht denn so etwas?“ – Das Teilchen heißt Monte-Carlo-Simulation und wird in Mathematik, Physik, Finanzwesen, … seit über 60 Jahren angewandt. Weite Infos – beispielsweise Wikipedia.

Und ich bin begeistert – Excel rechnet so wie ich will!

Echte Männer fahren Traktor

Mit Lorenz Hölscher (http://www.software-dozent.de/) haben wir für unsere Exceltage (www.munich-office-group.de) 2019 einen hervorragenden Dozenten gefunden. Er hat über Themen wie sichere Datenqualität und Dateneingabe, Verbesserungen im VBA-Code referiert. Und er stellte die neuen Array-Funktionen vor, die in Excel nun Einzug gefunden haben.

Auch er kann sich manchmal freche Bemerkungen nicht verkneifen, wenn er fragt, warum in Excel die Funktion TEXT in VBA Format genannt wurde – warum HEUTE() in Access Datum() heißt … Und er machte Witze über Praktikanten, die so etwas implementiert haben – solche Witze machen seine Vorträge nicht nur lehrreich, sondern auch amüsant.

Das Auge liest mit!

Die Frage ist interessant: Aus einer Geburtstagsliste sollen all diejenigen angezeigt werden, die in dieser (laufenden Kalender-)Woche Geburtstag haben. Eine kleine Fingerübung, oder:

Zuerst wird das Geburtsdatum in ein Datum des aktuellen Jahres „transformiert“. Dann wird von diesem Datum und vom aktuellen Tag die ISOKALENDERWOCHE berechnet. Und schließlich beides miteinander verglichen:

Hallo Wolfgang,

und so geht es:

=WENN(ISOKALENDERWOCHE(HEUTE())=ISOKALENDERWOCHE(DATUM(JAHR(HEUTE());MONAT(K2);TAG(K2)));“x“;““)

Schau dir mal die Schritt in der angefügten Tabelle an

Viel Spaß mit KW und Geburtstag

Rene

Lieber René,

vielen Dank. Ich hatte es gleich gestern Abend noch nachgetüfftelt und bin zum Ergebnis gekommen, allerdings hatte ich das Datum anders umgewandelt.

Da sah dann so aus: =WENN(ISOKALENDERWOCHE(TAG(A1)&“.“&MONAT(A1)&“.“&JAHR(HEUTE()))=ISOKALENDERWOCHE(HEUTE());“Happy Birthday“;““)
Viele Grüße
Wolfgang

Ist auch richtig, Wolfgang – meine Lösung ist natürlich besser *lach*

Im Ernst: ich mag es nicht, wenn du ein Datum (intern eine Zahl) in einen Text umwandelst und diese implizit wieder in ein Datum konvertieren lässt. Bei sehr vielen Datensätzen dauert es länger als meine Lösung, die ein Datum als Datum lässt.

Liebe Grüße Rene

Gott ist alleinerziehend

Etwas verblüfft war ich in der letzten Excelschulung. Ich löse mit den Teilnehmern folgendes Problem: Es werden in zwei verschiedenen Zellen zwei Monate ausgewählt und die Kosten von – bis werden berechnet. BERICH.VERSCHIEBEN eignet sich hervorragend zur Lösung dieses Problems.

Meine Lösung:

BEREICH.VERSCHIEBEN:

Beginne bei A1.

Suche E1 im Datumsbereich mit der Funktion VERGLEICH und wandere so viele Zeilen nach unten.

Wandere eine Spalte nach rechts.

Ermittle die Höhe des aufzuspannenden Bereichs als Differenz beider Werte Ende – Anfang, die mit VERGLEICH berechnet werden.

Die Breite des Bereichs ist eine Spalte.

Klappt. Ein Teilnehmer präsentiert eine andere Lösung, die er parallel entwickelte:

SUMME(BEREICH.VERSCHIEBEN(A1;VERGLEICH();1:BEREICH.VERSCHIEBEN(A1;VERGLEICH();1))

Mich irritiert der Doppelpunkt. Dann wird mir klar, wie der Teilnehmer gedacht und wie die Formel gearbeitet hat:

Mit =C3 wird eine Referenz auf die Zelle C3 gesetzt. Diese Formel liefert den Wert der Zelle C3. Also steht „C3“ für zweierlei: die Zelle C3 als Objekt, als Bezug, aber auch der Inhalt der Zelle C3.

Und genau so arbeitet seine Formel – Während „meine“ Funktion BEREICH.VERSCHIEBEN den Wert der Zelle (beziehungsweise die Werte der Zellen) zurückgibt, setzt er einen Bezug auf die erste und die letzte Zelle und spannt zwischen ihnen einen Bereich auf, dessen Werte summiert werden.

Verblüffend und clever!

Ich koche auch nur mit Wasser, aber ich würze richtig nach.

In der Schulung zeige ich eine Funktion (INDEX). Ich zeige sie zuerst mit dem Funktionsassistenten. Der erste Parameter verlangt eine Matrix, die sich auf dem anderen Tabellenblatt befinden. Die anderen beiden Parameter befinden sich auf dem gleichen Tabellenblatt wie die Funktion – hier: D2 und E2. Kein Problem:

Anschließend zeige ich das Gleiche, indem ich die Funktion tippe. Erster Parameter: anderes Tabellenblatt. Zweiter und dritter Parameter – ich muss zurück zum ursprünglichen Tabellenblatt. Excel notiert den Namen des Tabellenblattes (hier: Tabelle2!). „Ärgerlich“ findet ein Teilnehmer. Ich gebe ihm recht …

Als Baby wirst du für ein Bäuerchen gelobt; dann perfektionierst du es jahrelang und dann bekommst du Schelte!

Hallo René,

Frage am Rande:

Den Mittelwert Teilnahme über alle Spalten Teilnahme, die >0 bekomme ich wie am schicksten? Mittelwertwenn geht nur über einen zusammenhängenden Bereich, zählenwenn auch,…

Am Ende ist die Tabelle so:

Hallo Florian,

ja, das ist richtig: ZÄHLENWENN, MITTELWERTWENN kann keine getrennten Bereiche verarbeiten. Der Grund:

Wie soll man ZÄHLENWENN(A2:A5;C2:C5;“>0“)

verstehen?

Also muss du den MITTELWERT „nachbauen“. Mittelwert ist ja Summe/Anzahl

Also beispielsweise so:

=SUMME(A2:A6;D2:D6;G2:G6)/(ZÄHLENWENN(A2:A6;“>0″)+ZÄHLENWENN(D2:D6;“>0″)+ZÄHLENWENN(G2:G6;“>0″))

die Spalten A, D und G

Oder:

{=SUMME(A2:A6;D2:D6;G2:G6)/SUMME((A2:A6>0)*1;(D2:D6>0)*1;(G2:G6>0)*1)}

als Matrixfunktion mit Umschalt+Strg+Enter beendet.

Zu Erklärung der zweiten Funktion: A2:A6>0 liefert WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR

diese Wahrheitswert kann ich jedoch nicht summieren. Multipliziere ich sie mit 1, so erhalte ich

1;1;0;1;1;1

Und das kann ich summieren.

Es gibt noch weitere Ansätze.

Liebe Grüße

Rene u

Jeder Topf hat einen Deckel! Aber ich bin da, glaub ich, ein Wok

Lieber Herr Martin,

Ein Studienkollege meines Sohnes hat auf seinem PC Excel als Programm nicht und benutzte Excel als Online-Version (one-drive). Er wollte dort eine Tabelle transportieren. Er findet aber dort nicht die entsprechende Funktion.

In Excel geht das ja über Einfügen > Inhalte einfügen à Transponieren oder über den rechten Mausklick. Aber in diesem Online-Excel scheint es diese Funktion nicht zu geben. Oder gibt es doch eine Möglichkeit einer Transponierung auf dieser Excel-Website? Vorausgesetzt, daß Sie diese mal benutzt haben?

Hallo Herr F.,

Ich habe nachgeschaut: Excel online hat viele Funktionen nicht – beispielsweise transponieren. Auch die Funktion MTRANS klappt nicht, weil Excel online keine Matrixfunktionen unterstützt.

Man kann die Tabelle natürlich mit Formeln transponieren. Beispielsweise mit:

=BEREICH.VERSCHIEBEN($C$1;SPALTE(A1);ZEILE(A1))

Es funktioniert auch mit INDIREKT

Wenn man immer nur tut, was sich gehört, verpasst man den ganzen Spaß.

In Excel 2016 wurde das ausschließliche ODER (die Kontravalenz oder antivalente Disjunktion) eingeführt. In den seltensten kaufmännischen, mathematischen oder statistischen Berechnung wird diese Funktion, die im Bereich der Schaltalgebra in der Elektrotechnik und in der Verschlüsselungstheorie, eine große Rolle spielt, benötigt.

Zur Erläuterung: die Funktion ODER (eigentlich ein Konjunktor) bedeutet: das eine oder das andere oder beide. Umgangssprachlich häufig mit und/oder bezeichnet.

XODER meint dagegen ein ausschließliches ODER – also: entweder das eine oder das andere. Aber nicht beide gleichzeitig. Eine Verknüpfungstabelle erklärt dies:

WAHR XOR WAHR = FALSCH

WAHR XOR FALSCH = FALSCH XOR WAHR = WAHR

FALSCH XOR FALSCH = FALSCH

XOR ist also kommutativ. Und auch assoziativ:

WAHR XOR (WAHR XOR WAHR) = WAHR XOR FALSCH = WAHR

analog:

(WAHR XOR WAHR) XOR WAHR = FALSCH XOR WAHR = WAHR

Das erstaunt: drei mit XOR verknüpfte WAHR-Werte liefern also WAHR (und nicht FALSCH, wie man vielleicht vermuten würde)

Ebenso liefern vier mit XOR verknüpfte WAHR-Werte FALSCH, dagegen ist:

WAHR XOR WAHR XOR WAHR XOR FALSCH = WAHR

(die Reihenfolge ist vertauschbar)

Und Excel? Excel berechnet dies korrekt:

ABER: in der Microsoft-Hilfe ist das falsch erklärt
https://support.office.com/de-de/article/xoder-funktion-1548d4c2-5e47-4f77-9a92-0533bba14f37

Das ist nicht korrekt!

=XODER(3>0;2<9) liefert FALSCH. Nicht WAHR, wie auf der Hilfeseite zu lesen ist. Und der Satz:

„Da eine der beiden Prüfungen „Wahr“ ergibt, wird WAHR zurückgegeben.“

ist so auch nicht korrekt!

Der Satz

„Das Ergebnis von XODER ist gleich WAHR, wenn die Anzahl von Eingaben mit dem Ergebnis WAHR ungerade ist, und gleich FALSCH, wenn die Anzahl von Eingaben mit dem Ergebnis WAHR gerade ist.“

ist korrekt.

Danke an Paul für den Hinweis!

Hab nie Angst vor dem Altwerden. Du kannst immer noch viel Unsinn machen. Nur langsamer.

Ich weiß – es gibt Schlimmeres in Excel. Trotzdem musste ich schmunzeln: Die Cubefunktion CUBEMENGE verwendet als vorletzten Parameter „Sortier_reihenfolge“. Lustig geschrieben: Ich hätte ihn „Sortierreihenfolge“ genannt – der Unterstrich ist albern. Wahrscheinlich eine Analogie zum letzten Parameter „Sortieren_nach“.

Ich spüre das Tier in mir. Es ist ein kleines Faultier.

Manchmal verblüfft mich Excel. Beziehungsweise Phänomene, die ich noch nie bemerkt habe.

Man erstelle eine Funktion, die einen leeren Wert zurückgibt, beispielsweise

=WENN(A1=“Januar“;1;““)

Man markiere die Spalte mit den Formeln, kopiere sie und fügen sie als Werte ein.

Mit [Strg] + [↓] springt Excel zu letzten (leeren?) Zelle. Stoppt nicht bei den Zahlen. Überspringt die vermeintlich leeren Zellen. Man sieht zwar nichts, aber die Funktion =ISTLEER liefert den Wert FALSCH. Diese Zellen sind nicht leer.

Headlines nerven

In der letzten Excelschulung.

Wir verknüpfen zwei Arbeitsblätter. Ich ziehe den Bezug =ErstesBlatt!ErsteZelle herunter. Da ich nicht weiß wie weit, schieße ich weit übers Ziel hinaus. Das Ergebnis sind Nullen.

Ich frage die Teilnehmer, wie man die Nullen unterdrücken kann. Ich erhalte die Antwort: „in den Optionen“. Richtig: Dort kann man in Datei / Optionen / Erweitert in der Kategorie „Optionen für dieses Arbeitsblatt anzeigen“ die Einstellung „In Zellen mit Nullwert eine Null anzeigen“ ausschalten.

Ich erkläre, dass diese Option nicht immer geschickt ist, denn so werden SÄMTLICHE Nullen unterdrückt. Ich möchte es gerne lokal unterbinden.

Wenn ich solche Fragen stelle, erwarte ich eine Antwort. Erwarte ich WENN(=““. Ein Teilnehmer meldet sich und antwortet: „Mit der Funktion ISTLEER“. Ich bin verblüfft. Damit habe ich nicht gerechnet. Stimmt – ISTLEER prüft das Gleiche wie =““. Wirklich? Wir testen es und stellen natürlich kleine Unterschiede fest. Wenn in der Zelle ein Fehler ist. Oder beispielsweise ein leerer String:

=““

Wir lernen – was auf den ersten Blick gleich aussieht, muss nicht unbedingt gleich sein …

Reality is disappointing

Wenn man in (intelligenten / dynamischen / formatierten) Tabellen eine Ergebniszeile einfügt, kann man dort die aggregierenden Funktionen SUMME, ANZAHL, MAX, … verwenden. Hierfür benutzt Excel die Funktion TEILERGEBNIS, die ausgeblendete Zeilen übergeht:

Warum nicht die Funktion AGGREGAT, fragt man sich und reibt verwundert die Augen. Die Antwort ist einfach: Die Tabellen wurden mit Excel 2007 eingeführt. Damals gab es schon TEILERGEBNIS. Die Funktion AGGREGAT hielt allerdings erst in Excel 2010 Einzug in die Tabellenkalkulation. Und wenn etwas einmal drin ist, dann wird es so schnell nicht wieder geändert.

Ich mag Nashörner. Die sind wie Einhörner. Nur fetter.

Auflösung nach Zinssatz

=======

Die finanzmathematische Funktion zur Auflösung nach dem Zinssatz i lautet:

ZSATZINVEST(Zzr;Bw;Zw)

 

K0  = 10.000

K10 = 20.000

= 10 Jahre

 

Gesucht:

= ZSATZINVEST (10;10000;20000) = 7,18%

 

Hallo Christian,

Sag mal: ist das korrekt: löst die Funktion ZSATZINVEST die Funktion RMZ nach dem Zinssatz auf? (S. 5) Tut das nicht die Funktion Zins?

Hallo Rene,

Hier muss man unterscheiden, ZSATZINVEST ist für eine einmalige Anlage, Laufzeit, BW und Endkapital sind bekannt.

ZINS liefert Ergebnisse bei periodische Zahlungen z. B. den effektiven Jahreszins im Kreditbereich. Hier ist Zinssatz, (mtl.) Rate, Kreditbetrag und Laufzeit bekannt.

####

Wieder etwas gelernt!

Die Eselsbrücke ist die ideale Verbindung zwischen zwei Gedächtnislücken

Traurig fragte mich eine Teilnehmerin der Excelschulung, was sie denn machen könne. Sie erhält von einem System regelmäßig Listen mit einer leeren Spalte. Jedoch befinden sich einige Zwischenüberschriften in dieser Liste.

Ihre Aufgabe ist es, eine fortlaufende Nummerierung zu erstellen, bei der die Zwischenüberschriften übergangen werden. Mein Vorschlag war folgender: Schreiben Sie in die erste Zelle eine 1. Markieren Sie ab der zweiten Zelle bis zum Ende der Liste. Wählen Sie nur die sichtbaren Zellen aus: Start / Suchen und Auswählen / Inhalte auswählen oder Tastenkombination [ALT] + [;] (also: [ALT] + [Umschalt] + [,].

Schreiben Sie in die erste Zelle die Formel

=MAX($H$5:H5)+1

und beenden die Eingabe mit [STRG] + [Enter]. Damit sie für alle Zellen übernommen wird.

Diese Formel kann man anschließend mit Kopieren / Inhalte einfügen: Werte in Zahlen umwandeln.

Sie war glücklich.

Dumm sein ist nicht leicht. Ich habe viel Konkurrenz.

Hallo René,

 

ich bin auf deine Webseite gestoßen und hoffe, dass es ok ist, eine E-Mail zu schreiben.

ich habe folgendes Problem: Mir steht eine Exceltabelle mit einer Liste von verschiedenen Kostenarten in Spalte A zur Verfügung, z.B. „MasterCard, Adobe“, „Klipfolio 30€“ und „Juni Klipfolio VisaCard“. Diese möchte ich in die Kategorien „Adobe“ und „Klipfolio“ einteilen (nur als Beispiel, in Wirklichkeit sind es natürlich wesentlich mehr Kategorien) und suche deshalb nach genau diesen Stichwörtern im Text. Wird eines dieser Stichwörter gefunden, soll es entsprechend in Spalte B geschrieben werden, so dass ich das ganze später bequem in einer Pivottabelle zusammenfassen kann.

 

                                                  B

Kostenart                                      Stichwort

MasterCard, Adobe                        Adobe

Klipfolio 30€                                  Klipfolio

Juni Klipfolio VisaCard                    Klipfolio

Insgesamt handelt es sich um ca. 1.000 Datensätze und rund 50 verschiedene Stichwörter. Das „Problem“ ist zur Zeit über eine lange verschachtelete Wenn-Funktion gelöst. Ich dachte aber, es gäbe über VBA vllt eine schnellere und unkompliziertere Lösung. Zur Zeit bin ich so weit, dass ich nach einem bestimmten Stichwort (egal wo im Text es steht) suchen lassen kann.

Sub a()

Dim Rng As Range
Dim wks As Worksheet

Set wks = Worksheets(„data“)

Set Rng = wks.Range(wks.Cells(1, 1), wks.Cells(1000, 1)).Find(what:=“Adobe“, lookat:=xlPart, LookIn:=xlValues, MatchCase:=True)

If Not Rng Is Nothing Then Rng.Select

End Sub

Ich komme allerdings jetzt nicht mehr weiter bzw. bin mir auch nicht sicher, ob das überhaupt ein guter Ansatz ist.

Kannst du mir weiterhelfen?

Vielen Dank im Voraus,

#####

Hallo J.,

ich würde das mit einem SVERWEIS lösen. Oder mit der Funktion INDEX und VERGLEICH. Kannst du?

Liebe Grüße

Rene

#####

Hallo René,
vielen Dank für deine super schnelle Antwort.
Ja, beide Funktionen sind mir bekannt. Da ich ja aber ca. 50 verschiedene Suchkriterien habe, würde ja auch da wieder eine extrem lange und verschachtelte Formel in der Spalte B stehen- oder übersehe ich etwas? Aus diesem Grund dachte ich an eine VBA Abfrage. Diese wäre vllt ebenfalls lang und verschachtelt- würde ja aber quasi „im Hintergrund“ laufen- andere Nutzer müssten dann ja nur noch monatlich in der Kostenübersicht das Makro starten. Siehst du bzgl VBA auch eine Möglichkeit?
Danke und LG J.
#####
Wer nervt mehr? Excel oder die Menschen, die Excel benutzen?

Es ist gut, dass nicht jeder ein Smartphone hat. Wir brauchen Menschen, die hupen, wenn es grün wird.

Hallo Herr Martin,

ich habe eine Art Kalender, also beginnend mit dem 01.01.2018, endend mit
dem 31.12.2018.
Bei der Aufziehung des Datums lasse ich das Wochenende weg.
In einer Extra-Spalte soll vor jedem Montag die Kalenderwochenzahl stehen.
Wenn ich diese Funktion anwende, wird die Kalenderwochenzahl auch vor den
Tagen Dienstag bis Freitag eingefügt.
Gibt es eine Möglichkeit, z. B. über die Bedingte Formatierung, daß die
Kalenderwochenzahl nur neben dem Montag gezeigt wird?
Herzliche Grüße

####

Hallo Herr F.,
ich würde es mit einer WENN-Funktion lösen. Beispielsweise so (wenn in B1) das Datum steht:
=WENN(WOCHENTAG(B1;2)=1;ISOKALENDERWOCHE(B1);„“)
schöne Grüße
Rene Martin

####

Hallo Herr Martin,

das ging aber recht schnell mit Ihrer Antwort. Vielen Dank.

Ich habe etwas herum experimentiert und diese Lösung benutzt.

Einzig beim Übergang von 2018 zu 2019 scheint dies nicht zu funktionieren, denn der 31.12.2018 ist zwar noch zum alten Jahr gehörig, müßte aber dennoch mit der 1 beginnen, macht es aber nicht:

Herzliche Grüße

####

Hallo Herr F.,
ich bin nicht sicher, ob in Excel 2010 bereits der Parameter 21 in der Funktion KALENDERWOCHE vorhanden war. Schauen Sie mal bitte nach – ich habe hier kein Excel 2010. Wenn das nicht der Fall ist, müssen Sie die Funktion ISOKALENDERWOCHE nachbauen. Anleitungen finden Sie im Internet.
schöne Grüße
Rene Martin

####

Hallo Herr Martin,
vielen Dank für Ihre letzte Antwort. Sie haben natürlich recht: mit ISOKALENDERWOCHE läßt sich das Problem beheben.
Wie ist es aber, wenn man Excel 2010 benutzt. Da gibt es diese Funktion nicht. Wie ließe ich hier das Problem lösen, also nur daß vor dem Montag die Kalenderwochenzahl steht?
Herzliche Grüße

####

Hallo Herr Martin,

den Parameter 21 hat Excel 2010 nicht.

Mal sehen, was ich im Internet finde, bisher habe ich nichts gefunden.

Dennoch vielen Dank.

Herzliche Grüße

####

Hallo Herr Martin,

ich muß mich korrigieren. Der Parameter 21 ist in Excel 2010 vorhanden.

Ich habe wie folgt die Funktion geschrieben:

=WENN(WOCHENTAG(B1;2)=1;KALENDERWOCHE(C1;21);„“)

Und es funktioniert auch der Wechsel von der 52. KW 2018 zur KW 1 2019.

Herzliche Grüße

####

Wer nervt mehr? Excel oder Menschen?

Ok Leute, ich war gerade kurz motiviert. Aber keine Sorge, hab’s wieder in den Griff bekommen.

Schade.

Der Cursor befindet sich unterhalb einer Liste.

Eine Formel kann erstellt werden indem man mit der Tastenkombination [⇑] + [Strg] + [↑] nach oben markiert.

Mit [Strg] + [Rückschritt/Backspace] gelangt man wieder zu der Zelle zurück, in der man gearbeitet hat:

Leider funktioniert dieses „Zurückspringen“ nicht blattübergreifend. Schade!

 

Excel – Zahlen / Formeln / rechnen

Mein neues Excel-Buch – 544 Seiten zum Thema Zahlen, rechnen, Formeln, Funktionen, verknüpfen, knobeln, denken, Probleme in und um Excel lösen. Mein gesamtes Wissen (okay – ein großer Teil davon). Von Erklärungen SVERWEIS, verschachtelte WENN-Funktionen und Datumsberechnungen über die Funktionen der Kategorie Nachschlagen & Verweisen, Textfunktionen und Statistik bis hin zum numerischen Integrieren, Iterationen und Determinatenberechnungen. ich denke – da ist für jeden etwas dabei. Erhältlich in jedem Buchladen: Excel: Zahlen rechnen Formeln: Formeln, Berechnungen und Verknüpfungen in Excel Taschenbuch – 10. Januar 2018 von René Martin (Autor) ISBN-10: 3746064872 ISBN-13: 978-3746064871

Schützt die Bäume – esst mehr Biber!

Manchmal stolpert man (ich) über seine eigenen Füße.

Heute. Excelschulung. Ich erkläre die Funktion INDEX. Ich verweise mit INDEX vom zweiten Tabellenblatt auf das erste Blatt „Diäten“. Ich ziehe mit der Maus den Bereich auf. Bei den ersten beiden Spalten lautet die Formel:

=INDEX(Diäten!A1:B24

füge ich die Spalte C hinzu lautet die Formel jedoch:

=INDEX(Diäten

Schließe ich Spalte D ein finde ich nun folgende Formel:

=INDEX(Diäten!A1:D24

Ich grüble eine Weile über das seltsame Phänomen, bis ich dahinter komme, dass ich ja den Bereich A1:C24 benannt habe: er heißt „Diäten“.

Man sollte nicht Tabellenblätter so beschriften wie die Namen, die in der Datei vergeben wurden. Das führt zu Chaos!

Nach den bisher bekannten physikalischen Gesetzen der Aerodynamik ist es unmöglich, dass die Hummel fliegt. Die Hummel weiß das aber nicht. Sie fliegt einfach!

Excel-Schulung. Eine Teilnehmerin präsentiert eine Liste. In einer Spalte stehen Geldbeträge. Jeweils unter einer bestimmten Gruppe befinden sich Zwischensummen. Manche Summenbeträge sind positiv, manche negativ. Sie möchte gerne nur die positiven Zwischensummen und die dazugehörigen darüberstehenden Werte sehen. Das funktioniert mit einem einfachen Filter nicht. Man muss mit einer Hilfsspalte arbeiten. Dort wird überprüft, ob die Spalte eine Zwischenspalte ist – ob der Wert positiv oder negativ ist. Dann wird es als solches gekennzeichnet. Falls nicht, wird der untenstehende Wert übernommen:

=WENN(UND(B2=““;D2<=0);“neg“;WENN(UND(B2=““;D2>0);“pos“;H3))

Jede Formel bezieht sich auf die darunter stehende Zelle. Excel rechnet von links nach rechts, von oben nach unten. Eigentlich muss der letzte, unterste Wert zuerst berechnet werden, dann den zweiten Wert von unten. Auf diesen bezieht sich die Formel darüber … und so weiter bis zur zweiten Zeile.

Wie rechnet Excel? Iterativ? Wahrscheinlich!

Die Hummel besitzt 0,7 qcm Flügelfläche bei 1,2 Gramm Gewicht.
Nach den bisher bekannten physikalischen Gesetzen der Aerodynamik, sowie unserem physikalischen Verständnis ist es unmöglich, bei diesem Verhältnis zu fliegen!

Die Hummel weiß das aber alles nicht. Sie fliegt einfach!

Vielleicht weiß Excel das auch nicht – und rechnet einfach.

Ein Text ist ein Text ist ein Text

Was passieren kann, wenn man einen Text als Text formatiert – darauf habe ich schon hingewiesen. Auch was passiert, wenn man einen (langen) Text als Buchhaltung formatiert:

Amüsant ist dagegen auch folgendes Phänomen: Wenn man einen Text als Datum, Prozentwert oder Währung formatiert:

und dann mit der Funktion LÄNGE weiter rechnet – allerdings mit mindestens zwei Rechenoperationen (beispielsweise LÄNGE – 1 oder LÄNGE x 1), dann wird das Zahlenformat übernommen:

Erstaunlicherweise: FINDEN und SUCHEN liefern auch Zahlen – sie übernehmen jedoch nicht das Zahlenformat.

Ich weiß, du hast recht – aber meine Meinung gefällt mir besser.

Hallo Herr Martin,

ich befinde mich derzeit in einem Excel-Grundkurs und bin bei einer „verschachtelten Wenn-Funktion“ auf folgende Problematik gestoßen:
Excel rechnet nicht weiter, sobald der erste „Wahr“wert erreicht ist, was zu logischen Fehlern führt, sofern die Abfragewerte aufsteigend abgefragt werden.
Der „Sonst“Wert wird allerdings korrekt ausgegeben.
In meinem Beispiel habe ich dann die Abfragewerte 25% und 20% umgekehrt und es hat wie geplant funktioniert.
Wo liegt mein Fehler?
Haben Sie eventuell eine Idee zur Vereinfachung der Funktion?
Im voraus vielen Dank für Ihre Hilfe.
Mit freundlichen Grüßen
Jan

Hallo Herr S.,

Sie haben recht: Excel arbeitet Wenn-Funktionen baumartig ab. Also:

Wenn > 80% dann

wenn > 75% dann

sonst: Rest <= 75%

Sie können es aber auch „umbauen“:

wenn <= 75% dann

wenn <= 80% dann

Rest: sonst > 80%

Tipp: ich zeichne manchmal so eine Baumstruktur auf, um es besser zu verstehen.

Tipp 2: schreiben Sie 2 und 5 und nicht „2“ oder „5“. Sie möchten ja mit dem Zahle weiterrechnen. Sie können übrigens auch 2% und 5% verwenden

Hallo Herr Martin,

vielen dank für die schnelle Antwort.

Das ganze ist ja ganz schön verwirrend, gibt es dann noch eine andere Funktion die das Ziel einfacher erreicht?

Schöne Grüße

Jan

Hallo Herr S.

schauen Sie sich einmal die Funktion SVWEIS an – dort werden mehrere Fälle auf einer Ebene abgearbeitet.

Bitte bevorraten Sie sich, dieser Artikel ist nicht ständig im Sortiment…

Wer in Excel gerne mit Namen arbeitet, weiß die Funktionstaste [F3] zu schätzen. Man kann den Dialog, der die Namen anzeigt aus der bedingten Formatierung, der Datenüberprüfung, den Diagrammen, … heraus aufrufen.

Leider nicht aus den Steuerelementen heraus, wenn dort im Formatierungsdialog ein Wert in eine Zelle geschrieben wird, die einen Namen hat.

F3 geht leider nicht

Danke an Tony de Jonker für diesen Hinweis

Nur weil du nicht paranoid bist, heißt es noch lange nicht, dass sie hier nicht hinter dir her sind…

Einfach blöde. Ich habe einen Verweis von einer Excelmappe auf eine andere:

vorher …

Diese Mappe wird unter einem anderen Namen gespeichert. Der Verweis wird nun auf die neue Datei gesetzt; bleibt nicht mehr bei der alten:

… nachher

Leider stellt Excel keinen Schalter zur Verfügung, diesen Verweis NICHT mitzunehmen.

Für eine vernünftige Headline fehlt mir die richtige Kreativitätstechnik.

Schon irgendwie doof!

In einer Arbeitsmappe befindet sich ein Tabellenblatt – nennen wir es „Jena“. In dieser Mappe befindet sich ein zweites Tabellenblatt, das Bezug auf das erste Blatt nimmt. Die Formeln können dann beispielsweise so aussehen:

=Jena!H4

In einer zweiten Arbeitsmappe gibt es auch ein Tabellenblatt „Jena“. Wird nun das zweite Blatt aus der ersten Daten in die zweite kopiert oder werden die Zellen in die zweite Datei kopiert, so wird der Bezug auf die erste Datei „mitgenommen“ und nicht auf die zweite Datei:

Ärgerlich!

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:

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!

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)

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 …

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.

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.

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!

 

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

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!

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

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

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.

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?

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)