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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Усовершенствование выборки по типоразмерам (Формулы/Formulas)
Усовершенствование выборки по типоразмерам
AB0885 Дата: Суббота, 18.06.2022, 20:56 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 20% ±

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

Автор - AB0885
Дата добавления - 18.06.2022 в 20:56
jakim Дата: Воскресенье, 19.06.2022, 10:17 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1115
Репутация: 295 ±
Замечаний: 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
 
Ответить
Сообщение
Может так с 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]

Автор - jakim
Дата добавления - 19.06.2022 в 10:17
AB0885 Дата: Воскресенье, 19.06.2022, 11:40 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 20% ±

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

Автор - AB0885
Дата добавления - 19.06.2022 в 11:40
jakim Дата: Воскресенье, 19.06.2022, 15:14 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1115
Репутация: 295 ±
Замечаний: 0% ±

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

Автор - jakim
Дата добавления - 19.06.2022 в 15:14
scriptapplications Дата: Воскресенье, 19.06.2022, 16:06 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 9 ±
Замечаний: 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
 
Ответить
Сообщение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))

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

Автор - scriptapplications
Дата добавления - 19.06.2022 в 16:06
AB0885 Дата: Воскресенье, 19.06.2022, 21:54 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 20% ±

scriptapplications, здравствуйте. Ваше решение подходит для решения второй задачи, но при отсутствии данных, где стоят ноли или в коде "", почему-то не сносит их вниз.
Для понимания первой задачи составил выборку вручную. Показательны будут строки с типоразмерами 159х4, учтены все конструкции с типоразмерами и минимальными толщинами.
К сообщению приложен файл: 5011051.xlsx(28.4 Kb)


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

Автор - AB0885
Дата добавления - 19.06.2022 в 21:54
прохожий2019 Дата: Воскресенье, 19.06.2022, 23:29 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 908
Репутация: 250 ±
Замечаний: 0% ±

365 Beta Channel
составил выборку вручную
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]
К сообщению приложен файл: 7846848.xlsx(34.6 Kb)


Сообщение отредактировал прохожий2019 - Воскресенье, 19.06.2022, 23:55
 
Ответить
Сообщение
составил выборку вручную
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
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 9 ±
Замечаний: 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)
 
Ответить
Сообщение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) но там почему-то Вы включили не все записи основной таблицы.

Автор - scriptapplications
Дата добавления - 20.06.2022 в 01:11
AB0885 Дата: Понедельник, 20.06.2022, 09:29 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 20% ±

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

Автор - AB0885
Дата добавления - 20.06.2022 в 09:29
прохожий2019 Дата: Понедельник, 20.06.2022, 09:40 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 908
Репутация: 250 ±
Замечаний: 0% ±

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

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

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

Автор - прохожий2019
Дата добавления - 20.06.2022 в 09:40
AB0885 Дата: Понедельник, 20.06.2022, 10:03 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 20% ±

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

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

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

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

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


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

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

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

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

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

Автор - AB0885
Дата добавления - 20.06.2022 в 10:03
AB0885 Дата: Понедельник, 20.06.2022, 10:35 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 20% ±

прохожий2019, уточню свой второй вопрос: из умной таблицы (далее УТ) я не могу "выдернуть" диаметр из типоразмера (из ячеек без УТ я "выдёргиваю" диаметр с помощью
Код
=ЛЕВСИМВ(J3;ПОИСК("х";J3)-1)
). Как в этом случае поступить? файл с примером приложил
К сообщению приложен файл: 7846848_1.xlsx(34.1 Kb)
 
Ответить
Сообщениепрохожий2019, уточню свой второй вопрос: из умной таблицы (далее УТ) я не могу "выдернуть" диаметр из типоразмера (из ячеек без УТ я "выдёргиваю" диаметр с помощью
Код
=ЛЕВСИМВ(J3;ПОИСК("х";J3)-1)
). Как в этом случае поступить? файл с примером приложил

Автор - AB0885
Дата добавления - 20.06.2022 в 10:35
китин Дата: Понедельник, 20.06.2022, 10:48 | Сообщение № 13
Группа: Модераторы
Ранг: Экселист
Сообщений: 6895
Репутация: 1056 ±
Замечаний: 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]
К сообщению приложен файл: 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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 908
Репутация: 250 ±
Замечаний: 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]
К сообщению приложен файл: 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
Группа: Проверенные
Ранг: Ветеран
Сообщений: 908
Репутация: 250 ±
Замечаний: 0% ±

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


Сообщение отредактировал прохожий2019 - Понедельник, 20.06.2022, 11:29
 
Ответить
Сообщение
я не могу "выдернуть" диаметр из типоразмера
там разделитель x, а вы ищете х (120 и 245 символы только выглядят одинаково и то не во всех шрифтах - поправьте или в коде или в формуле)

Автор - прохожий2019
Дата добавления - 20.06.2022 в 11:28
AB0885 Дата: Понедельник, 20.06.2022, 11:44 | Сообщение № 16
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 20% ±

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

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


Сообщение отредактировал AB0885 - Понедельник, 20.06.2022, 11:56
 
Ответить
Сообщениепрохожий2019, всё, разобрался. Теперь работает. Спасибо.

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

Автор - AB0885
Дата добавления - 20.06.2022 в 11:44
AB0885 Дата: Понедельник, 20.06.2022, 17:41 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 20% ±

Составил выборку по типоразмерам и учётом конструкций, но столбец получился технический (выделил серым в файле), помогите его поправить. Разделить типоразмер с конструкцией.
К сообщению приложен файл: 4752105.xlsx(27.4 Kb)
 
Ответить
СообщениеСоставил выборку по типоразмерам и учётом конструкций, но столбец получился технический (выделил серым в файле), помогите его поправить. Разделить типоразмер с конструкцией.

Автор - AB0885
Дата добавления - 20.06.2022 в 17:41
scriptapplications Дата: Вторник, 21.06.2022, 17:49 | Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 9 ±
Замечаний: 0% ±

AB0885, попробуйте так, как в приложенном файле
К сообщению приложен файл: 4752105_2.xlsx(35.8 Kb)


Сообщение отредактировал scriptapplications - Вторник, 21.06.2022, 17:50
 
Ответить
СообщениеAB0885, попробуйте так, как в приложенном файле

Автор - scriptapplications
Дата добавления - 21.06.2022 в 17:49
AB0885 Дата: Вторник, 21.06.2022, 19:32 | Сообщение № 19
Группа: Пользователи
Ранг: Участник
Сообщений: 58
Репутация: 0 ±
Замечаний: 20% ±

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


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

Автор - AB0885
Дата добавления - 21.06.2022 в 19:32
scriptapplications Дата: Вторник, 21.06.2022, 20:59 | Сообщение № 20
Группа: Пользователи
Ранг: Участник
Сообщений: 55
Репутация: 9 ±
Замечаний: 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
 
Ответить
СообщениеСпасибо!
Цитата 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 в 20:59
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Усовершенствование выборки по типоразмерам (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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