Lỗi hàm Indirect() khi dùng kết hợp Sumproduct()!

[vu_tuan_manh_linh]

Tôi sử dụng hàm indirect() thường xuyên, có thấy 1 lỗi mà không biết nguyên nhân, lỗi như sau: Dùng indirect() mà tham chiếu trong hàm có hàm Row(), khi sử dụng với hàm Sum() thì tính toán bình thường, nhưng khi sử dụng với hàm Sumproduct() thì báo lỗi nên thay vì dùng trực tiếp hàm Row() trong hàm Indirect(), tôi phải sử dụng cột phụ với hàm row() rồi tham chiếu kết quả đến hàm Indirect(). Mong diễn đàn tháo gỡ giúp! (Ví dụ đính kèm)

 

[ndu96081631]

Tôi sử dụng hàm indirect() thường xuyên, có thấy 1 lỗi mà không biết nguyên nhân, lỗi như sau: Dùng indirect() mà tham chiếu trong hàm có hàm Row(), khi sử dụng với hàm Sum() thì tính toán bình thường, nhưng khi sử dụng với hàm Sumproduct() thì báo lỗi nên thay vì dùng trực tiếp hàm Row() trong hàm Indirect(), tôi phải sử dụng cột phụ với hàm row() rồi tham chiếu kết quả đến hàm Indirect(). Mong diễn đàn tháo gỡ giúp! (Ví dụ đính kèm)

Có vài lần tôi đã đề cập đến hàm ROW() này rồi nhưng mọi người không để ý (lại cứ thích làm theo ý mình)
Nói chung ROW() là hàm mảng, không nên dùng nó ngoại trừ trường hợp bất khả kháng
Trong file của bạn, dòng đầu tiên có hàm ROW, hãy thay nó thành ROWS($1:2) nhé
---------------------
Xin nói thêm: Nếu tôi làm công thức cho file này thì tôi sẽ không làm phức tạp vậy. Công thức tại G2 chỉ đơn giản vầy thôi:

=2*SUBTOTAL(109,$G3:$G$1000)-SUM($G3:$G$1000)

Copy xuống cho các cell còn lại bên dưới

 

[VMH0307]

Với dữ liệu của bạn thì thử công thức sau tại G2:

=SUMPRODUCT(E3:$E$32,F3:$F$32)-SUMPRODUCT(ISNUMBER(A3:$A$32)*1,G3:$G$32)

Rồi copy cho các ô màu xanh khác xem.

 

[vu_tuan_manh_linh]

Công thức tại G2 chỉ đơn giản vầy thôi:

=2*SUBTOTAL(109,$G3:$G$1000)-SUM($G3:$G$1000)

SUBTOTAL() là một hàm khó dùng, nếu không hiểu kỹ rất dễ tính toán sai! Công thức của anh rất hay, nhưng em không dám dùng vì chưa hiểu rõ hàm này lắm.
Đọc tài liệu thì thấy chỉ số 109 nghĩa là "hoạt động như hàm sum(), Không tính toán các giá trị ẩn". Ở đây em hiểu nó không cộng các ô có chứa chính hàm Subtotal() không biết có đúng không anh?

 

[ndu96081631]

Ở đây em hiểu nó không cộng các ô có chứa chính hàm Subtotal() không biết có đúng không anh?

Chính xác là vậy nên ta mới lợi dụng để loại trừ!
Nếu không thích SUBTOTAL thì thiếu chi cách khác, chẳng hạn là:

=2*SUMIF($B3:$B$1000,"<>",$G3:$G$1000)-SUM($G3:$G$1000)

(công thức gõ tại G2 rồi copy paste xuống các cell bên dưới)
Ôi... cả rừng cách

 

[vu_tuan_manh_linh]

Ôi... cả rừng cách

Theo tư duy thông thường, em cứ lăm le tính toán cho vùng dữ liệu tham chiếu trực tiếp, nhưng hóa ra tính toán kiểu gián tiếp lại nhanh và gọn hơn nhiều! Cảm ơn anh nhiều!