Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
power pivot: перенос вычислений из "вычисляемых столбцов" в меры.
 
#1
18.01.2018 13:23:24
Уважаемые форумчане, всем здравия! Есть данные по работе с поставщиками и условиями оплат. Необходимо общую сумму заказа разделить на 2 платежа: первый и второй в зависимости от условий работы поставщика. Далее представить все это дело в читабельном виде в сводной таблице, где все платежи разделяются по дням с обязательным указанием: первый или второй платеж. Я эту задачку решил в power pivot с помощью расчетов в "вычисляемых столбцах" модели данных (пример с расчетами во вложении), но проблема в том, что при применении любого фильтра в сводной она очень долго пересчитывает данные: по несколько минут. Происходит это из-за того, что в настройках сводной (списки полей) я перенес значения из поля"столбцы" в поле "строки", иначе сводная не читабельна. У меня рабочий файл в 1000 строк примерно и он долго думает, в примере во вложении этого не заметно, но все расчеты там приведены аналогичные. Подозреваю, чтобы ускорить работу сводной необходимо перевести расчеты из "вычисляемых столбцов" в меры, но я не смог. Может кто-то сможет решить данную задачу?
 
 
 
#2
18.01.2018 14:00:36
На первый взгляд у вас там в условных столбцах слишком много излишних условий, для примера в столбце Дата первого платежа, вместо:
Код
=IF([условия оплаты]="100% предоплата";
    [дата заказа];
    IF([условия оплаты]="30%/70% (70% после поставки)";
        [дата заказа];
        IF([условия оплаты]="50%/50% (50% после поставки)";[дата заказа];
            IF([условия оплаты]="100% постоплата (после поставки)";
            BLANK()))))
можно смело написать
Код
=IF([условия оплаты]="100% постоплата (после поставки)";BLANK();[дата заказа])
аналогично для даты второго платежа:
Код
=IF([условия оплаты]="100% предоплата";BLANK();[дата заказа]+[срок поставки, дней])
Вообще расчетный столбец в вашей модели лично мне видится только один "Коэффициент первого платежа":
Код
=SWITCH([условия оплаты];
         "100% предоплата";1;
         "50%/50% (50% после поставки)";0,5;
         "30%/70% (70% после поставки)";0,3;
         "100% постоплата (после поставки)";0)
А дальше можно будет сочинять меры на его основе.
Не исключено, что тормоза при расчетах именно из-за избыточных условий, попробуйте для начала упростить формулы в расчетных столбцах на основе предложенного мной, и проверьте быстродействие. После этого можно будет сочинять меры.
Изменено: PooHkrd - 18.01.2018 14:07:55
Вот горшок пустой, он предмет простой...
 
 
 
#3
18.01.2018 14:04:21
Vladimir Chebykin, сложно оценить на сколько это поможет, потому что у меня файл пример не подвисает, какие бы фильтры я не ставил, но я бы сделал для начала следующее:
- создал таблицу "УсловияОплаты" - так будет проще управлять в дальнейшем расчетами, соответственно расчет можно сразу перенести в меры.
- упростил формулы для дат платежей
Код
дата первого платежа=IF([условия оплаты]="100% постоплата (после поставки)";BLANK();[дата заказа])
дата второго платежа=IF([условия оплаты]="100% предоплата";BLANK();[дата заказа]+[срок поставки, дней])

Модель немного подросла и не влазит даже в архиве по размеру, поэтому  см.яндекс.диск
 
 
 
#4
18.01.2018 14:10:32
PooHkrd, StepanWolkoff, спасибо за советы, попробую так сделать. Отпишусь по результату.
 
 
 
#5
18.01.2018 14:25:30
Вычисляемый столбец не пересчитывается при обновлении фильтров, в отличие от меры. Поэтому на изменение фильтров он реагировать должен быстро.
Однако он увеличивает размер модели данных (много уникальных значений) и хранится в оперативке, что может сказаться на поворотливости. Но вроде бы тоже не тот случай.
В вашем случае вычисляемый столбец не обязателен, но я уверен, что причина в чем-то другом - в общей громоздкости сводной в реальном файле, например, или в параллельных пересчетах формул на листе, и т.п.

мелкие дополнения:

вполне достаточно в вычисляемом столбце так (иначе у вас ошибка разных типов данных вылезает):
Код
дата первого платежа=IF([условия оплаты]="100% постоплата (после поставки)";BLANK();[дата заказа])
дата второго платежа =IF([условия оплаты]="100% предоплата";BLANK();[дата заказа]+[срок поставки, дней])


Далее мерами:
Скрытый текст

Столбцы "сумма оплаты", "первый платеж, руб.", "второй платеж, руб." можно удалить (хотя с суммой это и не обязательно)
Изменено: Максим Зеленский - 18.01.2018 14:27:46
F1 творит чудеса
 
 
 
#6
18.01.2018 14:27:22
:) все немного об одном и том же написали:)
F1 творит чудеса
 
 
 
#7
19.01.2018 11:55:16
описываю полученные результаты. Первым делом я оптимизировал формулы согласно всем вашим советам выше: добавил коэффициенты и доп. таблицу "условия оплаты", но проверка показала, что результат не изменился - время ожидания обработки сводной примерно 3 мин..что очень долго. Тогда я стал искать причины в других местах, прислушавшись к Максим Зеленский, В итоге я обнаружил, что все дело в дополнительной связи в модели данных. у меня было два поля с заказом: в основной таблице с данными и отдельная таблица справочник. Так вот, в сводной было значение из таблицы-справочник, заменив его полем из основной таблицы время расчетов сократилось до 11 сек.! Далее я сделал меры по примеру Максим Зеленский, и время обработки сократилось еще практически в 2 раза - до 6 сек!. Вообщем всем: PooHkrd, StepanWolkoff, Максим Зеленский, спасибо - благодаря Вам проблема решена!
 
 
 
#8
19.01.2018 15:19:27
Спасибо, что рассказали!
Интересный результат. Вообще справочник из одного поля - это нонсенс, он не нужен, если у него только одна связь. Я, конечно, слышал о том, что связи могут быть затратными, но настолько...
Любопытно.
F1 творит чудеса
 
 
 
#9
19.01.2018 16:20:35
Я подозреваю что затратными здесь были именно расчеты движком одновременного размещения в полях сводной столбцов из двух разных таблиц связанных с основной таблицей фактов, особенно учитывая то количество неактивных связей, которые использовал ТС для связи с календарем. Слишком до фига виртуальных таблиц создавалось в оперативке. Это получается декартово произведение по одной связи, после чего еще такие же произведения с календарем. Получается, что как только вместо поля из связанной таблицы ТС переставил поле из основной, то количество расчетов уменьшилось в (количество уникальных элементов в этом поле)^2 раз.
Ну что ж не удивительно!
Вот горшок пустой, он предмет простой...
 
 
 
Страницы: 1
Наверх