Funzioni per il calcolo del risultato e incertezza

Sommario

Le funzioni di calcolo Microsoft VBA (Visual Basic for Applications) e funzioni di calcolo Microsoft Excel
Esempio 1: Utilizzo delle funzioni e formule per il calcolo del risultato e dell'incertezza in prove microbiologiche
Esempio 2: Utilizzo delle funzioni per l'espressione del risultato e dell'incertezza in prove microbiologiche
Esempio 3: Utilizzo delle funzioni e formule per il calcolo del risultato e dell'incertezza in prove chimiche

FAQ: Esempi e soluzioni varie utili nel calcolo del risultato e dell'incertezza

Devo utilizzare una formula per sommare i risultati di alcune prove, ma le prove non sempre hanno un risultato numerico (es. <LQ).
Devo moltiplicare il risultato ottenuto per un coefficiente (es. calcolo stechiometrico), ma solo se il risultato è numerico (es. non è < LQ)
Devo utilizzare un parametro SR (scarto tipo di ripetibilità) diverso in relazione alla matrice/tipo campione su cui eseguo la prova.
Devo calcolare l'incertezza utilizzando un coefficiente di moltiplicazione diverso in relazione alla grandezza di misura del risultato della prova
Devo calcolare l'incertezza utilizzando un coefficiente di moltiplicazione diverso e un diverso numero di decimali in relazione alla grandezza di misura del risultato della prova

Labpro ver. AC - Funzioni per il calcolo del risultato e incertezza - esempi di utilizzo in Microsoft VBA e Microsoft EXCEL

Lo scopo del presente documento non è quello di indicare agli operatori quali formule utilizzare nei calcoli dei risultati/incertezze delle prove chimiche e microbiologiche, ma di fornire degli esempi di sintassi che possono essere utilizzati nell'applicativo (VBA) anche confrontando la sintassi della formula che si utilizzerebbe in Microsoft Excel.
Questa documentazione di approfondimento presume che l'utente abbia già una conoscenza di base nell'inserimento di formule e funzioni nell'applicativo. Si suggerisce di consultare preliminarmente il documento di approfondimento: Calcolare i risultati delle prove

Le funzioni di calcolo Microsoft VBA (Visual Basic for Applications) e funzioni di calcolo Microsoft Excel

L’impostazione delle formule e delle funzioni di calcolo direttamente in anagrafica prove del software consente di effettuare calcoli o comunque valutare un'espressione VBA che restituisce una stringa di testo o un valore numerico (attraverso l'utilizzo della funzione eval di VBA - Visual Basic for Applications).

Anche Microsoft Excel permette l'utilizzo di varie funzioni e formule di calcolo utilizzando sempre VBA, ma con una sintassi ed una struttura logica leggermente diversa da quella richiesta dall'applicativo LabPro (VBA originale), come esemplificato nella tabella sottostante. Il motivo principale delle differenze di sintassi deriva dalla implementazione in italiano del software Microsoft Excel rispetto al VBA originale (traduzione delle funzioni in lingua italiana, utilizzo della virgola come separatore decimale ecc)

Principali differenze nell'utilizzo di formule e funzioni LabPro ver ac (VBA) e microsoft excel

Descrizione	Esempio Formula di calcolo VBA in Labpro AC	Esempio Formula di calcolo VBA in Microsoft Excel
Costanti inserite in formula: separatore decimale punto anzichè virgola e non si usa = per precedere la formula	*[A]3.2**	*= A13,2**
Variabili di calcolo; in Labpro AC Vengono elencate sotto la formula riferendosi ai risultati dei parametri inseriti nella prova oppure al risultato di altri parametri/analisi. Le variabili di calcolo nella formula vanno indicate racchiuse fra parentesi quadre. In M Excel le variabili sono indicate con la posizione della cella in cui la variabile è inserita	[A]+[B]+[C]	= A1+A2+B5
Operatori aritmetici di calcolo: non ci sono particolari differenze per gli operatorio standard: +, -, *, /, ^. Altri operatori possono essere diversi (es. RADICE QUADRATA)	SQR	RADQ
Funzioni di calcolo. La sintassi è generalmente diversa; le principali motivazioni sono che M Excel è tradotto in italiano e inoltre che, usando la virgola come separatore decimale, il separatore fra gli argomenti delle funzioni è normalmente il punto e virgola anzichè la virgola.	iif([test],[se_vero],[se_falso])	SE([test];[se_vero];[se_falso])

Per quanto sopra evidenziato, una formula di calcolo "copiata" da M Excel non funzionerà se non vengono fatti aggiustamenti successivi che portino dalla sintassi M Excel a quella VBA originale. L'esempio sotto riportato evidenzia le modifiche necessarie per passare da una formula Excel ad una formula VBA funzionante in LabPro ver AC

Formula Excel:
=SE(A3>=20;((A3+(2*(RADQ(A3))))/(B3))*C3;((A3+2+(2*(RADQ(A3+1))))/(B3))*C3)

Variabili:

Z = cella A3
Vtot = cella B3
Vs = cella C3

Modifiche necessarie per passare dalla formula M Excel alla formula VBA funzionante in LabPro ver AC

Sostituire le variabile rappresentate dalle posizioni delle celle (A3,B3,C3) con variabili inserite nella struttura dell'esame, racchiuse fra parentesi quadre
Sostituire SE con IIF ed i separatori fra gli argomenti da ; (punto e virgola) a , (virgola)
Sostituire RADQ con SQR
Togliere il segno =

Formula VBA da utilizzare in LabPro ver AC:
iif([Z]>=20,(([Z]+(2*(SQR([Z]))))/([Vtot]))*[Vs],(([Z]+2+(2*(SQR([Z]+1))))/([Vtot]))*[Vs])

Esempio 1: Utilizzo delle funzioni e formule per il calcolo del risultato e dell'incertezza in prove microbiologiche

Partendo da un esempio (Esempio 1A) di un foglio Excel con formule per il calcolo del risultato e dell'incertezza vediamo la sintassi delle formule da Excel a VBA per utilizzo in LabPro ver AC

Vediamo un altro esempio (Esempio 1B) dove l'incertezza estesa è calcolata in base a SR (scarto tipo di riproducibilità)

Esempio utilizzi formule di calcolo in LabPro ver. AC e Microsoft Excel

Descrizione	Esempio Formula di calcolo VBA in Labpro AC	Esempio Formula di calcolo VBA in Microsoft Excel
Variabili inserite nell'esempio 1A (Identificativo cella in Excel)	D = Prima diluiz. utile L1 = 1a diluiz. Piastra 1 L2 = 2a diluiz. Piastra 1	B5= Prima diluiz. utile C5 = 1a diluiz. Piastra 1 D5 = 2a diluiz. Piastra 1
Formula utilizzata per il calcolo del RISULTATO	iif(isnumeric([L2]),(([L1]+[L2])/1.1)*10^[D],[L1])	=SE(VAL.NUMERO(D5);((C5+D5)/1,1)*10^B5;C5)
Formula utilizzata per il calcolo dell'INCERTEZZA - LIMITE INFERIORE	10^(LOG([RIS])/LOG(10)-[U])	=10^(LOG10(H5)-A5)
Formula utilizzata per il calcolo dell'INCERTEZZA - LIMITE SUPERIORE	10^(LOG([RIS])/LOG(10)+[U])	=10^(LOG10(H5)+A5)
Variabili aggiuntive inserite nell'esempio 1B (Identificativo cella in Excel)	SR = Scarto tipo di riproducibilità	A4 = scarto tipo di riproducibilità
Formula utilizzata per il calcolo dell'INCERTEZZA ESTESA	2*SQR([SR]^2+(0.18861/([L1]+[L2])))	=2*RADQ(A5^2+(0,18861/(C5+D5)))

Esempio 2: Utilizzo delle funzioni per l'espressione del risultato e dellincertezza in prove microbiologiche

Le funzioni possono essere utilizzate non solo per i calcoli ma per indicare come esprimere sul Rapporto di Prova i valori calcolati sia relativamente ai risultati che all'incertezza. Anche in questo caso vediamo la sintassi delle formule da Excel a VBA per utilizzo in LabPro ver AC.

Esempio utilizzi FUNZIONI per l'espressione del risultato e incertezza in LabPro ver. AC (VBA) e Microsoft Excel

Descrizione	Esempio Formula di calcolo VBA in Labpro AC	Esempio Formula di calcolo VBA in Microsoft Excel
Variabili inserite (Identificativo cella in Excel)	RIS = risultato calcolato RIS1 = risultato approssimato alle prime 2 cifre significative INC1 = incertezza limite inferiore INC2 = incertezza limite superiore	H5 = risultato calcolato K5 = risultato approssimato alle prime 2 cifre significative L5 = incertezza limite inferiore M5 = incertezza limite superiore
Formula utilizzata per approssimare il risultato e l'incertezza alle prime 2 cifre significative	[RIS] Il risultato potrà essere formattato scegliendo dalla pop-up formattazione la voce "Approssimato prime 2 cifre significative"	=TESTO(H5;"0,0E+00")*1
Formula utilizzata per esprimere il risultato in modo diverso in funzione del valore del risultato stesso	iif([RIS1]<1,"m.o inferiori a 1/d UFG/g",iif([RIS1]<4,"m.o. presenti ma < 4/d UFG/g",iif([RIS1]<10,([RIS1]&" UFC stimate/g"),[RIS1])))	=SE(K5<1;"m.o inferiori a 1/d UFG/g";SE(K5<4;"m.o. presenti ma < 4/d UFG/g";SE(K5<10;CONCATENA(K5;" UFC stimate/g");K5)))
Formula utilizzata per esprimere l'incertezza come intervallo di misura [LI;LS]	iif([RIS1]<10,"n/a","["&[INC1]&";"&[INC2]&"]")	=SE(K5<10;"N.A.";CONCATENA("[";L5;";";M5;"]"))

Esempio 3: Utilizzo delle funzioni e formule per il calcolo del risultato e dell'incertezza in prove chimiche

Anche in questo caso partendo da un esempio di un foglio Excel con formule per il calcolo del risultato e dell'incertezza vediamo la sintassi delle formule da Excel a VBA per utilizzo in LabPro ver. AC

Esempio utilizzi formule di calcolo in LabPro ver. AC e Microsoft Excel

Descrizione	Esempio Formula di calcolo VBA in Labpro AC	Esempio Formula di calcolo VBA in Microsoft Excel
Variabili inserite nell'esempio (Identificativo cella in Excel)	V = Volume Titolante 1(ml) C = Concentrazione titolante (N) m = Pesata olio 1 (g) V2 = Volume Titolante 2 (ml) m2 = Pesata olio 2 (g)	C4= Volume Titolante 1(ml) C5 = Concentrazione titolante (N) C6 = Pesata olio 1 (g) C8 = Volume Titolante 2 (ml) C9 = Pesata olio 2 (g)
Formula utilizzata per il calcolo del RISULTATO ACIDITA' 1	([V][C]282)/(10*[m]) Il risultato potrà essere arrotondato a 2 cifre decimali selezionando da pop-up il numero di decimali	=ARROTONDA((C4C5282)/(10*C6);2)
Formula utilizzata per il calcolo del RISULTATO ACIDITA' 2	([V2][C]282)/(10*[m2]) Il risultato potrà essere arrotondato a 2 cifre decimali selezionando da pop-up il numero di decimali	=ARROTONDA((C8C5282)/(10*C9);2)
Formula utilizzata per il calcolo del RISULTATO DELLA PROVA ACIDITA' (come Acido Oleico)	([ACID1]+[ACID2])/2	=(C7+C10)/2
Formula utilizzata per il CALCOLO e ESPRESSIONE dell'incertezza	± &(Arrotonda([RIS]0.03432,3))	CONCATENA("± ";ARROTONDA(C110,03432;3))

Per esprimere l'incertezza sul Rapporto di Prova è stata usata una funzione di concatenamento di stringa che in VBA è possibile effettuare con l'operatore & (e commerciale).

FAQ: Esempi e soluzioni varie utili nel calcolo del risultato e dell'incertezza

Vediamo la sintassi di alcune formule utili nell'ambito del calcolo dei risultati delle prove e dell'incertezza sia su Excel che in VBA per utilizzo in LabPro ver AC

Devo utilizzare una formula per sommare i risultati di alcune prove, ma le prove non sempre hanno un risultato numerico (es. <LQ). In questo caso impostare una formula del tipo [A]+[B]+[C] in VBA ottiene lo stesso effetto di inserire una formula tipo =A1+A2+A3 e poi inserire in una cella un risultato non numerico,ovvero nella cella viene visualizzato l'errore #VALORE!. Per ottenere la somma dei soli valori numerici inseriti in un intervallo su Excel si utilizza la funzione SOMMA(intervallo). Per permettere di ottenere un analogo risultato abbiamo sviluppato una funzione chiamata SommaVariabili(), non nativa di VBA. La funzione restituisce la somma dei soli valori numerici inseriti nelle variabili della funzione. Può essere utlizzata quindi quando non tutti i valori sono numerici o compilati. Per sommare tutte la variabili definite indicare SommaVariabili('*');per sommarne solo alcune indicare , ad esempio , SommaVariabili('[a];[b];[c]').
Quando occorre calcolare la concentrazione cumulativa (o sommatoria) di più sostanze, di cui alcune risultano NR, possono essere adottati 3 sistemi di stima:
1.NR=0 -> stima LOWER-BOUND;
2.NR=LR -> stima UPPER-BOUND
3.NR=LR/2 -> stima MEDIUM-BOUND
La funzione SommaVariabiliNEW (già presente) considera i valori non numerici – es <LQ – pari a zero e pertanto adotta il criterio di stima LOWER-BOUND
Dalla versione 6D-013 Sono state aggiunte le funzioni di calcolo:
- SommaVariabiliMB (stima Medium-Bound) = Restituisce la somma di tutti i valori numerici inseriti nelle variabili definite. Qualora il risultato sia non numerico (es. <LQ, <LR) verrà sommato LQ/2 (approccio MEDIUM BOUND)
- SommaVariabiliUB (stima Upper-Bound) = Restituisce la somma di tutti i valori numerici inseriti nelle variabili definite. Qualora il risultato sia non numerico (es. <LQ, <LR) verrà sommato LQ (detto approccio UPPER BOUND)

Anche in questo caso cercare di moltiplicare un elemento non mumerico per un valore con una semplice formula aritmetica mi dà un errore sia in Excel che in VBA.

Occorre anteporre una condizione che valuta se l'elemento è numerico e moltiplicarlo solo in questo caso.
In questo caso sia in M Excel che in VBA occorrerà utilizzare la funzione condizionale - SE([test];[se_vero];[se_falso]) in M Excel e iif([test],[se_vero],[se_falso]) in VBA e una funzione che restituisce Vero se il valore da utilizzare è numerico, altrimenti restituisce Falso - VAL.NUMERO(test) in M Excel e IsNumeric(test) in VBA.

Devo utilizzare un parametro SR (scarto tipo di ripetibilità) diverso in relazione alla matrice/tipo campione su cui eseguo la prova.

Anche in questo caso posso utilizzare la funzione logica (SE in M Excel o IIF in VBA) per restituire la variabile SR condizionata al tipo campione/matrice relativa al campione accettato.
Nelle variabili che è possibile utilizzare nella costruzione delle formule/funzioni di LabPro AC esiste anche il tipo campione/matrice, rappresentato con [@TC@]. La funzione logica può essere utilizzata sia con sintassi di concatenazione che di annidamento, come negli esempi sotto, ottenendo di fatto lo stesso risultato:

IIF concatenata - iif([@TC@]="SED",0.22,"")&iif([@TC@]="AQP",0.63,"")& iif([@TC@]="AQR",0.69,"")
IIF annidata - iif([@TC@]="SED",0.22,(iif([@TC@]="AQP",0.63,(iif([@TC@]="AQR",0.69,"")))))

Devo calcolare l'incertezza utilizzando un coefficiente di moltiplicazione diverso in relazione alla grandezza di misura del risultato della prova

Per risolvere problematiche di questo tipo abbiamo elaborato una funzione, non nativa di VBA, chiamata SelectRange. La funzione serve ad inserire dei valori in base all’appartenenza di un parametro specificato ad un intervallo numerico. Il valore da restituire può essere un numero o un testo.
Gli intervalli devono essere espressi con valori numerici, separati da : e compresi fra cancelletti (#); qualora il numero sia con decimali utilizzare il punto (.) per separare la parte decimale dall’intero.
Qualora il valore da restituire sia un numero con decimali utilizzare il punto (.) per separare la parte decimale dall’intero; qualora il valore da restituire sia un testo, questo dovrà essere compreso fra apici (‘).

Esempio:
SelectRange([RIS],#0:199.9#,([RIS]*0.0104),#200:1999#,([RIS]*0.1038),#2000:19999#,([RIS]*0.078))

Nell'esempio sopra riportato qualora il risultato sia compreso fra 0 e 199,9 (0 ≤ RIS < 199,9) il coefficente moltiplicatore sarà 0,0104, se compreso fra 200 e 1999 sarà 0,1038, se compreso fra 2000 e 19.999 sarà 0,078.

Attenzione! Qualora il risultato non rientri in nessuno degli intervalli definiti la funzione non darà alcun risultato.

Devo calcolare l'incertezza utilizzando un coefficiente di moltiplicazione diverso e un diverso numero di decimali in relazione alla grandezza di misura del risultato della prova

La funzione SelectRangeNEW ha lo stesso scopo della funione SelectRange ma poichè Il valore da restituire può essere un numero o un testo o UNA FUNZIONE. Pertanto può essere utilizzata per annidare altre funzioni all'interno. Nell'esempio sotto abbiamo utilizzato la funzione SelectRangeNEW con la funzione ARROTONDA.

Esempio:
SelectRangeNEW([RIS],'0:199.9',Arrotonda([RIS]*0.0104,1),'200:1999',Arrotonda([RIS]*0.1038,0),'2000:19999',Arrotonda([RIS]*0.078,0))

Nell'esempio sopra riportato qualora il risultato sia compreso fra 0 e 199,9 (0 ≤ RIS < 199,9) il coefficente moltiplicatore sarà 0,0104 ed il risultato sarà arrotondato ad una cifra decimale, se compreso fra 200 e 1999 sarà 0,1038, se compreso fra 2000 e 19.999 sarà 0,078. In entrambi i casi l'arrotondamento sarà a 0 cifre decimali.

SINTESI Esempio utilizzi formule di calcolo in LabPro ver. AC e Microsoft Excel

FAQ	Esempio Formula di calcolo VBA in Labpro AC	Esempio Formula di calcolo VBA in Microsoft Excel
Si deve utilizzare una formula per sommare i risultati di alcune prove, ma le prove non sempre hanno un risultato numerico (es. < LQ)	SommaVariabili('*')	=SOMMA(B2:B4)
Si deve moltiplicare il risultato ottenuto per un coefficiente, ma solo se il risultato è numerico.	iif(isnumeric([ris]),[ris]*1.2,"")	=SE(VAL.NUMERO(D3);D3*2,99;"")
Si deve utilizzare un parametro SR (scarto tipo di ripetibilità) diverso in relazione alla matrice/tipo campione su cui eseguo la prova	iif([@TC@]="SED",0.22,"")& iif([@TC@]="AQP",0.63,"")& iif([@TC@]="AQR",0.69,"") OPPURE iif([@TC@]="SED",0.22,(iif([@TC@]="AQP",0.63,(iif([@TC@]="AQR",0.69,"")))))	=SE(A5="SED";0,22;SE(A5="AQP";;0,63;SE(A5="TC";0,69)))
Si deve calcolare l'incertezza moltiplicanto per un coefficente diverso in relazione al valore del risultato.	SelectRange([RIS],#0:199.9#, ([RIS]0.0104),#200:1999#,([RIS]0.1038),#2000:19999#,([RIS]*0.078))	NB La funzione SelectRange è stata sviluppata da noi, pertanto non esiste una funzione corrispondente in Microsoft Excel
Si deve calcolare l'incertezza utilizzando un coefficiente di moltiplicazione diverso e un diverso numero di decimali in relazione al valore del risultato della prova	SelectRangeNEW([RIS],'0:199.9', Arrotonda([RIS]0.0104,1),'200:1999', Arrotonda([RIS]0.1038,0),'2000:19999',Arrotonda([RIS]*0.078,0))	NB La funzione SelectRange è stata sviluppata da noi,pertanto non esiste una funzione corrispondente in Microsoft Excel