Trong bài viết này, chúng ta sẽ khám phá 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 hàng trên Excel. Đây là một kỹ năng quan trọng dành cho những ai muốn tối ưu hóa chiến lược giá của mình nhằm đạt được lợi nhuận cao nhất. Bạn sẽ học cách sử dụng các hàm trong Excel và VBA để tự động hóa quy trình này, giúp tiết kiệm thời gian và công sức.
1. Trình bày logic và trình tự công việc
Để thực hiện tính lợi nhuận qua VBA trong Excel, chúng ta cần xác định rõ trình tự và tính logic của công việc. 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, từ đó 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, hãy tham khảo bài viết sau: Hướng dẫn cách chèn code vào module trong VBA Excel.
Trong module1, ta tạo ra một thủ tục (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 SubKhi chạy sub này (nhấn 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 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.
Hướng dẫn tự động tính lợi nhuận trong Excel bằng VBA
3. Tạo Macro tự động chạy toàn bộ kết quả
Để gom các Sub riêng lẻ lại, chúng ta sẽ tạo một 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 SubSub này có thể gán vào một nút bấm để chỉ cần nhấn 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 một tháng bất kỳ tại một mức đơn giá bất kỳ trong 4 mức giá, chúng ta sẽ làm cách nào để kết quả tự cập nhật? Đây hẳn là vấ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 đến 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ới tỷ lệ cố định. 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 lần 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.
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!