Excel-TippWerte außerhalb einer Toleranz zählen

Um Werte zu zählen, die in einer Liste außerhalb einer bestimmten Toleranz liegen, können Sie eine Matrixformel einsetzen. Sie sehen in der folgenden Abbildung eine Liste von Messwerten.

In Zelle B2 ist der Zielwert (1,00) eingetragen. Erlaubt ist eine Toleranzabweichung von 0,05 nach oben und unten, was in Zelle C2 eingetragen ist.

Formel für das Zählen von Werten außerhalb eines Toleranzbereichs

Um alle Werte zu zählen, die sich außerhalb der Toleranz befinden, können Sie die folgende Formel einsetzen:

{=SUMME(WENN(ABS(B2-A2:A21)>C2;1;0))}

Erfassen Sie die Formel ohne die geschweiften Klammern am Anfang und am Ende.

Bestätigen Sie die Formel dann aber nicht mit Enter, sondern mit der Tastenkombination Strg + Umschalttaste + Enter, da es sich um eine Matrixformel handelt. Durch die Tastenkombination werden dann automatisch die geschweiften Klammern am Anfang und Ende eingefügt.

Hinweis: Besitzen Sie ein Microsoft 365-Abo, dann können Sie die Formel auch normal mit Enter erfassen.

Hinweis: Die Werte, die außerhalb des Toleranzbereichs liegen, sind mit einer bedingten Formatierung hervorgehoben. Geprüft wird:

Welcher Zelleintrag ist größer als $B$2+$C$2 oder kleiner als $B$2-$C$2?

Was berechnet die Formel?

Die Matrixformel ermittelt zuerst für jede Zelle im Bereich A2:A21 die Abweichung zum Zielwert in Zelle B2 (1,00): {=SUMME(WENN(ABS(B2-A2:A21)>C2;1;0))}

Anschließend wird die Abweichung pro Zelle an die Funktion ABS() weitergeleitet, die aus dem Wert den absoluten Wert berechnet.

Der absolute Wert einer Zahl ist der Betrag der Zahl, unabhängig von ihrem Vorzeichen. Er gibt an, wie weit die Zahl von Null entfernt ist. Der absolute Wert einer Zahl macht somit eine negative Zahl positiv.

Nach dieser Berechnung gibt es somit nur noch positive Werte (Abweichungen), weil es für die Aufgabenstellung unerheblich ist, ob die Abweichung nach oben oder nach unten erfolgt.

{=SUMME(WENN(ABS(B2-A2:A21)>C2;1;0))}

Die (positiven) Abweichung werden nun an die WENN()-Funktion weitergeleitet. Ist die Abweichung größer als die Toleranz in Zelle C2 (0,05), dann gibt die WENN()-Funktion den Wert 1, sonst 0 zurück.

{=SUMME(WENN(ABS(B2-A2:A21)>C2;1;0))}

Die Werte der WENN()-Funktion – also alle 0er und 1er – werden anschließend an die Funktion SUMME() weitergeleitet, welche die Werte addiert.

{=SUMME(WENN(ABS(B2-A2:A21)>C2;1;0))}

Auf diese Weise können Sie alle Werte in einer Liste zählen, deren Wert außerhalb der Zieltoleranz sind.

Werte innerhalb des Toleranzbereichs zählen

Wollen Sie hingegen die Werte zählen, die sich innerhalb der Toleranzabweichung befinden, dann müssen Sie die Formel nur geringfügig anpassen. Tauschen Sie einfach in der WENN-Funktion den Vergleichsoperator > durch <= aus:

{=SUMME(WENN(ABS(B2-A2:A21)<=C2;1;0))}

Hinweis: In diesem Fall sind die Werte mit einer Bedingten Formatierung hervorgehoben, die innerhalb des Toleranzbereichs liegen.