Arrotondamento dei dati di Excel

Lo strumento di arrotondamento incorporato in Excel può far apparire errato il risultato dei calcoli, perché Excel può considerare solo il valore di ogni cella singolarmente. Le funzioni di arrotondamento dei dati di Excel di thinkcell considerano i calcoli in modo olistico e arrotondano in modo che lo scostamento dai valori precisi sia minimo, mantenendo al tempo stesso corretto il calcolo con i valori arrotondati, quando ciò è matematicamente possibile.

Introduzione a thinkcell round

Quando i dati vengono compilati per un report o una presentazione PowerPoint, l'arrotondamento delle somme in Excel è un problema frequente. Spesso è auspicabile, ma difficile da ottenere, che i totali arrotondati corrispondano esattamente al totale degli addendi arrotondati. Si consideri ad esempio la tabella seguente:

Quando i valori vengono arrotondati a numeri interi utilizzando la funzione Formato celle di Excel, si ottiene la tabella seguente. I totali che sembrano “calcolati in modo errato” sono in grassetto:

Analogamente, quando si utilizzano le funzioni di arrotondamento standard di Excel, i totali dei valori arrotondati vengono calcolati correttamente, ma gli errori di arrotondamento si accumulano e i risultati spesso si discostano in modo sostanziale dai totali effettivi dei valori originali. La tabella seguente mostra il risultato di =ROUND(x,0) per l'esempio precedente. I totali che si discostano dal valore originale di 1 o più sono in grassetto:

Utilizzando thinkcell round, è possibile ottenere totali arrotondati coerenti con un “aggiustamento” minimo: mentre la maggior parte dei valori viene arrotondata all'intero più vicino, alcuni valori vengono arrotondati nella direzione opposta, mantenendo così calcoli corretti senza accumulare errori di arrotondamento. Poiché esistono molte possibilità per ottenere totali arrotondati correttamente modificando i valori, il software sceglie una soluzione che richiede il numero minimo di valori modificati e la deviazione minima dai valori precisi. Ad esempio, arrotondare per difetto 10,5 a 10 è preferibile rispetto ad arrotondare per difetto 3,7 a 3. La tabella seguente mostra una soluzione ottimale per l'esempio precedente, con i valori “aggiustati” in grassetto:

Per ottenere questo output nel proprio calcolo, selezionare semplicemente l'intervallo interessato di celle di Excel. Quindi, fare clic sul pulsante nella scheda Formulas e, se necessario, regolare la precisione di arrotondamento utilizzando la casella a discesa della barra degli strumenti.

Utilizzare thinkcell round

thinkcell round si integra perfettamente in Microsoft Excel, fornendo un insieme di funzioni simili alle funzioni di arrotondamento standard di Excel. È possibile applicare facilmente queste funzioni ai propri dati utilizzando il gruppo della barra multifunzione thinkcell round nella scheda Formulas.

Parametri di arrotondamento

Come le funzioni di Excel, le funzioni di arrotondamento di thinkcell accettano due parametri:

thinkcell round può arrotondare non solo a valori interi, ma a qualsiasi multiplo. Ad esempio, se si desidera rappresentare i dati con incrementi 5-10-15-..., è sufficiente arrotondare a multipli di cinque. Utilizzando la casella a discesa nella barra degli strumenti di thinkcell round, digitare o selezionare semplicemente la precisione di arrotondamento desiderata. thinkcell round sceglie la funzione e i parametri appropriati. La tabella seguente fornisce alcuni esempi di arrotondamento di determinati valori x mediante la barra degli strumenti insieme al relativo parametro n specifico.

Se i valori non vengono visualizzati come previsto, verificare che la formattazione celle di Excel sia impostata su General e che le colonne siano abbastanza larghe da visualizzare tutte le posizioni decimali.

Per risultati ottimali con la minima deviazione possibile dai valori sottostanti, è consigliabile utilizzare TCROUND ogni volta che è possibile. Utilizzare le funzioni più restrittive TCROUNDDOWN, TCROUNDUP o TCROUNDNEAR solo se necessario.

Attenzione: Non utilizzare mai funzioni non deterministiche come RAND() all'interno di formule TCROUND. Se le funzioni restituiscono un valore diverso a ogni valutazione, thinkcell round commetterà errori nel calcolo dei valori.

Layout del calcolo

Il layout rettangolare dell'esempio precedente ha solo scopo dimostrativo. È possibile utilizzare le funzioni TCROUND per determinare la visualizzazione di somme arbitrarie distribuite nel foglio Excel. Sono supportati anche i riferimenti 3D di Excel ad altri fogli e i collegamenti ad altri file.

Posizionamento delle funzioni TCROUND

Poiché le funzioni TCROUND servono a controllare l'output di una cella, devono essere la funzione più esterna:

Se si inserisce qualcosa di simile agli esempi errati, thinkcell round notificherà l'errore con il valore di errore Excel #VALUE!.

Limitazioni di thinkcell round

thinkcell round trova sempre una soluzione per somme arbitrarie con subtotali e totali. thinkcell round fornisce anche soluzioni sensate per alcuni altri calcoli che implicano moltiplicazione e funzioni numeriche. Tuttavia, per ragioni matematiche, l'esistenza di una soluzione arrotondata in modo coerente non può essere garantita non appena si utilizzano operatori diversi da +, - e SUM.

Moltiplicazione per una costante

In molti casi, thinkcell round produce buoni risultati quando è coinvolta una moltiplicazione per una costante, ovvero al massimo uno dei coefficienti deriva dal risultato di un'altra funzione TCROUND. Si consideri l'esempio seguente:

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:

Tuttavia, thinkcell round può solo “aggiustare” arrotondando per eccesso o per difetto. Non sono supportate ulteriori deviazioni dai valori originali. Di conseguenza, per determinate combinazioni di valori di input, non è possibile trovare alcuna soluzione arrotondata in modo coerente. In questo caso, la funzione TCROUND restituisce il valore di errore Excel #NUM!. L'esempio seguente illustra un problema irrisolvibile:

Il calcolo preciso per la cella C1 è 6×1,3+1,4=9,2. Arrotondare le 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 e l'output di thinkcell round ha questo aspetto:

Nota: La funzione Excel AVERAGE viene interpretata da thinkcell round come una combinazione di somma e moltiplicazione per una costante. Inoltre, una somma in cui lo stesso addendo compare più di una volta è matematicamente equivalente a una moltiplicazione per una costante e l'esistenza di una soluzione non è garantita.

Moltiplicazione generale e altre funzioni

Finché le funzioni TCROUND vengono utilizzate per tutte le celle pertinenti e i risultati intermedi sono collegati semplicemente da +, -, SUM e AVERAGE, gli addendi e i totali (intermedi) vengono integrati in un unico problema di arrotondamento. In questi casi, thinkcell round troverà una soluzione che garantisce coerenza in tutte le celle coinvolte, se tale soluzione esiste.

Poiché TCROUND è una normale funzione Excel, può essere combinata con funzioni e operatori arbitrari. Tuttavia, quando si utilizzano funzioni diverse da quelle menzionate sopra per collegare risultati di istruzioni TCROUND, thinkcell round non può integrare i componenti in un unico problema interconnesso. I componenti della formula verranno invece trattati come problemi distinti, che verranno risolti in modo indipendente. I risultati verranno quindi utilizzati come input per altre formule.

In molti casi, l'output di thinkcell round sarà comunque ragionevole. Tuttavia, esistono casi in cui l'uso di operatori diversi da +, -, SUM e AVERAGE porta a risultati arrotondati molto lontani dal risultato del calcolo non arrotondato. Si consideri l'esempio seguente:

In questo caso, il calcolo preciso per la cella C1 sarebbe 8,7×1,7=14,79. Poiché la cella A1 e la cella B1 sono collegate da una moltiplicazione, thinkcell round non può integrare le formule di queste celle in un problema comune. Invece, dopo aver rilevato la cella A1 come input valido, la cella B1 viene valutata in modo indipendente e l'output viene trattato come una costante all'interno del problema rimanente. Poiché non vi sono ulteriori vincoli, il valore 1,7 della cella B1 viene arrotondato all'intero più vicino, cioè 2.

A questo punto, il calcolo “preciso” per la cella C1 è 8,7×2=17,4. Questo è il problema che thinkcell round tenta ora di risolvere. Esiste una soluzione coerente che richiede di arrotondare per eccesso 17,4 a 18. Il risultato ha questo aspetto:

Si noti che il valore arrotondato nella cella C1, pari a 18, è molto diverso dal valore originale 14,79.

Risolvere i problemi delle formule TCROUND

Esistono due possibili risultati di errore che possono verificarsi quando si utilizza thinkcell round: #VALUE! e #NUM!.

#VALUE!

L'errore #VALUE! indica problemi sintattici, come formule digitate in modo errato o parametri non validi. Prestare inoltre attenzione a usare i delimitatori corretti: ad esempio, mentre nella versione inglese di Excel la formula si presenta così: =TCROUND(1.7, 0), in una versione tedesca localizzata di Excel deve essere scritta come =TCROUND(1,7; 0).

Un altro errore specifico di thinkcell round riguarda la posizione della chiamata alla funzione TCROUND: non è possibile usare una funzione TCROUND all'interno di un'altra formula. Assicurarsi che TCROUND sia la funzione più esterna della formula della cella. (vedere Posizionamento delle funzioni TCROUND)

#NUM!

L'errore #NUM! deriva da problemi numerici. Quando l'output di una funzione TCROUND è #NUM!, significa che il problema, così come definito dall'insieme di formule specificato, è matematicamente irrisolvibile. (vedere Limitazioni di thinkcell round)

Finché le formule racchiuse da funzioni TCROUND contengono solo +, - e SUM, e tutte le istruzioni TCROUND condividono la stessa precisione (secondo parametro), l'esistenza di una soluzione è garantita e thinkcell round la troverà. Tuttavia, nei casi seguenti non è garantito che esista una soluzione arrotondata in modo coerente:

• Le formule includono altre operazioni, come la moltiplicazione o funzioni numeriche. Inoltre, le sommatorie in cui lo stesso addendo compare più di una volta sono matematicamente equivalenti a una moltiplicazione.
• Si usano precisioni diverse nel secondo parametro della funzione TCROUND.
• Si usano frequentemente le funzioni specifiche TCROUNDDOWN, TCROUNDUP e TCROUNDNEAR.

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

• Usare una precisione più fine per alcune o tutte le istruzioni TCROUND.
• Non usare TCROUND con moltiplicazioni o funzioni numeriche diverse da +, - e SUM.
• Usare la stessa precisione (secondo parametro) per tutte le istruzioni TCROUND.
• Usare TCROUND invece delle funzioni più specifiche TCROUNDDOWN, TCROUNDUP e TCROUNDNEAR ove possibile.