Help công thức Sumproduct và Indirect (1 người xem)

[anhtraibuon]

Các bác xem giúp công thức này bị sai chỗ nào với

Đây là công thức ra kết quả đúng
SUMPRODUCT(SUBTOTAL(3,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"1"),ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"9"&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&COUNTA($F:$F)+7))-1,))*(O9:O7507="K"))

Khi tớ thay cụm (O9:O7507="K") bằng cụm Indirect thì công thức báo Ref!
SUMPRODUCT(SUBTOTAL(3,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"1"),ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"9"&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&COUNTA($F:$F)+7))-1,))*INDIRECT("("&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"9"&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&COUNTA($F:$F)+7&"="&"""K"""&")"))
Hoặc có cách nào dùng indirect cho cái cụm đó thì nhờ các bác chỉ giúp
Cám ơn các bác nhiều

 

[Lê Hồng Minh83]

Các bác xem giúp công thức này bị sai chỗ nào với

Đây là công thức ra kết quả đúng
SUMPRODUCT(SUBTOTAL(3,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"1"),ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"9"&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&COUNTA($F:$F)+7))-1,))*(O9:O7507="K"))

Khi tớ thay cụm (O9:O7507="K") bằng cụm Indirect thì công thức báo Ref!
SUMPRODUCT(SUBTOTAL(3,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"1"),ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"9"&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&COUNTA($F:$F)+7))-1,))*INDIRECT("("&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&"9"&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")&COUNTA($F:$F)+7&"="&"""K"""&")"))
Hoặc có cách nào dùng indirect cho cái cụm đó thì nhờ các bác chỉ giúp
Cám ơn các bác nhiều

Thay COLUMN() bằng 1 số và bạn xem kết quả nhé vì COLUMN cho kết quả mảng, không phải giá trị. Nhìn thì giống vậy thôi. Còn bạn muốn thay đổi công thức khác thì đưa file lên

 

[anhtraibuon]

Thay COLUMN() bằng 1 số và bạn xem kết quả nhé vì COLUMN cho kết quả mảng, không phải giá trị. Nhìn thì giống vậy thôi. Còn bạn muốn thay đổi công thức khác thì đưa file lên

Bạn xem File nhé
Công thức ban đầu là ô P4. Sau đó mình muốn công thức tự tính toán khi mình thêm các đòng nên mình đã sửa thành công thức như ô O4 thì công thức vẫn đúng, mình tiếp tục đổi sang công thức như ô O2 thì bị REF!

 

[Ba Tê]

Bạn xem File nhé
Công thức ban đầu là ô P4. Sau đó mình muốn công thức tự tính toán khi mình thêm các đòng nên mình đã sửa thành công thức như ô O4 thì công thức vẫn đúng, mình tiếp tục đổi sang công thức như ô O2 thì bị REF!

Ý bạn muốn làm gì với cái mớ bòng bong ấy? Có phải bắt buộc phải SUMPRODUCT & 1 đống hàm mới có kết quả ?
Ví dụ công thức ô N4 kết quả là 7, tôi dùng =COUNTA($N$9:$N$10000) vẫn ra kết quả là 7.

 

[Phan Thế Hiệp]

Bạn xem File nhé
Công thức ban đầu là ô P4. Sau đó mình muốn công thức tự tính toán khi mình thêm các đòng nên mình đã sửa thành công thức như ô O4 thì công thức vẫn đúng, mình tiếp tục đổi sang công thức như ô O2 thì bị REF!

Muốn Filter theo nhiều điều kiện!??

Thử:

O4=SUM(IFERROR(SUBTOTAL(3,OFFSET(P$8,ROW(INDIRECT("1:"&COUNTA($P$9:$P$100))),))*(P9:P100="Active")*(O9:O100="K"),))

Kết thúc bằng Ctrl+Shift+Enter.

Thân

 

[Ba Tê]

Muốn Filter theo nhiều điều kiện!??

Đọc cái mớ bòng bong đó mà cũng đoán được ý người ta thì "bái phục".

 

[Phan Thế Hiệp]

Đọc cái mớ bòng bong đó mà cũng đoán được ý người ta thì "bái phục".

Chơi chút cho vui thôi anh!
Thật ra, không cần INDIRECT() thì công thức vẫn chạy tốt, tại thấy chủ thớt "nhấn mạnh" vào INDIRECT() nên chìu theo.

Chúc anh ngày vui.


@chủ thớt: Vầy cũng ngon mà:

=SUM(IFERROR(SUBTOTAL(3,OFFSET(P$8,ROW(1:1000),))*(P9:P1000="Active")*(O9:O1000="K"),))

Ctrl+Shift+Enter.

Thân

 

[anhtraibuon]

Cám ơn các bác đã giúp đỡ, tuy nhiên vì sao mình phải cài công thức như vậy thì mình cần giải thích thêm:
- Vì dữ liệu của mình khá nhiều, trên 100 ngàn dòng và mỗi tháng dữ liệu sẽ được bổ sung thêm nên mình cài công thức trên để công thức tự lấy số dòng chặn dưới của bảng dữ liệu mà mình không cần sửa lại vùng dữ liệu trong công thức.
- Vì mình muốn công thức tự tính toán lại mỗi khi được filter theo từng cột
- Vì phía sau con nhiều cột cần phải tính toán tương tự nên mình muốn dùng hàm indirect để khi kéo công thức sang ngang công thức vẫn tính được cho cột nên mình dùng thêm hàm substitute để lấy tên cột

Như trong file thì ở ô P4 mình đã cài công thức SUMPRODUCT(SUBTOTAL(3,OFFSET(P$1,ROW(P915)-1,))*(P915="Active"))
Công thức này đã đáp ứng được yêu cầu, tuy nhiên khi vùng dữ liệu thay đổi thì mình phài sửa lại vùng dữ liệu trong công thức ( không phải là P915 mà có thể là P9100000) và khi kéo công thức sang ngang thi không còn là cột P nữa mà là cột Q,R,S .....

 

[anhtraibuon]

Thay COLUMN() bằng 1 số và bạn xem kết quả nhé vì COLUMN cho kết quả mảng, không phải giá trị. Nhìn thì giống vậy thôi. Còn bạn muốn thay đổi công thức khác thì đưa file lên

mình đã xem lại công thức, đúng là có vấn đề ở phần column(), có cách nào chuyển nó về thành giá trị không ban?

 

[anhtraibuon]

Cám ơn các bác đã tư vấn, mình đã thay công thức column() bằng columns thì vấn đề đã được giải quyết

 

[Lê Hồng Minh83]

Cám ơn các bác đã tư vấn, mình đã thay công thức column() bằng columns thì vấn đề đã được giải quyết

uh, columns thì cho giá trị như mình đã nói ở trên, nên đáp ứng được yêu cầu của bạn.
Còn nếu dữ liệu của bạn mà 100000 dòng thì công thức Cltr+shift+enter là thua rồi, không chạy nổi đâu
Bạn cứ sử dụng công thức của bạn chắc vẫn chạy được

 

[anhtraibuon]

uh, columns thì cho giá trị như mình đã nói ở trên, nên đáp ứng được yêu cầu của bạn.
Còn nếu dữ liệu của bạn mà 100000 dòng thì công thức Cltr+shift+enter là thua rồi, không chạy nổi đâu
Bạn cứ sử dụng công thức của bạn chắc vẫn chạy được

ok thanks bac

 

[dazkangel]

Muốn nhẹ thì thử kiểu sau, thêm 1 cột phụ là nhẹ nhàng biết bao: