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))

 

[xuongrongdat]

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))

Em hiểu rồi. Đúng là sai ở ô F46 em tô vàng bên dưới như cái bẫy anh nói:

@phuocam , @bomberman211 : mình không nhận ra luôn á. Vì dữ liệu nhiều mà nhìn vào công thức đôi khi dễ rơi vào mê hồn trận.

Các anh, các bạn giúp nốt mình cột G luôn với. Như hình trên thì mình đã điền tay các số màu đỏ á. Nghĩa là đếm số năm liên tiếp đạt danh diệu của 1 nhân viên tại năm nhất định.

Mọi người lấy file ở #1 hay ở ngay bài này đều được. Mình chỉnh lại dữ liệu chuẩn hết rồi. Cảm ơn mọi người.

PS: ủa vậy rồi cái xét 3 năm liên tiếp vẫn chưa giải quyết xong luôn, éc. Công thức của anh @huuthang_bd ở trên cũng chưa giải quyết được sự "liên tiếp"?!

 

[huuthang_bd]

Em hiểu rồi. Đúng là sai ở ô F46 em tô vàng bên dưới như cái bẫy anh nói:
View attachment 249228
@phuocam , @bomberman211 : mình không nhận ra luôn á. Vì dữ liệu nhiều mà nhìn vào công thức đôi khi dễ rơi vào mê hồn trận.

Các anh, các bạn giúp nốt mình cột G luôn với. Như hình trên thì mình đã điền tay các số màu đỏ á. Nghĩa là đếm số năm liên tiếp đạt danh diệu của 1 nhân viên tại năm nhất định.

Mọi người lấy file ở #1 hay ở ngay bài này đều được. Mình chỉnh lại dữ liệu chuẩn hết rồi. Cảm ơn mọi người.

PS: ủa vậy rồi cái xét 3 năm liên tiếp vẫn chưa giải quyết xong luôn, éc. Công thức của anh @huuthang_bd ở trên cũng chưa giải quyết được sự "liên tiếp"?!

3 năm liên tiếp vẫn đúng mà bạn. Trên hình F66 và F95 tính đúng 3 năm liên tiếp rồi, chỉ có F46 là sai nhưng không ảnh hưởng đến yêu cầu ban đầu của bạn.
--
G31 sao không phải 2 mà là 1 vậy bạn?

 

[xuongrongdat]

3 năm liên tiếp vẫn đúng mà bạn. Trên hình F66 và F95 tính đúng 3 năm liên tiếp rồi, chỉ có F46 là sai nhưng không ảnh hưởng đến yêu cầu ban đầu của bạn.
--
G31 sao không phải 2 mà là 1 vậy bạn?

G31 là 2 đó anh. Em quáng rồi.

Còn F46 phải là 1 vì nó mất tính liên tiếp rồi anh. Nên nó phải bắt đầu lại từ đầu là 1 á anh.

 

[huuthang_bd]

Còn F46 phải là 1 vì nó mất tính liên tiếp rồi anh. Nên nó phải bắt đầu lại từ đầu là 1 á anh.

Nhưng bạn chỉ cần tô màu những kết quả 3 mà, 3 thì vẫn đúng.

 

[xuongrongdat]

Nhưng bạn chỉ cần tô màu những kết quả 3 mà, 3 thì vẫn đúng.

À à, đúng rồi. Em cần tô 3 thôi.
Còn cột G mình làm sao vậy anh? Em muốn đếm lên luôn 345678 nếu họ cứ liên tiếp đạt mãi.

 

[huuthang_bd]

À à, đúng rồi. Em cần tô 3 thôi.
Còn cột G mình làm sao vậy anh? Em muốn đếm lên luôn 345678 nếu họ cứ liên tiếp đạt mãi.

Bạn thử công thức mảng này

=SUM(--(IFERROR(LARGE($E$4:$E4*($B$4:$B4=B4),{1;2;3;4;5;6;7;8;9;10}),0)=E4-{0;1;2;3;4;5;6;7;8;9}))

 

[bomberman211]

À à, đúng rồi. Em cần tô 3 thôi.
Còn cột G mình làm sao vậy anh? Em muốn đếm lên luôn 345678 nếu họ cứ liên tiếp đạt mãi.

Góp vui luôn
H4=IF(COUNTIFS($B$4:B4,B4,$E$4:E4,E4-1)=0,1,LOOKUP(2,1/($B$3:B3=B4),$H$3:H3)+1)

 

[phuongvq123]

À à, đúng rồi. Em cần tô 3 thôi.
Còn cột G mình làm sao vậy anh? Em muốn đếm lên luôn 345678 nếu họ cứ liên tiếp đạt mãi.

Giá file ban đầu không thiếu năm thì đỡ đau đầu hơn không )

 

[xuongrongdat]

Bạn thử công thức mảng này

=SUM(--(IFERROR(LARGE($E$4:$E4*($B$4:$B4=B4),{1;2;3;4;5;6;7;8;9;10}),0)=E4-{0;1;2;3;4;5;6;7;8;9}))

Em thử rồi. Công thức anh cho kết quả đúng. Cảm ơn anh. Em đang tìm hiểu công thức anh.

Góp vui luôn
H4=IF(COUNTIFS($B$4:B4,B4,$E$4:E4,E4-1)=0,1,LOOKUP(2,1/($B$3:B3=B4),$H$3:H3)+1)

Em thử rồi. Công thức anh cho kết quả đúng. Cảm ơn anh. Em đang tìm hiểu công thức anh.

Giá file ban đầu không thiếu năm thì đỡ đau đầu hơn không )

Thiếu năm là do mình tính sai ở cột Năm, chứ không phải thiếu. Xin lỗi nha !

 

[phuongvq123]

Em thử rồi. Công thức anh cho kết quả đúng. Cảm ơn anh. Em đang tìm hiểu công thức anh.

Em thử rồi. Công thức anh cho kết quả đúng. Cảm ơn anh. Em đang tìm hiểu công thức anh.

Thiếu năm là do mình tính sai ở cột Năm, chứ không phải thiếu. Xin lỗi nha !

Công thức bên dưới nghĩa là ví dụ từ 2010-2012 được thì đếm có danh hiệu thì đánh số 123.., nếu 2013 không có thì vế điều kiện true, 2014 -2016 mà có thì từ 2014 lại đếm bắt đầu từ 1, 2015 sẽ lấy 2014 +1