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

標題: [發問] 有關類似現金帳的加總問題 [打印本頁]

作者: 97forum 時間: 2013-5-29 12:00 標題: 有關類似現金帳的加總問題

請教各位先進幫忙解決，如附加檔表格[attach]15100[/attach]。
首先說明需求，每月固定付款日期約是5、10、15、20、25以及月底。所以需要有加總計算餘額的需求，但是表格雖是固定付款日，但有可能會隨時插入某日(如表中第三例中之10號下須增加三個收付款項目)。

由於小弟所學有限，故請教先進如何可以做到下列需求(如何下函數、或者是必須採VBA方式解決，因為小弟現在所下的函數無法解決我的下列全部需求)
1. 餘額加總是必須的。
2. 但有可能是5號有金額，但是10號沒有，但是20號又有金額。(跳行處理)
3. 沒有金額的部分，是否可以顯示為空白，如果不可以，在以後未發生的月份餘額是否可以不顯示(第二例30號之後的餘額)。
4. 如果如第三例中之10號需增加三個項目(插入三行)，公式可由操作者自行複製上(下)方之公式，且公式不須再修改。

以上請先進們幫忙解惑！

作者: GBKEE 時間: 2013-5-29 13:30

回復 1# 97forum
是這樣嗎?

[attach]15101[/attach]

作者: 97forum 時間: 2013-5-29 14:34

回復 2# GBKEE

感謝前輩的回覆，正是如此。
原來可以使用名稱定義的方式來解決如此的需求，只是有些不懂的地方請教，名稱定義的過程中，如何可以讓範圍不受限制(隨表格變更改變範圍)。
因為看不懂前輩對於名稱的設定方式。

作者: GBKEE 時間: 2013-5-29 15:00

本帖最後由 GBKEE 於 2013-5-29 15:01 編輯

回復 3# 97forum
名稱定義的儲存格位置可分
絕對位置:  例 [名稱]=$A$4  永遠是 $A$4
相對位置:  當滑鼠選定B4(作用中儲存格)時,定義: [名稱]=A4 (B4相對於左邊一欄的儲存格) , 如你移動滑鼠在任一選定儲存格,貼上的名稱後會是  作用中儲存格的左邊一欄同一列號的數值
         當滑鼠選定D4(作用中儲存格)時,定義: [名稱]=$A4 (永遠相對於A欄的同一列的儲存格)
         當滑鼠選定D4(作用中儲存格)時,定義: [名稱]=A$4 (永遠相對於左邊3欄的的第4列的儲存格)
         類推......
         當滑鼠選定A4(作用中儲存格)時,定義: [名稱]=D$4 (永遠相對於右邊3欄的的第4列的儲存格)

注意: 相對的位置不可以超出工作表的範圍.(B4相對於左邊一欄的儲存格,當你在A欄這[名稱]=A4 ,會有錯誤的.)

作者: 97forum 時間: 2013-5-29 15:07

回復 4# GBKEE

所以前輩只是在於絕對位置與相對位置上的不同而已，是這樣子嗎?
想另外請教一件事情，如果想將餘額的儲存格(或欄位)鎖定保護 (也就是指開放日期、摘要、收入、支出這幾個儲存格)，但在於表格中應當如何去做設定，然後在表格最後一欄之後新增加一筆資料，表格可以自行新增一列 (非插入之方式)，不會去影響表格之特性？

作者: GBKEE 時間: 2013-5-29 16:05

回復 5# 97forum
用VBA 寫公式

[attach]15102[/attach]

作者: 97forum 時間: 2013-5-29 16:27

回復 6# GBKEE
前輩：
有些不解，是否如此工作表就不需要做保護？
這樣子可以保護不會去修改到餘額的欄位嗎?
我測試後發現餘額並不正確(沒有計算)！

如果不行也就是只能不保護工作表了？

作者: Hsieh 時間: 2013-5-29 16:34

回復 1# 97forum
F4=IF((C4="")*(D4="")*(E4=""),"",SUM(INDIRECT("R4C4:RC4",0))-SUM(INDIRECT("R4C5:RC5",0)))

作者: GBKEE 時間: 2013-5-29 16:34

回復 7# 97forum
2010版可能有問題

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Range("F4:f" & UsedRange.Rows.Count) 'F4到已範圍範圍的底部
.Value = "=餘額" '寫入公式
' .Value = .Value '*** 這行程式碼不要用試試看*** '等於公式的值
End With
Application.EnableEvents = True
End Sub

複製代碼

作者: 97forum 時間: 2013-5-29 17:16

本帖最後由 97forum 於 2013-5-29 17:19 編輯

回復 97forum
F4=IF((C4="")*(D4="")*(E4=""),"",SUM(INDIRECT("R4C4:RC4",0))-SUM(INDIRECT("R4C5:RC5" ...
Hsieh 發表於 2013-5-29 16:34

這個可用，謝謝前輩！
另前輩您的公式中 "*" 代表 OR 的意思是嗎？
如果要做保護該表格中公式的部分又該如何處理？想保有表格的特性(自動增加一筆新資料)

回復 97forum
2010版可能有問題
GBKEE 發表於 2013-5-29 16:34

測試之後還是不行。:(

作者: Hsieh 時間: 2013-5-29 18:25

回復 10# 97forum

條件式相乘，相當於AND

作者: GBKEE 時間: 2013-5-29 20:03

回復 10# 97forum
不行的話除了F欄的儲存格取銷鎖定格式,然後設定工作表保護.

作者: 97forum 時間: 2013-5-30 09:46

本帖最後由 97forum 於 2013-5-30 09:48 編輯

回復 11# Hsieh
了解了，謝謝前輩教導。

回復 12# GBKEE
試過了，還是無法達到保護的效果

以上，小弟現在最主要想將公式(F攔)部分保護，避免填寫人員誤將公式儲存格給填入內容。
現在的情形是，由於會採用表格方式是圖表格可以自動新增一筆資料的方便性(也可以達到公式一致性)，但是卻找不到要領來做公式(F)攔的保護。

公式部分非常感謝兩位前輩的教導：
>> Hsieh 前輩，小弟由於對於 indirect 函數相當陌生，所以需花點時間去了解用法，至於第一次才知道公式可採用 "*" 來做 "And" 的功能，受教了！
>> GBKEE 前輩的VBA部分，那真的小弟是鴨子聽雷，完全看不懂，但會慢慢去研究了！

最後非常感謝兩位前輩的幫忙了！

作者: GBKEE 時間: 2013-5-30 10:45

本帖最後由 GBKEE 於 2013-5-30 10:46 編輯

回復 13# 97forum
只做(F)攔的保護
如圖示
[attach]15105[/attach]

[attach]15107[/attach]

作者: 97forum 時間: 2013-5-31 09:19

回復 14# GBKEE
可以用了，謝謝前輩的幫忙。

小弟不才有點不懂，小弟今日才發現原本在前輩的檔案中F欄無論輸入任何文數字皆不受影響，
這部分是因為在前輩寫在VBA的關係嗎?(因為無關F欄有無保護的狀況)
還是其他的因素。

作者: GBKEE 時間: 2013-5-31 09:45

回復 15# 97forum
6# 的檔案所寫的工作表事件,寫入當工作表的任何儲存格(含F欄)有修改時F欄永遠是公式"=餘額",
如此不就是在保護F欄不被修改.

作者: 97forum 時間: 2013-6-3 08:51

回復 16# GBKEE

了解了，順道請教前輩，小弟一直不解，如果一般的使用者是要如何才能達到像前輩如此的功力，小弟指的是除了爬文(估狗或者是論壇)、看書(坊間書籍)、工作上應用以外，是否坊間有開在函數、巨集這方面的課程(指的是全部的函數)，也就是前輩是如何達到這種功力的方式，因為小弟很興趣在VB、VBA這方面的學習，只可惜現在都不開VB相關的課程了，至於Excel是個人興趣及工作部份需求。

麻煩請各位前輩解惑。:P

作者: GBKEE 時間: 2013-6-3 13:56

本帖最後由 GBKEE 於 2013-6-3 14:09 編輯

回復 17# 97forum
一山還有一山高,我只是一座小山,花在Excel上學習的時間比你多一些罷了.(多看,多問,多學習)

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