У Дуга есть таблица данных, в которой есть столбец с номерами деталей. Эти данные отсортированы по столбцу номеров деталей. Номера деталей не уникальны; например, некоторые номера деталей появляются в таблице три раза, а другие — пять раз. Дуг хотел бы отформатировать таблицу так, чтобы строки таблицы имели эффект «зеленой полосы».

Например, первые пять рядов могут иметь одинаковый номер детали, поэтому Дуг хочет, чтобы эти строки были закрашены зеленым. Следующие два ряда имеют другой номер детали, поэтому он не хочет, чтобы они были затенены зеленым. В следующих трех рядах указан следующий номер детали, поэтому они должны снова стать зелеными и так далее. Каждый раз при изменении номера детали оттенок строки (зеленый или не зеленый) должен меняться.

Один из простых способов выполнить эту задачу — создать вспомогательный столбец, который отображает либо 0, либо 1 в зависимости от номера детали в столбце A.

Например, предположим, что вы хотите поместить свой вспомогательный столбец в столбец Z. Вы можете поместить следующую формулу в ячейку Z2:

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

Скопируйте формулу в столбец Z для каждой строки таблицы данных. По завершении столбец Z будет содержать либо 1, либо 0, переключение будет происходить только при изменении номера детали в столбце A. Затем вы можете использовать значение в столбце Z в качестве управляющего значения для условного форматирования. Все, что вам нужно сделать, это установить формулу в таком формате, чтобы если столбец Z содержал 1, то ваши ячейки были зелеными.

Вы должны отметить, что после того, как ваше условное форматирование настроено и работает правильно, вы можете скрыть столбец Z, чтобы он не отвлекал кого-либо, использующего вашу таблицу данных.

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

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

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

Помните, что это единственная формула, введенная в правиле условного форматирования, в одной строке. В этой формуле предполагается, что номера деталей находятся в столбце A, а таблица данных начинается в ячейке A2.

Кроме того, если вы удалите какие-либо строки в таблице данных, вы захотите повторно применить условный формат ко всем ячейкам в таблице данных.

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

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

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

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

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

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

Этот совет (10517) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Changing_Shading_when_a_Column_Value_Changes [Изменение затенения при изменении значения столбца].