Formula generica
=SUMPRODUCT(--(ABS(data-target)>tolerance))
Sommario
Per contare i valori fuori tolleranza in un set di dati, è possibile utilizzare una formula basata sulle funzioni SUMPRODUCT e ABS. Nell'esempio mostrato, la formula in F6 è:
=SUMPRODUCT(--(ABS(data-target)>tolerance))
dove "dati" è l'intervallo denominato B5: B14, "obiettivo" è l'intervallo denominato F4 e "tolleranza" è l'intervallo denominato F5.
Spiegazione
Questa formula conta quanti valori non rientrano nell'intervallo di una tolleranza fissa. La variazione di ogni valore si calcola con questo:
ABS(data-target)
Poiché l'intervallo denominato "dati" contiene 10 valori, sottraendo il valore target in F4 verrà creato un array con 10 risultati:
(0.001;-0.002;-0.01;0.003;0.008;0;-0.003;-0.01;0.002;-0.006)
La funzione ABS cambia qualsiasi valore negativo in positivo:
(0.001;0.002;0.01;0.003;0.008;0;0.003;0.01;0.002;0.006)
Questo array viene confrontato con la tolleranza fissa in F5:
ABS(data-target)>tolerance
Il risultato è un array o valori TRUE FALSE, e il doppio negativo li cambia in uno e zero. All'interno di SUMPRODUCT, l'array finale ha questo aspetto:
(0;0;1;0;1;0;0;1;0;1)
dove gli zeri rappresentano i valori entro la tolleranza e gli 1 rappresentano i valori fuori tolleranza. SUMPRODUCT quindi somma gli elementi nell'array e restituisce un risultato finale, 4.
Tutti i valori entro la tolleranza
Per restituire "Sì" se tutti i valori in un intervallo di dati rientrano in una determinata tolleranza e "No" in caso contrario, puoi adattare la formula in questo modo:
=IF(SUMPRODUCT(--(ABS(data-target)>tolerance)),"Yes","No")
Se SUMPRODUCT restituisce un numero maggiore di zero, IF valuterà il test logico come TRUE. Un risultato pari a zero verrà valutato come FALSO.
Evidenzia i valori fuori tolleranza
Puoi evidenziare i valori fuori tolleranza con una regola di formattazione condizionale basata su una formula come questa:
=ABS(B5-target)>tolerance
Questa pagina elenca altri esempi di formattazione condizionale con formule.
