=ВПР(C6;ЕСЛИ({1;0};данные!$C$5:$C$10;данные!$A$5:$A$10);2;)

=СУММ((Лист2!A2:A19=ЕСЛИ((ДЛСТР(Z4:DZ4)>0)*(ОСТАТ(СТОЛБЕЦ(F4:DF4);6)=0);W4:DW4))*Лист2!E2:E19)

=ВПР(C6;данные!$C$5:$D$10;2;)

Table.FromRecords(Json.Document([Content])[productModsSearch][search][results])

Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_})))

Table.SelectColumns(_,col[results])

Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]}))

let
    ExpandAll = (TableToExpand as table, optional ColumnNumber as number) as table =>
    let
        ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
        ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
        ColumnContents = Table.Column(TableToExpand, ColumnName),
        ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
        NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
        CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
        ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
        NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1
    in if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else fn{0}(ExpandedTable, NextColumnNumber),
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
            name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = {
        ExpandAll, //callback для рекурсии
        each {[file=[Folder Path]&[Name],results=Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_}))),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
        {"results",each Table.TransformColumns(Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]})),{{"vendor",each Table.FromRecords(
            {Record.SelectFields(_,col[vendor])})},{"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),
            {"specGuruModelcard",Table.FromRecords})},{"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})}
    },
    step1 = ExpandAll(Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn{1})),fn{2})),
    step2 = Table.ReplaceValue(step1,"",null,Replacer.ReplaceValue,{col[value]}),
    step3 = Table.Pivot(step2, List.Distinct(Table.Column(step2,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step3

Option Explicit
Private Declare Function GetClassName& Lib "user32" Alias "GetClassNameA" (ByVal hwnd&, ByVal lpClassName$, ByVal nMaxCount&)
Private Declare Function AccessibleObjectFromWindow& Lib "oleacc" (ByVal hwnd&, ByVal dwId&, riid As GUID, xlWB As Object)
Private Declare Function GetDesktopWindow& Lib "user32" ()
Private Declare Function GetWindow& Lib "user32" (ByVal hwnd&, ByVal wCmd&)
Private Const GW_HWNDNEXT = 2
Private Const GW_CHILD = 5
Private Const OBJID_NATIVEOM = &HFFFFFFF0
Private Type GUID
    lData1 As Long
    iData2 As Integer
    iData3 As Integer
    aBData4(0 To 7) As Byte
End Type
Private IDispatch As GUID, oWnd As Window
Public Sub AddReference()
    Dim i&
    With IDispatch
        .lData1 = &H20400: .iData2 = &H0: .iData3 = &H0
        .aBData4(0) = &HC0: .aBData4(1) = &H0: .aBData4(2) = &H0
        .aBData4(3) = &H0: .aBData4(4) = &H0: .aBData4(5) = &H0
        .aBData4(6) = &H0: .aBData4(7) = &H46
    End With
    Referece2AllWorkbooks 0, "EXCEL7", 0, 0, 0, Application.UserLibraryPath & "MyFunction.xla"
    Set oWnd = Nothing
End Sub
Private Function Referece2AllWorkbooks&(hWndStart&, ClassName$, level&, lHolder&, lCnt&, sFile$)
    Dim hwnd&, sWindowTitle$, sClassName$, wb as Workbook
    If level = 0 Then
        If hWndStart = 0 Then
            hWndStart = GetDesktopWindow()
        End If
    End If
    
    'Increase recursion counter
    '--------------------------
    level = level + 1
    
    'Get first child window
    '----------------------
    hwnd = GetWindow(hWndStart, GW_CHILD)
    
    Do While hwnd > 0
        'Search children by recursion
        '----------------------------
        lHolder = Referece2AllWorkbooks(hwnd, ClassName, level, lHolder, lCnt, sFile)
        
        'get the class name
        '------------------
        sClassName = Space$(255)
        r = GetClassName(hwnd, sClassName, 255)
        sClassName = Left$(sClassName, r)
        
        If sClassName Like ClassName & "*" Or sClassName = ClassName Then
            Referece2AllWorkbooks = hwnd
            lHolder = hwnd
            AccessibleObjectFromWindow hwnd, OBJID_NATIVEOM, IDispatch, oWnd
            If Not oWnd Is Nothing Then
                If oWnd.Visible Then
                    lCnt = lCnt + 1
                    On Error Resume Next
                    For Each wb In oWnd.Application.Workbooks
                        wb.VBProject.References.AddFromFile sFile
                    Next
                End If
            End If
        End If
        
        'Get next child window
        '---------------------
        hwnd = GetWindow(hwnd, GW_HWNDNEXT)
    Loop
    Referece2AllWorkbooks = lHolder
    
End Function

let
    col = [results={"id", "vendor", "shortName", "titles", "offers", "isNew", "parentId", "specGroups"},vendor={"id", "name"},specGroups="specGuruModelcard",
            name="results.specGroups.specGuruModelcard.name",value="results.specGroups.specGuruModelcard.value"],
    fn = (tbl as table, column as text, columns as list) as table => Table.ExpandTableColumn(tbl, column, columns, List.Transform(columns,each column & "." & _)),
    fn1 = each {[file=[Folder Path]&[Name],results=Table.Combine(List.Transform(Json.Document([Content])[productModsSearch][search][results],each Table.FromRecords({_}))),
            total=Json.Document([Content])[productModsSearch][search][total]]}{0},
    fn2 = {"results",each Table.TransformColumns(Table.SelectColumns(_,List.Intersect({Table.ColumnNames(_),col[results]})),{{"vendor",each Table.FromRecords(
            {Record.SelectFields(_,col[vendor])})},{"specGroups",each Table.TransformColumns(Table.SelectColumns(Table.FromRecords(_),col[specGroups]),
            {"specGuruModelcard",Table.FromRecords})},{"offers",each Table.FromRecords({_})},{"titles",each Table.FromRecords({_})}})},
    step1 = (Table.TransformColumns(Table.FromRecords(Table.TransformRows(Folder.Files(GetValue("pathName")),fn1)),fn2)),
    step2  = fn(fn(fn(fn(step1, "results", col[results]),"results.vendor", col[vendor]),"results.specGroups", {"specGuruModelcard"}),"results.offers", {"count"}),
    step3 = fn(fn(step2, "results.specGroups.specGuruModelcard", {"name", "value"}),"results.titles", {"raw"}),
    step4 = Table.ReplaceValue(step3,"",null,Replacer.ReplaceValue,{col[value]}),
    step5 = Table.Pivot(step4,List.Distinct(Table.Column(step4,col[name])),col[name],col[value],each Text.Combine(_,"|"))
in
    step5

=ПРОСМОТР(ТРАНСП(RndUnicArr(СУММ($E:$E)))-1;МУМНОЖ((СТРОКА($E$1:$E$54)>=ТРАНСП(СТРОКА($E$1:$E$54)))*Ч(ТРАНСП($E$1:$E$54));СТРОКА($E$1:$E$54)^0);$A$2:$A$54)

Sub d()
    Dim start&, count&
    start = 3: count = 5
    Dim rng As Range
    With ActiveDocument.Shapes(1).TextFrame.TextRange
        Set rng = .Characters(start)
        rng.End = .Characters(start + count).End
        rng.Select
    End With
End Sub

function RndUnicArr(cnt) {
    return Array.apply(null, Array(cnt))
        .map(Number.call, Number)
        .map(function(n){return [Math.random(),n]})
        .sort().map(function(n){return n[1]})
}

=ArrayFormula(ПРОСМОТР(RndUnicArr(СУММ($E:$E));МУМНОЖ((СТРОКА($E$1:$E$54)>=ТРАНСП(СТРОКА($E$1:$E$54)))*Ч(ТРАНСП($E$1:$E$54));СТРОКА($E$1:$E$54)^0);A2:A54))

=СЧЁТЕСЛИ(A$1:A$10;B1&"*")

=1-СЧЁТ(1/(ПРОСМОТР(--(J$5&"."&МАКС($J$4:J$4));КОНМЕСЯЦА(--$G7:$H7;{-1;0}))=КОНМЕСЯЦА($G7;-1)))

=РАБДЕНЬ(C4-1;1;ДАННЫЕ!$E$2:$E$13)>C4

Private Const Login$ = "Логин", Pwd$ = "Пароль"
Private Const Host$ = "https://webdav.yandex.ru:443/"
Public Function DownloadFile(RemoteFilePath$, SaveTo$)
    Dim FileContents() As Byte, LocalFilePath$
    SaveTo = IIf(Right(SaveTo, 1) = "\", SaveTo, SaveTo & "\")
    With CreateObject("MSXML2.XMLHTTP")
        .Open "get", urlencode(Host & RemoteFilePath), False, Login, Pwd
        .setrequestheader "Host", "webdav.yandex.ru"
        .setrequestheader "Accept", "*/*"
        .setrequestheader "Authorization", "Basic " & Token
        .send
        FileContents = .responseBody
    End With
    LocalFilePath = SaveTo & StrReverse(Split(StrReverse(RemoteFilePath), "/")(0))
    If Dir(LocalFilePath) <> "" Then Kill LocalFilePath
    Open LocalFilePath For Binary Access Write As #1
    Put #1, 1, FileContents
    Close #1
    DownloadFile = LocalFilePath
End Function
Public Sub UploadFile(LocalFilePath$, RemotePath$)
    Dim FileContents As Variant, FileName$
    FileName = StrReverse(Split(StrReverse(LocalFilePath), "\")(0))
    RemotePath = IIf(RemotePath <> "", RemotePath & "/", "")
    With CreateObject("ADODB.Stream")
        .Type = 1: .Open: .LoadFromFile LocalFilePath: FileContents = .Read: .Close
    End With
    With CreateObject("MSXML2.XMLHTTP")
        .Open "put", urlencode(Host & RemotePath & FileName), False, Login, Pwd
        .setrequestheader "Host", "webdav.yandex.ru"
        .setrequestheader "Accept", "*/*"
        .setrequestheader "Transfer-Encoding", "chunked"
        .setrequestheader "Etag", MD5(FileContents)
        .setrequestheader "Sha256", Sha256(FileContents)
        .setrequestheader "Expect", "100-continue"
        .setrequestheader "Content-Type", "application/binary"
        .setrequestheader "Authorization", "Basic " & Token
        .setrequestheader "Content-Length", UBound(FileContents) + 1
        .send FileContents
    End With
End Sub
Private Function Str2Byte(str$) As Byte()
    Str2Byte = StrConv(str, vbFromUnicode)
End Function
Private Function urlencode$(url$)
    With CreateObject("scriptcontrol")
        .Language = "JavaScript"
        urlencode = .eval("encodeURI('" & url & "')")
    End With
End Function
Private Function MD5(ByVal bytes) As String
    Dim sTmp$, i%, byteArr() As Byte
    byteArr = bytes
    With CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider")
        byteArr = .ComputeHash_2(byteArr)
    End With
    For i = 0 To UBound(byteArr)
        sTmp = sTmp & LCase(Right("0" & Hex(byteArr(i)), 2))
    Next
    MD5 = sTmp
End Function
Private Function Sha256(ByVal bytes) As String
    Dim sTmp$, i%, byteArr() As Byte
    byteArr = bytes
    With CreateObject("System.Security.Cryptography.SHA256Managed")
        byteArr = .ComputeHash_2(byteArr)
    End With
    For i = 0 To UBound(byteArr)
        sTmp = sTmp & LCase(Right("0" & Hex(byteArr(i)), 2))
    Next
    Sha256 = sTmp
End Function
Private Function Token()
    With CreateObject("MSXML2.DOMDocument").createElement("b64")
        .DataType = "bin.base64"
        .nodeTypedValue = Str2Byte(Login): Token = .Text & ":"
        .nodeTypedValue = Str2Byte(Pwd): Token = Token & .Text
    End With
End Function

Sub test()
    'открываем файл из папки 111 в ЯДиске
    Workbooks.Open DownloadFile("123/1.xlsm", "D:\")
    'открываем файл из корня ЯДиска
    Workbooks.Open DownloadFile("123.xlsx", Environ("tmp"))
    'выгружаем файл в папку 123 в ЯДиске
    UploadFile "D:\0.xlsm", "123"
    'выгружаем файл в корень в ЯДиска    не грузит
    UploadFile "D:\0.xlsm", ""
End Sub

CreateObject("WScript.Network").MapNetworkDrive "W:", "https://webdav.yandex.ru:443/Документы/Клиент/Templates", False, "Login", "Password"