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. Choisissez l’option Cellules dans le menu Format. Excel affiche la boîte de dialogue Format des cellules.

  3. Assurez-vous que l’onglet Nombre est sélectionné.

  4. Dans la liste des catégories de format, choisissez Personnalisé. (Voir la figure 1.)

  5. 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.)

  6. 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étique 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 (2928) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

lien: / excelribbon-Engineering_Calculations [Calculs d’ingénierie].