麻辣家族討論版版 - Powered by Discuz! Board

標題: 奬金要如何用vba計算呢 [打印本頁]

作者: sillykin 時間: 2013-7-18 23:20 標題: 如何計算

A1為單位 A2為職稱 A3為目標額 A4為達成額 A5為比率計算公式為((A4/A3)-100)*75
:
:
:
A300

筆數約為300筆(如何代入公式VBA)

如何用VBA寫成呢???

作者: c_c_lai 時間: 2013-7-19 08:51

回復 3# sillykin
你把檔案t傳到哪裡去了？
要點選附件(迴紋針)才行！
Excel 的檔案要先壓縮成 Winrar 的 .rar、或 Winzip 的 .zip。

作者: sillykin 時間: 2013-7-19 21:56

回復 5# sillykin

檔案巳上傳...請大大幫忙

作者: sillykin 時間: 2013-7-20 09:38 標題: 奬金要如何用vba計算呢

奬金要如何用vba計算呢??
在檔案裡有相關的條件..請參閱

作者: GBKEE 時間: 2013-7-20 14:24

本帖最後由 GBKEE 於 2013-7-21 05:30 編輯

回復 1# sillykin
試試看

Option Explicit
Sub Ex()
Dim I As Integer
With Sheets("＆＆單位成績總表＆＆").Range("A3")
Do While .Offset(I) <> ""
獎金程式 .Offset(I), .Offset(I, 2), .Offset(I, 4)
I = I + 1
Loop
End With
End Sub
Private Sub 獎金程式(單位 As Range, 職稱 As String, 達成額 As Integer)
Dim Ar(1 To 2), M, 責任額 As Integer, 達成百分比 As Single, 獎金 As Integer, 副總獎金 As Integer
' ****(條件工作表中) 沒有的職稱 -> 高級專員, 權理經理 ***
If 單位 Like "管理*" Then
'管理單位: 二等襄理, 一等襄理, 副經理, 經理
'責任額 : 600 , 700 , 800 , 900
Ar(1) = Array("二等襄理", "一等襄理", "副經理", "經理") '***管理單位 :沒有的職稱 ,副總經理
Ar(2) = Array(300, 600, 700, 800, 900, 2500) '陣列(責任額) :
Else
'營業單位: 二等襄理, 一等襄理, 副經理, ( 協理、經理) 副總經理
'責任額 : 1500 , 2000, 2200, (註1) , 2,500
Ar(1) = Array("二等襄理", "一等襄理", "副經理", "協理", "經理", "副總經理")
Ar(2) = Array(500, 1500, 2000, 2200, "(註1)", "(註1)", 2500) '陣列(責任額) :
End If
M = Application.Match(職稱, Ar(1), 0) 'M > 0 為幹部
副總獎金 = 0
If IsError(M) Then
'***** Match不到職稱時, M = 錯誤值 -> 非幹部**********
責任額 = Ar(2)(0) '非幹部責任額: 300 或 500
If 單位 Like "管理*" Then '管理單位
'陣列:管理單位非幹部 (獎懲獎金表)
Ar(2) = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 500, 55)
' , , <3% , <4%, <5%, <6%, <7%, <%8, <9% , <10%,>=10%,每1%.
Else '營業單位
'陣列:營業單位非幹部 (獎懲獎金表)
Ar(2) = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 800, 75)
'0, 0, <3% , <4%, <5%, <6%, <7%, <%8, <9% , <10%,>=10%,每1%.
End If
'***** Match不到職稱時, M = 錯誤值 -> 非幹部 **********
Else
'**************幹部 ************************************
If M = UBound(Ar(1)) + 1 Then 副總獎金 = 2000 '副總達到獎金會再多2000元
責任額 = Ar(2)(M)
If 單位 Like "管理*" Then
'陣列:管理單位幹部 (獎懲獎金表)
Ar(2) = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 1000, 75)
' , , <3% , <4%, <5%, <6%, <7%, <%8, <9% , <10%,>=10%,每1%.
Else
'陣列:營業單位幹部 (獎懲獎金表)
Ar(2) = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 2000, 155)
' , , <3% , <4%, <5%, <6%, <7%, <%8, <9% , <10%,>=10%,每1%.
End If
'**************幹部 ************************************
End If
單位.Offset(, 3) = 責任額
達成百分比 = Round(達成額 / 責任額 * 100, 0)
單位.Offset(, 5) = 達成百分比
獎金 = 0
If 達成額 / 責任額 * 10 < 3 Then
M = 2
ElseIf 達成額 / 責任額 * 10 > 10 Then
M = 10
獎金 = Ar(2)(UBound(Ar(2)))
Else
M = Int(達成額 / 責任額 * 10)
End If
'Ar(2)(M) = 獎懲的金額
單位.Offset(, 6) = Ar(2)(M) + ((達成百分比 - 100) * 獎金) + 副總獎金
'獎金計算公式: 獎懲的金額+ ((達成100%以上-100)* 獎金) + 副總獎金
End Sub

複製代碼

作者: GBKEE 時間: 2013-7-20 15:56

回復 10# sillykin
如圖中: 貼在同一模組裡都可(因條件已寫死)

[attach]15507[/attach]

作者: sillykin 時間: 2013-7-20 16:24

大大在請教...
如何在逹成比率（％）算出來為四捨五入為整數
例如：320.55555
答案：321

作者: sillykin 時間: 2013-7-20 16:25

回復 12# sillykin

測試結果..奬金都不太對

作者: GBKEE 時間: 2013-7-20 16:49

回復 13# sillykin
請修改如下

單位.Offset(, 3) = 責任額
達成百分比 = Round(達成額 / 責任額 * 100, 0)

複製代碼

獎金的計算公式為何???
不是這樣嗎->= 獎懲獎金 + ((達成率100%以上-100)* 獎金) + 副總獎金
達成率未達100%以上是沒獎金的(達成率100%以上-100)* 獎金
達成率有達100%,獎懲獎金才會為正值
你的公式 (ROUND(F7-100,0)*155+2000) -> 獎懲獎金(一律都有) +2000

作者: sillykin 時間: 2013-7-20 17:17

回復 14# GBKEE

謝謝大大的協助..檢附檔案測試的結果..發生J3:J17跟奬金不合...能否幫解決..謝謝

作者: sillykin 時間: 2013-7-20 18:02

回復 15# sillykin

區分                      營業單位       管理單位
                     幹部非幹部    幹部    非幹部
30%未滿者 -25,000 -10,000 -25,000 -10,000
30%以上~40%未滿者 -20,000 -7,000 -20,000 -7,000
40%以上~50%未滿者 -16,000 -5,000 -16,000 -5,000
50%以上~60%未滿者 -12,000 -4,000 -12,000 -4,000
60%以上~70%未滿者 -8,000 -3,000 -8,000 -3,000
70%以上~80%未滿者 -6,000 -2,000 -6,000 -2,000
80%以上~90%未滿者 -4,000 -1,000 -4,000 -1,000
90%以上~100%未滿者 -2,000 -500 -2,000 -500
達成100%                2,000 800    1,000    500
逾100%以上          每1%×155 每1%×75    每1%× 75每1%× 55

只有達到100%才會有加額外的奬金...低於100%以下依照未滿比率扣奬金(但是要分營業單位或管理單位)(幹部或非幹部)

作者: sillykin 時間: 2013-7-20 20:47

回復 16# sillykin

如果逹成比率（％）這欄位格由儲存格自己用函數去算,,,只求奬金部份即可...是否可行..感謝大大在指教一下..感恩

作者: GBKEE 時間: 2013-7-20 21:09

回復 16# sillykin
程式已修改請重新下載8# 程式碼
有錯誤請看圖

[attach]15510[/attach]

作者: sillykin 時間: 2013-7-20 21:47

回復 19# sillykin

不好意思我使用的版本是ecxel2000
執行結果為圖片顯示"未定義"

作者: sillykin 時間: 2013-7-20 23:06

回復 20# sillykin

大大你好..上述的問題都巳經解決了..在請教一個小問題

假如"營業單位"中--分為12個小單位-如 :單位1、單位2、單位3、單位4、單位5.....、單位12
"管理單位"中---分為8個小單位

在這種情形要如何去處理呢??
VBA要如何去判斷呢???

作者: GBKEE 時間: 2013-7-21 05:32

回復 21# sillykin
請上傳檔案看看

作者: sillykin 時間: 2013-7-21 10:04

回復 22# GBKEE

請大大查閱...

作者: GBKEE 時間: 2013-7-21 18:03

回復 23# sillykin

Option Explicit
Sub Ex()
Dim I As Integer
With Sheets("＆＆單位成績總表＆＆").Range("A3")
Do While .Offset(I) <> ""
獎金程式 .Offset(I), .Offset(I, 2), .Offset(I, 4)
I = I + 1
Loop
End With
End Sub
Private Sub 獎金程式(單位 As Range, 職稱 As String, 達成額 As Integer)
Dim Ar, 責任額 As Integer, 達成百分比 As Single, 獎金 As Integer, 副總獎金 As Integer
' ****(條件工作表中) 沒有的職稱 -> 高級專員, 權理經理 ***
Dim 部門 As String, 稱呼 As String, M As Integer, A As Integer
' *******LTrim、RTrim 與 Trim 函數傳回一個沒有前頭空白 (LTrim)、後面空白 (RTrim)***************************
'或前後均無空白的Variant (String)，其中所含為給定的字串。*
稱呼 = Trim(單位.Offset(, 2)) '傳回職稱前後均無空白的Variant (String) *
'************************************************************************************************************
單位.Offset(, 7) = Application.Match(稱呼, Array(0, "副總經理", "協理", "高級專員", "經理", _
"權理經理", "副經理", "副理", "一等襄理", "二等襄理", "業務專員", "辦事員", "助理員", "雇員", "服務生"), 0)
'************************************************************************************************************
'************************************************************************************************************
部門 = Replace(單位, "單位", "") '去掉 "單位" 傳回數字或文字 *
If Mid(部門, 1, 1) Like "[0-9]" Then 部門 = "營業部門" '為數字->營業部門 *
責任額 = IIf(部門 = "營業部門", 500, 300) '預設為非幹部的責任額 *
副總獎金 = 0 '預設為副總獎金為 0 *
'************************************************************************************************************
If 部門 = "營業部門" Then
Ar = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 800, 75) '非幹部(獎懲獎金)
If 單位.Offset(, 7) <= 10 Then
Ar = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 2000, 155) '幹部 (獎懲獎金)
M = Application.Match(稱呼, Array("高級專員", "二等襄理", "一等襄理", "副經理", "協理", "經理", "副總經理"), 0)
'M = > Match 的數字
責任額 = Application.Index(Array(100, 1500, 2000, 2200, "(註1)", "(註1)", 2500), M) '責任額陣列(高級專員->100)
'Index 傳回一個表格或範圍內的某個值或參照位址。
副總獎金 = IIf(稱呼 = "副總經理", 2000, 0)
End If
Else
部門 = "管理部門"
Ar = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 500, 55) '非幹部(獎懲獎金)
If 單位.Offset(, 7) <= 10 Then
Ar = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 1000, 75) '幹部 (獎懲獎金)
M = Application.Match(稱呼, Array("高級專員", "二等襄理", "一等襄理", "副經理", "經理"), 0) '***管理單位 :沒有的職稱 ,副總經理
責任額 = Application.Index(Array(100, 600, 700, 800, 900, 2500), M) '責任額陣列(高級專員->100)
'管理部門沒有副總經理 -> ' 副總獎金 = IIf(稱呼 = "副總經理", 2000, 0)
End If
End If
單位.Offset(, 3) = 責任額
'*************************************** F欄 (達成比率％)用公式有兩式可用*******************************
單位.Offset(, 5) = "=Round(RC[-1] / RC[-2] * 100, 0)" 'R1C1表示法
'單位.Offset(, 5) = "=Round(" & 單位.Offset(, 4).Address & " / " & 單位.Offset(, 3).Address & "* 100, 0)"
'*******************************************************************************************************
達成百分比 = Round(達成額 / 責任額 * 100, 0)
'單位.Offset(, 5) = 達成百分比
獎金 = 0
If 達成額 / 責任額 * 10 < 3 Then
M = 2
ElseIf 達成額 / 責任額 * 10 > 10 Then
M = 10
獎金 = Ar(UBound(Ar))
Else
M = Int(達成額 / 責任額 * 10)
End If
單位.Offset(, 6) = Ar(M) + ((達成百分比 - 100) * 獎金) + 副總獎金 'Ar(M) = 獎懲的金額
'獎金計算公式: = 獎懲的金額+ ((達成100%以上-100)* 獎金) + 副總獎金
End Sub

複製代碼

作者: sillykin 時間: 2013-7-21 21:09

回復 24# GBKEE

謝謝大大的回覆..由衷感謝板主的幫忙
單位方面有點寫錯..
營業單位應為 :萬里分部、金山分部、石門分部、三之分部、義山分部、水碓分部、營業部、英專分部、竹圍分部、龍形分部、八里分部、板橋分部、桃園分部、代理部、出納部、業務部、理財部
管理分部應為 :審查部、法務部、會計部、總務部、行政部、信用部、企劃部、教育部、人事部

不好意思..製造板主很多的問題 ,身感抱歉

作者: GBKEE 時間: 2013-7-22 06:54

回復 24# sillykin

Option Explicit
Const 管理部門 = "審查部、法務部、會計部、總務部、行政部、信用部、企劃部、教育部、人事部"
Const 職務別 = "0,副總經理,協理,高級專員,經理,權理經理,副經理,副理,一等襄理,二等襄理,業務專員,辦事員,助理員,雇員,服務生"
Sub Ex()
Dim I As Integer
With Sheets("＆＆單位成績總表＆＆").Range("A3")
Do While .Offset(I) <> ""
獎金程式 .Offset(I), .Offset(I, 2), .Offset(I, 4)
I = I + 1
Loop
End With
End Sub
Private Sub 獎金程式(單位 As Range, 職稱 As String, 達成額 As Integer)
' ****(條件工作表中) 沒有的職稱 -> 高級專員, 權理經理 ***
Dim Ar, 責任額 As Integer, 達成百分比 As Single, 獎金 As Integer, 副總獎金 As Integer
Dim 部門 As String, 職稱代號 As String, M As Integer '
'LTrim、RTrim 與 Trim 函數傳回一個沒有前頭空白 (LTrim)、後面空白 (RTrim)或前後均無空白的Variant (String)，其中所含為給定的字串。*
職稱代號 = Trim(單位.Offset(, 2)) '傳回職稱代號前後均無空白的Variant (String)
'Split 函數傳回一個陳列索引從零開始的一維陣列，它包含指定數目的子字串。
單位.Offset(, 7) = Application.Match(職稱代號, Split(職務別, ","), 0) 'Match 職稱代號在職務別以Split分割出的陣列中的位置
'InStr 函數傳回在某字串中一字串的最先出現位置，此位置為 Variant (Long)。
部門 = IIf(InStr(管理部門, 單位), "管理部門", "營業部門") 'InStr傳回0->沒找到="營業部門"
副總獎金 = 0 '預設為副總獎金為 0
責任額 = IIf(部門 = "管理部門", 300, 500)
If 部門 = "營業部門" Then
Ar = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 800, 75) '非幹部(獎懲獎金)
If 單位.Offset(, 7) <= 10 Then
Ar = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 2000, 155) '幹部 (獎懲獎金)
M = Application.Match(職稱代號, Array("高級專員", "二等襄理", "一等襄理", "副經理", "協理", "經理", "副總經理"), 0)
'M = > Match 的數字
責任額 = Application.Index(Array(500, 1500, 2000, 2200, "(註1)", "(註1)", 2500), M) '責任額陣列(高級專員->100)
'Index 傳回一個表格或範圍內的某個值或參照位址。
副總獎金 = IIf(職稱代號 = "副總經理", 2000, 0)
End If
Else ' 部門 => "管理部門"
Ar = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 500, 55) '非幹部(獎懲獎金)
If 單位.Offset(, 7) <= 10 Then
Ar = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 1000, 75) '幹部 (獎懲獎金)
M = Application.Match(職稱代號, Array("高級專員", "二等襄理", "一等襄理", "副經理", "經理"), 0)
'***沒有的職稱,[副總經理],管理單位如有副總經理(程式執行會產生錯誤)*****************************
責任額 = Application.Index(Array(100, 600, 700, 800, 900, 2500), M) '責任額陣列(高級專員->100)
'管理部門沒有副總經理 -> ' 副總獎金 = IIf(職稱代號 = "副總經理", 2000, 0)
End If
End If
單位.Offset(, 3) = 責任額
'*************************************** F欄 (達成比率％)用公式有兩式可用*******************************
單位.Offset(, 5) = "=Round(RC[-1] / RC[-2] * 100, 0)" 'R1C1表示法
'單位.Offset(, 5) = "=Round(" & 單位.Offset(, 4).Address & " / " & 單位.Offset(, 3).Address & "* 100, 0)"
'*******************************************************************************************************
達成百分比 = Round(達成額 / 責任額 * 100, 0)
'單位.Offset(, 5) = 達成百分比
獎金 = 0
If 達成額 / 責任額 * 10 < 3 Then
M = 2
ElseIf 達成額 / 責任額 * 10 > 10 Then
M = 10
獎金 = Ar(UBound(Ar))
Else
M = Int(達成額 / 責任額 * 10)
End If
單位.Offset(, 6) = Ar(M) + ((達成百分比 - 100) * 獎金) + 副總獎金 'Ar(M) = 獎懲的金額
'獎金計算公式: = 獎懲的金額+ ((達成100%以上-100)* 獎金) + 副總獎金
End Sub

複製代碼

作者: sillykin 時間: 2013-7-22 12:38

回復 25# GBKEE

大大不好意思..在打擾一下

營業單位應為
萬里分部、金山分部、石門分部、三之分部、義山分部、水碓分部、營業部、英專分部、竹圍分部、龍形分部、八里分部、板橋分部、桃園分部、代理部、出納部、業務部、理財部管理分部應為
審查部、法務部、會計部、總務部、行政部、信用部、企劃部、教育部、人事部

管理部有在vba
但營業單位沒有進到vba????

作者: GBKEE 時間: 2013-7-22 14:05

回復 27# sillykin
不在管理部門的職稱會是在"營業部門"的

Const 管理部門 = "審查部、法務部、會計部、總務部、行政部、信用部、企劃部、教育部、人事部"
部門 = IIf(InStr(管理部門, 單位), "管理部門", "營業部門") 'InStr傳回 0->沒找到="營業部門"

複製代碼

為何會沒有vba會找不到"營業部門" 請上傳檔案程式碼看看

作者: sillykin 時間: 2013-7-22 22:19

本帖最後由 sillykin 於 2013-7-22 22:20 編輯

依回覆22檔案
如果條件欄位不動..
但隨時可去條件欄位更動
責任額、達成額計算標準、幹部、非幹部獎懲標準表
是否可行呢???
因為直接去VBA去更動有點冒險，直接在條件儲存格去更動數字，比較不會犯錯．

板主不好意思．．幾日來製造你很多問題．身感抱歉

作者: sillykin 時間: 2013-7-23 00:10

回復 30# sillykin

Option Explicit
Const 管理部門 = "審查室、法務室、會計室、總務室、行政管理室、徵信室、企劃室、教育發展室、資訊室"
Const 職務別 = "0,副總經理,協理,高級專員,經理,權理經理,副經理,副理,一等襄理,二等襄理,業務專員,辦事員,助理員,雇員,服務生"
Sub Ex()
Dim I As Integer
With Sheets("＆＆單位成績總表＆＆").Range("A3")
      Do While .Offset(I) <> ""
         獎金程式 .Offset(I), .Offset(I, 2), .Offset(I, 4)
         I = I + 1
      Loop
End With
End Sub

Private Sub 獎金程式(單位 As Range, 職稱 As String, 達成額 As Integer)
'  ****(條件工作表中) 沒有的職稱 ->  高級專員, 權理經理  ***
Dim Ar, 責任額 As Integer, 達成百分比 As Single, 獎金 As Integer, 副總獎金 As Integer
Dim 部門 As String, 職稱代號 As String, M As Integer '
'LTrim、RTrim 與 Trim 函數  傳回一個沒有前頭空白 (LTrim)、後面空白 (RTrim)或前後均無空白的Variant (String)，其中所含為給定的字串。*
職稱代號 = Trim(單位.Offset(, 2))                                                 '傳回職稱代號前後均無空白的Variant (String)
'Split 函數傳回一個陳列索引從零開始的一維陣列，它包含指定數目的子字串。
單位.Offset(, 7) = Application.Match(職稱代號, Split(職務別, ","), 0)             'Match 職稱代號在職務別以Split分割出的陣列中的位置
'InStr 函數傳回在某字串中一字串的最先出現位置，此位置為 Variant (Long)。
部門 = IIf(InStr(管理部門, 單位), "管理部門", "營業部門")             'InStr傳回0->沒找到="營業部門"
副總獎金 = 0                                                       '預設為副總獎金為 0
責任額 = IIf(部門 = "管理部門", 300, 500)
If 部門 = "營業部門" Then
      Ar = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 800, 75)          '非幹部(獎懲獎金)
      If 單位.Offset(, 7) <= 10 Then
         Ar = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 2000, 155) '幹部  (獎懲獎金)
         M = Application.Match(職稱代號, Array("高級專員", "二等襄理", "一等襄理", "副經理", "協理", "經理", "副總經理"), 0)
         'M = > Match 的數字
         責任額 = Application.Index(Array(500, 1500, 2000, 2200, "(註1)", "(註1)", 2500), M)  '責任額陣列(高級專員->100)
         'Index 傳回一個表格或範圍內的某個值或參照位址。
         副總獎金 = IIf(職稱代號 = "副總經理", 2000, 0)
      End If
Else    ' 部門 => "管理部門"
      Ar = Array(0, 0, -10000, -7000, -5000, -4000, -3000, -2000, -1000, -500, 500, 55)  '非幹部(獎懲獎金)
      If 單位.Offset(, 7) <= 10 Then
         Ar = Array(0, 0, -25000, -20000, -16000, -12000, -8000, -6000, -4000, -2000, 1000, 75) '幹部  (獎懲獎金)
         M = Application.Match(職稱代號, Array("高級專員", "二等襄理", "一等襄理", "副經理", "經理"), 0)
         '***沒有的職稱,[副總經理],管理單位如有副總經理(程式執行會產生錯誤)*****************************
         責任額 = Application.Index(Array(100, 600, 700, 800, 900, 2500), M)                      '責任額陣列(高級專員->100)
         '管理部門沒有副總經理 -> ' 副總獎金 = IIf(職稱代號  = "副總經理", 2000, 0)
      End If
End If
單位.Offset(, 3) = 責任額
'*************************************** F欄 (達成比率％)用公式有兩式可用*******************************
單位.Offset(, 5) = "=Round(RC[-1] / RC[-2] * 100, 0)"             'R1C1表示法
'單位.Offset(, 5) = "=Round(" & 單位.Offset(, 4).Address & " / " & 單位.Offset(, 3).Address & "* 100, 0)"
'*******************************************************************************************************
達成百分比 = Round(達成額 / 責任額 * 100, 0)
'單位.Offset(, 5) = 達成百分比
獎金 = 0
If 達成額 / 責任額 * 10 < 3 Then
      M = 2
ElseIf 達成額 / 責任額 * 10 > 10 Then
      M = 10
      獎金 = Ar(UBound(Ar))
Else
      M = Int(達成額 / 責任額 * 10)
End If
單位.Offset(, 6) = Ar(M) + ((達成百分比 - 100) * 獎金) + 副總獎金 'Ar(M) = 獎懲的金額
'獎金計算公式: = 獎懲的金額+  ((達成100%以上-100)* 獎金) + 副總獎金
End Sub
執行出來會發生錯誤

作者: GBKEE 時間: 2013-7-23 10:15

本帖最後由 GBKEE 於 2013-7-23 10:53 編輯

回復 31# sillykin
沒有檔案無法偵錯

30# 試試看

[attach]15527[/attach]

作者: GBKEE 時間: 2013-7-23 10:50

回復 33# sillykin

[attach]15528[/attach]

Option Explicit
Sub Ex()
Dim I As Integer
With Sheets("單位成績總表") '*** 請修改一下工作表的名稱 ***
If Application.CountA(.[A:A]) <> .Cells(.Rows.Count, "A").End(xlUp).Row Then
MsgBox "單位別欗資料不全"
End
End If
Do While .Range("A3").Offset(I) <> ""
獎金程式 .Range("A3").Offset(I)
I = I + 1
Loop
End With
End Sub
Private Sub 獎金程式(單位 As Range)
Dim 部門 As String, 職稱代號 As Variant, 職稱 As String, 非幹部 As Integer, Col As String, M
Dim 責任額 As Integer, 達成額 As Long, 獎金 As Integer, 副總獎金 As Integer
With Sheets("條件") '工作表
部門 = IIf(IsError(Application.Match(單位, .Range("E2", .Range("E2").End(xlDown)), 0)), "營業單位", "管理單位")
職稱 = 單位.Offset(, 2)
職稱代號 = Application.Match(職稱, .Range("A:A"), 0) '尋找職稱的列位
If IsError(職稱代號) Then
MsgBox IIf(職稱 = "", "職稱沒有輸入!! ", "職稱 : " & 職稱 & " 找不到!!!"), , 部門
End
End If
非幹部 = .Range("B" & Application.Match("非幹部", .Range("C:C"), 0))
'C欗尋找第1 個"非幹部"的列位帶入B欄中得到"非幹部"的代號
'E欄管理單位範圍裡尋找部門的列位 : 沒找到為 "營業單位" ,找到為"管理單位"
If 職稱代號 >= 非幹部 Then
責任額 = IIf(部門 = "營業單位", .Range("H2"), .Range("J2")) '可在 H2,J2 修改非幹部的責任額
Col = IIf(部門 = "營業單位", "Q", "S") '非幹部獎懲標準表挌 : 在"Q欗" 或 "S欗"
Else
Col = IIf(部門 = "營業單位", "G:G", "I:I") '幹部責任額表挌 : 在"G:G欗" 或 "I:I欗"
M = Application.Match(職稱, .Range(Col), 0) '尋找幹部責任額的職稱位置
If IsError(M) Then
MsgBox 部門 & " 中沒有 " & 職稱, , 部門
End
End If
責任額 = .Range(Col).Cells(M).Offset(, 1) '幹部的責任額
Col = IIf(部門 = "營業單位", "P", "R") '非幹部獎懲標準表挌 : 在"P欗" 或 "R欗"
End If
單位.Offset(, 3) = 責任額
達成額 = 單位.Offset(, 4)
達成百分比 = Round(達成額 / 責任額 * 100, 0)
單位.Offset(, 5) = 達成百分比
單位.Offset(, 7) = 職稱代號
If 達成額 / 責任額 * 10 < 3 Then
M = 3
ElseIf 達成額 / 責任額 * 10 > 10 Then
M = 11
獎金 = .Range(Col & 12)
副總獎金 = IIf(職稱 = "副總經理", 2000, 0) '副總達到獎金會在多2000元
Else
M = Int(達成額 / 責任額 * 10) + 1
End If
'獎懲的金額 = .Range(Col & M)
'獎金計算公式: 獎懲的金額+ ((達成100%以上-100)* 獎金) + 副總獎金
'下式為在獎金欗上輸入公式
單位.Offset(, 6) = "=" & .Range(Col & M) & " + ((" & 達成百分比 & " - 100) *" & 獎金 & ") +" & 副總獎金
'****************************************************************************************
'單位.Offset(, 6) = .Range(Col & M) + ((達成百分比 - 100) * 獎金) + 副總獎金 '直接給數值
End With
End Sub

複製代碼

作者: sillykin 時間: 2013-7-26 21:31

回復 35# sillykin

如回覆31所檢附檔案
大大不好意思...想在請問...三個問題
第一問題
營業部上限最多奬金150,000元
管理部上限最多奬金120,000元
第二問題
假設
資訊室是一個獨立單位..且奬金是另外制定
例如
責任額100
奬金計算方式
達成額130
達成比率130%
達成100%加發250元
計算方式為(達成額/責任額)*100 = 逹成比率（％） ==>奬金計算為((逹成比率（％） - 100)*達成100每一點加發5元)+達成100%加發250元

計算方式為(130/100)*100 = 130 ==>奬金計算為((130 - 100)*5)+250 = 400元
是否能在條件sheet中加設欄位值；方便更正
營業部上限最多奬金
管理部上限最多奬金
及資訊定控制項目
請大大指教

作者: GBKEE 時間: 2013-7-27 07:35

本帖最後由 GBKEE 於 2013-7-27 07:53 編輯

回復 36# sillykin
資訊室是一個獨立單位
1有無幹部,非幹部之分.
2有無未達100%的獎懲.
3有無上限奬金

作者: sillykin 時間: 2013-7-29 21:03

本帖最後由 sillykin 於 2013-7-29 21:08 編輯

回復 37# GBKEE

條件如圖

例如
責任額100
奬金計算方式
達成額130
達成比率130%
達成100%加發250元
計算方式為(達成額/責任額)*100 = 逹成比率（％） ==>奬金計算為((逹成比率（％） - 100)*達成100每一點加發5元)+達成100%加發250元

非幹部計算方式為(130/100)*100 = 130 ==>奬金計算為((130 - 100)*5)+250 = 400元
幹部計算方式為(400/200)*100 = 200 ==>奬金計算為((200 - 100)*5)+500= 1,000元
是否能在條件sheet中加設欄位值；方便更正
營業部上限最多奬金150,000元
管理部上限最多奬金120,000元
資訊部上限最多奬金100,000元
及資訊定控制項目
請大大指教

作者: GBKEE 時間: 2013-7-30 09:13

回復 38# sillykin

[attach]15633[/attach]

作者: sillykin 時間: 2013-7-30 14:50

回復 40# sillykin

大大不好意思，請問小問題
營業單位責任額不一定為500，有少數人員會更動
能否不去判斷責任額
還有營業單位上限為150，000
管理單位上限為120，000
資訊單位上限為100，000
能否解決呢，謝謝

作者: GBKEE 時間: 2013-7-30 15:10

回復 41# sillykin
營業單位責任額不一定為500，有少數人員會更動少數人是哪些人,要有名單可參考

程式碼要了解一下,這樣是不會進步的

'獎金上限 = IIf(部門 = "營業單位", 15000, 12000)
獎金上限 = IIf(部門 = "營業單位", 15000, IIf(部門 = "管理單位", 12000, 10000))

複製代碼

作者: sillykin 時間: 2013-7-30 17:01

回復 42# GBKEE

謝謝大指教，我會努力學習
責任額能否從總表去抓責任額，總表責任額由人工自行輸入

作者: sillykin 時間: 2013-7-30 21:25

回復 43# sillykin

獎金上限 = IIf(部門 = "營業單位", 150000, IIf(部門 = "管理單位", 120000, 100000))
我把奬金上限更改為150,000 ; 120,000 ; 100,000
為何會產生溢位情形????

作者: luhpro 時間: 2013-8-1 00:11

回復 sillykin

獎金上限 = IIf(部門 = "營業單位", 150000, IIf(部門 = "管理單位", 120000, 10 ...
sillykin 發表於 2013-7-30 21:25

有溢位是正常的啊.

請參看 ExcelVBA 說明中的 "資料型態摘要"
裡面有說到 :

Integer 佔2 個位元組, 可表示數字範圍從 -32,768 到 32,767
而 Long(long integer) 則佔 4 個位元組, 可表示數字範圍從 -2,147,483,648 到 2,147,483,647

所以你只要把程式中 Dim 後面有 As Integer 的改成 As Long 應該就可以了

作者: GBKEE 時間: 2013-8-1 15:42

回復 44# sillykin
對不起眼花了, 42帖; 金額數字少了1個0 獎金上限 As Integer 要改成如luhpro說獎金上限 As Long

作者: sillykin 時間: 2013-8-12 23:40

回復 38# GBKEE

謝謝大大的回覆..感謝

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)