Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 15 След.
Развернуть вертикальную вложенную таблицу (xml) в один ряд PowerQuery., XML
 
вот тут еще вариант решения точно такой же задачи
График работ в PQ Продолжительность в месяцах
 
Михаил Л, ну не последнее, а лишнее пустое :)
График работ в PQ Продолжительность в месяцах
 
Ну дык это List.Split, у меня в excel 2010 и 2013 почти постоянно приходилось его оборачивать в
Код
List.RemoveMatchingItems(list,{{}})
Проверил в 2013 - оба запроса вывалили ошибку
так работает без ошибок
Код
let
    Источник   = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Combine    = Table.CombineColumns(Источник,List.Skip(Table.ColumnNames(Источник),3),each List.RemoveMatchingItems(List.Split(List.RemoveNulls(_),3),{{}}),"x"),
    Years=(a) => Date.Year(a)+(Number.From(a)-Number.From(Date.StartOfYear(a)))/((Number.From(Date.EndOfYear(a))-Number.From(Date.StartOfYear(a)))),
    Transform  = Table.ReplaceValue(
                     Combine,
                     each [Начало],
                     each Years([Окончание])-Years([Начало]),
                     (a,b,c)=>
                         let 
                             list = List.TransformMany(
                                        a,
                                        each 
                                            let
                                                d = {List.Range(_,0,2)},
                                                e = Number.RoundUp(c/_{1}),
                                                f = Table.FromRows(List.Repeat(d,e)),
                                                g = Table.AddIndexColumn(f,"i"),
                                                h = Table.CombineColumns(g,{"i","Column2"},each Date.AddMonths(b,_{0}*_{1}*12),"x")
                                            in
                                                Table.ToRows(h),
                                        (x,y)=>y
                                    )
                         in 
                             Table.FromRows(list),
                     {"x"}
                 ),
    Expand     = Table.ExpandTableColumn(Transform, "x", {"Column1", "Column2"}),
    Typed      = Table.TransformColumnTypes(Expand,{{"Начало", type date}, {"Окончание", type date}, {"Column2", type date}}),
    Result     = Table.SelectRows(Typed, each ([Column2] <= [Окончание]))
in
    Result

Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    typ = Table.TransformColumnTypes(from,{{"Начало", type date}, {"Окончание", type date}}),
    l1 = List.Skip(Table.ColumnNames(from),3),
    l2={"Действие","Продолжительность","Ед. изм."},
    fn = (d) => Date.MonthName(d) & " " & Text.From(Date.Year(d)),
    tbl = Table.CombineColumns(typ,l1,(l)=>Table.FromColumns(List.Zip(List.RemoveMatchingItems(List.Split(List.RemoveNulls(l),3),{{}})),l2),"tmp"),
    exp = Table.ExpandTableColumn(tbl, "tmp",l2),
    add = Table.AddColumn(exp, "Даты", each 
                                [a=[Начало],b=[Продолжительность]*12,c=[Окончание], 
                                    d=List.Generate(()=>[i=0,t=fn(a)],
                                        each Date.AddMonths(a,[i]*b)<=c,
                                        each [i=[i]+1,t=fn(Date.AddMonths(a,([i]+1)*b))],
                                        each[t])][d]),
    to = Table.ExpandListColumn(add, "Даты")[[Название],[Действие],[Даты]]
in
    to
PQ Получить первый день недели зная Год и Номер недели
 
Добрый день
Код
= let
    x = (y,w)=>
        let 
            a = Date.StartOfWeek(Date.From("10.01."&Text.From(y),"ru")),
            b = Date.AddWeeks(a,w-Date.WeekOfYear(a)) 
        in 
            b
in
    x(2020,20)
Как определить количество ночного времени
 
Код
=СУММПРОИЗВ(ВЫБОР({1;2;3};ОСТАТ(МИН(C5+25/24;C6+D6)-МАКС(17/24;D5);1)+(D5<=1/24)*(1/24-D5);МАКС(D6-17/24;);(C6-C5-1)*8/24)*(C6-C5>{-1;0;1}))*24
Как исправить ошибку: "Ошибка инициализации MCI" ?
 
Цитата
Красноглазый Пиркаф написал:
Может нужно подключить какую-то библиотеку ?
с такой постановкой вопроса без файла вам скорее сюда
График работ в PQ Продолжительность в месяцах
 
у меня оба без ошибок отрабатывают
VBA. Найти все совпадения по дате в умной таблице и вывести результат в другую умной
 
Доброго времени суток
Код
Sub CopyRows()
    Dim List1Obj As ListObject
    Dim List2Obj As ListObject
    Set List1Obj = Excel.Range("Таблица1").ListObject
    Set List2Obj = Excel.Range("Таблица2").ListObject
    Dim oRng As Range, oRng1 As Range
    
    With List2Obj.ListColumns("дата оплаты").DataBodyRange
        Application.FindFormat.Clear
        .Replace Format([Лист1!E1], "dd.MM.yyyy"), "=" & .Cells(0, 1).Address(), xlWhole, , , , True, False
        .Replace [Лист1!E1], "=" & .Cells(0, 1).Address(), xlWhole, , , , True, False
        On Error GoTo er
        Set oRng = .Cells(0, 1).DirectDependents
        Set oRng1 = List1Obj.ListRows.Add.Range.Cells(1, 1)
        Intersect(oRng.EntireRow, .ListObject.DataBodyRange.Resize(, 3)).Copy
        oRng1.PasteSpecial xlPasteValues
        oRng.Value = [Лист1!E1]
    End With
    
    Exit Sub
er: MsgBox "Не найдено!"
End Sub
Изменено: Андрей Лящук - 25 Май 2020 00:57:09
График работ в PQ Продолжительность в месяцах
 
еще вариант
Код
let
    Источник   = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Combine    = Table.CombineColumns(Источник,List.Skip(Table.ColumnNames(Источник),3),each List.Split(List.RemoveNulls(_),3),"x"),
    Years=(a) => Date.Year(a)+(Number.From(a)-Number.From(Date.StartOfYear(a)))/((Number.From(Date.EndOfYear(a))-Number.From(Date.StartOfYear(a)))),
    Transform  = Table.ReplaceValue(
                     Combine,
                     each [Начало],
                     each Years([Окончание])-Years([Начало]),
                     (a,b,c)=>
                         let 
                             list = List.TransformMany(
                                        a,
                                        each 
                                            let
                                                d = {List.Range(_,0,2)},
                                                e = Number.RoundUp(c/_{1}),
                                                f = Table.FromRows(List.Repeat(d,e)),
                                                g = Table.AddIndexColumn(f,"i"),
                                                h = Table.CombineColumns(g,{"i","Column2"},each Date.AddMonths(b,_{0}*_{1}*12),"x")
                                            in
                                                Table.ToRows(h),
                                        (x,y)=>y
                                    )
                         in 
                             Table.FromRows(list),
                     {"x"}
                 ),
    Expand     = Table.ExpandTableColumn(Transform, "x", {"Column1", "Column2"}),
    Typed      = Table.TransformColumnTypes(Expand,{{"Начало", type date}, {"Окончание", type date}, {"Column2", type date}}),
    Result     = Table.SelectRows(Typed, each ([Column2] <= [Окончание]))
in
    Result
Изменено: Андрей Лящук - 24 Май 2020 18:39:14
В таблице с 10ю параметрами, выписать все возможные варианты из 4,3,2 значений
 
кросс
ответил там, продублирую тут
Вариант в Power Query
Код
let
    Source = List.Buffer(List.Transform(Table.ToColumns(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]),List.RemoveNulls)),
    fn     = (n,k,optional i, optional j)=>
             let
                 i   = if i=null then 0 else i, 
                 j   = if j=null then 0 else j, 
                 tbl = Table.FromRecords(
                           List.TransformMany(
                               {i..n-k+j},
                               each Source{_},
                               (a,b)=>[x=b]&(if j<k-1 then [y=@fn(n,k,a+1,j+1)] else [])
                           )
                       ) 
             in 
                 if j<k-1 
                     then Table.ExpandTableColumn(
                         tbl,
                         "y",
                         {"x"}&List.Transform({j+1..k-2}, each "x"&Text.From(_+1)),
                         List.Transform({j+1..k-1}, each "x"&Text.From(_))
                     ) 
                 else tbl,
    ret    = fn(List.Count(Source),4)
in
    ret
Изменено: Андрей Лящук - 24 Май 2020 16:17:13
Импорт xml с сайта в Power Query, Импорт с заданной ссылки xml
 
видимо, сайт использует TLS 1.3. Если это так, то через Power Query к нему не подключиться, ибо Internet Explorer TLS 1.3 не поддерживает (если память не изменяет)
Power Query - обработка выгрузки статистики ВКонтакте, Перевести данные из строк в столбцы
 
Цитата
alimd написал:
в файле показаны исходные данные
в каком?
Проверка отсортированы ли значения (например, по возрастанию) в колонке, как это сделать одной формулой или функцией?
 
Добрый день Массивная формула
Код
=ИЛИ(МУМНОЖ(ТРАНСП(--(ВЫБОР({1;2};A1:A9;A2:A10)<=ВЫБОР({1;2};A2:A10;A1:A9)));СТРОКА(A1:A9)^0)=ЧСТРОК(A1:A9))
VBA. Наполнить таблицу в форме отчёта данными из "плоской" таблицы
 
для разнообразия, создал шаблон отчета в Fastreport.Desktop trial, подключил xlsx через OleDB
Изменено: Андрей Лящук - 21 Май 2020 03:19:31
Как выгрузить в гугл таблицу H1, Title, Description, Нужна формула для ячеек
 
а description так
Код
=importxml(A2;"//meta[@name='description']/@content")
PQ: LeftJoin таблицы и развертывание одной строки по условию соответствия ближайшей ранней дате., прошу помочь исправить код запроса
 
может как-то так
Код
let
    Sales     = Excel.CurrentWorkbook(){[Name="Sales"]}[Content][[ID Cust], [Date], [ID Merch], [Rev]],
    Tax       = Table.Buffer(Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Tax"]}[Content],{"ID БН",type number},"")),
    Prefix    = Table.TransformColumns(Tax,{"ID БН", each "БН "&Text.From(_,"")}),
    IDБН      = List.Buffer(List.Transform({1..List.Max(Tax[#"ID БН"])},each "БН "&Text.From(_,""))),
    Indexed   = Table.AddIndexColumn(Sales, "i", 0),
    Joined    = Table.NestedJoin(Indexed,"ID Cust",Prefix,"ID Cust","x"),
    Transform = Table.ReplaceValue(Joined,
                    each [Date],
                    0,
                    (a,b,c)=> let 
                        filtered = Table.SelectRows(a,each [ContractDate]<=b), 
                        grouped  = Table.Group(filtered, {"ID БН"}, 
                                       {"_", each Table.Max(_,"ContractDate"), type []}
                                   ),
                        totable  = Table.FromRecords(grouped[_]),
                        Typed    = Table.TransformColumnTypes(totable, {"Rate", Percentage.Type},""),
                        pivot    = Table.Pivot(totable, IDБН, "ID БН", "Rate")
                    in pivot,
                    {"x"}
                ),
    Expand    = Table.ExpandTableColumn(Transform, "x", IDБН),
    Typed     = Table.TransformColumnTypes(Expand, 
                    {{"Date",type date}}&
                    List.Zip({
                        IDБН&{"ID Cust","ID Merch","Rev"},
                        List.Repeat({type number},List.Count(IDБН)+3)
                    }),
                    ""
                ),
    Sorted    = Table.Sort(Typed,{{"i", Order.Ascending}}),
    RemoveCol = Table.RemoveColumns(Sorted,{"i"})
in
    RemoveCol
Изменено: Андрей Лящук - 18 Май 2020 21:19:12
PQ функция List.Generate. Получить значение предыдущего элемента списка при расчёте следующего внутри функции next
 
DrillPipe, дабы никого не ввести в заблуждение, уточню: текущее значение - record возвращаемая funct (оно же _ при использовании each, или, как вы писали, x при записи (x)=>), а [i] это значение поля i этой record
Изменено: Андрей Лящук - 17 Май 2020 13:18:09
Поиск из списка, с помощью Power Query, Power Pivot
 
Здравствуйте
Код
let
    Source   = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Source1  = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    Join     = Table.Join(Source,{},Source1,{}),
    Merge    = Table.SelectRows(Join, each Text.Contains([Наименование],[Артикул]))&Source,
    Distinct = Table.Distinct(Merge, {"Наименование"})
in
    Distinct
Power Query / перенос и транспонирование даты из названия столбца в шаблоне-таблице, Ошибка при преобразовании транспонированных столбцов с датой, загрузка в функцию
 
Код
let
    Source   = Excel.CurrentWorkbook(){[Name="data"]}[Content][[Column1],[Column2]],
    ToRows   = List.Buffer(Table.ToRows(Source)),
    fn       = (j)=>
               let
                  a = List.Generate(
                        ()=>[i=j+1,v=ToRows{i},s=v{1}], 
                        each if [i]<List.Count(ToRows) 
                            then Number.Round([s],10)<=Number.Round(ToRows{j}{1},10) 
                            else false,
                        each [i=[i]+1,v=ToRows{i},s=[s]+v{1}],each [v]
                      ),
                  b = {List.Count(a)+j+1,ToRows{j}{0},Date.From(ToRows{0}{1}),Table.FromRows(a)}
               in b,
    Generate = Table.FromRows(
                   List.Generate(
                       ()=>fn(1),
                       each _{0}<=List.Count(ToRows), 
                       each fn(_{0}),
                       each List.Skip(_,1)
                   ),
                   {"Направление","Дата","x"}
               ),
    Expand   = Table.ExpandTableColumn(Generate, "x", {"Column1", "Column2"}, {"Вид груза", "Тыс. т"}),
    Typed    = Table.TransformColumnTypes(Expand,{{"Дата", type date}})
in
    Typed
Разница в минутах между датами без учета определенных условий
 
Код
=СУММПРОИЗВ(ЧИСТРАБДНИ(ВЫБОР({1;2;3};A2;B2;A2+1);ВЫБОР({1;2;3};A2;B2;B2-1);праздники!$A$1:$A$59);ВЫБОР({1;2;3};ОСТАТ(МИН(ОТБР(A2)+18/24;B2)-МАКС(9/24;МИН(ОСТАТ(A2;1);18/24))-1/24*(ОСТАТ(A2;1)<13/24);1);(МИН(18/24;ОСТАТ(B2;1))-9/24-1/24*(ОСТАТ(B2;1)>13/24));8/24)*((ОТБР(B2)-ОТБР(A2))>{-1;0;1}))*1440
Изменено: Андрей Лящук - 16 Май 2020 14:30:08
Разница в минутах между датами без учета определенных условий
 
Цитата
БМВ написал:
результат пока ошибочен и сильно.
Эт все потому, что формула массивная (забыл об этом написать), а в файле не уследил, выложил с немассивным вводом в С2
Избежать возможной ошибки при использовании ТЕКСТ() можно как-то так (немассивная формула)
Код
=СУММПРОИЗВ(ЧИСТРАБДНИ(ВЫБОР({1;2;3};A2;B2;A2+1);ВЫБОР({1;2;3};A2;B2;B2-1);праздники!$A$1:$A$59);ВЫБОР({1;2;3};ОСТАТ(МИН(ОТБР(A2)+18/24;B2)-МАКС(9/24;ОСТАТ(A2;1))-1/24*(ОСТАТ(A2;1)<13/24);1);(МИН(18/24;ОСТАТ(B2;1))-9/24-1/24*(ОСТАТ(B2;1)>13/24));8/24)*((ОТБР(B2)-ОТБР(A2))>{-1;0;1}))*1440
Смена источника в Power Query с папки на файл и обратно
 
Видимо что-то пошло не так и функции fnTransformFile и fnTransformSheet перестали быть обновляемыми. Пересоздал функции, обновил свой пост с файлом, сейчас все норм. Функции обновляются при изменении запросов Workbook и Sheet.
Разница в минутах между датами без учета определенных условий
 
еще вариант
Код
=СУММ(ТЕКСТ(ЧИСТРАБДНИ(ВЫБОР({1;2;3};A2;B2;A2+1);ВЫБОР({1;2;3};A2;B2;B2-1);праздники!$A$1:$A$59);"[>0]0;\0")*ВЫБОР({1;2;3};ОСТАТ(МИН(ОТБР(A2)+18/24;B2)-МАКС(9/24;ОСТАТ(A2;1))-1/24*(ОСТАТ(A2;1)<13/24);1);(МИН(18/24;ОСТАТ(B2;1))-9/24-1/24*(ОСТАТ(B2;1)>13/24))*(ОТБР(B2)>ОТБР(A2));8/24))*1440
Изменено: Андрей Лящук - 15 Май 2020 15:55:31
Преобразование многоуровневой таблицы в плоскую с помощью Power Query
 
Добрый день
Код
let
    source     = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]{0}[source]), null, true){[Item="Данные",Kind="Sheet"]}[Data],
    HeaderRows = 2,
    Headers    = Table.ToRows(Table.TransformColumns(Table.Transpose(Table.FirstN(source,HeaderRows)),List.Zip({List.Transform({1..HeaderRows},each "Column"&Text.From(_)),List.Repeat({each Text.From(_)},HeaderRows)}))),
    NewHeaders = List.Generate(
                    ()=> [
                        i = 0,
                        r = List.RemoveLastN(Headers{i},each _=null),
                        v = r
                    ],
                    each [i]<List.Count(Headers),
                    each [  
                        i = [i]+1,
                        r = List.RemoveLastN(Headers{i},each _=null),
                        v = let 
                                prev=[v] 
                            in 
                                List.Generate(
                                    ()=> [
                                        i=0,
                                        s=if r{i}=null then prev{i} else r{i}
                                    ], 
                                    each [i]<List.Count(r),
                                    each [
                                        i=[i]+1,
                                        s=if r{i}=null then prev{i} else r{i}
                                    ], 
                                    each [s]
                                )
                    ],
                    each Text.Combine([v],"|")
                 ),
    AddHeaders = Table.PromoteHeaders(Table.InsertRows(Table.Skip(source,2),0,{Record.FromList(NewHeaders,Table.ColumnNames(source))})),
    FilledDown = Table.FillDown(AddHeaders,{"шт."}),
    Filtered   = Table.SelectRows(FilledDown, each ([Выпито] <> "Всего")),
    Unpivot    = Table.UnpivotOtherColumns(Filtered, {"шт.", "Выпито"}, "Атрибут", "Значение"),
    Split      = Table.SplitColumn(Unpivot, "Атрибут", each Text.Split(_,"|"), {"Атрибут","Дата"}),
    Typed      = Table.TransformColumnTypes(Split,{{"Дата", type date}})
in
    Typed
Смена источника в Power Query с папки на файл и обратно
 
или так
Код
// debug
1 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]

// FolderPath
"J:\xlsx" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

// Запрос1
let
    Source    = Folder.Files(FolderPath),
    Filtered  = Table.SelectRows(Source, each not [Attributes]?[NotContentIndexed]?),
    Transform = Table.TransformColumns(Filtered, {"Content",each fnTransformFile(_, 0, 0)}),
    ForDebug  = Transform meta (if debug=1 then [bin=Filtered{i}[Content]] else [])
in
    ForDebug

// fnTransformSheet
let
    Источник = (tbl as any, debug as number) => let
    Source   = if debug=1 then Value.Metadata(Workbook)[tbl] else tbl,
    Promoted = Table.PromoteHeaders(Source),
    Result   = {Promoted,Table.ColumnNames(Promoted)}
    in
        Result
in
    Источник

// fnTransformFile
let
    Источник = (Bin as binary, debug as number, j as number) => let
    Source    = if debug=1 then Value.Metadata(Запрос1)[bin] else Bin,
    Workbook  = Excel.Workbook(Source),
    Filtered  = Table.SelectRows(Workbook, each ([Kind] = "Sheet"))[[Name],[Data]],
    Transform = Table.SplitColumn(Filtered,"Data",(a)=>fnTransformSheet(a, 0),{"Data","Columns"}),
    Expand    = Table.ExpandTableColumn(Transform[[Name],[Data]], "Data", List.Distinct(List.Combine(Transform[Columns]))),
    ForDebug  = Expand meta (if debug=1 then [tbl=Filtered{j}[Data]] else [])
    in
        ForDebug
in
    Источник

// i
0 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]

// Bin
null meta [IsParameterQuery=true, Type="Binary", IsParameterQueryRequired=false]

// Workbook
let
    Source    = if debug=1 then Value.Metadata(Запрос1)[bin] else Bin,
    Workbook  = Excel.Workbook(Source),
    Filtered  = Table.SelectRows(Workbook, each ([Kind] = "Sheet"))[[Name],[Data]],
    Transform = Table.SplitColumn(Filtered,"Data",(a)=>fnTransformSheet(a, 0),{"Data","Columns"}),
    Expand    = Table.ExpandTableColumn(Transform[[Name],[Data]], "Data", List.Distinct(List.Combine(Transform[Columns]))),
    ForDebug  = Expand meta (if debug=1 then [tbl=Filtered{j}[Data]] else [])
in
    ForDebug

// j
0 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=false]

// tbl
null meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=false]

// Sheet
let
    Source   = if debug=1 then Value.Metadata(Workbook)[tbl] else tbl,
    Promoted = Table.PromoteHeaders(Source),
    Result   = {Promoted,Table.ColumnNames(Promoted)}
in
    Result
Изменено: Андрей Лящук - 16 Май 2020 08:21:44
Заливка цветом праздничных дней в табеле, Помогите, пожалуйста, разобраться как правильно прописать формулу условного форматирования, чтоб закрасить праздничные дни цветом
 
Доброе утро.


Цитата
tvyur написал:
в какую ячейку надо ставить курсор
в любую из столбца F
Преобразовал текст в даты на листе праздники
Код
=ДЕНЬ(РАБДЕНЬ.МЕЖД($R$7+F$10-2;1;1;праздники!$A$2:$A$5))>F$10
Все комбинации значений из столбцов. Оценка опционов с разными вариантами развития
 
вариант в Power Query
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],    
    Grouped = Table.Group(Source, {"Опцион"}, {{"_", each 
                  let 
                      Table = Table.FillDown(_,{"Опцион"}),
                      Columns = Table.ColumnNames(Table ),
                      Collapsed = Table.FromColumns(List.Transform(Table.ToColumns(Table ),each {_}),Columns),
                      Expanded = List.Accumulate(Columns,Collapsed,(a,b)=>Table.ExpandListColumn(a,b))
                  in 
                      Expanded
              }},0,(a,b)=>if b[Опцион]<>null then 1 else 0),
    Result = Table.Combine(Grouped[_])    
in
    Result
Как найти последнюю строку,и ячейку в умной таблице с помощью VBA
 
добавил в файл
Как найти последнюю строку,и ячейку в умной таблице с помощью VBA
 
Здравствуйте
Код
Private Sub CommandButton_Add_Regions_Click()
    Call Add_Country_City
End Sub
Код
Sub Add_Country_City()
    Dim Country$, City$, ListCol As ListColumn, r As Range
    Country = UF_Add_Regions.ComboBox_Country
    City = UF_Add_Regions.ComboBox_City
    With Excel.Range("Страны.xlsm!Regions").ListObject.ListColumns
        On Error Resume Next
        Set ListCol = .Item(Country)        
        If ListCol Is Nothing Then
            Set ListCol = .Add(.Count + 1)
            ListCol.Name = Country
        End If
        Set r = ListCol.DataBodyRange.SpecialCells(2, 23)
        If Not r Is Nothing Then
            If r.Find(City, , xlValues, xlWhole, , , True, , False) Is Nothing Then
                With r.Find("*", r(1, 1), , xlPart, , xlPrevious, , , False).Offset(1)
                    .NumberFormat = "@"
                    .Value = City
                End With
            End If
        Else
            With ListCol.Range(2, 1)
                .NumberFormat = "@"
                .Value = City
            End With
        End If
    End With
End Sub
Оценка показателя по оценочной таблице в power query
 
Цитата
planB написал:
максимально возможное число операций в полупьяном режиме (автозаписью кода)
Столько хватит? из всех строк вручную написано 3
Код
// Таблица1
let
    Источник  = Excel.CurrentWorkbook(){[Name = "Таблица1"]}[Content],
    #"Несвернутые столбцы" = Table.UnpivotOtherColumns(Источник, {"ind_mapping.Ind_corr2", "Instrument", "bs_rep_date_corr (Год)"}, "Атрибут", "Значение")
in
    #"Несвернутые столбцы"

// Таблица2
let
    Источник    = Excel.CurrentWorkbook(){[Name = "Таблица2"]}[Content],
    #"Несвернутые столбцы" = Table.UnpivotOtherColumns(Источник, {"Profiles", "Coefficients", "score"}, "Атрибут", "Значение"),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Несвернутые столбцы", "a", each if Text.EndsWith([Атрибут],"2") then "to" else "from"),
    #"Замененное значение" = Table.ReplaceValue(#"Добавлен пользовательский объект","2","",Replacer.ReplaceText,{"Атрибут"}),
    #"Сведенный столбец" = Table.Pivot(#"Замененное значение", List.Distinct(#"Замененное значение"[a]), "a", "Значение", List.Sum)
in
    #"Сведенный столбец"

// tbl
null meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=false]

// num
null meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=false]

// debug
1 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]

// i
31 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]

// Запрос1
let
    Источник = Таблица1,
    #"Объединенные запросы" = Table.NestedJoin(Источник,{"ind_mapping.Ind_corr2", "Атрибут"},Таблица2,{"Атрибут", "Coefficients"},"Таблица2",JoinKind.LeftOuter),
    #"Вызвана настраиваемая функция" = Table.AddColumn(#"Объединенные запросы", "score", each fnFilter([Таблица2], [Значение], 0)),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Вызвана настраиваемая функция",{"Таблица2"}),
    #"Сгруппированные строки" = Table.Group(#"Удаленные столбцы", {"ind_mapping.Ind_corr2", "Instrument", "bs_rep_date_corr (Год)","Атрибут"}, {{"_", each _{0}[[Значение],[score]]}}),
    #"Сведенный столбец" = Table.Pivot(#"Сгруппированные строки", List.Distinct(#"Сгруппированные строки"[Атрибут]), "Атрибут", "_"),
    #"Развернутый элемент Revenue in $ bln" = Table.ExpandRecordColumn(#"Сведенный столбец", "Revenue in $ bln", {"Значение", "score"}, {"Revenue in $ bln.Значение", "Revenue in $ bln.score"}),
    #"Развернутый элемент nPPE in $ bln" = Table.ExpandRecordColumn(#"Развернутый элемент Revenue in $ bln", "nPPE in $ bln", {"Значение", "score"}, {"nPPE in $ bln.Значение", "nPPE in $ bln.score"}),
    Пользовательская1 = #"Развернутый элемент nPPE in $ bln" meta (if debug=1 then [a=#"Объединенные запросы",tbl=a{i}[Таблица2],v=a{i}[Значение]] else [])
in
    Пользовательская1

// fnFilter
let
    Источник = (tbl as any, num as number, debug as number) => let
        Источник = if debug=1 then Value.Metadata(Запрос1)[tbl] else tbl,
        Num = if debug=1 then Value.Metadata(Запрос1)[v] else num,
        #"Строки с примененным фильтром" = Table.SelectRows(Источник, each [from] <= Num),
        #"Строки с примененным фильтром1" = Table.SelectRows(#"Строки с примененным фильтром", each [to] > Num),
        #"Вычисленное максимальное значение" = List.Max(#"Строки с примененным фильтром1"[score])
    in
        #"Вычисленное максимальное значение"
in
    Источник

// Запрос2
let
    Источник = if debug=1 then Value.Metadata(Запрос1)[tbl] else tbl,
    Num = if debug=1 then Value.Metadata(Запрос1)[v] else num,
    #"Строки с примененным фильтром" = Table.SelectRows(Источник, each [from] <= Num),
    #"Строки с примененным фильтром1" = Table.SelectRows(#"Строки с примененным фильтром", each [to] > Num),
    #"Вычисленное максимальное значение" = List.Max(#"Строки с примененным фильтром1"[score])
in
    #"Вычисленное максимальное значение"
Изменено: Андрей Лящук - 13 Май 2020 04:16:45
Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 15 След.
Наверх