Xin hỏi cách sử dụng hàm Sumproduct trong VBA

[KVP]

Tôi có bảng tính muốn tính số lao động có mã "TN" có tháng =04/2009 trong bảng tính
sử dụng hàm Sumproduct mà không cho ra kết quả. Mong các anh chị chỉ dùm.

Sub sumpoduct1()
Dim endR As Long
Dim rng As Range, rngSum As Range
    endR = [a65000].End(xlUp).Row
    Set rng = Range("A1,A" & endR)
    Set rngSum = Range("B2,B" & endR)
    'Cells(endR + 1,2) = Evaluate([sumproduct((rng=cells(2,1))*1,(rngsum="TN")*1)])
    Cells(endR + 1,2) = CDbl([sumproduct((rng=cells(2,1))*1,(rngsum="TN")*1)])
End Sub

 

[sealand]

Sumproduct() là hàm của bảng tính nên bạn phải viết dẫn chiếu đến mới áp dụng được.Cụ thể:

Application.WorksheetFunctions.Sumproduct()

 

[KVP]

Em có đọc bài nào đó trên diễn đàn. Hàm này dùng đặc biệt hơn các hàm khác
Em cho thêm Application.WorksheetFunction cũng không được
Em tham khảo theo bài này mà làm không ra:
http://www.giaiphapexcel.com/forum/showthread.php?t=816

 

[ndu96081631]

Tôi có bảng tính muốn tính số lao động có mã "TN" có tháng =04/2009 trong bảng tính
sử dụng hàm Sumproduct mà không cho ra kết quả. Mong các anh chị chỉ dùm.

Sub sumpoduct1()
Dim endR As Long
Dim rng As Range, rngSum As Range
    endR = [a65000].End(xlUp).Row
    Set rng = Range("A1:A" & endR)
    Set rngSum = Range("A2:A" & endR)
    'Cells(endR + 1) = Evaluate([sumproduct((rng=cells(1,1))*1,(rngsum="TN")*1)])
    Cells(endR + 1) = CDbl([sumproduct((rng=cells(1,1))*1,(rngsum="TN")*1)])
End Sub

Bạn viết sai nhiều quá
Giờ tôi viết y chang với công thức tại E6 nhé:

Sub sumpoduct1()
  With Range([A2], [A65000].End(xlUp))
    [E6] = "=SUMPRODUCT((" & .Address & "=A2)*(" & .Offset(, 1).Address & "=""TN""))"
  End With
End Sub

Từ đây bạn muốn "phăng" thế nào thì tùy

 

[sealand]

To ndu: Mình thấy câu hỏi của bạn ấy có 2 cách giải quyết:

1-Gán giá trị đã được tính cho ô (Value): Bạn ấy đang làm theo hướng này.
2-Gán công thức cho ô (Formula):Cách giải quyết của bạn
Thương cho trót, bạn sử lý chút nữa nhé.

 

[ndu96081631]

To ndu: Mình thấy câu hỏi của bạn ấy có 2 cách giải quyết:

1-Gán giá trị đã được tính cho ô (Value): Bạn ấy đang làm theo hướng này.
2-Gán công thức cho ô (Formula):Cách giải quyết của bạn
Thương cho trót, bạn sử lý chút nữa nhé.

Ôi.. anh ơi... Có kết quả từ công thức rồi, muốn biến nó thành Value đơn giản mà:
Range("E6").Value = Range("E6").Value
Hoặc giả như người ta không biết vụ này, ít ra cũng biết đến chức năng Copy\PasteSpecial\Value chứ nhỉ?

 

[KVP]

Quả thật, em viết tham chiếu sai thật, nhưng sửa lại nếu gán vùng như cách của em thì vẫn không được. Có lẽ dùng name.Đúng như Anh sealand, bác làm giúp em cách thứ 2 với.
Em trich dẫn bài viết của anh NVSON

1. Nếu các tham số của của hàm Sumproduct mà không có điều kiện thì OK, bác cứ sử dụng bình thường.

Code:
gtri = WorksheetFunction.SumProduct(Range("B1:B11"), Range("C1:C11"))
2. Nếu các tham số của của hàm Sumproduct có điều kiện thì không thể sử dụng theo cách trên được (Mình cũng ko rõ là tại sao nữa!).
- Lúc đó phải sử dụng thêm hàm Evaluate:

Code:
[LEFT]Sub Tinhtoan()Dim gtrigtri = Evaluate("=SUMPRODUCT((A1:A11=""a"")*(B1:B11))")MsgBox gtriEnd Sub[/LEFT]

- hoặc:

Code:
gtri = CDbl([SumProduct((A1:A11="a")*1,B1:B11)])
nhưng nếu vùng mà bác đã đặt tên rồi thì sử dụng như sau:

Code:
gtri = CDbl([SumProduct((data1="a")*1,data2)])
Bác chú ý cái dấu [, ] nhé và data1 và data2 là Names nhưng lại không dùng Range("data1") như cách thông thường.

nếu muốn làm theo 2 cách cuối cùng thì viết thế nào?

 

[tedaynui]

Mình làm 1 ví dụ nhỏ về cách dùng SUMPRODUCT trong VBA
File ví dụ dùng để thống kê các số liệu ở Sheet2 và cho phép lựa chọn điều kiện thống kê (tối đa 5 điều kiện).

Các bạn tối ưu thêm
Download
TDN

 

[hoangtronglanh]

các bác cho em hỏi: Thay đổi dữ liệu ở trang in hóa đơn tiền điện trong VBA thì làm thế nào? Nhờ các bác chỉ dùm cho. Xin cảm ơn!

From: Sa_DQ
Bạn đền chổ ni: http://www.giaiphapexcel.com/forum/showthread.php?t=19999&page=2
& lần sau nhớ đừng chen ngang kiểu này nữa nha.

 

[haphuong1895]

Làm báo cáo với nhiều điều kiện

Em có một vấn đề đau đầu muốn nhờ các anh chị trên diễn đàn giúp đỡ.
Ngày nào em cũng phải làm một báo cáo mà làm được nó vô cùng tốn thời gian.

Báo cáo bao gồm: Phòng, ban, bộ phận, vị trí. Nhiệm vụ của em là update hàng ngày với mỗi vị trí thì số người là bao nhiêu trong từng ngày. Em đã dùng pivot table để lọc nhưng vẫn phải điền tay vào từng vị trí.

Các anh chị giúp em có cách nào dò tìm nhanh mà không dùng cách manual như vậy không?
Em gửi file lên nhờ anh chị giúp đỡ!

 

[pycckuu410]

Em có vấn đề tương tự chủ thớt, mà chưa biết xử lý như thế nào các bác giúp em với ạ. Em cám ơn!
Các bác vui lòng xem trong file nhé.

 

[HieuCD]

Em có vấn đề tương tự chủ thớt, mà chưa biết xử lý như thế nào các bác giúp em với ạ. Em cám ơn!
Các bác vui lòng xem trong file nhé.

bấm nút lệnh Run để chạy code

 

[pycckuu410]

bấm nút lệnh Run để chạy code

Cám ơn Bác HieuCD giúp đỡ nhiệt tình.
Bác ơi ví dụ của em chỉ có 2 mặt hàng và đơn giá tương ứng. Tuy nhiên thực tế em phát sinh nhiều mặt hàng và đơn giá tương ứng thì chỉnh sửa code có phức tạp không ạ?
Và còn cách viết code nào để gắn trực tiếp công thức vào ô F4 như này không ạ: =SUMPRODUCT(D4:E4;'Gia ban'!C44)
Em cám ơn các bác quan tâm!

 

[HieuCD]

Cám ơn Bác HieuCD giúp đỡ nhiệt tình.
Bác ơi ví dụ của em chỉ có 2 mặt hàng và đơn giá tương ứng. Tuy nhiên thực tế em phát sinh nhiều mặt hàng và đơn giá tương ứng thì chỉnh sửa code có phức tạp không ạ?
Và còn cách viết code nào để gắn trực tiếp công thức vào ô F4 như này không ạ: =SUMPRODUCT(D4:E4;'Gia ban'!C44)
Em cám ơn các bác quan tâm!

nếu công thức dạng đó thì viết code chi cho rối, dùng công thức của Excel cho gọn và thuận lợi cho việc thay đổi
F4 =IFERROR(SUMPRODUCT(D4:E4,OFFSET('Gia ban'!$C$3:$D$3,MATCH(B4,'Gia ban'!$A$4:$A$9,0),)),"")

bạn dùng code sau và chỉnh lại theo file thực tế, nếu không được thì hỏi tiếp

Sub GPE1()
Dim Tong As Long, i As Long, tmp(), Rng As Range
'xoa cot tên khach
Range(Sheets("Hang ngay").Range("B4"), Sheets("Hang ngay").Range("B6500").End(xlUp)).Offset(0, 1) = ""
'xoa cot thanh tien
Range(Sheets("Hang ngay").Range("B4"), Sheets("Hang ngay").Range("B6500").End(xlUp)).Offset(0, 4) = ""
'xet tmp la vung du lieu, tu cot B den cot F (5 cot)
tmp = Range(Sheets("Hang ngay").Range("B4"), Sheets("Hang ngay").Range("B6500").End(xlUp).Resize(, 5))
For i = 1 To UBound(tmp) - 1
'tim ma khach hang
    Set Rng = Range(Sheets("Gia ban").Range("A4"), Sheets("Gia ban").Range("A4").End(xlDown)).Find(tmp(i, 1), , , 1)
    If Not Rng Is Nothing Then
        tmp(i, 2) = Rng(, 2)    'lay ten khach hang
        tmp(i, 5) = tmp(i, 3) * Rng(, 3) + tmp(i, 4) * Rng(, 4) 'tinh thanh tien
        Tong = Tong + tmp(i, 5)
    End If
Next
tmp(UBound(tmp), 5) = Tong
Sheets("Hang ngay").Range("B4").Resize(UBound(tmp), 5) = tmp
End Sub

 

[pycckuu410]

nếu công thức dạng đó thì viết code chi cho rối, dùng công thức của Excel cho gọn và thuận lợi cho việc thay đổi
F4 =IFERROR(SUMPRODUCT(D4:E4,OFFSET('Gia ban'!$C$3:$D$3,MATCH(B4,'Gia ban'!$A$4:$A$9,0),)),"")

bạn dùng code sau và chỉnh lại theo file thực tế, nếu không được thì hỏi tiếp

*********
Cám ơn bác nhé! Công thức bác viết đúng theo ý hiểu của em, mà tại em không biết cách ứng dụng được Offset nên mới phải nhờ các bác viết code.
Vậy là thắc mắc của em đã được các bác giúp đỡ giải quyết hiệu quả và nhiệt tình.
Em chân thành cảm ơn các bác!

 

[hoahongden142917]

Bạn viết sai nhiều quá
Giờ tôi viết y chang với công thức tại E6 nhé:

Sub sumpoduct1()
  With Range([A2], [A65000].End(xlUp))
    [E6] = "=SUMPRODUCT((" & .Address & "=A2)*(" & .Offset(, 1).Address & "=""TN""))"
  End With
End Sub

Từ đây bạn muốn "phăng" thế nào thì tùy

xin cảm ơn bác ndu96081631 và em đã chế biến tí nên load lên vì vui mừng đã hiểu chút ít về nó

Sub sumpoduct1()
LASTROW = Cells(Rows.Count, "AL").End(xlUp).Row	AL tức là vba sẽ tính tới dữ liệu cuối cùng của cột AL
With Range("AL5:AL" & LASTROW)	Clà vùng quét dữ liệu từ AL5 đến dòng cuối của cùng của cột Al
[E6] = "=SUMPRODUCT((" & .Address & "=A2)*(" & .Offset(, -34).Address & "))"	KQ sẽ trả về ô E6 A2 tức là điều kiện ở ô A2 -34 tức là số tính tổng sẽ được lấy ở cột nằm bên trái của cột Al thứ tự 34 tức là cột D
Range("E6").Value = Range("E6").Value	Biến chuỗi thành số tại ô E6
End With
End Sub

Nhưng mà bác ơi, làm sao mà chuyển kết quả sumproduct đó không trả về ô E6 của trang tính, mà em muốn nó trả qua E6 của trang khác. giống như Sheet1.E6 minh không muốn mà muốn nó trả qua sheet2.E6 chẳng hạn.
Xin cảm ơn ạ

 

[hoahongden142917]

Bạn viết sai nhiều quá
Giờ tôi viết y chang với công thức tại E6 nhé:

Sub sumpoduct1()
  With Range([A2], [A65000].End(xlUp))
    [E6] = "=SUMPRODUCT((" & .Address & "=A2)*(" & .Offset(, 1).Address & "=""TN""))"
  End With
End Sub

Từ đây bạn muốn "phăng" thế nào thì tùy

Sub sumpoduct1()
With Range([AL5], [M65000].End(xlUp))
[E6] = "=SUMPRODUCT((" & .Address & "=A2)*(" & .Offset(, -34).Address & "))"
End With
End Sub

Chỉ em với anh ơi. coojg A có 20 dữ liệu từ AL2 đến AL21
1/ Ví dụ kết quả trả về ô Sheet1!E6, nhưng em muốn nó trả về Sheet2.E6 thì sửa làm sao.
2/ Vùng quét dữ liệu tìm điều kiện là B2:E65000 thì lập tức kết quả nó trả về là *(D5:E21)?, em muốn nó chỉ tính tổng ở 1 cột D thôi.
Em cảm ơn

 

[a0907092389]

Hi các anh
nhờ các anh viết giúp em VBA cho hàm sumproduct trong sheet 2 với ạ. các ô trong sheet 2 có hàm Sumproduct em cần đổi thành VBA hết để phát triễn trang tính số lượng lớn ấy. nếu tính ít thế này thì em viêt công thức được nhưng số lượng hàm sumproduct quá nhiều thì lại bị nặng máy. nên em đinh chuyển sang VBA thử xem có nhanh hơn không. nhờ các anh chỉ giáo giúp ạ
thanks all

 

[bebo021999]

Hi các anh
nhờ các anh viết giúp em VBA cho hàm sumproduct trong sheet 2 với ạ. các ô trong sheet 2 có hàm Sumproduct em cần đổi thành VBA hết để phát triễn trang tính số lượng lớn ấy. nếu tính ít thế này thì em viêt công thức được nhưng số lượng hàm sumproduct quá nhiều thì lại bị nặng máy. nên em đinh chuyển sang VBA thử xem có nhanh hơn không. nhờ các anh chỉ giáo giúp ạ
thanks all

Sheet1, dòng 2, là mã hàng duy nhất đúng không bạn? Vì thấy mã VIN01K036 bị trùng ở BW2, CA2, CB2.

 

[a0907092389]

Sheet1, dòng 2, là mã hàng duy nhất đúng không bạn? Vì thấy mã VIN01K036 bị trùng ở BW2, CA2, CB2.

vâng anh. các mã hàng sẽ là duy nhất anh ạ. em làm nhanh để lấy ví dụ á . nên có thể nó trùng