Board logo

標題: [發問] VBA請益 [打印本頁]
作者: jjloiasd    時間: 2020-12-12 23:52     標題: 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

再麻煩各位大神~~~

[attach]32798[/attach]
作者: n7822123    時間: 2020-12-14 00:10

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

回復 1# jjloiasd


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

所以抽核%數是多少?

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

暫估 or 預估?

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

但是其他科目不用區分?

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

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

[attach]32800[/attach]
作者: jjloiasd    時間: 2020-12-15 09:08

回復 2# n7822123

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

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

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

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

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

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

[attach]32805[/attach]
作者: n7822123    時間: 2020-12-15 12:19

回復 3# jjloiasd


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

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

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

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

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

都已排除，檔案如附件

[attach]32806[/attach]
作者: jjloiasd    時間: 2020-12-20 14:15

本帖最後由 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大~~~
作者: n7822123    時間: 2020-12-22 09:08

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

回復 5# jjloiasd

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

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

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

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

你先確認我的理解有無問題，我要再找時間改給你
作者: n7822123    時間: 2020-12-22 11:55

本帖最後由 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項目分別計算)，刪減資料

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

[attach]32861[/attach]



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