Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Аналог FORECAST в Power Query - Страница 2 - Мир MS Excel

Старая форма входа
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Аналог FORECAST в Power Query (Microsoft Query)
Аналог FORECAST в Power Query
прохожий2019 Дата: Четверг, 04.02.2021, 20:27 | Сообщение № 21
Группа: Проверенные
Ранг: Ветеран
Сообщений: 553
Репутация: 135 ±
Замечаний: 0% ±

365 Beta Channel
можно относительно колонки Segment
без нормального примера - нельзя
[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,y)=>[  a = List.Zip({Table.Column(x,y),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},
    l=(x,y)=>[  a = List.Buffer(j(x,y)),
                b = Table.AddColumn(x,"Forecast "&y,(r)=> f(Record.Field(r,y),a))][b],

    from = Excel.CurrentWorkbook(){[Name="Issuers"]}[Content],
    tcn = Table.ColumnNames(from),
    nms = List.Transform(tcn,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]})),
    fcn = List.Select(tcn,(x)=>Text.Contains(x,"маржа") or Text.Contains(x,"Дох-ть") or Text.Contains(x,"долг") ),
    to = List.Accumulate(fcn,add,l)
in
    to
[/vba]
К сообщению приложен файл: 8008961.xls(244.0 Kb)


Сообщение отредактировал прохожий2019 - Четверг, 04.02.2021, 20:45
 
Ответить
Сообщение
можно относительно колонки Segment
без нормального примера - нельзя
[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,y)=>[  a = List.Zip({Table.Column(x,y),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},
    l=(x,y)=>[  a = List.Buffer(j(x,y)),
                b = Table.AddColumn(x,"Forecast "&y,(r)=> f(Record.Field(r,y),a))][b],

    from = Excel.CurrentWorkbook(){[Name="Issuers"]}[Content],
    tcn = Table.ColumnNames(from),
    nms = List.Transform(tcn,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]})),
    fcn = List.Select(tcn,(x)=>Text.Contains(x,"маржа") or Text.Contains(x,"Дох-ть") or Text.Contains(x,"долг") ),
    to = List.Accumulate(fcn,add,l)
in
    to
[/vba]

Автор - прохожий2019
Дата добавления - 04.02.2021 в 20:27
semenizer87 Дата: Пятница, 05.02.2021, 10:09 | Сообщение № 22
Группа: Пользователи
Ранг: Новичок
Сообщений: 32
Репутация: 0 ±
Замечаний: 0% ±

Excel 365
Добавил несколько строк, надеюсь файл будет доступен.
К сообщению приложен файл: Bonds_1.xlsm(297.8 Kb)
 
Ответить
СообщениеДобавил несколько строк, надеюсь файл будет доступен.

Автор - semenizer87
Дата добавления - 05.02.2021 в 10:09
прохожий2019 Дата: Пятница, 05.02.2021, 12:08 | Сообщение № 23
Группа: Проверенные
Ранг: Ветеран
Сообщений: 553
Репутация: 135 ±
Замечаний: 0% ±

365 Beta Channel
ну вот и сразу вопросы по живому примеру - что делать с Average Шкала, когда все рейтинги null? Что делать, когда по сегменту одна строчка?
сделал как посчитал нужным, допиливайте самостоятельно:
[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,y)=>[  a = List.Zip({Table.Column(x,y),x[#"Average Шкала"]}),
                a1= List.Select(a,(x)=> not (x{0}=null or x{1}=null)),
                b = List.Accumulate(a1,[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 = if List.Count(a1)>1 then {c[j],c[i]} else {0,0}][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},
    l=(x,y)=>[  a = List.Buffer(j(x,y)),
                b = Table.AddColumn(x,"Forecast "&y,(r)=> f(Record.Field(r,y),a))][b],
    m=(x)=> [   a = List.Transform(x,(x)=> try Number.From(x) otherwise null),
                b = List.Average(a)][b],

    from = Excel.CurrentWorkbook(){[Name="Issuers"]}[Content],
    tcn = Table.ColumnNames(from),
    nms = List.Transform(tcn,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 m({[Шкала S], [Шкала M], [Шкала F]})),
    fcn = List.Select(tcn,(x)=>Text.Contains(x,"маржа") or Text.Contains(x,"Дох-ть") or Text.Contains(x,"долг") ),
    grp = Table.Group(add, "Segment", {"tmp", each List.Accumulate(fcn,_,l)}),
    to = Table.Combine(grp[tmp])
in
    to
[/vba]
К сообщению приложен файл: 2473375.xlsm(254.8 Kb)
 
Ответить
Сообщениену вот и сразу вопросы по живому примеру - что делать с Average Шкала, когда все рейтинги null? Что делать, когда по сегменту одна строчка?
сделал как посчитал нужным, допиливайте самостоятельно:
[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,y)=>[  a = List.Zip({Table.Column(x,y),x[#"Average Шкала"]}),
                a1= List.Select(a,(x)=> not (x{0}=null or x{1}=null)),
                b = List.Accumulate(a1,[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 = if List.Count(a1)>1 then {c[j],c[i]} else {0,0}][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},
    l=(x,y)=>[  a = List.Buffer(j(x,y)),
                b = Table.AddColumn(x,"Forecast "&y,(r)=> f(Record.Field(r,y),a))][b],
    m=(x)=> [   a = List.Transform(x,(x)=> try Number.From(x) otherwise null),
                b = List.Average(a)][b],

    from = Excel.CurrentWorkbook(){[Name="Issuers"]}[Content],
    tcn = Table.ColumnNames(from),
    nms = List.Transform(tcn,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 m({[Шкала S], [Шкала M], [Шкала F]})),
    fcn = List.Select(tcn,(x)=>Text.Contains(x,"маржа") or Text.Contains(x,"Дох-ть") or Text.Contains(x,"долг") ),
    grp = Table.Group(add, "Segment", {"tmp", each List.Accumulate(fcn,_,l)}),
    to = Table.Combine(grp[tmp])
in
    to
[/vba]

Автор - прохожий2019
Дата добавления - 05.02.2021 в 12:08
semenizer87 Дата: Пятница, 05.02.2021, 12:52 | Сообщение № 24
Группа: Пользователи
Ранг: Новичок
Сообщений: 32
Репутация: 0 ±
Замечаний: 0% ±

Excel 365
Спасибо, добрый человек! Этого кода более чем достаточно. Куда донатить?)
 
Ответить
СообщениеСпасибо, добрый человек! Этого кода более чем достаточно. Куда донатить?)

Автор - semenizer87
Дата добавления - 05.02.2021 в 12:52
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Аналог FORECAST в Power Query (Microsoft Query)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

Яндекс цитирования Jobsora jooble
© 2010-2021 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!