Định dạng có điều kiện (Conditional Formatting) phức tạp?

[Tony Jaa]

Chào cả nhà, mình mò mẫm vài tiếng mà chưa ra vụ này, nhờ các bác chung tay giải quyết dùm mình, dùng cách nào để định dạng có điều kiện (Conditional Formatting) cho bảng tính này với điều kiện tương đối phức tạp như sau:

1. Tính theo ngày vào làm (Cột G):
   
   
   a)- Nếu Chức danh là Nhân Viên (Căn cứ Cột F) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 60 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (Automatic).
   ​
   b)- Các chức danh khác (Căn cứ Cột F) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 30 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (Automatic).
   
   ​
2. Tại cột J nếu có nhập ngày thì chữ sẽ được tô màu xanh dương, nhưng sau 35 ngày (Tính theo ngày nhập trong cột J) thì chữ sẽ tự động trở về màu đen (Automatic)[/COLOR]
   
   
3. Tại cột H hoặc cột I, nếu 1 trong 2 cột này mà khác rỗng (<>""), thì tự động chữ sẽ được tô thành màu đỏ. bất kể các điều kiện đã nêu trên.

Lưu ý:

1. Chữ tô tự động cho nguyên dòng trong bảng tính từ A đến J
2. Giả sử, trong trường hợp một người Công Nhân mới vô làm được 15 ngày (tính từ ngày vào làm) -> chữ vẫn tô màu xanh, nhưng đến ngày thứ 16 người này nghỉ việc (một trong 2 cột H hoặc I khác rỗng) thì chữ được tự động tô màu đỏ.
   [*]

Cảm ơn các bạn!

(File đính kèm)

 

[hoangminhtien]

Bạn xem trong file đính kèm nhé!

 

[ptm0412]

Gom lại 3 điều kiện:
1. Chữ đen
=OR(AND(TODAY()-$G3>=60;LEFT($B3;2)="NV");AND(TODAY()-$G3>=30;LEFT($B3;2)<>"NV"))

2. Chữ đỏ:
=$H3&$I3<>""

3. Chữ xanh dương:
=$J3<>""

ngoài ra, mặc định là xanh lá tuốt.

 

[huuthang_bd]

Bạn xem trong file. Cũng may là có 2 điều kiện cùng màu xanh lá, chứ nếu ko thì không thực hiện được trong Excel 2003

 

[Tony Jaa]

Đúng là GPE, nơi hội tụ các cao thủ về Excel, mình mò mãi kg được, trong khi các bác chỉ cần vài phút!

Tuy cả 3 bài đều vẫn chưa đúng tuyệt đối về điều kiện, giả sử bạn nhập vào cột chức danh chữ Công Nhân hoặc chữ Nhân Viên, bên cột ngày vào làm, các bạn nhập tương ứng với khoảng cách 30 ngày và 60 ngày, thấy hiệu ứng tô màu vẫn chưa đúng.

Vui lòng đọc kỹ lại yêu cầu ở bài 1 dùm mình nhé!

Cảm ơn quý vị!

 

[sealand]

Nếu bạn là người có khiếu về màu sắc thì viết code định màu chắc là giới hạn ĐK không còn là vấn đề nữa

 

[Tony Jaa]

Chào cả nhà, mình mò mẫm vài tiếng mà chưa ra vụ này, nhờ các bác chung tay giải quyết dùm mình, dùng cách nào để định dạng có điều kiện (Conditional Formatting) cho bảng tính này với điều kiện tương đối phức tạp như sau:

1. Tính theo ngày vào làm (Cột G):
   
   
   a)- Nếu Chức danh là Nhân Viên (Căn cứ Cột F) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 60 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (Automatic).
   ​
   b)- Các chức danh khác (Căn cứ Cột F) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 30 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (Automatic).
   
   ​
2. Tại cột J nếu có nhập ngày thì chữ sẽ được tô màu xanh dương, nhưng sau 35 ngày (Tính theo ngày nhập trong cột J) thì chữ sẽ tự động trở về màu đen (Automatic)[/COLOR]
   
   
3. Tại cột H hoặc cột I, nếu 1 trong 2 cột này mà khác rỗng (<>""), thì tự động chữ sẽ được tô thành màu đỏ. bất kể các điều kiện đã nêu trên.

Lưu ý:

1. Chữ tô tự động cho nguyên dòng trong bảng tính từ A đến J
2. Giả sử, trong trường hợp một người Công Nhân mới vô làm được 15 ngày (tính từ ngày vào làm) -> chữ vẫn tô màu xanh, nhưng đến ngày thứ 16 người này nghỉ việc (một trong 2 cột H hoặc I khác rỗng) thì chữ được tự động tô màu đỏ.
   [*]

Cảm ơn các bạn!

Dựa trên giải pháp của các bác đã làm, mình đã sửa lại như sau cho đúng với yêu cầu đầu bài:
(Đặt lần lượt các công thức sau trong hộp thoại Conditional Formatting)

Chữ màu đỏ:

=OR($H3<>"",$I3<>"")

Chữ màu xanh lá cây:

=OR(AND(TODAY()-$G3<=60,($F3)="Nhaân Vieân"),AND(TODAY()-$G3<=30,($F3)<>"Nhaân Vieân"))

Chữ màu xanh dương:

=OR($J3<>"",TODAY()-$J3<=35)

Các bác thử test lại xem có đúng không dùm mình với nhé!

Cảm ơn mọi người!

 

[Tony Jaa]

CŨNG FILE NÀY, LẠI PHÁT SINH VẤN ĐỀ RẮC RỐI HƠN, MÒ MÃI CHƯA RA, MONG ĐƯỢC MỌI NGƯỜI CHỈ GIÁO THÊM!

Yêu cầu lúc ban đầu như sau (đã thực hiện):

chào cả nhà, mình mò mẫm vài tiếng mà chưa ra vụ này, nhờ các bác chung tay giải quyết dùm mình, dùng cách nào để định dạng có điều kiện (conditional formatting) cho bảng tính này với điều kiện tương đối phức tạp như sau:

1. tính theo ngày vào làm (cột g):
   
   
   a)- Nếu chức danh là nhân viên (căn cứ cột f) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 60 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (automatic).
   ​
   b)- Các chức danh khác (căn cứ cột f) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 30 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (automatic).
   ​
2. tại cột j nếu có nhập ngày thì chữ sẽ được tô màu xanh dương, nhưng sau 35 ngày (tính theo ngày nhập trong cột j) thì chữ sẽ tự động trở về màu đen (automatic)[/color]
   
3. tại cột h hoặc cột i, nếu 1 trong 2 cột này mà khác rỗng (<>""), thì tự động chữ sẽ được tô thành màu đỏ. bất kể các điều kiện đã nêu trên.

lưu ý:

1. chữ tô tự động cho nguyên dòng trong bảng tính từ a đến j
2. giả sử, trong trường hợp một người công nhân mới vô làm được 15 ngày (tính từ ngày vào làm) -> chữ vẫn tô màu xanh, nhưng đến ngày thứ 16 người này nghỉ việc (một trong 2 cột h hoặc i khác rỗng) thì chữ được tự động tô màu đỏ.
   [*]

cảm ơn các bạn!
(file đính kèm)

CÒN DƯỚI ĐÂY LÀ YÊU CẦU MỚI PHÁT SINH THÊM:

a)- Đối với các chức danh là Công Nhân, Tổ trưởng, Tổ Phó, Tài Xế (căn cứ cột f) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 30 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (automatic).
​

b)- Các chức danh khác còn lại, khác 4 chức danh trong mục a nêu trên (căn cứ cột F) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 60 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (automatic).
​

MONG SỚM CÓ SỰ TRỢ GIÚP, CẢM ƠN MỌI NGƯỜI ĐÃ QUAN TÂM!

 

[ptm0412]

Tuy cả 3 bài đều vẫn chưa đúng tuyệt đối về điều kiện, giả sử bạn nhập vào cột chức danh chữ Công Nhân hoặc chữ Nhân Viên, bên cột ngày vào làm, các bạn nhập tương ứng với khoảng cách 30 ngày và 60 ngày, thấy hiệu ứng tô màu vẫn chưa đúng.
Vui lòng đọc kỹ lại yêu cầu ở bài 1 dùm mình nhé!

Cái của mình cũng đúng đấy Tony à, chỉ là vì mình tìm chả thấy cái nhân viên nào trong cột F, mà theo nguyên tắc cơ sở dữ liệu thì nên căn cứ vào mã, ở đây là cột B: NVxxx
Cho nên mình lấy Left(B3, 2)!!

Nếu lấy theo cột F thì vẫn OK, nhưng lỡ mà gõ sai, dư, thiếu 1 ký tự thì anh Bill cũng bó tay!

a)- Đối với các chức danh là Công Nhân, Tổ trưởng, Tổ Phó, Tài Xế (căn cứ cột f) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 30 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (automatic).

b)- Các chức danh khác còn lại, khác 4 chức danh trong mục a nêu trên (căn cứ cột F) thì kể từ khi bắt đầu vào làm, khi nhập liệu xong, tự động chữ được tô thành màu xanh lá, sau 60 ngày (kể từ ngày vào làm) chữ trở lại màu đen bình thường (automatic)

Cũng vậy thôi, sửa cái công thức sau:
Or(And(đk ngày>=30, đk NV), And(đk ngày >= 60, đkNV)) thành:

Or(And(đk ngày>=30, Or(đk NV1, đk NV2, đk NV3, ...)), And(đk ngày>=60, Or(đk NV5, đk NV6, đk NV7, ...)))

 

[ptm0412]

Tại cột J nếu có nhập ngày thì chữ sẽ được tô màu xanh dương, nhưng sau 35 ngày (Tính theo ngày nhập trong cột J) thì chữ sẽ tự động trở về màu đen (Automatic)

Hình như hôm qua không có câu màu xanh in đậm ở trên? Cái này làm anh em hỏng giò té ngửa đập đầu rồi Tony à. Chiêu thức võ Thái chăng?

 

[Tony Jaa]

Hình như hôm qua không có câu màu xanh in đậm ở trên? Cái này làm anh em hỏng giò té ngửa đập đầu rồi Tony à. Chiêu thức võ Thái chăng?

Vâng đúng vậy, xin lỗi bác, bởi vì sau khi xem lại file, em đã đính chính và bổ sung điều kiện, nên em đã đăng bài và post lại file ở bài #8 (cùng topic này).

Cảm ơn bác đã quan tâm, chỉ giáo!

 

[huuthang_bd]

Tuy cả 3 bài đều vẫn chưa đúng tuyệt đối về điều kiện, giả sử bạn nhập vào cột chức danh chữ Công Nhân hoặc chữ Nhân Viên, bên cột ngày vào làm, các bạn nhập tương ứng với khoảng cách 30 ngày và 60 ngày, thấy hiệu ứng tô màu vẫn chưa đúng.

Tôi vẫn chưa hiểu những bài trả lời cho bạn chưa đúng tuyệt đối là chưa đúng ở chỗ nào?! Còn nữa, nếu muốn đính chính thì đính chính bằng cách trả lời, đừng sửa bài cũ khi người khác đã trả lời.

 

[Tony Jaa]

Cái của mình cũng đúng đấy Tony à, chỉ là vì mình tìm chả thấy cái nhân viên nào trong cột F, mà theo nguyên tắc cơ sở dữ liệu thì nên căn cứ vào mã, ở đây là cột B: NVxxx
Cho nên mình lấy Left(B3, 2)!!

Nếu lấy theo cột F thì vẫn OK, nhưng lỡ mà gõ sai, dư, thiếu 1 ký tự thì anh Bill cũng bó tay!



Cũng vậy thôi, sửa cái công thức sau:
Or(And(đk ngày>=30, đk NV), And(đk ngày >= 60, đkNV)) thành:

Or(And(đk ngày>=30, Or(đk NV1, đk NV2, đk NV3, ...)), And(đk ngày>=30, Or(đk NV5, đk NV6, đk NV7, ...)))

Em đã làm công thức theo cú pháp bác nêu trên, sao vẫn không được nhỉ?

 

[ptm0412]

Bây giờ vầy, như đã nói, nên và chỉ nên lấy điều kiện từ mã nhân viên, cột B làm điều kiện
Vậy, để cho đơn giản, thêm 1 ký tự vào mã nhân viên, là 1 cho loại nhân viên 30 ngày, 2 cho loại nhân viên 60 ngày (hoặc ký tự khác).
Thí dụ mã là: NV1001, TP1002, LX1003
hoặc mã là QD2015, v. v...

Công thức sẽ là:
Or(And(đk ngày, Mid($B3, 3, 1) ="1"), And(đk ngày , Mid($B3, 3, 1) ="2"))

Ở đây cái điều kiện ngày thì tuỳ theo file, do file của tôi là >= cho màu đen, 2 file của hoangminhtien và HuuThang là < cho màu xanh.

Nói thêm: Dùng mã nhân viên ngoài việc tránh sai sót về dư thiếu ký tự, còn tránh sai sót về Font. File của Hoangminhtien đặt điều kiện theo font unicde, trong khi thực tế font là VNI-Times, chả đời nào mà điều kiện thoả để tô màu.

 

[ptm0412]

Or(And(đk ngày>=30, Or(đk NV1, đk NV2, đk NV3, ...)), And(đk ngày>=30, Or(đk NV5, đk NV6, đk NV7, ...)))
Em đã làm công thức theo cú pháp bác nêu trên, sao vẫn không được nhỉ?

Có thể có sai sót nào đó khi định nghĩa thế nào là đk NV1, đk NV2, đk NV3
Công Nhân, Tổ trưởng, Tổ Phó, Tài Xế (căn cứ cột f): vậy

1. trong cái And thứ nhất:
đk NV1 : $F3="Công nhân"
đk NV2 : $F3="Tổ trưởng"
đk NV3 : $F3="Tổ Phó"
đk NV4 : $F3="Tài Xế"

2. Trong cái And thứ 2:

đk NV5 : $F3<>"Công nhân"
đk NV6 : $F3<>"Tổ trưởng"
đk NV7 : $F3<>"Tổ Phó"
đk NV8 : $F3<>"Tài Xế"

Đó là chưa kể lỗi Font điều kiện lại vẫn gõ unicode.

Ngoài ra công thức của tô icó 1 cái sai, là cái sai Logic học:

Not (A or B) = (Not A) And (Not B)

Vậy công thức đúng là:

Or(And(đk ngày>=30, Or(đk NV1, đk NV2, đk NV3, ...)), And(đk ngày>=60, And(đk NV5, đk NV6, đk NV7, ...)))

 

[Tony Jaa]

Có thể có sai sót nào đó khi định nghĩa thế nào là đk NV1, đk NV2, đk NV3
Công Nhân, Tổ trưởng, Tổ Phó, Tài Xế (căn cứ cột f): vậy

1. trong cái And thứ nhất:
đk NV1 : $F3="Công nhân"
đk NV2 : $F3="Tổ trưởng"
đk NV3 : $F3="Tổ Phó"
đk NV4 : $F3="Tài Xế"

2. Trong cái And thứ 2:

đk NV5 : $F3<>"Công nhân"
đk NV6 : $F3<>"Tổ trưởng"
đk NV7 : $F3<>"Tổ Phó"
đk NV8 : $F3<>"Tài Xế"

Đó là chưa kể lỗi Font điều kiện lại vẫn gõ unicode.

Ngoài ra công thức của tô icó 1 cái sai, là cái sai Logic học:

Not (A or B) = (Not A) And (Not B)

Vậy công thức đúng là:

Or(And(đk ngày>=30, Or(đk NV1, đk NV2, đk NV3, ...)), And(đk ngày>=30, And(đk NV5, đk NV6, đk NV7, ...)))

Cú pháp này vẫn chưa đúng bác ạ!

Or(And(đk ngày>=30, Or(đk NV1, đk NV2, đk NV3, ...)), And(đk ngày>=30, And(đk NV5, đk NV6, đk NV7, ...)))

Em đã làm theo cú pháp này, nhưng vẫn không đúng (nhức cái đầu luôn)?!

[/COLOR][/B]

=OR(AND(TODAY()-$G3<31,OR($F3="Coâng Nhaân",$F3="Toå tröôûng",$F3="Toå Phoù",$F3="Taøi Xeá")),AND(TODAY()-$G3<61,($F3)<>"Coâng Nhaân",$F3<>"Toå tröôûng",$F3="Toå Phoù",$F3="Taøi Xeá")) 

(gõ theo kiểu VNI)

1. trong cái And thứ nhất:

đk NV1 : $F3="Công nhân"
đk NV2 : $F3="Tổ trưởng"
đk NV3 : $F3="Tổ Phó"
đk NV4 : $F3="Tài Xế"

2. Trong cái And thứ 2:

đk NV5 : $F3<>"Công nhân"
đk NV6 : $F3<>"Tổ trưởng"
đk NV7 : $F3<>"Tổ Phó"
đk NV8 : $F3<>"Tài Xế"

Bác xem cụ thể trong file đính kèm tại bài này, em tìm mãi không biết sai chỗ nào?

Cảm ơn bác!

 

[ptm0412]

Bác xem cụ thể trong file đính kèm tại bài này, em tìm mãi không biết sai chỗ nào?

Điều kiện 2:
=OR(AND(TODAY()-$G3<31;OR($F3="Coâng Nhaân";$F3="Toå tröôûng";$F3="Toå Phoù";$F3="Taøi Xeá"));AND(TODAY()-$G3<61;($F3)<>"Coâng Nhaân";$F3<>"Toå tröôûng";$F3="Toå Phoù";$F3="Taøi Xeá"))

Còn 2 cái dấu bằng zô ziên

Điều kiện 3:
=OR($J3<>"";TODAY()-$J3<=35)

sửa thành:
=AND($J3<>"";TODAY()-$J3<=35)

 

[Tony Jaa]

Điều kiện 2:
=OR(AND(TODAY()-$G3<31;OR($F3="Coâng Nhaân";$F3="Toå tröôûng";$F3="Toå Phoù";$F3="Taøi Xeá"));AND(TODAY()-$G3<61;($F3)<>"Coâng Nhaân";$F3<>"Toå tröôûng";$F3="Toå Phoù";$F3="Taøi Xeá"))

Còn 2 cái dấu bằng zô ziên

Điều kiện 3:
=OR($J3<>"";TODAY()-$J3<=35)

sửa thành:
=AND($J3<>"";TODAY()-$J3<=35)

Điều kiện 1: Đã OK tốt.

=OR($H3<>"",$I3<>"")

Điều kiện 2: Sửa đủ kiểu, vẫn chưa được!

=OR(AND(TODAY()-$G4<31,OR($F4="Coâng Nhaân",$F4="Toå tröôûng",$F4="Toå Phoù",$F4="Taøi Xeá")),AND(TODAY()-$G4<61,AND($F4)<>"Coâng Nhaân",$F4<>"Toå tröôûng",$F4<>"Toå Phoù",$F4<>"Taøi Xeá"))

Hoặc:

=OR(AND(TODAY()-$G4<31,OR($F4="Coâng Nhaân",$F4="Toå tröôûng",$F4="Toå Phoù",$F4="Taøi Xeá")),AND(TODAY()-$G4<61,OR($F4<>"Coâng Nhaân",$F4<>"Toå tröôûng",$F4<>"Toå Phoù",$F4<>"Taøi Xeá"))

Điều kiện 3: Đã OK tốt.

=AND($J3<>"",TODAY()-$J3<=35)

(Làm bác cũng nhức đầu theo em luôn, bữa nào mời nhậu bữa xả xì trét nhé bác!)

 

[ptm0412]

Nhức đầu từ lúc té ngửa cơ. Giờ thì đỡ đỡ rồi. Xem file.

 

[tutientrung]

Mình cũng có một bài cần giúp đỡ về CF ,bài hơi bị rối .Mong các bạn giúp mình .Xin cám ơn các bạn.Nội dung mình đã ghi trong file .