Khi làm việc với Excel, đôi khi bạn cần tìm giá trị gần đúng nhất trong một dãy số nhất định. Khi gặp yêu cầu “Tìm giá trị gần đúng nhất của một số trong một dãy số bất kỳ trong Excel?”, bạn sẽ dễ dàng gặp khó khăn do tính chất phức tạp của vấn đề này. Ngày hôm nay, chúng ta sẽ cùng khám phá cách giải quyết vấn đề này một cách đơn giản thông qua việc sử dụng cú pháp VBA.
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
Tìm số gần đúng nhất
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à nhỏ nhất (hay nhỏ nhất). Các bước thực hiện thuật toán này như sau:
Công thức tìm số gần đúng nhất
Bước 1: Xét độ lệch với từng giá trị của dãy
Tại ô B bạn sẽ đặt công thức như sau: lấy số đang mẫu và trừ đi giá trị trong dãy ở ô 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 về độ lệch 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 đến B23 sẽ đượ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:
Chạy thử câu lệnh trên chúng ta thu được kết quả như sau:
Kết quả tìm số gần đúng
Chú ý: Trong vùng A3:B23 áp dụng chức năng Conditional formatting để làm nổi bật vị trí 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 để 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 phải lê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.
Tải về file mẫu: Tìm giá trị gần đúng nhất
Chúc các bạn học tốt cùng Học Excel Online!