Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Подставить значения в ячейку с разделителем из массива. - Страница 2 - Мир MS Excel

Старая форма входа
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подставить значения в ячейку с разделителем из массива. (Формулы/Formulas)
Подставить значения в ячейку с разделителем из массива.
Egyptian Дата: Вторник, 02.08.2022, 14:49 | Сообщение № 21
Группа: Проверенные
Ранг: Обитатель
Сообщений: 372
Репутация: 126 ±
Замечаний: 0% ±

Excel 2013/2016
scriptapplications, Видимо дело все-таки в версиях Excel. Судя по всему в версиях вплоть до 2019 функция INDEX просто не принимает массив строк, (к примеру для первой строки {2;3;4;5;6;7;8;9}), который подсовывает ей MATCH. Для того чтобы объяснить INDEX-у что так делать не следует, можно прибегнуть к некоему трюку, например такому:
Код
=TEXTJOIN(";";1;IFERROR(INDEX($A$2:$A$59;N(INDEX(MATCH(TRIM(MID( C2; FIND( CHAR( 3 ); SUBSTITUTE( ";"&C2; ";"; CHAR(3); ROW( INDIRECT( "1:"&LEN( C2 ) - LEN( SUBSTITUTE( C2; ";"; "" )) + 1)))); FIND( ";"; ";"&C2&";"; FIND( CHAR( 3 ); SUBSTITUTE( ";"&C2; ";"; CHAR( 3 ); ROW( INDIRECT( "1:"&LEN( C2 ) - LEN( SUBSTITUTE( C2; ";"; "")) + 1)))) + 1) - FIND( CHAR( 3 ); SUBSTITUTE( ";"&C2; ";"; CHAR(3); ROW( INDIRECT( "1:"&LEN( C2 ) - LEN( SUBSTITUTE( C2; ";"; "")) + 1)))) - 1));$B$2:$B$59;0);)));""))

Теперь, если выделить MATCH обернутый в N(INDEX...) и нажать F9 но можно опять увидеть тот же самый массив строк {2;3;4;5;6;7;8;9} (ну как тут не вспомнить анекдот про суслика), который, INDEX принимает и передает функции TEXTJOIN, как массив значений.
 
Ответить
Сообщениеscriptapplications, Видимо дело все-таки в версиях Excel. Судя по всему в версиях вплоть до 2019 функция INDEX просто не принимает массив строк, (к примеру для первой строки {2;3;4;5;6;7;8;9}), который подсовывает ей MATCH. Для того чтобы объяснить INDEX-у что так делать не следует, можно прибегнуть к некоему трюку, например такому:
Код
=TEXTJOIN(";";1;IFERROR(INDEX($A$2:$A$59;N(INDEX(MATCH(TRIM(MID( C2; FIND( CHAR( 3 ); SUBSTITUTE( ";"&C2; ";"; CHAR(3); ROW( INDIRECT( "1:"&LEN( C2 ) - LEN( SUBSTITUTE( C2; ";"; "" )) + 1)))); FIND( ";"; ";"&C2&";"; FIND( CHAR( 3 ); SUBSTITUTE( ";"&C2; ";"; CHAR( 3 ); ROW( INDIRECT( "1:"&LEN( C2 ) - LEN( SUBSTITUTE( C2; ";"; "")) + 1)))) + 1) - FIND( CHAR( 3 ); SUBSTITUTE( ";"&C2; ";"; CHAR(3); ROW( INDIRECT( "1:"&LEN( C2 ) - LEN( SUBSTITUTE( C2; ";"; "")) + 1)))) - 1));$B$2:$B$59;0);)));""))

Теперь, если выделить MATCH обернутый в N(INDEX...) и нажать F9 но можно опять увидеть тот же самый массив строк {2;3;4;5;6;7;8;9} (ну как тут не вспомнить анекдот про суслика), который, INDEX принимает и передает функции TEXTJOIN, как массив значений.

Автор - Egyptian
Дата добавления - 02.08.2022 в 14:49
jakim Дата: Вторник, 02.08.2022, 15:54 | Сообщение № 22
Группа: Друзья
Ранг: Старожил
Сообщений: 1108
Репутация: 294 ±
Замечаний: 0% ±

Excel 2010
Power Query

[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Для_Алексея"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Др. цвета", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Др. цвета.1", "Др. цвета.2", "Др. цвета.3", "Др. цвета.4", "Др. цвета.5", "Др. цвета.6", "Др. цвета.7", "Др. цвета.8"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ID", "Артикул"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Артикул", "Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Sorted Rows" = Table.Sort(#"Trimmed Text",{{"Value", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Value"}, {{"Count", each _, type table [ID=number, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][ID]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Custom", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Values",{{"Custom", "ID"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Value"})
in
    #"Removed Columns2"
[/vba]
К сообщению приложен файл: 9850874.xlsx(20.4 Kb)
 
Ответить
Сообщение
Power Query

[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Для_Алексея"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Др. цвета", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Др. цвета.1", "Др. цвета.2", "Др. цвета.3", "Др. цвета.4", "Др. цвета.5", "Др. цвета.6", "Др. цвета.7", "Др. цвета.8"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"ID", "Артикул"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Артикул", "Attribute"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Value", Text.Trim, type text}}),
    #"Sorted Rows" = Table.Sort(#"Trimmed Text",{{"Value", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Value"}, {{"Count", each _, type table [ID=number, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each [Count][ID]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns1", {"Custom", each Text.Combine(List.Transform(_, Text.From), "; "), type text}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Values",{{"Custom", "ID"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"Value"})
in
    #"Removed Columns2"
[/vba]

Автор - jakim
Дата добавления - 02.08.2022 в 15:54
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подставить значения в ячейку с разделителем из массива. (Формулы/Formulas)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!