| 
 
 jakim | 
 Дата: Понедельник, 24.10.2022, 14:11 | 
 Сообщение № 2 |   
   
 | 
  |  
 
  
 Группа: Друзья 
  
 Ранг: Старожил 
 
 Сообщений: 1218  
 
 
 
  Репутация:  
 316  
 ±
 
  
 Замечаний:
 0%  ±  
   Excel 2010       |       | 
 
 
 Power Query  [vba]Код let     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],     #"Merged Columns" = Table.CombineColumns(Source,{"Путь", "Column1"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged"),     #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column2", "Column3"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged.1"),     #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged.2"),     #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns2",{{"Время выезда", type date}, {"Время прибытия", type date}}),     #"Grouped Rows" = Table.Group(#"Changed Type1", {"Авто", "Merged.2"}, {{"min", each List.Min([Время выезда]), type date}, {"max", each List.Max([Время прибытия]), type date}}),     #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [max]-[min]),     #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),     #"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"min", type text}, {"max", type text}}, "lt-LT"),{"min", "max"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),     #"Added Prefix" = Table.TransformColumns(#"Merged Columns3", {{"Merged", each "(" & _, type text}}),     #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Merged", each _ & ")", type text}}),     #"Merged Columns4" = Table.CombineColumns(#"Added Suffix",{"Merged.2", "Merged"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),     #"Renamed Columns" = Table.RenameColumns(#"Merged Columns4",{{"Custom", "Кол. Дней"}, {"Merged.1", "Путь"}}) in     #"Renamed Columns"  [/vba] 
 
 
Power Query  [vba]Код let     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],     #"Merged Columns" = Table.CombineColumns(Source,{"Путь", "Column1"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged"),     #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column2", "Column3"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged.1"),     #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Merged", "Merged.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged.2"),     #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns2",{{"Время выезда", type date}, {"Время прибытия", type date}}),     #"Grouped Rows" = Table.Group(#"Changed Type1", {"Авто", "Merged.2"}, {{"min", each List.Min([Время выезда]), type date}, {"max", each List.Max([Время прибытия]), type date}}),     #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [max]-[min]),     #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),     #"Merged Columns3" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"min", type text}, {"max", type text}}, "lt-LT"),{"min", "max"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),     #"Added Prefix" = Table.TransformColumns(#"Merged Columns3", {{"Merged", each "(" & _, type text}}),     #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Merged", each _ & ")", type text}}),     #"Merged Columns4" = Table.CombineColumns(#"Added Suffix",{"Merged.2", "Merged"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),     #"Renamed Columns" = Table.RenameColumns(#"Merged Columns4",{{"Custom", "Кол. Дней"}, {"Merged.1", "Путь"}}) in     #"Renamed Columns"  [/vba]jakim 
 
 
 
 Сообщение отредактировал jakim - Понедельник, 24.10.2022, 14:12  |  
|   | 
  |    Ответить
  |