Giúp đỡ tính trung bình có điều kiện (1 người xem)

[kan]

Xìn chào các thành viên của diễn đàn !
Nhờ các thành viên chỉ giùm cách tính trung bình có điều kiện của một (nhiều) mảng.
Có 2 cột
Tháng 1
Lượt khách Hoa hồng
0 1200
0 1200
0 1200
0 1200
2 2000
3 3000
4 4000
5 5000
6 6000
5 5000
Tổng ?
Trung bình ?
Nhờ tính trung bình giá trị cột "Hoa hồng" với điều kiện giá trị cột "Lượt khách" khác không (<>0). vì có rất nhiều bảng và số lượt khách hàng tháng là khác nhau nên cần một công thức hoặc 1 đoạn code để thực thi việc này cho nhanh.
Xin cảm ơn !
Tôi không thấy chỗ inser file đính kèm

 

[hoangdanh282vn]

Xìn chào các thành viên của diễn đàn !
Nhờ các thành viên chỉ giùm cách tính trung bình có điều kiện của một (nhiều) mảng.
Có 2 cột
Tháng 1
Lượt khách Hoa hồng
0 1200
0 1200
0 1200
0 1200
2 2000
3 3000
4 4000
5 5000
6 6000
5 5000
Tổng ?
Trung bình ?
Nhờ tính trung bình giá trị cột "Hoa hồng" với điều kiện giá trị cột "Lượt khách" khác không (<>0). vì có rất nhiều bảng và số lượt khách hàng tháng là khác nhau nên cần một công thức hoặc 1 đoạn code để thực thi việc này cho nhanh.
Xin cảm ơn !
Tôi không thấy chỗ inser file đính kèm

giả sử vùng lượt khách là A1:A10, vùng hoa hồng là B1:B10
Tổng lượt khách :=SUM((A1:A10<>0)*A1:A10)
Tổng hoa hồng : :=SUM((A1:A10<>0)*B1:B10)
Trung bình hoa hồng/khách ::=SUM((A1:A10<>0)*B1:B10)/SUM((A1:A10<>0)*A1:A10)
sau ghi gõ xong công thức nhấn Ctrl+shift+enter vì là công thức mảng

 

[kan]

Vẫn chưa đúng ý

Cám ơn bạn hoangdanh82vn !Theo cách bạn chỉ thì vẫn chưa đúng ý của đề bài.Yêu cầu là tính trung bình cột "Hoa hồng" nhưng chỉ tính với các giá trị mà ở hàng của cột "Lượt khách" khác không (0) thôi !Xin diễn tả thế này: ở cột "Lượt khách": dòng thứ 1, 2, 3, 4 là bằng không nên cần tính trung bình cột" Hoa hồng" sẽ bắt đầu từ dòng thứ 5 đến hết cột "Hoa hồng", giá trị tính được sẽ là 4166,66667.Vì có rất nhiều bảng với định dạng giống nhau, chỉ khác là cột Khách hàng sẽ khác nhau nên cần một công thức (một đoạn code) để thực thi cho các bảng sau.Xin cám ơn các thành viên !

 

[PhanTuHuong]

Công thức tính kiểu này đơn giản thôi, chỉ cần sử dụng hàm excel là ổn thôi. Cho dù dùng VBA thì vẫn phải có công tác chọn vùng để tính, trừ trường hợp địa chỉ cột, dòng cố định.

 

[trung_dang]

Công thức này khá đơn giản thôi mà. Chỉ cần thêm 1 cột lọc các dữ liệu cần tính của cột Hoa hồng là OK. Giả sử cột Lượt khách là cột A, cột Hoa Hồng là cột B, hàm : if(a1<>0,b1,0). Chúc bạn thành công. Thân!

 

[cadafi]

Cám ơn bạn hoangdanh82vn !Theo cách bạn chỉ thì vẫn chưa đúng ý của đề bài.Yêu cầu là tính trung bình cột "Hoa hồng" nhưng chỉ tính với các giá trị mà ở hàng của cột "Lượt khách" khác không (0) thôi !Xin diễn tả thế này: ở cột "Lượt khách": dòng thứ 1, 2, 3, 4 là bằng không nên cần tính trung bình cột" Hoa hồng" sẽ bắt đầu từ dòng thứ 5 đến hết cột "Hoa hồng", giá trị tính được sẽ là 4166,66667.Vì có rất nhiều bảng với định dạng giống nhau, chỉ khác là cột Khách hàng sẽ khác nhau nên cần một công thức (một đoạn code) để thực thi cho các bảng sau.Xin cám ơn các thành viên !

Cái này dùng sumproduct một phát là xong ngay, chẳng cần cột phụ, chẳng cần viết code. Bạn xem file đính kèm.

 

[viendo]

Cám ơn bạn hoangdanh82vn !Theo cách bạn chỉ thì vẫn chưa đúng ý của đề bài.Yêu cầu là tính trung bình cột "Hoa hồng" nhưng chỉ tính với các giá trị mà ở hàng của cột "Lượt khách" khác không (0) thôi !Xin diễn tả thế này: ở cột "Lượt khách": dòng thứ 1, 2, 3, 4 là bằng không nên cần tính trung bình cột" Hoa hồng" sẽ bắt đầu từ dòng thứ 5 đến hết cột "Hoa hồng", giá trị tính được sẽ là 4166,66667.Vì có rất nhiều bảng với định dạng giống nhau, chỉ khác là cột Khách hàng sẽ khác nhau nên cần một công thức (một đoạn code) để thực thi cho các bảng sau.Xin cám ơn các thành viên !

Tôi thấy công thức mảng bạn Hoangdanh82vn đúng mà.

giả sử vùng lượt khách là A1:A10, vùng hoa hồng là B1:B10
Tổng hoa hồng : B11=SUM((A1:A10<>0)*B1:B10)

Và tính trung bình với công thức bình thường sau:

B12=B11/COUNTIF(A1:A10,"<>0")

Bạn xem đúng chưa nhé.

 

[kan]

Đã ra kết quả

Cám ơn các thành viên đã chỉ dùm cách làm !
Tuy nhiên, khối lượng bảng tính khá lớn khoảng gần 200 bảng cố định và có định dạng giống nhau, mỗi bảng có khoảng gần 30 dòng, việc lập xong công thức rồi copy vào các bảng có lẽ nhiều thời gian chỉ với 1 động tác copy công thức vừa lập vào các bảng ! vì vậy nhờ các thành viên viết cho đoạn code để thực thi việc tính toán (hoặc copy công thức) cho nhanh.

 

[piccolo]

Nên theo CT của Cadi_dafi

Chà chà, xét theo logic và ngắn gọn. Tôi thấy phương án của bác cadi_dafi là ổn nhất.