EXCEL 公式運算

2015-05-31 6:05 am
承接上題,
R8是預算金額
收入 支出津助額 R9是$28,000.00
A B C D A+B -D E
參加者收費學校津貼中心津貼 活動總支出 盈餘 / 津貼 年度預算餘額
M欄 N欄 O欄 P欄 Q欄 R欄
12 $2,000.00$0.00$3,000.00$5,000.00-$3,000.00$25,000.00
13 $5,000.00$0.00$5,000.00$10,000.00-$5,000.00-$8,000.00
14 $4,000.00$0.00$2,000.00$6,000.00-$2,000.00-$7,000.00

使用公式運算後有些問題未能達致以下效果,詳列如下:
年度預算餘額是$28,000.00, R9-Q12=R12=$25,000.00,
R12=$25,000.00-Q13=$5,000.00=R13=$20,000.00,
R13=$20,000.00-Q13=2,000.00=R14=$18,000.00
當中如Q15=正數$4,000.00=R15=22,000.00,如似類推,直至例:R39答案扣至$0.請教教怎樣設定公式。如有遺漏,請提點。謝!

另之前問題可能表達不太好,按儲存格格式-數值-自訂-$#,##0.00出現「-$2,000.00」全部數字統一格式,便出現上述效果,其實凡是負數要這樣表達「$(2,000.00)」,請教教。謝!

回答 (1)

2015-06-01 6:21 am
✔ 最佳答案
為令輸入時有活動數字的列才顯示計算的行,令表上數字更簡潔,建議引入IF(ISNUMBER()) 函數檢查P行來協助。
1. 將O12公式改為:
=IF(ISNUMBER(P12),MIN($R$9,MAX(P12-M12-N12,0)),"")
然後將O12公式複製至O13:O39。
2. 將P12公式改為:
=IF(ISNUMBER(P12),M12+N12-P12,"")
然後將P12公式複製至P13:P39。
3. 將R12公式改為:
=IF(ISNUMBER(P12),$R$8+Q12,"")
R13至R39公式與R12有些不同,要參照上一項活動完成後的年度預算餘額,R13公式為:
=IF(ISNUMBER(P13),R12+Q13,"")
然後將R13公式複製至R14:R39。
由於早前未有考慮到活動可以有盈餘而撥入年度預算餘額,那麼中心津貼或可允許上限大於年度預算金額。如需這樣變更,可將O12公式進一步修改成另一較高上限金額(舉例$40,000):
=IF(ISNUMBER(P12),MIN(40000,MAX(P12-M12-N12,0)),"")
又或者是上一項活動扣減後的年度預算餘額,O12不用修改、由O13開始修改:
=IF(ISNUMBER(P13),MIN(R12,MAX(P12-M12-N12,0)),"")
然後將修改公式在該行向下複製至第`39列。

至於M12:R40數字儲存格格式設定,可選 [數值] - [自訂] - $#,##0.00;$(#,##0.00)
當中分號前是正數設定,分號後是負數設定。
如負數須用紅色顯示,可將自訂格式修改如下:
[數值] - [自訂] - $#,##0.00;[紅色]$(#,##0.00)

2015-05-31 23:26:39 補充:
不好意思,O13公式中MAX括弧內應該是P13-M13-N13才對:
=IF(ISNUMBER(P13),MIN(R12,MAX(P13-M13-N13,0)),"")

如果只選Q12,再查看儲存格格式,是否選了另一自訂格式?
我將所有數字儲存格選取後,全部選用一套自訂格式,並沒有出現負號在數字前面,可嘗試重複步驟一次。

2015-06-01 00:39:54 補充:
O40公式:
=INDEX(R12:R39,ROWS(R12:R39)-COUNTBLANK(R12:R39),1)
註釋:INDEX函數可以傳回一儲存格範圍內某列某行的儲存格數值,現所需範圍是R12:R39;然後計算某列,用ROWS先計出範圍內總列數,再減去COUNTBLANK計出的空白列數,便可算出有數值的列數,所以每入了新一列活動數字後,COUNTBLANK會計少一列,直至入滿活動後COUNTBLANK會是零,亦即會指向最後第39列的儲存格;某行不用計算,指定是第一行,因儲存格範圍R12:R39只得一行。

2015-06-01 18:34:30 補充:
要封鎖某些儲存格不被修改,可以運用Excel的「保護工作表」功能:
1. 先將允許編緝的儲存格解除鎖定,選取儲存格(例如是M12:N39、P12:P39)按下滑鼠右鍵,執行快顯功能表上的【儲存格格式】;

2. 點擊【保護】索引標籤,取消勾選【鎖定】再按下【確定】按鈕

3. 檢查要鎖定的區域的設定是否為「鎖定」

4. 執行【校閱】>【保護工作表】

5. 只勾選【選取未鎖定的儲存格】,也可以設定取消保護的密碼

2015-06-01 18:35:01 補充:
6. 完成設定之後,此工作表就只有未設定鎖定的區域能編輯

7. 要解除謮執行【檔案】>【資訊】,按下【取消保護】,若在步驟5中有設定密碼,則需要輸入密碼之後才能取消保護

如果有需要封鎖整個工作簿,在步驟4中改選「保護工作簿」。


收錄日期: 2021-04-11 21:04:00
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20150530000051KK00073

檢視 Wayback Machine 備份