Giúp sửa code hàm Sumif (1 người xem)

dongducnhiem · 7/5/12

Chào các bạn!
Tôi có viết đoạn code để thay thế cho hàm Sumif:

PHP:

Sub TaoTH()
  On Error Resume Next
  With Range([R40], [R5000].End(xlUp))
   .Parent.ShowAllTH
    .Offset(1, 0).Value = "=SUMIF(TH!$K$9:$K$40,TH!$K41,TH!$R$9:$R$40)+SUMIF(TH!$K$41:K41,TH!$K41,TH!$N$41:N41)-SUMIF(TH!$K$41:K41,TH!$K41,TH!$P$41:P41)"
    .Offset(1, 1).Value = "=SUMIF(TH!$K$9:$K$40,TH!$K41,TH!$S$9:$S$40)+SUMIF(TH!$K$41:K41,TH!$K41,TH!$O$41:O41)-SUMIF(TH!$K$41:K41,TH!$K41,TH!$Q$41:Q41)"
     With .Resize(, 2)
      .Value = .Value
     End With
    
  End With
End Sub

Tại Sheet TH, code bắt đầu chạy từ R41 và S41 trở xuống
Chạy code lần thứ 1 thì bình thường, khi chạy thêm vài lần nữa thì bị các lỗi sau:
1/ Báo lỗi "Circular reference ..."
2/ Cái List do tôi tạo từ Data, nó tự dịch chuyển xuống dưới thêm một số dòng
3/ Tạo rác như #Ref ở một số dòng

Tôi thiết nghĩ code sai từ chỗ này!?

PHP:

With Range([R40], [R5000].End(xlUp))

Nhờ các bạn hoàn chỉnh giúp! Cảm ơn.
----------
P/s: Tại Sheet TH_1, tôi có lập công thức Sumif, để các bạn kiểm tra và đối chiếu

ThuNghi · 7/5/12

dongducnhiem đã viết:
Chào các bạn!
Tôi có viết đoạn code để thay thế cho hàm Sumif:

PHP:

..

Nhờ các bạn hoàn chỉnh giúp! Cảm ơn.
----------
P/s: Tại Sheet TH_1, tôi có lập công thức Sumif, để các bạn kiểm tra và đối chiếu

Dùng thử code sau

PHP:

Option Explicit

Sub TaoTH()
  Dim endR&
  With Sheets("TH")
    On Error Resume Next
    .ShowAllData
    On Error GoTo 0
    endR = .Range("K41").End(xlDown).Row
    With .Range("R41:R" & endR)
      .Offset(, 0).Value = "=SUMIF(TH!$K$9:$K$40,TH!$K41,TH!$R$9:$R$40)+SUMIF(TH!$K$41:K41,TH!$K41,TH!$N$41:N41)-SUMIF(TH!$K$41:K41,TH!$K41,TH!$P$41:P41)"
      .Offset(, 1).Value = "=SUMIF(TH!$K$9:$K$40,TH!$K41,TH!$S$9:$S$40)+SUMIF(TH!$K$41:K41,TH!$K41,TH!$O$41:O41)-SUMIF(TH!$K$41:K41,TH!$K41,TH!$Q$41:Q41)"
       With .Resize(, 2)
        .Value = .Value
       End With
      
    End With
  End With
End Sub

Do nó xác định endRow sai nên bị lặp.
Còn nếu thích dùng Dic thì dùng thử code sau:

PHP:

Sub TaoTH02()
  Dim endR&, i&, nR&, s&
  Dim Arr, ArrKQ, ArrDK, ArrTon()
  Dim Dic As Object
  Set Dic = CreateObject("Scripting.Dictionary")
  With Sheets("TH")
    On Error Resume Next
      .ShowAllData
    On Error GoTo 0
    ArrDK = .Range("K9:S40").Value
    endR = .Range("K41").End(xlDown).Row
    Arr = .Range("K41:Q" & endR).Value
    ArrKQ = .Range("R41:S" & endR).Value
    s = 0
    For i = LBound(ArrDK) To UBound(ArrDK)
      If Not Dic.Exists(ArrDK(i, 1)) Then
        s = s + 1
        Dic.Add ArrDK(i, 1), s
        ReDim Preserve ArrTon(1 To 2, 1 To s)
        ArrTon(1, s) = ArrDK(i, 8) 'SL
        ArrTon(2, s) = ArrDK(i, 9) 'Gt
      End If
    Next i
    For i = LBound(Arr) To UBound(Arr)
      If Not Dic.Exists(Arr(i, 1)) Then
        s = s + 1
        Dic.Add Arr(i, 1), s
        ReDim Preserve ArrTon(1 To 2, 1 To s)
      End If
      nR = Dic.Item(Arr(i, 1))
      ArrTon(1, nR) = ArrTon(1, nR) + Arr(i, 4) - Arr(i, 6) 'SL
      ArrTon(2, nR) = ArrTon(2, nR) + Arr(i, 5) - Arr(i, 7) 'GT
      'gan lai arrKQ
      ArrKQ(i, 1) = ArrTon(1, nR) 'sl
      ArrKQ(i, 2) = ArrTon(2, nR) 'GT
    Next i
    .[R41].Resize(UBound(Arr), 2) = ArrKQ
  End With
Erase Arr, ArrKQ, ArrDK, ArrTon
Set Dic = Nothing
End Sub

Góp ý như sau
Phần tồn từ dòng 9-40, sao không xem là khoản nhập trong kỳ và ngày là 31/12/2011. Như vậy sẽ giảm 1 sumif.

dongducnhiem · 8/5/12

Các bạn cho tôi hỏi thêm
Trong một Sheet, tôi thường sử dụng Menu Data/List để tạo List, nhưng khi chạy code (vài lần)thì nó hay tạo thêm các dòng dưới và rác, các bạn hướng dẫn giùm cách khắc phục nhược điểm này
Trong File đính kèm, mục đích của code là:
Lấy cuối kỳ tháng trước làm đầu kỳ tháng sau, tạo đơn giá BQGQ, Thành tiền xuất và tính tồn cuối kỳ. Tính Sum cho từng nhóm hàng (ở đây phải dùng cột phụ và cthức Sumif )và tính tổng của bảng tính
(Tôi chỉ biết sơ về VAB, nhìn code các bạn đừng cười)
Mong các bạn quan tâm và chia sẻ. xin cảm ơn!
---------------------------------------------------
Gởi bạn Thunghi, bạn là dân kế toán nên nhìn File là biết
Thực tế, Sheet TH là tổng hợp toàn bộ nhập xuất cho một năm. sau đó tôi thiết lập Sheet nhập xuất tồn cho tháng 01 là T01 (và ở Sheet T01 và và sẽ lấy mã hàng hóa & mã T01 tương ứng của Sheet TH để tổng hợp nhập xuất từng mã hàng tương ứng), tương tự T12 cho tháng 12. Như vậy, nếu theo gợi ý của bạn

Phần tồn từ dòng 9-40, sao không xem là khoản nhập trong kỳ và ngày là 31/12/2011. Như vậy sẽ giảm 1 sumif.

Thì ta phải thiết lập thêm Sheet T00 hoặc T13 (cũng là ý hay)
Mặc khác, tôi dùng Sheet TH lập sổ " Chi tiết vật liệu, hàng hóa" cho từng mã hàng = lệnh Sort.
Thân!

ThuNghi · 8/5/12

dongducnhiem đã viết:
Các bạn cho tôi hỏi thêm
Trong một Sheet, tôi thường sử dụng Menu Data/List để tạo List, nhưng khi chạy code (vài lần)thì nó hay tạo thêm các dòng dưới và rác, các bạn hướng dẫn giùm cách khắc phục nhược điểm này
Trong File đính kèm, mục đích của code là:
Lấy cuối kỳ tháng trước làm đầu kỳ tháng sau, tạo đơn giá BQGQ, Thành tiền xuất và tính tồn cuối kỳ. Tính Sum cho từng nhóm hàng (ở đây phải dùng cột phụ và cthức Sumif )và tính tổng của bảng tính
(Tôi chỉ biết sơ về VAB, nhìn code các bạn đừng cười)
Mong các bạn quan tâm và chia sẻ. xin cảm ơn!

1/ Bạn dùng List để làm gì vậy?
2/ Xem như T01 là bạn đã hoàn thiện. Vậy sẽ lấy số đầu kỳ T02 = CK T01.
3/ Lấy xong DK rồi mới nhập dữ liệu nhập và SL xuất ?
4/ Nếu thêm mới những mặt hàng thì sao. T01 có mã B001 sao T02 kg thấy
5/ Nếu đã quy định mã V là 152, H là 156 và C là 153 thì cần gì cột phụ.
6/ Phần Total theo nhóm TK DK có cần thiết dùng Vlookup?
Bạn lạm dụng công thức quá.
Tôi cũng đã xem qua các file bạn up mà chưa hiểu nhiều lắm về quy trình để tạo ra các sh này.
Bạn tóm lược quy trình nhập chỉ 2 tháng xem. Còn viết code lấy CK Tn sang Tn+1 thì kg khó lắm.

dongducnhiem · 8/5/12

ThuNghi đã viết:
1/ Bạn dùng List để làm gì vậy?
4/ Nếu thêm mới những mặt hàng thì sao.

Dùng List khi chèn thêm hàng, thì sẽ có công thức sẵn và ta thêm mã mới Ở các Sheet T02, T03 ... (sau này sử dụng code thì không cần)
Mặt khác, tôi có Sub lọc, mỗi khi muốn lọc thì phải bỏ List ở Sheet muốn lọc ra

T01 có mã B001 sao T02 kg thấy

À, vì là bài giả định, nên để mã B001 xem có báo lỗi gì không (quên sửa, các bạn thông cảm)

6/ Phần Total theo nhóm TK DK có cần thiết dùng Vlookup?
5/ Nếu đã quy định mã V là 152, H là 156 và C là 153 thì cần gì cột phụ.
Bạn lạm dụng công thức quá.

Phần Total của từng nhóm hàng, nếu ở ngòai bảng tính thì dùng công thức quá dễ, nhưng trong Code để Total thì tôi phải dùng cột phụ thì mới Sumif được.
Thực tế, bài này tôi đã giải bằng công thức, nhưng File quá nặng, và để áp dụng vào công việc thực, nên tạm thời dùng chiêu tà đạo, sau này có thời gian sẽ nghiên cứu sâu về nó.

3/ Lấy xong DK rồi mới nhập dữ liệu nhập và SL xuất ?
Tôi cũng đã xem qua các file bạn up mà chưa hiểu nhiều lắm về quy trình để tạo ra các sh này.
Bạn tóm lược quy trình nhập chỉ 2 tháng xem.

Bạn xem File đính kèm, thì bạn rỏ cách lấy nhập xuất ở các cột G, H, I của các Sheet T01, T02...
Ở File đính kèm tôi có làm Name động T01_1,(ở bài trên tôi làm được), bài này lại bị báo lỗi Circular

PHP:

=INDIRECT("'"&"T"&RIGHT("0"&--RIGHT('T03'!$A$5;2)-1;2)&"'!$B$9:$M$200")

Tôi muốn 'T03' sẽ thay đổi tên khi đang đứng hiện hành (Ví dụ đang đứng Sheet T02 thì trở thành 'T02')
Nhờ Thunghi & các bạn hoàn chỉnh code ở các cột G, H, I của các Sheet T01... giùm
Xin cảm ơn các bạn bỏ thời gian & công sức!

ThuNghi · 8/5/12

Tôi đề xuất quy trình như sau:
1/ Bỏ bớt một số name không cần.
2/ Sh TH đã tổng hợp toàn bộ chi tiết tồn, nhập và SL xuất. vậy từ đây tính luôn giá xuất BQ luôn.
- Sẽ tính giá xuất theo tuần tự tháng 1, 2, ... 12.
- Tính xong sẽ update vào GT xuất.
- Tạo BC_NXT theo tháng.
Không cần thiết phải tạo 12 sh theo từng tháng.
3/ Đề xuất
- sh TH - Phần tồn đầu kỳ từ dòng 9- 40 sẽ thêm vào cột B là ngày 31/12/2011
- Cột R, và S sh TH nên chuyển sang thành nhập trong kỳ. Cột M, N.
Nếu bạn OK thì tôi hỗ trợ.

dongducnhiem · 8/5/12

ThuNghi đã viết:
Tôi đề xuất quy trình như sau:
1/ Bỏ bớt một số name không cần.
2/ Sh TH đã tổng hợp toàn bộ chi tiết tồn, nhập và SL xuất. vậy từ đây tính luôn giá xuất BQ luôn.
- Sẽ tính giá xuất theo tuần tự tháng 1, 2, ... 12.
- Tính xong sẽ update vào GT xuất.
- Tạo BC_NXT theo tháng.
Không cần thiết phải tạo 12 sh theo từng tháng.
3/ Đề xuất
- sh TH - Phần tồn đầu kỳ từ dòng 9- 40 sẽ thêm vào cột B là ngày 31/12/2011
- Cột R, và S sh TH nên chuyển sang thành nhập trong kỳ. Cột M, N.
Nếu bạn OK thì tôi hỗ trợ.

Cảm ơn chủ ý của bạn.
Thực tế File này có liên quan đến cấu trúc đến nhiều File khác như "sổ kế toán"... hoặc nhiều Sheet liên quan.
Mặc khác, tôi muốn từ bài của bạn để áp dụng một số bài khác.
Vậy mong bạn cố gắng hỗ trợ!
Thân!
-------------
P/s: Name động T01_1 đã chỉnh sửa được

PHP:

 =INDIRECT("'"&"T"&RIGHT("0"&--RIGHT(INDIRECT("$A$5");2)-1;2)&"'!$B$9:$M$200")

donxxiii · 29/5/12

http://www.giaiphapexcel.com/forum/showthread.php?64341-Chương-trình-quản-lý-nguyên-liệu-vật-liệu
tham khảo xử lý list data tính NXT như trong file trên

dongducnhiem · 29/5/12

donxxiii đã viết:
http://www.giaiphapexcel.com/forum/showthread.php?64341-Ch%C6%B0%C6%A1ng-tr%C3%ACnh-qu%E1%BA%A3n-l%C3%BD-nguy%C3%AAn-li%E1%BB%87u-v%E1%BA%ADt-li%E1%BB%87u
tham khảo xử lý list data tính NXT như trong file trên

Tôi đã tham khảo File của bạn, nhưng không thấy có cách nào giải quyết vấn đề "List" để áp dụng vào bài của tôi
Nhờ bạn hướng dẫn! Tks.

Giúp sửa code hàm Sumif (1 người xem)

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

dongducnhiem

Thành viên tiêu biểu

File đính kèm

ThuNghi

Hãy cho rồi sẽ nhận!

dongducnhiem

Thành viên tiêu biểu

File đính kèm

ThuNghi

Hãy cho rồi sẽ nhận!

dongducnhiem

Thành viên tiêu biểu

File đính kèm

ThuNghi

Hãy cho rồi sẽ nhận!

dongducnhiem

Thành viên tiêu biểu

donxxiii

Thành viên mới

dongducnhiem

Thành viên tiêu biểu

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

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

Đếm ngược thời gian