Xin giúp đỡ về hàm offset kết hợp với hàm AVERAGE và hàm thay thế offset

[hmhungtn]

Các anh chị làm ơn cho hỏi

Để tính doanh thu gần nhất các tháng, mình sử dụng công thức này:

=AVERAGE(OFFSET($B$5;COUNTA(B:B)-E4;0;E4;1))

Chi tiết trong file đính kèm

Nhưng khi muốn thực hiện trong bảng tính chiều ngang, thì dùng công thức nào để thay thế offset được, bởi theo mình hiểu offset chỉ tính theo chiều dọc. Nếu sử dụng hàm if thì lệnh dài quá.



Cám ơn anh chị nhiều

 

[giaiphap]

Các anh chị làm ơn cho hỏi

Để tính doanh thu gần nhất các tháng, mình sử dụng công thức này:

=AVERAGE(OFFSET($B$5;COUNTA(B:B)-E4;0;E4;1))

Chi tiết trong file đính kèm

Nhưng khi muốn thực hiện trong bảng tính chiều ngang, thì dùng công thức nào để thay thế offset được, bởi theo mình hiểu offset chỉ tính theo chiều dọc. Nếu sử dụng hàm if thì lệnh dài quá.



Cám ơn anh chị nhiều

Hàm Offset có thể mở rộng cả chiều dọc lẫn chiều ngang, tham số thứ 3 của bạn chính là chiều ngang đó. bạn thay thử số 0 thành số khác thử xem.

 

[Phan Thế Hiệp]

Các anh chị làm ơn cho hỏi
Để tính doanh thu gần nhất các tháng, mình sử dụng công thức này:
=AVERAGE(OFFSET($B$5;COUNTA(B:B)-E4;0;E4;1))
Chi tiết trong file đính kèm
Nhưng khi muốn thực hiện trong bảng tính chiều ngang, thì dùng công thức nào để thay thế offset được, bởi theo mình hiểu offset chỉ tính theo chiều dọc. Nếu sử dụng hàm if thì lệnh dài quá.
Cám ơn anh chị nhiều

1/ Dùng COUNTA(B:B):

1. Không nên dùng cả cột tham chiếu B: B, nên hạn định số dòng như: B6: B17
2. Dùng COUNTA(B:B) sẽ không đúng khi tháng 12 có giá trị=0 (không phải là ô rỗng), hoặc giả các dòng trống ở trên/ hoặc dưới các số, Vd: B20=5 chẳng hạn, cũng làm ảnh hưởng đến kết quả không chính xác.

Thử dùng:

E5=AVERAGE(OFFSET($B$5,LOOKUP(2,1/$B$6:$B$17,$A$6:$A$17)-$E$4+1,,$E$4))

tương tự cho hàm MAX và MIN.​

​

2/ Muốn Offset() chuyển theo chiều ngang dùng:

F5=AVERAGE(OFFSET($D$20,,LOOKUP(2,1/$E$20:$P$20,$E$19:$P$19)-$E$4+1,,$E$4))

tương tự cho hàm MAX và MIN.

Thắc mắc thêm: lỡ như 1 tháng nằm trong 4 tháng gần nhất mà không có doanh số, ví dụ bạn đang cộng bình quân 4 tháng có doanh thu là 8,9,10,11, giả sử tháng 10 không có doanh thu thì có lấy tháng 7 tính không: tức chỉ tính 7+8+9+11!?

Thân

 

[hmhungtn]

Hàm Offset có thể mở rộng cả chiều dọc lẫn chiều ngang, tham số thứ 3 của bạn chính là chiều ngang đó. bạn thay thử số 0 thành số khác thử xem.

Cám ơn bác Giải Pháp
nhờ vào gợi ý của bác, mình đã chuyển công thức thành :=AVERAGE(OFFSET($D$20;0;COUNT($E$20:$P$20)-E4+1;1;E4)) và đã phát huy tác dụng đối với hàng ngang

Bài đã được tự động gộp: 27/12/18

1/ Dùng COUNTA(B:B):

1. Không nên dùng cả cột tham chiếu B: B, nên hạn định số dòng như: B6: B17
2. Dùng COUNTA(B:B) sẽ không đúng khi tháng 12 có giá trị=0 (không phải là ô rỗng), hoặc giả các dòng trống ở trên/ hoặc dưới các số, Vd: B20=5 chẳng hạn, cũng làm ảnh hưởng đến kết quả không chính xác.

Thử dùng:

E5=AVERAGE(OFFSET($B$5,LOOKUP(2,1/$B$6:$B$17,$A$6:$A$17)-$E$4+1,,$E$4))

tương tự cho hàm MAX và MIN.​

​

2/ Muốn Offset() chuyển theo chiều ngang dùng:

F5=AVERAGE(OFFSET($D$20,,LOOKUP(2,1/$E$20:$P$20,$E$19:$P$19)-$E$4+1,,$E$4))

tương tự cho hàm MAX và MIN.

Thắc mắc thêm: lỡ như 1 tháng nằm trong 4 tháng gần nhất mà không có doanh số, ví dụ bạn đang cộng bình quân 4 tháng có doanh thu là 8,9,10,11, giả sử tháng 10 không có doanh thu thì có lấy tháng 7 tính không: tức chỉ tính 7+8+9+11!?

Thân

Dear Bác Phan Thế Hiệp!
1. Đúng là không nên tham chiếu (B:B), cám ơn bác đã lưu ý, và cũng thay CountA = count vì nếu doanh số =0 thì kết quả không chính xác thật
2. Về vụ có 2 tháng không có doanh số, thì đúng là cũng cần phải xem xét lại, hiện tại đang tính như vậy bác ạ.
3. Cái hàm bác gợi ý Thử dùng:

E5=AVERAGE(OFFSET($B$5,LOOKUP(2,1/$B$6:$B$17,$A$6:$A$17)-$E$4+1,,$E$4)), Bác có thể chỉ giáo kỹ hơn không, đã thử nhưng chưa thành công, Bác có thể cho hẳn vào bảng đã gửi lúc trước không? Dù mình đã tìm được cách sử dụng cho hàng ngang nhưng vẫn muốn tham khảo thêm của bác, nhỡ đâu cần dùng sau này.

Cám ơn bác nhiều

 

[Phan Thế Hiệp]

Cám ơn bác Giải Pháp
nhờ vào gợi ý của bác, mình đã chuyển công thức thành :=AVERAGE(OFFSET($D$20;0;COUNT($E$20:$P$20)-E4+1;1;E4)) và đã phát huy tác dụng đối với hàng ngang

Bài đã được tự động gộp: 27/12/18

Dear Bác Phan Thế Hiệp!
1. Đúng là không nên tham chiếu (B:B), cám ơn bác đã lưu ý, và cũng thay CountA = count vì nếu doanh số =0 thì kết quả không chính xác thật
2. Về vụ có 2 tháng không có doanh số, thì đúng là cũng cần phải xem xét lại, hiện tại đang tính như vậy bác ạ.
3. Cái hàm bác gợi ý Thử dùng:

E5=AVERAGE(OFFSET($B$5,LOOKUP(2,1/$B$6:$B$17,$A$6:$A$17)-$E$4+1,,$E$4)), Bác có thể chỉ giáo kỹ hơn không, đã thử nhưng chưa thành công, Bác có thể cho hẳn vào bảng đã gửi lúc trước không? Dù mình đã tìm được cách sử dụng cho hàng ngang nhưng vẫn muốn tham khảo thêm của bác, nhỡ đâu cần dùng sau này.

Cám ơn bác nhiều

Trong file tôi đã làm rồi mà! Cột E và cột F đó.

Thân

 

[hmhungtn]

Trong file tôi đã làm rồi mà! Cột E và cột F đó.

Thân

Sao không thấy file đính kèm của Bác nhỉ

 

[Phan Thế Hiệp]

Sao không thấy file đính kèm của Bác nhỉ

Xem file kèm.

Thân

 

[hmhungtn]

Xem file kèm.

Thân

Dear Bác Phan Thế Hiệp!
Bác có thể giải thích hộ ý nghĩa của hàm này được không =AVERAGE(OFFSET($B$5;LOOKUP(2;1/$B$6:$B$17;$A$6:$A$17)-$E$4+1;;$E$4)), và sao cần phải sử dụng hai dấu chấm phẩy liền nhau ";;".
Cũng mới tìm hiểu về excel một thời gian nên chưa biết được nhiều.
Ngoài ra hỏi thêm bác về hàm lookup
LOOKUP(2;1/(D8:O8<>"");$D$7:$O$7)
Nếu để như trên thì cho kết quả đúng khi từ (D8:O8 <>"" ) có điều nếu D8:O8 mà có chứa "0" hay "-" thì kết quả cũng không đúng; nhưng nếu muốn thay là >"0" hay >="1" LOOKUP(2;1/(D8:O8>="1");$D$7:$O$7) thì kết quả lại không chính xác, bác xem hộ xem sai ở chỗ nào nhé.

Cám ơn bác

 

[Phan Thế Hiệp]

Dear Bác Phan Thế Hiệp!
Bác có thể giải thích hộ ý nghĩa của hàm này được không =AVERAGE(OFFSET($B$5;LOOKUP(2;1/$B$6:$B$17;$A$6:$A$17)-$E$4+1;;$E$4)), và sao cần phải sử dụng hai dấu chấm phẩy liền nhau ";;".
Cũng mới tìm hiểu về excel một thời gian nên chưa biết được nhiều.
Ngoài ra hỏi thêm bác về hàm lookup
LOOKUP(2;1/(D8:O8<>"");$D$7:$O$7)
Nếu để như trên thì cho kết quả đúng khi từ (D8:O8 <>"" ) có điều nếu D8:O8 mà có chứa "0" hay "-" thì kết quả cũng không đúng; nhưng nếu muốn thay là >"0" hay >="1" LOOKUP(2;1/(D8:O8>="1");$D$7:$O$7) thì kết quả lại không chính xác, bác xem hộ xem sai ở chỗ nào nhé.

Cám ơn bác

Xem file kèm có giải thích trong file.

Thân

 

[hmhungtn]

Xem file kèm có giải thích trong file.

Thân

Dear Bác Phan Thế Hiệp!
Cám ơn vì sự nhiệt tình của Bác, bảng của bác gửi đã giải thích rất khi tiết khiến cho người mới mọ mẫm vào lĩnh vực này hiểu thêm được nhiều vấn đề.
Sau khi xem lại vẫn thấy có chút lấn cấn là bởi vì hàm của bác đưa ra vẫn trả số liệu cho những tháng có xuất hàng = "0" hoặc "-", sáng nay ngồi nghiên cứu thêm lúc nữa thì đã sửa lại cái hàm cũ như sau:
Tháng xuất hàng gần nhất =IFERROR(LOOKUP(1;0/($D8:$O8>0);$D$7:$O$7);"")
và hàm này đã thỏa mãn được yêu cầu là chỉ những tháng có xuất hàng, tức >="0" thì mới trả kết quả.

Chúc Bác một năm mới vui khỏe, thuận lợi trong công việc và cuộc sống

 

[Phan Thế Hiệp]

Tháng xuất hàng gần nhất =IFERROR(LOOKUP(1;0/($D8:$O8>0);$D$7:$O$7);"")

Bạn xem lại công thức:
LOOKUP(1;0/($D8:$O8>0);$D$7:$O$7)
Chắc bạn nhầm chỗ màu đỏ (0/....)
-------------------------------------------------
À! Bạn không nhầm, trong trường hợp này thì hàm vẫn lấy đúng, có điều hơi lạ.
Với công thức trên thì chỉ cần:
=IFERROR(LOOKUP(1;0/$D8:$O8;$D$7:$O$7);"")
Không cần phải so thêm D8:O8>0


Thân