[發問] 依累計數量計算不同單價問題請教

paddyliu

小妹最近快被此表搞到頭痛，故上傳檔案 數量單價表.rar (4.43 KB)

下載次數: 8

2013-10-20 18:21

來求教解決方式，
假設1月單位小於等於70,000，則以1月單位x單位內單價計算：若大於70,000則以(70,000X單位內單價)+(超出70,000單位X單位外單價)計算，
2、3月是以累計的方式依上面的定率來計算，以附件來說，合計1月的單位仍不到70,000，故還是依單位內單價計算，
比較複雜的是，4月會有新的單位和單價，但仍要考慮若1~3月單位數若不到70,000，就要以70,000的差額計算補足，超過就用新的單價來計算，
附上檔案說明各種可能的狀況，能否幫小妹在小計那設公式，輸入數量即可計算出來，感謝！

luhpro

小妹最近快被此表搞到頭痛，故上傳檔案來求教解決方式，
假設1月單位小於等於70,000，則以1月單位x單位內單 ...
paddyliu 發表於 2013-10-20 18:23

當儲存格的公式變的很複雜時,
可以考慮用空間來換取公式的簡單化:
數量單價表-a.zip (7.17 KB)

下載次數: 7

2013-10-20 23:06

paddyliu

回復 2# luhpro
您好，因我無下載權限，不知能否將寫法貼給我，謝謝！:)

luhpro

回復 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 列皆直接下拉即可)

當然, 越後面越複雜, 因為需要判斷的可能性越多...