Excel это не сложно
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
28.03.2024, 19:05:03

Войти
Хотите поблагодарить участника за дельный совет? Нажмите [Повысить]. Так вы заслуженно поднимите репутацию активному участнику.
33 233 Сообщений в 5 454 Тем от 6 750 Пользователей
Последний пользователь: Alex1210
*
Перейти на сайт Хитрости Надстройка MulTEx Обучающие тренинги Наша группа ВКонтакте
Правила форума Начало Помощь Поиск Календарь Войти Регистрация Выйти
+  Excel это не сложно
|-+  Основные форумы
| |-+  Вопросы по Excel и VBA
| | |-+  Суммирование по нескольким условиям в разных диапазонах
Страниц: [1]   Вниз
Печать
Автор Тема: Суммирование по нескольким условиям в разных диапазонах  (Прочитано 3420 раз)
0 Пользователей и 1 Гость смотрят эту тему.
as_sa
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 5


Просмотр профиля E-mail
« : 16.04.2019, 10:53:11 »

Добрый день.
Есть большой рабочий файл, в котором надо суммировать значения по нескольким условиям.
В данный момент есть две формулы - СУММ и СУММПРОИЗВ, но обе суммируют только значения по первому совпадению.  Грустный
НО в столбце В могут неоднократно повторяться условия формулы (например, несколько раз СМР) и количество строк может быть разное у объектов.
Помогите, пожалуйста, добавить суммирование всех совпадений (в идеале без ввода формулы массива Ctrl + Shift + Enter) любым доступным способом (формулой).  Плачущий
Добавила значения в Пример1 (красным), которые не попадают в суммирование на данный момент, а должны попадать.
Спасибо!
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 828



Просмотр профиля WWW
« Ответ #1 : 16.04.2019, 11:07:27 »

Не совсем понятны условия. Как в одной строке может быть и ПИР и МБ, например? Ведь они в разных строках.
Даже если вчитаться в условия и предположить, что ПИР и МБ и должны быть в разных строках - непонятна связь с результатом. Например, почему для МБ в Пример 789,75? Откуда эта цифра, если учесть, что для ПИР в Пример нет вообще ни одной строки?
Теоретически, исходя из условия - можно было бы применить такую формулу массива:
=ЕСЛИ(СУММ(ЕСЛИ(($B$5:$B$51=$B59)*($A$5:$A$51=$A59);$K$5:$K$51;0))>0;СУММ(ЕСЛИ(($B$5:$B$51=J$58)*($A$5:$A$51=$A59);$K$5:$K$51;0));0)
Но: нигде не написано, надо искать частичное совпадение по "пример*" или полное? Если частичное - то пример сам по себе неудачный и реального решения не имеет, т.к. для строки с "Пример" будут найдены ВСЕ остальные "примеры".
« Последнее редактирование: 16.04.2019, 11:17:58 от Дмитрий Щербаков(The_Prist) » Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
as_sa
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 5


Просмотр профиля E-mail
« Ответ #2 : 16.04.2019, 12:01:08 »

Распишу подробно на первом примере.
Есть объект (Пример1 - строки 4:19), у которого есть n-ное количество контрактов (ПИР, СМР, ОБОР, могут повторяться эти названия - строки 11:19).
Сумма каждого контракта может быть разбита выше на 3 строки (или меньше) - ФБ, ОБ и МБ (строки 6:8 ).
Значит если у объекта "Пример1" (столбец А) есть контракты ПИР (столбец В),  у которых есть числа (D11:J19 больше 0 и/или не пусто?!), то необходимо суммировать строку МБ (D8:J8) - получаем 398,91886 + 390,82958 = 789,75 (всё правильно считает).

НО если у того же объекта есть контракты СМР 3 штуки, то суммирование происходит только по первому условию  Плачущий , а не по всем 3 (красные не попадают)
В этом и нужна очень помощь.

Старалась понятно объяснить )
P.S. столбец К не участвует в формулах.

Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 828



Просмотр профиля WWW
« Ответ #3 : 16.04.2019, 15:08:47 »

если у того же объекта есть контракты СМР 3 штуки, то суммирование происходит только по первому условию
это как? Я выше предложил формулу - она суммирует ВСЕ. Вопрос в том, что у Вас для Пример1 такого нет, а остальные названы "Пример3", пример31, 32 и т.д. Т.е. они самостоятельные или относятся к Пример3?
Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
as_sa
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 5


Просмотр профиля E-mail
« Ответ #4 : 16.04.2019, 16:07:31 »

Почему нет? у Пример1 есть три контракта СМР - строки 12,15 и 17.
Сейчас в строке 8 суммируются значения, только при значениях >0 в строке 12, а до 15 и 17 не доходит формула, хотя >0 там есть.
Это я про свои формулы СУММ и СУММПРОИЗВ.

А Ваша формула мне показывает 0  В замешательстве
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 828



Просмотр профиля WWW
« Ответ #5 : 16.04.2019, 16:44:19 »

у Пример1 есть три контракта СМР - строки 12,15 и 17
А мы точно один и тот же пример смотрим? я брал за основу самый первый файл - гляньте сколько там строк с "Пример1"? Ровно 1 - ПИР.
Смотрим второй файл: там для "Пример1" есть строки ФБ, ОБ, МБ, Пр, но нет ни одной строки ПИР.
Там не "Пример1", а "Пример12", "Пример15" и "Пример17". Вы понимаете, что это разные значения? И вопрос такой я задавал - что с ними делать и как быть, если будет и "Пример1" как основной и "Пример11"(внутри которого будут уже "Пример111", "Пример112" и т.д.). Думали над этим?
И что делать? Какое описание надо смотреть, какие вопросы еще задать, чтобы ответ был на них дан так, как написан пример? Если хоть одна строка будет отвечать написанным условиям - то все будет считаться. Вот формула(массива - Ctrl+Shift+Enter):
=ЕСЛИ(СУММ(ЕСЛИ(($B$5:$B$51=$B59)*($A$5:$A$51=$A59);$K$5:$K$51;0))>0;СУММ(ЕСЛИ(($B$5:$B$51=J$58)*($A$5:$A$51=$A59);$D$5:$J$51;0));0)

она нормально все считает, если условия будут соблюдаться. Я просто в строке 11 "Пример11" заменил на "Пример1"(чтобы хоть ПИР для "Пример1" был). При этом и "СМР", как я вижу, тоже не всегда "СМР", а иногда очень даже "СМР1". Это тоже часть плана по недомолвке условий?
Да, можно сделать такую формулу:
=ЕСЛИ(СУММ(ЕСЛИ(($B$5:$B$51=$B59)*(ПСТР($A$5:$A$51;1;ДЛСТР($A59))=$A59);$D$5:$J$51;0))>0;СУММ(ЕСЛИ(($B$5:$B$51=J$58)*($A$5:$A$51=$A59);$D$5:$J$51;0));0)

Но и тут вопрос: а нафига тогда столько ячеек? Ведь судя по условию, если нашли ПИР - суммируем строки с МБ. Если нашли СМР, то...Правильно - суммируем строки с МБ. Где логика?

столбец К не участвует в формулах
это требование? Зачем он тогда нужен? Он же суммирует именно нужные данные. Его потом не будет?


Итого, давайте вчитаемся хотя бы в описанное условие:
Цитировать
Если у объекта "Пример" (столбец А) есть контракты ПИР (столбец В), у которого есть числа (D11:J19 больше 0 и/или не пусто?!), то необходимо суммировать  строку МБ (D8:J8)
А что делать с СМР, которых несколько? И почему ИХ надо суммировать, когда они вроде как относятся к части условия ПИР - т.е. если в СМР есть ненулевые суммы(хоть где-то) - суммируем МБ.
Как видно - Ваши описания никак не сходятся с задачей. Значит надо начать сначала и попытаться описать задачу так, как она должна звучать. Я пока не понимаю что Вы хотите с такими требованиями, описаниями и примером.


P.S. Приложил пример с формулой
« Последнее редактирование: 16.04.2019, 16:47:42 от Дмитрий Щербаков(The_Prist) » Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
as_sa
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 5


Просмотр профиля E-mail
« Ответ #6 : 17.04.2019, 12:22:53 »

Дмитрий, добрый день.
Извиняюсь за хаотичное описание задачи - цели ввести в заблуждение не было и нет.
Давайте попробую с самого начала:
Есть объект Пример1, у которого есть контракты (может быть любое количество в зависимости от объекта) - Пример11, Пример12, Пример13, Пример14 и т.д. (к Пример1 добавляются номера контрактов по очереди) - они имеют признаки ПИР, СМР, ОБОР.

Вы понимаете, что это разные значения?
Понимаю конечно, но на этом не заостряла внимание, потому что это решаемо путем подстановки &"*" к Пример1 (Пример1&"*" - это как раз любой номер контракта) . Это видно в моей формуле СУММ (СУММПРОИЗВ вообще это пропускает).
В Вашей формуле ЕСЛИ такой фокус не удается у меня, к сожалению ((( Соответственно такая же ситуация и с СМР и СМР1.

Но и тут вопрос: а нафига тогда столько ячеек? Ведь судя по условию, если нашли ПИР - суммируем строки с МБ. Если нашли СМР, то...Правильно - суммируем строки с МБ. Где логика?
Здесь главное недопонимание: если мы нашли ПИР, то суммируем не всю строку МБ, а только ячейки строки МБ, под которыми есть значения >0 в строке ПИР ! (нарисовала стрелочки в файле) Строит глазки Еще раз извините  В замешательстве
398,91886+395,82958=789,75
А сумма строки ОБ для ПИР будет всего 5,00 !

Сейчас в обеих моих формулах правильное суммирование, но только для контрактов, где признак не повторяется (это как раз ПИР - он один).
А вот СМР и ОБОР по несколько контрактов, тогда формула находит только первую строку СМР или ОБОР, а остальные с теми же признаками >0 в условие не попадают и соответственно ячейки в суммирование.
Для этого и добавила вручную числа, которые должны получится ( "Должно быть" ) в результате.

Спасибо.
Записан
as_sa
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 5


Просмотр профиля E-mail
« Ответ #7 : 19.04.2019, 11:02:23 »

И тишина ...  Непонимающий Плачущий
Записан
Страниц: [1]   Вверх
Печать
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2011, Simple Machines Valid XHTML 1.0! Valid CSS!
Яндекс.Метрика Рейтинг@Mail.ru