Doug có một bảng dữ liệu bao gồm một cột số bộ phận. Dữ liệu này được sắp xếp theo cột số bộ phận. Số bộ phận không phải là duy nhất; ví dụ: một số bộ phận số xuất hiện ba lần trong bảng và những số khác xuất hiện năm lần. Doug muốn định dạng bảng để các hàng của bảng có hiệu ứng “thanh màu xanh lá cây”.

Ví dụ, năm hàng đầu tiên có thể có cùng số phần, vì vậy Doug muốn các hàng đó được tô màu xanh lục. Hai hàng tiếp theo có số phần khác nhau, vì vậy anh ta muốn những hàng đó không có bóng xanh. Ba hàng tiếp theo có số phần tiếp theo, vì vậy những hàng đó sẽ có màu xanh lục trở lại, v.v. Mỗi khi số bộ phận thay đổi, bóng của hàng (xanh lục hoặc không xanh lục) sẽ thay đổi.

Một cách dễ dàng để thực hiện nhiệm vụ này là tạo một cột trợ giúp hiển thị số 0 hoặc số 1 tùy thuộc vào số bộ phận trong cột A.

Ví dụ: giả sử bạn muốn đặt cột trợ giúp của mình vào cột Z. Bạn có thể đặt công thức sau vào ô Z2:

=IF(A2=A1,Z1,1-Z1)

Sao chép công thức xuống cột Z cho mỗi hàng trong bảng dữ liệu của bạn. Khi hoàn tất, cột Z sẽ chứa 1 hoặc 0, chỉ chuyển đổi khi số bộ phận trong cột A thay đổi. Sau đó, bạn có thể sử dụng giá trị trong cột Z làm giá trị kiểm soát cho định dạng có điều kiện của mình. Tất cả những gì bạn cần làm là đặt công thức ở định dạng sao cho nếu cột Z chứa 1, thì các ô của bạn có màu xanh lục.

Bạn nên lưu ý rằng sau khi định dạng có điều kiện của bạn được thiết lập và hoạt động bình thường, bạn có thể ẩn cột Z để bất kỳ ai sử dụng bảng dữ liệu của bạn không bị phân tâm.

Nếu bạn không thể sử dụng cột trợ giúp vì lý do nào đó, thì có một công thức khá thú vị mà bạn có thể sử dụng ở định dạng có điều kiện. Chỉ cần đảm bảo bảng dữ liệu của bạn được sắp xếp theo cột A (số bộ phận) và sau đó chọn tất cả các ô trong bảng, ngoại trừ bất kỳ tiêu đề cột nào. Sau đó, xác định một định dạng có điều kiện sử dụng công thức này:

=MOD(SUMPRODUCT(--(($A$1:INDIRECT(ADDRESS(ROW()-1,1,3,1))

=$A$2:INDIRECT(ADDRESS(ROW(),1,3,1)))=FALSE)),2)

Hãy nhớ rằng đây là một công thức duy nhất, được nhập theo quy tắc định dạng có điều kiện, tất cả trên một dòng. Công thức này giả định rằng số bộ phận nằm trong cột A và bảng dữ liệu bắt đầu trong ô A2.

Hơn nữa, nếu bạn xóa bất kỳ hàng nào trong bảng dữ liệu, bạn sẽ muốn áp dụng lại định dạng có điều kiện cho tất cả các ô trong bảng dữ liệu.

Cuối cùng, có bất kỳ số lượng macro nào bạn có thể viết để áp dụng định dạng. Tất cả những gì bạn cần làm là thực hiện bước macro qua các ô trong cột A, xác định xem số bộ phận có thay đổi hay không, sau đó áp dụng định dạng đúng dựa trên những gì nó phát hiện ra. Đây là một ví dụ:

Sub ShadeRows()

Dim ThisOrder As Long     Dim PrvOrder As Long     Dim LastRow As Long     Dim Clr As Integer     Dim R As Long

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

' Enter desired color codes here     ' (24 is Lavender, 35 is Light Green)

RwColor = Array(24, 35)



Clr = 0  ' Used to toggle between the two colors

For R = 2 To LastRow         ThisOrder = Cells(R, 1).Value         PrvOrder = Cells(R - 1, 1).Value         If ThisOrder <> PrvOrder Then Clr = 1 - Clr

' Select only the columns that are used         Range("A" & R & ":M" & R).Select         Selection.Interior.ColorIndex = RwColor(Clr)

Next R End Sub

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (10518) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: