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

Вход

Регистрация

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

 

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

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 212»
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование наибольших значений по условию. (Формулы/Formulas)
Суммирование наибольших значений по условию.
olegus Дата: Понедельник, 04.07.2016, 13:40 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 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(26Kb)
 
Ответить
СообщениеДобрый день форумчане!
Прошу помощи.
В ячейке 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

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


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениемассивная
Код
=МАКС(ЕСЛИ(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
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 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
Группа: Модераторы
Ранг: Старожил
Сообщений: 1590
Репутация: 669 ±
Замечаний: 0% ±

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


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


marinamorozova_box@mail.ru
ЯД: 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 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(16Kb)


Сообщение отредактировал 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

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


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениесводная, фильтруйте

Автор - Nic70y
Дата добавления - 04.07.2016 в 14:25
olegus Дата: Понедельник, 04.07.2016, 14:32 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 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
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

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

{=ЕСЛИ(СЧЁТЕСЛИ($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(32Kb)


вот вам барабан
яд 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
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

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


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеManyasha, если меньшее значение в столбце А идёт впереди большего, то могут быть сюрпризы :)

Автор - Udik
Дата добавления - 04.07.2016 в 15:00
Nic70y Дата: Понедельник, 04.07.2016, 15:20 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
Код
=ЕСЛИ(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(11Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал 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
Группа: Модераторы
Ранг: Старожил
Сообщений: 1590
Репутация: 669 ±
Замечаний: 0% ±

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

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

Если строк будет от 1000 до 2000 формулы сильно тормозить будут?

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


marinamorozova_box@mail.ru
ЯД: 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
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
не поняла в чем ошибка

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


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


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

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

Автор - Udik
Дата добавления - 04.07.2016 в 15:39
Manyasha Дата: Понедельник, 04.07.2016, 15:43 | Сообщение № 13
Группа: Модераторы
Ранг: Старожил
Сообщений: 1590
Репутация: 669 ±
Замечаний: 0% ±

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

UPD
Хотя общая сумма правильная :D .

вот-вот))


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804


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

UPD
Хотя общая сумма правильная :D .

вот-вот))

Автор - Manyasha
Дата добавления - 04.07.2016 в 15:43
olegus Дата: Понедельник, 04.07.2016, 15:43 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 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
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
olegus,

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

а мой вариант не проверяли? или Вы принципиально против UDF?

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

Excel 2013
ответ на 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
Для суммы без разницы
в смысле?


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
Для суммы без разницы
в смысле?

Автор - Nic70y
Дата добавления - 04.07.2016 в 15:48
olegus Дата: Понедельник, 04.07.2016, 15:48 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Модератор. Дико извиняюсь. Поторопился. Формулой Еслиошибка и доп столбцом поправил. Спасибо всем!
 
Ответить
СообщениеМодератор. Дико извиняюсь. Поторопился. Формулой Еслиошибка и доп столбцом поправил. Спасибо всем!

Автор - olegus
Дата добавления - 04.07.2016 в 15:48
olegus Дата: Понедельник, 04.07.2016, 15:50 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 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
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

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


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеNic70y, это я на 13 пост отвечал

Автор - Udik
Дата добавления - 04.07.2016 в 15:51
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование наибольших значений по условию. (Формулы/Formulas)
Страница 1 из 212»
Поиск:

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