Усовершенствование выборки по типоразмерам - Мир MS Excel

Регистрация

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

= Мир MS Excel/Усовершенствование выборки по типоразмерам - Мир MS Excel

Старая форма входа

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

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Усовершенствование выборки по типоразмерам (Формулы/Formulas)

Усовершенствование выборки по типоразмерам

Дата: Суббота, 18.06.2022, 20:56 | Сообщение № 1

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

Ранг: Участник

Сообщений: 60

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

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

Здравствуйте. Тему уже поднимал, но в ходе работы понял, что не хватает функционала. Есть замечательная выборка (в файле), но она считает только из всех типоразмеров без учёта конструкции. Попробую по другому сформулировать, в выборке должны фигурировать не только типоразмеры с минимальными толщинами, но и учёт конструкций, к которым они относятся. (в исходной таблице фигурируют 4 вида конструкций к каждой свои типоразмеры с минимальными толщинами).
Так же выборку пробовал составить по типоразмеру по убыванию через команду "НАИБОЛЬШЕЕ", но у меня в типоразмере стоит буква и поэтому не получается.
Мои задачи:
1. составить выборку по типоразмерам с учётом конструкций.
2. в выборке типоразмеры по убыванию.

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

Ответить

Дата: Воскресенье, 19.06.2022, 10:17 | Сообщение № 2

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

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

Сообщений: 1199

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

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

Excel 2010

Может так с Power Query?

[vba]

Код

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Sorted Rows" = Table.Sort(Source,{{"Наружный диаметр, мм", Order.Descending}, {"Толщина стенки, мм.", Order.Descending}}),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Наружный диаметр, мм", "Толщина стенки, мм."}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Duplicates", {{"Наружный диаметр, мм", type text}, {"Толщина стенки, мм.", type text}}, "lt-LT"),{"Наружный диаметр, мм", "Толщина стенки, мм."},Combiner.CombineTextByDelimiter("X", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"№ места контроля"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "типоразмер"}})
in
    #"Renamed Columns"

[/vba]

К сообщению приложен файл: 4994848-1-.xlsx (29.1 Kb)

Сообщение отредактировал jakim - Воскресенье, 19.06.2022, 10:18

Ответить

Дата: Воскресенье, 19.06.2022, 11:40 | Сообщение № 3

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

Ранг: Участник

Сообщений: 60

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

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

jakim, спасибо за попытку, но много недостатков. Подскажите умные таблицы самостоятельно обновляются?

Ответить

Дата: Воскресенье, 19.06.2022, 15:14 | Сообщение № 4

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

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

Сообщений: 1199

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

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

Excel 2010

Нет, необходимо войти в Данные и нажать Обновить всё.

Ответить

scriptapplications

Дата: Воскресенье, 19.06.2022, 16:06 | Сообщение № 5

Группа: Проверенные

Ранг: Участник

Сообщений: 68

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

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

AB0885, попробуйте формулы массива (ввод через Ctrl+Shift+Enter)
Типоразмер

Код

=ИНДЕКС($G$3:$G$61;ПОИСКПОЗ(НАИБОЛЬШИЙ($B$3:$B$61*100000000+$C$3:$C$61*10000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ");СТРОКА()-СТРОКА($B$3)+1);$B$3:$B$61*100000000+$C$3:$C$61*10000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ");0))

Толщина

Код

ИНДЕКС($D$3:$D$61;ПОИСКПОЗ(НАИБОЛЬШИЙ($B$3:$B$61*100000000+$C$3:$C$61*10000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ");СТРОКА()-СТРОКА($B$3)+1);$B$3:$B$61*100000000+$C$3:$C$61*10000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ");0))

Конструкция

Код

=ИНДЕКС($E$3:$E$61;ПОИСКПОЗ(НАИБОЛЬШИЙ($B$3:$B$61*100000000+$C$3:$C$61*10000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ");СТРОКА()-СТРОКА($B$3)+1);$B$3:$B$61*100000000+$C$3:$C$61*10000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ");0))

Каждую формулу растяните на весь столбец таблицы

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

Сообщение отредактировал scriptapplications - Воскресенье, 19.06.2022, 16:15

Ответить

Дата: Воскресенье, 19.06.2022, 21:54 | Сообщение № 6

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

Ранг: Участник

Сообщений: 60

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

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

scriptapplications, здравствуйте. Ваше решение подходит для решения второй задачи, но при отсутствии данных, где стоят ноли или в коде "", почему-то не сносит их вниз.
Для понимания первой задачи составил выборку вручную. Показательны будут строки с типоразмерами 159х4, учтены все конструкции с типоразмерами и минимальными толщинами.

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

Сообщение отредактировал AB0885 - Воскресенье, 19.06.2022, 21:55

Ответить

прохожий2019

Дата: Воскресенье, 19.06.2022, 23:29 | Сообщение № 7

Группа: Проверенные

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

Сообщений: 1242

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

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

365 Beta Channel

Цитата AB0885, 19.06.2022 в 21:54, в сообщении № 6 (

)

составил выборку вручную

PQ по примеру:
[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    comb = Table.CombineColumns(sort,{"Наружный диаметр, мм", "Толщина стенки, мм."},(x)=>Text.Combine(List.Transform(x,Text.From),"x"),"Типоразмер"),
    to = Table.ReorderColumns(comb,{"Типоразмер", "Мин толщина", "конструкция"})
in
    to

[/vba]
хотя для себя я бы так написал:
[vba]

Код

let
    lst={"Типоразмер", "Мин толщина", "Конструкция"},
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, (r)=>List.AllTrue(List.Transform(Record.ToList(r),(x)=>x<>0))),
    nms = Table.RenameColumns(filtr,List.Zip({Table.ColumnNames(filtr),{"a".."d",lst{2}}})),
    group = Table.Group(nms, {"b", "c", lst{2}}, {lst{1}, each List.Min([d])}),
    sort = Table.Sort(group,{{"b", 1},{"c", 1},lst{2}}),
    comb = Table.CombineColumns(sort,{"b", "c"},(x)=>Text.Combine(List.Transform(x,Text.From),"x"),lst{0}),
    to = Table.ReorderColumns(comb,lst)
in
    to

[/vba]

Цитата AB0885, 19.06.2022 в 21:54, в сообщении № 6 (

)

составил выборку вручную

PQ по примеру:
[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    comb = Table.CombineColumns(sort,{"Наружный диаметр, мм", "Толщина стенки, мм."},(x)=>Text.Combine(List.Transform(x,Text.From),"x"),"Типоразмер"),
    to = Table.ReorderColumns(comb,{"Типоразмер", "Мин толщина", "конструкция"})
in
    to

[/vba]
хотя для себя я бы так написал:
[vba]

Код

let
    lst={"Типоразмер", "Мин толщина", "Конструкция"},
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, (r)=>List.AllTrue(List.Transform(Record.ToList(r),(x)=>x<>0))),
    nms = Table.RenameColumns(filtr,List.Zip({Table.ColumnNames(filtr),{"a".."d",lst{2}}})),
    group = Table.Group(nms, {"b", "c", lst{2}}, {lst{1}, each List.Min([d])}),
    sort = Table.Sort(group,{{"b", 1},{"c", 1},lst{2}}),
    comb = Table.CombineColumns(sort,{"b", "c"},(x)=>Text.Combine(List.Transform(x,Text.From),"x"),lst{0}),
    to = Table.ReorderColumns(comb,lst)
in
    to

[/vba]прохожий2019

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

Сообщение отредактировал прохожий2019 - Воскресенье, 19.06.2022, 23:55

Ответить

Сообщение

Цитата AB0885, 19.06.2022 в 21:54, в сообщении № 6 (

)

составил выборку вручную

PQ по примеру:
[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    comb = Table.CombineColumns(sort,{"Наружный диаметр, мм", "Толщина стенки, мм."},(x)=>Text.Combine(List.Transform(x,Text.From),"x"),"Типоразмер"),
    to = Table.ReorderColumns(comb,{"Типоразмер", "Мин толщина", "конструкция"})
in
    to

[/vba]
хотя для себя я бы так написал:
[vba]

Код

let
    lst={"Типоразмер", "Мин толщина", "Конструкция"},
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, (r)=>List.AllTrue(List.Transform(Record.ToList(r),(x)=>x<>0))),
    nms = Table.RenameColumns(filtr,List.Zip({Table.ColumnNames(filtr),{"a".."d",lst{2}}})),
    group = Table.Group(nms, {"b", "c", lst{2}}, {lst{1}, each List.Min([d])}),
    sort = Table.Sort(group,{{"b", 1},{"c", 1},lst{2}}),
    comb = Table.CombineColumns(sort,{"b", "c"},(x)=>Text.Combine(List.Transform(x,Text.From),"x"),lst{0}),
    to = Table.ReorderColumns(comb,lst)
in
    to

[/vba]

Автор - прохожий2019
Дата добавления - 19.06.2022 в 23:29

scriptapplications

Дата: Понедельник, 20.06.2022, 01:11 | Сообщение № 8

Группа: Проверенные

Ранг: Участник

Сообщений: 68

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

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

AB0885, там получилась ошибка в формулах надо так
Типоразмер

Код

=ИНДЕКС($G$3:$G$61;ПОИСКПОЗ(НАИБОЛЬШИЙ($B$3:$B$61*1000000000+$C$3:$C$61*100000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;2;1);"0123456789АБВГДЕ");СТРОКА()-СТРОКА($B$3)+1);$B$3:$B$61*1000000000+$C$3:$C$61*100000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;2;1);"0123456789АБВГДЕ");0))

Мин толщина

Код

=ИНДЕКС($D$3:$D$61;ПОИСКПОЗ(НАИБОЛЬШИЙ($B$3:$B$61*1000000000+$C$3:$C$61*100000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;2;1);"0123456789АБВГДЕ");СТРОКА()-СТРОКА($B$3)+1);$B$3:$B$61*1000000000+$C$3:$C$61*100000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;2;1);"0123456789АБВГДЕ");0))

Конструкция

Код

=ИНДЕКС($E$3:$E$61;ПОИСКПОЗ(НАИБОЛЬШИЙ($B$3:$B$61*1000000000+$C$3:$C$61*100000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;2;1);"0123456789АБВГДЕ");СТРОКА()-СТРОКА($B$3)+1);$B$3:$B$61*1000000000+$C$3:$C$61*100000+НАЙТИ(ПСТР($E$3:$E$61;1;1);"0123456789АБВГДЕ")*16+НАЙТИ(ПСТР($E$3:$E$61;2;1);"0123456789АБВГДЕ");0))

Для решения первого варианта надо формулы запустить по выборке,(J:L) но там почему-то Вы включили не все записи основной таблицы.

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

Ответить

Дата: Понедельник, 20.06.2022, 09:29 | Сообщение № 9

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

Ранг: Участник

Сообщений: 60

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

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

прохожий2019, здравствуйте. PQ замечательно подходит, выборка идеальна, но самый важный момент, это обновление (внесение данных в документ и разнообразных, есть большая вероятность забыть об обновлении).
Я не работаю с PQ поэтому у меня есть вопросы:
1. сможет ли PQ собирать информацию с разных листов?
2. есть возможность разложить на составляющие "типоразмер" (надо будет дальше использовать дальше данные из таблицы в расчётах)?

Ответить

прохожий2019

Дата: Понедельник, 20.06.2022, 09:40 | Сообщение № 10

Группа: Проверенные

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

Сообщений: 1242

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

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

365 Beta Channel

1. разумеется, равно как и с разных файлов
2. ну то есть надо не из двух столбцов соорудить типоразмер, а к двум столбцам добавить ещё и типоразмер - конечно можно

по обновлению - его можно вешать на открытие файла

Ответить

Дата: Понедельник, 20.06.2022, 10:03 | Сообщение № 11

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

Ранг: Участник

Сообщений: 60

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

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

scriptapplications, до замечательной выборки осталось немного.
1. можно убрать повторяющиеся данные (в файле выделил жёлтым)?
2. почему то вместо минимальных данных по толщине (159х4 в таблице 3,2. выделил серым)?

Цитата AB0885, 20.06.2022 в 09:29, в сообщении № 9 (

)

почему-то Вы включили не все записи основной таблицы

Да там выборка идёт по типовым типоразмерам с их минимальной толщиной без учёта конструкции, поэтому и возникла просьба о помощи составления с учётом конструкции.

Цитата AB0885, 20.06.2022 в 09:29, в сообщении № 9 (

)

надо формулы запустить по выборке,(J:L)

из вышесказанного (Да там выборка идёт по типовым типоразмерам с их минимальной толщиной без учёта конструкции) получается нельзя запустить формулы по моей выборке (J:L)

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

Сообщение отредактировал AB0885 - Понедельник, 20.06.2022, 10:07

Ответить

Дата: Понедельник, 20.06.2022, 10:35 | Сообщение № 12

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

Ранг: Участник

Сообщений: 60

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

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

прохожий2019, уточню свой второй вопрос: из умной таблицы (далее УТ) я не могу "выдернуть" диаметр из типоразмера (из ячеек без УТ я "выдёргиваю" диаметр с помощью

Код

=ЛЕВСИМВ(J3;ПОИСК("х";J3)-1)

). Как в этом случае поступить? файл с примером приложил

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

Ответить

Дата: Понедельник, 20.06.2022, 10:48 | Сообщение № 13

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

Ранг: Экселист

Сообщений: 7014

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

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

Excel 2007;2010;2016

[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    comb = Table.CombineColumns(sort,{"Наружный диаметр, мм", "Толщина стенки, мм."},(x)=>Text.Combine(List.Transform(x,Text.From),"x"),"Типоразмер"),
    to = Table.ReorderColumns(comb,{"Типоразмер", "Мин толщина", "конструкция"}),
    #"Дублированный столбец" = Table.DuplicateColumn(to, "Типоразмер", "Копия Типоразмер"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Дублированный столбец", "Копия Типоразмер", Splitter.SplitTextByEachDelimiter({"x"}, QuoteStyle.Csv, false), {"Копия Типоразмер.1", "Копия Типоразмер.2"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Копия Типоразмер.1", type number}, {"Копия Типоразмер.2", type number}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"Копия Типоразмер.2"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы",{{"Копия Типоразмер.1", "Диаметр"}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы",{"Типоразмер", "Диаметр", "Мин толщина", "конструкция"})
in
    #"Переупорядоченные столбцы"

[/vba]

[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    comb = Table.CombineColumns(sort,{"Наружный диаметр, мм", "Толщина стенки, мм."},(x)=>Text.Combine(List.Transform(x,Text.From),"x"),"Типоразмер"),
    to = Table.ReorderColumns(comb,{"Типоразмер", "Мин толщина", "конструкция"}),
    #"Дублированный столбец" = Table.DuplicateColumn(to, "Типоразмер", "Копия Типоразмер"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Дублированный столбец", "Копия Типоразмер", Splitter.SplitTextByEachDelimiter({"x"}, QuoteStyle.Csv, false), {"Копия Типоразмер.1", "Копия Типоразмер.2"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Копия Типоразмер.1", type number}, {"Копия Типоразмер.2", type number}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"Копия Типоразмер.2"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы",{{"Копия Типоразмер.1", "Диаметр"}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы",{"Типоразмер", "Диаметр", "Мин толщина", "конструкция"})
in
    #"Переупорядоченные столбцы"

[/vba]китин

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

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

Сообщение[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    comb = Table.CombineColumns(sort,{"Наружный диаметр, мм", "Толщина стенки, мм."},(x)=>Text.Combine(List.Transform(x,Text.From),"x"),"Типоразмер"),
    to = Table.ReorderColumns(comb,{"Типоразмер", "Мин толщина", "конструкция"}),
    #"Дублированный столбец" = Table.DuplicateColumn(to, "Типоразмер", "Копия Типоразмер"),
    #"Разделить столбец по разделителю" = Table.SplitColumn(#"Дублированный столбец", "Копия Типоразмер", Splitter.SplitTextByEachDelimiter({"x"}, QuoteStyle.Csv, false), {"Копия Типоразмер.1", "Копия Типоразмер.2"}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Разделить столбец по разделителю",{{"Копия Типоразмер.1", type number}, {"Копия Типоразмер.2", type number}}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Измененный тип",{"Копия Типоразмер.2"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы",{{"Копия Типоразмер.1", "Диаметр"}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Переименованные столбцы",{"Типоразмер", "Диаметр", "Мин толщина", "конструкция"})
in
    #"Переупорядоченные столбцы"

[/vba]

Автор - китин
Дата добавления - 20.06.2022 в 10:48

прохожий2019

Дата: Понедельник, 20.06.2022, 11:24 | Сообщение № 14

Группа: Проверенные

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

Сообщений: 1242

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

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

365 Beta Channel

китин, зачем так сложно-то?
[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    add = Table.AddColumn(sort,"Типоразмер",(r)=>Text.Combine(List.Transform({r[#"Наружный диаметр, мм"], r[#"Толщина стенки, мм."]},Text.From),"x")),
    to = Table.SelectColumns(add,{"Типоразмер", "Наружный диаметр, мм","Мин толщина", "конструкция"})
in
    to

[/vba]
или по экселевски:
[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    add = Table.AddColumn(sort,"Типоразмер",(r)=>Text.From(r[#"Наружный диаметр, мм"])&"x"&Text.From(r[#"Толщина стенки, мм."])),
    to = Table.SelectColumns(add,{"Типоразмер", "Наружный диаметр, мм","Мин толщина", "конструкция"})
in
    to

[/vba]

китин, зачем так сложно-то?
[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    add = Table.AddColumn(sort,"Типоразмер",(r)=>Text.Combine(List.Transform({r[#"Наружный диаметр, мм"], r[#"Толщина стенки, мм."]},Text.From),"x")),
    to = Table.SelectColumns(add,{"Типоразмер", "Наружный диаметр, мм","Мин толщина", "конструкция"})
in
    to

[/vba]
или по экселевски:
[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    add = Table.AddColumn(sort,"Типоразмер",(r)=>Text.From(r[#"Наружный диаметр, мм"])&"x"&Text.From(r[#"Толщина стенки, мм."])),
    to = Table.SelectColumns(add,{"Типоразмер", "Наружный диаметр, мм","Мин толщина", "конструкция"})
in
    to

[/vba]прохожий2019

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

Сообщение отредактировал прохожий2019 - Понедельник, 20.06.2022, 11:26

Ответить

Сообщениекитин, зачем так сложно-то?
[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    add = Table.AddColumn(sort,"Типоразмер",(r)=>Text.Combine(List.Transform({r[#"Наружный диаметр, мм"], r[#"Толщина стенки, мм."]},Text.From),"x")),
    to = Table.SelectColumns(add,{"Типоразмер", "Наружный диаметр, мм","Мин толщина", "конструкция"})
in
    to

[/vba]
или по экселевски:
[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    filtr = Table.SelectRows(from, each ([#"Наружный диаметр, мм"] <> 0) and ([#"Толщина стенки, мм."] <> 0) and ([#"Минимальное значение фактической толщины, мм."] <> 0)),
    group = Table.Group(filtr, {"Наружный диаметр, мм", "Толщина стенки, мм.", "конструкция"}, {"Мин толщина", each List.Min([#"Минимальное значение фактической толщины, мм."])}),
    sort = Table.Sort(group,{{"Наружный диаметр, мм", Order.Descending},{"Толщина стенки, мм.", Order.Descending},{"конструкция", Order.Ascending}}),
    add = Table.AddColumn(sort,"Типоразмер",(r)=>Text.From(r[#"Наружный диаметр, мм"])&"x"&Text.From(r[#"Толщина стенки, мм."])),
    to = Table.SelectColumns(add,{"Типоразмер", "Наружный диаметр, мм","Мин толщина", "конструкция"})
in
    to

[/vba]

Автор - прохожий2019
Дата добавления - 20.06.2022 в 11:24

прохожий2019

Дата: Понедельник, 20.06.2022, 11:28 | Сообщение № 15

Группа: Проверенные

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

Сообщений: 1242

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

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

365 Beta Channel

Цитата AB0885, 20.06.2022 в 10:35, в сообщении № 12 (

)

я не могу "выдернуть" диаметр из типоразмера

там разделитель x, а вы ищете х (120 и 245 символы только выглядят одинаково и то не во всех шрифтах - поправьте или в коде или в формуле)

Сообщение отредактировал прохожий2019 - Понедельник, 20.06.2022, 11:29

Ответить

Дата: Понедельник, 20.06.2022, 11:44 | Сообщение № 16

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

Ранг: Участник

Сообщений: 60

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

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

прохожий2019, всё, разобрался. Теперь работает. Спасибо.

Но хотел бы увидеть окончание по предложенному scriptapplications варианту.

Сообщение отредактировал AB0885 - Понедельник, 20.06.2022, 11:56

Ответить

Дата: Понедельник, 20.06.2022, 17:41 | Сообщение № 17

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

Ранг: Участник

Сообщений: 60

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

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

Составил выборку по типоразмерам и учётом конструкций, но столбец получился технический (выделил серым в файле), помогите его поправить. Разделить типоразмер с конструкцией.

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

Ответить

scriptapplications

Дата: Вторник, 21.06.2022, 17:49 | Сообщение № 18

Группа: Проверенные

Ранг: Участник

Сообщений: 68

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

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

AB0885, попробуйте так, как в приложенном файле

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

Сообщение отредактировал scriptapplications - Вторник, 21.06.2022, 17:50

Ответить

Дата: Вторник, 21.06.2022, 19:32 | Сообщение № 19

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

Ранг: Участник

Сообщений: 60

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

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

scriptapplications, если честно, то думал обойтись без "технической таблицы".
Но по поводу вашей выборки - Превосходно. Выборка нашла даже мою ошибку в выборке составленной вручную. Для себя расширил возможность писать весь алфавит со строчными буквами (в ущерб скорости). Прекрасно. hands

hands

Сообщение отредактировал AB0885 - Вторник, 21.06.2022, 19:34

Ответить

scriptapplications

Дата: Вторник, 21.06.2022, 20:59 | Сообщение № 20

Группа: Проверенные

Ранг: Участник

Сообщений: 68

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

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

Спасибо!

Цитата AB0885, [url=http://www.excelworld.ru/forum/2-50085-327885-16-1[p.s. ()

655829134]21.06.2022 в 19:32, в сообщении № 19[/url];327885]Для себя расширил возможность писать весь алфавит со строчными буквами (в ущерб скорости).

При изменении длинны алфавита, возможно, придётся изменить формулы
Технический столбец можно исключить, заменив диапазон формулой из технического столбца, с заменой ячеек на соответствующие диапазоны.
При этом формулы станут длиннее.

Сообщение отредактировал scriptapplications - Вторник, 21.06.2022, 21:14

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Усовершенствование выборки по типоразмерам (Формулы/Formulas)

Страница 1 из 2
1
2
»

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