Cách viết công thức Excel trong Conditional Formatting và Data Validation

Dùng Cell để viết công thức trên Excel đã quá quen thuộc rồi. Nhưng chúng ta có thể viết công thức Excel trong Conditional Formatting và Data Validation. Tìm hiểu ngay nào!

Phân biệt công thức Excel và hàm trong Excel

Hầu hết mọi người thường sử dụng Cell để viết công thức. Nhưng bạn có biết rằng chúng ta cũng có thể sử dụng các công thức trong Conditional Formatting, Data Validation, Pivot Table và Advance Filter.

Trước khi tìm hiểu cách sử dụng chúng, Gitiho sẽ giải thích ngắn gọn cho bạn thế nào là công thức và đâu là sự khác biệt giữa hàm và công thức. Đa số mọi người sử dụng hai thuật ngữ này để thay thế cho nhau nhưng thực chất chúng không giống nhau.

Hàm trong Excel là một mật mã thiết lập sẵn trong excel để thực hiện một số loại tính toán. Có nhiều loại hàm khác nhau có sẵn trong Excel như hàm toán học, hàm tra cứu, hàm logic, v.v

Công thức Excel là phương trình được viết bởi người dùng để tính toán và cho ra kết quả. Một công thức có thể chứa các hàm, giá trị, tham chiếu ô, tên được xác định hoặc kết hợp tất cả.

Sau đây là cách sử dụng công thức ở những công cụ khác ngoài Cell.  Bên cạnh đó, nếu bạn muốn được tất tần tật kiến thức từ cơ bản đến nâng cao về Excel thì hãy tham gia khóa học online Tuyệt đỉnh Excel của Gitiho nhé:

Cách viết công thức Excel trong Conditional Formatting và Data Validation

Data Validation (Xác thực dữ liệu) trong Excel

Bạn hoàn toàn có thể sử dụng công thức để tạo xác thực tùy chỉnh với Data Validation. Ví dụ, nếu bạn muốn người dùng chỉ nhập số trong một ô hoặc một phạm vi ô thì bạn có thể làm điều đó bằng cách thêm công thức.

Nhấn vào Data Validation trong phần Data Tools section của tab Data.

Hộp thoại Data Validation sẽ hiển thị lên.

Trong tab Setting, chọn Custom trong Allow và nhập công thức vào hộp thoại Formula bên dưới. Ví dụ, chúng tôi đang sử dụng chức năng ISNUMBER. Hàm này sẽ kiểm tra xem đầu vào phải là số hay không và đưa ra thông báo lỗi nếu không phải.

Bạn cũng có thể sử dụng tùy chọn List trong Allow. Ở đây bạn có thể đưa ra một tham chiếu về phạm vi chứa danh sách của bạn.

Ví dụ, nếu bạn đã tạo một danh sách các mục trong phạm vi “ U1:U12 “ trong cùng một trang tính, bạn có thể cung cấp thêm tên trang tính trước tham chiếu phạm vi.

Lưu ý:

Trong tùy chọn List, phần lớn các công thức tra cứu và tham chiếu được sử dụng vì chúng trả về một số tham chiếu ô hoặc phạm vi làm đầu ra.

Xem thêm: Hướng dẫn cách sử dụng thiết lập Custom trong Data Validation

Conditional Formatting (Định dạng có điều kiện) trong Excel

Đây là một tính năng khá thú vị trong Excel được sử dụng để làm nổi bật các ô tương tự dựa trên một số tiêu chí và điều kiện nhất định.

Có một vài quy tắc xác định trước được xây dựng trong Excel có thể làm nổi bật các ô dựa trên giá trị của nó.

Tuy nhiên, nếu bạn có một điều kiện hoặc tiêu chí phức tạp thì bạn sẽ cần trợ giúp của các công thức. Bằng cách sử dụng các công thức tùy chỉnh của riêng bạn, bạn có thể nâng cao sức mạnh của Conditional Formatting.

Nhấp vào Conditional Formatting trong phần Styles của tab Home.

Nó sẽ hiển thị menu của Conditional Formatting.

Như bạn có thể thấy nó có 5 mục ở trên cùng được in đậm chỉ ra các quy tắc khác nhau được xây dựng trong Excel. Bạn có thể nhấp vào mũi tên phía bên phải mỗi danh mục để nhận danh sách các quy tắc và chọn quy tắc bạn cần.

Để sử dụng công thức, nhấp vào New Rule sẽ mở ra cửa sổ New Formatting Rules.

Chọn tùy chọn cuối cùng trong hộp danh sách Select a rule type.  Nó sẽ đưa ra hai lựa chọn.  Cái đầu tiên là Formula, bạn có thể nhập công thức của mình. Cái thứ hai là Format, bạn có thể cài đặt định dạng ưa thích của mình.  Hơn nữa, nó còn có một cửa sổ xem trước để xem nó trông như thế nào trước khi bạn hoàn thành.

Bây giờ chúng ta hãy thực hiện một ví dụ để hiểu rõ hơn.

Giả sử chúng ta có bảng dữ liệu khách hàng có một trường được gọi là thông tin và bạn muốn làm nổi bật các ô hoặc các mục không phải là số.  Để làm điều đó, chúng ta có thể sử dụng một hàm gọi là ISTEXT.

Chúng ta sẽ đặt công thức vào Formula với tham chiếu ô của ô đầu tiên.  Trong ví dụ của chúng tôi, nó là ô I2.  Chọn định dạng cần thiết và nhấn OK.

Lưu ý rằng giữ phần cột là tuyệt đối (được biểu thị bằng dấu $ trước tên cột) và hàng là tương đối vì công thức sẽ được áp dụng cho tất cả các hàng trong cột I.

Và đây là kết quả:

Xem thêm: Hướng dẫn cách xóa bỏ định dạng theo điều kiện Conditional formatting trong Excel

Formula hoạt động như thế nào?

Công thức Excel trong Conditional Formatting hoạt động bằng cách xác định xem kết quả của công thức là đúng hay sai? Vì vậy, bạn có thể sử dụng tất cả các hàm trả về đúng hoặc sai như các hàm logic hoặc hàm thông tin.  Bạn cũng có thể sử dụng các chức năng khác nhưng nó phải nằm trong chức năng logic hoặc thông tin.

Vừa rồi là một ví dụ về việc sử dụng một công thức đơn giản trong Conditional Formatting. 

Bây giờ chúng ta hãy xem một ví dụ phức tạp hơn.

Giả sử chúng ta có một bộ dữ liệu chứa thông tin chi tiết về nhân viên với hồ sơ ứng tuyển của họ và bạn muốn làm nổi bật những ngày rơi vào cuối tuần. Đây có thể là một tập dữ liệu rất lớn với hàng ngàn hàng và thực hiện thủ công sẽ cần rất nhiều công sức.  Vì vậy, thay vì định dạng thủ công, bạn có thể sử dụng một công thức trong Conditional Formatting để làm điều đó.

Đối với trường hợp này, chúng tôi sẽ sử dụng hai chức năng, WEEKDAY và OR.  Chúng tôi sẽ đưa công thức vào Formula hiển thị bên dưới.

Chọn định dạng và nhấp vào OK.

Nếu bạn thấy rằng nó không định dạng như mong đợi, đừng lo lắng, chỉ cần kiểm tra lại bằng cách nhấp vào Manage Rules.

Cửa sổ Manage Rules sẽ hiển thị lên.

Tại đây, hãy kiểm tra để đảm bảo rằng hộp Applies To đúng phạm vi đã chọn.  Nếu nó đúng thì nhấp vào Edit rule và kiểm tra công thức đang hiển thị. Đôi khi sau khi bạn đã nhập công thức, nó có thể bị lỗi vì lí do nào đó.

Cuối cùng và quan trọng nhất là kiểm tra xem các tham chiếu tuyệt đối và tương đối  được sử dụng cho các hàng và cột trong công thức đã chính xác chưa.

Lưu ý: Hãy nhớ rằng công thức được sử dụng trong Conditional Formatting có liên quan đến ô đầu tiên hoặc ô trên cùng bên trái của phạm vi được định dạng.

Các công thức cũng có thể được sử dụng trong Advance Filter nhưng không thể thực hiện trực tiếp trong hộp thoại.  Bạn có thể viết công thức ở bất cứ đâu trong Excel cùng với các tiêu đề cột và trỏ đến tham chiếu ô trong hộp thoại. Vì vậy, hãy sử dụng các công thức ở tất cả những nơi có thể sử dụng và nâng cao kỹ năng Excel của bạn lên một tầm cao mới.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ cần nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table… Khi kết hợp tốt các hàm và các công cụ với nhau, bạn sẽ thấy hiệu quả công việc trên Excel sẽ được cải thiện rất nhiều.

Kết luận

Hy vọng bài chia sẻ của Gitiho có thể giúp bạn có thêm kiến thức mới về cách viết công thức Excel. 

Chúc các bạn học tập hiệu quả!