Страницы: 1
RSS
Подсчет количества строк и суммы для комбинаций характеристик.
 
Добрый вечер. Помогите пожалуйста с такой задачей.
Есть таблица условно у товара ряд характеристик указаных в отдельных столбцах.
Нужно рассчитать количество строк и сумму с различных комбинаций. Характеристик в исходном файле более 20 поэтому ручной перебор не помог ((, пробовал через PQ но сообразил только для частных случаев при помощи группировки. А как можно перебрать все возможные комбинации характеристик. В примере попытался изобразить то что нужно. Как null указал столбец когда для подсчета характеристика не используется. Например подсчет строк только наименованию. (надеюсь будет понятно).  
 
Доброе время суток.
Вариант.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    cols = List.Range(Table.ColumnNames(Source), 1, Table.ColumnCount(Source) - 2),
    calc = List.Transform({1..List.Count(cols)}, (count) =>
        Table.Group(Source, List.Range(cols, 0, count), {"Сумма", each List.Sum([Сумма])})
    ),
    return = Table.Combine(calc)
in
    return
Изменено: Андрей VG - 30.09.2020 20:58:18
 
Можно формулами.
В ячейку R2:
Код
=СЧЁТЕСЛИМН(Таблица1[name];ЕСЛИ($L2="null";"*";$L2);Таблица1[price];ЕСЛИ($M2="null";"<1E300";$M2);Таблица1[Фасовка];ЕСЛИ($N2="null";"<1E300";$N2);Таблица1[характ1];ЕСЛИ($O2="null";"*";$O2);Таблица1[характ2];ЕСЛИ($P2="null";"<1E300";$P2);Таблица1[характ3];ЕСЛИ($Q2="null";"<1E300";$Q2))

В ячейку S2:
Код
=СУММЕСЛИМН(Таблица1[Сумма];Таблица1[name];ЕСЛИ($L2="null";"*";$L2);Таблица1[price];ЕСЛИ($M2="null";"<1E300";$M2);Таблица1[Фасовка];ЕСЛИ($N2="null";"<1E300";$N2);Таблица1[характ1];ЕСЛИ($O2="null";"*";$O2);Таблица1[характ2];ЕСЛИ($P2="null";"<1E300";$P2);Таблица1[характ3];ЕСЛИ($Q2="null";"<1E300";$Q2))

А где в Excel используется let?
 
Цитата
tolikt написал:
А где в Excel используется let?
1. в PQ
2. в Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета
По вопросам из тем форума, личку не читаю.
 
Андрей VG, Спасибо за интересное решение, А поправьте пожалуйста что бы количество строк считало. попытался сам, не получилось. Так и не понял полностью как это работает ((
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    cols = List.Range(Table.ColumnNames(Source), 1, Table.ColumnCount(Source) - 2),
    calc = List.Transform({1..List.Count(cols)}, (count) =>
        Table.Group(Source, List.Range(cols, 0, count), {{"Сумма", each List.Sum([Сумма])},{"Количество", each Table.RowCount(_), type number}})
    ),
    return = Table.Combine(calc)
in
    return
Изменено: kolyaya - 30.09.2020 21:46:46
 
Цитата
БМВ написал:
2. в Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета
У меня нет 365 (2013), но тоже могу использовать ))
 
Цитата
Юрий М написал:
но тоже могу использовать ))
я понимаю, что модераторам всё можно, но КАК? я на предварительный канал перелез, чтоб она у меня появилась  8-0
Соблюдение правил форума не освобождает от модераторского произвола
 
Могу использовать, но это необязательно ))
Код
Sub qqq()
Dim x
    Let x = 77
    MsgBox x
End Sub
 
Цитата
Юрий М написал:
но это необязательно
Можно вас попросить продемонстрировать это на примере свойства класса для присвоения скалярного значения? ;)
 
Андрей, так нечестно )) Только обычные свойства:
Код
Sub qq()
    Let Rows(3).Hidden = True
End Sub
 
Попробовал на рабочем варианте, заработало НО обрабатывает только варианты когда  данные например: данные, null, null,  а вот null, null, данные не обрабатывает.
Можно что-нибудь с этим сделать?
 
Цитата
kolyaya написал:
Можно что-нибудь с этим сделать?
Можно
Цитата
2.3. Приложите файл(ы) с примером (общим весом не более 300Кб) в реальной структуре и форматах данных того, что есть сейчас и того, что хотелось бы на выходе.
 
Андрей VG, Добавил пару строк для примера. Заранее благодарен.
Изменено: kolyaya - 01.10.2020 13:49:51
 
Цитата
kolyaya написал:
пару строк для примера
Застенчивый? :)  Думаете догадаюсь?
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    cols = List.Range(Table.ColumnNames(Source), 1, Table.ColumnCount(Source) - 2),
    calc1 = List.Transform({1..List.Count(cols)}, (count) =>
        Table.Group(Source, List.Range(cols, 0, count), {{"Сумма", each List.Sum([Сумма])},{"Количество", each Table.RowCount(_), type number}})
    ),
    calc2 = List.Transform(List.Range(cols, 1), (col) =>
        Table.Group(Source, {"name", col}, {{"Сумма", each List.Sum([Сумма])},{"Количество", each Table.RowCount(_), type number}})
    ),
    return = Table.Combine(calc1 & calc2)
in
    return
 
Подобрал сложный пример для того что бы вывести полный итог.
На листе2 упростил пример и попробовал скомбинировать более широкий ряд вариантов (не полный), что бы было понятнее. (нужны все возможные варианты комбираций характеристик )
Изменено: kolyaya - 02.10.2020 15:15:22
 
Цитата
kolyaya написал:
Подобрал сложный пример
А сразу нельзя было этот пример показать? Теперь Андрею переделывать?
 
Думал будет понятно исходя из первого примера, но как уже написал к сожалению пример оказался трудным для вывода нужного диапозона. Что скрывать я и на облегченный вариант минут 30 потратил.
 
Цитата
Юрий М написал:
переделывать?
Ладно, Юрий, по фразе
Цитата
kolyaya написал:
Думал будет понятно
сразу ясно, что человек - гуманитарий. Дадим тогда все возможные комбинации: по одному, по два, по три и т. д., а он уж как Микеланджело Буонарроти - отсечёт всё лишнее :)
Скрытый текст

P. S. Николай, а вы задумывались - сколько будет таких комбинаций, если
Цитата
kolyaya написал:
Характеристик в исходном файле более 20
:qstn:
Изменено: Андрей VG - 03.10.2020 09:57:32 (Один маленький нюанс на порядке упустил)
 
Андрей VG, Спасибо огромное. Круто получилось, жаль только не опнятно как это работает ((
Можно вопросик по этой части кода?
Код
    hexToBinary = Table.FromRows({
        {"0", "0000"}, {"1", "0001"}, {"2", "0010"}, {"3", "0011"}, {"4", "0100"},
        {"5", "0101"}, {"6", "0110"}, {"7", "0111"}, {"8", "1000"}, {"9", "1001"},
        {"a", "1010"}, {"b", "1011"}, {"c", "1100"}, {"d", "1101"}, {"e", "1110"}, {"f", "1111"}},
        type table [h = Text.Type, b = Text.Type]
    ),



Это для создание таблицы, которая потом используется для комбинирования возможных вариантов столбцов характеристик?
Почему именно 16 строк?

На 22 столбца пока не пробовал, пробую частями пока на 8 характеристик 14000 строк получилось.
Изменено: kolyaya - 03.10.2020 20:56:51
 
Таблица нужна для создания битовой таблицы (пример в файле) из шестнадцатеричного представления числа. Естественно, её можно было бы составить и через List.Generage с функциями как в таблице на листе (пример в Power Query тоже есть). Но было интересно это направление.
P. S. Николай, вы уверены, что совершенно не стоит отвечать на поставленные вам вопросы?
 
Уточните пожалуйста какой вопрос я пропустил.
Если этот -  
Цитата
Андрей VG написал:
Николай, а вы задумывались - сколько будет таких комбинаций, если более 20 характеристик
Вроде написал что начал тестировать частями и на 8 характеристик полчилось 14000 строк. Сейчас запустил на 14 характеристик уже больше часа считает, но с учетом что предположительно будет более 1 млн вариантов сразу поставил фильтр на количество. На 20 характеристик думаю будет несколько десятков миллионов комбинаций. Опять таки с фильтром думаю выведет, если нет придется как-то делить задачу на части. Но как я пока вижу работает на 10 из 10. Еще раз спасибо.
Страницы: 1
Наверх