Dans un environnement d’ingénierie, il n’est pas rare de devoir « normaliser »

chiffres d’une certaine manière. Par exemple, vous devrez peut-être afficher des valeurs numériques normalisées à des multiples de 10 ^ 3, de telle sorte que 7340 est exprimé par 7,34 et 73400 par 73,4.

Il est possible dans Excel d’utiliser un format de nombre personnalisé pour exprimer des informations en notation scientifique qui normalisera l’affichage d’un nombre à un multiple de 10 ^ 3. Pour ce faire, vous suivrez ces étapes:

  1. Sélectionnez les cellules que vous souhaitez mettre en forme.

  2. Affichez l’onglet Accueil du ruban.

  3. Cliquez sur la petite icône dans le coin inférieur droit du groupe Nombre.

Excel affiche la boîte de dialogue Format des cellules.

  1. Assurez-vous que l’onglet Nombre est sélectionné. (Voir la figure 1.)

  2. Dans la liste des catégories de format, choisissez Personnalisé.

  3. Dans la zone Type, entrez ## 0.0E + 0 comme format. (Cela ne fournit qu’un seul nombre à droite de la décimale. Si vous en voulez plus, augmentez le nombre de zéros après la décimale.)

  4. Cliquez sur OK.

Désormais, lorsque vous entrez un nombre tel que 7340 dans la cellule, Excel l’affiche sous la forme 7.3E + 3. En raison de la façon dont le format de cellule a été entré, la partie après le E sera toujours un multiple de 3.

C’est très bien, mais que se passe-t-il si vous ne voulez que le 7,3 dans la cellule, puis un préfixe métrique avec une unité dans une cellule adjacente, comme des kilogrammes? C’est un peu plus complexe, mais cela peut être fait à l’aide de formules. Par exemple, supposons que vous ayez votre numéro d’origine dans la cellule A2, que vous vouliez le nombre normalisé dans la cellule B2, et le préfixe métrique et le nom d’unité dans la cellule C2. Tout ce que vous avez à faire est de saisir la formule suivante dans la cellule B2:

=IF(OR(A2>=1,A2<=-1),SIGN(A2)(ABS(A2)/(10^(3INT(LOG(ABS(A2))/3))))), IF(A2=0,0,SIGN(A2)(ABS(A2)10^(-3*INT(LOG(ABS(A2))/3)))))

En supposant que les unités avec lesquelles vous travaillez sont une unité imaginaire appelée foo, dans la cellule C2, vous utiliseriez une formule différente, comme suit:

=IF(OR(A2>=1, A2<=-1),CHOOSE(INT(LOG(ABS(A2))/3)+1, "Foos", "Kilofoos", "Megafoos", "Gigafoos", "Terafoos", "Petafoos", "Exafoos"), IF(A2=0,"",CHOOSE(INT(-LOG(ABS(A2))/3)+1, "Millifoos", "Microfoos", "Nanofoos", "Picofoos", "Femtofoos", "Attofoos")))

Ces formules peuvent sembler un peu longues, et elles le sont. Cependant, ils fonctionneront pour n’importe quel nombre compris entre environ -9,99999E-18 et 9,99999E + 20.

Par exemple, si vous mettez le nombre .000125 dans la cellule A2, la cellule B2 contiendra 125 et la cellule C2 contiendra Millifoos.

Si vous préférez ne pas utiliser de formules plus longues telles que celles-ci dans vos classeurs, vous pouvez développer quelques fonctions VBA pour faire l’affaire.

La fonction suivante, MySciNum, renvoie un nombre normalisé. Ainsi, vous utiliseriez = MySciNum (A2) dans la cellule B2 pour obtenir les mêmes résultats que ceux indiqués ci-dessus:

Function MySciNum(BaseNum As Double) As Double     Select Case BaseNum         Case Is >= 1             While Abs(BaseNum) > 1000                 BaseNum = BaseNum / 1000             Wend         Case 0             'Do nothing         Case Else             While Abs(BaseNum) < 1                 BaseNum = BaseNum * 1000             Wend     End Select     MySciNum = BaseNum End Function

Cette fonction ne renvoie qu’un nombre. Pour renvoyer les unités avec le préfixe métrique approprié, vous utiliserez la fonction suivante. Tout ce que vous avez à faire est de lui transmettre la référence de la cellule et le nom d’une seule unité.

Par exemple, vous pouvez utiliser = MySciPre (A2, « foo »). La macro est la suivante:

Function MySciPre(BaseNum As Double, Unit As String) As String     Dim OrigNum As Double     Dim Pref As Integer     Dim Temp As String

Pref = 0     OrigNum = BaseNum     Select Case BaseNum         Case Is >= 1             While Abs(BaseNum) > 1000                 BaseNum = BaseNum / 1000                 Pref = Pref + 1             Wend         Case 0             Pref = 99         Case Else             While Abs(BaseNum) < 1                 BaseNum = BaseNum * 1000                 Pref = Pref - 1             Wend     End Select

Select Case Pref         Case -6             Temp = "atto" & Unit         Case -5             Temp = "femto" & Unit         Case -4             Temp = "pico" & Unit         Case -3             Temp = "nano" & Unit         Case -2             Temp = "micro" & Unit         Case -1             Temp = "milli" & Unit         Case 0             Temp = Unit         Case 1             Temp = "kilo" & Unit         Case 2             Temp = "mega" & Unit         Case 3             Temp = "giga" & Unit         Case 4             Temp = "tera" & Unit         Case 5             Temp = "peta" & Unit         Case 6             Temp = "exa" & Unit         Case Else             Temp = ""

End Select

If Len(Temp) > 0 Then         Temp = LCase(Temp)

Temp = UCase(Left(Temp, 1)) & Mid(Temp, 2)

If Abs(OrigNum) <> 1 Then Temp = Temp & "s"

End If

MySciPre = Temp End Function

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (12874) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Engineering_Calculations [Calculs d’ingénierie].