Định dạng có điều kiện hiện là một phần không thể thiếu trong trải nghiệm Microsoft Excel. Nó giúp chúng ta xác định các giá trị cụ thể trong một nhóm dữ liệu lớn hoặc ngẫu nhiên. Chúng ta không chỉ nhận diện các giá trị nhất định mà còn có thể xác định giá trị thuộc phạm vi nào, giá trị chuỗi, số, lỗi, v.v. nhờ định dạng có điều kiện. Như tên gọi, đó là việc Excel định dạng ô dựa trên việc một điều kiện là đúng hay sai. Trong hướng dẫn này, chúng ta sẽ thảo luận về định dạng có điều kiện với công thức trong Excel.
Video hướng dẫn sử dụng Định dạng có điều kiện với Công thức trong Excel
Trong bài giảng video này:
- Bạn sẽ học cách định dạng ô bằng định dạng có điều kiện dựa trên công thức.
- Bạn sẽ tìm hiểu về hàm ISTEXT.
- Và bạn sẽ học lại cách sử dụng Tham chiếu tuyệt đối trong công thức.
Bạn có thể tải workbook dùng cho bài demo từ link tải bên dưới.
Cách sử dụng Định dạng có điều kiện với Công thức trong Excel
Yếu tố cốt lõi của việc dùng định dạng có điều kiện với công thức trong Excel khá đơn giản. Chọn tính năng định dạng có điều kiện, thay vì chọn tùy chọn có sẵn, hãy chọn New Rule (Quy tắc mới) rồi chọn loại "Use a formula to determine which cells to format" (Sử dụng công thức để xác định ô nào được định dạng).
Lưu ý rằng bằng "công thức" ở đây, chúng ta thường nhập một điều kiện vào hộp định dạng có điều kiện. Nếu điều kiện đúng, định dạng sẽ được áp dụng và ngược lại.
Dưới đây là minh họa chi tiết về định dạng có điều kiện với công thức trong Excel.
Các bước:
- Trước tiên, chọn phạm vi ô bạn muốn định dạng bằng công thức.
- Đi đến tab Home trên ribbon.
- Chọn Conditional Formatting từ nhóm Styles.
- Chọn New Rule từ menu thả xuống.
- Hộp New Formatting Rule sẽ hiện ra.
- Nhập công thức vào trường dưới Format values where this formula is true.
- Bạn cũng có thể chọn kiểu định dạng bằng cách nhấp Format.
- Sau khi hoàn tất các bước và kiểu định dạng, nhấp OK.
Ví dụ, chúng ta nhập công thức sau như hình sau khi làm theo tất cả các bước.
Phạm vi trên bảng tính là B3:B6. Nó sẽ trông như sau.
Ở đây, chúng ta nhập điều kiện giá trị ô lớn hơn 3. Vì vậy, trong vùng chọn nơi có giá trị lớn hơn 3, định dạng sẽ được áp dụng.
21 Ví dụ sử dụng Định dạng có điều kiện với Công thức trong Excel
Trong phần này, chúng ta sẽ demo các ví dụ khác nhau về việc dùng công thức trong tính năng định dạng có điều kiện trong Excel. Khả năng kết hợp tính năng này là rất lớn. Chúng tôi đã cố gắng bao gồm một số công thức chính mà chúng tôi nghĩ bạn sẽ thấy hữu ích. Nếu bạn chỉ cần một cái cụ thể, bạn có thể tìm chúng ở bảng nội dung phía trên.
1. Định dạng giá trị văn bản
Đầu tiên, xem tập dữ liệu này chứa cả giá trị số và chuỗi.
Với định dạng có điều kiện bằng công thức, chúng ta dễ dàng tách riêng các giá trị chuỗi trong Excel. Để làm điều đó, cần dùng hàm ISTEXT. Làm theo các bước sau:
Các bước:
- Chọn tất cả ô trong tập dữ liệu (trừ tiêu đề).
- Đi đến tab Home trên ribbon.
- Chọn Conditional Formatting từ nhóm Styles.
- Chọn New Rule từ menu thả xuống.
- Trong hộp Formatting Rule, chọn tùy chọn Use a formula to determine which cells to format dưới Select a Rule Type.
- Nhập công thức sau vào Format values where this formula is true.
=ISTEXT(B5)
- Chọn kiểu định dạng ưa thích.
- Nhấp OK.
Bạn sẽ thấy Excel định dạng tất cả văn bản trong tập dữ liệu sau khi dùng công thức này.
2. Làm nổi bật ô bằng giá trị của ô khác
Xem ví dụ khác về khớp giá trị.
Có một bộ dữ liệu lớn. Chúng ta sẽ định dạng những ô khớp với giá trị ô E5.
Các bước:
- Chọn tất cả ô trong tập dữ liệu (trừ tiêu đề).
- Đi đến tab Home, chọn Conditional Formatting từ Styles.
- Chọn New Rule.
- Trong Formatting Rule, chọn Use a formula to determine which cells to format.
- Nhập công thức:
=B5=$E$5
- Chọn kiểu định dạng.
- Nhấp OK.
Đây là cách làm nổi bật ô bằng giá trị ô khác dùng định dạng có điều kiện với công thức.
3. Định dạng có điều kiện dựa trên ô khác
Bây giờ chúng ta sẽ định dạng tập dữ liệu dựa trên lớn/nhỏ hơn giá trị ô khác.
Thực hiện trên tập dữ liệu sau:
Định dạng phạm vi C5:C9 dựa trên giá trị ô E5.
Các bước:
- Chọn ô dữ liệu (trừ tiêu đề).
- Tab Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập công thức:
=C5>$E$5
- Chọn định dạng.
- Nhấp OK.
- Lặp lại cho giá trị nhỏ hơn để có kết quả như sau.
4. Định dạng có điều kiện dùng công thức IF trong Excel
Cách dùng hàm IF trong công thức định dạng có điều kiện. Không dùng trực tiếp IF trong hộp định dạng, nhưng có thể dùng IF để lấy giá trị rồi định dạng.
Tập dữ liệu ví dụ:
Các bước:
- Chọn ô E5 nhập công thức:
=IF(D5>C5,"Profit","Loss") - Nhấn Enter.
- Kéo thanh fill handle xuống cuối cột.
- Chọn tất cả ô dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập công thức:
=E5="Profit" - Chọn định dạng → OK.
5. Làm nổi bật ô với nhiều điều kiện
Quay lại tập dữ liệu đầu tiên.
Làm nổi bật ô bằng 5, 6 hoặc chứa "cat". Cần hàm OR.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=OR(B5=5,B5=6,B5="cat") - Chọn định dạng → OK.
6. Làm nổi bật hàng trùng lặp
Làm nổi bật cả hàng khi hàng đó trùng với hàng khác. Cần COUNTIF và CONCATENATE.
Tập dữ liệu mẫu:
Hàng 3 và 6 trùng hoàn toàn. Các bước:
Các bước:
- Ô F5 nhập:
=CONCATENATE(B5,C5,D5,E5) - Nhấn Enter → kéo fill handle.
- Chọn phạm vi B5:B10 → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=COUNTIF($F$5:$F$10,$F5)>1 - Chọn định dạng → OK.
- Click chuột phải tiêu đề cột F → Hide.
7. Làm nổi bật giá trị ô chứa công thức
Tập dữ liệu có công thức:
Dùng ISFORMULA để làm nổi bật.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=ISFORMULA(B5) - Chọn định dạng → OK.
8. Làm nổi bật doanh số từ khu vực cụ thể
Làm nổi bật doanh số thuộc khu vực Arizona.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=$D5="Arizona" - Chọn định dạng → OK.
9. Làm nổi bật khác biệt cột
Làm nổi bật hàng có cột khác nhau so với hàng liền kề.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=$B5<>$C5 - Chọn định dạng → OK.
10. Dùng công thức làm nổi bật giá trị thiếu
Làm nổi bật giá trị thiếu trong Danh sách 2 so với Danh sách 1.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=COUNTIF($D$5:$D$10,$B5)=0 - Chọn định dạng → OK.
11. Tạo ô tìm kiếm đơn giản để làm nổi bật ô
Nhập giá trị vào ô E4, Excel làm nổi bật các ô khớp trong phạm vi. Cần ISNUMBER và SEARCH.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=ISNUMBER(SEARCH($E$4,B5)) - Chọn định dạng → OK.
Các từ chứa "game" sẽ được đánh dấu.
Thay E4 thành "health" → làm nổi bật thay đổi.
12. Làm nổi bật giá trị nhỏ hơn trung bình
Làm nổi bật giá trị cột E nhỏ hơn trung bình. Cần AVERAGE.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=E5<AVERAGE($E$5:$E$10) - Chọn định dạng → OK.
13. Làm nổi bật giá trị lớn hơn trung bình
Tương tự, làm nổi bật giá trị lớn hơn trung bình cột E.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=E5>AVERAGE($E$5:$E$10) - Chọn định dạng → OK.
14. Tìm giá trị gần nhất (kể cả khớp chính xác)
Tìm giá trị gần nhất với số trong ô C13. Dùng MIN, ABS, OR.
Các bước:
- Ô C14 nhập:
=MIN(ABS(B5:D11-(C13)))→ Enter.
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=OR(B5=$C$13-$C$14,B5=$C$13+$C$14) - Chọn định dạng → OK.
15. Tìm 3 giá trị lớn nhất
Dùng LARGE để tìm top 3.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=B5>=LARGE($B$5:$D$11,3) - Chọn định dạng → OK.
16. Tìm 3 giá trị nhỏ nhất
Dùng SMALL để tìm bottom 3.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=B5<=SMALL($B$5:$D$11,3) - Chọn định dạng → OK.
17. Hiển thị nhiệt độ bằng thang màu
Thay đổi màu dựa trên nhiệt độ hiện tại ô E5. Dùng IF và AND.
Các bước:
- Ô C6 nhập:
=IF(B6=$E$5,"",IF(AND(B6<$E$5,$E$5<B5),"",B6))→ Enter.
- Kéo fill handle xuống cột.
- Conditional Formatting → Color Scales → chọn thang màu.
- Thay E5 → thang màu thay đổi tương ứng.
18. Làm nổi bật hàng xen kẽ bằng Định dạng có điều kiện
Làm nổi bật hàng cách nhau. Dùng INT và MOD.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=INT(MOD(ROW(),2)) - Chọn định dạng → OK.
19. Làm nổi bật ô chứa lỗi
Dùng ISERROR để phát hiện lỗi trong dữ liệu lớn.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=ISERROR(B5) - Chọn định dạng → OK.
20. Tạo danh sách kiểm tra (Checklist) với Định dạng có điều kiện
Tạo checklist: khi tick checkbox, task đổi định dạng (gạch ngang). Dùng giá trị boolean của checkbox.
Các bước:
- Tab Developer → Insert → Check box (Form Control).
- Đặt checkbox → chuột phải → Format Control.
- Tab Control → ô liên kết C5 → OK.
- Lặp lại cho các task khác.
- Khi tick → TRUE/FALSE xuất hiện → dùng màu font trắng để ẩn.
- Chọn phạm vi task → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=C5=TRUE - Chọn định dạng (gạch ngang) → OK.
21. Làm nổi bật cuối tuần trong tuần lịch
Dùng OR và WEEKDAY để làm nổi bật cuối tuần.
Các bước:
- Chọn dữ liệu → Home → Conditional Formatting → New Rule.
- Chọn Use a formula to determine which cells to format.
- Nhập:
=OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7) - Chọn định dạng → OK.
Kết luận
Đây là các phương pháp và ví dụ dùng công thức cho định dạng có điều kiện trong Excel. Hy vọng bạn đã nắm được ý tưởng và có thể áp dụng. Nếu có câu hỏi hoặc góp ý, hãy để lại bình luận.
Để xem thêm hướng dẫn tương tự, truy cập Exceldemy.com.