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:

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

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!