excel每二欄位數加總如何修改公式?【求助】



贊助商連結


頁 : [1] 2

batty
2002-09-04, 08:31 PM
A B C D E F G H I J
3 月份職稱 A業績 B業績 A業績 B業績 業績總額 業績總額
4 姓名 9107 9108 9107 9107+ 9108 9108+ 9107 9108
5 A 1 2 10 100 5 50 100 5


問題:
每二欄位數加總,將I5公式設好拖曳至J5(J5必須先搜尋D5職稱再計算,計算應為G5與H5欄)如何修改公式?

I5已設定公式
"I5公式=IF(C5=1,F5,IF(C5=2,F5,IF(C5=3,SUM(E5:F5),IF(C5=4,SUM(E5:F5),IF(C5=5,0)*VLOOKUP($B$5,$B$4:$H$5,MATCH(I4,$B$4:$H$4,0),0)))))"

拖曳至J5公式變成
"J5公式=IF(D5=1,G5,IF(D5=2,G5,IF(D5=3,SUM(F5:G5),IF(D5=4,SUM(F5:G5),IF(D5=5,0)*VLOOKUP($B$5,$B$4:$H$5,MATCH(J4,$B$4:$H$4,0),0)))))"


[/IMG]
[/IMG]

贊助商連結


pasca
2002-09-04, 11:24 PM
不像是新手上路的感覺

沙拉油
2002-09-05, 01:20 AM
先談談I5這個公式吧!
在這個公式內,當C5=5這一段
IF(C5=5,0)*VLOOKUP($B$5,$B$4:$H$5,MATCH(I4,$B$4:$H$4,0),0)
在這段內有個疑問,當C5=5會傳回0,否則會傳回 False
所以不管是傳回0或False再乘以VLOOKUP所找到的傳回的值,這個結果一定都是0(除非VLOOKUP傳回的是錯誤值)
所以你有必要VLOOKUP($B$5,$B$4:$H$5,MATCH(I4,$B$4:$H$4,0),0)這一段嗎?

另外你的工作表欄位排列是否如下圖?

http://home.pchome.com.tw/mysite/ahho_chang/images/excel4bt.jpg

batty
2002-09-06, 12:47 AM
沙拉油高手:
由於此討論區功能我不太會用,可否煩請您進入下列網址看看!謝謝。
[URL=http://home.pchome.com.tw/soho/9999wei/Page66.htm]

沙拉油
2002-09-06, 08:29 PM
先跟你講 M4 的公式 =CHOOSE(B4,OFFSET(B4,,COLUMN(H4)-2),OFFSET(B4,,COLUMN(H4)-2),SUM(OFFSET(B4,,COLUMN(H4)-3,,2)),SUM(OFFSET(B4,,COLUMN(H4)-3,,2)),0)
使用這個公式建議將 B4 設定資料驗證設定只能輸入1,2,3,4,5以避免M4出現錯誤值

因小弟對業務一蓋不懂,所以其他的待我先看看它們的意思是什麼再研究

沙拉油
2002-09-06, 08:51 PM
再來說P4的公式,在做這個公式前先定義一個名稱
先選擇插入>>名稱>>定義
現有名稱內輸入直展督導業績
參照到輸入=OFFSET($G$3,,,COUNTA($G$3:$G$65536),COLUMN($M$3)-COLUMN($G$3))
然後按新增再按關閉

然後在P4輸入公式=HLOOKUP(P$1,直展督導業績,ROW()-ROW(P$3)+1,0)


突然間想問,你這張表的月份會一直新增直到9112+對吧!?人數也會一直增加對吧?!
你一定得將這些資料放在同一張工作表嗎?

沙拉油
2002-09-06, 09:44 PM
R4,S4,T4,W4,X4,Y4,Z4 你得先用說明一下它的計算標準!
Q4,U4,V4 直接複製 P4 的公式貼過去就好(P4的公式要先遵從上一篇文章發表的步驟做好再複製)

batty
2002-09-07, 11:57 AM
沙拉油高手:
在此謝謝您!佔用您不少時間。您實在太厲害了。
今天看了您給予函數公式我套用進去已可以用了!我照您函數步驟再用一遍,但其中有些函數我還不是很清楚,等彙總好問題再請教您!)謝謝您!
下面是當月份獎金標準。(R4、S4、T4欄)

當月份獎金 標準
R直展津貼
職稱為1:
1~250,000
直展業績<=250,000為直展業績*30%
大於等於250,001
直展業績>=250,001為((直展業績-250,000*33%)+75,000))
職稱為2:
大於50,001
直展業績>=50,001為((直展業績*23%)+10,000))
1~50,000
直展業績<=50,000為直展業績*20%
職稱為3:
直展業績*20%
職稱為4:
與上3相同 
職稱為5:0
S督導津貼
職稱為1:
大於等於800,000
業績總額>=800,000為業績總額*2%     
小於800,000
業績總額<800,000為0
職稱為2:
大於等於150,000
業績總額>=150,000為業績總額*3。5%
小於150,000
業績總額<150,000為0

職稱為3:
督導業績*20%
職稱為4:
與上3相同
職稱為5:0
T超額獎金
職稱為1:
大於等於2,000,001
業績總額>=2,000,001 為((業績總額-2,000,000)*4。5%)+40,000)))
1,000,001~2,000,000
業績總額<=2,000,000 為(業績總額-1,000,000)*4%))
小於等於1,000,000     
業績總額<=1,000,000 為 0
職稱為2:
大於950,001
業績總額>=950,001為((業績總額-950,000)*15%)+76,000)))
750,001~950,000
業績總額>=750,001為((業績總額-750,000)*13%)+50,000)))
250,001~750,000
業績總額>=250,001為 業績總額-250,000)*10%
小於等於250,000
業績總額<=250,000為 0
職稱為3:
小於等於60,000
業績總額<=60,000為 0
60,001~210,000
業績總額<=210,000為((業績總額-60,000)*7%))    

大於等於210,001
業績總額>=210,001為((業績總額-210,000)*10%)+10,500)))
職稱為4:與上3相同
職稱為5:0  

batty
2002-09-07, 12:18 PM
收回部分標準
有些複雜,麻煩您了!謝謝。

收回部分我漏列二欄,增設二欄,為w與x欄,實際收回業績(直展業績)、(督導業績)為自行輸入


收回月份業績 實際收回業績 收回月份獎金
--------------------------------------------------------------------
(U)直展業績 (V)督導業績 (W)直展業績 (X)督導業績 (Y)直展津貼 (Z)督導津貼 (AA)超額獎金
------------------------------------------------------------------------
(U4)50,000 (W4)1,000 (Y4)-200
--------------------------------------------------------------------
50,000 50,000 -10,000

收回月份設9107職稱為3
直展津貼(設收回業績為1000)
當月已發獎金為50,000*20%=10, 000
更正後實際業績為50,000-1,000=49,000
更正後應發獎金為49,000*20%=9,800
收回月份獎金直展津貼則為9,800-10,000=-200

必須先算出9107扣除收回業績更正後實際業績,再以更正後實際業績運算更正實際應發獎金與已發獎金相減,則為需收回部分。(必須配合當時職稱)

如收回業績為50,000
當月已發獎金為50,000*20%=10, 000
更正後實際業績為50,000-50,000=0
更正後應發獎金為0*20%=0
收回月份獎金直展津貼則為0-10,000=-10,000

沙拉油
2002-09-07, 09:55 PM
其他部分的公式你應該都會吧?!
如果你都會那小弟就不囉唆了

瞭解您的狀況比寫公式還難,有點同情你了
如果你有哪些不懂可以儘管到這裡或 QA 那邊發問
我相信有很多人樂意幫助你的。