Подскажите, пожалуйста, как просуммировать значения с несколькими условиями, в том числе по цвету. По отдельности-то у меня получается, через SUMIFS и ColorFunction, а вот как их объединить в одной формуле..
В примере необходимо просуммировать числа выделенные разным цветом для "а", при условии "x".
Доброго времени суток,
Подскажите, пожалуйста, как просуммировать значения с несколькими условиями, в том числе по цвету. По отдельности-то у меня получается, через SUMIFS и ColorFunction, а вот как их объединить в одной формуле..
В примере необходимо просуммировать числа выделенные разным цветом для "а", при условии "x".user0
Function СУММ_ЦВЕТ(Диапазон_суммирования As Range, Цвет_условия As Range, _ Услови_A, Услови_x) As Variant For Each cll In Диапазон_суммирования If cll.Interior.ColorIndex = Цвет_условия.Interior.ColorIndex Then If Cells(cll.Row, 1) = Услови_A And Cells(cll.Row, 2) = Услови_x Then summa = summa + cll.Value End If End If Next
СУММ_ЦВЕТ = summa End Function
[/vba]
можно так сделать, но на больших диапазонах будет тормозить
[vba]
Код
Function СУММ_ЦВЕТ(Диапазон_суммирования As Range, Цвет_условия As Range, _ Услови_A, Услови_x) As Variant For Each cll In Диапазон_суммирования If cll.Interior.ColorIndex = Цвет_условия.Interior.ColorIndex Then If Cells(cll.Row, 1) = Услови_A And Cells(cll.Row, 2) = Услови_x Then summa = summa + cll.Value End If End If Next
СУММ_ЦВЕТ = summa End Function
[/vba]
можно так сделать, но на больших диапазонах будет тормозитьStaniiislav
Единственный способ стать умнее, играть с более умным соперником
Сообщение отредактировал Staniiislav - Четверг, 27.12.2012, 11:56
Попробовал его на рабочем файле, и не считает, если немного передвинуть данные (см обновленный пример).. Полагаю это можно поправить, добавив возможность выбора диапазона для условий A, x. Подскажите, как это реализовать пожалуйста )
ps: тормоза не так страшны, у меня как раз диапазон на пару тысяч значений максимум
Спасибо, отлично работает на примере, но..
Попробовал его на рабочем файле, и не считает, если немного передвинуть данные (см обновленный пример).. Полагаю это можно поправить, добавив возможность выбора диапазона для условий A, x. Подскажите, как это реализовать пожалуйста )
ps: тормоза не так страшны, у меня как раз диапазон на пару тысяч значений максимумuser0
[/vba] J$17:J$22 - диапазон суммирования $G26 - цвет ячейки "a" - условие для А (можно ссылаться на ячейку с этим условием, можно вводить в формулу в кавычках, как в примере) "F" - буква столбца с условиями А "x" - условие для X (можно ссылаться на ячейку с этим условием, можно вводить в формулу в кавычках, как в примере) "G" - буква столбца с условиями X
[vba]
Код
Function СУММ_ЦВЕТ(Диапазон_суммирования As Range, Цвет_условия As Range, _ Услови_A, Столбца_A As String, Услови_x, Столбца_X As String) For Each cll In Диапазон_суммирования If cll.Interior.ColorIndex = Цвет_условия.Interior.ColorIndex Then If Cells(cll.Row, Столбца_A) = Услови_A And Cells(cll.Row, Столбца_X) = Услови_x Then summa = summa + cll.Value End If End If Next
СУММ_ЦВЕТ = summa End Function
[/vba]
изменил, для ячейки [vba]
Код
J26==СУММ_ЦВЕТ(J$17:J$22;$G26;"a";"F";"x";"G")
[/vba] J$17:J$22 - диапазон суммирования $G26 - цвет ячейки "a" - условие для А (можно ссылаться на ячейку с этим условием, можно вводить в формулу в кавычках, как в примере) "F" - буква столбца с условиями А "x" - условие для X (можно ссылаться на ячейку с этим условием, можно вводить в формулу в кавычках, как в примере) "G" - буква столбца с условиями X
[vba]
Код
Function СУММ_ЦВЕТ(Диапазон_суммирования As Range, Цвет_условия As Range, _ Услови_A, Столбца_A As String, Услови_x, Столбца_X As String) For Each cll In Диапазон_суммирования If cll.Interior.ColorIndex = Цвет_условия.Interior.ColorIndex Then If Cells(cll.Row, Столбца_A) = Услови_A And Cells(cll.Row, Столбца_X) = Услови_x Then summa = summa + cll.Value End If End If Next
Function СУММ_ЦВЕТ_2(Диапазон_суммирования As Range, Диапазон_условий As Range, Цвет_условия As Range, _ Услови_A, Услови_x) As Variant For Each cll In Диапазон_суммирования If cll.Interior.ColorIndex = Цвет_условия.Interior.ColorIndex Then If Cells(cll.Row, Диапазон_условий.Column) = Услови_A And Cells(cll.Row, Диапазон_условий.Column + 1) = Услови_x Then summa = summa + cll.Value End If End If Next СУММ_ЦВЕТ_2 = summa End Function
[/vba] См. файл
Еще вариант. [vba]
Код
Function СУММ_ЦВЕТ_2(Диапазон_суммирования As Range, Диапазон_условий As Range, Цвет_условия As Range, _ Услови_A, Услови_x) As Variant For Each cll In Диапазон_суммирования If cll.Interior.ColorIndex = Цвет_условия.Interior.ColorIndex Then If Cells(cll.Row, Диапазон_условий.Column) = Услови_A And Cells(cll.Row, Диапазон_условий.Column + 1) = Услови_x Then summa = summa + cll.Value End If End If Next СУММ_ЦВЕТ_2 = summa End Function
даже правильнее наверное будет так: с исправлением от AlexM
[vba]
Код
Function СУММ_ЦВЕТ(Диапазон_суммирования As Range, Диапазон_условий As Range, Цвет_условия As Range, _ Услови_A, Услови_x) As Double Dim cll As Range, summa Application.Volatile True For Each cll In Диапазон_суммирования If cll.Interior.ColorIndex = Цвет_условия.Interior.ColorIndex Then If Cells(cll.Row, Диапазон_условий.Column) = Услови_A And _ Cells(cll.Row, Диапазон_условий.Column + 1) = Услови_x Then summa = summa + cll.Value End If End If Next
СУММ_ЦВЕТ = summa End Function
[/vba]
для ячейки J26: [vba]
Код
=СУММ_ЦВЕТ(J$17:J$22;$F$17:$G$22;$G26;"a";"x")
[/vba] где, J$17:J$22 - диапазон суммирования $F$17:$G$22 - диапазон уловий $G26 - цвет ячейки "a" - условие для А (можно ссылаться на ячейку с этим условием, можно вводить в формулу в кавычках, как в примере) "x" - условие для X (можно ссылаться на ячейку с этим условием, можно вводить в формулу в кавычках, как в примере) добавлено позже:
не забывайте про регистр (маленькая или большая буква в условиях А,Х), а также про язык ввода (рус. или англ.)
даже правильнее наверное будет так: с исправлением от AlexM
[vba]
Код
Function СУММ_ЦВЕТ(Диапазон_суммирования As Range, Диапазон_условий As Range, Цвет_условия As Range, _ Услови_A, Услови_x) As Double Dim cll As Range, summa Application.Volatile True For Each cll In Диапазон_суммирования If cll.Interior.ColorIndex = Цвет_условия.Interior.ColorIndex Then If Cells(cll.Row, Диапазон_условий.Column) = Услови_A And _ Cells(cll.Row, Диапазон_условий.Column + 1) = Услови_x Then summa = summa + cll.Value End If End If Next
СУММ_ЦВЕТ = summa End Function
[/vba]
для ячейки J26: [vba]
Код
=СУММ_ЦВЕТ(J$17:J$22;$F$17:$G$22;$G26;"a";"x")
[/vba] где, J$17:J$22 - диапазон суммирования $F$17:$G$22 - диапазон уловий $G26 - цвет ячейки "a" - условие для А (можно ссылаться на ячейку с этим условием, можно вводить в формулу в кавычках, как в примере) "x" - условие для X (можно ссылаться на ячейку с этим условием, можно вводить в формулу в кавычках, как в примере) добавлено позже:
не забывайте про регистр (маленькая или большая буква в условиях А,Х), а также про язык ввода (рус. или англ.)Staniiislav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
