Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сложные, неоднотипные и комплексные вычисления в сводной, Разность промежуточных итогов, несколько итогов под разные вычисления и прочее
 
#1
16.01.2018 12:12:50
Доброго времени суток, Планетяне!
Вопрос про вычисления в сводной. 4 года плодотворной работы в Excel сводные таблицы в их "первозданном" виде служили мне верой и правдой. Знаю то, что есть какие-то меры, формулы DAX и Power Pivot и то, что когда-нибудь придётся включить эти замечательные инструменты в свою библиотеку. Возможно этот момент настал)))
В общем, продолжают поступать задачи от начальника по созданию мегаинформативной динамической таблицы, а я "поплыл" как только за привычные рамки пивота вылез)))

Проще показать, чем тут расписывать, что нужно (см. файл).
Коротко: несколько неоднотипных итогов в сводной и неоднотипные промежуточные итоги с "если".
Буду рад любой помощи  :)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#2
16.01.2018 15:44:52
UP: Господа, вы хоть подскажите - может что-то не так описал или банально неинтересно? А то просмотры есть, а отзывов нет(( Может вообще в данном случае проще обработчик макросный написать на основе данных в сводной???
Изменено: Jack Famous - 16.01.2018 15:45:05
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#3
16.01.2018 16:32:22
Привет, Алексей.
Цитата
Jack Famous написал:
может что-то не так описал или банально неинтересно?
Просто банально времени нет. Единственное что скажу, многострочные общие итоги создать не получится. А так примеры ассиметричных мер и способы их форматирования через наборы уже на форуме. Вот у соседей свежий пример.
 
 
 
#4
16.01.2018 16:58:32
Андрей VG, спасибо большое за отклик!
Да итоги - это самое меньшее (их всегда можно макросом быстро добавить). Глянул ссылку - в целом похоже, но у меня тип промежуточных итогов разнится от уровня группировки — это самое "безобразное", что есть, но директор хочет именно так (как это часто у многих бывает).
Самостоятельно не разберусь (в ближайшее время точно). Да и кто знает - что и как мне нужно будет учитывать завтра в пределах одной несчастной сводной)))

Думаю, что пока остановлюсь на варианте массивного обработчика сводной (одной или нескольких) с приведением к нужному виду и последующим форматированием.
Если у кого-нибудь будет желание разобраться с моим примером средствами сводной, то буду признателен  :)
Изменено: Jack Famous - 16.01.2018 16:58:45
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#5
16.01.2018 21:24:12
Jack Famous, вы пишите условие:
Цитата
в жёлтых разница между зелёными по сложному условию "=ЕСЛИ(план>факт; план-факт; факт-план)"
но в примере не все значение соответствуют этому правилу, выделил красным, зеленым то, что по правилу

Файл с моделью в PowerPivot на Яндекс.Диск, а то по размеру не влазит примерно так можно сделать то, что вы хотите, но надо разобраться  с замечанием выше.
Изменено: StepanWolkoff - 16.01.2018 21:55:28
 
 
 
#6
17.01.2018 06:40:43
В примере сообщения #1 результат предложенной формулы
=ЕСЛИ(план>факт; план-факт; факт-план)
для строки "мясо" совпадает с приведенными на листе  [что нужно] значениями только для декабря:
Ожидается:| Мясо | -2145563.18 | -111410.62 | +16428373.18
ПоФормуле:| Мясо | +2145563.18| +111410.62| +16428373.18
Там везде план > факт, поэтому формула выдает положительные значения.
Соответственно и итоги будут отличаться.
В решении StepanWolkoff в строке "Общий итог №3" считается разность желтых ячеек, но в задании вроде бы требуется сумма.
Изменено: ZVI - 17.01.2018 06:45:54
 
 
 
#7
17.01.2018 07:13:32
Приложил вариант формирования отчета макросом без PowerPivot с использованием штатных группировок строк, может это и устроит.
Макрос обновляет сводную таблицу, копирует ее на лист отчета, группирует строки, добавляет итоги и вписывает заказанные формулы.
Посмотрите, что там Ваши формулы насчитали, в коде в константе FM задается исходная формула, ее можно откорректировать.
Для формирования отчета на листе4 нужно нажать кнопку [Run].
Изменено: ZVI - 17.01.2018 08:00:53
 
 
 
#8
17.01.2018 08:22:46
Цитата
ZVI написал:
В решении StepanWolkoff в строке "Общий итог №3" считается разность желтых ячеек, но в задании вроде бы требуется сумма.
Я осознано поставил туда данную формулу, чтобы дополнительно показать, что у автора описание расходится с желанием и к чему это может привести. А так пример больше для того, чтобы показать путь решения желаемого результат с помощью PowerPivot и DAX.
 
 
 
#9
17.01.2018 08:44:34
Цитата
StepanWolkoff написал: А так пример больше для того, чтобы показать путь решения желаемого результат с помощью PowerPivot и DAX.
Так и подумал. Пример хороший, спасибо! :)
 
 
 
#10
17.01.2018 10:03:06
Степан, нету возможности с Я.Д. скачивать. Можете выложить сюда текст самой формулы меры?
Вот горшок пустой, он предмет простой...
 
 
 
#11
17.01.2018 10:28:20
Доброго времени суток! Господа, огромное спасибо за ответы!
StepanWolkoff, ZVI, по замечаниям вы совершенно правы - мой косяк и недосмотр. Буду разбираться в предложенных вариантах и отписываться сюда…
PooHkrd, если правильно понял, что вам нужно, то вот…
Из Power Pivot
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#12
17.01.2018 10:34:59
PooHkrd, пожалуйста
Общий итог№1
Скрытый текст


Общий итог№2
Скрытый текст


Общий итог№3 по аналогии со вторым, только формула другая вместо CALCULATE
 
 
 
#13
17.01.2018 10:43:06
Jack Famous, обратите внимание на Общий Итог№3 в моей модели. За Октябрь и Ноябрь у меня получились такие же суммы как у вас на листе "что нужно", хотя я использовал не суммирование разниц, а условное выражение, которое вы озвучили в условиях, потому что как раз, если действовать по описанной логике, то у вас в "Общий итог№3" верная сумма разницы, а вот по "мясу" нет.
 
 
 
#14
17.01.2018 10:52:34
StepanWolkoff, как я уже написал, я ошибся в визуализации (формула верная). Я пытаюсь разобраться в вашем решении, потому что то, что вы продемонстрировали - очень круто и в динамике, но я не хочу просто вставить свои данные, я хочу понять, как вы к этому пришли. И явно мне нужно плотно приступить к изучению Power Pivot - это реально мощная штука. Не понимаю только, почему у мелкомягких 3 разных языка: функции листа, DAX и M… С ума сойти)))

ZVI, также очень крутой вариант. Подход "с другой стороны" - мне очень пригодиться, т.к. мои обработчики на порядок примитивнее и медленнее — буду изучать ваш код и применять на практике! А вариант с "понятной" константой, доступной для редактирования, которая потом Replace'ом преобразуется в формулу, вообще вижу впервые))))

Спасибо большое! Разбираюсь… По вопросам буду писать в тему.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#15
17.01.2018 11:01:01
Цитата
Jack Famous написал:
я хочу понять, как вы к этому пришли
каждодневное чтение, в основном англоязычных - блогов, книг по DAX и инструментам, где он применяется (PowerPivot, PowerBI, SSAS). Целый год работы по созданию и улучшению бизнес-аналитики для одного крупного ретейлера. И вот такие вот постоянные разминки для ума по типу вашего задания. Помогают не только найти новые и интересные решения, но и не забыть старые - как любой язык, не только программирвоания, если не используешь - начинаешь забывать.
Изменено: StepanWolkoff - 17.01.2018 11:01:44
 
 
 
#16
17.01.2018 11:11:34
StepanWolkoff, терпение и труд - всё перетрут. Действительно работающий совет  :)  добавил ссылку на эту тему в очередь на изучение PP, а пока вернусь к обработчику, т.к. на работе уже нужно решение, а обработчик я хоть самостоятельно контролить и модифицировать могу. Ещё раз большое вам спасибо! Не думал, что даже ТАКОЕ можно делать в PP  :idea:
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#17
17.01.2018 14:51:04
Jack Famous, я только чего не понял: почему план - по мясу, а факт - по рыбе ?
 
 
 
#18
17.01.2018 14:56:17
С.М., заполнялось рандомом просто для примера. Сводная включает несколько строк исходной базы по каждому из итогов
Можно отсортировать с Даты по Крит 2 и посмотреть (см. скрин)
Изменено: Jack Famous - 17.01.2018 16:59:12
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#19
17.01.2018 15:21:00
Цитата
Jack Famous написал:
Не понимаю только, почему у мелкомягких 3 разных языка: функции листа, DAX и M… С ума сойти)))
Так исторически сложилось. Росло же это все из 3 разных инструментов, объединение, которых дает просто сумасшедшую синергию, и, на мой взгляд, вдохнуло вторую жизнь в Excel. По крайней мере, когда я уже думал, что без изучения SQL моя задача нерешаема известными мне средствами подвернулось вот это вот все. И тут такое началось!
Вот горшок пустой, он предмет простой...
 
 
 
#20
17.01.2018 16:26:08
PooHkrd, спасибо за объяснение))) PP и PQ в некоторых моментах пересекаются и хотелось бы иметь общий язык хотя бы для них))) ну - работаем с тем, что есть
Изменено: Jack Famous - 17.01.2018 16:26:18
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
#21
17.01.2018 16:35:23
PQ, оно больше для забора инфы и "причесывания", а вот PP уже для связывания всего причесанного в одну структуру и анализа.
Плюс PQ лично для меня больше выступает как замена автоматизации на VBA, я в нем ни бельмеса и разбираться особо некогда было, а PQ зашел прямо в тему, жалею только, что раньше за эту штуку не схватился, смог бы гораздо больше всего интересного накрутить.
Вот горшок пустой, он предмет простой...
 
 
 
#22
17.01.2018 16:53:57
PooHkrd,
OFF: согласен))) сейчас на PQ бум начался, уроки появились крутые (у того же глубокоуважаемого НП) и грех не изучать.
Я использую PQ для динамического связывания, объединения запросов (связь "один-ко-многим") и "подтягивания" таблиц-справочников из стационарных файлов во все другие
Изменено: Jack Famous - 17.01.2018 16:54:14
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
 
 
Страницы: 1
Наверх