Суммирование наибольших значений по условию.
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 обновляются из других книг и имеют числовой формат. Заранее спасибо.
Добрый день форумчане! Прошу помощи. В ячейке A2 нужна формула, которая суммирует наибольшие значения из столбца A по критерию из столбца B. Например: помимо остальных значений в столбце A по кодам 157 и 211 нужно суммировать значения 6 385 600,00 и 123 491 300,00, игнорируя значения 53 980,00 и 1 942 057,25. В оригинале столбцы A и B обновляются из других книг и имеют числовой формат. Заранее спасибо. olegus
Ответить
Сообщение Добрый день форумчане! Прошу помощи. В ячейке 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))
массивнаяКод
=МАКС(ЕСЛИ(B3:B40=157;A3:A40))+МАКС(ЕСЛИ(B3:B40=211;A3:A40))
немассивнаяКод
=СУММПРОИЗВ(МАКС((B3:B40=157)*A3:A40)+МАКС((B3:B40=211)*A3:A40))
Nic70y
Ю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
Ответить
Сообщение 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".
olegus , я так поняла, что по уникальным кодам тоже нужно суммировать. Так подойдет (массивная)?Код
=МАКС(ЕСЛИ($B$3:$B$40=B3;$A$3:$A$40))*(СЧЁТЕСЛИ($B$2:B3;B3)=1)
Если нужно суммировать только максимумы по повторам, то замените в конце формулы "=1" на ">1".Manyasha
ЯД: 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]
Как понял (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
Сообщение отредактировал 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
сводная, фильтруйте
Ю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
Ответить
Сообщение Спасибо 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))))
Вариант с доп.столбцом. В нём флаг выводит, хотя можно и значение. массивкаКод
{=ЕСЛИ(СЧЁТЕСЛИ($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
К сообщению приложен файл:
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 , если меньшее значение в столбце А идёт впереди большего, то могут быть сюрпризы
Manyasha , если меньшее значение в столбце А идёт впереди большего, то могут быть сюрпризы Udik
вот вам барабан яд 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)
Код
=ЕСЛИ(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
Ю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 учитываем последнее, т.к. оно максимальное ))Если строк будет от 1000 до 2000 формулы сильно тормозить будут?
ну на паре тысяч вроде не должны, а вот если больше намного, то лучше массивные не использовать.
Udik , не поняла в чем ошибка. Надо же вывести максимальное значение для повторяющихся кодов: для кода 157 есть 2 значение 1,00 6 385 600,00 учитываем последнее, т.к. оно максимальное ))Если строк будет от 1000 до 2000 формулы сильно тормозить будут?
ну на паре тысяч вроде не должны, а вот если больше намного, то лучше массивные не использовать.Manyasha
ЯД: 410013299366744 WM: R193491431804
Ответить
Сообщение Udik , не поняла в чем ошибка. Надо же вывести максимальное значение для повторяющихся кодов: для кода 157 есть 2 значение 1,00 6 385 600,00 учитываем последнее, т.к. оно максимальное ))Если строк будет от 1000 до 2000 формулы сильно тормозить будут?
ну на паре тысяч вроде не должны, а вот если больше намного, то лучше массивные не использовать.Автор - Manyasha Дата добавления - 04.07.2016 в 15:32
Udik
Дата: Понедельник, 04.07.2016, 15:39 |
Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация:
192
±
Замечаний:
0% ±
Excel 2016 х 64
для 157 кода напротив 1 вывело 6385600 напротив 6 385 600,00 вывело 0 Хотя общая сумма правильная .
для 157 кода напротив 1 вывело 6385600 напротив 6 385 600,00 вывело 0 Хотя общая сумма правильная .Udik
вот вам барабан яд 41001231307558 wm R419131876897 udik1968@gmail.com
Сообщение отредактировал Udik - Понедельник, 04.07.2016, 15:42
Ответить
Сообщение для 157 кода напротив 1 вывело 6385600 напротив 6 385 600,00 вывело 0 Хотя общая сумма правильная .Автор - Udik Дата добавления - 04.07.2016 в 15:39
Manyasha
Дата: Понедельник, 04.07.2016, 15:43 |
Сообщение № 13
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация:
898
±
Замечаний:
0% ±
Excel 2010, 2016
Да, но ведь нам же по этому столбцу потом сумму считать, какая разница в какую строчку вывелось число, если оно правильное? :) Сумма-то от этого не поменяетсяUPD Хотя общая сумма правильная
.
вот-вот))
Да, но ведь нам же по этому столбцу потом сумму считать, какая разница в какую строчку вывелось число, если оно правильное? :) Сумма-то от этого не поменяетсяUPD Хотя общая сумма правильная
.
вот-вот)) Manyasha
ЯД: 410013299366744 WM: R193491431804
Сообщение отредактировал Manyasha - Понедельник, 04.07.2016, 15:43
Ответить
Сообщение Да, но ведь нам же по этому столбцу потом сумму считать, какая разница в какую строчку вывелось число, если оно правильное? :) Сумма-то от этого не поменяетсяUPD Хотя общая сумма правильная
.
вот-вот)) Автор - Manyasha Дата добавления - 04.07.2016 в 15:43
olegus
Дата: Понедельник, 04.07.2016, 15:43 |
Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
Забыл сказать. В столбце B присутствуют #Н/Д и #ЗНАЧ и похоже формулы не то считают... [moder]А почему в своем примере не показали такой вариант? И напишите, что делать со строками, в которых ошибка, складывать или нет.[/moder]
Забыл сказать. В столбце B присутствуют #Н/Д и #ЗНАЧ и похоже формулы не то считают... [moder]А почему в своем примере не показали такой вариант? И напишите, что делать со строками, в которых ошибка, складывать или нет.[/moder] olegus
Сообщение отредактировал 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
Ответить
Сообщение olegus, а мой вариант не проверяли? или Вы принципиально против UDF?Автор - abtextime Дата добавления - 04.07.2016 в 15:45
Udik
Дата: Понедельник, 04.07.2016, 15:46 |
Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация:
192
±
Замечаний:
0% ±
Excel 2016 х 64
ответ на 13 пост Для суммы без разницы, а вот если какие-то другие действия совершать, могут появиться интересные результаты .
ответ на 13 пост Для суммы без разницы, а вот если какие-то другие действия совершать, могут появиться интересные результаты . Udik
вот вам барабан яд 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
ЮMoney 41001841029809
Ответить
Сообщение в смысле? Автор - Nic70y Дата добавления - 04.07.2016 в 15:48
olegus
Дата: Понедельник, 04.07.2016, 15:48 |
Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
Модератор. Дико извиняюсь. Поторопился. Формулой Еслиошибка и доп столбцом поправил. Спасибо всем!
Модератор. Дико извиняюсь. Поторопился. Формулой Еслиошибка и доп столбцом поправил. Спасибо всем! olegus
Ответить
Сообщение Модератор. Дико извиняюсь. Поторопился. Формулой Еслиошибка и доп столбцом поправил. Спасибо всем! Автор - olegus Дата добавления - 04.07.2016 в 15:48
olegus
Дата: Понедельник, 04.07.2016, 15:50 |
Сообщение № 19
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
abtextime. Я не против. Я не знаю что это такое и соответственно не умею этим пользоваться. :'( [moder]Главное - желание: что такое Udf [/moder]
abtextime. Я не против. Я не знаю что это такое и соответственно не умею этим пользоваться. :'( [moder]Главное - желание: что такое Udf [/moder] olegus
Сообщение отредактировал 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 пост отвечал
Nic70y , это я на 13 пост отвечалUdik
вот вам барабан яд 41001231307558 wm R419131876897 udik1968@gmail.com
Ответить
Сообщение Nic70y , это я на 13 пост отвечалАвтор - Udik Дата добавления - 04.07.2016 в 15:51