Ergebnis 1 bis 1 von 1

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

  1. #1
    Office-Hilfe.com - Durchblicker
    Registriert seit
    30.12.2016
    Beiträge
    239

    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!

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

    Arbeitszeitfensterstunden-Beispiel:

    http://office-loesung.de/p/viewtopic...44788#p3144788 vom 5.6.2018


    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
    Geändert von lupo1 (06.06.2018 um 08:29 Uhr) Grund: Update Arbeitszeitfensterbeispiel

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  
Excel Ticker - News, Tipps und Tricks zu Microsoft Excel | SMS kostenlos versenden | Forenuser - Die Foren Findmaschine