Cần học cách tô màu dòng xen kẽ dựa trên giá trị ô trong Excel? Khi làm việc trên bảng dữ liệu lớn, cần xen kẽ màu dòng để trực quan hóa tập dữ liệu tốt hơn. Nếu tìm thủ thuật độc đáo, bạn đến đúng nơi. Ở đây, chúng tôi đưa bạn qua 10 phương pháp dễ dàng, tiện lợi xen kẽ màu dòng dựa trên giá trị ô trong Excel.
Bạn có thể tải sổ làm việc Excel sau để hiểu rõ và tự thực hành.
10 cách tô màu dòng xen kẽ dựa trên giá trị ô trong Excel
Để minh họa, xem xét báo cáo Doanh số hàng ngày - Phần trái cây của cửa hàng tạp hóa. Tập dữ liệu chứa tên Sản phẩm cột B, tên Khách hàng và Số lượng cột C và D.
Bây giờ, chỉ cách xen kẽ màu dòng dựa trên giá trị trong Excel dùng tập dữ liệu trên. Trong trường hợp này, xem giá trị ô cột B làm giá trị cơ sở. Dựa trên giá trị này, toàn bộ dòng sẽ được tô màu.
1. Tô màu dòng xen kẽ thủ công dựa trên giá trị ô trong Excel
Ở đây, xen kẽ màu dòng bằng cách chọn các dòng rồi chọn màu nền mong muốn. Xem các bước.
📌 Các bước
- Vì các dòng xen kẽ không liền kề, chọn chúng bằng nhấn giữ CTRL.
- Sau chọn, vào tab Home.
- Sau đó, chọn thả xuống Fill Color nhóm Font.
- Sau đó, chọn Green, Accent 6, Lighter 60% từ màu có sẵn.
- Theo cách này, tô màu dòng xen kẽ dựa trên giá trị ô trong Excel.
Đọc thêm: Cách tô màu dòng xen kẽ cho ô hợp nhất trong Excel
2. Dùng tùy chọn Format as Table trong Excel
Để tô dòng xen kẽ bất kỳ màu, dùng Format as Table dễ dàng. Các quy trình:
📌 Các bước
- Đầu tiên, chọn tập dữ liệu. Ở đây, chọn ô phạm vi B4:D14.
- Thứ hai, vào tab Home.
- Sau đó, chọn thả xuống Format as Table nhóm Styles.
- Sau đó, chọn Light Green, Table Style Light 7 từ tùy chọn.
- Ngay lập tức, hộp thoại Create Table hiện ra.
- Lúc này, đảm bảo tích My table has headers.
- Sau đó, nhấp OK.
- Sau đó, nhận bảng sau với màu dòng xen kẽ.
- Bây giờ, chọn toàn bảng. Ở đây, chọn ô B4:D14.
- Sau đó, vào tab Table Design.
- Tiếp theo, chọn Convert to Range nhóm Tools.
- Lúc này, nhấp Yes trong hộp cảnh báo.
- Cuối cùng, nhận lại phạm vi dữ liệu trước nhưng có màu dòng xen kẽ dựa trên giá trị ô.
Đọc thêm: Cách xen kẽ màu dòng trong Excel không dùng Table (5 phương pháp)
3. Tạo Bảng tô màu dòng xen kẽ dựa trên giá trị ô trong Excel
Dùng tùy chọn Table xen kẽ màu dòng dựa trên giá trị ô tập dữ liệu sau. Làm theo các bước.
📌 Các bước
- Trước hết, chọn ô B4:D14.
- Sau đó, vào tab Insert.
- Sau đó, chọn Table nhóm Tables.
- Đột ngột, hộp thoại Create Table bật lên.
- Sau đó, nhấp OK.
- Vậy, chuyển phạm vi dữ liệu thành bảng sau với màu dòng xen kẽ.
- Lại, để chuyển bảng thành phạm vi làm theo Phương pháp 2.
Đọc thêm: Cách xen kẽ màu dòng dựa trên nhóm trong Excel (6 phương pháp)
4. Dùng tùy chọn Format Painter
Phần này dùng Format Painter xen kẽ màu dòng dựa trên giá trị tập dữ liệu sau. Xem quy trình.
📌 Các bước
- Sau chọn dòng hai phạm vi, vào tab Home.
- Sau đó, chọn thả xuống Fill Color nhóm Font.
- Sau đó, chọn Green, Accent 6, Lighter 60% từ màu có sẵn.
Vậy đã tạo định dạng chứa một dòng không màu và một dòng có màu. Bây giờ, dùng Format Painter sao chép định dạng này cho dòng còn lại.
- Đầu tiên, chọn hai dòng đầu là dòng 5 và 6.
- Thứ hai, chọn Format Painter nhóm Clipboard.
- Sau đó dấu Format Painter hiện ra, kéo xuống và sang phải.
Kết quả, thấy phạm vi dữ liệu có dòng dải.
5. Áp dụng hàm IF và MOD
Cách này dùng IF và MOD lấy nhóm nhị phân tập dữ liệu. Các bước:
📌 Các bước
- Ban đầu, chọn ô E4 ghi Zero (0).
0
- Sau đó, nhấn ENTER.
- Sau đó, chọn ô E5 ghi công thức sau.
=MOD(IF(B5=B4,E4,E4+1),2) Phân tích công thứcIF(B5=B4, E4, E4+1): Hàm IF kiểm tra giá trị ô B5 với B4. Nếu khớp, trả về giá trị ô E4. Ngược lại, cộng 1 với giá trị ô E4 trả về đó.
MOD(IF(B5=B4, E4, E4+1),2): Hàm MOD chia kết quả hàm IF cho 2 hiện dư.
- Chỉ cần nhấn ENTER.
- Sau đó, nhấp đúp Fill Handle sao chép công thức đến ô E14.
- Kết quả, các ô còn lại cột E có kết quả.
- Bây giờ, chọn phạm vi B5:E14.
- Sau đó, vào tab Home
- Sau đó, nhấp mũi tên thả xuống Conditional Formatting nhóm Styles.
- Sau đó, chọn New Rule từ danh sách thả xuống.
- Kết quả, hộp thoại nhỏ New Formatting Rule hiện ra.
- Sau đó, chọn Use a formula to determine which cells to format phần Select a Rule Type.
- Sau đó, ghi công thức sau vào ô trống dưới Format values where the formula is true.
=$E5=1
- Sau đó, nhấp Format.
- Hộp thoại Format Cells hiện ra.
- Tab Fill, chọn màu theo ý để dòng dễ phân biệt. Chọn Green, Accent 6, Lighter 60%.
- Cuối cùng, nhấp OK.
- Lại nhấp OK đóng New Formatting Rule.
- Theo cách này, tô màu dòng xen kẽ dựa trên giá trị trong Excel.
- Sau đó, nhấp chuột phải Cột E.
- Sau đó, chọn Hide menu ngữ cảnh.
- Kết quả, cột thêm ẩn. Kết quả như ảnh dưới.
6. Kết hợp IF, MOD, và ROW
Quy trình này IF, MOD, ROW giúp nhóm nhị phân tập dữ liệu. Khám phá từng bước.
📌 Các bước
- Chủ yếu, chọn ô E4 ghi Zero (0).
0
- Sau đó, nhấn ENTER.
- Sau đó, chọn ô E5 ghi công thức sau.
=MOD(IF(ROW()=2,0,IF(B5=B4,E4,E4+1)),2) Phân tích công thứcROW(): Hàm ROW trả về số hàng. Ở đây giá trị 5.
IF(B5=B4, E4, E4+1): Hàm IF kiểm tra ô B5 với B4. Nếu khớp, trả về ô E4. Ngược lại, cộng 1 với ô E4 trả về đó.
IF(ROW()=2,0,IF(B5=B4,E4, E4+1)): Công thức này, hàm IF kiểm tra số hàng có bằng 2. Nếu đúng, trả về 0. Nếu sai, trả về kết quả hàm IF thứ hai.
MOD(IF(ROW()=2,0,IF(B5=B4,E4, E4+1)),2): Hàm MOD chia kết quả hàm IF cho 2 hiện dư.
- Chỉ cần nhấn ENTER.
- Bây giờ, chọn phạm vi B5:E14.
- Sau đó, vào tab Home
- Sau đó, nhấp mũi tên thả xuống Conditional Formatting nhóm Styles.
- Sau đó, chọn New Rules từ danh sách thả xuống.
- Kết quả, hộp thoại New Formatting Rule hiện ra.
- Sau đó, chọn Use a formula to determine which cells to format phần Select a Rule Type.
- Sau đó, ghi công thức sau vào ô trống dưới Format values where the formula is true.
=$E5=1
- Sau đó, nhấp Format.
- Hộp thoại Format Cells hiện ra.
- Tab Fill, chọn màu theo ý. Chọn Green, Accent 6, Lighter 60%.
- Cuối cùng, nhấp OK.
- Lại nhấp OK đóng New Formatting Rule.
- Theo cách này, tô màu dòng xen kẽ dựa trên giá trị trong Excel.
- Ngoài ra, làm theo Phương pháp 5 ẩn cột thêm.
7. Dùng AND, IF, LEN, và MOD
Phương pháp này dùng hàm IF lấy nhóm số. Bên cạnh đó, dùng AND, LEN, MOD trong quy tắc định dạng có điều kiện xen kẽ màu dòng. Các bước:
📌 Các bước
- Đầu tiên, chọn ô E4 ghi Zero (0).
0
- Sau đó, nhấn ENTER.
- Sau đó, chọn ô E5 ghi công thức sau.
=IF(B5=B4,E4,E4+1) Phân tích công thứcIF(B5=B4, E4, E4+1): Hàm IF kiểm tra ô B5 với B4. Nếu khớp, trả về ô E4. Ngược lại, cộng 1 với ô E4 trả về đó.
- Chỉ cần nhấn ENTER.
- Bây giờ, chọn phạm vi B5:E14.
- Sau đó, vào tab Home
- Sau đó, nhấp mũi tên thả xuống Conditional Formatting nhóm Styles.
- Sau đó, chọn New Rules từ danh sách thả xuống.
- Kết quả, hộp thoại New Formatting Rule hiện ra.
- Sau đó, chọn Use a formula to determine which cells to format phần Select a Rule Type.
- Sau đó, ghi công thức sau vào ô trống dưới Format values where the formula is true.
=AND(LEN($B5)>0,MOD($E5,2)=0) Phân tích công thứcLEN($B5): Hàm LEN đếm độ dài giá trị ô. Ở đây giá trị 5.
MOD($E5,2): Hàm chia giá trị ô E5 cho 2 hiện dư. Kết quả 1.
AND(LEN($B5)>0, MOD($E5,2)=0): Công thức này, hàm AND kiểm tra giá trị hàm LEN lớn hơn 5 và kết quả hàm MOD bằng 0. Nếu cả hai True, dòng hiện màu chọn. Ngược lại, bình thường.
- Sau đó, nhấp Format.
- Hộp thoại Format Cells hiện ra.
- Tab Fill, chọn màu theo ý. Chọn Green, Accent 6, Lighter 60%.
- Cuối cùng, nhấp OK.
- Lại nhấp OK đóng New Formatting Rule.
- Theo cách này, tô màu dòng xen kẽ dựa trên giá trị ô trong Excel.
8. Áp dụng hàm ISODD tô màu dòng xen kẽ dựa trên giá trị ô trong Excel
Thủ tục này dùng IF và SUM trước lấy nhóm số. Sau đó, áp dụng hàm ISODD trong Conditional Formatting xen kẽ màu dòng. Quy trình:
📌 Các bước
- Đầu tiên, chọn ô E4 ghi Zero (0).
0
- Sau đó, nhấn ENTER.
- Sau đó, chọn ô E5 ghi công thức sau.
=IF(B4=B5,E4,SUM(E4,1)) Phân tích công thứcSUM(E4,1): Hàm SUM cộng 1 với ô E4. Với ô này, hàm trả 1.
IF(B4=B5,E4,SUM(E4,1)): Hàm IF kiểm tra ô B5 với B4. Nếu khớp, trả về ô E4. Ngược lại, sai, trả về kết quả hàm SUM.
- Chỉ cần nhấn ENTER.
- Bây giờ, chọn phạm vi B5:E14.
- Sau đó, vào tab Home
- Sau đó, nhấp mũi tên thả xuống Conditional Formatting nhóm Styles.
- Sau đó, chọn New Rules từ danh sách thả xuống.
- Kết quả, hộp thoại New Formatting Rule hiện ra.
- Sau đó, chọn Use a formula to determine which cells to format phần Select a Rule Type.
- Sau đó, ghi công thức sau vào ô trống dưới Format values where the formula is true.
=ISODD($E5)
Sau đó, ISODD xác định giá trị ô tương ứng lẻ hay không, nếu lẻ trả TRUE. Nếu TRUE dòng hiện màu chọn. Ngược lại, bình thường.
- Sau đó, nhấp Format.
- Hộp thoại Format Cells hiện ra.
- Tab Fill, chọn màu theo ý. Chọn Green, Accent 6, Lighter 60%.
- Cuối cùng, nhấp OK.
- Lại nhấp OK đóng New Formatting Rule.
- Theo cách này, tô màu dòng xen kẽ dựa trên giá trị ô trong Excel.
9. Liên kết ISEVEN với tùy chọn Filter
Phần này dùng hàm ISEVEN và Filter xen kẽ màu dòng dựa trên giá trị. Khám phá từng bước.
📌 Các bước
- Cho tiện, thêm cột Helper.
- Thứ hai, chọn ô E5 ghi công thức sau.
=ISEVEN(ROW())
Hàm ROW trả về số hàng. Sau đó, ISEVEN xác định số hàng chẵn hay không, nếu chẵn trả TRUE ngược lại FALSE.
- Chỉ cần nhấn ENTER.
- Lúc này, chọn tập dữ liệu. Ở đây, chọn ô B4:E14.
- Sau đó, vào tab Home.
- Sau đó, chọn thả xuống Sort & Filter nhóm Editing.
- Sau đó, chọn Filter.
- Kết quả, nhận bảng lọc.
- Hiện tại, nhấp biểu tượng thả xuống cột Helper.
- Sau đó, bỏ chọn TRUE ẩn nó và nhấn OK.
- Kết quả, hàng TRUE bị ẩn.
- Tiếp theo, chọn hàng không ẩn trong tập dữ liệu.
- Bây giờ, vào tab Home.
- Sau đó, chọn thả xuống Fill Color nhóm Font.
- Sau đó, chọn Green, Accent 6, Lighter 60% từ màu có sẵn.
- Cuối cùng, nhận dòng được highlight.
- Lại nhấp biểu tượng thả xuống cột Helper.
- Lúc này, chọn Clear Filter From “Helper”.
- Sau đó, nhấp nút OK.
- Kết quả, nhận màu dòng xen kẽ mong muốn dựa trên giá trị ô.
- Lại chọn toàn bộ tập dữ liệu kể cả hàng tiêu đề.
- Sau đó, vào tab Home.
- Tiếp theo, chọn thả xuống Sort & Filter nhóm Editing.
- Cuối cùng, bỏ chọn Filter.
Vậy, bây giờ có phạm vi dữ liệu bình thường có màu dòng xen kẽ.
10. Dùng mã VBA tô màu dòng xen kẽ dựa trên giá trị ô trong Excel
Dùng VBA luôn là lựa chọn thay thế tuyệt vời. Làm theo các bước để giải quyết theo cách này.
📌 Các bước
- Đầu tiên, nhấn ALT+F11.
- Đột ngột, cửa sổ Microsoft Visual Basic for Applications mở ra.
- Sau đó, vào tab Insert.
- Sau đó, chọn Module từ tùy chọn.
- Mở module mã cần dán mã dưới.
Sub Alternate_Row_Color() Dim y As Integer For y = 1 To Selection.Rows.Count If y Mod 2 = 0 Then Selection.Rows(y).Interior.Color = RGB(198, 224, 180) End If Next End Sub
Ở đây, khai báo y là Integer và vòng FOR chạy cho mỗi hàng tập dữ liệu, câu IF đảm bảo nếu số hàng chia hết 2 hoặc chẵn thì chỉ tô màu dòng đó.
- Bây giờ, quay lại trang tính chọn tập dữ liệu.
- Sau đó, vào tab Developer.
- Tiếp theo, chọn Macros nhóm Code.
- Đột ngột, Macro wizard mở.
- Sau đó, chọn macro Alternate_Row_Color(tạo ở bước trước).
- Cuối cùng, nhấp Run.
Cuối cùng, tô màu dòng xen kẽ màu mong muốn. Một điều chú ý: Excel coi hàng đầu tiên chọn là Hàng 1 nên lẻ (thực tế Hàng 5) và hàng sau là Hàng 2 nên tô màu vì chẵn (thực tế Hàng 6).
Phần thực hành
Để tự thực hành, chúng tôi cung cấp phần thực hành như sau ở trang cuối tên Practice Section trong sổ làm việc. Hãy tự làm.
Kết luận
Bài viết giải pháp dễ dàng, súc tích tô màu dòng xen kẽ dựa trên giá trị ô trong Excel. Đừng quên tải file Thực hành. Cảm ơn đã đọc, hy vọng hữu ích. Cho chúng tôi biết ở phần bình luận nếu có thắc mắc hoặc đề xuất. Truy cập trang web Exceldemy khám phá thêm.
Bài viết liên quan
- Cách tô màu từng hàng khác nhau trong Excel (3 cách)