Board logo

標題: [發問] 多條件計算交易明細 [打印本頁]
作者: shuo1125    時間: 2023-5-2 00:32     標題: 多條件計算交易明細

各位前輩好!
資料庫為明細帳，依交易明細中的交易對象.科目.日期.條件計算相對應之交易情形，
※明細帳A.R等欄位資訊不正確...因範例是篩選撈出的。(該資料不需用到)
有先試做一版，但遇到問題是，若交易對象不同則需對每個交易對象以各別的程式碼處理，以及資料若不先經排序也會出錯，
想請問是否有比較便捷的做法嗎...?
需求如下:sheets(交易明細)
收入淨額(N)=銷貨收入(H)-上期自調(I)+本期自調(J)-退回/折讓(L)。
營業成本(Q)=5開頭之費用。(非全部)。
營業費用(S)=6開頭之費用。(非全部)。
進貨(X)=1215+1218+5132(不含入庫未請款及在途)。
上月暫估(V)=1215+5132+1218入庫未請款及在途貸方沖銷數。
期末暫估(AB)=1215+5132+1218入庫未請款及在途。
固資購(AG)=固定資產科目借方增加數。
固資售(AI)=固定資產科目貸方增加數。
目前都暫不考慮原幣數，因為外幣都只填在摘要而已，不知道如何處理..
附上附檔..在勞煩能者出手簡化了，感謝不盡!
作者: singo1232001    時間: 2023-12-15 10:34

本帖最後由 singo1232001 於 2023-12-15 10:46 編輯

回復 1# shuo1125

這個題目

確實有正規的作法  而且 只能用這個做法
看到你的代碼後 我認為你該走向這方向

這種 不同多種 x 多次 x 多條件 x 多表 x 多篩選  基本上就只能指望SQL  就只能指望SQL  就只能指望SQL
很重要說三次 別拿VBA試
基本上公司除了專門的MIS 基本上不可能讓非專業人士開啟資料庫操作

所以我建議
最佳方式
自己安裝SQL server + SSMS (安裝 操作 使用 也可以叫GPT教)
將這筆資料直接匯入或貼上SQL  或者大量的話  直接跟mis拿.bak檔 .csv .xlsx檔案

然後GPT4+SQL語法 叫GPT幫出代碼 9成都能秒殺(不過基礎要自學影片至少40小時 但保證不虧)
你的問題應該可以在3~10分鐘內解決  而且看往後這種查表篩選有多少問題都能快速解決


若要用VBA 基本上 也是直接調用adodb.connect SQL代碼(但功能殘缺 需各種花式)
但這個方向 網上會搞的人 基本上不會待著 他們會直接轉向直接使用SQL
想查VBA+SQL 基本上查到的資料非常少 反而GPT資料多  整合起來有機會使用
就算問GPT 也只能給你6成答案 4成自己補

但我看了看資料  也做不了
原因是G表中 5.6開頭的 沒有半筆Mn資料 (我創了假資料)
M表中 5.6開頭的 也沒半筆  無法測試

我會嘗試 是因為正在練習對付非正規化格式的 花式SQL語法
需開一個工作表1 再執行 (要把你原本的代碼全部槓掉)
但工作表1沒有任何資料

Sub t1()
i = Split("Provider=Microsoft.,Jet.OLEDB.4,.0;Extended Properties=Excel ,8,.0;Data Source=", ",")
If Application.Version > 12 Then i(1) = "ACE.OLEDB.12": i(3) = 12
Set cn = CreateObject("adodb.connection"): cn.Open Join(i, "") & ThisWorkbook.FullName

q = "select [DATA_序],[DATA_月],[DATA_餘額] from [G明細帳$A1:T] where "
q = q & " [DATA_序] like '5__'  and  [DATA_摘要] like '%mn%' "
Set rs = cn.Execute(q)

Sheets("工作表1").[A2].CopyFromRecordset rs
Sheets("工作表1").[a1:d1] = Split("DATA_序,DATA_月,DATA_餘額,總餘額", ",")


q1 = "select sum(DATA_餘額) as sumX from ( " & q
q1 = q1 & " )  "
Sheets("工作表1").[D2].CopyFromRecordset cn.Execute(q1)

'...
End Sub

當然你想要用其他資料庫也可以  反正都有外掛圖形化介面
mysql sqlserver ptsql
[attach]37135[/attach]
作者: shuo1125    時間: 2023-12-15 11:49

回復 2# singo1232001
慶大好~
有看過你分享的SQL寫法，你也是熱心人士，
網上針對VBA引用ADODB的範例確實很少...
但尚未花時間去理解主因是...若VB我都尚未理解透徹及應用後，就去學習其他領域感覺會更吃力....
後來我還是直接分開寫程序及引用多字典，暫時能解決問題便好，
(但這缺點就是當出現新型態交易，就要再調整代碼。)
因公司系統其實是用SQL開發的，所以我大概知道其實以SQL調用是最佳解，無奈程度有限只能漸進學習了...
感謝關注此帖並提供方向，謝謝您!!!
作者: singo1232001    時間: 2023-12-15 18:21

本帖最後由 singo1232001 於 2023-12-15 18:35 編輯

回復 3# shuo1125


從我目前的觀察 就當前你的功力 基本摸到門檻很夠了
若沒加廣度 儘早跳到SQL 由於你的業務性質 學習VBA 應該會更吃力 正常應該是會失敗
當然此題用3~4維陣列 能解 或字典嵌套 例如 ar(0)(1)(2)(3) 這類陣列 (但要複驗測試答案 會是一個更嚴重的問題 SQL能複驗正確性)
對比直接跳到SQL 的情況 若系統量化 大概是如下表
-------------------------
有GPT的情況下 打5分
對比 含SQL ,單靠vba
輕鬆  4,1  (用GPT超級咒語 跟 邏輯分解 )  GPT超咒: 用生活生動事情說明邏輯流程 並舉兩例       GPT邏輯分解:將代碼邏輯流程分解並詳細說明
簡單  5,2  (GPT無腦生答案 任君選五種不同口味解答)
實用  5,1  (1分是真的無法 該特殊題型限制)
學程  5.2  (只包括資料結構的部分 其他類型不管,SQL有GPT學習時間約3個月,VBA若只有資料結構分析部分 大概1年 都最拚的情況下)

沒GPT的情況下
輕鬆  2,3 (一開始的學習的徬徨感,跟一開始學陣列字典差不多的感覺)
簡單  3,2 (沒人教 花式題不會解)
實用  3,1 (沒人教 花式題不會解,但你的題型在SQL中是基礎題,大概學15天(20~30小時)就會的那種)
學程  2,2  (學習時間約1年 沒人解題)
----------------------
最後取決衝不衝而已
畢竟 此題用vba硬上有解  一題約要解3~4H  但條件會隨時修改 可能有100種哈姆雷特
若SQL 就是3分鐘的事情
大概是這樣 僅共參考

另外 資料分析 跟您們公司的SQL系統 無關
任何資料都可以自建SQL分析 正常來說
應該是公司伺服器原本的一個系統  
你用虛擬機或者用個人電腦 灌一個另外個人用的SQL 兩者無關 獨立  別用access 太多限制
作者: shuo1125    時間: 2023-12-15 23:46

回復 4# singo1232001
慶大好~
應該說當時會開始接觸VB是為簡化作業，因公司無法隨意使用其他軟體及程式，所以能用的也只有XL...
但也如同你說因業務性質關係，在剛入門完全不懂的情況下看了相關書籍及網上範例還是無法運用在工作上，
得知此論壇上來提問後獲得准大的幫助，一開始當然只是想解決問題，慢慢去理解後才發現...
原來我是得到多麼厲害的高手的幫助，漸漸地才逐步逐項的去學習，當然准大的邏輯跟技巧非常人所及.....
不過隨著多看高手的程序及編碼，從連迴圈是什麼都不知道，也受到眾多人的幫助，開始能大概調適代碼簡易的運用在工作上，
雖然確實搭不上邊，但還是希望能慢慢更深入了解VB，就算失敗也無妨，畢竟是自己學習用。
-------------------------------------------
公司使用自主開發的SQL作為ERP系統，但財務方面的整合較少，這也是為什麼會想要盡可能利用VB減少手工作業，
而之所以會說最佳解是因為看過資訊寫的編程直接能調用資料庫帶出所需資訊。
所以你建議我直接學習SQL是怕我走太多彎路:)
會再抽空去學習~
-------------------------------------------
您辛勞地分析利弊，熱情的提供建議和指導，論壇真的充滿熱心專業的人士。
感謝您及大家!!!!
作者: jackyq    時間: 2023-12-16 23:19

本帖最後由 jackyq 於 2023-12-16 23:27 編輯

回復 5# shuo1125

你寫法的優點就是 它能暫時解燃眉之急
這很重要  很多時候 解燃眉之急比什都重要
缺點是 它是一種炸彈式寫法  以後100%會引爆   
引爆日就是你code 流程全部重新改寫之日

如果非要用 VBA  我不會想用字典
我會用排序 但我不會去用 EXCEL 自帶的多欄排序
我會去弄一個用 VBA實現的多欄排序函數
如果你能自己弄一個多欄排序函數
你看著那個你自己搞出來的函數
你的腦子裡應該就自然會浮現出 後續完美流程寫法

有很多時候 流程寫不好 是因為少了一些關鍵性功能函數
所以只能用暴力式流程
作者: singo1232001    時間: 2023-12-17 00:34

本帖最後由 singo1232001 於 2023-12-17 00:43 編輯

回復 5# shuo1125


    才想到  目前這題的主要問題是之前給的"資料有問題有缺"
我可以用vba+SQL 幫忙試作
但問題是檔案內的資料有問題  所以我也不知道要怎麼作
要重新給對的資料  不然應該早有人幫忙解題了 或者補充說明一下 是否有哪些流程沒提到

另外 可以一次提3~5種 不同的查詢需求  我用adodb應該可以馬上生出來

例如
第1題. 2023,1~12月  模糊名稱 5開頭 A表
第2題. A表(2022,某月  非某名稱 6開頭 )+B表(N年,N月  要某名稱 6開頭 N金額區間) 有相同的才算  或 金額誤差內 或相同日期
...
...
像上面這類題目 SQL 配GPT都是秒解
作者: 准提部林    時間: 2023-12-17 18:11

只能給個"模版"設計方向, 統計金額可能要自行去確認及修改, 大概猜著寫//
[attach]37136[/attach]
作者: shuo1125    時間: 2023-12-17 23:59

回復 6# jackyq
J大好~
確實是為了先快速得到結果來做...您講得沒錯，當交易形態越多維護就越不易，到頭來可能就要打掉重練了...
感謝給了另外一個方向去思考，但因我連常規都寫不好，所以暫不考慮以自定義來做...
謝謝你!!
作者: shuo1125    時間: 2023-12-18 00:02

回復 7# singo1232001
慶大好~
因財務資料較為敏感...故大部分只能暫時上傳模擬數據來提問..
若您想測試資料，有空我在整理測試資料上傳，
謝謝您!!
作者: shuo1125    時間: 2023-12-18 00:14

回復 8# 准提部林
准大好~
又勞煩您出手幫忙了，我抽空測試後再給予回饋，
百忙之際還幫忙註解及協助，大恩不言謝...
作者: 准提部林    時間: 2023-12-18 13:16

稍修:
1) A6"資訊"兩字前有空格, 加入TRIM去除
2) 增加工作表簡碼對照表
3) "自調"金額與你模擬有出入, 已修改, 可再自行調修
4) 取得來源工作表(排序後加入陣列), 捨棄字典用法, 避免工作表太多, 使字典帶太多陣列資料而佔用資源,
   改用"當工作表名稱改變時"才執行排序取陣列, 所以, 同一工作表的順序必須連續, 才不用一直反覆排序!!

[attach]37137[/attach]
作者: shuo1125    時間: 2023-12-18 13:41

本帖最後由 shuo1125 於 2023-12-18 13:46 編輯

回復 12# 准提部林
准大好~
剛開始試著理解#8的代碼，又馬上給了另一方式..
#8基本上已經完成近90%....唯一需要調整只是因為原提供邏輯未完善造成。
您的思路細膩，字典嵌套用的高段...
原代碼調試了兩段:
-----------------------------------------------------
一)
收入上期自調=只看摘要有"上期"的貸方金額
        S1 = InStr(Arr(i, 10), "上期"): S2 = InStr(Arr(i, 10), "本期") '判斷為前期/本期
        If (InStr(Tx(2), T) > 1) * (S1 > 0) Then
           Brr(2)(M, 0) = Brr(2)(M, 0) + V17 '02.自調i
        End If
收入本期自調=只看摘要有"本期"的借方-貸方金額
        If (InStr(Tx(2), T) > 1) * (S2 > 0) Then
           Brr(3)(M, 0) = Brr(3)(M, 0) + V16 - V17 '03.自調j
        End If
二)
進貨的暫估位置稍反
          If (P1 = 0) + P2 Then Brr(7)(M, 0) = Brr(7)(M, 0) + V16 - V17 '07.進貨(未稅)(B)
           If P1 > 0 Then
          Brr(6)(M, 0) = Brr(6)(M, 0) + V17 '06.上期暫估金額(A)
-----------------------------------------------------
另想請問....若該月無交易的金額要顯示0該如何做?
謝謝您~
作者: 准提部林    時間: 2023-12-18 13:55

回復 13# shuo1125

另想請問....若該月無交易的金額要顯示0該如何做?

定義Xrr的型態//
若都整數:
Xrr(1 To 12, 0) As Long
若有小數又比較適合財務數字:
Xrr(1 To 12, 0) As Currency
作者: shuo1125    時間: 2023-12-18 14:00

本帖最後由 shuo1125 於 2023-12-18 14:01 編輯

回復 14# 准提部林
准大好~
...忘記這方式就可以，感謝您的指導!
Xrr&(1 To 12, 0)。
作者: 准提部林    時間: 2023-12-18 14:03

回復 13# shuo1125

S1 = InStr(Arr(i, 10), "上期"): S2 = InStr(Arr(i, 10), "本期") '判斷為前期/本期

S1/S2...可沿用P1/P2, 它是隨用隨動的 不會有衝突, 免得變數太多維護不易//
作者: shuo1125    時間: 2023-12-18 14:12

回復 16# 准提部林
准大好~
我想得太淺...感謝指導!!(您以立沖帳來看更直覺)
該表單我後續還有增加實科目的餘額，不過實科目較單純，直接抓該月餘額而已。
因目前只抓虛科目的交易，您提供的模板及方式，我再自行調適看看...
太感謝一直以來的幫忙了!!!
作者: singo1232001    時間: 2023-12-18 18:06

本帖最後由 singo1232001 於 2023-12-18 18:08 編輯

[attach]37139[/attach]回復 17# shuo1125


之前有做過一個查詢介面  剛好趁這次練習一下

[
作者: singo1232001    時間: 2023-12-18 22:09

本帖最後由 singo1232001 於 2023-12-18 22:19 編輯

回復 17# shuo1125


        修正一些功能  增加排除字 跟說明
目前只有表名中含有 "明細表"的 表才會被篩選

[聯集交集] O滿足任一條件 , X全部條件滿足
[順排0逆排1] 該欄位 0-順排  1-逆排  空白-不排列 (只能選一欄 )
[模糊;;] 模糊比對        [完整;;] 完整比對
[小於] 數字小於         [大於] 數字大於
[模糊排除字] 排除掉某個字
其他1: 任一欄位 ;;符號 多條件 (大小於不可用) 滿足其一就有
其他2: 篩選順序是由上到下
其他3: 全部留空 全部顯示
其他4: 黑色部分 輸入無效    (再次點擊說明可關閉)

這種方式 就是把 SQL語法 動態化
改關鍵字 語法就會變化 大概是這樣的作法
大概就是一個 強化版的篩選功能  能排除字 合併表 模糊比對  大小於 順便排序
但我沒有深入測試 或許有bug

[attach]37143[/attach]
作者: shuo1125    時間: 2023-12-18 22:30

回復 19# singo1232001
慶大好~
感謝分享，之後若開始研究SQL這會是很好的範本可參閱。
謝謝您了!!
作者: singo1232001    時間: 2023-12-19 11:31

回復 20# shuo1125



修正日期 大小搜索
簡化代碼 完整版代碼有保留
[attach]37149[/attach]
作者: singo1232001    時間: 2023-12-19 13:09

回復 20# shuo1125


    全功能 各種篩選一應具全
若遇到[attach]37152[/attach]bug的話請回報
作者: shuo1125    時間: 2023-12-19 13:34

回復 22# singo1232001
慶大好~
大概測試點選OK，謝謝提供SQL的方式，
後續若有其他問題再煩請您指導，謝謝您了~
作者: singo1232001    時間: 2023-12-19 18:39

回復 23# shuo1125


1.追加功能 顯示SQL語法
2.增加範例
3.修正選取 與排除的順序 問題
4.增加查詢時間與筆數
   
[attach]37153[/attach]
作者: shuo1125    時間: 2023-12-19 23:32

回復 24# singo1232001
回復 16# 准提部林
謝謝准大提供思路及解法，測試無誤且代碼簡化非常多...感激不盡!!
----------------------------------------------------------
慶大好~
上傳樣本，若方便在指導SQL解法，謝謝您~
----------------------------------------------------------
[attach]37154[/attach]
作者: singo1232001    時間: 2023-12-20 06:52

本帖最後由 singo1232001 於 2023-12-20 06:57 編輯

回復 25# shuo1125


av
同月有兩筆以上
有需要相加嗎?


[attach]37155[/attach]

大概目前進度30 只有第一列做到一半  
應該會有2~3個版本  其中應該一個是完整不精簡的  語法容易看
另一個就是全部用迴圈跟變數 替代壓縮起來
但不過有幾項我確實不太能理解關聯
作者: shuo1125    時間: 2023-12-20 08:31

本帖最後由 shuo1125 於 2023-12-20 08:35 編輯

回復 26# singo1232001
慶大好~
我測試過您代碼抓的數字無誤，請問是哪邊的關聯無法理解?
應當是我敘述的不夠清晰。
------------------------------
※虛帳戶(收入.損益.進貨.資產)
這裡的資產其實是固資(只是單抓實科目較為簡化)
特定時點之累計餘額。<例:截止日:2023/09/30，則為2023/01~09累積金額>
※實帳戶(餘額)
特定時點之餘額。<例:截止日:2023/09/30，則為2023/09當月餘額>
------------------------------
其涉及財務相關概念..可能就要針對部分做解釋。
以科目編號第1碼來看
實帳戶:1.資產(借餘)2.負債(貸餘)3.權益(貸餘)
虛帳戶:4.收入(貸餘)5.成本(借餘)6.費用(借餘)7.業外損益(借.貸餘)8.稅費.其他(借餘)
同向相加，反之相減。
------------------------------
以上，謝謝。
作者: singo1232001    時間: 2023-12-20 19:33

回復 27# shuo1125


        再測試 內有簡單一行學習版 問GPT版 完整版 跟 簡化測試版
若可行  我再把其他的部分弄完

[attach]37160[/attach]
作者: shuo1125    時間: 2023-12-20 23:16

回復 28# singo1232001
慶大好~
勞您費心整理了，還將程序分析講解並提供範例及查詢方式....
由衷感謝!!
作者: singo1232001    時間: 2023-12-20 23:42

回復 29# shuo1125


    [attach]37161[/attach]

av ax az 異常  我忘了綁關鍵字 重傳
作者: shuo1125    時間: 2023-12-20 23:45

回復 30# singo1232001
慶大好~
這麼晚了還幫忙一直調試。
抽空我在看，對SQL相當陌生....
早點休息先，感謝你了~
作者: singo1232001    時間: 2023-12-21 02:01

回復 31# shuo1125


    [attach]37162[/attach]

也可以參考多行放工作表版 SQL語法全部改放工作表 的寫法

Sub 查放工作表精簡版()  '可搭配 表"SQL GPT4prompt" 跟 "SQL" 工作表問GPT放語法
i = Split("Provider=Microsoft.,Jet.OLEDB.4,.0;Extended Properties=Excel ,8,.0;Data Source=", ",")
If Application.Version > 12 Then i(1) = "ACE.OLEDB.12": i(3) = 12
Set cn = CreateObject("adodb.connection"): cn.Open Join(i, "") & ThisWorkbook.FullName

Set sj = Sheets("交易明細2"): Set SQ = Sheets("SQL")
For i = 8 To 40 Step 16
Set s = Sheets(sj.Cells(i - 2, "B").Value)
E1 = "'%" & sj.Cells(i - 2, "E") & "%'"
B1 = "[" & sj.Cells(i - 2, "B") & "$A1:T]  "
    For j = 1 To 9
    sj.Cells(i, SQ.Cells(j, 3).Value).Resize(12, 1) = 0
    repSQL = Replace(Replace(SQ.Cells(j, 2), "'%GN%'", E1), "[M明細帳$A1:T]", B1)
    sj.Cells(i, SQ.Cells(j, 3).Value).CopyFromRecordset cn.Execute(repSQL)
    Next
Next
End Sub
作者: shuo1125    時間: 2024-1-19 12:22

回復 12# 准提部林
准大不好意思再打擾~~
請問[交易明細!AL:BF]餘額，這裡有個狀況....
若該月無交易明細，可以讓餘額承襲前期數字嗎?
<說明>
-----------------------------------------------------------------------------
1月餘額$100，然後中間都無變化...到12月餘額變$150，
此時1月有餘額$100，若中間都沒變化，那2-11月都會是一樣餘額$100，
只有12月當月餘額會變為是$150。
11201 $100
11202 $100
11203 $100
11204 $100
11205 $100
11206 $100
11207 $100
11208 $100
11209 $100
11210 $100
11211 $100
11212 $150
-----------------------------------------------------------------------------
若以您原提供的代碼有辦法實現嗎?
感謝您!!!
作者: 准提部林    時間: 2024-1-19 14:32

回復 33# shuo1125

加一段//
[attach]37312[/attach]
作者: shuo1125    時間: 2024-1-19 15:11

回復 34# 准提部林
我想破頭都無法實現，
准大真的太神了....完全符合需求，太感謝了!!!!
作者: Andy2483    時間: 2024-1-19 15:55

回復 34# 准提部林
回復 35# shuo1125


    請教前輩
雖然12月沒有交易,但是如果AV19這餘額的值要承襲AV18的餘額 8686996 該如何處理
作者: 准提部林    時間: 2024-1-19 17:21

回復 36# Andy2483


將 and m >= mx 去掉~~
作者: 准提部林    時間: 2024-1-19 17:25

回復 35# shuo1125

最大月份要歸零
y = 0: Erase Brr: Mx = 0
作者: Andy2483    時間: 2024-1-19 17:28

回復 38# 准提部林


    謝謝前輩 我再找時間試試看
作者: shuo1125    時間: 2024-1-20 08:46

回復 36# Andy2483
回復 38# 准提部林
感謝Andy大把可能性都想進去了.....
也感謝准大再三幫忙!!
測試發現有個較特殊的狀況餘額會異常，就是當該月交易後餘額剛好為0，且往後月份皆無交易..
會導致當月及後續餘額也異常...
以下例子:
1-10月有其交易與餘額，11月交易後餘額變0，12月剛好也無交易，
變成11211.11212餘額都異常了。
-----------------------------------------------------
實際                                                            實際
11201 $150   11207 $150
11202 $130   11208 $150
11203 $110   11209 $150
11204 $100   11210 $150
11205 $150   11211 $0
11206 $140   11212 $0
------------------------------------
執行
11201 $150   11207 $150
11202 $130   11208 $150
11203 $110   11209 $150
11204 $100   11210 $150
11205 $150   11211 $150
11206 $140   11212 $150
------------------------------------
Brr(x + 4)(M, 0) = 0 這段判斷式的關係...不知道如何修正?
謝謝!!!
作者: 准提部林    時間: 2024-1-20 09:20

回復  Andy2483
回復  准提部林
感謝Andy大把可能性都想進去了.....
也感謝准大再三幫忙!!
測試發現有 ...
shuo1125 發表於 2024-1-20 08:46



If Brr(x + 4)(M, 0) = 0 And M <= Mx Then Brr(x + 4)(M, 0) = Brr(x + 4)(M - 1, 0)  
刪去"最大月份"的判別即可!!!
但是,,這樣的數據是失真的!!! 未來式餘額???
作者: shuo1125    時間: 2024-1-20 09:38

回復 41# 准提部林
准大好~
And M <= Mx該段是必須要存在的..
Brr(x + 4)(M, 0) = 0 這段一開始有想到，但我怎試都出不來...
您添增M,Mx及判斷式讓資料更完善...只是以下狀況會有問題....
(一)當該月交易後餘額剛好為0，往後皆無發生交易。
(二)1月有交易，2-12月都無交易。
以上，不知道怎解....謝謝准大!!!
作者: 准提部林    時間: 2024-1-20 10:19

回復 42# shuo1125


若只記錄到"最大月份"為止, 看38#的補充~~Mx在下次回圈前要歸零
作者: Andy2483    時間: 2024-1-20 10:56

本帖最後由 Andy2483 於 2024-1-20 11:08 編輯

回復 41# 准提部林
回復 42# shuo1125


    謝謝 准提部林前輩
請教 准提部林前輩:
AV20的公式要怎麼下才能在AV20顯示 8686996 ,而不是0,  或者VBA要怎麼改才能讓AV20填入最後年度餘額 8686996
我猜樓主的需求應該是這個
作者: shuo1125    時間: 2024-1-20 11:28

回復 42# shuo1125
抱歉更正一下…
（二）這個紀錄最大月就不會有該問題
也謝謝Andy大提問，感謝！！
作者: 准提部林    時間: 2024-1-20 11:31

回復 44# Andy2483

只是一個判斷//
[attach]37323[/attach]
作者: Andy2483    時間: 2024-1-20 11:38

本帖最後由 Andy2483 於 2024-1-20 12:39 編輯
If Brr(x + 4)(M, 0) = 0 And M
准提部林 發表於 2024-1-20 09:20



    謝謝前輩,如果承襲前一個月的餘額是失真了
如下所示VBA怎麼設計第20列的值
[attach]37324[/attach]
假設1049124之後的9月剛好餘額是0而且10~12月無交易,20列要寫入0
假設8686996之後的12月是無交易 ,20列要寫入8686996
樓主應該是要AV20~BF20呈現每項的年度最後餘額,如果VBA執行完後再下公式抓餘額會有問題,所以應該要在VBA把AV20~BF20寫入最後餘額
謝謝前輩指導
作者: 准提部林    時間: 2024-1-20 12:47

本帖最後由 准提部林 於 2024-1-20 13:07 編輯

回復 47# Andy2483


那一行是公式//
=IF(OR(AV8:AV19,0),LOOKUP(1,0/AV8:AV19,AV8:AV19),0)
作者: Andy2483    時間: 2024-1-20 12:56

本帖最後由 Andy2483 於 2024-1-20 12:58 編輯

回復 48# 准提部林


    AV20~BF20呈現每項的年度最後餘額,如果VBA執行完後再下公式抓餘額會有問題,所以應該要在VBA把AV20~BF20寫入最後餘額
[attach]37325[/attach]
不好意思問得不好
作者: 准提部林    時間: 2024-1-20 13:08

本帖最後由 准提部林 於 2024-1-20 13:10 編輯

回復 49# Andy2483


公式是預先置入, 不用程式去填,  如其它欄位的 SUM 公式
[attach]37326[/attach]
作者: Andy2483    時間: 2024-1-20 13:22

本帖最後由 Andy2483 於 2024-1-20 13:28 編輯

回復 50# 准提部林


    謝謝前輩 可是假設如果如下圖情況 1049124之後的9月份餘額算完是0,且數據庫裡沒有這項10~12的紀錄
先下公式在儲存格好像與事實不符,20列的104912那格應該是0
[attach]37327[/attach]
作者: shuo1125    時間: 2024-1-20 14:00

本帖最後由 shuo1125 於 2024-1-20 14:03 編輯

回復 49# Andy2483
回復 50# 准提部林
Andy大抱歉...表達描述不好還要你一直幫忙補充，謝謝你!!
也感謝准大!
以圖來說明可能較清楚，抱歉浪費兩位時間....
也感謝你們幫忙!!!!!
------------------------------------
其實主因在於當月無交易，明細會完全沒有資料....
[attach]37328[/attach]
作者: 准提部林    時間: 2024-1-20 17:05

再看看//
[attach]37330[/attach]
作者: shuo1125    時間: 2024-1-20 17:31

回復 53# 准提部林
回復 52# shuo1125
謝謝Andy陸續提問!
怕誤解剛要再上傳兩邊比較差異....准大理解力真高，
測試各月數字已正確，感謝准大再三幫忙。
怕打擾兩位時間，若再有其他狀況我先嘗試自行解決，感激不盡!!!
作者: Andy2483    時間: 2024-1-20 17:46

謝謝兩位前輩，後學學到很多知識
作者: Andy2483    時間: 2024-1-22 12:48

回復 53# 准提部林


    再請教前輩,如下圖的情況要怎麼改?
[attach]37332[/attach]
作者: 准提部林    時間: 2024-1-22 13:52

回復 56# Andy2483

12月沒交易, 填上金額反而怪怪~~
作者: Andy2483    時間: 2024-1-22 13:53

本帖最後由 Andy2483 於 2024-1-22 13:55 編輯

回復 57# 准提部林


    懂  可是長官要看每個月的前幾個月結餘餘額,所以抬頭改成累計餘額
除非1月及整年都沒交易才會有0出現
作者: 准提部林    時間: 2024-1-22 13:59

回復 58# Andy2483


填滿最後一個月餘額
    For x = 8 To 18 ' 2024.01.20
        For M = 1 To Mx
            Srr(x + 4) = Brr(x + 4)(M, 0) '記錄最後一月餘額
        Next M
        For M = Mx + 1 To 12
            Brr(x + 4)(M, 0) = Brr(x + 4)(Mx, 0)
        Next
    Next x
作者: shuo1125    時間: 2024-1-22 14:01

本帖最後由 shuo1125 於 2024-1-22 14:06 編輯

回復 57# 准提部林
回復 56# Andy2483
准大/Andy大好~
我這裡說明一下好了....該表單的用意為同時記錄交易的明細/餘額。
----------------------------------------
交易明細[H:AI]>>交易 (單月)
交易明細[AL:BF]>>餘額 (累計到各月)
----------------------------------------
餘額其實是指各月交易後累計餘額。
但因准大代碼提供是針對交易明細來做....
餘額是我自行修改的...未考慮到眾多變化導致有異常狀況....
餘額是會承襲前期(當月無交易，交易雖是0，累積餘額沒有變化。)
----------------------------------------
例:科目19152 只有 7 月 跟 9月有發生交易。(G明細帳)
(錯誤)       餘額                 (正確)       餘額
月份         本幣金額         月份         本幣金額
01月        0                         01月        0
02月        0                         02月        0
03月        0                         03月        0
04月        0                         04月        0
05月        0                         05月        0
06月        0                         06月        0
07月        900,000            07月        900,000
08月        0                         08月        900,000
09月        1,800,000         09月        1,800,000
10月        0                         10月        1,800,000
11月        0                         11月        1,800,000
12月        0                         12月        1,800,000
小計        0                         小計        1,800,000
----------------------------------------
若要做各月的累計餘額會有下列問題:
因為當Brr(x + 4)(M, 0) = 0 >>有可能是該月交易剛好0..
而餘額可能會有連續單月都無交易>>變成M-1.M+1都無法用...
描述得不好...不知兩位是否能理解~
謝謝你們!!!
作者: 准提部林    時間: 2024-1-22 14:12

34# 不是給段代碼了, 將無交易的月份沿用上月或更上月的餘額
1//只填至有交易最大月份
        For M = 2 To 12
            If Brr(x + 4)(M, 0) = 0 And M <= Mx Then Brr(x + 4)(M, 0) = Brr(x + 4)(M - 1, 0)
        Next M
2//填滿12個月
        For M = 2 To 12
            If Brr(x + 4)(M, 0) = 0 Then Brr(x + 4)(M, 0) = Brr(x + 4)(M - 1, 0)
        Next M

只要從2月開始斷即可, 1月若無交易, 要取去年底的,
那明細表中就要先建立一個上期餘額, 且交易日為一月一日, 類似會計帳的"上期結轉"
作者: shuo1125    時間: 2024-1-22 14:18

回復 61# 准提部林
准大好~
34#我有用過沒問題~
但Brr(x + 4)(M, 0) = 0
會有個狀況是...當該月交易後餘額剛好為0。
會變成承襲上期了.....謝謝你!!
作者: 准提部林    時間: 2024-1-22 14:24

加入"有交易月份"的陣列參數Mrr//
[attach]37333[/attach]
作者: 准提部林    時間: 2024-1-22 14:27

模擬資料要能測出各種狀況,
否則言辭往返都是多花時間~~
作者: shuo1125    時間: 2024-1-22 14:54

本帖最後由 shuo1125 於 2024-1-22 14:56 編輯

回復 64# 准提部林
造成兩位花費不少時間理解深感抱歉....
我上傳個範例，並對某科目做餘額差異對照，希望這樣能較清楚表達!!
謝謝你們花費時間幫忙!!!
[attach]37334[/attach]
作者: 准提部林    時間: 2024-1-22 15:20

回復 65# shuo1125

Xrr@(1 To 12, 0) 這造成很大的困擾, 有交易沒交易都是0, 無法作判斷,
改成 Xrr(1 To 12, 0),  最後"空格"都取代為0
[attach]37336[/attach]
作者: shuo1125    時間: 2024-1-22 15:35

回復 66# 准提部林
回復 56# Andy2483
感謝准大一再指導...
也謝謝Andy一直幫助釐清需求~
一直都被既定的想法侷限了...兩位的做法千變萬化!
依據不同需求各有不同解法，需求描述不清我會再檢討....
感謝兩位幫助!!!!
作者: 准提部林    時間: 2024-1-22 15:51

這也應改改//
        For M = 1 To Mx
            If Brr(x + 4)(M, 0) <> "" Then Srr(x + 4) = Brr(x + 4)(M, 0) '記錄最後一月餘額
        Next M
作者: shuo1125    時間: 2024-1-22 16:07

回復 68# 准提部林
我才剛注意到小計數字...准大又先提示修改了。
謝謝您了!!!



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