Board logo

標題: [發問] 報表消帳扣除數量 [打印本頁]
作者: home1913    時間: 2014-3-1 22:47     標題: 報表消帳扣除數量

請教一下各位大師，目前報表需使用到類SAP功能.
例如附件中D欄如有對應到各點，各點總量扣除購買量的剩餘數.
感謝大師們指導.
[attach]17627[/attach]
作者: sunnyso    時間: 2014-3-2 09:24

回復 1# home1913


    可否再詳細點解說
作者: home1913    時間: 2014-3-2 12:18

如D3.D4來說,數量=A2前7碼對應到(G1:K1)後,D3.D4需使用右表中相同貨號之數量扣除C3.C4後的數量.
而D6數量=A5前7碼對應到(G1:K1)後,扣除先前剩餘的數量(EX3-C6).

PS.條件A欄必須要和右表(G1:K1)有相對應才可扣除相同貨號的數量.
以上.感謝
作者: yen956    時間: 2014-3-4 10:20

回復 3# home1913
試試看:
  1. Option Base 1
  2. Option Explicit
  3. Private Sub CommandButton1_Click()
  4.     Dim i, j, 列數, Barcode數, 原庫存量 As Integer
  5.     Dim code1, code2, 欄名 As String
  6.     Dim findCell, findRng As Range
  7.    
  8.     Barcode數 = [B1].End(xlDown).Row - 1
  9.     '1. 插入輔助欄 欄A(手動)
  10.    
  11.     '2. 將 欄C 貨號合併, 且 欄A 填入遞增數列
  12.     [C2].Resize(Barcode數, 1) = ""
  13.     [E2].Resize(Barcode數, 1) = ""
  14.     For i = 2 To Barcode數 + 1
  15.         Cells(i, 1) = i
  16.         If Left(Cells(i, 2), 1) = "w" Then
  17.             code1 = Left(Cells(i, 2), 7)
  18.         Else
  19.             code2 = Left(Cells(i, 2), 6)
  20.             Cells(i, 3) = code1 & code2
  21.         End If
  22.     Next
  23.    
  24.     '3. 按 主鍵 欄C, 次鍵 欄A 遞增排序
  25.     [A1].Resize(Barcode數 + 1, 5).Sort _
  26.            Key1:=Range("C1"), Order1:=xlAscending, _
  27.            Key2:=Range("A1"), Order2:=xlAscending, _
  28.            Header:=xlYes
  29.               
  30.     '4. 重新寫入公式, 防操作不小心刪掉公式
  31.     [F3] = "=MATCH(F2,G2:G7,0)"    '0 →精確查詢, 不可省略, Lookup_array 可以依任意次序排列。
  32.     [F6] = "=MATCH(F5,H1:L1,0)"    '1 →可省略, 找到等於或僅次於 lookup_value 的值, Lookup_array必須遞增排序
  33.                                    '-1→不可省略, 會找到等於或大於 lookup_value 的最小值, Lookup_array必須遞減排序
  34.     [F8] = "=INDEX(H2:L7,F3,F6)"   '傳回查詢範圍, 列與欄 的交义點的值
  35.    
  36.     '以上三列為 庫存表固定大小 使用,
  37.     '若 庫存表固定大小 會變動, 請改用下列五列
  38. '    列數 = CStr([G1].End(xlDown).Row)
  39. '    欄名 = Chr([G1].End(xlToRight).Column + 63)
  40. '    [F3] = "=MATCH(F2,G2:G" & 列數 & ",0)"
  41. '    [F6] = "=MATCH(F5,H1:" & 欄名 & "1,0)"
  42. '    [F8] = "=INDEX(H2:" & 欄名 & "7,F3,F6)"
  43.    
  44.     '5. 主程式
  45.     i = 1
  46.     Do
  47.         i = i + 1
  48.         If Cells(i, 3) = "" Then GoTo done1:
  49.         [F5] = Left(Cells(i, 3), 7)        '將Barcode存到 [F5], 供 MATCH 查詢"欄"用
  50.         [F2] = Right(Cells(i, 3), 6)       '將貨號存到 [E2], 供 MATCH 查詢"列"用
  51.         '[F8] = 庫存表中的 庫存量
  52.         Cells(i, 5) = [F8] - Cells(i, 4)   '存量 = 原庫存量 - 銷售量
  53.         
  54.         '如果 貨號 重覆, 第二筆開始, 則
  55.         If Cells(i, 3) = Cells(i + 1, 3) Then
  56.             Do
  57.                 i = i + 1
  58.                 If Cells(i, 3) = "" Then GoTo done1:
  59.                 Cells(i, 5) = Cells(i - 1, 5) - Cells(i, 4)     '存量 = 上一筆存量 - 銷售量
  60.             Loop Until i > Barcode數 Or Cells(i, 3) <> Cells(i + 1, 3)
  61.         End If
  62.     Loop Until i > Barcode數
  63.    
  64. done1:
  65.     '6. 按原序排序
  66.         [A1].Resize(Barcode數 + 1, 5).Sort _
  67.               Key1:=Range("A1"), Order1:=xlAscending, _
  68.               Header:=xlYes
  69. End Sub
複製代碼




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