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

Вход

Регистрация

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

 

= Мир MS Excel/Суммирование наибольших значений по условию. - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование наибольших значений по условию. (Формулы/Formulas)
Суммирование наибольших значений по условию.
olegus Дата: Понедельник, 04.07.2016, 13:40 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день форумчане!
Прошу помощи.
В ячейке A2 нужна формула, которая суммирует наибольшие значения из столбца A по критерию из столбца B.
Например: помимо остальных значений в столбце A по кодам 157 и 211 нужно суммировать значения 6 385 600,00 и 123 491 300,00, игнорируя значения 53 980,00 и 1 942 057,25.
В оригинале столбцы A и B обновляются из других книг и имеют числовой формат.
Заранее спасибо.
К сообщению приложен файл: 2505458.xls (26.5 Kb)
 
Ответить
СообщениеДобрый день форумчане!
Прошу помощи.
В ячейке A2 нужна формула, которая суммирует наибольшие значения из столбца A по критерию из столбца B.
Например: помимо остальных значений в столбце A по кодам 157 и 211 нужно суммировать значения 6 385 600,00 и 123 491 300,00, игнорируя значения 53 980,00 и 1 942 057,25.
В оригинале столбцы A и B обновляются из других книг и имеют числовой формат.
Заранее спасибо.
Автор - olegus
Дата добавления - 04.07.2016 в 13:40
Nic70y Дата: Понедельник, 04.07.2016, 13:53 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2273 ±
Замечаний: 0% ±

Excel 2010
массивная
Код
=МАКС(ЕСЛИ(B3:B40=157;A3:A40))+МАКС(ЕСЛИ(B3:B40=211;A3:A40))
немассивная
Код
=СУММПРОИЗВ(МАКС((B3:B40=157)*A3:A40)+МАКС((B3:B40=211)*A3:A40))
К сообщению приложен файл: 5237197.xls (26.5 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщениемассивная
Код
=МАКС(ЕСЛИ(B3:B40=157;A3:A40))+МАКС(ЕСЛИ(B3:B40=211;A3:A40))
немассивная
Код
=СУММПРОИЗВ(МАКС((B3:B40=157)*A3:A40)+МАКС((B3:B40=211)*A3:A40))
Автор - Nic70y
Дата добавления - 04.07.2016 в 13:53
olegus Дата: Понедельник, 04.07.2016, 13:59 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Nic70y спасибо, но не подойдет. Кроме кодов 157 и 211 могут быть и другие повторяющиеся коды. Каждый месяц могут быть разные. Максимум повторяющихся кодов может быть от 1 до 999 %) .
 
Ответить
СообщениеNic70y спасибо, но не подойдет. Кроме кодов 157 и 211 могут быть и другие повторяющиеся коды. Каждый месяц могут быть разные. Максимум повторяющихся кодов может быть от 1 до 999 %) .
Автор - olegus
Дата добавления - 04.07.2016 в 13:59
Manyasha Дата: Понедельник, 04.07.2016, 14:07 | Сообщение № 4
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 0% ±

Excel 2010, 2016
olegus, я так поняла, что по уникальным кодам тоже нужно суммировать.
Так подойдет (массивная)?
Код
=МАКС(ЕСЛИ($B$3:$B$40=B3;$A$3:$A$40))*(СЧЁТЕСЛИ($B$2:B3;B3)=1)


Если нужно суммировать только максимумы по повторам, то замените в конце формулы "=1" на ">1".
К сообщению приложен файл: 2505458-1.xls (25.0 Kb)

ЯД: 410013299366744 WM: R193491431804
 
Ответить
Сообщениеolegus, я так поняла, что по уникальным кодам тоже нужно суммировать.
Так подойдет (массивная)?
Код
=МАКС(ЕСЛИ($B$3:$B$40=B3;$A$3:$A$40))*(СЧЁТЕСЛИ($B$2:B3;B3)=1)


Если нужно суммировать только максимумы по повторам, то замените в конце формулы "=1" на ">1".
Автор - Manyasha
Дата добавления - 04.07.2016 в 14:07
abtextime Дата: Понедельник, 04.07.2016, 14:19 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
Как понял (UDF)

[vba]
Код
Public Function MySum(R As Range, N As Long) As Double
    Dim S() As Double
    ReDim S(N) As Double
    For i = 1 To R.Rows.Count
        If R.Cells(i, 1) > S(R.Cells(i, 2)) Then S(R.Cells(i, 2)) = R.Cells(i, 1)
    Next i
    For i = 0 To N
        MySum = MySum + S(i)
    Next i
End Function
[/vba]
К сообщению приложен файл: 2505458.xlsm (15.9 Kb)


Сообщение отредактировал abtextime - Понедельник, 04.07.2016, 14:49
 
Ответить
СообщениеКак понял (UDF)

[vba]
Код
Public Function MySum(R As Range, N As Long) As Double
    Dim S() As Double
    ReDim S(N) As Double
    For i = 1 To R.Rows.Count
        If R.Cells(i, 1) > S(R.Cells(i, 2)) Then S(R.Cells(i, 2)) = R.Cells(i, 1)
    Next i
    For i = 0 To N
        MySum = MySum + S(i)
    Next i
End Function
[/vba]
Автор - abtextime
Дата добавления - 04.07.2016 в 14:19
Nic70y Дата: Понедельник, 04.07.2016, 14:25 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2273 ±
Замечаний: 0% ±

Excel 2010
сводная, фильтруйте
К сообщению приложен файл: 5237197.xlsx (14.0 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщениесводная, фильтруйте
Автор - Nic70y
Дата добавления - 04.07.2016 в 14:25
olegus Дата: Понедельник, 04.07.2016, 14:32 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо Manyasha и Nic70y!
И последний вопрос. Если строк будет от 1000 до 2000 формулы сильно тормозить будут?
 
Ответить
СообщениеСпасибо Manyasha и Nic70y!
И последний вопрос. Если строк будет от 1000 до 2000 формулы сильно тормозить будут?
Автор - olegus
Дата добавления - 04.07.2016 в 14:32
Udik Дата: Понедельник, 04.07.2016, 14:47 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
Вариант с доп.столбцом. В нём флаг выводит, хотя можно и значение.
массивка
Код

{=ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$40;B3)>1;ЕСЛИ(A3=МАКС($A$3:$A$40*($B$3:$B$40=B3));1;0);1)}

простая
Код

=СУММПРОИЗВ(--(СЧЁТЕСЛИ($B$3:$B$40;B3)=1))+СУММПРОИЗВ((СЧЁТЕСЛИ($B$3:$B$40;B3)>1)*(A3=МАКС($A$3:$A$40*($B$3:$B$40=B3))))
К сообщению приложен файл: 0t.xls (32.0 Kb)

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеВариант с доп.столбцом. В нём флаг выводит, хотя можно и значение.
массивка
Код

{=ЕСЛИ(СЧЁТЕСЛИ($B$3:$B$40;B3)>1;ЕСЛИ(A3=МАКС($A$3:$A$40*($B$3:$B$40=B3));1;0);1)}

простая
Код

=СУММПРОИЗВ(--(СЧЁТЕСЛИ($B$3:$B$40;B3)=1))+СУММПРОИЗВ((СЧЁТЕСЛИ($B$3:$B$40;B3)>1)*(A3=МАКС($A$3:$A$40*($B$3:$B$40=B3))))
Автор - Udik
Дата добавления - 04.07.2016 в 14:47
Udik Дата: Понедельник, 04.07.2016, 15:00 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
Manyasha, если меньшее значение в столбце А идёт впереди большего, то могут быть сюрпризы :)
К сообщению приложен файл: 3043589.xls (31.0 Kb)

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеManyasha, если меньшее значение в столбце А идёт впереди большего, то могут быть сюрпризы :)
Автор - Udik
Дата добавления - 04.07.2016 в 15:00
Nic70y Дата: Понедельник, 04.07.2016, 15:20 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2273 ±
Замечаний: 0% ±

Excel 2010
Код
=ЕСЛИ(1-СЧЁТЕСЛИМН(A$3:A$40;">"&A3;B$3:B$40;B3);A3;0)

не, неправильно, так правильнее:
Код
=ЕСЛИ(СЧЁТЕСЛИМН(A$3:A$40;">"&A3;B$3:B$40;B3)=0;A3;0)/СЧЁТЕСЛИМН(A$3:A$40;A3;B$3:B$40;B3)
К сообщению приложен файл: 5237197-1-1-.xlsx (11.0 Kb)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Понедельник, 04.07.2016, 15:50
 
Ответить
Сообщение
Код
=ЕСЛИ(1-СЧЁТЕСЛИМН(A$3:A$40;">"&A3;B$3:B$40;B3);A3;0)

не, неправильно, так правильнее:
Код
=ЕСЛИ(СЧЁТЕСЛИМН(A$3:A$40;">"&A3;B$3:B$40;B3)=0;A3;0)/СЧЁТЕСЛИМН(A$3:A$40;A3;B$3:B$40;B3)
Автор - Nic70y
Дата добавления - 04.07.2016 в 15:20
Manyasha Дата: Понедельник, 04.07.2016, 15:32 | Сообщение № 11
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 0% ±

Excel 2010, 2016
Udik, не поняла в чем ошибка. Надо же вывести максимальное значение для повторяющихся кодов:
для кода 157 есть 2 значение
1,00
6 385 600,00

учитываем последнее, т.к. оно максимальное ))

Цитата olegus, 04.07.2016 в 14:32, в сообщении № 7
Если строк будет от 1000 до 2000 формулы сильно тормозить будут?

ну на паре тысяч вроде не должны, а вот если больше намного, то лучше массивные не использовать.

ЯД: 410013299366744 WM: R193491431804
 
Ответить
СообщениеUdik, не поняла в чем ошибка. Надо же вывести максимальное значение для повторяющихся кодов:
для кода 157 есть 2 значение
1,00
6 385 600,00

учитываем последнее, т.к. оно максимальное ))

Цитата olegus, 04.07.2016 в 14:32, в сообщении № 7
Если строк будет от 1000 до 2000 формулы сильно тормозить будут?

ну на паре тысяч вроде не должны, а вот если больше намного, то лучше массивные не использовать.
Автор - Manyasha
Дата добавления - 04.07.2016 в 15:32
Udik Дата: Понедельник, 04.07.2016, 15:39 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
Цитата Manyasha, 04.07.2016 в 15:32, в сообщении № 11
не поняла в чем ошибка

для 157 кода
напротив 1 вывело 6385600
напротив 6 385 600,00 вывело 0
Хотя общая сумма правильная :D .

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com

Сообщение отредактировал Udik - Понедельник, 04.07.2016, 15:42
 
Ответить
Сообщение
Цитата Manyasha, 04.07.2016 в 15:32, в сообщении № 11
не поняла в чем ошибка

для 157 кода
напротив 1 вывело 6385600
напротив 6 385 600,00 вывело 0
Хотя общая сумма правильная :D .
Автор - Udik
Дата добавления - 04.07.2016 в 15:39
Manyasha Дата: Понедельник, 04.07.2016, 15:43 | Сообщение № 13
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 0% ±

Excel 2010, 2016
Да, но ведь нам же по этому столбцу потом сумму считать, какая разница в какую строчку вывелось число, если оно правильное? :)
Сумма-то от этого не поменяется

UPD
Цитата Udik, 04.07.2016 в 15:39, в сообщении № 12
Хотя общая сумма правильная :D .

вот-вот))

ЯД: 410013299366744 WM: R193491431804

Сообщение отредактировал Manyasha - Понедельник, 04.07.2016, 15:43
 
Ответить
СообщениеДа, но ведь нам же по этому столбцу потом сумму считать, какая разница в какую строчку вывелось число, если оно правильное? :)
Сумма-то от этого не поменяется

UPD
Цитата Udik, 04.07.2016 в 15:39, в сообщении № 12
Хотя общая сумма правильная :D .

вот-вот))
Автор - Manyasha
Дата добавления - 04.07.2016 в 15:43
olegus Дата: Понедельник, 04.07.2016, 15:43 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Забыл сказать. В столбце B присутствуют #Н/Д и #ЗНАЧ и похоже формулы не то считают...
[moder]А почему в своем примере не показали такой вариант?
И напишите, что делать со строками, в которых ошибка, складывать или нет.[/moder]


Сообщение отредактировал Manyasha - Понедельник, 04.07.2016, 15:46
 
Ответить
СообщениеЗабыл сказать. В столбце B присутствуют #Н/Д и #ЗНАЧ и похоже формулы не то считают...
[moder]А почему в своем примере не показали такой вариант?
И напишите, что делать со строками, в которых ошибка, складывать или нет.[/moder]
Автор - olegus
Дата добавления - 04.07.2016 в 15:43
abtextime Дата: Понедельник, 04.07.2016, 15:45 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 828
Репутация: 117 ±
Замечаний: 0% ±

Excel 2010
olegus,

а мой вариант не проверяли? или Вы принципиально против UDF?
 
Ответить
Сообщениеolegus,

а мой вариант не проверяли? или Вы принципиально против UDF?
Автор - abtextime
Дата добавления - 04.07.2016 в 15:45
Udik Дата: Понедельник, 04.07.2016, 15:46 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
ответ на 13 пост
Для суммы без разницы, а вот если какие-то другие действия совершать, могут появиться интересные результаты :) .

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com

Сообщение отредактировал Udik - Понедельник, 04.07.2016, 15:52
 
Ответить
Сообщениеответ на 13 пост
Для суммы без разницы, а вот если какие-то другие действия совершать, могут появиться интересные результаты :) .
Автор - Udik
Дата добавления - 04.07.2016 в 15:46
Nic70y Дата: Понедельник, 04.07.2016, 15:48 | Сообщение № 17
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2273 ±
Замечаний: 0% ±

Excel 2010
Цитата Udik, 04.07.2016 в 15:46, в сообщении № 16
Для суммы без разницы
в смысле?

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата Udik, 04.07.2016 в 15:46, в сообщении № 16
Для суммы без разницы
в смысле?
Автор - Nic70y
Дата добавления - 04.07.2016 в 15:48
olegus Дата: Понедельник, 04.07.2016, 15:48 | Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Модератор. Дико извиняюсь. Поторопился. Формулой Еслиошибка и доп столбцом поправил. Спасибо всем!
 
Ответить
СообщениеМодератор. Дико извиняюсь. Поторопился. Формулой Еслиошибка и доп столбцом поправил. Спасибо всем!
Автор - olegus
Дата добавления - 04.07.2016 в 15:48
olegus Дата: Понедельник, 04.07.2016, 15:50 | Сообщение № 19
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
abtextime. Я не против. Я не знаю что это такое и соответственно не умею этим пользоваться. :'(
[moder]Главное - желание: что такое Udf
:) [/moder]


Сообщение отредактировал Manyasha - Понедельник, 04.07.2016, 15:56
 
Ответить
Сообщениеabtextime. Я не против. Я не знаю что это такое и соответственно не умею этим пользоваться. :'(
[moder]Главное - желание: что такое Udf
:) [/moder]
Автор - olegus
Дата добавления - 04.07.2016 в 15:50
Udik Дата: Понедельник, 04.07.2016, 15:51 | Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация: 192 ±
Замечаний: 0% ±

Excel 2016 х 64
Nic70y, это я на 13 пост отвечал

вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеNic70y, это я на 13 пост отвечал
Автор - Udik
Дата добавления - 04.07.2016 в 15:51
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование наибольших значений по условию. (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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