Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Многомерная сводная таблица, Данные из плоской таблицы необходимо перенести в многомерную сводную.
 
#1
05.03.2022 13:45:49
Приветствую.
Excel 2016 Home&Business  
С помощью PQ создал плоскую таблицу (ранее не пользовался, но результат получился удовлетворительный, но теперь возникла сложность в сборе этих данных в таблицу. Есть ряд данных по одному значению на период, и ряд данных с данными в разрезе периода. Сам период приходится на 10 лет 2017-2027 помесячно.
Плюс по некоторым периодичным данным необходимы итоговые данные по кварталам и годам.
Единичных 12
В периоде 12

Проблема заключается в
1. Удобстве использования - если макет в формате структуры или сжатой формы на каждый договор приходится по 12 пустых строк в периодичных данных, если макет в табличный, тогда 12 колонок. (в идеале все единичные данные поместить под договор не теряя возможности фильтровать данные - достаточно 1 поля)
2. Не удается сформировать промежуточные итоги. По некоторым данным необходимо поквартально, по некоторым общий. ФинРез и ФСБУ нужно в периоде, а положительный и отрицательный только как итог, но по всем контрагентам в периоде (июнь 2021 например).

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

Кварталы не добавлены, но очевидно как они лягут.  
Изменено: Андрей - 24.03.2022 14:21:27
 
 
 
#2
23.03.2022 00:26:34
UP
 
 
 
#3
23.03.2022 06:56:33
Андрей, по картинкам вряд ли кто-то будет гадать. Судя по обеим Вашим темам, Вам в платный раздел. Тут не один вопрос, с которым нужно помочь, а не очень понятное ТЗ. Хотя, может, кто и возьмется :)
 
 
 
#4
23.03.2022 07:58:42
Цитата
написал:
Андрей , по картинкам вряд ли кто-то будет гадать. Судя по обеим Вашим темам, Вам в платный раздел. Тут не один вопрос, с которым нужно помочь, а не очень понятное ТЗ. Хотя, может, кто и возьмется
Спасибо, но по сути вопрос как из плоской таблицы сделать многомерную. Говоря иначе
                                 Дата1 - Дата2 ...
Договор 1 - Расход
Договор 1 - Приход
 
 
 
#5
23.03.2022 08:40:58
Цитата
Андрей написал:
по сути вопрос как из плоской таблицы сделать многомерную
Из картинки - никак.
 
 
 
#6
24.03.2022 11:22:39
Цитата
написал:
Из картинки - никак.
Таблички с полным циклом подготовлю.
Пока экспериментировал обнаружил закономерности. Больше всего нагрузка из-за формул судя по всему в самом файле.
Т.е. если удаляю вывод запросов - обновление данных происходит моментально.
Когда произвожу вывод данных в книгу, обновление занимает 3 мин. (вместо 1+ час)

В результате больше нагрузки на сбор данных на листе. Т.е. куча плоских таблиц из которых данные собираются в одну "сводную" с помощью ВПР и Индекс.
Сложность скорее в том, что листов более 5 шт. по 1000+ строк и по 120+ колонок. В целом около 1 млн. ячеек опрашивается из примерно 120 тыс.  
Изменено: Андрей - 24.03.2022 11:28:09
 
 
 
#7
24.03.2022 13:07:14
Андрей, в свойствах выводимых из PQ таблиц уберите галочку автоматического изменения ширины столбцов.
 
 
 
#8
24.03.2022 14:23:26
Цитата
написал:
Андрей , в свойствах выводимых из PQ таблиц уберите галочку автоматического изменения ширины столбцов.
Спасибо, сейчас попробую. Файлы порезал, загрузил.
 
 
 
#9
25.03.2022 09:28:24
Не помогло. Дополнительно отключил в этих же свойствах остальные флажки. Также озарило что формулы могут пересчитываться в процессе загрузки и тоже их отключил. В идеале все же сделать все обработки в PQ но вот соединить пока не понимаю как и возможно ли это. Обратил внимание на PBi, но с ним вовсе не работал.

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

Код
 #"Сведенный столбец1" = Table.Pivot(Table.TransformColumnTypes(#"Сведенный столбец", {{"Дата", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(#"Сведенный столбец", {{"Дата", type text}}, "ru-RU")[Дата]), "Дата", "Без НДС", List.Sum)
Изменено: Андрей - 25.03.2022 09:41:31
 
 
 
#10
25.03.2022 10:56:23
Частично удалось решить вопрос. В запросе "объединение" изменил таким образом. Теперь разбивается как нужно по договорам в разрезе дат и вида значения (даты колонками).
Теперь думаю как объединить со значениями которые нужно только 1 раз на договор (без дат) и значения которые нужны без учета договоров (только по датам).

Код
let
    Источник = БезНДС,
    #"+Т.Амортизация" = Table.NestedJoin(Источник, {"Договор (поиск)", "Дата"}, Амортизация, {"Договор (поиск)", "Дата"}, "Амортизация", JoinKind.FullOuter),
    #"Развернутый элемент Амортизация" = Table.ExpandTableColumn(#"+Т.Амортизация", "Амортизация", {"Договор (поиск)", "Амортизация", "Дата"}, {"Амортизация.Договор (поиск)", "Амортизация.Амортизация", "Амортизация.Дата"}),
    #"+Договор(отбор)" = Table.AddColumn(#"Развернутый элемент Амортизация", "Договор (отбор)", each if [#"Договор (поиск)"] = null then [#"Амортизация.Договор (поиск)"] else if [#"Договор (поиск)"] = """""" then [#"Амортизация.Договор (поиск)"] else [#"Договор (поиск)"]),
    #"+Дата(отбор)" = Table.AddColumn(#"+Договор(отбор)", "Дата (отбор)", each if [#"Дата"] = null then [#"Амортизация.Дата"] else if [#"Дата"] = """""" then [#"Амортизация.Дата"] else [#"Дата"]),
    #"Удаленные столбцы" = Table.RemoveColumns(#"+Дата(отбор)",{"Договор (поиск)", "Дата", "Амортизация.Договор (поиск)", "Амортизация.Дата"}),
    #"+Т.Расход" = Table.NestedJoin(#"Удаленные столбцы", {"Договор (отбор)", "Дата (отбор)"}, Расход, {"Договор (поиск)", "Дата"}, "Расход", JoinKind.FullOuter),
    #"Развернутый элемент Расход" = Table.ExpandTableColumn(#"+Т.Расход", "Расход", {"Договор (поиск)", "Дата", "Сумма Расход"}, {"Расход.Договор (поиск)", "Расход.Дата", "Расход.Сумма Расход"}),
    #"+Договор(отб)" = Table.AddColumn(#"Развернутый элемент Расход", "Договор (отб)", each if [#"Договор (отбор)"] = null then [#"Расход.Договор (поиск)"] else if [#"Договор (отбор)"] = """""" then [#"Расход.Договор (поиск)"] else [#"Договор (отбор)"]),
    #"+Дата(отб)" = Table.AddColumn(#"+Договор(отб)", "Дата (отб)", each if [#"Дата (отбор)"] = null then [Расход.Дата] else [#"Дата (отбор)"]),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"+Дата(отб)",{"Договор (отбор)", "Дата (отбор)", "Расход.Договор (поиск)", "Расход.Дата"}),
    #"+Т.Доход" = Table.NestedJoin(#"Удаленные столбцы1", {"Договор (отб)", "Дата (отб)"}, Доход, {"Договор (поиск)", "Дата"}, "Доход", JoinKind.FullOuter),
    #"Развернутый элемент Доход" = Table.ExpandTableColumn(#"+Т.Доход", "Доход", {"Договор (поиск)", "Доход", "Дата"}, {"Доход.Договор (поиск)", "Доход.Доход", "Доход.Дата"}),
    #"+Договор" = Table.AddColumn(#"Развернутый элемент Доход", "Договор", each if [#"Договор (отб)"] = null then [#"Доход.Договор (поиск)"] else if [#"Договор (отб)"] = """""" then [#"Доход.Договор (поиск)"] else [#"Договор (отб)"]),
    #"+Дата" = Table.AddColumn(#"+Договор", "Дата", each if [#"Дата (отб)"] = null then [Доход.Дата] else [#"Дата (отб)"]),
    #"Удаленные столбцы2" = Table.RemoveColumns(#"+Дата",{"Договор (отб)", "Дата (отб)", "Доход.Договор (поиск)", "Доход.Дата"}),
    #"Замененное значение" = Table.ReplaceValue(#"Удаленные столбцы2",null,0,Replacer.ReplaceValue,{"Без НДС", "Амортизация.Амортизация", "Расход.Сумма Расход", "Доход.Доход"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Замененное значение",{{"Амортизация.Амортизация", "Амортизация"}, {"Расход.Сумма Расход", "Расход"}, {"Доход.Доход", "Доход"}}),
    #"Другие столбцы с отмененным свертыванием" = Table.UnpivotOtherColumns(#"Переименованные столбцы", {"Договор", "Дата"}, "Атрибут", "Значение"),
    #"Сведенный столбец" = Table.Pivot(#"Другие столбцы с отмененным свертыванием", List.Distinct(#"Другие столбцы с отмененным свертыванием"[Атрибут]), "Атрибут", "Значение", List.Sum),
    #"Отменено свертывание только для выбранных столбцов" = Table.Unpivot(#"Сведенный столбец", {"Без НДС", "Амортизация", "Расход", "Доход"}, "Атрибут", "Значение"),
    #"Сведенный столбец1" = Table.Pivot(Table.TransformColumnTypes(#"Отменено свертывание только для выбранных столбцов", {{"Дата", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(#"Отменено свертывание только для выбранных столбцов", {{"Дата", type text}}, "ru-RU")[Дата]), "Дата", "Значение", List.Sum)
in
    #"Сведенный столбец1"
 
 
 
#11
25.03.2022 14:39:00
Правильно говорят, хочешь пообщаться с собой, встань напротив зеркала ))) На форуме можно и без зеркала.

В общем уже победил 2 из 3 задач. Осталось придумать как добавить колонки от "маржа начальная" до "маржа конечная". А так, теперь уменьшил количество запросов до 22 (удалил конечные запросы таблиц, которые добавил в текущий запрос).

Вернее проблема не в их добавлении, а в отражении всего по 1 разу.

Код
let
    Источник = Объединение,
    #"Объединенные запросы" = Table.NestedJoin(Источник, {"Договор", "Дата"}, Начисления, {"Договор (поиск)", "Дата"}, "Начисления", JoinKind.FullOuter),
    #"Развернутый элемент Начисления" = Table.ExpandTableColumn(#"Объединенные запросы", "Начисления", {"Договор (поиск)", "Дата", "Начисление"}, {"Начисления.Договор (поиск)", "Начисления.Дата", "Начисления.Начисление"}),
    #"+Договор" = Table.AddColumn(#"Развернутый элемент Начисления", "Пользовательский", each if [Договор] = null then [#"Начисления.Договор (поиск)"] else [Договор]),
    #"+Дата" = Table.AddColumn(#"+Договор", "Пользовательский.1", each if [Дата] = null then [Начисления.Дата] else [Дата]),
    #"Объединенные запросы1" = Table.NestedJoin(#"+Дата", {"Пользовательский", "Пользовательский.1"}, ФинРезФСБУ, {"Договор", "Дата"}, "ФинРезФСБУ", JoinKind.FullOuter),
    #"Развернутый элемент ФинРезФСБУ" = Table.ExpandTableColumn(#"Объединенные запросы1", "ФинРезФСБУ", {"Договор", "Дата", "ФинРез", "ФСБУ"}, {"ФинРезФСБУ.Договор", "ФинРезФСБУ.Дата", "ФинРезФСБУ.ФинРез", "ФинРезФСБУ.ФСБУ"}),
    #"Условный столбец добавлен" = Table.AddColumn(#"Развернутый элемент ФинРезФСБУ", "Пользовательский.2", each if [Пользовательский] = null then [ФинРезФСБУ.Договор] else [Пользовательский]),
    #"Условный столбец добавлен1" = Table.AddColumn(#"Условный столбец добавлен", "Пользовательский.3", each if [Пользовательский.1] = null then [ФинРезФСБУ.Дата] else [Пользовательский.1]),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Условный столбец добавлен1",{"Договор", "Дата", "Пользовательский", "Пользовательский.1", "ФинРезФСБУ.Договор", "ФинРезФСБУ.Дата", "Начисления.Договор (поиск)", "Начисления.Дата"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Удаленные столбцы",{{"Пользовательский.2", "Договор (поиск)"}, {"Пользовательский.3", "Дата"}, {"Начисления.Начисление", "Начисление"}, {"ФинРезФСБУ.ФинРез", "ФинРез"}, {"ФинРезФСБУ.ФСБУ", "ФСБУ"}}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Переименованные столбцы",{{"Без НДС", type number}, {"Амортизация", type number}, {"Расход", type number}, {"Доход", type number}, {"Начисление", type number}, {"ФинРез", type number}, {"ФСБУ", type number}, {"Дата", type date}}),
    #"Переупорядоченные столбцы" = Table.ReorderColumns(#"Измененный тип",{"Договор (поиск)", "Дата", "Начисление", "Без НДС", "Амортизация", "Расход", "ФинРез", "Доход", "ФСБУ"}),
    #"Отменено свертывание только для выбранных столбцов1" = Table.Unpivot(#"Переупорядоченные столбцы", {"Начисление", "Без НДС", "Амортизация", "Расход", "ФинРез", "Доход", "ФСБУ"}, "Атрибут", "Значение"),
    #"Сведенный столбец" = Table.Pivot(Table.TransformColumnTypes(#"Отменено свертывание только для выбранных столбцов1", {{"Дата", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(#"Отменено свертывание только для выбранных столбцов1", {{"Дата", type text}}, "ru-RU")[Дата]), "Дата", "Значение", List.Sum),
    #"Добавленный запрос" = Table.Combine({#"Сведенный столбец", #"ФинРезФСБУ (мес)"})
in
    #"Добавленный запрос"
Изменено: Андрей - 25.03.2022 14:55:55
 
 
 
#12
25.03.2022 15:47:54
Андрей, Андрей, просто тут не подсказку Вам дать нужно, а проверить Ваш код (причем не длинный), попутно угадывая логику :)
При этом очень велика вероятность, что там нет какой-то "интересной" задачки, а рутинная обработка :)
Сделать по Вашему желанию с максимальной производительностью - пожалуйста в платный раздел. А так, не вызвала у меня интереса задачка :)
 
 
 
#13
25.03.2022 16:08:11
surkenny, обидно так то. Это ведь код только одного из запросов. Но уже решил, да и проблема не в запросах, а в моменте выгрузки, даже этот маленький объем выгружается больше 10 минут(первый раз). Файл приложил.
Изменено: Андрей - 25.03.2022 16:09:02
 
 
 
#14
25.03.2022 17:30:25
Цитата
Андрей написал:
обидно так то. Это ведь код только одного из запросов.
Как говорил Ваш тезка Аршавин: "Ваши ожидания — ваши проблемы" :) Я помогаю бесплатно, если интересно/просто и мне не лень :)
У вас join нескольких запросов:

Так еще и с логикой (у Вас в голове она есть, так как Вы с этим работаете), на понимание которой нужно потратить не 10 минут:
Код
...
  #"+Договор(отбор)" = Table.AddColumn (
    #"Развернутый элемент Амортизация", 
    "Договор (отбор)", 
    each 
      if [#"Договор (поиск)"] = null then
        [#"Амортизация.Договор (поиск)"]
      else if [#"Договор (поиск)"] = """""" then
        [#"Амортизация.Договор (поиск)"]
      else
        [#"Договор (поиск)"]
  )
...
Изменено: surkenny - 25.03.2022 17:30:56
 
 
 
#15
25.03.2022 19:37:03
surkenny, я про это имел ввиду.
Цитата
surkenny написал:
Ваш код (причем не длинный)
Уже больше недели занимаюсь им, а тут "не длинный", конечно большая часть это была постоянна переделка, но все же.

В платный бы обратился, но смущает 2 момента.
1. Запрос обрабатывается моментально, через кнопку "обновить все" (без визуализации), в консоле запроса так же не долго обрабатывается. Т.е. проблема больше скорее в выводе данных на лист.
2. Тесты на переданных таблицах не будут соответствовать действительности, т.к. рабочая таблица в тысячи раз больше, а следовательно скорей всего сложно будет понять есть ли результат платной "доработки.

В целом для себя конечно интересно познать более оптимальные методы, но скорее больше советом. В любом случае планирую еще оптимизировать запросы, но после того как точно будет работать, сегодня еще не до конца на рабочий вариант перенес, в понедельник только смогу продолжить.
 
 
 
#16
25.03.2022 20:00:41
Цитата
Андрей написал:
рабочая таблица в тысячи раз больше
Не пора ли посмотреть в сторону баз данных, а не ячеек Excel? :)  
 
 
 
#17
25.03.2022 20:27:20
_Igor_61, ТС и не использует ячейки :) В случае необходимости, он в Power BI сможет перенести.
Андрей, как я уже писал, для предпросмотра в редакторе, PQ (если нет необходимости) грузит и обрабатывает только часть данных. Поэтому НЕ оценивайте скорость по обновлению в редакторе. Я не знаю, какие данные и как Вам нужно обработать. Почти наверняка можно написать на порядок(-и) более оптимальные запросы. Но для этого нужно понять, что нужно сделать, какие данные и тд, и тп.
 
 
 
#18
25.03.2022 20:40:38
_Igor_61, хотите ха-ха? Это абсурдно, но да, я думал, особенно учитывая что являюсь действующим программистом 1С )

Эксель я люблю, но такими объемами данных столкнулся впервые. Изначально нужно было сделать таблицу в той же книге в виде листа.

Ок, сделал, - ммм... сильно тупит, выведи в другой файл.
Сделал просто копирование(чтобы ссылалось на основной файл), эксель завис.
Потом полез искать другие варианты, наткнулся на PQ оценил, мега инструмент, потратил уже много времени и тогда подумал все таки в сторону базы, ну хотя бы начать что-то писать пока эксель думает, но многого не написал, продумал логику и как это будет работать и так, по мелочи. В итоге сейчас быстрее доделать и показать результат чем согласовывать полное переделывание на 1С.

В целом проблемы с файлом, часть данных из 1С, часть вручную, куча ошибок было, и наверняка будут. В общем пока что это самый замороченный файл в моей жизни. Зато многое узнал и еще больше стал уважать эксель.
 
 
 
#19
25.03.2022 20:44:21
surkenny, думаю для этого как минимум теперь нужно завершить файл так, как вижу, чтобы было как можно меньше грубых ошибок, а так, я конечно не сомневаюсь что допустил не мало ошибок оптимизации, при первом то столкновении с PQ и полным отсутствием понимания PBi
 
 
 
#20
25.03.2022 21:18:25
Цитата
Андрей написал:
_Igor_61 , хотите ха-ха?
Извините, ни в коем случае.
Я не профи, но на форуме частенько такие вопросы попадаются, что не поймешь - это вправду человеку нужно или прикол такой с его стороны...особенно последнее время... Вот например абсолютносвежее к сказанному  :)  ТС прилагает к вопросу снимок "Умной таблицы" а вопрос совсем о другом... УТ конечно хороший инструмент, но пару раз (очень давно) нарвался на непредсказуемость их поведения, и сделал вывод, что надежней использовать обычные простые диапазоны. Да, возможно с формулам и с доп.столбцами или с кодом придется побольше усилий приложить, но уже будешь знать, где искать косяки.
А по теме - Вы же не обозначили, что Вы уже крутой программист и работаете с выгрузкой из 1С :)  У меня Excel 2007, с новыми модными примочками дел не имел, извините, а на VBA Вы не пробовали решить эту задачу? Довольно быстро (конечно, это тоже относительная условность) можно с листа забрать и результат на лист выгрузить...
ИМХО: 2003-го вполне хватало для решения большинства задач. А мода каждый год меняется...
Не помню откуда эта фраза:  "Лучшее - враг хорошего" :)  
Изменено: _Igor_61 - 25.03.2022 21:46:19
 
 
 
#21
25.03.2022 21:34:58
_Igor_61, извинений точно не стоит приносить. Не считаю себя крутым программистом, да и среди программистов 1С не воспринимается как язык программирования в связи с тем, что он на русском языке, а ситуация меня одновременно и смешит и удручает ибо 1С позиционирует себя как "все еще на эксель?! Ха, переходите на 1С", а тут прогер и действует обратно ))  

VBA пробовал для своих личных целей и сильно не понравилось его использовать. Ладно синтаксис, который необходимо будет изучать, но в нем были и другие проблемы, то ли то, что он обновляет ячейки сам, то ли еще что-то, но из-за этого и еще пары случаев использования поставил на нем жирный крест.
Изменено: Андрей - 25.03.2022 21:38:22
 
 
 
Страницы: 1
Наверх