Tư vấn dùng code thay thế hàm index

[tu205489039]

Dạ chuyện chẳng là em đang viết 1 file báo cáo để gom dữ liệu mới phát sinh tháng này với tháng trước lại để hợp nhất lại thành 1 báo cáo nhưng số lượng công thức nhiều quá, khoảng hơn 80.000 công thức index(....)
Nên nhờ anh chị tư vấn giúp em nên viết code như thế nào để có thể không phải dùng công thức
File e mới viết nên sào nấu lại cấu trúc file thế nào cũng được ạ. e có ghi chú thêm trong file đính kèm
Em cảm ơn anh chị nhiều

 

[SA_DQ]

Nếu thật sự chỉ có không quá 65 mã hàng thì trang báo cáo nên xoay lại: Các cột là chứa mã HH & các dòng là ngày tháng tiếp nối nhau

Mà theo mình thì bạn đang thực hiện qui trình ngược để đi đến báo cáo thì phải.

 

[tu205489039]

Dạ nếu đúng thì sẽ có khoảng 200 mã có đầu số "20000....". Mỗi mã này sẽ có chi tiết 7 mã con đầu số "55....". Mỗi mã như vậy kéo dài trong 190 ngày
Nên nếu đảo qua lại giữa mã 20000 và ngày tháng thì cũng ko biến động nhiều lắm
Nhưng a nghĩ sao nếu e gom hết về cột ở tất cả các sheet.
Cụ thể: cột A chứa mã 20000...
cột B chứa mã 55...
cột C chứa ngày
Làm như thế này thì có khoảng 300.000 dòng dữ liệu
Nếu làm như vậy thì hướng xử lý có đơn giản hơn không ạ. Mong a tư vấn thêm giúp em ạ. E cảm ơn

Nếu thật sự chỉ có không quá 65 mã hàng thì trang báo cáo nên xoay lại: Các cột là chứa mã HH & các dòng là ngày tháng tiếp nối nhau

Mà theo mình thì bạn đang thực hiện qui trình ngược để đi đến báo cáo thì phải.

Bài đã được tự động gộp: 6/4/21

Ơ, hay làm như rứa rồi dùng consolidate nhỉ. Để mai e chạy thử
Nếu có cách nào khác nhờ a chị chỉ giáo e thêm ạ

 

[HieuCD]

Dạ chuyện chẳng là em đang viết 1 file báo cáo để gom dữ liệu mới phát sinh tháng này với tháng trước lại để hợp nhất lại thành 1 báo cáo nhưng số lượng công thức nhiều quá, khoảng hơn 80.000 công thức index(....)
Nên nhờ anh chị tư vấn giúp em nên viết code như thế nào để có thể không phải dùng công thức
File e mới viết nên sào nấu lại cấu trúc file thế nào cũng được ạ. e có ghi chú thêm trong file đính kèm
Em cảm ơn anh chị nhiều

Chạy code

Sub ABC()
  Dim aSheet, sArr(), aNgay(), aMa(), Res(), Dic As Object
  Dim n&, eRow&, sRow&, sR&, eCol&, sCol&, sC&, i&, iR&, j&, jC&
 
  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("Sheet1")
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    eCol = .Range("XFD3").End(xlToLeft).Column
    If eRow < 4 Or eCol < 4 Then MsgBox ("Khong co gi de tinh"): Exit Sub
    aMa = .Range("A4:A" & eRow).Value
    aNgay = .Range("D3", .Cells(3, eCol)).Value
    sRow = UBound(aMa):      sCol = UBound(aNgay, 2)
    ReDim Res(1 To sRow, 1 To sCol)
    For i = 1 To sRow
      If aMa(i, 1) <> Empty Then Dic.Item(aMa(i, 1)) = i
    Next i
    For j = 1 To sCol
      If aNgay(1, j) <> Empty Then Dic.Item(aNgay(1, j)) = j
    Next j
  End With
  aSheet = Array("data", "data2")
  For n = 0 To 1
    With Sheets(aSheet(n))
      eRow = .Range("A" & Rows.Count).End(xlUp).Row
      eCol = .Range("XFD2").End(xlToLeft).Column
      If eRow > 2 And eCol > 3 Then
        sArr = .Range("A2", .Cells(eRow, eCol)).Value
        sR = UBound(sArr):      sC = UBound(sArr, 2)
        For i = 2 To sR
          iR = Dic.Item(sArr(i, 1))
          If iR > 0 Then
            For j = 4 To sC
              jC = Dic.Item(sArr(1, j))
              If jC > 0 Then Res(iR, jC) = Res(iR, jC) + sArr(i, j)
            Next j
          End If
        Next i
      End If
    End With
  Next n
  Sheets("Sheet1").Range("D4").Resize(sRow, sCol) = Res
End Sub

 

[tu205489039]

Chạy code

Sub ABC()
  Dim aSheet, sArr(), aNgay(), aMa(), Res(), Dic As Object
  Dim n&, eRow&, sRow&, sR&, eCol&, sCol&, sC&, i&, iR&, j&, jC&

  Set Dic = CreateObject("scripting.dictionary")
  With Sheets("Sheet1")
    eRow = .Range("A" & Rows.Count).End(xlUp).Row
    eCol = .Range("XFD3").End(xlToLeft).Column
    If eRow < 4 Or eCol < 4 Then MsgBox ("Khong co gi de tinh"): Exit Sub
    aMa = .Range("A4:A" & eRow).Value
    aNgay = .Range("D3", .Cells(3, eCol)).Value
    sRow = UBound(aMa):      sCol = UBound(aNgay, 2)
    ReDim Res(1 To sRow, 1 To sCol)
    For i = 1 To sRow
      If aMa(i, 1) <> Empty Then Dic.Item(aMa(i, 1)) = i
    Next i
    For j = 1 To sCol
      If aNgay(1, j) <> Empty Then Dic.Item(aNgay(1, j)) = j
    Next j
  End With
  aSheet = Array("data", "data2")
  For n = 0 To 1
    With Sheets(aSheet(n))
      eRow = .Range("A" & Rows.Count).End(xlUp).Row
      eCol = .Range("XFD2").End(xlToLeft).Column
      If eRow > 2 And eCol > 3 Then
        sArr = .Range("A2", .Cells(eRow, eCol)).Value
        sR = UBound(sArr):      sC = UBound(sArr, 2)
        For i = 2 To sR
          iR = Dic.Item(sArr(i, 1))
          If iR > 0 Then
            For j = 4 To sC
              jC = Dic.Item(sArr(1, j))
              If jC > 0 Then Res(iR, jC) = Res(iR, jC) + sArr(i, j)
            Next j
          End If
        Next i
      End If
    End With
  Next n
  Sheets("Sheet1").Range("D4").Resize(sRow, sCol) = Res
End Sub

em phải nói là nó chạy nhanh hơn 1 cơn gió. lúc đầu click vào em tưởng code bị lỗi ko chạy mới ghê. em cảm ơn a nhiều ạ

 

[Mr_Fly]

chào mọi người, minh hiện cũng đang làm 1 bảng tính như vậy, tuy nhiên mình đang muốn viết 1 macro thay cho hàm consolidate trong excell để tự động việc consolidate đó.
không biết có ai có thể share cho mình được không?

 

[Maika8008]

chào mọi người, minh hiện cũng đang làm 1 bảng tính như vậy, tuy nhiên mình đang muốn viết 1 macro thay cho hàm consolidate trong excell để tự động việc consolidate đó.
không biết có ai có thể share cho mình được không?

Bạn ghi macro việc consolidate đó rồi chèn các biến vào code

 

[befaint]

Bạn ghi macro việc consolidate đó rồi chèn các biến vào code

Ở thớt khác họ được các thành viên chăm sóc tận răng rồi anh ơi.
Đúng là quăng lưới nhiều dễ túm được cá là có thật.

 

[Maika8008]

Ở thớt khác họ được các thành viên chăm sóc tận răng rồi anh ơi.
Đúng là quăng lưới nhiều dễ túm được cá là có thật.

Gặp cá mập là rách lưới he

 

[Mr_Fly]

Bạn ghi macro việc consolidate đó rồi chèn các biến vào code

như nào vậy bạn, mình không hiểu lắm

 

[Maika8008]

Ghi macro, biết chưa?
Ghi xong. Thứ gì có thể thay đổi được như tên sheet, địa chỉ vùng cần consolidate, nơi đặt kết quả... thì có thể tùy biến: đặt chúng vào biến rồi chèn vào đúng chỗ của chúng trong code.

Thế là có 1 cái sub dùng cho nhiều dữ liệu khác nhau chứ không nhất thiết là vùng vừa thực hiện.