Cần giúp: Xét đạt thành tích 3 năm liên tiếp (1 người xem)

[xuongrongdat]

Mình nhờ mọi người giúp mình xét những nhân viên đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp.
Mình đính kèm file. Có thể giúp mình bằng 2 cách: conditional formatting hoặc cột phụ luôn được không!
Dữ liệu mình chép từ 2010 đến 2019. Nghĩa là ai đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp thì tại cái năm thứ 3 liên tiếp đó mình "cảnh báo" để xét danh hiệu cấp cao hơn tại năm đó.

Xin cảm ơn.

(mình đã cập nhật file đính kèm lại để dữ liệu đúng và chuẩn)

 

[Phuocam]

Mình nhờ mọi người giúp mình xét những nhân viên đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp.
Mình đính kèm file. Có thể giúp mình bằng 2 cách: conditional formatting hoặc cột phụ luôn được không!
Dữ liệu mình chép từ 2010 đến 2019. Nghĩa là ai đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp thì tại cái năm thứ 3 liên tiếp đó mình "cảnh báo" để xét danh hiệu cấp cao hơn tại năm đó.
Xin cảm ơn.

Trường hợp 3,4,5,6,7,8,9,10 năm liên tiếp luôn thì tô năm cuối hay tô hết?

 

[xuongrongdat]

Trường hợp 3,4,5,6,7,8,9,10 năm liên tiếp luôn thì tô năm cuối hay tô hết?

Dạ tô hết luôn bác.

Thực tế thì khi được xét 3 năm rồi thì người đó sẽ quay lại năm đầu tiên. Nhưng ở đây con muốn tô luôn, theo điều kiện cứ được 3 năm liên tiếp là cảnh báo thôi.

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

Ối, mọi người đợi xíu để mình Replace bỏ hết chữ Ông/Bà trong phần Họ tên cái đã. (đã update lại file ở #1)

 

[Phuocam]

Cột G =IF(E2="","",0+RIGHT(TEXT(E2,"yyyy"),4)), định dạng số.

Thử công thức này trong H2:

=SUMPRODUCT(COUNTIFS($B$2:B2,B2,$G$2:G2,G2-{0,1,2}))

 

[bomberman211]

Mình nhờ mọi người giúp mình xét những nhân viên đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp.
Mình đính kèm file. Có thể giúp mình bằng 2 cách: conditional formatting hoặc cột phụ luôn được không!
Dữ liệu mình chép từ 2010 đến 2019. Nghĩa là ai đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp thì tại cái năm thứ 3 liên tiếp đó mình "cảnh báo" để xét danh hiệu cấp cao hơn tại năm đó.
View attachment 249151
Xin cảm ơn.

Bạn xem file thử coi đúng chưa nhé

 

[saobekhonglac]

Cột G =IF(E2="","",0+RIGHT(TEXT(E2,"yyyy"),4)), định dạng số.

Thử công thức này trong H2:

=SUMPRODUCT(COUNTIFS($B$2:B2,B2,$G$2:G2,G2-{0,1,2}))

Chỗ -{0,1,2} có ý nghĩa sau vậy Anh.

 

[xuongrongdat]

Bạn xem file thử coi đúng chưa nhé

Mình xem công thức của bạn thì thấy sai trong trường hợp không liên tiếp á. Trường hợp này nè:
Ông A đạt những năm 2012, 2013, 2015, 2016 => dù là 4 lần nhưng không được 3 năm liên tiếp á.

Cảm ơn bạn.

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

Chỗ -{0,1,2} có ý nghĩa sau vậy Anh.

Mình mới nghĩ ra nè, là để xét 3 năm liên tiếp trước liền kề.
Đang kiểm tra kết quả, híc.

 

[bomberman211]

Mình xem công thức của bạn thì thấy sai trong trường hợp không liên tiếp á. Trường hợp này nè:
Ông A đạt những năm 2012, 2013, 2015, 2016 => dù là 4 lần nhưng không được 3 năm liên tiếp á.

Cảm ơn bạn.

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

Mình mới nghĩ ra nè, là để xét 3 năm liên tiếp trước liền kề.
Đang kiểm tra kết quả, híc.

Trường hợp ông Đoàn Văn Ảnh cùng năm 2017 được khen 2 lần thì sao bạn?

 

[xuongrongdat]

Trường hợp ông Đoàn Văn Ảnh cùng năm 2017 được khen 2 lần thì sao bạn?

Mình chép bị duplicate dữ liệu rồi.
Chờ mình xíu mình "làm" dữ liệu lại cho đúng. Xin lỗi.

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

@bomberman211 : mình đính kèm lại file rồi. Mỗi nhân viên mỗi năm chỉ đạt danh hiệu này 1 lần hà.

 

[bomberman211]

Mình chép bị duplicate dữ liệu rồi.
Chờ mình xíu mình "làm" dữ liệu lại cho đúng. Xin lỗi.

Không sao, nếu có trường hợp 1 người 1 năm được khen nhiều lần thì lồng thêm 1 hàm IF vào công thức của bác @phuocam (#4) là được.
H2=SUM(IF(COUNTIFS($B$2:B2,B2,$G$2:G2,G2-{0,1,2})>0,1,0))

 

[xuongrongdat]

@phuocam, @bomberman211 : trường hợp này sumproduct với sum đều được nè, kết quả y chang luôn á, hihi.


Xét điều kiện mảng với mảng hay quá. Mình mới thấy và mới biết kiểu này. Không biết mốt gặp trường hợp cần tới có nhớ mà ứng dụng vào không, híc. Ngồi Evaluate Formula một hồi mới hiểu.

Cảm ơn mọi người nhiều.

 

[bebo021999]

Tính năm sao không dùng year() cho gọn

G2

=IF(E2="","",YEAR(E2+0))

Đếm năm liên tiếp trong vòng 3 năm đổ lại:

H2

=COUNTIFS($B$1:$B$122,B2,$G$1:$G$122,">="&G2-2,$G$1:$G$122,"<="&G2)

 

[phuongvq123]

Mình nhờ mọi người giúp mình xét những nhân viên đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp.
Mình đính kèm file. Có thể giúp mình bằng 2 cách: conditional formatting hoặc cột phụ luôn được không!
Dữ liệu mình chép từ 2010 đến 2019. Nghĩa là ai đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp thì tại cái năm thứ 3 liên tiếp đó mình "cảnh báo" để xét danh hiệu cấp cao hơn tại năm đó.
View attachment 249151
Xin cảm ơn.

Hình như thiếu 1 năm

 

[xuongrongdat]

Trường hợp ông Đoàn Văn Ảnh cùng năm 2017 được khen 2 lần thì sao bạn?

Hình như thiếu 1 năm

Cảm ơn các bạn, nhờ 2 bạn, cả anh @bebo021999 nữa, mình mới phát hiện ra là cột năm của mình dựa vào năm của ngày ra Quyết định là sai, híc. Vì ví dụ công nhận danh hiệu cho năm 2018 thì ngày ra quyết định có thể là ngày 31/12/2018 mà cũng có thể là 1 ngày nào đó ở tháng 01/2019, 10/01/2019 chẳng hạn. Chết dở thiệt chớ.

Mình lại sửa cái cột đó trước, phải cắt cái đuôi của cột tên danh hiệu chi tiết mới được:

 

[phuongvq123]

Cảm ơn các bạn, nhờ 2 bạn, cả anh @bebo021999 nữa, mình mới phát hiện ra là cột năm của mình dựa vào năm của ngày ra Quyết định là sai, híc. Vì ví dụ công nhận danh hiệu cho năm 2018 thì ngày ra quyết định có thể là ngày 31/12/2018 mà cũng có thể là 1 ngày nào đó ở tháng 01/2019, 10/01/2019 chẳng hạn. Chết dở thiệt chớ.

Mình lại sửa cái cột đó trước, phải cắt cái đuôi của cột tên danh hiệu chi tiết mới được:
View attachment 249187

) File ban đầu đang ông/bà, sau đó lại không ông bà, giá có mã nhân viên thì hay.

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

) File ban đầu đang ông/bà, sau đó lại không ông bà, giá có mã nhân viên thì hay.

Có công thức này hơi cùi tý, bạn tham khảo

 

[huuthang_bd]

@phuocam, @bomberman211 : trường hợp này sumproduct với sum đều được nè, kết quả y chang luôn á, hihi.
View attachment 249164

Xét điều kiện mảng với mảng hay quá. Mình mới thấy và mới biết kiểu này. Không biết mốt gặp trường hợp cần tới có nhớ mà ứng dụng vào không, híc. Ngồi Evaluate Formula một hồi mới hiểu.

Cảm ơn mọi người nhiều.

Xét đủ 3 năm liên tiếp thì đúng còn gọi là đếm số năm liên tiếp thì không đúng. Ví dụ 2010 và 2012 thì không thể là 2 năm liên tiếp được.

 

[phuongvq123]

Mình nhờ mọi người giúp mình xét những nhân viên đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp.
Mình đính kèm file. Có thể giúp mình bằng 2 cách: conditional formatting hoặc cột phụ luôn được không!
Dữ liệu mình chép từ 2010 đến 2019. Nghĩa là ai đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp thì tại cái năm thứ 3 liên tiếp đó mình "cảnh báo" để xét danh hiệu cấp cao hơn tại năm đó.
View attachment 249151
Xin cảm ơn.

Bạn sửa lại năm và xem công thức file này xem thế nào ) File trên kia, tớ làm chưa hoàn chỉnh nên file này sửa lại lỗi đó

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

Xét đủ 3 năm liên tiếp thì đúng còn gọi là đếm số năm liên tiếp thì không đúng. Ví dụ 2010 và 2012 thì không thể là 2 năm liên tiếp được.

Nếu năm liên tiếp thì công thức mảng kia đúng giải quyết vấn đề đúng không ạ

 

[phuongvq123]

Mình nhờ mọi người giúp mình xét những nhân viên đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp.
Mình đính kèm file. Có thể giúp mình bằng 2 cách: conditional formatting hoặc cột phụ luôn được không!
Dữ liệu mình chép từ 2010 đến 2019. Nghĩa là ai đạt thành tích "Chiến sĩ thi đua" trong 3 năm liên tiếp thì tại cái năm thứ 3 liên tiếp đó mình "cảnh báo" để xét danh hiệu cấp cao hơn tại năm đó.
View attachment 249151
Xin cảm ơn.

Dùng lookup sẽ chính xác

 

[xuongrongdat]

Xét đủ 3 năm liên tiếp thì đúng còn gọi là đếm số năm liên tiếp thì không đúng. Ví dụ 2010 và 2012 thì không thể là 2 năm liên tiếp được.

Tại em làm lỗi cột Năm đó anh. Chứ năm nào cũng có người đạt danh hiệu này hết á. Và mỗi năm mỗi người chỉ đạt 1 lần trong năm đó thôi. Em làm được rồi anh. Cảm ơn anh.

Nếu năm liên tiếp thì công thức mảng kia đúng giải quyết vấn đề đúng không ạ

Đúng rồi bạn. Cảm ơn bạn nhiều. Mình dùng Sum với Countifs để đếm xong rồi mình dùng Conditional Formatting để hightlight cảnh báo, hihi. Mình làm lại file dữ liệu chuẩn và đính kèm lại trên #1 á. Cảm ơn bạn nhiều nha.

 

[huuthang_bd]

Tại em làm lỗi cột Năm đó anh. Chứ năm nào cũng có người đạt danh hiệu này hết á. Và mỗi năm mỗi người chỉ đạt 1 lần trong năm đó thôi. Em làm được rồi anh. Cảm ơn anh.

Có lẽ bạn chưa hiểu ý tôi. Tôi không nói có năm không có người đạt danh hiệu hay 1 người đạt danh hiệu 2 lần trong 1 năm. Ý tôi là công thức thỏa mãn yêu cầu đánh dấu những người đạt danh hiệu 3 năm liên tục nhưng công thức đó không gọi là đếm số năm đạt danh hiệu liên tục được. Ví dụ ông A đạt danh hiệu năm 2010 và 2012, năm 2011 không đạt thì tại năm 2012, rõ ràng số năm đạt danh hiệu liên tiếp là 1 nhưng kết quả của công thức là 2.
Và để đếm như vậy thì không cần SUM hay SUMPRODUCT, chỉ dùng hàm COUNTIFS là được.

=COUNTIFS($B$4:$B4,B4,$F$4:$F4,">"&(F4-3))