Ok, peut-être pas mille 😁.
Je vous propose par le formatage, les fonctions ARRONDI, ARRONDI.INF, ARRONDI.SUP, ARRONDI.AU.MULTIPLE, PAIR, IMPAIR, ENT, TRONQUE, PLAFOND.MATH/PLAFOND, PLANCHER.MATH/PLANCHER.
Je m'excuse à l'avance pour toutes les bêtises que je vais dire dans cet article n'étant ni mathématicien ni banquier (Voir plus bas).
Déjà c'est quoi arrondir un nombre ?
Pour moi, l'arrondi classique (mathématique) est celui-là :
Quand ont arrondi une valeur d'une décimale, on retire une décimale et la décimale restante ou l'unité s'il n'y a plus de décimale est augmentée ou diminuée de 1 selon cette logique.
Les 5 premières valeurs (en vert) sont arrondies à la valeur inférieure et les 5 suivant à la valeur supérieure (donc 3.5 passe à 4).
Mais cette définition se complique avec les nombres négatifs. Le terme supérieur peut porter à confusion (-3 est supérieur à -4).
Il est peut plus judicieux d'indiquer que les 5 valeurs les plus proches de zéro (en vert) s'en rapprochent, les autres s'en éloignent (en bleu).
Arrondir à l'écran et à l'impression
On utilise un format numérique appliqué à la cellule.
Par exemple :
La cellule (B2) affiche 3 mais contient 3,4.
On va calculer le double de B2.
Excel utilise 3,4 comme valeur pour la cellule B2 donc B2 * 2 est égale à 6.8 qui arrondi affiche 7 en cellule C2.
Quoi le double de 3 est 7 😁 !
Cette méthode est plus un "maquillage" des valeurs et peut être trompeur.
Les fonctions d'arrondi
Arrondi à une fraction proche, le classique
La fonction ARRONDI
L'arrondi "Classique".
= ARRONDI(Nombre ; No_chiffres)- Nombre : Valeur à arrondir (scalaire ou matrice voir Excel et les matrices).
- No_chiffres : Position de l'arrondi par rapport à la virgule, positif à gauche, négatif à droite (scalaire ou matrice).
Arrondir un nombre à la valeur inférieure/supérieure
Les fonctions ARRONDI.INF & ARRONDI.SUP
L'arrondi à la valeur inférieure/supérieure.
=ARRONDI.INF(Nombre ; No_chiffres)
=ARRONDI.SUP(Nombre ; No_chiffres)- Nombre : Valeur à arrondir (scalaire ou matrice voir Excel et les matrices).
- No_chiffres : Position de l'arrondi par rapport à la virgule, positif à gauche, négatif à droite (scalaire ou matrice).
Mathématiquement ARRONDI.INF d'une valeur négative renvoie un chiffre plus grand ! 🤔
Rappel :
> ARRONDI.INF : S'approche toujours de 0.
> ARRONDI.SUP : S'éloigne toujours de 0.
Les fonctions ENT et TRONQUE
=ENT(Nombre)
=TRONQUE(Nombre [ ; No_chiffres])- Nombre : Valeur à arrondir (scalaire ou matrice voir Excel et les matrices).
- No_chiffres : Position de l'arrondi par rapport à la virgule, positif à gauche, négatif à droite (scalaire ou matrice), 0 valeur par défaut.
Attention :
- ENT s'éloigne de 0 pour les nombres négatifs donc ENT(-4,3) = -5 ! 🤪
- C'est pourquoi je privilégie TRONQUE(Nombre ; 0) à ENT(Nombre).
Arrondir un nombre à un multiple
La fonction ARRONDI.AU.MULTIPLE
=ARRONDI.AU.MULTIPLE(Nombre ; Multiple)- Nombre : Valeur à arrondir (scalaire seulement !).
- Multiple : Le multiple auquel vous souhaitez arrondir le nombre (scalaire ou matrice) ; il(s) doit/doivent être du même signe que nombre.
La fonction ARRONDI est une version de ARRONDI.AU.MULTIPLE utilisant des multiples basés sur 1 : ...0.01, 0.1, 1, 10, 100...
Remarque : Le * SIGNE() sert à obtenir un argument Multiple du même signe que l'argument Nombre pour la recopie.
Je fais le rapprochement avec la fonction ARRONDI car il n'est pas évident de comprendre que l'arrondi de 6 au multiple de 4 est... 8 et non 4 (toujours le même problème à savoir si la valeur "milieu" s'approche ou s'éloigne de 0) !
C'est le même cas que le 3.5 du début de l'article qui s'arrondi à 4.
Remarque :
Les résultats ARRONDI.AU.MULTIPLE pour les valeurs de Multiple non entier sont un peu "aléatoire" 😱.
=ARRONDI.AU.MULTIPLE(6,05 ; 0,1) '=> 6,0 et non la valeur attendue 6,1 (idem pour 9,35 ; 10,45 ; ...) ! Je suppose que le problème est lié à la conversion de nombres à virgule flottante (problème classique de l'informatique voir et voir par exemple).
On n'a pas ce problème avec la fonction ARRONDI.
=ARRONDI(6,05 ; 1) '=> 6,1 Ouf ! Les fonctions PAIR, IMPAIR
=PAIR(Nombre)
=IMPAIR(Nombre)- Nombre : Valeur à arrondir (scalaire ou matrice voir Excel et les matrices).
Ce sont des arrondis au multiple de 2, PAIR ne renvoyant que des valeurs pairs, IMPAIR que des valeurs IMPAIR.
Les fonctions PLAFOND.MATH (anciennement PLAFOND), PLANCHER.MATH (anciennement PLANCHER)
Ces fonctions remplacent les fonctions PLAFOND et PLANCHER (toujours disponibles pour compatibilité mais à ne plus utiliser).
=PLAFOND.MATH(Nombre [; Précision] [; Mode])
=PLANCHER.MATH(Nombre [; Précision] [; Mode])- Nombre : Valeur à arrondir (scalaire ou matrice voir Excel et les matrices).
- Précision : Le multiple auquel vous souhaitez arrondir le nombre (scalaire ou matrice).
- Mode : Pour les nombres négatifs, détermine si Nombre est arrondi en se rapprochant (Vide, 0, FAUX) ou en s’éloignant de zéro (VRAI ou autres valeurs numériques que 0). Argument n'existant pas pour PLAFOND et PLANCHER).
Il s'agit d'une ARRONDI.SUP/ARRONDI.INF au multiple
Le troncage manuel
Dans les options d'Excel (Fichier>Options) on a ce paramètre.
En l'activant Excel arrondit les valeurs en supprimant réellement les décimales qui seront définitivement perdues (bien évidemment que pour celle provenant d'une saisie pas d'une formule).
Ok mais pourquoi le banquier du début de l'article ?
Il existe une autre méthode d'arrondi qui est utilisé par Excel portant plusieurs noms dont arrondi des banquiers (Voir).
Par exemple 0.5, 1.5, 2.5, 3.5 ... seront alternativement arrondi en s'approchant de zéro puis en s'en éloignant.
On a ce type d'arrondi en VBA :
Sub Test2()
Debug.Print Round(0.5) ' = 0 > S'approche de 0
Debug.Print Round(1.5) ' = 2 > S'éloigne de 0
Debug.Print Round(2.5) ' = 2 > S'approche de 0
Debug.Print Round(3.5) ' = 4 > S'éloigne de 0
Debug.Print Round(4.5) ' = 4 > S'approche de 0
Debug.Print Round(-0.5) ' = 0 > S'approche de 0
Debug.Print Round(-1.5) ' = -2 > S'éloigne de 0
Debug.Print Round(-2.5) ' = -2 > S'approche de 0
Debug.Print Round(-3.5) ' = -4 > S'éloigne de 0
Debug.Print Round(-4.5) ' = -4 > S'approche de 0
End SubEt aussi dans PowerQuery.
Mais pas dans PowerPivot !
Arrondi banquier avec une formule
=SI( ABS(B3-2*TRONQUE(B3/2;0))=0,5 ; ARRONDI.INF(B3;0) ; ARRONDI(B3;0) )On corrige un arrondi sur 2 (modulo 2) qui au lieu de s'éloigner de 0 doit s'en approcher.
Pour information :
B3-2*TRONQUE(B3/2;0) => Reste de la division du nombre par 2 (la fonction MOD ou ENT ne renvoient pas un résultat correcte pour calculer le reste).
A voir : MACRO : Arrondi inférieur des valeurs (pas via un formatage)
Synthèse
Merci pour votre attention bienveillante.
Un commentaire