Conditional formatting trong excel – Cách định dạng có điều kiện từ PRO

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