В инженерной среде нет ничего необычного в необходимости «нормализовать»

числа каким-то образом. Например, вам может потребоваться показать числовые значения, нормализованные до кратных 10 ^ 3, так что 7340 выражается как 7,34, а 73400 выражается как 73,4.

В Excel можно использовать настраиваемый числовой формат для выражения информации в экспоненциальной нотации, которая нормализует отображение числа до кратного 10 ^ 3. Для этого выполните следующие действия:

  1. Выделите ячейки, которые хотите отформатировать.

  2. Выберите параметр «Ячейки» в меню «Формат». Excel отображает диалоговое окно «Формат ячеек».

  3. Убедитесь, что выбрана вкладка Число.

  4. В списке категорий формата выберите Пользовательский. (См. Рис. 1.)

  5. В поле Тип введите ## 0.0E + 0 в качестве формата. (Это дает только одно число справа от десятичной точки. Если вы хотите больше, увеличьте количество нулей после десятичной точки.)

  6. Щелкните ОК.

Теперь, когда вы вводите в ячейку число, например 7340, Excel отображает его как 7.3E + 3. Из-за способа ввода формата ячейки часть после E всегда будет кратна 3.

Это нормально и хорошо, но что, если вам нужно только 7,3 в ячейке, а затем метрический префикс с единицей измерения в соседней ячейке, например килограммы? Это немного сложнее, но это можно сделать с помощью формул. Например, предположим, что у вас есть исходный номер в ячейке A2, вам нужен нормализованный номер в ячейке B2, а метический префикс и название единицы в ячейке C2. Все, что вам нужно сделать, это ввести следующую формулу в ячейку 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)))))

Предполагая, что единицы, с которыми вы работаете, являются воображаемой единицей, называемой foo, в ячейке C2 вы должны использовать другую формулу, а именно:

=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")))

Эти формулы могут показаться немного длинными, и так оно и есть. Однако они будут работать для любого числа от -9.99999E-18 до 9.99999E + 20.

Например, если вы поместите число .000125 в ячейку A2, тогда ячейка B2 будет содержать 125, а ячейка C2 будет содержать Millifoos.

Если вы предпочитаете не использовать более длинные формулы, подобные этим, в своих книгах, вы можете разработать пару функций VBA, чтобы добиться цели.

Следующая функция MySciNum возвращает нормализованное число. Таким образом, вы должны использовать = MySciNum (A2) в ячейке B2, чтобы получить такие же результаты, как указано выше:

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

Эта функция возвращает только число. Чтобы вернуть единицы с соответствующим префиксом метрики, вы должны использовать следующую функцию. Все, что вам нужно сделать, это передать ему ссылку на ячейку и имя отдельного объекта.

Например, вы можете использовать = MySciPre (A2, «foo»). Макрос выглядит следующим образом:

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

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2928) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Engineering_Calculations [Инженерные расчеты].