帖子: 121
主題: 8
精華: 0
積分: 170
點名: 62
作業系統: win10
軟體版本: Office2016
閱讀權限: 20
性別: 男
註冊時間: 2021-1-21
最後登錄: 2024-8-20

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 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)=固定資產科目貸方增加數。
目前都暫不考慮原幣數，因為外幣都只填在摘要而已，不知道如何處理..
附上附檔..在勞煩能者出手簡化了，感謝不盡!

TEXT交易統計GM_20230430.zip (136.42 KB)

樓主熱帖

singo1232001

　暱稱: 熱狗慶

中學生 Rank: 2

帖子: 354
主題: 5
精華: 0
積分: 387
點名: 0
作業系統: windows7
軟體版本: vba,vb,excel2007
閱讀權限: 20
性別: 男
註冊時間: 2017-1-8
最後登錄: 2024-8-2

論壇榮譽勳章論壇贊助勳章

2^#

發表於 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

TEXT交易統計GM_20230430.zip (137.09 KB)

TOP

shuo1125

中學生 Rank: 2

帖子: 121
主題: 8
精華: 0
積分: 170
點名: 62
作業系統: win10
軟體版本: Office2016
閱讀權限: 20
性別: 男
註冊時間: 2021-1-21
最後登錄: 2024-8-20

3^#

發表於 2023-12-15 11:49 | 只看該作者

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

TOP

singo1232001

　暱稱: 熱狗慶

中學生 Rank: 2

帖子: 354
主題: 5
精華: 0
積分: 387
點名: 0
作業系統: windows7
軟體版本: vba,vb,excel2007
閱讀權限: 20
性別: 男
註冊時間: 2017-1-8
最後登錄: 2024-8-2

論壇榮譽勳章論壇贊助勳章

4^#

發表於 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 太多限制

TOP

shuo1125

中學生 Rank: 2

帖子: 121
主題: 8
精華: 0
積分: 170
點名: 62
作業系統: win10
軟體版本: Office2016
閱讀權限: 20
性別: 男
註冊時間: 2021-1-21
最後登錄: 2024-8-20

5^#

發表於 2023-12-15 23:46 | 只看該作者

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

TOP

jackyq

中學生 Rank: 2

帖子: 320
主題: 6
精華: 0
積分: 310
點名: 0
作業系統: xp
軟體版本: 2k
閱讀權限: 20
性別: 男
註冊時間: 2015-6-24
最後登錄: 2024-12-21

6^#

發表於 2023-12-16 23:19 | 只看該作者

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

回復 5# shuo1125

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

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

有很多時候流程寫不好是因為少了一些關鍵性功能函數
所以只能用暴力式流程

TOP

singo1232001

　暱稱: 熱狗慶

中學生 Rank: 2

帖子: 354
主題: 5
精華: 0
積分: 387
點名: 0
作業系統: windows7
軟體版本: vba,vb,excel2007
閱讀權限: 20
性別: 男
註冊時間: 2017-1-8
最後登錄: 2024-8-2

論壇榮譽勳章論壇贊助勳章

7^#

發表於 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都是秒解

TOP

准提部林