- 帖子
- 824
- 主題
- 6
- 精華
- 0
- 積分
- 904
- 點名
- 0
- 作業系統
- Win 10,7
- 軟體版本
- 2019,2013,2003
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2010-5-3
- 最後登錄
- 2023-12-24
|
4#
發表於 2013-10-22 22:56
| 只看該作者
本帖最後由 luhpro 於 2013-10-22 23:00 編輯
回復 3# paddyliu
共 3 個 Sheet : Sheet1 1~3月 4~6月
Sheets("Sheet1") 的第 2 列:
D E F G ...
-- +-----------------------------------------------------
2 | 1月單位 1月小計 2月 2月小計 3月 3月小計 4月 4月小計 5月 5月小計 6月 6月小計
E3='1~3月'!B3+'1~3月'!C3
G3='1~3月'!E3+'1~3月'!F3
I3=IF(H3="","",'1~3月'!H3+'1~3月'!I3)
K3=IF(J3="","",'4~6月'!B3+'4~6月'!C3+'4~6月'!D3)
M3=IF(L3="","",'4~6月'!F3+'4~6月'!G3+'4~6月'!H3)
O3=IF(N3="","",'4~6月'!J3+'4~6月'!K3+'4~6月'!L3)
Sheets("1~3月") 的第 2 列:
A B C D ...
-- +-----------------------------------------------------
2 | 1月累計 單位內 單位外 2月累計 單位內 單位外 3月累計 單位內 單位外
A3=Sheet1!D3
B3=IF(A3<=Sheet1!$A3,A3*Sheet1!$B3,Sheet1!$A3*Sheet1!$B3)
C3=IF(A3<=Sheet1!$A3,0,(A3-Sheet1!$A3)*Sheet1!$C3)
D3=A3+Sheet1!F3
E3=IF(D3<=Sheet1!$A3,D3*Sheet1!$B3,Sheet1!$A3*Sheet1!$B3)
F3=IF(D3<=Sheet1!$A3,0,(D3-Sheet1!$A3)*Sheet1!$C3)
G3=D3+Sheet1!H3
H3=IF(Sheet1!H3="","",IF(G3<=Sheet1!$A3,G3*Sheet1!$B3,Sheet1!$A3*Sheet1!$B3))
I3=IF(Sheet1!H3="","",IF(G3<=Sheet1!$A3,0,(G3-Sheet1!$A3)*Sheet1!$C3))
Sheets("4~6月") 的第 2 列:
A B C D ...
-- +-----------------------------------------------------
2 | 4月累計 單位內1 單位內2 單位外 5月累計 單位內1 單位內2 單位外 6月累計 單位內1 單位內2 單位外
A3='1~3月'!G3+Sheet1!J3
B3=IF(Sheet1!J3="","",IF('4~6月'!A3<=Sheet1!$A3,'4~6月'!A3*Sheet1!$B3,Sheet1!$A3*Sheet1!$B3))
C3=IF(Sheet1!J3="","",IF(A3<=Sheet1!$A3+Sheet1!$A11,IF(A3<=Sheet1!$A3,0,(A3-Sheet1!$A3)*Sheet1!$B11),Sheet1!$A3*Sheet1!$B3+Sheet1!$A11*Sheet1!$B11+(A3-Sheet1!$A3-Sheet1!$A11*Sheet1!$C11)))
D3=IF(Sheet1!J3="","",IF(A3<=Sheet1!$A3+Sheet1!$A11,0,(A3-Sheet1!$A3-Sheet1!$A11)*Sheet1!$C11))
E3=A3+Sheet1!L3
F3=IF(Sheet1!P3="","",IF('4~6月'!E3<=Sheet1!$A3,'4~6月'!E3*Sheet1!$B3,Sheet1!$A3*Sheet1!$B3))
G3=IF(Sheet1!P3="","",IF(E3<=Sheet1!$A3+Sheet1!$A11,IF(E3<=Sheet1!$A3,0,(E3-Sheet1!$A3)*Sheet1!$B11),Sheet1!$A3*Sheet1!$B3+Sheet1!$A11*Sheet1!$B11+(E3-Sheet1!$A3-Sheet1!$A11*Sheet1!$C11)))
H3=IF(Sheet1!P3="","",IF(E3<=Sheet1!$A3+Sheet1!$A11,0,(E3-Sheet1!$A3-Sheet1!$A11)*Sheet1!$C11))
I3=E3+Sheet1!N3
J3=IF(Sheet1!T3="","",IF('4~6月'!I3<=Sheet1!$A3,'4~6月'!I3*Sheet1!$B3,Sheet1!$A3*Sheet1!$B3))
K3=IF(Sheet1!T3="","",IF(I3<=Sheet1!$A3+Sheet1!$A11,IF(I3<=Sheet1!$A3,0,(I3-Sheet1!$A3)*Sheet1!$B11),Sheet1!$A3*Sheet1!$B3+Sheet1!$A11*Sheet1!$B11+(I3-Sheet1!$A3-Sheet1!$A11*Sheet1!$C11)))
L3=IF(Sheet1!T3="","",IF(I3<=Sheet1!$A3+Sheet1!$A11,0,(I3-Sheet1!$A3-Sheet1!$A11)*Sheet1!$C11))
(所有 Sheet 於上述欄位中的 4 ~ 6 列皆直接下拉即可)
當然, 越後面越複雜, 因為需要判斷的可能性越多... |
|
