Во вложении вариант на базе Power Query и Power Pivot.
Вот таким запросом формируем строчки из сочетаний:
Скрытый текст |
---|
Код |
---|
let
from = Excel.CurrentWorkbook(){[Name="Из"]}[Content]{0}[Column1],
by = Excel.CurrentWorkbook(){[Name="По"]}[Content]{0}[Column1],
q = from - by + 1,
t1 = Table.FromColumns( {List.Numbers( 1, q )}, {Text.From(1)}),
CombsRec = (t as table, i as number, end as number) =>
let
list = List.Numbers( i - 1, q ),
add = Table.ExpandListColumn( Table.AddColumn( t, Text.From( i ), each List.Numbers( Record.FieldValues(_){i - 2} + 1 , q - List.PositionOf( list, Record.FieldValues(_){i - 2}) )), Text.From( i ) ),
out = if i < end
then @CombsRec( add, i + 1, end )
else add
in
out,
CombsReady = CombsRec( t1, 2, by ),
ChangedType = Table.TransformColumnTypes(CombsReady, List.Transform( List.Transform( {1..by}, Text.From ), each { _, Int64.Type}) )
in
ChangedType |
|
Загружаем их в модель данных и в модели уже рисуем любую формулу, и её выводим в сводную в качестве агрегатора. Работает медленнее чем на VBA, но вдруг кому потребуется.
Во вложении файл с вариантом из 30 по 6. (это чтобы файлик по размеру на форум влез)
Чтобы изменить вариант нужно вместо 20 указать 49 или сколько вам там надо, потом обновить сводную и немножко подождать %).
Обратите внимание, что если будете менять число 6, то поломается формула в столбце Отношение в модели данных, но строчки в модель загрузятся, останется только формулу переписать.
Пробовал готовый вариант сразу в PQ посчитать, но группировка очень уж медленно работает. Ждал 5 минут и бросил.