Arrotondamento dei dati di Excel

Quando si compilano dei dati per un report o una presentazione in PowerPoint, l’arrotondamento delle sommatorie in Excel è spesso causa di problemi. È desiderabile, anche se difficile da ottenere, che i totali arrotondati corrispondano esattamente al totale degli addendi arrotondati. Considerare ad esempio la tabella seguente:

Esempio di valori precisi in Excel

Quando i valori vengono arrotondati a numeri interi tramite la funzione Formatta cella di Excel si ottiene la tabella seguente. I totali che presentano errori di calcolo sono visualizzati in grassetto:

Arrotondamento mediante la funzione Formatta cella di Excel

In modo analogo, quando vengono utilizzate le funzioni predefinite di Excel per l’arrotondamento, i totali dei valori arrotondati sono calcolati correttamente ma gli errori di arrotondamento si accumulano man mano e i risultati finali si discostano spesso notevolmente dai totali effettivi dei valori originali. La tabella seguente mostra il risultato dell’utilizzo di =ROUND(x,0) nell’esempio riportato sopra. I totali che si discostano dai valori originali di 1 o più vengono visualizzati in grassetto:

Esempio di utilizzo della funzione ROUND di Excel

Utilizzando think-cell round è possibile ottenere dei totali arrotondati in modo coerente applicando degli “aggiustamenti” minimi: mentre la maggior parte dei valori vengono arrotondati al numero intero più vicino, pochi altri vengono arrotondati in direzione opposta, ottenendo in tal modo dei calcoli corretti senza accumulare errori di arrotondamento. Poiché esistono molti modi per ottenere dei totali arrotondati correttamente applicando alcuni “aggiustamenti” ai valori, il software adotta la soluzione che richiede il numero minimo di cambiamenti e il minimo scostamento dai valori precisi. Arrotondare 10,5 a 10 è preferibile, ad esempio, piuttosto che arrotondare 3,7 a 3. La tabella seguente mostra un’ottima soluzione per l’esempio riportato sopra. I valori modificati sono visualizzati in grassetto:

Esempio con think-cell round

Per ottenere questo risultato nei propri calcoli, selezionare semplicemente l’intervallo di celle di Excel interessato. Poi, fare clic sul pulsante image sulla scheda Formule e, se necessario, regolare la precisione dell’arrotondamento usando la casella a discesa della barra degli strumenti.

19.1
Utilizzo di think-cell round
19.2
Limiti di think-cell round
19.3
Risoluzione dei problemi delle formule TCROUND

19.1 Utilizzo di think-cell round

think-cell round si integra perfettamente con Microsoft Excel e fornisce una serie di funzioni di arrotondamento simili a quelle predefinite di Excel. È possibile applicare facilmente tali funzioni ai propri dati tramite i pulsanti della barra degli strumenti think-cell round.

Barra degli strumenti think-cell round in Excel

In Excel 2007 e versioni successive le barre degli strumenti sono state sostituite da barre multifunzione. La barra multifunzione think-cell round è disponibile nella scheda Formule.

Barra multifunzione think-cell round in Excel 2007 e versioni successive

19.1.1 Parametri di arrotondamento

Come le funzioni di Excel, anche le funzioni di arrotondamento di think-cell utilizzano due parametri:

x
Il valore che deve essere arrotondato, che può essere una costante, una formula o un riferimento a un’altra cella.
n
La precisione di arrotondamento. Il significato di tale parametro dipende dalla funzione utilizzata. I parametri adottati dalle funzioni di think-cell sono gli stessi utilizzati dalle funzioni equivalenti di Excel. La tabella riportata di seguito mostra alcuni esempi.

Esattamente come in Excel, anche in think-cell round è possibile non solo arrotondare a valori interi, ma anche a qualsiasi valore multiplo. Se ad esempio si desidera rappresentare i dati per blocchi di 5-10-15 e così via, occorre arrotondare i valori per multipli di cinque.

In Excel è necessario utilizzare funzioni diverse a seconda della precisione di arrotondamento che si desidera ottenere. Le funzioni ROUND e MROUND interpretano ad esempio il parametro n in modo diverso. Mediante l’elenco a discesa disponibile sulla barra degli strumenti think-cell round, immettere o selezionare la precisione di arrotondamento desiderata: think-cell round sceglierà la funzione e i parametri più appropriati.

La tabella seguente offre alcuni esempi di arrotondamento, utilizzando la barra degli strumenti, ROUND o MROUND assieme al rispettivo parametro n specifico.

Barra degli strumenti 100 50 2 1 0,01
ROUND -2 - - 0 2
MROUND 100 50 2 1 -
1,018 0 0 2 1 1,02
17 0 0 18 17 17,00
54,6 100 50 55 54 54,60
1234,1234 1200 1250 1234 1234 1234,12
8776,54321 8800 8800 8776 8777 8776,54

Se i valori non vengono visualizzati come dovrebbero, verificare che la formattazione delle celle Excel sia impostata su Generale e che le colonne siano abbastanza grandi da visualizzare tutte le posizioni decimali.

Pulsante Formula Descrizione
image TCROUND(x, n) Consentire a think-cell round di decidere quale dei due multipli più vicini è opportuno arrotondare, in modo da ridurre al minimo gli errori di arrotondamento.
image TCROUNDUP(x, n) Impone l’arrotondamento di x lontano da zero.
image TCROUNDDOWN(x, n) Impone l’arrotondamento di x verso zero.
image TCROUNDNEAR(x, n) Impone l’arrotondamento di x al valore multiplo più vicino in base alla precisione desiderata.
image Rimuove tutte le funzioni think-cell round dalle celle selezionate.
image
Consente di immettere o selezionare il multiplo di arrotondamento desiderato.
image Evidenzia tutte le celle in cui in think-cell round sono stati effettuati arrotondamenti verso il più lontano dei due multipli più vicini, anziché verso il più vicino.
image La rotellina che gira indica che think-cell round è occupato.

Per ottenere dei risultati ottimali applicando degli scostamenti minimi dai valori sottostanti, occorre utilizzare TCROUND ogni volta che è possibile. Utilizzare le funzioni più restrittive TCROUNDDOWN, TCROUNDUP o TCROUNDNEAR solo se necessario.

Attenzione: è vietato utilizzare funzioni non deterministiche come RAND() all’interno delle formule TCROUND. Se le funzioni restituiscono un valore diverso ogni volta che vengono valutate, think-cell round produce degli errori durante il calcolo dei valori.

19.1.2 Layout del calcolo

Il layout rettangolare utilizzato nell’esempio riportato sopra è stato adottato solo a scopo dimostrativo. È possibile utilizzare le funzioni TCROUND per stabilire la visualizzazione di sommatorie arbitrarie distribuite in tutto il foglio Excel. Funzionano anche i riferimenti 3-D di Excel ad altri fogli e i collegamenti ad altri file.

19.1.3 Posizionamento delle funzioni TCROUND

Poiché le funzioni TCROUND determinano il risultato di una cella, devono essere poste nella posizione più esterna:

Funzione errata:=TCROUND(A1, 1)+TCROUND( SUM(B1:E1), 1 )
Funzione corretta:=TCROUND( A1+SUM(B1:E1), 1 )
 
Funzione errata:=3*TCROUNDDOWN(A1, 1)
Funzione corretta:=TCROUNDDOWN(3*A1, 1)

Se l’utente posiziona le funzioni come negli esempi incorretti, think-cell round lo avverte visualizzando il valore di errore di Excel #VALUE!.

19.2 Limiti di think-cell round

think-cell round offre sempre una soluzione per sommatorie arbitrarie che presentano subtotali e totali e fornisce inoltre soluzioni intelligenti per altri tipi di calcolo che prevedono moltiplicazioni e funzioni numeriche. Per ragioni matematiche, tuttavia, non è sempre possibile garantire un arrotondamento coerente nel caso in cui vengano utilizzati operatori diversi da +, - e SUM.

19.2.1 Moltiplicazione con una costante

In molti casi think-cell round produce buoni risultati quando la moltiplicazione prevede una costante, ossia quando almeno uno dei coefficienti deriva dal risultato di un’altra funzione TCROUND. Considerare l’esempio seguente:

Moltiplicazione con una costante in think-cell round

Il calcolo preciso per la cella C1 è 3×1,3+1,4=5,3. Questo risultato può essere ottenuto arrotondando per eccesso il valore 1,4 a 2:

Esempio di arrotondamento con think-cell round (TCROUND)

In think-cell round è possibile tuttavia fare degli “aggiustamenti” solo arrotondando per eccesso o per difetto. Non è supportato un ulteriore scostamento dai valori originali. Pertanto, per alcune combinazioni di valori di input, non è possibile trovare una soluzione arrotondata in modo coerente. In questo caso la funzione TCROUND produce il valore errato #NUM! di Excel. L’esempio riportato di seguito illustra un problema non risolvibile.

Arrotondamento incoerente in think-cell round

Il calcolo preciso per la cella C1 è 6×1,3+1,4=9,2. L’arrotondamento delle celle A1 e B1 darebbe come risultato 6×1+2=8 o 6×2+1=13. Il risultato effettivo (9,2) non può essere arrotondato a 8 o 13. think-cell round produrrebbe dunque il seguente output:

Errore #NUM! in think-cell round

Nota: la funzione AVERAGE di Excel viene interpretata da think-cell round come una combinazione di sommatoria e moltiplicazione con una costante. Inoltre, una sommatoria in cui lo stesso addendo appare più volte è equivalente da un punto di vista matematico a una moltiplicazione con una costante e in questo caso l’esistenza di una soluzione non è garantita.

19.2.2 Moltiplicazione generale e altre funzioni

Nei casi in cui le funzioni TCROUND vengono utilizzate per tutte le celle interessate e i risultati intermedi sono connessi esclusivamente tramite +, -, SUM e AVERAGE, gli addendi, come pure i totali (intermedi), vengono integrati in un unico problema di arrotondamento. In questi casi, think-cell round troverà una soluzione che garantisce uniformità in tutte le celle coinvolte, ove una tale soluzione sia disponibile.

Poiché TCROUND è una normale funzione di Excel, può essere combinata con funzioni e operatori arbitrari. Ma quando si utilizzano delle funzioni diverse da quelle menzionate sopra per connettere i risultati di formule TCROUND, think-cell round non è in grado di integrare i componenti in un unico problema interconnesso. I componenti della formula vengono trattati, invece, come problemi distinti da risolvere separatamente. I risultati verranno quindi utilizzati come input per altre formule.

In molti casi, il risultato offerto da think-cell round sarà comunque ragionevole. Esistono tuttavia dei casi in cui l’utilizzo di operatori diversi da +, -, SUM e AVERAGE porta a ottenere risultati arrotondati molto diversi dal risultato del calcolo non arrotondato. Considerare l’esempio seguente:

Effetti dell’arrotondamento dovuti all’utilizzo di una formula errata

In questo caso il calcolo preciso per la cella C1 sarebbe 8,6×1,7=14,62. Poiché la cella A1 e la cella B1 sono connesse da una moltiplicazione, think-cell round non può integrare le formule che fanno riferimento a queste celle in un unico problema. Al contrario, dopo che la cella A1 è stata rilevata come un input valido, la cella B1 viene valutata in modo indipendente e il relativo output viene considerato come una costante nell’ambito del problema rimanente. Poiché non sono previste ulteriori limitazioni, il valore 1,7 della cella B1 viene arrotondato al numero intero più vicino, ossia 2.

A questo punto il calcolo “preciso” per la cella C1 equivale a 8,6×2=17,2. Questo è il problema che think-cell round deve ora tentare di risolvere. Una soluzione coerente prevederebbe di arrotondare per eccesso 17,2 a 18. Il risultato sarebbe il seguente:

Arrotondamento e moltiplicazione tramite think-cell round

Notare che il valore arrotondato presente nella cella C1 (18) è molto diverso dal valore originale 14,62.

19.3 Risoluzione dei problemi delle formule TCROUND

In think-cell round sono previsti due possibili risultati di errore: #VALUE! e #NUM!.

19.3.1 #VALUE!

L’errore #VALUE! segnala problemi di sintassi, ad esempio errori nella digitazione delle formule o parametri errati. Il secondo parametro di TCROUND deve essere ad esempio un valore intero. Fare attenzione, inoltre, a utilizzare i delimitatori corretti. Ad esempio, mentre nella versione internazionale di Excel la formula menzionata sopra ha il seguente aspetto: =TCROUND(1.7, 0), nella versione localizzata in tedesco deve essere scritta come segue: =TCROUND(1,7; 0)

Un altro errore tipico di think-cell round è il posizionamento della chiamata di funzione TCROUND: una funzione TCROUND non deve essere posizionata all’interno di un’altra formula. Assicurarsi che TCROUND sia la funzione più esterna della formula contenuta nella cella (vedere Posizionamento delle funzioni TCROUND).

19.3.2 #NUM!

L’errore #NUM! è il risultato di problemi numerici. Quando l’output di una funzione TCROUND è #NUM!, significa che il problema enunciato dall’insieme delle formule è matematicamente irrisolvibile (vedere Limiti di think-cell round).

Finché le formule racchiuse dalle funzioni TCROUND contengono esclusivamente +, - e SUM e tutti gli enunciati TCROUND condividono la stessa precisione (secondo parametro), think-cell round è in grado di individuare una soluzione al problema. Tuttavia, nei seguenti casi non vi è alcuna garanzia che esista una soluzione arrotondata uniforme:

  • Le formule includono altre operazioni come la moltiplicazione o funzioni numeriche. Tenere inoltre presente che le sommatorie in cui lo stesso addendo è presente più volte sono equivalenti da un punto di vista matematico a una moltiplicazione.
  • Il secondo parametro della funzione TCROUND utilizza precisioni diverse.
  • Si fa un uso frequente delle funzioni specifiche TCROUNDDOWN, TCROUNDUP e TCROUNDNEAR.

È possibile provare a riformulare il problema in modo da ottenere una soluzione coerente. Provare quanto segue:

  • Utilizzare una precisione più accurata per alcuni o per tutti gli enunciati TCROUND.
  • Non utilizzare TCROUND con moltiplicazioni o funzioni numeriche diverse da +, - e SUM.
  • Utilizzare la stessa precisione (secondo parametro) per tutti gli enunciati TCROUND.
  • Utilizzare se possibile TCROUND invece delle funzioni più specifiche TCROUNDDOWN, TCROUNDUP e TCROUNDNEAR.