Суммирование по нескольким условиям в разных диапазонах

Страница 1 из 1 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Суммирование по нескольким условиям в разных диапазонах (Формулы/Formulas)

as_sa

Дата: Четверг, 28.02.2019, 13:43 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Добрый день.
В файле данные для суммирования по нескольким критериям в разных диапазонах с описанием.
Путем долгих страданий и поиска информации с примерами, удалось победить только при помощи СУММПРОИЗВ.
Но в данном случае идет привязка к данным (номера строк), которых много (больше 2500) и периодически изменяются.
Затем мне подсказали как сделать без привязки к диапазонам, но при помощи формулы массива, которая тяжела для коллег в возрасте.

Подскажите, пожалуйста, есть ли шансы сделать тоже самое, например с СУММПРОИЗВ (чтобы не нажимать Ctrl + Shift + Enter)?

К сообщению приложен файл: 3329451.xlsx(21.5 Kb)

Ответить

_Boroda_

Дата: Четверг, 28.02.2019, 14:09 | Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 14424

Репутация: 5774 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Так нужно?

Код

=СУММПРОИЗВ((ИНДЕКС($D:$J;СУММПРОИЗВ(($A$5:$A$41=$A59)*($B$10:$B$46=$B59)*СТРОКА(H$10:H$46));)>0)*ИНДЕКС($D:$J;СУММПРОИЗВ(($A$5:$A$41=$A59)*($B$5:$B$41=H$58)*СТРОКА(H$5:H$41));))

К сообщению приложен файл: 3329451_1.xlsx(21.4 Kb)

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

Ответить

as_sa

Дата: Четверг, 28.02.2019, 14:58 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

_Boroda_, Дааа! Именно так. Спасибо огромное.
А не могли бы Вы объяснить функцию формулы "СТРОКА" в данном случае, чтобы я могла перенести эту формулу в другой от исходных данных файл?
Благодарю.

Ответить

_Boroda_

Дата: Четверг, 28.02.2019, 15:09 | Сообщение № 4

Группа: Модераторы

Ранг: Местный житель

Сообщений: 14424

Репутация: 5774 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Внутри СУММПРОИЗВ два условия в скобках, если оба выполняются, то для соответствующей записи показываем номер ее строки. Поскольку совпадение может быть только одно, то складываем все полученное (куча нулей и один номер нужной строки), получаем этот самый номер. Потом Индексом вынимаем эту строку из столбцов B:J и сравниваем с нулем
Аналогично по второму СУММПРОИЗВ, но с другим условием для $B$5:$B$41
Перемножаем полученное и складываем снова с помощью СУММПРОИЗВ

Ответить

as_sa

Дата: Четверг, 28.02.2019, 15:16 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Спасибо. Буду разбираться и переносить hands

Ответить

krosav4ig

Дата: Четверг, 28.02.2019, 18:47 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1982

Репутация: 840 ±

Замечаний: 0% ±

Excel 2007,2010,2013

а я тут все с PowerQuery развлекаюсь

[vba]

Код

let
    x=(t as table) as table=>let
        Пользовательская = Table.FromRecords(Table.TransformRows(t, each let r=_, c1=_[Column1],c2=_[Column2] in Record.TransformFields(r,{{"Column2",each if c2=null then c1 else if c1<>null then null else _},{"Column1",each if c2=null then null else c1}}))),
        #"Несвернутые столбцы" = Table.UnpivotOtherColumns(Пользовательская, {"Column1", "Column2"}, "Атрибут", "Значение"),
        #"Строки с примененным фильтром" = Table.SelectRows(#"Несвернутые столбцы", each ([Column2] = null)),
        #"Объединенные запросы" = Table.NestedJoin(#"Строки с примененным фильтром",{"Атрибут"},#"Несвернутые столбцы",{"Атрибут"},"Строки с примененным фильтром",JoinKind.LeftOuter),
        #"Развернутый элемент Строки с примененным фильтром" = Table.ExpandTableColumn(#"Объединенные запросы", "Строки с примененным фильтром", {"Column2", "Значение"}, {"Column2.1", "Значение.1"}),
        #"Строки с примененным фильтром1" = Table.SelectRows(#"Развернутый элемент Строки с примененным фильтром", each ([Значение] <> 0) and ([Атрибут] <> "Column10") and ([Column2.1] <> null))
    in #"Строки с примененным фильтром1",
    Источник = Excel.CurrentWorkbook(){[Name="Данные"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Column1", type text}, {"Column2", type text}, {"Column3", type number}, {"Column4", type number}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", type any}}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Измененный тип", "Индекс", 0, 1),
    Пользовательская1 = Table.FromRecords(Table.TransformRows(#"Добавлен индекс", each Record.TransformFields(_,{{"Индекс",each if (try #"Добавлен индекс"{_+1}[Column1] otherwise "")="∑" then _ else null}}))),
    #"Заполнение вниз" = Table.FillDown(Пользовательская1,{"Индекс"}),
    #"Сгруппированные строки" = Table.Group(#"Заполнение вниз", {"Индекс"}, {{"Table", each _, type table}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Сгруппированные строки", "Пример", each [Table]{0}[Column2]),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"Индекс"}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Удаленные столбцы",{"Пример", "Table"}),
    Пользовательская3 = Table.TransformColumns(#"Переупорядоченные столбцы",{{"Table",x}}),
    #"Развернутый элемент Table" = Table.ExpandTableColumn(Пользовательская3, "Table", {"Column1", "Column2.1", "Атрибут", "Значение.1"}, {"Column1", "Column2", "Атрибут", "Значение"}),
    #"Строки с примененным фильтром" = Table.SelectRows(#"Развернутый элемент Table", each ([Атрибут] <> "Индекс") and ([Column2] <> "∑")),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Строки с примененным фильтром",{"Атрибут"})
in
    #"Удаленные столбцы1"

[/vba]

а я тут все с PowerQuery развлекаюсь

[vba]

Код