У Шриниваса есть данные в обоих столбцах A и B. Ему нужно просуммировать значения в столбце B, для которых формат ячеек соответствующих ячеек в столбце A желтый. Например, если формат в ячейке A17 желтый, то значение в ячейке B17 должно быть включено в сумму.

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

Function SumNextYellow(ByVal r As Range)

Dim c As Range     Dim a As Double

For Each c In r         If c.Offset(0, -1).Interior.ColorIndex = 6 Then 'Yellow             a = a + c.Value         End If     Next c     SumNextYellow = a End Function

Функция может использоваться в формуле рабочего листа и принимает ссылку на диапазон в качестве аргумента. Затем он проходит через каждую ячейку в диапазоне, и если ячейка слева желтая, то значение включается в сумму. (Обратите внимание, что ColorIndex, используемый в макросе, должен быть протестирован с вашей версией Excel, чтобы убедиться, что он применим; в разных версиях он может отличаться.)

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

Function ColorConditionSum(cSample As Excel.Range, rng As Excel.Range)

' This Function returns sum of values in rng.Columns(2) if ' corresponding cell in rng.Columns(1) is colored with sample ' color (cSample cell)



' Arguments: cSample = cell colored by sample color '            rng = cell range to be processed (2 columns)



Dim rngCol2 As Excel.Range     Dim rngConstants As Excel.Range     Dim rngFormulas As Excel.Range     Dim lColorIndex As Long     Dim MySum As Double     Dim area As Excel.Range     Dim c As Excel.Range

ColorConditionSum = False     If Not TypeOf cSample Is Excel.Range Then Exit Function '>>>     lColorIndex = cSample.Interior.ColorIndex

MySum = 0     Set rngCol2 = Nothing     If TypeOf rng Is Excel.Range Then         If rng.Columns.Count < 2 Then Exit Function '>>>         On Error Resume Next         For Each area In rng.Areas             If rngCol2 Is Nothing Then                 Set rngCol2 = area.Columns(2).SpecialCells(xlCellTypeConstants, 1)

If rngCol2 Is Nothing Then                     Set rngCol2 = area.Columns(2).SpecialCells(xlCellTypeFormulas, 1)

Else                     Set rngCol2 = Application.Union( _                         rngCol2, area.Columns(2).SpecialCells(xlCellTypeFormulas, 1))

End If             Else                 Set rngCol2 = Application.Union( _                     rngCol2, area.Columns(2).SpecialCells(xlCellTypeConstants, 1))

Set rngCol2 = Application.Union( _                     rngCol2, area.Columns(2).SpecialCells(xlCellTypeFormulas, 1))

End If         Next area

For Each area In rngCol2.Areas             For Each c In area.Cells                 With c.Offset(0, -1)

If .Interior.ColorIndex = lColorIndex Then                         MySum = MySum + c.Value                     End If                 End With             Next c         Next area     End If

ColorConditionSum = MySum End Function

Вы используете эту функцию на листе следующим образом:

=ColorConditionSum(A10, A12:B22)

В данном случае это ячейка с внутренним цветом, который вы хотите сопоставить, а A12: B22 — это диапазон ячеек для оценки. Значения извлекаются из второго столбца в диапазоне, а форматирование проверяется в ячейках в первом столбце.

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

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

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

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

Этот совет (3298) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Summing_Based_on_Formatting_in_Adjacent_Cells [Суммирование на основе форматирования в соседних ячейках].