Đặt hàm bình quân có điều kiện (2 người xem)

[binhkf1]

Em có một bài toán trong Excel mong quý Anh Chị giúp đỡ ạ
Em muốn tính lương bình quân của 6 tháng gần nhất tại thời điểm cập nhật cột lương của tháng mới, mà cột thể hiện giá trị bình quân đó tự động nhảy giá trị khi cột lương tháng mới đó được cập nhật ạ.
1. Nếu tháng bất kỳ giá trị bằng 0 thì vẫn dc coi là giá trị để chia bình quân.
2. Giá trị có tính nối tiếp của năm trước, nên nhờ các Anh/Chị đặt hàm giúp em có cả sheet trước với ạ.
Em chân thành cảm ơn Anh/Chị nhiều ạ

 

[binhkf1]

Mong quý Anh/Chị để lại cho em vài ý kiến xem có thể đặt được công thức này không ạ

 

[minhminh27]

thắc mắc nhờ bạn giải thích.
1. Trường hợp trùng tên nhân thì có yếu tố nào để phân biệt?
2. Các cột có thay đổi giữa các sheet không, có liền mạch từ 1 -> 12 không (ví dụ cột tháng 1 luôn là cột B ở các sheet)
3. Dòng bắt đầu có thay đổi giữa các sheet không. giữ nguyên từ dòng thứ 5 của các sheet
4. Các ô chứa không có giá trị lương là công thức (hàm) hay là blank -null - trống nếu không có dữ liệu.
5. Dùng vba được không bạn
nếu trong 1 sheet bạn thử hàm này xem đúng yêu cầu không ô N5 thêm

=AVERAGE(OFFSET(B5,0,COUNT(B5:M5)-6,1,6))

 

[binhkf1]

thắc mắc nhờ bạn giải thích.
1. Trường hợp trùng tên nhân thì có yếu tố nào để phân biệt?
2. Các cột có thay đổi giữa các sheet không, có liền mạch từ 1 -> 12 không (ví dụ cột tháng 1 luôn là cột B ở các sheet)
3. Dòng bắt đầu có thay đổi giữa các sheet không. giữ nguyên từ dòng thứ 5 của các sheet
4. Các ô chứa không có giá trị lương là công thức (hàm) hay là blank -null - trống nếu không có dữ liệu.
5. Dùng vba được không bạn

Em xin trả lời Anh/Chị như sau ạ
1. Mỗi nhân tố đều đếu có mã số khác nhau ạ
2. Các cột không thay đổi giữa các sheet ạ, và liền lạch từ tháng 1 đến tháng 12 ạ
3.Các dòng và các cột giữa các sheet với nhau là giống nhau y chang ạ
4. Các ô không có giá trị là có công thức hàm
5.Mình chưa dùng VBA nên ko biết như thế nào
Mình đã đặt thử hàm =AVERAGE(OFFSET(B5,0,COUNT(B5:M5)-6,1,6)) cho Ô N5 nhưng bị lỗi ạ
Xin chân thành cảm ơn

 

[minhminh27]

nếu các ô dữ liệu có hàm và vẫn tính giá trị =0 thì công thức của mình sai rồi. Chờ các bác khác vậy.

Mình mới hỏi AI thì có hàm như này. Bạn tham khảo =))

Giả sử bạn có dữ liệu như sau:
Sheet1:

A	B	C	D	E	F	G	H	I	J	K	L	M
Mã NV	Tháng 1	Tháng 2	Tháng 3	Tháng 4	Tháng 5	Tháng 6	Tháng 7	Tháng 8	Tháng 9	Tháng 10	Tháng 11	Tháng 12
NV01	1000	1100	1200	1300	1400	1500	1600	1700	1800	1900	2000	2100

Sheet2:

A	B	C	D	E	F	G	H	I	J	K	L	M
Mã NV	Tháng 1	Tháng 2	Tháng 3	Tháng 4	Tháng 5	Tháng 6	Tháng 7	Tháng 8	Tháng 9	Tháng 10	Tháng 11	Tháng 12
NV01	2200	2300	2400	2500	2600	2700	2800	2900	3000	3100	3200	3300

Trên Sheet1, ô N2 sẽ chứa công thức:

=IF(COUNT(Sheet2!B2:M2)>=6,
    AVERAGE(INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2)-5):INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2))),
    AVERAGE(
        INDEX(Sheet2!B2:M2, 1):INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2)),
        INDEX(Sheet1!B2:M2, COUNT(Sheet1!B2:M2)-(6-COUNT(Sheet2!B2:M2))+1):INDEX(Sheet1!B2:M2, COUNT(Sheet1!B2:M2))
    )
)

Giải thích công thức:

• IF(COUNT(Sheet2!B2:M2)>=6, ...): Nếu Sheet2 có ít nhất 6 tháng dữ liệu, lấy 6 tháng cuối cùng từ Sheet2.
• AVERAGE(INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2)-5):INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2))): Tính giá trị bình quân của 6 tháng cuối cùng từ Sheet2.
• AVERAGE(INDEX(Sheet2!B2:M2, 1):INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2)), INDEX(Sheet1!B2:M2, COUNT(Sheet1!B2:M2)-(6-COUNT(Sheet2!B2:M2))+1):INDEX(Sheet1!B2:M2, COUNT(Sheet1!B2:M2))): Nếu Sheet2 có ít hơn 6 tháng dữ liệu, lấy tất cả dữ liệu từ Sheet2 và lấy số tháng còn lại từ Sheet1 để đủ 6 tháng.

 

[binhkf1]

nếu các ô dữ liệu có hàm và vẫn tính giá trị =0 thì công thức của mình sai rồi. Chờ các bác khác vậy.

Mình mới hỏi AI thì có hàm như này. Bạn tham khảo =))

Giả sử bạn có dữ liệu như sau:
Sheet1:

A	B	C	D	E	F	G	H	I	J	K	L	M
Mã NV	Tháng 1	Tháng 2	Tháng 3	Tháng 4	Tháng 5	Tháng 6	Tháng 7	Tháng 8	Tháng 9	Tháng 10	Tháng 11	Tháng 12
NV01	1000	1100	1200	1300	1400	1500	1600	1700	1800	1900	2000	2100

Sheet2:

A	B	C	D	E	F	G	H	I	J	K	L	M
Mã NV	Tháng 1	Tháng 2	Tháng 3	Tháng 4	Tháng 5	Tháng 6	Tháng 7	Tháng 8	Tháng 9	Tháng 10	Tháng 11	Tháng 12
NV01	2200	2300	2400	2500	2600	2700	2800	2900	3000	3100	3200	3300

Trên Sheet1, ô N2 sẽ chứa công thức:

=IF(COUNT(Sheet2!B2:M2)>=6,
    AVERAGE(INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2)-5):INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2))),
    AVERAGE(
        INDEX(Sheet2!B2:M2, 1):INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2)),
        INDEX(Sheet1!B2:M2, COUNT(Sheet1!B2:M2)-(6-COUNT(Sheet2!B2:M2))+1):INDEX(Sheet1!B2:M2, COUNT(Sheet1!B2:M2))
    )
)

Giải thích công thức:

• IF(COUNT(Sheet2!B2:M2)>=6, ...): Nếu Sheet2 có ít nhất 6 tháng dữ liệu, lấy 6 tháng cuối cùng từ Sheet2.
• AVERAGE(INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2)-5):INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2))): Tính giá trị bình quân của 6 tháng cuối cùng từ Sheet2.
• AVERAGE(INDEX(Sheet2!B2:M2, 1):INDEX(Sheet2!B2:M2, COUNT(Sheet2!B2:M2)), INDEX(Sheet1!B2:M2, COUNT(Sheet1!B2:M2)-(6-COUNT(Sheet2!B2:M2))+1):INDEX(Sheet1!B2:M2, COUNT(Sheet1!B2:M2))): Nếu Sheet2 có ít hơn 6 tháng dữ liệu, lấy tất cả dữ liệu từ Sheet2 và lấy số tháng còn lại từ Sheet1 để đủ 6 tháng.

Gửi tới Bác; minhminh27 ạ
Nếu ô bất kỳ chứa giá trị bằng 0 thì công thức của Bác cho thì đúng tuyệt vời ạ
Nhưng do đây là bảng lương nên được link nhiều bảng biểu tới, nên ô đó sẽ có công thức và ký tự thể hiện khi tháng đó không có lương là ký tự - này ạ
Dù sao thì em cũng chân thành cảm ơn Bác rất nhiều
Với Bác cho em hỏi Bác dùng phần mềm AI nào để hỏi thế ạ, Bác có thể chia sẻ cho em được không ạ.! Thank Bác

 

[minhminh27]

Gửi tới Bác; minhminh27 ạ
Nếu ô bất kỳ chứa giá trị bằng 0 thì công thức của Bác cho thì đúng tuyệt vời ạ
Nhưng do đây là bảng lương nên được link nhiều bảng biểu tới, nên ô đó sẽ có công thức và ký tự thể hiện khi tháng đó không có lương là ký tự - này ạ
Dù sao thì em cũng chân thành cảm ơn Bác rất nhiều
Với Bác cho em hỏi Bác dùng phần mềm AI nào để hỏi thế ạ, Bác có thể chia sẻ cho em được không ạ.! Thank Bác

Tùy lúc ngồi máy đâu thì mình xài AI đó(Gemini -Google, Copilot - Micosoft), chứ không nhất định xài 1 loại AI nào.
Câu vừa rồi là Copilot trả lời. Bạn có thể hỏi nó thêm xem sao.

 

[SA_DQ]

Mình cho rằng thiết kế trang tính như vầy thì tính toán khả dĩ hơn:

Mã	Lương	Ngày
NVA00	6,000,000	1/1/2024
LVB00	3,500,000	1/1/2024
TVC00	1,000,000	1/1/2024
NTD00	4,000,000	1/1/2024
NVA00	4,500,000	2/1/2024
LVB00	8,000,000	2/1/2024
TVC00	5,000,000	2/1/2024
NTD00	3,000,000	2/1/2024
NVA00	5,000,000	3/1/2024
LVB00	5,000,000	3/1/2024
TVC00	4,000,000	3/1/2024
NTD00	0	3/1/2024
NVA00	0	. . .
LVB00	6,000,000
TVC00	5,500,000
NTD00	0
NVA00	7,000,000
LVB00	2,000,000
TVC00	7,000,000
NTD00	7,000,000
NVA00	8,000,000
LVB00	1,000,000
TVC00	6,500,000
NTD00	6,000,000
LVB00	500,000
TVC00	6,300,000
NTD00	4,500,000
TVC00	6,000,000
NTD00	6,500,000
NTD00	7,000,000

​