hàm subtotal (1 người xem)

[tn06065]

như tiêu đề và file đính kèm

Bình thường mình dung hàm subtotal đó là hàm trung Bình của 11 cây rác dày đó, nhưng giờ mình muốn những ô trống b3,8,11,14,16,17,18 đó mặc định rác dày 4cm và kết quả ở ô b21 đó sẽ có kết quả là trung Bình của cả ô b2:b20 ( bao gồm cả ô trống b3,8,11,14,16,17,18 nhưng mặc định là 4 mặc dù mình không hề nhập vào giá trị 4)
các bác cho lời giải giúp
thank's giaiphapexcel

 

[tn06065]

up up up ae giúp mình sớm
đang cần gấp quá

 

[eke_rula]

Bạn thử CT này xem:

=AVERAGE(IFERROR(1/(1/SUBTOTAL(9,OFFSET($B$1,ROW($1:$19),))),4))

Ctrl+Shift+Enter

 

[Gió Đông]

hoặc công thức này cho B21
=(SUM([c])+4*COUNTBLANK([c]))/ROWS([c])

 

[tn06065]

mình thấy kết quả thì ok, nhưng vùng quét số lieu từ hàng 1 đến hàng 19 (tại sao lại đến hàng 19 thôi mà không đến hàng 20 vậy bạn)

 

[tn06065]

công thức này hay, vì chỉ quét riêng cột đó thôi, nhưng công thức này khi mình lọc thì kết quả không đúng (giữ nguyên kết quả đó )
mình muốn khi lọc kết quả sẽ thay đổi theo lọc

 

[tn06065]

hoặc công thức này cho B21
=(SUM([c])+4*COUNTBLANK([c]))/ROWS([c])

công thức này hay, vì chỉ quét riêng cột đó thôi, nhưng công thức này khi mình lọc thì kết quả không đúng (giữ nguyên kết quả đó )
mình muốn khi lọc kết quả sẽ thay đổi theo lọc

 

[eke_rula]

mình thấy kết quả thì ok, nhưng vùng quét số lieu từ hàng 1 đến hàng 19 (tại sao lại đến hàng 19 thôi mà không đến hàng 20 vậy bạn)

Vì dữ liệu bạn chỉ có 19 dòng!!!

 

[Gió Đông]

công thức này hay, vì chỉ quét riêng cột đó thôi, nhưng công thức này khi mình lọc thì kết quả không đúng (giữ nguyên kết quả đó )
mình muốn khi lọc kết quả sẽ thay đổi theo lọc

Muốn lọc thì dùng công thức này
=(SUBTOTAL(109,[c])+4*(ROWS([c])-SUBTOTAL(102,[c])))/ROWS([c])

 

[tn06065]

Muốn lọc thì dùng công thức này
=(SUBTOTAL(109,[c])+4*(ROWS([c])-SUBTOTAL(102,[c])))/ROWS([c])

lỗi không nhập được công thức bạn à, bạn có thể nhập và upfile lên giúp mình

 

[Gió Đông]

lỗi không nhập được công thức bạn à, bạn có thể nhập và upfile lên giúp mình

Lỗi chỗ nào thì bạn quét chọn [c] và thay vào bằng cách dùng chuột chọn vùng cột C của bảng,

NẾU đặt đúng vào ô B21 thì sẽ không có lỗi

----------
thêm công thức rút gọn hơn sau

=4+(SUBTOTAL(109,[c])-4*SUBTOTAL(102,[c]))/ROWS([c])

nhớ copy dán đúng vào ô B21, vì nếu chỗ khác thì [c] không còn đúng khi đó phải là Table1[c]

 

[tn06065]

Muốn lọc thì dùng công thức này
=(SUBTOTAL(109,[c])+4*(ROWS([c])-SUBTOTAL(102,[c])))/ROWS([c])

mình đã lọc được với công thức này nhưng kết quả sai bạn à
xem lại giúp mình

 

[Gió Đông]

mình đã lọc được với công thức này nhưng kết quả sai bạn à
xem lại giúp mình

Sai là sai thế nào? không rõ sai ở đâu, bạn cần nói rõ

 

[tn06065]

Lỗi chỗ nào thì bạn quét chọn [c] và thay vào bằng cách dùng chuột chọn vùng cột C của bảng,

NẾU đặt đúng vào ô B21 thì sẽ không có lỗi

----------
thêm công thức rút gọn hơn sau

=4+(SUBTOTAL(109,[c])-4*SUBTOTAL(102,[c]))/ROWS([c])

nhớ copy dán đúng vào ô B21, vì nếu chỗ khác thì [c] không còn đúng khi đó phải là Table1[c]

khi lọc kết quả cho sai bạn à

 

[Gió Đông]

khi lọc kết quả cho sai bạn à

Đúng là khi lọc mới thấy sai, vì tính sai số ô,

Thử lại với công thức mới này cho B21
=4+(SUBTOTAL(109,[c])-4*SUBTOTAL(102,[c]))/SUBTOTAL(102,[stt])

 

[tn06065]

Sai là sai thế nào? không rõ sai ở đâu, bạn cần nói rõ

đây bạn à, file đính kèm như dưới bạn

 

[Gió Đông]

đây bạn à, file đính kèm như dưới bạn

Thử công thức bài #15 trên là đẹp rui

 

[tn06065]

Đúng là khi lọc mới thấy sai, vì tính sai số ô,

Thử lại với công thức mới này cho B21
=4+(SUBTOTAL(109,[c])-4*SUBTOTAL(102,[c]))/SUBTOTAL(102,[stt])

thank's bạn, công thức này đã cho kết quả đúng

 

[tn06065]

kết quả ở ô k124
+ nếu lọc kết quả STT 1,2,3 thì kết quả đúng = 4.3333
+nếu lọc kết quả STT 99T, 10T, 103T thì sai = #DIV/0!
các bác xem giúp em

 

[tn06065]

thank's bạn, công thức này đã cho kết quả đúng

mình có thêm file này bạn xem giúp mình nhé

kết quả ở ô k124
+ nếu lọc kết quả STT 1,2,3 thì kết quả đúng = 4.3333
+nếu lọc kết quả STT 99T, 10T, 103T thì sai = #DIV/0!
các bác xem giúp em