Giúp viết code cho công thức Mảng (index, match) (1 người xem)

  • Thread starter Thread starter outway
  • Ngày gửi Ngày gửi
Liên hệ QC

Người dùng đang xem chủ đề này

Đang trực tuyến: 1 (Thành viên: 0, Khách: 1)
O

outway

Thành viên mới
Tham gia
17/10/10
Bài viết
8
Được thích
1
Tình hình là mình có file ex quản lý kiểm kê của 3x cửa hàng, data có thể lên đến 100000 dòng.
các câu lệnh mảng làm cho file QUÁ NẶNG, không chạy nổi. Nhờ các cao thủ viết code giùm phần tham chiếu SHORTAGE QTT,ÊXCESS QTT ở các sheet THIEU, THUA vào sheet SUM DATA.

HIỆN TẠI ĐANG DÙNG CÔNG THỨC MẢNG RỒI FILL: {=INDEX(THIEU!$A$4:$E$37,MATCH('SUM DATA'!B4&'SUM DATA'!F4,THIEU!$B$4:$B$37&THIEU!$E$4:$E$37,0),4)}

CẢM ƠN TRƯỚC.
 

File đính kèm

Sắp xếp theo thời gian sắp xếp theo bầu chọn
outway đã viết:
Tình hình là mình có file ex quản lý kiểm kê của 3x cửa hàng, data có thể lên đến 100000 dòng.
các câu lệnh mảng làm cho file QUÁ NẶNG, không chạy nổi. Nhờ các cao thủ viết code giùm phần tham chiếu SHORTAGE QTT,ÊXCESS QTT ở các sheet THIEU, THUA vào sheet SUM DATA.

HIỆN TẠI ĐANG DÙNG CÔNG THỨC MẢNG RỒI FILL: {=INDEX(THIEU!$A$4:$E$37,MATCH('SUM DATA'!B4&'SUM DATA'!F4,THIEU!$B$4:$B$37&THIEU!$E$4:$E$37,0),4)}

CẢM ƠN TRƯỚC.
Nhấp chuột vào đây để mở rộng...
Thử thế này xem sao
PHP: 
Sub test()
Dim data(), thieu(), thua(), i, j, k
data = Range([B4], [B65536].End(3)).Resize(, 5).Value
With Sheets("THIEU")
   thieu = .Range(.[B4], .[B65536].End(3)).Resize(, 5).Value
End With
With Sheets("THUA")
   thua = .Range(.[B4], .[B65536].End(3)).Resize(, 4).Value
End With
For i = 1 To UBound(data)
   For j = 1 To UBound(thieu)
      If data(i, 1) & data(i, 5) = thieu(j, 1) & thieu(j, 4) Then
         data(i, 3) = thieu(j, 3)
      End If
   Next
   For k = 1 To UBound(thua)
      If data(i, 1) & data(i, 5) = thua(k, 1) & thua(k, 4) Then
         data(i, 4) = thua(k, 3)
      End If
   Next
Next
[B4].Resize(i - 1, 5) = data
End Sub
 
Upvote 0
quanghai1969 đã viết:
Thử thế này xem sao
PHP: 
Sub test()
Dim data(), thieu(), thua(), i, j, k
data = Range([B4], [B65536].End(3)).Resize(, 5).Value
With Sheets("THIEU")
   thieu = .Range(.[B4], .[B65536].End(3)).Resize(, 5).Value
End With
With Sheets("THUA")
   thua = .Range(.[B4], .[B65536].End(3)).Resize(, 4).Value
End With
For i = 1 To UBound(data)
   For j = 1 To UBound(thieu)
      If data(i, 1) & data(i, 5) = thieu(j, 1) & thieu(j, 4) Then
.....................
End Sub
Nhấp chuột vào đây để mở rộng...

Anh Hải cho e hỏi : tại sao anh không dùng .Autofiltermode = flase, vì nếu dùng End(3) khi đang ở chế độ filter ---> e rằng kích thước vùng dữ liệu sẽ không đúng
 
Lần chỉnh sửa cuối:
Upvote 0
hungpecc1 đã viết:
Anh Hải cho e hỏi : tại sao anh không dùng .Autofiltermode = flase, vì nếu dùng End(3) khi đang ở chế độ filter ---> e rằng kích thước vùng dữ liệu sẽ không đúng
Nhấp chuột vào đây để mở rộng...
Ai biết đâu, nếu chủ topic để chế độ filter thì ráng chịu chứ.
Nếu muốn an toàn thì thêm cái sự kiện hủy chế độ filter khi thoát sheet
 
Upvote 0
Cảm ơn anh hải nhiều. đã thực hiện đúng yêu cầu
 
Upvote 0
dear a. Hải,

Code của anh chạy kết quả đúng, nhưng có lẽ thuật toán chưa tối ưu lắm.
em là newbie nên không hiểu ngôn ngữ này lắm.
Vấn đề ở chỗ, em có CSDL hơn 100.000 dòng. và em đã áp dụng cách của anh vào.
Nhưng thời gian chạy của nó hơn 20 phút. ?????\

So sánh với thời gian em lưu macro Hàm Index rồi Fill to End thì chỉ mất 15phút thôi.
Anh có cách nào tối ưu hơn không? Thanks anh trước
 
Upvote 0
Bạn thử dùng Code này xem sao
Mã: 
Sub SoSanh()
    Dim data(), thieu(), thua(), Res(), i As Long, k As Long
    data = Range([B4], [B65536].End(3)).Resize(, 5).Value
    With Sheets("THIEU")
        thieu = .Range(.[B4], .[B65536].End(3)).Resize(, 5).Value
    End With
    With Sheets("THUA")
        thua = .Range(.[B4], .[B65536].End(3)).Resize(, 4).Value
    End With


    ReDim Res(1 To UBound(thieu) + UBound(thua), 1 To 2)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(thieu)
            If Not .exists(thieu(i, 1) & thieu(i, 4)) Then
                k = k + 1
                .Add thieu(i, 1) & thieu(i, 4), k
                Res(k, 1) = thieu(i, 3)
            End If
        Next
        For i = 1 To UBound(thua)
            If Not .exists(thua(i, 1) & thua(i, 4)) Then
                k = k + 1
                .Add thua(i, 1) & thua(i, 4), k
                Res(k, 2) = thua(i, 3)
            Else
                Res(.Item(thua(i, 1) & thua(i, 4)), 2) = thua(i, 3)
            End If
        Next
        For i = 1 To UBound(data)
            If .exists(data(i, 1) & data(i, 5)) Then
                data(i, 3) = Res(.Item(data(i, 1) & data(i, 5)), 1)
                data(i, 4) = Res(.Item(data(i, 1) & data(i, 5)), 2)
            End If
        Next
    End With
    [B4].Resize(i - 1, 5) = data
End Sub
 
Lần chỉnh sửa cuối:
Upvote 0
dhn46 đã viết:
Bạn thử dùng Code này xem sao
Mã: 
Sub SoSanh()
    Dim data(), thieu(), thua(), Res(), i As Long, k As Long
    data = Range([B4], [B65536].End(3)).Resize(, 5).Value
    With Sheets("THIEU")
        thieu = .Range(.[B4], .[B65536].End(3)).Resize(, 5).Value
    End With
    With Sheets("THUA")
        thua = .Range(.[B4], .[B65536].End(3)).Resize(, 4).Value
    End With


    ReDim Res(1 To UBound(thieu) + UBound(thua), 1 To 2)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(thieu)
            If Not .exists(thieu(i, 1) & thieu(i, 4)) Then
                k = k + 1
                .Add thieu(i, 1) & thieu(i, 4), k
                Res(k, 1) = thieu(i, 3)
            End If
        Next
        For i = 1 To UBound(thua)
            If Not .exists(thua(i, 1) & thua(i, 4)) Then
                k = k + 1
                .Add thua(i, 1) & thua(i, 4), k
                Res(k, 2) = thua(i, 3)
            Else
                Res(.Item(thua(i, 1) & thua(i, 4)), 2) = thua(i, 3)
            End If
        Next
        For i = 1 To UBound(data)
            If .exists(data(i, 1) & data(i, 4)) Then
                data(i, 3) = Res(.Item(data(i, 1) & data(i, 4)), 1)
                data(i, 3) = Res(.Item(data(i, 1) & data(i, 4)), 2)
            End If
        Next
    End With
    [B4].Resize(i - 1, 5) = data
End Sub
Nhấp chuột vào đây để mở rộng...

Thật không may bạn ah, code của bạn không chạy ra kết quả.
Nhìn số bước mà nó lặp lại thấy ít hơn --> có lẽ sẽ nhanh hơn.
Nếu bạn lỡ giúp thì mình có thêm ít điều kiện ntnayf:
Câu hỏi cũng như trên:
Tham chiếu theo 2 điều kiện
nhưng điền thêm 1 cột nữa.
Trước đó chỉ có THIEU, THUA. bây giờ thêm cột TONG ( sheet TONG, BARCODE B4:B65000, CỬA HÀNG F4:F65000, THAM CHIẾU CỘT E4:E65000) vào sheet DATA
 
Upvote 0
outway đã viết:
dear a. Hải,

Code của anh chạy kết quả đúng, nhưng có lẽ thuật toán chưa tối ưu lắm.
em là newbie nên không hiểu ngôn ngữ này lắm.
Vấn đề ở chỗ, em có CSDL hơn 100.000 dòng. và em đã áp dụng cách của anh vào.
Nhưng thời gian chạy của nó hơn 20 phút. ?????\

So sánh với thời gian em lưu macro Hàm Index rồi Fill to End thì chỉ mất 15phút thôi.
Anh có cách nào tối ưu hơn không? Thanks anh trước
Nhấp chuột vào đây để mở rộng...
Hic nếu kiên nhẫn đợi được kết quả suốt 15 phút thì quả là đáng nể
Thử lại code này nha
PHP: 
Sub test2()
Dim data(), thieu(), thua(), i, k, dkthieu, dkthua, Res()
data = Range([B4], [B1000000].End(3)).Resize(, 5).Value
ReDim Res(1 To UBound(data), 1 To 4)
With Sheets("THIEU")
   thieu = .Range(.[B4], .[B1000000].End(3)).Resize(, 4).Value
End With
With Sheets("THUA")
   thua = .Range(.[B4], .[B1000000].End(3)).Resize(, 4).Value
End With
With CreateObject("scripting.dictionary")
   For i = 1 To UBound(data)
      If Not .exists(data(i, 1) & data(i, 5)) Then
         k = k + 1
         .Add data(i, 1) & data(i, 5), k
         Res(k, 1) = data(i, 1)
         Res(k, 2) = data(i, 2)
      End If
   Next
   For i = 1 To UBound(thieu)
      dkthieu = thieu(i, 1) & thieu(i, 4)
      If .exists(dkthieu) Then Res(.Item(dkthieu), 3) = thieu(i, 3)
   Next
   For i = 1 To UBound(thua)
      dkthua = thua(i, 1) & thua(i, 4)
      If .exists(dkthua) Then Res(.Item(dkthua), 4) = thua(i, 3)
   Next
End With
[B4].Resize(i - 1).NumberFormat = "@"
[B4].Resize(i - 1, 4) = Res
End Sub
 
Upvote 0
outway đã viết:
Thật không may bạn ah, code của bạn không chạy ra kết quả.
Nhìn số bước mà nó lặp lại thấy ít hơn --> có lẽ sẽ nhanh hơn.
Nếu bạn lỡ giúp thì mình có thêm ít điều kiện ntnayf:
Câu hỏi cũng như trên:
Tham chiếu theo 2 điều kiện
nhưng điền thêm 1 cột nữa.
Trước đó chỉ có THIEU, THUA. bây giờ thêm cột TONG ( sheet TONG, BARCODE B4:B65000, CỬA HÀNG F4:F65000, THAM CHIẾU CỘT E4:E65000) vào sheet DATA
Nhấp chuột vào đây để mở rộng...
Tôi đã Test Code với dữ liệu ít ỏi của bạn cho và cũng đã so sánh với code bài #2, đồng thời tôi cũng đã Fill cho 65535 dòng dữ liệu để chạy thử. => Bạn có thể up File chạy lỗi lên không?
Nếu kiểm tra xong sẽ xử lý tiếp yêu cầu của bạn!
 
Upvote 0
quanghai1969 đã viết:
Hic nếu kiên nhẫn đợi được kết quả suốt 15 phút thì quả là đáng nể
Thử lại code này nha
PHP: 
Sub test2()
Dim data(), thieu(), thua(), i, k, dkthieu, dkthua, Res()
data = Range([B4], [B1000000].End(3)).Resize(, 5).Value
ReDim Res(1 To UBound(data), 1 To 4)
With Sheets("THIEU")
   thieu = .Range(.[B4], .[B1000000].End(3)).Resize(, 4).Value
End With
With Sheets("THUA")
   thua = .Range(.[B4], .[B1000000].End(3)).Resize(, 4).Value
End With
With CreateObject("scripting.dictionary")
   For i = 1 To UBound(data)
      If Not .exists(data(i, 1) & data(i, 5)) Then
         k = k + 1
         .Add data(i, 1) & data(i, 5), k
         Res(k, 1) = data(i, 1)
         Res(k, 2) = data(i, 2)
      End If
   Next
   For i = 1 To UBound(thieu)
      dkthieu = thieu(i, 1) & thieu(i, 4)
      If .exists(dkthieu) Then Res(.Item(dkthieu), 3) = thieu(i, 3)
   Next
   For i = 1 To UBound(thua)
      dkthua = thua(i, 1) & thua(i, 4)
      If .exists(dkthua) Then Res(.Item(dkthua), 4) = thua(i, 3)
   Next
End With
[B4].Resize(i - 1).NumberFormat = "@"
[B4].Resize(i - 1, 4) = Res
End Sub
Nhấp chuột vào đây để mở rộng...

Chạy được kết quả đến dòng thứ 34 thì dừng lại???
Em thuộc típ người thông minh nhờ kiên nhẫn anh ah.
Dù sao cũng cảm ơn sự tận tâm của anh
 
Upvote 0
dhn46 đã viết:
Tôi đã Test Code với dữ liệu ít ỏi của bạn cho và cũng đã so sánh với code bài #2, đồng thời tôi cũng đã Fill cho 65535 dòng dữ liệu để chạy thử. => Bạn có thể up File chạy lỗi lên không?
Nếu kiểm tra xong sẽ xử lý tiếp yêu cầu của bạn!
Nhấp chuột vào đây để mở rộng...

Có lẽ bạn đang khá bực bội vì bạn đã test mà mình lại nói là không thực hiện được.
Dù sao cũng cảm ơn bạn rất nhiều. Đừng bực bội bạn nhé.
Tất cả cùng giúp nhau thôi mà
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
outway đã viết:
Chạy được kết quả đến dòng thứ 34 thì dừng lại???
Em thuộc típ người thông minh nhờ kiên nhẫn anh ah.
Dù sao cũng cảm ơn sự tận tâm của anh
Nhấp chuột vào đây để mở rộng...
Còn mình thì thuộc dạng người trán ngắn...
Bạn vui tính quá, dữ liệu chỉ có tới dòng 34 thì hết rồi lấy gì chạy nữa.
Mình thử với 15 000 dòng dữ liệu chạy trong nháy mắt
 

File đính kèm

Lần chỉnh sửa cuối:
Upvote 0
outway đã viết:
Có lẽ bạn đang khá bực bội vì bạn đã test mà mình lại nói là không thực hiện được.
Dù sao cũng cảm ơn bạn rất nhiều. Đừng bực bội bạn nhé.
Tất cả cùng giúp nhau thôi mà
Nhấp chuột vào đây để mở rộng...
Không bực gì đâu bạn, nhưng mình đang thắc mắc xem code sai chỗ nào để sửa thôi mà. Ngay cả code bài anh Hải tại #10 mình test không thấy lỗi tại sao bạn lại báo lỗi tại dòng số 34???
- Lập trình nó là thế: phải chuẩn (tôi đoán bạn cũng biết ít nhiều về Code vì bài #2 chỉ viết tới 65536 dòng và bạn đã tùy biến được cho 100000 dòng)
 
Upvote 0
dhn46 đã viết:
Không bực gì đâu bạn, nhưng mình đang thắc mắc xem code sai chỗ nào để sửa thôi mà. Ngay cả code bài anh Hải tại #10 mình test không thấy lỗi tại sao bạn lại báo lỗi tại dòng số 34???
- Lập trình nó là thế: phải chuẩn (tôi đoán bạn cũng biết ít nhiều về Code vì bài #2 chỉ viết tới 65536 dòng và bạn đã tùy biến được cho 100000 dòng)
Nhấp chuột vào đây để mở rộng...

Gửi bạn và anh Hải xem file mình Test. Mọi người có thể xóa record và chạy lại.
Mình đọc được 1 ít code thôi. Còn lập trình thì không biết gì.
Sẵn tiện, mình thấy hình như trong code test2 này của anh Hải quên tính đến việc lặp lại các điều kiện giống nhau của data.????, nếu không tìm ra điều kiện giống thì dữ liệu data = 0...

Dear a.Hải,

đã đọc file test của anh và thấy THUA, THIEU có nhiều dữ kiện tương đương Data. Vậy nếu ít hơn thì sao, code của anh có hỗ trợ được không?



Nói chung, cảm ơn mọi người đã giúp mình.
 

File đính kèm

Upvote 0
outway đã viết:
Gửi bạn và anh Hải xem file mình Test. Mọi người có thể xóa record và chạy lại.
Mình đọc được 1 ít code thôi. Còn lập trình thì không biết gì.
Sẵn tiện, mình thấy hình như trong code test2 này của anh Hải quên tính đến việc lặp lại các điều kiện giống nhau của data.????, nếu không tìm ra điều kiện giống thì dữ liệu data = 0...

Dear a.Hải,

đã đọc file test của anh và thấy THUA, THIEU có nhiều dữ kiện tương đương Data. Vậy nếu ít hơn thì sao, code của anh có hỗ trợ được không?



Nói chung, cảm ơn mọi người đã giúp mình.
Nhấp chuột vào đây để mở rộng...
Thôi mình giúp hết khả năng rồi.
Tạm biệt topic này nếu không sẽ ăn tết chẳng vui vẽ tí nào.
Hy vọng với cách hỏi bài của bạn sẽ sớm có đáp án nhé.
 
Upvote 0
cám ơn anh Hải rất nhiều. Vì ham học hỏi nên có lẽ khiến anh không vui.
Đưng để bụng anh Hải nhé, Chúc năm mới đầy thành công và sự bình an!!!
 
Upvote 0
Mình up File bạn tham khảo nhé.
 

File đính kèm

Upvote 0
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.

Bài viết mới nhất

Tuấn Bụng Bự
Mở Pass file excel phiên bản .xlsx , xlsm, xlsb (1 người xem)
Trả lời
3
Đọc
57
PhanTuHuong
PhanTuHuong
V
  • Question Question
xác định xu hướng
Trả lời
3
Đọc
91
vuthienfx
V
giaiphap
Lập trình C# tạo Add-ins cho excel. (1 người xem)
    • Thích
Trả lời
52
Đọc
7K
nguyendang95
N
ongke0711
  • Question Question
Tải hóa đơn điện tử (https://hoadondientu.gdt.gov.vn/) Excel Vba
    • Thích
    • Yêu thích
    • Cảm ơn
2 3 4
Trả lời
383
Đọc
45K
nguyendang95
N
OverAC
  • Dán lên cao
  • Suggestion Suggestion
Chúc mừng sinh nhật GiaiPhapExcel 19 tuổi và tặng ebook "Lập trình VBA phần cơ bản."
    • Thích
    • Yêu thích
    • Cảm ơn
Trả lời
7
Đọc
1K
PhanTuHuong
PhanTuHuong
Y
Lấy dữ liệu từ sheet chi tiết sang sheet tổng hợp hàng dọc (1 người xem)
Trả lời
6
Đọc
567
yoomi
Y
E
Đánh số cho các cặp số chứng từ và số chứng từ đối ứng
Trả lời
16
Đọc
391
Phan Thế Hiệp
Phan Thế Hiệp
N
Hàm tính chuỗi số trong 1 ô hoặc 1 dãy ô ra tổng kết quả.
Trả lời
2
Đọc
110
minhminh27
minhminh27
NguyenthiH
Mở file Excel có Pass Open
Trả lời
39
Đọc
3K
Hoàng Tuấn 868
H
M
Tách dữ liệu trong 1 file thành nhiều sheet theo số lượng dòng
    • Ngạc nhiên
Trả lời
25
Đọc
3K
Hoàng Tuấn 868
H
T
Tìm số hóa đơn trong chuỗi
Trả lời
11
Đọc
439
thao nguyen01
T
T
  • Question Question
Nhờ ACE cao thủ hỗ trợ mình có một chương trình EXCEL về phân công lớp cho giáo viên
Trả lời
6
Đọc
337
DeTong
D
T
Nhờ các anh chị sửa dùm công thức ạ.
Trả lời
10
Đọc
226
trantrungvinh1989
T
C
Chia sẻ: Kết quả xổ số Keno
    • Cười
    • Thích
Trả lời
24
Đọc
2K
phuongdaik98
P
Phan Thế Hiệp
Quy đổi từ Thu nhập sau thuế (TNCN) ra Thu nhập tính thuế
    • Yêu thích
    • Thích
Trả lời
1
Đọc
367
Phan Thế Hiệp
Phan Thế Hiệp
V
Quên mật khẩu file excel
Trả lời
25
Đọc
2K
YêuCN
Y
Nguyen Rem
  • Question Question
Lỗi "User-defined type not defined" khi khai báo biến
Trả lời
28
Đọc
2K
nguyendang95
N
V
Công thức để phân công chuyên môn cho giáo viên
Trả lời
5
Đọc
311
vietvanyen
V
G
AddIn convert ngày dương lịch sang âm lịch và ngược lại + đọc ngày tháng
Trả lời
20
Đọc
10K
du trung kien
D
8
Hàm trong excel (1 người xem)
Trả lời
3
Đọc
137
Hoàng Tuấn 868
H
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Thời gian đếm ngược.

000
Ngày
00
Giờ
00
phút
00
giây
Thân mời tham dự sự kiện sinh nhật GPE 2025
Back
Top Bottom