Julie muốn có một số ô đầu vào trên trang tính mà khi ai đó nhập giá trị, tự động thêm 20% vào bất kỳ ô nào được nhập.

Ví dụ: nếu ai đó nhập 200 vào một trong các ô này, thì giá trị thực sự được nhập là 240.

Có vô số cách bạn có thể tiếp cận tác vụ này bằng cách sử dụng macro.

Phương pháp tốt nhất là tạo macro được chạy tự động khi một ô được thay đổi trong trang tính. Sau đó, bạn có thể kiểm tra xem liệu thay đổi có được thực hiện ở một trong các ô đầu vào hay không và điều chỉnh các giá trị cho phù hợp.

Ví dụ sau sẽ sửa đổi giá trị đã nhập nếu nó được tạo ở bất kỳ ô nào trong ba ô: A1, C3 hoặc B8.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rInput As Range     Dim rInt As Range     Dim rCell As Range

'change the input cell range as desired     Set rInput = Range("A1, C3, B8")



Set rInt = Intersect(Target, rInput)

If Not rInt Is Nothing Then         For Each rCell In rInt             If IsNumeric(rCell) Then                 With Application                     .EnableEvents = False                     rCell = rCell * 1.2                     .EnableEvents = True                 End With             End If         Next     End If End Sub

Hãy nhớ rằng đây là một trình xử lý sự kiện, có nghĩa là nó được kích hoạt (trong trường hợp này) bất cứ khi nào có điều gì đó thay đổi trong trang tính. Để sử dụng macro này, hãy nhấp chuột phải vào tab trang tính và chọn Mã Chế độ xem từ trình đơn Ngữ cảnh kết quả. Excel hiển thị Trình soạn thảo VB và sau đó bạn có thể thêm mã Worksheet_Change.

Lưu ý rằng chìa khóa để tìm ra liệu thay đổi có được thực hiện ở một trong ba ô đầu vào đã xác định hay không là hàm Intersect. Nó kiểm tra xem có sự giao nhau giữa phạm vi Mục tiêu (các ô đã thay đổi đã kích hoạt trình xử lý Worksheet_Change) và phạm vi rInput (các ô đầu vào của bạn) hay không. Nếu có, thì rInt sẽ chứa các ô đã giao nhau.

Sau đó, macro sẽ bước qua các ô đó và nếu ô chứa giá trị số, nó sẽ nhân các ô đó với 120%. (Nhân với 120% cũng giống như tăng giá trị lên 20%.) Lưu ý rằng thuộc tính .EnableEvents được đặt thành Sai khi thực hiện xong phép nhân; nếu biện pháp bảo vệ này không được thực hiện, thì mỗi phép nhân sẽ kích hoạt lại trình xử lý sự kiện này và bạn sẽ lặp lại (và mãi mãi) nhân giá trị ô với 120%.

Nếu bạn thực sự muốn thực hiện một số xử lý khác đối với các giá trị — chẳng hạn như làm tròn đến một số dấu thập phân cụ thể hoặc thành một giá trị số nguyên — thì bạn chỉ cần thực hiện thay đổi đối với dòng đơn thực sự thực hiện phép nhân.

Nếu các ô đầu vào của bạn nằm trong vùng liền kề, thì cách tiếp cận tốt hơn có thể là xác định các ô đầu vào đó là một dải ô đã đặt tên, rồi sử dụng dải ô được đặt tên đó trong macro để xác định giao điểm của các ô đã thay đổi. Theo cách đó, bạn không cần phải sửa đổi macro khi hoặc nếu nhóm ô đầu vào của bạn thay đổi.

Để sử dụng phương pháp này, giả sử rằng phạm vi ô đầu vào của bạn là B7: B19. Chọn các ô đó, sau đó, sử dụng hộp tên ở góc trên bên trái của vùng ô, nhập tên “plus20pct”. Hành động này chỉ định tên cho phạm vi. Sau đó, bạn có thể sử dụng tên đó trong macro.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rInt As Range     Dim rCell As Range

'change the input cell range as desired

Set rInt = Intersect(Target, Range("plus20pct"))

If Not rInt Is Nothing Then         For Each rCell In rInt             If IsNumeric(rCell) Then                 With Application                     .EnableEvents = False                     rCell = rCell * 1.2                     .EnableEvents = True                 End With             End If         Next     End If End Sub

Lưu ý rằng thay đổi duy nhất là ở cách xác định giao điểm của các ô — hàm Intersect sử dụng phạm vi “plus20pct” làm tham số. Mọi thứ khác hoạt động như trước đây.

Bây giờ bạn đã thấy cách thực hiện việc này bằng cách sử dụng macro, câu hỏi vẫn là liệu bạn có thực sự nên làm điều đó bằng cách sử dụng macro hay không. Đầu tiên, có những giới hạn đối với những gì các macro này có thể làm. Ví dụ: điều gì sẽ xảy ra nếu người dùng của bạn nhập ngày hoặc giờ vào một trong các ô đầu vào? Bên trong, Excel xử lý ngày và giờ dưới dạng số, nghĩa là chúng cũng sẽ được tăng lên 20%.

Thứ hai, bạn cần xem xét điều gì sẽ xảy ra với trang tính của mình nếu ai đó sửa đổi cấu trúc trang tính bằng cách thêm hoặc xóa các hàng hoặc cột.

Các macro đang sử dụng tham chiếu ô tuyệt đối (A1, C3 và B8) hoặc một dải ô đã đặt tên (plus20pct). Mặc dù phạm vi đã đặt tên có thể được điều chỉnh bằng cách thêm hoặc xóa hàng hoặc cột, nhưng các tham chiếu ô tuyệt đối sẽ không thay đổi. Vì vậy, bạn có thể kết thúc với việc kiểm tra macro (và điều chỉnh)

ô không còn là ô nhập dữ liệu mong đợi.

Thứ ba, giả sử rằng ai đó nhập giá trị (200) vào một trong các ô đầu vào của bạn. Nó tự động tăng 20% ​​và trở thành 240. Người đó nhìn thấy sự thay đổi này và tự hỏi điều gì đã xảy ra, vì vậy họ chọn ô và nhấn F2 để bắt đầu chỉnh sửa ô. Trước khi thực hiện thay đổi, họ nhớ rằng “Ồ, đúng rồi; nó được cho là sẽ tự động tăng 20%.” Vì vậy, họ chỉ cần nhấn Enter để chấp nhận giá trị 240.

Tuy nhiên, Excel coi đây là một sự thay đổi và tăng 240 lên 20%, dẫn đến 288 — không phải những gì bạn hoặc người dùng dự định.

Cân nhắc thứ hai này — sự nhầm lẫn của người dùng — là vấn đề tiềm ẩn lớn nhất với việc tự động thay đổi những gì người dùng nhập vào trang tính. Một cách tiếp cận ít nhầm lẫn hơn sẽ là có một vùng đầu vào được xác định rõ ràng cho sổ làm việc của bạn. Người dùng đưa các số liệu vào vùng nhập liệu và những số liệu đó vẫn còn khi họ nhập chúng. Sau đó, trong các ô khác hoặc trong công thức của bạn, bạn thực hiện điều chỉnh 20%.

Cách tiếp cận thiết kế này (sửa đổi thiết kế trang tính để dễ dàng nhập dữ liệu) có khả năng ít gây nhầm lẫn cho người dùng hơn là tự động thay đổi những gì họ đã nhập trong một ô trang tính. Nó cũng loại bỏ rủi ro vốn có với bất kỳ sổ làm việc nào hỗ trợ macro — người dùng có thể tải sổ làm việc mà không cần bật macro, do đó đảm bảo rằng các số liệu không được điều chỉnh như bạn dự định.

_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 (12684) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.