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

Thu Huyền
12 thg 12, 2024

là trong excel. Áp dụng cho excel 2003, 2007, 2010, 2013, 2024.

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

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

  • 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

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

Now, let's move your ass!

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.

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)

Đố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.

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

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.

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

(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

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.

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

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.

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

    Đơ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.

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

      • B1: Bôi đen vùng cần định dạng
      • 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)

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

      =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

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

      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.

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

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

      • B1: Bôi đen A2:A11
      • 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

      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 😀

      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.

      : 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,...

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

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

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

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

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

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

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

      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.

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

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

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

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

      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.

      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:

      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)

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

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

      Next Post Previous Post