Generische Formel
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Zusammenfassung
Um Werte zu zählen, die in einem Datensatz außerhalb der Toleranz liegen, können Sie eine Formel verwenden, die auf den Funktionen SUMPRODUCT und ABS basiert. In dem gezeigten Beispiel lautet die Formel in F6:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Dabei ist "Daten" der benannte Bereich B5: B14, "Ziel" der benannte Bereich F4 und "Toleranz" der benannte Bereich F5.
Erläuterung
Diese Formel zählt, wie viele Werte nicht im Bereich einer festen Toleranz liegen. Die Variation jedes Wertes wird damit berechnet:
ABS(data-target)
Da der benannte Bereich "Daten" 10 Werte enthält, wird durch Subtrahieren des Zielwerts in F4 ein Array mit 10 Ergebnissen erstellt:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
Die ABS-Funktion ändert alle negativen Werte in positive:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Dieses Array wird mit der festen Toleranz in F5 verglichen:
ABS(data-target)>tolerance
Das Ergebnis ist ein Array oder TRUE FALSE-Werte, und das doppelte Negativ ändert diese in Einsen und Nullen. In SUMPRODUCT sieht das endgültige Array folgendermaßen aus:
(0;0;1;0;1;0;0;1;0;1)
Dabei stehen Nullen für Werte innerhalb der Toleranz und Einsen für Werte außerhalb der Toleranz. SUMPRODUCT summiert dann die Elemente im Array und gibt das Endergebnis 4 zurück.
Alle Werte innerhalb der Toleranz
Um "Ja" zurückzugeben, wenn alle Werte in einem Datenbereich innerhalb einer bestimmten Toleranz liegen, und "Nein", wenn nicht, können Sie die Formel wie folgt anpassen:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Wenn SUMPRODUCT eine Zahl größer als Null zurückgibt, bewertet IF den logischen Test als TRUE. Ein Null-Ergebnis wird als FALSE ausgewertet.
Markieren Sie Werte außerhalb der Toleranz
Sie können Werte außerhalb der Toleranz mit einer bedingten Formatierungsregel hervorheben, die auf einer Formel wie der folgenden basiert:
=ABS(B5-target)>tolerance
Diese Seite enthält weitere Beispiele für die bedingte Formatierung mit Formeln.
