返回列表 上一主題 發帖

[發問] VBA請益

[發問] VBA請益

各位大大好,小弟有一問題想請教各位高手,還望不吝賜教
小弟是公司會計,公司定期會產生明細帳,而小弟工作就是先用樞紐計算各個科目的總額,然後依照上面給的抽核%數,抽出至少大於該%數的金額
但每次都要用手動操作,比較曠日廢時,因此想來請教是否能用VBA得到一樣的結果

附檔為範例
步驟是:
1.系統拋轉名細
2.樞紐得到各科目總額
3.再依月份比例換算全年
4.再回到明細抓出至少大於該%數的正數金額

有以下幾點需要補充:
1.在抓出各科目的金額後,要用明細中最後的月份去計算年化比例(例如明細中最近月份是11月,各科目總金額需要除上11再乘12)
2.希望能自動產生各個科目的分頁,也要依照銷售、管理、研發分類(範例是簡化檔案,僅租金支出-一般是實際的樣子,後續是簡化檔案)
3.假如抓到某科目的管理費用是負的,就無需再抓該科目的明細
4.若摘要中含有"暫估"字樣的不要選
只需要抓名細即可,例如租金支出-一般只要抓出以下即可

銷售    109/11/30        TR-1091130009        甲店109年 營業用租金 02-0590         1,905        
銷售    109/11/11        TR-1091111001        乙店 109/11 辦公室租金                         1,905        
銷售    109/10/31        TR-1091031006        甲店109年 營業用租金 02-0590         1,905        
管理    109/02/29        TR-1090229001        甲店109年 營業用租金 02-0590        1,905
管理    109/03/12        TR-1090312001        乙店 109/03 辦公室租金                         1,905
管理    109/03/31        TR-1090331005        甲店109年 營業用租金 02-0590         1,905

再麻煩各位大神~~~

範例.zip (13.23 KB)

本帖最後由 n7822123 於 2020-12-14 00:12 編輯

回復 1# jjloiasd


然後依照上面給的抽核%數,抽出至少大於該%數的金額

所以抽核%數是多少?

若摘要中含有"暫估"字樣的不要選

暫估 or 預估?

為什麼你的範例,"租金支出-一般" 有區分"銷售費用" 與 "管理費用"

但是其他科目不用區分?

為什麼你的範例,"保險費" 多了一欄 "Total",但是其他科目沒有?

我先不管抽核%數,資料都秀出來,你先看看格式、資料是不是你要的


範例-1214.rar (22.68 KB)
程式是依需求寫的,需求表達不清楚
或者沒有上傳附件,愛莫能助

TOP

回復 2# n7822123

N大您好,首先真心感謝您的回覆
您的VBA正好是我想要的結果
也要和您說聲抱歉,小弟第一次發文,有諸多未注意到的地方,感謝N大的指教

針對您的問題,回覆如下:
1.抽核%數並非固定,有時為20%、15%、10%甚或30%均有
故當時沒給出一個特定的比例
但平均而言,20%為多數(若N大需要的話,以20%為主)

2.這點是我的疏忽
主因是填寫的人不一,大部分會要求用"暫估"字樣填寫
但仍有出現N大所提的例如"預估"、"估列"字樣
若N大方便的話,請設定為若摘要中含有"估"字樣的不要選

3."租金支出-一般" 有區分"銷售費用" 與 "管理費用",但是其他科目不用區分?
當時只是想說用一個科目做說明,後續就簡便形式,已把其他科目一併修正

4.為什麼你的範例,"保險費" 多了一欄 "Total",但是其他科目沒有?
這欄是不要的,忘記刪除,已修正

再一次感謝N大的指導~~

範例.zip (13.41 KB)

TOP

回復 3# jjloiasd


N大您好,首先真心感謝您的回覆
您的VBA正好是我想要的結果
也要和您說聲抱歉,小弟第一次發文,有諸多未注意到的地方,感謝N大的指教

沒事,下次發問,記得你的範例與敘述要能符合就好,若問題太多我也不會寫VBA回覆了

1.抽核%數並非固定,有時為20%、15%、10%甚或30%均有
故當時沒給出一個特定的比例
但平均而言,20%為多數(若N大需要的話,以20%為主)

那做個詢問功能給你輸入,並且預設20%,可自己更改(亂輸入會跑不出迴圈)

2.這點是我的疏忽
主因是填寫的人不一,大部分會要求用"暫估"字樣填寫
但仍有出現N大所提的例如"預估"、"估列"字樣
若N大方便的話,請設定為若摘要中含有"估"字樣的不要選

都已排除,檔案如附件

範例-1215.rar (25.86 KB)
程式是依需求寫的,需求表達不清楚
或者沒有上傳附件,愛莫能助

TOP

本帖最後由 jjloiasd 於 2020-12-20 14:18 編輯

回復 4# n7822123

N大您好,不好意思想請教您一些問題
想請問N大是如何計算"換算全年"的數字?
在研究N大的VBA後,發現N大是先把資料中有"*估*"字樣和負數的值跳過,然後再用"淨額_(I欄)計算"換算全年"的數字(以上是我從結果推論,小弟VBA生手,尚未能了解N大的寫法)
也就是以保險費的銷售費用來說,N大所計算的數字是71406,接著再換算全年比例

這部份是我沒有說明清楚,非常不好意思
其實在計算"換算全年"的數字的時候,會先把所有的資料一併計算(也就是直接把保險費的銷售費用之"淨額"(I欄)的數字全部加起來,所以其總額會是66673,然後再換算全年,假若保險費的銷售費用算出來是負數的話,就不用抓明細)
計算後,在抓資料時,才要避開有"*估*"字樣和負數的值(一樣從"淨額"抓,但只抓正數的部分)
因此這部分能否再麻煩N大調整

另外還想再麻煩N大一事
可否在這一段Brr = Array("銷售", "管理", "研發"),多加入"製造"的分類,並在.Value = Array("日期", "傳票號碼", "DESCRIPTION", "銷售費用", "管理費用", "研發費用", "REMARK")加上"製造費用"
我有試著改寫,但遇到問題,因此想麻煩N大

感謝N大~~~

TOP

本帖最後由 n7822123 於 2020-12-22 09:11 編輯

回復 5# jjloiasd

這部份是我沒有說明清楚,非常不好意思
其實在計算"換算全年"的數字的時候,會先把所有的資料一併計算(也就是直接把保險費的銷售費用之"淨額"(I欄)的數字全部加起來,所以其總額會是66673,然後再換算全年,假若保險費的銷售費用算出來是負數的話,就不用抓明細)

也就是說,"負值"也要計算到,"換算全年的數字"喔?

我是先挑出來到各個工作表再算總合的,照你這樣說,程式順序要改一下

負值先一樣搬到各個工作表再算總合(換算全年數字),才能把負值刪除

你先確認我的理解有無問題,我要再找時間改給你
程式是依需求寫的,需求表達不清楚
或者沒有上傳附件,愛莫能助

TOP

本帖最後由 n7822123 於 2020-12-22 12:07 編輯

回復 6# n7822123

另外還想再麻煩N大一事
可否在這一段Brr = Array("銷售", "管理", "研發"),多加入"製造"的分類,並在.Value = Array("日期", "傳票號碼", "DESCRIPTION", "銷售費用", "管理費用", "研發費用", "REMARK")加上"製造費用"
我有試著改寫,但遇到問題,因此想麻煩N大

改好了

這部份是我沒有說明清楚,非常不好意思
其實在計算"換算全年"的數字的時候,會先把所有的資料一併計算(也就是直接把保險費的銷售費用之"淨額"(I欄)的數字全部加起來,所以其總額會是66673,然後再換算全年,假若保險費的銷售費用算出來是負數的話,就不用抓明細)
計算後,在抓資料時,才要避開有"*估*"字樣和負數的值(一樣從"淨額"抓,但只抓正數的部分)
因此這部分能否再麻煩N大調整

我沒照你的順序,我先全部抓出來計算"換算全年"的數字,再用刪去法,刪掉不要的

我把程式拆分成4個部分,你可能比較看得懂,順序如下


Step1 : 依各科目區分成各個工作表分頁,並依部門欄位填入 "淨額"
Step2 : 設定各分頁儲存格格式+公式,並且計算 "換算全年" 的金額
Step3 : 依各分頁C欄,刪除有 ''字樣、以及負數淨額的資料
Step4 : 各分頁依抽核比例 (分4項目分別計算),刪減資料


你再試看看有無問題,檔案如下

範例-1222.rar (25.64 KB)
程式是依需求寫的,需求表達不清楚
或者沒有上傳附件,愛莫能助

TOP

        靜思自在 : 【行善要及時】行善要及時,功德要持續。如燒開水一般,未燒開之前千萬不要停熄火候,否則重來就太費事了。
返回列表 上一主題