John có một bảng tính nhập dữ liệu cho phép người dùng nhập thông tin.

Anh ấy muốn một ô được định dạng tự động để hiển thị chính xác số vị trí thập phân mà người dùng nhập. Ví dụ: nếu người dùng nhập “12.345” thì anh ta muốn ô được định dạng tự động để hiển thị 3 chữ số thập phân. John biết rằng anh ấy có thể sử dụng định dạng Chung cho một ô (điều này rất hay), nhưng cách tiếp cận đó không hoạt động nếu người dùng nhập giá trị kết thúc bằng 0, chẳng hạn như “12.34500”, mà anh ấy muốn được định dạng (tự động) để hiển thị 5 chữ số thập phân.

Nếu bạn đang nghĩ rằng bạn có thể sử dụng một định dạng tùy chỉnh để giải quyết nhu cầu, điều đó sẽ không hoạt động. Với định dạng tùy chỉnh mà chúng tôi có thể nghĩ ra, Excel sẽ loại bỏ mọi số 0 ở cuối khỏi những gì nó hiển thị. (Hoặc ngược lại, nếu định dạng tùy chỉnh bao gồm “0” làm trình giữ chỗ, nó sẽ thêm số 0 vào cuối mục nhập.)

Cách dễ nhất để xử lý điều này, khá thành thật, là chỉ cần định dạng các ô dưới dạng Văn bản trước khi bạn bắt đầu nhập thông tin. Theo cách đó, Excel sẽ đơn giản chấp nhận những gì được nhập — bao gồm bất kỳ số 0 ở cuối — và nhét nó vào ô. Hơn nữa, bạn có thể căn chỉnh bên phải nội dung của các ô để chúng ít nhất trông giống giá trị số hơn một chút.

Hạn chế của điều này là bạn phải cẩn thận trong việc sử dụng các giá trị trong công thức. Cách an toàn nhất là chỉ cần bao quanh bất kỳ tham chiếu nào đến ô trong hàm VALUE, theo cách này:

=VALUE(A1) * 1.375

Một cách tiếp cận khác là tạo một macro để kiểm tra những gì được nhập vào một dải ô. Bắt đầu bằng cách định dạng các ô dưới dạng Văn bản, rồi tạo một dải ô đã đặt tên (DataEntry) từ các ô đó. Sau đó, bạn có thể thêm mã sau vào bảng mã cho trang tính bạn đang sử dụng:

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

Macro được kích hoạt mỗi khi có gì đó thay đổi trong trang tính. Sau đó, nó sẽ kiểm tra xem liệu thay đổi đó có xảy ra ở một trong các ô trong phạm vi DataEntry hay không. Nếu vậy, nó sẽ kiểm tra những gì được nhập vào ô (mà Excel coi là văn bản, vì đó là cách ô được định dạng)

và xác định xem nó có phải là một số hay không và thêm nữa có bao nhiêu chữ số ở bên phải của chữ số thập phân. Sau đó, nó định dạng ô để có nhiều chữ số thập phân hiển thị và nhồi giá trị số trở lại ô.

Điều kiện duy nhất mà phương pháp này sẽ không hoạt động là nếu bạn đặt một giá trị vào một ô trong phạm vi DataEntry (chuyển đổi ô thành định dạng số) và sau đó bạn nhập một giá trị số khác vào cùng một ô. Trong trường hợp đó, macro không có cách nào biết được nếu có bất kỳ số 0 ở cuối nào được nhập vào hay không. (Hãy nhớ rằng các số 0 ở cuối chỉ được giữ lại nếu ô được định dạng là Văn bản. Vì ô không phải là ô, Excel sẽ loại bỏ các số 0 ở cuối và macro hoạt động với giá trị đó như thể nó đã được nhập vào.)

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (1963) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.