Xin giúp đỡ về tham chiếu (1 người xem)

[hadoan-pap]

Xin chào mọi người!

Em có 1 bài toán về tham chiếu lập trình trong excel, nhưng do kiến thức còn hạn chế nên em rất mong mọi người xem và giúp đỡ ạ.

Em có gửi file đính kèm mô tả qua về 1 dạng tham chiếu mà em đang muốn xin giúp đỡ ạ. Em xin trình bày trong File như sau :

- Trong file có 2 bang. Bảng " Des " và bang " Origin ". Và các items trong bang " Des " sẽ được tham chiếu từ bang " Origin " ạ.

- Trong bang " Des " thì Item " Keo " sẽ đc tham chiếu lấy giá trị từ Item " A " trong bang " Origin "
Item " But " sẽ là tham chiếu là tổng của 2 item " B " & " C " trong bang " Origin "
Item " Muc " sẽ là tham chiếu của item " D " trong bang " Origin ". Nhưng là tham chiếu của D 1 ngày trước ó. Ví du : " Muc " ngày 15/3 sẽ là " D " của ngày 14/3.

Trong file ở bang " Des " em có input vài giá trị để mọi người xem qua ạ.

Trong file chính của em thì file data rất nhiều cột và hang ạ. Nên em gửi file mô tả bài toán same same như đính kèm ạ.

Rất mong các thầy và mọi người giúp em lập trình VBA để giải quyết bài toán này ạ.

Em xin trân trọng cảm ơn!

 

[hadoan-pap]

mình dùng Dictionary để tham chiếu đến từng mã, bạn xem ghi chú trong code và chỉnh lại theo file thực tế, nếu không làm được thì gởi file với các vùng dữ liệu sát thực tế, còn dữ liệu cho ví dụ thôi

Sub Button1_Click()
Dim Dic As Object, Sarr(), Darr(), Arr(), Tmp As String, i As Long, j As Long
Set Dic = CreateObject("scripting.dictionary")
Sarr = Range("B5:O12").Value  'Bang dieu kien lay ket qua
Darr = Range("C19:H28").Value 'Bang du lieu
ReDim Arr(1 To UBound(Sarr) - 1, 1 To 1) 'Ket qua cua 1 cot theo tung ngay
Application.ScreenUpdating = False
'gán giá tri tìm kiem vào Dic
For i = 2 To UBound(Darr) 'vùng giá tri bat dau o dòng thu 2
  For j = 3 To UBound(Darr, 2)  'vùng giá tri bat dau o cot thu 3
    Tmp = Darr(i, 1) & "#" & Darr(i, 2) & "#" & Darr(1, j)  ' tao Key, là dieu kien tìm giá tri
    Dic.Item(Tmp) = Darr(i, j)  'gán Item, là giá tri can lay o bang ket qua
  Next j
Next i
'gán ket qua vào tung cot, bat dau là cot thu 3, cot ke cách 3 cot trong bang Sarr
For j = 3 To UBound(Sarr, 2) Step 3
  For i = 2 To UBound(Sarr) 'dong dau gan ket qua là dòng 2 trong bang Sarr
    If Sarr(i, 1) <> "" Then
      If Sarr(i, 2) = "Keo" Then 'neu là "Keo" thì chuyen thành "A"=Darr(22)
        Tmp = Sarr(i, 1) & "#" & Darr(2, 2) & "#" & Sarr(1, j) 'tao Key cua Dic
        If Dic.exists(Tmp) Then Arr(i - 1, 1) = Dic.Item(Tmp)
      ElseIf Sarr(i, 2) = "But" Then '"B"=Darr(3,2), "C"=Darr(4,2)
        Tmp = Sarr(i, 1) & "#" & Darr(3, 2) & "#" & Sarr(1, j)
        If Dic.exists(Tmp) Then Arr(i - 1, 1) = Dic.Item(Tmp)
        Tmp = Sarr(i, 1) & "#" & Darr(4, 2) & "#" & Sarr(1, j)
        If Dic.exists(Tmp) Then Arr(i - 1, 1) = Arr(i - 1, 1) + Dic.Item(Tmp)
      ElseIf j > 3 Then ' khong tính cot dau tien, "D"=Darr(5,2)
        Tmp = Sarr(i, 1) & "#" & Darr(5, 2) & "#" & Sarr(1, j - 3) 'j-3 là lùi lai 3 cot, lay truoc do 1 ngay
        If Dic.exists(Tmp) Then Arr(i - 1, 1) = Dic.Item(Tmp)
      End If
    End If
  Next i
  Cells(6, j + 1).Resize(UBound(Arr)) = Arr 'gán ket qua vao tung cot, cot dau tien là cot "D" là cot thu 4=j+1 voi j=3
Next j
Application.ScreenUpdating = True
End Sub

Gửi bạn HieuCD.

Quả that để ngồi ghi chú note từng dòng code như bạn làm that sự k có nhiều người sẵn long giúp.

Mình chân thành cảm ơn bạn nhé.

Mình sẽ tự học để chỉnh sửa cho hợp lý với nhu cầu bài toán bên mình.

Một lần nữa cảm ơn bạn rất nhiều!

chúc bạn một ngày vui vẻ!

 

[hadoan-pap]

Gửi bạn HieuCD và mọi người!

Mấy hôm nay mình ngồi làm nhưng quả that không biết tham chiếu như nào vì lý do là bảng " origin " số Cột thì cố định nhưng có số Hàng rất nhiều và biến đổi liên tục, do đó số Hàng nó không cố định để mình dùng tham chiếu chính xác được.

Liệu có cách nào tham chiếu dựa vào mã như :

- Cùng là mã C1, Kẹo ở bảng " Des " sẽ bằng A ở bảng " Origin ".
- Cùng là mã C1, But ở bảng " Des " sẽ bang B + C ở bảng " Origin ".
- Cùng là mã C1, Muc ở bảng " Des " sẽ bang D ở bảng " Origin " ngày trước đó ( Ví dụ : Muc ngày 15/3 bảng " Des " sẽ bằng D ngày 14/3 ở bảng " Origin "

Vì số Hàng ở bảng " Origin " là rất nhiều và biến đổi liên tục ( vì nó có nhiều mã C1,C2,C3,C4,C5,C6.... ) nên không thể tham chiếu được như Code trong file đính kèm mình có gửi. Tuy nó có nhiều mã nhưng tương ứng với các mã đó thì số thứ tự hàng theo mã là cố định như định dạng file mình gửi.

Vậy rất mong HieuCD cùng mọi người giúp đỡ mình giải quyết bài toán này với nhé.

Ai mới xem vui long đọc lại giúp mình từ đầu nhé.

Mình xin cảm ơn!

 

[HieuCD]

Gửi bạn HieuCD và mọi người!
Mấy hôm nay mình ngồi làm nhưng quả that không biết tham chiếu như nào vì lý do là bảng " origin " số Cột thì cố định nhưng có số Hàng rất nhiều và biến đổi liên tục, do đó số Hàng nó không cố định để mình dùng tham chiếu chính xác được.
Liệu có cách nào tham chiếu dựa vào mã như :
- Cùng là mã C1, Kẹo ở bảng " Des " sẽ bằng A ở bảng " Origin ".
- Cùng là mã C1, But ở bảng " Des " sẽ bang B + C ở bảng " Origin ".
- Cùng là mã C1, Muc ở bảng " Des " sẽ bang D ở bảng " Origin " ngày trước đó ( Ví dụ : Muc ngày 15/3 bảng " Des " sẽ bằng D ngày 14/3 ở bảng " Origin "
Vì số Hàng ở bảng " Origin " là rất nhiều và biến đổi liên tục ( vì nó có nhiều mã C1,C2,C3,C4,C5,C6.... ) nên không thể tham chiếu được như Code trong file đính kèm mình có gửi. Tuy nó có nhiều mã nhưng tương ứng với các mã đó thì số thứ tự hàng theo mã là cố định như định dạng file mình gửi.
Vậy rất mong HieuCD cùng mọi người giúp đỡ mình giải quyết bài toán này với nhé.
Ai mới xem vui long đọc lại giúp mình từ đầu nhé.
Mình xin cảm ơn!

do file của bạn với vị trí của 2 bảng không đúng thực tế nên không thể viết code dạng tương đối tổng quát
bạn gởi lại file với vị trí thực sự của 2 bảng: cột dòng cụ thể và nằm ở sheet nào?

 

[hadoan-pap]

do file của bạn với vị trí của 2 bảng không đúng thực tế nên không thể viết code dạng tương đối tổng quát
bạn gởi lại file với vị trí thực sự của 2 bảng: cột dòng cụ thể và nằm ở sheet nào?

Dear HieuCD.

Cảm ơn bạn đã ghé qua.

File chính của mình do nhiều dữ liệu có tính bảo mật nên rất xin lỗi mình k đưa lên đc. Nhưng mình có mô tả nó thành file giống như file Tham Chiếu mà mình có gửi.

ở Bảng " Origin " thì số Hàng nó nhiều , k cố định vì có nhiều mã " C1,C2,C3,C4...C100 ". Nhưng tương ứng với từng Mã thì số hàng của nó là cố định. Ví dụ " Với Mã C1 hoặc C2 hoặc C3 thì có 4 Hàng với 4 mã con " A,B,C,D ".

ở Bảng " Des " thì chúng ta tham chiếu giá trị từ bang " Origin " lên. ví dụ bang " Des " chỉ có mã " C1,C2,C3,C4 " thì phai đc tham chiếu 4 mã này trong số rất nhiều mã từ bang " Origin "

Vì số hàng trong bảng " origin " là không cố định nên k tham chiếu tuyệt đối như kiểu " Drr(3,2) hay Drr(4,2) " được. Trong bảng " origin "chỉ có những cái như sau cố định :

- Cột cố định
- Tương ứng với từng mã " C1,C2...C100 " thì số hàng con theo nó như " A,B,C,D " là cố định
- Tên các mã là cố định.

Vậy nhờ bạn bớt chút thời gian hỗ trợ mình tìm cách tham chiếu theo mã chính C1, sau đó theo các mã con A,B,C,D với nhé.

Mình xin cảm ơn!

 

[befaint]

File chính của mình do nhiều dữ liệu có tính bảo mật nên rất xin lỗi mình k đưa lên đc. Nhưng mình có mô tả nó thành file giống như file Tham Chiếu mà mình có gửi.

Thì bạn xóa hết cái bảo mật đó đi. Viết cái gì đó không thật vào đó, miễn là giữ nguyên cấu trúc trong file để giải quyết vấn đề.

 

[hadoan-pap]

Mình xin gửi lại file nhé. Bảng " Origin " như trong file mình có gửi. Tương ứng với từng Mã chính thì số hàng theo nó là cố định. Ví dụ với từng mã như C1,C2 thì nó luôn có 4 hàng con " A,B,C,D.

Mình cảm ơn!

 

[hadoan-pap]

Thì bạn xóa hết cái bảo mật đó đi. Viết cái gì đó không thật vào đó, miễn là giữ nguyên cấu trúc trong file để giải quyết vấn đề.

... Thật ra file đó như nào thì giống hệt cái mình mô tả qua file mà mình có gửi bạn ạ. Nó không khác nhau đâu, chỉ là file chính thì nó lằng nhằng vì data rất nhiều và nhìn vào k quen mắt sẽ rất rối nên mình tóm tắt lại thành file " Tham Chieu " mà mình có gửi cho dễ nhìn, còn bản chat là như nhau ban ạ.

Số cột cố định, còn số hàng thì biến đổi liên tục mỗi khi chiết xuất data. Chỉ duy nhất là số hàng thay đổi, còn Tên Mã, Cột là không đổi.

Mình cảm ơn!

 

[HieuCD]

Mình xin gửi lại file nhé. Bảng " Origin " như trong file mình có gửi. Tương ứng với từng Mã chính thì số hàng theo nó là cố định. Ví dụ với từng mã như C1,C2 thì nó luôn có 4 hàng con " A,B,C,D.
Mình cảm ơn!

vấn đề của bạn là 2 dòng lệnh nầy, bạn tự sửa chổ màu đỏ

Sarr = [COLOR=#ff0000]Range("B5:O12")[/COLOR].Value  'Bang dieu kien lay ket qua
Darr = [COLOR=#ff0000]Range("C19:H28")[/COLOR].Value 'Bang du lieu

và vị trí trả kết quả

[COLOR=#ff0000]Cells(6, j + 1)[/COLOR].Resize(UBound(Arr)) = Arr

 

[Ba Tê]

... Thật ra file đó như nào thì giống hệt cái mình mô tả qua file mà mình có gửi bạn ạ. Nó không khác nhau đâu, chỉ là file chính thì nó lằng nhằng vì data rất nhiều và nhìn vào k quen mắt sẽ rất rối nên mình tóm tắt lại thành file " Tham Chieu " mà mình có gửi cho dễ nhìn, còn bản chat là như nhau ban ạ.

Số cột cố định, còn số hàng thì biến đổi liên tục mỗi khi chiết xuất data. Chỉ duy nhất là số hàng thay đổi, còn Tên Mã, Cột là không đổi.

Mình cảm ơn!

Bạn xem thử file này, tách 2 bảng 2 nơi cho khỏi đụng.
Dữ liệu thật không giống như trong file thì "tèo".

 

[hadoan-pap]

vấn đề của bạn là 2 dòng lệnh nầy, bạn tự sửa chổ màu đỏ

Sarr = [COLOR=#ff0000]Range("B5:O12")[/COLOR].Value  'Bang dieu kien lay ket qua
Darr = [COLOR=#ff0000]Range("C19:H28")[/COLOR].Value 'Bang du lieu

và vị trí trả kết quả

[COLOR=#ff0000]Cells(6, j + 1)[/COLOR].Resize(UBound(Arr)) = Arr

Gửi bạn HieuCD.

Số Hàng của bảng " origin " có thể thay đổi nhưng nó thay đổi k nhiều và mình có thể giới hạn đc cho nó ( ví dụ nó dao động từ 400-500 hàng, nhưng mình cứ để Range to hẳn ra nên k lo lắm ).

Cái khó là do số hàng trong bảng " origin " thay đổi thì khi đó đoạn Code bên dưới chạy k chính xác ở chỗ mình bôi đỏ :

If Sarr(i, 2) = "Keo" Then 'neu là "Keo" thì chuyen thành "A"=Darr(22)
Tmp = Sarr(i, 1) & "#" & Darr(2, 2) & "#" & Sarr(1, j) 'tao Key cua Dic
If Dic.exists(Tmp) Then Arr(i - 1, 1) = Dic.Item(Tmp)
ElseIf Sarr(i, 2) = "But" Then '"B"=Darr(3,2), "C"=Darr(4,2)
Tmp = Sarr(i, 1) & "#" & Darr(3, 2) & "#" & Sarr(1, j)
If Dic.exists(Tmp) Then Arr(i - 1, 1) = Dic.Item(Tmp)
Tmp = Sarr(i, 1) & "#" & Darr(4, 2) & "#" & Sarr(1, j)
If Dic.exists(Tmp) Then Arr(i - 1, 1) = Arr(i - 1, 1) + Dic.Item(Tmp)
ElseIf j > 3 Then ' khong tính cot dau tien, "D"=Darr(5,2)
Tmp = Sarr(i, 1) & "#" & Darr(5, 2) & "#" & Sarr(1, j - 3) 'j-3 là lùi lai 3 cot, lay truoc do 1 ngay
If Dic.exists(Tmp) Then Arr(i - 1, 1) = Dic.Item(Tmp)

Vậy nhờ bạn có thể giúp mình sửa lỗi này được không? Liệu có cách nào tham chiếu ( Với mã C1 thì 3 dòng con Keo,But,Muc ở bảng " Des " đc tham chiếu từ mã C1 với 4 dòng con A,B,C,D ở bảng " origin " ) đc k?

Cảm ơn bạn rất nhiều!

 

[hadoan-pap]

Bạn xem thử file này, tách 2 bảng 2 nơi cho khỏi đụng.
Dữ liệu thật không giống như trong file thì "tèo".

Cảm ơn Bate rất nhiều .

Vì yêu cầu file là 2 bảng này phải cùng trong 1 Sheet mới khó, mình cũng muốn tách k cho chung đụng nhưng thực tế nó k cho phép như vậy. Vì file gốc có mấy chục Sheet, mỗi Sheet lại làm them 1 Sheet nữa thì khi đó File sẽ rất to và hơi rối.

Liệu bạn có thể giúp mình tham chiếu 2 bảng đó trên cùng 1 Sheet đc k?

Mình sẽ lấy cách của bạn để làm phương án dự phòng trong trường hợp không giải quyết đc bài toán 2 bảng trên cùng 1 Sheet.

Cảm ơn bạn rất nhiều

 

[Ba Tê]

Cảm ơn Bate rất nhiều .

Vì yêu cầu file là 2 bảng này phải cùng trong 1 Sheet mới khó, mình cũng muốn tách k cho chung đụng nhưng thực tế nó k cho phép như vậy. Vì file gốc có mấy chục Sheet, mỗi Sheet lại làm them 1 Sheet nữa thì khi đó File sẽ rất to và hơi rối.

Liệu bạn có thể giúp mình tham chiếu 2 bảng đó trên cùng 1 Sheet đc k?

Mình sẽ lấy cách của bạn để làm phương án dự phòng trong trường hợp không giải quyết đc bài toán 2 bảng trên cùng 1 Sheet.

Cảm ơn bạn rất nhiều

Vì cả 2 bảng dữ liệu đều không liên tục (có dòng trống xen giữa) nên không thể xác định tổng quát mỗi bảng có bao nhiêu dòng.
Trên xuống bị dòng trống, dưới lên đụng bảng dưới.
Việc xác định vị trí các bảng dành cho bạn nhập thủ công vậy.
Chạy code này cho file của bạn.

Public Sub GPE()
Dim sArr(), dArr(), tArr(), I As Long, J As Long, C As Long, R As Long, Rws As Long, Col As Long
With CreateObject("Scripting.Dictionary")
    sArr = Range("D5", Range("D5").End(xlToRight)).Value
    For J = 1 To UBound(sArr, 2) Step 3
        .Item(sArr(1, J)) = J
    Next J
        sArr = Range("B6:B12").Value    '<------------Bang DES tu dong 6 den 12'
        C = J: R = UBound(sArr)
        ReDim dArr(1 To R, 1 To C)
        tArr = Range("C19:C28").Resize(, Range("E19").End(xlToRight).Column - 2).Value '<--Bang Origin tu dong 19 den 28'
    For I = 2 To UBound(tArr) Step 5
        .Item(sArr(I, 1)) = I
    Next I
        For I = 1 To UBound(sArr) Step 4
            Rws = .Item(sArr(I, 1))
            For J = 3 To UBound(tArr, 2)
                Col = .Item(tArr(1, J))
                dArr(I, Col) = tArr(Rws, J)
                dArr(I + 1, Col) = tArr(Rws + 1, J) + tArr(Rws + 2, J)
                dArr(I + 2, Col + 3) = tArr(Rws + 3, J)
            Next J
        Next I
    Range("D6").Resize(R, C) = dArr
End With
End Sub

 

[hadoan-pap]

Vì cả 2 bảng dữ liệu đều không liên tục (có dòng trống xen giữa) nên không thể xác định tổng quát mỗi bảng có bao nhiêu dòng.
Trên xuống bị dòng trống, dưới lên đụng bảng dưới.
Việc xác định vị trí các bảng dành cho bạn nhập thủ công vậy.
Chạy code này cho file của bạn.

Public Sub GPE()
Dim sArr(), dArr(), tArr(), I As Long, J As Long, C As Long, R As Long, Rws As Long, Col As Long
With CreateObject("Scripting.Dictionary")
    sArr = Range("D5", Range("D5").End(xlToRight)).Value
    For J = 1 To UBound(sArr, 2) Step 3
        .Item(sArr(1, J)) = J
    Next J
        sArr = Range("B6:B12").Value    '<------------Bang DES tu dong 6 den 12'
        C = J: R = UBound(sArr)
        ReDim dArr(1 To R, 1 To C)
        tArr = Range("C19:C28").Resize(, Range("E19").End(xlToRight).Column - 2).Value '<--Bang Origin tu dong 19 den 28'
    For I = 2 To UBound(tArr) Step 5
        .Item(sArr(I, 1)) = I
    Next I
        For I = 1 To UBound(sArr) Step 4
            Rws = .Item(sArr(I, 1))
            For J = 3 To UBound(tArr, 2)
                Col = .Item(tArr(1, J))
                dArr(I, Col) = tArr(Rws, J)
                dArr(I + 1, Col) = tArr(Rws + 1, J) + tArr(Rws + 2, J)
                dArr(I + 2, Col + 3) = tArr(Rws + 3, J)
            Next J
        Next I
    Range("D6").Resize(R, C) = dArr
End With
End Sub

Bate ơi cảm ơn bạn nhiều nhé. Mình đang bận chưa xem lại dc kỹ Code của bạn gửi. Nhưng nhìn qua thì khả năng đúng cái mình cần rồi

Cảm ơn Bate và mọi người rất nhiều.

Trân trọng!

 

[hadoan-pap]

Vì cả 2 bảng dữ liệu đều không liên tục (có dòng trống xen giữa) nên không thể xác định tổng quát mỗi bảng có bao nhiêu dòng.
Trên xuống bị dòng trống, dưới lên đụng bảng dưới.
Việc xác định vị trí các bảng dành cho bạn nhập thủ công vậy.
Chạy code này cho file của bạn.

Public Sub GPE()
Dim sArr(), dArr(), tArr(), I As Long, J As Long, C As Long, R As Long, Rws As Long, Col As Long
With CreateObject("Scripting.Dictionary")
    sArr = Range("D5", Range("D5").End(xlToRight)).Value
    For J = 1 To UBound(sArr, 2) Step 3
        .Item(sArr(1, J)) = J
    Next J
        sArr = Range("B6:B12").Value    '<------------Bang DES tu dong 6 den 12'
        C = J: R = UBound(sArr)
        ReDim dArr(1 To R, 1 To C)
        tArr = Range("C19:C28").Resize(, Range("E19").End(xlToRight).Column - 2).Value '<--Bang Origin tu dong 19 den 28'
    For I = 2 To UBound(tArr) Step 5
        .Item(sArr(I, 1)) = I
    Next I
        For I = 1 To UBound(sArr) Step 4
            Rws = .Item(sArr(I, 1))
            For J = 3 To UBound(tArr, 2)
                Col = .Item(tArr(1, J))
                dArr(I, Col) = tArr(Rws, J)
                dArr(I + 1, Col) = tArr(Rws + 1, J) + tArr(Rws + 2, J)
                dArr(I + 2, Col + 3) = tArr(Rws + 3, J)
            Next J
        Next I
    Range("D6").Resize(R, C) = dArr
End With
End Sub

Gửi Bate.

Bạn ơi hôm nay mình vừa xem lại code của bạn, that sự kiến thức về mảng của mình khá kém nên đọc khá khó hiểu. Mà để hiểu thì cần nhiều thời gian.

Bate có thể vui long giải thích giúp mình ý nghĩa của từng dòng lệnh thực thi được k? Mình có thể vừa nhìn vừa học và vận dung cho bài toán của mình.

Cảm ơn bạn và làm phiền bạn một lần nữa nhé.

Trân trọng!

Dim sArr(), dArr(), tArr(), I As Long, J As Long, C As Long, R As Long, Rws As Long, Col As Long
With CreateObject("Scripting.Dictionary")
sArr = Range("D5", Range("D5").End(xlToRight)).Value
For J = 1 To UBound(sArr, 2) Step 3
.Item(sArr(1, J)) = J
Next J
sArr = Range("B6:B12").Value '<------------Bang DES tu dong 6 den 12'
C = J: R = UBound(sArr)
ReDim dArr(1 To R, 1 To C)
tArr = Range("C19:C28").Resize(, Range("E19").End(xlToRight).Column - 2).Value '<--Bang Origin tu dong 19 den 28'
For I = 2 To UBound(tArr) Step 5
.Item(sArr(I, 1)) = I
Next I
For I = 1 To UBound(sArr) Step 4
Rws = .Item(sArr(I, 1))
For J = 3 To UBound(tArr, 2)
Col = .Item(tArr(1, J))
dArr(I, Col) = tArr(Rws, J)
dArr(I + 1, Col) = tArr(Rws + 1, J) + tArr(Rws + 2, J)
dArr(I + 2, Col + 3) = tArr(Rws + 3, J)
Next J
Next I
Range("D6").Resize(R, C) = dArr
End With

 

[hadoan-pap]

Mình xin phép gởi trả lời cho đỡ trôi bài.

xin lỗi mọi ng!