tamvie
ERP consultant
- Tham gia
- 5/11/06
- Bài viết
- 6
- Được thích
- 7
- Giới tính
- Nam
Chào các bạn
Hôm này mình xin giới thiệu với các bạn công cụ Goal seek trong Excel kết hợp với Macro để tính ra mức lương Gross.
Trước hết xin giới thiệu tính năng goal seek trong excel:
Goal seek (Hàm mục tiêu) thường được áp dụng trong các bài toán như tính ngược lại mức lương Gross khi biết trước mức lương Net.
Goal Seek hoạt động bằng cách sử dụng phương pháp lặp đi lặp lại (iterative method) để tìm ra lời giải. Nghĩa là, Goal Seek sẽ thử giá trị ban đầu của biến để xem nó có tạo ra kết quả mà bạn muốn hay không. Nếu không, Goal Seek sẽ thử tiếp với những giá trị khác nhau, cho đến khi nó tìm ra lời giải, hay nói cách khác, cho đến khi nào kết quả mà nó tìm được gần giống với kết quả của bạn muốn nhất.
LƯƠNG NET = LƯƠNG GROSS - (BHXH + BHYT + BHTN + KPCĐ+THUẾ TNCN)
Trong đó:
- Lương Net là số đã cho trước
- Còn BHXH (8%), BHYT(1.5%), BHTN (1%), KPCĐ (1%), Thuế TNCN lại được tính dựa trên Lương Gross.
Giả sử lương Net cua Ông Phạm Văn An là : 43.000.000 đồng/ tháng.
Mức lương đóng bảo hiểm của ông An là: 14.440.000 đồng/ tháng. Tổng Lương (Gross) của ông An = Lương đóng bảo hiểm (14.440.000) + lương kinh doanh. Số người phụ thuộc của ông An là 1 người. Thuế thu nhập cá nhân tính trên tổng mức lương của Ông An.
Yêu cầu bài toán là tính ra lương kinh doanh của ông An là bao nhiêu để lương Net của ông An đúng bằng 43.000.000
Hay nói cách khác là cần tính ra Lương kinh doanh của ông A bằng bao nhiêu để: LƯƠNG GROSS - (BHXH + BHYT + BHTN + KPCĐ+THUẾ TNCN)- LƯƠNG NET= 0 (cột U)
Khi bạn thiết lập một bảng tính để sử dụng Goal Seek, bạn thường có một công thức trong một ô, và các biến cho công thức này (với một giá trị ban đầu) trong những ô khác. Công thức có thể có nhiều biến, nhưng Goal Seek chỉ cho phép bạn xử lý mỗi lần một biến mà thôi.
Chạy Goal Seek
Thiết lập một ô làm ô thay đổi (changing cell: Lương kinh doanh, cột F). Đây là giá trị mà Goal Seek sẽ xử lý lặp đi lặp lại để cố gắng tìm ra giá trị mà khi đó cột U=0
Như vậy sau khi bạn chạy goal seek thì excel tính ra được mức lương kinh doanh của ông A là : 35.640.800
Tuy nhiên, mỗi lần chạy Goal seek, excel chỉ chạy được có một dòng dữ liệu. Muốn tính cho hàng loạt nhân viên (hàng trăm nhân viên, trong ví dụ là 101 người) thì phải chạy goal seek nhiều lần.
Kết hợp với Macro: Để chạy Goal Seek cho hàng loạt nhân viên bạn nên sử dụng macro để thực hiện
Sau đây là câu lệnh macro bạn có thể áp dụng trong trường hợp này.
Sub GoalSeek()
Dim i As Integer
Dim x As String
Dim y As String
For i = 3 To 105 --(số dòng nhân viên cần tính mức lương kinh doanh)
x = "U" & i --- Cột U:
y = "F" & i ----Cột F: lương kinh doanh
Range(x).GoalSeek Goal:=0, ChangingCell:=Range(y)
Next i
End Sub
Sau đó, bạn chỉ cần chạy Macro, excel sẽ tính toán hàng loạt mức lương kinh doanh cho các nhân viên trong bảng.
Dữ liệu trước khi chạy. (giá trị cột U chưa bằng không)
Dữ liệu sau khi chạy (giá trị cột U đã bằng 0)
Tham khảo file excel dữ liệu trước khi chạy và sau khi chạy Macro đính kèm.
Hôm này mình xin giới thiệu với các bạn công cụ Goal seek trong Excel kết hợp với Macro để tính ra mức lương Gross.
Trước hết xin giới thiệu tính năng goal seek trong excel:
Goal seek (Hàm mục tiêu) thường được áp dụng trong các bài toán như tính ngược lại mức lương Gross khi biết trước mức lương Net.
Goal Seek hoạt động bằng cách sử dụng phương pháp lặp đi lặp lại (iterative method) để tìm ra lời giải. Nghĩa là, Goal Seek sẽ thử giá trị ban đầu của biến để xem nó có tạo ra kết quả mà bạn muốn hay không. Nếu không, Goal Seek sẽ thử tiếp với những giá trị khác nhau, cho đến khi nó tìm ra lời giải, hay nói cách khác, cho đến khi nào kết quả mà nó tìm được gần giống với kết quả của bạn muốn nhất.
LƯƠNG NET = LƯƠNG GROSS - (BHXH + BHYT + BHTN + KPCĐ+THUẾ TNCN)
Trong đó:
- Lương Net là số đã cho trước
- Còn BHXH (8%), BHYT(1.5%), BHTN (1%), KPCĐ (1%), Thuế TNCN lại được tính dựa trên Lương Gross.
Giả sử lương Net cua Ông Phạm Văn An là : 43.000.000 đồng/ tháng.
Mức lương đóng bảo hiểm của ông An là: 14.440.000 đồng/ tháng. Tổng Lương (Gross) của ông An = Lương đóng bảo hiểm (14.440.000) + lương kinh doanh. Số người phụ thuộc của ông An là 1 người. Thuế thu nhập cá nhân tính trên tổng mức lương của Ông An.
Yêu cầu bài toán là tính ra lương kinh doanh của ông An là bao nhiêu để lương Net của ông An đúng bằng 43.000.000
Hay nói cách khác là cần tính ra Lương kinh doanh của ông A bằng bao nhiêu để: LƯƠNG GROSS - (BHXH + BHYT + BHTN + KPCĐ+THUẾ TNCN)- LƯƠNG NET= 0 (cột U)
Khi bạn thiết lập một bảng tính để sử dụng Goal Seek, bạn thường có một công thức trong một ô, và các biến cho công thức này (với một giá trị ban đầu) trong những ô khác. Công thức có thể có nhiều biến, nhưng Goal Seek chỉ cho phép bạn xử lý mỗi lần một biến mà thôi.
Chạy Goal Seek
Thiết lập một ô làm ô thay đổi (changing cell: Lương kinh doanh, cột F). Đây là giá trị mà Goal Seek sẽ xử lý lặp đi lặp lại để cố gắng tìm ra giá trị mà khi đó cột U=0
Như vậy sau khi bạn chạy goal seek thì excel tính ra được mức lương kinh doanh của ông A là : 35.640.800
Tuy nhiên, mỗi lần chạy Goal seek, excel chỉ chạy được có một dòng dữ liệu. Muốn tính cho hàng loạt nhân viên (hàng trăm nhân viên, trong ví dụ là 101 người) thì phải chạy goal seek nhiều lần.
Kết hợp với Macro: Để chạy Goal Seek cho hàng loạt nhân viên bạn nên sử dụng macro để thực hiện
Sau đây là câu lệnh macro bạn có thể áp dụng trong trường hợp này.
Sub GoalSeek()
Dim i As Integer
Dim x As String
Dim y As String
For i = 3 To 105 --(số dòng nhân viên cần tính mức lương kinh doanh)
x = "U" & i --- Cột U:
y = "F" & i ----Cột F: lương kinh doanh
Range(x).GoalSeek Goal:=0, ChangingCell:=Range(y)
Next i
End Sub
Sau đó, bạn chỉ cần chạy Macro, excel sẽ tính toán hàng loạt mức lương kinh doanh cho các nhân viên trong bảng.
Dữ liệu trước khi chạy. (giá trị cột U chưa bằng không)
Dữ liệu sau khi chạy (giá trị cột U đã bằng 0)
Tham khảo file excel dữ liệu trước khi chạy và sau khi chạy Macro đính kèm.
File đính kèm
Lần chỉnh sửa cuối:
