Каскадные выпадающие списки. Список в Power Query, Нужно настроить сложную связку выпадающих списков у двух книг или через запрос создать именованный диапазон.
Power Query не может создавать именованные диапазоны, за этим вам к VBS. Но связанные выпадающие списки на PQ решаются другим способом - делаете на PQ таблицы, по одной для каждого уровня вложенных списков. Все они вида: "Имя параметра в выпадающем списке" - "Диапазон значений, соответствующих данному параметру, в таблице следущего уровня":
Значения в списке уровня 1: =INDIRECT(INDEX(Table_Level_1;;1)
Table_Level_1:
A: Выбранное в списке 1 значение
B: Ссылка для уровня 2
Группа товаров 1
C2:C3
Группа товаров 2
C4:C5
Значения в списке уровня 2: =INDIRECT(VLOOKUP(Table_Level_1;(введенное пользователем значение на уровне 1);2))
Table_Level_2:
C: Выбранное в списке 2 значение
D: Ссылка для уровня 3
Товар A в группе 1
E1:E2
Товар B в группе 1
E3:E3
Товар C в группе 2
E4:E4
Товар D в группе 2
E5:E8
Значения в списке уровня 3: =INDIRECT(VLOOKUP(Table_Level_2;(введенное пользователем значение на уровне 2);2))
Table_Level_3:
E: Компоненты
F: Не используется
Компонент 1 товара A
Компонент 2 товара А
Компонент товара B
Компонент товара C
Компонент 1 товара D
Компонент 2 товара D
Компонент 3 товара D
Компонент 4 товара D
Идея в том что диапазон из первой таблицы ссылается на диапазон во второй таблице, в выпадающем списке вы используете его через ДВССЫЛ(). В этом диапазоне как раз будут данные из выпадающего списка. Может, гуру экселя вам подскажут более изящный способ. PQ в данном случае используется чтобы построить данные справочные таблицы со ссылками и значениям
argyman, попробуйте Power Query - имхо, не захотите больше возвращаться на поделие от гугл никогда больше. Ну только если вам не надо постоянно правит одну и ту же маленькую таблицу сотне человек одновременно.
Добавляете столбец, значение которого увеличивается на единицу каждый раз когда при переходе на новую строку у вас есть изменение в полях, которые вам надо отслеживать. Делается через List.Accumulate со скармливанием ему всех строк таблицы, либо хитрым способом с индексными колонками (одна начинается с нуля, другая с единицы, потом их ждойните, получив в одной строке значения столбцов на этой строке и на предыдущей, а потом сравниваете изменения). Но имхо, аккумулятор тут лучше.
Далее группируете по этому столбцу.
Aleksey_Zhigulin - не так, смотрите мой пример (я сначала так же подумал, как и вы).
Группируете одновременно по полям Код - Статус (так можно). В один столбец - минимум из списка номеров, в другой - максимум. Это одно действие. Все.
Доработайте по вкусу.
P.S. У вас правда в результате есть разбивка на поддиапазоны. Это уже усложняет задачу, но, в принципе, вам нужно добавить временный столбец, который будет зависеть от смены статусов. И добавить его третьим в группировку.
Вариант на PQ - быстро и удобно (Data -> Refresh all)
Код
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Types = Table.TransformColumnTypes(Source,{{"Time", type time}}),
Grp = Table.Group(Types, {"ID"}, {{"Maximum", each List.Max([Time]), type time}})
in
Grp
Меня, наверное, закидают тапками, но я бы сначала через VLOOKUP (ВПР) обратился бы к таблице, в первом столбце которой - ключи "ДуXX", а во второй - диапазоны данных в виде текста, например "A100:A110". А уже эти диапазоны преобразовывать в список через INDIRECT (ДВССЫЛ).
Умная таблица - это попытка Microsoft хотя бы немного приструнить разгулявшихся пользователей и убрать дурные вещи, откровенно нарушающие принцип 1NF, как, например, УФ или объединение ячеек. Учитесь вместо УФ пользоваться фильтрами.
Проверил на такой таблице из миллиона строк. Выполнение основного запроса заняло 50 секунд.
Код
let
Source = List.Transform({0..10}, each "Магазин " & Text.From(_)),
Max = List.Count(Source) - 1,
Tt = Table.FromRows(List.Transform(List.Random(1000000), each {Number.Round(_, 3) * 10000000, Source{Number.Round(Number.RandomBetween(0, Max), 0)}}), {"Код товара", "Магазин"})
in
Tt
Сводная на миллионе строк сводная обновилась за 10 секунд Ну очевидно же что схожие операции сводная выполнит быстрее.
Правда у сводной будет две проблемы - похабный вид данных (в ячейках суммы). Наверное, решается через Custom-столбец, но не проверял сколько времени в этом случае займет обновление. Ну и второе - из 10 файлов данные тянуть.
Предполагаю что это один из самых быстрых способов.
Код
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Группируем таблицу по коду товара
Grp = Table.Group
(
// На таких объемах данных буферизация - наше все имхо
Table.Buffer(Source),
{"Код товара"},
{
// Добавляя столбец Data, в ячейках которого будут содержаться таблицы
// с колонками, соответствующими именам магазинов где есть данный товар
// и единственной строкой, содержащей во всех колонках "x".
{
"Data",
each Table.Buffer
(
// Нестандартное использование Pivot - вместо агрегации данных
// ставим значение "x" во все колонки (имена магазинов, в которых
// есть группируемый товар.
Table.Pivot(_, List.Distinct([Магазин]), "Магазин", "Код товара", each "x")
)
}
}
),
// Это получение уникального сортированного списка всех магазинов для использования в качестве
// имен колонок в готовой таблице.
// Я создаю его, преобразовывая столбец Data, каждая ячейка которого является таблицей,
// в столбец, содержащий список имен колонок таблицы в ячейке. А дальше путем слияния
// списка списков (мутировавший столбец [Data] я получаю все возможные варианты имен магазинов.
List = List.Sort
(
List.Distinct
(
// Как вариант - Вместо List.Combine использовать List.Buffer(Source[Магазин]).
// То есть тупо вычислить уникальные комбинации из колонки Магазин исходной таблицы
// Вполне может быть быстрее..
List.Combine
(
Table.TransformColumns(Grp, {{"Data", each Table.ColumnNames(_)}})[Data]
)
),
Order.Ascending
),
// А дальше просто разворачиваем столбец Data по полному списку магазинов.
// В тех строках, где встречаются имена колонок, которые есть в в List,
// выставляется значение "x", которое там хранится. Если нет (то есть товара
// не было в магазине - то null)
Expanded = Table.ExpandTableColumn(Grp, "Data", List)
in
// В итоге получаем результат.
Expanded
Dark1589 написал: но фильтровать артикулы лучше всё-равно заранее
В данном случае не думаю что PQ будет иметь преимущества по скорости перед сводной. Так же считаю что вариант Dark1589 не самый оптимальный с точки зрения скорости.
Table.Join(
table1 as table,
key1 as any,
table2 as table,
key2 as any,
optional joinKind as nullable number,
optional joinAlgorithm as nullable number,
optional keyEqualityComparers as nullable list // <--- Есть подозрение что это список по числу ключевых колонок и сюда можно засобачить самодельный Comparer
) as table
К сожалению ,у майкрософта документацию на эту тему никакой, гугл тоже ничего особо не находит.
В 2004 году победителем конкурса стала графическая многозадачная операционная система с поддержкой клавиатуры, мыши, примитивной оконной подсистемой, поддержкой файловой системы, возможностью запускать ELF-программы. В поставку системы входит просмотрщик текстовых файлов, а также рудиментарный командный интерпретатор. Исходный код системы вместе с приложениями имеет размер около 3,5 килобайт.
Есть вариант спрятать лист с выходной таблицей PQ, а на нужный лист подтягивать данные формулами. На этом листе, видном пользователю, уже запретить форматирование.
Способ не подходит для больших объемов выходных данных (тысячи строк и более). В этом случае можно делать обновлени PQ через VBA с копированием на нужный лист.
Мучаем List.Intersect на PQ.. Но, наверное формулами в данном случае проще
Скрипт очень медленный из-за наличия TransformColumns и генерации списков с List.Intersect. На моем ноуте скорость ~200 строк в секунду для 100 тыс. записей по 5 пар колонок. Но он и не рассчитан на большие объемы данных, скорее было интересно придумать что-нибудь этакое.
Код
// Готовые списки чтобы каждый раз руками не забивать.
Unpf = {"УНПФ"}, RegL = {"регистрация", "ликвидация"},
// Исходная таблица
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Убираем УПНФ из списка заголовков
List = List.Difference(Table.ColumnNames(Source), Unpf),
// И для этого списка столбцов преобразуем колонки в числовое представление дат.
// установить для колонки тип number в данном случае - бесполезно, не переварит.
Type = Table.TransformColumns(Source, List.Transform(List, (Item) => {Item, each Number.From(Date.From(_))})),
// Таблица создается путем группировки по трем основным колонкам (упнф, региистрация, ликвидация)
// Добавляемые столбцы генерируются путем трансформации половинного списка индексов (так как колонки прием/увольнение идут всегда парами)
Grp = Table.Group
(
Type,
Unpf & RegL,
List.Transform
(
// Список индексов 0.. до половины длины списка доп. колонок
{0..Number.RoundDown(List.Count(List.Difference(List, RegL)) / 2) - 1},
// В качестве каждого элемента будущего списка будет списочек вида {имя столбца, функция_генерации_значений_столбца}
(Id) => {
// Имя стобца
"Диапазон " & Text.From(Id + 1),
// Функция генерации значений
each
let
// Так как в таблице-аргументе всегда одна строка в данном случае.
Row = _{0}
in
// Нужно для отлова null в исходных данных
try
// Признак - пересечение списков дат не пустое.
not List.IsEmpty
(
List.Intersect
(
{
{Row[регистрация].. Row[ликвидация]},
// Обращение к столбцу по его имени через индекс в том
// самом "половинном" списке индексов.
{Record.Field(Row, List{Id*2})..Record.Field(Row, List{Id*2 + 1})}
}
)
)
otherwise null
}
)
),
// Приведение чисел в даты для двух основных столбцов
CType = Table.TransformColumnTypes(Grp,{{"регистрация", type date}, {"ликвидация", type date}})
in
CType
Вариант функции на PQ. Была такая в моем конвертере таблиц в автокадовские спецификации по ГОСТ (кто знает тот поймет какой это трэш).
Алгоритм тупой так как надо разбивать не по числу символов, а по суммарным их весам (ввиду того что не для моноширинных шрифтов разные символы имеют разный размер). В связи с тем что уже давно не работаю с ACad, новая версия генератора спецификаций по ГОСТ осталась недопиленной.
Код
Text.CrLfSymbols = {" ",",",":",";","-","/","*","\","!","?","[","]","{","}","~","'","|"},
Text.SplitSmart =
(
InputText as nullable text, // Входной текст
SegmentMin as number, // Минимальное число символов, функция не разбивает строку до этого числа символов
SegmentMax as number // Максимальное число символов. Если функция не найдет символа для переноса от SegmentMin до SegMentMax, перенос будет произведен по SegmentMax.
) as list =>
let
Len = if InputText = null then 0 else Text.Length(InputText),
Out = if Len > SegmentMax then
let
Pos_A = Text.PositionOfAny(Text.Range(InputText, SegmentMin, SegmentMax - SegmentMin), Text.CrLfSymbols, Occurrence.Last),
Pos_B = if Pos_A < 0 then SegmentMax else SegmentMin + Pos_A + 1,
Txt_A = Text.Start(InputText, Pos_B),
Txt_B = Text.End(InputText, Len - Pos_B)
in List.Combine({{Txt_A}, @Text.SplitSmart(Txt_B, SegmentMin, SegmentMax)})
else
{InputText}
in Out,