Office: Simulation =WENNNULL() und =WENNNULLODERNEGATIV()

Helfe beim Thema Simulation =WENNNULL() und =WENNNULLODERNEGATIV() in Microsoft Excel Tutorials um das Problem gemeinsam zu lösen; Formel-Simulation: =WENNNULL() und =WENNNULLODERNEGATIV() ohne VBA Einleitung =WENNFEHLER() ist eine der wichtigen neuen Funktionen in Excel 2007.... Dieses Thema im Forum "Microsoft Excel Tutorials" wurde erstellt von lupo1, 12. Januar 2017.

  1. lupo1
    lupo1 Tutorial Guru

    Simulation =WENNNULL() und =WENNNULLODERNEGATIV()


    Formel-Simulation: =WENNNULL() und =WENNNULLODERNEGATIV() ohne VBA

    Einleitung

    =WENNFEHLER() ist eine der wichtigen neuen Funktionen in Excel 2007. Damit gelingt es erstmals, Fehlerbehandlungen ohne doppelte Erwähnung (und Berechnung) eines Ausdrucks vorzunehmen. Das war bei älteren Versionen vor allem bei langen Formeln ärgerlich.

    Mit zwei unabhängigen Tricks kann man das nun ausweiten auf Nullwerte und Null-oder-Negativ-Werte.

    =WENNNULL()

    Nullwerte, die optisch stören, blendet man bekanntlich mit 0;-0; oder Optionen/Nullwerte nicht anzeigen aus.

    Daher ist eine Behandlung mit =WENN(Ausdruck<>0;Ausdruck;"") gar nicht nötig. Sie ist aus zwei Gründen sogar schlecht:

    1) Der Ausdruck wird zweimal erwähnt
    2) Bei Null entsteht ein leerer Textwert mit den damit verbundenen Problemen

    Davon abhängige Formeln müssen nämlich dann berücksichtigen, ob es ein Wert oder Text ist.

    Nun könnte es aber sein, dass eine Null einen Fehler erzeugt, z.B. im Nenner einen #DIV/0!-Fehler:

    =(A1+F1+H1+K1+M1)/(B1+5*C1+3*D1^2-7*E1^4)

    Falls der Nenner im Falle Null den Wert 1 annehmen soll, funktioniert (ich habe absichtlich Klammern stehen lassen):

    =WENNFEHLER(
    (A1+F1+H1+K1+M1)/(B1+5*C1+3*D1^2-7*E1^4);
    (A1+F1+H1+K1+M1))


    Der Zähler ist aber ärgerlicherweise noch doppelt aufgeführt! Wir haben die Nichtwiederholung nur im Nenner erreicht.

    Jetzt kommt Trick 1: =1/(1/3,14) ergibt nämlich wieder 3,14. Nehmen wir statt 3,14 die 0, gibt es einen Fehler. Sonst nicht. Also:

    =(A1+F1+H1+K1+M1)/WENNFEHLER(1/(1/(B1+5*C1+3*D1^2-7*E1^4));1)

    Wir haben es geschafft. Nenner und Zähler tauchen beide nur noch einmal auf, und trotzdem wird der Fehler #DIV/0! wie gewünscht behandelt. Damit müssen lange Ausdrücke (hier: Zählerausdruck und Nennerausdruck) zumindest nicht aus diesem Grund in Hilfszellen ausgelagert werden. Hilfszellen sind übrigens ansonsten absolut gut und sinnvoll - sie beschleunigen und verkleinern Modelle!


    Fazit: Stelle WENNNULL() dar mit:

    =WENNFEHLER(1/(1/ZahlOderNull);BeiNullDies)


    - Fehler werden weiterhin berücksichtigt


    =WENNNULLODERNEGATIV()

    Für den Nutzen dieser Funktion muss man etwas weiter ausholen.

    Angenommen, Arbeitszeit wird von 6:00-20:00 ohne Zuschläge bezahlt. Wir lassen Pausen mal eben gedanklich außen vor.

    Nun gibt es an einem Tag 5 Möglichkeiten, diese "Tagesarbeitszeit 6:00-20:00" zu berühren:

    1) gar nicht (frei oder außerhalb des Fensters, z.B. 20:00-23:00) = 0 Stunden Tagesarbeitszeit
    2) voll überdeckend (z.B. 5:00-20:00), also bis zur Grenze oder darüber hinaus = 14 Stunden Tagesarbeitszeit (nicht: 15)
    3) weder von Beginn an noch bis zum Ende reichend (7:00-17:00) = 10 Stunden Tagesarbeitszeit
    4) vor oder am Beginn startend, aber vor dem Ende aufhörend (5:00-17:00) = 11 Stunden Tagesarbeitszeit (nicht: 12)
    5) nach Beginn startend, aber am oder nach dem Ende aufhörend (9:00-20:30) = 11 Stunden Tagesarbeitszeit (nicht: 11:30)

    Diese 5 Möglichkeiten lassen sich mit folgender Formel "Länge des gearbeiteten Tageszeitanteils" für einen Tag wie folgt wiedergeben:

    =MAX(0;MIN("20:";Checkout)-MAX("6:";Checkin))

    Bitte selbst nachprüfen!

    Möchte man dies nun für einen Monat tun, versagt die Formel (ich nutze im folgenden das Doppelminus -- als Typumwandlung, auch wenn es nicht immer nötig ist):

    =SUMMENPRODUKT(--MAX(0;MIN("20:";C2:C32)-MAX("6:";B2:B32)))

    mit Arbeitsenden in Spalte C und Arbeitsbeginn in B.

    MAX und MIN sind nämlich nicht nach außen hin arrayfähig, sondern werten nur innere Arrays aus. Notwendig ist eine Umformulierung mit Vergleichen:

    {=SUMME(--WENN(
    WENN(--"20:"<C2:C32;--"20:";C2:C32)-
    WENN(--"6:">B2:B32;--"6:";B2:B32)<0;0;
    WENN(--"20:"<C2:C32;--"20:";C2:C32)-
    WENN(--"6:">B2:B32;--"6:";B2:B32)))
    } ohne {}, aber mit Strg-Umschalt-Enter einzugeben

    Das ist ärgerlich! Nicht nur sind die inneren beiden WENN's doppelt so lang, wie MAX und MIN; nein: Die äußere Behandlung auf "mindestens 0" erfordert sogar eine weitere Verdopplung des schon verdoppelten Terms!

    Dabei wollten wir mit =MAX(0;Ausdruck) doch nur ein Minimum von 0 erreichen.

    Nun kommt Trick 2: =EXP(LN(x)) erzeugt für positive Werte x wiederum den Wert x, ansonsten aber einen Fehler. Das machen wir uns jetzt zunutze:

    {=SUMME(--WENNFEHLER(EXP(LN(
    WENN(--"20:"<C2:C32;--"20:";C2:C32)-
    WENN(--"6:">B2:B32;--"6:";B2:B32)));0))
    }

    Vorsicht: Boolesche Vergleiche von nicht umgewandelten Textzahlen mit Gleitkommazahlen sind gefährlich. "20:"=5/6 ergibt FALSCH, --"20:"=5/6 hingegen WAHR!

    Die Ersparnis ist die Hälfte an Ausdrücken! Und im folgenden Ausdruck reduzieren wir mit der gleichen Methode innerhalb der Klammer noch einmal auf die Hälfte:

    {=SUMME(WENNFEHLER(EXP(LN(
    "20:"-WENNFEHLER(EXP(LN("20:"-C2:C32));0)-
    "06:"-WENNFEHLER(EXP(LN(B2:B32-"06:"));0)));0))
    }

    bzw., wer es mag,

    {=SUMME(WENNFEHLER(EXP(LN("14:"-
    WENNFEHLER(EXP(LN("20:"-C2:C32));0)-
    WENNFEHLER(EXP(LN(B2:B32-"06:"));0)));0))
    }

    B2:B32 und C2:C32 gibt es also nur noch je einmal. Zu Beginn war es je viermal!

    Auch wenn die Formel selbst im letzten Schritt nicht mehr kürzer geworden ist: Mit beispielsweise =INDEX(C:C;Berechnung1):INDEX(C:C;Berechnung2) statt C2:C32 und entsprechend für B2:B32 wäre sie es!

    Für die Excel-Versionen ab März 2020 gibt es nun die Allzweckwaffe LET() - kürzer, und weniger kryptisch!:

    =LET(a;1/4;d;5/6;b;B2:B32;c;C2:C32;e;WENN(d<c;d;c)-WENN(a>b;a;b);SUMME((e>0)*e))

    Ein anderes Beispiel:

    Die korrekte Behandlung von Pausenabzügen 30 und 15 Minuten von der Arbeitszeit > 6 und 9 Stunden in A1 erfolgt mit:

    =A1-(2/8<A1)/48-(3/8<A1)/96 oder
    =A1-SUMMENPRODUKT(({2.3}/8<A1)*{2.1})/96

    Will der Arbeitgeber jedoch den Arbeitnehmer mit 6:01 gearbeiteter Zeit kulanterweise statt 5:31 mit wenigstens 6:00 Stunden entlohnen, wenn dieser nachweislich nicht pausiert hat, kann er dafür verwenden:

    =SUMMENPRODUKT(WENNFEHLER(EXP(LN((A1-{0.24.26.36.37}/96)));)*{1.-1.1.-1.1})

    mit LET() dann als:
    =LET(t;{0.24.26.36.37}/96;-SUMME((A1-t)*(A1>t)*-1^SEQUENZ(;5)))

    Beispiele:

    http://office-loesung.de/p/viewtopic.php?f=166&t=768102&p=3144788#p3144788 Arbeitszeitfenster vom 5.6.2018
    https://www.ms-office-forum.net/forum/showthread.php?t=369014 Raumbelegungsüberschneidung vom 16.7.2020


    Fazit: Stelle WENNNULLODERNEGATIV() dar mit:

    =WENNFEHLER(EXP(LN(Zahl));FallsNichtPositiv)


    - funktioniert auch array-zurückgebend, im Gegensatz zu MAX(;Zahl)
    - Fehler werden weiterhin berücksichtigt


    Performancevergleich mit Alternativen

    WENNFEHLER(EXP(LN(...))) liegt dabei vorn, mit 15% bis 70% Ausführungsdauer ggü den anderen:

    Sub PerformanceArrayMax()
    Workbooks.Add xlWorksheet: d = [A1:G1]: e = Split("0 0 0 0 0 0 1 1")
    [C1] = "Formel": [C2] = "Zeit"
    a = "RC1:R[999999]C1": b = Replace(a, "C1", "C2"): c = a & "-" & b
    [A:B].Formula = "=TRUNC(RAND()*6)": [A:B].Value = [A:B].Value
    d(1, 4) = "=SUMPRODUCT(--SUBSTITUTE(" & c & ",""-""&ABS(" & c & "),0))"
    d(1, 5) = "=SUMPRODUCT((" & a & ">" & b & ")*(" & c & "))"
    d(1, 6) = "=SUM(IFERROR(--SUBSTITUTE(" & c & ",""-"",""#""),0))"
    d(1, 7) = "=SUM(IFERROR(EXP(LN(" & c & ")),0))"
    For j = 4 To 7
    Cells(1, j) = IIf(e(j), "{", "'") & d(1, j) & IIf(e(j), "}", "")
    a = Timer
    With Cells(3, j)
    If e(j) Then .FormulaArray = d(1, j) Else .FormulaR1C1 = d(1, j)
    .Resize(30, 1).FillDown
    End With
    Cells(2, j) = Timer - a
    With Range(Cells(4, j), Cells(32, j)): .Value = .Value: End With
    Next
    End Sub
     
    Zuletzt bearbeitet: 18. Juli 2020
Thema:

Simulation =WENNNULL() und =WENNNULLODERNEGATIV()

Die Seite wird geladen...
  1. Simulation =WENNNULL() und =WENNNULLODERNEGATIV() - Similar Threads - Simulation =WENNNULL =WENNNULLODERNEGATIV

  2. Einführung in die Monte-Carlo-Simulation in Excel

    in Microsoft Excel Tutorials
    Einführung in die Monte-Carlo-Simulation in Excel: Einführung in die Monte-Carlo-Simulation in Excel Excel für Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Mehr......
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden