V/v Sumproduct với vùng chọn thay đổi theo số dòng.... (1 người xem)

[tamrua]

Dear các Anh Chị,
Tình hình là em tạo công thức sumproduct từ dòng đầu đến dòng cuối (VD: A2:A1523) xong rồi.
Hôm sau ở Tổ gửi tiếp dữ liệu, Em cập nhật vào Sheet.... thế là sumproduct không còn đúng nữa, phải chỉnh lại vùng chọn thành A2:A1812.....

Em nhớ có 1 bài trên diễn đàn của chúng ta có hướng dẫn để vùng chọn luôn là dòng cuối cùng, chứ nếu để vùng chọn A:A thì chạy cực kỳ chậm. Tuy nhiên, Em tìm hoài mà không thấy vì bài đó cũng lâu rồi.

Nay Em mạn phép hỏi lại nhờ các Anh chị chỉ lại giúp Em với nhé.

Em cám ơn nhiều nhiều lắm.....

 

[phulien1902]

Dear các Anh Chị,
Tình hình là em tạo công thức sumproduct từ dòng đầu đến dòng cuối (VD: A2:A1523) xong rồi.
Hôm sau ở Tổ gửi tiếp dữ liệu, Em cập nhật vào Sheet.... thế là sumproduct không còn đúng nữa, phải chỉnh lại vùng chọn thành A2:A1812.....

Em nhớ có 1 bài trên diễn đàn của chúng ta có hướng dẫn để vùng chọn luôn là dòng cuối cùng, chứ nếu để vùng chọn A:A thì chạy cực kỳ chậm. Tuy nhiên, Em tìm hoài mà không thấy vì bài đó cũng lâu rồi.

Nay Em mạn phép hỏi lại nhờ các Anh chị chỉ lại giúp Em với nhé.

Em cám ơn nhiều nhiều lắm.....

Để xác định dòng cuối dữ liệu bạn thử:

Sub LR_abc()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(3).Row
    ' Code cua ban
    MsgBox LR
End Sub

Hay:

Sub Select_abc()
    Dim i, LR
    With Sheets(1)
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = LR To 1 Step -1
            Range(Cells(1, 1), Cells(LR, 1)).Select
        Next i
        Debug.Print Range(Cells(1, 1), Cells(LR, 1)).Address
    End With
End Sub

 

[quocgiacan]

Dear các Anh Chị,
Tình hình là em tạo công thức sumproduct từ dòng đầu đến dòng cuối (VD: A2:A1523) xong rồi.
Hôm sau ở Tổ gửi tiếp dữ liệu, Em cập nhật vào Sheet.... thế là sumproduct không còn đúng nữa, phải chỉnh lại vùng chọn thành A2:A1812.....
Em nhớ có 1 bài trên diễn đàn của chúng ta có hướng dẫn để vùng chọn luôn là dòng cuối cùng, chứ nếu để vùng chọn A:A thì chạy cực kỳ chậm. Tuy nhiên, Em tìm hoài mà không thấy vì bài đó cũng lâu rồi.
Nay Em mạn phép hỏi lại nhờ các Anh chị chỉ lại giúp Em với nhé.
Em cám ơn nhiều nhiều lắm.....

Nếu là dùng công thức thì tạo 'Vùng động' cho công thức bằng hàm OFFSET().
Ví dụ: =SUMPRODUCT( OFFSET($A$2,,,COUNTA($A$2:$A$5000)) * 'Mảng điều kiện' ), nếu nhiều hơn 5000 dòng thì chỉnh tiếp 6000, 7000.

Chúc bạn ngày vui.

 

[tamrua]

Để xác định dòng cuối dữ liệu bạn thử:

Sub LR_abc()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(3).Row
    ' Code cua ban
    MsgBox LR
End Sub

Hay:

Sub Select_abc()
    Dim i, LR
    With Sheets(1)
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = LR To 1 Step -1
            Range(Cells(1, 1), Cells(LR, 1)).Select
        Next i
        Debug.Print Range(Cells(1, 1), Cells(LR, 1)).Address
    End With
End Sub

Với PHP mình làm:

Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(R2C6:R4224C6*(R2C2:R4224C2=RC[-8])*(R2C3:R4224C3=""New"")*(R2C9:R4224C9=1))"

Nhưng mình không biết thay LR cho 4224 như thế nào á....
Bạn chỉ thêm giúp mình với....
Cám ơn Bạn

 

[tamrua]

Nếu là dùng công thức thì tạo 'Vùng động' cho công thức bằng hàm OFFSET().
Ví dụ: =SUMPRODUCT( OFFSET($A$2,,,COUNTA($A$2:$A$5000)) * 'Mảng điều kiện' ), nếu nhiều hơn 5000 dòng thì chỉnh tiếp 6000, 7000.

Chúc bạn ngày vui.

Hiiiii
Cái hàm offset này hay thiệt, trước giờ chưa từng biết đến nên cũng còn khó hiểu quá à...... Cám ơn Quocgiacan đã hỗ trợ nhiều nhé..... Tám theo công thức này được ngay...
Giờ nghiên cứu thêm việc đưa vào PHP cho tiện.

 

[phulien1902]

Với PHP mình làm:

Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(R2C6:R4224C6*(R2C2:R4224C2=RC[-8])*(R2C3:R4224C3=""New"")*(R2C9:R4224C9=1))"

Nhưng mình không biết thay LR cho 4224 như thế nào á....
Bạn chỉ thêm giúp mình với....
Cám ơn Bạn

Bài toán của bạn phải làm như sau:
1. Xác định dòng cuối của bảng dữ liệu là cột nào đó, trong bảng dữ liệu của bạn bạn biết.
2. Có công thức cho J2 rồi
3. Bây giờ Filldown công thức xuống đến dòng cuối của bảng dữ liệu là xong.
Bạn có thể gửi File Demo lên đây để mọi người cùng xem giúp.

 

[tamrua]

Bài toán của bạn phải làm như sau:
1. Xác định dòng cuối của bảng dữ liệu là cột nào đó, trong bảng dữ liệu của bạn bạn biết.
2. Có công thức cho J2 rồi
3. Bây giờ Filldown công thức xuống đến dòng cuối của bảng dữ liệu là xong.
Bạn có thể gửi File Demo lên đây để mọi người cùng xem giúp.

Cám ơn Bạn đã phân tính đúng vấn đề của mình.
Dòng cuối của bảng dữ liệu là cột B. Cột cần công thức là cột J.
Nếu nhập công thức tại Ô J1: SUMPRODUCT($F$1:$F$4224*($B$1:$B$4224=B1)*($C$1:$C$4224="New")*($I$1:$I$4224=1))
Mình chuyển vào VBA với tham số LR được xác định theo cột B đã post phía trên.
Giờ tại các vùng dữ liệu, mình không biết thay giá trị 4224 = LR như thế nào.
Mình đã thử: SUMPRODUCT($F$1:$F$LR*($B$1:$B$LR=B1)*($C$1:$C$LR="New")*($I$1:$I$LR=1)) nhưng bị báo lỗi......
Bạn giúp mình cách điền tham số LR vào với.
Rất xin lỗi vì file mình không thể up lên diễn đàn được.
Cám ơn Bạn nhiều nhé

 

[khongcan]

Nếu bạn dùng bảng (format as table), thì mỗi một filed của talbe là một vùng động, tự mở rộng, chả cần phải code với hàm chi hết.