麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 請問如何自動列出資料庫資料 [打印本頁]

作者: urlsyu 時間: 2020-2-10 13:57 標題: 請問如何自動列出資料庫資料

本帖最後由 urlsyu 於 2020-2-10 14:00 編輯

請問大大，假設我有資料庫如下
想要將其轉置成報表內格式後A4印出，方便現場人員核對進出貨
例如我想調出某批號的規格0~6

這需要如何製作？謝謝

作者: hcm19522 時間: 2020-2-10 16:13

本帖最後由 hcm19522 於 2020-2-10 16:14 編輯

B2 右拉下拉{=IFERROR(INDEX(資料庫!$E:$E,SMALL(IF((OFFSET($A1,MATCH(1=1,$A2:$A999<>"",),)="規格"&資料庫!$D$2:$D$999)*(資料庫!$C$2:$C$999=$B$1),ROW($2:$999)),(ROW()-LOOKUP(1,0/($A$1:$A1<>""),ROW($1:1)))*10-10+COLUMN(A1))),"")

B2複製到各數量下一格右拉下拉

作者: urlsyu 時間: 2020-2-10 17:52

大大你好，我貼上以後是空白顯示
我之前爬文也是知道用OFFSET語法
但卡在如何判斷資料庫批號跟規格
請問你提供判斷規格依據是在那?

大感謝

作者: hcm19522 時間: 2020-2-10 21:31

回復 3# urlsyu

數組公式 :複製公式不含 "=" ,貼上後加 "=" ;最前 "{" 代表 shift+ctrl+enter 三鍵一起按所產生 ,非程式裡面

作者: urlsyu 時間: 2020-2-10 22:15

本帖最後由 urlsyu 於 2020-2-10 22:19 編輯

回復 4# hcm19522
大大你好
爬文爬的頭殼暈暈
成功了

大大大感恩:L

作者: hcm19522 時間: 2020-2-11 14:42

回復 5# urlsyu

https://blog.xuite.net/hcm19522/twblog/588890415

作者: urlsyu 時間: 2020-2-11 22:17

回復 6# hcm19522

請問大大
加入出貨資料後，想製作庫存資料，要如何判斷規格欄？

感恩~
[attach]31699[/attach]

作者: hcm19522 時間: 2020-2-12 10:30

回復 7# urlsyu

庫存 C3:I4=SUMIFS(資料庫!$E:$E,資料庫!$C:$C,$B3,資料庫!$D:$D,C$2)-SUMIFS(出貨!$E:$E,出貨!$C:$C,$B3,出貨!$D:$D,C$2)

作者: urlsyu 時間: 2020-2-16 14:38

回復 8# hcm19522

再次感謝大大
最後想將庫存只列出尚有庫存批號如檔案所列，研究好幾天寫不出來:dizzy:
[attach]31730[/attach]

感恩感恩

作者: hcm19522 時間: 2020-2-16 16:42

回復 9# urlsyu

庫存A3 下拉{=IFERROR("A000"&SMALL(IF(SUMIF(資料庫!B:B,"A000"&ROW($1:$9)-1,資料庫!$D:$D)-SUMIF(出貨!$C:$C,"A000"&ROW($1:$9)-1,出貨!$E:$E),ROW($1:$9)-1),ROW(A1)),"")

庫存B3 右拉下拉=IF($A3="","",SUMIFS(資料庫!$D:$D,資料庫!$B:$B,$A3,資料庫!$C:$C,B$2)-SUMIFS(出貨!$E:$E,出貨!$C:$C,$A3,出貨!$D:$D,B$2))

作者: urlsyu 時間: 2020-2-16 22:06

回復 10# hcm19522

大大你好
庫存A3 下拉{=IFERROR("A000"&SMALL(IF(SUMIF(資料庫!B:B,"A000"&ROW($1:$9)-1,資料庫!$D:$D)-SUMIF(出貨!$C:$C,"A000"&ROW($1:$9)-1,出貨!$E:$E),ROW($1:$9)-1),ROW(A1)),"")
這句寫法有個問題，萬一我的編號超過十筆(如：A0011)就無法使用

謝謝你

作者: hcm19522 時間: 2020-2-17 09:41

本帖最後由 hcm19522 於 2020-2-17 09:44 編輯

回復 11# urlsyu

=IFERROR("A"&TEXT(SMALL(IF(SUMIF(資料庫!B:B,"A"&TEXT(ROW($1:$99)-1,"0000"),資料庫!$D:$D)-SUMIF(出貨!$C:$C,"A"&TEXT(ROW($1:$99)-1,"0000"),出貨!$E:$E),ROW($1:$99)-1),ROW(A1)),"0000"),"")

作者: urlsyu 時間: 2020-2-17 21:13

回復 12# hcm19522

大大安安~
剛剛試過，不能正確顯示
[attach]31738[/attach]

大大感恩~

作者: hcm19522 時間: 2020-2-18 10:47

(輔助取批號不重複) 資料庫F1 下拉{=INDEX(B:B,MIN(IF((COUNTIF(F$1:F1,B$2:B$999)=0)*(B$2:B$999<>""),ROW($2:$999),4^8)))&""

庫存A3 下拉 {=IFERROR(INDEX(資料庫!F:F,SMALL(IF(SUMIF(資料庫!B:B,資料庫!F$2:F$99,資料庫!$D:$D)-SUMIF(出貨!$C:$C,資料庫!F$2:F$99,出貨!$E:$E),ROW($2:$99)),ROW(A1))),"")

作者: Andy2483 時間: 2023-12-14 14:43

謝謝論壇,謝謝各位前輩
後學藉此帖以1#範例練習陣列與字典,學習方案如下,請各位前輩指教
資料表:
[attach]37128[/attach]

結果表:
[attach]37129[/attach]

Option Explicit
Sub TEST()
Dim Brr, Crr(10, 11), Z, A, i&, R&, C%, T$, T3$, T4$, xR
'↑宣告變數
'Application.ScreenUpdating = False
Set Z = CreateObject("Scripting.Dictionary")
'↑令Z變數是字典
Brr = Range([資料庫!E2], [資料庫!A65536].End(3))
'↑令Brr變數是帶入範圍儲存格值的二維陣列
For i = 1 To UBound(Brr)
'↑設順迴圈!i從1到Brr陣列縱向最大索引列號
T3 = Trim(Brr(i, 3)): T4 = Trim(Brr(i, 4))
'↑令T3變數是i迴圈列第3欄Brr陣列值,令T4變數是i迴圈列第4欄Brr陣列值
If T3 = "" Or T4 = "" Then GoTo i01 Else T = T3 & "|" & T4
'↑如果規格或批號是空的!就跳到標示 i01位置繼續執行,
'否則就令T變數是以"|"符號連接T3與T4變數組成的新字串
A = Z(T): R = Z(T & "/r"): C = Z(T & "/c")
'↑令A變數是以T變數查Z字典回傳item值,
'令R變數是以T變數連接"/r"組成的新字串,查Z字典回傳item值
'令C變數是以T變數連接"/c"組成的新字串,查Z字典回傳item值
If Not IsArray(A) Then A = Crr: A(0, 0) = "批號": A(0, 1) = T3: R = 1: Z(T & "/規格") = "規格" & T4
'↑如果A變數不是陣列!就令A變數是Crr空陣列,令最左上角陣列值是 "批號"字串,
'令0列/1欄陣列值是T3變數(批號),令R變數是 1
'令T變數連接 "/規格"組成的新字串為key,item是 "規格"字串連接T4變數組成的新字串,納入Z字典中
C = C + 1
'↑令C變數累加 1
If C = 11 Then C = 1: R = R + 1
'↑如果C變數是 11,就令C=1,R+1 (陣列換行放結果值)
A(R, C) = Val(Brr(i, 5))
'↑令重量值放入陣列裡
Z(T & "/r") = R: Z(T & "/c") = C: Z(T) = A
'↑令列數與欄數紀錄在字典中
'令A二維陣列放回字典中
i01: Next
Sheets("報表").UsedRange.EntireRow.Delete
'↑令舊的結果刪除
Set xR = [報表!A1]
'↑令xR變數是物件 ("報表"工作表的[A1]儲存格)
For Each A In Z.KEYS
'↑設逐項迴圈!令A變數是 Z字典裡的key之一
If InStr(A, "/") Then GoTo A01 Else R = Z(A & "/r")
'↑如果A變數裡有包含"/"字元!就跳到標示A01位置繼續執行,否則就令R變數取出紀錄在Z字典的列數
With xR.Resize(R + 3, 12)
'↑以下是關於結果範圍儲存格的程序
   .Value = Z(A)
   '↑令該範圍儲存格值,以A變數查Z字典回傳的陣列值寫入
   .Cells(R + 3, 1) = Z(A & "/規格"): .Cells(R + 3, 2) = "數量": .Cells(R + 3, 4) = "小計:"
   '↑令該範圍儲存格最後列1,2,4欄儲存格寫入字串
   .Cells(R + 3, 3) = "=COUNT(" & xR.Resize(R, 12).Offset(1).Address & ")"
   '↑令該範圍儲存格最後列第3欄儲存格寫入非空格的儲存格數公式=COUNT()
   .Cells(R + 3, 5) = "=SUM(" & xR.Resize(R, 12).Offset(1).Address & ")"
   '↑令該範圍儲存格最後列第5欄儲存格寫入儲存格數值總和公式=SUM()
   For i = 7 To 10: .Borders(i).Weight = 4: Next
   '↑令該儲存格範圍最外圍設為粗格線
End With
Set xR = xR(R + 4, 1)
'↑令xR變數是下個結果範圍的第1格儲存格
A01: Next
End Sub

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