Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
анализ "что если" в Power Pivot
 
#1
19.03.2019 14:22:58
Добрый день! Подскажите, пожалуйста:
Допустим, есть модель, реализованная в Power Pivot. Есть несколько входных параметров X,Y, Z и несколько результативных показателей A,B,C. Можно ли в Power Pivot произвести анализ чувствительности показателя A к параметру X? Т.е. для набора значений X (например, X =1 ... 100 с шагом 1) нужно получить 100 расчетных значений показателя A.
В Excel аналогом того, что хотелось бы получить в Power Pivot, является "Анализ что-если" / "Таблица данных"
 
 
 
#2
19.03.2019 15:01:36
Можно. Это обычная сводная таблица, в строках у вас столбец "Значения Х", а в значениях - "Мера показателя А"
F1 творит чудеса
 
 
 
#3
19.03.2019 15:11:17
Максим, а каким образом в модель 100 раз подставить разные значения X?
 
 
 
#4
19.03.2019 15:20:22
Вы бы показали для начала структуру той модели что у вас имеется. В неё можно добавить справочник с параметрами X, Y, Z, и сделать сводную с измерениями в строках ваших показателей А, В, С, а в столбцах добавить измерения с параметрами, далее нужно составить меру, которая помещенная в такой контекст сводной таблицы описывала бы зависимости одного от другого - получится нужная вам матрица зависимостей.
Дело за малым - дайте пример исходных данных и опишите функцию зависимости показателей от параметров.
Вот горшок пустой, он предмет простой...
 
 
 
#5
19.03.2019 15:39:12
Реализованной модели пока, к сожалению, нет. Пример зависимости: Х = "% изменения выручки по N из M магазинам розничной сети"; A = "соотношение прибыль / долг по кредитам". Одно от другого зависит, но написать формулу A = f (X) - думаю, нереально.
 
 
 
#6
20.03.2019 09:45:39
да почему же нереально?
Самый простой пример: создаете табличку на листе с одним столбцом, в котором значения Х, загоняете ее в модель данных:
1
2
3
и так далее.
Формула для A, которая умножает Х на 2:
Код
A  = SELECTEDVALUE(НоваяТабличка[X]) * 2

или так, чтобы прям с Х:
Код
A = VAR __X = SELECTEDVALUE(НоваяТабличка[X]) RETURN __X*2
F1 творит чудеса
 
 
 
#7
20.03.2019 13:46:46
Добрый день. Максим, я правильно понял, что зависимость A от Х обязательно должна быть реализована в одной формуле? Между А и Х есть масса промежуточных вычислений.
 
 
 
#8
20.03.2019 13:50:23
futurama3000, не обязательно. Вы покажите эти вычисления, тогда можно будет обсуждать предметно. А так про сферического коня в вакууме можно бесконечно переписываться.
Вот горшок пустой, он предмет простой...
 
 
 
#9
20.03.2019 18:17:07
Сделал небольшую модель https://drive.google.com/open?id=10PkseU35cs-nRqAryFI1CB_YOe64vvBF
Исходные данные: выручка по годам, затраты по годам, инвестиции по годам, ставка дисконтирования по годам. В модели рассчитываются NPV и период окупаемости по NPV.
Есть набор вариантов по приросту выручки к исходному уровню и набор вариантов по ставке дисконтирования. Нужно получить для этих наборов данных "Период окупаемости". В модели создана соответствующая мера "Период окупаемости".
 
 
 
#10
21.03.2019 01:02:09
Ну, можно же было и заархивировать.
Вот горшок пустой, он предмет простой...
 
 
 
#11
21.03.2019 07:36:34
Цитата
futurama3000 написал: "Период окупаемости".
интересно, однако... на досуге попробую и в PowerQuery
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#12
21.03.2019 08:50:11
Цитата
futurama3000 написал:
правильно понял, что зависимость A от Х обязательно должна быть реализована в одной формуле
Зависимой от текущего значения X нужно сделать ту формулу, которая использует параметр Х.
Если у вас цепочка X -> C -> B -> A, и нужно посчитать X->A, то логично менять C.
А может и вообще не надо ничего менять - х.з.
Подумайте об этой цепочке как о цепочке вычислений в ячейках Excel, станет понятнее.
F1 творит чудеса
 
 
 
#13
21.03.2019 09:55:38
Как я это вижу - сделать расчет формулы меры ТС не на базе расчетных столбцов из таблицы, а на базе таблицы с двумя ADDCOLUMNS. Но, если первый столбец мне получается посчитать, то второй, который с накопительным итогом - не выходит каменный цветок, запутался в контекстах строки.  :D Вот мои ущербные попытки (как я понял ТС, формула не рабочая, но проследить ход мысли по ней в общем можно):
Код
Период окупаемости2:=MINX(
   FILTER(
      ADDCOLUMNS(
         ADDCOLUMNS(
            'Расчет';
            "DiscountedCF";
            DIVIDE(
               [Выручка] * (1 + IF(HASONEVALUE('ВариантыРостаВыручки'[Варианты роста выручки]) ;VALUES('ВариантыРостаВыручки'[Варианты роста выручки]) ;0))-[Затраты]-[Инвестиции];
               (1+IF(HASONEVALUE('ВариантыСтавкиДисконт'[Варианты Ставки дисконт]) ;VALUES('ВариантыСтавкиДисконт'[Варианты Ставки дисконт]) ;[Ставка дисконт]) )^([Год]-1)));
         "_NPV";
         SUMX(FILTER('Расчет';'Расчет'[Год]<=EARLIER('Расчет'[Год]) );[DiscountedCF]) );
      [_NPV]>=0);
   'Расчет'[Год])
Изменено: PooHkrd - 21.03.2019 10:22:16
Вот горшок пустой, он предмет простой...
 
 
 
#14
21.03.2019 19:33:13
Максим, а что такое SELECTEDVALUE()? У меня нет такой функции в PP
 
 
 
#15
21.03.2019 22:19:21
futurama3000, как это нету?
Вот горшок пустой, он предмет простой...
 
 
 
#16
22.03.2019 10:17:37
вот так)
 
 
 
#17
22.03.2019 10:29:53
futurama3000, обновиться надо.
В любом случае вместо этой функции можно использовать конструкцию из моей формулы:
Код
= IF(HASONEVALUE('ВариантыРостаВыручки'[Варианты роста выручки]) ;VALUES('ВариантыРостаВыручки'[Варианты роста выручки]) ;0)

Это полный аналог SELECTEDVALUE()
Осталось только допилить меру, чтобы она правильно накопительный столбец считала и определяла где мы выходим в плюс. А это нме не под силу сейчас - приболел, башка гудит.
Вот горшок пустой, он предмет простой...
 
 
 
#18
22.03.2019 11:04:24
PooHkrd, спасибо вам за идею! вы говорите о создании суперформулы, которая включает в себя все промежуточные вычисления. реальная модель может быть не сравнимо сложнее моего примера.
 
 
 
#19
22.03.2019 11:14:39
futurama3000, да это та же самая идея, которую предложил и Максим. Просто было у меня чуток времени и задачка показалась интересной. Но вот до конца сделать не получилось.
А так да, если вы хотите вводить динамически изменяемые параметры и смотреть как будет вести себя показатель, то все расчеты нужно вести не в вычисляемых столбцах, а в мере. А дальше уже выбирайте приемлемое решение.
Цитата
futurama3000 написал:
реальная модель может быть не сравнимо сложнее
Это все понятно, но вам отвечали на простой вопрос в вашей теме можно ли реализовать такой анализ в РР. Ответ: да, можно, делать нужно так-то и так-то. Но никто не обещал что это будет легко. ;)
Вот горшок пустой, он предмет простой...
 
 
 
#20
22.03.2019 12:49:34
я вас понял, спасибо за ответы)!
 
 
 
#21
22.03.2019 15:37:06
Цитата
PooHkrd написал:
если вы хотите вводить динамически изменяемые параметры и смотреть как будет вести себя показатель
8)  всё-таки, думается мне, что  есть в природе нормальный способ расчёта точки окупаемости, а не методом высоконаучного тыка - не совсем "что если"
Цитата
futurama3000 написал:
Есть набор вариантов по приросту выручки к исходному уровню и набор вариантов по ставке дисконтирования. Нужно получить для этих наборов данных "Период окупаемости".
а просчитать ! все варианты... все периоды окупаемости... по знакомому вам алгоритму... вот это я понимаю модель! всё и сразу (зачем тогда о динамических параметрах мечтать) - если можно тупо расчёты прогнать по тому, что УЖЕ есть - всё в нужном виде... просто посмотрите на слово "модель данных" шире, чем на слово "модель вашЕГО экономическОГО процессА" -- у вас ведь уже по сути: есть данные по ВСЕМ однотипным экономическим процессам (одного рода) -- вот и получайте ВСЕ возможные выходные линии... логичнее ведь потом, видя всё, сравнить на глаз... будут др. входные данные - введёте, обновитесь...
при чём тут "что если" и зачем динамика, если PP даёт возможность рассмотреть всё и сразу... имхо (в алгоритм ваших расчётов не лезу)...
p.s. упрощайте там, где это возможно... может и PQ будет проще и функциональнее, чем PP для данной задачи? (решать не буду, но интуиция подсказывает)... не понятен выбор PP?
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#22
22.03.2019 19:11:33
JeyCi, извините, не совсем понятна ваша логика. Мне и нужно просчитать ВСЕ варианты) только хотелось бы автоматически. Каким образом вы предполагаете реализовать расчеты в PQ?  
 
 
 
#23
23.03.2019 21:10:43
futurama3000, а в чем проблема перебрать в PQ все варианты? Более того с его помощью можно и выбрать оптимальный вариант, если критерий заранее известен. Но это уже вопрос для новой темы. Здесь только варианты для РР.
Вот горшок пустой, он предмет простой...
 
 
 
#24
24.03.2019 11:34:19
Цитата
futurama3000 написал:
извините, не совсем понятна ваша логика.
анализ "что если" так и рассматривает... это отдельный инструмент в xl...
Цитата
futurama3000 написал:
Каким образом вы предполагаете реализовать расчеты в PQ?  
я не видела ваших пояснений о том, какие расчёты вам нужны... если вы ждёте расчёт периода окупаемости полностью - то эта задача в раздел Работа (сделать что-то за вас)... а на вопрос по xl вам в принципе уже ответили... я лишь добавила, что PQ и PP иногда могут быть взаимозаменяемы - выбор инструмента на любителя... если вам сложно на PP, пробуйте PQ... но и там определитесь с вопросом по инструменту, вызывающему у вас затруднения... а не по расчётам периода окупаемости... ваш алгоритм которого мы даже не знаем... успехов
Изменено: JeyCi - 24.03.2019 11:35:58
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
Страницы: 1
Наверх