Trong bài viết: Cách tính lợi nhuận dựa vào biến động giá bán kèm theo số lượng bán trên Excel chúng ta đã tìm hiểu về cách sử dụng các hàm trong Excel để giải quyết bài toán tìm mức giá bán tối ưu, mức giá bán có lợi nhuận cao nhất trong 4 mức giá. Tuy nhiên trong cách làm đó có 1 bước làm khá thủ công đó là phải thay đổi từng mức giá bán, sau đó phải ghi lại kết quả vào bảng kết quả lợi nhuận theo các mức giá bán.

Ba thao tác trên, nếu sử dụng VBA thì chúng ta có thể tự động lấy được kết quả mà không cần thực hiện thao tác gì. Hãy cùng tìm hiểu cách làm nhé.

1. Trình bày logic, trình tự công việc

Để có thể viết lệnh trong VBA Excel thì chúng ta cần xác định rõ trình tự, tính logic của công việc, từ khâu bắt đầu cho tới bước ghi nhận kết quả cuối cùng. Các bước thực hiện như sau:

• Bước 1: Thay đổi giá bán trong ô H1
• Bước 2: Kết quả sẽ được tự động tính dựa vào các công thức, do đó thu được giá trị Tổng lợi nhuận tại ô H5
• Bước 3: Ghi nhận kết quả từ ô H5 vào cột Lợi nhuận (C17:C20) theo đúng mức giá bán được chọn.

2. Tạo Module viết code cho từng trường hợp

Nếu bạn chưa biết cách tạo module trong VBA, cách chèn code trong VBA thì bạn có thể tìm hiểu qua bài viết:

Hướng dẫn cách chèn code vào module trong VBA Excel

Trong module1, chúng ta tạo 1 thủ tục (1 sub) như sau:

Sub LoiNhuan_Theo_DG01()

With Sheet1               ‘Thực hiện trong Sheet1 nên có thể viết chung cho đối tượng Sheet1

.Range(“H1”).Value = .Range(“A17”).Value       ‘Lấy giá trị đơn giá tại ô A17 vào ô H1

.Range(“C17”).Value = .Range(“H5”).Value       ‘Lấy giá trị kết quả từ ô H5 vào ô C17

End With

End Sub

Khi chạy sub này (bấm phím F5 trên bàn phím) ta thu được kết quả như sau:

Tương tự như vậy chúng ta sẽ viết ra thêm 3 sub nữa tương ứng với các mức giá tại ô A18, A19, A20 và lấy kết quả tương ứng vào các vị trí C18, C19, C20

Khi chạy từng Sub thì chúng ta cũng thu được kết quả như mong muốn.

3. Tạo Macro tự động chạy toàn bộ kết quả

Vì các Sub riêng lẻ nên có thể gom lại bằng cách tạo 1 Sub gọi ra từng Sub lẻ theo thứ tự:

Sub Lay_KetQua()

Call LoiNhuan_Theo_DG01

Call LoiNhuan_Theo_DG02

Call LoiNhuan_Theo_DG03

Call LoiNhuan_Theo_DG04

End Sub

Sub này có thể gán vào 1 nút bấm để chỉ cần bấm nút đó là tự động ra được kết quả.

(Xem thêm: Cách gán Macro vào nút bấm trong Excel)

4. Tự động lấy kết quả khi thay đổi số lượng bán trong các mức đơn giá

Khi thay đổi số lượng bán trong 1 tháng bất kỳ tại 1 mức đơn giá bất kỳ trong 4 mức giá thì làm cách nào để kết quả tự cập nhật? Đây hẳn là băn khoăn của nhiều bạn, bởi việc thay đổi số lượng bán tại mỗi tháng sẽ ảnh hưởng tới toàn bộ kết quả.

Trong bài toán này, số lượng bán phụ thuộc vào số lượng tại mức giá 100.000đ. Các mức giá khác không thay đổi về tỷ lệ. Khi thay đổi số lượng tại bất kỳ tháng nào thì cũng ảnh hưởng tới kết quả cuối cùng. Do đó mỗi khi thay đổi trong vùng B11:G11 thì sẽ phải cập nhật lại kết quả.

Cách thực hiện như sau:

Bước 1: Tạo sự kiện Worksheet_Change tại Sheet1 trong cửa sổ VBA

Bước 2: Thiết lập lệnh chỉ áp dụng thay đổi tại vùng B11:G11 trong Sheet

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range(“B11:G11”), Range(Target.Address)) Is Nothing Then

Call Lay_KetQua

End If

End Sub

(Xem thêm: Tạo sự kiện trong Worksheet khi thay đổi giá trị tại một vị trí xác định)

Bước 3: Kiểm tra kết quả khi thay đổi ở số lượng bán ở tháng bất kỳ trong vùng B11:G11

Các bạn có thể tải về file mẫu tại địa chỉ: http://bit.ly/2leKb9F để kiểm tra kết quả nhé.

Để tìm hiểu thêm nhiều kiến thức về VBA, mời bạn tham dự khóa học VBA101 – Tự động hóa Excel với lập trình VBA. Đây là khóa học rất hữu ích dành cho bạn, giúp bạn hiểu một cách bài bản về VBA và cách sử dụng VBA, ứng dụng VBA vào công việc để tăng hiệu quả. Hãy cùng khám phá nào: