jakim
Дата: Четверг, 20.07.2023, 08:42 |
Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1210
Репутация:
314
±
Замечаний:
0% ±
Excel 2010
Power Query
[vba]Код
let Source = Table.NestedJoin(Table2,{"Должность"},Table1,{"Должность"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Номенклатура"}, {"Номенклатура"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([Номенклатура] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ФИО", "Должность"}, {{"Count", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Номенклатура]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ". "), type text}) in #"Extracted Values"
[/vba]
Power Query
[vba]Код
let Source = Table.NestedJoin(Table2,{"Должность"},Table1,{"Должность"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Номенклатура"}, {"Номенклатура"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([Номенклатура] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ФИО", "Должность"}, {{"Count", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Номенклатура]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ". "), type text}) in #"Extracted Values"
[/vba]jakim
Ответить
Сообщение Power Query
[vba]Код
let Source = Table.NestedJoin(Table2,{"Должность"},Table1,{"Должность"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Номенклатура"}, {"Номенклатура"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([Номенклатура] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ФИО", "Должность"}, {{"Count", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Номенклатура]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ". "), type text}) in #"Extracted Values"
[/vba]Автор - jakim Дата добавления - 20.07.2023 в 08:42
jakim
Дата: Четверг, 20.07.2023, 09:16 |
Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1210
Репутация:
314
±
Замечаний:
0% ±
Excel 2010
Так?
[vba]Код
let Source = Table.NestedJoin(Table2,{"Должность"},Table1,{"Должность"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Номенклатура"}, {"Номенклатура"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([Номенклатура] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ФИО", "Должность"}, {{"Count", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Номенклатура]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values","Custom",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ФИО", "Должность"}, "Attribute", "Value"), #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Value", "Номенклатура"}}) in #"Renamed Columns"
[/vba]
Так?
[vba]Код
let Source = Table.NestedJoin(Table2,{"Должность"},Table1,{"Должность"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Номенклатура"}, {"Номенклатура"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([Номенклатура] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ФИО", "Должность"}, {{"Count", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Номенклатура]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values","Custom",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ФИО", "Должность"}, "Attribute", "Value"), #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Value", "Номенклатура"}}) in #"Renamed Columns"
[/vba]jakim
Ответить
Сообщение Так?
[vba]Код
let Source = Table.NestedJoin(Table2,{"Должность"},Table1,{"Должность"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Номенклатура"}, {"Номенклатура"}), #"Filtered Rows" = Table.SelectRows(#"Expanded NewColumn", each ([Номенклатура] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"ФИО", "Должность"}, {{"Count", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][Номенклатура]), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values","Custom",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ФИО", "Должность"}, "Attribute", "Value"), #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Value", "Номенклатура"}}) in #"Renamed Columns"
[/vba]Автор - jakim Дата добавления - 20.07.2023 в 09:16