Conditional Formatting Trong Excel, Cách định Dạng Có điều Kiện Từ ...

Conditional Formatting là định dạng có điều kiện trong excel. Áp dụng cho excel 2003, 2007, 2010, 2013, 2016.

Bạn dùng CF để tô màu theo 1/ nhiều điều kiện. Ví dụ: Bôi màu ô trống, ô chứa lỗi, Tìm giá trị trùng, duy nhất, tìm TOP 3/5/10… sản phẩm có doanh số lớn nhất,…

Bài viết này dành cho: Nhân sự, Kế toán, Sale admin, Kinh doanh, Quản lý sản xuất,…

Trường đã dành ra 2 ngày lễ để viết bài này gửi tặng các bạn và sẽ dành nhiều ngày khác nữa để viết tiếp. Khối lượng kiến thức rất lớn và thường xuyên được cập nhật.

Bạn hãy luôn ủng hộ Trường nhé .

Các nội dung chính (thường xuyên bổ sung):

1. Cơ bản

• Giới thiệu chung (đã có)
• Giới thiệu về các điều kiện sẵn có (mặc định) (đã có)

2. Nâng cao bằng Ví dụ thực tế

• Ẩn lỗi (đã có)
• Tô màu các ô RỖNG (đã có)
• Ẩn bớt các ô chứa giá trị bị trùng: Phiếu nhập/ phiếu xuất (đã có)
• Xác định ngày đến hạn (tô màu chỉ định cho các ô này)  (đã có)
• Tô màu nền cách dòng (đã có)
• Bôi màu những nhân viên có ngày sinh nhật thuộc tháng hiện tại (đã có)
• Tô màu các ngày thứ 7 và chủ nhật/ ngày lễ (đã có)
• Tìm TOP 3/10/…. giá trị lớn nhất bằng cách bôi màu (đã có)

3. Lập báo cáo chuyên nghiệp khi sử dụng excel 2007 trở lên

• Sử dụng Databar: Chèn Biểu đồ ngay trong ô Excel
• Color scales: Bôi màu các ô chứa giá trị theo các nhóm
• Icon sets: Chèn biểu tượng tăng giảm ngay cạnh số trong excel giúp lập báo cáo chuyên nghiệp

Lưu ý:

• Trên đây là sơ lược nội dung, Trường sẽ thường xuyên bổ sung nội dung mới.
• Có một số mục đã được viết, hàng tuần Trường sẽ bổ sung các nội dung còn lại.

Do đó bạn hãy lưu lại bài viết này NGAY & LUÔN để tuần sau đỡ phải mất thời gian tìm kiếm nhé.

Now, let’s move your ass!

1. Định dạng có điều kiện: Cơ bản

1.1. Giới thiệu chung

Conditional formatting là  Định dạng có điều kiện trong excel.

Định dạng có điều kiện là việc áp dụng 1 hoặc nhiều loại định dạng cho 1/ 1 vùng ô theo 1/ nhiều điều kiện.

Sau khi xóa quy luật định dạng của CF thì định dạng mặc định của ô sẽ được khôi phục.

Ví dụ:

Tôi muốn bôi nền vàng tên của các sinh viên có điểm thi đạt loại GIỎI. Màu của các ô excel được xác định bởi:

• Màu = Vàng
• Điều kiện = GIỎI

(Trường xin dùng từ viết tắt CF thay thế cho conditional formatting trong một số chỗ của bài viết này)

Hoặc làm được những báo cáo đẹp như thế này (Click để download mẫu báo cáo dưới)

Bắt đầu từ excel 97, Microsoft đã bổ sung tính năng này vào các bảng tính của mình.

Đối với người lâu năm trong nghề phân tích tài chính như Trường thì CF là một công cụ tuyệt vời. CF là không thể thiếu để làm các báo cáo phân tích khoa học, trực quan và đẹp.

CF trong excel 2003:

Bị giới hạn số lượng điều kiện: Tối đa là 3

CF trong excel 2007 trở lên (2010, 2013, 2016):

Cho phép dùng tới 64 điều kiện

Trong bài viết này ad sẽ hướng dẫn các bạn cách sử dụng CF trên 2007 trở lên. Về cơ bản các tính năng trong phần 2 sẽ áp dụng được cho cả excel 2003.

2.2. Giới thiệu về các điều kiện có sẵn của conditional formatting

2.2.1. Cách Mở bảng điều khiển Định dạng có điều kiện

Để mở bảng điều khiển của Định dạng có điều kiện bạn chọn như sau:

Chọn Home > Conditional formatting > New rule

Bảng “New formatting rule” gồm 2 phần chính:

(1) Select a rule type – chọn loại điều kiện

(2) Edit the rule description – chỉnh sửa điều kiện và định dạng đi kèm

Lưu ý: Khi select rule ở phần 1 thì các thông tin ở phần 2 sẽ tự động thay đổi theo.

2.2.2. Giải thích Chi tiết các nhóm:

• All cells based on their values

Dựa vào giá trị của chính các ô trong vùng được chọn, excel sẽ tự động bôi màu. Ví dụ A1:E1, tôi chọn bôi màu giá trị nhỏ nhất (đậm nhất) và nhạt dần tới giá trị lớn nhất. Tham khảo hình dưới.

• Cells that contain

Nhóm điều kiện này chỉ bôi màu các ô có giá trị thỏa mãn điều kiện. Ví dụ sau sẽ giúp bạn rõ hơn:

Trường muốn bôi vàng  các ô có giá trị năm trong khoảng 2 – 3.

Trường cần phải thiết lập phần điều kiện định dạng như sau:

• Top or bottom ranked values

Cho phép bạn bôi màu cho những ô chứa giá trị lớn nhất, 2 ô có giá trị nhỏ nhất,…

Bạn không cần làm gì cả, chỉ cần bôi đen vùng ô chứa giá trị và thiết lập điều kiện mong muốn là được.

Trong hình trên, Trường muốn bôi vàng 2 ô chứa 2 giá trị lớn nhất trong vùng.

• Bước 1 là bôi đen
• Bước 2 là thiết lập các điều kiện như ở hình dưới.

• Values that are above or below average

Đơn giản bạn chỉ cần nói cho excel biết:

Tôi muốn bôi màu các ô có giá trị cao hơn giá trị trung bình của tất cả các ô có trong vùng được chọn.

Không phải làm nhiều & rất dễ thực hiện.

• Unique or duplicate values

Dễ như ăn kem!

Bạn chọn Duplicates hoặc Unique trong phần điều kiện rồi chọn định dạng cần cho các giá trị tương ứng là được.

Ví dụ như, Trường muốn bôi nền vàng cho các ô có giá trị trùng nhau hình dưới đây:

• Use a formula to determine which cells to format

Đây là phần mạnh nhất và được ứng dụng nhiều nhất trong excel. Bạn thiết lập công thức ở mức đơn giản như hình dưới đây hoặc phức tạp khi dùng thêm hàm if, countif,…

Hình trên là điều kiện để bôi màu các ô có giá trị = 3 trong vùng dữ liệu.

2. Học Conditional formatting Nâng cao thông qua ví dụ thực tế

2.1. Ẩn lỗi trong báo cáo tự động bằng định dạng có điều kiện trong excel

Ở cột A hình dưới, Trường có một danh sách các ô chứa giá trị số và giá trị là lỗi.

Khi trình bày báo cáo ta không thể để các lỗi đó xuất hiện được. Do đó cần chuyển hết các ô có lỗi thành màu trắng sẽ làm báo cáo đẹp và chuyên nghiệp hơn.

Bạn làm như sau:

• B1: Bôi đen vùng cần định dạng
• B2: Chọn Home > Conditional formatting > New rule
• B3: Trong bảng định dạng, bạn chọn loại định dạng: Use a formula to determine which cells to format
• B4: Trong phần soạn thảo định dạng chi tiết bạn chọn:

Nhập vào Format value where this formula is true: = ISERROR($A3)

Chọn format sẽ có bảng định dạng dữ liệu hiện ra, bạn chọn màu chữ “Trắng” là ok.

Sau khi chọn xong ta nhấn OK để hoàn tất quá trình ẩn lỗi trong excel.

Mở rộng:

=Iserror (value): Kiểm tra tất cả các lỗi

=Isna (value): Kiểm tra các ô chứa lỗi #N/A

=Iserr (value): Kiểm tra tất cả các lỗi trừ lỗi #N/A

Xem thêm: Học excel Doanh nghiệp chuyên sâu – thực tế

2.2. Tô màu các ô RỖNG

Trong bảng dữ liệu dưới đây, có không ít ô trống và nếu để màu nền giống với các ô có dữ liệu thì sao?

Rất khó nhận biết ô nào trống khi vùng dữ liệu lên tới hàng nghìn ô.

Khi đó, định dạng có điều kiện sẽ giúp ta rất nhiều bạn ạ.

Trường sẽ bôi màu nền (“xanh”) cho tất cả các ô trống trong vùng dữ liệu để dễ dàng nhận biết như sau:

• B1: Chọn vùng dữ liệu
• B2: Thiết lập điều kiện trong bảng CF như sau: Isblank (A1)
• B3: Chọn màu nền tại Format rồi nhấn OK

Sau khi hoàn tất các bước trên, ta sẽ một bảng dữ liệu với các ô trống được bôi nền màu xanh. Thực sự khác biệt phải không nào các bạn.

Mở rộng:

Dưới đây là các hàm được dùng để kiểm tra dữ liệu trong excel. Bạn hãy học và vận dụng kiến thức được học nhé.

Click Download ảnh dưới

2.3. Ẩn bớt các ô chứa giá trị bị trùng: Phiếu nhập/ phiếu xuất

Trong hình ảnh dưới đây, Trường có một bảng kê các phiếu nhập kho trong kỳ.

Bạn có nhận thấy ở cột số chứng từ và ngày tháng có nhiều giá trị bị lặp nhau.

Điều này gây khó khăn trong việc nhận biết các phiếu nhập/ xuất.

Vì Vậy!

Chúng ta sẽ dùng Conditional formatting để ẩn bớt các ô chứa giá trị trùng đi nhé.

Hình sau sẽ minh họa cho điều chúng ta sẽ làm.

Cách làm như sau:

• B1: Bôi đen A2:A11
• B2: Chọn Home > CF > New Rules > Use formula to …
• B3: Nhập công thức sau vào phần formula: =A1=A2
• B4: Chọn màu chữ “Trắng” trong Format
• B5: Nhấn OK để hoàn tất quá trình định dạng có điều kiện

Sau khi định dạng xong cho cột A, ta copy định dạng sang cho cột B (vùng: B2:B11) là hoàn tất bạn nhé.

Quả thực làm rất nhanh và dễ, đúng không nào các bạn.

Nhưng kết quả thu được đúng là quả ngọt 😀

Xem thêm: Học excel Doanh nghiệp chuyên sâu – thực tế

2.4. Xác định ngày đến hạn (tô màu chỉ định cho các ô này)

Phần này đặc biệt hữu ích cho những ai làm kế toán công nợ, thanh toán. Ngoài ra thì những ai đang quản lý phần chăm sóc khách hàng cũng cần phải biết.

Mục đích: Dễ dàng biết được hôm nay phải thu, trả tiền ai? Phải gọi điện chăm sóc ai,…

Cách làm như sau:

• B1: Bôi đen vùng dữ liệu cần bôi màu

• B2: Chọn Home > Conditional formatting > New rule
• B3: Nhập công thức để xác định ngày nào trong khoảng [Ngày hiện tại +0 -> +7]. Cùng với đó là vào format chọn màu cho ngày thỏa mãn điều kiện.

Cuối cùng ta nhấn OK để hoàn tất việc định dạng có điều kiện. Kết quả ta được như sau:

Vậy tại sao lại dùng hàm AND trong trường hợp này?

Yêu cầu là tô màu những ô chứa ngày đến hạn trong vòng 7 ngày tới.

Do đó, các ngày thỏa mãn điều kiện là ngày nằm trong khoảng: [Ngày hôm nay] và [Ngày hôm nay + 7]

Và khi đó, ta phải dùng hàm And để xác định điều kiện 2 chiều này.

Lưu ý:

• Định dạng: Việc chọn màu và tìm tới vùng nhập công thức (điều kiện) định dạng thì rất dễ rồi. Ai cũng phải biết được khi đã học tới bước này.
• Công thức: Nhưng việc sử dụng công thức như thế nào và tại sao lại dùng như vậy? Thì với nhiều bạn cần phải nghiên cứu đi nghiên cứu lại, thực hành đi thực hành lại.

Xem thêm: Học excel Doanh nghiệp chuyên sâu – thực tế

2.5. Tô màu nền cách dòng

Cái này hay nè!

Bạn có một bảng dữ liệu với chỉ toàn một màu trắng – hơi khó nhìn dữ liệu phải không nào?

• Bảng bên trái hình dưới đây là báo cáo chưa được định dạng.
• Bảng bên phải hình dưới là báo cáo sau khi định dạng cách dòng.

Về cảm quan, Trường thấy đọc báo cáo bảng bên phải sẽ dễ nắm thông tin hơn nhiều.

Và Trường sẽ dạy các bạn bôi màu cách dòng cả nghìn dòng trong nháy mắt

Vậy ta phải làm thế nào?

Dễ ợt!

• B1: Chọn vùng ô cần định dạng (đương nhiên rồi)
• B2: Mở bảng điều khiển Định dạng có điều kiện để nhập công thức định dạng
• B3: Nhập công thức để xác định điều kiện, chọn màu nền và nhấn OK

Mở rộng:

Công thức sử dụng trong việc tô màu nền cách dòng:

• 1. Tô màu các ô thuộc dòng lẻ: =Mod(Row(),2)
• 2. Tô màu các ô thuộc dòng chẵn: =Mod(Row(),2)=0
• 3. Tô màu nhiều ô:  =Mod(Row(),n) – trong đó n là mẫu số 3 dòng, 4 dòng,…

2.6. Bôi màu những nhân viên có ngày sinh nhật thuộc tháng hiện tại

Với những người làm nhân sự thì việc hàng tháng thống kê người có ngày sinh nhật trong tháng là chuyện thường ở phố huyện.

Nếu công ty chỉ có vài người thì dễ rồi. Nhưng nếu có hàng trăm/ nghìn nhân viên mà ngồi đếm tay thì hơi vất.

Ta có thể dùng Conditional formatting để bôi màu nền cho mọi nhân viên có ngày sinh nhật trong tháng hiện tại. Chỉ mất vài giây cho hàng nghìn nhân viên luôn nhé.

Thực hành định dạng có điều kiện:

Dưới đây là phần đầu của danh sách 100 nhân viên với thông tin: Tên & Ngày sinh

Tháng này là tháng 01 năm 2017, Trường sẽ dùng CF để bôi màu xanh tất những ai có sinh nhật tháng này.

Bạn làm theo các bước thông thường để mở được cửa sổ nhập điều kiện cho việc định dạng.

Công thức: =Month($B4)=Month(Now())

Cụ thể bạn xem hình dưới.

Và kết quả là ta thu được một danh sách nhân viên sặc sỡ 😀

Mở rộng:

• 1. Hàm Month sẽ tự động tính ra tháng của ngày được chọn
• 2. Hàm Now() sẽ lấy ngày hiện tại của hệ thống

Hàm Today() – là sự thay thế cho hàm Now(). Có tác dụng gần tương đương nhau.

2.7. Tô màu các ngày thứ 7 và chủ nhật/ ngày lễ

Nhiều khi ta phải lập báo cáo với nhiều cột, mỗi cột là dữ liệu của một ngày. Và bạn phải bôi màu các cột ngày thứ 7 và chủ nhật để làm nổi bật các ngày này.

Đặc biệt khi bạn là kế toán lương, nhân sự thì bảng chấm công là công việc hàng ngày của bạn.

Và phần hướng dẫn dưới đây của Trường sẽ giúp các bạn dễ dàng quản lý việc chấm công của mình.

Cách làm như sau:

• B1: Bôi đen các cột muốn định dạng có điều kiện
• B2: Mở bảng định dạng conditional formatting
• B3: Chọn Use a formula to determine which cells to format
• B4: Nhập công thức để xác định ngày nào thứ 7 hoặc chủ nhật bằng hàm Weekday. Bạn xem hình dưới để hiểu rõ hơn cách sử dụng hàm.

• B5: Chọn màu và định dạng mong muốn của các ô thỏa mãn điều kiện rồi nhấn OK để hoàn tất.

Kết quả ta có được bảng như sau:

2.8 Tìm TOP 3/10/…. giá trị lớn nhất bằng cách bôi màu

Dưới đây là danh sách các giao dịch trong 3 ngày, làm thế nào để định dạng TOP 3 giao dịch có số lượng bán lớn nhất.

Định dạng TOP 3 có thể gồm:

• Bôi màu nền
• Bôi màu chữ
• In đậm, in nghiêng
• Gạch ngang…

Cách làm như sau:

B1: Chọn vùng dữ liệu cần định dạng TOP 3 (chính là cột số lượng)

B2: Chọn thẻ HOME > Conditional Formatting > Top/ Bottom rules > More rules

B3: Thiết lập điều kiện và định dạng mong muốn

Đây là phần quan trọng nhất bạn cần phải quan tâm khi định dạng có điều kiện.

• TOP/ BOTTOM: Nếu là Top là các giá trị lớn nhất; còn Bottom là các giá trị nhỏ nhất. Tùy vào bạn cần gì để chọn cái tương ứng
• 10 => Bạn có thể thay là số 3 hay 15 hay 100 tùy vào bạn muốn Định dạng Top hay Bottom bằng ấy giá trị
• Format: Là nơi bạn bôi màu, bôi đậm, gạch chân,…

Bước cuối cùng là nhấn OK để hoàn tất.

Trong ví dụ trên, Trường sẽ bôi đậm TOP 3 giao dịch có số lượng bán nhỏ nhất. Và kết quả như sau:

3. Các tính năng siêu đỉnh của CF có từ excel 2007 trở lên bạn nên biết

Sẽ có ngày 29/11/2017

Xem thêm: Học Conditional qua video cùng Thầy Trường