Tính trung bình của tháng đầu tiên. (2 người xem)

[SteveNguyen1991]

Em chào các bác, Em có bảng số liệu như trong file. Ở ô V2 là kết quả tính trung bình của các tháng đầu tiên có giá trị khác #N/A và giá trị bắt đầu 1 dòng phải = #N/A.
Bài này em thấy ở link sau: https://www.excelforum.com/excel-ge...-cell-with-values-in-an-irregular-column.html Link
Tìm cách giải không cần cột phụ mà không được. Mong các bác giúp!!

 

[Phan Thế Hiệp]

Em chào các bác, Em có bảng số liệu như trong file. Ở ô V2 là kết quả tính trung bình của các tháng đầu tiên có giá trị khác #N/A và giá trị bắt đầu 1 dòng phải = #N/A.
Bài này em thấy ở link sau: https://www.excelforum.com/excel-ge...-cell-with-values-in-an-irregular-column.html Link
Tìm cách giải không cần cột phụ mà không được. Mong các bác giúp!!

Thử: ô V2 dán

=SUM((ISNA($C$2:$C$63)*COUNTIF(OFFSET($C$1,ROW($1:$62),,,COLUMN($A:$M)),">0")=ROWS($1:1))*IFERROR(
$C$2:$O$63,))/COUNT(1/(NOT(ISNA($C$2:$O$63))*ISNA($C$2:$C$63)*COUNTIF(OFFSET($C$1,ROW($1:$62),,,COLUMN($A:$M)),">0")=ROWS($1:1)))

Fill xuống 3 dòng.

Thân

 

[Phan Thế Hiệp]

Tôi lấy ý giải pháp trong các cột phụ của bạn đã làm (vùng P2: S63) đưa thêm 1 công thức khác, với phiên bản từ 365 trở lên, để bạn tiện tham khảo:

=LET(Reg,BYROW(IFERROR(COLUMN(C:O)/($C$2:$O$63>0)/ISNA($C$2:$C$63),""),LAMBDA(arr,SMALL(arr,ROWS($1:1)))),SUM(INDEX($A$2:$O$63,SEQUENCE(62),IFERROR(Reg,1)))/COUNT(1/Reg))

Xem file đính kèm

Thân

 

[Phuocam]

Tính trung bình dùng hàm AVERAGE, cũng có thể dùng SUM/COUNT:

=LET(Reg,BYROW(IF(ISNUMBER($C$2:$O$63)*ISNA($C$2:$C$63),$C$2:$O$63,""),LAMBDA(arr,INDEX(FILTER(arr,arr<>""),,ROWS($1:1)))),AVERAGE(IFERROR(Reg,"")))

 

[SteveNguyen1991]

Các bác giải thích giúp em đoạn này với: COUNTIF(OFFSET($C$1,ROW($1:$62),,,COLUMN($A:$M)),">0")=ROWS($1:1))
Theo em hiều, bác đang tạo ra 1 mảng để so sánh xem tháng nào là tháng đầu tiên có giá trị > 0.
Có cái em không hiểu là: ở bước này



Nếu bấm tiếp Evaluate thì hàm OFFSET hiện lên #VALUE



Bấm tiếp Evaluate thì chuyển thành các giá trị


Em hiểu các số 0,1,2…10 là dựa vào điều kiện > 0.

 

[SteveNguyen1991]

Theo em tìm hiểu lỗi #Value là do không thể bỏ kết quả của 1 dải vào 1 ô. Ví dụ:
1 ) lần tính đầu tiên công thức sẽ có dạng : D10 = OFFSET($B$2,1,,,1)
2) Lần tính thứ 2 công thức sẽ có dạng : D10 = OFFSET($B$2,1,,,2) lúc này D10 ̀= B3:C3. Tuy nhiên 1 ô sẽ không chứa được vùng B3:C3 nên gây ra lỗi. Nếu đúng như vậy thì lần tính đầu tiên sẽ không gây ra lỗi.

Thêm 1 cái nữa. Có thể hàm OFFSET trả về kết quả lỗi nhưng khi kết hợp với các hàm Countif, Sumif thì giá trị của mỗi ô vẫn được giữ nguyên. Cho nên công thức: COUNTIF(OFFSET($C$1,ROW($1:$62),,,COLUMN($A:$M)),">0") sẽ trả về mảng 0,0,0,0,1,2,3,4,5,6,7,8,9 cho lần tính đầu tiên của công thức :
Mong bác nào có nhã hứng cùng em thảo luận. Cảm ơn các bác!!