In un laboratorio di prova Excel non è soltanto un foglio di calcolo. Se costruito bene, può diventare uno strumento potentissimo per gestire tarature, controlli qualità, carte di controllo, incertezza di misura, PT/ILC, verifiche intermedie, scadenze, recuperi, duplicati, bianchi, trend e accettabilità dei risultati.
Il punto non è conoscere cento funzioni. Il vero salto di qualità consiste nel conoscere le formule giuste, applicarle correttamente al dato analitico e soprattutto costruire fogli che riducano gli errori manuali.
Questa guida raccoglie le formule Excel che, nella pratica, risultano più utili in un laboratorio che opera secondo un sistema qualità coerente con la UNI CEI EN ISO/IEC 17025.
Prima regola: il foglio deve essere controllato, non soltanto “funzionare”
Una formula corretta inserita in un file non controllato può comunque produrre un dato sbagliato. Per questo un foglio Excel utilizzato per calcoli tecnici dovrebbe prevedere almeno:
- celle di input chiaramente distinguibili;
- celle contenenti formule protette da modifica accidentale;
- unità di misura sempre visibili;
- controlli automatici di congruità;
- gestione dei valori mancanti e degli errori;
- versione del file e identificazione della revisione;
- verifica iniziale delle formule con casi noti;
- protezione delle parti critiche del calcolo;
- tracciabilità delle modifiche quando il file è utilizzato per attività rilevanti.
In altre parole: Excel può essere semplice da usare, ma il risultato non deve dipendere dalla fortuna.
1. MEDIA: la formula più usata, ma non sempre la più adatta
La funzione:
=MEDIA(B2:B11)
calcola la media aritmetica dei risultati. È utilizzata continuamente per:
- repliche analitiche;
- controlli di taratura;
- carte di controllo;
- prove di ripetibilità;
- valutazioni di recupero;
- serie di pesate, temperature, volumi o segnali strumentali.
Attenzione però: la media è significativa quando i dati appartengono realmente alla stessa popolazione e non sono presenti valori anomali non investigati.
2. MEDIANA: quando serve un valore più robusto
=MEDIANA(B2:B11)
La mediana può essere utile quando si desidera una misura meno sensibile ai valori estremi. Non sostituisce la media nei calcoli per i quali il metodo richiede esplicitamente quest’ultima, ma è un ottimo indicatore diagnostico.
3. DEV.ST.C: la deviazione standard del campione
Per una serie di risultati sperimentali:
=DEV.ST.C(B2:B11)
È una delle funzioni statistiche più importanti per il laboratorio. Microsoft indica che DEV.ST.C stima la deviazione standard sulla base di un campione, utilizzando il denominatore n−1.
È fondamentale per:
- ripetibilità;
- precisione intermedia;
- carte di controllo;
- valutazione dell’incertezza tipo A;
- studi di omogeneità;
- controlli statistici sui dati.
4. RSD% o coefficiente di variazione
Il coefficiente di variazione relativo è uno degli indicatori più usati in chimica analitica:
RSD (%) = s / media × 100
In Excel:
=DEV.ST.C(B2:B11)/MEDIA(B2:B11)*100
È particolarmente utile quando si devono confrontare precisioni ottenute a livelli di concentrazione differenti.
5. MIN, MAX e intervallo
=MIN(B2:B11)
=MAX(B2:B11)
Per ottenere l’ampiezza dell’intervallo:
=MAX(B2:B11)-MIN(B2:B11)
Semplici, ma indispensabili per verifiche intermedie, studi di stabilità, controlli ambientali e individuazione rapida di valori estremi.
6. ASS: il valore assoluto nei criteri di accettabilità
La funzione ASS evita che il segno positivo o negativo alteri una valutazione:
=ASS(B2-C2)
Per esempio, se vogliamo valutare lo scostamento percentuale rispetto a un valore atteso:
=ASS((B2-C2)/C2)*100
È una formula molto utile per controlli su standard, pesate, temperature, volumi, recuperi o verifiche intermedie.
7. SE: trasformare un calcolo in una decisione
La funzione SE è probabilmente la più importante per automatizzare un foglio di laboratorio.
Esempio: accettazione di uno standard entro ±5%:
=SE(ASS((B2-C2)/C2)*100<=5;"OK";"KO")
In questo modo il foglio non restituisce soltanto un numero, ma anche una valutazione automatica.
8. E e O: più condizioni contemporaneamente
Per verificare che un valore sia compreso tra un limite minimo e massimo:
=SE(E(B2>=C2;B2<=D2);"OK";"KO")
Per accettare una condizione quando è sufficiente che almeno uno dei criteri sia soddisfatto:
=SE(O(B2<C2;B2>D2);"VERIFICARE";"OK")
Queste formule sono molto utili nella gestione automatica dei controlli qualità.
9. SE.ERRORE: evitare fogli pieni di #DIV/0! e #N/D
Una formula robusta deve gestire anche il dato mancante.
=SE.ERRORE(B2/C2;"")
oppure:
=SE.ERRORE(B2/C2;"VERIFICARE")
È particolarmente importante quando il foglio viene alimentato automaticamente da esportazioni strumentali o CSV.
10. ARROTONDA: il risultato va gestito, non semplicemente visualizzato
=ARROTONDA(B2;2)
Restituisce il numero con due cifre decimali.
Attenzione però a un aspetto fondamentale: formattare una cella con due decimali non è la stessa cosa che arrotondare il numero. Nel primo caso Excel conserva tutte le cifre sottostanti; nel secondo modifica il risultato restituito dalla formula.
Questa differenza può diventare importante nelle regole decisionali.
11. CONTA.NUMERI e CONTA.SE
Per sapere quanti risultati numerici sono realmente presenti:
=CONTA.NUMERI(B2:B100)
Per contare quanti controlli sono risultati non conformi:
=CONTA.SE(C2:C100;"KO")
Per contare quante determinazioni superano una determinata soglia:
=CONTA.SE(B2:B100;">10")
Queste funzioni sono perfette per cruscotti qualità e riepiloghi periodici.
12. SOMMA.SE e SOMMA.PIÙ.SE
Consentono di sommare valori che soddisfano uno o più criteri.
Per esempio, se in colonna A abbiamo il parametro e in B il consumo di standard:
=SOMMA.SE(A:A;"Piombo";B:B)
Con più condizioni è possibile usare SOMMA.PIÙ.SE, ad esempio per riepilogare dati per analita, mese, matrice o strumento.
13. CERCA.X: una delle formule più potenti per un laboratorio moderno
CERCA.X permette di cercare un valore in una tabella e restituire automaticamente l’informazione associata.
Esempio:
=CERCA.X(A2;TabellaLimiti[Parametro];TabellaLimiti[Limite];"NON TROVATO")
Applicazioni tipiche:
- richiamare automaticamente il LOQ di ogni parametro;
- associare un limite normativo a un analita;
- recuperare l’incertezza associata al metodo;
- richiamare la tolleranza di uno strumento;
- associare matrice, metodo, unità di misura e accreditamento;
- compilare automaticamente moduli di controllo.
Rispetto alle vecchie ricerche verticali, CERCA.X può lavorare in entrambe le direzioni e utilizza la corrispondenza esatta come comportamento predefinito.
14. FILTRO, UNICI e ORDINA: creare report dinamici
Con Excel moderno, molte elaborazioni che un tempo richiedevano macro possono essere ottenute con formule dinamiche.
Per estrarre soltanto i controlli KO:
=FILTRO(A2:F100;F2:F100="KO")
Per ottenere l’elenco degli analiti presenti senza duplicati:
=UNICI(A2:A1000)
Per ordinare automaticamente un elenco:
=ORDINA(A2:A100)
Sono formule eccellenti per cruscotti di laboratorio, registri delle non conformità e riepiloghi delle prove.
15. PENDENZA, INTERCETTA e RQ: la base della taratura lineare
Supponiamo di avere le concentrazioni in A2:A6 e le risposte strumentali in B2:B6.
Pendenza della retta:
=PENDENZA(B2:B6;A2:A6)
Intercetta:
=INTERCETTA(B2:B6;A2:A6)
Coefficiente di determinazione:
=RQ(B2:B6;A2:A6)
Queste funzioni consentono di costruire rapidamente il modello:
y = mx + q
e, per ricavare la concentrazione dal segnale:
x = (y − q) / m
In Excel:
=(B10-$E$2)/$E$1
dove E1 contiene la pendenza ed E2 l’intercetta.
16. REGR.LIN: molto più di una semplice retta
La funzione REGR.LIN permette di ottenere non soltanto pendenza e intercetta, ma anche ulteriori informazioni statistiche sulla regressione.
=REGR.LIN(B2:B6;A2:A6;VERO;VERO)
È particolarmente utile quando il laboratorio vuole approfondire il comportamento della taratura, anziché limitarsi al solo R².
17. Ricalcolo del punto di taratura
Un controllo molto utile consiste nel ricalcolare la concentrazione del singolo standard dalla retta:
=(Risposta-Intercetta)/Pendenza
e quindi calcolare l’errore percentuale:
=(Concentrazione_calcolata-Concentrazione_attesa)/Concentrazione_attesa*100
oppure, se interessa il solo scostamento assoluto:
=ASS((C2-B2)/B2)*100
Questo approccio è spesso molto più informativo del semplice coefficiente di determinazione.
18. Recupero percentuale
Per un materiale di riferimento o un controllo fortificato semplice:
Recovery (%) = risultato / valore atteso × 100
In Excel:
=B2/C2*100
Per un Matrix Spike, quando è necessario sottrarre il contributo naturale del campione:
Recovery (%) = (MS − campione) / spike aggiunto × 100
=(C2-B2)/D2*100
Il foglio deve naturalmente essere costruito secondo la formula prevista dal metodo applicato.
19. RPD: confronto tra due risultati
Il Relative Percent Difference è usato frequentemente per duplicati e coppie MS/MSD:
RPD (%) = |x₁ − x₂| / [(x₁ + x₂)/2] × 100
In Excel:
=ASS(B2-C2)/MEDIA(B2;C2)*100
20. Correzione del bianco
La formula è semplice:
=B2-$B$1
ma l’uso del riferimento assoluto $B$1 è fondamentale quando la formula viene trascinata su molte righe.
Il simbolo $ è quindi una delle “funzioni invisibili” più importanti di Excel: consente di bloccare riga, colonna o entrambe.
21. Fattore di diluizione
Se un campione viene diluito:
FD = volume finale / aliquota prelevata
=C2/B2
Risultato corretto:
=D2*(C2/B2)
Nei fogli complessi è consigliabile mantenere il fattore di diluizione in una cella separata, così da renderlo sempre verificabile.
22. Incertezza tipo A dalla ripetibilità
Quando la media di n misure viene utilizzata come risultato e l’approccio lo prevede:
u = s / √n
In Excel:
=DEV.ST.C(B2:B11)/RADQ(CONTA.NUMERI(B2:B11))
Attenzione: questa formula non deve essere applicata automaticamente a qualunque componente di incertezza. La definizione del contributo deve essere coerente con il modello di misura.
23. Combinazione delle componenti di incertezza
Per componenti indipendenti espresse come incertezze standard:
uc = √(u₁² + u₂² + u₃² + …)
In Excel:
=RADQ(B2^2+C2^2+D2^2+E2^2)
Per ottenere l’incertezza estesa:
U = k × uc
=B2*C2
dove B2 contiene l’incertezza standard combinata e C2 il fattore di copertura.
24. z-score nei Proficiency Testing
La formula classica è:
z = (x − X) / σpt
In Excel:
=(B2-C2)/D2
Per attribuire automaticamente l’esito:
=SE(ASS(E2)<=2;"SODDISFACENTE";SE(ASS(E2)<3;"DUBBIO";"NON SODDISFACENTE"))
La classificazione deve naturalmente essere coerente con il criterio adottato dallo specifico programma PT.
25. En score
Quando il confronto è basato sulle incertezze estese:
En = (x − Xref) / √(Ulab² + Uref²)
In Excel:
=(B2-C2)/RADQ(D2^2+E2^2)
Per la valutazione automatica:
=SE(ASS(F2)<=1;"OK";"VERIFICARE")
26. Carte di controllo: media, ±2s e ±3s
Una carta di controllo può essere costruita partendo da:
Linea centrale:
=MEDIA(B2:B30)
Limite di attenzione superiore:
=MEDIA($B$2:$B$30)+2*DEV.ST.C($B$2:$B$30)
Limite di attenzione inferiore:
=MEDIA($B$2:$B$30)-2*DEV.ST.C($B$2:$B$30)
Limite di azione superiore:
=MEDIA($B$2:$B$30)+3*DEV.ST.C($B$2:$B$30)
Limite di azione inferiore:
=MEDIA($B$2:$B$30)-3*DEV.ST.C($B$2:$B$30)
Il laboratorio deve però definire nella propria procedura quali regole di controllo applicare e come gestire trend, run e segnali di fuori controllo.
27. Scostamento percentuale rispetto a un controllo
Per verificare, ad esempio, uno standard di controllo rispetto al valore nominale:
=(B2-C2)/C2*100
Oppure, se interessa soltanto il valore assoluto:
=ASS((B2-C2)/C2)*100
Associando la formula a un SE, è possibile gestire automaticamente l’accettazione.
28. OGGI: scadenze automatiche di tarature, CRM e reagenti
=OGGI()
Per sapere quanti giorni mancano a una scadenza:
=B2-OGGI()
Per generare un avviso:
=SE(B2<OGGI();"SCADUTO";SE(B2-OGGI()<=30;"IN SCADENZA";"OK"))
È una delle formule più semplici per costruire scadenziari di:
- tarature;
- CRM;
- reagenti;
- PT;
- manutenzioni;
- formazione del personale;
- qualifiche degli operatori.
29. Controlli automatici sui LOQ
Supponiamo che B2 contenga il risultato e C2 il LOQ:
=SE(B2<C2;"< LOQ";B2)
Oppure si può utilizzare CERCA.X per richiamare automaticamente il LOQ specifico dell’analita da una tabella centralizzata.
È importante distinguere tra valore utilizzato nei calcoli interni e modalità di espressione del risultato sul rapporto di prova: non sempre devono coincidere.
30. LOD e LOQ: attenzione alle formule “universali”
In alcuni approcci statistici si incontrano formule del tipo:
LOD = 3 × s / m
LOQ = 10 × s / m
dove s rappresenta una stima della dispersione e m la sensibilità della taratura.
In Excel, ad esempio:
=3*B2/C2
=10*B2/C2
Ma qui serve molta attenzione: LOD e LOQ non hanno una formula unica valida per ogni metodo. Il laboratorio deve applicare l’approccio previsto dal metodo, dalla norma tecnica o dalla propria procedura di validazione/verifica.
31. Il vero valore di Excel: collegare le formule tra loro
La forza di Excel non sta nella singola formula, ma nella possibilità di costruire una catena logica:
dato grezzo → calcolo → confronto con criterio → esito → grafico → segnalazione
Per esempio, una sequenza ICP, GC o IC può essere importata da CSV e trasformata automaticamente in un foglio che:
- riconosce gli standard;
- calcola gli scostamenti;
- valuta ICC/CCV;
- gestisce bianchi e controlli matrice;
- calcola recuperi e RPD;
- evidenzia i campioni potenzialmente interessati da un controllo fuori criterio;
- costruisce grafici e carte di controllo;
- produce un riepilogo finale “OK / VERIFICARE / KO”.
È qui che Excel smette di essere un semplice foglio elettronico e diventa un vero strumento operativo per il laboratorio.
32. Cinque errori da evitare
- Scrivere valori dentro le formule invece di richiamare celle dedicate ai criteri.
- Non usare riferimenti assoluti quando una costante deve rimanere fissa.
- Confondere celle vuote, zero e “< LOQ”.
- Nascondere gli errori con SE.ERRORE senza capirne la causa.
- Modificare un file validato senza rieseguire i controlli necessari.
Una tabella Excel ben fatta può prevenire molti errori
Un buon foglio di calcolo dovrebbe permettere a un tecnico di vedere immediatamente:
- che cosa deve inserire;
- che cosa viene calcolato automaticamente;
- quale criterio viene applicato;
- da dove arriva il limite utilizzato;
- se il risultato è conforme;
- quale dato ha generato l’eventuale anomalia.
La formula migliore non è necessariamente la più complessa. È quella che rende il processo più controllabile, trasparente e verificabile.
DN Consulenze
DN Consulenze sviluppa e revisiona fogli Excel dedicati ai laboratori di prova: tarature, carte di controllo, controlli qualità, incertezza di misura, LOD/LOQ, PT/ILC, verifiche intermedie, monitoraggio delle apparecchiature, elaborazione automatica di CSV e sistemi di supporto alla gestione dei dati in coerenza con la UNI CEI EN ISO/IEC 17025.
Le formule riportate nell’articolo sono esempi operativi. Prima dell’utilizzo in un processo di prova devono essere adattate al metodo applicato, alle unità di misura, ai criteri di accettabilità e alle procedure del laboratorio.
Approfondimenti Excel
- Microsoft – funzione DEV.ST.C
- Microsoft – funzione CERCA.X
- Microsoft – funzione PENDENZA
- Microsoft – funzione INTERCETTA
- Microsoft – funzione RQ
Foto di Gorilla ROI Data Connector su Unsplash.