Forum
TÌM GIÁ TRỊ GẦN ĐÚNG NHẤT BẰNG VBA
Bạn sẽ làm thế nào nếu gặp phải yêu cầu “Tìm giá trị gần đúng nhất của 1 số trong 1 dãy số bất kỳ trong Excel?” Đây chắc chắn là 1 yêu cầu rất khó, bởi chẳng có hàm Excel nào có thể giúp bạn trả lời được câu hỏi này.
Tuy nhiên hôm nay Học Excel Online sẽ giúp bạn trả lời câu hỏi đó một cách dễ dàng bằng việc sử dụng 1 câu lệnh trong VBA. Câu lệnh đó có khó không? Hãy cùng tìm hiểu thông qua ví dụ sau:
YÊU CẦU
Tìm số gần đúng nhất trong vùng A3:A23 so với giá trị tại ô E2
CÁCH THỰC HIỆN
1. Tìm hiểu về thuật toán (tính logic)
Số gần đúng nhất có đặc điểm là có độ lệch so với mẫu là ít nhất (hay nhỏ nhất)
Các bước thực hiện thuật toán này như sau:
Bước 1: Xét độ lệch với từng giá trị của dãy
Tại cột B đặt công thức : lấy số làm mẫu trừ mỗi giá trị trong dãy ở cột A
B3=$E$2-A3
Vì với những số lớn hơn số mẫu, nên sử dụng hàm ABS để quy hết độ lệch về giá trị tuyệt đối để có thể xét cả cận trên và cận dưới.
B3=ABS($E$2-A3)
Áp dụng công thức cho vùng từ B3 tới B23 ta được kết quả ở cột B
Bước 2: Xét độ lệch nhỏ nhất (với cận dưới — Nếu lấy cận trên thì xét độ lệch lớn nhất)
Sử dụng hàm MIN để tìm giá trị nhỏ nhất (ô J6)
Bước 3: Tham chiếu tới số có độ lệch nhỏ nhất bằng hàm Index+Match (ô J7)
Như vậy chúng ta đã xác định được thuật toán với từng bước thực hiện một cách thủ công. Đây là bước rất quan trọng giúp chúng ta phát triển tư duy lập trình trong VBA
2. XÂY DỰNG LỆNH TỰ ĐỘNG TRONG VBA
Câu lệnh VBA và logic được diễn giải trong câu lệnh này như sau:
Sub Tim_so_gan_dung()
Dim rng As Range
Dim r As Range
Dim Mx As Single
Dim i As Long
Dim Target As Integer
Target = Range("E2").Value 'Số làm mẫu tại ô E2
Set rng = Range([A3], Range("A" & Rows.Count).End(xlUp)) 'Phạm vi dãy số ở cột A từ A3 tới dòng cuối có chứa dữ liệu
rng.Offset(, 1).ClearContents 'Xóa kết quả tại cột B (cách rgn 1 cột). Đây là nơi sẽ thông báo kết quả của lệnh VBA
Mx = Application.Max(rng) 'Biến Mx xác định độ lệch. Khi bắt đầu sẽ gán với giá trị lớn nhất trong dãy số
'Điều kiện sai lệch không được lớn hơn số lớn nhất
If Target > Mx * 2 Then
MsgBox "Gia tri khong phu hop"
Exit Sub
Else
For Each r In rng
If Abs(Target - r) < Mx Then 'Xác định sai số theo từng lần đối chiếu
'Hàm Abs để lấy giá trị tuyệt đối của phép trừ để tìm số có độ lệch nhỏ nhất
Mx = Abs(Target - r) 'Nếu độ lệch mới tìm được nhỏ hơn giá trị đã tìm được trước đó _
'thì sẽ lấy theo giá trị độ lệch mới (nhỏ hơn)
i = r.Row 'Xác định dòng tại vị trí có độ lệch nhỏ hơn
End If
Next r
Cells(i, 2) = "Match" 'Khi xét hết các giá trị trong danh sách thì dòng nào được ghi nhận tại biến i
'sẽ là vị trí số gần đúng nhất
End If
End Sub
view rawTim_so_gan_dung.bas hosted with ❤ by GitHub
Chạy thử câu lệnh trên chúng ta thu được kết quả như sau:
Chú ý: Trong vùng A3:B23 có áp dụng chức năng Conditional formatting để làm nổi bật vị trí dòng có chữ Match ở cột B, giúp chúng ta dễ dàng nhận ra giá trị kết quả cần tìm.
3. GÁN LỆNH VBA TRONG SỰ KIỆN THAY ĐỔI GIÁ TRỊ TẠI E2
Để mỗi lần thay đổi giá trị tại ô E2 thì Excel sẽ tự động chạy câu lệnh Macro trên để tìm kết quả một cách tự động, chúng ta tạo sự kiện WorkSheet_Change cho Sheet như sau:
Bước 1: mở cửa sổ VBA, trong mục Properties bấm chuột chọn Sheet chứa nội dung cần thực hiện và chọn sự kiện Change
Bước 2: Chỉ thực hiện lệnh macro khi có sự thay đổi tại ô E2. Do đó thực hiện câu lệnh sau:
Câu lệnh trên được hiểu là:
Nếu có sự thay đổi ở vùng E2 thì thực hiện lệnh macro Tìm số gần đúng
