Tự động tìm mảng mới khi sang tháng mới

[tieuthubuongbinh]

Chào các bác,
Nhờ các bác giúp em tạo mảng động sao cho mỗi tháng khi cập nhật vào B1 thì CT tự tính average tương ứng :
M3: lấy 6 cột trước cột tháng ở B1
N3: lấy 3 cột trước cột tháng ở B1
Em đoán là dùng match mà loay hoay mãi chưa ra
Cám ơn các bác nhiều

 

[befaint]

m3=average(indirect(address(3,match(date(year($b$1),month($b$1),1),$a$2:$l$2,0)-6)&":"&address(3,match(date(year($b$1),month($b$1),1),$a$2:$l$2,0)-1)))

n3=average(indirect(address(3,match(date(year($b$1),month($b$1),1),$a$2:$l$2,0)-3)&":"&address(3,match(date(year($b$1),month($b$1),1),$a$2:$l$2,0)-1)))

 

[chucuoi92]

Chào các bác,
Nhờ các bác giúp em tạo mảng động sao cho mỗi tháng khi cập nhật vào B1 thì CT tự tính average tương ứng :
M3: lấy 6 cột trước cột tháng ở B1
N3: lấy 3 cột trước cột tháng ở B1
Em đoán là dùng match mà loay hoay mãi chưa ra
Cám ơn các bác nhiều

Test công thức này xem sao:

M3=AVERAGE(OFFSET(A3,,MATCH(B1,A2:L2,1)-2,,-6))
N3=AVERAGE(OFFSET(A3,,MATCH(B1,A2:L2,1)-2,,-3))

 

[Ba Tê]

Chào các bác,
Nhờ các bác giúp em tạo mảng động sao cho mỗi tháng khi cập nhật vào B1 thì CT tự tính average tương ứng :
M3: lấy 6 cột trước cột tháng ở B1
N3: lấy 3 cột trước cột tháng ở B1
Em đoán là dùng match mà loay hoay mãi chưa ra
Cám ơn các bác nhiều

12 cột tháng theo thứ tự từ 1 đến 12.
Tính 6 tháng trước tháng ô B1.

M4=AVERAGE(OFFSET(A3;;MONTH(B1)-7;;6))

Tính 3 tháng trước tháng ô B1.

N4=AVERAGE(OFFSET(A3;;MONTH(B1)-4;;3))

 

[tieuthubuongbinh]

m3=average(indirect(address(3,match(date(year($b$1),month($b$1),1),$a$2:$l$2,0)-6)&":"&address(3,match(date(year($b$1),month($b$1),1),$a$2:$l$2,0)-1)))

n3=average(indirect(address(3,match(date(year($b$1),month($b$1),1),$a$2:$l$2,0)-3)&":"&address(3,match(date(year($b$1),month($b$1),1),$a$2:$l$2,0)-1)))

CT của bạn chỉ đúng trong trường hợp vd của mình, nếu mình chèn cột thì lại không đúng, có cách nào khác không nhỉ

 

[befaint]

CT của bạn chỉ đúng trong trường hợp vd của mình, nếu mình chèn cột thì lại không đúng, có cách nào khác không nhỉ

Chỗ nào chưa đúng thì ta điều chỉnh lại.
Bạn gửi file thật bạn đang làm lên xem nào.

 

[tieuthubuongbinh]

nhờ CT chucuoi92 và bác Ba Tê mình làm được rồi, cám ơn bạn befait nhé


Cám ơn chucuoi92 va bác Ba Tê

 

[tieuthubuongbinh]

Em chèn them cột Total vào giữa 2 năm thì bị vướng, không áp dung CT được, nhờ các bác giúp lại lần nữa ạ.
Em xin thêm CT cho sum year to date (YTD) luôn ạ

 

[chucuoi92]

em chèn them cột total vào giữa 2 năm thì bị vướng, không áp dung ct được, nhờ các bác giúp lại lần nữa ạ.
Em xin thêm ct cho sum year to date (ytd) luôn ạ

Xem công thức này xem sao
M14=AVERAGEIFS($A$12:$Z$12,$A$11:$Z$11,">="&EDATE($B$10,-5),$A$11:$Z$11,"<="&$B$10)
N14=AVERAGEIFS($A$12:$Z$12,$A$11:$Z$11,">="&EDATE($B$10,-2),$A$11:$Z$11,"<="&$B$10)
O14=SUM(OFFSET($A$12,,MATCH(EDATE($B$10,-12),$A$11:$Z$11,0)-1,,-MONTH($B$10)))
P14=SUM(OFFSET($A$12,,MATCH($B$10,$A$11:$Z$11,0)-1,,-MONTH($B$10)))