У Джона есть рабочий лист для ввода данных, который позволяет пользователям вводить информацию.

Он хотел бы, чтобы ячейка автоматически форматировалась, чтобы отображать именно то количество десятичных знаков, которое вводит пользователь. Например, если пользователь набирает «12,345», он хочет, чтобы ячейка автоматически форматировалась для отображения трех десятичных знаков. Джон знает, что он может использовать общий формат для ячейки (что делает это прекрасно), но этот подход не работает, если пользователь вводит значение, заканчивающееся на 0, например «12.34500», которое он хотел бы отформатировать (автоматически). для отображения 5 десятичных знаков.

Если вы думаете, что можете использовать собственный формат для удовлетворения потребности, это не сработает. В любом настраиваемом формате, который мы могли придумать, Excel удаляет любые конечные нули из того, что он отображает. (Или, наоборот, если пользовательский формат включает «0» в качестве заполнителя, он добавляет нули в конце записи.)

Честно говоря, самый простой способ справиться с этим — просто отформатировать ячейки как текст, прежде чем начинать вводить информацию. Таким образом, Excel просто примет то, что введено, включая любые нули в конце, и поместит это в ячейку. Кроме того, вы можете выровнять содержимое ячеек по правому краю, чтобы они, по крайней мере, больше походили на числовые значения.

Недостатком этого является то, что вы должны быть осторожны при использовании значений в формулах. Самый безопасный способ — просто заключить любую ссылку на ячейку в функцию VALUE следующим образом:

=VALUE(A1) * 1.375

Другой подход — создать макрос, который проверяет, что вводится в диапазон ячеек. Начните с форматирования ячеек как текстовых, а затем создайте именованный диапазон (DataEntry) из этих ячеек. Затем вы можете добавить следующий код в кодовую таблицу для рабочего листа, который вы используете:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim c As Range     Dim sEntry As String     Dim dEntryNumber As Double     Dim arr

If Not Intersect(Target, Range("DataEntry")) Is Nothing Then         Application.EnableEvents = False         For Each c In Target.Cells             If IsEmpty(c) Then                 c.NumberFormat = "@"    ' Reset to Text format             Else                 If IsNumeric(c) Then                     If Len(c.Value) = 0 Then                         c.NumberFormat = "@"    ' Reset to Text format                     Else                         sEntry = c.Value                         dEntryNumber = CDbl(sEntry)



arr = Split(sEntry, ".")

If UBound(arr) = 1 Then                             ' Change NumberFormat in accordance with                             ' the number of digits after the decimal point                             c.NumberFormat = "0." & String(Len(arr(1)), "0")

c.Value = dEntryNumber                         End If                     End If                 End If             End If         Next c         Application.EnableEvents = True     End If End Sub

Макрос запускается каждый раз, когда что-то изменяется на листе. Затем он проверяет, произошло ли это изменение в одной из ячеек в диапазоне DataEntry. Если да, то он проверяет, что введено в ячейку (которую Excel рассматривает как текст, поскольку именно так была отформатирована ячейка)

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

Единственное условие, при котором этот подход не сработает, — это если вы поместите значение в ячейку в диапазоне DataEntry (который преобразует ячейку в числовой формат), а затем вы введете другое числовое значение в ту же ячейку. В этом случае макрос не может узнать, вводятся ли какие-либо завершающие нули. (Помните, что конечные нули сохраняются только в том случае, если ячейка отформатирована как текст. Поскольку ячейка не форматируется, Excel обрезает конечные нули, и макрос работает с этим значением, как если бы оно было введено.)

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

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

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

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

Этот совет (1963 г.) применим к Microsoft Excel 2007, 2010, 2013 и 2016.