Zar có nhu cầu áp dụng định dạng có điều kiện cho các giá trị trong cột A của một trang tính, nhưng anh ta không thể tìm ra các quy tắc có thể là gì. Ngoài cột A, anh ấy cũng có dữ liệu bắt đầu từ cột B và anh ấy định kỳ thêm các cột dữ liệu mới. Nếu có dữ liệu trong tất cả các cột lẻ bắt đầu bằng B, Zar muốn áp dụng một định dạng trong cột A. (B là cột dữ liệu 1 cho trang tính của anh ấy, vì vậy anh ấy coi nó là số lẻ.) Nếu có dữ liệu trong tất cả các cột chẵn bắt đầu bằng C, Zar muốn áp dụng một định dạng khác trong cột A. Nếu có dữ liệu trong tất cả các cột dữ liệu bắt đầu bằng B — tuy nhiên có thể có nhiều — thì cậu ấy muốn áp dụng định dạng thứ ba.

Vì Zar không còn nghi ngờ gì nữa, bạn có thể dễ dàng tạo một công thức để xác định liệu có thông tin trong cột B và C hay không và áp dụng định dạng cho phù hợp. Trên thực tế, một công thức đơn giản như sau sẽ giải quyết được vấn đề:

=COUNTA(B1:C1)=2 =COUNTA(B1)=1 =COUNTA(C1)=1

Công thức đầu tiên trả về True nếu có thông tin trong cả B và C, công thức thứ hai nếu có thông tin trong B và công thức thứ ba nếu có thông tin trong C. Miễn là bạn chọn “Stop If True” cho mỗi quy tắc / công thức, thì định dạng của bạn sẽ hoạt động tốt.

Việc tạo công thức cho nhiều cột ngoài B và C chỉ khó hơn một chút. Ba loại công thức giống nhau, theo thứ tự sẽ như sau:

=COUNTA(B1:G1)=6)

=COUNTA(B1,D1,F1)=3 =COUNTA(C1,E1,G1)=3

Bạn có thể dễ dàng thêm các tham chiếu ô bổ sung vào công thức, nếu cần. Cách tiếp cận như vậy trả về True chỉ trong ba điều kiện: nếu TẤT CẢ các ô trong phạm vi B1: G1 có nội dung nào đó trong đó, nếu TẤT CẢ các ô lẻ (B1, D1, F1) có nội dung nào đó và nếu TẤT CẢ các ô chẵn (C1, E1, G1) có một cái gì đó trong họ. Nó sẽ không trả về True nếu chỉ một số ô trong phạm vi có giá trị trong đó. Ví dụ: có các giá trị trong ô B1, C1 và E1, thì nó sẽ không trả về True và không có tiêu chí định dạng nào được đáp ứng.

Mặc dù tất cả những thứ này đều hoạt động tốt với giới hạn đã lưu ý, chúng không chính xác là những gì Zar đang tìm kiếm — anh ấy muốn một công thức sẽ phát hiện số lượng cột đang được sử dụng tuần này qua tuần khác, khi anh ấy tiếp tục thêm dữ liệu vào các cột và điều chỉnh công thức phù hợp mà không cần phải chỉnh sửa thủ công công thức để tính đến dữ liệu đã thêm. Nói cách khác, nếu anh ấy thêm dữ liệu vào cột H, anh ấy sẽ muốn các công thức tự động được điều chỉnh để tính đến cột đã thêm:

=COUNTA(B1:H1)=7)

=COUNTA(B1,D1,F1,H1)=4 =COUNTA(C1,E1,G1)=3

Đó rõ ràng là một nhu cầu phức tạp hơn. Có lẽ cách tốt nhất để tiếp cận vấn đề là tạo một hàm do người dùng xác định (một macro) có thể xem xét một loạt các ô và xác định xem một trong ba tiêu chí có được đáp ứng hay không.

Hãy xem xét macro sau:

Function CellChk(crng As Range) As String     Dim iNumOdds As Integer     Dim iNumEvens As Integer     Dim iOdds As Integer     Dim iEvens As Integer     Dim iTots As Integer     Dim iTotCells As Integer     Dim rWork As Range     Dim rCell As Range     Dim iLastCol As Integer     Dim sTemp As String

iOdds = 0     iEvens = 0     iTots = 0

' Figure out the real last column in the worksheet and set range     iLastCol = ActiveSheet.Cells.Find(What:="*", _         SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _         LookIn:=xlFormulas).Column     Set rWork = Range(Cells(crng.Row, 2), Cells(crng.Row, iLastCol))



iTotCells = rWork.Count     iNumOdds = (iTotCells + 1) \ 2    ' Number of odd columns     iNumEvens = iTotCells - iNumOdds  ' Number of even columns

For Each rCell In rWork         If rCell <> "" Then             If ((rCell.Column - 1) Mod 2) = 1 Then                 iOdds = iOdds + 1             Else                 iEvens = iEvens + 1             End If             iTots = iTots + 1         End If     Next rCell

sTemp = ""

If iTots = iTotCells Then         sTemp = "t"

ElseIf iOdds = iNumOdds Then         sTemp = "o"

ElseIf iEvens = iNumEvens Then         sTemp = "e"

End If     CellChk = sTemp End Function

Bạn sử dụng macro bằng cách chuyển cho nó một địa chỉ trong hàng bạn muốn kiểm tra.

Vì vậy, ví dụ: nếu bạn đang áp dụng quy tắc định dạng có điều kiện cho ô A3, bạn sẽ chuyển cho macro một địa chỉ của B3 hoặc C3 — bất kỳ thứ gì ngoại trừ A3, vì điều đó sẽ gây ra tham chiếu vòng tròn. Macro tìm kiếm ô cuối cùng được sử dụng trong hàng đó và sau đó xác định có bao nhiêu ô chẵn và lẻ có thứ gì đó trong đó. Macro trả về bất kỳ giá trị nào trong bốn giá trị; nếu tiêu chí đầu tiên được đáp ứng (tất cả các ô trong hàng bắt đầu bằng cột B đều có nội dung nào đó) thì trả về “t”. Nếu tất cả các cột lẻ (với B là cột lẻ đầu tiên) có thứ gì đó trong chúng, thì “o” được trả về. Nếu tất cả các cột chẵn (với C là cột chẵn đầu tiên)

có một cái gì đó trong chúng, sau đó “e” được trả về. Nếu không có tiêu chí nào trong ba tiêu chí được đáp ứng, thì hàm không trả về kết quả nào.

Bạn vẫn cần thiết lập ba quy tắc định dạng có điều kiện dựa trên việc đánh giá công thức. Dưới đây là ba cách bạn có thể sử dụng với macro này:

=CellChk(B1)="t")

=CellChk(B1)="o")

=CellChk(B1)="e")

Các ví dụ này là để áp dụng định dạng có điều kiện cho ô A1; điều chỉnh các tham chiếu ô đến đúng hàng mà bạn muốn macro phân tích. Hãy nhớ rằng mặc dù bạn chỉ định một ô duy nhất (B1 trong các ví dụ này), macro sẽ tính toán số lượng ô trong hàng thực sự cần xem xét.

_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 (5945) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.