多條件計算交易明細
各位前輩好!資料庫為明細帳，依交易明細中的交易對象.科目.日期.條件計算相對應之交易情形，
※明細帳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)=固定資產科目貸方增加數。
目前都暫不考慮原幣數，因為外幣都只填在摘要而已，不知道如何處理..
附上附檔..在勞煩能者出手簡化了，感謝不盡! [i=s] 本帖最後由 singo1232001 於 2023-12-15 10:46 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=121124&ptid=23976]1#[/url] [i]shuo1125[/i] [/b]
這個題目
確實有正規的作法 而且 只能用這個做法
看到你的代碼後 我認為你該走向這方向
這種 不同多種 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] [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122281&ptid=23976]2#[/url] [i]singo1232001[/i] [/b]
慶大好~
有看過你分享的SQL寫法，你也是熱心人士，
網上針對VBA引用ADODB的範例確實很少...
但尚未花時間去理解主因是...若VB我都尚未理解透徹及應用後，就去學習其他領域感覺會更吃力....
後來我還是直接分開寫程序及引用多字典，暫時能解決問題便好，
(但這缺點就是當出現新型態交易，就要再調整代碼。)
因公司系統其實是用SQL開發的，所以我大概知道其實以SQL調用是最佳解，無奈程度有限只能漸進學習了...
感謝關注此帖並提供方向，謝謝您!!! [i=s] 本帖最後由 singo1232001 於 2023-12-15 18:35 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122284&ptid=23976]3#[/url] [i]shuo1125[/i] [/b]
從我目前的觀察 就當前你的功力 基本摸到門檻很夠了
若沒加廣度 儘早跳到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 太多限制 [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122287&ptid=23976]4#[/url] [i]singo1232001[/i] [/b]
慶大好~
應該說當時會開始接觸VB是為簡化作業，因公司無法隨意使用其他軟體及程式，所以能用的也只有XL...
但也如同你說因業務性質關係，在剛入門完全不懂的情況下看了相關書籍及網上範例還是無法運用在工作上，
得知此論壇上來提問後獲得准大的幫助，一開始當然只是想解決問題，慢慢去理解後才發現...
原來我是得到多麼厲害的高手的幫助，漸漸地才逐步逐項的去學習，當然准大的邏輯跟技巧非常人所及.....
不過隨著多看高手的程序及編碼，從連迴圈是什麼都不知道，也受到眾多人的幫助，開始能大概調適代碼簡易的運用在工作上，
雖然確實搭不上邊，但還是希望能慢慢更深入了解VB，就算失敗也無妨，畢竟是自己學習用。
-------------------------------------------
公司使用自主開發的SQL作為ERP系統，但財務方面的整合較少，這也是為什麼會想要盡可能利用VB減少手工作業，
而之所以會說最佳解是因為看過資訊寫的編程直接能調用資料庫帶出所需資訊。
所以你建議我直接學習SQL是怕我走太多彎路:)
會再抽空去學習~
-------------------------------------------
您辛勞地分析利弊，熱情的提供建議和指導，論壇真的充滿熱心專業的人士。
感謝您及大家!!!! [i=s] 本帖最後由 jackyq 於 2023-12-16 23:27 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122288&ptid=23976]5#[/url] [i]shuo1125[/i] [/b]
你寫法的優點就是 它能暫時解燃眉之急
這很重要 很多時候 解燃眉之急比什都重要
缺點是 它是一種炸彈式寫法 以後100%會引爆
引爆日就是你code 流程全部重新改寫之日
如果非要用 VBA 我不會想用字典
我會用排序 但我不會去用 EXCEL 自帶的多欄排序
我會去弄一個用 VBA實現的多欄排序函數
如果你能自己弄一個多欄排序函數
你看著那個你自己搞出來的函數
你的腦子裡應該就自然會浮現出 後續完美流程寫法
有很多時候 流程寫不好 是因為少了一些關鍵性功能函數
所以只能用暴力式流程 [i=s] 本帖最後由 singo1232001 於 2023-12-17 00:43 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122288&ptid=23976]5#[/url] [i]shuo1125[/i] [/b]
才想到 目前這題的主要問題是之前給的"資料有問題有缺"
我可以用vba+SQL 幫忙試作
但問題是檔案內的資料有問題 所以我也不知道要怎麼作
要重新給對的資料 不然應該早有人幫忙解題了 或者補充說明一下 是否有哪些流程沒提到
另外 可以一次提3~5種 不同的查詢需求 我用adodb應該可以馬上生出來
例如
第1題. 2023,1~12月 模糊名稱 5開頭 A表
第2題. A表(2022,某月 非某名稱 6開頭 )+B表(N年,N月 要某名稱 6開頭 N金額區間) 有相同的才算 或 金額誤差內 或相同日期
...
...
像上面這類題目 SQL 配GPT都是秒解 只能給個"模版"設計方向, 統計金額可能要自行去確認及修改, 大概猜著寫//
[attach]37136[/attach] [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122289&ptid=23976]6#[/url] [i]jackyq[/i] [/b]
J大好~
確實是為了先快速得到結果來做...您講得沒錯，當交易形態越多維護就越不易，到頭來可能就要打掉重練了...
感謝給了另外一個方向去思考，但因我連常規都寫不好，所以暫不考慮以自定義來做...
謝謝你!! [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122290&ptid=23976]7#[/url] [i]singo1232001[/i] [/b]
慶大好~
因財務資料較為敏感...故大部分只能暫時上傳模擬數據來提問..
若您想測試資料，有空我在整理測試資料上傳，
謝謝您!! [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122292&ptid=23976]8#[/url] [i]准提部林[/i] [/b]
准大好~
又勞煩您出手幫忙了，我抽空測試後再給予回饋，
百忙之際還幫忙註解及協助，大恩不言謝... 稍修:
1) A6"資訊"兩字前有空格, 加入TRIM去除
2) 增加工作表簡碼對照表
3) "自調"金額與你模擬有出入, 已修改, 可再自行調修
4) 取得來源工作表(排序後加入陣列), 捨棄字典用法, 避免工作表太多, 使字典帶太多陣列資料而佔用資源,
改用"當工作表名稱改變時"才執行排序取陣列, 所以, 同一工作表的順序必須連續, 才不用一直反覆排序!!
[attach]37137[/attach] [i=s] 本帖最後由 shuo1125 於 2023-12-18 13:46 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122296&ptid=23976]12#[/url] [i]准提部林[/i] [/b]
准大好~
剛開始試著理解#8的代碼，又馬上給了另一方式..
#8基本上已經完成近90%....唯一需要調整只是因為原提供邏輯未完善造成。
您的思路細膩，字典嵌套用的高段...
原代碼調試了兩段:
-----------------------------------------------------
一)
收入上期自調=只看摘要有"上期"的貸方金額
[color=Red]S1 = InStr(Arr(i, 10), "上期"): S2 = InStr(Arr(i, 10), "本期") '判斷為前期/本期[/color]
If (InStr(Tx(2), T) > 1) [color=Red]* (S1 > 0)[/color] Then
Brr(2)(M, 0) = Brr(2)(M, 0) + V17 '02.自調i
End If
收入本期自調=只看摘要有"本期"的借方-貸方金額
If (InStr(Tx(2), T) > 1) [color=Red]* (S2 > 0) [/color]Then
Brr(3)(M, 0) = Brr(3)(M, 0) + V16 - V17 '03.自調j
End If
二)
進貨的暫估位置稍反
[color=Red] If (P1 = 0) + P2 Then Brr(7)(M, 0) = Brr(7)(M, 0) + V16 - V17 '07.進貨(未稅)(B)[/color]
If P1 > 0 Then
[color=Red]Brr(6)(M, 0) = Brr(6)(M, 0) + V17 '06.上期暫估金額(A)[/color]
-----------------------------------------------------
另想請問....若該月無交易的金額要顯示0該如何做?
謝謝您~ [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122297&ptid=23976]13#[/url] [i]shuo1125[/i] [/b]
另想請問....若該月無交易的金額要顯示0該如何做?
定義Xrr的型態//
若都整數:
Xrr(1 To 12, 0) As Long
若有小數又比較適合財務數字:
Xrr(1 To 12, 0) As Currency [i=s] 本帖最後由 shuo1125 於 2023-12-18 14:01 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122298&ptid=23976]14#[/url] [i]准提部林[/i] [/b]
准大好~
...忘記這方式就可以，感謝您的指導!
Xrr[color=Red]&[/color](1 To 12, 0)。 [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122297&ptid=23976]13#[/url] [i]shuo1125[/i] [/b]
[color=Red]S1 = InStr(Arr(i, 10), "上期"): S2 = InStr(Arr(i, 10), "本期") '判斷為前期/本期[/color]
S1/S2...可沿用P1/P2, 它是隨用隨動的 不會有衝突, 免得變數太多維護不易// [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122300&ptid=23976]16#[/url] [i]准提部林[/i] [/b]
准大好~
我想得太淺...感謝指導!!(您以立沖帳來看更直覺)
該表單我後續還有增加實科目的餘額，不過實科目較單純，直接抓該月餘額而已。
因目前只抓虛科目的交易，您提供的模板及方式，我再自行調適看看...
太感謝一直以來的幫忙了!!! [i=s] 本帖最後由 singo1232001 於 2023-12-18 18:08 編輯 [/i]
[attach]37139[/attach][b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122301&ptid=23976]17#[/url] [i]shuo1125[/i] [/b]
之前有做過一個查詢介面 剛好趁這次練習一下
[ [i=s] 本帖最後由 singo1232001 於 2023-12-18 22:19 編輯 [/i]
[b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122301&ptid=23976]17#[/url] [i]shuo1125[/i] [/b]
修正一些功能 增加排除字 跟說明
目前只有表名中含有 "明細表"的 表才會被篩選
[聯集交集] O滿足任一條件 , X全部條件滿足
[順排0逆排1] 該欄位 0-順排 1-逆排 空白-不排列 (只能選一欄 )
[模糊;;] 模糊比對 [完整;;] 完整比對
[小於] 數字小於 [大於] 數字大於
[模糊排除字] 排除掉某個字
其他1: 任一欄位 ;;符號 多條件 (大小於不可用) 滿足其一就有
其他2: 篩選順序是由上到下
其他3: 全部留空 全部顯示
其他4: 黑色部分 輸入無效 (再次點擊說明可關閉)
這種方式 就是把 SQL語法 動態化
改關鍵字 語法就會變化 大概是這樣的作法
大概就是一個 強化版的篩選功能 能排除字 合併表 模糊比對 大小於 順便排序
但我沒有深入測試 或許有bug
[attach]37143[/attach] [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=122306&ptid=23976]19#[/url] [i]singo1232001[/i] [/b]
慶大好~
感謝分享，之後若開始研究SQL這會是很好的範本可參閱。
謝謝您了!!