kết hợp hàm subtotal và hàm sumproduct như thế nào? (1 người xem)

[khanhnth02]

giả sử tôi có hàm sumproduct($A$1:$A$20,B1:B20).
khi tôi dùng autofilter tôi muốn kết hợp thêm hàm subtotal và hàm sumproduct trên như thế nào để cho ra kết quả theo ý muốn,
cảm ơn mọi người tham gia

 

[ndu96081631]

giả sử tôi có hàm sumproduct($A$1:$A$20,B1:B20).
khi tôi dùng autofilter tôi muốn kết hợp thêm hàm subtotal và hàm sumproduct trên như thế nào để cho ra kết quả theo ý muốn,
cảm ơn mọi người tham gia

Không chắc có thể làm được hay không nhưng chắc chắn 100% rằng sẽ không làm được nếu không thấy file
Vậy đi nha!

 

[khanhnth02]

Không chắc có thể làm được hay không nhưng chắc chắn 100% rằng sẽ không làm được nếu không thấy file
Vậy đi nha!

làm sao để ô H1, thể hiện số tiền theo auto filter ( như hàm subtotal ) chứ không muốn thể hiện hết.

 

[Let'GâuGâu]

làm sao để ô H1, thể hiện số tiền theo auto filter ( như hàm subtotal ) chứ không muốn thể hiện hết.

chắc vậy hả

=SUMPRODUCT(SUBTOTAL(2,A5:A118)*(E5:E118)*(H5:H118))

 

[khanhnth02]

sao tôi ráp công thức vào mà ra kết quả cao quá. a xem lại giúp với

 

[Let'GâuGâu]

sao tôi ráp công thức vào mà ra kết quả cao quá. a xem lại giúp với

ùh hén, sorry....hehe tại nhắm nhắm làm đại chứ chưa thử vào file
bạn thự lại vậy xem

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A4,ROW(A5:A118)-4,))*(E5:E118)*(H5:H118))

 

[hieu1806]

Nguyên văn bởi khanhnth02
làm sao để ô H1, thể hiện số tiền theo auto filter ( như hàm subtotal ) chứ không muốn thể hiện hết.



chắc vậy hả
Code:
=SUMPRODUCT(SUBTOTAL(2,A5:A118)*(E5:E118)*(H5:H118))

bạn có thể giải thích giúp mình duoc757 không? xin cảm ơn !

 

[trang_bom]

ùh hén, sorry....hehe tại nhắm nhắm làm đại chứ chưa thử vào file
bạn thự lại vậy xem

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A4,ROW(A5:A118)-4,))*(E5:E118)*(H5:H118))

Cảm ơn anh nhé. em làm được rồi. Nhưng em không hiểu về công thức anh ạ. Anh giải thích thêm được không ạ? em cảm ơn anh

 

[Phan Thế Hiệp]

Cảm ơn anh nhé. em làm được rồi. Nhưng em không hiểu về công thức anh ạ. Anh giải thích thêm được không ạ? em cảm ơn anh

Cũng có thể thay bằng:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(E3,ROW($1:$100),))*(H4:H103))
Hoặc
=SUMPRODUCT(SUBTOTAL(109,OFFSET(E3,ROW($1:$100),))*(H4:H103))

Thường khi filter, chỉ có hàm Subtotal(n,...) mới tác động trên những ô hiện hữu (ô không bị 'Hide').
Kết hợp với hàm OFFSET(.'Ô làm mốc', ROW(1:100), ) sẽ cung cấp đầy đủ các ô trong Vùng, như công thức sẽ là: Vùng E4:E103. Khi đó Subtotal('đối số',.....) (với đối số 2: để đếm, hoặc đối số 9: để cộng), chỉ đếm hoặc lấy giá trị hiện hữu trên màn hình (khi filter), các ô bị che sẽ có giá trị 0.
Ví dụ: Sau khi filter, Vùng E4:E103 chỉ hiện các ô hàng: 5, 6, 36, 38, 39, lúc đó kết quả trong bộ nhớ sau khi thực hiện Subtotal() sẽ là 1 Mảng dọc,

• nếu là đối số 1: {0;0;0;0;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;1;1;0;0......}, lấy Mảng này nhân với (E4:E103)*(H4:H103).
• nếu là đối số 9: {0;0;0;0;50000;75000;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;100;0;100;100;0;0......}, nhân Mảng này với H4:H103

Sau cùng Sumproduct() làm nhiệm vụ cộng lại.
Thân.

 

[yummy411]

ùh hén, sorry....hehe tại nhắm nhắm làm đại chứ chưa thử vào file
bạn thự lại vậy xem

=SUMPRODUCT(SUBTOTAL(2,OFFSET(A4,ROW(A5:A118)-4,))*(E5:E118)*(H5:H118))

Cho mình hỏi chỗ ROW(A5:A118)-4 ==> -4 có ý nghĩ gì vậy bạn ?

 

[congdungyenbai]

Cho mình hỏi chỗ ROW(A5:A118)-4 ==> -4 có ý nghĩ gì vậy bạn ?

Hàm Row trả về số thứ tự của dòng, ROW(A5:A118)=5. Do hàm offset tham chiếu từ ô A4 (tức 4 dòng đầu ko được đếm vào công thức) nên cần trừ đi 4.