Formule per arrotondare i numeri in Excel

Spesso i tuoi clienti vogliono guardare numeri puliti e tondi. Inondare un utente con valori decimali e cifre non necessarie per motivi di precisione può effettivamente rendere i tuoi rapporti più difficili da leggere. Per questo motivo, potresti prendere in considerazione l'utilizzo delle funzioni di arrotondamento di Excel.

Questa figura illustra come il numero 9.45 è influenzato dall'uso delle funzioni ROUND, ROUNDUP e ROUNDDOWN.

La funzione ROUND di Excel viene utilizzata per arrotondare un determinato numero a un numero specificato di cifre. La funzione ROUND accetta due argomenti: il valore originale e il numero di cifre a cui arrotondare.

L'immissione di uno 0 come secondo argomento indica a Excel di rimuovere tutte le posizioni decimali e di arrotondare la parte intera del numero in base alla prima posizione decimale. Ad esempio, questa formula viene arrotondata a 94:

=ROUND(94.45,0)

L'immissione di 1 come secondo argomento indica a Excel di arrotondare a un decimale in base al valore della seconda cifra decimale. Ad esempio, questa formula viene arrotondata a 94,5:

=ROUND(94.45,1)

Puoi anche inserire un numero negativo come secondo argomento, dicendo a Excel di arrotondare in base ai valori a sinistra della virgola decimale. La formula seguente, ad esempio, restituisce 90:

=ROUND(94.45,-1)

È possibile forzare l'arrotondamento in una direzione particolare utilizzando le funzioni ROUNDUP o ROUNDDOWN.

Questa formula ROUNDDOWN arrotonda 94.45 per difetto a 94:

=ARROTONDAMENTO(94.45,0)

Questa formula ROUNDUP arrotonda 94.45 fino a 95:

=AROUNDUP(94.45,0)

Arrotondamento al centesimo più vicino in Excel

In alcuni settori, è pratica comune arrotondare un importo in dollari al centesimo più vicino. La figura mostra come l'arrotondamento di un importo in dollari per eccesso o per difetto al centesimo più vicino può influire sul numero risultante.

È possibile arrotondare al centesimo più vicino utilizzando le funzioni SOFFITTO o PAVIMENTO.

La funzione CEILING arrotonda un numero al multiplo di significato più vicino che gli viene passato. Questa utilità è utile quando è necessario sovrascrivere il protocollo di arrotondamento standard con le proprie regole aziendali. Ad esempio, puoi forzare Excel ad arrotondare 123.222 a 124 utilizzando la funzione CEILING con un significato di 1.

=SOFFITTO(123.222,1)

Quindi inserendo un .01 come significato indica alla funzione CEILING di arrotondare al centesimo più vicino.

Se volessi arrotondare al nichelino più vicino, potresti usare 0,05 come significato. Ad esempio, la seguente formula restituisce 123.15:

=SOFFITTO(123.11,.05)

La funzione FLOOR funziona allo stesso modo, tranne per il fatto che impone un arrotondamento per difetto al significato più vicino. La seguente funzione di esempio arrotonda 123,19 per difetto al nichelino più vicino, ottenendo come risultato 123,15:

=PIANO(123.19,.05)

Arrotondamento a cifre significative in Excel

In alcuni rapporti finanziari, le cifre sono presentate in cifre significative. L'idea è che quando si ha a che fare con numeri in milioni, non è necessario inondare un report con numeri superflui per mostrare una precisione fino alle decine, centinaia e migliaia.

Ad esempio, invece di mostrare il numero 883.788, puoi scegliere di arrotondare il numero a una cifra significativa. Ciò significherebbe visualizzare lo stesso numero di 900.000. Arrotondare 883.788 a due cifre significative mostrerebbe il numero come 880.000.

In sostanza, stai ritenendo che il posto di un particolare numero sia abbastanza significativo da mostrare. Il resto del numero può essere sostituito con zeri. Potresti pensare che farlo potrebbe introdurre problemi, ma quando hai a che fare con numeri abbastanza grandi, qualsiasi numero al di sotto di un certo significato è irrilevante.

La figura seguente mostra come implementare una formula che arrotonda i numeri a un determinato numero di cifre significative.

Si utilizza la funzione ROUND di Excel per arrotondare un determinato numero a un numero specificato di cifre. La funzione ROUND accetta due argomenti: il valore originale e il numero di cifre a cui arrotondare.

L'immissione di un numero negativo come secondo argomento indica a Excel di arrotondare in base a cifre significative a sinistra della virgola decimale. La seguente formula, ad esempio, restituisce 9500:

=ROUND(9489,-2)

La modifica dell'argomento delle cifre significative in -3 restituisce un valore di 9000.

=ROUND(B14,-3)

Funziona alla grande, ma cosa succede se hai numeri su scale diverse? Cioè, cosa succede se alcuni dei tuoi numeri sono milioni mentre altri sono centinaia di migliaia? Se volessi mostrarli tutti con 1 cifra significativa, dovresti creare una funzione ROUND diversa per ogni numero per tenere conto del diverso argomento delle cifre significative di cui avresti bisogno per ogni tipo di numero.

Per aiutare a risolvere questo problema, puoi sostituire l'argomento delle cifre significative hardcoded con una formula che calcoli quale dovrebbe essere quel numero.

Immagina che il tuo numero sia -2330.45. Puoi usare questa formula come argomento delle cifre significative nella tua funzione ROUND:

LEN(INT(ABS(-2330.45)))*-1+2

Questa formula prima avvolge il tuo numero nella funzione ABS, rimuovendo efficacemente qualsiasi simbolo negativo che potrebbe esistere. Quindi avvolge il risultato nella funzione INT, eliminando tutti i decimali che potrebbero esistere. Quindi esegue il wrapping di tale risultato nella funzione LEN per ottenere una misura di quanti caratteri sono presenti nel numero senza decimali o simboli di negazione.

In questo esempio, questa parte della formula risulta nel numero 4. Se prendi il numero –2330,45 e togli i decimali e il simbolo negativo, rimangono quattro caratteri.

Questo numero viene quindi moltiplicato per -1 per renderlo un numero negativo e quindi aggiunto al numero di cifre significative che stai cercando. In questo esempio, il calcolo è simile al seguente: 4 * –1 + 2 = –2.

Anche in questo caso, questa formula verrà utilizzata come secondo argomento per la funzione ROUND. Inserisci questa formula in Excel e arrotonda il numero a 2300 (2 cifre significative):

=ROUND(-2330,45, LEN(INT(ABS(-2330,45)))*-1+2)

È quindi possibile sostituire questa formula con riferimenti di cella che puntano al numero di origine e alla cella che contiene il numero di cifre significative desiderate.

=ROUND(B5,LEN(INT(ABS(B5)))*-1+$E$3)