Hướng dẫn tra cứu thời gian và tìm giá trị gần đúng nhất bằng VBA

(7)
217 lượt xem
Share

Làm thế nào để tra cứu thời gian và tìm giá trị gần đúng nhất bằng VBA. Hãy đọc bài viết dưới đây do eLib biên soạn để tìm cho mình được câu trả lời chính xác nhất nhé.

Mục lục nội dung

Hướng dẫn tra cứu thời gian và tìm giá trị gần đúng nhất bằng VBA

1. Cách tra cứu thời gian bằng VBA

Trong Excel chúng ta biết tới những hàm xác định thời gian hiện tại như hàm Today, hàm Now. Vậy thì trong VBA chúng ta xác định thời gian hiện tại như thế nào? Sau đây chúng ta cùng tìm hiểu nội dung này nhé.

1.1. Những đối tượng xác định thời gian trong VBA

Xác định Ngày hiện tại

Để xác định ngày hiện tại trong VBA thì chúng ta sử dụng DATE chứ không phải TODAY. Câu lệnh gọi giá trị ngày hiện tại như sau:

Sub Time_01() 'Xác định ngày hiện tại
Dim a As Date
a = Date
MsgBox a
End Sub

Xác  định Ngày+Giờ hiện tại

Sử dụng đối tượng là NOW, tương tự hàm NOW trong Excel

Câu lệnh gọi giá trị ngày giờ hiện tại như sau:

Sub Time_02() 'Xác định ngày giờ hiện tại
Dim a As Date
a = Now
MsgBox a
End Sub

1.2. Ứng dụng xác định thời gian thực hiện 1 câu lệnh VBA

Tại mỗi thời điểm bắt đầu và kết thúc mỗi câu lệnh thì giá trị thời gian xác định bởi NOW là khác nhau. Do đó chúng ta có thể dựa vào điều này để xác định độ chênh lệch thời gian khi bắt đầu và khi kết thúc 1 câu lệnh. Đó chính là thời gian VBA thực hiện xong câu lệnh đó.

Sub Time_03() 'Xác định thời gian thực hiện câu lệnh
Dim t As Date
t=Now() 'Thời gian bắt đầu câu lệnh
'Nội dung câu lệnh đặt tại đây
'Xác định thời gian thực hiện câu lệnh
MsgBox Format(Now() - t, "hh:mm:ss")
End Sub

Biến  “t” là thời điểm bắt đầu, khi gán biến t cho thời điểm đó thì giá trị thời điểm đó sẽ được ghi nhận và cố định lại.

Khi kết thúc câu lệnh thì giá trị Now sẽ thay đổi. Now() – t sẽ thể hiện được  độ lệch giữa 2 khoảng thời gian.

Format lại giá trị hiệu của 2 khoảng thời gian về dạng giờ:phút:giây để xác định cụ thể độ lệch thời gian.

Việc sử dụng biến thời gian trong VBA rất đơn giản. Bạn chỉ cần gán giá trị thời gian đó vào biến. Tại thời điểm gán thì giá trị thời gian đó sẽ được lưu lại và có thể sử dụng vào vị trí bất kỳ sau khi gán.

2. Cách duy nhất để 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 Gitiho.com 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

Tìm giá trị gần đúng nhất bằng VBA

2.1. Cách thực hiện

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:

Tìm giá trị gần đúng nhất bằng VBA

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.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

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.

2.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

Như vậy bài viết đã Hướng dẫn tra cứu thời gian và tìm giá trị gần đúng nhất bằng VBA. Chỉ cần vài thao tác đơn giản, bạn đã có thể tra cứu và tìm giá trị bằng VBA một cách nhanh chóng rồi. Chúc các bạn thực hiện thao tác thành công!

(7)
217 lượt xem
Share
Ngày:13/08/2020 Chia sẻ bởi:Xuân Quỳnh
TẢI VỀ XEM ONLINE

CÓ THỂ BẠN QUAN TÂM