Định dạng theo điều kiện dùng công thức

Chia sẻ bài viết này:

Định dạng theo điều kiện dùng công thức

Định dạng theo điều kiện làm cho bảng tính trở lên lung linh đẹp đẽ, định dạng sử dụng công thức là chìa khóa để cho phép định dạng theo những điều kiện phức tạp. ( dinh dang theo dieu kien)

Về nguyên lý thì ta cần cung cấp cho Excel các thông tin sau:

  • Vùng áp dụng, thường ta sẽ chọn vùng này trước khi ra lệnh định dạng.
  • Công thức định dạng, nó giống như mọi công thức thông thường trong excel.
    • Nên tạo trong worksheet vì sẽ rất dễ viết.
    • Công thức này nên trả lại chỉ True hoặc False.
    • Nó trả lại True có nghĩa là định dạng được áp dụng.
  • Định dạng, định dạng thì rất phong phú, thiết lập thông số này như chúng ta định dạng thông thường. được thực hiện trong hộp thoại Format Cells.

Các bạn tham khảo nội dung video sau để có thêm các ví dụ cụ thể hơn:

A.  Định dạng theo điều kiện dùng công thức qua một ví dụ ( dinh dang theo dieu kien)

Các bước làm ( cho trường hợp hàng lẻ):

  • Viết công thức khảo sát cho ô đầu tiên ( góc trái trên của vùng cần định dạng).( dinh dang theo dieu kien)
    • Công thức tại ô H5 chính là công thức khảo sát cho ô E5.
    • Công thức tại cột H luân phiên là true và false

Dinh dang theo dieu kien

 

  • Copy công thức vừa khảo sát xong.
  • Chọn vùng cần định dạng
  • Chạy lệnh định dạng theo điều kiên.
    • Home->Styles->Conditional Formatting->Mange Rules…
    • Click New Rule
    • Chọn use formula to determine which cells to format.
  • Paste công thức vào trong “format values where this formula is true”.

Dinh dang theo dieu kien

  • Click Format… để định dạng. Ở đây chúng ta sẽ đổ  màu cho các ô.

Dinh dang theo dieu kien

Hoàn tất định dạng ta được toàn bộ các hàng lẻ đã được đổ màu:

Dinh dang theo dieu kien

Xem thêm: Khóa học Word Excel

Làm tương tự cho trường hợp hàng chẵn.

  • Tạo công thức khảo sát.

Dinh dang theo dieu kien su dung cong thuc

  • Dán công thức kháo sát cho công thức định dạng.

Dinh dang theo dieu kien su dung cong thuc

  • Định dạng ( đổ màu cho các ô).

Dinh dang theo dieu kien su dung cong thuc

  • Kết quả tổng hợp. ( dinh dang theo dieu kien)

Dinh dang theo dieu kien su dung cong thuc

B. Gỡ lỗi định dạng theo điều kiện ( dinh dang theo dieu kien)

Xét một ví dụ định dạng như hình, với công thức và kết quả như hình, rõ ràng nó đang bị sai so với yêu cầu.

dinh dang theo dieu kien su dung cong thuc

Cách gỡ lỗi ( dinh dang theo dieu kien su dung cong thuc):

  • Nhập công thức định dạng vào một vùng tạm để khảo sát.
  • Mở rộng vùng khảo sát bằng cách keó sang phải và xuống dưới như hình.

dinh dang theo dieu kien su dung cong thuc

Rõ ràng do ô K15 bằng FALSE nên ô tương ứng H15 đã không được tô màu. Nhìn lên thanh công thức ta thấy công thức này đã bị sai, nó lại phụ thuộc giá trị vào cột I. Nguyên nhân chính là do dùng định chỉ tương đối trong công thức định dạng. Sửa lại ta được công thức mới ( hơi khác với công thức ban đầu một chút).

dinh dang theo dieu kien su dung cong thuc

Chỉnh lại phần định dạng ta được kết quả như ý.

dinh dang theo dieu kien su dung cong thuc

Trên đây chỉ là cách gỡ lỗi một trường hợp đơn giản, về cơ bản bạn copy công thức định dạng đang bị sai vào một vùng tạm, rồi dùng các kỹ năng phân giải công thức để tìm ra nguyên nhân sai. Và từ đó có cách để sửa. Nôi dung phân giải công thức có thể tham khảo trong video sau:

Tác giả: Nguyễn Ngọc Thu Hiền


Chia sẻ bài viết này: