as_sa
Дата: Четверг, 28.02.2019, 13:43 |
Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
Добрый день. В файле данные для суммирования по нескольким критериям в разных диапазонах с описанием. Путем долгих страданий и поиска информации с примерами, удалось победить только при помощи СУММПРОИЗВ. Но в данном случае идет привязка к данным (номера строк), которых много (больше 2500) и периодически изменяются. Затем мне подсказали как сделать без привязки к диапазонам, но при помощи формулы массива, которая тяжела для коллег в возрасте. Подскажите, пожалуйста, есть ли шансы сделать тоже самое, например с СУММПРОИЗВ (чтобы не нажимать Ctrl + Shift + Enter)?
Добрый день. В файле данные для суммирования по нескольким критериям в разных диапазонах с описанием. Путем долгих страданий и поиска информации с примерами, удалось победить только при помощи СУММПРОИЗВ. Но в данном случае идет привязка к данным (номера строк), которых много (больше 2500) и периодически изменяются. Затем мне подсказали как сделать без привязки к диапазонам, но при помощи формулы массива, которая тяжела для коллег в возрасте. Подскажите, пожалуйста, есть ли шансы сделать тоже самое, например с СУММПРОИЗВ (чтобы не нажимать Ctrl + Shift + Enter)? as_sa
Ответить
Сообщение Добрый день. В файле данные для суммирования по нескольким критериям в разных диапазонах с описанием. Путем долгих страданий и поиска информации с примерами, удалось победить только при помощи СУММПРОИЗВ. Но в данном случае идет привязка к данным (номера строк), которых много (больше 2500) и периодически изменяются. Затем мне подсказали как сделать без привязки к диапазонам, но при помощи формулы массива, которая тяжела для коллег в возрасте. Подскажите, пожалуйста, есть ли шансы сделать тоже самое, например с СУММПРОИЗВ (чтобы не нажимать Ctrl + Shift + Enter)? Автор - as_sa Дата добавления - 28.02.2019 в 13:43
_Boroda_
Дата: Четверг, 28.02.2019, 15:09 |
Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 16895
Репутация:
6613
±
Замечаний:
±
2003; 2007; 2010; 2013 RUS
Внутри СУММПРОИЗВ два условия в скобках, если оба выполняются, то для соответствующей записи показываем номер ее строки. Поскольку совпадение может быть только одно, то складываем все полученное (куча нулей и один номер нужной строки), получаем этот самый номер. Потом Индексом вынимаем эту строку из столбцов B:J и сравниваем с нулем Аналогично по второму СУММПРОИЗВ, но с другим условием для $B$5:$B$41 Перемножаем полученное и складываем снова с помощью СУММПРОИЗВ
Внутри СУММПРОИЗВ два условия в скобках, если оба выполняются, то для соответствующей записи показываем номер ее строки. Поскольку совпадение может быть только одно, то складываем все полученное (куча нулей и один номер нужной строки), получаем этот самый номер. Потом Индексом вынимаем эту строку из столбцов B:J и сравниваем с нулем Аналогично по второму СУММПРОИЗВ, но с другим условием для $B$5:$B$41 Перемножаем полученное и складываем снова с помощью СУММПРОИЗВ _Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение Внутри СУММПРОИЗВ два условия в скобках, если оба выполняются, то для соответствующей записи показываем номер ее строки. Поскольку совпадение может быть только одно, то складываем все полученное (куча нулей и один номер нужной строки), получаем этот самый номер. Потом Индексом вынимаем эту строку из столбцов B:J и сравниваем с нулем Аналогично по второму СУММПРОИЗВ, но с другим условием для $B$5:$B$41 Перемножаем полученное и складываем снова с помощью СУММПРОИЗВ Автор - _Boroda_ Дата добавления - 28.02.2019 в 15:09
krosav4ig
Дата: Четверг, 28.02.2019, 18:47 |
Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2348
Репутация:
997
±
Замечаний:
0% ±
Excel 2007,2010,2013
а я тут все с PowerQuery развлекаюсь
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"
а я тут все с PowerQuery развлекаюсь
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"
krosav4ig
email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
Ответить
Сообщение а я тут все с PowerQuery развлекаюсь
[vba]
let x =(t as table ) as table =>let Пользовательская = Тable.FromRecords(Тable.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 }}))); Пользовательская "Несвернутые столбцы" = Тable.UnpivotOtherColumns(each ; {"Column1"; "Column2"}; "Атрибут"; "Значение"); Column2 "Строки с примененным фильтром" = Тable.SelectRows(null "Несвернутые столбцы"; JoinKind.LeftOuter ([each ] = Значение )); and "Объединенные запросы" = Тable.NestedJoin(Атрибут "Строки с примененным фильтром";{"Атрибут"};and "Несвернутые столбцы";{"Атрибут"};"Строки с примененным фильтром";Column2.1 ); null "Развернутый элемент Строки с примененным фильтром" = Тable.ExpandТableColumn(in "Объединенные запросы"; "Строки с примененным фильтром"; {"Column2"; "Значение"}; {"Column2.1"; "Значение.1"}); Источник "Строки с примененным фильтром1" = Тable.SelectRows(Name "Развернутый элемент Строки с примененным фильтром"; Content ([Источник ] <> 0) type ([text ] <> "Column10") type ([text ] <> type )) number type "Строки с примененным фильтром1"; number = Excel.CurrentWorkbook(){[Int64 ="Данные"]}[Type ]; Int64 "Измененный тип" = Тable.TransformColumnТypes(Type ;{{"Column1"; Int64 Type }; {"Column2"; Int64 Type }; {"Column3"; Int64 Type }; {"Column4"; type any }; {"Column5"; Пользовательская1 .each }; {"Column6"; _ .each }; {"Column7"; if .try }; {"Column8"; _ .Column1 }; {"Column9"; otherwise .then }; {"Column10"; _ else }}); null "Добавлен индекс" = Тable.AddIndexColumn(Пользовательская1 "Измененный тип"; "Индекс"; 0; 1); each = Тable.FromRecords(Тable.TransformRows(_ "Добавлен индекс"; type Record.TransformFields(table ;{{"Индекс";each Table (Column2 Пользовательская3 "Добавлен индекс"{x +1}[Пользовательская3 ] each "")="∑" Атрибут and Column2 in }}))); undefined"Заполнение вниз" = Тable.FillDown(undefined;{"Индекс"}); undefined"Сгруппированные строки" = Тable.Group(undefined"Заполнение вниз"; {"Индекс"}; {{"Тable"; undefined undefined; undefined undefined}}); undefined"Добавлен пользовательский объект" = Тable.AddColumn(undefined"Сгруппированные строки"; "Пример"; undefined [undefined]{0}[undefined]); undefined"Удаленные столбцы" = Тable.RemoveColumns(undefined"Добавлен пользовательский объект";{"Индекс"}); undefined"Переупорядоченные столбцы" = Тable.ReorderColumns(undefined"Удаленные столбцы";{"Пример"; "Тable"}); undefined = Тable.TransformColumns(undefined"Переупорядоченные столбцы";{{"Тable";undefined}}); undefined"Развернутый элемент Тable" = Тable.ExpandТableColumn(undefined; "Тable"; {"Column1"; "Column2.1"; "Атрибут"; "Значение.1"}; {"Column1"; "Column2"; "Атрибут"; "Значение"}); undefined"Строки с примененным фильтром" = Тable.SelectRows(undefined"Развернутый элемент Тable"; undefined ([undefined] <> "Индекс") undefined ([undefined] <> "∑")); undefined"Удаленные столбцы1" = Тable.RemoveColumns(undefined"Строки с примененным фильтром";{"Атрибут"}) undefined undefined"Удаленные столбцы1"
[/vba]
Автор - krosav4ig Дата добавления - 28.02.2019 в 18:47
as_sa
Дата: Четверг, 04.04.2019, 13:34 |
Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация:
0
±
Замечаний:
0% ±
Excel 2016
_Boroda_ , Добрый день. Прошу еще раз помощи Всё в том же файле я не учла что могут неоднократно повторяться условия формулы в столбце В и количество строк может быть разное у объектов. В данный момент суммируются только значения по первому совпадению (а если повторы идут подряд, например, СМР СМР СМР, то выдает #ССЫЛКА) . Помогите, пожалуйста, добавить суммирование всех совпадений. Добавила значения в Пример1 (красным). Спасибо!
_Boroda_ , Добрый день. Прошу еще раз помощи Всё в том же файле я не учла что могут неоднократно повторяться условия формулы в столбце В и количество строк может быть разное у объектов. В данный момент суммируются только значения по первому совпадению (а если повторы идут подряд, например, СМР СМР СМР, то выдает #ССЫЛКА) . Помогите, пожалуйста, добавить суммирование всех совпадений. Добавила значения в Пример1 (красным). Спасибо!as_sa
Сообщение отредактировал as_sa - Четверг, 04.04.2019, 14:23
Ответить
Сообщение _Boroda_ , Добрый день. Прошу еще раз помощи Всё в том же файле я не учла что могут неоднократно повторяться условия формулы в столбце В и количество строк может быть разное у объектов. В данный момент суммируются только значения по первому совпадению (а если повторы идут подряд, например, СМР СМР СМР, то выдает #ССЫЛКА) . Помогите, пожалуйста, добавить суммирование всех совпадений. Добавила значения в Пример1 (красным). Спасибо!Автор - as_sa Дата добавления - 04.04.2019 в 13:34