Добрый день, уважаемые форумчане. Подскажите, а то нигде не нашел на просторах интернета, есть ли аналог функции FORECAST (ПРЕДСКАЗ) для Power Query?
Добрый день, уважаемые форумчане. Подскажите, а то нигде не нашел на просторах интернета, есть ли аналог функции FORECAST (ПРЕДСКАЗ) для Power Query?semenizer87
Сообщение отредактировал Serge_007 - Четверг, 04.02.2021, 09:35
штатного нет, можно написать, линейный случай несложный, просто на формулах делается (в лоб и без оптимизации): [vba]
Код
(z,y,x)=> let a = List.Zip({x,y}), b = List.Accumulate(a, [ n=0, x=0, x2=0, y=0, xy=0], (s,c)=>[ n=s[n]+1, x=s[x]+c{0}, x2=s[x2]+c{0}*c{0}, y=s[y]+c{1}, xy=s[xy]+c{0}*c{1} ] ), c = [ i = (b[n]*b[xy]-b[x]*b[y])/(b[n]*b[x2]-b[x]*b[x]), j = (b[y]-i*b[x])/b[n] ], d = c[j]+c[i]*z in d
штатного нет, можно написать, линейный случай несложный, просто на формулах делается (в лоб и без оптимизации): [vba]
Код
(z,y,x)=> let a = List.Zip({x,y}), b = List.Accumulate(a, [ n=0, x=0, x2=0, y=0, xy=0], (s,c)=>[ n=s[n]+1, x=s[x]+c{0}, x2=s[x2]+c{0}*c{0}, y=s[y]+c{1}, xy=s[xy]+c{0}*c{1} ] ), c = [ i = (b[n]*b[xy]-b[x]*b[y])/(b[n]*b[x2]-b[x]*b[x]), j = (b[y]-i*b[x])/b[n] ], d = c[j]+c[i]*z in d
Что такое регрессия, я представляю, непонятно только куда такие коды вставлять. В PQ я все выполняю пошагово, и все более-менее интуитивно понятно. Когда же залезаю в расширенный редактор, то увы-увы... Можете подсказать, куда нужно вставить Ваш скрипт, если необходимо в моем скрипте соотнести колонку "EBITDA маржа’21" с колонкой "Average Шкала".
[vba]
Код
let Source = Excel.CurrentWorkbook(){[Name="Issuers"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Name", type text}, {"[b]EBITDA маржа’21[/b]", type any}, {"EBITDA маржа’22", type any}, {"EBITDA маржа’23", type any}, {"Чистый долг/ EBITDA’21", type any}, {"Чистый долг/ EBITDA’22", type any}, {"Чистый долг/ EBITDA’23", type any}, {"Дох-ть СДП’21", type any}, {"Дох-ть СДП’22", type any}, {"Дох-ть СДП’23", type any}, {"Страна", type text}, {"Sub-industry", type text}, {"Z spread", type any}, {"S&P", type any}, {"Moody`s", type any}, {"Fitch", type any}}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"EBITDA маржа’21", 0}, {"EBITDA маржа’22", 0}, {"EBITDA маржа’23", 0}, {"Дох-ть СДП’21", 0}, {"Дох-ть СДП’22", 0}, {"Дох-ть СДП’23", 0}, {"Чистый долг/ EBITDA’21", 10000}, {"Чистый долг/ EBITDA’22", 10000}, {"Чистый долг/ EBITDA’23", 10000}, {"Z spread", null}, {"S&P", null}, {"Moody`s", null}, {"Fitch", null}}), #"Merged Queries" = Table.NestedJoin(#"Replaced Errors",{"ID"},Segment,{"ID"},"Segment",JoinKind.LeftOuter), #"Expanded Segment" = Table.ExpandTableColumn(#"Merged Queries", "Segment", {"Segment"}, {"Segment"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Segment",{"S&P"},Rating,{"Rating"},"Rating",JoinKind.LeftOuter), #"Expanded Rating" = Table.ExpandTableColumn(#"Merged Queries1", "Rating", {"Шкала"}, {"Шкала S"}), #"Merged Queries2" = Table.NestedJoin(#"Expanded Rating",{"Moody`s"},Rating,{"Rating"},"Rating",JoinKind.LeftOuter), #"Expanded Rating1" = Table.ExpandTableColumn(#"Merged Queries2", "Rating", {"Шкала"}, {"Шкала M"}), #"Merged Queries3" = Table.NestedJoin(#"Expanded Rating1",{"Fitch"},Rating,{"Rating"},"Rating",JoinKind.LeftOuter), #"Expanded Rating2" = Table.ExpandTableColumn(#"Merged Queries3", "Rating", {"Шкала"}, {"Шкала F"}), #"Inserted Average" = Table.AddColumn(#"Expanded Rating2", "Average Шкала", each List.Average({[Шкала S], [Шкала M], [Шкала F]})), #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Average",{{"[b]Average Шкала[/b]", Int64.Type}}) in #"Changed Type1"
[/vba]
Что такое регрессия, я представляю, непонятно только куда такие коды вставлять. В PQ я все выполняю пошагово, и все более-менее интуитивно понятно. Когда же залезаю в расширенный редактор, то увы-увы... Можете подсказать, куда нужно вставить Ваш скрипт, если необходимо в моем скрипте соотнести колонку "EBITDA маржа’21" с колонкой "Average Шкала".
[vba]
Код
let Source = Excel.CurrentWorkbook(){[Name="Issuers"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Name", type text}, {"[b]EBITDA маржа’21[/b]", type any}, {"EBITDA маржа’22", type any}, {"EBITDA маржа’23", type any}, {"Чистый долг/ EBITDA’21", type any}, {"Чистый долг/ EBITDA’22", type any}, {"Чистый долг/ EBITDA’23", type any}, {"Дох-ть СДП’21", type any}, {"Дох-ть СДП’22", type any}, {"Дох-ть СДП’23", type any}, {"Страна", type text}, {"Sub-industry", type text}, {"Z spread", type any}, {"S&P", type any}, {"Moody`s", type any}, {"Fitch", type any}}), #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"EBITDA маржа’21", 0}, {"EBITDA маржа’22", 0}, {"EBITDA маржа’23", 0}, {"Дох-ть СДП’21", 0}, {"Дох-ть СДП’22", 0}, {"Дох-ть СДП’23", 0}, {"Чистый долг/ EBITDA’21", 10000}, {"Чистый долг/ EBITDA’22", 10000}, {"Чистый долг/ EBITDA’23", 10000}, {"Z spread", null}, {"S&P", null}, {"Moody`s", null}, {"Fitch", null}}), #"Merged Queries" = Table.NestedJoin(#"Replaced Errors",{"ID"},Segment,{"ID"},"Segment",JoinKind.LeftOuter), #"Expanded Segment" = Table.ExpandTableColumn(#"Merged Queries", "Segment", {"Segment"}, {"Segment"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded Segment",{"S&P"},Rating,{"Rating"},"Rating",JoinKind.LeftOuter), #"Expanded Rating" = Table.ExpandTableColumn(#"Merged Queries1", "Rating", {"Шкала"}, {"Шкала S"}), #"Merged Queries2" = Table.NestedJoin(#"Expanded Rating",{"Moody`s"},Rating,{"Rating"},"Rating",JoinKind.LeftOuter), #"Expanded Rating1" = Table.ExpandTableColumn(#"Merged Queries2", "Rating", {"Шкала"}, {"Шкала M"}), #"Merged Queries3" = Table.NestedJoin(#"Expanded Rating1",{"Fitch"},Rating,{"Rating"},"Rating",JoinKind.LeftOuter), #"Expanded Rating2" = Table.ExpandTableColumn(#"Merged Queries3", "Rating", {"Шкала"}, {"Шкала F"}), #"Inserted Average" = Table.AddColumn(#"Expanded Rating2", "Average Шкала", each List.Average({[Шкала S], [Шкала M], [Шкала F]})), #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Average",{{"[b]Average Шкала[/b]", Int64.Type}}) in #"Changed Type1"
В файле запрос Issuers, в котором хочу воспользоваться вашим скриптом, соотнеся колонку "EBITDA маржа’21" с колонкой "Average Шкала". Таким образом, новая колонка будет иметь значения от 1 до 21.
В файле запрос Issuers, в котором хочу воспользоваться вашим скриптом, соотнеся колонку "EBITDA маржа’21" с колонкой "Average Шкала". Таким образом, новая колонка будет иметь значения от 1 до 21.semenizer87
Ваще крутяк!!! Спасибо огромное. А можно как-то модифицировать скрипт, чтобы Forecast выдавал значения на основании Segment? Имеется в виду аналог формулы Excel (см 2-ое сообщение).
завязывайте с этим, это как минимум неинтересно, а как максимум неэффективно
да уж, это точно!
Ваще крутяк!!! Спасибо огромное. А можно как-то модифицировать скрипт, чтобы Forecast выдавал значения на основании Segment? Имеется в виду аналог формулы Excel (см 2-ое сообщение).
чтобы Forecast выдавал значения на основании Segment?
у вас большинству сегментов соответствует ОДНА строка - не нужно быть великим математиком, чтобы понять, что через одну точку можно провести бесконечное число прямых а так да - группируете по сегменту и в каждой таблице отдельно добавляете столбец
чтобы Forecast выдавал значения на основании Segment?
у вас большинству сегментов соответствует ОДНА строка - не нужно быть великим математиком, чтобы понять, что через одну точку можно провести бесконечное число прямых а так да - группируете по сегменту и в каждой таблице отдельно добавляете столбецпрохожий2019
Небольшое уточнение. Я хочу сделать FORECAST для остальных 8 столбцов. Нужно ли для этого делать отдельные формулы (как у вас в несколько строк ch, a, b, c, d, f, to) или можно в них как-то зашить колонки начиная с EBITDA маржа`21 по чистый долг/EBITDA`23?
Небольшое уточнение. Я хочу сделать FORECAST для остальных 8 столбцов. Нужно ли для этого делать отдельные формулы (как у вас в несколько строк ch, a, b, c, d, f, to) или можно в них как-то зашить колонки начиная с EBITDA маржа`21 по чистый долг/EBITDA`23?semenizer87
let f=(x,y)=> y{0}+y{1}*x, g=(x,y)=>[ a = Table.NestedJoin(x,{y{0}},Segment,{"ID"},"tmp",JoinKind.LeftOuter), b = Table.ExpandTableColumn(a, "tmp", {"Segment"}, {y{1}})][b], h=(x,y)=>[ a = Table.NestedJoin(x,{y{0}},Rating,{"Rating"},"tmp",JoinKind.LeftOuter), b = Table.ExpandTableColumn(a, "tmp", {"Шкала"}, {y{1}})][b], j=(x)=>[ a = List.Zip({x[#"EBITDA маржа’21"],x[#"Average Шкала"]}), b = List.Accumulate(a,[n=0,x=0,x2=0,y=0,xy=0],(s,c)=>[n=s[n]+1,x=s[x]+c{0},x2=s[x2]+c{0}*c{0},y=s[y]+c{1},xy=s[xy]+c{0}*c{1}]), c = [i=(b[n]*b[xy]-b[x]*b[y])/(b[n]*b[x2]-b[x]*b[x]),j=(b[y]-i*b[x])/b[n]], d = {c[j],c[i]}][d], k=(x)=>if Text.Contains(x,"маржа") or Text.Contains(x,"Дох-ть") then {x,0} else if Text.Contains(x,"долг") then {x,10000} else {x,null},
from = Excel.CurrentWorkbook(){[Name="Issuers"]}[Content], nms = List.Transform(Table.ColumnNames(from),k), rep = Table.ReplaceErrorValues(from, nms), seg = g(rep,{"ID","Segment"}), lst = {{"S&P","Шкала S"},{"Moody`s","Шкала M"},{"Fitch","Шкала F"}}, rtg = List.Accumulate(lst,seg,h), add = Table.AddColumn(rtg, "Average Шкала", each List.Average({[Шкала S], [Шкала M], [Шкала F]})), dict = List.Buffer(j(add)), to = Table.AddColumn(add,"Forecast",each f([#"EBITDA маржа’21"],dict)) in to
[/vba]
я бы вот так писал: [vba]
Код
let f=(x,y)=> y{0}+y{1}*x, g=(x,y)=>[ a = Table.NestedJoin(x,{y{0}},Segment,{"ID"},"tmp",JoinKind.LeftOuter), b = Table.ExpandTableColumn(a, "tmp", {"Segment"}, {y{1}})][b], h=(x,y)=>[ a = Table.NestedJoin(x,{y{0}},Rating,{"Rating"},"tmp",JoinKind.LeftOuter), b = Table.ExpandTableColumn(a, "tmp", {"Шкала"}, {y{1}})][b], j=(x)=>[ a = List.Zip({x[#"EBITDA маржа’21"],x[#"Average Шкала"]}), b = List.Accumulate(a,[n=0,x=0,x2=0,y=0,xy=0],(s,c)=>[n=s[n]+1,x=s[x]+c{0},x2=s[x2]+c{0}*c{0},y=s[y]+c{1},xy=s[xy]+c{0}*c{1}]), c = [i=(b[n]*b[xy]-b[x]*b[y])/(b[n]*b[x2]-b[x]*b[x]),j=(b[y]-i*b[x])/b[n]], d = {c[j],c[i]}][d], k=(x)=>if Text.Contains(x,"маржа") or Text.Contains(x,"Дох-ть") then {x,0} else if Text.Contains(x,"долг") then {x,10000} else {x,null},
from = Excel.CurrentWorkbook(){[Name="Issuers"]}[Content], nms = List.Transform(Table.ColumnNames(from),k), rep = Table.ReplaceErrorValues(from, nms), seg = g(rep,{"ID","Segment"}), lst = {{"S&P","Шкала S"},{"Moody`s","Шкала M"},{"Fitch","Шкала F"}}, rtg = List.Accumulate(lst,seg,h), add = Table.AddColumn(rtg, "Average Шкала", each List.Average({[Шкала S], [Шкала M], [Шкала F]})), dict = List.Buffer(j(add)), to = Table.AddColumn(add,"Forecast",each f([#"EBITDA маржа’21"],dict)) in to
Что-то я не могу понять синтаксис скрипта. Добавив перед in дополнительную строку tod = Table.AddColumn(add,"Forecast Чистый долг/ EBITDA’22",each f([#"Чистый долг/ EBITDA’22"],dict)) и изменив in to на in tod, последняя строка вашего скрипта (to = Table.AddColumn(add,"Forecast",each f([#"EBITDA маржа’21"],dict))) куда-то подевалась.
Что-то я не могу понять синтаксис скрипта. Добавив перед in дополнительную строку tod = Table.AddColumn(add,"Forecast Чистый долг/ EBITDA’22",each f([#"Чистый долг/ EBITDA’22"],dict)) и изменив in to на in tod, последняя строка вашего скрипта (to = Table.AddColumn(add,"Forecast",each f([#"EBITDA маржа’21"],dict))) куда-то подевалась.semenizer87
Супер! И если совсем обнаглеть, то можно относительно колонки Segment, а то всю голову сломал, но так и не понял куда вставлять группировку)
Супер! И если совсем обнаглеть, то можно относительно колонки Segment, а то всю голову сломал, но так и не понял куда вставлять группировку)semenizer87
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
