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

Страницы: 1 2 3 4 5 6 7 8 9 След.
Автоматическое добавление названий новых листов в формулу
 
В диспетчере имен создаем имя SheetNames по формуле
Код
=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)

На листе массивная формула
Код
=ВПР($C$2;ДВССЫЛ("'"&ИНДЕКС(SheetNames;ПОИСКПОЗ(1;ПОИСК("[*]"&$C4;SheetNames);0))&"'!B8:J39");СТОЛБЕЦ(B$1);0)
Подставить год к дате (в идеале в Power Query)
 
Цитата
PooHkrd написал:
зачем в List.Buffer тащите список таблиц? Оно же с составными элементами не работает.
Не до конца проснулся еще, видимо, когда писал.
Подставить год к дате (в идеале в Power Query)
 
еще вариант
Код
let
    Source = 
        Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Grouped =
        Table.Group(
            Source, 
            {"Столбец2"}, 
            {{"a", each _, type table}},
            0,
            (a,b)=>let c=(s)=>Number.From(Text.AfterDelimiter(s,".")) 
                   in Number.From(c(b[Столбец2])<c(a[Столбец2]))
        )[a],
    fn=(i)=>
        Text.Split(Grouped{0}{0}[Столбец1],"/"){i},
    Transformed = 
        List.Generate(()=>
            [i=0,year=fn(i)],
            each [i]<List.Count(Grouped),
            each [i=[i]+1,year=fn(i)],
            each 
                let 
                    year=[year], 
                    addyear=Table.TransformColumns(Grouped{[i]},{{"Столбец2", each Date.From(_&"."&year)}}) 
                in Table.TransformColumnTypes(addyear,{{"Столбец2",type date}})),
    Combined = 
        Table.Combine(Transformed)
in
    Combined
Изменено: Андрей Лящук - 9 Янв 2020 16:40:45
Можно ли нормализовать следующую таблицу с расписанием, используя power query?
 
Цитата
extrafant написал:
можно ли сделать так, чтобы он показывал и пустые уроки (пустые ячейки)
Какой смысл плодить пустые ячейки? Если только для того, чтобы учителя без уроков не терялись вот вариант (выводятся только существующие уроки и по 1 пустой строке для учителей без нагрузки)
Код
let
    f1 = (t as table,nrows as number)=> let
        t          = Table.Buffer(t),
        OldNames   = Table.Transpose(Table.FirstN(t,nrows)),
        TmpCols    = List.Transform({1..nrows},each "Column"&Text.From(_)),
        Operations = List.Zip({TmpCols,List.Repeat({each Text.Clean(Text.Trim(Text.From(_)))},nrows)}),
        TrimClean  = List.Buffer(Table.ToRows(Table.TransformColumns(OldNames,Operations))),
        NewNames   = List.Generate(()=>
            [ i = 0,
              r = List.RemoveLastN(TrimClean{i},each _=null),
              v = r ],
            each [i]<List.Count(TrimClean),
            each [
                i = [i]+1,
                r = List.RemoveLastN(TrimClean{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],"|")
        ),
        HeadreRow  = Record.FromList(NewNames,Table.ColumnNames(t))
    in Table.PromoteHeaders(Table.InsertRows(Table.Skip(t,nrows),0,{HeadreRow})),
    f2 = (t,columns)=> let  
        t        = Table.Buffer(t),
        l        = List.Select(Table.ColumnNames(t),each not Text.Contains(_,"|")),
        t1       = Table.SelectColumns(Table.Distinct(t,l),l),
        Unpivot  = Table.UnpivotOtherColumns(t,List.Select(Table.ColumnNames(t),each not Text.Contains(_,"|")),"Атрибут","Значение"),
        Nested   = Table.ExpandTableColumn(Table.NestedJoin(t1,l,Unpivot,l,""),"",{"Атрибут","Значение"}),
        Split    = Table.SplitColumn(Nested, "Атрибут",each Text.Split(if _=null then "" else _,"|"),columns),
        Pivot    = Table.Pivot(Split, List.RemoveNulls(List.Distinct(Split[Атрибут])), "Атрибут", "Значение"),
        WeekDays = List.Transform({1..14},each Text.Proper(Date.ToText(#date(1,1,_),"ddd"&(if _>7 then "d" else ""),"ru-ru"))),
        Replace  = Table.ReplaceValue(Pivot,0,0,(a,b,c)=>Number.Mod(List.PositionOf(WeekDays,a),7)+1,{"День"})
    in Replace,
    f3 = (tbl as table)=> let
        NewHeaders = Table.FillDown(f1(Table.Skip(tbl,each [Column1]<>"День"),2),{"День"}),
        Group      = Table.Group(
            NewHeaders, 
            {"День", "#"}, 
            List.Transform(
                List.Skip(Table.ColumnNames(NewHeaders),2),
                each let s = _
                     in {s,(t)=>Replacer.ReplaceValue(List.RemoveNulls(Table.Column(t,s)),{},null)}
            ),
            0,
            (a,b)=>Number.From(b[#"#"]<>null)
        )
    in Table.ExpandListColumn(Table.ExpandListColumn(f2(Group,{"Класс","Атрибут"}), "Предмет"), "Каб."),
    Source          = Excel.Workbook(File.Contents("C:\TEMP\Расписание классов 0112.xlsx"), null, true)[[Data]],
    Source2         = Excel.Workbook(File.Contents("C:\TEMP\Расписание учителей 0112.xlsx"), null, true)[Data]{1},
    ClassSchedule   = Table.ExpandTableColumn(Table.TransformColumns(Source,{{"Data",f3}}), "Data", {"День", "#", "Класс", "Предмет", "Каб."}),
    TeacherSchedule = Table.TransformColumnTypes(f2(f1(Table.RemoveLastN(Table.Skip(Source2,each [Column1]<>"#"),each [Column1]=null),3),{"День", "Урок", "Атрибут"}),{{"Урок",type number}}),
    Nested          = Table.ExpandTableColumn(Table.NestedJoin(TeacherSchedule,{"День","Урок","Класс","Каб."},ClassSchedule,{"День","#","Класс","Каб."},""), "", {"Предмет"}),
    Sort          = Table.Sort(Nested,{{"#", Order.Ascending}, {"День", Order.Ascending}, {"Урок", Order.Ascending}})
in
    Sort
Можно ли нормализовать следующую таблицу с расписанием, используя power query?
 
как-то так наворотил
Код
let
    f1 = (t as table,nrows as number)=> let
        OldNames   = Table.Transpose(Table.FirstN(t,nrows)),
        TmpCols    = List.Transform({1..nrows},each "Column"&Text.From(_)),
        Operations = List.Zip({TmpCols,List.Repeat({each Text.Clean(Text.Trim(Text.From(_)))},nrows)}),
        TrimClean  = List.Buffer(Table.ToRows(Table.TransformColumns(OldNames,Operations))),
        NewNames   = List.Generate(()=>
            [ i = 0,
              r = List.RemoveLastN(TrimClean{i},each _=null),
              v = r ],
            each [i]<List.Count(TrimClean),
            each [
                i = [i]+1,
                r = List.RemoveLastN(TrimClean{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],"|")
        ),
        HeadreRow  = Record.FromList(NewNames,Table.ColumnNames(t))
    in Table.PromoteHeaders(Table.InsertRows(Table.Skip(t,nrows),0,{HeadreRow})),
    f2 = (t,columns)=> let
        Unpivot  = Table.UnpivotOtherColumns(t,List.Select(Table.ColumnNames(t),each not Text.Contains(_,"|")),"Атрибут","Значение"),
        Split    = Table.SplitColumn(Unpivot, "Атрибут",each Text.Split(_,"|"),columns),
        Pivot    = Table.Pivot(Split, List.Distinct(Split[Атрибут]), "Атрибут", "Значение"),
        WeekDays = List.Transform({1..14},each Text.Proper(Date.ToText(#date(1,1,_),"ddd"&(if _>7 then "d" else ""),"ru-ru"))),
        Replace  = Table.ReplaceValue(Pivot,0,0,(a,b,c)=>Number.Mod(List.PositionOf(WeekDays,a),7)+1,{"День"})
    in Replace,
    f3 = (tbl as table)=> let
        NewHeaders = Table.FillDown(f1(Table.Skip(tbl,each [Column1]<>"День"),2),{"День"}),
        Group      = Table.Group(
            NewHeaders, 
            {"День", "#"}, 
            List.Transform(
                List.Skip(Table.ColumnNames(NewHeaders),2),
                each let s = _
                     in {s,(t)=>Replacer.ReplaceValue(List.RemoveNulls(Table.Column(t,s)),{},null)}
            ),
            0,
            (a,b)=>Number.From(b[#"#"]<>null)
        )
    in Table.ExpandListColumn(Table.ExpandListColumn(f2(Group,{"Класс","Атрибут"}), "Предмет"), "Каб."),
    Source          = Excel.Workbook(File.Contents("C:\TEMP\Расписание классов 0112.xlsx"), null, true)[[Data]],
    Source2         = Excel.Workbook(File.Contents("C:\TEMP\Расписание учителей 0112.xlsx"), null, true)[Data]{1},
    ClassSchedule   = Table.ExpandTableColumn(Table.TransformColumns(Source,{{"Data",f3}}), "Data", {"День", "#", "Класс", "Предмет", "Каб."}),
    TeacherSchedule = Table.TransformColumnTypes(f2(f1(Table.Skip(Source2,each [Column1]<>"#"),3),{"День", "Урок", "Атрибут"}),{{"Урок",type number}}),
    Nested          = Table.ExpandTableColumn(Table.NestedJoin(TeacherSchedule,{"День","Урок","Класс","Каб."},ClassSchedule,{"День","#","Класс","Каб."},""), "", {"Предмет"}),
    Sorted          = Table.Sort(Nested,{{"#", Order.Ascending}, {"День", Order.Ascending}, {"Урок", Order.Ascending}})
in
    Sorted
ADO. Как получить данные из закрытой книги?
 
Ну дык написано же
Цитата
Важно: если данные извлекаются только из одной строки, то следует все равно указать минимум две строки: А1:B10. Это особенность работы с запросами. При попытке указать только одну строку А1:A10 функция вернет значение ошибки. При этом первая строка воспринимается как заголовки. Т.е. данные должны начинаться как минимум со второй строк(A2), а в A1 - заголовок
замените в коде
Код
.Open "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};ReadOnly=1;DBQ=" & sFullFileName & ";"

на
Код
Dim sFileType$
Select Case Right(sFileName, 1)
    Case "s": sFileType = "8.0"
    Case "b": sFileType = "12.0"
    Case "x": sFileType = "12.0 xml"
    Case "m": sFileType = "12.0 macro"
End Select
.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel " & sFileType & _
      ";HDR=no;IMEX=1;"";Data Source=" & sFullFileName & ";"
Выбор данных из списка, не затирая предыдущие значения
 
Вариант с проверкой данных (в C4:F6)
SQL: получить все строки таблицы с количеством значений по определённому полю
 
а если так?
Код
    Dim ObjRs As Object, Sql$, Conn$
    Conn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;Data Source=" & DirPath
    Sql = "select t2.*, t1.countID from (select count(*) as countID,id from table group by id) t1 inner join table t2 on t1.id=t2.id"
    Set ObjRs = CreateObject("ADODB.Recordset")
    With ObjRs
        .Open Sql, Conn, 3, 4
        If Not .EOF Then Sheets.Add.[A1].CopyFromRecordset ObjRs Else MsgBox "что-то пошло не так..."
        .Close
    End With
    Set ObjRs = Nothing
Изменено: Андрей Лящук - 16 Дек 2019 09:41:37
График Парето. Сделать горизонтальную линию на отметке 80% для наглядности
 
Есть такое решение
Изменено: Андрей Лящук - 8 Дек 2019 18:58:37
SQL: получить все строки таблицы с количеством значений по определённому полю
 
Код
select t2.*, t1.countID from (select count(*) as countID,id from table group by id) t1 inner join table t2 on t1.id=t2.id
Power Query - как получить имя текущего запроса
 
Код
// порядок вычислений
    ((Arg)=>Table.RemoveColumns(                  // 12. удалили список столбцов
        Arg{0},     
        Table.ColumnNames(                        // 11. получили список столбцов
            Table.RemoveColumns(                  // 10. удалили первый элемент
                Table.RenameColumns(              // 8.  переименовали таблицу шага 2
                        Arg{1},
                        List.Zip({Arg{3},Arg{2}}) // 7.  составили список переименований
                ),
                Arg{2}{0}                         // 9.  получили первый элемент
            ),
         )
     ))(
        List.Buffer(                              // 6.  кладем список в буфер
            Record.ToList(                        // 5.  преобразовали в список
                [a=get_table( "ттт_2" ),          // 1.  получили таблицу через обертку get_table
                 b=get_table( "ттт_1" ),          // 2.  получили таблицу через обертку get_table
                 c=Table.ColumnNames(a),          // 3.  получили список столбцов
                 d=Table.ColumnNames(b)           // 4.  получили список столбцов
                ]
            )
        )
     )
Изменено: Андрей Лящук - 7 Дек 2019 16:17:08
Подсчет суммы ячеек формулой учитывая, что данные ячеек отображают время
 
ну дык вот эта формула
Цитата
Андрей Лящук написал:
=СУММ($A$1:A2)
Подсчет суммы ячеек формулой учитывая, что данные ячеек отображают время
 
В B1 формула
Код
=СУММ($A$1:A2)
и тянем вниз
Power Query - выделение в таблицах отключено, так как страница использует режим совместимости Internet Explorer
 
Ну дык правильно, остальное скриптами из JSon генерируется
Код
= Table.FromRecords(Json.Document(Web.Contents("https://webdata.pfts.ua:8484/sitedata/rest/eqnn-quotes/"))[quotes])
Ввод в Excel тригонометрической функции, Помощь с вводом.
 
Код
=2*(4*A2^2+TAN(ПИ()^2/4-A2^2))*(1+TAN(ПИ()^2/4-A2^2)^2)/TAN(ПИ()^2/4-A2^2)^3
Массивная формула
Код
=СУММ(ЕСЛИ({0;1;0};2;8*A2^2)/TAN(ПИ()^2/4-A2^2)^{1;2;3};2)
Изменено: Андрей Лящук - 4 Дек 2019 14:49:21
Макросом подобрать правильный ответ из перечня, макросом найти ответы на вопросы
 
еще вариант
Код
Sub BoldSum2()
    Dim Q As Range, A As Range
    With Application.FindFormat
        .Clear
        .Font.FontStyle = "полужирный"
    End With    
    With Application
        .ScreenUpdating = 0: .EnableEvents = 0
        With Range([B5], Cells(Rows.Count, 2).End(xlUp))
            .Offset(, 1).Resize(, 2).ClearContents
            On Error Resume Next
            Set A = [B5]
            Do
                Set Q = .Find("*:", A, xlFormulas, 1, 1, 1, 0, 0, True)
                If Q.Row < A.Row Then Exit Do
                Set A = .Find("*", A, xlFormulas, 1, 1, 1, 0, 0, True)
                Do While IsEmpty(A(0))
                    Set Q = A
                    Set A = .Find("*", Q, xlFormulas, 1, 1, 1, 0, 0, True)
                    DoEvents
                Loop
                Q.Offset(, 1) = A: Q.Offset(, 2) = "в": A.Offset(, 2) = "о"
                DoEvents
            Loop
        End With
        .ScreenUpdating = 1: .EnableEvents = 1
    End With
End Sub
Формат ячейки, при котором отображается прочерк или числовое значение без изменений
 
как бы
Код
Основной;Основной;-;@
Ввод в Excel тригонометрических функций, Тригонометрические функции в экселе.
 
функция ПИ должна быть со скобками
Перевод номера в установленный формат, Пробовала автозамену, формулы, пока выход так и не нашла
 
=ТЕКСТ(СУММ(ОСТАТ(ПСТР(ПОДСТАВИТЬ(G7;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);9^9^{1;1;0}/1%)*10^{8;2;0});"0000-000000-00")
Изменено: Андрей Лящук - 1 Дек 2019 10:06:11
Объединить ячейки в один столбец excel, Объединить значения из ячеек в один столбец
 
Код
=ИНДЕКС(A:G;ОСТАТ(СТРОКА()-2;ЧСТРОК(2:18))+2;ОТБР((СТРОКА()-2)/ЧСТРОК(2:18))+1)
Перевод номера в установленный формат, Пробовала автозамену, формулы, пока выход так и не нашла
 
Код
=ТЕКСТ(СУММ(ОСТАТ(ПСТР(ПОДСТАВИТЬ(G7;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);9^9^{1;1;0}/1%)*10^{7;2;0});"0000-00000-00")
Создание файла JSON или как с помощью Power Query парсить сайты.
 
Цитата
Slava977 написал:
обращаться к api
для начала нужно, чтобы к нему был доступ. У вас есть? Не думаю.
Ладно, допустим у меня есть ссылка https://api.edadeal.ru/web/localities/moskva, кто мне даст структуру для разбора возвращаемого по ней бинарника?
Переход на сайт при вводе формулы в ячейку
 
Код
Function ya()
    Application.Volatile False
    Shell Environ("comspec") & " /c start """" http://ya.ru", vbHide
End Function
Функция для смены формата ячейки
 
в стандартном модуле
Код
Option Explicit
#If VBA7 Then
    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Declare PtrSafe Function GetMemObj Lib "msvbvm60" (ByVal pSrc As LongPtr, ByRef MyObj As Object) As Long
#Else
    Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
    Declare Function GetMemObj Lib "msvbvm60" (ByVal pSrc As Long, ByRef MyObj As Object) As Long
#End If
Public Function AngleForm(ByVal Value As Variant, Optional sFromat As String = "0\°.00\'00\.0\'\'") As Variant
    Static oDic As Object
    If oDic Is Nothing Then Set oDic = CreateObject("scripting.dictionary")
    Dim t As New task
    Set t.Rng = Application.Caller
    t.sFormat = sFromat
    Set oDic(t.Rng) = t
    AngleForm = Round(Value, 5)
    SetTimer Application.hwnd, VarPtr(oDic), 0, AddressOf SetFormat
End Function
#If VBA7 Then
    Sub SetFormat(ByVal hwnd As LongPtr, ByVal uMsg As Long, ByVal nIDEvent As LongPtr, ByVal dwTimer As Long)
#Else
    Sub SetFormat(ByVal hwnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
#End If
    Dim oDic As Object, key
    KillTimer hwnd, nIDEvent
    GetMemObj nIDEvent, oDic
    For Each key In oDic.keys
        With oDic(key)
            .Rng.NumberFormat = .sFormat
            oDic.Remove key
        End With
    Next
End Sub

в модуле класса task
Код
Option Explicit

Public Rng As Range
Public sFormat As String
Изменено: Андрей Лящук - 30 Ноя 2019 10:39:54
Выполнить сцепку ячеек добавляя пробелы
 
1 UDF
Код
Function JoinStr$(arr, Optional delim$ = "")
    JoinStr$ = Join(arr, delim)
End Function

и в ячейке массивная формула
Код
=JoinStr(Левб(ЗАМЕНИТЬ(ПОВТОР(" ";B$7:M$7);1;ДЛСТР(СЖПРОБЕЛЫ(B8:M8));СЖПРОБЕЛЫ(B8:M8));B$7:M$7))


или UDF
Код
Function JoinFixedLenghts$(l As Variant, s As Variant)
    Dim n, i
    For Each n In l
        i = i + 1
        stmp = Left(Trim(s(i)), n)
        stmp = stmp & Space(n - Len(stmp))
        x = x & stmp
    Next
End Function

и в ячейке
Код
=JoinFixedLengths(B$7:M$7;B8:M8)
Изменено: Андрей Лящук - 28 Ноя 2019 13:54:46
Сгрупировать структуру категорий PQ
 
вариант с рекурсией
Код
let
    fn = (t,optional depth)=>
        let 
            depth=if depth=null then 1 else depth, 
            t=Table.AddColumn(t, "x", each
                let 
                    Childs  = Table.SelectRows(Source,(r)=>r[parent_id]=[parent])[category_id],
                    ToTable = Table.FromColumns({Childs},{"parent"}),
                    Dummy   = #table({"parent","x"},{})
                in if List.Count(Childs)>0 then @fn(ToTable,depth+1) else Dummy
            ),
            AllColumns    = Table.ColumnNames(t[x]{0}),
            NeededColumns = List.RemoveMatchingItems(AllColumns,{"x"}),
            NewColumns    = {"child"&Text.From(depth)}&List.RemoveMatchingItems(AllColumns,{"parent","x"})
        in Table.ExpandTableColumn(t,"x",NeededColumns,NewColumns),
    Source   = Table.Buffer(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]),
    TopLevel = Table.FromColumns({List.Difference(List.Distinct(Source[parent_id]),Source[category_id])},{"parent"}),
    Chains   = fn(TopLevel)
in
    Chains
Изменено: Андрей Лящук - 27 Ноя 2019 22:04:36
Как перемножить 2 строки с 2-мя условиями?
 
оно?
Код
=СУММПРОИЗВ((C7:AL7*C8:AL8)/СЧЁТЕСЛИМН($C$4:$AL$4;$AR$4;$C$3:$AL$3;"<"&$AT$1-25)/AR7;($C$4:$AL$4=$AR$4)*($C$3:$AL$3<$AT$1-25))
Как организовать три подчиненных выпадающих списка статей?.., Как организовать три подчиненных выпадающих списка статей?..
 
Тут вкладывал пример реализации многоуровневого выпадающего списка на UDF
Преобразование табеля в плоскую таблицу с помощью Power Query
 
немного мозговыноса
Код
let
    Источник    = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="source"]}[Content]{0}[Column1]), null, true){[Item="табель",Kind="Sheet"]}[Data],
    Date        = Excel.CurrentWorkbook(){[Name="date"]}[Content]{0}[Column1],
    Headers     = Table.ToRows(Table.TransformColumns(Table.Transpose(Table.Range(Источник,3,8)),List.Zip({List.Transform({1..8},each "Column"&Text.From(_)),List.Repeat({each Text.From(_)},8)}))),
    NewHeaders  = List.Generate(()=>[i=0,r=List.RemoveLastN(Headers{i},each _=null),n=0,v=r],each [i]<List.Count(Headers),each [i=[i]+1,r=List.RemoveLastN(Headers{i},each _=null),n=List.PositionOf(r,null,0,(a,b)=>a<>b),v=let prev=[v] in List.Generate(()=>[i=0,s=if r{i}=null or r{i}="Из них по причинам" then prev{i} else r{i}], each [i]<List.Count(r),each [i=[i]+1,s=if r{i}=null or r{i}="В т.ч." then prev{i} else r{i}], each try if [s]="Из них" then null else [s] otherwise null)],each Text.Combine([v],"|")),
    AddHeaders  = Table.PromoteHeaders(Table.InsertRows(Table.Skip(Источник,11),0,{Record.FromList(NewHeaders,Table.ColumnNames(Источник))})),
    FilledDown  = Table.FillDown(AddHeaders,{"№ п/п", "Фамилия И.О.", "Табельный номер"}),
    ReplaceNull = Table.ReplaceValue(FilledDown,null,"",Replacer.ReplaceValue,Table.ColumnNames(FilledDown)),
    Indexed     = Table.TransformColumns(Table.AddIndexColumn(ReplaceNull, "Индекс", 1),{{"Индекс", each Number.Mod(_-1, 4)+1, type number}}),
    Unpivot     = Table.UnpivotOtherColumns(Indexed, {"№ п/п", "Фамилия И.О.", "Табельный номер","Индекс"}, "Атрибут", "Значение"),
    ToDate      = Table.SplitColumn(Unpivot, "Атрибут", each {try #date(Date.Year(Date),Date.Month(Date),Number.From(Text.AfterDelimiter(_,"|"))) otherwise null}, {"Дата"}),
    Group       = Table.Group(ToDate, {"№ п/п", "Фамилия И.О.", "Табельный номер", "Дата"}, {{"_",(t)=>let Rows=List.RemoveMatchingItems(List.Split(List.Transform({3,1,4,2},each t{[Индекс=_]}?[Значение]?),2),{{}}) in Table.SelectRows(Table.FromRows(Rows,{"Вид рабочего времени","Табельное время"}), each [Вид рабочего времени]<>null and [Вид рабочего времени]<>""), type table}}),
    Filtered    = Table.SelectRows(Group, each ([Дата] <> null) and Table.RowCount([_])>0),
    Expand      = Table.ExpandTableColumn(Filtered, "_", {"Вид рабочего времени", "Табельное время"}),
    Result      = Table.ReplaceValue(Expand,"",null,Replacer.ReplaceValue,Table.ColumnNames(Expand))
in
    Result
Список по условию из двумерного массива
 
=ЕСЛИОШИБКА(ИНДЕКС(schedule!B:B;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ(ЕСЛИОШИБКА(ПОИСКПОЗ(СМЕЩ(schedule!A$1;6;D$2-1;E$2;D$3-D$2);{"V";"C";"CH"};)^0;);СТРОКА(СМЕЩ(A$1;D$2-1;;D$3-D$2))^0);СТРОКА(СМЕЩ(A$1;6;;E$2)));СТРОКА(СМЕЩ(A$1;;;E$2))));"")
Код
=ЕСЛИОШИБКА(ИНДЕКС(schedule!B:B;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ(ЕСЛИОШИБКА(ПОИСКПОЗ(ИНДЕКС(schedule!$A:$AAA;7;D$2):ИНДЕКС(schedule!$A:$AAA;E$2;D$3);{"V";"C";"CH"};)^0;);СТРОКА(ИНДЕКС(A:A;D$2):ИНДЕКС(A:A;D$3))^0);СТРОКА($A$7:ИНДЕКС(A:A;E$2)));СТРОКА($A$1:ИНДЕКС(A:A;E$2-6))));"")
Изменено: Андрей Лящук - 24 Ноя 2019 10:30:17
Страницы: 1 2 3 4 5 6 7 8 9 След.
Наверх