Cách sử dụng Conditional Formatting để to màu
Conditional Formatting là định dạng có điều kiện trong excel. Áp dụng cho excel 2003, 2007, 2010, 2013, 2016. Show
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
2. Nâng cao bằng Ví dụ thực tế
3. Lập báo cáo chuyên nghiệp khi sử dụng excel 2007 trở lên
Lưu ý:
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, lets move your ass! 1. Định dạng có điều kiện: Cơ bản1.1. Giới thiệu chungConditional 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:
(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 formatting2.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:
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.
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:
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.
Đơ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.
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:
Đâ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:
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 excelDoanh nghiệp chuyên sâu thực tế 2.2. Tô màu các ô RỖNGTrong 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:
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ấtTrong 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:
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 excelDoanh 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:
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 ý:
Xem thêm:Học excelDoanh nghiệp chuyên sâu thực tế 2.5. Tô màu nền cách dòngCá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?
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!
Mở rộng: Công thức sử dụng trong việc tô màu nền cách 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ạiVớ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:
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:
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àuDướ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ánlớn nhất. Định dạng TOP 3 có thể gồm:
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.
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 |