Зару необходимо применить условный формат к значениям в столбце A рабочего листа, но он не может понять, какими могут быть правила. Помимо столбца A, у него также есть данные, начинающиеся в столбце B, и он периодически добавляет новые столбцы данных. Если есть данные во всех нечетных столбцах, начинающихся с B, Zar хочет, чтобы в столбце A был применен один формат (B — столбец данных 1 для его рабочего листа, поэтому он считает его нечетным). Если есть данные во всех четных столбцах, начинающихся с C, Zar хочет, чтобы в столбце A был применен другой формат. Если во всех столбцах данных, начинающихся с B, есть данные — сколько бы их ни было, — он хочет, чтобы был применен третий формат.

Как Zar, без сомнения, понял, вы можете легко создать формулу, чтобы определить, есть ли информация в столбцах B и C, и соответствующим образом применить форматирование. Фактически, такая простая формула поможет:

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

Первая формула возвращает True, если есть информация как в B, так и в C, вторая, если есть информация в B, и третья, если есть информация в C. Пока вы выбираете «Stop If True» для каждого правила / формулы, тогда ваше форматирование будет работать нормально.

Создание формулы для нескольких столбцов за пределами B и C лишь немного сложнее. Те же три типа формул, по порядку, будут следующими:

=COUNTA(B1:G1)=6)

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

При необходимости вы можете легко добавить в формулы дополнительные ссылки на ячейки. Такой подход возвращает True только в трех условиях: если ВСЕ ячейки в диапазоне B1: G1 что-то содержат, если ВСЕ нечетные ячейки (B1, D1, F1) что-то содержат, и если ВСЕ четные ячейки (C1, E1, G1) что-то в них есть. Он не вернет True, если только некоторые из ячеек в диапазоне содержат значения. Например, есть значения в ячейках B1, C1 и E1, тогда он не вернет True, и ни один из критериев форматирования не будет соблюден.

Хотя все они отлично работают с отмеченным ограничением, это не совсем то, что ищет Zar — ему нужна формула, которая будет определять, сколько столбцов используется неделя за неделей, поскольку он продолжает добавлять данные в столбцы и настраивать формула соответственно без необходимости вручную редактировать формулу с учетом добавленных данных. Другими словами, если он добавляет данные в столбец H, он хочет, чтобы формулы автоматически корректировались с учетом добавленного столбца:

=COUNTA(B1:H1)=7)

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

Очевидно, что это более сложная потребность. Возможно, лучший способ решить эту проблему — создать определяемую пользователем функцию (макрос), которая может просматривать диапазон ячеек и определять, удовлетворяется ли один из трех критериев.

Рассмотрим следующий макрос:

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

Вы используете макрос, передавая ему адрес в строке, которую хотите проверить.

Так, например, если вы применяли правило условного форматирования к ячейке A3, вы бы передали макросу адрес B3 или C3 — что угодно, кроме A3, поскольку это вызовет циклическую ссылку. Макрос ищет последнюю использованную в этой строке ячейку, а затем определяет, сколько четных и нечетных ячеек содержит что-то. Макрос возвращает любое из четырех значений; если первый критерий соблюден (во всех ячейках в строке, начинающейся со столбца B, что-то есть), то возвращается «t». Если во всех нечетных столбцах (где B является первым нечетным столбцом) что-то есть, возвращается «o». Если все четные столбцы (причем C — первый четный столбец)

есть что-то в них, то возвращается «е». Если ни один из трех критериев не соблюден, функция ничего не возвращает.

Вам по-прежнему потребуется настроить три правила условного форматирования, которые зависят от вычисления формулы. Вот три, которые вы можете использовать с этим макросом:

=CellChk(B1)="t")

=CellChk(B1)="o")

=CellChk(B1)="e")

Эти примеры предназначены для применения условного формата к ячейке A1; настройте ссылки на ячейки на правильную строку, которую вы хотите проанализировать с помощью макроса. Помните, что даже если вы указываете одну ячейку (B1 в этих примерах), макрос вычисляет, сколько ячеек в строке нужно просмотреть.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (5945) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.