Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Суммирование значений в строке по условию - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование значений в строке по условию (Формулы/Formulas)
Суммирование значений в строке по условию
Бадя Дата: Среда, 15.05.2019, 10:15 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Всем привет, помогите с формулой, возможно VBA (см. Пример)

Задача.
1. Необходимо посчитать сумму значений ячеек в строке №3 соответствующие ячейкам строки №1 и цвету заливке ячейки (серый)
2. Необходимо посчитать сумму значений ячеек в строке №4 соответствующие ячейкам строки №1 и цвету заливке ячейки (оранжевый)

Прошу извинить, если глупая задача!
Спасибо!
К сообщению приложен файл: 0057574.xlsx(9.3 Kb)


Сообщение отредактировал Бадя - Среда, 15.05.2019, 11:17
 
Ответить
СообщениеВсем привет, помогите с формулой, возможно VBA (см. Пример)

Задача.
1. Необходимо посчитать сумму значений ячеек в строке №3 соответствующие ячейкам строки №1 и цвету заливке ячейки (серый)
2. Необходимо посчитать сумму значений ячеек в строке №4 соответствующие ячейкам строки №1 и цвету заливке ячейки (оранжевый)

Прошу извинить, если глупая задача!
Спасибо!

Автор - Бадя
Дата добавления - 15.05.2019 в 10:15
boa Дата: Среда, 15.05.2019, 12:19 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 397
Репутация: 110 ±
Замечаний: 0% ±

2013, 365
Бадя,
[vba]
Код
Function SumByColor2(SumRange As Range, Optional ColorRange As Range, Optional ColorSample As Range) As Double
'функция суммирует ячейки с определенным цветом
'SumRange - диапазон суммирования
'ColorRange - диапазон раскрашенных ячеек.
'ColorSample - ячейка, цвет которой принимается как образец для суммирования
'за основу взят http://www.planetaexcel.ru/techniques/9/162/
     
    Dim Sum#, iCell&
    Application.Volatile True 'что бы функция пересчитывалась при изменении значений на листе
    Dim MyColor&
    If ColorSample Is Nothing Then MyColor = Application.Caller.Interior.Color Else MyColor = ColorSample.Interior.Color
    If SumRange.Cells.Count > 1000000 Then
        If vbCancel = MsgBox("Задан очень большой диапазон." & vbCrLf & "Подсчет может затянуться.", vbOKCancel) Then Exit Function
    End If
    
    If ColorRange Is Nothing Then Set ColorRange = SumRange
    If SumRange.Cells.Count <> ColorRange.Cells.Count Then
        MsgBox "Диапазон суммирования и диапазон задающий цвета отличаются по размеру." & vbCrLf & "Укажите равные диапазоны.", vbOK, "Не правильная формула в ячейке " & Application.Caller.Address: Exit Function
    End If
    For iCell = 1 To SumRange.Cells.Count
        If ColorRange(iCell).Interior.Color = MyColor Then
            Sum = Sum + SumRange(iCell).Value
        End If
    Next iCell
    
    SumByColor2 = Sum
End Function
[/vba]
К сообщению приложен файл: 0057574.xlsm(19.2 Kb)


 
Ответить
СообщениеБадя,
[vba]
Код
Function SumByColor2(SumRange As Range, Optional ColorRange As Range, Optional ColorSample As Range) As Double
'функция суммирует ячейки с определенным цветом
'SumRange - диапазон суммирования
'ColorRange - диапазон раскрашенных ячеек.
'ColorSample - ячейка, цвет которой принимается как образец для суммирования
'за основу взят http://www.planetaexcel.ru/techniques/9/162/
     
    Dim Sum#, iCell&
    Application.Volatile True 'что бы функция пересчитывалась при изменении значений на листе
    Dim MyColor&
    If ColorSample Is Nothing Then MyColor = Application.Caller.Interior.Color Else MyColor = ColorSample.Interior.Color
    If SumRange.Cells.Count > 1000000 Then
        If vbCancel = MsgBox("Задан очень большой диапазон." & vbCrLf & "Подсчет может затянуться.", vbOKCancel) Then Exit Function
    End If
    
    If ColorRange Is Nothing Then Set ColorRange = SumRange
    If SumRange.Cells.Count <> ColorRange.Cells.Count Then
        MsgBox "Диапазон суммирования и диапазон задающий цвета отличаются по размеру." & vbCrLf & "Укажите равные диапазоны.", vbOK, "Не правильная формула в ячейке " & Application.Caller.Address: Exit Function
    End If
    For iCell = 1 To SumRange.Cells.Count
        If ColorRange(iCell).Interior.Color = MyColor Then
            Sum = Sum + SumRange(iCell).Value
        End If
    Next iCell
    
    SumByColor2 = Sum
End Function
[/vba]

Автор - boa
Дата добавления - 15.05.2019 в 12:19
Бадя Дата: Среда, 15.05.2019, 12:31 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Большое Вам спасибо! Гениально!
 
Ответить
СообщениеБольшое Вам спасибо! Гениально!

Автор - Бадя
Дата добавления - 15.05.2019 в 12:31
_Boroda_ Дата: Среда, 15.05.2019, 12:34 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15575
Репутация: 6075 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Если формулой, то нужно описание принципа покраски ячеек. Почему некоторые серые, а другие оранжевые?
Если раскраска произвольна, то или макрос, или в диспетчер имен (Контрл F3) две формулы
цв0 (находясь при этом в ячейке А3)
Код
=ПОЛУЧИТЬ.ЯЧЕЙКУ(63;Лист1!A3)

цв
Код
=ПОЛУЧИТЬ.ЯЧЕЙКУ(63;СМЕЩ(Лист1!$A$1;;Ч(ИНДЕКС(СТОЛБЕЦ(Лист1!$C:$L)-1;));))

И в А3 формулу
Код
=СУММПРОИЗВ((цв=цв0)*C3:L3)

Макросов в файле нет, но они должны быть разрешены
К сообщению приложен файл: 0057574_1.xlsm(9.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли формулой, то нужно описание принципа покраски ячеек. Почему некоторые серые, а другие оранжевые?
Если раскраска произвольна, то или макрос, или в диспетчер имен (Контрл F3) две формулы
цв0 (находясь при этом в ячейке А3)
Код
=ПОЛУЧИТЬ.ЯЧЕЙКУ(63;Лист1!A3)

цв
Код
=ПОЛУЧИТЬ.ЯЧЕЙКУ(63;СМЕЩ(Лист1!$A$1;;Ч(ИНДЕКС(СТОЛБЕЦ(Лист1!$C:$L)-1;));))

И в А3 формулу
Код
=СУММПРОИЗВ((цв=цв0)*C3:L3)

Макросов в файле нет, но они должны быть разрешены

Автор - _Boroda_
Дата добавления - 15.05.2019 в 12:34
Бадя Дата: Среда, 15.05.2019, 13:40 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
boa, подскажите, что не так?
выдаёт сбой (см. Вложение) и перезапускает Эксель, когда начинаешь выделять диапазон задающий цвет (выбираешь первую ячейку и всплывает сообщение, хотя диапазон еще не выбран)
К сообщению приложен файл: 2183182.gif(61.1 Kb)
 
Ответить
Сообщениеboa, подскажите, что не так?
выдаёт сбой (см. Вложение) и перезапускает Эксель, когда начинаешь выделять диапазон задающий цвет (выбираешь первую ячейку и всплывает сообщение, хотя диапазон еще не выбран)

Автор - Бадя
Дата добавления - 15.05.2019 в 13:40
boa Дата: Среда, 15.05.2019, 14:20 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 397
Репутация: 110 ±
Замечаний: 0% ±

2013, 365
Бадя,
Правьте аргументы функции через строку формул или закомментируйте строки
[vba]
Код
'    If SumRange.Cells.Count <> ColorRange.Cells.Count Then
'        MsgBox "Диапазон суммирования и диапазон задающий цвета отличаются по размеру." & vbCrLf & "Укажите равные диапазоны.", vbOK, "Не правильная формула в ячейке " & Application.Caller.Address: Exit Function
'    End If
[/vba]


 
Ответить
СообщениеБадя,
Правьте аргументы функции через строку формул или закомментируйте строки
[vba]
Код
'    If SumRange.Cells.Count <> ColorRange.Cells.Count Then
'        MsgBox "Диапазон суммирования и диапазон задающий цвета отличаются по размеру." & vbCrLf & "Укажите равные диапазоны.", vbOK, "Не правильная формула в ячейке " & Application.Caller.Address: Exit Function
'    End If
[/vba]

Автор - boa
Дата добавления - 15.05.2019 в 14:20
Бадя Дата: Среда, 15.05.2019, 14:21 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
boa, Да, спасибо, работает!
 
Ответить
Сообщениеboa, Да, спасибо, работает!

Автор - Бадя
Дата добавления - 15.05.2019 в 14:21
_Boroda_ Дата: Среда, 15.05.2019, 14:29 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15575
Репутация: 6075 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Иван, а вариант с диспетчером имен не смотрели? Если у Вас много ячеек для подсчета, то он может быть побыстрее, чем UDF-ки


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеИван, а вариант с диспетчером имен не смотрели? Если у Вас много ячеек для подсчета, то он может быть побыстрее, чем UDF-ки

Автор - _Boroda_
Дата добавления - 15.05.2019 в 14:29
Бадя Дата: Среда, 15.05.2019, 15:12 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, диапазон не большой, массив 100 на 100, достаточно быстро считает и просто
 
Ответить
Сообщение_Boroda_, диапазон не большой, массив 100 на 100, достаточно быстро считает и просто

Автор - Бадя
Дата добавления - 15.05.2019 в 15:12
_Boroda_ Дата: Среда, 15.05.2019, 15:20 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15575
Репутация: 6075 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Не, 100 UDF - это много. Вы посмотрели мой файл, попробовали?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНе, 100 UDF - это много. Вы посмотрели мой файл, попробовали?

Автор - _Boroda_
Дата добавления - 15.05.2019 в 15:20
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование значений в строке по условию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!