Hướng dẫn cách đánh dấu hàng dựa trên giá trị ô trong Excel

Trong bài viết này Gitiho sẽ hướng dẫn các bạn cách sử dụng định dạng có điều kiện để đánh dấu các hàng dựa trên nhiều tiêu chí trong các ô của bảng dữ liệu Excel.

Định dạng có điều kiện cho phép bạn định dạng một ô (hoặc một hàng) dựa trên giá trị trong đó. Nhưng đôi khi, thay vì chỉ đánh dấu ô bạn có thể muốn đánh dấu toàn bộ hàng (hoặc cột) dựa trên giá trị trong một ô. 

Để giúp các bạnh hiểu rõ hơn về cách sử dụng định dạng có điều kiện linh hoạt trong Excel, Gitiho sẽ hướng dẫn các bạn thông quá các ví dụ cụ thể. 

Đánh dấu hàng dựa trên tiêu chí văn bản

Giả sử bạn có một bảng dữ liệu như được hiển thị bên dưới và muốn đánh dấu tất cả các dòng có tên của cột đại diện bán hàng (Sales Rep) là Bob.

Dưới đây là các bước để thực hiện việc này:

Bước 1: Chọn toàn bộ bảng dữ liệu (A2:F17 trong ví dụ này).

Bước 2: Truy cập vào thẻ Home.

Bước 3: Trong nhóm Styles, bấm nút Conditional Formattin.

Bước 4: Trong menu xổ xuống chọn tùy chọn New Rules

Bước 5: Trong hộp thoại New Formatting Rule, kích tùy chọn ‘Use a formula to determine which cells to format’.

Bước 6: Trong trường công thức, hãy nhập công thức sau:  =$C2=”Bob”

Bước 7: Bấm nút Format.

Bước 8: Trong hộp thoại mở ra, hãy chọn màu mà bạn muốn đánh dấu cho hàng.

Bước 9: Bấm nút OK. Điều này sẽ đánh dấu tất cả các hàng của cột Sales có tên là Bob.

Cách hoạt động của hàm trên như thế nào?

Định dạng có điều kiện sẽ kiểm tra từng ô cho điều kiện bạn chỉ định, đó là =$C2=”Bob”

Vì vậy, khi phân tích từng ô trong hàng A2, nó sẽ kiểm tra xem ô C2 có tên Bob hay không. Nếu có, ô đó sẽ được đánh dấu, nếu không sẽ giữ nguyên.

Lưu ý: Ở đây sử dụng ký hiệu $ trước cột C2. Bằng cách này sẽ khóa luôn cột C. Vì vậy, ngay cả khi ô A2 đang được kiểm tra công thức, nó sẽ kiểm tra C2 và khi A3 được kiểm tra điều kiện, nó sẽ kiểm tra C3.

Điều này cho phép đánh dấu toàn bộ hàng bằng định dạng có điều kiện.

Đọc thêm:  Hướng dẫn cách sử dụng định dạng có điều kiện trong Excel 2010, 2013 và 2016

Đánh dấu hàng dựa trên tiêu chí số

Trong ví dụ trên, chúng ta đã thấy cách kiểm tra tên và đánh dấu toàn bộ hàng. Chúng ta có thể sử dụng cùng một phương pháp để kiểm tra các giá trị số và đánh dấu các hàng dựa trên một điều kiện.

Giả sử bạn có bảng dữ liệu như bên dưới và muốn đánh dấu tất cả các hàng có số lượng lớn hơn 15.

Dưới đây là các bước để thực hiện việc này:

Bước 1: Chọn toàn bộ bảng dữ liệu (A2:F17 trong ví dụ này).

Bước 2: Truy cập vào thẻ Home.

Bước 3: Trong nhóm Styles, bấm nút Conditional Formattin.

Bước 4: Trong menu xổ xuống chọn tùy chọn New Rules

Bước 5: Trong hộp thoại New Formatting Rule, kích tùy chọn ‘Use a formula to determine which cells to format’. 

Bước 6: Trong trường công thức, hãy nhập công thức sau:  =$D2>=15

Bước 7: Bấm nút Format, trong hộp thoại mở ra, hãy chọn màu mà bạn muốn đánh dấu cho hàng.

Bước 8: Bấm nút OK và điều này sẽ đánh dấu nổi bật tất cả các hàng có số lượng lớn hơn hoặc bằng 15.

Tương tự, chúng ta cũng có thể sử dụng điều này để có tiêu chí cho ngày.

Ví dụ: nếu bạn muốn đánh dấu tất cả các hàng có ngày sau ngày 10 tháng 7 năm 2018, bạn có thể sử dụng công thức ngày dưới đây :

=$A2>DATE(2018,7,10)

Đánh dấu các hàng dựa trên nhiều tiêu chí (AND/OR)

Bạn cũng có thể sử dụng nhiều tiêu chí để đánh dấu các hàng bằng cách sử dụng định dạng có điều kiện.

Ví dụ: Nếu bạn muốn đánh dấu tất cả các hàng của cột Sales Rep có tên là 'Bob' và có số lượng lớn hơn 10, bạn có thể làm điều đó bằng cách sử dụng các bước sau:

Bước 1: Chọn toàn bộ bảng dữ liệu (A2:F17 trong ví dụ này).

Bước 2: Truy cập vào thẻ Home.

Bước 3: Trong nhóm Styles, bấm nút Conditional Formattin.

Bước 4: Trong menu xổ xuống chọn tùy chọn New Rules

Bước 5: Trong hộp thoại New Formatting Rule, kích tùy chọn ‘Use a formula to determine which cells to format’. 

Bước 6: Trong trường công thức, hãy nhập công thức sau:  =AND($C2=”Bob”,$D2>10)

Bước 7: Bấm nút Format, trong hộp thoại mở ra, hãy chọn màu mà bạn muốn đánh dấu cho hàng.

Bước 8: Bấm nút OK. Như vậy những hàng được đánh dấu chỉ khi cả hai điều kiện được đáp ứng (điều này được thực hiện bằng cách sử dụng hàm  AND).

Tương tự, bạn cũng có thể sử dụng hàm OR. Ví dụ: Nếu bạn muốn đánh dấu các hàng của cột Sales Rep có tên là 'Bob' hoặc số lượng lớn hơn 15, bạn có thể sử dụng công thức dưới đây:

=OR($C2="Bob",$D2>15)

Đọc thêm: Hướng dẫn chi tiết cách sử dụng các hàm And, hàm Or, hàm XOR và hàm Not trong Excel

Đánh dấu các hàng có màu khác nhau dựa trên nhiều điều kiện

Đôi khi, bạn có thể muốn đánh dấu các hàng bằng màu dựa trên nhiều điều kiện. Ví dụ: Có thể bạn muốn đánh dấu tất cả các hàng có số lượng lớn hơn 20 bằng màu xanh lá cây và hàng có số lượng lớn hơn 15 (nhưng ít hơn 20) bằng màu cam.

Để làm điều này, bạn cần tạo hai quy tắc định dạng có điều kiện và đặt mức độ ưu tiên. Dưới đây là các bước để thực hiện việc này:

Bước 1: Chọn toàn bộ bảng dữ liệu (A2:F17 trong ví dụ này).

Bước 2: Truy cập vào thẻ Home.

Bước 3: Trong nhóm Styles, bấm nút Conditional Formattin.

Bước 4: Trong menu xổ xuống chọn tùy chọn New Rules

Bước 5: Trong hộp thoại New Formatting Rule, kích tùy chọn ‘Use a formula to determine which cells to format’. 

Bước 6: Trong trường công thức, hãy nhập công thức sau:  =$D2>15

Bước 7: Bấm nút Format, trong hộp thoại mở ra, hãy chọn màu Orange.

Bước 8: Bấm nút OK.

Bước 9: Cũng trong hộp thoại Conditional Formatting Rules Manager, bạn bấm tiếp nút New Rule.

Bước 10: Trong hộp thoại New Formatting Rule, kích tùy chọn ‘Use a formula to determine which cells to format’. 

Bước 11: Trong trường công thức, hãy nhập công thức sau:  =$D2>20

Bước 12: Bấm nút Format, trong hộp thoại mở ra, hãy chọn màu Green.

Bước 13: Bấm nút OK. Các bước trên sẽ đánh dấu tất cả các hàng có số lượng lớn hơn 20 màu xanh lá cây và những hàng có số lượng lớn hơn 15 (nhưng nhỏ hơn 20 màu cam).

Thứ tự các quy tắc trên là thế nào?

Khi sử dụng nhiều điều kiện, điều quan trọng là phải đảm bảo thứ tự của các điều kiện là chính xác.

Trong ví dụ trên, điều kiện màu xanh lá cây nằm trên điều kiện màu Cam.

Nếu là theo chiều ngược lại, tất cả các hàng sẽ chỉ có màu cam. Tại sao lại như vậy? 

Bởi vì một hàng có số lượng lớn hơn 20 (giả sử 23) thỏa mãn cả hai điều kiện của chúng ta (=$D2>15 và =$D2>20). Và vì điều kiện màu cam ở trên cùng nên nó được ưu tiên.

Bạn có thể thay đổi thứ tự ưu tiên của các điều kiện bằng cách sử dụng các nút Move Up/Down.

Đánh dấu các hàng có ô trống bất kỳ

Nếu bạn muốn đánh dấu tất cả các hàng có bất kỳ ô nào, bạn cần kiểm tra từng ô bằng cách sử dụng định dạng có điều kiện. Dưới đây là các bước để thực hiện việc này:

Bước 1: Chọn toàn bộ bảng dữ liệu (A2:F17 trong ví dụ này).

Bước 2: Truy cập vào thẻ Home.

Bước 3: Trong nhóm Styles, bấm nút Conditional Formattin.

Bước 4: Trong menu xổ xuống chọn tùy chọn New Rules

Bước 5: Trong hộp thoại New Formatting Rule, kích tùy chọn ‘Use a formula to determine which cells to format’. 

Bước 6: Trong trường công thức, hãy nhập công thức sau: =COUNTIF($A2:$F2,””)>0

Bước 7: Bấm nút Format, trong hộp thoại mở ra, hãy chọn màu Orange.

Bước 8: Bấm nút OK.

Công thức trên sẽ đếm số ô trống. Nếu kết quả lớn hơn 0, có nghĩa là có các ô trống trong hàng đó. Nếu bất kỳ ô nào trống, nó sẽ đánh dấu toàn bộ hàng.

Đánh dấu hàng dựa trên lựa chọn thả xuống

Trong các ví dụ trên, tất cả các điều kiện đã được chỉ định bằng hộp thoại định dạng có điều kiện.

Trong phần này Gitiho sẽ chỉ cho bạn cách làm cho lựa chọn trở thành  động (để bạn có thể nhập điều kiện trong một ô trong Excel và nó sẽ tự động đánh dấu các hàng dựa trên điều kiện bạn chọn).

Dưới đây là một ví dụ để bạn có thể chọn một tên từ menu thả xuống và tất cả các hàng có tên đó sẽ được đánh dấu:

Bước 1: Tạo danh sách thả xuống trong ô A2. Trong ví dụ này sử dụng tên của cột Sales rep để tạo danh sách thả xuống. 

Đọc thêm: Hướng dẫn cách tạo danh sách thả xuống trong Excel

Bước 2: Chọn toàn bộ tập dữ liệu (C2:H17 trong ví dụ này).

Bước 2: Truy cập vào thẻ Home.

Bước 3: Trong nhóm Styles, bấm nút Conditional Formattin.

Bước 4: Trong menu xổ xuống chọn tùy chọn New Rules

Bước 5: Trong hộp thoại New Formatting Rule, kích tùy chọn ‘Use a formula to determine which cells to format’. 

Bước 6: Trong trường công thức, hãy nhập công thức sau: =$E2=$A$2

Bước 7: Bấm nút Format, trong hộp thoại mở ra, hãy chọn màu Orange.

Bước 8: Bấm nút OK.

Bây giờ khi bạn chọn bất kỳ tên nào từ menu thả xuống, nó sẽ tự động đánh dấu các hàng có tên giống với tên mà bạn đã chọn từ menu thả xuống.

Để có thể biết thêm nhiều hàm nâng cao trong Excel cũng như sử dụng tốt hơn các công cụ của Excel, các bạn hãy tham gia ngay khóa học EXG01 – Học Microsoft Excel từ cơ bản tới nâng cao dành cho người đi làm của Gitiho.com. Đây là khóa học chi tiết và đầy đủ nhất, giúp bạn phát triển kiến thức một cách đầy đủ, có hệ thống. Đặc biệt khóa học này không giới hạn thời gian học tập, nên bạn có thể học thoải mái và xem lại bất cứ khi nào.